aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorBoris Kolpackov <boris@codesynthesis.com>2011-08-22 10:47:30 +0200
committerBoris Kolpackov <boris@codesynthesis.com>2011-08-22 10:47:30 +0200
commit5bca7cdc208dd5c9261e47e7350e37ea9692d6ed (patch)
tree797bf3151c060a98059ee343f8e0a1ca040930ad
parent0da4b5d62dc10993db4d55236cb175e891a9c62b (diff)
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".
-rw-r--r--boost/database.hxx13
-rw-r--r--composite/database.hxx13
-rw-r--r--container/database.hxx13
-rw-r--r--hello/database.hxx13
-rw-r--r--inheritance/database.hxx13
-rw-r--r--inverse/database.hxx13
-rw-r--r--mapping/database.hxx13
-rw-r--r--qt/database.hxx13
-rw-r--r--query/database.hxx13
-rw-r--r--relationship/database.hxx13
-rw-r--r--schema/custom/driver.cxx19
-rw-r--r--template/database.hxx13
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));