Implement SQL migrations

This allows to update the SQL schema incrementally.

1 files changed, 22 insertions, 5 deletions

diff --git a/lib/db.dart b/lib/db.dart
index 8afd3eb..a39db1d 100644
--- a/lib/db.dart
+++ b/lib/db.dart
@@ -7,16 +7,22 @@ import 'package:path/path.dart' as p;
 
 class DB {
   final Completer<Database?> _dbCompleter = Completer<Database?>();
+  static const List<String> migrations = <String>[
+    '''
+PRAGMA user_version = 1;
+CREATE TABLE "key_value" (
+	"key"	TEXT NOT NULL UNIQUE,
+	"value"	TEXT,
+	PRIMARY KEY("key")
+);
+    ''',
+  ];
 
   Future<void> connect() async {
     String path = await _getDbPath();
     Database candidate = sqlite3.open(path);
 
-    int? userVersion = _getUserVersion(candidate);
-    if (userVersion == null || userVersion != 0) {
-      _dbCompleter.complete(null);
-      return;
-    }
+    migrate(candidate);
 
     _dbCompleter.complete(candidate);
   }
@@ -35,6 +41,17 @@ class DB {
     return p.join(supportDir.path, 'main.sqlite3');
   }
 
+  static void migrate(Database db) {
+    for (int i=0; i<migrations.length; i++) {
+      int? userVersion = _getUserVersion(db);
+      if (userVersion == null) return;
+
+      if (i == userVersion) {
+        db.execute(migrations[i]);
+      }
+    }
+  }
+
   static int? _getUserVersion(Database db) {
     ResultSet result = db.select('PRAGMA user_version;');
     if (result.length != 1) return null;