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