abstract out tags_subselect

diff --git a/lib/ID3FS/DB.pm b/lib/ID3FS/DB.pm
index 9930609..0ed658c 100644 (file)
--- a/lib/ID3FS/DB.pm
+++ b/lib/ID3FS/DB.pm
@@ -191,14 +191,15 @@ sub tags
     my @ids=();
 
     my $main_sql_start=("SELECT t2.name\n" .
-                       "\tFROM (SELECT files_id FROM tags t1\n" .
-                       "\t\tINNER JOIN files_x_tags ON t1.id=files_x_tags.tags_id\n" .
-                       "\t\tWHERE t1.id in\n\t\t\t(");
-    my $main_sql_mid=(")\n\t\t) AS subselect\n" .
-                     "\tINNER JOIN files_x_tags ON subselect.files_id=files_x_tags.files_id\n" .
-                     "\tINNER JOIN tags t2 ON files_x_tags.tags_id=t2.id\n" .
-                     "\tWHERE t2.id NOT IN (");
-    my $main_sql_end=")\n\tGROUP BY t2.name;";
+                       "\tFROM (\n" .
+                       $self->tags_subselect(@constraints) .
+                       ") 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" .
+                     "WHERE t2.id NOT IN (");
+    my $main_sql_end=")\nGROUP BY t2.name;";
+    # FIXME: generalise more?
+
     while(my $constraint=shift @constraints)
     {
        my $cid=$constraint->{id};
@@ -207,7 +208,6 @@ sub tags
     @ids = map( { "\"$_\""; } grep { defined; } @ids) unless($self->{postgres});
     my $tagstr=join(", ", @ids);
     my $sql = ($main_sql_start . $tagstr .
-              $main_sql_mid   . $tagstr .
               $main_sql_end);
     print "SQL: $sql\n";
     my $result=$self->cmd_rows($sql);
@@ -238,23 +238,12 @@ sub artists
        return(map { $_->[0]; } @$tags);
     }
     my @ids=();
-    my $main_sql_start=("SELECT artists.name\n" .
-                       "\tFROM (SELECT files_id FROM tags\n" .
-                       "\t\tINNER JOIN files_x_tags ON tags.id=files_x_tags.tags_id\n" .
-                       "\t\tWHERE tags.id in\n\t\t\t(");
-    my $main_sql_end=(")\n\t\t) AS subselect\n" .
-                     "\tINNER JOIN files ON subselect.files_id=files.id\n" .
-                     "\tINNER JOIN artists ON files.artists_id=artists.id\n" .
-                     "\n\tGROUP BY artists.name;");
-    while(my $constraint=shift @constraints)
-    {
-       my $cid=$constraint->{id};
-       push(@ids, $cid);
-    }
-    @ids = map( { "\"$_\""; } grep { defined; } @ids) unless($self->{postgres});
-    my $tagstr=join(", ", @ids);
-    my $sql = ($main_sql_start . $tagstr .
-              $main_sql_end);
+    my $sql=("SELECT artists.name FROM (\n" .
+            $self->tags_subselect(@constraints) .
+            ") AS subselect\n" .
+            "INNER JOIN files ON subselect.files_id=files.id\n" .
+            "INNER JOIN artists ON files.artists_id=artists.id\n" .
+            "GROUP BY artists.name;");
     print "SQL: $sql\n";
     my $result=$self->cmd_rows($sql);
     my @tagnames=map { $_->[0]; } @$result;
@@ -271,23 +260,13 @@ sub albums
     {
        return $self->artist_albums($constraints[$#constraints]->{id});
     }
-    my $main_sql_start=("SELECT albums.name\n" .
-                       "\tFROM (SELECT files_id FROM tags\n" .
-                       "\t\tINNER JOIN files_x_tags ON tags.id=files_x_tags.tags_id\n" .
-                       "\t\tWHERE tags.id in\n\t\t\t(");
-    my $main_sql_end=(")\n\t\t) AS subselect\n" .
-                     "\tINNER JOIN files ON subselect.files_id=files.id\n" .
-                     "\tINNER JOIN albums ON files.albums_id=albums.id\n" .
-                     "\n\tGROUP BY albums.name;");
-    while(my $constraint=shift @constraints)
-    {
-       my $cid=$constraint->{id};
-       push(@ids, $cid);
-    }
-    @ids = map( { "\"$_\""; } grep { defined; } @ids) unless($self->{postgres});
-    my $str=join(", ", @ids);
-    my $sql = ($main_sql_start . $str .
-              $main_sql_end);
+    my $sql=("SELECT albums.name\n" .
+            "\tFROM (\n" .
+            $self->tags_subselect(@constraints) .
+            "\t) AS subselect\n" .
+            "INNER JOIN files ON subselect.files_id=files.id\n" .
+            "INNER JOIN albums ON files.albums_id=albums.id\n" .
+            "GROUP BY albums.name;");
     my $result=$self->cmd_rows($sql);
     my @names=map { $_->[0]; } @$result;
     print "ALBUMS: ", join(', ', @names), "\n";
@@ -359,23 +338,12 @@ sub tracks
        return $self->album_tracks($artist_id, $constraints[$#constraints]->{id});
     }
 
-    my $main_sql_start=("SELECT files.name\n" .
-                       "\tFROM (SELECT files_id FROM tags\n" .
-                       "\t\tINNER JOIN files_x_tags ON tags.id=files_x_tags.tags_id\n" .
-                       "\t\tWHERE tags.id in\n\t\t\t(");
-    my $main_sql_end=(")\n\t\t) AS subselect\n" .
-                     "\tINNER JOIN files ON files.id=subselect.files_id" .
-                     "\tGROUP BY files.name;");
-    my @ids;
-    while(my $constraint=shift @constraints)
-    {
-       my $cid=$constraint->{id};
-       push(@ids, $cid);
-    }
-    @ids = map( { "\"$_\""; } grep { defined; } @ids) unless($self->{postgres});
-    my $str=join(", ", @ids);
-    my $sql = ($main_sql_start . $str .
-              $main_sql_end);
+    my $sql=("SELECT files.name\n" .
+            "\tFROM (\n" .
+            $self->tags_subselect(@constraints) .
+            "\t) AS subselect\n" .
+            "INNER JOIN files ON files.id=subselect.files_id\n" .
+            "GROUP BY files.name;");
     print "SQL: $sql\n";
     my $result=$self->cmd_rows($sql);
     my @names=map { $_->[0]; } @$result;
@@ -401,6 +369,20 @@ sub filename
     die("DB::filename: unhandled case\n"); #FIXME
 }
 
+sub tags_subselect
+{
+    my($self,@constraints)=@_;
+    my @ids = grep { defined; } map { $_->{id}; } @constraints;
+    @ids=map( { "\"$_\""; } @ids) unless($self->{postgres});
+    my $sql=("\tSELECT files_id FROM tags t1\n" .
+            "\tINNER JOIN files_x_tags ON t1.id=files_x_tags.tags_id\n" .
+            "\tWHERE t1.id IN (\n\t\t" .
+            join(', ', @ids) .
+            "\n\t)\n");
+    return $sql;
+}
+
+
 sub relativise
 {
     my($self, $path, $name, $mountpoint, $id3fs_path)=@_;