lib/ID3FS/DB.pm

   1 package ID3FS::DB;
   2
   3 use strict;
   4 use warnings;
   5 use DBI;
   6 use ID3FS::File;
   7
   8 our $SCHEMA_VERSION=1;
   9 my $dbfile=".id3fs";
  10
  11 sub new
  12 {
  13     my $proto=shift;
  14     my $class=ref($proto) || $proto;
  15     my $self={};
  16     bless($self,$class);
  17
  18     my($dir, $init, $me)=@_;
  19     $self->{dbpath}="$dir/$dbfile";
  20     $self->{me}=$me;
  21
  22     my $exists=-f $self->{dbpath};
  23
  24     $self->{postgres}=0;
  25
  26     unless($self->{postgres})
  27     {
  28         die("$me: $self->{dbpath}: not found. use --init to create.\n") if(!$exists && !$init);
  29         die("$me: --init used but $self->{dbpath} exists.\n")           if($exists && $init);
  30     }
  31
  32     my $connectstr="dbi:SQLite:dbname=$self->{dbpath}";
  33     my ($user, $pass)=("", "");
  34     if($self->{postgres})
  35     {
  36         $connectstr="dbi:Pg:dbname=id3fs";
  37         $user="ianb";
  38         $pass="foo";
  39     }
  40     $self->{dbh}=DBI->connect($connectstr, $user, $pass,
  41                               { AutoCommit=>1 } );
  42     unless(defined($self->{dbh}))
  43     {
  44         die("$me: DB Error: " . $DBI::errstr . "\n");
  45     }
  46
  47     if($init)
  48     {
  49         $self->create();
  50     }
  51     else
  52     {
  53         $self->checkschema();
  54     }
  55
  56     return $self;
  57 }
  58
  59 sub create
  60 {
  61     my($self,$name)=@_;
  62     my @schema=split(/\n\n/,join("", <DATA>));
  63     close(DATA);
  64     for my $cmd (@schema)
  65     {
  66         $self->{dbh}->do($cmd);
  67     }
  68     if($self->{postgres})
  69     {
  70         $self->cmd("CREATE SEQUENCE seq");
  71     }
  72     else
  73     {
  74         my %indexes=( "idx_files_id"  => "files(id)",
  75                       "idx_fxt_both"  => "files_x_tags(files_id, tags_id)",
  76                       "idx_fxt_files" => "files_x_tags (files_id)",
  77                       "idx_fxt_tags"  => "files_x_tags (tags_id)",
  78                       "idx_tags_id"   => "tags (id)",
  79                       "idx_tags_name" => "tags(name)" );
  80         for my $index (keys %indexes)
  81         {
  82             $self->{dbh}->do("CREATE INDEX $index ON " . $indexes{$index});
  83         }
  84     }
  85     $self->cmd("INSERT INTO id3fs (schema_version) VALUES (?)", $SCHEMA_VERSION);
  86 }
  87
  88 sub checkschema
  89 {
  90     my $self=shift;
  91     my ($version)=$self->cmd_onerow("SELECT schema_version from id3fs");
  92     if(!defined($version) || $version != $SCHEMA_VERSION)
  93     {
  94         die("$self->{me}: id3fs database version " .
  95             defined($version) ? $version : '""' .
  96             "not known, current version is $SCHEMA_VERSION.\n");
  97     }
  98 }
  99
 100 sub cmd_sth
 101 {
 102     my($self, $sql, @params)=@_;
 103     my $sth=$self->{dbh}->prepare($sql);
 104     my $idx=1;
 105     for my $param (@params)
 106     {
 107         $param="" unless(defined($param));
 108         $sth->bind_param($idx++, $param);
 109     }
 110     $sth->execute();
 111     return $sth;
 112 }
 113
 114 sub tags
 115 {
 116     my($self, @constraints)=@_;
 117     if(!@constraints) # /
 118     {
 119         my $sql="SELECT DISTINCT name FROM tags;";
 120         my $tags=$self->cmd_rows($sql);
 121         return(map { $_->[0]; } @$tags);
 122     }
 123     my @file_ids=();
 124     my @tag_ids=();
 125
 126     my $main_sql_start=("SELECT t2.name\n" .
 127                         "\tFROM (SELECT files_id FROM tags t1\n" .
 128                         "\t\tINNER JOIN files_x_tags ON t1.id=files_x_tags.tags_id\n" .
 129                         "\t\tWHERE t1.id in\n\t\t\t(");
 130     my $main_sql_mid=(")\n\t\t) AS subselect\n" .
 131                       "\tINNER JOIN files_x_tags ON subselect.files_id=files_x_tags.files_id\n" .
 132                       "\tINNER JOIN tags t2 ON files_x_tags.tags_id=t2.id\n" .
 133                       "\tWHERE t2.id NOT IN (");
 134     my $main_sql_end=")\n\tGROUP BY t2.name;";
 135     while(my $constraint=shift @constraints)
 136     {
 137         print "CONSTRAINT: $constraint->{name}\n";
 138         my $cid=$constraint->{id};
 139         push(@tag_ids, $cid);
 140     }
 141     @tag_ids = map( { "\"$_\""; } @tag_ids) unless($self->{postgres});
 142     my $tagstr=join(", ", @tag_ids);
 143     my $sql = ($main_sql_start . $tagstr .
 144                $main_sql_mid   . $tagstr .
 145                $main_sql_end);
 146     print "SQL: $sql\n";
 147     my $result=$self->cmd_rows($sql);
 148     my @tagnames=map { $_->[0]; } @$result;
 149     print "SUBNAMES: ", join(', ', @tagnames), "\n";
 150     return(@tagnames);
 151 }
 152
 153 sub tag_values
 154 {
 155     my($self, $tag)=@_;
 156     my $sql=("SELECT DISTINCT tagvals.name FROM tags\n" .
 157              "INNER JOIN tags_x_tagvals ON tags.id=tags_x_tagvals.tags_id\n" .
 158              "INNER JOIN tagvals ON tagvals.id=tags_x_tagvals.tagvals_id\n" .
 159              "WHERE tags.name=?");
 160     my $tags=$self->cmd_rows($sql, $tag);
 161     return(map { $_->[0]; } @$tags);
 162 }
 163
 164 sub artists
 165 {
 166     my($self, @constraints)=@_;
 167     if(!@constraints) # /ALL
 168     {
 169         my $sql="SELECT DISTINCT name FROM artists;";
 170         my $tags=$self->cmd_rows($sql);
 171         return(map { $_->[0]; } @$tags);
 172     }
 173     my @file_ids=();
 174     my @tag_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(@tag_ids, $cid);
 188     }
 189     @tag_ids = map( { "\"$_\""; } @tag_ids) unless($self->{postgres});
 190     my $tagstr=join(", ", @tag_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 @file_ids=(); # FIXME: needed? what about in artists()
 204     my @tag_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     return(); # FIXME
 211     my $main_sql_start=("SELECT artists.name\n" .
 212                         "\tFROM (SELECT files_id FROM tags\n" .
 213                         "\t\tINNER JOIN files_x_tags ON tags.id=files_x_tags.tags_id\n" .
 214                         "\t\tWHERE tags.id in\n\t\t\t(");
 215     my $main_sql_end=(")\n\t\t) AS subselect\n" .
 216                       "\tINNER JOIN files_x_artists ON subselect.files_id=files_x_artists.files_id\n" .
 217                       "\tINNER JOIN artists ON artists.id=files_x_artists.artists_id\n" .
 218                       "\n\tGROUP BY artists.name;");
 219     while(my $constraint=shift @constraints)
 220     {
 221         print "CONSTRAINT: $constraint->{name}\n";
 222         my $cid=$constraint->{id};
 223         push(@tag_ids, $cid);
 224     }
 225     @tag_ids = map( { "\"$_\""; } @tag_ids) unless($self->{postgres});
 226     my $tagstr=join(", ", @tag_ids);
 227     my $sql = ($main_sql_start . $tagstr .
 228                $main_sql_end);
 229     print "SQL: $sql\n";
 230     my $result=$self->cmd_rows($sql);
 231     my @tagnames=map { $_->[0]; } @$result;
 232     print "ARTISTS: ", join(', ', @tagnames), "\n";
 233     return(@tagnames);
 234 }
 235
 236 sub artist_albums
 237 {
 238     my($self, $artist_id)=@_;
 239     my $sql=("SELECT albums.name FROM artists\n\t" .
 240              "INNER JOIN artists_x_albums ON artists.id=artists_x_albums.artists_id\n\t" .
 241              "INNER JOIN albums ON albums.id=artists_x_albums.albums_id\n\t" .
 242              "WHERE artists.id=?\n\t" .
 243              "GROUP BY albums.name\n");
 244     print "ARTIST_ALBUMS SQL: $sql\n";
 245     my $result=$self->cmd_rows($sql, $artist_id);
 246     my @albums=map { $_->[0]; } @$result;
 247     print "ALBUMS: ", join(', ', @albums), "\n";
 248     return(@albums);
 249 }
 250
 251 sub artist_tracks
 252 {
 253     my($self, $artist_id)=@_;
 254     my $sql=("SELECT files.name FROM artists\n\t" .
 255              "INNER JOIN artists_x_files ON artists.id=files_x_artists.artists_id\n\t" .
 256              "INNER JOIN files ON files.id=files_x_artists.files_id\n\t" .
 257              "WHERE artists.id=?\n\t" .
 258              "GROUP BY files.name\n");
 259     print "ARTIST_TRACKS SQL: $sql\n";
 260     my $result=$self->cmd_rows($sql, $artist_id);
 261     my @albums=map { $_->[0]; } @$result;
 262     print "ALBUMS: ", join(', ', @albums), "\n";
 263     return(@albums);
 264 }
 265
 266 sub album_tracks
 267 {
 268     # FIXME: need albums_x_files table
 269     my($self, $album_id)=@_;
 270     my $sql=("SELECT files.name FROM albums\n\t" .
 271              "INNER JOIN files_x_albums ON albums.id=files_x_albums.albums_id\n\t" .
 272              "INNER JOIN albums ON albums.id=files_x_albums.albums_id\n\t" .
 273              "WHERE albums.id=?\n\t" .
 274              "GROUP BY files.name\n");
 275     print "ALBUM_TRACKS SQL: $sql\n";
 276     my $result=$self->cmd_rows($sql, $album_id);
 277     my @tracks=map { $_->[0]; } @$result;
 278     print "TRACKS: ", join(', ', @tracks), "\n";
 279     return(@tracks);
 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     return(); # FIXME
 297     my $main_sql_start=("SELECT artists.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_x_artists ON subselect.files_id=files_x_artists.files_id\n" .
 303                       "\tINNER JOIN artists ON artists.id=files_x_artists.artists_id\n" .
 304                       "\n\tGROUP BY artists.name;");
 305     my @tag_ids;
 306     while(my $constraint=shift @constraints)
 307     {
 308         print "CONSTRAINT: $constraint->{name}\n";
 309         my $cid=$constraint->{id};
 310         push(@tag_ids, $cid);
 311     }
 312     @tag_ids = map( { "\"$_\""; } @tag_ids) unless($self->{postgres});
 313     my $tagstr=join(", ", @tag_ids);
 314     my $sql = ($main_sql_start . $tagstr .
 315                $main_sql_end);
 316     print "SQL: $sql\n";
 317     my $result=$self->cmd_rows($sql);
 318     my @tagnames=map { $_->[0]; } @$result;
 319     print "ARTISTS: ", join(', ', @tagnames), "\n";
 320     return(@tagnames);
 321 }
 322
 323 sub id
 324 {
 325     my($self, $type, $val)=@_;
 326     my $sql="SELECT id FROM $type WHERE name=?";
 327     my ($id)=$self->cmd_onerow($sql, $val);
 328     return($id);
 329 }
 330
 331 sub add
 332 {
 333     my($self,$path)=@_;
 334     my $file=ID3FS::File->new($path);
 335     return unless(defined($file));
 336     my $artist=$file->artist();
 337     my $album=$file->album();
 338     my $v1genre=$file->v1genre();
 339     my $year=$file->year();
 340     my $audiotype=$file->album();
 341     my $tags=$file->tags();
 342     my $haspic=$file->haspic();
 343
 344     my $file_id=$self->add_to_table("files", $path);
 345     my $artists_id=$self->add_to_table("artists",  $artist);
 346     my $albums_id=$self->add_to_table("albums",  $album);
 347     for my $tag (keys %$tags)
 348     {
 349         $self->add_tag($file_id, $tag, $tags->{$tag});
 350     }
 351
 352     if($self->ok($year))
 353     {
 354         $self->add_tag($file_id, "year", $year);
 355         if($year=~/^(\d\d\d)\d$/)
 356         {
 357             $self->add_tag($file_id, "decade", "${1}0s");
 358         }
 359     }
 360     if($self->ok($v1genre))
 361     {
 362         $self->add_tag($file_id, "v1genre", $v1genre);
 363     }
 364
 365     if($haspic)
 366     {
 367         $self->add_tag($file_id, "haspic", undef);
 368     }
 369
 370     $self->add_relation("files_x_artists",
 371                         { "files_id" => $file_id,
 372                           "artists_id" => $artists_id });
 373
 374     $self->add_relation("artists_x_albums",
 375                       { "artists_id" => $artists_id,
 376                         "albums_id" => $albums_id});
 377 }
 378
 379 sub add_tag
 380 {
 381     my($self, $file_id, $tag, $val)=@_;
 382     my $tag_id=$self->add_to_table("tags",  $tag);
 383     $self->add_relation("files_x_tags",
 384                         { "files_id" => $file_id,
 385                           "tags_id"  => $tag_id });
 386     if(defined($val))
 387     {
 388         my $val_id=$self->add_to_table("tagvals", $val);
 389         $self->add_relation("tags_x_tagvals",
 390                             { "tags_id"     => $tag_id,
 391                               "tagvals_id"  => $val_id });
 392     }
 393 }
 394
 395 sub add_to_table
 396 {
 397     my($self, $table, $name, $extradata)=@_;
 398     my $id=$self->lookup_id($table, $name);
 399     unless(defined($id))
 400     {
 401         my $sql="INSERT INTO $table (";
 402         $sql .= "id, " if($self->{postgres});
 403         my @fields=qw(name);
 404         if(defined($extradata))
 405         {
 406             push(@fields, sort keys(%$extradata));
 407         }
 408         $sql .= join(", ", @fields);
 409         $sql .=") VALUES (";
 410         $sql .=") nextval('seq'), " if($self->{postgres});
 411         $sql .= join(", ", map { "?"; } @fields);
 412         $sql .= ");";
 413         $id=$self->cmd_id($sql, $name, map { $extradata->{$_} || ""; } sort keys %$extradata);
 414     }
 415     return $id;
 416 }
 417
 418 sub add_relation
 419 {
 420     my ($self, $relname, $fields)=@_;
 421     return if($self->relation_exists($relname, $fields));
 422     my $sql="INSERT INTO $relname (";
 423     $sql .= join(", ", sort keys(%$fields));
 424     $sql .= ") VALUES (";
 425     $sql .= join(", ", map { "?"; } sort keys(%$fields));
 426     $sql .= ");";
 427     $self->cmd($sql, map { $fields->{$_}; } sort keys(%$fields));
 428 }
 429
 430 sub lookup_id
 431 {
 432     my($self, $table, $name)=@_;
 433     my($id)=$self->cmd_onerow("SELECT id FROM $table where name=?", $name);
 434     return $id;
 435 }
 436
 437 sub relation_exists
 438 {
 439     my ($self, $relname, $fields)=@_;
 440     my $sql="SELECT count(1) FROM $relname WHERE ";
 441     my @exprs=();
 442     my @vals=();
 443     for my $field (keys %$fields)
 444     {
 445         push(@exprs,$field);
 446         push(@vals,$fields->{$field});
 447     }
 448     $sql .= join(' AND ', map { "$_=?"; } @exprs);
 449     my ($ret)=$self->cmd_onerow($sql, @vals);
 450     return $ret;
 451 }
 452
 453 sub ok
 454 {
 455     my($self, $thing)=@_;
 456     return(defined($thing) && length($thing));
 457 }
 458
 459 sub cmd
 460 {
 461     my ($self, @args)=@_;
 462     # don't care about retcode
 463     $self->cmd_sth(@args);
 464 }
 465
 466 sub cmd_onerow
 467 {
 468     my ($self, @args)=@_;
 469     my $sth=$self->cmd_sth(@args);
 470     return($sth->fetchrow_array());
 471 }
 472
 473 sub cmd_rows
 474 {
 475     my ($self, @args)=@_;
 476     my $sth=$self->cmd_sth(@args);
 477     return $sth->fetchall_arrayref();
 478 }
 479
 480 sub cmd_id
 481 {
 482     my ($self, @args)=@_;
 483     $self->cmd_sth(@args);
 484     return($self->last_insert_id());
 485 }
 486
 487 sub last_insert_id
 488 {
 489     my $self=shift;
 490     if($self->{postgres})
 491     {
 492         return $self->{dbh}->last_insert_id(undef, undef, undef, undef,
 493                                             { sequence => "seq" });
 494     }
 495     else
 496     {
 497         return $self->{dbh}->last_insert_id("","","","");
 498     }
 499 }
 500
 501 __DATA__
 502
 503 CREATE TABLE id3fs (
 504     schema_version INTEGER
 505 );
 506
 507 CREATE TABLE files (
 508     id INTEGER PRIMARY KEY,
 509     name text
 510 );
 511
 512 CREATE TABLE artists (
 513     id INTEGER PRIMARY KEY,
 514     name text
 515 );
 516
 517 CREATE TABLE albums (
 518     id INTEGER PRIMARY KEY,
 519     name text
 520 );
 521
 522 CREATE TABLE tags (
 523     id INTEGER PRIMARY KEY,
 524     name text
 525 );
 526
 527 CREATE TABLE tagvals (
 528     id INTEGER PRIMARY KEY,
 529     name text
 530 );
 531
 532 CREATE TABLE files_x_tags (
 533     files_id INTEGER,
 534     tags_id INTEGER
 535 );
 536
 537 CREATE TABLE tags_x_tagvals (
 538     tags_id INTEGER,
 539     tagvals_id INTEGER
 540 );
 541
 542 CREATE TABLE files_x_artists (
 543     files_id INTEGER,
 544     artists_id INTEGER
 545 );
 546
 547 CREATE TABLE artists_x_albums (
 548     artists_id INTEGER,
 549     albums_id INTEGER
 550 );