From c22526c3423f58c399fbd9ee806f08f654d44556 Mon Sep 17 00:00:00 2001
From: dakkar <dakkar@thenautilus.net>
Date: Wed, 29 Dec 2021 11:10:08 +0000
Subject: database

---
 META6.json                      |  1 +
 README.md                       |  7 ++++++-
 config.toml                     |  3 +++
 lib/App/MediaControl/DB.rakumod | 37 +++++++++++++++++++++++++++++++++++++
 media-control.raku              |  9 +++++++++
 5 files changed, 56 insertions(+), 1 deletion(-)
 create mode 100644 lib/App/MediaControl/DB.rakumod

diff --git a/META6.json b/META6.json
index e9be473..e22af51 100644
--- a/META6.json
+++ b/META6.json
@@ -9,6 +9,7 @@
         "Cro::HTTP::Router",
         "Cro::HTTP::Server",
         "Cro::Uri::HTTP",
+        "DB::SQLite",
         "NativeCall",
         "XML"
     ],
diff --git a/README.md b/README.md
index 2919a9f..ff91f6c 100644
--- a/README.md
+++ b/README.md
@@ -1,6 +1,11 @@
 very WIP…
 
-    zef install --deps-only --contained --install-to=./local/ .
+    zef install \
+        --deps-only \
+        --contained \
+        --install-to=./local/ \
+        --exclude=sqlite3 \
+        .
 
 then `raku ./media-control.raku`
 
diff --git a/config.toml b/config.toml
index cc23482..05a0b0f 100644
--- a/config.toml
+++ b/config.toml
@@ -4,3 +4,6 @@ base-uri = "http://127.0.0.1:8080/requests/"
 
 [server]
 port = 8090
+
+[db]
+filename = "media-files.sqlite"
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
+        }
+    }
+}
diff --git a/media-control.raku b/media-control.raku
index 879a11f..8110b18 100644
--- a/media-control.raku
+++ b/media-control.raku
@@ -2,10 +2,12 @@
 use v6.d;
 use lib 'inst#local','file#lib';
 use Config::TOML;
+use DB::SQLite;
 use Vlc::Client;
 use Lirc::Client;
 use Lirc::Commands;
 use App::MediaControl;
+use App::MediaControl::DB;
 
 my $config = from-toml(file=>'config.toml');
 
@@ -17,6 +19,13 @@ my Vlc::Client $vlc .= new(
 my Lirc::Client $lirc-client .= new();
 my Lirc::Commands $lirc .= new(client=>$lirc-client);
 
+my App::MediaControl::DB $db .= new(
+    pool => DB::SQLite.new(
+        filename => $config<db><filename>,
+    ),
+);
+$db.ensure-schema();
+
 my App::MediaControl $app .= new(
     port => $config<server><port>,
     :$vlc, :$lirc,
-- 
cgit v1.2.3