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));
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});
{
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});
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" .
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" .
}
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)
{
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)=@_;
return(\@tags, \@tags_vals, $lasttag);
}
-
-sub bare_tags
-{
- my($self)=@_;
- my $sql=("SELECT tags.name FROM tags\n" .
- "WHERE tags.parents_id=''\n" .
- "GROUP BY tags.name\n");
- my @names=$self->{db}->cmd_firstcol($sql);
- return (@names);
-}
-
-sub tags_with_values
-{
- # FIXME: only shows one level of tag depth
- my($self)=@_;
- 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";
- my $result=$self->{db}->cmd_rows($sql);
- my $tags={};
- for my $pair (@$result)
- {
- push(@{$tags->{$pair->[0]}}, $pair->[1]);
- }
- return $tags;
-}
-
-
sub filter
{
my($self, @dirs)=@_;