X-Git-Url: http://erislabs.net/gitweb/?a=blobdiff_plain;f=lib%2FID3FS%2FDB.pm;h=955e1173ebf26ee966730cdf262de63c35fe51f7;hb=e1213ba532d655f97bcb2cdae368b710f20e3d6e;hp=c1d6cd62a76f15ad8f28846b1fd18f7017caf083;hpb=8452f6a2f8603a378c41f333087ab98d15e7bd5b;p=id3fs.git

diff --git a/lib/ID3FS/DB.pm b/lib/ID3FS/DB.pm
index c1d6cd6..955e117 100644
--- a/lib/ID3FS/DB.pm
+++ b/lib/ID3FS/DB.pm
@@ -23,11 +23,11 @@ sub new
     $self->{base}=shift;
     my $fallbackdir=shift;
 
-    $dbpath=$self->find_db($init, $dbpath, $fallbackdir);
-    return undef unless($dbpath);
+    $self->{dbpath}=$self->find_db($init, $dbpath, $fallbackdir);
+    return undef unless($self->{dbpath});
     $self->{absbase}=Cwd::abs_path($self->{base});
 
-    my $connectstr="dbi:SQLite:dbname=$dbpath";
+    my $connectstr="dbi:SQLite:dbname=$self->{dbpath}";
     my ($user, $pass)=("", "");
     if($self->{postgres})
     {
@@ -35,7 +35,7 @@ sub new
 	$user="ianb";
 	$pass="foo";
     }
-    my $exists=-f $dbpath;
+    my $exists=-f $self->{dbpath};
     $self->{dbh}=DBI->connect($connectstr, $user, $pass,
 			      { AutoCommit=>1 } );
     unless(defined($self->{dbh}))
@@ -169,12 +169,31 @@ sub last_update
     return $newval;
 }
 
-sub id
+sub bare_tags
 {
-    my($self, $type, $val)=@_;
-    my $sql="SELECT id FROM $type WHERE name=?";
-    my ($id)=$self->cmd_onerow($sql, $val);
-    return($id);
+    my($self)=@_;
+    my $sql=("SELECT tags.name FROM tags\n" .
+	     "WHERE tags.parents_id=''\n" .
+	     "GROUP BY tags.name\n");
+    my @names=$self->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->cmd_rows($sql);
+    my $tags={};
+    for my $pair (@$result)
+    {
+	push(@{$tags->{$pair->[0]}}, $pair->[1]);
+    }
+    return $tags;
 }
 
 sub tag_has_values
@@ -188,7 +207,8 @@ sub tag_has_values
 
 sub relativise
 {
-    my($self, $path, $name, $mountpoint, $id3fs_path)=@_;
+    my($self, $path, $name, $mountpoint)=@_;
+    my $id3fs_path=$self->{dbpath};
     $id3fs_path=~s/(.*)\/.*/$1/;
     my $rpath="$self->{absbase}/$path";
     my $vpath="$mountpoint/$id3fs_path";
@@ -223,7 +243,7 @@ sub add
 {
     my($self,$path)=@_;
     my $relpath=Cwd::abs_path($path);
-    $relpath =~ s/^\Q$self->{base}\E\/?//;
+    $relpath =~ s/^\Q$self->{absbase}\E\/?//;
     my($filepart,$pathpart);
     if($relpath !~ /\//)
     {
@@ -270,6 +290,10 @@ sub add
     {
 	$self->add_tag($file_id, "decade", "${1}0s");
     }
+    else
+    {
+	$self->add_tag($file_id, "decade", "UNKNOWN");
+    }
 
     if($self->ok($v1genre))
     {
@@ -303,7 +327,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 (";
@@ -507,8 +536,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;
 }
 
@@ -520,35 +556,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 (