From 2dc31225e08260a4a8f84fd61623e28f1c8c001e Mon Sep 17 00:00:00 2001
From: xengineering <me@xengineering.eu>
Date: Fri, 3 Apr 2026 21:29:39 +0200
Subject: Introduce class DB

This centralises database related code in one class.
---
 lib/db.dart | 55 +++++++++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 55 insertions(+)
 create mode 100644 lib/db.dart

(limited to 'lib/db.dart')

diff --git a/lib/db.dart b/lib/db.dart
new file mode 100644
index 0000000..8afd3eb
--- /dev/null
+++ b/lib/db.dart
@@ -0,0 +1,55 @@
+import 'dart:io';
+import 'dart:async';
+
+import 'package:path_provider/path_provider.dart';
+import 'package:sqlite3/sqlite3.dart';
+import 'package:path/path.dart' as p;
+
+class DB {
+  final Completer<Database?> _dbCompleter = Completer<Database?>();
+
+  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;
+    }
+
+    _dbCompleter.complete(candidate);
+  }
+
+  void dispose() async {
+    if (_dbCompleter.isCompleted == false) {
+      return;
+    }
+    Database? db = await _dbCompleter.future;
+    if (db == null) return;
+    db.close();
+  }
+
+  static Future<String> _getDbPath() async {
+    Directory supportDir = await getApplicationSupportDirectory();
+    return p.join(supportDir.path, 'main.sqlite3');
+  }
+
+  static int? _getUserVersion(Database db) {
+    ResultSet result = db.select('PRAGMA user_version;');
+    if (result.length != 1) return null;
+    return result.first.values.first as int;
+  }
+
+  Future<String?> getServerFqdn() async {
+    Database? db = await _dbCompleter.future;
+    if (db == null) return null;
+
+    ResultSet result = db.select(
+      'SELECT value FROM key_value WHERE key = \'server_fqdn\';'
+    );
+    if (result.length != 1) return null;
+
+    return result[0]['value'];
+  }
+}
-- 
cgit v1.3


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(-)

(limited to 'lib/db.dart')

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


From fcfb2a17733a38b690f4e034c78bc414be5527ef Mon Sep 17 00:00:00 2001
From: xengineering <me@xengineering.eu>
Date: Mon, 6 Apr 2026 16:57:58 +0200
Subject: Implement setting server_fqdn

This persists the used server_fqdn on connection. This makes sure the
user does not need to specify the FQDN every time.
---
 lib/data.dart |  1 +
 lib/db.dart   | 18 ++++++++++++++++++
 2 files changed, 19 insertions(+)

(limited to 'lib/db.dart')

diff --git a/lib/data.dart b/lib/data.dart
index e4d7524..ca350e5 100644
--- a/lib/data.dart
+++ b/lib/data.dart
@@ -70,6 +70,7 @@ class AppState with ChangeNotifier {
 
   void setFqdn(String value) {
     fqdn = value;
+    unawaited(db.setServerFqdn(value));
   }
 
   void process(MachineEvent event) {
diff --git a/lib/db.dart b/lib/db.dart
index a39db1d..0b2cf7a 100644
--- a/lib/db.dart
+++ b/lib/db.dart
@@ -69,4 +69,22 @@ CREATE TABLE "key_value" (
 
     return result[0]['value'];
   }
+
+  Future<void> setServerFqdn(String value) async {
+    Database? db = await _dbCompleter.future;
+    if (db == null) return;
+
+    String? current = await getServerFqdn();
+    if (current == null) {
+      db.execute(
+        'INSERT INTO key_value (key, value) VALUES (\'server_fqdn\', ?);',
+        <Object?>[value],
+      );
+    } else {
+      db.execute(
+        'UPDATE key_value SET value = ? WHERE key = \'server_fqdn\';',
+        <Object?>[value],
+      );
+    }
+  }
 }
-- 
cgit v1.3