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;
constant $ROOT-DIR-ID=0;
my @migrations = (
-> $dbh {
$dbh.do(q:to/END/);
CREATE TABLE directories (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
parent_id INTEGER NOT NULL,
FOREIGN KEY (parent_id) REFERENCES directories(id),
UNIQUE (name,parent_id)
)
END
$dbh.do(q:to/END/,$ROOT-DIR-ID,'/',$ROOT-DIR-ID);
INSERT INTO directories(id,name,parent_id) VALUES (?,?,?)
END
$dbh.do(q:to/END/);
CREATE TABLE artists (
id INTEGER PRIMARY KEY,
name TEXT UNIQUE NOT NULL
)
END
$dbh.do(q:to/END/);
CREATE TABLE albums (
id INTEGER PRIMARY KEY,
title TEXT UNIQUE NOT NULL,
artist_id INTEGER NOT NULL,
FOREIGN KEY (artist_id) REFERENCES artists(id),
UNIQUE (title,artist_id)
)
END
$dbh.do(q:to/END/);
CREATE TABLE songs (
path TEXT NOT NULL PRIMARY KEY,
mtime INTEGER NOT NULL,
directory_id INTEGER,
album_id INTEGER,
metadata TEXT DEFAULT '{}',
FOREIGN KEY (directory_id) REFERENCES directories(id),
FOREIGN KEY (album_id) REFERENCES albums(id)
)
END
},
);
has $!dbh = do {
my $dbh = DBIish.connect($!db-driver, |%!db-args);
$dbh.do(q{PRAGMA foreign_keys=ON});
my Ultramarine::Model::DBMigration $migration .= new(:$dbh,:@migrations);
$migration.ensure-schema;
$dbh;
};
method ensure-artist(:$name!) {
my $sth = $!dbh.prepare(q:to/END/);
INSERT OR IGNORE INTO artists(name) VALUES (?)
END
$sth.execute($name);
$sth.finish;
$sth = $!dbh.prepare(q:to/END/);
SELECT id
FROM artists
WHERE name=?
END
$sth.execute($name);
return $sth.row()[0];
LEAVE { .finish with $sth }
}
method ensure-album(:$title!,:$artist-id!) {
my $sth = $!dbh.prepare(q:to/END/);
INSERT OR IGNORE INTO albums(title,artist_id) VALUES (?,?)
END
$sth.execute($title,$artist-id);
$sth.finish;
$sth = $!dbh.prepare(q:to/END/);
SELECT id
FROM albums
WHERE title=? AND artist_id=?
END
$sth.execute($title,$artist-id);
return $sth.row()[0];
LEAVE { .finish with $sth }
}
method ensure-one-directory(:$name!,:$parent-id!) {
my $sth = $!dbh.prepare(q:to/END/);
INSERT OR IGNORE INTO directories(name,parent_id) VALUES (?,?)
END
$sth.execute($name,$parent-id // $ROOT-DIR-ID);
$sth.finish;
$sth = $!dbh.prepare(q:to/END/);
SELECT id
FROM directories
WHERE name=? AND parent_id=?
END
$sth.execute($name,$parent-id // $ROOT-DIR-ID);
return $sth.row()[0];
LEAVE { .finish with $sth }
}
method ensure-directories(IO() :$path! is copy) {
my @components = gather {
while $path ne '.'|'/' {
take $path.basename;
$path .= parent;
}
};
my $parent-id=Nil;
while my $name = @components.pop {
$parent-id = self.ensure-one-directory(:$name,:$parent-id);
}
return $parent-id;
}
method ensure-song(IO() :$path!,:$mtime!,:%metadata!) {
CATCH { default { .perl.say } }
my $last-dir-id = self.ensure-directories(:path($path.dirname));
my $artist-id = self.ensure-artist(:name($_))
with %metadata<tags><artist>;
my $album-id = self.ensure-album(:title($_),:$artist-id)
with %metadata<tags><album>;
my $sth = $!dbh.prepare(q:to/END/);
INSERT OR REPLACE INTO songs(path,mtime,directory_id,album_id,metadata)
VALUES (?,?,?,?,?)
END
LEAVE { .finish with $sth }
$sth.execute($path.Str,$mtime,$last-dir-id,$album-id,to-json(%metadata));
}
# seen-file
# * add a row into a "seen" table, fk to songs
# remove-unseen-files
# * delete from songs where not in seen
# remove-empty
# * delete from directories where not in directories or songs
# ** repeat until nothing gets deleted
# * delete from albums where not in songs
# * delete from artists where not in albums
sub unpack-row(%song is copy) {
%song<metadata> = from-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;
}
}
