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         # 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, $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              "WHERE albums.id=?\n\t" . #AND albums.name <> ''\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 ON files.paths_id=paths.id\n" .
 343                  "WHERE files.id=?\n" .
 344                  "GROUP BY paths.name, files.name");
 345         print "FILENAME SQL: $sql\n";
 346         my ($path, $name)=$self->cmd_onerow($sql, $id);
 347         return($self->{absbase} . "/$path/$name");
 348     }
 349     die("DB::filename: unhandled case\n"); #FIXME
 350 }
 351
 352 sub id
 353 {
 354     my($self, $type, $val)=@_;
 355     print "ID: $type $val\n";
 356     my $sql="SELECT id FROM $type WHERE name=?";
 357     my ($id)=$self->cmd_onerow($sql, $val);
 358     return($id);
 359 }
 360
 361 sub add
 362 {
 363     my($self,$path)=@_;
 364     my $relpath=$path;
 365     $relpath =~ s/^\Q$self->{base}\E\/?//;
 366     my($filepart,$pathpart);
 367     if($path !~ /\//)
 368     {
 369         $pathpart='';
 370         $filepart=$relpath;
 371     }
 372     else
 373     {
 374         ($pathpart, $filepart) = ($relpath =~ /(.*)\/(.*)/);
 375     }
 376     my $file=ID3FS::File->new($path);
 377     return unless(defined($file));
 378     my $artist=$file->artist();
 379     my $album=$file->album();
 380     my $v1genre=$file->v1genre();
 381     my $year=$file->year();
 382     my $audiotype=$file->album();
 383     my $tags=$file->tags();
 384     my $haspic=$file->haspic();
 385
 386     $artist=undef unless($self->ok($artist));
 387     my $artist_id=$self->add_to_table("artists",  $artist);
 388     my $path_id=$self->add_to_table("paths", $pathpart);
 389     $album=undef unless($self->ok($album));
 390     my $albums_id=$self->add_to_table("albums", $album);
 391     my $file_id=$self->add_to_table("files", $filepart,
 392                                     { "artists_id" => $artist_id,
 393                                       "albums_id"  => $albums_id,
 394                                       "paths_id"   => $path_id });
 395     for my $tag (keys %$tags)
 396     {
 397         $self->add_tag($file_id, $tag, $tags->{$tag});
 398     }
 399
 400     if($self->ok($year))
 401     {
 402         $self->add_tag($file_id, "year", $year);
 403         if($year=~/^(\d\d\d)\d$/)
 404         {
 405             $self->add_tag($file_id, "decade", "${1}0s");
 406         }
 407     }
 408
 409     if($self->ok($v1genre))
 410     {
 411         $self->add_tag($file_id, "v1genre", $v1genre);
 412     }
 413
 414     if($haspic)
 415     {
 416         $self->add_tag($file_id, "haspic", undef);
 417     }
 418 }
 419
 420 sub add_tag
 421 {
 422     my($self, $file_id, $tag, $val)=@_;
 423     my $tag_id=$self->add_to_table("tags",  $tag);
 424     $self->add_relation("files_x_tags",
 425                         { "files_id" => $file_id,
 426                           "tags_id"  => $tag_id });
 427     if(defined($val))
 428     {
 429         my $val_id=$self->add_to_table("tagvals", $val);
 430         $self->add_relation("tags_x_tagvals",
 431                             { "tags_id"     => $tag_id,
 432                               "tagvals_id"  => $val_id });
 433     }
 434 }
 435
 436 sub add_to_table
 437 {
 438     my($self, $table, $name, $extradata)=@_;
 439     my $id=$self->lookup_id($table, $name);
 440     unless(defined($id))
 441     {
 442         my $sql="INSERT INTO $table (";
 443         $sql .= "id, " if($self->{postgres});
 444         my @fields=qw(name);
 445         if(defined($extradata))
 446         {
 447             push(@fields, sort keys(%$extradata));
 448         }
 449         $sql .= join(", ", @fields);
 450         $sql .=") VALUES (";
 451         $sql .=") nextval('seq'), " if($self->{postgres});
 452         $sql .= join(", ", map { "?"; } @fields);
 453         $sql .= ");";
 454         $id=$self->cmd_id($sql, $name, map { $extradata->{$_} || ""; } sort keys %$extradata);
 455     }
 456     return $id;
 457 }
 458
 459 sub add_relation
 460 {
 461     my ($self, $relname, $fields)=@_;
 462     return if($self->relation_exists($relname, $fields));
 463     my $sql="INSERT INTO $relname (";
 464     $sql .= join(", ", sort keys(%$fields));
 465     $sql .= ") VALUES (";
 466     $sql .= join(", ", map { "?"; } sort keys(%$fields));
 467     $sql .= ");";
 468     $self->cmd($sql, map { $fields->{$_}; } sort keys(%$fields));
 469 }
 470
 471 sub lookup_id
 472 {
 473     my($self, $table, $name)=@_;
 474     my($id)=$self->cmd_onerow("SELECT id FROM $table where name=?", $name);
 475     return $id;
 476 }
 477
 478 sub relation_exists
 479 {
 480     my ($self, $relname, $fields)=@_;
 481     my $sql="SELECT count(1) FROM $relname WHERE ";
 482     my @exprs=();
 483     my @vals=();
 484     for my $field (keys %$fields)
 485     {
 486         push(@exprs,$field);
 487         push(@vals,$fields->{$field});
 488     }
 489     $sql .= join(' AND ', map { "$_=?"; } @exprs);
 490     my ($ret)=$self->cmd_onerow($sql, @vals);
 491     return $ret;
 492 }
 493
 494 sub ok
 495 {
 496     my($self, $thing)=@_;
 497     return(defined($thing) && length($thing) && $thing =~ /\S+/);
 498 }
 499
 500 sub cmd
 501 {
 502     my ($self, @args)=@_;
 503     # don't care about retcode
 504     $self->cmd_sth(@args);
 505 }
 506
 507 sub cmd_onerow
 508 {
 509     my ($self, @args)=@_;
 510     my $sth=$self->cmd_sth(@args);
 511     return($sth->fetchrow_array());
 512 }
 513
 514 sub cmd_rows
 515 {
 516     my ($self, @args)=@_;
 517     my $sth=$self->cmd_sth(@args);
 518     return $sth->fetchall_arrayref();
 519 }
 520
 521 sub cmd_id
 522 {
 523     my ($self, @args)=@_;
 524     $self->cmd_sth(@args);
 525     return($self->last_insert_id());
 526 }
 527
 528 sub last_insert_id
 529 {
 530     my $self=shift;
 531     if($self->{postgres})
 532     {
 533         return $self->{dbh}->last_insert_id(undef, undef, undef, undef,
 534                                             { sequence => "seq" });
 535     }
 536     else
 537     {
 538         return $self->{dbh}->last_insert_id("","","","");
 539     }
 540 }
 541
 542 __DATA__
 543
 544 CREATE TABLE id3fs (
 545     schema_version INTEGER,
 546     last_update
 547 );
 548
 549 CREATE TABLE files (
 550     id INTEGER PRIMARY KEY,
 551     artists_id,
 552     albums_id,
 553     paths_id,
 554     name text
 555 );
 556
 557 CREATE TABLE paths (
 558     id INTEGER PRIMARY KEY,
 559     name text
 560 );
 561
 562 CREATE TABLE artists (
 563     id INTEGER PRIMARY KEY,
 564     name text
 565 );
 566
 567 CREATE TABLE albums (
 568     id INTEGER PRIMARY KEY,
 569     name text
 570 );
 571
 572 CREATE TABLE tags (
 573     id INTEGER PRIMARY KEY,
 574     name text
 575 );
 576
 577 CREATE TABLE tagvals (
 578     id INTEGER PRIMARY KEY,
 579     name text
 580 );
 581
 582 CREATE TABLE files_x_tags (
 583     files_id INTEGER,
 584     tags_id INTEGER
 585 );
 586
 587 CREATE TABLE tags_x_tagvals (
 588     tags_id INTEGER,
 589     tagvals_id INTEGER
 590 );