From 5bca7cdc208dd5c9261e47e7350e37ea9692d6ed Mon Sep 17 00:00:00 2001 From: Boris Kolpackov Date: Mon, 22 Aug 2011 10:47:30 +0200 Subject: Enable foreign key constraints checking in SQLite Due to bugs in SQLite DDL foreign key support, we have to temporarily disable foreign keys when re-creating the schema. New manual section: 12.5.3, "Foreign Key Constraints". --- boost/database.hxx | 13 +++++++++++-- composite/database.hxx | 13 +++++++++++-- container/database.hxx | 13 +++++++++++-- hello/database.hxx | 13 +++++++++++-- inheritance/database.hxx | 13 +++++++++++-- inverse/database.hxx | 13 +++++++++++-- mapping/database.hxx | 13 +++++++++++-- qt/database.hxx | 13 +++++++++++-- query/database.hxx | 13 +++++++++++-- relationship/database.hxx | 13 +++++++++++-- schema/custom/driver.cxx | 19 ++++++++++++++++++- template/database.hxx | 13 +++++++++++-- 12 files changed, 139 insertions(+), 23 deletions(-) diff --git a/boost/database.hxx b/boost/database.hxx index 612db0a..d47fac3 100644 --- a/boost/database.hxx +++ b/boost/database.hxx @@ -19,6 +19,7 @@ #if defined(DATABASE_MYSQL) # include #elif defined(DATABASE_SQLITE) +# include # include # include # include @@ -55,12 +56,20 @@ create_database (int& argc, char* argv[]) new odb::sqlite::database ( argc, argv, false, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE)); - // Create the database schema. + // Create the database schema. Due to bugs in SQLite foreign key + // support for DDL statements, we need to temporarily disable + // foreign keys. // { - transaction t (db->begin ()); + connection_ptr c (db->connection ()); + + c->execute ("PRAGMA foreign_keys=OFF"); + + transaction t (c->begin ()); schema_catalog::create_schema (*db); t.commit (); + + c->execute ("PRAGMA foreign_keys=ON"); } #elif defined(DATABASE_PGSQL) auto_ptr db (new odb::pgsql::database (argc, argv)); diff --git a/composite/database.hxx b/composite/database.hxx index d1b9292..2a13381 100644 --- a/composite/database.hxx +++ b/composite/database.hxx @@ -19,6 +19,7 @@ #if defined(DATABASE_MYSQL) # include #elif defined(DATABASE_SQLITE) +# include # include # include # include @@ -55,12 +56,20 @@ create_database (int& argc, char* argv[]) new odb::sqlite::database ( argc, argv, false, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE)); - // Create the database schema. + // Create the database schema. Due to bugs in SQLite foreign key + // support for DDL statements, we need to temporarily disable + // foreign keys. // { - transaction t (db->begin ()); + connection_ptr c (db->connection ()); + + c->execute ("PRAGMA foreign_keys=OFF"); + + transaction t (c->begin ()); schema_catalog::create_schema (*db); t.commit (); + + c->execute ("PRAGMA foreign_keys=ON"); } #elif defined(DATABASE_PGSQL) auto_ptr db (new odb::pgsql::database (argc, argv)); diff --git a/container/database.hxx b/container/database.hxx index 0e407d4..9a30039 100644 --- a/container/database.hxx +++ b/container/database.hxx @@ -19,6 +19,7 @@ #if defined(DATABASE_MYSQL) # include #elif defined(DATABASE_SQLITE) +# include # include # include # include @@ -55,12 +56,20 @@ create_database (int& argc, char* argv[]) new odb::sqlite::database ( argc, argv, false, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE)); - // Create the database schema. + // Create the database schema. Due to bugs in SQLite foreign key + // support for DDL statements, we need to temporarily disable + // foreign keys. // { - transaction t (db->begin ()); + connection_ptr c (db->connection ()); + + c->execute ("PRAGMA foreign_keys=OFF"); + + transaction t (c->begin ()); schema_catalog::create_schema (*db); t.commit (); + + c->execute ("PRAGMA foreign_keys=ON"); } #elif defined(DATABASE_PGSQL) auto_ptr db (new odb::pgsql::database (argc, argv)); diff --git a/hello/database.hxx b/hello/database.hxx index 398f014..b56851d 100644 --- a/hello/database.hxx +++ b/hello/database.hxx @@ -19,6 +19,7 @@ #if defined(DATABASE_MYSQL) # include #elif defined(DATABASE_SQLITE) +# include # include # include # include @@ -55,12 +56,20 @@ create_database (int& argc, char* argv[]) new odb::sqlite::database ( argc, argv, false, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE)); - // Create the database schema. + // Create the database schema. Due to bugs in SQLite foreign key + // support for DDL statements, we need to temporarily disable + // foreign keys. // { - transaction t (db->begin ()); + connection_ptr c (db->connection ()); + + c->execute ("PRAGMA foreign_keys=OFF"); + + transaction t (c->begin ()); schema_catalog::create_schema (*db); t.commit (); + + c->execute ("PRAGMA foreign_keys=ON"); } #elif defined(DATABASE_PGSQL) auto_ptr db (new odb::pgsql::database (argc, argv)); diff --git a/inheritance/database.hxx b/inheritance/database.hxx index 76c61bd..af683a7 100644 --- a/inheritance/database.hxx +++ b/inheritance/database.hxx @@ -19,6 +19,7 @@ #if defined(DATABASE_MYSQL) # include #elif defined(DATABASE_SQLITE) +# include # include # include # include @@ -55,12 +56,20 @@ create_database (int& argc, char* argv[]) new odb::sqlite::database ( argc, argv, false, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE)); - // Create the database schema. + // Create the database schema. Due to bugs in SQLite foreign key + // support for DDL statements, we need to temporarily disable + // foreign keys. // { - transaction t (db->begin ()); + connection_ptr c (db->connection ()); + + c->execute ("PRAGMA foreign_keys=OFF"); + + transaction t (c->begin ()); schema_catalog::create_schema (*db); t.commit (); + + c->execute ("PRAGMA foreign_keys=ON"); } #elif defined(DATABASE_PGSQL) auto_ptr db (new odb::pgsql::database (argc, argv)); diff --git a/inverse/database.hxx b/inverse/database.hxx index feac5a8..8b97823 100644 --- a/inverse/database.hxx +++ b/inverse/database.hxx @@ -19,6 +19,7 @@ #if defined(DATABASE_MYSQL) # include #elif defined(DATABASE_SQLITE) +# include # include # include # include @@ -55,12 +56,20 @@ create_database (int& argc, char* argv[]) new odb::sqlite::database ( argc, argv, false, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE)); - // Create the database schema. + // Create the database schema. Due to bugs in SQLite foreign key + // support for DDL statements, we need to temporarily disable + // foreign keys. // { - transaction t (db->begin ()); + connection_ptr c (db->connection ()); + + c->execute ("PRAGMA foreign_keys=OFF"); + + transaction t (c->begin ()); schema_catalog::create_schema (*db); t.commit (); + + c->execute ("PRAGMA foreign_keys=ON"); } #elif defined(DATABASE_PGSQL) auto_ptr db (new odb::pgsql::database (argc, argv)); diff --git a/mapping/database.hxx b/mapping/database.hxx index 4d11615..4f3bb9a 100644 --- a/mapping/database.hxx +++ b/mapping/database.hxx @@ -19,6 +19,7 @@ #if defined(DATABASE_MYSQL) # include #elif defined(DATABASE_SQLITE) +# include # include # include # include @@ -55,12 +56,20 @@ create_database (int& argc, char* argv[]) new odb::sqlite::database ( argc, argv, false, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE)); - // Create the database schema. + // Create the database schema. Due to bugs in SQLite foreign key + // support for DDL statements, we need to temporarily disable + // foreign keys. // { - transaction t (db->begin ()); + connection_ptr c (db->connection ()); + + c->execute ("PRAGMA foreign_keys=OFF"); + + transaction t (c->begin ()); schema_catalog::create_schema (*db); t.commit (); + + c->execute ("PRAGMA foreign_keys=ON"); } #elif defined(DATABASE_PGSQL) auto_ptr db (new odb::pgsql::database (argc, argv)); diff --git a/qt/database.hxx b/qt/database.hxx index fac2841..6afccbe 100644 --- a/qt/database.hxx +++ b/qt/database.hxx @@ -19,6 +19,7 @@ #if defined(DATABASE_MYSQL) # include #elif defined(DATABASE_SQLITE) +# include # include # include # include @@ -55,12 +56,20 @@ createDatabase (int& argc, char* argv[]) new odb::sqlite::database ( argc, argv, false, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE)); - // Create the database schema. + // Create the database schema. Due to bugs in SQLite foreign key + // support for DDL statements, we need to temporarily disable + // foreign keys. // { - transaction t (db->begin ()); + connection_ptr c (db->connection ()); + + c->execute ("PRAGMA foreign_keys=OFF"); + + transaction t (c->begin ()); schema_catalog::create_schema (*db); t.commit (); + + c->execute ("PRAGMA foreign_keys=ON"); } #elif defined(DATABASE_PGSQL) auto_ptr db (new odb::pgsql::database (argc, argv)); diff --git a/query/database.hxx b/query/database.hxx index d3534cf..f96b9a3 100644 --- a/query/database.hxx +++ b/query/database.hxx @@ -19,6 +19,7 @@ #if defined(DATABASE_MYSQL) # include #elif defined(DATABASE_SQLITE) +# include # include # include # include @@ -55,12 +56,20 @@ create_database (int& argc, char* argv[]) new odb::sqlite::database ( argc, argv, false, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE)); - // Create the database schema. + // Create the database schema. Due to bugs in SQLite foreign key + // support for DDL statements, we need to temporarily disable + // foreign keys. // { - transaction t (db->begin ()); + connection_ptr c (db->connection ()); + + c->execute ("PRAGMA foreign_keys=OFF"); + + transaction t (c->begin ()); schema_catalog::create_schema (*db); t.commit (); + + c->execute ("PRAGMA foreign_keys=ON"); } #elif defined(DATABASE_PGSQL) auto_ptr db (new odb::pgsql::database (argc, argv)); diff --git a/relationship/database.hxx b/relationship/database.hxx index d05cc4c..eb55885 100644 --- a/relationship/database.hxx +++ b/relationship/database.hxx @@ -19,6 +19,7 @@ #if defined(DATABASE_MYSQL) # include #elif defined(DATABASE_SQLITE) +# include # include # include # include @@ -55,12 +56,20 @@ create_database (int& argc, char* argv[]) new odb::sqlite::database ( argc, argv, false, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE)); - // Create the database schema. + // Create the database schema. Due to bugs in SQLite foreign key + // support for DDL statements, we need to temporarily disable + // foreign keys. // { - transaction t (db->begin ()); + connection_ptr c (db->connection ()); + + c->execute ("PRAGMA foreign_keys=OFF"); + + transaction t (c->begin ()); schema_catalog::create_schema (*db); t.commit (); + + c->execute ("PRAGMA foreign_keys=ON"); } #elif defined(DATABASE_PGSQL) auto_ptr db (new odb::pgsql::database (argc, argv)); diff --git a/schema/custom/driver.cxx b/schema/custom/driver.cxx index d08bb62..1ca563c 100644 --- a/schema/custom/driver.cxx +++ b/schema/custom/driver.cxx @@ -6,6 +6,7 @@ #include #include +#include #include #include @@ -28,7 +29,17 @@ main (int argc, char* argv[]) // #if defined(DATABASE_MYSQL) || defined(DATABASE_SQLITE) { - transaction t (db->begin ()); + + // Due to bugs in SQLite foreign key support for DDL statements, + // we need to temporarily disable foreign keys. + // + connection_ptr c (db->connection ()); + +#ifdef DATABASE_SQLITE + c->execute ("PRAGMA foreign_keys=OFF"); +#endif + + transaction t (c->begin ()); // Try to drop the tables if they exist and ignore the error // if they don't. @@ -60,6 +71,10 @@ main (int argc, char* argv[]) "degree VARCHAR (255) NOT NULL)"); t.commit (); + +#ifdef DATABASE_SQLITE + c->execute ("PRAGMA foreign_keys=ON"); +#endif } #elif defined(DATABASE_PGSQL) { @@ -101,6 +116,8 @@ main (int argc, char* argv[]) t.commit (); } +#else +# error unknown database #endif // Create a few persistent objects. diff --git a/template/database.hxx b/template/database.hxx index fe815e9..cd7dd51 100644 --- a/template/database.hxx +++ b/template/database.hxx @@ -19,6 +19,7 @@ #if defined(DATABASE_MYSQL) # include #elif defined(DATABASE_SQLITE) +# include # include # include # include @@ -55,12 +56,20 @@ create_database (int& argc, char* argv[]) new odb::sqlite::database ( argc, argv, false, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE)); - // Create the database schema. + // Create the database schema. Due to bugs in SQLite foreign key + // support for DDL statements, we need to temporarily disable + // foreign keys. // { - transaction t (db->begin ()); + connection_ptr c (db->connection ()); + + c->execute ("PRAGMA foreign_keys=OFF"); + + transaction t (c->begin ()); schema_catalog::create_schema (*db); t.commit (); + + c->execute ("PRAGMA foreign_keys=ON"); } #elif defined(DATABASE_PGSQL) auto_ptr db (new odb::pgsql::database (argc, argv)); -- cgit v1.1