X-Git-Url: http://erislabs.net/gitweb/?a=blobdiff_plain;f=lib%2FID3FS%2FDB.pm;h=93f1de7269d143c277f3433df583e21bee854054;hb=d0fe9656010ae6c7976afeb61c0a48a30244200f;hp=f2151e4e55e9db65e4008590efad5d2c01d9692b;hpb=10735afb2cc92efa82c27ad7a5a9b514c1091b4b;p=id3fs.git diff --git a/lib/ID3FS/DB.pm b/lib/ID3FS/DB.pm index f2151e4..93f1de7 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; @@ -16,21 +16,19 @@ sub new my $self={}; bless($self,$class); - my($dir, $init, $me)=@_; - $self->{base}=$dir; - $self->{absbase}=Cwd::abs_path($dir); - $self->{dbpath}="$dir/$dbfile"; - $self->{me}=$me; + $self->{me}=shift; + $self->{dbpath}=shift; + $self->{base}=shift; + $self->{fallbackdir}=shift; - my $exists=-f $self->{dbpath}; - - $self->{postgres}=0; - - unless($self->{postgres}) + if(!defined($self->{base}) && + defined($self->{fallbackdir}) && + -d $self->{fallbackdir}) { - die("$me: $self->{dbpath}: not found. use --init to create.\n") if(!$exists && !$init); - die("$me: --init used but $self->{dbpath} exists.\n") if($exists && $init); + $self->{base}=$self->{fallbackdir}; } + $self->{dbpath}="$self->{base}/$dbfile" unless(defined($self->{dbpath})); + $self->{absbase}=Cwd::abs_path($self->{base}); my $connectstr="dbi:SQLite:dbname=$self->{dbpath}"; my ($user, $pass)=("", ""); @@ -40,20 +38,21 @@ sub new $user="ianb"; $pass="foo"; } + my $exists=-f $self->{dbpath}; $self->{dbh}=DBI->connect($connectstr, $user, $pass, { AutoCommit=>1 } ); unless(defined($self->{dbh})) { - die("$me: DB Error: " . $DBI::errstr . "\n"); + die("$self->{me}: DB Error: " . $DBI::errstr . "\n"); } - if($init) + if($exists) { - $self->create(); + $self->checkschema(); } else { - $self->checkschema(); + $self->create(); } return $self; @@ -85,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 @@ -100,6 +100,20 @@ sub checkschema } } +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)=@_; @@ -119,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); @@ -154,13 +169,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 @@ -178,8 +194,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) { @@ -212,8 +228,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) { @@ -235,10 +251,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); @@ -250,29 +266,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; + @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 albums.id=files_x_albums.albums_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"; @@ -289,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" . @@ -325,8 +348,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"; @@ -360,50 +382,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); @@ -482,6 +484,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)=@_; @@ -549,11 +562,15 @@ sub last_insert_id __DATA__ CREATE TABLE id3fs ( - schema_version INTEGER + schema_version INTEGER, + last_update ); CREATE TABLE files ( id INTEGER PRIMARY KEY, + artists_id, + albums_id, + paths_id, name text ); @@ -582,11 +599,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 @@ -596,18 +608,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 -);