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, $tagid)=@_;
 173     my $sql=("SELECT DISTINCT tagvals.name FROM tagvals\n" .
 174              "INNER JOIN tags_x_tagvals ON tagvals.id=tags_x_tagvals.tagvals_id\n" .
 175              "WHERE tags_x_tagvals.tags_id=?");
 176     my $tags=$self->cmd_rows($sql, $tagid);
 177     my @tags=map { $_->[0]; } @$tags;
 178     # FIXME: handle when indexing, not here
 179     @tags=map { s/[^[:print:]]//g; $_; } @tags;
 180     @tags=map { length($_) ? $_ : "NOVALUE"; } @tags;
 181     return @tags;
 182 }
 183
 184 sub artists
 185 {
 186     my($self, @constraints)=@_;
 187     if(!@constraints) # /ALL
 188     {
 189         my $sql="SELECT DISTINCT name FROM artists;";
 190         my $tags=$self->cmd_rows($sql);
 191         return(map { $_->[0]; } @$tags);
 192     }
 193     my @ids=();
 194     my $main_sql_start=("SELECT artists.name\n" .
 195                         "\tFROM (SELECT files_id FROM tags\n" .
 196                         "\t\tINNER JOIN files_x_tags ON tags.id=files_x_tags.tags_id\n" .
 197                         "\t\tWHERE tags.id in\n\t\t\t(");
 198     my $main_sql_end=(")\n\t\t) AS subselect\n" .
 199                       "\tINNER JOIN files ON subselect.files_id=files.id\n" .
 200                       "\tINNER JOIN artists ON files.artists_id=artists.id\n" .
 201                       "\n\tGROUP BY artists.name;");
 202     while(my $constraint=shift @constraints)
 203     {
 204         print "CONSTRAINT: $constraint->{name}\n";
 205         my $cid=$constraint->{id};
 206         push(@ids, $cid);
 207     }
 208     @ids = map( { "\"$_\""; } @ids) unless($self->{postgres});
 209     my $tagstr=join(", ", @ids);
 210     my $sql = ($main_sql_start . $tagstr .
 211                $main_sql_end);
 212     print "SQL: $sql\n";
 213     my $result=$self->cmd_rows($sql);
 214     my @tagnames=map { $_->[0]; } @$result;
 215     print "ARTISTS: ", join(', ', @tagnames), "\n";
 216     return(@tagnames);
 217 }
 218
 219 sub albums
 220 {
 221     my($self, @constraints)=@_;
 222     my @ids=();
 223     # FIXME: rework PathElements
 224     if(ref($constraints[$#constraints]) eq "ID3FS::PathElement::Artist")
 225     {
 226         return $self->artist_albums($constraints[$#constraints]->{id});
 227     }
 228     my $main_sql_start=("SELECT albums.name\n" .
 229                         "\tFROM (SELECT files_id FROM tags\n" .
 230                         "\t\tINNER JOIN files_x_tags ON tags.id=files_x_tags.tags_id\n" .
 231                         "\t\tWHERE tags.id in\n\t\t\t(");
 232     my $main_sql_end=(")\n\t\t) AS subselect\n" .
 233                       "\tINNER JOIN files ON subselect.files_id=files.id\n" .
 234                       "\tINNER JOIN albums ON files.albums_id=albums.id\n" .
 235                       "\n\tGROUP BY albums.name;");
 236     while(my $constraint=shift @constraints)
 237     {
 238         print "CONSTRAINT: $constraint->{name}\n";
 239         my $cid=$constraint->{id};
 240         push(@ids, $cid);
 241     }
 242     @ids = map( { "\"$_\""; } @ids) unless($self->{postgres});
 243     my $str=join(", ", @ids);
 244     my $sql = ($main_sql_start . $str .
 245                $main_sql_end);
 246     print "SQL: $sql\n";
 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         print "CONSTRAINT: $constraint->{name}\n";
 331         my $cid=$constraint->{id};
 332         push(@ids, $cid);
 333     }
 334     @ids = map( { "\"$_\""; } @ids) unless($self->{postgres});
 335     my $str=join(", ", @ids);
 336     my $sql = ($main_sql_start . $str .
 337                $main_sql_end);
 338     print "SQL: $sql\n";
 339     my $result=$self->cmd_rows($sql);
 340     my @names=map { $_->[0]; } @$result;
 341     @names = map { s/.*\///; $_; } @names;
 342     print "TRACKS: ", join(', ', @names), "\n";
 343     return(@names);
 344 }
 345
 346 sub filename
 347 {
 348     my($self, @constraints)=@_;
 349     if(ref($constraints[$#constraints]) eq "ID3FS::PathElement::File")
 350     {
 351         my $id=$constraints[$#constraints]->{id};
 352         my $sql=("SELECT paths.name, files.name FROM files\n" .
 353                  "INNER JOIN paths ON files.paths_id=paths.id\n" .
 354                  "WHERE files.id=?\n" .
 355                  "GROUP BY paths.name, files.name");
 356         print "FILENAME SQL: $sql\n";
 357         my ($path, $name)=$self->cmd_onerow($sql, $id);
 358         return($self->{absbase} . "/$path/$name");
 359     }
 360     die("DB::filename: unhandled case\n"); #FIXME
 361 }
 362
 363 sub id
 364 {
 365     my($self, $type, $val)=@_;
 366     print "ID: $type $val\n";
 367     my $sql="SELECT id FROM $type WHERE name=?";
 368     my ($id)=$self->cmd_onerow($sql, $val);
 369     return($id);
 370 }
 371
 372 sub add
 373 {
 374     my($self,$path)=@_;
 375     my $relpath=$path;
 376     $relpath =~ s/^\Q$self->{base}\E\/?//;
 377     my($filepart,$pathpart);
 378     if($path !~ /\//)
 379     {
 380         $pathpart='';
 381         $filepart=$relpath;
 382     }
 383     else
 384     {
 385         ($pathpart, $filepart) = ($relpath =~ /(.*)\/(.*)/);
 386     }
 387     my $file=ID3FS::AudioFile->new($path);
 388     return unless(defined($file));
 389     my $artist=$file->artist();
 390     my $album=$file->album();
 391     my $v1genre=$file->v1genre();
 392     my $year=$file->year();
 393     my $audiotype=$file->audiotype();
 394     my $tags=$file->tags();
 395     my $haspic=$file->haspic();
 396
 397     $artist=undef unless($self->ok($artist));
 398     my $artist_id=$self->add_to_table("artists",  $artist);
 399     my $path_id=$self->add_to_table("paths", $pathpart);
 400     $album=undef unless($self->ok($album));
 401     my $albums_id=$self->add_to_table("albums", $album);
 402     my $file_id=$self->add_to_table("files", $filepart,
 403                                     { "artists_id" => $artist_id,
 404                                       "albums_id"  => $albums_id,
 405                                       "paths_id"   => $path_id });
 406     for my $tag (keys %$tags)
 407     {
 408         $self->add_tag($file_id, $tag, $tags->{$tag});
 409     }
 410
 411     if($self->ok($year))
 412     {
 413         $self->add_tag($file_id, "year", $year);
 414         if($year=~/^(\d\d\d)\d$/)
 415         {
 416             $self->add_tag($file_id, "decade", "${1}0s");
 417         }
 418     }
 419
 420     if($self->ok($v1genre))
 421     {
 422         $self->add_tag($file_id, "v1genre", $v1genre);
 423     }
 424
 425     if($haspic)
 426     {
 427         $self->add_tag($file_id, "haspic", undef);
 428     }
 429 }
 430
 431 sub add_tag
 432 {
 433     my($self, $file_id, $tag, $val)=@_;
 434     my $tag_id=$self->add_to_table("tags",  $tag);
 435     $self->add_relation("files_x_tags",
 436                         { "files_id" => $file_id,
 437                           "tags_id"  => $tag_id });
 438     if(defined($val))
 439     {
 440         my $val_id=$self->add_to_table("tagvals", $val);
 441         $self->add_relation("tags_x_tagvals",
 442                             { "tags_id"     => $tag_id,
 443                               "tagvals_id"  => $val_id });
 444     }
 445 }
 446
 447 sub add_to_table
 448 {
 449     my($self, $table, $name, $extradata)=@_;
 450     my $id=$self->lookup_id($table, $name);
 451     unless(defined($id))
 452     {
 453         my $sql="INSERT INTO $table (";
 454         $sql .= "id, " if($self->{postgres});
 455         my @fields=qw(name);
 456         if(defined($extradata))
 457         {
 458             push(@fields, sort keys(%$extradata));
 459         }
 460         $sql .= join(", ", @fields);
 461         $sql .=") VALUES (";
 462         $sql .=") nextval('seq'), " if($self->{postgres});
 463         $sql .= join(", ", map { "?"; } @fields);
 464         $sql .= ");";
 465         $id=$self->cmd_id($sql, $name, map { $extradata->{$_} || ""; } sort keys %$extradata);
 466     }
 467     return $id;
 468 }
 469
 470 sub add_relation
 471 {
 472     my ($self, $relname, $fields)=@_;
 473     return if($self->relation_exists($relname, $fields));
 474     my $sql="INSERT INTO $relname (";
 475     $sql .= join(", ", sort keys(%$fields));
 476     $sql .= ") VALUES (";
 477     $sql .= join(", ", map { "?"; } sort keys(%$fields));
 478     $sql .= ");";
 479     $self->cmd($sql, map { $fields->{$_}; } sort keys(%$fields));
 480 }
 481
 482 sub lookup_id
 483 {
 484     my($self, $table, $name)=@_;
 485     my($id)=$self->cmd_onerow("SELECT id FROM $table where name=?", $name);
 486     return $id;
 487 }
 488
 489 sub tag_has_values
 490 {
 491     my($self, $id)=@_;
 492     my $sql=("SELECT COUNT(*) FROM tags\n\t" .
 493              "INNER JOIN tags_x_tagvals ON tags.id=tags_x_tagvals.tags_id\n\t" .
 494              "INNER JOIN tagvals ON tagvals.id=tags_x_tagvals.tagvals_id\n\t" .
 495              "WHERE tags.id=?\n");
 496     my ($rows)=$self->cmd_onerow($sql, $id);
 497     return $rows;
 498 }
 499
 500 sub relation_exists
 501 {
 502     my ($self, $relname, $fields)=@_;
 503     my $sql="SELECT count(1) FROM $relname WHERE ";
 504     my @exprs=();
 505     my @vals=();
 506     for my $field (keys %$fields)
 507     {
 508         push(@exprs,$field);
 509         push(@vals,$fields->{$field});
 510     }
 511     $sql .= join(' AND ', map { "$_=?"; } @exprs);
 512     my ($ret)=$self->cmd_onerow($sql, @vals);
 513     return $ret;
 514 }
 515
 516 sub ok
 517 {
 518     my($self, $thing)=@_;
 519     return(defined($thing) && length($thing) && $thing =~ /\S+/);
 520 }
 521
 522 sub cmd
 523 {
 524     my ($self, @args)=@_;
 525     # don't care about retcode
 526     $self->cmd_sth(@args);
 527 }
 528
 529 sub cmd_onerow
 530 {
 531     my ($self, @args)=@_;
 532     my $sth=$self->cmd_sth(@args);
 533     return($sth->fetchrow_array());
 534 }
 535
 536 sub cmd_rows
 537 {
 538     my ($self, @args)=@_;
 539     my $sth=$self->cmd_sth(@args);
 540     return $sth->fetchall_arrayref();
 541 }
 542
 543 sub cmd_id
 544 {
 545     my ($self, @args)=@_;
 546     $self->cmd_sth(@args);
 547     return($self->last_insert_id());
 548 }
 549
 550 sub last_insert_id
 551 {
 552     my $self=shift;
 553     if($self->{postgres})
 554     {
 555         return $self->{dbh}->last_insert_id(undef, undef, undef, undef,
 556                                             { sequence => "seq" });
 557     }
 558     else
 559     {
 560         return $self->{dbh}->last_insert_id("","","","");
 561     }
 562 }
 563
 564 __DATA__
 565
 566 CREATE TABLE id3fs (
 567     schema_version INTEGER,
 568     last_update
 569 );
 570
 571 CREATE TABLE files (
 572     id INTEGER PRIMARY KEY,
 573     artists_id,
 574     albums_id,
 575     paths_id,
 576     name text
 577 );
 578
 579 CREATE TABLE paths (
 580     id INTEGER PRIMARY KEY,
 581     name text
 582 );
 583
 584 CREATE TABLE artists (
 585     id INTEGER PRIMARY KEY,
 586     name text
 587 );
 588
 589 CREATE TABLE albums (
 590     id INTEGER PRIMARY KEY,
 591     name text
 592 );
 593
 594 CREATE TABLE tags (
 595     id INTEGER PRIMARY KEY,
 596     name text
 597 );
 598
 599 CREATE TABLE tagvals (
 600     id INTEGER PRIMARY KEY,
 601     name text
 602 );
 603
 604 CREATE TABLE files_x_tags (
 605     files_id INTEGER,
 606     tags_id INTEGER
 607 );
 608
 609 CREATE TABLE tags_x_tagvals (
 610     tags_id INTEGER,
 611     tagvals_id INTEGER
 612 );