pass path to db methods, not just elements

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

diff --git a/lib/ID3FS/DB.pm b/lib/ID3FS/DB.pm
index 96b88f9..50d9043 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?
@@ -201,8 +202,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)
@@ -245,7 +246,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;";
@@ -268,12 +270,13 @@ 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" .
@@ -282,6 +285,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";
@@ -290,7 +294,8 @@ 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) .
             "\t) AS subselect\n" .
@@ -308,7 +313,8 @@ 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) .
             "\t) AS subselect\n" .
@@ -341,7 +347,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")
     {
@@ -374,7 +381,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};
@@ -392,6 +400,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 +483,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 +516,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 +570,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 +598,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)
@@ -539,7 +622,7 @@ sub add
     my $relpath=$path;
     $relpath =~ s/^\Q$self->{base}\E\/?//;
     my($filepart,$pathpart);
-    if($path !~ /\//)
+    if($relpath !~ /\//)
     {
        $pathpart='';
        $filepart=$relpath;
@@ -736,8 +819,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);
 }