use tagtree in queries

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

diff --git a/lib/ID3FS/DB.pm b/lib/ID3FS/DB.pm
index 6424e8c..309df56 100644 (file)
--- a/lib/ID3FS/DB.pm
+++ b/lib/ID3FS/DB.pm
@@ -180,7 +180,8 @@ sub cmd_sth
 
 sub tags
 {
-    my($self, @constraints)=@_;
+    my($self, $path)=@_;
+    my @constraints=@{$path->{elements}};
     if(!@constraints) # /
     {
        # FIXME: add ALL?
@@ -191,38 +192,17 @@ sub tags
     my @ids=();
 
     my $sql=("SELECT t2.name FROM (\n" .
-            $self->tags_subselect(@constraints) .
+            $self->tags_subselect($path) .
             ") AS subselect\n" .
             "INNER JOIN files_x_tags ON subselect.files_id=files_x_tags.files_id\n" .
             "INNER JOIN tags t2 ON files_x_tags.tags_id=t2.id\n");
-    my ($tags, $tags_vals, $parent)=$self->constraints_tag_list(@constraints);
-    my @tags=@$tags;
-    my @tags_vals=@$tags_vals;;
-    my @orclauses=();
-    my @andclauses=();
-    use Data::Dumper;
-#    print "TAGS: ", Dumper \@tags;
-#    print "VALS: ", Dumper \@tags_vals;
-
-    push(@andclauses, "( t2.parents_id=" . (defined($parent) ? $parent : "''") . " )");
-    if(@tags)
-    {
-       push(@orclauses, "( t2.id NOT IN ( " . join(', ', @tags) ." ) )");
-    }
-    for my $pair (@tags_vals)
-    {
-       my($tag, $val)=@$pair;
-#      push(@orclauses, "( NOT ( t2.parents_id=$tag AND t2.id=$val ) )");
-       push(@andclauses, "( NOT ( t2.id=$tag ) )");
-    }
-    if(@orclauses)
+    my @used=$path->used_tags();
+    print "tags(): USED: ", join(", ", @used), "\n";
+    if(@used)
     {
-       push(@andclauses, join("\n\tOR ", @orclauses));
-    }
-    if(@andclauses)
-    {
-       $sql .= "\tWHERE\n\t\t";
-       $sql .= join("\n\tAND ", @andclauses) . "\n";
+       $sql .= "WHERE t2.id NOT IN (";
+       $sql .= join(', ', @used);
+       $sql .= ")\n";
     }
     $sql .= "GROUP BY t2.name;";
     print "SQL: $sql\n";
@@ -245,7 +225,8 @@ sub tag_values
 
 sub artists
 {
-    my($self, @constraints)=@_;
+    my($self, $path)=@_;
+    my @constraints=@{$path->{elements}};
     if(!@constraints) # /ALL
     {
        my $sql="SELECT DISTINCT name FROM artists;";
@@ -254,7 +235,7 @@ sub artists
     }
     my @ids=();
     my $sql=("SELECT artists.name FROM (\n" .
-            $self->tags_subselect(@constraints) .
+            $self->tags_subselect($path) .
             ") AS subselect\n" .
             "INNER JOIN files ON subselect.files_id=files.id\n" .
             "INNER JOIN artists ON files.artists_id=artists.id\n" .
@@ -268,16 +249,17 @@ sub artists
 
 sub albums
 {
-    my($self, @constraints)=@_;
+    my($self, $path)=@_;
+    my @constraints=@{$path->{elements}};
     my @ids=();
     # FIXME: rework PathElements
     if(ref($constraints[$#constraints]) eq "ID3FS::PathElement::Artist")
     {
-       return $self->artist_albums($constraints[$#constraints]->{id}, @constraints);
+       return $self->artist_albums($constraints[$#constraints]->{id}, $path);
     }
     my $sql=("SELECT albums.name\n" .
             "\tFROM (\n" .
-            $self->tags_subselect(@constraints) .
+            $self->tags_subselect($path) .
             "\t) AS subselect\n" .
             "INNER JOIN files ON subselect.files_id=files.id\n" .
             "INNER JOIN albums ON files.albums_id=albums.id\n" .
@@ -291,9 +273,10 @@ sub albums
 
 sub artist_albums
 {
-    my($self, $artist_id, @constraints)=@_;
+    my($self, $artist_id, $path)=@_;
+    my @constraints=@{$path->{elements}};
     my $sql=("SELECT albums.name FROM (\n" .
-            $self->tags_subselect(@constraints) .
+            $self->tags_subselect($path) .
             "\t) AS subselect\n" .
             "INNER JOIN files ON subselect.files_id=files.id\n" .
             "INNER JOIN albums ON albums.id=files.albums_id\n\t" .
@@ -309,9 +292,10 @@ sub artist_albums
 
 sub artist_tracks
 {
-    my($self, $artist_id, @constraints)=@_;
+    my($self, $artist_id, $path)=@_;
+    my @constraints=@{$path->{elements}};
     my $sql=("SELECT files.name FROM (\n" .
-            $self->tags_subselect(@constraints) .
+            $self->tags_subselect($path) .
             "\t) AS subselect\n" .
             "INNER JOIN files ON subselect.files_id=files.id\n" .
             "INNER JOIN artists ON artists.id=files.artists_id\n\t" .
@@ -342,7 +326,8 @@ sub album_tracks
 
 sub tracks
 {
-    my($self, @constraints)=@_;
+    my($self, $path)=@_;
+    my @constraints=@{$path->{elements}};
     # FIXME: rework PathElements
     if(ref($constraints[$#constraints]) eq "ID3FS::PathElement::Artist")
     {
@@ -362,7 +347,7 @@ sub tracks
 
     my $sql=("SELECT files.name\n" .
             "\tFROM (\n" .
-            $self->tags_subselect(@constraints) .
+            $self->tags_subselect($path) .
             "\t) AS subselect\n" .
             "INNER JOIN files ON files.id=subselect.files_id\n" .
             "GROUP BY files.name;");
@@ -375,7 +360,8 @@ sub tracks
 
 sub filename
 {
-    my($self, $mountpoint, @constraints)=@_;
+    my($self, $mountpoint, $path)=@_;
+    my @constraints=@{$path->{elements}};
     if(ref($constraints[$#constraints]) eq "ID3FS::PathElement::File")
     {
        my $id=$constraints[$#constraints]->{id};
@@ -393,9 +379,56 @@ sub filename
 
 sub tags_subselect
 {
-    return shift->tags_subselect_and(@_);
+    my($self, $path)=@_;
+    my $tree=$path->{tagtree};
+    my ($sqlclause, $joinsneeded)=$tree->to_sql();
+    print "SQL($joinsneeded): $sqlclause\n";
+    my $sql="\tSELECT fxt1.files_id FROM files_x_tags fxt1\n";
+    for(my $i=2; $i <= $joinsneeded; $i++)
+    {
+       $sql .= ("\tINNER JOIN files_x_tags fxt$i ON " .
+                "fxt1.files_id=fxt${i}.files_id\n");
+    }
+    $sql .= "\tWHERE $sqlclause\n";
+    $sql .= "\tGROUP BY fxt${joinsneeded}.files_id\n";
+    return $sql;
+}
+
+sub tags_subselect_and_not
+{
+    my($self,@constraints)=@_;
+    my ($tags, $tags_vals, $parent)=$self->constraints_tag_list(@constraints);
+    my @tags=@$tags;
+    my @tags_vals=@$tags_vals;;
+    my $cnt=1;
+    my @andclauses=();
+    my $sql='';
+    for my $tag (@tags)
+    {
+       if($cnt == 1)
+       {
+           $sql="\tSELECT fxt" . scalar(@tags) . ".files_id FROM files_x_tags fxt1\n";
+           push(@andclauses, "\t\tfxt${cnt}.tags_id=$tag");
+       }
+       else
+       {
+           $sql .= ("\tLEFT JOIN files_x_tags fxt$cnt ON fxt" .
+                ($cnt-1) . ".files_id=fxt${cnt}.files_id\n");
+           push(@andclauses, "\t\tfxt${cnt}.tags_id IS NULL");
+       }
+       print "AND: @andclauses\n";
+       $cnt++;
+    }
+    if(@andclauses)
+    {
+       $sql .= "\tWHERE\n\t\t";
+       $sql .= join(" AND\n\t\t", @andclauses) . "\n";
+    }
+    $sql .= "\tGROUP BY fxt". scalar(@tags).".files_id\n";
+    return $sql;
 }
 
+
 sub tags_subselect_and
 {
     my($self,@constraints)=@_;
@@ -776,6 +809,7 @@ sub remove_unused
        LEFT JOIN files_x_tags ON files_x_tags.tags_id=tags.id
        WHERE files_x_tags.files_id IS NULL);
 
+    VACUUM
 EOT
     print "SQL: $sql\n";
     my @sql=split(/\n\n/, $sql);