From 5cefaeb85716b21f2bbe57165a244844114b8dbe Mon Sep 17 00:00:00 2001
From: John Firebaugh <john.firebaugh@gmail.com>
Date: Tue, 23 Feb 2016 18:02:52 -0800
Subject: [core] Enforce foreign key constraints in the offline database

---
 platform/default/mbgl/storage/offline_database.cpp  | 21 +++++++++++----------
 platform/default/mbgl/storage/offline_database.hpp  |  1 +
 .../default/mbgl/storage/offline_schema.cpp.include |  4 ++--
 platform/default/mbgl/storage/offline_schema.sql    |  4 ++--
 4 files changed, 16 insertions(+), 14 deletions(-)

(limited to 'platform/default')

diff --git a/platform/default/mbgl/storage/offline_database.cpp b/platform/default/mbgl/storage/offline_database.cpp
index aac5e4a54c..983b84a3b4 100644
--- a/platform/default/mbgl/storage/offline_database.cpp
+++ b/platform/default/mbgl/storage/offline_database.cpp
@@ -39,11 +39,16 @@ OfflineDatabase::~OfflineDatabase() {
     }
 }
 
+void OfflineDatabase::connect(int flags) {
+    db = std::make_unique<Database>(path.c_str(), flags);
+    db->setBusyTimeout(Milliseconds::max());
+    db->exec("PRAGMA foreign_keys = ON");
+}
+
 void OfflineDatabase::ensureSchema() {
     if (path != ":memory:") {
         try {
-            db = std::make_unique<Database>(path.c_str(), ReadWrite);
-            db->setBusyTimeout(Milliseconds::max());
+            connect(ReadWrite);
 
             {
                 auto userVersionStmt = db->prepare("PRAGMA user_version");
@@ -57,24 +62,20 @@ void OfflineDatabase::ensureSchema() {
             }
 
             removeExisting();
-            db = std::make_unique<Database>(path.c_str(), ReadWrite | Create);
-            db->setBusyTimeout(Milliseconds::max());
+            connect(ReadWrite | Create);
         } catch (mapbox::sqlite::Exception& ex) {
             if (ex.code == SQLITE_CANTOPEN) {
-                db = std::make_unique<Database>(path.c_str(), ReadWrite | Create);
-                db->setBusyTimeout(Milliseconds::max());
+                connect(ReadWrite | Create);
             } else if (ex.code == SQLITE_NOTADB) {
                 removeExisting();
-                db = std::make_unique<Database>(path.c_str(), ReadWrite | Create);
-                db->setBusyTimeout(Milliseconds::max());
+                connect(ReadWrite | Create);
             }
         }
     }
 
     #include "offline_schema.cpp.include"
 
-    db = std::make_unique<Database>(path.c_str(), ReadWrite | Create);
-    db->setBusyTimeout(Milliseconds::max());
+    connect(ReadWrite | Create);
     db->exec(schema);
     db->exec("PRAGMA user_version = " + util::toString(schemaVersion));
 }
diff --git a/platform/default/mbgl/storage/offline_database.hpp b/platform/default/mbgl/storage/offline_database.hpp
index 1e6666c2aa..f207ab406a 100644
--- a/platform/default/mbgl/storage/offline_database.hpp
+++ b/platform/default/mbgl/storage/offline_database.hpp
@@ -50,6 +50,7 @@ public:
     OfflineRegionStatus getRegionCompletedStatus(int64_t regionID);
 
 private:
+    void connect(int flags);
     void ensureSchema();
     void removeExisting();
 
diff --git a/platform/default/mbgl/storage/offline_schema.cpp.include b/platform/default/mbgl/storage/offline_schema.cpp.include
index 3068dadfe0..a80c7677e6 100644
--- a/platform/default/mbgl/storage/offline_schema.cpp.include
+++ b/platform/default/mbgl/storage/offline_schema.cpp.include
@@ -33,12 +33,12 @@ static const char * schema =
 "  description BLOB\n"
 ");\n"
 "CREATE TABLE region_resources (\n"
-"  region_id INTEGER NOT NULL REFERENCES regions(id),\n"
+"  region_id INTEGER NOT NULL REFERENCES regions(id) ON DELETE CASCADE,\n"
 "  resource_id INTEGER NOT NULL REFERENCES resources(id),\n"
 "  UNIQUE (region_id, resource_id)\n"
 ");\n"
 "CREATE TABLE region_tiles (\n"
-"  region_id INTEGER NOT NULL REFERENCES regions(id),\n"
+"  region_id INTEGER NOT NULL REFERENCES regions(id) ON DELETE CASCADE,\n"
 "  tile_id INTEGER NOT NULL REFERENCES tiles(id),\n"
 "  UNIQUE (region_id, tile_id)\n"
 ");\n"
diff --git a/platform/default/mbgl/storage/offline_schema.sql b/platform/default/mbgl/storage/offline_schema.sql
index cba922f3f7..9df8fa6a89 100644
--- a/platform/default/mbgl/storage/offline_schema.sql
+++ b/platform/default/mbgl/storage/offline_schema.sql
@@ -36,13 +36,13 @@ CREATE TABLE regions (
 );
 
 CREATE TABLE region_resources (
-  region_id INTEGER NOT NULL REFERENCES regions(id),
+  region_id INTEGER NOT NULL REFERENCES regions(id) ON DELETE CASCADE,
   resource_id INTEGER NOT NULL REFERENCES resources(id),
   UNIQUE (region_id, resource_id)
 );
 
 CREATE TABLE region_tiles (
-  region_id INTEGER NOT NULL REFERENCES regions(id),
+  region_id INTEGER NOT NULL REFERENCES regions(id) ON DELETE CASCADE,
   tile_id INTEGER NOT NULL REFERENCES tiles(id),
   UNIQUE (region_id, tile_id)
 );
-- 
cgit v1.2.1