$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}))
{
$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";
{
$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());
}
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
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";
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 !~ /\//)
{
{
$self->add_tag($file_id, "decade", "${1}0s");
}
+ else
+ {
+ $self->add_tag($file_id, "decade", "UNKNOWN");
+ }
if($self->ok($v1genre))
{
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 (";
- $sql .= "id, " if($self->{postgres});
my @fields=qw(name);
if(defined($extradata))
{
}
$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);
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)=@_;
- 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)
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);
}
VACUUM
EOT
- print "SQL: $sql\n";
+# print "SQL: $sql\n";
my @sql=split(/\n\n/, $sql);
$self->cmd($_) for (@sql);
}
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
{
- 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;
}
);
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 (
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)
projects / id3fs.git / blobdiff