implement tags query from http://joinfu.com/presentations/tagging.pdf

acceptably fast with sqlite
also create indexes

diff --git a/lib/ID3FS/DB.pm b/lib/ID3FS/DB.pm
index ac84aa0..01b41d8 100644 (file)
--- a/lib/ID3FS/DB.pm
+++ b/lib/ID3FS/DB.pm
@@ -69,6 +69,19 @@ sub create
     {
        $self->cmd("CREATE SEQUENCE seq");
     }
+    else
+    {
+       my %indexes=( "idx_files_id"  => "files(id)",
+                     "idx_fxt_both"  => "files_x_tags(files_id, tags_id)",
+                     "idx_fxt_files" => "files_x_tags (files_id)",
+                     "idx_fxt_tags"  => "files_x_tags (tags_id)",
+                     "idx_tags_id"   => "tags (id)",
+                     "idx_tags_name" => "tags(name)" );
+       for my $index (keys %indexes)
+       {
+           $self->{dbh}->do("CREATE INDEX $index ON " . $indexes{$index});
+       }
+    }
     $self->cmd("INSERT INTO id3fs (schema_version) VALUES (?)", $SCHEMA_VERSION);
 }
 
@@ -109,13 +122,16 @@ sub tags
     }
     my @file_ids=();
     my @tag_ids=();
-    my $main_sql_start=("SELECT tags.name FROM files\n" .
-                       "  INNER JOIN files_x_tags fxt1 ON files.id=fxt1.files_id\n" .
-                       "  INNER JOIN files_x_tags fxt2 ON files.id=fxt2.files_id\n" .
-                       "  INNER JOIN tags ON tags.id=fxt2.tags_id\n" .
-                       "  WHERE fxt1.tags_id IN \n\t(");
-    my $main_sql_mid=")\n\tAND fxt2.tags_id NOT IN \n\t(";
-    my $main_sql_end=")\n  GROUP BY tags.name;";
+
+    my $main_sql_start=("SELECT t2.name\n" .
+                       "\tFROM (SELECT files_id FROM tags t1\n" .
+                       "\t\tINNER JOIN files_x_tags ON t1.id=files_x_tags.tags_id\n" .
+                       "\t\tWHERE t1.id in \n\t\t\t(");
+    my $main_sql_mid=(")\n\t\t) AS fxt1\n" .
+                     "\tINNER JOIN files_x_tags fxt2 ON fxt1.files_id=fxt2.files_id\n" .
+                     "\tINNER JOIN tags t2 ON fxt2.tags_id=t2.id\n" .
+                     "\tWHERE t2.id NOT IN (");
+    my $main_sql_end=")\n\tGROUP BY t2.name;";
     while(my $constraint=shift @constraints)
     {
        print "CONSTRAINT: $constraint->{name}\n";