better db

1 files changed, 35 insertions, 10 deletions

diff --git a/lib/App/MediaControl/DB.rakumod b/lib/App/MediaControl/DB.rakumod
index b5771bb..7ce562e 100644
--- a/lib/App/MediaControl/DB.rakumod
+++ b/lib/App/MediaControl/DB.rakumod
@@ -22,16 +22,41 @@ class App::MediaControl::DB {
             'table', 'files',
         ).value.defined };
 
-        self!db: { .execute(q:to/END/);
-        CREATE TABLE files (
-            id INTEGER PRIMARY KEY,
-            parent_id INTEGER NULL REFERENCES files(id),
-            is_dir BOOLEAN NOT NULL,
-            name TEXT NOT NULL,
-            matpath TEXT NOT NULL,
-            watched_time INTEGER NULL
-        )
-        END
+        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
         }
     }
 }