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         print "CONSTRAINT: $constraint->{name}\n";
 155         my $cid=$constraint->{id};
 156         push(@ids, $cid);
 157     }
 158     @ids = map( { "\"$_\""; } @ids) unless($self->{postgres});
 159     my $tagstr=join(", ", @ids);
 160     my $sql = ($main_sql_start . $tagstr .
 161                $main_sql_mid   . $tagstr .
 162                $main_sql_end);
 163     print "SQL: $sql\n";
 164     my $result=$self->cmd_rows($sql);
 165     my @tagnames=map { $_->[0]; } @$result;
 166     print "SUBNAMES: ", join(', ', @tagnames), "\n";
 167     return(@tagnames);
 168 }
 169
 170 sub tag_values
 171 {
 172     my($self, $tag)=@_;
 173     my $sql=("SELECT DISTINCT tagvals.name FROM tags\n" .
 174              "INNER JOIN tags_x_tagvals ON tags.id=tags_x_tagvals.tags_id\n" .
 175              "INNER JOIN tagvals ON tagvals.id=tags_x_tagvals.tagvals_id\n" .
 176              "WHERE tags.name=?");
 177     my $tags=$self->cmd_rows($sql, $tag);
 178     return(map { $_->[0]; } @$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         print "CONSTRAINT: $constraint->{name}\n";
 202         my $cid=$constraint->{id};
 203         push(@ids, $cid);
 204     }
 205     @ids = map( { "\"$_\""; } @ids) unless($self->{postgres});
 206     my $tagstr=join(", ", @ids);
 207     my $sql = ($main_sql_start . $tagstr .
 208                $main_sql_end);
 209     print "SQL: $sql\n";
 210     my $result=$self->cmd_rows($sql);
 211     my @tagnames=map { $_->[0]; } @$result;
 212     print "ARTISTS: ", join(', ', @tagnames), "\n";
 213     return(@tagnames);
 214 }
 215
 216 sub albums
 217 {
 218     my($self, @constraints)=@_;
 219     my @ids=();
 220     # FIXME: rework PathElements
 221     if(ref($constraints[$#constraints]) eq "ID3FS::PathElement::Artist")
 222     {
 223         return $self->artist_albums($constraints[$#constraints]->{id});
 224     }
 225     my $main_sql_start=("SELECT albums.name\n" .
 226                         "\tFROM (SELECT files_id FROM tags\n" .
 227                         "\t\tINNER JOIN files_x_tags ON tags.id=files_x_tags.tags_id\n" .
 228                         "\t\tWHERE tags.id in\n\t\t\t(");
 229     my $main_sql_end=(")\n\t\t) AS subselect\n" .
 230                       "\tINNER JOIN files ON subselect.files_id=files.id\n" .
 231                       "\tINNER JOIN albums ON files.albums_id=albums.id\n" .
 232                       "\n\tGROUP BY albums.name;");
 233     while(my $constraint=shift @constraints)
 234     {
 235         print "CONSTRAINT: $constraint->{name}\n";
 236         my $cid=$constraint->{id};
 237         push(@ids, $cid);
 238     }
 239     @ids = map( { "\"$_\""; } @ids) unless($self->{postgres});
 240     my $str=join(", ", @ids);
 241     my $sql = ($main_sql_start . $str .
 242                $main_sql_end);
 243     print "SQL: $sql\n";
 244     my $result=$self->cmd_rows($sql);
 245     my @names=map { $_->[0]; } @$result;
 246     print "ALBUMS: ", join(', ', @names), "\n";
 247     return(@names);
 248 }
 249
 250 sub artist_albums
 251 {
 252     my($self, $artist_id)=@_;
 253     my $sql=("SELECT albums.name FROM files\n\t" .
 254              "INNER JOIN albums ON albums.id=files.albums_id\n\t" .
 255              "INNER JOIN artists ON artists.id=files.artists_id\n\t" .
 256              "WHERE artists.id=? and albums.name <> ''\n\t" .
 257              "GROUP BY albums.name\n");
 258     print "ARTIST_ALBUMS SQL: $sql\n";
 259     my $result=$self->cmd_rows($sql, $artist_id);
 260     my @albums=map { $_->[0]; } @$result;
 261     print "ALBUMS: ", join(', ', @albums), "\n";
 262     return(@albums);
 263 }
 264
 265 sub artist_tracks
 266 {
 267     my($self, $artist_id)=@_;
 268     my $sql=("SELECT files.name FROM files\n\t" .
 269              "INNER JOIN artists ON artists.id=files.artists_id\n\t" .
 270              "INNER JOIN albums  ON albums.id=files.albums_id\n\t" .
 271              "WHERE artists.id=? AND albums.name=''\n\t" .
 272              "GROUP BY files.name\n");
 273     print "ARTIST_TRACKS SQL: $sql\n";
 274     my $result=$self->cmd_rows($sql, $artist_id);
 275     my @names=map { $_->[0]; } @$result;
 276     @names = map { s/.*\///; $_; } @names;
 277     print "ARTISTTRACKS: ", join(', ', @names), "\n";
 278     return(@names);
 279 }
 280
 281 sub album_tracks
 282 {
 283     my($self, $artist_id, $album_id)=@_;
 284     my $sql=("SELECT files.name FROM files\n\t" .
 285              "INNER JOIN albums  ON albums.id=files.albums_id\n\t" .
 286              "INNER JOIN artists ON artists.id=files.artists_id\n\t" .
 287              "WHERE artists.id=? AND albums.id=?\n\t" .
 288              "GROUP BY files.name\n");
 289     print "ALBUM_TRACKS SQL($artist_id, $album_id): $sql\n";
 290     my $result=$self->cmd_rows($sql, $artist_id, $album_id);
 291     my @names=map { $_->[0]; } @$result;
 292     @names = map { s/.*\///; $_;} @names;
 293     print "TRACKS: ", join(', ', @names), "\n";
 294     return(@names);
 295 }
 296
 297 sub tracks
 298 {
 299     my($self, @constraints)=@_;
 300     # FIXME: rework PathElements
 301     if(ref($constraints[$#constraints]) eq "ID3FS::PathElement::Artist")
 302     {
 303         return $self->artist_tracks($constraints[$#constraints]->{id});
 304     }
 305     elsif(ref($constraints[$#constraints]) eq "ID3FS::PathElement::Album")
 306     {
 307         my $artist_id=0;
 308         my $artist=$constraints[($#constraints)-1];
 309         if(defined($artist) && (ref($artist) eq "ID3FS::PathElement::Artist"))
 310         {
 311             # should always happen
 312             $artist_id=$artist->{id};
 313         }
 314         return $self->album_tracks($artist_id, $constraints[$#constraints]->{id});
 315     }
 316
 317     my $main_sql_start=("SELECT files.name\n" .
 318                         "\tFROM (SELECT files_id FROM tags\n" .
 319                         "\t\tINNER JOIN files_x_tags ON tags.id=files_x_tags.tags_id\n" .
 320                         "\t\tWHERE tags.id in\n\t\t\t(");
 321     my $main_sql_end=(")\n\t\t) AS subselect\n" .
 322                       "\tINNER JOIN files ON files.id=subselect.files_id" .
 323                       "\tGROUP BY files.name;");
 324     my @ids;
 325     while(my $constraint=shift @constraints)
 326     {
 327         print "CONSTRAINT: $constraint->{name}\n";
 328         my $cid=$constraint->{id};
 329         push(@ids, $cid);
 330     }
 331     @ids = map( { "\"$_\""; } @ids) unless($self->{postgres});
 332     my $str=join(", ", @ids);
 333     my $sql = ($main_sql_start . $str .
 334                $main_sql_end);
 335     print "SQL: $sql\n";
 336     my $result=$self->cmd_rows($sql);
 337     my @names=map { $_->[0]; } @$result;
 338     @names = map { s/.*\///; $_; } @names;
 339     print "TRACKS: ", join(', ', @names), "\n";
 340     return(@names);
 341 }
 342
 343 sub filename
 344 {
 345     my($self, @constraints)=@_;
 346     if(ref($constraints[$#constraints]) eq "ID3FS::PathElement::File")
 347     {
 348         my $id=$constraints[$#constraints]->{id};
 349         my $sql=("SELECT paths.name, files.name FROM files\n" .
 350                  "INNER JOIN paths ON files.paths_id=paths.id\n" .
 351                  "WHERE files.id=?\n" .
 352                  "GROUP BY paths.name, files.name");
 353         print "FILENAME SQL: $sql\n";
 354         my ($path, $name)=$self->cmd_onerow($sql, $id);
 355         return($self->{absbase} . "/$path/$name");
 356     }
 357     die("DB::filename: unhandled case\n"); #FIXME
 358 }
 359
 360 sub id
 361 {
 362     my($self, $type, $val)=@_;
 363     print "ID: $type $val\n";
 364     my $sql="SELECT id FROM $type WHERE name=?";
 365     my ($id)=$self->cmd_onerow($sql, $val);
 366     return($id);
 367 }
 368
 369 sub add
 370 {
 371     my($self,$path)=@_;
 372     my $relpath=$path;
 373     $relpath =~ s/^\Q$self->{base}\E\/?//;
 374     my($filepart,$pathpart);
 375     if($path !~ /\//)
 376     {
 377         $pathpart='';
 378         $filepart=$relpath;
 379     }
 380     else
 381     {
 382         ($pathpart, $filepart) = ($relpath =~ /(.*)\/(.*)/);
 383     }
 384     my $file=ID3FS::AudioFile->new($path);
 385     return unless(defined($file));
 386     my $artist=$file->artist();
 387     my $album=$file->album();
 388     my $v1genre=$file->v1genre();
 389     my $year=$file->year();
 390     my $audiotype=$file->audiotype();
 391     my $tags=$file->tags();
 392     my $haspic=$file->haspic();
 393
 394     $artist=undef unless($self->ok($artist));
 395     my $artist_id=$self->add_to_table("artists",  $artist);
 396     my $path_id=$self->add_to_table("paths", $pathpart);
 397     $album=undef unless($self->ok($album));
 398     my $albums_id=$self->add_to_table("albums", $album);
 399     my $file_id=$self->add_to_table("files", $filepart,
 400                                     { "artists_id" => $artist_id,
 401                                       "albums_id"  => $albums_id,
 402                                       "paths_id"   => $path_id });
 403     for my $tag (keys %$tags)
 404     {
 405         $self->add_tag($file_id, $tag, $tags->{$tag});
 406     }
 407
 408     if($self->ok($year))
 409     {
 410         $self->add_tag($file_id, "year", $year);
 411         if($year=~/^(\d\d\d)\d$/)
 412         {
 413             $self->add_tag($file_id, "decade", "${1}0s");
 414         }
 415     }
 416
 417     if($self->ok($v1genre))
 418     {
 419         $self->add_tag($file_id, "v1genre", $v1genre);
 420     }
 421
 422     if($haspic)
 423     {
 424         $self->add_tag($file_id, "haspic", undef);
 425     }
 426 }
 427
 428 sub add_tag
 429 {
 430     my($self, $file_id, $tag, $val)=@_;
 431     my $tag_id=$self->add_to_table("tags",  $tag);
 432     $self->add_relation("files_x_tags",
 433                         { "files_id" => $file_id,
 434                           "tags_id"  => $tag_id });
 435     if(defined($val))
 436     {
 437         my $val_id=$self->add_to_table("tagvals", $val);
 438         $self->add_relation("tags_x_tagvals",
 439                             { "tags_id"     => $tag_id,
 440                               "tagvals_id"  => $val_id });
 441     }
 442 }
 443
 444 sub add_to_table
 445 {
 446     my($self, $table, $name, $extradata)=@_;
 447     my $id=$self->lookup_id($table, $name);
 448     unless(defined($id))
 449     {
 450         my $sql="INSERT INTO $table (";
 451         $sql .= "id, " if($self->{postgres});
 452         my @fields=qw(name);
 453         if(defined($extradata))
 454         {
 455             push(@fields, sort keys(%$extradata));
 456         }
 457         $sql .= join(", ", @fields);
 458         $sql .=") VALUES (";
 459         $sql .=") nextval('seq'), " if($self->{postgres});
 460         $sql .= join(", ", map { "?"; } @fields);
 461         $sql .= ");";
 462         $id=$self->cmd_id($sql, $name, map { $extradata->{$_} || ""; } sort keys %$extradata);
 463     }
 464     return $id;
 465 }
 466
 467 sub add_relation
 468 {
 469     my ($self, $relname, $fields)=@_;
 470     return if($self->relation_exists($relname, $fields));
 471     my $sql="INSERT INTO $relname (";
 472     $sql .= join(", ", sort keys(%$fields));
 473     $sql .= ") VALUES (";
 474     $sql .= join(", ", map { "?"; } sort keys(%$fields));
 475     $sql .= ");";
 476     $self->cmd($sql, map { $fields->{$_}; } sort keys(%$fields));
 477 }
 478
 479 sub lookup_id
 480 {
 481     my($self, $table, $name)=@_;
 482     my($id)=$self->cmd_onerow("SELECT id FROM $table where name=?", $name);
 483     return $id;
 484 }
 485
 486 sub relation_exists
 487 {
 488     my ($self, $relname, $fields)=@_;
 489     my $sql="SELECT count(1) FROM $relname WHERE ";
 490     my @exprs=();
 491     my @vals=();
 492     for my $field (keys %$fields)
 493     {
 494         push(@exprs,$field);
 495         push(@vals,$fields->{$field});
 496     }
 497     $sql .= join(' AND ', map { "$_=?"; } @exprs);
 498     my ($ret)=$self->cmd_onerow($sql, @vals);
 499     return $ret;
 500 }
 501
 502 sub ok
 503 {
 504     my($self, $thing)=@_;
 505     return(defined($thing) && length($thing) && $thing =~ /\S+/);
 506 }
 507
 508 sub cmd
 509 {
 510     my ($self, @args)=@_;
 511     # don't care about retcode
 512     $self->cmd_sth(@args);
 513 }
 514
 515 sub cmd_onerow
 516 {
 517     my ($self, @args)=@_;
 518     my $sth=$self->cmd_sth(@args);
 519     return($sth->fetchrow_array());
 520 }
 521
 522 sub cmd_rows
 523 {
 524     my ($self, @args)=@_;
 525     my $sth=$self->cmd_sth(@args);
 526     return $sth->fetchall_arrayref();
 527 }
 528
 529 sub cmd_id
 530 {
 531     my ($self, @args)=@_;
 532     $self->cmd_sth(@args);
 533     return($self->last_insert_id());
 534 }
 535
 536 sub last_insert_id
 537 {
 538     my $self=shift;
 539     if($self->{postgres})
 540     {
 541         return $self->{dbh}->last_insert_id(undef, undef, undef, undef,
 542                                             { sequence => "seq" });
 543     }
 544     else
 545     {
 546         return $self->{dbh}->last_insert_id("","","","");
 547     }
 548 }
 549
 550 __DATA__
 551
 552 CREATE TABLE id3fs (
 553     schema_version INTEGER,
 554     last_update
 555 );
 556
 557 CREATE TABLE files (
 558     id INTEGER PRIMARY KEY,
 559     artists_id,
 560     albums_id,
 561     paths_id,
 562     name text
 563 );
 564
 565 CREATE TABLE paths (
 566     id INTEGER PRIMARY KEY,
 567     name text
 568 );
 569
 570 CREATE TABLE artists (
 571     id INTEGER PRIMARY KEY,
 572     name text
 573 );
 574
 575 CREATE TABLE albums (
 576     id INTEGER PRIMARY KEY,
 577     name text
 578 );
 579
 580 CREATE TABLE tags (
 581     id INTEGER PRIMARY KEY,
 582     name text
 583 );
 584
 585 CREATE TABLE tagvals (
 586     id INTEGER PRIMARY KEY,
 587     name text
 588 );
 589
 590 CREATE TABLE files_x_tags (
 591     files_id INTEGER,
 592     tags_id INTEGER
 593 );
 594
 595 CREATE TABLE tags_x_tagvals (
 596     tags_id INTEGER,
 597     tagvals_id INTEGER
 598 );