[core] Update SQLite schema with WAL journal mode and normal sync

Offers 2×-4× performance, depending on device vintage.

3 files changed, 17 insertions, 7 deletions

diff --git a/platform/default/mbgl/storage/offline_database.cpp b/platform/default/mbgl/storage/offline_database.cpp
index 46a2c2bc25..d93b1734b4 100644
--- a/platform/default/mbgl/storage/offline_database.cpp
+++ b/platform/default/mbgl/storage/offline_database.cpp
@@ -48,7 +48,8 @@ void OfflineDatabase::ensureSchema() {
             case 0: break; // cache-only database; ok to delete
             case 1: break; // cache-only database; ok to delete
             case 2: migrateToVersion3(); // fall through
-            case 3: return;
+            case 3: migrateToVersion4(); // fall through
+            case 4: return;
             default: throw std::runtime_error("unknown schema version");
             }
 
@@ -79,8 +80,10 @@ void OfflineDatabase::ensureSchema() {
 
         // If you change the schema you must write a migration from the previous version.
         db->exec("PRAGMA auto_vacuum = INCREMENTAL");
+        db->exec("PRAGMA synchronous = NORMAL");
+        db->exec("PRAGMA journal_mode = WAL");
         db->exec(schema);
-        db->exec("PRAGMA user_version = 3");
+        db->exec("PRAGMA user_version = 4");
     } catch (...) {
         Log::Error(Event::Database, "Unexpected error creating database schema: %s", util::toString(std::current_exception()).c_str());
         throw;
@@ -111,6 +114,12 @@ void OfflineDatabase::migrateToVersion3() {
     db->exec("PRAGMA user_version = 3");
 }
 
+void OfflineDatabase::migrateToVersion4() {
+    db->exec("PRAGMA synchronous = NORMAL");
+    db->exec("PRAGMA journal_mode = WAL");
+    db->exec("PRAGMA user_version = 4");
+}
+
 OfflineDatabase::Statement OfflineDatabase::getStatement(const char * sql) {
     auto it = statements.find(sql);
 
diff --git a/platform/default/mbgl/storage/offline_database.hpp b/platform/default/mbgl/storage/offline_database.hpp
index 55cb7ad2fa..00394aa720 100644
--- a/platform/default/mbgl/storage/offline_database.hpp
+++ b/platform/default/mbgl/storage/offline_database.hpp
@@ -60,6 +60,7 @@ private:
     void ensureSchema();
     void removeExisting();
     void migrateToVersion3();
+    void migrateToVersion4();
 
     class Statement {
     public:
diff --git a/test/storage/offline_database.cpp b/test/storage/offline_database.cpp
index 81da46bc78..bcea3cd8f3 100644
--- a/test/storage/offline_database.cpp
+++ b/test/storage/offline_database.cpp
@@ -559,18 +559,18 @@ TEST(OfflineDatabase, MigrateFromV2Schema) {
 
     // v2.db is a v2 database containing a single offline region with a small number of resources.
 
-    deleteFile("test/fixtures/offline_database/v3.db");
-    writeFile("test/fixtures/offline_database/v3.db", util::read_file("test/fixtures/offline_database/v2.db"));
+    deleteFile("test/fixtures/offline_database/v4.db");
+    writeFile("test/fixtures/offline_database/v4.db", util::read_file("test/fixtures/offline_database/v2.db"));
 
     {
-        OfflineDatabase db("test/fixtures/offline_database/v3.db", 0);
+        OfflineDatabase db("test/fixtures/offline_database/v4.db", 0);
         auto regions = db.listRegions();
         for (auto& region : regions) {
             db.deleteRegion(std::move(region));
         }
     }
 
-    EXPECT_EQ(3, databaseUserVersion("test/fixtures/offline_database/v3.db"));
-    EXPECT_LT(databasePageCount("test/fixtures/offline_database/v3.db"),
+    EXPECT_EQ(4, databaseUserVersion("test/fixtures/offline_database/v4.db"));
+    EXPECT_LT(databasePageCount("test/fixtures/offline_database/v4.db"),
               databasePageCount("test/fixtures/offline_database/v2.db"));
 }