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