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;
32 return $self->builder->{_rels}->{$rel};
37 return $self->{_builder};
43 $self->builder->{_relative_time} = $t if (defined $t);
44 return $self->builder->{_relative_time};
51 if (defined($val) && $val =~ /^::(.+)$/o) {
52 $val = $self->get_param($1);
55 if (defined($val) && !ref($val)) {
67 my $rs = OpenILS::Reporter::SQLBuilder::ResultSet->new;
69 if (!$report->{order_by} || @{$report->{order_by}} == 0) {
70 $report->{order_by} = $report->{select};
73 $rs->is_subquery( 1 ) if ( $report->{alias} );
75 $rs ->set_builder( $self )
76 ->set_subquery_alias( $report->{alias} )
77 ->set_select( $report->{select} )
78 ->set_from( $report->{from} )
79 ->set_where( $report->{where} )
80 ->set_having( $report->{having} )
81 ->set_order_by( $report->{order_by} )
82 ->set_pivot_data( $report->{pivot_data} )
83 ->set_pivot_label( $report->{pivot_label} )
84 ->set_pivot_default( $report->{pivot_default} );
90 #-------------------------------------------------------------------------------------------------
91 package OpenILS::Reporter::SQLBuilder::ResultSet;
92 use base qw/OpenILS::Reporter::SQLBuilder/;
97 $self->{_is_subquery} = $flag if (defined $flag);
98 return $self->{_is_subquery};
103 return $self->builder->{_pivot_data};
108 return $self->builder->{_pivot_label};
113 return $self->builder->{_pivot_default};
116 sub set_pivot_default {
119 $self->builder->{_pivot_default} = $p if (defined $p);
126 $self->builder->{_pivot_data} = $p if (defined $p);
130 sub set_pivot_label {
133 $self->builder->{_pivot_label} = $p if (defined $p);
137 sub set_subquery_alias {
140 $self->{_alias} = $alias if (defined $alias);
148 $self->{_select} = [];
150 return $self unless (@cols && defined($cols[0]));
151 @cols = @{ $cols[0] } if (@cols == 1 && ref($cols[0]) eq 'ARRAY');
153 push @{ $self->{_select} }, map { OpenILS::Reporter::SQLBuilder::Column::Select->new( $_ )->set_builder( $self->builder ) } @cols;
162 $self->{_from} = OpenILS::Reporter::SQLBuilder::Relation->parse( $f, $self );
171 $self->{_where} = [];
173 return $self unless (@cols && defined($cols[0]));
174 @cols = @{ $cols[0] } if (@cols == 1 && ref($cols[0]) eq 'ARRAY');
176 push @{ $self->{_where} }, map { OpenILS::Reporter::SQLBuilder::Column::Where->new( $_ )->set_builder( $self->builder ) } @cols;
185 $self->{_having} = [];
187 return $self unless (@cols && defined($cols[0]));
188 @cols = @{ $cols[0] } if (@cols == 1 && ref($cols[0]) eq 'ARRAY');
190 push @{ $self->{_having} }, map { OpenILS::Reporter::SQLBuilder::Column::Having->new( $_ )->set_builder( $self->builder ) } @cols;
199 $self->{_order_by} = [];
201 return $self unless (@cols && defined($cols[0]));
202 @cols = @{ $cols[0] } if (@cols == 1 && ref($cols[0]) eq 'ARRAY');
204 push @{ $self->{_order_by} }, map { OpenILS::Reporter::SQLBuilder::Column::OrderBy->new( $_ )->set_builder( $self->builder ) } @cols;
209 sub column_label_list {
213 push @labels, $self->resolve_param( $_->{_alias} ) for ( @{ $self->{_select} } );
220 $base = 1 unless (defined $base);
225 for my $c ( @{ $self->{_select} } ) {
226 if ($base == 0 && !$seen_label && defined($self->pivot_label) && $gcount == $self->pivot_label - 1) {
230 push @group_by, $gcount if (!$c->is_aggregate);
240 return $self->{_sql} if ($self->{_sql});
244 if ($self->is_subquery) {
248 $sql .= "SELECT\t" . join(",\n\t", map { $_->toSQL } @{ $self->{_select} }) . "\n" if (@{ $self->{_select} });
249 $sql .= " FROM\t" . $self->{_from}->toSQL . "\n" if ($self->{_from});
250 $sql .= " WHERE\t" . join("\n\tAND ", map { $_->toSQL } @{ $self->{_where} }) . "\n" if (@{ $self->{_where} });
252 my @group_by = $self->group_by_list;
254 $sql .= ' GROUP BY ' . join(', ', @group_by) . "\n" if (@group_by);
255 $sql .= " HAVING " . join("\n\tAND ", map { $_->toSQL } @{ $self->{_having} }) . "\n" if (@{ $self->{_having} });
256 $sql .= ' ORDER BY ' . join(', ', map { $_->toSQL } @{ $self->{_order_by} }) . "\n" if (@{ $self->{_order_by} });
258 if ($self->is_subquery) {
259 $sql .= ') '. $self->{_alias} . "\n";
262 return $self->{_sql} = $sql;
266 #-------------------------------------------------------------------------------------------------
267 package OpenILS::Reporter::SQLBuilder::Input;
268 use base qw/OpenILS::Reporter::SQLBuilder/;
272 my $self = $class->SUPER::new;
274 my $col_data = shift;
276 if (ref($col_data)) {
277 $self->{params} = $col_data->{params};
278 my $trans = $col_data->{transform} || 'Bare';
279 my $pkg = "OpenILS::Reporter::SQLBuilder::Input::Transform::$trans";
280 if (UNIVERSAL::can($pkg => 'toSQL')) {
281 $self->{_transform} = $trans;
283 $self->{_transform} = 'GenericTransform';
285 } elsif( defined($col_data) ) {
286 $self->{_transform} = 'Bare';
287 $self->{params} = $col_data;
289 $self->{_transform} = 'NULL';
299 my $type = $self->{_transform};
300 return $self->{_sql} if ($self->{_sql});
301 my $toSQL = "OpenILS::Reporter::SQLBuilder::Input::Transform::${type}::toSQL";
302 return $self->{_sql} = $self->$toSQL;
306 #-------------------------------------------------------------------------------------------------
307 package OpenILS::Reporter::SQLBuilder::Input::Transform::NULL;
314 #-------------------------------------------------------------------------------------------------
315 package OpenILS::Reporter::SQLBuilder::Input::Transform::Bare;
320 my $val = $self->{params};
321 $val = $$val[0] if (ref($val));
323 $val =~ s/\\/\\\\/go;
330 #-------------------------------------------------------------------------------------------------
331 package OpenILS::Reporter::SQLBuilder::Input::Transform::age;
336 my $val = $self->{params};
337 $val = $$val[0] if (ref($val));
339 $val =~ s/\\/\\\\/go;
342 return "AGE(NOW(),'" . $val . "'::TIMESTAMPTZ)";
345 sub is_aggregate { return 0 }
348 #-------------------------------------------------------------------------------------------------
349 package OpenILS::Reporter::SQLBuilder::Input::Transform::relative_year;
354 my $rtime = $self->relative_time || 'now';
356 $rtime =~ s/\\/\\\\/go;
357 $rtime =~ s/'/\\'/go;
359 my $val = $self->{params};
360 $val = $$val[0] if (ref($val));
362 $val =~ s/\\/\\\\/go;
365 return "EXTRACT(YEAR FROM '$rtime'::TIMESTAMPTZ + '$val years')";
369 #-------------------------------------------------------------------------------------------------
370 package OpenILS::Reporter::SQLBuilder::Input::Transform::relative_month;
375 my $rtime = $self->relative_time || 'now';
377 $rtime =~ s/\\/\\\\/go;
378 $rtime =~ s/'/\\'/go;
380 my $val = $self->{params};
381 $val = $$val[0] if (ref($val));
383 $val =~ s/\\/\\\\/go;
386 return "EXTRACT(YEAR FROM '$rtime'::TIMESTAMPTZ + '$val months')" .
387 " || '-' || LPAD(EXTRACT(MONTH FROM '$rtime'::TIMESTAMPTZ + '$val months'),2,'0')";
391 #-------------------------------------------------------------------------------------------------
392 package OpenILS::Reporter::SQLBuilder::Input::Transform::relative_date;
397 my $rtime = $self->relative_time || 'now';
399 $rtime =~ s/\\/\\\\/go;
400 $rtime =~ s/'/\\'/go;
402 my $val = $self->{params};
403 $val = $$val[0] if (ref($val));
405 $val =~ s/\\/\\\\/go;
408 return "DATE('$rtime'::TIMESTAMPTZ + '$val days')";
412 #-------------------------------------------------------------------------------------------------
413 package OpenILS::Reporter::SQLBuilder::Input::Transform::relative_week;
418 my $rtime = $self->relative_time || 'now';
420 $rtime =~ s/\\/\\\\/go;
421 $rtime =~ s/'/\\'/go;
423 my $val = $self->{params};
424 $val = $$val[0] if (ref($val));
426 $val =~ s/\\/\\\\/go;
429 return "EXTRACT(WEEK FROM '$rtime'::TIMESTAMPTZ + '$val weeks')";
433 #-------------------------------------------------------------------------------------------------
434 package OpenILS::Reporter::SQLBuilder::Column;
435 use base qw/OpenILS::Reporter::SQLBuilder/;
439 my $self = $class->SUPER::new;
441 my $col_data = shift;
442 $self->{_relation} = $col_data->{relation};
443 $self->{_column} = $col_data->{column};
445 $self->{_aggregate} = $col_data->{aggregate};
447 if (ref($self->{_column})) {
448 my $trans = $self->{_column}->{transform} || 'Bare';
449 my $pkg = "OpenILS::Reporter::SQLBuilder::Column::Transform::$trans";
450 if (UNIVERSAL::can($pkg => 'toSQL')) {
451 $self->{_transform} = $trans;
453 $self->{_transform} = 'GenericTransform';
455 } elsif( defined($self->{_column}) ) {
456 $self->{_transform} = 'Bare';
458 $self->{_transform} = 'NULL';
467 if (ref($self->{_column})) {
468 return $self->{_column}->{colname};
470 return $self->{_column};
476 my $type = $self->{_transform};
477 return $self->{_sql} if ($self->{_sql});
478 my $toSQL = "OpenILS::Reporter::SQLBuilder::Column::Transform::${type}::toSQL";
479 return $self->{_sql} = $self->$toSQL;
484 my $type = $self->{_transform};
485 my $is_agg = "OpenILS::Reporter::SQLBuilder::Column::Transform::${type}::is_aggregate";
486 return $self->$is_agg;
490 #-------------------------------------------------------------------------------------------------
491 package OpenILS::Reporter::SQLBuilder::Column::OrderBy;
492 use base qw/OpenILS::Reporter::SQLBuilder::Column/;
496 my $self = $class->SUPER::new(@_);
498 my $col_data = shift;
499 $self->{_direction} = $col_data->{direction} || 'ascending';
505 my $dir = ($self->{_direction} =~ /^d/oi) ? 'DESC' : 'ASC';
506 return $self->{_sql} if ($self->{_sql});
507 return $self->{_sql} = $self->SUPER::toSQL . " $dir";
511 #-------------------------------------------------------------------------------------------------
512 package OpenILS::Reporter::SQLBuilder::Column::Select;
513 use base qw/OpenILS::Reporter::SQLBuilder::Column/;
517 my $self = $class->SUPER::new(@_);
519 my $col_data = shift;
520 $self->{_alias} = $col_data->{alias} || $self->name;
526 return $self->{_sql} if ($self->{_sql});
527 return $self->{_sql} = $self->SUPER::toSQL . ' AS "' . $self->resolve_param( $self->{_alias} ) . '"';
531 #-------------------------------------------------------------------------------------------------
532 package OpenILS::Reporter::SQLBuilder::Column::Transform::GenericTransform;
536 my $name = $self->name;
537 my $func = $self->{_column}->{transform};
540 @params = @{ $self->resolve_param( $self->{_column}->{params} ) } if ($self->{_column}->{params});
542 my $sql = $func . '("' . $self->{_relation} . '"."' . $self->name . '"';
543 $sql .= ",'" . join("','", @params) . "'" if (@params);
549 sub is_aggregate { return $self->{_aggregate} }
551 #-------------------------------------------------------------------------------------------------
552 package OpenILS::Reporter::SQLBuilder::Column::Transform::Bare;
556 return '"' . $self->{_relation} . '"."' . $self->name . '"';
559 sub is_aggregate { return 0 }
561 #-------------------------------------------------------------------------------------------------
562 package OpenILS::Reporter::SQLBuilder::Column::Transform::upper;
566 my $params = $self->resolve_param( $self->{_column}->{params} );
567 my $start = $$params[0];
568 my $len = $$params[1];
569 return 'UPPER("' . $self->{_relation} . '"."' . $self->name . '")';
572 sub is_aggregate { return 0 }
575 #-------------------------------------------------------------------------------------------------
576 package OpenILS::Reporter::SQLBuilder::Column::Transform::lower;
580 my $params = $self->resolve_param( $self->{_column}->{params} );
581 my $start = $$params[0];
582 my $len = $$params[1];
583 return 'LOWER("' . $self->{_relation} . '"."' . $self->name . '")';
586 sub is_aggregate { return 0 }
589 #-------------------------------------------------------------------------------------------------
590 package OpenILS::Reporter::SQLBuilder::Column::Transform::substring;
594 my $params = $self->resolve_param( $self->{_column}->{params} );
595 my $start = $$params[0];
596 my $len = $$params[1];
597 return 'SUBSTRING("' . $self->{_relation} . '"."' . $self->name . "\",$start,$len)";
600 sub is_aggregate { return 0 }
603 #-------------------------------------------------------------------------------------------------
604 package OpenILS::Reporter::SQLBuilder::Column::Transform::day_name;
608 return 'TO_CHAR("' . $self->{_relation} . '"."' . $self->name . '", \'Day\')';
611 sub is_aggregate { return 0 }
614 #-------------------------------------------------------------------------------------------------
615 package OpenILS::Reporter::SQLBuilder::Column::Transform::month_name;
619 return 'TO_CHAR("' . $self->{_relation} . '"."' . $self->name . '", \'Month\')';
622 sub is_aggregate { return 0 }
625 #-------------------------------------------------------------------------------------------------
626 package OpenILS::Reporter::SQLBuilder::Column::Transform::doy;
630 return 'EXTRACT(DOY FROM "' . $self->{_relation} . '"."' . $self->name . '")';
633 sub is_aggregate { return 0 }
636 #-------------------------------------------------------------------------------------------------
637 package OpenILS::Reporter::SQLBuilder::Column::Transform::woy;
641 return 'EXTRACT(WEEK FROM "' . $self->{_relation} . '"."' . $self->name . '")';
644 sub is_aggregate { return 0 }
647 #-------------------------------------------------------------------------------------------------
648 package OpenILS::Reporter::SQLBuilder::Column::Transform::moy;
652 return 'EXTRACT(MONTH FROM "' . $self->{_relation} . '"."' . $self->name . '")';
655 sub is_aggregate { return 0 }
658 #-------------------------------------------------------------------------------------------------
659 package OpenILS::Reporter::SQLBuilder::Column::Transform::qoy;
663 return 'EXTRACT(QUARTER FROM "' . $self->{_relation} . '"."' . $self->name . '")';
666 sub is_aggregate { return 0 }
669 #-------------------------------------------------------------------------------------------------
670 package OpenILS::Reporter::SQLBuilder::Column::Transform::dom;
674 return 'EXTRACT(DAY FROM "' . $self->{_relation} . '"."' . $self->name . '")';
677 sub is_aggregate { return 0 }
680 #-------------------------------------------------------------------------------------------------
681 package OpenILS::Reporter::SQLBuilder::Column::Transform::dow;
685 return 'EXTRACT(DOW FROM "' . $self->{_relation} . '"."' . $self->name . '")';
688 sub is_aggregate { return 0 }
691 #-------------------------------------------------------------------------------------------------
692 package OpenILS::Reporter::SQLBuilder::Column::Transform::year_trunc;
696 return 'EXTRACT(YEAR FROM "' . $self->{_relation} . '"."' . $self->name . '")';
699 sub is_aggregate { return 0 }
702 #-------------------------------------------------------------------------------------------------
703 package OpenILS::Reporter::SQLBuilder::Column::Transform::month_trunc;
707 return 'EXTRACT(YEAR FROM "' . $self->{_relation} . '"."' . $self->name . '")' .
708 ' || \'-\' || LPAD(EXTRACT(MONTH FROM "' . $self->{_relation} . '"."' . $self->name . '"),2,\'0\')';
711 sub is_aggregate { return 0 }
714 #-------------------------------------------------------------------------------------------------
715 package OpenILS::Reporter::SQLBuilder::Column::Transform::date_trunc;
719 return 'DATE("' . $self->{_relation} . '"."' . $self->name . '")';
722 sub is_aggregate { return 0 }
725 #-------------------------------------------------------------------------------------------------
726 package OpenILS::Reporter::SQLBuilder::Column::Transform::hour_trunc;
730 return 'DATE_TRUNC("' . $self->{_relation} . '"."' . $self->name . '")';
733 sub is_aggregate { return 0 }
736 #-------------------------------------------------------------------------------------------------
737 package OpenILS::Reporter::SQLBuilder::Column::Transform::quarter;
741 return 'EXTRACT(YEAR FROM "' . $self->{_relation} . '"."' . $self->name . '")' .
742 ' || \'-Q\' || EXTRACT(QUARTER FROM "' . $self->{_relation} . '"."' . $self->name . '")';
745 sub is_aggregate { return 0 }
748 #-------------------------------------------------------------------------------------------------
749 package OpenILS::Reporter::SQLBuilder::Column::Transform::months_ago;
753 return 'EXTRACT(MONTH FROM AGE(NOW(),"' . $self->{_relation} . '"."' . $self->name . '"))';
756 sub is_aggregate { return 0 }
759 #-------------------------------------------------------------------------------------------------
760 package OpenILS::Reporter::SQLBuilder::Column::Transform::hod;
764 return 'EXTRACT(HOUR FROM "' . $self->{_relation} . '"."' . $self->name . '")';
767 sub is_aggregate { return 0 }
770 #-------------------------------------------------------------------------------------------------
771 package OpenILS::Reporter::SQLBuilder::Column::Transform::quarters_ago;
775 return 'EXTRACT(QUARTER FROM AGE(NOW(),"' . $self->{_relation} . '"."' . $self->name . '"))';
778 sub is_aggregate { return 0 }
781 #-------------------------------------------------------------------------------------------------
782 package OpenILS::Reporter::SQLBuilder::Column::Transform::age;
786 return 'AGE(NOW(),"' . $self->{_relation} . '"."' . $self->name . '")';
789 sub is_aggregate { return 0 }
792 #-------------------------------------------------------------------------------------------------
793 package OpenILS::Reporter::SQLBuilder::Column::Transform::first;
797 return 'FIRST("' . $self->{_relation} . '"."' . $self->name . '")';
800 sub is_aggregate { return 1 }
803 #-------------------------------------------------------------------------------------------------
804 package OpenILS::Reporter::SQLBuilder::Column::Transform::last;
808 return 'LAST("' . $self->{_relation} . '"."' . $self->name . '")';
811 sub is_aggregate { return 1 }
814 #-------------------------------------------------------------------------------------------------
815 package OpenILS::Reporter::SQLBuilder::Column::Transform::min;
819 return 'MIN("' . $self->{_relation} . '"."' . $self->name . '")';
822 sub is_aggregate { return 1 }
825 #-------------------------------------------------------------------------------------------------
826 package OpenILS::Reporter::SQLBuilder::Column::Transform::max;
830 return 'MAX("' . $self->{_relation} . '"."' . $self->name . '")';
833 sub is_aggregate { return 1 }
836 #-------------------------------------------------------------------------------------------------
837 package OpenILS::Reporter::SQLBuilder::Column::Transform::count;
841 return 'COUNT("' . $self->{_relation} . '"."' . $self->name . '")';
844 sub is_aggregate { return 1 }
847 #-------------------------------------------------------------------------------------------------
848 package OpenILS::Reporter::SQLBuilder::Column::Transform::count_distinct;
852 return 'COUNT(DISTINCT "' . $self->{_relation} . '"."' . $self->name . '")';
855 sub is_aggregate { return 1 }
858 #-------------------------------------------------------------------------------------------------
859 package OpenILS::Reporter::SQLBuilder::Column::Transform::sum;
863 return 'SUM("' . $self->{_relation} . '"."' . $self->name . '")';
866 sub is_aggregate { return 1 }
869 #-------------------------------------------------------------------------------------------------
870 package OpenILS::Reporter::SQLBuilder::Column::Transform::average;
874 return 'AVG("' . $self->{_relation} . '"."' . $self->name . '")';
877 sub is_aggregate { return 1 }
880 #-------------------------------------------------------------------------------------------------
881 package OpenILS::Reporter::SQLBuilder::Column::Where;
882 use base qw/OpenILS::Reporter::SQLBuilder::Column/;
886 my $self = $class->SUPER::new(@_);
888 my $col_data = shift;
889 $self->{_condition} = $col_data->{condition};
894 sub _flesh_conditions {
897 $cond = [$cond] unless (ref($cond) eq 'ARRAY');
901 push @out, OpenILS::Reporter::SQLBuilder::Input->new( $c )->set_builder( $builder );
910 return $self->{_sql} if ($self->{_sql});
914 my $rel = $self->find_relation($self->{_relation});
916 if ($rel && $rel->is_join && $rel->join_type ne 'inner') {
917 $sql = "($sql IS NULL OR ";
920 $sql .= $self->SUPER::toSQL;
922 my ($op) = keys %{ $self->{_condition} };
923 my $val = _flesh_conditions( $self->resolve_param( $self->{_condition}->{$op} ), $self->builder );
925 if (lc($op) eq 'in') {
926 $sql .= " IN (". join(",", map { $_->toSQL } @$val).")";
927 } elsif (lc($op) eq 'not in') {
928 $sql .= " NOT IN (". join(",", map { $_->toSQL } @$val).")";
929 } elsif (lc($op) eq 'between') {
930 $sql .= " BETWEEN ". join(" AND ", map { $_->toSQL } @$val);
931 } elsif (lc($op) eq 'not between') {
932 $sql .= " NOT BETWEEN ". join(" AND ", map { $_->toSQL } @$val);
933 } elsif (lc($op) eq 'like') {
934 $val = $$val[0] if (ref($val) eq 'ARRAY');
935 $val =~ s/^'(.*)'$/$1/o;
938 $sql .= " LIKE '\%$val\%'";
939 } elsif (lc($op) eq 'ilike') {
940 $val = $$val[0] if (ref($val) eq 'ARRAY');
941 $val =~ s/^'(.*)'$/$1/o;
944 $sql .= " ILIKE '\%$val\%'";
946 $val = $$val[0] if (ref($val) eq 'ARRAY');
947 $sql .= " $op " . $val->toSQL;
950 if ($rel && $rel->is_join && $rel->join_type ne 'inner') {
954 return $self->{_sql} = $sql;
958 #-------------------------------------------------------------------------------------------------
959 package OpenILS::Reporter::SQLBuilder::Column::Having;
960 use base qw/OpenILS::Reporter::SQLBuilder::Column::Where/;
962 #-------------------------------------------------------------------------------------------------
963 package OpenILS::Reporter::SQLBuilder::Relation;
964 use base qw/OpenILS::Reporter::SQLBuilder/;
968 $self = $self->SUPER::new if (!ref($self));
970 my $rel_data = shift;
972 $self->set_builder($b);
974 $self->{_table} = $rel_data->{table};
975 $self->{_alias} = $rel_data->{alias} || $self->name;
977 $self->{_columns} = [];
979 $self->builder->{_rels}{$self->{_alias}} = $self;
981 if ($rel_data->{join}) {
983 $_ => OpenILS::Reporter::SQLBuilder::Relation->parse( $rel_data->{join}->{$_}, $b ) => $rel_data->{join}->{$_}->{key} => $rel_data->{join}->{$_}->{type}
984 ) for ( keys %{ $rel_data->{join} } );
994 push @{ $self->{_columns} }, $col;
1000 return (grep { $_->name eq $col} @{ $self->{_columns} })[0];
1008 my $type = lc(shift()) || 'inner';
1010 if (UNIVERSAL::isa($col,'OpenILS::Reporter::SQLBuilder::Join')) {
1011 push @{ $self->{_join} }, $col;
1013 push @{ $self->{_join} }, OpenILS::Reporter::SQLBuilder::Join->build( $self => $col, $frel => $fkey, $type );
1022 $self->{_is_join} = $j if ($j);
1023 return $self->{_is_join};
1029 $self->{_join_type} = $j if ($j);
1030 return $self->{_join_type};
1035 return $self->{_sql} if ($self->{_sql});
1037 my $sql = $self->{_table} .' AS "'. $self->{_alias} .'"';
1039 if (!$self->is_join) {
1040 for my $j ( @{ $self->{_join} } ) {
1045 return $self->{_sql} = $sql;
1048 #-------------------------------------------------------------------------------------------------
1049 package OpenILS::Reporter::SQLBuilder::Join;
1050 use base qw/OpenILS::Reporter::SQLBuilder/;
1054 my $self = $class->SUPER::new if (!ref($class));
1056 $self->{_left_rel} = shift;
1057 ($self->{_left_col}) = split(/-/,shift());
1059 $self->{_right_rel} = shift;
1060 $self->{_right_col} = shift;
1062 $self->{_join_type} = shift;
1064 $self->{_right_rel}->is_join(1);
1065 $self->{_right_rel}->join_type($self->{_join_type});
1067 bless $self => "OpenILS::Reporter::SQLBuilder::Join::$self->{_join_type}";
1075 my $sql = "JOIN " . $self->{_right_rel}->toSQL .
1076 ' ON ("' . $self->{_left_rel}->{_alias} . '"."' . $self->{_left_col} .
1077 '" = "' . $self->{_right_rel}->{_alias} . '"."' . $self->{_right_col} . '")';
1079 $sql .= $_->toSQL for (@{ $self->{_right_rel}->{_join} });
1084 #-------------------------------------------------------------------------------------------------
1085 package OpenILS::Reporter::SQLBuilder::Join::left;
1086 use base qw/OpenILS::Reporter::SQLBuilder::Join/;
1090 #return $self->{_sql} if ($self->{_sql});
1092 my $sql = "\n\tLEFT OUTER ". $self->SUPER::toSQL;
1094 #$sql .= $_->toSQL for (@{ $self->{_right_rel}->{_join} });
1096 return $self->{_sql} = $sql;
1099 #-------------------------------------------------------------------------------------------------
1100 package OpenILS::Reporter::SQLBuilder::Join::right;
1101 use base qw/OpenILS::Reporter::SQLBuilder::Join/;
1105 #return $self->{_sql} if ($self->{_sql});
1107 my $sql = "\n\tRIGHT OUTER ". $self->SUPER::toSQL;
1109 #$sql .= $_->toSQL for (@{ $self->{_right_rel}->{_join} });
1111 return $self->{_sql} = $sql;
1114 #-------------------------------------------------------------------------------------------------
1115 package OpenILS::Reporter::SQLBuilder::Join::inner;
1116 use base qw/OpenILS::Reporter::SQLBuilder::Join/;
1120 #return $self->{_sql} if ($self->{_sql});
1122 my $sql = "\n\tINNER ". $self->SUPER::toSQL;
1124 #$sql .= $_->toSQL for (@{ $self->{_right_rel}->{_join} });
1126 return $self->{_sql} = $sql;
1129 #-------------------------------------------------------------------------------------------------
1130 package OpenILS::Reporter::SQLBuilder::Join::cross;
1131 use base qw/OpenILS::Reporter::SQLBuilder::Join/;
1135 #return $self->{_sql} if ($self->{_sql});
1137 my $sql = "\n\tFULL OUTER ". $self->SUPER::toSQL;
1139 #$sql .= $_->toSQL for (@{ $self->{_right_rel}->{_join} });
1141 return $self->{_sql} = $sql;