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 $rs->is_subquery( 1 ) if ( $report->{alias} );
65 $rs ->set_builder( $self )
66 ->set_subquery_alias( $report->{alias} )
67 ->set_select( $report->{select} )
68 ->set_from( $report->{from} )
69 ->set_where( $report->{where} )
70 ->set_having( $report->{having} )
71 ->set_order_by( $report->{order_by} )
72 ->set_pivot_data( $report->{pivot_data} )
73 ->set_pivot_label( $report->{pivot_label} )
74 ->set_pivot_default( $report->{pivot_default} );
80 #-------------------------------------------------------------------------------------------------
81 package OpenILS::Reporter::SQLBuilder::ResultSet;
82 use base qw/OpenILS::Reporter::SQLBuilder/;
87 $self->{_is_subquery} = $flag if (defined $flag);
88 return $self->{_is_subquery};
93 return $self->{_pivot_data};
98 return $self->{_pivot_label};
103 return $self->{_pivot_label};
106 sub set_pivot_default {
109 $self->{_pivot_default} = $p if (defined $p);
116 $self->{_pivot_data} = $p if (defined $p);
120 sub set_pivot_label {
123 $self->{_pivot_label} = $p if (defined $p);
127 sub set_subquery_alias {
130 $self->{_alias} = $alias if (defined $alias);
138 $self->{_select} = [];
140 return $self unless (@cols && defined($cols[0]));
141 @cols = @{ $cols[0] } if (@cols == 1 && ref($cols[0]) eq 'ARRAY');
143 push @{ $self->{_select} }, map { OpenILS::Reporter::SQLBuilder::Column::Select->new( $_ )->set_builder( $self->builder ) } @cols;
152 $self->{_from} = OpenILS::Reporter::SQLBuilder::Relation->parse( $f );
161 $self->{_where} = [];
163 return $self unless (@cols && defined($cols[0]));
164 @cols = @{ $cols[0] } if (@cols == 1 && ref($cols[0]) eq 'ARRAY');
166 push @{ $self->{_where} }, map { OpenILS::Reporter::SQLBuilder::Column::Where->new( $_ )->set_builder( $self->builder ) } @cols;
175 $self->{_having} = [];
177 return $self unless (@cols && defined($cols[0]));
178 @cols = @{ $cols[0] } if (@cols == 1 && ref($cols[0]) eq 'ARRAY');
180 push @{ $self->{_having} }, map { OpenILS::Reporter::SQLBuilder::Column::Having->new( $_ )->set_builder( $self->builder ) } @cols;
189 $self->{_order_by} = [];
191 return $self unless (@cols && defined($cols[0]));
192 @cols = @{ $cols[0] } if (@cols == 1 && ref($cols[0]) eq 'ARRAY');
194 push @{ $self->{_order_by} }, map { OpenILS::Reporter::SQLBuilder::Column::OrderBy->new( $_ )->set_builder( $self->builder ) } @cols;
199 sub column_label_list {
203 push @labels, $self->resolve_param( $_->{_alias} ) for ( @{ $self->{_select} } );
210 $base = 1 unless (defined $base);
214 for my $c ( @{ $self->{_select} } ) {
215 push @group_by, $gcount if (!$c->is_aggregate);
225 return $self->{_sql} if ($self->{_sql});
229 if ($self->is_subquery) {
233 $sql .= "SELECT\t" . join(",\n\t", map { $_->toSQL } @{ $self->{_select} }) . "\n" if (@{ $self->{_select} });
234 $sql .= " FROM\t" . $self->{_from}->toSQL . "\n" if ($self->{_from});
235 $sql .= " WHERE\t" . join("\n\tAND ", map { $_->toSQL } @{ $self->{_where} }) . "\n" if (@{ $self->{_where} });
237 my @group_by = $self->group_by_list;
239 $sql .= ' GROUP BY ' . join(', ', @group_by) . "\n" if (@group_by);
240 $sql .= " HAVING " . join("\n\tAND ", map { $_->toSQL } @{ $self->{_having} }) . "\n" if (@{ $self->{_having} });
241 $sql .= ' ORDER BY ' . join(', ', map { $_->toSQL } @{ $self->{_order_by} }) . "\n" if (@{ $self->{_order_by} });
243 if ($self->is_subquery) {
244 $sql .= ') '. $self->{_alias} . "\n";
247 return $self->{_sql} = $sql;
251 #-------------------------------------------------------------------------------------------------
252 package OpenILS::Reporter::SQLBuilder::Input;
253 use base qw/OpenILS::Reporter::SQLBuilder/;
257 my $self = $class->SUPER::new;
259 my $col_data = shift;
261 if (ref($col_data)) {
262 $self->{params} = $col_data->{params};
263 my $trans = $col_data->{transform} || 'Bare';
264 my $pkg = "OpenILS::Reporter::SQLBuilder::Input::Transform::$trans";
265 if (UNIVERSAL::can($pkg => 'toSQL')) {
266 $self->{_transform} = $trans;
268 $self->{_transform} = 'GenericTransform';
270 } elsif( defined($col_data) ) {
271 $self->{_transform} = 'Bare';
272 $self->{params} = $col_data;
274 $self->{_transform} = 'NULL';
284 my $type = $self->{_transform};
285 return $self->{_sql} if ($self->{_sql});
286 my $toSQL = "OpenILS::Reporter::SQLBuilder::Input::Transform::${type}::toSQL";
287 return $self->{_sql} = $self->$toSQL;
291 #-------------------------------------------------------------------------------------------------
292 package OpenILS::Reporter::SQLBuilder::Input::Transform::NULL;
299 #-------------------------------------------------------------------------------------------------
300 package OpenILS::Reporter::SQLBuilder::Input::Transform::Bare;
305 my $val = $self->{params};
306 $val = $$val[0] if (ref($val));
308 $val =~ s/\\/\\\\/go;
315 #-------------------------------------------------------------------------------------------------
316 package OpenILS::Reporter::SQLBuilder::Input::Transform::relative_year;
321 my $rtime = $self->relative_time || 'now';
323 $rtime =~ s/\\/\\\\/go;
324 $rtime =~ s/'/\\'/go;
326 my $val = $self->{params};
327 $val = $$val[0] if (ref($val));
329 $val =~ s/\\/\\\\/go;
332 return "EXTRACT(YEAR FROM '$rtime'::TIMESTAMPTZ + '$val years')";
336 #-------------------------------------------------------------------------------------------------
337 package OpenILS::Reporter::SQLBuilder::Input::Transform::relative_month;
342 my $rtime = $self->relative_time || 'now';
344 $rtime =~ s/\\/\\\\/go;
345 $rtime =~ s/'/\\'/go;
347 my $val = $self->{params};
348 $val = $$val[0] if (ref($val));
350 $val =~ s/\\/\\\\/go;
353 return "EXTRACT(YEAR FROM '$rtime'::TIMESTAMPTZ + '$val months')" .
354 " || '-' || LPAD(EXTRACT(MONTH FROM '$rtime'::TIMESTAMPTZ + '$val months'),2,'0')";
358 #-------------------------------------------------------------------------------------------------
359 package OpenILS::Reporter::SQLBuilder::Input::Transform::relative_date;
364 my $rtime = $self->relative_time || 'now';
366 $rtime =~ s/\\/\\\\/go;
367 $rtime =~ s/'/\\'/go;
369 my $val = $self->{params};
370 $val = $$val[0] if (ref($val));
372 $val =~ s/\\/\\\\/go;
375 return "DATE('$rtime'::TIMESTAMPTZ + '$val days')";
379 #-------------------------------------------------------------------------------------------------
380 package OpenILS::Reporter::SQLBuilder::Input::Transform::relative_week;
385 my $rtime = $self->relative_time || 'now';
387 $rtime =~ s/\\/\\\\/go;
388 $rtime =~ s/'/\\'/go;
390 my $val = $self->{params};
391 $val = $$val[0] if (ref($val));
393 $val =~ s/\\/\\\\/go;
396 return "EXTRACT(WEEK FROM '$rtime'::TIMESTAMPTZ + '$val weeks')";
400 #-------------------------------------------------------------------------------------------------
401 package OpenILS::Reporter::SQLBuilder::Column;
402 use base qw/OpenILS::Reporter::SQLBuilder/;
406 my $self = $class->SUPER::new;
408 my $col_data = shift;
409 $self->{_relation} = $col_data->{relation};
410 $self->{_column} = $col_data->{column};
412 $self->{_aggregate} = $col_data->{aggregate};
414 if (ref($self->{_column})) {
415 my $trans = $self->{_column}->{transform} || 'Bare';
416 my $pkg = "OpenILS::Reporter::SQLBuilder::Column::Transform::$trans";
417 if (UNIVERSAL::can($pkg => 'toSQL')) {
418 $self->{_transform} = $trans;
420 $self->{_transform} = 'GenericTransform';
422 } elsif( defined($self->{_column}) ) {
423 $self->{_transform} = 'Bare';
425 $self->{_transform} = 'NULL';
434 if (ref($self->{_column})) {
435 return $self->{_column}->{colname};
437 return $self->{_column};
443 my $type = $self->{_transform};
444 return $self->{_sql} if ($self->{_sql});
445 my $toSQL = "OpenILS::Reporter::SQLBuilder::Column::Transform::${type}::toSQL";
446 return $self->{_sql} = $self->$toSQL;
451 my $type = $self->{_transform};
452 my $is_agg = "OpenILS::Reporter::SQLBuilder::Column::Transform::${type}::is_aggregate";
453 return $self->$is_agg;
457 #-------------------------------------------------------------------------------------------------
458 package OpenILS::Reporter::SQLBuilder::Column::OrderBy;
459 use base qw/OpenILS::Reporter::SQLBuilder::Column/;
463 my $self = $class->SUPER::new(@_);
465 my $col_data = shift;
466 $self->{_direction} = $col_data->{direction} || 'ascending';
472 my $dir = ($self->{_direction} =~ /^d/oi) ? 'DESC' : 'ASC';
473 return $self->{_sql} if ($self->{_sql});
474 return $self->{_sql} = $self->SUPER::toSQL . " $dir";
478 #-------------------------------------------------------------------------------------------------
479 package OpenILS::Reporter::SQLBuilder::Column::Select;
480 use base qw/OpenILS::Reporter::SQLBuilder::Column/;
484 my $self = $class->SUPER::new(@_);
486 my $col_data = shift;
487 $self->{_alias} = $col_data->{alias} || $self->name;
493 return $self->{_sql} if ($self->{_sql});
494 return $self->{_sql} = $self->SUPER::toSQL . ' AS "' . $self->resolve_param( $self->{_alias} ) . '"';
498 #-------------------------------------------------------------------------------------------------
499 package OpenILS::Reporter::SQLBuilder::Column::Transform::GenericTransform;
503 my $name = $self->name;
504 my ($func) = keys %{ $self->{_column} };
507 @params = @{ $self->resolve_param( $self->{_column}->{params} ) } if ($self->{_column}->{params});
509 my $sql = $func . '("' . $self->{_relation} . '"."' . $self->name . '"';
510 $sql .= ",'" . join("','", @params) . "'" if (@params);
516 sub is_aggregate { return $self->{_aggregate} }
518 #-------------------------------------------------------------------------------------------------
519 package OpenILS::Reporter::SQLBuilder::Column::Transform::Bare;
523 return '"' . $self->{_relation} . '"."' . $self->name . '"';
526 sub is_aggregate { return 0 }
528 #-------------------------------------------------------------------------------------------------
529 package OpenILS::Reporter::SQLBuilder::Column::Transform::substring;
533 my $params = $self->resolve_param( $self->{_column}->{params} );
534 my $start = $$params[0];
535 my $len = $$params[1];
536 return 'SUBSTRING("' . $self->{_relation} . '"."' . $self->name . "\",$start,$len)";
539 sub is_aggregate { return 0 }
542 #-------------------------------------------------------------------------------------------------
543 package OpenILS::Reporter::SQLBuilder::Column::Transform::day_name;
547 return 'TO_CHAR("' . $self->{_relation} . '"."' . $self->name . '", \'Day\')';
550 sub is_aggregate { return 0 }
553 #-------------------------------------------------------------------------------------------------
554 package OpenILS::Reporter::SQLBuilder::Column::Transform::month_name;
558 return 'TO_CHAR("' . $self->{_relation} . '"."' . $self->name . '", \'Month\')';
561 sub is_aggregate { return 0 }
564 #-------------------------------------------------------------------------------------------------
565 package OpenILS::Reporter::SQLBuilder::Column::Transform::doy;
569 return 'EXTRACT(DOY FROM "' . $self->{_relation} . '"."' . $self->name . '")';
572 sub is_aggregate { return 0 }
575 #-------------------------------------------------------------------------------------------------
576 package OpenILS::Reporter::SQLBuilder::Column::Transform::woy;
580 return 'EXTRACT(WEEK FROM "' . $self->{_relation} . '"."' . $self->name . '")';
583 sub is_aggregate { return 0 }
586 #-------------------------------------------------------------------------------------------------
587 package OpenILS::Reporter::SQLBuilder::Column::Transform::moy;
591 return 'EXTRACT(MONTH FROM "' . $self->{_relation} . '"."' . $self->name . '")';
594 sub is_aggregate { return 0 }
597 #-------------------------------------------------------------------------------------------------
598 package OpenILS::Reporter::SQLBuilder::Column::Transform::qoy;
602 return 'EXTRACT(QUARTER FROM "' . $self->{_relation} . '"."' . $self->name . '")';
605 sub is_aggregate { return 0 }
608 #-------------------------------------------------------------------------------------------------
609 package OpenILS::Reporter::SQLBuilder::Column::Transform::dom;
613 return 'EXTRACT(DAY FROM "' . $self->{_relation} . '"."' . $self->name . '")';
616 sub is_aggregate { return 0 }
619 #-------------------------------------------------------------------------------------------------
620 package OpenILS::Reporter::SQLBuilder::Column::Transform::dow;
624 return 'EXTRACT(DOW FROM "' . $self->{_relation} . '"."' . $self->name . '")';
627 sub is_aggregate { return 0 }
630 #-------------------------------------------------------------------------------------------------
631 package OpenILS::Reporter::SQLBuilder::Column::Transform::year_trunc;
635 return 'EXTRACT(YEAR FROM "' . $self->{_relation} . '"."' . $self->name . '")';
638 sub is_aggregate { return 0 }
641 #-------------------------------------------------------------------------------------------------
642 package OpenILS::Reporter::SQLBuilder::Column::Transform::month_trunc;
646 return 'EXTRACT(YEAR FROM "' . $self->{_relation} . '"."' . $self->name . '")' .
647 ' || \'-\' || LPAD(EXTRACT(MONTH FROM "' . $self->{_relation} . '"."' . $self->name . '"),2,\'0\')';
650 sub is_aggregate { return 0 }
653 #-------------------------------------------------------------------------------------------------
654 package OpenILS::Reporter::SQLBuilder::Column::Transform::quarter;
658 return 'EXTRACT(YEAR FROM "' . $self->{_relation} . '"."' . $self->name . '")' .
659 ' || \'-Q\' || EXTRACT(QUARTER FROM "' . $self->{_relation} . '"."' . $self->name . '")';
662 sub is_aggregate { return 0 }
665 #-------------------------------------------------------------------------------------------------
666 package OpenILS::Reporter::SQLBuilder::Column::Transform::months_ago;
670 return 'EXTRACT(MONTH FROM AGE(NOW(),"' . $self->{_relation} . '"."' . $self->name . '"))';
673 sub is_aggregate { return 0 }
676 #-------------------------------------------------------------------------------------------------
677 package OpenILS::Reporter::SQLBuilder::Column::Transform::quarters_ago;
681 return 'EXTRACT(QUARTER FROM AGE(NOW(),"' . $self->{_relation} . '"."' . $self->name . '"))';
684 sub is_aggregate { return 0 }
687 #-------------------------------------------------------------------------------------------------
688 package OpenILS::Reporter::SQLBuilder::Column::Transform::age;
692 return 'AGE(NOW(),"' . $self->{_relation} . '"."' . $self->name . '")';
695 sub is_aggregate { return 0 }
698 #-------------------------------------------------------------------------------------------------
699 package OpenILS::Reporter::SQLBuilder::Column::Transform::first;
703 return 'FIRST("' . $self->{_relation} . '"."' . $self->name . '")';
706 sub is_aggregate { return 1 }
709 #-------------------------------------------------------------------------------------------------
710 package OpenILS::Reporter::SQLBuilder::Column::Transform::last;
714 return 'LAST("' . $self->{_relation} . '"."' . $self->name . '")';
717 sub is_aggregate { return 1 }
720 #-------------------------------------------------------------------------------------------------
721 package OpenILS::Reporter::SQLBuilder::Column::Transform::min;
725 return 'MIN("' . $self->{_relation} . '"."' . $self->name . '")';
728 sub is_aggregate { return 1 }
731 #-------------------------------------------------------------------------------------------------
732 package OpenILS::Reporter::SQLBuilder::Column::Transform::max;
736 return 'MAX("' . $self->{_relation} . '"."' . $self->name . '")';
739 sub is_aggregate { return 1 }
742 #-------------------------------------------------------------------------------------------------
743 package OpenILS::Reporter::SQLBuilder::Column::Transform::count;
747 return 'COUNT("' . $self->{_relation} . '"."' . $self->name . '")';
750 sub is_aggregate { return 1 }
753 #-------------------------------------------------------------------------------------------------
754 package OpenILS::Reporter::SQLBuilder::Column::Transform::count_distinct;
758 return 'COUNT(DISTINCT "' . $self->{_relation} . '"."' . $self->name . '")';
761 sub is_aggregate { return 1 }
764 #-------------------------------------------------------------------------------------------------
765 package OpenILS::Reporter::SQLBuilder::Column::Transform::sum;
769 return 'SUM("' . $self->{_relation} . '"."' . $self->name . '")';
772 sub is_aggregate { return 1 }
775 #-------------------------------------------------------------------------------------------------
776 package OpenILS::Reporter::SQLBuilder::Column::Transform::average;
780 return 'AVG("' . $self->{_relation} . '"."' . $self->name . '")';
783 sub is_aggregate { return 1 }
786 #-------------------------------------------------------------------------------------------------
787 package OpenILS::Reporter::SQLBuilder::Column::Where;
788 use base qw/OpenILS::Reporter::SQLBuilder::Column/;
792 my $self = $class->SUPER::new(@_);
794 my $col_data = shift;
795 $self->{_condition} = $col_data->{condition};
800 sub _flesh_conditions {
802 $cond = [$cond] unless (ref($cond) eq 'ARRAY');
806 push @out, OpenILS::Reporter::SQLBuilder::Input->new( $c );
815 return $self->{_sql} if ($self->{_sql});
816 my $sql = $self->SUPER::toSQL;
818 my ($op) = keys %{ $self->{_condition} };
819 my $val = _flesh_conditions( $self->resolve_param( $self->{_condition}->{$op} ) );
821 if (lc($op) eq 'in') {
822 $sql .= " IN (". join(",", map { $_->toSQL } @$val).")";
823 } elsif (lc($op) eq 'not in') {
824 $sql .= " NOT IN (". join(",", map { $_->toSQL } @$val).")";
825 } elsif (lc($op) eq 'between') {
826 $sql .= " BETWEEN ". join(" AND ", map { $_->toSQL } @$val);
827 } elsif (lc($op) eq 'not between') {
828 $sql .= " NOT BETWEEN ". join(" AND ", map { $_->toSQL } @$val);
830 $val = $$val[0] if (ref($val) eq 'ARRAY');
831 $sql .= " $op " . $val->toSQL;
834 return $self->{_sql} = $sql;
838 #-------------------------------------------------------------------------------------------------
839 package OpenILS::Reporter::SQLBuilder::Column::Having;
840 use base qw/OpenILS::Reporter::SQLBuilder::Column::Where/;
842 #-------------------------------------------------------------------------------------------------
843 package OpenILS::Reporter::SQLBuilder::Relation;
844 use base qw/OpenILS::Reporter::SQLBuilder/;
848 $self = $self->SUPER::new if (!ref($self));
850 my $rel_data = shift;
852 $self->{_table} = $rel_data->{table};
853 $self->{_alias} = $rel_data->{alias} || $self->name;
855 $self->{_columns} = [];
857 if ($rel_data->{join}) {
859 $_ => OpenILS::Reporter::SQLBuilder::Relation->parse( $rel_data->{join}->{$_} ) => $rel_data->{join}->{$_}->{key}
860 ) for ( keys %{ $rel_data->{join} } );
870 push @{ $self->{_columns} }, $col;
876 return (grep { $_->name eq $col} @{ $self->{_columns} })[0];
885 if (ref($col) eq 'OpenILS::Reporter::SQLBuilder::Join') {
886 push @{ $self->{_join} }, $col;
888 push @{ $self->{_join} }, OpenILS::Reporter::SQLBuilder::Join->build( $self => $col, $frel => $fkey );
897 $self->{_is_join} = $j if ($j);
898 return $self->{_is_join};
903 return $self->{_sql} if ($self->{_sql});
905 my $sql = $self->{_table} .' AS "'. $self->{_alias} .'"';
907 if (!$self->is_join) {
908 for my $j ( @{ $self->{_join} } ) {
913 return $self->{_sql} = $sql;
916 #-------------------------------------------------------------------------------------------------
917 package OpenILS::Reporter::SQLBuilder::Join;
918 use base qw/OpenILS::Reporter::SQLBuilder/;
922 $self = $self->SUPER::new if (!ref($self));
924 $self->{_left_rel} = shift;
925 ($self->{_left_col}) = split(/-/,shift());
927 $self->{_right_rel} = shift;
928 $self->{_right_col} = shift;
930 $self->{_right_rel}->is_join(1);
937 return $self->{_sql} if ($self->{_sql});
939 my $sql = "\n\tJOIN " . $self->{_right_rel}->toSQL .
940 ' ON ("' . $self->{_left_rel}->{_alias} . '"."' . $self->{_left_col} .
941 '" = "' . $self->{_right_rel}->{_alias} . '"."' . $self->{_right_col} . '")';
943 $sql .= $_->toSQL for (@{ $self->{_right_rel}->{_join} });
945 return $self->{_sql} = $sql;