From: Ian Beckwith <ianb@erislabs.net>
Date: Sat, 9 Oct 2010 17:58:23 +0000 (+0100)
Subject: use tagtree in queries
X-Git-Tag: debian/1.0-1~119
X-Git-Url: http://erislabs.net/gitweb/?p=id3fs.git;a=commitdiff_plain;h=8c6c420ca9104a94e92f470ef7cca8b4ef242ab5

use tagtree in queries
---

diff --git a/lib/ID3FS/DB.pm b/lib/ID3FS/DB.pm
index 50d9043..309df56 100644
--- a/lib/ID3FS/DB.pm
+++ b/lib/ID3FS/DB.pm
@@ -192,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 @used=$path->used_tags();
+    print "tags(): USED: ", join(", ", @used), "\n";
+    if(@used)
     {
-	my($tag, $val)=@$pair;
-#	push(@orclauses, "( NOT ( t2.parents_id=$tag AND t2.id=$val ) )");
-	push(@andclauses, "( NOT ( t2.id=$tag ) )");
-    }
-    if(@orclauses)
-    {
-	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";
@@ -256,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" .
@@ -280,7 +259,7 @@ sub albums
     }
     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" .
@@ -297,7 +276,7 @@ sub artist_albums
     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" .
@@ -316,7 +295,7 @@ sub artist_tracks
     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" .
@@ -368,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;");
@@ -400,10 +379,21 @@ 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)=@_;
diff --git a/lib/ID3FS/Path.pm b/lib/ID3FS/Path.pm
index 33d3ccd..68218ed 100644
--- a/lib/ID3FS/Path.pm
+++ b/lib/ID3FS/Path.pm
@@ -23,6 +23,7 @@ sub new
     my $self={};
     bless($self,$class);
 
+    $self->{elements}=[];
     $self->{db}=shift;
     $self->{path}=shift;
     $self->parse();
@@ -297,16 +298,21 @@ sub parse
 	    $self->state($STATE_INVALID);
 	}
     }
+    # remove trailing boolean
+    if(@{$self->{elements}} &&
+       ref($self->{elements}->[$#{$self->{elements}}]) eq "ID3FS::PathElement::Boolean")
+    {
+	$self->{lastop}=pop @{$self->{elements}};
+    }
     # sort elements by precedence
     @{$self->{elements}}=$self->sort_elements(@{$self->{elements}});
-    my $thing=$self->elements_to_tree([ @{$self->{elements}} ]);
     $self->{tagtree}=$self->elements_to_tree([ @{$self->{elements}} ]);
     if($self->{tagtree})
     {
 	($self->{sqlconditions},
 	 $self->{andsneeded}) = $self->{tagtree}->to_sql();
-	print("SQL CONDITION(", $self->{andsneeded}, "): ",
-	      $self->{sqlconditions}, "\n");
+#	print("SQL CONDITION(", $self->{andsneeded}, "): ",
+#	      $self->{sqlconditions}, "\n");
 #	use Data::Dumper;
 #	print Dumper $self->{tagtree};
     }
@@ -389,4 +395,12 @@ sub sort_elements
     return @output;
 }
 
+sub used_tags
+{
+    my($self)=@_;
+    print "TAGTREE UNDEF\n" unless(defined($self->{tagtree}));
+    return undef unless(defined($self->{tagtree}));
+    return($self->{tagtree}->used_tags());
+}
+
 1;
diff --git a/lib/ID3FS/Path/Node.pm b/lib/ID3FS/Path/Node.pm
index 771a15a..9fc4570 100644
--- a/lib/ID3FS/Path/Node.pm
+++ b/lib/ID3FS/Path/Node.pm
@@ -89,11 +89,28 @@ sub node_to_sql
     my($self, $node, $andlevel)=@_;
     return ("", $andlevel) unless(defined($node));
     return $node->to_sql($andlevel) if(ref($node) eq "ID3FS::Path::Node");
-    # FIXME: switch to id when debugged
-#    return( ( "fxt" . $andlevel . "=\"" . $node->{id} . "\""), $andlevel);
-    return( ( "fxt" . $andlevel . "=\"" . $node->{name} . "\""), $andlevel);
+    return( ( "fxt${andlevel}.tags_id=\"" . $node->{id} . "\""), $andlevel);
 }
 
+sub used_tags
+{
+    my($self)=@_;
+    my @used=(grep { defined; }  ($self->node_used_tags($self->left()),
+				  $self->node_used_tags($self->right())));
+    print "used_tags: ", join(", ", @used), "\n";
+    return(@used);
+}
+
+sub node_used_tags
+{
+    my($self, $node)=@_;
+    return (undef) unless(defined($node));
+    return $node->used_tags() if(ref($node) eq "ID3FS::Path::Node");
+    print $node->{id}, "\n";
+    return($node->{id});
+}
+
+
 sub max
 {
     my($self, $a, $b)=@_;