more sql tweaks

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

diff --git a/lib/ID3FS/DB.pm b/lib/ID3FS/DB.pm
index 2ca7f73..96b88f9 100644 (file)
--- a/lib/ID3FS/DB.pm
+++ b/lib/ID3FS/DB.pm
@@ -195,25 +195,34 @@ sub tags
             ") 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)=$self->constraints_tag_list(@constraints);
+    my ($tags, $tags_vals, $parent)=$self->constraints_tag_list(@constraints);
     my @tags=@$tags;
     my @tags_vals=@$tags_vals;;
-    my @clauses=();
+    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(@clauses, "(t2.id NOT IN ( " . join(', ', @tags) ." ) )");
+       push(@orclauses, "( t2.id NOT IN ( " . join(', ', @tags) ." ) )");
     }
     for my $pair (@tags_vals)
     {
        my($tag, $val)=@$pair;
-       push(@clauses, "( NOT (t2.parents_id=$tag AND t2.id=$val ) )");
+#      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(@clauses)
+    if(@andclauses)
     {
-       $sql .= "WHERE\n\t\t" . join("\n\t\tAND ", @clauses) . "\n";
+       $sql .= "\tWHERE\n\t\t";
+       $sql .= join("\n\tAND ", @andclauses) . "\n";
     }
     $sql .= "GROUP BY t2.name;";
     print "SQL: $sql\n";
@@ -384,25 +393,33 @@ sub filename
 sub tags_subselect
 {
     my($self,@constraints)=@_;
-    my ($tags, $tags_vals)=$self->constraints_tag_list(@constraints);
+    my ($tags, $tags_vals, $parent)=$self->constraints_tag_list(@constraints);
     my @tags=@$tags;
     my @tags_vals=@$tags_vals;;
 
     my $sql=("\tSELECT files_x_tags.files_id FROM tags t1\n" .
             "\tINNER JOIN files_x_tags ON t1.id=files_x_tags.tags_id\n");
-    my @clauses=();
+    my @orclauses=();
+    my @andclauses=();
     if(@tags)
     {
-       push(@clauses, "(t1.id IN ( " . join(', ', @tags) ." ) )");
+       push(@andclauses, "( t1.parents_id=" . (defined($parent) ? $parent : "''") . " )");
+       push(@andclauses, "( t1.id IN ( " . join(', ', @tags) ." ) )");
     }
     for my $pair (@tags_vals)
     {
        my($tag, $val)=@$pair;
-       push(@clauses, "( t1.parents_id=$tag AND t1.id=$val )");
+       push(@orclauses, "( t1.parents_id=$tag AND t1.id=$val )");
+    }
+#    push(@andclauses, "( t1.parents_id=" . (defined($parent) ? $parent : "''") . " )");
+    if(@orclauses)
+    {
+       push(@andclauses, join("\n\t\tOR ", @orclauses));
     }
-    if(@clauses)
+    if(@andclauses)
     {
-       $sql .= "\tWHERE\n\t\t" . join("\n\t\tOR ", @clauses) . "\n";
+       $sql .= "\tWHERE\n\t\t";
+       $sql .= join("\n\t\tAND ", @andclauses) . "\n";
     }
     $sql .= "\tGROUP BY files_x_tags.files_id\n";
     return $sql;
@@ -416,7 +433,7 @@ sub constraints_tag_list
     my @tags_vals=();
     for my $constraint (@constraints)
     {
-       print ref($constraint), $constraint->{name}, "\n";
+       print ref($constraint), ": ", $constraint->{name}, "\n";
        if(ref($constraint) eq "ID3FS::PathElement::Tag")
        {
            if(defined($lasttag))
@@ -441,8 +458,9 @@ sub constraints_tag_list
     {
        @tags=map{ "\"$_\""; } @tags;
        @tags_vals=map( { [ map({ "\"$_\""; } @$_ ) ] } @tags_vals);
+       $lasttag="\"$lasttag\"" if defined($lasttag);
     }
-    return(\@tags, \@tags_vals);
+    return(\@tags, \@tags_vals, $lasttag);
 }