lib/ID3FS/Path.pm

   1 package ID3FS::Path;
   2
   3 use strict;
   4 use warnings;
   5 use ID3FS::PathElement::Artist;
   6 use ID3FS::PathElement::Album;
   7 use ID3FS::PathElement::Boolean;
   8 use ID3FS::PathElement::File;
   9 use ID3FS::PathElement::Tag;
  10 use ID3FS::PathElement::Tagval;
  11 use ID3FS::Path::Node;
  12
  13 our ($STATE_INVALID, $STATE_ROOT, $STATE_TAG, $STATE_TAGVAL,
  14      $STATE_BOOLEAN, $STATE_ALBUMS, $STATE_TRACKLIST,
  15      $STATE_FILE, $STATE_ALL)=(0..8);
  16
  17 our %priorities=( "OR" => 0, "AND" => 1, "NOT" => 2 );
  18
  19 sub new
  20 {
  21     my $proto=shift;
  22     my $class=ref($proto) || $proto;
  23     my $self={};
  24     bless($self,$class);
  25
  26     $self->{elements}=[];
  27     $self->{db}=shift;
  28     $self->{path}=shift;
  29     $self->parse();
  30 #    print "STATE: ", $self->state(), "\n";
  31     return $self;
  32 }
  33
  34 sub isdir
  35 {
  36     my($self)=@_;
  37     if(($self->state() == $STATE_FILE) ||
  38        ($self->state() == $STATE_INVALID))
  39     {
  40         return 0;
  41     }
  42     return 1;
  43 }
  44
  45 sub isfile
  46 {
  47     my($self)=@_;
  48     return 1 if($self->state() == $STATE_FILE);
  49     return 0;
  50 }
  51
  52 sub isvalid
  53 {
  54     my($self)=@_;
  55     return($self->state() != $STATE_INVALID);
  56 }
  57
  58 sub dest
  59 {
  60     my($self, $mountpoint)=@_;
  61     if($self->state() == $STATE_FILE)
  62     {
  63         return $self->filename($mountpoint, $self);
  64     }
  65     return "ERROR"; #should never happen?
  66 }
  67
  68 sub dirents
  69 {
  70     my($self)=@_;
  71     my @dents=();
  72     my $state=$self->state();
  73 #    print "DIRENTS: STATE: $state\n";
  74 #    print "DIRENTS: FILE: $self->{path}\n";
  75     if($state==$STATE_ALL)
  76     {
  77         push(@dents, qw(TRACKS NOARTIST), $self->artists());
  78     }
  79     elsif($state==$STATE_TAG || $state==$STATE_TAGVAL)
  80     {
  81         my $tag=$self->tail();
  82         if($state==$STATE_TAG &&
  83            defined($tag) &&
  84            ref($tag) eq "ID3FS::PathElement::Tag" &&
  85            $self->{db}->tag_has_values($tag->{id}))
  86         {
  87             @dents=$self->tags();
  88         }
  89         else
  90         {
  91             push(@dents, qw(AND OR TRACKS NOARTIST), $self->artists());
  92         }
  93     }
  94     elsif($state==$STATE_BOOLEAN)
  95     {
  96         my $parent=$self->tail();
  97         unless(defined($parent) &&
  98                ref($parent) eq "ID3FS::PathElement::Boolean" &&
  99                $parent->{name} eq "NOT")
 100         {
 101             push(@dents, "NOT");
 102         }
 103         push(@dents, $self->tags());
 104     }
 105     elsif($state==$STATE_ROOT)
 106     {
 107         @dents=(qw(ALL NOT), $self->tags());
 108     }
 109     elsif($state==$STATE_ALBUMS)
 110     {
 111         @dents=(qw(TRACKS NOALBUM),$self->albums());
 112     }
 113     elsif($state==$STATE_TRACKLIST)
 114     {
 115         @dents=$self->tracks();
 116     }
 117     else
 118     {
 119         print "DIRENTS: UNHANDLED STATE: $state\n";
 120     }
 121     return(@dents);
 122 }
 123
 124 sub parse
 125 {
 126     my($self)=@_;
 127     @{$self->{components}}=split(/\//, $self->{path});
 128     shift @{$self->{components}}; # drop empty field before leading /
 129 #    print "PATH: $self->{path}\n";
 130     $self->state($STATE_ROOT);
 131     return if($self->{path} eq "/");
 132     my @parts=@{$self->{components}};
 133     my($tag, $tagval);
 134     $self->{elements}=[];
 135     $self->{bare_not}=0;
 136     my $root_not=0;
 137     my $tags_seen=0;
 138     while(defined(my $name=shift @parts))
 139     {
 140 #       print "NAME: $name\n";
 141         my $state=$self->state();
 142         if($state==$STATE_INVALID)
 143         {
 144 #           print "SM: INVALID: $name\n";
 145             return;
 146         }
 147         elsif($state==$STATE_ROOT)
 148         {
 149 #           print "SM: ROOT: $name\n";
 150             if($name eq "ALL")
 151             {
 152                 $self->state($STATE_ALL);
 153             }
 154             elsif($name eq "NOT")
 155             {
 156                 $root_not=1;
 157                 push(@{$self->{elements}}, ID3FS::PathElement::Boolean->new($self->{db}, $name));
 158                 $self->state($STATE_BOOLEAN);
 159             }
 160             else
 161             {
 162                 $tag=ID3FS::PathElement::Tag->new($self->{db}, $name);
 163                 if($tag)
 164                 {
 165                     push(@{$self->{elements}}, $tag);
 166                     $tags_seen++;
 167                     $self->state($STATE_TAG);
 168                 }
 169                 else
 170                 {
 171                     $self->state($STATE_INVALID);
 172                 }
 173             }
 174         }
 175         elsif($state==$STATE_TAG || $state==$STATE_TAGVAL)
 176         {
 177 #           print "SM: TAG/TAGVAL($state): $name\n";
 178             my $tag=$self->tail();
 179             if($state==$STATE_TAG &&
 180                defined($tag) &&
 181                ref($tag) eq "ID3FS::PathElement::Tag" &&
 182                $self->{db}->tag_has_values($tag->{id}))
 183             {
 184 #               print "Parsing: parent: $tag->{id}\n";
 185                 my $tagval=ID3FS::PathElement::Tag->new($self->{db}, $name, $tag->{id});
 186                 if(defined($tagval))
 187                 {
 188                     $self->state($STATE_TAGVAL);
 189                     # stay in tag state
 190                     push(@{$self->{elements}}, $tagval);
 191                 }
 192                 else
 193                 {
 194                     $self->state($STATE_INVALID);
 195                 }
 196             }
 197             elsif($name eq "TRACKS")
 198             {
 199                 $self->state($STATE_TRACKLIST);
 200             }
 201             elsif($name eq "NOARTIST")
 202             {
 203                 $self->state($STATE_TRACKLIST);
 204             }
 205             elsif($name eq "AND")
 206             {
 207                 $self->state($STATE_BOOLEAN);
 208                 push(@{$self->{elements}}, ID3FS::PathElement::Boolean->new($self->{db}, $name));
 209             }
 210             elsif($name eq "OR")
 211             {
 212                 $self->state($STATE_BOOLEAN);
 213                 push(@{$self->{elements}}, ID3FS::PathElement::Boolean->new($self->{db}, $name));
 214             }
 215             else
 216             {
 217                 my $artist=ID3FS::PathElement::Artist->new($self->{db}, $name);
 218                 if($artist)
 219                 {
 220                     push(@{$self->{elements}}, $artist);
 221                     $self->state($STATE_ALBUMS);
 222                 }
 223                 else
 224                 {
 225                     $self->state($STATE_INVALID);
 226                 }
 227             }
 228         }
 229         elsif($state==$STATE_BOOLEAN)
 230         {
 231 #           print "SM: BOOLEAN: $name\n";
 232             my $parent=$self->tail();
 233             my $allownot=1;
 234             if(defined($parent) &&
 235                ref($parent) eq "ID3FS::PathElement::Boolean" &&
 236                $parent->{name} eq "NOT")
 237             {
 238                 $allownot=0;
 239             }
 240             if($allownot && $name eq "NOT")
 241             {
 242                 $self->state($STATE_BOOLEAN);
 243                 push(@{$self->{elements}}, ID3FS::PathElement::Boolean->new($self->{db}, $name));
 244             }
 245             else
 246             {
 247                 my $tag=ID3FS::PathElement::Tag->new($self->{db}, $name);
 248                 if($tag)
 249                 {
 250                     push(@{$self->{elements}}, $tag);
 251                     $tags_seen++;
 252                     $self->state($STATE_TAG);
 253                 }
 254                 else
 255                 {
 256                     $self->state($STATE_INVALID);
 257                 }
 258             }
 259         }
 260         elsif($state==$STATE_ALBUMS)
 261         {
 262 #           print "SM: ALBUM: $name\n";
 263             if($name eq "TRACKS")
 264             {
 265                 $self->state($STATE_TRACKLIST);
 266             }
 267             elsif($name eq "NOALBUM")
 268             {
 269                 $self->state($STATE_TRACKLIST);
 270             }
 271             else
 272             {
 273                 my $album=ID3FS::PathElement::Album->new($self->{db}, $name);
 274                 if($album)
 275                 {
 276                     push(@{$self->{elements}}, $album);
 277                     $self->state($STATE_TRACKLIST);
 278                 }
 279                 else
 280                 {
 281                     $self->state($STATE_INVALID);
 282                 }
 283             }
 284         }
 285         elsif($state==$STATE_TRACKLIST)
 286         {
 287 #           print "SM: TRACKLIST: $name\n";
 288             my $track=ID3FS::PathElement::File->new($self->{db}, $name);
 289             if($track)
 290             {
 291                 push(@{$self->{elements}}, $track);
 292                 $self->state($STATE_FILE);
 293             }
 294             else
 295             {
 296                 $self->state($STATE_INVALID);
 297             }
 298         }
 299         elsif($state==$STATE_FILE)
 300         {
 301 #           print "SM: FILE: $name\n";
 302             # Can't have anything after a filename
 303             $self->state($STATE_INVALID);
 304         }
 305         elsif($state==$STATE_ALL)
 306         {
 307             if($name eq "TRACKS")
 308             {
 309                 $self->state($STATE_TRACKLIST);
 310             }
 311             elsif($name eq "NOARTIST")
 312             {
 313                 $self->state($STATE_TRACKLIST);
 314             }
 315             else
 316             {
 317                 my $artist=ID3FS::PathElement::Artist->new($self->{db}, $name);
 318                 if($artist)
 319                 {
 320                     push(@{$self->{elements}}, $artist);
 321                     $self->state($STATE_ALBUMS);
 322                 }
 323                 else
 324                 {
 325                     $self->state($STATE_INVALID);
 326                 }
 327             }
 328         }
 329         else
 330         {
 331             print "SM: ERROR: UNKNOWN STATE: $self->{state}\n";
 332             $self->state($STATE_INVALID);
 333         }
 334     }
 335
 336     print "ROOT_NOT: $root_not TAGS_SEEN: $tags_seen\n";
 337     if($root_not && ($tags_seen < 2))
 338     {
 339         $self->{bare_not}=1;
 340     }
 341
 342     # remove trailing boolean
 343     my @elements=@{$self->{elements}};
 344     while(@elements && ref($elements[$#elements]) eq "ID3FS::PathElement::Boolean")
 345     {
 346         pop @elements;
 347     }
 348     # sort elements by precedence
 349     @elements=$self->sort_elements(@elements);
 350     $self->{tagtree}=$self->elements_to_tree(\@elements);
 351     if($self->{tagtree})
 352     {
 353         ($self->{sqlconditions},
 354          $self->{joins}) = $self->{tagtree}->to_sql();
 355 #       print "TREE: ",  $self->{tagtree}->print(), "\n";
 356 #       print("SQL CONDITION(", scalar(@{$self->{joins}}), "): ",
 357 #             $self->{sqlconditions}, "\n");
 358 #       use Data::Dumper;
 359 #       print Dumper $self->{tagtree};
 360     }
 361 }
 362
 363 sub state
 364 {
 365     my($self, $newstate)=@_;
 366     $self->{state}=$newstate if(defined($newstate));
 367     return $self->{state};
 368 }
 369
 370 sub elements_to_tree
 371 {
 372     my($self, $elements)=@_;
 373     return undef unless(@$elements);
 374     my ($left, $right, $op)=(undef, undef, undef);
 375     my $thing=pop @$elements;
 376     if(ref($thing) eq "ID3FS::PathElement::Boolean")
 377     {
 378         my $op=$thing;
 379         $right=$self->elements_to_tree($elements);
 380         if($op->{name} ne "NOT")
 381         {
 382             $left=$self->elements_to_tree($elements);
 383         }
 384         return ID3FS::Path::Node->new($left, $op, $right);
 385     }
 386     else
 387     {
 388         return ID3FS::Path::Node->new($thing);
 389     }
 390 }
 391
 392 # Dijkstra's shunting-yard algorithm
 393 sub sort_elements
 394 {
 395     my ($self, @input)=@_;
 396     my @opstack=();
 397     my @output=();
 398 #    print "INPUT: ", join(', ', map { $_->{name}; } @input), "\n";
 399     while(my $thing = shift @input)
 400     {
 401         if(ref($thing) eq "ID3FS::PathElement::Tag")
 402         {
 403             # Handle tag values by dropping parent
 404             if(@input && ref($input[0]) eq "ID3FS::PathElement::Tag")
 405             {
 406                 $thing=shift @input;
 407             }
 408             push(@output, $thing);
 409         }
 410         elsif(ref($thing) eq "ID3FS::PathElement::Boolean")
 411         {
 412             # bool
 413             while(@opstack &&
 414                   ($priorities{$thing->{name}} <= $priorities{$opstack[$#opstack]->{name}}))
 415             {
 416                 push(@output, pop(@opstack));
 417             }
 418             push(@opstack, $thing);
 419         }
 420     }
 421     while(@opstack)
 422     {
 423         push(@output, pop(@opstack));
 424     }
 425 #    print "STACK: ", join(', ', map { $_->{name}; } @output), "\n";
 426     return @output;
 427 }
 428
 429 sub used_tags
 430 {
 431     my($self)=@_;
 432     return() unless(defined($self->{tagtree}));
 433     return($self->{tagtree}->used_tags());
 434 }
 435
 436 sub tag_has_values
 437 {
 438     my($self)=@_;
 439     my $tail=$self->tail();
 440     if($tail && ref($tail) eq "ID3FS::PathElement::Tag")
 441     {
 442         return($self->{db}->tag_has_values($tail->{id}));
 443     }
 444 }
 445
 446 sub trailing_tag_id
 447 {
 448     my($self)=@_;
 449     my $tail=$self->tail();
 450     if($tail && ref($tail) eq "ID3FS::PathElement::Tag")
 451     {
 452         return($tail->{id});
 453     }
 454     return undef;
 455 }
 456
 457 sub trailing_tag_parent
 458 {
 459     my($self)=@_;
 460     my $tail=$self->tail();
 461     if($tail && ref($tail) eq "ID3FS::PathElement::Tag")
 462     {
 463         return($tail->{parents_id});
 464     }
 465     return undef;
 466 }
 467
 468 sub tail
 469 {
 470     my($self)=@_;
 471     return($self->{elements}->[$#{$self->{elements}}]);
 472 }
 473
 474 # the one before last
 475 sub tail_parent
 476 {
 477     my($self)=@_;
 478     return($self->{elements}->[($#{$self->{elements}}) - 1]);
 479 }
 480
 481 ######################################################################
 482
 483 sub tags
 484 {
 485     my($self)=@_;
 486     if(!$self->{tagtree}) # / or /NOT
 487     {
 488         my $sql="SELECT DISTINCT name FROM tags WHERE parents_id='';";
 489         return($self->{db}->cmd_firstcol($sql));
 490     }
 491     my $hasvals=$self->tag_has_values();
 492     my $parent=$self->trailing_tag_parent();
 493 #    print "THASVALS: $hasvals\n";
 494 #    print "TPARENT: ", (defined($parent)? $parent : "NO"), "\n";
 495     my @ids=();
 496     my $sql=("SELECT tags.name FROM (\n" .
 497              $self->tags_subselect() .
 498              ") AS subselect\n" .
 499              "INNER JOIN files_x_tags ON subselect.files_id=files_x_tags.files_id\n" .
 500              "INNER JOIN tags ON files_x_tags.tags_id=tags.id\n");
 501     my @allused=$self->used_tags();
 502     my @used=grep { ref($_) ne "ARRAY"; } @allused;
 503     my @used_with_vals=grep { ref($_) eq "ARRAY"; } @allused;
 504 #    print "tags(): USED: ", join(", ", @used), "\n";
 505 #    print "tags(): USED_WITH_VALS: ", join(", ", map { "[".$_->[0]. ", ".$_->[1]."]";} @used_with_vals), "\n";
 506     my @orclauses=();
 507     my @andclauses=();
 508     my $id=$self->trailing_tag_id();
 509     if($hasvals)
 510     {
 511 #       print "HAS_VALUES\n";
 512         my @values=map { "'".$_->[1]."'"; } grep { $_->[0] == $id; } @used_with_vals;
 513         my $clause="(tags.parents_id='$id'";
 514         if(@values)
 515         {
 516             $clause .= " AND tags.id NOT IN (" . join(', ', @values) . ")";
 517         }
 518         $clause .= ")";
 519         push(@andclauses, $clause);
 520     }
 521     else
 522     {
 523 #       print "HASNT VALUES\n";;
 524         if(@used)
 525         {
 526             push(@andclauses, "(NOT (tags.parents_id='' AND tags.id IN (" . join(', ', @used) . ")))");
 527         }
 528         for my $pair (@used_with_vals)
 529         {
 530             push(@andclauses, "(NOT (tags.parents_id='" . $pair->[0] . "' AND tags.id='" . $pair->[1] . "'))");
 531         }
 532     }
 533
 534     my $parentclause= "(tags.parents_id='";
 535     if($hasvals)
 536     {
 537         $parentclause .= $id;
 538     }
 539     elsif($parent)
 540     {
 541         $parentclause .= $parent;
 542     }
 543     $parentclause .= "')";
 544     push(@andclauses, $parentclause);
 545
 546     if(@orclauses)
 547     {
 548         push(@andclauses, '( ' . join(' OR ', @orclauses) . ' )');
 549     }
 550     if(@andclauses)
 551     {
 552         $sql .= "WHERE " . join(' AND ', @andclauses) . "\n";
 553     }
 554     $sql .= "GROUP BY tags.name;";
 555     print "SQL(TAGS): $sql\n";
 556     my @tagnames=$self->{db}->cmd_firstcol($sql);
 557     print "SUBNAMES: ", join(', ', @tagnames), "\n";
 558     return(@tagnames);
 559 }
 560
 561 sub tag_values
 562 {
 563     my($self, $tagid)=@_;
 564     my $sql=("SELECT DISTINCT name FROM tags\n" .
 565              "WHERE parents_id=?");
 566     my @tags=$self->{db}->cmd_firstcol($sql, $tagid);
 567     @tags=map { length($_) ? $_ : "NOVALUE"; } @tags;
 568     return @tags;
 569 }
 570
 571 sub artists
 572 {
 573     my($self)=@_;
 574     if(!@{$self->{elements}}) # /ALL
 575     {
 576         my $sql="SELECT DISTINCT name FROM artists WHERE name!='';";
 577         return($self->{db}->cmd_firstcol($sql));
 578     }
 579     my @ids=();
 580     my $sql=("SELECT artists.name FROM (\n" .
 581              $self->tags_subselect() .
 582              ") AS subselect\n" .
 583              "INNER JOIN files ON subselect.files_id=files.id\n" .
 584              "INNER JOIN artists ON files.artists_id=artists.id\n" .
 585              "WHERE artists.name != ''\n" .
 586              "GROUP BY artists.name;");
 587     print "SQL(ARTISTS): $sql\n";
 588     my @tagnames=$self->{db}->cmd_firstcol($sql);
 589     print "ARTISTS: ", join(', ', @tagnames), "\n";
 590     return(@tagnames);
 591 }
 592
 593 sub albums
 594 {
 595     my($self)=@_;
 596     my @ids=();
 597     my $tail=$self->tail();
 598     # FIXME: rework PathElements
 599     if(ref($tail) eq "ID3FS::PathElement::Artist")
 600     {
 601         return $self->artist_albums($tail->{id});
 602     }
 603     my $sql=("SELECT albums.name\n" .
 604              "\tFROM (\n" .
 605              $self->tags_subselect() .
 606              "\t) AS subselect\n" .
 607              "INNER JOIN files ON subselect.files_id=files.id\n" .
 608              "INNER JOIN albums ON files.albums_id=albums.id\n" .
 609              "WHERE albums.name != ''\n" .
 610              "GROUP BY albums.name;");
 611     print "SQL(ALBUMS): \n$sql\n";
 612     my @names=$self->{db}->cmd_firstcol($sql);
 613     print "ALBUMS: ", join(', ', @names), "\n";
 614     return(@names);
 615 }
 616
 617 sub artist_albums
 618 {
 619     my($self, $artist_id)=@_;
 620     my $sql=("SELECT albums.name FROM (\n" .
 621              $self->tags_subselect() .
 622              "\t) AS subselect\n" .
 623              "INNER JOIN files ON subselect.files_id=files.id\n" .
 624              "INNER JOIN albums ON albums.id=files.albums_id\n\t" .
 625              "INNER JOIN artists ON artists.id=files.artists_id\n\t" .
 626              "WHERE artists.id=? and albums.name <> ''\n\t" .
 627              "GROUP BY albums.name\n");
 628     print "ARTIST_ALBUMS SQL: $sql\n";
 629     my @albums=$self->{db}->cmd_firstcol($sql, $artist_id);
 630     print "ALBUMS: ", join(', ', @albums), "\n";
 631     return(@albums);
 632 }
 633
 634 sub artist_tracks
 635 {
 636     my($self, $artist_id)=@_;
 637     my $sql=("SELECT files.name FROM (\n" .
 638              $self->tags_subselect() .
 639              "\t) AS subselect\n" .
 640              "INNER JOIN files ON subselect.files_id=files.id\n" .
 641              "INNER JOIN artists ON artists.id=files.artists_id\n" .
 642              "INNER JOIN albums  ON albums.id=files.albums_id\n" .
 643              "WHERE artists.id=? AND albums.name=''\n" .
 644              "GROUP BY files.name\n");
 645     print "ARTIST_TRACKS SQL: $sql\n";
 646     my @names=$self->{db}->cmd_firstcol($sql, $artist_id);
 647     print "ARTISTTRACKS: ", join(', ', @names), "\n";
 648     return(@names);
 649 }
 650
 651 sub album_tracks
 652 {
 653     my($self, $artist_id, $album_id)=@_;
 654     my $sql=("SELECT files.name FROM files\n\t" .
 655              "INNER JOIN albums  ON albums.id=files.albums_id\n\t" .
 656              "INNER JOIN artists ON artists.id=files.artists_id\n\t" .
 657              "WHERE artists.id=? AND albums.id=?\n\t" .
 658              "GROUP BY files.name\n");
 659     print "ALBUM_TRACKS SQL($artist_id, $album_id): $sql\n";
 660     my @names=$self->{db}->cmd_firstcol($sql, $artist_id, $album_id);
 661     print "TRACKS: ", join(', ', @names), "\n";
 662     return(@names);
 663 }
 664
 665 sub tracks
 666 {
 667     my($self)=@_;
 668     # FIXME: rework PathElements
 669     my $tail=$self->tail();
 670     if(ref($tail) eq "ID3FS::PathElement::Artist")
 671     {
 672         return $self->artist_tracks($tail->{id});
 673     }
 674     elsif(ref($tail) eq "ID3FS::PathElement::Album")
 675     {
 676         my $artist_id=0;
 677         my $artist=$self->tail_parent();
 678         if(defined($artist) && (ref($artist) eq "ID3FS::PathElement::Artist"))
 679         {
 680             # should always happen
 681             $artist_id=$artist->{id};
 682         }
 683         return $self->album_tracks($artist_id, $tail->{id});
 684     }
 685     my $sql=("SELECT files.name\n" .
 686              "\tFROM (\n" .
 687              $self->tags_subselect() .
 688              "\t) AS subselect\n" .
 689              "INNER JOIN files ON files.id=subselect.files_id\n" .
 690              "INNER JOIN artists ON files.artists_id=artists.id\n");
 691     if($self->{components}->[$#{$self->{components}}] eq "NOARTIST")
 692     {
 693         $sql .= "WHERE artists.name =''\n";
 694     }
 695     $sql .= "GROUP BY files.name;";
 696     print "TRACKS SQL: $sql\n";
 697     my @names=$self->{db}->cmd_firstcol($sql);
 698     print "TRACKS: ", join(', ', @names), "\n";
 699     return(@names);
 700 }
 701
 702 sub filename
 703 {
 704     my($self, $mountpoint)=@_;
 705     my $tail=$self->tail();
 706     if(ref($tail) eq "ID3FS::PathElement::File")
 707     {
 708         my $id=$tail->{id};
 709         my $sql=("SELECT paths.name, files.name FROM files\n" .
 710                  "INNER JOIN paths ON files.paths_id=paths.id\n" .
 711                  "WHERE files.id=?\n" .
 712                  "GROUP BY paths.name, files.name");
 713         print "FILENAME SQL: $sql\n";
 714         my ($path, $name)=$self->{db}->cmd_onerow($sql, $id);
 715         my $id3fs_path=join('/', map { $_->{name}; }  @{$self->{elements}});
 716         return($self->{db}->relativise($path, $name, $mountpoint, $id3fs_path));
 717     }
 718     die("DB::filename: unhandled case\n"); #FIXME
 719 }
 720
 721 sub tags_subselect
 722 {
 723     my($self)=@_;
 724     # we need to specially handle a bare /NOT/tag with no other clauses,
 725     # using a simple WHERE id !='tagid' instead of a LEFT JOIN
 726     if($self->{bare_not})
 727     {
 728         return $self->bare_not_subselect();
 729     }
 730     my $tree=$self->{tagtree};
 731     my $hasvals=$self->tag_has_values();
 732     my $parent=$self->trailing_tag_parent();
 733
 734 #    print "ELEMENTS: ", join('/', map { $_->{name}; } @{$self->{elements}}), "\n";
 735 #    print "TREE: ", $tree->print(), "\n";
 736     my $tag=undef;
 737     if($hasvals)
 738     {
 739         $tag=$self->trailing_tag_id();
 740 #       print "Trailing id: $tag\n";
 741     }
 742     my ($sqlclause, @joins)=(undef, ());
 743     ($sqlclause, @joins) = $tree->to_sql() if($tree);
 744 #    print "SQL(" . scalar(@joins) .": $sqlclause\n";
 745     my $sql="\tSELECT fxt1.files_id FROM tags t1";
 746     my @crosses=();
 747     my @inners=();
 748 #    $joinsneeded++ if($tag);
 749     for(my $i=0; $i <= $#joins; $i++)
 750     {
 751         my $cnt=$i+1;
 752         my $join=$joins[$i];
 753         my $inner=("\t$join JOIN files_x_tags fxt$cnt ON " .
 754                    "t${cnt}.id=fxt${cnt}.tags_id");
 755         if($i > 0)
 756         {
 757             push(@crosses, "CROSS JOIN tags t$cnt");
 758             $inner .= " AND fxt1.files_id=fxt${cnt}.files_id";
 759         }
 760         push(@inners, $inner);
 761     }
 762     $sql .= ("\n\t" . join(" ", @crosses)) if(@crosses);
 763     $sql .= ("\n" . join("\n", @inners)) if(@inners);
 764     $sql .= "\n\tWHERE $sqlclause" if($sqlclause);
 765 #    if($tag)
 766 #    {
 767 #       $sql .= " AND t${joinsneeded}.parents_id='$tag'";
 768 #    }
 769     $sql .= "\n\tGROUP BY fxt1.files_id\n";
 770     return $sql;
 771 }
 772
 773 sub bare_not_subselect
 774 {
 775     my($self)=@_;
 776     my @tags=grep { ref($_) eq "ID3FS::PathElement::Tag"; } @{$self->{elements}};
 777     my $sql=("\tSELECT f1.id AS files_id FROM files f1 WHERE f1.id NOT IN (\n" .
 778              "\t\tSELECT fxt1.files_id FROM tags t1\n" .
 779              "\t\tINNER JOIN files_x_tags fxt1 ON t1.id=fxt1.tags_id\n" .
 780              "\t\tWHERE ");
 781     if(scalar(@tags) > 1)
 782     {
 783         $sql .= ("(t1.parents_id='" . $tags[0]->{id} . "' AND t1.id='" .
 784                  $tags[1]->{id} . "')");
 785     }
 786     else
 787     {
 788         $sql .= ("(t1.parents_id='' AND t1.id='" . $tags[0]->{id} . "')");
 789     }
 790     $sql .= "\n\t\tGROUP BY fxt1.files_id\n\t)\n";
 791     return($sql);
 792 }
 793
 794 sub tags_subselect_and_not
 795 {
 796     my($self,@constraints)=@_;
 797     my ($tags, $tags_vals, $parent)=$self->constraints_tag_list(@constraints);
 798     my @tags=@$tags;
 799     my @tags_vals=@$tags_vals;;
 800     my $cnt=1;
 801     my @andclauses=();
 802     my $sql='';
 803     for my $tag (@tags)
 804     {
 805         if($cnt == 1)
 806         {
 807             $sql="\tSELECT fxt" . scalar(@tags) . ".files_id FROM files_x_tags fxt1\n";
 808             push(@andclauses, "\t\tfxt${cnt}.tags_id=$tag");
 809         }
 810         else
 811         {
 812             $sql .= ("\tLEFT JOIN files_x_tags fxt$cnt ON fxt" .
 813                  ($cnt-1) . ".files_id=fxt${cnt}.files_id\n");
 814             push(@andclauses, "\t\tfxt${cnt}.tags_id IS NULL");
 815         }
 816         print "AND: @andclauses\n";
 817         $cnt++;
 818     }
 819     if(@andclauses)
 820     {
 821         $sql .= "\tWHERE\n\t\t";
 822         $sql .= join(" AND\n\t\t", @andclauses) . "\n";
 823     }
 824     $sql .= "\tGROUP BY fxt". scalar(@tags).".files_id\n";
 825     return $sql;
 826 }
 827
 828
 829 sub tags_subselect_and
 830 {
 831     my($self,@constraints)=@_;
 832     my ($tags, $tags_vals, $parent)=$self->constraints_tag_list(@constraints);
 833     my @tags=@$tags;
 834     my @tags_vals=@$tags_vals;;
 835     my $cnt=1;
 836     my @andclauses=();
 837     my $sql='';
 838     for my $tag (@tags)
 839     {
 840         if($cnt == 1)
 841         {
 842             $sql="\tSELECT fxt" . scalar(@tags) . ".files_id FROM files_x_tags fxt1\n";
 843         }
 844         else
 845         {
 846             $sql .= ("\tINNER JOIN files_x_tags fxt$cnt ON fxt" .
 847                  ($cnt-1) . ".files_id=fxt${cnt}.files_id\n");
 848         }
 849         push(@andclauses, "\t\tfxt${cnt}.tags_id = $tag");
 850         print "AND: @andclauses\n";
 851         $cnt++;
 852     }
 853     if(@andclauses)
 854     {
 855         $sql .= "\tWHERE\n\t\t";
 856         $sql .= join(" AND\n\t\t", @andclauses) . "\n";
 857     }
 858     $sql .= "\tGROUP BY fxt". scalar(@tags).".files_id\n";
 859     return $sql;
 860 }
 861
 862 sub tags_subselect_or
 863 {
 864     my($self,@constraints)=@_;
 865     my ($tags, $tags_vals, $parent)=$self->constraints_tag_list(@constraints);
 866     my @tags=@$tags;
 867     my @tags_vals=@$tags_vals;;
 868
 869     my $sql=("\tSELECT files_x_tags.files_id FROM tags t1\n" .
 870              "\tINNER JOIN files_x_tags ON t1.id=files_x_tags.tags_id\n");
 871     my @orclauses=();
 872     my @andclauses=();
 873     # FIXME: and / or?
 874     if(@tags)
 875     {
 876         push(@andclauses, "( t1.parents_id=" . (defined($parent) ? $parent : "''") . " )");
 877         push(@andclauses, "( t1.id IN ( " . join(', ', @tags) ." ) )");
 878     }
 879     for my $pair (@tags_vals)
 880     {
 881         my($tag, $val)=@$pair;
 882         push(@orclauses, "( t1.parents_id=$tag AND t1.id=$val )");
 883     }
 884 #    push(@andclauses, "( t1.parents_id=" . (defined($parent) ? $parent : "''") . " )");
 885     if(@orclauses)
 886     {
 887         push(@andclauses, join("\n\t\tOR ", @orclauses));
 888     }
 889     if(@andclauses)
 890     {
 891         $sql .= "\tWHERE\n\t\t";
 892         $sql .= join("\n\t\tAND ", @andclauses) . "\n";
 893     }
 894     $sql .= "\tGROUP BY files_x_tags.files_id\n";
 895     return $sql;
 896 }
 897
 898 sub constraints_tag_list
 899 {
 900     my($self, @constraints)=@_;
 901     my $lasttag=undef;
 902     my @tags=();
 903     my @tags_vals=();
 904     for my $constraint (@constraints)
 905     {
 906 #       print ref($constraint), ": ", $constraint->{name}, "\n";
 907         if(ref($constraint) eq "ID3FS::PathElement::Tag")
 908         {
 909             if(defined($lasttag))
 910             {
 911 #               print "TAGVAL\n";
 912                 push(@tags_vals, [$lasttag, $constraint->{id}]) if defined($constraint->{id});
 913                 $lasttag=undef;
 914             }
 915             elsif($self->tag_has_values($constraint->{id}))
 916             {
 917 #               print "HASVALUES\n";
 918                 $lasttag=$constraint->{id} if defined($constraint->{id});
 919             }
 920             else
 921             {
 922 #               print "NOVALUES\n";
 923                 push(@tags, $constraint->{id}) if(defined($constraint->{id}));
 924             }
 925         }
 926     }
 927     unless($self->{db}->{postgres})
 928     {
 929         @tags=map{ "\"$_\""; } @tags;
 930         @tags_vals=map( { [ map({ "\"$_\""; } @$_ ) ] } @tags_vals);
 931         $lasttag="\"$lasttag\"" if defined($lasttag);
 932     }
 933     return(\@tags, \@tags_vals, $lasttag);
 934 }
 935
 936
 937 sub bare_tags
 938 {
 939     my($self)=@_;
 940     my $sql=("SELECT tags.name FROM tags\n" .
 941              "WHERE tags.parents_id=''\n" .
 942              "GROUP BY tags.name\n");
 943     my @names=$self->{db}->cmd_firstcol($sql);
 944     return (@names);
 945 }
 946
 947 sub tags_with_values
 948 {
 949     # FIXME: only shows one level of tag depth
 950     my($self)=@_;
 951     my $sql=("SELECT p.name, t.name  FROM tags t\n" .
 952              "INNER JOIN tags p ON t.parents_id=p.id\n" .
 953              "GROUP BY p.name, t.name\n");
 954     print "SQL: $sql\n";
 955     my $result=$self->{db}->cmd_rows($sql);
 956     my $tags={};
 957     for my $pair (@$result)
 958     {
 959         push(@{$tags->{$pair->[0]}}, $pair->[1]);
 960     }
 961     return $tags;
 962 }
 963
 964 1;