From 3e57c662b42831555498607d607c349f30c5b7de 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". --- doc/manual.xhtml | 93 ++++++++++++++++++++++++++++++++++++++++++++++++-------- 1 file changed, 81 insertions(+), 12 deletions(-) (limited to 'doc') 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. - - + + +
12.5.1Query Result Caching
12.5.2Automatic Assignment of Object Ids
12.5.3Constraint Violations
12.5.4Sharing of Queries
12.5.3Foreign Key Constraints
12.5.4Constraint Violations
12.5.5Sharing of Queries
@@ -8523,12 +8524,14 @@ namespace odb public: database (const std::string& name, int flags = SQLITE_OPEN_READWRITE, + bool foreign_keys = true, std::auto_ptr<connection_factory> = 0); database (int& argc, char* argv[], bool erase = false, int flags = SQLITE_OPEN_READWRITE, + bool foreign_keys = true, std::auto_ptr<connection_factory> = 0); static void @@ -8566,11 +8569,15 @@ namespace odb :memory: special value, then a temporary, in-memory database is created. The flags argument allows us to specify SQLite opening flags. For more information on the possible - values, refer to the sqlite3_open_v2 function description - in the SQLite C API documentation. The following example shows how - we can open the test.db database in the read-write - mode and create it if it does not exist: -

+ values, refer to the sqlite3_open_v2() function description + in the SQLite C API documentation. The foreign_keys + argument specifies whether foreign key constraints checking + should be enabled. See Section 12.5.3, + "Foreign Key Constraints" for more information on foreign + keys.

+ +

The following example shows how we can open the test.db + database in the read-write mode and create it if it does not exist:

 auto_ptr<odb::database> db (
@@ -8613,7 +8620,7 @@ auto_ptr<odb::database> db (
 
   
The second constructor throws the odb::sqlite::cli_exception
      exception if the SQLite option values are missing or invalid.
-     See section Section 12.4, "SQLite Exceptions"
+     See Section 12.4, "SQLite Exceptions"
      for more information on this exception.

 
   
The static print_usage() function prints the list of options
@@ -8905,7 +8912,69 @@ class person
 };
-

12.5.3 Constraint Violations

+

12.5.3 Foreign Key Constraints

+ +

By default the SQLite ODB runtime enables foreign key constraints + checking (PRAGMA foreign_keys=ON). You can disable foreign + keys by passing false as the foreign_keys + argument to one of the odb::sqlite::database constructors. + Foreign keys will also be disabled if the SQLite library is built without + support for foreign keys (SQLITE_OMIT_FOREIGN_KEY and + SQLITE_OMIT_TRIGGER macros) or if you are using + an SQLite version prior to 3.6.19, which does not support foreign + key constraints checking.

+ +

If foreign key constraints checking is disabled or not available, + then inconsistencies in object relationships will not be detected. + Furthermore, using the erase_query() function (@@ ref) + to delete persistent objects that contain containers will not work + correctly. Container data for such objects will not be deleted.

+ +

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:

+ +
+#include <odb/connection.hxx>
+#include <odb/transaction.hxx>
+#include <odb/schema-catalog.hxx>
+
+odb::database& 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");
+}
+
+ +

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 DEFERRABLE INITIALLY DEFERRED, as + shown in the following example. Schemas generated by the ODB compiler + meet this requirement automatically.

+ + 
+CREATE TABLE Employee (
+  ...
+  employer BIGINT REFERENCES Employer (name) DEFERRABLE INITIALLY DEFERRED);
+
+ + +

12.5.4 Constraint Violations

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 object_not_persistent exception (Section 3.11, "ODB Exceptions").

-

12.5.4 Sharing of Queries

+

12.5.5 Sharing of Queries

As discussed in Section 4.3, "Executing a Query", a query instance that does not have any by-reference parameters is @@ -9442,8 +9511,8 @@ namespace odb

13.5.2 Foreign Key Constraints

-

ODB relies on the standard SQL behavior which requires that - the foreign key constraints checking is deferred until the +

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 -- cgit v1.1