basic tag path traversal

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

diff --git a/lib/ID3FS/DB.pm b/lib/ID3FS/DB.pm
index b8a2c8c..fe660d5 100644 (file)
--- a/lib/ID3FS/DB.pm
+++ b/lib/ID3FS/DB.pm
@@ -83,12 +83,60 @@ sub cmd_sth
 
 sub tags
 {
-    my($self, $path)=@_;
-    my $sql="SELECT DISTINCT name FROM tags;";
-    my $tags=$self->cmd_rows($sql);
+    my($self, @constraints)=@_;
+    if(!@constraints) # /
+    {
+       my $sql="SELECT DISTINCT name FROM tags;";
+       my $tags=$self->cmd_rows($sql);
+       return(map { $_->[0]; } @$tags);
+    }
+    my @file_ids=();
+    my @tag_ids=();
+    my $main_sql_start=("SELECT DISTINCT tags.name FROM files\n" .
+                       "INNER JOIN files_x_tags ON files.id=files_x_tags.files_id\n" .
+                       "INNER JOIN tags ON tags.id=files_x_tags.tags_id\n" .
+                       "WHERE files.id in (" .
+                       ("\tSELECT DISTINCT files.id FROM files\n" .
+                        "\tINNER JOIN files_x_tags ON files.id=files_x_tags.files_id\n" .
+                        "\tINNER JOIN tags ON tags.id=files_x_tags.tags_id\n" .
+                        "\tWHERE tags.id in ("));
+    my $main_sql_mid=")\n) AND tags.id NOT IN (";
+    my $main_sql_end=")\n";
+    while(my $constraint=shift @constraints)
+    {
+       print "CONSTRAINT: $constraint->{name}\n";
+       my $cid=$constraint->{id};
+       push(@tag_ids, $cid);
+    }
+    my $sql = ($main_sql_start . join(", ", @tag_ids) .
+              $main_sql_mid   . join(", ", @tag_ids) .
+              $main_sql_end);
+    print "SQL: $sql\n";
+    my $result=$self->cmd_rows($sql);
+    my @tagnames=map { $_->[0]; } @$result;
+    print "SUBNAMES: ", join(', ', @tagnames), "\n";
+    return(@tagnames);
+}
+
+sub tag_values
+{
+    my($self, $tag)=@_;
+    my $sql=("SELECT DISTINCT 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" .
+            "WHERE tags.name=?");
+    my $tags=$self->cmd_rows($sql, $tag);
     return(map { $_->[0]; } @$tags);
 }
 
+sub tag_id
+{
+    my($self, $tag)=@_;
+    my $sql='SELECT id FROM tags WHERE name=?';
+    my ($id)=$self->cmd_onerow($sql, $tag);
+    return($id);
+}
+
 sub add
 {
     my($self,$path)=@_;