From 2dd1450fcf12379624609ba7ef5710f5de2b9029 Mon Sep 17 00:00:00 2001 From: miker Date: Thu, 2 Nov 2006 01:17:50 +0000 Subject: [PATCH] completing support for nullable joins git-svn-id: svn://svn.open-ils.org/ILS/trunk@6542 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/examples/reporter-sql-builder-test.pl | 2 +- .../src/perlmods/OpenILS/Reporter/SQLBuilder.pm | 64 ++++++++++++++++------ 2 files changed, 49 insertions(+), 17 deletions(-) diff --git a/Open-ILS/examples/reporter-sql-builder-test.pl b/Open-ILS/examples/reporter-sql-builder-test.pl index 06b5633618..b7ebb9117e 100755 --- a/Open-ILS/examples/reporter-sql-builder-test.pl +++ b/Open-ILS/examples/reporter-sql-builder-test.pl @@ -88,7 +88,7 @@ my $report = { pivot_label => 2, }; -$select = JSON->JSON2perl('{"select":[{"relation":"b312819df8fe889b50f70ea9fa054e72","path":"au-home_ou-aou-shortname","alias":"ILS User:Home Library:Short (Policy) Name","column":{"transform":"Bare","colname":"shortname"}},{"relation":"80bfa74cd4909b585f6187fe8f8591c5","path":"au-survey_responses-asvr-survey-asv-name","alias":"ILS User:Survey Responses:survey:name","column":{"transform":"Bare","colname":"name"}},{"relation":"8a6cb366f41b2b8186df7c7749ff41ba","path":"au-survey_responses-asvr-answer-asva-answer","alias":"ILS User:Survey Responses:answer:Answer Text","column":{"transform":"Bare","colname":"answer"}},{"relation":"8bcc25c96aa5a71f7a76309077753e67","path":"au-id","alias":"count","column":{"transform":"count","colname":"id"}}],"from":{"table":"actor.usr","path":"au","alias":"8bcc25c96aa5a71f7a76309077753e67","join":{"id-survey_responses":{"key":"usr","type":"left","table":"action.survey_response","path":"au-survey_responses-asvr","alias":"cab1b47d26fa649f9a795d191bac0642","join":{"survey":{"key":"id","table":"action.survey","path":"au-survey_responses-asvr-survey-asv","alias":"80bfa74cd4909b585f6187fe8f8591c5"},"answer":{"key":"id","table":"action.survey_answer","path":"au-survey_responses-asvr-answer-asva","alias":"8a6cb366f41b2b8186df7c7749ff41ba"}}},"home_ou":{"key":"id","table":"actor.org_unit","path":"au-home_ou-aou","alias":"b312819df8fe889b50f70ea9fa054e72"}}},"where":[{"relation":"80bfa74cd4909b585f6187fe8f8591c5","path":"au-survey_responses-asvr-survey-asv-id","column":{"transform":"Bare","colname":"id"},"condition":{"in":"::P0"}},{"relation":"b312819df8fe889b50f70ea9fa054e72","path":"au-home_ou-aou-id","column":{"transform":"Bare","colname":"id"},"condition":{"in":"::P1"}}],"having":[],"order_by":[]}'); +$report = JSON->JSON2perl('{"select":[{"relation":"b312819df8fe889b50f70ea9fa054e72","path":"au-home_ou-aou-shortname","alias":"ILS User:Home Library:Short (Policy) Name","column":{"transform":"Bare","colname":"shortname"}},{"relation":"80bfa74cd4909b585f6187fe8f8591c5","path":"au-survey_responses-asvr-survey-asv-name","alias":"ILS User:Survey Responses:survey:name","column":{"transform":"Bare","colname":"name"}},{"relation":"8a6cb366f41b2b8186df7c7749ff41ba","path":"au-survey_responses-asvr-answer-asva-answer","alias":"ILS User:Survey Responses:answer:Answer Text","column":{"transform":"Bare","colname":"answer"}},{"relation":"8bcc25c96aa5a71f7a76309077753e67","path":"au-id","alias":"count","column":{"transform":"count","colname":"id"}}],"from":{"table":"actor.usr","path":"au","alias":"8bcc25c96aa5a71f7a76309077753e67","join":{"id-survey_responses":{"key":"usr","type":"left","table":"action.survey_response","path":"au-survey_responses-asvr","alias":"cab1b47d26fa649f9a795d191bac0642","join":{"survey":{"key":"id","table":"action.survey","path":"au-survey_responses-asvr-survey-asv","alias":"80bfa74cd4909b585f6187fe8f8591c5"},"answer":{"key":"id","table":"action.survey_answer","path":"au-survey_responses-asvr-answer-asva","alias":"8a6cb366f41b2b8186df7c7749ff41ba"}}},"home_ou":{"key":"id","table":"actor.org_unit","path":"au-home_ou-aou","alias":"b312819df8fe889b50f70ea9fa054e72"}}},"where":[{"relation":"80bfa74cd4909b585f6187fe8f8591c5","path":"au-survey_responses-asvr-survey-asv-id","column":{"transform":"Bare","colname":"id"},"condition":{"in":"::P0"}},{"relation":"b312819df8fe889b50f70ea9fa054e72","path":"au-home_ou-aou-id","column":{"transform":"Bare","colname":"id"},"condition":{"in":"::P1"}}],"having":[],"order_by":[]}'); my $params = { PARAM1 => [ 18, 19, 20, 21, 22, 23 ], diff --git a/Open-ILS/src/perlmods/OpenILS/Reporter/SQLBuilder.pm b/Open-ILS/src/perlmods/OpenILS/Reporter/SQLBuilder.pm index 0c976aa3e8..20aad45b11 100644 --- a/Open-ILS/src/perlmods/OpenILS/Reporter/SQLBuilder.pm +++ b/Open-ILS/src/perlmods/OpenILS/Reporter/SQLBuilder.pm @@ -26,12 +26,6 @@ sub set_builder { return $self; } -sub find_relation { - my $self = shift; - my $rel = shift; - return $self->builder->{_rels}->{$rel}; -} - sub builder { my $self = shift; return $self->{_builder}; @@ -173,7 +167,7 @@ sub set_where { return $self unless (@cols && defined($cols[0])); @cols = @{ $cols[0] } if (@cols == 1 && ref($cols[0]) eq 'ARRAY'); - push @{ $self->{_where} }, map { OpenILS::Reporter::SQLBuilder::Column::Where->new( $_ )->set_builder( $self->builder ) } @cols; + push @{ $self->{_where} }, map { OpenILS::Reporter::SQLBuilder::Column::Where->new( $_, $self->{_from}->builder->{_rels} )->set_builder( $self->builder ) } @cols; return $self; } @@ -444,6 +438,8 @@ sub new { $self->{_aggregate} = $col_data->{aggregate}; + $self->{_rels} = shift; + if (ref($self->{_column})) { my $trans = $self->{_column}->{transform} || 'Bare'; my $pkg = "OpenILS::Reporter::SQLBuilder::Column::Transform::$trans"; @@ -462,6 +458,11 @@ sub new { return $self; } +sub find_relation { + my $self = shift; + return $self->{_rels}->{$self->{_relation}}; +} + sub name { my $self = shift; if (ref($self->{_column})) { @@ -911,9 +912,9 @@ sub toSQL { my $sql; - my $rel = $self->find_relation($self->{_relation}); - if ($rel && $rel->is_join && $rel->join_type ne 'inner') { - $sql = "($sql IS NULL OR "; + my $rel = $self->find_relation(); + if ($rel && $rel->is_nullable) { + $sql = "(". $self->SUPER::toSQL ." IS NULL OR "; } $sql .= $self->SUPER::toSQL; @@ -946,7 +947,7 @@ sub toSQL { $sql .= " $op " . $val->toSQL; } - if ($rel && $rel->is_join && $rel->join_type ne 'inner') { + if ($rel && $rel->is_nullable) { $sql .= ")"; } @@ -1015,6 +1016,11 @@ sub add_join { return $self; } +sub is_nullable { + my $self = shift; + return $self->{_nullable}; +} + sub is_join { my $self = shift; my $j = shift; @@ -1060,6 +1066,8 @@ sub build { $self->{_join_type} = shift; + $self->{_right_rel}->set_builder($self->{_left_rel}->builder); + $self->{_right_rel}->is_join(1); $self->{_right_rel}->join_type($self->{_join_type}); @@ -1070,12 +1078,13 @@ sub build { sub toSQL { my $self = shift; + my $dir = shift; my $sql = "JOIN " . $self->{_right_rel}->toSQL . ' ON ("' . $self->{_left_rel}->{_alias} . '"."' . $self->{_left_col} . '" = "' . $self->{_right_rel}->{_alias} . '"."' . $self->{_right_col} . '")'; - $sql .= $_->toSQL for (@{ $self->{_right_rel}->{_join} }); + $sql .= $_->toSQL($dir) for (@{ $self->{_right_rel}->{_join} }); return $sql; } @@ -1086,9 +1095,16 @@ use base qw/OpenILS::Reporter::SQLBuilder::Join/; sub toSQL { my $self = shift; + my $dir = shift; #return $self->{_sql} if ($self->{_sql}); - my $sql = "\n\tLEFT OUTER ". $self->SUPER::toSQL; + my $_nullable_rel = $dir && $dir eq 'r' ? '_left_rel' : '_right_rel'; + $self->{_right_rel}->{_nullable} = 'l'; + $self->{$_nullable_rel}->{_nullable} = $dir; + + my $j = $dir && $dir eq 'r' ? 'FULL OUTER' : 'LEFT OUTER'; + + my $sql = "\n\t$j ". $self->SUPER::toSQL('l'); #$sql .= $_->toSQL for (@{ $self->{_right_rel}->{_join} }); @@ -1101,9 +1117,16 @@ use base qw/OpenILS::Reporter::SQLBuilder::Join/; sub toSQL { my $self = shift; + my $dir = shift; #return $self->{_sql} if ($self->{_sql}); - my $sql = "\n\tRIGHT OUTER ". $self->SUPER::toSQL; + my $_nullable_rel = $dir && $dir eq 'l' ? '_right_rel' : '_left_rel'; + $self->{_left_rel}->{_nullable} = 'r'; + $self->{$_nullable_rel}->{_nullable} = $dir; + + my $j = $dir && $dir eq 'l' ? 'FULL OUTER' : 'RIGHT OUTER'; + + my $sql = "\n\t$j ". $self->SUPER::toSQL('r'); #$sql .= $_->toSQL for (@{ $self->{_right_rel}->{_join} }); @@ -1116,9 +1139,15 @@ use base qw/OpenILS::Reporter::SQLBuilder::Join/; sub toSQL { my $self = shift; + my $dir = shift; #return $self->{_sql} if ($self->{_sql}); - my $sql = "\n\tINNER ". $self->SUPER::toSQL; + my $_nullable_rel = $dir && $dir eq 'l' ? '_right_rel' : '_left_rel'; + $self->{$_nullable_rel}->{_nullable} = $dir; + + my $j = $dir ? ( $dir eq 'l' ? 'LEFT OUTER' : ( $dir eq 'r' ? 'RIGHT OUTER' : 'FULL OUTER' ) ) : 'INNER'; + + my $sql = "\n\t$j ". $self->SUPER::toSQL; #$sql .= $_->toSQL for (@{ $self->{_right_rel}->{_join} }); @@ -1133,7 +1162,10 @@ sub toSQL { my $self = shift; #return $self->{_sql} if ($self->{_sql}); - my $sql = "\n\tFULL OUTER ". $self->SUPER::toSQL; + $self->{_right_rel}->{_nullable} = 'f'; + $self->{_left_rel}->{_nullable} = 'f'; + + my $sql = "\n\tFULL OUTER ". $self->SUPER::toSQL('f'); #$sql .= $_->toSQL for (@{ $self->{_right_rel}->{_join} }); -- 2.11.0