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 albums\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: $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         # FIXME
 293         return(());
 294 #       return $self->album_tracks($constraints[$#constraints]->{id});
 295     }
 296
 297     my $main_sql_start=("SELECT files.name\n" .
 298                         "\tFROM (SELECT files_id FROM tags\n" .
 299                         "\t\tINNER JOIN files_x_tags ON tags.id=files_x_tags.tags_id\n" .
 300                         "\t\tWHERE tags.id in\n\t\t\t(");
 301     my $main_sql_end=(")\n\t\t) AS subselect\n" .
 302                       "\tINNER JOIN files ON files.id=subselect.files_id" .
 303                       "\tGROUP BY files.name;");
 304     my @ids;
 305     while(my $constraint=shift @constraints)
 306     {
 307         print "CONSTRAINT: $constraint->{name}\n";
 308         my $cid=$constraint->{id};
 309         push(@ids, $cid);
 310     }
 311     @ids = map( { "\"$_\""; } @ids) unless($self->{postgres});
 312     my $str=join(", ", @ids);
 313     my $sql = ($main_sql_start . $str .
 314                $main_sql_end);
 315     print "SQL: $sql\n";
 316     my $result=$self->cmd_rows($sql);
 317     my @names=map { $_->[0]; } @$result;
 318     @names = map { s/.*\///; $_; } @names;
 319     print "TRACKS: ", join(', ', @names), "\n";
 320     return(@names);
 321 }
 322
 323 sub filename
 324 {
 325     my($self, @constraints)=@_;
 326     if(ref($constraints[$#constraints]) eq "ID3FS::PathElement::File")
 327     {
 328         my $id=$constraints[$#constraints]->{id};
 329         my $sql=("SELECT paths.name, files.name FROM files\n" .
 330                  "INNER JOIN paths_x_files ON files.id=paths_x_files.files_id\n" .
 331                  "INNER JOIN paths ON paths_x_files.paths_id=paths.id\n" .
 332                  "WHERE files.id=?\n" .
 333                  "GROUP BY paths.name, files.name");
 334         print "FILENAME SQL: $sql\n";
 335         my ($path, $name)=$self->cmd_onerow($sql, $id);
 336         return($self->{absbase} . "/$path/$name");
 337     }
 338     die("DB::filename: unhandled case\n"); #FIXME
 339 }
 340
 341 sub id
 342 {
 343     my($self, $type, $val)=@_;
 344     print "ID: $type $val\n";
 345     my $sql="SELECT id FROM $type WHERE name=?";
 346     my ($id)=$self->cmd_onerow($sql, $val);
 347     return($id);
 348 }
 349
 350 sub add
 351 {
 352     my($self,$path)=@_;
 353     my $relpath=$path;
 354     $relpath =~ s/$self->{base}\/?//;
 355     my($filepart,$pathpart);
 356     if($path !~ /\//)
 357     {
 358         $pathpart='';
 359         $filepart=$relpath;
 360     }
 361     else
 362     {
 363         ($pathpart, $filepart) = ($relpath =~ /(.*)\/(.*)/);
 364     }
 365     my $file=ID3FS::File->new($path);
 366     return unless(defined($file));
 367     my $artist=$file->artist();
 368     my $album=$file->album();
 369     my $v1genre=$file->v1genre();
 370     my $year=$file->year();
 371     my $audiotype=$file->album();
 372     my $tags=$file->tags();
 373     my $haspic=$file->haspic();
 374
 375     my $file_id=$self->add_to_table("files", $filepart);
 376     my $path_id=$self->add_to_table("paths", $pathpart);
 377     $self->add_relation("paths_x_files",
 378                         { "paths_id" => $path_id,
 379                           "files_id" => $file_id});
 380
 381     for my $tag (keys %$tags)
 382     {
 383         $self->add_tag($file_id, $tag, $tags->{$tag});
 384     }
 385
 386     my $artist_id;
 387     if($self->ok($artist))
 388     {
 389         $artist_id=$self->add_to_table("artists",  $artist);
 390         $self->add_relation("files_x_artists",
 391                             { "files_id" => $file_id,
 392                               "artists_id" => $artist_id });
 393     }
 394
 395     if($self->ok($album))
 396     {
 397         my $albums_id=$self->add_to_table("albums", $album);
 398         $self->add_relation("files_x_albums",
 399                             { "files_id" => $file_id,
 400                               "albums_id" => $albums_id});
 401         if($self->ok($artist))
 402         {
 403             $self->add_relation("artists_x_albums",
 404                                 { "artists_id" => $artist_id,
 405                                   "albums_id" => $albums_id});
 406         }
 407     }
 408
 409     if($self->ok($year))
 410     {
 411         $self->add_tag($file_id, "year", $year);
 412         if($year=~/^(\d\d\d)\d$/)
 413         {
 414             $self->add_tag($file_id, "decade", "${1}0s");
 415         }
 416     }
 417
 418     if($self->ok($v1genre))
 419     {
 420         $self->add_tag($file_id, "v1genre", $v1genre);
 421     }
 422
 423     if($haspic)
 424     {
 425         $self->add_tag($file_id, "haspic", undef);
 426     }
 427 }
 428
 429 sub add_tag
 430 {
 431     my($self, $file_id, $tag, $val)=@_;
 432     my $tag_id=$self->add_to_table("tags",  $tag);
 433     $self->add_relation("files_x_tags",
 434                         { "files_id" => $file_id,
 435                           "tags_id"  => $tag_id });
 436     if(defined($val))
 437     {
 438         my $val_id=$self->add_to_table("tagvals", $val);
 439         $self->add_relation("tags_x_tagvals",
 440                             { "tags_id"     => $tag_id,
 441                               "tagvals_id"  => $val_id });
 442     }
 443 }
 444
 445 sub add_to_table
 446 {
 447     my($self, $table, $name, $extradata)=@_;
 448     my $id=$self->lookup_id($table, $name);
 449     unless(defined($id))
 450     {
 451         my $sql="INSERT INTO $table (";
 452         $sql .= "id, " if($self->{postgres});
 453         my @fields=qw(name);
 454         if(defined($extradata))
 455         {
 456             push(@fields, sort keys(%$extradata));
 457         }
 458         $sql .= join(", ", @fields);
 459         $sql .=") VALUES (";
 460         $sql .=") nextval('seq'), " if($self->{postgres});
 461         $sql .= join(", ", map { "?"; } @fields);
 462         $sql .= ");";
 463         $id=$self->cmd_id($sql, $name, map { $extradata->{$_} || ""; } sort keys %$extradata);
 464     }
 465     return $id;
 466 }
 467
 468 sub add_relation
 469 {
 470     my ($self, $relname, $fields)=@_;
 471     return if($self->relation_exists($relname, $fields));
 472     my $sql="INSERT INTO $relname (";
 473     $sql .= join(", ", sort keys(%$fields));
 474     $sql .= ") VALUES (";
 475     $sql .= join(", ", map { "?"; } sort keys(%$fields));
 476     $sql .= ");";
 477     $self->cmd($sql, map { $fields->{$_}; } sort keys(%$fields));
 478 }
 479
 480 sub lookup_id
 481 {
 482     my($self, $table, $name)=@_;
 483     my($id)=$self->cmd_onerow("SELECT id FROM $table where name=?", $name);
 484     return $id;
 485 }
 486
 487 sub relation_exists
 488 {
 489     my ($self, $relname, $fields)=@_;
 490     my $sql="SELECT count(1) FROM $relname WHERE ";
 491     my @exprs=();
 492     my @vals=();
 493     for my $field (keys %$fields)
 494     {
 495         push(@exprs,$field);
 496         push(@vals,$fields->{$field});
 497     }
 498     $sql .= join(' AND ', map { "$_=?"; } @exprs);
 499     my ($ret)=$self->cmd_onerow($sql, @vals);
 500     return $ret;
 501 }
 502
 503 sub ok
 504 {
 505     my($self, $thing)=@_;
 506     return(defined($thing) && length($thing) && $thing =~ /\S+/);
 507 }
 508
 509 sub cmd
 510 {
 511     my ($self, @args)=@_;
 512     # don't care about retcode
 513     $self->cmd_sth(@args);
 514 }
 515
 516 sub cmd_onerow
 517 {
 518     my ($self, @args)=@_;
 519     my $sth=$self->cmd_sth(@args);
 520     return($sth->fetchrow_array());
 521 }
 522
 523 sub cmd_rows
 524 {
 525     my ($self, @args)=@_;
 526     my $sth=$self->cmd_sth(@args);
 527     return $sth->fetchall_arrayref();
 528 }
 529
 530 sub cmd_id
 531 {
 532     my ($self, @args)=@_;
 533     $self->cmd_sth(@args);
 534     return($self->last_insert_id());
 535 }
 536
 537 sub last_insert_id
 538 {
 539     my $self=shift;
 540     if($self->{postgres})
 541     {
 542         return $self->{dbh}->last_insert_id(undef, undef, undef, undef,
 543                                             { sequence => "seq" });
 544     }
 545     else
 546     {
 547         return $self->{dbh}->last_insert_id("","","","");
 548     }
 549 }
 550
 551 __DATA__
 552
 553 CREATE TABLE id3fs (
 554     schema_version INTEGER
 555 );
 556
 557 CREATE TABLE files (
 558     id INTEGER PRIMARY KEY,
 559     name text
 560 );
 561
 562 CREATE TABLE paths (
 563     id INTEGER PRIMARY KEY,
 564     name text
 565 );
 566
 567 CREATE TABLE artists (
 568     id INTEGER PRIMARY KEY,
 569     name text
 570 );
 571
 572 CREATE TABLE albums (
 573     id INTEGER PRIMARY KEY,
 574     name text
 575 );
 576
 577 CREATE TABLE tags (
 578     id INTEGER PRIMARY KEY,
 579     name text
 580 );
 581
 582 CREATE TABLE tagvals (
 583     id INTEGER PRIMARY KEY,
 584     name text
 585 );
 586
 587 CREATE TABLE paths_x_files (
 588     paths_id INTEGER,
 589     files_id INTEGER
 590 );
 591
 592 CREATE TABLE files_x_tags (
 593     files_id INTEGER,
 594     tags_id INTEGER
 595 );
 596
 597 CREATE TABLE tags_x_tagvals (
 598     tags_id INTEGER,
 599     tagvals_id INTEGER
 600 );
 601
 602 CREATE TABLE files_x_artists (
 603     files_id INTEGER,
 604     artists_id INTEGER
 605 );
 606
 607 CREATE TABLE files_x_albums (
 608     files_id INTEGER,
 609     albums_id INTEGER
 610 );
 611
 612 CREATE TABLE artists_x_albums (
 613     artists_id INTEGER,
 614     albums_id INTEGER
 615 );