lib/ID3FS/DB.pm

   1 package ID3FS::DB;
   2
   3 use strict;
   4 use warnings;
   5 use DBI;
   6 use ID3FS::AudioFile;
   7 use Cwd;
   8
   9 our $SCHEMA_VERSION=1;
  10 my $dbfile=".id3fs";
  11
  12 sub new
  13 {
  14     my $proto=shift;
  15     my $class=ref($proto) || $proto;
  16     my $self={};
  17     bless($self,$class);
  18
  19     $self->{me}=shift;
  20     $self->{dbpath}=shift;
  21     $self->{base}=shift;
  22     $self->{fallbackdir}=shift;
  23
  24     if(!defined($self->{base}) &&
  25        defined($self->{fallbackdir}) &&
  26        -d $self->{fallbackdir})
  27     {
  28         $self->{base}=$self->{fallbackdir};
  29     }
  30     $self->{dbpath}="$self->{base}/$dbfile" unless(defined($self->{dbpath}));
  31     $self->{absbase}=Cwd::abs_path($self->{base});
  32
  33     my $connectstr="dbi:SQLite:dbname=$self->{dbpath}";
  34     my ($user, $pass)=("", "");
  35     if($self->{postgres})
  36     {
  37         $connectstr="dbi:Pg:dbname=id3fs";
  38         $user="ianb";
  39         $pass="foo";
  40     }
  41     my $exists=-f $self->{dbpath};
  42     $self->{dbh}=DBI->connect($connectstr, $user, $pass,
  43                               { AutoCommit=>1 } );
  44     unless(defined($self->{dbh}))
  45     {
  46         die("$self->{me}: DB Error: " . $DBI::errstr . "\n");
  47     }
  48
  49     if($exists)
  50     {
  51         $self->checkschema();
  52     }
  53     else
  54     {
  55         $self->create();
  56     }
  57
  58     return $self;
  59 }
  60
  61 sub create
  62 {
  63     my($self,$name)=@_;
  64     my @schema=split(/\n\n/,join("", <DATA>));
  65     close(DATA);
  66     for my $cmd (@schema)
  67     {
  68         $self->{dbh}->do($cmd);
  69     }
  70     if($self->{postgres})
  71     {
  72         $self->cmd("CREATE SEQUENCE seq");
  73     }
  74     else
  75     {
  76         my %indexes=( "idx_files_id"  => "files (id)",
  77                       "idx_fxt_both"  => "files_x_tags (files_id, tags_id)",
  78                       "idx_fxt_files" => "files_x_tags (files_id)",
  79                       "idx_fxt_tags"  => "files_x_tags (tags_id)",
  80                       "idx_tags_id"   => "tags (id)",
  81                       "idx_tags_name" => "tags (name)");
  82         for my $index (keys %indexes)
  83         {
  84             $self->{dbh}->do("CREATE INDEX $index ON " . $indexes{$index});
  85         }
  86     }
  87     $self->cmd("INSERT INTO id3fs (schema_version, last_update) VALUES (?, ?)",
  88                $SCHEMA_VERSION, time());
  89 }
  90
  91 sub checkschema
  92 {
  93     my $self=shift;
  94     my ($version)=$self->cmd_onerow("SELECT schema_version from id3fs");
  95     if(!defined($version) || $version != $SCHEMA_VERSION)
  96     {
  97         die("$self->{me}: id3fs database version " .
  98             defined($version) ? $version : '""' .
  99             "not known, current version is $SCHEMA_VERSION.\n");
 100     }
 101 }
 102
 103 sub last_update
 104 {
 105     my($self, $newval)=@_;
 106     if(defined($newval))
 107     {
 108         $self->cmd("UPDATE id3fs SET last_update=?", $newval);
 109     }
 110     else
 111     {
 112         ($newval)=$self->cmd_onerow("SELECT last_update from id3fs");
 113     }
 114     return $newval;
 115 }
 116
 117 sub cmd_sth
 118 {
 119     my($self, $sql, @params)=@_;
 120     my $sth=$self->{dbh}->prepare($sql);
 121     my $idx=1;
 122     for my $param (@params)
 123     {
 124         $param="" unless(defined($param));
 125         $sth->bind_param($idx++, $param);
 126     }
 127     $sth->execute();
 128     return $sth;
 129 }
 130
 131 sub tags
 132 {
 133     my($self, @constraints)=@_;
 134     if(!@constraints) # /
 135     {
 136         # FIXME: add ALL?
 137         my $sql="SELECT DISTINCT name FROM tags;";
 138         my $tags=$self->cmd_rows($sql);
 139         return(map { $_->[0]; } @$tags);
 140     }
 141     my @ids=();
 142
 143     my $main_sql_start=("SELECT t2.name\n" .
 144                         "\tFROM (SELECT files_id FROM tags t1\n" .
 145                         "\t\tINNER JOIN files_x_tags ON t1.id=files_x_tags.tags_id\n" .
 146                         "\t\tWHERE t1.id in\n\t\t\t(");
 147     my $main_sql_mid=(")\n\t\t) AS subselect\n" .
 148                       "\tINNER JOIN files_x_tags ON subselect.files_id=files_x_tags.files_id\n" .
 149                       "\tINNER JOIN tags t2 ON files_x_tags.tags_id=t2.id\n" .
 150                       "\tWHERE t2.id NOT IN (");
 151     my $main_sql_end=")\n\tGROUP BY t2.name;";
 152     while(my $constraint=shift @constraints)
 153     {
 154         my $cid=$constraint->{id};
 155         push(@ids, $cid);
 156     }
 157     @ids = map( { "\"$_\""; } @ids) unless($self->{postgres});
 158     my $tagstr=join(", ", @ids);
 159     my $sql = ($main_sql_start . $tagstr .
 160                $main_sql_mid   . $tagstr .
 161                $main_sql_end);
 162     print "SQL: $sql\n";
 163     my $result=$self->cmd_rows($sql);
 164     my @tagnames=map { $_->[0]; } @$result;
 165     print "SUBNAMES: ", join(', ', @tagnames), "\n";
 166     return(@tagnames);
 167 }
 168
 169 sub tag_values
 170 {
 171     my($self, $tagid)=@_;
 172     my $sql=("SELECT DISTINCT tagvals.name FROM tagvals\n" .
 173              "INNER JOIN tags_x_tagvals ON tagvals.id=tags_x_tagvals.tagvals_id\n" .
 174              "WHERE tags_x_tagvals.tags_id=?");
 175     my $tags=$self->cmd_rows($sql, $tagid);
 176     my @tags=map { $_->[0]; } @$tags;
 177     @tags=map { length($_) ? $_ : "NOVALUE"; } @tags;
 178     return @tags;
 179 }
 180
 181 sub artists
 182 {
 183     my($self, @constraints)=@_;
 184     if(!@constraints) # /ALL
 185     {
 186         my $sql="SELECT DISTINCT name FROM artists;";
 187         my $tags=$self->cmd_rows($sql);
 188         return(map { $_->[0]; } @$tags);
 189     }
 190     my @ids=();
 191     my $main_sql_start=("SELECT artists.name\n" .
 192                         "\tFROM (SELECT files_id FROM tags\n" .
 193                         "\t\tINNER JOIN files_x_tags ON tags.id=files_x_tags.tags_id\n" .
 194                         "\t\tWHERE tags.id in\n\t\t\t(");
 195     my $main_sql_end=(")\n\t\t) AS subselect\n" .
 196                       "\tINNER JOIN files ON subselect.files_id=files.id\n" .
 197                       "\tINNER JOIN artists ON files.artists_id=artists.id\n" .
 198                       "\n\tGROUP BY artists.name;");
 199     while(my $constraint=shift @constraints)
 200     {
 201         my $cid=$constraint->{id};
 202         push(@ids, $cid);
 203     }
 204     @ids = map( { "\"$_\""; } @ids) unless($self->{postgres});
 205     my $tagstr=join(", ", @ids);
 206     my $sql = ($main_sql_start . $tagstr .
 207                $main_sql_end);
 208     print "SQL: $sql\n";
 209     my $result=$self->cmd_rows($sql);
 210     my @tagnames=map { $_->[0]; } @$result;
 211     print "ARTISTS: ", join(', ', @tagnames), "\n";
 212     return(@tagnames);
 213 }
 214
 215 sub albums
 216 {
 217     my($self, @constraints)=@_;
 218     my @ids=();
 219     # FIXME: rework PathElements
 220     if(ref($constraints[$#constraints]) eq "ID3FS::PathElement::Artist")
 221     {
 222         return $self->artist_albums($constraints[$#constraints]->{id});
 223     }
 224     my $main_sql_start=("SELECT albums.name\n" .
 225                         "\tFROM (SELECT files_id FROM tags\n" .
 226                         "\t\tINNER JOIN files_x_tags ON tags.id=files_x_tags.tags_id\n" .
 227                         "\t\tWHERE tags.id in\n\t\t\t(");
 228     my $main_sql_end=(")\n\t\t) AS subselect\n" .
 229                       "\tINNER JOIN files ON subselect.files_id=files.id\n" .
 230                       "\tINNER JOIN albums ON files.albums_id=albums.id\n" .
 231                       "\n\tGROUP BY albums.name;");
 232     while(my $constraint=shift @constraints)
 233     {
 234         my $cid=$constraint->{id};
 235         push(@ids, $cid);
 236     }
 237     @ids = map( { "\"$_\""; } @ids) unless($self->{postgres});
 238     my $str=join(", ", @ids);
 239     my $sql = ($main_sql_start . $str .
 240                $main_sql_end);
 241     my $result=$self->cmd_rows($sql);
 242     my @names=map { $_->[0]; } @$result;
 243     print "ALBUMS: ", join(', ', @names), "\n";
 244     return(@names);
 245 }
 246
 247 sub artist_albums
 248 {
 249     my($self, $artist_id)=@_;
 250     my $sql=("SELECT albums.name FROM files\n\t" .
 251              "INNER JOIN albums ON albums.id=files.albums_id\n\t" .
 252              "INNER JOIN artists ON artists.id=files.artists_id\n\t" .
 253              "WHERE artists.id=? and albums.name <> ''\n\t" .
 254              "GROUP BY albums.name\n");
 255     print "ARTIST_ALBUMS SQL: $sql\n";
 256     my $result=$self->cmd_rows($sql, $artist_id);
 257     my @albums=map { $_->[0]; } @$result;
 258     print "ALBUMS: ", join(', ', @albums), "\n";
 259     return(@albums);
 260 }
 261
 262 sub artist_tracks
 263 {
 264     my($self, $artist_id)=@_;
 265     my $sql=("SELECT files.name FROM files\n\t" .
 266              "INNER JOIN artists ON artists.id=files.artists_id\n\t" .
 267              "INNER JOIN albums  ON albums.id=files.albums_id\n\t" .
 268              "WHERE artists.id=? AND albums.name=''\n\t" .
 269              "GROUP BY files.name\n");
 270     print "ARTIST_TRACKS SQL: $sql\n";
 271     my $result=$self->cmd_rows($sql, $artist_id);
 272     my @names=map { $_->[0]; } @$result;
 273     @names = map { s/.*\///; $_; } @names;
 274     print "ARTISTTRACKS: ", join(', ', @names), "\n";
 275     return(@names);
 276 }
 277
 278 sub album_tracks
 279 {
 280     my($self, $artist_id, $album_id)=@_;
 281     my $sql=("SELECT files.name FROM files\n\t" .
 282              "INNER JOIN albums  ON albums.id=files.albums_id\n\t" .
 283              "INNER JOIN artists ON artists.id=files.artists_id\n\t" .
 284              "WHERE artists.id=? AND albums.id=?\n\t" .
 285              "GROUP BY files.name\n");
 286     print "ALBUM_TRACKS SQL($artist_id, $album_id): $sql\n";
 287     my $result=$self->cmd_rows($sql, $artist_id, $album_id);
 288     my @names=map { $_->[0]; } @$result;
 289     @names = map { s/.*\///; $_;} @names;
 290     print "TRACKS: ", join(', ', @names), "\n";
 291     return(@names);
 292 }
 293
 294 sub tracks
 295 {
 296     my($self, @constraints)=@_;
 297     # FIXME: rework PathElements
 298     if(ref($constraints[$#constraints]) eq "ID3FS::PathElement::Artist")
 299     {
 300         return $self->artist_tracks($constraints[$#constraints]->{id});
 301     }
 302     elsif(ref($constraints[$#constraints]) eq "ID3FS::PathElement::Album")
 303     {
 304         my $artist_id=0;
 305         my $artist=$constraints[($#constraints)-1];
 306         if(defined($artist) && (ref($artist) eq "ID3FS::PathElement::Artist"))
 307         {
 308             # should always happen
 309             $artist_id=$artist->{id};
 310         }
 311         return $self->album_tracks($artist_id, $constraints[$#constraints]->{id});
 312     }
 313
 314     my $main_sql_start=("SELECT files.name\n" .
 315                         "\tFROM (SELECT files_id FROM tags\n" .
 316                         "\t\tINNER JOIN files_x_tags ON tags.id=files_x_tags.tags_id\n" .
 317                         "\t\tWHERE tags.id in\n\t\t\t(");
 318     my $main_sql_end=(")\n\t\t) AS subselect\n" .
 319                       "\tINNER JOIN files ON files.id=subselect.files_id" .
 320                       "\tGROUP BY files.name;");
 321     my @ids;
 322     while(my $constraint=shift @constraints)
 323     {
 324         my $cid=$constraint->{id};
 325         push(@ids, $cid);
 326     }
 327     @ids = map( { "\"$_\""; } @ids) unless($self->{postgres});
 328     my $str=join(", ", @ids);
 329     my $sql = ($main_sql_start . $str .
 330                $main_sql_end);
 331     print "SQL: $sql\n";
 332     my $result=$self->cmd_rows($sql);
 333     my @names=map { $_->[0]; } @$result;
 334     @names = map { s/.*\///; $_; } @names;
 335     print "TRACKS: ", join(', ', @names), "\n";
 336     return(@names);
 337 }
 338
 339 sub filename
 340 {
 341     my($self, @constraints)=@_;
 342     if(ref($constraints[$#constraints]) eq "ID3FS::PathElement::File")
 343     {
 344         my $id=$constraints[$#constraints]->{id};
 345         my $sql=("SELECT paths.name, files.name FROM files\n" .
 346                  "INNER JOIN paths ON files.paths_id=paths.id\n" .
 347                  "WHERE files.id=?\n" .
 348                  "GROUP BY paths.name, files.name");
 349         print "FILENAME SQL: $sql\n";
 350         my ($path, $name)=$self->cmd_onerow($sql, $id);
 351         return($self->{absbase} . "/$path/$name");
 352     }
 353     die("DB::filename: unhandled case\n"); #FIXME
 354 }
 355
 356 sub id
 357 {
 358     my($self, $type, $val)=@_;
 359     my $sql="SELECT id FROM $type WHERE name=?";
 360     my ($id)=$self->cmd_onerow($sql, $val);
 361     return($id);
 362 }
 363
 364 sub add
 365 {
 366     my($self,$path)=@_;
 367     my $relpath=$path;
 368     $relpath =~ s/^\Q$self->{base}\E\/?//;
 369     my($filepart,$pathpart);
 370     if($path !~ /\//)
 371     {
 372         $pathpart='';
 373         $filepart=$relpath;
 374     }
 375     else
 376     {
 377         ($pathpart, $filepart) = ($relpath =~ /(.*)\/(.*)/);
 378     }
 379     my $file=ID3FS::AudioFile->new($path);
 380     return unless(defined($file));
 381     my $artist=$file->artist();
 382     my $album=$file->album();
 383     my $v1genre=$file->v1genre();
 384     my $year=$file->year();
 385     my $audiotype=$file->audiotype();
 386     my $tags=$file->tags();
 387     my $haspic=$file->haspic();
 388
 389     $artist=undef unless($self->ok($artist));
 390     my $artist_id=$self->add_to_table("artists",  $artist);
 391     my $path_id=$self->add_to_table("paths", $pathpart);
 392     $album=undef unless($self->ok($album));
 393     my $albums_id=$self->add_to_table("albums", $album);
 394     my $file_id=$self->add_to_table("files", $filepart,
 395                                     { "artists_id" => $artist_id,
 396                                       "albums_id"  => $albums_id,
 397                                       "paths_id"   => $path_id });
 398     for my $tag (keys %$tags)
 399     {
 400         $self->add_tag($file_id, $tag, $tags->{$tag});
 401     }
 402
 403     if($self->ok($year))
 404     {
 405         $self->add_tag($file_id, "year", $year);
 406         if($year=~/^(\d\d\d)\d$/)
 407         {
 408             $self->add_tag($file_id, "decade", "${1}0s");
 409         }
 410     }
 411
 412     if($self->ok($v1genre))
 413     {
 414         $self->add_tag($file_id, "v1genre", $v1genre);
 415     }
 416
 417     if($haspic)
 418     {
 419         $self->add_tag($file_id, "haspic", undef);
 420     }
 421 }
 422
 423 sub add_tag
 424 {
 425     my($self, $file_id, $tag, $val)=@_;
 426     my $tag_id=$self->add_to_table("tags",  $tag);
 427     $self->add_relation("files_x_tags",
 428                         { "files_id" => $file_id,
 429                           "tags_id"  => $tag_id });
 430     if(defined($val))
 431     {
 432         my $val_id=$self->add_to_table("tagvals", $val);
 433         $self->add_relation("tags_x_tagvals",
 434                             { "tags_id"     => $tag_id,
 435                               "tagvals_id"  => $val_id });
 436     }
 437 }
 438
 439 sub add_to_table
 440 {
 441     my($self, $table, $name, $extradata)=@_;
 442     my $id=$self->lookup_id($table, $name);
 443     unless(defined($id))
 444     {
 445         my $sql="INSERT INTO $table (";
 446         $sql .= "id, " if($self->{postgres});
 447         my @fields=qw(name);
 448         if(defined($extradata))
 449         {
 450             push(@fields, sort keys(%$extradata));
 451         }
 452         $sql .= join(", ", @fields);
 453         $sql .=") VALUES (";
 454         $sql .=") nextval('seq'), " if($self->{postgres});
 455         $sql .= join(", ", map { "?"; } @fields);
 456         $sql .= ");";
 457         $id=$self->cmd_id($sql, $name, map { $extradata->{$_} || ""; } sort keys %$extradata);
 458     }
 459     return $id;
 460 }
 461
 462 sub add_relation
 463 {
 464     my ($self, $relname, $fields)=@_;
 465     return if($self->relation_exists($relname, $fields));
 466     my $sql="INSERT INTO $relname (";
 467     $sql .= join(", ", sort keys(%$fields));
 468     $sql .= ") VALUES (";
 469     $sql .= join(", ", map { "?"; } sort keys(%$fields));
 470     $sql .= ");";
 471     $self->cmd($sql, map { $fields->{$_}; } sort keys(%$fields));
 472 }
 473
 474 sub lookup_id
 475 {
 476     my($self, $table, $name)=@_;
 477     my($id)=$self->cmd_onerow("SELECT id FROM $table where name=?", $name);
 478     return $id;
 479 }
 480
 481 sub tag_has_values
 482 {
 483     my($self, $id)=@_;
 484     my $sql=("SELECT COUNT(*) FROM tags\n\t" .
 485              "INNER JOIN tags_x_tagvals ON tags.id=tags_x_tagvals.tags_id\n\t" .
 486              "INNER JOIN tagvals ON tagvals.id=tags_x_tagvals.tagvals_id\n\t" .
 487              "WHERE tags.id=?\n");
 488     my ($rows)=$self->cmd_onerow($sql, $id);
 489     return $rows;
 490 }
 491
 492 sub relation_exists
 493 {
 494     my ($self, $relname, $fields)=@_;
 495     my $sql="SELECT count(1) FROM $relname WHERE ";
 496     my @exprs=();
 497     my @vals=();
 498     for my $field (keys %$fields)
 499     {
 500         push(@exprs,$field);
 501         push(@vals,$fields->{$field});
 502     }
 503     $sql .= join(' AND ', map { "$_=?"; } @exprs);
 504     my ($ret)=$self->cmd_onerow($sql, @vals);
 505     return $ret;
 506 }
 507
 508 sub ok
 509 {
 510     my($self, $thing)=@_;
 511     return(defined($thing) && length($thing) && $thing =~ /\S+/);
 512 }
 513
 514 sub cmd
 515 {
 516     my ($self, @args)=@_;
 517     # don't care about retcode
 518     $self->cmd_sth(@args);
 519 }
 520
 521 sub cmd_onerow
 522 {
 523     my ($self, @args)=@_;
 524     my $sth=$self->cmd_sth(@args);
 525     return($sth->fetchrow_array());
 526 }
 527
 528 sub cmd_rows
 529 {
 530     my ($self, @args)=@_;
 531     my $sth=$self->cmd_sth(@args);
 532     return $sth->fetchall_arrayref();
 533 }
 534
 535 sub cmd_id
 536 {
 537     my ($self, @args)=@_;
 538     $self->cmd_sth(@args);
 539     return($self->last_insert_id());
 540 }
 541
 542 sub last_insert_id
 543 {
 544     my $self=shift;
 545     if($self->{postgres})
 546     {
 547         return $self->{dbh}->last_insert_id(undef, undef, undef, undef,
 548                                             { sequence => "seq" });
 549     }
 550     else
 551     {
 552         return $self->{dbh}->last_insert_id("","","","");
 553     }
 554 }
 555
 556 __DATA__
 557
 558 CREATE TABLE id3fs (
 559     schema_version INTEGER,
 560     last_update
 561 );
 562
 563 CREATE TABLE files (
 564     id INTEGER PRIMARY KEY,
 565     artists_id,
 566     albums_id,
 567     paths_id,
 568     name text
 569 );
 570
 571 CREATE TABLE paths (
 572     id INTEGER PRIMARY KEY,
 573     name text
 574 );
 575
 576 CREATE TABLE artists (
 577     id INTEGER PRIMARY KEY,
 578     name text
 579 );
 580
 581 CREATE TABLE albums (
 582     id INTEGER PRIMARY KEY,
 583     name text
 584 );
 585
 586 CREATE TABLE tags (
 587     id INTEGER PRIMARY KEY,
 588     name text
 589 );
 590
 591 CREATE TABLE tagvals (
 592     id INTEGER PRIMARY KEY,
 593     name text
 594 );
 595
 596 CREATE TABLE files_x_tags (
 597     files_id INTEGER,
 598     tags_id INTEGER
 599 );
 600
 601 CREATE TABLE tags_x_tagvals (
 602     tags_id INTEGER,
 603     tagvals_id INTEGER
 604 );