X-Git-Url: http://erislabs.net/gitweb/?a=blobdiff_plain;f=lib%2FID3FS%2FDB.pm;h=9369fbfc35503d190ca7d1635cb3d42c49ca2934;hb=cb45ec109897ea5a79841a5cc9b5d3dff040f761;hp=2ca7f733f609e24f090741bc7a90ae4942bffa3a;hpb=fd9b7c47be287471cbac40281f25143ac5a7bb6e;p=id3fs.git

diff --git a/lib/ID3FS/DB.pm b/lib/ID3FS/DB.pm
index 2ca7f73..9369fbf 100644
--- 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;
+#    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";
@@ -273,6 +282,7 @@ sub albums
 	     "INNER JOIN files ON subselect.files_id=files.id\n" .
 	     "INNER JOIN albums ON files.albums_id=albums.id\n" .
 	     "GROUP BY albums.name;");
+    print "SQL(ALBUMS): \n$sql\n";
     my $result=$self->cmd_rows($sql);
     my @names=map { $_->[0]; } @$result;
     print "ALBUMS: ", join(', ', @names), "\n";
@@ -383,26 +393,109 @@ sub filename
 
 sub tags_subselect
 {
+    return shift->tags_subselect_and(@_);
+}
+
+
+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)=@_;
+    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";
+	}
+	else
+	{
+	    $sql .= ("\tINNER JOIN files_x_tags fxt$cnt ON fxt" .
+		 ($cnt-1) . ".files_id=fxt${cnt}.files_id\n");
+	}
+	push(@andclauses, "\t\tfxt${cnt}.tags_id = $tag");
+	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_or
+{
     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=();
+    # FIXME: and / or?
     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,23 +509,23 @@ 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))
 	    {
-		print "TAGVAL\n";
+#		print "TAGVAL\n";
 		push(@tags_vals, [$lasttag, $constraint->{id}]) if defined($constraint->{id});
 		$lasttag=undef;
 	    }
 	    elsif($self->tag_has_values($constraint->{id}))
 	    {
-		print "HASVALUES\n";
+#		print "HASVALUES\n";
 		$lasttag=$constraint->{id} if defined($constraint->{id});
 	    }
 	    else
 	    {
-		print "NOVALUES\n";
+#		print "NOVALUES\n";
 		push(@tags, $constraint->{id}) if(defined($constraint->{id}));
 	    }
 	}
@@ -441,8 +534,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);
 }
 
 
@@ -469,9 +563,9 @@ sub relativise
     {
 	shift(@path);
 	shift(@rel);
-	print "POP ";
+#	print "POP ";
     }
-    print "\n";
+#    print "\n";
     my $upcount=scalar(@rel);
     my $result="../" x $upcount;
     $result .= join("/",@path);
@@ -497,7 +591,7 @@ sub tags_with_values
     my $sql=("SELECT p.name, t.name  FROM tags t\n" .
 	     "INNER JOIN tags p ON t.parents_id=p.id\n" .
 	     "GROUP BY p.name, t.name\n");
-#    print "SQL: $sql\n";
+    print "SQL: $sql\n";
     my $result=$self->cmd_rows($sql);
     my $tags={};
     for my $pair (@$result)
@@ -521,7 +615,7 @@ sub add
     my $relpath=$path;
     $relpath =~ s/^\Q$self->{base}\E\/?//;
     my($filepart,$pathpart);
-    if($path !~ /\//)
+    if($relpath !~ /\//)
     {
 	$pathpart='';
 	$filepart=$relpath;
@@ -718,8 +812,9 @@ 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";
+    print "SQL: $sql\n";
     my @sql=split(/\n\n/, $sql);
     $self->cmd($_) for (@sql);
 }