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".

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 <odb/mysql/database.hxx>
 #elif defined(DATABASE_SQLITE)
+#  include <odb/connection.hxx>
 #  include <odb/transaction.hxx>
 #  include <odb/schema-catalog.hxx>
 #  include <odb/sqlite/database.hxx>
@@ -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<database> 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 <odb/mysql/database.hxx>
 #elif defined(DATABASE_SQLITE)
+#  include <odb/connection.hxx>
 #  include <odb/transaction.hxx>
 #  include <odb/schema-catalog.hxx>
 #  include <odb/sqlite/database.hxx>
@@ -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<database> 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 <odb/mysql/database.hxx>
 #elif defined(DATABASE_SQLITE)
+#  include <odb/connection.hxx>
 #  include <odb/transaction.hxx>
 #  include <odb/schema-catalog.hxx>
 #  include <odb/sqlite/database.hxx>
@@ -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<database> 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 <odb/mysql/database.hxx>
 #elif defined(DATABASE_SQLITE)
+#  include <odb/connection.hxx>
 #  include <odb/transaction.hxx>
 #  include <odb/schema-catalog.hxx>
 #  include <odb/sqlite/database.hxx>
@@ -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<database> 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 <odb/mysql/database.hxx>
 #elif defined(DATABASE_SQLITE)
+#  include <odb/connection.hxx>
 #  include <odb/transaction.hxx>
 #  include <odb/schema-catalog.hxx>
 #  include <odb/sqlite/database.hxx>
@@ -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<database> 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 <odb/mysql/database.hxx>
 #elif defined(DATABASE_SQLITE)
+#  include <odb/connection.hxx>
 #  include <odb/transaction.hxx>
 #  include <odb/schema-catalog.hxx>
 #  include <odb/sqlite/database.hxx>
@@ -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<database> 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 <odb/mysql/database.hxx>
 #elif defined(DATABASE_SQLITE)
+#  include <odb/connection.hxx>
 #  include <odb/transaction.hxx>
 #  include <odb/schema-catalog.hxx>
 #  include <odb/sqlite/database.hxx>
@@ -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<database> 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 <odb/mysql/database.hxx>
 #elif defined(DATABASE_SQLITE)
+#  include <odb/connection.hxx>
 #  include <odb/transaction.hxx>
 #  include <odb/schema-catalog.hxx>
 #  include <odb/sqlite/database.hxx>
@@ -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<database> 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 <odb/mysql/database.hxx>
 #elif defined(DATABASE_SQLITE)
+#  include <odb/connection.hxx>
 #  include <odb/transaction.hxx>
 #  include <odb/schema-catalog.hxx>
 #  include <odb/sqlite/database.hxx>
@@ -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<database> 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 <odb/mysql/database.hxx>
 #elif defined(DATABASE_SQLITE)
+#  include <odb/connection.hxx>
 #  include <odb/transaction.hxx>
 #  include <odb/schema-catalog.hxx>
 #  include <odb/sqlite/database.hxx>
@@ -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<database> 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 <iostream>
 
 #include <odb/database.hxx>
+#include <odb/connection.hxx>
 #include <odb/session.hxx>
 #include <odb/transaction.hxx>
 
@@ -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 <odb/mysql/database.hxx>
 #elif defined(DATABASE_SQLITE)
+#  include <odb/connection.hxx>
 #  include <odb/transaction.hxx>
 #  include <odb/schema-catalog.hxx>
 #  include <odb/sqlite/database.hxx>
@@ -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<database> db (new odb::pgsql::database (argc, argv));