15 my $class=ref($proto) || $proto;
19 my($dir, $init, $me)=@_;
21 $self->{absbase}=Cwd::abs_path($dir);
22 $self->{dbpath}="$dir/$dbfile";
25 my $exists=-f $self->{dbpath};
29 unless($self->{postgres})
31 die("$me: $self->{dbpath}: not found. use --init to create.\n") if(!$exists && !$init);
32 die("$me: --init used but $self->{dbpath} exists.\n") if($exists && $init);
35 my $connectstr="dbi:SQLite:dbname=$self->{dbpath}";
36 my ($user, $pass)=("", "");
39 $connectstr="dbi:Pg:dbname=id3fs";
43 $self->{dbh}=DBI->connect($connectstr, $user, $pass,
45 unless(defined($self->{dbh}))
47 die("$me: DB Error: " . $DBI::errstr . "\n");
65 my @schema=split(/\n\n/,join("", <DATA>));
69 $self->{dbh}->do($cmd);
73 $self->cmd("CREATE SEQUENCE seq");
77 my %indexes=( "idx_files_id" => "files (id)",
78 "idx_fxt_both" => "files_x_tags (files_id, tags_id)",
79 "idx_fxt_files" => "files_x_tags (files_id)",
80 "idx_fxt_tags" => "files_x_tags (tags_id)",
81 "idx_tags_id" => "tags (id)",
82 "idx_tags_name" => "tags (name)");
83 for my $index (keys %indexes)
85 $self->{dbh}->do("CREATE INDEX $index ON " . $indexes{$index});
88 $self->cmd("INSERT INTO id3fs (schema_version) VALUES (?)", $SCHEMA_VERSION);
94 my ($version)=$self->cmd_onerow("SELECT schema_version from id3fs");
95 if(!defined($version) || $version != $SCHEMA_VERSION)
97 die("$self->{me}: id3fs database version " .
98 defined($version) ? $version : '""' .
99 "not known, current version is $SCHEMA_VERSION.\n");
105 my($self, $sql, @params)=@_;
106 my $sth=$self->{dbh}->prepare($sql);
108 for my $param (@params)
110 $param="" unless(defined($param));
111 $sth->bind_param($idx++, $param);
119 my($self, @constraints)=@_;
120 if(!@constraints) # /
122 my $sql="SELECT DISTINCT name FROM tags;";
123 my $tags=$self->cmd_rows($sql);
124 return(map { $_->[0]; } @$tags);
128 my $main_sql_start=("SELECT t2.name\n" .
129 "\tFROM (SELECT files_id FROM tags t1\n" .
130 "\t\tINNER JOIN files_x_tags ON t1.id=files_x_tags.tags_id\n" .
131 "\t\tWHERE t1.id in\n\t\t\t(");
132 my $main_sql_mid=(")\n\t\t) AS subselect\n" .
133 "\tINNER JOIN files_x_tags ON subselect.files_id=files_x_tags.files_id\n" .
134 "\tINNER JOIN tags t2 ON files_x_tags.tags_id=t2.id\n" .
135 "\tWHERE t2.id NOT IN (");
136 my $main_sql_end=")\n\tGROUP BY t2.name;";
137 while(my $constraint=shift @constraints)
139 print "CONSTRAINT: $constraint->{name}\n";
140 my $cid=$constraint->{id};
143 @ids = map( { "\"$_\""; } @ids) unless($self->{postgres});
144 my $tagstr=join(", ", @ids);
145 my $sql = ($main_sql_start . $tagstr .
146 $main_sql_mid . $tagstr .
149 my $result=$self->cmd_rows($sql);
150 my @tagnames=map { $_->[0]; } @$result;
151 print "SUBNAMES: ", join(', ', @tagnames), "\n";
158 my $sql=("SELECT DISTINCT tagvals.name FROM tags\n" .
159 "INNER JOIN tags_x_tagvals ON tags.id=tags_x_tagvals.tags_id\n" .
160 "INNER JOIN tagvals ON tagvals.id=tags_x_tagvals.tagvals_id\n" .
161 "WHERE tags.name=?");
162 my $tags=$self->cmd_rows($sql, $tag);
163 return(map { $_->[0]; } @$tags);
168 my($self, @constraints)=@_;
169 if(!@constraints) # /ALL
171 my $sql="SELECT DISTINCT name FROM artists;";
172 my $tags=$self->cmd_rows($sql);
173 return(map { $_->[0]; } @$tags);
176 my $main_sql_start=("SELECT artists.name\n" .
177 "\tFROM (SELECT files_id FROM tags\n" .
178 "\t\tINNER JOIN files_x_tags ON tags.id=files_x_tags.tags_id\n" .
179 "\t\tWHERE tags.id in\n\t\t\t(");
180 my $main_sql_end=(")\n\t\t) AS subselect\n" .
181 "\tINNER JOIN files_x_artists ON subselect.files_id=files_x_artists.files_id\n" .
182 "\tINNER JOIN artists ON artists.id=files_x_artists.artists_id\n" .
183 "\n\tGROUP BY artists.name;");
184 while(my $constraint=shift @constraints)
186 print "CONSTRAINT: $constraint->{name}\n";
187 my $cid=$constraint->{id};
190 @ids = map( { "\"$_\""; } @ids) unless($self->{postgres});
191 my $tagstr=join(", ", @ids);
192 my $sql = ($main_sql_start . $tagstr .
195 my $result=$self->cmd_rows($sql);
196 my @tagnames=map { $_->[0]; } @$result;
197 print "ARTISTS: ", join(', ', @tagnames), "\n";
203 my($self, @constraints)=@_;
205 # FIXME: rework PathElements
206 if(ref($constraints[$#constraints]) eq "ID3FS::PathElement::Artist")
208 return $self->artist_albums($constraints[$#constraints]->{id});
210 my $main_sql_start=("SELECT albums.name\n" .
211 "\tFROM (SELECT files_id FROM tags\n" .
212 "\t\tINNER JOIN files_x_tags ON tags.id=files_x_tags.tags_id\n" .
213 "\t\tWHERE tags.id in\n\t\t\t(");
214 my $main_sql_end=(")\n\t\t) AS subselect\n" .
215 "\tINNER JOIN files_x_albums ON subselect.files_id=files_x_albums.files_id\n" .
216 "\tINNER JOIN albums ON albums.id=files_x_albums.albums_id\n" .
217 "\n\tGROUP BY albums.name;");
218 while(my $constraint=shift @constraints)
220 print "CONSTRAINT: $constraint->{name}\n";
221 my $cid=$constraint->{id};
224 @ids = map( { "\"$_\""; } @ids) unless($self->{postgres});
225 my $str=join(", ", @ids);
226 my $sql = ($main_sql_start . $str .
229 my $result=$self->cmd_rows($sql);
230 my @names=map { $_->[0]; } @$result;
231 print "ALBUMS: ", join(', ', @names), "\n";
237 my($self, $artist_id)=@_;
238 my $sql=("SELECT albums.name FROM artists\n\t" .
239 "INNER JOIN artists_x_albums ON artists.id=artists_x_albums.artists_id\n\t" .
240 "INNER JOIN albums ON albums.id=artists_x_albums.albums_id\n\t" .
241 "WHERE artists.id=?\n\t" .
242 "GROUP BY albums.name\n");
243 print "ARTIST_ALBUMS SQL: $sql\n";
244 my $result=$self->cmd_rows($sql, $artist_id);
245 my @albums=map { $_->[0]; } @$result;
246 print "ALBUMS: ", join(', ', @albums), "\n";
252 my($self, $artist_id)=@_;
253 my $sql=("SELECT files.name FROM artists\n\t" .
254 "INNER JOIN artists_x_files ON artists.id=files_x_artists.artists_id\n\t" .
255 "INNER JOIN files ON files.id=files_x_artists.files_id\n\t" .
256 "WHERE artists.id=?\n\t" .
257 "GROUP BY files.name\n");
258 print "ARTIST_TRACKS SQL: $sql\n";
259 my $result=$self->cmd_rows($sql, $artist_id);
260 my @names=map { $_->[0]; } @$result;
261 @names = map { s/.*\///; } @names;
262 print "ARTISTTRACKS: ", join(', ', @names), "\n";
268 my($self, $album_id)=@_;
269 my $sql=("SELECT files.name FROM files\n\t" .
270 "INNER JOIN files_x_albums ON albums.id=files_x_albums.albums_id\n\t" .
271 "INNER JOIN albums ON albums.id=files_x_albums.albums_id\n\t" .
272 "WHERE albums.id=?\n\t" .
273 "GROUP BY files.name\n");
274 print "ALBUM_TRACKS SQL($album_id): $sql\n";
275 my $result=$self->cmd_rows($sql, $album_id);
276 my @names=map { $_->[0]; } @$result;
277 @names = map { s/.*\///; $_;} @names;
278 print "TRACKS: ", join(', ', @names), "\n";
284 my($self, @constraints)=@_;
285 # FIXME: rework PathElements
286 if(ref($constraints[$#constraints]) eq "ID3FS::PathElement::Artist")
288 return $self->artist_tracks($constraints[$#constraints]->{id});
290 elsif(ref($constraints[$#constraints]) eq "ID3FS::PathElement::Album")
292 return $self->album_tracks($constraints[$#constraints]->{id});
295 my $main_sql_start=("SELECT files.name\n" .
296 "\tFROM (SELECT files_id FROM tags\n" .
297 "\t\tINNER JOIN files_x_tags ON tags.id=files_x_tags.tags_id\n" .
298 "\t\tWHERE tags.id in\n\t\t\t(");
299 my $main_sql_end=(")\n\t\t) AS subselect\n" .
300 "\tINNER JOIN files ON files.id=subselect.files_id" .
301 "\tGROUP BY files.name;");
303 while(my $constraint=shift @constraints)
305 print "CONSTRAINT: $constraint->{name}\n";
306 my $cid=$constraint->{id};
309 @ids = map( { "\"$_\""; } @ids) unless($self->{postgres});
310 my $str=join(", ", @ids);
311 my $sql = ($main_sql_start . $str .
314 my $result=$self->cmd_rows($sql);
315 my @names=map { $_->[0]; } @$result;
316 @names = map { s/.*\///; $_; } @names;
317 print "TRACKS: ", join(', ', @names), "\n";
323 my($self, @constraints)=@_;
324 if(ref($constraints[$#constraints]) eq "ID3FS::PathElement::File")
326 my $id=$constraints[$#constraints]->{id};
327 my $sql=("SELECT paths.name, files.name FROM files\n" .
328 "INNER JOIN paths_x_files ON files.id=paths_x_files.files_id\n" .
329 "INNER JOIN paths ON paths_x_files.paths_id=paths.id\n" .
330 "WHERE files.id=?\n" .
331 "GROUP BY paths.name, files.name");
332 print "FILENAME SQL: $sql\n";
333 my ($path, $name)=$self->cmd_onerow($sql, $id);
334 return($self->{absbase} . "/$path/$name");
336 die("DB::filename: unhandled case\n"); #FIXME
341 my($self, $type, $val)=@_;
342 print "ID: $type $val\n";
343 my $sql="SELECT id FROM $type WHERE name=?";
344 my ($id)=$self->cmd_onerow($sql, $val);
352 $relpath =~ s/$self->{base}\/?//;
353 my($filepart,$pathpart);
361 ($pathpart, $filepart) = ($relpath =~ /(.*)\/(.*)/);
363 my $file=ID3FS::File->new($path);
364 return unless(defined($file));
365 my $artist=$file->artist();
366 my $album=$file->album();
367 my $v1genre=$file->v1genre();
368 my $year=$file->year();
369 my $audiotype=$file->album();
370 my $tags=$file->tags();
371 my $haspic=$file->haspic();
373 my $file_id=$self->add_to_table("files", $filepart);
374 my $path_id=$self->add_to_table("paths", $pathpart);
375 $self->add_relation("paths_x_files",
376 { "paths_id" => $path_id,
377 "files_id" => $file_id});
379 for my $tag (keys %$tags)
381 $self->add_tag($file_id, $tag, $tags->{$tag});
385 if($self->ok($artist))
387 $artist_id=$self->add_to_table("artists", $artist);
388 $self->add_relation("files_x_artists",
389 { "files_id" => $file_id,
390 "artists_id" => $artist_id });
393 if($self->ok($album))
395 my $albums_id=$self->add_to_table("albums", $album);
396 $self->add_relation("files_x_albums",
397 { "files_id" => $file_id,
398 "albums_id" => $albums_id});
399 if($self->ok($artist))
401 $self->add_relation("artists_x_albums",
402 { "artists_id" => $artist_id,
403 "albums_id" => $albums_id});
409 $self->add_tag($file_id, "year", $year);
410 if($year=~/^(\d\d\d)\d$/)
412 $self->add_tag($file_id, "decade", "${1}0s");
416 if($self->ok($v1genre))
418 $self->add_tag($file_id, "v1genre", $v1genre);
423 $self->add_tag($file_id, "haspic", undef);
429 my($self, $file_id, $tag, $val)=@_;
430 my $tag_id=$self->add_to_table("tags", $tag);
431 $self->add_relation("files_x_tags",
432 { "files_id" => $file_id,
433 "tags_id" => $tag_id });
436 my $val_id=$self->add_to_table("tagvals", $val);
437 $self->add_relation("tags_x_tagvals",
438 { "tags_id" => $tag_id,
439 "tagvals_id" => $val_id });
445 my($self, $table, $name, $extradata)=@_;
446 my $id=$self->lookup_id($table, $name);
449 my $sql="INSERT INTO $table (";
450 $sql .= "id, " if($self->{postgres});
452 if(defined($extradata))
454 push(@fields, sort keys(%$extradata));
456 $sql .= join(", ", @fields);
458 $sql .=") nextval('seq'), " if($self->{postgres});
459 $sql .= join(", ", map { "?"; } @fields);
461 $id=$self->cmd_id($sql, $name, map { $extradata->{$_} || ""; } sort keys %$extradata);
468 my ($self, $relname, $fields)=@_;
469 return if($self->relation_exists($relname, $fields));
470 my $sql="INSERT INTO $relname (";
471 $sql .= join(", ", sort keys(%$fields));
472 $sql .= ") VALUES (";
473 $sql .= join(", ", map { "?"; } sort keys(%$fields));
475 $self->cmd($sql, map { $fields->{$_}; } sort keys(%$fields));
480 my($self, $table, $name)=@_;
481 my($id)=$self->cmd_onerow("SELECT id FROM $table where name=?", $name);
487 my ($self, $relname, $fields)=@_;
488 my $sql="SELECT count(1) FROM $relname WHERE ";
491 for my $field (keys %$fields)
494 push(@vals,$fields->{$field});
496 $sql .= join(' AND ', map { "$_=?"; } @exprs);
497 my ($ret)=$self->cmd_onerow($sql, @vals);
503 my($self, $thing)=@_;
504 return(defined($thing) && length($thing) && $thing =~ /\S+/);
509 my ($self, @args)=@_;
510 # don't care about retcode
511 $self->cmd_sth(@args);
516 my ($self, @args)=@_;
517 my $sth=$self->cmd_sth(@args);
518 return($sth->fetchrow_array());
523 my ($self, @args)=@_;
524 my $sth=$self->cmd_sth(@args);
525 return $sth->fetchall_arrayref();
530 my ($self, @args)=@_;
531 $self->cmd_sth(@args);
532 return($self->last_insert_id());
538 if($self->{postgres})
540 return $self->{dbh}->last_insert_id(undef, undef, undef, undef,
541 { sequence => "seq" });
545 return $self->{dbh}->last_insert_id("","","","");
552 schema_version INTEGER
556 id INTEGER PRIMARY KEY,
561 id INTEGER PRIMARY KEY,
565 CREATE TABLE artists (
566 id INTEGER PRIMARY KEY,
570 CREATE TABLE albums (
571 id INTEGER PRIMARY KEY,
576 id INTEGER PRIMARY KEY,
580 CREATE TABLE tagvals (
581 id INTEGER PRIMARY KEY,
585 CREATE TABLE paths_x_files (
590 CREATE TABLE files_x_tags (
595 CREATE TABLE tags_x_tagvals (
600 CREATE TABLE files_x_artists (
605 CREATE TABLE files_x_albums (
610 CREATE TABLE artists_x_albums (