1 #-------------------------------------------------------------------------------------------------
2 package OpenILS::Reporter::SQLBuilder;
6 $class = ref($class) || $class;
8 return bless { _sql => undef } => $class;
14 $self->{_params} = $p;
20 return $self->{_builder}->{_params}->{$p};
25 $self->{_builder} = shift;
31 return $self->{_builder};
37 $self->builder->{_relative_time} = $t if (defined $t);
38 return $self->builder->{_relative_time};
45 if (defined($val) && $val =~ /^::(.+)$/o) {
46 $val = $self->get_param($1);
49 if (defined($val) && !ref($val)) {
61 my $rs = OpenILS::Reporter::SQLBuilder::ResultSet->new;
63 if (!$report->{order_by} || @{$report->{order_by}} == 0) {
64 $report->{order_by} = $report->{select};
67 $rs->is_subquery( 1 ) if ( $report->{alias} );
69 $rs ->set_builder( $self )
70 ->set_subquery_alias( $report->{alias} )
71 ->set_select( $report->{select} )
72 ->set_from( $report->{from} )
73 ->set_where( $report->{where} )
74 ->set_having( $report->{having} )
75 ->set_order_by( $report->{order_by} )
76 ->set_pivot_data( $report->{pivot_data} )
77 ->set_pivot_label( $report->{pivot_label} )
78 ->set_pivot_default( $report->{pivot_default} );
84 #-------------------------------------------------------------------------------------------------
85 package OpenILS::Reporter::SQLBuilder::ResultSet;
86 use base qw/OpenILS::Reporter::SQLBuilder/;
91 $self->{_is_subquery} = $flag if (defined $flag);
92 return $self->{_is_subquery};
97 return $self->builder->{_pivot_data};
102 return $self->builder->{_pivot_label};
107 return $self->builder->{_pivot_default};
110 sub set_pivot_default {
113 $self->builder->{_pivot_default} = $p if (defined $p);
120 $self->builder->{_pivot_data} = $p if (defined $p);
124 sub set_pivot_label {
127 $self->builder->{_pivot_label} = $p if (defined $p);
131 sub set_subquery_alias {
134 $self->{_alias} = $alias if (defined $alias);
142 $self->{_select} = [];
144 return $self unless (@cols && defined($cols[0]));
145 @cols = @{ $cols[0] } if (@cols == 1 && ref($cols[0]) eq 'ARRAY');
147 push @{ $self->{_select} }, map { OpenILS::Reporter::SQLBuilder::Column::Select->new( $_ )->set_builder( $self->builder ) } @cols;
156 $self->{_from} = OpenILS::Reporter::SQLBuilder::Relation->parse( $f, $self->builder );
165 $self->{_where} = [];
167 return $self unless (@cols && defined($cols[0]));
168 @cols = @{ $cols[0] } if (@cols == 1 && ref($cols[0]) eq 'ARRAY');
170 push @{ $self->{_where} }, map { OpenILS::Reporter::SQLBuilder::Column::Where->new( $_ )->set_builder( $self->builder ) } @cols;
179 $self->{_having} = [];
181 return $self unless (@cols && defined($cols[0]));
182 @cols = @{ $cols[0] } if (@cols == 1 && ref($cols[0]) eq 'ARRAY');
184 push @{ $self->{_having} }, map { OpenILS::Reporter::SQLBuilder::Column::Having->new( $_ )->set_builder( $self->builder ) } @cols;
193 $self->{_order_by} = [];
195 return $self unless (@cols && defined($cols[0]));
196 @cols = @{ $cols[0] } if (@cols == 1 && ref($cols[0]) eq 'ARRAY');
198 push @{ $self->{_order_by} }, map { OpenILS::Reporter::SQLBuilder::Column::OrderBy->new( $_ )->set_builder( $self->builder ) } @cols;
203 sub column_label_list {
207 push @labels, $self->resolve_param( $_->{_alias} ) for ( @{ $self->{_select} } );
214 $base = 1 unless (defined $base);
219 for my $c ( @{ $self->{_select} } ) {
220 if ($base == 0 && !$seen_label && defined($self->pivot_label) && $gcount == $self->pivot_label - 1) {
224 push @group_by, $gcount if (!$c->is_aggregate);
234 return $self->{_sql} if ($self->{_sql});
238 if ($self->is_subquery) {
242 $sql .= "SELECT\t" . join(",\n\t", map { $_->toSQL } @{ $self->{_select} }) . "\n" if (@{ $self->{_select} });
243 $sql .= " FROM\t" . $self->{_from}->toSQL . "\n" if ($self->{_from});
244 $sql .= " WHERE\t" . join("\n\tAND ", map { $_->toSQL } @{ $self->{_where} }) . "\n" if (@{ $self->{_where} });
246 my @group_by = $self->group_by_list;
248 $sql .= ' GROUP BY ' . join(', ', @group_by) . "\n" if (@group_by);
249 $sql .= " HAVING " . join("\n\tAND ", map { $_->toSQL } @{ $self->{_having} }) . "\n" if (@{ $self->{_having} });
250 $sql .= ' ORDER BY ' . join(', ', map { $_->toSQL } @{ $self->{_order_by} }) . "\n" if (@{ $self->{_order_by} });
252 if ($self->is_subquery) {
253 $sql .= ') '. $self->{_alias} . "\n";
256 return $self->{_sql} = $sql;
260 #-------------------------------------------------------------------------------------------------
261 package OpenILS::Reporter::SQLBuilder::Input;
262 use base qw/OpenILS::Reporter::SQLBuilder/;
266 my $self = $class->SUPER::new;
268 my $col_data = shift;
270 if (ref($col_data)) {
271 $self->{params} = $col_data->{params};
272 my $trans = $col_data->{transform} || 'Bare';
273 my $pkg = "OpenILS::Reporter::SQLBuilder::Input::Transform::$trans";
274 if (UNIVERSAL::can($pkg => 'toSQL')) {
275 $self->{_transform} = $trans;
277 $self->{_transform} = 'GenericTransform';
279 } elsif( defined($col_data) ) {
280 $self->{_transform} = 'Bare';
281 $self->{params} = $col_data;
283 $self->{_transform} = 'NULL';
293 my $type = $self->{_transform};
294 return $self->{_sql} if ($self->{_sql});
295 my $toSQL = "OpenILS::Reporter::SQLBuilder::Input::Transform::${type}::toSQL";
296 return $self->{_sql} = $self->$toSQL;
299 #-------------------------------------------------------------------------------------------------
300 package OpenILS::Reporter::SQLBuilder::Input::Transform::GenericTransform;
304 my $func = $self->{transform};
307 @params = @{ $self->{params} } if ($self->{params});
309 my $sql = $func . '(\'';
310 $sql .= join("','", @params) if (@params);
317 #-------------------------------------------------------------------------------------------------
318 package OpenILS::Reporter::SQLBuilder::Input::Transform::NULL;
325 #-------------------------------------------------------------------------------------------------
326 package OpenILS::Reporter::SQLBuilder::Input::Transform::Bare;
331 my $val = $self->{params};
332 $val = $$val[0] if (ref($val));
334 $val =~ s/\\/\\\\/go;
341 #-------------------------------------------------------------------------------------------------
342 package OpenILS::Reporter::SQLBuilder::Input::Transform::age;
347 my $val = $self->{params};
348 $val = $$val[0] if (ref($val));
350 $val =~ s/\\/\\\\/go;
353 return "AGE(NOW(),'" . $val . "'::TIMESTAMPTZ)";
356 sub is_aggregate { return 0 }
359 #-------------------------------------------------------------------------------------------------
360 package OpenILS::Reporter::SQLBuilder::Input::Transform::relative_year;
365 my $rtime = $self->relative_time || 'now';
367 $rtime =~ s/\\/\\\\/go;
368 $rtime =~ s/'/\\'/go;
370 my $val = $self->{params};
371 $val = $$val[0] if (ref($val));
373 $val =~ s/\\/\\\\/go;
376 return "EXTRACT(YEAR FROM '$rtime'::TIMESTAMPTZ + '$val years')";
380 #-------------------------------------------------------------------------------------------------
381 package OpenILS::Reporter::SQLBuilder::Input::Transform::relative_month;
386 my $rtime = $self->relative_time || 'now';
388 $rtime =~ s/\\/\\\\/go;
389 $rtime =~ s/'/\\'/go;
391 my $val = $self->{params};
392 $val = $$val[0] if (ref($val));
394 $val =~ s/\\/\\\\/go;
397 return "EXTRACT(YEAR FROM '$rtime'::TIMESTAMPTZ + '$val months')" .
398 " || '-' || LPAD(EXTRACT(MONTH FROM '$rtime'::TIMESTAMPTZ + '$val months'),2,'0')";
402 #-------------------------------------------------------------------------------------------------
403 package OpenILS::Reporter::SQLBuilder::Input::Transform::relative_date;
408 my $rtime = $self->relative_time || 'now';
410 $rtime =~ s/\\/\\\\/go;
411 $rtime =~ s/'/\\'/go;
413 my $val = $self->{params};
414 $val = $$val[0] if (ref($val));
416 $val =~ s/\\/\\\\/go;
419 return "DATE('$rtime'::TIMESTAMPTZ + '$val days')";
423 #-------------------------------------------------------------------------------------------------
424 package OpenILS::Reporter::SQLBuilder::Input::Transform::relative_week;
429 my $rtime = $self->relative_time || 'now';
431 $rtime =~ s/\\/\\\\/go;
432 $rtime =~ s/'/\\'/go;
434 my $val = $self->{params};
435 $val = $$val[0] if (ref($val));
437 $val =~ s/\\/\\\\/go;
440 return "EXTRACT(WEEK FROM '$rtime'::TIMESTAMPTZ + '$val weeks')";
444 #-------------------------------------------------------------------------------------------------
445 package OpenILS::Reporter::SQLBuilder::Column;
446 use base qw/OpenILS::Reporter::SQLBuilder/;
450 my $self = $class->SUPER::new;
452 my $col_data = shift;
453 $self->{_relation} = $col_data->{relation};
454 $self->{_column} = $col_data->{column};
456 $self->{_aggregate} = $col_data->{aggregate};
458 if (ref($self->{_column})) {
459 my $trans = $self->{_column}->{transform} || 'Bare';
460 my $pkg = "OpenILS::Reporter::SQLBuilder::Column::Transform::$trans";
461 if (UNIVERSAL::can($pkg => 'toSQL')) {
462 $self->{_transform} = $trans;
464 $self->{_transform} = 'GenericTransform';
466 } elsif( defined($self->{_column}) ) {
467 $self->{_transform} = 'Bare';
469 $self->{_transform} = 'NULL';
478 return $self->builder->{_rels}->{$self->{_relation}};
483 if (ref($self->{_column})) {
484 return $self->{_column}->{colname};
486 return $self->{_column};
492 my $type = $self->{_transform};
493 return $self->{_sql} if ($self->{_sql});
494 my $toSQL = "OpenILS::Reporter::SQLBuilder::Column::Transform::${type}::toSQL";
495 return $self->{_sql} = $self->$toSQL;
500 my $type = $self->{_transform};
501 my $is_agg = "OpenILS::Reporter::SQLBuilder::Column::Transform::${type}::is_aggregate";
502 return $self->$is_agg;
506 #-------------------------------------------------------------------------------------------------
507 package OpenILS::Reporter::SQLBuilder::Column::OrderBy;
508 use base qw/OpenILS::Reporter::SQLBuilder::Column/;
512 my $self = $class->SUPER::new(@_);
514 my $col_data = shift;
515 $self->{_direction} = $col_data->{direction} || 'ascending';
521 my $dir = ($self->{_direction} =~ /^d/oi) ? 'DESC' : 'ASC';
522 return $self->{_sql} if ($self->{_sql});
523 return $self->{_sql} = $self->SUPER::toSQL . " $dir";
527 #-------------------------------------------------------------------------------------------------
528 package OpenILS::Reporter::SQLBuilder::Column::Select;
529 use base qw/OpenILS::Reporter::SQLBuilder::Column/;
533 my $self = $class->SUPER::new(@_);
535 my $col_data = shift;
536 $self->{_alias} = $col_data->{alias} || $self->name;
542 return $self->{_sql} if ($self->{_sql});
543 return $self->{_sql} = $self->SUPER::toSQL . ' AS "' . $self->resolve_param( $self->{_alias} ) . '"';
547 #-------------------------------------------------------------------------------------------------
548 package OpenILS::Reporter::SQLBuilder::Column::Transform::GenericTransform;
552 my $name = $self->name;
553 my $func = $self->{_column}->{transform};
556 @params = @{ $self->resolve_param( $self->{_column}->{params} ) } if ($self->{_column}->{params});
558 my $sql = $func . '("' . $self->{_relation} . '"."' . $self->name . '"';
559 $sql .= ",'" . join("','", @params) . "'" if (@params);
565 sub is_aggregate { return $self->{_aggregate} }
567 #-------------------------------------------------------------------------------------------------
568 package OpenILS::Reporter::SQLBuilder::Column::Transform::Bare;
572 return '"' . $self->{_relation} . '"."' . $self->name . '"';
575 sub is_aggregate { return 0 }
577 #-------------------------------------------------------------------------------------------------
578 package OpenILS::Reporter::SQLBuilder::Column::Transform::upper;
582 my $params = $self->resolve_param( $self->{_column}->{params} );
583 my $start = $$params[0];
584 my $len = $$params[1];
585 return 'UPPER("' . $self->{_relation} . '"."' . $self->name . '")';
588 sub is_aggregate { return 0 }
591 #-------------------------------------------------------------------------------------------------
592 package OpenILS::Reporter::SQLBuilder::Column::Transform::lower;
596 my $params = $self->resolve_param( $self->{_column}->{params} );
597 my $start = $$params[0];
598 my $len = $$params[1];
599 return 'LOWER("' . $self->{_relation} . '"."' . $self->name . '")';
602 sub is_aggregate { return 0 }
605 #-------------------------------------------------------------------------------------------------
606 package OpenILS::Reporter::SQLBuilder::Column::Transform::substring;
610 my $params = $self->resolve_param( $self->{_column}->{params} );
611 my $start = $$params[0];
612 my $len = $$params[1];
613 return 'SUBSTRING("' . $self->{_relation} . '"."' . $self->name . "\",$start,$len)";
616 sub is_aggregate { return 0 }
619 #-------------------------------------------------------------------------------------------------
620 package OpenILS::Reporter::SQLBuilder::Column::Transform::day_name;
624 return 'TO_CHAR("' . $self->{_relation} . '"."' . $self->name . '", \'Day\')';
627 sub is_aggregate { return 0 }
630 #-------------------------------------------------------------------------------------------------
631 package OpenILS::Reporter::SQLBuilder::Column::Transform::month_name;
635 return 'TO_CHAR("' . $self->{_relation} . '"."' . $self->name . '", \'Month\')';
638 sub is_aggregate { return 0 }
641 #-------------------------------------------------------------------------------------------------
642 package OpenILS::Reporter::SQLBuilder::Column::Transform::doy;
646 return 'EXTRACT(DOY FROM "' . $self->{_relation} . '"."' . $self->name . '")';
649 sub is_aggregate { return 0 }
652 #-------------------------------------------------------------------------------------------------
653 package OpenILS::Reporter::SQLBuilder::Column::Transform::woy;
657 return 'EXTRACT(WEEK FROM "' . $self->{_relation} . '"."' . $self->name . '")';
660 sub is_aggregate { return 0 }
663 #-------------------------------------------------------------------------------------------------
664 package OpenILS::Reporter::SQLBuilder::Column::Transform::moy;
668 return 'EXTRACT(MONTH FROM "' . $self->{_relation} . '"."' . $self->name . '")';
671 sub is_aggregate { return 0 }
674 #-------------------------------------------------------------------------------------------------
675 package OpenILS::Reporter::SQLBuilder::Column::Transform::qoy;
679 return 'EXTRACT(QUARTER FROM "' . $self->{_relation} . '"."' . $self->name . '")';
682 sub is_aggregate { return 0 }
685 #-------------------------------------------------------------------------------------------------
686 package OpenILS::Reporter::SQLBuilder::Column::Transform::dom;
690 return 'EXTRACT(DAY FROM "' . $self->{_relation} . '"."' . $self->name . '")';
693 sub is_aggregate { return 0 }
696 #-------------------------------------------------------------------------------------------------
697 package OpenILS::Reporter::SQLBuilder::Column::Transform::dow;
701 return 'EXTRACT(DOW FROM "' . $self->{_relation} . '"."' . $self->name . '")';
704 sub is_aggregate { return 0 }
707 #-------------------------------------------------------------------------------------------------
708 package OpenILS::Reporter::SQLBuilder::Column::Transform::year_trunc;
712 return 'EXTRACT(YEAR FROM "' . $self->{_relation} . '"."' . $self->name . '")';
715 sub is_aggregate { return 0 }
718 #-------------------------------------------------------------------------------------------------
719 package OpenILS::Reporter::SQLBuilder::Column::Transform::month_trunc;
723 return 'EXTRACT(YEAR FROM "' . $self->{_relation} . '"."' . $self->name . '")' .
724 ' || \'-\' || LPAD(EXTRACT(MONTH FROM "' . $self->{_relation} . '"."' . $self->name . '"),2,\'0\')';
727 sub is_aggregate { return 0 }
730 #-------------------------------------------------------------------------------------------------
731 package OpenILS::Reporter::SQLBuilder::Column::Transform::date_trunc;
735 return 'DATE("' . $self->{_relation} . '"."' . $self->name . '")';
738 sub is_aggregate { return 0 }
741 #-------------------------------------------------------------------------------------------------
742 package OpenILS::Reporter::SQLBuilder::Column::Transform::hour_trunc;
746 return 'EXTRACT(HOUR FROM "' . $self->{_relation} . '"."' . $self->name . '")';
749 sub is_aggregate { return 0 }
752 #-------------------------------------------------------------------------------------------------
753 package OpenILS::Reporter::SQLBuilder::Column::Transform::quarter;
757 return 'EXTRACT(YEAR FROM "' . $self->{_relation} . '"."' . $self->name . '")' .
758 ' || \'-Q\' || EXTRACT(QUARTER FROM "' . $self->{_relation} . '"."' . $self->name . '")';
761 sub is_aggregate { return 0 }
764 #-------------------------------------------------------------------------------------------------
765 package OpenILS::Reporter::SQLBuilder::Column::Transform::months_ago;
769 return 'EXTRACT(MONTH FROM AGE(NOW(),"' . $self->{_relation} . '"."' . $self->name . '"))';
772 sub is_aggregate { return 0 }
775 #-------------------------------------------------------------------------------------------------
776 package OpenILS::Reporter::SQLBuilder::Column::Transform::hod;
780 return 'EXTRACT(HOUR FROM "' . $self->{_relation} . '"."' . $self->name . '")';
783 sub is_aggregate { return 0 }
786 #-------------------------------------------------------------------------------------------------
787 package OpenILS::Reporter::SQLBuilder::Column::Transform::quarters_ago;
791 return 'EXTRACT(QUARTER FROM AGE(NOW(),"' . $self->{_relation} . '"."' . $self->name . '"))';
794 sub is_aggregate { return 0 }
797 #-------------------------------------------------------------------------------------------------
798 package OpenILS::Reporter::SQLBuilder::Column::Transform::age;
802 return 'AGE(NOW(),"' . $self->{_relation} . '"."' . $self->name . '")';
805 sub is_aggregate { return 0 }
808 #-------------------------------------------------------------------------------------------------
809 package OpenILS::Reporter::SQLBuilder::Column::Transform::first;
813 return 'FIRST("' . $self->{_relation} . '"."' . $self->name . '")';
816 sub is_aggregate { return 1 }
819 #-------------------------------------------------------------------------------------------------
820 package OpenILS::Reporter::SQLBuilder::Column::Transform::last;
824 return 'LAST("' . $self->{_relation} . '"."' . $self->name . '")';
827 sub is_aggregate { return 1 }
830 #-------------------------------------------------------------------------------------------------
831 package OpenILS::Reporter::SQLBuilder::Column::Transform::min;
835 return 'MIN("' . $self->{_relation} . '"."' . $self->name . '")';
838 sub is_aggregate { return 1 }
841 #-------------------------------------------------------------------------------------------------
842 package OpenILS::Reporter::SQLBuilder::Column::Transform::max;
846 return 'MAX("' . $self->{_relation} . '"."' . $self->name . '")';
849 sub is_aggregate { return 1 }
852 #-------------------------------------------------------------------------------------------------
853 package OpenILS::Reporter::SQLBuilder::Column::Transform::count;
857 return 'COUNT("' . $self->{_relation} . '"."' . $self->name . '")';
860 sub is_aggregate { return 1 }
863 #-------------------------------------------------------------------------------------------------
864 package OpenILS::Reporter::SQLBuilder::Column::Transform::count_distinct;
868 return 'COUNT(DISTINCT "' . $self->{_relation} . '"."' . $self->name . '")';
871 sub is_aggregate { return 1 }
874 #-------------------------------------------------------------------------------------------------
875 package OpenILS::Reporter::SQLBuilder::Column::Transform::sum;
879 return 'SUM("' . $self->{_relation} . '"."' . $self->name . '")';
882 sub is_aggregate { return 1 }
885 #-------------------------------------------------------------------------------------------------
886 package OpenILS::Reporter::SQLBuilder::Column::Transform::average;
890 return 'AVG("' . $self->{_relation} . '"."' . $self->name . '")';
893 sub is_aggregate { return 1 }
896 #-------------------------------------------------------------------------------------------------
897 package OpenILS::Reporter::SQLBuilder::Column::Where;
898 use base qw/OpenILS::Reporter::SQLBuilder::Column/;
902 my $self = $class->SUPER::new(@_);
904 my $col_data = shift;
905 $self->{_condition} = $col_data->{condition};
910 sub _flesh_conditions {
913 $cond = [$cond] unless (ref($cond) eq 'ARRAY');
917 push @out, OpenILS::Reporter::SQLBuilder::Input->new( $c )->set_builder( $builder );
926 return $self->{_sql} if ($self->{_sql});
930 my $rel = $self->find_relation();
931 if ($rel && $rel->is_nullable) {
932 $sql = "((". $self->SUPER::toSQL .") IS NULL OR ";
935 $sql .= $self->SUPER::toSQL;
937 my ($op) = keys %{ $self->{_condition} };
938 my $val = _flesh_conditions( $self->resolve_param( $self->{_condition}->{$op} ), $self->builder );
940 if (lc($op) eq 'in') {
941 $sql .= " IN (". join(",", map { $_->toSQL } @$val).")";
943 } elsif (lc($op) eq 'not in') {
944 $sql .= " NOT IN (". join(",", map { $_->toSQL } @$val).")";
946 } elsif (lc($op) eq 'is blank') {
947 $sql = '('. $self->SUPER::toSQL ." IS NULL OR ". $self->SUPER::toSQL ." = '')";
949 } elsif (lc($op) eq 'is not blank') {
950 $sql = '('. $self->SUPER::toSQL ." IS NOT NULL AND ". $self->SUPER::toSQL ." <> '')";
952 } elsif (lc($op) eq 'between') {
953 $sql .= " BETWEEN ". join(" AND ", map { $_->toSQL } @$val);
955 } elsif (lc($op) eq 'not between') {
956 $sql .= " NOT BETWEEN ". join(" AND ", map { $_->toSQL } @$val);
958 } elsif (lc($op) eq 'like') {
959 $val = $$val[0] if (ref($val) eq 'ARRAY');
961 $val =~ s/^'(.*)'$/$1/o;
964 $sql .= " LIKE '\%$val\%'";
966 } elsif (lc($op) eq 'ilike') {
967 $val = $$val[0] if (ref($val) eq 'ARRAY');
969 $val =~ s/^'(.*)'$/$1/o;
972 $sql .= " ILIKE '\%$val\%'";
975 $val = $$val[0] if (ref($val) eq 'ARRAY');
976 $sql .= " $op " . $val->toSQL;
979 if ($rel && $rel->is_nullable) {
983 return $self->{_sql} = $sql;
987 #-------------------------------------------------------------------------------------------------
988 package OpenILS::Reporter::SQLBuilder::Column::Having;
989 use base qw/OpenILS::Reporter::SQLBuilder::Column::Where/;
991 #-------------------------------------------------------------------------------------------------
992 package OpenILS::Reporter::SQLBuilder::Relation;
993 use base qw/OpenILS::Reporter::SQLBuilder/;
997 $self = $self->SUPER::new if (!ref($self));
999 my $rel_data = shift;
1001 $self->set_builder($b);
1003 $self->{_table} = $rel_data->{table};
1004 $self->{_alias} = $rel_data->{alias} || $self->{_table};
1005 $self->{_join} = [];
1006 $self->{_columns} = [];
1008 $self->builder->{_rels}{$self->{_alias}} = $self;
1010 if ($rel_data->{join}) {
1012 $_ => OpenILS::Reporter::SQLBuilder::Relation->parse( $rel_data->{join}->{$_}, $b ) => $rel_data->{join}->{$_}->{key} => $rel_data->{join}->{$_}->{type}
1013 ) for ( keys %{ $rel_data->{join} } );
1023 push @{ $self->{_columns} }, $col;
1029 return (grep { $_->name eq $col} @{ $self->{_columns} })[0];
1037 my $type = lc(shift()) || 'inner';
1039 if (UNIVERSAL::isa($col,'OpenILS::Reporter::SQLBuilder::Join')) {
1040 push @{ $self->{_join} }, $col;
1042 push @{ $self->{_join} }, OpenILS::Reporter::SQLBuilder::Join->build( $self => $col, $frel => $fkey, $type );
1050 return $self->{_nullable};
1056 $self->{_is_join} = $j if ($j);
1057 return $self->{_is_join};
1063 $self->{_join_type} = $j if ($j);
1064 return $self->{_join_type};
1069 return $self->{_sql} if ($self->{_sql});
1071 my $sql = $self->{_table} .' AS "'. $self->{_alias} .'"';
1073 if (!$self->is_join) {
1074 for my $j ( @{ $self->{_join} } ) {
1079 return $self->{_sql} = $sql;
1082 #-------------------------------------------------------------------------------------------------
1083 package OpenILS::Reporter::SQLBuilder::Join;
1084 use base qw/OpenILS::Reporter::SQLBuilder/;
1088 my $self = $class->SUPER::new if (!ref($class));
1090 $self->{_left_rel} = shift;
1091 ($self->{_left_col}) = split(/-/,shift());
1093 $self->{_right_rel} = shift;
1094 $self->{_right_col} = shift;
1096 $self->{_join_type} = shift;
1098 $self->{_right_rel}->set_builder($self->{_left_rel}->builder);
1100 $self->{_right_rel}->is_join(1);
1101 $self->{_right_rel}->join_type($self->{_join_type});
1103 bless $self => "OpenILS::Reporter::SQLBuilder::Join::$self->{_join_type}";
1105 if ( $self->{_join_type} eq 'inner' or !$self->{_join_type}) {
1106 $self->{_join_type} eq 'i';
1108 if ($self->{_join_type} eq 'left') {
1109 $self->{_right_rel}->{_nullable} = 'l';
1110 } elsif ($self->{_join_type} eq 'right') {
1111 $self->{_left_rel}->{_nullable} = 'r';
1113 $self->{_right_rel}->{_nullable} = 'f';
1114 $self->{_left_rel}->{_nullable} = 'f';
1125 my $sql = "JOIN " . $self->{_right_rel}->toSQL .
1126 ' ON ("' . $self->{_left_rel}->{_alias} . '"."' . $self->{_left_col} .
1127 '" = "' . $self->{_right_rel}->{_alias} . '"."' . $self->{_right_col} . '")';
1129 $sql .= $_->toSQL($dir) for (@{ $self->{_right_rel}->{_join} });
1134 #-------------------------------------------------------------------------------------------------
1135 package OpenILS::Reporter::SQLBuilder::Join::left;
1136 use base qw/OpenILS::Reporter::SQLBuilder::Join/;
1141 #return $self->{_sql} if ($self->{_sql});
1143 my $j = $dir && $dir eq 'r' ? 'FULL OUTER' : 'LEFT OUTER';
1145 my $sql = "\n\t$j ". $self->SUPER::toSQL('l');
1147 #$sql .= $_->toSQL for (@{ $self->{_right_rel}->{_join} });
1149 return $self->{_sql} = $sql;
1152 #-------------------------------------------------------------------------------------------------
1153 package OpenILS::Reporter::SQLBuilder::Join::right;
1154 use base qw/OpenILS::Reporter::SQLBuilder::Join/;
1159 #return $self->{_sql} if ($self->{_sql});
1161 my $_nullable_rel = $dir && $dir eq 'l' ? '_right_rel' : '_left_rel';
1162 $self->{_left_rel}->{_nullable} = 'r';
1163 $self->{$_nullable_rel}->{_nullable} = $dir;
1165 my $j = $dir && $dir eq 'l' ? 'FULL OUTER' : 'RIGHT OUTER';
1167 my $sql = "\n\t$j ". $self->SUPER::toSQL('r');
1169 #$sql .= $_->toSQL for (@{ $self->{_right_rel}->{_join} });
1171 return $self->{_sql} = $sql;
1174 #-------------------------------------------------------------------------------------------------
1175 package OpenILS::Reporter::SQLBuilder::Join::inner;
1176 use base qw/OpenILS::Reporter::SQLBuilder::Join/;
1181 #return $self->{_sql} if ($self->{_sql});
1183 my $_nullable_rel = $dir && $dir eq 'l' ? '_right_rel' : '_left_rel';
1184 $self->{$_nullable_rel}->{_nullable} = $dir;
1186 my $j = $dir ? ( $dir eq 'l' ? 'LEFT OUTER' : ( $dir eq 'r' ? 'RIGHT OUTER' : 'FULL OUTER' ) ) : 'INNER';
1188 my $sql = "\n\t$j ". $self->SUPER::toSQL;
1190 #$sql .= $_->toSQL for (@{ $self->{_right_rel}->{_join} });
1192 return $self->{_sql} = $sql;
1195 #-------------------------------------------------------------------------------------------------
1196 package OpenILS::Reporter::SQLBuilder::Join::cross;
1197 use base qw/OpenILS::Reporter::SQLBuilder::Join/;
1201 #return $self->{_sql} if ($self->{_sql});
1203 $self->{_right_rel}->{_nullable} = 'f';
1204 $self->{_left_rel}->{_nullable} = 'f';
1206 my $sql = "\n\tFULL OUTER ". $self->SUPER::toSQL('f');
1208 #$sql .= $_->toSQL for (@{ $self->{_right_rel}->{_join} });
1210 return $self->{_sql} = $sql;