return $sth;
}
+sub tags
+{
+ my($self, @constraints)=@_;
+ if(!@constraints) # /
+ {
+ my $sql="SELECT DISTINCT name FROM tags;";
+ my $tags=$self->cmd_rows($sql);
+ return(map { $_->[0]; } @$tags);
+ }
+ my @file_ids=();
+ my @tag_ids=();
+ my $main_sql_start=("SELECT DISTINCT tags.name FROM files\n" .
+ "INNER JOIN files_x_tags ON files.id=files_x_tags.files_id\n" .
+ "INNER JOIN tags ON tags.id=files_x_tags.tags_id\n" .
+ "WHERE files.id in (" .
+ ("\tSELECT DISTINCT files.id FROM files\n" .
+ "\tINNER JOIN files_x_tags ON files.id=files_x_tags.files_id\n" .
+ "\tINNER JOIN tags ON tags.id=files_x_tags.tags_id\n" .
+ "\tWHERE tags.id in ("));
+ my $main_sql_mid=")\n) AND tags.id NOT IN (";
+ my $main_sql_end=")\n";
+ while(my $constraint=shift @constraints)
+ {
+ print "CONSTRAINT: $constraint->{name}\n";
+ my $cid=$constraint->{id};
+ push(@tag_ids, $cid);
+ }
+ my $sql = ($main_sql_start . join(", ", @tag_ids) .
+ $main_sql_mid . join(", ", @tag_ids) .
+ $main_sql_end);
+ print "SQL: $sql\n";
+ my $result=$self->cmd_rows($sql);
+ my @tagnames=map { $_->[0]; } @$result;
+ print "SUBNAMES: ", join(', ', @tagnames), "\n";
+ return(@tagnames);
+}
+
+sub tag_values
+{
+ my($self, $tag)=@_;
+ my $sql=("SELECT DISTINCT tagvals.name FROM tags\n" .
+ "INNER JOIN tags_x_tagvals ON tags.id=tags_x_tagvals.tags_id\n" .
+ "INNER JOIN tagvals ON tagvals.id=tags_x_tagvals.tagvals_id\n" .
+ "WHERE tags.name=?");
+ my $tags=$self->cmd_rows($sql, $tag);
+ return(map { $_->[0]; } @$tags);
+}
+
+sub tag_id
+{
+ my($self, $tag)=@_;
+ my $sql='SELECT id FROM tags WHERE name=?';
+ my ($id)=$self->cmd_onerow($sql, $tag);
+ return($id);
+}
+
sub add
{
my($self,$path)=@_;
my $v1genre=$file->v1genre();
my $year=$file->year();
my $audiotype=$file->album();
-#FIXME
-# my $haspic=$file->haspic();
my $tags=$file->tags();
- print "$path:\n";
- for my $thing (qw(artist album v1genre year audiotype))
+ my $haspic=$file->haspic();
+
+ my $file_id=$self->add_to_table("files", $path);
+ my $artists_id=$self->add_to_table("artists", $artist);
+ my $albums_id=$self->add_to_table("albums", $album);
+ for my $tag (keys %$tags)
+ {
+ $self->add_tag($file_id, $tag, $tags->{$tag});
+ }
+
+ if($self->ok($year))
+ {
+ $self->add_tag($file_id, "year", $year);
+ if($year=~/^(\d\d\d)\d$/)
+ {
+ $self->add_tag($file_id, "decade", "${1}0s");
+ }
+ }
+ if($self->ok($v1genre))
{
- no strict 'refs';
- my $thingval=$file->$thing();
- use strict 'refs';
- print("\t$thing: ", $thingval, "\n") if(defined($thingval));
+ $self->add_tag($file_id, "v1genre", $v1genre);
+ }
+
+ if($haspic)
+ {
+ $self->add_tag($file_id, "haspic", undef);
+ }
+
+ $self->add_relation("files_x_artists",
+ { "files_id" => $file_id,
+ "artists_id" => $artists_id });
+
+ $self->add_relation("artists_x_albums",
+ { "artists_id" => $artists_id,
+ "albums_id" => $albums_id});
+}
+
+sub add_tag
+{
+ my($self, $file_id, $tag, $val)=@_;
+ my $tag_id=$self->add_to_table("tags", $tag);
+ $self->add_relation("files_x_tags",
+ { "files_id" => $file_id,
+ "tags_id" => $tag_id });
+ if(defined($val))
+ {
+ my $val_id=$self->add_to_table("tagvals", $val);
+ $self->add_relation("tags_x_tagvals",
+ { "tags_id" => $tag_id,
+ "tagvals_id" => $val_id });
}
}
+sub add_to_table
+{
+ my($self, $table, $name, $extradata)=@_;
+ my $id=$self->lookup_id($table, $name);
+ unless(defined($id))
+ {
+ my $sql="INSERT INTO $table (";
+ my @fields=qw(name);
+ if(defined($extradata))
+ {
+ push(@fields, sort keys(%$extradata));
+ }
+ $sql .= join(", ", @fields);
+ $sql .=") VALUES (";
+ $sql .= join(", ", map { "?"; } @fields);
+ $sql .= ");";
+ $id=$self->cmd_id($sql, $name, map { $extradata->{$_} || ""; } sort keys %$extradata);
+ }
+ return $id;
+}
+
+sub add_relation
+{
+ my ($self, $relname, $fields)=@_;
+ return if($self->relation_exists($relname, $fields));
+ my $sql="INSERT INTO $relname (";
+ $sql .= join(", ", sort keys(%$fields));
+ $sql .= ") VALUES (";
+ $sql .= join(", ", map { "?"; } sort keys(%$fields));
+ $sql .= ");";
+ $self->cmd($sql, map { $fields->{$_}; } sort keys(%$fields));
+}
+
+sub lookup_id
+{
+ my($self, $table, $name)=@_;
+ my($id)=$self->cmd_onerow("SELECT id FROM $table where name=?", $name);
+ return $id;
+}
+
+sub relation_exists
+{
+ my ($self, $relname, $fields)=@_;
+ my $sql="SELECT count(1) FROM $relname WHERE ";
+ my @exprs=();
+ my @vals=();
+ for my $field (keys %$fields)
+ {
+ push(@exprs,$field);
+ push(@vals,$fields->{$field});
+ }
+ $sql .= join(' AND ', map { "$_=?"; } @exprs);
+ my ($ret)=$self->cmd_onerow($sql, @vals);
+ return $ret;
+}
+
+sub ok
+{
+ my($self, $thing)=@_;
+ return(defined($thing) && length($thing));
+}
+
sub cmd
{
my ($self, @args)=@_;
return($sth->fetchrow_array());
}
+sub cmd_rows
+{
+ my ($self, @args)=@_;
+ my $sth=$self->cmd_sth(@args);
+ return $sth->fetchall_arrayref();
+}
+
+sub cmd_id
+{
+ my ($self, @args)=@_;
+ $self->cmd_sth(@args);
+ return($self->last_insert_id());
+}
+
+sub last_insert_id
+{
+ my $self=shift;
+ return $self->{dbh}->last_insert_id("","","","");
+}
+
__DATA__
CREATE TABLE id3fs (
CREATE TABLE files (
id INTEGER PRIMARY KEY,
- path
+ name
+);
+
+CREATE TABLE artists (
+ id INTEGER PRIMARY KEY,
+ name
+);
+
+CREATE TABLE albums (
+ id INTEGER PRIMARY KEY,
+ name
);
CREATE TABLE tags (
name
);
-CREATE TABLE tagvalues (
+CREATE TABLE tagvals (
id INTEGER PRIMARY KEY,
name
);
tags_id
);
-CREATE TABLE tags_x_tagvalues (
+CREATE TABLE tags_x_tagvals (
tags_id,
- tagvalues_id
+ tagvals_id
);
+CREATE TABLE files_x_artists (
+ files_id,
+ artists_id
+);
+
+CREATE TABLE artists_x_albums (
+ artists_id,
+ albums_id
+);
projects / id3fs.git / blobdiff