From: Ian Beckwith <ianb@erislabs.net>
Date: Sun, 17 Oct 2010 04:07:51 +0000 (+0100)
Subject: ensure tags with different parents have different entries; tweak schema
X-Git-Tag: debian/1.0-1~82
X-Git-Url: http://erislabs.net/gitweb/?p=id3fs.git;a=commitdiff_plain;h=27823f01c1b7a469a3fb72aaecdd2d6b03e1945d

ensure tags with different parents have different entries; tweak schema
---

diff --git a/lib/ID3FS/DB.pm b/lib/ID3FS/DB.pm
index a3a771f..1638c54 100644
--- a/lib/ID3FS/DB.pm
+++ b/lib/ID3FS/DB.pm
@@ -307,7 +307,12 @@ sub add_tag
 sub add_to_table
 {
     my($self, $table, $name, $extradata)=@_;
-    my $id=$self->lookup_id($table, $name);
+    my $parent=undef;
+    if($extradata && $extradata->{parents_id})
+    {
+	$parent=$extradata->{parents_id};
+    }
+    my $id=$self->lookup_id($table, $name, $parent);
     unless(defined($id))
     {
 	my $sql="INSERT INTO $table (";
@@ -511,8 +516,15 @@ sub last_insert_id
 
 sub lookup_id
 {
-    my($self, $table, $name)=@_;
-    my($id)=$self->cmd_onerow("SELECT id FROM $table where name=?", $name);
+    my($self, $table, $name, $parent)=@_;
+    my $sql="SELECT id FROM $table where name=?";
+    my @args=($name);
+    if($parent)
+    {
+	$sql .= " AND parents_id=?";
+	push(@args, $parent);
+    }
+    my($id)=$self->cmd_onerow($sql, @args);
     return $id;
 }
 
@@ -524,35 +536,40 @@ CREATE TABLE id3fs (
 );
 
 CREATE TABLE paths (
-    id INTEGER PRIMARY KEY,
-    name text
+    id INTEGER,
+    name text,
+    PRIMARY KEY(id DESC)
 );
 
 CREATE TABLE artists (
-    id INTEGER PRIMARY KEY,
-    name text
+    id INTEGER,
+    name text,
+    PRIMARY KEY(id DESC)
 );
 
 CREATE TABLE albums (
-    id INTEGER PRIMARY KEY,
-    name text
+    id INTEGER,
+    name text,
+    PRIMARY KEY(id DESC)
 );
 
 CREATE TABLE files (
-    id INTEGER PRIMARY KEY,
+    id INTEGER,
     name text,
     artists_id,
     albums_id,
     paths_id,
+    PRIMARY KEY(id DESC),
     FOREIGN KEY(artists_id) REFERENCES artists(id) ON DELETE CASCADE ON UPDATE CASCADE,
     FOREIGN KEY(albums_id)  REFERENCES albums(id)  ON DELETE CASCADE ON UPDATE CASCADE,
     FOREIGN KEY(paths_id)   REFERENCES paths(id)   ON DELETE CASCADE ON UPDATE CASCADE
 );
 
 CREATE TABLE tags (
-    id INTEGER PRIMARY KEY,
+    id INTEGER,
     parents_id INTEGER,
-    name text
+    name text,
+    PRIMARY KEY(id DESC)
 );
 
 CREATE TABLE files_x_tags (