[epiphany] Optimize sql database access

[Jan-Michael Brummer <jbrummer src gnome org>]

commit 2c687f0e0f0e5a1f2b57390ebe24bc572c8f12a0
Author: Jan-Michael Brummer <jan brummer tabos org>
Date:   Thu Jun 18 17:41:29 2020 +0200

    Optimize sql database access
    
    * Increase BATCH_SIZE in respect to new SQLITE_MAX_VARIABLE_NUMBER value
    * Switch to WAF journal mode, set synchronous to NORMAL and set cache
      size
    
    Fixes: https://gitlab.gnome.org/GNOME/epiphany/-/issues/790

 lib/ephy-sqlite-connection.c         | 13 ++++++++++---
 lib/safe-browsing/ephy-gsb-storage.c |  8 ++++----
 meson.build                          |  2 +-
 3 files changed, 15 insertions(+), 8 deletions(-)
---
diff --git a/lib/ephy-sqlite-connection.c b/lib/ephy-sqlite-connection.c
index 06574936f..18f1ab220 100644
--- a/lib/ephy-sqlite-connection.c
+++ b/lib/ephy-sqlite-connection.c
@@ -168,6 +168,10 @@ ephy_sqlite_connection_open (EphySQLiteConnection  *self,
     }
 
     sqlite3_close (init_db);
+  } else {
+    ephy_sqlite_connection_execute (self, "PRAGMA main.journal_mode=WAL", error);
+    ephy_sqlite_connection_execute (self, "PRAGMA main.synchronous=NORMAL", error);
+    ephy_sqlite_connection_execute (self, "PRAGMA main.cache_size=10000", error);
   }
 
   return TRUE;
@@ -185,18 +189,21 @@ ephy_sqlite_connection_close (EphySQLiteConnection *self)
 void
 ephy_sqlite_connection_delete_database (EphySQLiteConnection *self)
 {
-  char *journal;
+  g_autofree char *journal = NULL;
+  g_autofree char *shm = NULL;
 
   g_assert (EPHY_IS_SQLITE_CONNECTION (self));
 
   if (g_file_test (self->database_path, G_FILE_TEST_EXISTS) && g_unlink (self->database_path) == -1)
     g_warning ("Failed to delete database at %s: %s", self->database_path, g_strerror (errno));
 
-  journal = g_strdup_printf ("%s-journal", self->database_path);
+  journal = g_strdup_printf ("%s-wal", self->database_path);
   if (g_file_test (journal, G_FILE_TEST_EXISTS) && g_unlink (journal) == -1)
     g_warning ("Failed to delete database journal at %s: %s", journal, g_strerror (errno));
 
-  g_free (journal);
+  shm = g_strdup_printf ("%s-shm", self->database_path);
+  if (g_file_test (shm, G_FILE_TEST_EXISTS) && g_unlink (shm) == -1)
+    g_warning ("Failed to delete database shm at %s: %s", shm, g_strerror (errno));
 }
 
 void
diff --git a/lib/safe-browsing/ephy-gsb-storage.c b/lib/safe-browsing/ephy-gsb-storage.c
index 662cc0678..501dfaaf2 100644
--- a/lib/safe-browsing/ephy-gsb-storage.c
+++ b/lib/safe-browsing/ephy-gsb-storage.c
@@ -28,11 +28,11 @@
 
 #define EXPIRATION_THRESHOLD (8 * 60 * 60)
 
-/* Keep this lower than 200 or else you'll get "too many SQL variables" error
- * in ephy_gsb_storage_insert_batch(). SQLITE_MAX_VARIABLE_NUMBER is hardcoded
- * in sqlite3 as 999.
+/* Keep this lower than 6533 (SQLITE_MAX_VARIABLE_NUMBER / 5 slots) or else
+ * you'll get "too many SQL variables" error in ephy_gsb_storage_insert_batch().
+ * SQLITE_MAX_VARIABLE_NUMBER is hardcoded in sqlite3 (>= 3.22) as 32766.
  */
-#define BATCH_SIZE 199
+#define BATCH_SIZE 6553
 
 /* Increment schema version if you:
  * 1) Modify the database table structure.
diff --git a/meson.build b/meson.build
index aef352abd..05563d178 100644
--- a/meson.build
+++ b/meson.build
@@ -97,7 +97,7 @@ libsecret_dep = dependency('libsecret-1', version: '>= 0.19.0')
 libsoup_dep = dependency('libsoup-2.4', version: '>= 2.48.0')
 libxml_dep = dependency('libxml-2.0', version: '>= 2.6.12')
 nettle_dep = dependency('nettle', version: nettle_requirement)
-sqlite3_dep = dependency('sqlite3', version: '>= 3.0')
+sqlite3_dep = dependency('sqlite3', version: '>= 3.22')
 webkit2gtk_dep = dependency('webkit2gtk-4.0', version: webkitgtk_requirement)
 webkit2gtk_web_extension_dep = dependency('webkit2gtk-web-extension-4.0', version: webkitgtk_requirement)