X-Git-Url: http://erislabs.net/gitweb/?a=blobdiff_plain;ds=sidebyside;f=lib%2FID3FS%2FDB.pm;h=15855b24fd2f3e067e547723e1d8968b745fa470;hb=c9ddd26dec9ba36a19d1a55a788158d4ff6d8a40;hp=05b5f4fbe7bad13d73232ad0a19826df1b502d21;hpb=5b2547d90f42403c6b4148ee6f58315e8537f267;p=id3fs.git diff --git a/lib/ID3FS/DB.pm b/lib/ID3FS/DB.pm index 05b5f4f..15855b2 100644 --- a/lib/ID3FS/DB.pm +++ b/lib/ID3FS/DB.pm @@ -17,20 +17,17 @@ sub new bless($self,$class); $self->{me}=shift; - $self->{dbpath}=shift; + $self->{verbose}=shift; + my $init=shift; + my $dbpath=shift; $self->{base}=shift; - $self->{fallbackdir}=shift; + my $fallbackdir=shift; - if(!defined($self->{base}) && - defined($self->{fallbackdir}) && - -d $self->{fallbackdir}) - { - $self->{base}=$self->{fallbackdir}; - } - $self->{dbpath}="$self->{base}/$dbfile" unless(defined($self->{dbpath})); + $dbpath=$self->find_db($init, $dbpath, $fallbackdir); + return undef unless($dbpath); $self->{absbase}=Cwd::abs_path($self->{base}); - my $connectstr="dbi:SQLite:dbname=$self->{dbpath}"; + my $connectstr="dbi:SQLite:dbname=$dbpath"; my ($user, $pass)=("", ""); if($self->{postgres}) { @@ -38,7 +35,7 @@ sub new $user="ianb"; $pass="foo"; } - my $exists=-f $self->{dbpath}; + my $exists=-f $dbpath; $self->{dbh}=DBI->connect($connectstr, $user, $pass, { AutoCommit=>1 } ); unless(defined($self->{dbh})) @@ -54,10 +51,51 @@ sub new { $self->create(); } - + $self->enable_foreign_keys(); return $self; } +sub find_db +{ + my($self, $init, $dbpath, $fallbackdir)=@_; + my $file=undef; + my $base=undef; + if(defined($dbpath)) + { + $file=$dbpath; + } + if(defined ($self->{base})) + { + $file="$self->{base}/$dbfile" unless defined($file); + $base=$self->{base}; + } + elsif(defined($fallbackdir) && -d $fallbackdir) + { + my $path=Cwd::abs_path($fallbackdir); + do + { + $file="$path/$dbfile"; + $base=$path; + $path=~s/(.*)\/.*/$1/; + } + while(! -f $file && length($path) && -d $path); + if(! -f $file) + { + $file="$fallbackdir/$dbfile"; + $base=$fallbackdir; + } + } + if(!-f $file && !$init) + { + print "$self->{me}: db not found at $file\n"; + return undef; + } + $self->{base}=$base; + return $file; +} + +sub base_dir { return shift->{base}; } + sub create { my($self,$name)=@_; @@ -100,6 +138,18 @@ sub checkschema } } +sub analyze +{ + my $self=shift; + $self->cmd("ANALYZE"); +} + +sub enable_foreign_keys +{ + my $self=shift; + $self->cmd("PRAGMA foreign_keys = ON"); +} + sub last_update { my($self, $newval)=@_; @@ -154,7 +204,7 @@ sub tags 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 . @@ -201,7 +251,7 @@ sub artists 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); @@ -234,7 +284,7 @@ sub albums 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); @@ -270,7 +320,6 @@ sub artist_tracks 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); } @@ -286,7 +335,6 @@ sub album_tracks 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); } @@ -324,14 +372,13 @@ sub tracks 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); } @@ -353,6 +400,34 @@ 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)=@_; @@ -376,7 +451,7 @@ sub add { ($pathpart, $filepart) = ($relpath =~ /(.*)\/(.*)/); } - my $file=ID3FS::AudioFile->new($path); + my $file=ID3FS::AudioFile->new($path, $self->{me}); return unless(defined($file)); my $artist=$file->artist(); my $album=$file->album(); @@ -387,9 +462,15 @@ sub add my $haspic=$file->haspic(); $artist=undef unless($self->ok($artist)); + print "$self->{me}: $path: no artist tag defined\n" unless(defined($artist)); my $artist_id=$self->add_to_table("artists", $artist); my $path_id=$self->add_to_table("paths", $pathpart); $album=undef unless($self->ok($album)); + if($self->{verbose} && !defined($album)) + { + print "$self->{me}: $path: no album tag defined\n"; + } + my $albums_id=$self->add_to_table("albums", $album); my $file_id=$self->add_to_table("files", $filepart, { "artists_id" => $artist_id, @@ -489,6 +570,90 @@ sub tag_has_values return $rows; } +sub files_in +{ + my ($self, $dir)=@_; + $dir=~s/^$self->{base}\/?//; + my $sql=("SELECT files.name FROM files\n" . + "INNER JOIN paths ON files.paths_id=paths.id\n" . + "WHERE paths.name=?\n"); + my $files=$self->cmd_rows($sql, $dir); + return(map { $_->[0]; } @$files); +} + +sub prune_directories +{ + my($self)=@_; + my $sql=("SELECT name, id FROM paths ORDER BY name\n"); + my $pathsref=$self->cmd_rows($sql); + my @ids=(); + for my $pathpair (@$pathsref) + { + my($path, $id)=@$pathpair; + my $fullpath="$self->{absbase}/$path"; + unless(-d $fullpath) + { + push(@ids, $id) + } + } + $self->prune_paths(@ids); + return scalar(@ids); +} + +sub prune_paths +{ + my($self, @ids)=@_; + return unless(@ids); + my $sql=("DELETE FROM files WHERE paths_id IN (\n\t" . + join(', ', map { "\"$_\""; } @ids). "\n\t)"); + print "SQL: \n", $sql, "\n"; + $self->cmd($sql); +} + +sub remove_unused +{ + my($self)=@_; + my $sql=<<'EOT'; + DELETE FROM artists WHERE id IN ( + SELECT artists.id FROM artists + LEFT JOIN files ON files.artists_id=artists.id + WHERE files.id IS NULL); + + DELETE FROM albums WHERE id IN ( + SELECT albums.id FROM albums + LEFT JOIN files ON files.albums_id=albums.id + WHERE files.id IS NULL); + + DELETE FROM paths WHERE id IN ( + SELECT paths.id FROM paths + LEFT JOIN files ON files.paths_id=paths.id + WHERE files.id IS NULL); + + DELETE FROM files_x_tags WHERE files_id IN ( + SELECT files_x_tags.files_id FROM files_x_tags + LEFT JOIN files ON files.id=files_x_tags.files_id + WHERE files.id IS NULL); + + DELETE FROM tags WHERE id IN ( + SELECT tags.id FROM tags + LEFT JOIN files_x_tags ON files_x_tags.tags_id=tags.id + WHERE files_x_tags.files_id IS NULL); + + DELETE FROM tags_x_tagvals WHERE tags_id IN ( + SELECT tags_x_tagvals.tags_id FROM tags_x_tagvals + LEFT JOIN tags ON tags.id=tags_x_tagvals.tags_id + WHERE tags.id IS NULL); + + DELETE FROM tagvals WHERE id IN ( + SELECT tagvals.id FROM tagvals + LEFT JOIN tags_x_tagvals ON tags_x_tagvals.tagvals_id=tagvals.id + WHERE tags_x_tagvals.tagvals_id IS NULL); +EOT + print "SQL: $sql\n"; + my @sql=split(/\n\n/, $sql); + $self->cmd($_) for (@sql); +} + sub relation_exists { my ($self, $relname, $fields)=@_; @@ -560,14 +725,6 @@ CREATE TABLE id3fs ( last_update ); -CREATE TABLE files ( - id INTEGER PRIMARY KEY, - artists_id, - albums_id, - paths_id, - name text -); - CREATE TABLE paths ( id INTEGER PRIMARY KEY, name text @@ -583,6 +740,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 @@ -595,10 +763,15 @@ CREATE TABLE tagvals ( 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 + 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 ); +