database

1 files changed, 37 insertions, 0 deletions

diff --git a/lib/App/MediaControl/DB.rakumod b/lib/App/MediaControl/DB.rakumod
new file mode 100644
index 0000000..b5771bb
--- /dev/null
+++ b/lib/App/MediaControl/DB.rakumod
@@ -0,0 +1,37 @@
+use v6.d;
+use DB::SQLite;
+
+class App::MediaControl::DB {
+    has DB::SQLite $.pool is required;
+
+    method !db(Callable $code) {
+        my $conn = self.pool.db;
+        # we need an explicit LEAVE block because on 2021.10, `will
+        # leave { .finish }` kills precomp
+        LEAVE { .finish with $conn };
+        $conn.begin;
+        $conn.execute('PRAGMA foreign_keys=true');
+        my $result = $code($conn) with $conn;
+        $conn.commit;
+        return $result;
+    }
+
+    method ensure-schema() {
+        return if self!db: { .query(
+            'SELECT 1 FROM sqlite_schema WHERE type=? AND tbl_name=?',
+            'table', 'files',
+        ).value.defined };
+
+        self!db: { .execute(q:to/END/);
+        CREATE TABLE files (
+            id INTEGER PRIMARY KEY,
+            parent_id INTEGER NULL REFERENCES files(id),
+            is_dir BOOLEAN NOT NULL,
+            name TEXT NOT NULL,
+            matpath TEXT NOT NULL,
+            watched_time INTEGER NULL
+        )
+        END
+        }
+    }
+}