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

3 files changed, 106 insertions, 12 deletions

diff --git a/NEWS b/NEWS
index 3f84ddb..0d5db63 100644
--- a/NEWS
+++ b/NEWS
@@ -1,5 +1,14 @@
 Version 1.6.0
 
+  * SQLite ODB runtime now enabled foreign key constraints checking by
+    default. While this should not affect correct applications, due to
+    bugs in SQLite DDL foreign keys support, you may need to temporarily
+    disable foreign key constraints checking when re-creating the database
+    schema (the sign that you may need to do so is the "foreign key
+    constraint failed" exception thrown by the commit() function after the
+    call to schema_catalog::create_schema()). For more information, refer
+    to Section 12.5.3, "Foreign Key Constraints" in the ODB manual.
+
   * Support for value wrappers. An ODB value wrapper is a class template
     that wraps a value type. Common examples of wrappers are smart pointers,
     holders, and "optional value" containers such as boost::optional. A
diff --git a/doc/manual.xhtml b/doc/manual.xhtml
index 1b7b4a7..246f4af 100644
--- a/doc/manual.xhtml
+++ b/doc/manual.xhtml
@@ -521,8 +521,9 @@ for consistency.
               <table class="toc">
                 <tr><th>12.5.1</th><td><a href="#12.5.1">Query Result Caching</a></td></tr>
 		<tr><th>12.5.2</th><td><a href="#12.5.2">Automatic Assignment of Object Ids</a></td></tr>
-		<tr><th>12.5.3</th><td><a href="#12.5.3">Constraint Violations</a></td></tr>
-		<tr><th>12.5.4</th><td><a href="#12.5.4">Sharing of Queries</a></td></tr>
+		<tr><th>12.5.3</th><td><a href="#12.5.3">Foreign Key Constraints</a></td></tr>
+		<tr><th>12.5.4</th><td><a href="#12.5.4">Constraint Violations</a></td></tr>
+		<tr><th>12.5.5</th><td><a href="#12.5.5">Sharing of Queries</a></td></tr>
               </table>
             </td>
           </tr>
@@ -8523,12 +8524,14 @@ namespace odb
     public:
       database (const std::string&amp; name,
                 int flags = SQLITE_OPEN_READWRITE,
+                bool foreign_keys = true,
                 std::auto_ptr&lt;connection_factory> = 0);
 
       database (int&amp; argc,
                 char* argv[],
                 bool erase = false,
                 int flags = SQLITE_OPEN_READWRITE,
+                bool foreign_keys = true,
                 std::auto_ptr&lt;connection_factory> = 0);
 
       static void
@@ -8566,11 +8569,15 @@ namespace odb
      <code>:memory:</code> special value, then a temporary, in-memory
      database is created. The <code>flags</code> argument allows us to
      specify SQLite opening flags. For more information on the possible
