restructure DB schema, go plain DBIish

1 files changed, 52 insertions, 10 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;
     }
 }