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->{verbose}=shift;
  21     my $init=shift;
  22     my $dbpath=shift;
  23     $self->{base}=shift;
  24     my $fallbackdir=shift;
  25
  26     $dbpath=$self->find_db($init, $dbpath, $fallbackdir);
  27     return undef unless($dbpath);
  28     $self->{absbase}=Cwd::abs_path($self->{base});
  29
  30     my $connectstr="dbi:SQLite:dbname=$dbpath";
  31     my ($user, $pass)=("", "");
  32     if($self->{postgres})
  33     {
  34         $connectstr="dbi:Pg:dbname=id3fs";
  35         $user="ianb";
  36         $pass="foo";
  37     }
  38     my $exists=-f $dbpath;
  39     $self->{dbh}=DBI->connect($connectstr, $user, $pass,
  40                               { AutoCommit=>1 } );
  41     unless(defined($self->{dbh}))
  42     {
  43         die("$self->{me}: DB Error: " . $DBI::errstr . "\n");
  44     }
  45
  46     if($exists)
  47     {
  48         $self->checkschema();
  49     }
  50     else
  51     {
  52         $self->create();
  53     }
  54     $self->enable_foreign_keys();
  55     return $self;
  56 }
  57
  58 sub find_db
  59 {
  60     my($self, $init, $dbpath, $fallbackdir)=@_;
  61     my $file=undef;
  62     my $base=undef;
  63     if(defined($dbpath))
  64     {
  65         $file=$dbpath;
  66     }
  67     if(defined ($self->{base}))
  68     {
  69         $file="$self->{base}/$dbfile" unless defined($file);
  70         $base=$self->{base};
  71     }
  72     elsif(defined($fallbackdir) && -d $fallbackdir)
  73     {
  74         my $path=Cwd::abs_path($fallbackdir);
  75         do
  76         {
  77             $file="$path/$dbfile";
  78             $base=$path;
  79             $path=~s/(.*)\/.*/$1/;
  80         }
  81         while(! -f $file && length($path) && -d $path);
  82         if(! -f $file)
  83         {
  84             $file="$fallbackdir/$dbfile";
  85             $base=$fallbackdir;
  86         }
  87     }
  88     if(!-f $file && !$init)
  89     {
  90         print "$self->{me}: db not found at $file\n";
  91         return undef;
  92     }
  93     $self->{base}=$base;
  94     return $file;
  95 }
  96
  97 sub base_dir { return shift->{base}; }
  98
  99 sub create
 100 {
 101     my($self,$name)=@_;
 102     my @schema=split(/\n\n/,join("", <DATA>));
 103     close(DATA);
 104     for my $cmd (@schema)
 105     {
 106         $self->{dbh}->do($cmd);
 107     }
 108     if($self->{postgres})
 109     {
 110         $self->cmd("CREATE SEQUENCE seq");
 111     }
 112     else
 113     {
 114         my %indexes=( "idx_files_id"  => "files (id)",
 115                       "idx_fxt_both"  => "files_x_tags (files_id, tags_id)",
 116                       "idx_fxt_files" => "files_x_tags (files_id)",
 117                       "idx_fxt_tags"  => "files_x_tags (tags_id)",
 118                       "idx_tags_id"   => "tags (id)",
 119                       "idx_tags_name" => "tags (name)");
 120         for my $index (keys %indexes)
 121         {
 122             $self->{dbh}->do("CREATE INDEX $index ON " . $indexes{$index});
 123         }
 124     }
 125     $self->cmd("INSERT INTO id3fs (schema_version, last_update) VALUES (?, ?)",
 126                $SCHEMA_VERSION, time());
 127 }
 128
 129 sub checkschema
 130 {
 131     my $self=shift;
 132     my ($version)=$self->cmd_onerow("SELECT schema_version from id3fs");
 133     if(!defined($version) || $version != $SCHEMA_VERSION)
 134     {
 135         die("$self->{me}: id3fs database version " .
 136             defined($version) ? $version : '""' .
 137             "not known, current version is $SCHEMA_VERSION.\n");
 138     }
 139 }
 140
 141 sub analyze
 142 {
 143     my $self=shift;
 144     $self->cmd("ANALYZE");
 145 }
 146
 147 sub enable_foreign_keys
 148 {
 149     my $self=shift;
 150     $self->cmd("PRAGMA foreign_keys = ON");
 151 }
 152
 153 sub last_update
 154 {
 155     my($self, $newval)=@_;
 156     if(defined($newval))
 157     {
 158         $self->cmd("UPDATE id3fs SET last_update=?", $newval);
 159     }
 160     else
 161     {
 162         ($newval)=$self->cmd_onerow("SELECT last_update from id3fs");
 163     }
 164     return $newval;
 165 }
 166
 167 sub cmd_sth
 168 {
 169     my($self, $sql, @params)=@_;
 170     my $sth=$self->{dbh}->prepare($sql);
 171     my $idx=1;
 172     for my $param (@params)
 173     {
 174         $param="" unless(defined($param));
 175         $sth->bind_param($idx++, $param);
 176     }
 177     $sth->execute();
 178     return $sth;
 179 }
 180
 181 sub tags
 182 {
 183     my($self, $path)=@_;
 184     my @constraints=@{$path->{elements}};
 185     if(!@constraints) # /
 186     {
 187         my $sql="SELECT DISTINCT name FROM tags WHERE parents_id='';";
 188         my $tags=$self->cmd_rows($sql);
 189         return(map { $_->[0]; } @$tags);
 190     }
 191     my $hasvals=$path->tag_has_values();
 192     my $parent=$path->trailing_tag_parent();
 193     print "THASVALS: $hasvals\n";
 194     print "TPARENT: ", (defined($parent)? $parent : "NO"), "\n";
 195     my @ids=();
 196     my $sql=("SELECT tags.name FROM (\n" .
 197              $self->tags_subselect($path) .
 198              ") AS subselect\n" .
 199              "INNER JOIN files_x_tags ON subselect.files_id=files_x_tags.files_id\n" .
 200              "INNER JOIN tags ON files_x_tags.tags_id=tags.id\n");
 201     my (@allused)=$path->used_tags();
 202     my @used=grep { ref($_) ne "ARRAY"; } @allused;
 203     my @used_with_vals=grep { ref($_) eq "ARRAY"; } @allused;
 204     print "tags(): USED: ", join(", ", @used), "\n";
 205     print "tags(): USED_WITH_VALS: ", join(", ", map { "[".$_->[0]. ", ".$_->[1]."]";} @used_with_vals), "\n";
 206     my @orclauses=();
 207     my @andclauses=();
 208     my $id=$path->trailing_tag_id();
 209     if($hasvals)
 210     {
 211         print "HAS_VALUES\n";
 212         my @values=map { "'".$_->[1]."'"; } grep { $_->[0] == $id; } @used_with_vals;
 213         my $clause="(tags.parents_id='$id'";
 214         if(@values)
 215         {
 216             $clause .= " AND tags.id NOT IN (" . join(', ', @values) . ")";
 217         }
 218         $clause .= ")";
 219         push(@orclauses, $clause);
 220     }
 221     else
 222     {
 223         print "HASNT VALUES\n";;
 224         if(@used)
 225         {
 226             push(@orclauses, "(NOT (tags.parents_id='' AND tags.id IN (" . join(', ', @used) . ")))");
 227         }
 228         for my $pair (@used_with_vals)
 229         {
 230             push(@orclauses, "(NOT (tags.parents_id='" . $pair->[0] . "' AND tags.id='" . $pair->[1] . "'))");
 231         }
 232     }
 233
 234     my $parentclause= "(tags.parents_id='";
 235     if($hasvals)
 236     {
 237         $parentclause .= $id;
 238     }
 239     elsif($parent)
 240     {
 241         $parentclause .= $parent;
 242     }
 243     $parentclause .= "')";
 244     push(@andclauses, $parentclause);
 245
 246     if(@orclauses)
 247     {
 248         push(@andclauses, join(' OR ', @orclauses));
 249     }
 250     if(@andclauses)
 251     {
 252         $sql .= "WHERE " . join(' AND ', @andclauses) . "\n";
 253     }
 254     $sql .= "GROUP BY tags.name;";
 255     print "SQL: $sql\n";
 256     my $result=$self->cmd_rows($sql);
 257     my @tagnames=map { $_->[0]; } @$result;
 258     print "SUBNAMES: ", join(', ', @tagnames), "\n";
 259     return(@tagnames);
 260 }
 261
 262 sub tag_values
 263 {
 264     my($self, $tagid)=@_;
 265     my $sql=("SELECT DISTINCT name FROM tags\n" .
 266              "WHERE parents_id=?");
 267     my $tags=$self->cmd_rows($sql, $tagid);
 268     my @tags=map { $_->[0]; } @$tags;
 269     @tags=map { length($_) ? $_ : "NOVALUE"; } @tags;
 270     return @tags;
 271 }
 272
 273 sub artists
 274 {
 275     my($self, $path)=@_;
 276     my @constraints=@{$path->{elements}};
 277     if(!@constraints) # /ALL
 278     {
 279         my $sql="SELECT DISTINCT name FROM artists;";
 280         my $tags=$self->cmd_rows($sql);
 281         return(map { $_->[0]; } @$tags);
 282     }
 283     my @ids=();
 284     my $sql=("SELECT artists.name FROM (\n" .
 285              $self->tags_subselect($path) .
 286              ") AS subselect\n" .
 287              "INNER JOIN files ON subselect.files_id=files.id\n" .
 288              "INNER JOIN artists ON files.artists_id=artists.id\n" .
 289              "GROUP BY artists.name;");
 290     print "SQL: $sql\n";
 291     my $result=$self->cmd_rows($sql);
 292     my @tagnames=map { $_->[0]; } @$result;
 293     print "ARTISTS: ", join(', ', @tagnames), "\n";
 294     return(@tagnames);
 295 }
 296
 297 sub albums
 298 {
 299     my($self, $path)=@_;
 300     my @constraints=@{$path->{elements}};
 301     my @ids=();
 302     # FIXME: rework PathElements
 303     if(ref($constraints[$#constraints]) eq "ID3FS::PathElement::Artist")
 304     {
 305         return $self->artist_albums($constraints[$#constraints]->{id}, $path);
 306     }
 307     my $sql=("SELECT albums.name\n" .
 308              "\tFROM (\n" .
 309              $self->tags_subselect($path) .
 310              "\t) AS subselect\n" .
 311              "INNER JOIN files ON subselect.files_id=files.id\n" .
 312              "INNER JOIN albums ON files.albums_id=albums.id\n" .
 313              "GROUP BY albums.name;");
 314     print "SQL(ALBUMS): \n$sql\n";
 315     my $result=$self->cmd_rows($sql);
 316     my @names=map { $_->[0]; } @$result;
 317     print "ALBUMS: ", join(', ', @names), "\n";
 318     return(@names);
 319 }
 320
 321 sub artist_albums
 322 {
 323     my($self, $artist_id, $path)=@_;
 324     my @constraints=@{$path->{elements}};
 325     my $sql=("SELECT albums.name FROM (\n" .
 326              $self->tags_subselect($path) .
 327              "\t) AS subselect\n" .
 328              "INNER JOIN files ON subselect.files_id=files.id\n" .
 329              "INNER JOIN albums ON albums.id=files.albums_id\n\t" .
 330              "INNER JOIN artists ON artists.id=files.artists_id\n\t" .
 331              "WHERE artists.id=? and albums.name <> ''\n\t" .
 332              "GROUP BY albums.name\n");
 333     print "ARTIST_ALBUMS SQL: $sql\n";
 334     my $result=$self->cmd_rows($sql, $artist_id);
 335     my @albums=map { $_->[0]; } @$result;
 336     print "ALBUMS: ", join(', ', @albums), "\n";
 337     return(@albums);
 338 }
 339
 340 sub artist_tracks
 341 {
 342     my($self, $artist_id, $path)=@_;
 343     my $sql=("SELECT files.name FROM (\n" .
 344              $self->tags_subselect($path) .
 345              "\t) AS subselect\n" .
 346              "INNER JOIN files ON subselect.files_id=files.id\n" .
 347              "INNER JOIN artists ON artists.id=files.artists_id\n\t" .
 348              "INNER JOIN albums  ON albums.id=files.albums_id\n\t" .
 349              "WHERE artists.id=? AND albums.name=''\n\t" .
 350              "GROUP BY files.name\n");
 351     print "ARTIST_TRACKS SQL: $sql\n";
 352     my $result=$self->cmd_rows($sql, $artist_id);
 353     my @names=map { $_->[0]; } @$result;
 354     print "ARTISTTRACKS: ", join(', ', @names), "\n";
 355     return(@names);
 356 }
 357
 358 sub album_tracks
 359 {
 360     my($self, $artist_id, $album_id)=@_;
 361     my $sql=("SELECT files.name FROM files\n\t" .
 362              "INNER JOIN albums  ON albums.id=files.albums_id\n\t" .
 363              "INNER JOIN artists ON artists.id=files.artists_id\n\t" .
 364              "WHERE artists.id=? AND albums.id=?\n\t" .
 365              "GROUP BY files.name\n");
 366     print "ALBUM_TRACKS SQL($artist_id, $album_id): $sql\n";
 367     my $result=$self->cmd_rows($sql, $artist_id, $album_id);
 368     my @names=map { $_->[0]; } @$result;
 369     print "TRACKS: ", join(', ', @names), "\n";
 370     return(@names);
 371 }
 372
 373 sub tracks
 374 {
 375     my($self, $path)=@_;
 376     my @constraints=@{$path->{elements}};
 377     # FIXME: rework PathElements
 378     if(ref($constraints[$#constraints]) eq "ID3FS::PathElement::Artist")
 379     {
 380         my $artist_id=0;
 381         my $artist=$constraints[$#constraints];
 382         if(defined($artist) && (ref($artist) eq "ID3FS::PathElement::Artist"))
 383         {
 384             # should always happen
 385             $artist_id=$artist->{id};
 386         }
 387         return $self->artist_tracks($artist_id, $path);
 388     }
 389     elsif(ref($constraints[$#constraints]) eq "ID3FS::PathElement::Album")
 390     {
 391         my $artist_id=0;
 392         my $artist=$constraints[($#constraints)-1];
 393         if(defined($artist) && (ref($artist) eq "ID3FS::PathElement::Artist"))
 394         {
 395             # should always happen
 396             $artist_id=$artist->{id};
 397         }
 398         return $self->album_tracks($artist_id, $constraints[$#constraints]->{id});
 399     }
 400
 401     my $sql=("SELECT files.name\n" .
 402              "\tFROM (\n" .
 403              $self->tags_subselect($path) .
 404              "\t) AS subselect\n" .
 405              "INNER JOIN files ON files.id=subselect.files_id\n" .
 406              "GROUP BY files.name;");
 407     print "SQL: $sql\n";
 408     my $result=$self->cmd_rows($sql);
 409     my @names=map { $_->[0]; } @$result;
 410     print "TRACKS: ", join(', ', @names), "\n";
 411     return(@names);
 412 }
 413
 414 sub filename
 415 {
 416     my($self, $mountpoint, $path)=@_;
 417     my @constraints=@{$path->{elements}};
 418     if(ref($constraints[$#constraints]) eq "ID3FS::PathElement::File")
 419     {
 420         my $id=$constraints[$#constraints]->{id};
 421         my $sql=("SELECT paths.name, files.name FROM files\n" .
 422                  "INNER JOIN paths ON files.paths_id=paths.id\n" .
 423                  "WHERE files.id=?\n" .
 424                  "GROUP BY paths.name, files.name");
 425         print "FILENAME SQL: $sql\n";
 426         my ($path, $name)=$self->cmd_onerow($sql, $id);
 427         my $id3fs_path=join('/', map { $_->{name}; }  @constraints);
 428         return($self->relativise($path, $name, $mountpoint, $id3fs_path));
 429     }
 430     die("DB::filename: unhandled case\n"); #FIXME
 431 }
 432
 433 sub tags_subselect
 434 {
 435     my($self, $path)=@_;
 436     my $tree=$path->{tagtree};
 437     my $hasvals=$path->tag_has_values();
 438     my $parent=$path->trailing_tag_parent();
 439
 440     my $tag=undef;
 441     if($hasvals)
 442     {
 443         $tag=$path->trailing_tag_id();
 444         print "Trailing id: $tag\n";
 445     }
 446     my ($sqlclause, $joinsneeded)=(undef, 1);
 447     ($sqlclause, $joinsneeded) = $tree->to_sql($tag) if($tree);
 448     print "SQL($joinsneeded): $sqlclause\n";
 449     my $sql="\tSELECT fxt1.files_id FROM tags t1";
 450     my @crosses=();
 451     my @inners=();
 452 #    $joinsneeded++ if($tag);
 453     for(my $i=1; $i <= $joinsneeded; $i++)
 454     {
 455         if($i > 1)
 456         {
 457             push(@crosses, "CROSS JOIN tags t$i");
 458         }
 459         my $inner=("\tINNER JOIN files_x_tags fxt$i ON " .
 460                    "t${i}.id=fxt${i}.tags_id");
 461         if($i>2)
 462         {
 463             $inner .= " AND fxt1.files_id=fxt${i}.files_id";
 464         }
 465         push(@inners, $inner);
 466     }
 467     $sql .= ("\n\t" . join(" ", @crosses)) if(@crosses);
 468     $sql .= ("\n" . join("\n", @inners)) if(@inners);
 469     $sql .= "\n\tWHERE $sqlclause" if($sqlclause);
 470 #    if($tag)
 471 #    {
 472 #       $sql .= " AND t${joinsneeded}.parents_id='$tag'";
 473 #    }
 474     $sql .= "\n\tGROUP BY fxt1.files_id\n";
 475     return $sql;
 476 }
 477
 478 sub tags_subselect_and_not
 479 {
 480     my($self,@constraints)=@_;
 481     my ($tags, $tags_vals, $parent)=$self->constraints_tag_list(@constraints);
 482     my @tags=@$tags;
 483     my @tags_vals=@$tags_vals;;
 484     my $cnt=1;
 485     my @andclauses=();
 486     my $sql='';
 487     for my $tag (@tags)
 488     {
 489         if($cnt == 1)
 490         {
 491             $sql="\tSELECT fxt" . scalar(@tags) . ".files_id FROM files_x_tags fxt1\n";
 492             push(@andclauses, "\t\tfxt${cnt}.tags_id=$tag");
 493         }
 494         else
 495         {
 496             $sql .= ("\tLEFT JOIN files_x_tags fxt$cnt ON fxt" .
 497                  ($cnt-1) . ".files_id=fxt${cnt}.files_id\n");
 498             push(@andclauses, "\t\tfxt${cnt}.tags_id IS NULL");
 499         }
 500         print "AND: @andclauses\n";
 501         $cnt++;
 502     }
 503     if(@andclauses)
 504     {
 505         $sql .= "\tWHERE\n\t\t";
 506         $sql .= join(" AND\n\t\t", @andclauses) . "\n";
 507     }
 508     $sql .= "\tGROUP BY fxt". scalar(@tags).".files_id\n";
 509     return $sql;
 510 }
 511
 512
 513 sub tags_subselect_and
 514 {
 515     my($self,@constraints)=@_;
 516     my ($tags, $tags_vals, $parent)=$self->constraints_tag_list(@constraints);
 517     my @tags=@$tags;
 518     my @tags_vals=@$tags_vals;;
 519     my $cnt=1;
 520     my @andclauses=();
 521     my $sql='';
 522     for my $tag (@tags)
 523     {
 524         if($cnt == 1)
 525         {
 526             $sql="\tSELECT fxt" . scalar(@tags) . ".files_id FROM files_x_tags fxt1\n";
 527         }
 528         else
 529         {
 530             $sql .= ("\tINNER JOIN files_x_tags fxt$cnt ON fxt" .
 531                  ($cnt-1) . ".files_id=fxt${cnt}.files_id\n");
 532         }
 533         push(@andclauses, "\t\tfxt${cnt}.tags_id = $tag");
 534         print "AND: @andclauses\n";
 535         $cnt++;
 536     }
 537     if(@andclauses)
 538     {
 539         $sql .= "\tWHERE\n\t\t";
 540         $sql .= join(" AND\n\t\t", @andclauses) . "\n";
 541     }
 542     $sql .= "\tGROUP BY fxt". scalar(@tags).".files_id\n";
 543     return $sql;
 544 }
 545
 546 sub tags_subselect_or
 547 {
 548     my($self,@constraints)=@_;
 549     my ($tags, $tags_vals, $parent)=$self->constraints_tag_list(@constraints);
 550     my @tags=@$tags;
 551     my @tags_vals=@$tags_vals;;
 552
 553     my $sql=("\tSELECT files_x_tags.files_id FROM tags t1\n" .
 554              "\tINNER JOIN files_x_tags ON t1.id=files_x_tags.tags_id\n");
 555     my @orclauses=();
 556     my @andclauses=();
 557     # FIXME: and / or?
 558     if(@tags)
 559     {
 560         push(@andclauses, "( t1.parents_id=" . (defined($parent) ? $parent : "''") . " )");
 561         push(@andclauses, "( t1.id IN ( " . join(', ', @tags) ." ) )");
 562     }
 563     for my $pair (@tags_vals)
 564     {
 565         my($tag, $val)=@$pair;
 566         push(@orclauses, "( t1.parents_id=$tag AND t1.id=$val )");
 567     }
 568 #    push(@andclauses, "( t1.parents_id=" . (defined($parent) ? $parent : "''") . " )");
 569     if(@orclauses)
 570     {
 571         push(@andclauses, join("\n\t\tOR ", @orclauses));
 572     }
 573     if(@andclauses)
 574     {
 575         $sql .= "\tWHERE\n\t\t";
 576         $sql .= join("\n\t\tAND ", @andclauses) . "\n";
 577     }
 578     $sql .= "\tGROUP BY files_x_tags.files_id\n";
 579     return $sql;
 580 }
 581
 582 sub constraints_tag_list
 583 {
 584     my($self, @constraints)=@_;
 585     my $lasttag=undef;
 586     my @tags=();
 587     my @tags_vals=();
 588     for my $constraint (@constraints)
 589     {
 590 #       print ref($constraint), ": ", $constraint->{name}, "\n";
 591         if(ref($constraint) eq "ID3FS::PathElement::Tag")
 592         {
 593             if(defined($lasttag))
 594             {
 595 #               print "TAGVAL\n";
 596                 push(@tags_vals, [$lasttag, $constraint->{id}]) if defined($constraint->{id});
 597                 $lasttag=undef;
 598             }
 599             elsif($self->tag_has_values($constraint->{id}))
 600             {
 601 #               print "HASVALUES\n";
 602                 $lasttag=$constraint->{id} if defined($constraint->{id});
 603             }
 604             else
 605             {
 606 #               print "NOVALUES\n";
 607                 push(@tags, $constraint->{id}) if(defined($constraint->{id}));
 608             }
 609         }
 610     }
 611     unless($self->{postgres})
 612     {
 613         @tags=map{ "\"$_\""; } @tags;
 614         @tags_vals=map( { [ map({ "\"$_\""; } @$_ ) ] } @tags_vals);
 615         $lasttag="\"$lasttag\"" if defined($lasttag);
 616     }
 617     return(\@tags, \@tags_vals, $lasttag);
 618 }
 619
 620
 621 sub relativise
 622 {
 623     my($self, $path, $name, $mountpoint, $id3fs_path)=@_;
 624     $id3fs_path=~s/(.*)\/.*/$1/;
 625     my $rpath="$self->{absbase}/$path";
 626     my $vpath="$mountpoint/$id3fs_path";
 627     my @path=split(/\//,$rpath);
 628     my @rel=split(/\//,$vpath);
 629     #absolute paths have empty first element due to leading /
 630     shift(@path) if($path[0] eq "");
 631     shift(@rel)  if($rel[0]  eq "");
 632     if($path[0] ne $rel[0])
 633     {
 634         #no path in common, return absolute
 635         print "FAIL: NO PATHS IN COMMON\n";
 636         return $name;
 637     }
 638     # f: /home/foo/bar/baz.mp3
 639     # r: /home/ianb/music/albums
 640     while(@path && @rel && ($path[0] eq $rel[0]))
 641     {
 642         shift(@path);
 643         shift(@rel);
 644 #       print "POP ";
 645     }
 646 #    print "\n";
 647     my $upcount=scalar(@rel);
 648     my $result="../" x $upcount;
 649     $result .= join("/",@path);
 650     $result .= "/$name";
 651     return $result;
 652 }
 653
 654 sub bare_tags
 655 {
 656     my($self)=@_;
 657     my $sql=("SELECT tags.name FROM tags\n" .
 658              "WHERE tags.parents_id=''\n" .
 659              "GROUP BY tags.name\n");
 660     my $result=$self->cmd_rows($sql);
 661     my @names=map { $_->[0]; } @$result;
 662     return (@names);
 663 }
 664
 665 sub tags_with_values
 666 {
 667     # FIXME: only shows one level of tag depth
 668     my($self)=@_;
 669     my $sql=("SELECT p.name, t.name  FROM tags t\n" .
 670              "INNER JOIN tags p ON t.parents_id=p.id\n" .
 671              "GROUP BY p.name, t.name\n");
 672     print "SQL: $sql\n";
 673     my $result=$self->cmd_rows($sql);
 674     my $tags={};
 675     for my $pair (@$result)
 676     {
 677         push(@{$tags->{$pair->[0]}}, $pair->[1]);
 678     }
 679     return $tags;
 680 }
 681
 682 sub id
 683 {
 684     my($self, $type, $val)=@_;
 685     my $sql="SELECT id FROM $type WHERE name=?";
 686     my ($id)=$self->cmd_onerow($sql, $val);
 687     return($id);
 688 }
 689
 690 sub add
 691 {
 692     my($self,$path)=@_;
 693     my $relpath=$path;
 694     $relpath =~ s/^\Q$self->{base}\E\/?//;
 695     my($filepart,$pathpart);
 696     if($relpath !~ /\//)
 697     {
 698         $pathpart='';
 699         $filepart=$relpath;
 700     }
 701     else
 702     {
 703         ($pathpart, $filepart) = ($relpath =~ /(.*)\/(.*)/);
 704     }
 705     my $file=ID3FS::AudioFile->new($path, $self->{me});
 706     return unless(defined($file));
 707     my $artist=$file->artist();
 708     my $album=$file->album();
 709     my $v1genre=$file->v1genre();
 710     my $year=$file->year();
 711     my $audiotype=$file->audiotype();
 712     my @tags=$file->tags();
 713     my $haspic=$file->haspic();
 714
 715     $artist=undef unless($self->ok($artist));
 716     print "$self->{me}: $path: no artist tag defined\n" unless(defined($artist));
 717     my $artist_id=$self->add_to_table("artists",  $artist);
 718     my $path_id=$self->add_to_table("paths", $pathpart);
 719     $album=undef unless($self->ok($album));
 720     if($self->{verbose} && !defined($album))
 721     {
 722         print "$self->{me}: $path: no album tag defined\n";
 723     }
 724
 725     my $albums_id=$self->add_to_table("albums", $album);
 726     my $file_id=$self->add_to_table("files", $filepart,
 727                                     { "artists_id" => $artist_id,
 728                                       "albums_id"  => $albums_id,
 729                                       "paths_id"   => $path_id });
 730     for my $tag (@tags)
 731     {
 732         $self->add_tag($file_id, @$tag);
 733     }
 734
 735     if($self->ok($year))
 736     {
 737         $self->add_tag($file_id, "year", $year);
 738         if($year=~/^(\d\d\d)\d$/)
 739         {
 740             $self->add_tag($file_id, "decade", "${1}0s");
 741         }
 742     }
 743
 744     if($self->ok($v1genre))
 745     {
 746         $self->add_tag($file_id, "v1genre", $v1genre);
 747     }
 748
 749     if($haspic)
 750     {
 751         $self->add_tag($file_id, "haspic", undef);
 752     }
 753 }
 754
 755 sub add_tag
 756 {
 757     my($self, $file_id, $tag, $value)=@_;
 758     my $tag_id=$self->add_to_table("tags",  $tag,
 759                                    { "parents_id" => undef });
 760     $self->add_relation("files_x_tags",
 761                         { "files_id" => $file_id,
 762                           "tags_id"  => $tag_id });
 763     if(defined($value) && length($value))
 764     {
 765         my $val_id=$self->add_to_table("tags",  $value,
 766                                        { "parents_id" => $tag_id });
 767         $self->add_relation("files_x_tags",
 768                             { "files_id" => $file_id,
 769                               "tags_id"  => $val_id });
 770     }
 771 }
 772
 773 sub add_to_table
 774 {
 775     my($self, $table, $name, $extradata)=@_;
 776     my $id=$self->lookup_id($table, $name);
 777     unless(defined($id))
 778     {
 779         my $sql="INSERT INTO $table (";
 780         $sql .= "id, " if($self->{postgres});
 781         my @fields=qw(name);
 782         if(defined($extradata))
 783         {
 784             push(@fields, sort keys(%$extradata));
 785         }
 786         $sql .= join(", ", @fields);
 787         $sql .=") VALUES (";
 788         $sql .=") nextval('seq'), " if($self->{postgres});
 789         $sql .= join(", ", map { "?"; } @fields);
 790         $sql .= ");";
 791         $id=$self->cmd_id($sql, $name, map { $extradata->{$_} || ""; } sort keys %$extradata);
 792     }
 793     return $id;
 794 }
 795
 796 sub add_relation
 797 {
 798     my ($self, $relname, $fields)=@_;
 799     return if($self->relation_exists($relname, $fields));
 800     my $sql="INSERT INTO $relname (";
 801     $sql .= join(", ", sort keys(%$fields));
 802     $sql .= ") VALUES (";
 803     $sql .= join(", ", map { "?"; } sort keys(%$fields));
 804     $sql .= ");";
 805     $self->cmd($sql, map { $fields->{$_}; } sort keys(%$fields));
 806 }
 807
 808 sub lookup_id
 809 {
 810     my($self, $table, $name)=@_;
 811     my($id)=$self->cmd_onerow("SELECT id FROM $table where name=?", $name);
 812     return $id;
 813 }
 814
 815 sub tag_has_values
 816 {
 817     my($self, $id)=@_;
 818     my $sql=("SELECT COUNT(*) FROM tags\n\t" .
 819              "WHERE tags.parents_id=?\n");
 820     my ($rows)=$self->cmd_onerow($sql, $id);
 821     return $rows;
 822 }
 823
 824 sub files_in
 825 {
 826     my ($self, $dir)=@_;
 827     $dir=~s/^$self->{base}\/?//;
 828     my $sql=("SELECT files.name FROM files\n" .
 829              "INNER JOIN paths ON files.paths_id=paths.id\n" .
 830              "WHERE paths.name=?\n");
 831     my $files=$self->cmd_rows($sql, $dir);
 832     return(map { $_->[0]; } @$files);
 833 }
 834
 835 sub prune_directories
 836 {
 837     my($self)=@_;
 838     my $sql=("SELECT name, id FROM paths ORDER BY name\n");
 839     my $pathsref=$self->cmd_rows($sql);
 840     my @ids=();
 841     for my $pathpair (@$pathsref)
 842     {
 843         my($path, $id)=@$pathpair;
 844         my $fullpath="$self->{absbase}/$path";
 845         unless(-d $fullpath)
 846         {
 847             push(@ids, $id)
 848         }
 849     }
 850     $self->prune_paths(@ids);
 851     return scalar(@ids);
 852 }
 853
 854 sub prune_paths
 855 {
 856     my($self, @ids)=@_;
 857     return unless(@ids);
 858     my $sql=("DELETE FROM files WHERE paths_id IN (\n\t" .
 859              join(', ', map { "\"$_\""; } @ids). "\n\t)");
 860     print "SQL: \n", $sql, "\n";
 861     $self->cmd($sql);
 862 }
 863
 864 sub remove_unused
 865 {
 866     my($self)=@_;
 867     my $sql=<<'EOT';
 868    DELETE FROM artists WHERE id IN (
 869        SELECT artists.id FROM artists
 870        LEFT JOIN files ON files.artists_id=artists.id
 871        WHERE files.id IS NULL);
 872
 873    DELETE FROM albums WHERE id IN (
 874        SELECT albums.id FROM albums
 875        LEFT JOIN files ON files.albums_id=albums.id
 876        WHERE files.id IS NULL);
 877
 878    DELETE FROM paths WHERE id IN (
 879        SELECT paths.id FROM paths
 880        LEFT JOIN files ON files.paths_id=paths.id
 881        WHERE files.id IS NULL);
 882
 883    DELETE FROM files_x_tags WHERE files_id IN (
 884        SELECT files_x_tags.files_id FROM files_x_tags
 885        LEFT JOIN files ON files.id=files_x_tags.files_id
 886        WHERE files.id IS NULL);
 887
 888    DELETE FROM tags WHERE id IN (
 889        SELECT tags.id FROM tags
 890        LEFT JOIN files_x_tags ON files_x_tags.tags_id=tags.id
 891        WHERE files_x_tags.files_id IS NULL);
 892
 893     VACUUM
 894 EOT
 895     print "SQL: $sql\n";
 896     my @sql=split(/\n\n/, $sql);
 897     $self->cmd($_) for (@sql);
 898 }
 899
 900 sub relation_exists
 901 {
 902     my ($self, $relname, $fields)=@_;
 903     my $sql="SELECT count(1) FROM $relname WHERE ";
 904     my @exprs=();
 905     my @vals=();
 906     for my $field (keys %$fields)
 907     {
 908         push(@exprs,$field);
 909         push(@vals,$fields->{$field});
 910     }
 911     $sql .= join(' AND ', map { "$_=?"; } @exprs);
 912     my ($ret)=$self->cmd_onerow($sql, @vals);
 913     return $ret;
 914 }
 915
 916 sub ok
 917 {
 918     my($self, $thing)=@_;
 919     return(defined($thing) && length($thing) && $thing =~ /\S+/);
 920 }
 921
 922 sub cmd
 923 {
 924     my ($self, @args)=@_;
 925     # don't care about retcode
 926     $self->cmd_sth(@args);
 927 }
 928
 929 sub cmd_onerow
 930 {
 931     my ($self, @args)=@_;
 932     my $sth=$self->cmd_sth(@args);
 933     return($sth->fetchrow_array());
 934 }
 935
 936 sub cmd_rows
 937 {
 938     my ($self, @args)=@_;
 939     my $sth=$self->cmd_sth(@args);
 940     return $sth->fetchall_arrayref();
 941 }
 942
 943 sub cmd_id
 944 {
 945     my ($self, @args)=@_;
 946     $self->cmd_sth(@args);
 947     return($self->last_insert_id());
 948 }
 949
 950 sub last_insert_id
 951 {
 952     my $self=shift;
 953     if($self->{postgres})
 954     {
 955         return $self->{dbh}->last_insert_id(undef, undef, undef, undef,
 956                                             { sequence => "seq" });
 957     }
 958     else
 959     {
 960         return $self->{dbh}->last_insert_id("","","","");
 961     }
 962 }
 963
 964 __DATA__
 965
 966 CREATE TABLE id3fs (
 967     schema_version INTEGER,
 968     last_update
 969 );
 970
 971 CREATE TABLE paths (
 972     id INTEGER PRIMARY KEY,
 973     name text
 974 );
 975
 976 CREATE TABLE artists (
 977     id INTEGER PRIMARY KEY,
 978     name text
 979 );
 980
 981 CREATE TABLE albums (
 982     id INTEGER PRIMARY KEY,
 983     name text
 984 );
 985
 986 CREATE TABLE files (
 987     id INTEGER PRIMARY KEY,
 988     name text,
 989     artists_id,
 990     albums_id,
 991     paths_id,
 992     FOREIGN KEY(artists_id) REFERENCES artists(id) ON DELETE CASCADE ON UPDATE CASCADE,
 993     FOREIGN KEY(albums_id)  REFERENCES albums(id)  ON DELETE CASCADE ON UPDATE CASCADE,
 994     FOREIGN KEY(paths_id)   REFERENCES paths(id)   ON DELETE CASCADE ON UPDATE CASCADE
 995 );
 996
 997 CREATE TABLE tags (
 998     id INTEGER PRIMARY KEY,
 999     parents_id INTEGER,
1000     name text
1001 );
1002
1003 CREATE TABLE files_x_tags (
1004     files_id INTEGER,
1005     tags_id INTEGER,
1006     FOREIGN KEY(files_id) REFERENCES files(id) ON DELETE CASCADE ON UPDATE CASCADE,
1007     FOREIGN KEY(tags_id)  REFERENCES tags(id)  ON DELETE CASCADE ON UPDATE CASCADE
1008 );
1009