sub tag_values
{
my($self, $tagid)=@_;
- my $sql=("SELECT DISTINCT tagvals.name FROM tagvals\n" .
- "INNER JOIN tags_x_tagvals ON tagvals.id=tags_x_tagvals.tagvals_id\n" .
- "WHERE tags_x_tagvals.tags_id=?");
+ my $sql=("SELECT DISTINCT name FROM tags\n" .
+ "WHERE parents_id=?");
my $tags=$self->cmd_rows($sql, $tagid);
my @tags=map { $_->[0]; } @$tags;
@tags=map { length($_) ? $_ : "NOVALUE"; } @tags;
# FIXME: rework PathElements
if(ref($constraints[$#constraints]) eq "ID3FS::PathElement::Artist")
{
- return $self->artist_albums($constraints[$#constraints]->{id});
+ return $self->artist_albums($constraints[$#constraints]->{id}, @constraints);
}
my $sql=("SELECT albums.name\n" .
"\tFROM (\n" .
sub artist_albums
{
- my($self, $artist_id)=@_;
- my $sql=("SELECT albums.name FROM files\n\t" .
+ my($self, $artist_id, @constraints)=@_;
+ my $sql=("SELECT albums.name FROM (\n" .
+ $self->tags_subselect(@constraints) .
+ "\t) AS subselect\n" .
+ "INNER JOIN files ON subselect.files_id=files.id\n" .
"INNER JOIN albums ON albums.id=files.albums_id\n\t" .
"INNER JOIN artists ON artists.id=files.artists_id\n\t" .
"WHERE artists.id=? and albums.name <> ''\n\t" .
sub artist_tracks
{
- my($self, $artist_id)=@_;
- my $sql=("SELECT files.name FROM files\n\t" .
+ my($self, $artist_id, @constraints)=@_;
+ my $sql=("SELECT files.name FROM (\n" .
+ $self->tags_subselect(@constraints) .
+ "\t) AS subselect\n" .
+ "INNER JOIN files ON subselect.files_id=files.id\n" .
"INNER JOIN artists ON artists.id=files.artists_id\n\t" .
"INNER JOIN albums ON albums.id=files.albums_id\n\t" .
"WHERE artists.id=? AND albums.name=''\n\t" .
# FIXME: rework PathElements
if(ref($constraints[$#constraints]) eq "ID3FS::PathElement::Artist")
{
- return $self->artist_tracks($constraints[$#constraints]->{id});
+ return $self->artist_tracks($constraints[$#constraints]->{id}, @constraints);
}
elsif(ref($constraints[$#constraints]) eq "ID3FS::PathElement::Album")
{
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");
+ use Data::Dumper;
+ my @tags=();
+ my @tags_vals=();
+ my $lasttag='';
+# print "CONSTRAINTS: \n", Dumper \@constraints;
+ for my $constraint (@constraints)
+ {
+# print ref($constraint),"\n";
+ if(ref($constraint) eq "ID3FS::PathElement::Tag")
+ {
+ if($self->tag_has_values($constraint->{id}))
+ {
+# print "HASVALUES\n";
+ $lasttag=$constraint->{id} if defined($constraint->{id});
+ }
+ else
+ {
+# print "NOVALUES\n";
+ push(@tags, $constraint->{id}) if(defined($constraint->{id}));
+ }
+ }
+ elsif(ref($constraint) eq "ID3FS::PathElement::Tagval")
+ {
+# print "TAGVAL\n";
+ push(@tags_vals, [$lasttag, $constraint->{id}]) if defined($constraint->{id});
+ }
+ }
+ unless($self->{postgres})
+ {
+ @tags=map{ "\"$_\""; } @tags;
+ @tags_vals=map( { [ map({ "\"$_\""; } @$_ ) ] } @tags_vals);
+ }
+# print "TAGS\n", Dumper \@tags;
+# print "\nVALS\n", Dumper(\@tags_vals), "\n";
+ my $sql=("\tSELECT files_x_tags.files_id FROM tags t1\n" .
+ "\tINNER JOIN files_x_tags ON t1.id=files_x_tags.tags_id\n");
+ my @clauses=();
+ if(@tags)
+ {
+ push(@clauses, "(t1.id IN ( " . join(', ', @tags) ." ) )");
+ }
+ for my $pair (@tags_vals)
+ {
+ my($tag, $val)=@$pair;
+ push(@clauses, "( t1.parents_id=$tag AND t1.id=$val )");
+ }
+ if(@clauses)
+ {
+ $sql .= "\tWHERE\n\t\t" . join("\n\t\tOR ", @clauses) . "\n";
+ }
return $sql;
}
{
my($self)=@_;
my $sql=("SELECT tags.name FROM tags\n" .
- "LEFT JOIN tags_x_tagvals ON tags.id=tags_x_tagvals.tags_id\n" .
- "WHERE tags_x_tagvals.tags_id IS NULL\n" .
+ "WHERE tags.parents_id=''\n" .
"GROUP BY tags.name\n");
my $result=$self->cmd_rows($sql);
my @names=map { $_->[0]; } @$result;
sub tags_with_values
{
+ # FIXME: only shows one level of tag depth
my($self)=@_;
- my $sql=("SELECT tags.name, tagvals.name FROM tags\n" .
- "INNER JOIN tags_x_tagvals ON tags.id=tags_x_tagvals.tags_id\n" .
- "INNER JOIN tagvals ON tagvals.id=tags_x_tagvals.tagvals_id\n" .
- "GROUP BY tags.name, tagvals.name\n");
+ 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->cmd_rows($sql);
my $tags={};
for my $pair (@$result)
my $v1genre=$file->v1genre();
my $year=$file->year();
my $audiotype=$file->audiotype();
- my $tags=$file->tags();
+ my @tags=$file->tags();
my $haspic=$file->haspic();
$artist=undef unless($self->ok($artist));
{ "artists_id" => $artist_id,
"albums_id" => $albums_id,
"paths_id" => $path_id });
- for my $tag (keys %$tags)
+ for my $tag (@tags)
{
- $self->add_tag($file_id, $tag, $tags->{$tag});
+ $self->add_tag($file_id, @$tag);
}
if($self->ok($year))
sub add_tag
{
- my($self, $file_id, $tag, $val)=@_;
- my $tag_id=$self->add_to_table("tags", $tag);
- $self->add_relation("files_x_tags",
- { "files_id" => $file_id,
- "tags_id" => $tag_id });
- if(defined($val))
+ my($self, $file_id, @tags)=@_;
+ my $parent_id=undef;
+ for my $tag (@tags)
{
- my $val_id=$self->add_to_table("tagvals", $val);
- $self->add_relation("tags_x_tagvals",
- { "tags_id" => $tag_id,
- "tagvals_id" => $val_id });
+ my $tag_id=$self->add_to_table("tags", $tag,
+ { "parents_id" => $parent_id });
+ $self->add_relation("files_x_tags",
+ { "files_id" => $file_id,
+ "tags_id" => $tag_id });
+ $parent_id=$tag_id;
}
}
{
my($self, $id)=@_;
my $sql=("SELECT COUNT(*) FROM tags\n\t" .
- "INNER JOIN tags_x_tagvals ON tags.id=tags_x_tagvals.tags_id\n\t" .
- "INNER JOIN tagvals ON tagvals.id=tags_x_tagvals.tagvals_id\n\t" .
- "WHERE tags.id=?\n");
+ "WHERE tags.parents_id=?\n");
my ($rows)=$self->cmd_onerow($sql, $id);
return $rows;
}
LEFT JOIN files_x_tags ON files_x_tags.tags_id=tags.id
WHERE files_x_tags.files_id IS NULL);
- DELETE FROM tags_x_tagvals WHERE tags_id IN (
- SELECT tags_x_tagvals.tags_id FROM tags_x_tagvals
- LEFT JOIN tags ON tags.id=tags_x_tagvals.tags_id
- WHERE tags.id IS NULL);
-
- DELETE FROM tagvals WHERE id IN (
- SELECT tagvals.id FROM tagvals
- LEFT JOIN tags_x_tagvals ON tags_x_tagvals.tagvals_id=tagvals.id
- WHERE tags_x_tagvals.tagvals_id IS NULL);
EOT
print "SQL: $sql\n";
my @sql=split(/\n\n/, $sql);
CREATE TABLE tags (
id INTEGER PRIMARY KEY,
- name text
-);
-
-CREATE TABLE tagvals (
- id INTEGER PRIMARY KEY,
+ parents_id INTEGER,
name text
);
FOREIGN KEY(tags_id) REFERENCES tags(id) ON DELETE CASCADE ON UPDATE CASCADE
);
-CREATE TABLE tags_x_tagvals (
- tags_id INTEGER,
- tagvals_id INTEGER,
- FOREIGN KEY(tags_id) REFERENCES tags(id) ON DELETE CASCADE ON UPDATE CASCADE,
- FOREIGN KEY(tagvals_id) REFERENCES tagvals(id) ON DELETE CASCADE ON UPDATE CASCADE
-);
-
projects / id3fs.git / blobdiff