From da58515ff7bf354b9b19d35fa738dc4a041b43d3 Mon Sep 17 00:00:00 2001 From: Ian Beckwith Date: Sat, 2 Oct 2010 07:26:41 +0100 Subject: [PATCH] abstract out tags_subselect --- lib/ID3FS/DB.pm | 102 +++++++++++++++++++++++--------------------------------- 1 file changed, 42 insertions(+), 60 deletions(-) diff --git a/lib/ID3FS/DB.pm b/lib/ID3FS/DB.pm index 9930609..0ed658c 100644 --- a/lib/ID3FS/DB.pm +++ b/lib/ID3FS/DB.pm @@ -191,14 +191,15 @@ sub tags my @ids=(); my $main_sql_start=("SELECT t2.name\n" . - "\tFROM (SELECT files_id FROM tags t1\n" . - "\t\tINNER JOIN files_x_tags ON t1.id=files_x_tags.tags_id\n" . - "\t\tWHERE t1.id in\n\t\t\t("); - my $main_sql_mid=(")\n\t\t) AS subselect\n" . - "\tINNER JOIN files_x_tags ON subselect.files_id=files_x_tags.files_id\n" . - "\tINNER JOIN tags t2 ON files_x_tags.tags_id=t2.id\n" . - "\tWHERE t2.id NOT IN ("); - my $main_sql_end=")\n\tGROUP BY t2.name;"; + "\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}; @@ -207,7 +208,6 @@ sub tags @ids = map( { "\"$_\""; } grep { defined; } @ids) unless($self->{postgres}); my $tagstr=join(", ", @ids); my $sql = ($main_sql_start . $tagstr . - $main_sql_mid . $tagstr . $main_sql_end); print "SQL: $sql\n"; my $result=$self->cmd_rows($sql); @@ -238,23 +238,12 @@ sub artists return(map { $_->[0]; } @$tags); } my @ids=(); - my $main_sql_start=("SELECT artists.name\n" . - "\tFROM (SELECT files_id FROM tags\n" . - "\t\tINNER JOIN files_x_tags ON tags.id=files_x_tags.tags_id\n" . - "\t\tWHERE tags.id in\n\t\t\t("); - my $main_sql_end=(")\n\t\t) AS subselect\n" . - "\tINNER JOIN files ON subselect.files_id=files.id\n" . - "\tINNER JOIN artists ON files.artists_id=artists.id\n" . - "\n\tGROUP BY artists.name;"); - 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 artists.name FROM (\n" . + $self->tags_subselect(@constraints) . + ") AS subselect\n" . + "INNER JOIN files ON subselect.files_id=files.id\n" . + "INNER JOIN artists ON files.artists_id=artists.id\n" . + "GROUP BY artists.name;"); print "SQL: $sql\n"; my $result=$self->cmd_rows($sql); my @tagnames=map { $_->[0]; } @$result; @@ -271,23 +260,13 @@ sub albums { return $self->artist_albums($constraints[$#constraints]->{id}); } - my $main_sql_start=("SELECT albums.name\n" . - "\tFROM (SELECT files_id FROM tags\n" . - "\t\tINNER JOIN files_x_tags ON tags.id=files_x_tags.tags_id\n" . - "\t\tWHERE tags.id in\n\t\t\t("); - my $main_sql_end=(")\n\t\t) AS subselect\n" . - "\tINNER JOIN files ON subselect.files_id=files.id\n" . - "\tINNER JOIN albums ON files.albums_id=albums.id\n" . - "\n\tGROUP BY albums.name;"); - while(my $constraint=shift @constraints) - { - my $cid=$constraint->{id}; - push(@ids, $cid); - } - @ids = map( { "\"$_\""; } grep { defined; } @ids) unless($self->{postgres}); - my $str=join(", ", @ids); - my $sql = ($main_sql_start . $str . - $main_sql_end); + my $sql=("SELECT albums.name\n" . + "\tFROM (\n" . + $self->tags_subselect(@constraints) . + "\t) AS subselect\n" . + "INNER JOIN files ON subselect.files_id=files.id\n" . + "INNER JOIN albums ON files.albums_id=albums.id\n" . + "GROUP BY albums.name;"); my $result=$self->cmd_rows($sql); my @names=map { $_->[0]; } @$result; print "ALBUMS: ", join(', ', @names), "\n"; @@ -359,23 +338,12 @@ sub tracks return $self->album_tracks($artist_id, $constraints[$#constraints]->{id}); } - my $main_sql_start=("SELECT files.name\n" . - "\tFROM (SELECT files_id FROM tags\n" . - "\t\tINNER JOIN files_x_tags ON tags.id=files_x_tags.tags_id\n" . - "\t\tWHERE tags.id in\n\t\t\t("); - my $main_sql_end=(")\n\t\t) AS subselect\n" . - "\tINNER JOIN files ON files.id=subselect.files_id" . - "\tGROUP BY files.name;"); - my @ids; - while(my $constraint=shift @constraints) - { - my $cid=$constraint->{id}; - push(@ids, $cid); - } - @ids = map( { "\"$_\""; } grep { defined; } @ids) unless($self->{postgres}); - my $str=join(", ", @ids); - my $sql = ($main_sql_start . $str . - $main_sql_end); + my $sql=("SELECT files.name\n" . + "\tFROM (\n" . + $self->tags_subselect(@constraints) . + "\t) AS subselect\n" . + "INNER JOIN files ON files.id=subselect.files_id\n" . + "GROUP BY files.name;"); print "SQL: $sql\n"; my $result=$self->cmd_rows($sql); my @names=map { $_->[0]; } @$result; @@ -401,6 +369,20 @@ sub filename die("DB::filename: unhandled case\n"); #FIXME } +sub tags_subselect +{ + my($self,@constraints)=@_; + my @ids = grep { defined; } map { $_->{id}; } @constraints; + @ids=map( { "\"$_\""; } @ids) unless($self->{postgres}); + my $sql=("\tSELECT files_id FROM tags t1\n" . + "\tINNER JOIN files_x_tags ON t1.id=files_x_tags.tags_id\n" . + "\tWHERE t1.id IN (\n\t\t" . + join(', ', @ids) . + "\n\t)\n"); + return $sql; +} + + sub relativise { my($self, $path, $name, $mountpoint, $id3fs_path)=@_; -- 2.11.0