From f2390f03fdde545e04dc7d0acbf7c0dc0fc14c03 Mon Sep 17 00:00:00 2001 From: Ian Beckwith Date: Sat, 25 Sep 2010 05:54:02 +0100 Subject: [PATCH] rework schema avoiding unnecessary many-many relationships --- lib/ID3FS/DB.pm | 94 +++++++++++++++++-------------------------------------- lib/ID3FS/Path.pm | 2 ++ 2 files changed, 30 insertions(+), 66 deletions(-) diff --git a/lib/ID3FS/DB.pm b/lib/ID3FS/DB.pm index b6570ce..97ed883 100644 --- 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 --- 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) { -- 2.11.0