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