restructure DB schema, go plain DBIish

2 files changed, 59 insertions, 15 deletions

diff --git a/lib/Ultramarine/Model/DB.pm6 b/lib/Ultramarine/Model/DB.pm6
index cbc353d..7a0d512 100644
--- a/lib/Ultramarine/Model/DB.pm6
+++ b/lib/Ultramarine/Model/DB.pm6
@@ -1,6 +1,7 @@
 use v6.d.PREVIEW;
-use DBI::Async;
+use DBIish;
 use Ultramarine::Model::DBMigration;
+use JSON::Fast;
 
 class Ultramarine::Model::DB {
     has $.db-driver is required;
@@ -8,35 +9,76 @@ class Ultramarine::Model::DB {
 
     my @migrations = (
         -> $dbh {
-            $dbh.query(q:to/END/).finish;
+            $dbh.do(q:to/END/);
             CREATE TABLE songs (
                 path TEXT PRIMARY KEY,
-                title TEXT,
-                artist TEXT
+                mtime INTEGER NOT NULL,
+                metadata TEXT DEFAULT '{}'
             );
             END
         },
     );
 
     has $!dbh = do {
-        my DBI::Async $dbh .= new($!db-driver, |%!db-args);
+        my $dbh = DBIish.connect($!db-driver, |%!db-args);
         my Ultramarine::Model::DBMigration $migration .= new(:$dbh,:@migrations);
         $migration.ensure-schema;
         $dbh;
     };
 
-    method add-song(:$path!,:$title,:$artist) {
-        $!dbh.query(q:to/END/,$path,$title,$artist);
-        INSERT INTO songs(path,artist,title)
+    method set-song(:$path!,:$mtime!,:%metadata!) {
+        my %song = pack-row(%(:$path,:$mtime,:%metadata));
+        my $sth = $!dbh.prepare(q:to/END/);
+        INSERT OR REPLACE INTO songs(path,mtime,metadata)
         VALUES (?,?,?)
         END
+        LEAVE { .finish with $sth }
+        $sth.execute(%song<path mtime metadata>);
     }
 
-    method get-song($path) {
-        $!dbh.query(q:to/END/,$path).hash;
+    sub unpack-row(%song is copy) {
+        %song<metadata> = from-json(%song<metadata>);
+        return %song;
+    }
+    sub pack-row(%song is copy) {
+        %song<metadata> = to-json(%song<metadata>);
+        return %song;
+    }
+
+    method get-song(:$path!) {
+        my $sth = $!dbh.prepare(q:to/END/);
         SELECT *
         FROM songs
         WHERE path=?
         END
+        LEAVE { .finish with $sth }
+        $sth.execute($path);
+        return unpack-row($sth.row(:hash));
+    }
+
+    method all-songs() {
+        my $sth = $!dbh.query(q:to/END/);
+        SELECT *
+        FROM songs
+        ORDER BY path ASC
+        END
+        LEAVE { .finish with $sth };
+        return gather {
+            while $sth.row(:hash) -> %song {
+                take unpack-row(%song);
+            }
+        }
+    }
+
+    method is-up-to-date(:$path!,:$mtime!) {
+        my $sth = $!dbh.prepare(q:to/END/);
+        SELECT COUNT(*)
+        FROM songs
+        WHERE path=?
+          AND mtime >= $mtime
+        END
+        LEAVE { .finish with $sth };
+        $sth.execute($path,$mtime);
+        return ($sth.row[0]//0).Bool;
     }
 }
diff --git a/lib/Ultramarine/Model/DBMigration.pm6 b/lib/Ultramarine/Model/DBMigration.pm6
index e2ac3b8..d51697f 100644
--- a/lib/Ultramarine/Model/DBMigration.pm6
+++ b/lib/Ultramarine/Model/DBMigration.pm6
@@ -6,28 +6,30 @@ class Ultramarine::Model::DBMigration {
     has Callable @.migrations = ();
 
     method get-meta() {
-        return $.dbh.query(q:to/END/).hash;
+        my $sth = $.dbh.prepare(q:to/END/);
         SELECT *
         FROM meta
         ORDER BY version DESC
         LIMIT 1
         END
+        $sth.execute;
+        return $sth.row(:hash);
         CATCH { when X::DBDish::DBError { return Nil } }
+        LEAVE { .finish with $sth }
     }
 
     method !update-meta(*%new-values) {
         my @columns = %new-values.keys;
         my @values = %new-values{|@columns};
-        my $query = qq:to/END/;
+
+        $.dbh.do(qq:to/END/,@values);
         INSERT INTO meta({@columns.join(',')})
         VALUES ({@columns.map({'?'}).join(',')})
         END
-        say $query;
-        $.dbh.query($query,@values).finish;
     }
 
     method !create-meta() {
-        $.dbh.query(q:to/END/).finish;
+        $.dbh.do(q:to/END/);
         CREATE TABLE meta (
             version INTEGER NOT NULL,
             done_at TEXT DEFAULT (datetime('now'))