id3fs-index: removed unused entries when reindexing

[id3fs.git] / lib / ID3FS / DB.pm

diff --git a/lib/ID3FS/DB.pm b/lib/ID3FS/DB.pm
index b4c95aa..5f08a04 100644 (file)
--- a/lib/ID3FS/DB.pm
+++ b/lib/ID3FS/DB.pm
@@ -520,6 +520,91 @@ sub tag_has_values
     return $rows;
 }
 
+sub files_in
+{
+    my ($self, $dir)=@_;
+    $dir=~s/^$self->{base}\/?//;
+    print "Munged dir: $dir\n";
+    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";
+       print "PRUNING PATH $fullpath: ";
+       unless(-d $fullpath)
+       {
+           push(@ids, $id)
+       }
+    }
+    $self->prune_paths(@ids);
+    return scalar(@ids);
+}
+
+sub prune_paths
+{
+    my($self, @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)=@_;
@@ -640,3 +725,4 @@ CREATE TABLE tags_x_tagvals (
     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
 );
+