From 72614ea3786a31bcc1f24b0bdc9cd888f8a81f66 Mon Sep 17 00:00:00 2001
From: Ian Beckwith <ianb@erislabs.net>
Date: Sun, 3 Oct 2010 02:46:00 +0100
Subject: [PATCH] finish tags hierarchy schema change

---
 lib/ID3FS/AudioFile.pm       |  7 ++++++-
 lib/ID3FS/DB.pm              | 37 ++++++-------------------------------
 lib/ID3FS/Path.pm            |  2 +-
 lib/ID3FS/PathElement/Tag.pm |  1 +
 4 files changed, 14 insertions(+), 33 deletions(-)

diff --git a/lib/ID3FS/AudioFile.pm b/lib/ID3FS/AudioFile.pm
index 59e0dfa..cac87d3 100644
--- a/lib/ID3FS/AudioFile.pm
+++ b/lib/ID3FS/AudioFile.pm
@@ -95,7 +95,12 @@ sub tags
 	next unless(length($tag));
 	next unless($tag =~ /\S+/);
 	$tag=$self->sanitise($tag);
-	push(@outtags, [ split(/\s*\/\s*/, $tag) ]);
+	my ($tagname, $tagval)=($tag, undef);
+	if($tag=~/^([^\/]+)\/(.*)/)
+	{
+	    ($tagname, $tagval)=($1, $2);
+	}
+	push(@outtags, [ $tagname, $tagval ]);
     }
     return @outtags;
 }
diff --git a/lib/ID3FS/DB.pm b/lib/ID3FS/DB.pm
index 722eb63..5cd7815 100644
--- a/lib/ID3FS/DB.pm
+++ b/lib/ID3FS/DB.pm
@@ -219,9 +219,8 @@ sub tags
 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;
@@ -412,9 +411,8 @@ sub tags_subselect
     }
 #    print "TAGS\n", Dumper \@tags;
 #    print "\nVALS\n", Dumper(\@tags_vals), "\n";
-    my $sql=("\tSELECT files_id FROM tags t1\n" .
-	     "\tINNER JOIN files_x_tags ON t1.id=files_x_tags.tags_id\n" .
-	     "\tINNER JOIN tags_x_tagvals ON t1.id=tags_x_tagvals.tags_id\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)
     {
@@ -423,7 +421,7 @@ sub tags_subselect
     for my $pair (@tags_vals)
     {
 	my($tag, $val)=@$pair;
-	push(@clauses, "( t1.id=$tag AND tags_x_tagvals.tagvals_id=$val )");
+	push(@clauses, "( t1.parents_id=$tag AND t1.id=$val )");
     }
     if(@clauses)
     {
@@ -628,9 +626,7 @@ sub tag_has_values
 {
     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;
 }
@@ -704,15 +700,6 @@ sub remove_unused
        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);
@@ -822,11 +809,6 @@ CREATE TABLE tags (
     name text
 );
 
-CREATE TABLE tagvals (
-    id INTEGER PRIMARY KEY,
-    name text
-);
-
 CREATE TABLE files_x_tags (
     files_id INTEGER,
     tags_id INTEGER,
@@ -834,10 +816,3 @@ CREATE TABLE files_x_tags (
     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
-);
-
diff --git a/lib/ID3FS/Path.pm b/lib/ID3FS/Path.pm
index 807def7..c9553f9 100644
--- a/lib/ID3FS/Path.pm
+++ b/lib/ID3FS/Path.pm
@@ -168,7 +168,7 @@ sub parse
 	       ref($tag) eq "ID3FS::PathElement::Tag" &&
 	       $self->{db}->tag_has_values($tag->{id}))
 	    {
-		my $tagval=ID3FS::PathElement::Tagval->new($self->{db}, $name);
+		my $tagval=ID3FS::PathElement::Tag->new($self->{db}, $name, $tag->{id});
 		if(defined($tagval))
 		{
 		    $self->state($STATE_TAGVAL);
diff --git a/lib/ID3FS/PathElement/Tag.pm b/lib/ID3FS/PathElement/Tag.pm
index 9619582..2574d26 100644
--- a/lib/ID3FS/PathElement/Tag.pm
+++ b/lib/ID3FS/PathElement/Tag.pm
@@ -12,6 +12,7 @@ sub new
 
     $self->{db}=shift;
     $self->{name}=shift;
+    $self->{parent_id}=shift;
     $self->{id}=$self->{db}->id("tags", $self->{name});
     return(undef) unless(defined($self->{id}));
     return($self);
-- 
2.11.0