use v6.d.PREVIEW;
use DBIish;
use Ultramarine::Model::DBMigration;
use JSON::Fast;
class Ultramarine::Model::DB {
has $.db-driver is required;
has %.db-args is required;
my @migrations = (
-> $dbh {
$dbh.do(q:to/END/);
CREATE TABLE songs (
path TEXT PRIMARY KEY,
mtime INTEGER NOT NULL,
metadata TEXT DEFAULT '{}'
);
END
},
);
has $!dbh = do {
my $dbh = DBIish.connect($!db-driver, |%!db-args);
my Ultramarine::Model::DBMigration $migration .= new(:$dbh,:@migrations);
$migration.ensure-schema;
$dbh;
};
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>».Str);
}
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.Str);
return unpack-row($sth.row(:hash));
}
method all-songs() {
my $sth = $!dbh.prepare(q:to/END/);
SELECT *
FROM songs
ORDER BY path ASC
END
$sth.execute();
return gather {
while $sth.row(:hash) -> %song {
take unpack-row(%song);
}
.finish with $sth;
}
}
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.Str,$mtime);
return ($sth.row[0]//0).Bool;
}
}
