use v6.d;
use DB::SQLite;
class App::MediaControl::DB {
has DB::SQLite $.pool is required;
method !db(Callable:D $code) {
sub do-it {
my $conn = self.pool.db;
LEAVE { .finish with $conn };
$conn.execute('PRAGMA foreign_keys=true');
$conn.begin;
KEEP { .commit with $conn };
return $code($conn) with $conn;
}
loop {
CATCH {
when DB::SQLite::Error {
note "DB error: {$_.gist}, retrying";
sleep 0.5;
redo;
}
}
return do-it();
}
}
method ensure-schema() {
return if self!db: { .query(
'SELECT 1 FROM sqlite_schema WHERE type=? AND tbl_name=?',
'table', 'files',
).value.defined };
self!db: {
.query(q:to/END/);
CREATE TABLE files (
id INTEGER PRIMARY KEY,
parent_id INTEGER NULL REFERENCES files(id)
ON DELETE CASCADE,
path TEXT NOT NULL,
name TEXT NOT NULL,
is_dir BOOLEAN NOT NULL,
watched_time INTEGER NULL,
seen BOOLEAN NOT NULL DEFAULT false,
UNIQUE (path, name)
)
END
}
}
method clear-seen() {
self!db: {
.query(q:to/END/)
UPDATE files SET seen=false
END
}
}
method remove-unseen() {
self!db: {
.query(q:to/END/)
DELETE FROM files
WHERE seen=false
END
}
}
method add-entry(Str:D() :$path! is copy, Str:D() :$name!, Bool:D() :$is-dir!) {
$path ~~ s{<!after '/'>$} = '/';
$path ~~ s{<!before '/'>^} = '/';
self!db: {
.query(q:to/END/, :$path, :$name, :is_dir($is-dir));
WITH parent(id,path) AS (
SELECT id, path || name || '/' FROM files
),
newrow(path,name,is_dir) AS (
VALUES($path, $name, $is_dir)
)
INSERT INTO files(parent_id,path,name,is_dir,seen)
SELECT parent.id, newrow.path, newrow.name, newrow.is_dir, true
FROM newrow
LEFT JOIN parent ON parent.path=newrow.path
WHERE true
ON CONFLICT (path,name) DO UPDATE SET seen=true
END
}
}
method remove-entry(Int:D() $id) {
self!db: {
.query(q:to/END/, :$id);
DELETE FROM files
WHERE id=$id
END
}
}
multi method get-children-of(Any:U $) { 'IS NULL' }
multi method get-children-of(Int:D() $id) { '=?', $id }
proto method get-children-of($) {
my ($clause, @binds) = {*};
self!db: {
.query(qq:to/END/,|@binds).hashes;
SELECT id, path, name, is_dir, watched_time
FROM files
WHERE parent_id $clause
ORDER BY name ASC
END
}
}
method get-parents-of(Int:D() $id) {
self!db: {
.query(q:to/END/,:$id).hashes.reverse;
WITH f(id, parent_id, name) AS (
SELECT id, parent_id, name
FROM files
WHERE id=$id
UNION ALL
SELECT files.id, files.parent_id, files.name
FROM files JOIN f ON files.id=f.parent_id
)
SELECT id, name
FROM f
END
}
}
method get-entry(Int:D() $id) {
self!db: {
.query(q:to/END/,:$id).hash;
SELECT id, path, name, is_dir
FROM files
WHERE id=$id
END
}
}
method mark-entry-watched(Int:D() $id) {
self!db: {
.query(q:to/END/,:$id,:time(time));
UPDATE files
SET watched_time=$time
WHERE id=$id
END
}
}
method get-recently-watched-folders(Int:D() $limit=20) {
self!db: {
.query(qq:to/END/, $limit).hashes;
WITH recent(id,watched_time) AS (
SELECT parent_id AS id, MAX(watched_time) AS watched_time
FROM files
WHERE is_dir=false
AND watched_time IS NOT NULL
AND parent_id IS NOT NULL
GROUP BY parent_id
ORDER BY watched_time DESC
LIMIT ?
)
SELECT files.id, files.path, files.name, files.is_dir, recent.watched_time
FROM files
JOIN recent ON files.id=recent.id
END
}
}
}
