write to db

diff --git a/lib/ID3FS/DB.pm b/lib/ID3FS/DB.pm
index 25ef83b..af325ba 100644 (file)
--- a/lib/ID3FS/DB.pm
+++ b/lib/ID3FS/DB.pm
@@ -91,17 +91,120 @@ sub add
     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))
     {
-       no strict 'refs';
-       my $thingval=$file->$thing();
-       use strict 'refs';
-       print("\t$thing: ", $thingval, "\n") if(defined($thingval));
+       $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))
+    {
+       $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
@@ -118,6 +221,19 @@ sub cmd_onerow
     return($sth->fetchrow_array());
 }
 
+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 (
@@ -126,7 +242,17 @@ 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 (
@@ -134,7 +260,7 @@ CREATE TABLE tags (
     name
 );
 
-CREATE TABLE tagvalues (
+CREATE TABLE tagvals (
     id INTEGER PRIMARY KEY,
     name
 );
@@ -144,8 +270,17 @@ CREATE TABLE files_x_tags (
     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
+);

diff --git a/lib/ID3FS/File/Mp3.pm b/lib/ID3FS/File/Mp3.pm
index 86a7c4b..8f2dd11 100644 (file)
--- a/lib/ID3FS/File/Mp3.pm
+++ b/lib/ID3FS/File/Mp3.pm
@@ -50,7 +50,7 @@ sub album     { shift->choose("album", 1);  }
 # We don't care if year is not set
 sub year      { shift->choose("year", 0);   }
 sub audiotype { return "mp3";               }
-
+sub haspic    { return undef;               } # FIXME
 sub v1genre
 {
     my($self)=@_;
@@ -60,8 +60,6 @@ sub v1genre
     }
 }
 
-# FIXME: haspic;
-
 sub tags
 {
     my $self=shift;