use v6.d;
use DB::SQLite;
class App::MediaControl::DB {
has DB::SQLite $.pool is required;
method !db(Callable $code) {
my $conn = self.pool.db;
LEAVE { .finish with $conn };
$conn.begin;
$conn.execute('PRAGMA foreign_keys=true');
my $result = $code($conn) with $conn;
$conn.commit;
return $result;
}
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),
matpath TEXT NOT NULL,
name TEXT NOT NULL,
is_dir BOOLEAN NOT NULL,
watched_time INTEGER NULL,
UNIQUE (matpath, name)
)
END
}
}
method add-entry(Str :$path! is copy, Str :$name!, Bool :$is-dir!) {
$path ~~ s{<!after '/'>$} = '/';
$path ~~ s{<!before '/'>^} = '/';
note "add-entry($path,$name)";
self!db: {
.query(q:to/END/, :$path, :$name, :is_dir($is-dir));
WITH parent(id,path) AS (
SELECT id, matpath || name || '/' FROM files
),
newrow(path,name,is_dir) AS (
VALUES($path, $name, $is_dir)
)
INSERT INTO files(parent_id,matpath,name,is_dir)
SELECT id, newrow.path, name, is_dir
FROM newrow
LEFT JOIN parent ON parent.path=newrow.path
WHERE true
ON CONFLICT (matpath,name) DO NOTHING
END
}
}
}