From 34d52bf961071348d8262e6d08d1703530ff8556 Mon Sep 17 00:00:00 2001
From: xengineering <me@xengineering.eu>
Date: Mon, 6 Apr 2026 11:04:53 +0200
Subject: Implement SQL migrations

This allows to update the SQL schema incrementally.
---
 lib/db.dart | 27 ++++++++++++++++++++++-----
 1 file 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;
-- 
cgit v1.3