From: Ian Beckwith <ianb@erislabs.net>
Date: Thu, 23 Sep 2010 03:35:49 +0000 (+0100)
Subject: implement tags query from http://joinfu.com/presentations/tagging.pdf
X-Git-Tag: debian/1.0-1~191
X-Git-Url: http://erislabs.net/gitweb/?p=id3fs.git;a=commitdiff_plain;h=c22855a1c82add5238b01fd0aa0f111a0befa24b

implement tags query from 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
--- 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";