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