handle file deletion

1 files changed, 65 insertions, 9 deletions

diff --git a/lib/Ultramarine/Model/DB.pm6 b/lib/Ultramarine/Model/DB.pm6
index 24a6a4a..9673d8e 100644
--- a/lib/Ultramarine/Model/DB.pm6
+++ b/lib/Ultramarine/Model/DB.pm6
@@ -53,6 +53,12 @@ class Ultramarine::Model::DB {
                 FOREIGN KEY (album_id) REFERENCES albums(id)
             )
             END
+
+            $dbh.do(q:to/END/);
+            CREATE TABLE seen_files (
+                path TEXT NOT NULL
+            )
+            END
         },
     );
 
@@ -144,15 +150,65 @@ class Ultramarine::Model::DB {
         $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
+    method seen-file(Str() :$path!) {
+        $!dbh.do(q:to/END/,$path);
+        INSERT INTO seen_files(path) VALUES (?)
+        END
+    }
+    method remove-unseen-files() {
+        $!dbh.do(q:to/END/);
+        DELETE FROM songs
+        WHERE NOT EXISTS (
+            SELECT * FROM seen_files f WHERE f.path = songs.path
+        )
+        END
+        $!dbh.do('DELETE FROM seen_files');
+
+        self.remove-empty();
+    }
+
+    method ensure-file-absent(Str() :$path!) {
+        $!dbh.do(q:to/END/,$path);
+        DELETE FROM songs
+        WHERE path=?
+        END
+
+        self.remove-empty();
+    }
+
+    method remove-empty() {
+        my $affected-rows=1;
+        while $affected-rows > 0 {
+            $affected-rows = $!dbh.do(q:to/END/);
+            DELETE FROM directories
+            WHERE NOT EXISTS (
+                SELECT directory_id
+                FROM songs
+                WHERE songs.directory_id=directories.id
+                UNION ALL
+                SELECT parent_id
+                FROM directories d2
+                WHERE d2.parent_id=directories.id
+            )
+            END
+        }
+        $!dbh.do(q:to/END/);
+        DELETE FROM albums
+        WHERE NOT EXISTS (
+            SELECT album_id
+            FROM songs
+            WHERE songs.album_id=albums.id
+        )
+        END
+        $!dbh.do(q:to/END/);
+        DELETE FROM artists
+        WHERE NOT EXISTS (
+            SELECT artist_id
+            FROM albums
+            WHERE albums.artist_id=artists.id
+        )
+        END
+    }
 
     sub unpack-row(%song is copy) {
         %song<metadata> = from-json(%song<metadata>);