rework schema avoiding unnecessary many-many relationships

diff --git a/lib/ID3FS/DB.pm b/lib/ID3FS/DB.pm
index b6570ce..97ed883 100644 (file)
--- a/lib/ID3FS/DB.pm
+++ b/lib/ID3FS/DB.pm
@@ -133,6 +133,7 @@ sub tags
     my($self, @constraints)=@_;
     if(!@constraints) # /
     {
+       # FIXME: add ALL?
        my $sql="SELECT DISTINCT name FROM tags;";
        my $tags=$self->cmd_rows($sql);
        return(map { $_->[0]; } @$tags);
@@ -192,8 +193,8 @@ sub artists
                        "\t\tINNER JOIN files_x_tags ON tags.id=files_x_tags.tags_id\n" .
                        "\t\tWHERE tags.id in\n\t\t\t(");
     my $main_sql_end=(")\n\t\t) AS subselect\n" .
-                     "\tINNER JOIN files_x_artists ON subselect.files_id=files_x_artists.files_id\n" .
-                     "\tINNER JOIN artists ON artists.id=files_x_artists.artists_id\n" .
+                     "\tINNER JOIN files ON subselect.files_id=files.id\n" .
+                     "\tINNER JOIN artists ON files.artists_id=artists.id\n" .
                      "\n\tGROUP BY artists.name;");
     while(my $constraint=shift @constraints)
     {
@@ -226,8 +227,8 @@ sub albums
                        "\t\tINNER JOIN files_x_tags ON tags.id=files_x_tags.tags_id\n" .
                        "\t\tWHERE tags.id in\n\t\t\t(");
     my $main_sql_end=(")\n\t\t) AS subselect\n" .
-                     "\tINNER JOIN files_x_albums ON subselect.files_id=files_x_albums.files_id\n" .
-                     "\tINNER JOIN albums ON albums.id=files_x_albums.albums_id\n" .
+                     "\tINNER JOIN files ON subselect.files_id=files.id\n" .
+                     "\tINNER JOIN albums ON files.albums_id=albums.id\n" .
                      "\n\tGROUP BY albums.name;");
     while(my $constraint=shift @constraints)
     {
@@ -249,10 +250,10 @@ sub albums
 sub artist_albums
 {
     my($self, $artist_id)=@_;
-    my $sql=("SELECT albums.name FROM artists\n\t" .
-            "INNER JOIN artists_x_albums ON artists.id=artists_x_albums.artists_id\n\t" .
-            "INNER JOIN albums ON albums.id=artists_x_albums.albums_id\n\t" .
-            "WHERE artists.id=?\n\t" .
+    my $sql=("SELECT albums.name FROM files\n\t" .
+            "INNER JOIN albums ON albums.id=files.albums_id\n\t" .
+            "INNER JOIN artists ON artists.id=files.artists_id\n\t" .
+            "WHERE artists.id=? and albums.name <> ''\n\t" .
             "GROUP BY albums.name\n");
     print "ARTIST_ALBUMS SQL: $sql\n";
     my $result=$self->cmd_rows($sql, $artist_id);
@@ -264,15 +265,15 @@ sub artist_albums
 sub artist_tracks
 {
     my($self, $artist_id)=@_;
-    my $sql=("SELECT files.name FROM artists\n\t" .
-            "INNER JOIN artists_x_files ON artists.id=files_x_artists.artists_id\n\t" .
-            "INNER JOIN files ON files.id=files_x_artists.files_id\n\t" .
-            "WHERE artists.id=?\n\t" .
+    my $sql=("SELECT files.name FROM files\n\t" .
+            "INNER JOIN artists ON artists.id=files.artists_id\n\t" .
+            "INNER JOIN albums  ON albums.id=files.albums_id\n\t" .
+            "WHERE artists.id=? AND albums.name=''\n\t" .
             "GROUP BY files.name\n");
     print "ARTIST_TRACKS SQL: $sql\n";
     my $result=$self->cmd_rows($sql, $artist_id);
     my @names=map { $_->[0]; } @$result;
-    @names = map { s/.*\///; } @names;
+    @names = map { s/.*\///; $_; } @names;
     print "ARTISTTRACKS: ", join(', ', @names), "\n";
     return(@names);
 }
@@ -281,9 +282,8 @@ sub album_tracks
 {
     my($self, $album_id)=@_;
     my $sql=("SELECT files.name FROM files\n\t" .
-            "INNER JOIN files_x_albums ON files.id=files_x_albums.files_id\n\t" .
-            "INNER JOIN albums ON albums.id=files_x_albums.albums_id\n\t" .
-            "WHERE albums.id=?\n\t" .
+            "INNER JOIN albums ON albums.id=files.albums_id\n\t" .
+            "WHERE albums.id=?\n\t" . #AND albums.name <> ''\n\t" .
             "GROUP BY files.name\n");
     print "ALBUM_TRACKS SQL($album_id): $sql\n";
     my $result=$self->cmd_rows($sql, $album_id);
@@ -339,8 +339,7 @@ sub filename
     {
        my $id=$constraints[$#constraints]->{id};
        my $sql=("SELECT paths.name, files.name FROM files\n" .
-                "INNER JOIN paths_x_files ON files.id=paths_x_files.files_id\n" .
-                "INNER JOIN paths ON paths_x_files.paths_id=paths.id\n" .
+                "INNER JOIN paths ON files.paths_id=paths.id\n" .
                 "WHERE files.id=?\n" .
                 "GROUP BY paths.name, files.name");
        print "FILENAME SQL: $sql\n";
@@ -384,40 +383,20 @@ sub add
     my $tags=$file->tags();
     my $haspic=$file->haspic();
 
-    my $file_id=$self->add_to_table("files", $filepart);
+    $artist=undef unless($self->ok($artist));
+    my $artist_id=$self->add_to_table("artists",  $artist);
     my $path_id=$self->add_to_table("paths", $pathpart);
-    $self->add_relation("paths_x_files",
-                       { "paths_id" => $path_id,
-                         "files_id" => $file_id});
-
+    $album=undef unless($self->ok($album));
+    my $albums_id=$self->add_to_table("albums", $album);
+    my $file_id=$self->add_to_table("files", $filepart,
+                                   { "artists_id" => $artist_id,
+                                     "albums_id"  => $albums_id,
+                                     "paths_id"   => $path_id });
     for my $tag (keys %$tags)
     {
        $self->add_tag($file_id, $tag, $tags->{$tag});
     }
 
-    my $artist_id;
-    if($self->ok($artist))
-    {
-       $artist_id=$self->add_to_table("artists",  $artist);
-       $self->add_relation("files_x_artists",
-                           { "files_id" => $file_id,
-                             "artists_id" => $artist_id });
-    }
-
-    if($self->ok($album))
-    {
-       my $albums_id=$self->add_to_table("albums", $album);
-       $self->add_relation("files_x_albums",
-                           { "files_id" => $file_id,
-                             "albums_id" => $albums_id});
-       if($self->ok($artist))
-       {
-           $self->add_relation("artists_x_albums",
-                               { "artists_id" => $artist_id,
-                                 "albums_id" => $albums_id});
-       }
-    }
-
     if($self->ok($year))
     {
        $self->add_tag($file_id, "year", $year);
@@ -569,6 +548,9 @@ CREATE TABLE id3fs (
 
 CREATE TABLE files (
     id INTEGER PRIMARY KEY,
+    artists_id,
+    albums_id,
+    paths_id,
     name text
 );
 
@@ -597,11 +579,6 @@ CREATE TABLE tagvals (
     name text
 );
 
-CREATE TABLE paths_x_files (
-    paths_id INTEGER,
-    files_id INTEGER
-);
-
 CREATE TABLE files_x_tags (
     files_id INTEGER,
     tags_id INTEGER
@@ -611,18 +588,3 @@ CREATE TABLE tags_x_tagvals (
     tags_id INTEGER,
     tagvals_id INTEGER
 );
-
-CREATE TABLE files_x_artists (
-    files_id INTEGER,
-    artists_id INTEGER
-);
-
-CREATE TABLE files_x_albums (
-    files_id INTEGER,
-    albums_id INTEGER
-);
-
-CREATE TABLE artists_x_albums (
-    artists_id INTEGER,
-    albums_id INTEGER
-);

diff --git a/lib/ID3FS/Path.pm b/lib/ID3FS/Path.pm
index d2226ff..3440780 100644 (file)
--- a/lib/ID3FS/Path.pm
+++ b/lib/ID3FS/Path.pm
@@ -66,6 +66,7 @@ sub dirents
 {
     my($self)=@_;
     my @dents=();
+    print "DIRENTS: STATE: ",  $self->state(), "\n";
     given($self->state())
     {
        when($STATE_TAG)
@@ -80,6 +81,7 @@ sub dirents
        when($STATE_ROOT)
        {
            @dents=("ALL", $self->{db}->tags(@{$self->{elements}}));
+           print "\nDIRENTS: DENTS: ", join(", ", @dents),"\n\n";
        }
        when($STATE_ALBUMS)
        {