From a961fc0c45fa6af30b79bf9ea366a9535af4f16d Mon Sep 17 00:00:00 2001
From: Ian Beckwith <ianb@erislabs.net>
Date: Sun, 17 Oct 2010 06:15:41 +0100
Subject: [PATCH] more code tidying: sql_start()

---
 lib/ID3FS/Path.pm | 87 +++++++++++++++++++------------------------------------
 1 file changed, 29 insertions(+), 58 deletions(-)

diff --git a/lib/ID3FS/Path.pm b/lib/ID3FS/Path.pm
index 012cba6..56e1e80 100644
--- a/lib/ID3FS/Path.pm
+++ b/lib/ID3FS/Path.pm
@@ -493,7 +493,7 @@ sub tail_parent
 sub tags
 {
     my($self)=@_;
-    if(!$self->{tagtree}) # / or /NOT  # FIXME: /ALL too?
+    if(!$self->{tagtree}) # / or /NOT
     {
 	my $sql="SELECT DISTINCT name FROM tags WHERE parents_id='';";
 	return($self->{db}->cmd_firstcol($sql));
@@ -577,11 +577,8 @@ sub artists
 	return($self->{db}->cmd_firstcol($sql));
     }
     my @ids=();
-    my $sql=("SELECT artists.name FROM (\n" .
-	     $self->tags_subselect() .
-	     ") AS subselect\n" .
-	     "INNER JOIN files ON subselect.files_id=files.id\n" .
-	     "INNER JOIN artists ON files.artists_id=artists.id\n" .
+    my $sql=$self->sql_start("artists.name");
+    $sql .= ("INNER JOIN artists ON files.artists_id=artists.id\n" .
 	     "WHERE artists.name != ''\n" .
 	     "GROUP BY artists.name;");
     print "SQL(ARTISTS): $sql\n" if($self->{verbose});
@@ -600,22 +597,10 @@ sub albums
     {
 	return $self->artist_albums($tail->{id});
     }
-    my $sql;
-    if($self->{in_all})
-    {
-	$sql="SELECT name FROM albums";
-    }
-    else
-    {
-	$sql=("SELECT albums.name\n" .
-	      "\tFROM (\n" .
-	      $self->tags_subselect() .
-	      "\t) AS subselect\n" .
-	      "INNER JOIN files ON subselect.files_id=files.id\n" .
-	      "INNER JOIN albums ON files.albums_id=albums.id\n" .
-	      "WHERE albums.name != ''\n" .
-	      "GROUP BY albums.name;");
-    }
+    my $sql=$self->sql_start("albums.name");
+    $sql .= ("INNER JOIN albums ON files.albums_id=albums.id\n" .
+	     "WHERE albums.name != ''\n" .
+	     "GROUP BY albums.name;");
     print "SQL(ALBUMS): \n$sql\n" if($self->{verbose});
     my @names=$self->{db}->cmd_firstcol($sql);
     print("ALBUMS: ", join(', ', @names), "\n") if($self->{verbose});
@@ -625,18 +610,7 @@ sub albums
 sub artist_albums
 {
     my($self, $artist_id)=@_;
-    my $sql="SELECT albums.name FROM ";
-    if($self->{in_all})
-    {
-	$sql .= "files\n";
-    }
-    else
-    {
-	$sql .= ("(\n" .
-		 $self->tags_subselect() .
-		 ") AS subselect\n" .
-		 "INNER JOIN files ON subselect.files_id=files.id\n");
-    }
+    my $sql=$self->sql_start("albums.name");
     $sql .= ("INNER JOIN albums ON albums.id=files.albums_id\n" .
 	     "INNER JOIN artists ON artists.id=files.artists_id\n" .
 	     "WHERE artists.id=? and albums.name <> ''\n" .
@@ -650,18 +624,7 @@ sub artist_albums
 sub artist_tracks
 {
     my($self, $artist_id)=@_;
-    my $sql="SELECT files.name FROM ";
-    if($self->{in_all})
-    {
-	$sql .= "files\n";
-    }
-    else
-    {
-	$sql .= ("(\n" .
-		 $self->tags_subselect() .
-		 "\t) AS subselect\n" .
-		 "INNER JOIN files ON subselect.files_id=files.id\n");
-    }
+    my $sql=$self->sql_start("files.name");
     $sql .= ("INNER JOIN artists ON artists.id=files.artists_id\n" .
 	     "INNER JOIN albums  ON albums.id=files.albums_id\n" .
 	     "WHERE artists.id=? AND albums.name=''\n" .
@@ -706,18 +669,7 @@ sub tracks
 	}
 	return $self->album_tracks($artist_id, $tail->{id});
     }
-    my $sql="SELECT files.name FROM ";
-    if($self->{in_all})
-    {
-	$sql .= "files\n";
-    }
-    else
-    {
-	$sql .= ("(\n" .
-		 $self->tags_subselect() .
-		 ") AS subselect\n" .
-		 "INNER JOIN files ON files.id=subselect.files_id\n");
-    }
+    my $sql=$self->sql_start("files.name");
     $sql .= "INNER JOIN artists ON files.artists_id=artists.id\n";
     if($self->{components}->[$#{$self->{components}}] eq $PATH_NOARTIST)
     {
@@ -826,6 +778,25 @@ sub bare_not_subselect
     return($sql);
 }
 
+sub sql_start
+{
+    my($self, $tables)=@_;
+    my $sql="SELECT $tables FROM ";
+    if($self->{in_all})
+    {
+	$sql .= "files\n";
+    }
+    else
+    {
+	$sql .= ("(\n" .
+		 $self->tags_subselect() .
+		 ") AS subselect\n" .
+		 "INNER JOIN files ON subselect.files_id=files.id\n");
+    }
+    return $sql;
+}
+
+
 sub constraints_tag_list
 {
     my($self, @constraints)=@_;
-- 
2.11.0