From 383168447067bcf6a257ca77eb5c6fd4a95821bc Mon Sep 17 00:00:00 2001 From: Ian Beckwith Date: Sun, 10 Oct 2010 05:08:15 +0100 Subject: [PATCH] partial (broken) support for tagvals --- lib/ID3FS/DB.pm | 85 +++++++++++++++++++++++++++++++++++--------- lib/ID3FS/Path.pm | 22 ++++++++++++ lib/ID3FS/Path/Node.pm | 37 ++++++++++++++----- lib/ID3FS/PathElement/Tag.pm | 2 +- 4 files changed, 119 insertions(+), 27 deletions(-) diff --git a/lib/ID3FS/DB.pm b/lib/ID3FS/DB.pm index 309df56..4ff9bfd 100644 --- a/lib/ID3FS/DB.pm +++ b/lib/ID3FS/DB.pm @@ -190,21 +190,48 @@ sub tags return(map { $_->[0]; } @$tags); } my @ids=(); - - my $sql=("SELECT t2.name FROM (\n" . + my $sql=("SELECT tags.name FROM (\n" . $self->tags_subselect($path) . ") AS subselect\n" . "INNER JOIN files_x_tags ON subselect.files_id=files_x_tags.files_id\n" . - "INNER JOIN tags t2 ON files_x_tags.tags_id=t2.id\n"); - my @used=$path->used_tags(); + "INNER JOIN tags ON files_x_tags.tags_id=tags.id\n"); + my (@allused)=$path->used_tags(); + my @used=grep { ref($_) ne "ARRAY"; } @allused; + my @used_with_vals=grep { ref($_) eq "ARRAY"; } @allused; print "tags(): USED: ", join(", ", @used), "\n"; - if(@used) + print "tags(): USED_WITH_VALS: ", join(", ", map { "[".$_->[0]. ", ".$_->[1]."]";} @used_with_vals), "\n"; + my @orclauses=(); + if($path->tag_has_values()) + { + print "HAS_VALUES\n"; + my $parent=$path->trailing_tag_id(); + print "parent: $parent\n"; + my @values=map { "'".$_->[1]."'"; } grep { $_->[0] == $parent; } @used_with_vals; + my $clause="(tags.parents_id='$parent'"; + if(@values) + { + $clause .= " AND tags.id NOT IN (" . join(', ', @values) . ")"; + } + $clause .= ")"; + push(@orclauses, $clause); + } + else + { + print "HASNT VALUES\n";; + if(@used) + { + push(@orclauses, "(tags.parents_id='' AND tags.id NOT IN (" . join(', ', @used) . "))"); + } + for my $pair (@used_with_vals) + { + push(@orclauses, "(tags.parents_id='" . $pair->[0] . "' AND tags.id!='" . $pair->[1] . "')"); + } + } + if(@orclauses) { - $sql .= "WHERE t2.id NOT IN ("; - $sql .= join(', ', @used); - $sql .= ")\n"; + $sql .= "WHERE " . join(' OR ', @orclauses) . "\n"; } - $sql .= "GROUP BY t2.name;"; + $sql .= "GROUP BY tags.name;"; print "SQL: $sql\n"; my $result=$self->cmd_rows($sql); my @tagnames=map { $_->[0]; } @$result; @@ -381,16 +408,40 @@ sub tags_subselect { my($self, $path)=@_; my $tree=$path->{tagtree}; - my ($sqlclause, $joinsneeded)=$tree->to_sql(); - print "SQL($joinsneeded): $sqlclause\n"; - my $sql="\tSELECT fxt1.files_id FROM files_x_tags fxt1\n"; - for(my $i=2; $i <= $joinsneeded; $i++) + my $tag=undef; + if($path->tag_has_values()) { - $sql .= ("\tINNER JOIN files_x_tags fxt$i ON " . - "fxt1.files_id=fxt${i}.files_id\n"); + $tag=$path->trailing_tag_id(); + print "Trailing id: $tag\n"; } - $sql .= "\tWHERE $sqlclause\n"; - $sql .= "\tGROUP BY fxt${joinsneeded}.files_id\n"; + my ($sqlclause, $joinsneeded)=$tree->to_sql($tag); + print "SQL($joinsneeded): $sqlclause\n"; + my $sql="\tSELECT fxt1.files_id FROM tags t1"; + my @crosses=(); + my @inners=(); +# $joinsneeded++ if($tag); + for(my $i=1; $i <= $joinsneeded; $i++) + { + if($i > 1) + { + push(@crosses, "CROSS JOIN tags t$i"); + } + my $inner=("\tINNER JOIN files_x_tags fxt$i ON " . + "t${i}.id=fxt${i}.tags_id"); + if($i>2) + { + $inner .= " AND fxt1.files_id=fxt${i}.files_id"; + } + push(@inners, $inner); + } + $sql .= ("\n\t" . join(" ", @crosses)) if(@crosses); + $sql .= ("\n" . join("\n", @inners)) if(@inners); + $sql .= "\n\tWHERE $sqlclause"; +# if($tag) +# { +# $sql .= " AND t${joinsneeded}.parents_id='$tag'"; +# } + $sql .= "\n\tGROUP BY fxt1.files_id\n"; return $sql; } diff --git a/lib/ID3FS/Path.pm b/lib/ID3FS/Path.pm index 68218ed..5c7eacc 100644 --- a/lib/ID3FS/Path.pm +++ b/lib/ID3FS/Path.pm @@ -173,6 +173,7 @@ sub parse ref($tag) eq "ID3FS::PathElement::Tag" && $self->{db}->tag_has_values($tag->{id})) { +# print "Parsing: parent: $tag->{id}\n"; my $tagval=ID3FS::PathElement::Tag->new($self->{db}, $name, $tag->{id}); if(defined($tagval)) { @@ -403,4 +404,25 @@ sub used_tags return($self->{tagtree}->used_tags()); } +sub tag_has_values +{ + my($self)=@_; + my $tail=$self->{elements}->[$#{$self->{elements}}]; + if($tail && ref($tail) eq "ID3FS::PathElement::Tag") + { + return($self->{db}->tag_has_values($tail->{id})); + } +} + +sub trailing_tag_id +{ + my($self)=@_; + my $tail=$self->{elements}->[$#{$self->{elements}}]; + if($tail && ref($tail) eq "ID3FS::PathElement::Tag") + { + return($tail->{id}); + } + return undef; +} + 1; diff --git a/lib/ID3FS/Path/Node.pm b/lib/ID3FS/Path/Node.pm index 9fc4570..f03d040 100644 --- a/lib/ID3FS/Path/Node.pm +++ b/lib/ID3FS/Path/Node.pm @@ -58,22 +58,21 @@ sub print_node sub to_sql { - my($self, $andlevel, $parent_is_tag)=@_; + my($self, $parent, $andlevel)=@_; $andlevel=1 unless(defined($andlevel)); - $parent_is_tag=0 unless(defined($parent_is_tag)); my ($leftandlevel, $rightandlevel); my ($leftstr, $rightstr); my $op=$self->op(); my $left=$self->left(); my $right=$self->right(); return ("", $andlevel) unless($left || $right); - ($leftstr, $leftandlevel) = $self->node_to_sql($left, $andlevel); + ($leftstr, $leftandlevel) = $self->node_to_sql($left, $parent, $andlevel); $andlevel=$self->max($andlevel, $leftandlevel); if(defined($op) && (($op->{name} eq "AND") || ($op->{name} eq "NOT"))) { $andlevel++; } - ($rightstr, $rightandlevel) = $self->node_to_sql($right, $andlevel); + ($rightstr, $rightandlevel) = $self->node_to_sql($right, $parent, $andlevel); my $str=$leftstr; $str .= (" " . $op->{name} . " ") if($op); $str .= $rightstr; @@ -86,10 +85,27 @@ sub to_sql sub node_to_sql { - my($self, $node, $andlevel)=@_; + my($self, $node, $parent, $andlevel)=@_; return ("", $andlevel) unless(defined($node)); - return $node->to_sql($andlevel) if(ref($node) eq "ID3FS::Path::Node"); - return( ( "fxt${andlevel}.tags_id=\"" . $node->{id} . "\""), $andlevel); + return $node->to_sql($parent, $andlevel) if(ref($node) eq "ID3FS::Path::Node"); + my $sql; + if(defined($node->{parents_id})) + { + print "HIT PARENTS_ID: $node->{parents_id}\n"; + $sql= "(t$andlevel.parents_id='$node->{parents_id}'"; + $sql .= " AND fxt${andlevel}.tags_id='" . $node->{id} . "')"; + } +# elsif($parent) +# { +# print "HIT \$parent\n"; +# $sql= "(t$andlevel.parents_id='$parent')"; +# } + else + { + $sql= "(t$andlevel.parents_id=''"; + $sql .= " AND fxt${andlevel}.tags_id='" . $node->{id} . "')"; + } + return ($sql, $andlevel); } sub used_tags @@ -106,8 +122,11 @@ sub node_used_tags my($self, $node)=@_; return (undef) unless(defined($node)); return $node->used_tags() if(ref($node) eq "ID3FS::Path::Node"); - print $node->{id}, "\n"; - return($node->{id}); + if(defined($node->{parents_id})) + { + return([ $node->{parents_id}, $node->{id} ]); + } + return $node->{id}; } diff --git a/lib/ID3FS/PathElement/Tag.pm b/lib/ID3FS/PathElement/Tag.pm index 2574d26..3185955 100644 --- a/lib/ID3FS/PathElement/Tag.pm +++ b/lib/ID3FS/PathElement/Tag.pm @@ -12,7 +12,7 @@ sub new $self->{db}=shift; $self->{name}=shift; - $self->{parent_id}=shift; + $self->{parents_id}=shift; $self->{id}=$self->{db}->id("tags", $self->{name}); return(undef) unless(defined($self->{id})); return($self); -- 2.11.0