X-Git-Url: http://erislabs.net/gitweb/?a=blobdiff_plain;f=lib%2FID3FS%2FDB.pm;h=b4c95aaf0ed71ff914972d6e9d4a7b8ca76aa2c1;hb=44e2917bc06ac6c7a33143f185a2e56d0a5acf4a;hp=11381c6b954cff9470f39585163e712e6cf85066;hpb=7c549737241ec9dd03289a9710bb352b66e60569;p=id3fs.git diff --git a/lib/ID3FS/DB.pm b/lib/ID3FS/DB.pm index 11381c6..b4c95aa 100644 --- a/lib/ID3FS/DB.pm +++ b/lib/ID3FS/DB.pm @@ -3,7 +3,7 @@ package ID3FS::DB; use strict; use warnings; use DBI; -use ID3FS::File; +use ID3FS::AudioFile; use Cwd; our $SCHEMA_VERSION=1; @@ -54,7 +54,7 @@ sub new { $self->create(); } - + $self->enable_foreign_keys(); return $self; } @@ -84,7 +84,8 @@ sub create $self->{dbh}->do("CREATE INDEX $index ON " . $indexes{$index}); } } - $self->cmd("INSERT INTO id3fs (schema_version) VALUES (?)", $SCHEMA_VERSION); + $self->cmd("INSERT INTO id3fs (schema_version, last_update) VALUES (?, ?)", + $SCHEMA_VERSION, time()); } sub checkschema @@ -99,6 +100,26 @@ sub checkschema } } +sub enable_foreign_keys +{ + my $self=shift; + $self->cmd("PRAGMA foreign_keys = ON"); +} + +sub last_update +{ + my($self, $newval)=@_; + if(defined($newval)) + { + $self->cmd("UPDATE id3fs SET last_update=?", $newval); + } + else + { + ($newval)=$self->cmd_onerow("SELECT last_update from id3fs"); + } + return $newval; +} + sub cmd_sth { my($self, $sql, @params)=@_; @@ -118,6 +139,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); @@ -135,11 +157,10 @@ sub tags my $main_sql_end=")\n\tGROUP BY t2.name;"; while(my $constraint=shift @constraints) { - print "CONSTRAINT: $constraint->{name}\n"; my $cid=$constraint->{id}; push(@ids, $cid); } - @ids = map( { "\"$_\""; } @ids) unless($self->{postgres}); + @ids = map( { "\"$_\""; } grep { defined; } @ids) unless($self->{postgres}); my $tagstr=join(", ", @ids); my $sql = ($main_sql_start . $tagstr . $main_sql_mid . $tagstr . @@ -153,13 +174,14 @@ sub tags sub tag_values { - my($self, $tag)=@_; - my $sql=("SELECT DISTINCT tagvals.name FROM tags\n" . - "INNER JOIN tags_x_tagvals ON tags.id=tags_x_tagvals.tags_id\n" . - "INNER JOIN tagvals ON tagvals.id=tags_x_tagvals.tagvals_id\n" . - "WHERE tags.name=?"); - my $tags=$self->cmd_rows($sql, $tag); - return(map { $_->[0]; } @$tags); + my($self, $tagid)=@_; + my $sql=("SELECT DISTINCT tagvals.name FROM tagvals\n" . + "INNER JOIN tags_x_tagvals ON tagvals.id=tags_x_tagvals.tagvals_id\n" . + "WHERE tags_x_tagvals.tags_id=?"); + my $tags=$self->cmd_rows($sql, $tagid); + my @tags=map { $_->[0]; } @$tags; + @tags=map { length($_) ? $_ : "NOVALUE"; } @tags; + return @tags; } sub artists @@ -177,16 +199,15 @@ 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) { - print "CONSTRAINT: $constraint->{name}\n"; my $cid=$constraint->{id}; push(@ids, $cid); } - @ids = map( { "\"$_\""; } @ids) unless($self->{postgres}); + @ids = map( { "\"$_\""; } grep { defined; } @ids) unless($self->{postgres}); my $tagstr=join(", ", @ids); my $sql = ($main_sql_start . $tagstr . $main_sql_end); @@ -211,20 +232,18 @@ 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) { - print "CONSTRAINT: $constraint->{name}\n"; my $cid=$constraint->{id}; push(@ids, $cid); } - @ids = map( { "\"$_\""; } @ids) unless($self->{postgres}); + @ids = map( { "\"$_\""; } grep { defined; } @ids) unless($self->{postgres}); my $str=join(", ", @ids); my $sql = ($main_sql_start . $str . $main_sql_end); - print "SQL: $sql\n"; my $result=$self->cmd_rows($sql); my @names=map { $_->[0]; } @$result; print "ALBUMS: ", join(', ', @names), "\n"; @@ -234,10 +253,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); @@ -249,31 +268,29 @@ 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; print "ARTISTTRACKS: ", join(', ', @names), "\n"; return(@names); } sub album_tracks { - my($self, $album_id)=@_; + my($self, $artist_id, $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" . + "INNER JOIN artists ON artists.id=files.artists_id\n\t" . + "WHERE artists.id=? AND albums.id=?\n\t" . "GROUP BY files.name\n"); - print "ALBUM_TRACKS SQL($album_id): $sql\n"; - my $result=$self->cmd_rows($sql, $album_id); + print "ALBUM_TRACKS SQL($artist_id, $album_id): $sql\n"; + my $result=$self->cmd_rows($sql, $artist_id, $album_id); my @names=map { $_->[0]; } @$result; - @names = map { s/.*\///; $_;} @names; print "TRACKS: ", join(', ', @names), "\n"; return(@names); } @@ -288,7 +305,14 @@ sub tracks } elsif(ref($constraints[$#constraints]) eq "ID3FS::PathElement::Album") { - return $self->album_tracks($constraints[$#constraints]->{id}); + my $artist_id=0; + my $artist=$constraints[($#constraints)-1]; + if(defined($artist) && (ref($artist) eq "ID3FS::PathElement::Artist")) + { + # should always happen + $artist_id=$artist->{id}; + } + return $self->album_tracks($artist_id, $constraints[$#constraints]->{id}); } my $main_sql_start=("SELECT files.name\n" . @@ -301,18 +325,16 @@ sub tracks my @ids; while(my $constraint=shift @constraints) { - print "CONSTRAINT: $constraint->{name}\n"; my $cid=$constraint->{id}; push(@ids, $cid); } - @ids = map( { "\"$_\""; } @ids) unless($self->{postgres}); + @ids = map( { "\"$_\""; } grep { defined; } @ids) unless($self->{postgres}); my $str=join(", ", @ids); my $sql = ($main_sql_start . $str . $main_sql_end); print "SQL: $sql\n"; my $result=$self->cmd_rows($sql); my @names=map { $_->[0]; } @$result; - @names = map { s/.*\///; $_; } @names; print "TRACKS: ", join(', ', @names), "\n"; return(@names); } @@ -324,8 +346,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"; @@ -335,10 +356,37 @@ sub filename die("DB::filename: unhandled case\n"); #FIXME } +sub bare_tags +{ + my($self)=@_; + my $sql=("SELECT tags.name FROM tags\n" . + "LEFT JOIN tags_x_tagvals ON tags.id=tags_x_tagvals.tags_id\n" . + "WHERE tags_x_tagvals.tags_id IS NULL\n" . + "GROUP BY tags.name\n"); + my $result=$self->cmd_rows($sql); + my @names=map { $_->[0]; } @$result; + return (@names); +} + +sub tags_with_values +{ + my($self)=@_; + my $sql=("SELECT tags.name, tagvals.name FROM tags\n" . + "INNER JOIN tags_x_tagvals ON tags.id=tags_x_tagvals.tags_id\n" . + "INNER JOIN tagvals ON tagvals.id=tags_x_tagvals.tagvals_id\n" . + "GROUP BY tags.name, tagvals.name\n"); + my $result=$self->cmd_rows($sql); + my $tags={}; + for my $pair (@$result) + { + push(@{$tags->{$pair->[0]}}, $pair->[1]); + } + return $tags; +} + sub id { my($self, $type, $val)=@_; - print "ID: $type $val\n"; my $sql="SELECT id FROM $type WHERE name=?"; my ($id)=$self->cmd_onerow($sql, $val); return($id); @@ -359,50 +407,30 @@ sub add { ($pathpart, $filepart) = ($relpath =~ /(.*)\/(.*)/); } - my $file=ID3FS::File->new($path); + my $file=ID3FS::AudioFile->new($path); return unless(defined($file)); my $artist=$file->artist(); my $album=$file->album(); my $v1genre=$file->v1genre(); my $year=$file->year(); - my $audiotype=$file->album(); + my $audiotype=$file->audiotype(); 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); @@ -481,6 +509,17 @@ sub lookup_id return $id; } +sub tag_has_values +{ + my($self, $id)=@_; + my $sql=("SELECT COUNT(*) FROM tags\n\t" . + "INNER JOIN tags_x_tagvals ON tags.id=tags_x_tagvals.tags_id\n\t" . + "INNER JOIN tagvals ON tagvals.id=tags_x_tagvals.tagvals_id\n\t" . + "WHERE tags.id=?\n"); + my ($rows)=$self->cmd_onerow($sql, $id); + return $rows; +} + sub relation_exists { my ($self, $relname, $fields)=@_; @@ -548,12 +587,8 @@ sub last_insert_id __DATA__ CREATE TABLE id3fs ( - schema_version INTEGER -); - -CREATE TABLE files ( - id INTEGER PRIMARY KEY, - name text + schema_version INTEGER, + last_update ); CREATE TABLE paths ( @@ -571,6 +606,17 @@ CREATE TABLE albums ( name text ); +CREATE TABLE files ( + id INTEGER PRIMARY KEY, + name text, + artists_id, + albums_id, + paths_id, + FOREIGN KEY(artists_id) REFERENCES artists(id) ON DELETE CASCADE ON UPDATE CASCADE, + FOREIGN KEY(albums_id) REFERENCES albums(id) ON DELETE CASCADE ON UPDATE CASCADE, + FOREIGN KEY(paths_id) REFERENCES paths(id) ON DELETE CASCADE ON UPDATE CASCADE +); + CREATE TABLE tags ( id INTEGER PRIMARY KEY, name text @@ -581,32 +627,16 @@ 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 + tags_id INTEGER, + FOREIGN KEY(files_id) REFERENCES files(id) ON DELETE CASCADE ON UPDATE CASCADE, + FOREIGN KEY(tags_id) REFERENCES tags(id) ON DELETE CASCADE ON UPDATE CASCADE ); 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 + tagvals_id INTEGER, + FOREIGN KEY(tags_id) REFERENCES tags(id) ON DELETE CASCADE ON UPDATE CASCADE, + FOREIGN KEY(tagvals_id) REFERENCES tagvals(id) ON DELETE CASCADE ON UPDATE CASCADE );