sql: Only alter tables if user version changed

This avoids having to do various unneeded SQL queries on every start,
thus increasing startup time. While all those queries are idempotent,
doing them on every startup is still kind of expensive and might involve
disk I/O.

2 files changed, 38 insertions, 19 deletions

diff --git a/qutebrowser/browser/history.py b/qutebrowser/browser/history.py
index d39519bed..ecd96eecc 100644
--- a/qutebrowser/browser/history.py
+++ b/qutebrowser/browser/history.py
@@ -95,17 +95,20 @@ class CompletionMetaInfo(sql.SqlTable):
     def __init__(self, parent=None):
         super().__init__("CompletionMetaInfo", ['key', 'value'],
                          constraints={'key': 'PRIMARY KEY'})
-        for key, default in self.KEYS.items():
-            if key not in self:
-                self[key] = default
-
-        # force_rebuild is not in use anymore
-        self.delete('key', 'force_rebuild', optional=True)
+        if sql.user_version_changed():
+            self._init_default_values()
+            # force_rebuild is not in use anymore
+            self.delete('key', 'force_rebuild', optional=True)
 
     def _check_key(self, key):
         if key not in self.KEYS:
             raise KeyError(key)
 
+    def _init_default_values(self):
+        for key, default in self.KEYS.items():
+            if key not in self:
+                self[key] = default
+
     def __contains__(self, key):
         self._check_key(key)
         query = self.contains_query('key')
diff --git a/qutebrowser/misc/sql.py b/qutebrowser/misc/sql.py
index a0d2cb730..84c2d075f 100644
--- a/qutebrowser/misc/sql.py
+++ b/qutebrowser/misc/sql.py
@@ -69,6 +69,11 @@ db_user_version = None   # The user version we got from the database
 USER_VERSION = UserVersion(0, 3)    # The current / newest user version
 
 
+def user_version_changed():
+    """Whether the version stored in the database is different from the current one."""
+    return db_user_version != USER_VERSION
+
+
 class SqliteErrorCode:
 
     """Error codes as used by sqlite.
@@ -185,16 +190,17 @@ def init(db_path):
             "Database is too new for this qutebrowser version (database version "
             f"{db_user_version}, but {USER_VERSION.major}.x is supported)")
 
-    if db_user_version < USER_VERSION:
+    if user_version_changed():
         log.sql.debug(f"Migrating from version {db_user_version} to {USER_VERSION}")
-        # FIXME ...
+        # Enable write-ahead-logging and reduce disk write frequency
+        # see https://sqlite.org/pragma.html and issues #2930 and #3507
+        #
+        # We might already have done this (without a migration) in earlier versions, but
+        # as those are idempotent, let's make sure we run them once again.
+        Query("PRAGMA journal_mode=WAL").run()
+        Query("PRAGMA synchronous=NORMAL").run()
         Query(f'PRAGMA user_version = {USER_VERSION.to_int()}').run()
 
-    # Enable write-ahead-logging and reduce disk write frequency
-    # see https://sqlite.org/pragma.html and issues #2930 and #3507
-    Query("PRAGMA journal_mode=WAL").run()
-    Query("PRAGMA synchronous=NORMAL").run()
-
 
 def close():
     """Close the SQL connection."""
@@ -321,9 +327,7 @@ class SqlTable(QObject):
     changed = pyqtSignal()
 
     def __init__(self, name, fields, constraints=None, parent=None):
-        """Create a new table in the SQL database.
-
-        Does nothing if the table already exists.
+        """Wrapper over a table in the SQL database.
 
         Args:
             name: Name of the table.
@@ -332,22 +336,34 @@ class SqlTable(QObject):
         """
         super().__init__(parent)
         self._name = name
+        self._create_table(fields, constraints)
+
+    def _create_table(self, fields, constraints):
+        """Create the table if the database is uninitialized.
+
+        If the table already exists, this does nothing, so it can e.g. be called on
+        every user_version change.
+        """
+        if not user_version_changed():
+            return
 
         constraints = constraints or {}
         column_defs = ['{} {}'.format(field, constraints.get(field, ''))
                        for field in fields]
         q = Query("CREATE TABLE IF NOT EXISTS {name} ({column_defs})"
-                  .format(name=name, column_defs=', '.join(column_defs)))
-
+                  .format(name=self._name, column_defs=', '.join(column_defs)))
         q.run()
 
     def create_index(self, name, field):
-        """Create an index over this table.
+        """Create an index over this table if the database is uninitialized.
 
         Args:
             name: Name of the index, should be unique.
             field: Name of the field to index.
         """
+        if not user_version_changed():
+            return
+
         q = Query("CREATE INDEX IF NOT EXISTS {name} ON {table} ({field})"
                   .format(name=name, table=self._name, field=field))
         q.run()