note
blssu
<p> When you have a problem like "I need to create a string made up from values stored within arrays" you should think "maybe join will work." </p>
<p> When you have a problem like "has to work for any amount of arrays or values" you should think "maybe map or foreach will work." </p>
<p> First, you need to reduce the problem down to simpler sub-problems. Reformat your desired results so that similar structures are grouped together. Also split up separators and iteration patterns. </p>
<code>view0
full outer join
view1 on
view0.size = view1.size
and
view0.buffersize = view1.buffersize
full outer join
view2 on
view1.size = view2.size
and
view1.buffersize = view2.buffersize
full outer join
view3 on
view2.size = view3.size
and
view2.buffersize = view3.buffersize
</code>
<p> Now it's a bit easier to see the sub-problems. I've also marked each sub-problem as a join, map, or foreach kind of problem. It doesn't really matter if you get this wrong. As you try to solve each sub-problem the right solution will emerge. Here are the sub-problems I see:
<ol>
<li> create the "view0" statement -- join + map
<li> create the "view? on" clauses
<ol>
<li> match key columns between tables -- map
<li> generate a SQL comparison -- join
<li> combine with "and" -- join
</ol>
<li> run the query (DBI)
<li> fetch results for an unknown number of columns (DBI)
</ol>
</p>
<p> The problems marked (DBI) can be handled by DBI directly -- RTFM. I'm not going to step through the process of solving each sub-problem, I'm just going to dump out my first cut at the solutions:</p>
<code>use strict
my $last = 'view0';
print join(' full outer join ', 'view0', view_on_clauses()),"\n";
sub view_on_clauses {
return map { join(' ', $_, 'on', and_clause($_)) }
qw(view1 view2 view3);
}
sub and_clause {
my($view) = @_;
return join(' and ', equality_clauses($view));
}
sub equality_clauses {
my($view) = @_;
my @equality_clauses = map { "$last.$_=$view.$_" }
qw(size buffersize);
$last = $view;
return @equality_clauses;
}
</code>
<p> Right away we see a problem with your input and the first solution cut: hashes are unordered, but the solution requires an array of view names in a specific order. Also, the column names need to be an array, but you have them stored as strings. One thing that the first cut doesn't do is handle different column names for each view. Do you want to do that or should all columns names be the same? </p>
<p> Here's my final solution after fixing up those two issues and then replacing the hard-coded arrays with variables: </p>
<code>use strict;
my @views = ( 'view0', 'view1', 'view2', 'view3' );
my %columns = ( 'view0' => [qw(buffersize size value)],
'view1' => [qw(buffersize size value)],
'view2' => [qw(buffersize size value)],
'view3' => [qw(buffersize size value)] );
print build_query(\@views, \%columns), "\n";
my $last;
sub build_query {
my($views, $columns) = @_;
my $first = shift @{$views};
$last = $first;
return join(' full outer join ', $first,
view_on_clauses($views, $columns));
}
sub view_on_clauses {
my($views, $columns) = @_;
return map { join(' ', $_, 'on', and_clause($_, $columns)) } @{$views};
}
sub and_clause {
my($view, $columns) = @_;
return join(' and ', equality_clauses($view, $columns));
}
sub equality_clauses {
my($view, $columns) = @_;
my @equality_clauses = ( );
# Convert map into explicit for loop because we need to
# merge together two different arrays. If the column names
# are always the same, then map can be used.
for (my $i = 0; $i < @{$columns->{$view}} - 1; ++$i) {
push @equality_clauses,
$last . '.' . $columns->{$last}[$i] . '=' .
$view . '.' . $columns->{$view}[$i];
}
$last = $view;
return @equality_clauses;
}
</code>
196606
196606