From fd9b7c47be287471cbac40281f25143ac5a7bb6e Mon Sep 17 00:00:00 2001 From: Ian Beckwith Date: Sun, 3 Oct 2010 05:45:55 +0100 Subject: [PATCH] more schema change/tagval fixes --- lib/ID3FS/DB.pm | 136 +++++++++++++++++++++++++++++++----------------------- lib/ID3FS/Path.pm | 1 + 2 files changed, 78 insertions(+), 59 deletions(-) diff --git a/lib/ID3FS/DB.pm b/lib/ID3FS/DB.pm index 5cd7815..2ca7f73 100644 --- a/lib/ID3FS/DB.pm +++ b/lib/ID3FS/DB.pm @@ -184,31 +184,38 @@ sub tags if(!@constraints) # / { # FIXME: add ALL? - my $sql="SELECT DISTINCT name FROM tags;"; + my $sql="SELECT DISTINCT name FROM tags WHERE parents_id='';"; my $tags=$self->cmd_rows($sql); return(map { $_->[0]; } @$tags); } my @ids=(); - my $main_sql_start=("SELECT t2.name\n" . - "\tFROM (\n" . - $self->tags_subselect(@constraints) . - ") 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" . - "WHERE t2.id NOT IN ("); - my $main_sql_end=")\nGROUP BY t2.name;"; - # FIXME: generalise more? - - while(my $constraint=shift @constraints) - { - my $cid=$constraint->{id}; - push(@ids, $cid); - } - @ids = map( { "\"$_\""; } grep { defined; } @ids) unless($self->{postgres}); - my $tagstr=join(", ", @ids); - my $sql = ($main_sql_start . $tagstr . - $main_sql_end); + my $sql=("SELECT t2.name FROM (\n" . + $self->tags_subselect(@constraints) . + ") 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 ($tags, $tags_vals)=$self->constraints_tag_list(@constraints); + my @tags=@$tags; + my @tags_vals=@$tags_vals;; + my @clauses=(); + use Data::Dumper; + print "TAGS: ", Dumper \@tags; + print "VALS: ", Dumper \@tags_vals; + if(@tags) + { + push(@clauses, "(t2.id NOT IN ( " . join(', ', @tags) ." ) )"); + } + for my $pair (@tags_vals) + { + my($tag, $val)=@$pair; + push(@clauses, "( NOT (t2.parents_id=$tag AND t2.id=$val ) )"); + } + if(@clauses) + { + $sql .= "WHERE\n\t\t" . join("\n\t\tAND ", @clauses) . "\n"; + } + $sql .= "GROUP BY t2.name;"; print "SQL: $sql\n"; my $result=$self->cmd_rows($sql); my @tagnames=map { $_->[0]; } @$result; @@ -377,57 +384,65 @@ sub filename sub tags_subselect { my($self,@constraints)=@_; - use Data::Dumper; + my ($tags, $tags_vals)=$self->constraints_tag_list(@constraints); + my @tags=@$tags; + my @tags_vals=@$tags_vals;; + + my $sql=("\tSELECT files_x_tags.files_id FROM tags t1\n" . + "\tINNER JOIN files_x_tags ON t1.id=files_x_tags.tags_id\n"); + my @clauses=(); + if(@tags) + { + push(@clauses, "(t1.id IN ( " . join(', ', @tags) ." ) )"); + } + for my $pair (@tags_vals) + { + my($tag, $val)=@$pair; + push(@clauses, "( t1.parents_id=$tag AND t1.id=$val )"); + } + if(@clauses) + { + $sql .= "\tWHERE\n\t\t" . join("\n\t\tOR ", @clauses) . "\n"; + } + $sql .= "\tGROUP BY files_x_tags.files_id\n"; + return $sql; +} + +sub constraints_tag_list +{ + my($self, @constraints)=@_; + my $lasttag=undef; my @tags=(); my @tags_vals=(); - my $lasttag=''; -# print "CONSTRAINTS: \n", Dumper \@constraints; for my $constraint (@constraints) { -# print ref($constraint),"\n"; + print ref($constraint), $constraint->{name}, "\n"; if(ref($constraint) eq "ID3FS::PathElement::Tag") { - if($self->tag_has_values($constraint->{id})) + if(defined($lasttag)) + { + print "TAGVAL\n"; + push(@tags_vals, [$lasttag, $constraint->{id}]) if defined($constraint->{id}); + $lasttag=undef; + } + elsif($self->tag_has_values($constraint->{id})) { -# print "HASVALUES\n"; + print "HASVALUES\n"; $lasttag=$constraint->{id} if defined($constraint->{id}); } else { -# print "NOVALUES\n"; + print "NOVALUES\n"; push(@tags, $constraint->{id}) if(defined($constraint->{id})); } } - elsif(ref($constraint) eq "ID3FS::PathElement::Tagval") - { -# print "TAGVAL\n"; - push(@tags_vals, [$lasttag, $constraint->{id}]) if defined($constraint->{id}); - } } unless($self->{postgres}) { @tags=map{ "\"$_\""; } @tags; @tags_vals=map( { [ map({ "\"$_\""; } @$_ ) ] } @tags_vals); } -# print "TAGS\n", Dumper \@tags; -# print "\nVALS\n", Dumper(\@tags_vals), "\n"; - my $sql=("\tSELECT files_x_tags.files_id FROM tags t1\n" . - "\tINNER JOIN files_x_tags ON t1.id=files_x_tags.tags_id\n"); - my @clauses=(); - if(@tags) - { - push(@clauses, "(t1.id IN ( " . join(', ', @tags) ." ) )"); - } - for my $pair (@tags_vals) - { - my($tag, $val)=@$pair; - push(@clauses, "( t1.parents_id=$tag AND t1.id=$val )"); - } - if(@clauses) - { - $sql .= "\tWHERE\n\t\t" . join("\n\t\tOR ", @clauses) . "\n"; - } - return $sql; + return(\@tags, \@tags_vals); } @@ -567,16 +582,19 @@ sub add sub add_tag { - my($self, $file_id, @tags)=@_; - my $parent_id=undef; - for my $tag (@tags) + my($self, $file_id, $tag, $value)=@_; + my $tag_id=$self->add_to_table("tags", $tag, + { "parents_id" => undef }); + $self->add_relation("files_x_tags", + { "files_id" => $file_id, + "tags_id" => $tag_id }); + if(defined($value) && length($value)) { - my $tag_id=$self->add_to_table("tags", $tag, - { "parents_id" => $parent_id }); + my $val_id=$self->add_to_table("tags", $value, + { "parents_id" => $tag_id }); $self->add_relation("files_x_tags", { "files_id" => $file_id, - "tags_id" => $tag_id }); - $parent_id=$tag_id; + "tags_id" => $val_id }); } } @@ -701,7 +719,7 @@ sub remove_unused WHERE files_x_tags.files_id IS NULL); EOT - print "SQL: $sql\n"; +# print "SQL: $sql\n"; my @sql=split(/\n\n/, $sql); $self->cmd($_) for (@sql); } diff --git a/lib/ID3FS/Path.pm b/lib/ID3FS/Path.pm index c9553f9..0be7c45 100644 --- a/lib/ID3FS/Path.pm +++ b/lib/ID3FS/Path.pm @@ -67,6 +67,7 @@ sub dirents my @dents=(); my $state=$self->state(); # print "DIRENTS: STATE: $state\n"; +# print "DIRENTS: FILE: $self->{path}\n"; if($state==$STATE_TAG || $state==$STATE_TAGVAL) { my $tag=$self->{elements}->[$#{$self->{elements}}]; -- 2.11.0