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     my($dir, $init, $me)=@_;
  20     $self->{base}=$dir;
  21     $self->{absbase}=Cwd::abs_path($dir);
  22     $self->{dbpath}="$dir/$dbfile";
  23     $self->{me}=$me;
  24
  25     my $exists=-f $self->{dbpath};
  26
  27     $self->{postgres}=0;
  28
  29     unless($self->{postgres})
  30     {
  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);
  33     }
  34
  35     my $connectstr="dbi:SQLite:dbname=$self->{dbpath}";
  36     my ($user, $pass)=("", "");
  37     if($self->{postgres})
  38     {
  39         $connectstr="dbi:Pg:dbname=id3fs";
  40         $user="ianb";
  41         $pass="foo";
  42     }
  43     $self->{dbh}=DBI->connect($connectstr, $user, $pass,
  44                               { AutoCommit=>1 } );
  45     unless(defined($self->{dbh}))
  46     {
  47         die("$me: DB Error: " . $DBI::errstr . "\n");
  48     }
  49
  50     if($init)
  51     {
  52         $self->create();
  53     }
  54     else
  55     {
  56         $self->checkschema();
  57     }
  58
  59     return $self;
  60 }
  61
  62 sub create
  63 {
  64     my($self,$name)=@_;
  65     my @schema=split(/\n\n/,join("", <DATA>));
  66     close(DATA);
  67     for my $cmd (@schema)
  68     {
  69         $self->{dbh}->do($cmd);
  70     }
  71     if($self->{postgres})
  72     {
  73         $self->cmd("CREATE SEQUENCE seq");
  74     }
  75     else
  76     {
  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)
  84         {
  85             $self->{dbh}->do("CREATE INDEX $index ON " . $indexes{$index});
  86         }
  87     }
  88     $self->cmd("INSERT INTO id3fs (schema_version) VALUES (?)", $SCHEMA_VERSION);
  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 cmd_sth
 104 {
 105     my($self, $sql, @params)=@_;
 106     my $sth=$self->{dbh}->prepare($sql);
 107     my $idx=1;
 108     for my $param (@params)
 109     {
 110         $param="" unless(defined($param));
 111         $sth->bind_param($idx++, $param);
 112     }
 113     $sth->execute();
 114     return $sth;
 115 }
 116
 117 sub tags
 118 {
 119     my($self, @constraints)=@_;
 120     if(!@constraints) # /
 121     {
 122         my $sql="SELECT DISTINCT name FROM tags;";
 123         my $tags=$self->cmd_rows($sql);
 124         return(map { $_->[0]; } @$tags);
 125     }
 126     my @ids=();
 127
 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)
 138     {
 139         print "CONSTRAINT: $constraint->{name}\n";
 140         my $cid=$constraint->{id};
 141         push(@ids, $cid);
 142     }
 143     @ids = map( { "\"$_\""; } @ids) unless($self->{postgres});
 144     my $tagstr=join(", ", @ids);
 145     my $sql = ($main_sql_start . $tagstr .
 146                $main_sql_mid   . $tagstr .
 147                $main_sql_end);
 148     print "SQL: $sql\n";
 149     my $result=$self->cmd_rows($sql);
 150     my @tagnames=map { $_->[0]; } @$result;
 151     print "SUBNAMES: ", join(', ', @tagnames), "\n";
 152     return(@tagnames);
 153 }
 154
 155 sub tag_values
 156 {
 157     my($self, $tag)=@_;
 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);
 164 }
 165
 166 sub artists
 167 {
 168     my($self, @constraints)=@_;
 169     if(!@constraints) # /ALL
 170     {
 171         my $sql="SELECT DISTINCT name FROM artists;";
 172         my $tags=$self->cmd_rows($sql);
 173         return(map { $_->[0]; } @$tags);
 174     }
 175     my @ids=();
 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)
 185     {
 186         print "CONSTRAINT: $constraint->{name}\n";
 187         my $cid=$constraint->{id};
 188         push(@ids, $cid);
 189     }
 190     @ids = map( { "\"$_\""; } @ids) unless($self->{postgres});
 191     my $tagstr=join(", ", @ids);
 192     my $sql = ($main_sql_start . $tagstr .
 193                $main_sql_end);
 194     print "SQL: $sql\n";
 195     my $result=$self->cmd_rows($sql);
 196     my @tagnames=map { $_->[0]; } @$result;
 197     print "ARTISTS: ", join(', ', @tagnames), "\n";
 198     return(@tagnames);
 199 }
 200
 201 sub albums
 202 {
 203     my($self, @constraints)=@_;
 204     my @ids=();
 205     # FIXME: rework PathElements
 206     if(ref($constraints[$#constraints]) eq "ID3FS::PathElement::Artist")
 207     {
 208         return $self->artist_albums($constraints[$#constraints]->{id});
 209     }
 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)
 219     {
 220         print "CONSTRAINT: $constraint->{name}\n";
 221         my $cid=$constraint->{id};
 222         push(@ids, $cid);
 223     }
 224     @ids = map( { "\"$_\""; } @ids) unless($self->{postgres});
 225     my $str=join(", ", @ids);
 226     my $sql = ($main_sql_start . $str .
 227                $main_sql_end);
 228     print "SQL: $sql\n";
 229     my $result=$self->cmd_rows($sql);
 230     my @names=map { $_->[0]; } @$result;
 231     print "ALBUMS: ", join(', ', @names), "\n";
 232     return(@names);
 233 }
 234
 235 sub artist_albums
 236 {
 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";
 247     return(@albums);
 248 }
 249
 250 sub artist_tracks
 251 {
 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";
 263     return(@names);
 264 }
 265
 266 sub album_tracks
 267 {
 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";
 279     return(@names);
 280 }
 281
 282 sub tracks
 283 {
 284     my($self, @constraints)=@_;
 285     # FIXME: rework PathElements
 286     if(ref($constraints[$#constraints]) eq "ID3FS::PathElement::Artist")
 287     {
 288         return $self->artist_tracks($constraints[$#constraints]->{id});
 289     }
 290     elsif(ref($constraints[$#constraints]) eq "ID3FS::PathElement::Album")
 291     {
 292         return $self->album_tracks($constraints[$#constraints]->{id});
 293     }
 294
 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;");
 302     my @ids;
 303     while(my $constraint=shift @constraints)
 304     {
 305         print "CONSTRAINT: $constraint->{name}\n";
 306         my $cid=$constraint->{id};
 307         push(@ids, $cid);
 308     }
 309     @ids = map( { "\"$_\""; } @ids) unless($self->{postgres});
 310     my $str=join(", ", @ids);
 311     my $sql = ($main_sql_start . $str .
 312                $main_sql_end);
 313     print "SQL: $sql\n";
 314     my $result=$self->cmd_rows($sql);
 315     my @names=map { $_->[0]; } @$result;
 316     @names = map { s/.*\///; $_; } @names;
 317     print "TRACKS: ", join(', ', @names), "\n";
 318     return(@names);
 319 }
 320
 321 sub filename
 322 {
 323     my($self, @constraints)=@_;
 324     if(ref($constraints[$#constraints]) eq "ID3FS::PathElement::File")
 325     {
 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");
 335     }
 336     die("DB::filename: unhandled case\n"); #FIXME
 337 }
 338
 339 sub id
 340 {
 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);
 345     return($id);
 346 }
 347
 348 sub add
 349 {
 350     my($self,$path)=@_;
 351     my $relpath=$path;
 352     $relpath =~ s/^\Q$self->{base}\E\/?//;
 353     my($filepart,$pathpart);
 354     if($path !~ /\//)
 355     {
 356         $pathpart='';
 357         $filepart=$relpath;
 358     }
 359     else
 360     {
 361         ($pathpart, $filepart) = ($relpath =~ /(.*)\/(.*)/);
 362     }
 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();
 372
 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});
 378
 379     for my $tag (keys %$tags)
 380     {
 381         $self->add_tag($file_id, $tag, $tags->{$tag});
 382     }
 383
 384     my $artist_id;
 385     if($self->ok($artist))
 386     {
 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 });
 391     }
 392
 393     if($self->ok($album))
 394     {
 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))
 400         {
 401             $self->add_relation("artists_x_albums",
 402                                 { "artists_id" => $artist_id,
 403                                   "albums_id" => $albums_id});
 404         }
 405     }
 406
 407     if($self->ok($year))
 408     {
 409         $self->add_tag($file_id, "year", $year);
 410         if($year=~/^(\d\d\d)\d$/)
 411         {
 412             $self->add_tag($file_id, "decade", "${1}0s");
 413         }
 414     }
 415
 416     if($self->ok($v1genre))
 417     {
 418         $self->add_tag($file_id, "v1genre", $v1genre);
 419     }
 420
 421     if($haspic)
 422     {
 423         $self->add_tag($file_id, "haspic", undef);
 424     }
 425 }
 426
 427 sub add_tag
 428 {
 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 });
 434     if(defined($val))
 435     {
 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 });
 440     }
 441 }
 442
 443 sub add_to_table
 444 {
 445     my($self, $table, $name, $extradata)=@_;
 446     my $id=$self->lookup_id($table, $name);
 447     unless(defined($id))
 448     {
 449         my $sql="INSERT INTO $table (";
 450         $sql .= "id, " if($self->{postgres});
 451         my @fields=qw(name);
 452         if(defined($extradata))
 453         {
 454             push(@fields, sort keys(%$extradata));
 455         }
 456         $sql .= join(", ", @fields);
 457         $sql .=") VALUES (";
 458         $sql .=") nextval('seq'), " if($self->{postgres});
 459         $sql .= join(", ", map { "?"; } @fields);
 460         $sql .= ");";
 461         $id=$self->cmd_id($sql, $name, map { $extradata->{$_} || ""; } sort keys %$extradata);
 462     }
 463     return $id;
 464 }
 465
 466 sub add_relation
 467 {
 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));
 474     $sql .= ");";
 475     $self->cmd($sql, map { $fields->{$_}; } sort keys(%$fields));
 476 }
 477
 478 sub lookup_id
 479 {
 480     my($self, $table, $name)=@_;
 481     my($id)=$self->cmd_onerow("SELECT id FROM $table where name=?", $name);
 482     return $id;
 483 }
 484
 485 sub relation_exists
 486 {
 487     my ($self, $relname, $fields)=@_;
 488     my $sql="SELECT count(1) FROM $relname WHERE ";
 489     my @exprs=();
 490     my @vals=();
 491     for my $field (keys %$fields)
 492     {
 493         push(@exprs,$field);
 494         push(@vals,$fields->{$field});
 495     }
 496     $sql .= join(' AND ', map { "$_=?"; } @exprs);
 497     my ($ret)=$self->cmd_onerow($sql, @vals);
 498     return $ret;
 499 }
 500
 501 sub ok
 502 {
 503     my($self, $thing)=@_;
 504     return(defined($thing) && length($thing) && $thing =~ /\S+/);
 505 }
 506
 507 sub cmd
 508 {
 509     my ($self, @args)=@_;
 510     # don't care about retcode
 511     $self->cmd_sth(@args);
 512 }
 513
 514 sub cmd_onerow
 515 {
 516     my ($self, @args)=@_;
 517     my $sth=$self->cmd_sth(@args);
 518     return($sth->fetchrow_array());
 519 }
 520
 521 sub cmd_rows
 522 {
 523     my ($self, @args)=@_;
 524     my $sth=$self->cmd_sth(@args);
 525     return $sth->fetchall_arrayref();
 526 }
 527
 528 sub cmd_id
 529 {
 530     my ($self, @args)=@_;
 531     $self->cmd_sth(@args);
 532     return($self->last_insert_id());
 533 }
 534
 535 sub last_insert_id
 536 {
 537     my $self=shift;
 538     if($self->{postgres})
 539     {
 540         return $self->{dbh}->last_insert_id(undef, undef, undef, undef,
 541                                             { sequence => "seq" });
 542     }
 543     else
 544     {
 545         return $self->{dbh}->last_insert_id("","","","");
 546     }
 547 }
 548
 549 __DATA__
 550
 551 CREATE TABLE id3fs (
 552     schema_version INTEGER
 553 );
 554
 555 CREATE TABLE files (
 556     id INTEGER PRIMARY KEY,
 557     name text
 558 );
 559
 560 CREATE TABLE paths (
 561     id INTEGER PRIMARY KEY,
 562     name text
 563 );
 564
 565 CREATE TABLE artists (
 566     id INTEGER PRIMARY KEY,
 567     name text
 568 );
 569
 570 CREATE TABLE albums (
 571     id INTEGER PRIMARY KEY,
 572     name text
 573 );
 574
 575 CREATE TABLE tags (
 576     id INTEGER PRIMARY KEY,
 577     name text
 578 );
 579
 580 CREATE TABLE tagvals (
 581     id INTEGER PRIMARY KEY,
 582     name text
 583 );
 584
 585 CREATE TABLE paths_x_files (
 586     paths_id INTEGER,
 587     files_id INTEGER
 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 );
 599
 600 CREATE TABLE files_x_artists (
 601     files_id INTEGER,
 602     artists_id INTEGER
 603 );
 604
 605 CREATE TABLE files_x_albums (
 606     files_id INTEGER,
 607     albums_id INTEGER
 608 );
 609
 610 CREATE TABLE artists_x_albums (
 611     artists_id INTEGER,
 612     albums_id INTEGER
 613 );