-     values, refer to the <code>sqlite3_open_v2</code> function description
-     in the SQLite C API documentation. The following example shows how
-     we can open the <code>test.db</code> database in the read-write
-     mode and create it if it does not exist:
-  </p>
+     values, refer to the <code>sqlite3_open_v2()</code> function description
+     in the SQLite C API documentation. The <code>foreign_keys</code>
+     argument specifies whether foreign key constraints checking
+     should be enabled. See <a href="#12.5.3">Section 12.5.3,
+     "Foreign Key Constraints"</a> for more information on foreign
+     keys.</p>
+
+  <p>The following example shows how we can open the <code>test.db</code>
+     database in the read-write mode and create it if it does not exist:</p>
 
   <pre class="c++">
 auto_ptr&lt;odb::database> db (
@@ -8613,7 +8620,7 @@ auto_ptr&lt;odb::database> db (
 
   <p>The second constructor throws the <code>odb::sqlite::cli_exception</code>
      exception if the SQLite option values are missing or invalid.
-     See section <a href="#12.4">Section 12.4, "SQLite Exceptions"</a>
+     See <a href="#12.4">Section 12.4, "SQLite Exceptions"</a>
      for more information on this exception.</p>
 
   <p>The static <code>print_usage()</code> function prints the list of options
@@ -8905,7 +8912,69 @@ class person
 };
 </pre>
 
-  <h3><a name="12.5.3">12.5.3 Constraint Violations</a></h3>
+  <h3><a name="12.5.3">12.5.3 Foreign Key Constraints</a></h3>
+
+  <p>By default the SQLite ODB runtime enables foreign key constraints
+     checking (<code>PRAGMA foreign_keys=ON</code>). You can disable foreign
+     keys by passing <code>false</code> as the <code>foreign_keys</code>
+     argument to one of the <code>odb::sqlite::database</code> constructors.
+     Foreign keys will also be disabled if the SQLite library is built without
+     support for foreign keys (<code>SQLITE_OMIT_FOREIGN_KEY</code> and
+     <code>SQLITE_OMIT_TRIGGER</code> macros) or if you are using
+     an SQLite version prior to 3.6.19, which does not support foreign
+     key constraints checking.</p>
+
+  <p>If foreign key constraints checking is disabled or not available,
+     then inconsistencies in object relationships will not be detected.
+     Furthermore, using the <code>erase_query()</code> function (@@ ref)
+     to delete persistent objects that contain containers will not work
+     correctly. Container data for such objects will not be deleted.</p>
+
+  <p>When foreign key constraints checking is enabled, then you may
+     get the "foreign key constraint failed" error while re-creating the
+     database schema. This error is due to bugs in the SQLite DDL foreign
+     keys support. The recommended work-around for this problem is to
+     temporarily disable foreign key constraints checking while
+     re-creating the schema. The following code fragment shows how
+     this can be done:</p>
+
+<pre>
+#include &lt;odb/connection.hxx>
+#include &lt;odb/transaction.hxx>
+#include &lt;odb/schema-catalog.hxx>
+
+odb::database&amp; db = ...
+
+{
+  odb::connection_ptr c (db.connection ());
+
+  c->execute ("PRAGMA foreign_keys=OFF");
+
+  odb::transaction t (c->begin ());
+  odb::schema_catalog::create_schema (db);
+  t.commit ();
+
+  c->execute ("PRAGMA foreign_keys=ON");
+}
+</pre>
+
+  <p>Finally, ODB relies on standard SQL behavior which requires
+     that foreign key constraints checking is deferred until the
+     transaction is committed. Default SQLite behavior is to check such
+     constraints immediately. As a result, when used with ODB, a custom
+     database schema that defines foreign key constraints must declare
+     such constraints as <code>DEFERRABLE INITIALLY DEFERRED</code>, as
+     shown in the following example. Schemas generated by the ODB compiler
+     meet this requirement automatically.</p>
+
+  <pre class="sql">
+CREATE TABLE Employee (
+  ...
+  employer BIGINT REFERENCES Employer (name) DEFERRABLE INITIALLY DEFERRED);
+  </pre>
+
+
+  <h3><a name="12.5.4">12.5.4 Constraint Violations</a></h3>
 
   <p>Due to the granularity of the SQLite error codes, it is impossible
      to distinguish between the duplicate primary key and other constraint
@@ -8914,7 +8983,7 @@ class person
      <code>object_not_persistent</code> exception (<a href="#3.11">Section
      3.11, "ODB Exceptions"</a>).</p>
 
-  <h3><a name="12.5.4">12.5.4 Sharing of Queries</a></h3>
+  <h3><a name="12.5.5">12.5.5 Sharing of Queries</a></h3>
 
   <p>As discussed in <a href="#4.3">Section 4.3, "Executing a Query"</a>, a
      query instance that does not have any by-reference parameters is
@@ -9442,8 +9511,8 @@ namespace odb
 
   <h3><a name="13.5.2">13.5.2 Foreign Key Constraints</a></h3>
 
-  <p>ODB relies on the standard SQL behavior which requires that
-     the foreign key constraints checking is deferred until the
+  <p>ODB relies on standard SQL behavior which requires that
+     foreign key constraints checking is deferred until the
      transaction is committed. Default PostgreSQL behavior is
      to check such constraints immediately. As a result, when
      used with ODB, a custom database schema that defines foreign
diff --git a/odb/relational/sqlite/schema.cxx b/odb/relational/sqlite/schema.cxx
index 253772d..77a9590 100644
--- a/odb/relational/sqlite/schema.cxx
+++ b/odb/relational/sqlite/schema.cxx
@@ -19,6 +19,7 @@ namespace relational
       //
       // Create.
       //
+
       struct object_columns: relational::object_columns, context
       {
         object_columns (base const& x): base (x) {}
@@ -61,6 +62,21 @@ namespace relational
           }
         }
 
+        virtual void
+        reference (semantics::data_member& m)
+        {
+          // In SQLite, by default, constraints are immediate.
+          //
+          if (semantics::class_* c = object_pointer (member_type (m, prefix_)))
+          {
+            os << " REFERENCES " << table_qname (*c) << " (" <<
+              column_qname (*id_member (*c)) << ") " <<
+              "DEFERRABLE INITIALLY DEFERRED";
+          }
+          else
+            base::reference (m);
+        }
+
       };
       entry<object_columns> object_columns_;
     }