15 my $class=ref($proto) || $proto;
19 my($dir, $init, $me)=@_;
20 $self->{dbpath}="$dir/$dbfile";
23 my $exists=-f $self->{dbpath};
24 die("$me: $self->{dbpath}: not found. use --init to create.\n") if(!$exists && !$init);
25 die("$me: --init used but $self->{dbpath} exists.\n") if($exists && $init);
27 $self->{dbh}=DBI->connect("dbi:SQLite:dbname=$self->{dbpath}","","",
29 unless(defined($self->{dbh}))
31 die("$me: DB Error: " . $DBI::errstr . "\n");
49 my @schema=split(/\n\n/,join("", <DATA>));
53 $self->{dbh}->do($cmd);
55 $self->cmd("INSERT INTO id3fs (schema_version) VALUES (?)", $SCHEMA_VERSION);
61 my ($version)=$self->cmd_onerow("SELECT schema_version from id3fs");
62 if(!defined($version) || $version != $SCHEMA_VERSION)
64 die("$self->{me}: id3fs database version " .
65 defined($version) ? $version : '""' .
66 "not known, current version is $SCHEMA_VERSION.\n");
72 my($self, $sql, @params)=@_;
73 my $sth=$self->{dbh}->prepare($sql);
75 for my $param (@params)
77 $param="" unless(defined($param));
78 $sth->bind_param($idx++, $param);
87 my $sql="SELECT DISTINCT name FROM tags;";
88 my $tags=$self->cmd_rows($sql);
89 return(map { $_->[0]; } @$tags);
95 my $file=ID3FS::File->new($path);
96 return unless(defined($file));
97 my $artist=$file->artist();
98 my $album=$file->album();
99 my $v1genre=$file->v1genre();
100 my $year=$file->year();
101 my $audiotype=$file->album();
102 my $tags=$file->tags();
103 my $haspic=$file->haspic();
105 my $file_id=$self->add_to_table("files", $path);
106 my $artists_id=$self->add_to_table("artists", $artist);
107 my $albums_id=$self->add_to_table("albums", $album);
108 for my $tag (keys %$tags)
110 $self->add_tag($file_id, $tag, $tags->{$tag});
115 $self->add_tag($file_id, "year", $year);
116 if($year=~/^(\d\d\d)\d$/)
118 $self->add_tag($file_id, "decade", "${1}0s");
121 if($self->ok($v1genre))
123 $self->add_tag($file_id, "v1genre", $v1genre);
128 $self->add_tag($file_id, "haspic", undef);
131 $self->add_relation("files_x_artists",
132 { "files_id" => $file_id,
133 "artists_id" => $artists_id });
135 $self->add_relation("artists_x_albums",
136 { "artists_id" => $artists_id,
137 "albums_id" => $albums_id});
142 my($self, $file_id, $tag, $val)=@_;
143 my $tag_id=$self->add_to_table("tags", $tag);
144 $self->add_relation("files_x_tags",
145 { "files_id" => $file_id,
146 "tags_id" => $tag_id });
149 my $val_id=$self->add_to_table("tagvals", $val);
150 $self->add_relation("tags_x_tagvals",
151 { "tags_id" => $tag_id,
152 "tagvals_id" => $val_id });
158 my($self, $table, $name, $extradata)=@_;
159 my $id=$self->lookup_id($table, $name);
162 my $sql="INSERT INTO $table (";
164 if(defined($extradata))
166 push(@fields, sort keys(%$extradata));
168 $sql .= join(", ", @fields);
170 $sql .= join(", ", map { "?"; } @fields);
172 $id=$self->cmd_id($sql, $name, map { $extradata->{$_} || ""; } sort keys %$extradata);
179 my ($self, $relname, $fields)=@_;
180 return if($self->relation_exists($relname, $fields));
181 my $sql="INSERT INTO $relname (";
182 $sql .= join(", ", sort keys(%$fields));
183 $sql .= ") VALUES (";
184 $sql .= join(", ", map { "?"; } sort keys(%$fields));
186 $self->cmd($sql, map { $fields->{$_}; } sort keys(%$fields));
191 my($self, $table, $name)=@_;
192 my($id)=$self->cmd_onerow("SELECT id FROM $table where name=?", $name);
198 my ($self, $relname, $fields)=@_;
199 my $sql="SELECT count(1) FROM $relname WHERE ";
202 for my $field (keys %$fields)
205 push(@vals,$fields->{$field});
207 $sql .= join(' AND ', map { "$_=?"; } @exprs);
208 my ($ret)=$self->cmd_onerow($sql, @vals);
214 my($self, $thing)=@_;
215 return(defined($thing) && length($thing));
220 my ($self, @args)=@_;
221 # don't care about retcode
222 $self->cmd_sth(@args);
227 my ($self, @args)=@_;
228 my $sth=$self->cmd_sth(@args);
229 return($sth->fetchrow_array());
234 my ($self, @args)=@_;
235 my $sth=$self->cmd_sth(@args);
236 return $sth->fetchall_arrayref();
241 my ($self, @args)=@_;
242 $self->cmd_sth(@args);
243 return($self->last_insert_id());
249 return $self->{dbh}->last_insert_id("","","","");
259 id INTEGER PRIMARY KEY,
263 CREATE TABLE artists (
264 id INTEGER PRIMARY KEY,
268 CREATE TABLE albums (
269 id INTEGER PRIMARY KEY,
274 id INTEGER PRIMARY KEY,
278 CREATE TABLE tagvals (
279 id INTEGER PRIMARY KEY,
283 CREATE TABLE files_x_tags (
288 CREATE TABLE tags_x_tagvals (
293 CREATE TABLE files_x_artists (
298 CREATE TABLE artists_x_albums (