fix id3fs-index -l

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

diff --git a/lib/ID3FS/DB.pm b/lib/ID3FS/DB.pm
index aa9eaf1..fced79e 100644 (file)
--- a/lib/ID3FS/DB.pm
+++ b/lib/ID3FS/DB.pm
@@ -85,6 +85,11 @@ sub find_db
            $base=$fallbackdir;
        }
     }
+    else
+    {
+       print "$self->{me}: $fallbackdir: not a directory\n";
+       return undef;
+    }
     if(!-f $file && !$init)
     {
        print "$self->{me}: db not found at $file\n";
@@ -164,12 +169,31 @@ sub last_update
     return $newval;
 }
 
-sub id
+sub bare_tags
+{
+    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
 {
-    my($self, $type, $val)=@_;
-    my $sql="SELECT id FROM $type WHERE name=?";
-    my ($id)=$self->cmd_onerow($sql, $val);
-    return($id);
+    # 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
@@ -217,8 +241,8 @@ sub relativise
 sub add
 {
     my($self,$path)=@_;
-    my $relpath=$path;
-    $relpath =~ s/^\Q$self->{base}\E\/?//;
+    my $relpath=Cwd::abs_path($path);
+    $relpath =~ s/^\Q$self->{absbase}\E\/?//;
     my($filepart,$pathpart);
     if($relpath !~ /\//)
     {
@@ -259,13 +283,15 @@ sub add
        $self->add_tag($file_id, @$tag);
     }
 
-    if($self->ok($year))
+    $year="UNKNOWN" unless($self->ok($year));
+    $self->add_tag($file_id, "year", $year);
+    if($year=~/^(\d\d\d)\d$/)
     {
-       $self->add_tag($file_id, "year", $year);
-       if($year=~/^(\d\d\d)\d$/)
-       {
-           $self->add_tag($file_id, "decade", "${1}0s");
-       }
+       $self->add_tag($file_id, "decade", "${1}0s");
+    }
+    else
+    {
+       $self->add_tag($file_id, "decade", "UNKNOWN");
     }
 
     if($self->ok($v1genre))
@@ -300,7 +326,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 (";
@@ -335,14 +366,24 @@ sub add_relation
 sub files_in
 {
     my ($self, $dir)=@_;
-    $dir=~s/^$self->{base}\/?//;
     my $sql=("SELECT files.name FROM files\n" .
             "INNER JOIN paths ON files.paths_id=paths.id\n" .
             "WHERE paths.name=?\n");
-    my $files=$self->cmd_rows($sql, $dir);
-    return(map { $_->[0]; } @$files);
+#    print "files_in: SQL: $sql\n";
+    return($self->cmd_firstcol($sql, $dir));
+}
+
+sub unindex
+{
+    my($self, $path, $file)=@_;
+    my $sql=("DELETE FROM files WHERE id IN (" .
+            "\tSELECT files.id FROM files\n" .
+            "\tINNER JOIN paths ON paths.id=files.paths_id\n" .
+            "\tWHERE paths.name=? and files.name=? )\n");
+    $self->cmd_rows($sql, $path, $file);
 }
 
+
 sub prune_directories
 {
     my($self)=@_;
@@ -368,7 +409,7 @@ sub prune_paths
     return unless(@ids);
     my $sql=("DELETE FROM files WHERE paths_id IN (\n\t" .
             join(', ', map { "\"$_\""; } @ids). "\n\t)");
-    print "SQL: \n", $sql, "\n";
+#    print "SQL: \n", $sql, "\n";
     $self->cmd($sql);
 }
 
@@ -403,7 +444,7 @@ sub remove_unused
 
     VACUUM
 EOT
-    print "SQL: $sql\n";
+#    print "SQL: $sql\n";
     my @sql=split(/\n\n/, $sql);
     $self->cmd($_) for (@sql);
 }
@@ -494,8 +535,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;
 }
 
@@ -507,35 +555,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 (