tweak indexes in schema

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

diff --git a/lib/ID3FS/DB.pm b/lib/ID3FS/DB.pm
index 29c50e3..11b5525 100644 (file)
--- a/lib/ID3FS/DB.pm
+++ b/lib/ID3FS/DB.pm
@@ -23,20 +23,13 @@ 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 ($user, $pass)=("", "");
-    if($self->{postgres})
-    {
-       $connectstr="dbi:Pg:dbname=id3fs";
-       $user="ianb";
-       $pass="foo";
-    }
-    my $exists=-f $dbpath;
-    $self->{dbh}=DBI->connect($connectstr, $user, $pass,
+    my $connectstr="dbi:SQLite:dbname=$self->{dbpath}";
+    my $exists=-f $self->{dbpath};
+    $self->{dbh}=DBI->connect($connectstr, undef, undef,
                              { AutoCommit=>1 } );
     unless(defined($self->{dbh}))
     {
@@ -110,23 +103,6 @@ sub create
     {
        $self->{dbh}->do($cmd);
     }
-    if($self->{postgres})
-    {
-       $self->cmd("CREATE SEQUENCE seq");
-    }
-    else
-    {
-       my %indexes=( "idx_files_id"  => "files (id)",
-                     "idx_fxt_both"  => "files_x_tags (files_id, tags_id)",
-                     "idx_fxt_files" => "files_x_tags (files_id)",
-                     "idx_fxt_tags"  => "files_x_tags (tags_id)",
-                     "idx_tags_id"   => "tags (id)",
-                     "idx_tags_name" => "tags (name)");
-       for my $index (keys %indexes)
-       {
-           $self->{dbh}->do("CREATE INDEX $index ON " . $indexes{$index});
-       }
-    }
     $self->cmd("INSERT INTO id3fs (schema_version, last_update) VALUES (?, ?)",
               $SCHEMA_VERSION, time());
 }
@@ -169,6 +145,33 @@ sub last_update
     return $newval;
 }
 
+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
+{
+    # 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
 {
     my($self, $id)=@_;
@@ -180,7 +183,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";
@@ -308,7 +312,6 @@ sub add_to_table
     unless(defined($id))
     {
        my $sql="INSERT INTO $table (";
-       $sql .= "id, " if($self->{postgres});
        my @fields=qw(name);
        if(defined($extradata))
        {
@@ -316,7 +319,6 @@ sub add_to_table
        }
        $sql .= join(", ", @fields);
        $sql .=") VALUES (";
-       $sql .=") nextval('seq'), " if($self->{postgres});
        $sql .= join(", ", map { "?"; } @fields);
        $sql .= ");";
        $id=$self->cmd_id($sql, $name, map { $extradata->{$_} || ""; } sort keys %$extradata);
@@ -360,7 +362,7 @@ sub unindex
 sub prune_directories
 {
     my($self)=@_;
-    my $sql=("SELECT name, id FROM paths ORDER BY name\n");
+    my $sql=("SELECT name, id FROM paths\n");
     my $pathsref=$self->cmd_rows($sql);
     my @ids=();
     for my $pathpair (@$pathsref)
@@ -495,15 +497,7 @@ sub cmd_id
 sub last_insert_id
 {
     my $self=shift;
-    if($self->{postgres})
-    {
-       return $self->{dbh}->last_insert_id(undef, undef, undef, undef,
-                                           { sequence => "seq" });
-    }
-    else
-    {
-       return $self->{dbh}->last_insert_id("","","","");
-    }
+    return $self->{dbh}->last_insert_id("","","","");
 }
 
 sub lookup_id
@@ -571,3 +565,16 @@ CREATE TABLE files_x_tags (
     FOREIGN KEY(tags_id)  REFERENCES tags(id)  ON DELETE CASCADE ON UPDATE CASCADE
 );
 
+CREATE INDEX idx_fxt_both ON files_x_tags (files_id, tags_id)
+
+CREATE INDEX idx_fxt_tags ON files_x_tags (tags_id)
+
+CREATE INDEX idx_files_id_name ON files (id, name)
+
+CREATE INDEX idx_files_name_id ON files (name, id)
+
+CREATE INDEX idx_tags_id_parent_name ON tags (id, parents_id, name)
+
+CREATE INDEX idx_tags_parent_id_name ON tags (parents_id, id, name)
+
+CREATE INDEX idx_tags_name ON tags (name)