better db structure, plus notes

2 files changed, 130 insertions, 13 deletions

diff --git a/lib/Ultramarine/Model/Collection.pm6 b/lib/Ultramarine/Model/Collection.pm6
index 449e7ed..e894f1c 100644
--- a/lib/Ultramarine/Model/Collection.pm6
+++ b/lib/Ultramarine/Model/Collection.pm6
@@ -23,13 +23,16 @@ class Ultramarine::Model::Collection {
             when $path ~~ Ultramarine::Model::DirScanner::EndOfScan {
                 # we should use this to check that all the files in
                 # the db have been seen, and delete those that haven't
+                #
+                # calling db.remove-unseen-files, plus db.remove-empty
                 $!mark-ready.keep(True);
             }
             when $path ~~ IO::Path & :f {
                 my $mtime = $path.modified.floor;
+                # call db.ensure-song and db.seen-file
                 unless $.db.is-up-to-date(:$path,:$mtime) {
                     my %metadata = $.file-class.new(:$path).metadata;
-                    $.db.set-song(:$path,:$mtime,:%metadata) if %metadata.keys;
+                    $.db.ensure-song(:$path,:$mtime,:%metadata) if %metadata.keys;
                 }
             }
         }
diff --git a/lib/Ultramarine/Model/DB.pm6 b/lib/Ultramarine/Model/DB.pm6
index 57a5f0b..596da6a 100644
--- a/lib/Ultramarine/Model/DB.pm6
+++ b/lib/Ultramarine/Model/DB.pm6
@@ -7,43 +7,157 @@ 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 PRIMARY KEY,
+                path TEXT NOT NULL PRIMARY KEY,
                 mtime INTEGER NOT NULL,
-                metadata TEXT DEFAULT '{}'
-            );
+                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 set-song(:$path!,:$mtime!,:%metadata!) {
-        my %song = pack-row(%(:$path,:$mtime,:%metadata));
+    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,metadata)
-        VALUES (?,?,?)
+        INSERT OR REPLACE INTO songs(path,mtime,directory_id,album_id,metadata)
+        VALUES (?,?,?,?,?)
         END
         LEAVE { .finish with $sth }
-        $sth.execute(%song<path mtime metadata>».Str);
+        $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;
     }
-    sub pack-row(%song is copy) {
-        %song<metadata> = to-json(%song<metadata>);
-        return %song;
-    }
 
     method get-song(:$path!) {
         my $sth = $!dbh.prepare(q:to/END/);