lib/ID3FS/DB.pm

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