more retrieval methods in DB

2 files changed, 70 insertions, 2 deletions

diff --git a/lib/Ultramarine/Model/DB.pm6 b/lib/Ultramarine/Model/DB.pm6
index 8b034ca..47791b4 100644
--- a/lib/Ultramarine/Model/DB.pm6
+++ b/lib/Ultramarine/Model/DB.pm6
@@ -7,4 +7,24 @@ role Ultramarine::Model::DB {
     method is-up-to-date(:$path,:$mtime --> Bool) { ... }
     method ensure-song(:$path,:$mtime,:%metadata) { ... }
     method ensure-file-absent(:$path) { ... }
+
+    method top-folders(--> Iterable) { ... }
+    method dir-children-of(:$id) { ... }
+    method songs-children-of(:$id) { ... }
+}
+
+class Ultramarine::Model::DB::File {
+    has Int $.id is required;
+    has IO::Path $.path is required;
+    has Int $.mtime is required;
+    has $.artist_id;
+    has $.album_id;
+    has %.metadata;
+    submethod BUILD(Int() :$!id!, IO() :$!path!,Int() :$!mtime!, Int :$!album_id!, :%!metadata!) {}
+}
+
+class Ultramarine::Model::DB::Directory {
+    has Int $.id is required;
+    has Str $.name is required;
+    submethod BUILD(Int() :$!id!, Str() :$!name!) {}
 }
diff --git a/lib/Ultramarine/Model/DB/SQLite.pm6 b/lib/Ultramarine/Model/DB/SQLite.pm6
index 8a0e017..030a32f 100644
--- a/lib/Ultramarine/Model/DB/SQLite.pm6
+++ b/lib/Ultramarine/Model/DB/SQLite.pm6
@@ -130,7 +130,7 @@ class Ultramarine::Model::DB::SQLite does Ultramarine::Model::DB {
                 $rel-path .= parent;
             }
         };
-        my $parent-id=Nil;
+        my $parent-id=$ROOT-DIR-ID;
         while my $name = @components.pop {
             $parent-id = self.ensure-one-directory(:$name,:$parent-id);
         }
@@ -214,7 +214,9 @@ class Ultramarine::Model::DB::SQLite does Ultramarine::Model::DB {
 
     sub unpack-row(%song is copy) {
         %song<metadata> = from-json(%song<metadata>);
-        return %song;
+        return Ultramarine::Model::DB::File.new(
+            |(%song<id path mtime metadata album_id>:p.Capture),
+        );
     }
 
     method get-song(Str() :$path!) {
@@ -228,6 +230,52 @@ class Ultramarine::Model::DB::SQLite does Ultramarine::Model::DB {
         return unpack-row($sth.row(:hash));
     }
 
+    method top-folders() {
+        return (Ultramarine::Model::DB::Directory.new(
+            id => $ROOT-DIR-ID,
+            name => 'Music',
+        ),);
+    }
+
+    method dir-children-of(Int() :$id = $ROOT-DIR-ID) {
+        my $sth = $!dbh.prepare(q:to/END/);
+        SELECT *
+        FROM directories
+        WHERE parent_id = ?
+        AND id != ?
+        ORDER BY name ASC
+        END
+        # the C< AND id != ? > is because the root dir is its own
+        # parent, and we never want to return it
+        $sth.execute($id,$id);
+
+        return gather {
+            while $sth.row(:hash) -> %dir {
+                take Ultramarine::Model::DB::Directory.new(
+                    |(%dir<id name>:p.Capture),
+                );
+            }
+            .finish with $sth;
+        };
+    }
+
+    method songs-children-of(Int() :$id = $ROOT-DIR-ID) {
+        my $sth = $!dbh.prepare(q:to/END/);
+        SELECT *
+        FROM songs
+        WHERE directory_id = ?
+        ORDER BY path ASC
+        END
+        $sth.execute($id);
+
+        return gather {
+            while $sth.row(:hash) -> %song {
+                take unpack-row(%song);
+            }
+            .finish with $sth;
+        };
+    }
+
     method all-songs() {
         my $sth = $!dbh.prepare(q:to/END/);
         SELECT *