VACUUM when pruning DB

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

diff --git a/lib/ID3FS/DB.pm b/lib/ID3FS/DB.pm
index bbb1be5..9369fbf 100644 (file)
--- a/lib/ID3FS/DB.pm
+++ b/lib/ID3FS/DB.pm
@@ -201,8 +201,8 @@ sub tags
     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)
@@ -282,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";
@@ -392,6 +393,80 @@ 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, $parent)=$self->constraints_tag_list(@constraints);
     my @tags=@$tags;
@@ -401,6 +476,7 @@ sub tags_subselect
             "\tINNER JOIN files_x_tags ON t1.id=files_x_tags.tags_id\n");
     my @orclauses=();
     my @andclauses=();
+    # FIXME: and / or?
     if(@tags)
     {
        push(@andclauses, "( t1.parents_id=" . (defined($parent) ? $parent : "''") . " )");
@@ -433,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}));
            }
        }
@@ -487,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);
@@ -515,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)
@@ -736,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);
 }