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};
38 if ($val =~ /^::(.+)$/o) {
39 $val = $self->get_param($1);
42 $val =~ s/\\/\\\\/go if (!ref($val));
43 $val =~ s/"/\\"/go if (!ref($val));
51 my $rs = OpenILS::Reporter::SQLBuilder::ResultSet->new;
53 $rs->is_subquery( 1 ) if ( $report->{alias} );
55 $rs ->set_builder( $self )
56 ->set_subquery_alias( $report->{alias} )
57 ->set_select( $report->{select} )
58 ->set_from( $report->{from} )
59 ->set_where( $report->{where} )
60 ->set_having( $report->{having} )
61 ->set_order_by( $report->{order_by} )
62 ->set_pivot_data( $report->{pivot_data} )
63 ->set_pivot_label( $report->{pivot_label} )
64 ->set_pivot_default( $report->{pivot_default} );
70 #-------------------------------------------------------------------------------------------------
71 package OpenILS::Reporter::SQLBuilder::ResultSet;
72 use base qw/OpenILS::Reporter::SQLBuilder/;
77 $self->{_is_subquery} = $flag if (defined $flag);
78 return $self->{_is_subquery};
83 return $self->{_pivot_data};
88 return $self->{_pivot_label};
93 return $self->{_pivot_label};
96 sub set_pivot_default {
99 $self->{_pivot_default} = $p if (defined $p);
106 $self->{_pivot_data} = $p if (defined $p);
110 sub set_pivot_label {
113 $self->{_pivot_label} = $p if (defined $p);
117 sub set_subquery_alias {
120 $self->{_alias} = $alias if (defined $alias);
128 $self->{_select} = [];
130 return $self unless (@cols && defined($cols[0]));
131 @cols = @{ $cols[0] } if (@cols == 1 && ref($cols[0]) eq 'ARRAY');
133 push @{ $self->{_select} }, map { OpenILS::Reporter::SQLBuilder::Column::Select->new( $_ )->set_builder( $self->builder ) } @cols;
142 $self->{_from} = OpenILS::Reporter::SQLBuilder::Relation->parse( $f );
151 $self->{_where} = [];
153 return $self unless (@cols && defined($cols[0]));
154 @cols = @{ $cols[0] } if (@cols == 1 && ref($cols[0]) eq 'ARRAY');
156 push @{ $self->{_where} }, map { OpenILS::Reporter::SQLBuilder::Column::Where->new( $_ )->set_builder( $self->builder ) } @cols;
165 $self->{_having} = [];
167 return $self unless (@cols && defined($cols[0]));
168 @cols = @{ $cols[0] } if (@cols == 1 && ref($cols[0]) eq 'ARRAY');
170 push @{ $self->{_having} }, map { OpenILS::Reporter::SQLBuilder::Column::Having->new( $_ )->set_builder( $self->builder ) } @cols;
179 $self->{_order_by} = [];
181 return $self unless (@cols && defined($cols[0]));
182 @cols = @{ $cols[0] } if (@cols == 1 && ref($cols[0]) eq 'ARRAY');
184 push @{ $self->{_order_by} }, map { OpenILS::Reporter::SQLBuilder::Column::OrderBy->new( $_ )->set_builder( $self->builder ) } @cols;
189 sub column_label_list {
193 push @labels, $self->resolve_param( $_->{_alias} ) for ( @{ $self->{_select} } );
200 $base = 1 unless (defined $base);
204 for my $c ( @{ $self->{_select} } ) {
205 push @group_by, $gcount if (!$c->is_aggregate);
215 return $self->{_sql} if ($self->{_sql});
219 if ($self->is_subquery) {
223 $sql .= "SELECT\t" . join(",\n\t", map { $_->toSQL } @{ $self->{_select} }) . "\n" if (@{ $self->{_select} });
224 $sql .= " FROM\t" . $self->{_from}->toSQL . "\n" if ($self->{_from});
225 $sql .= " WHERE\t" . join("\n\tAND ", map { $_->toSQL } @{ $self->{_where} }) . "\n" if (@{ $self->{_where} });
227 my @group_by = $self->group_by_list;
229 $sql .= ' GROUP BY ' . join(', ', @group_by) . "\n" if (@group_by);
230 $sql .= " HAVING " . join("\n\tAND ", map { $_->toSQL } @{ $self->{_having} }) . "\n" if (@{ $self->{_having} });
231 $sql .= ' ORDER BY ' . join(', ', map { $_->toSQL } @{ $self->{_order_by} }) . "\n" if (@{ $self->{_order_by} });
233 if ($self->is_subquery) {
234 $sql .= ') '. $self->{_alias} . "\n";
237 return $self->{_sql} = $sql;
241 #-------------------------------------------------------------------------------------------------
242 package OpenILS::Reporter::SQLBuilder::Input;
243 use base qw/OpenILS::Reporter::SQLBuilder/;
247 my $self = $class->SUPER::new;
249 my $col_data = shift;
251 if (ref($col_data)) {
252 $self->{params} = $col_data->{params};
253 my $trans = $col_data->{transform} || 'Bare';
254 my $pkg = "OpenILS::Reporter::SQLBuilder::Input::Transform::$trans";
255 if (UNIVERSAL::can($pkg => 'toSQL')) {
256 $self->{_transform} = $trans;
258 $self->{_transform} = 'GenericTransform';
261 $self->{_transform} = 'Bare';
262 $self->{params} = $col_data;
271 my $type = $self->{_transform};
272 return $self->{_sql} if ($self->{_sql});
273 my $toSQL = "OpenILS::Reporter::SQLBuilder::Input::Transform::${type}::toSQL";
274 return $self->{_sql} = $self->$toSQL;
278 #-------------------------------------------------------------------------------------------------
279 package OpenILS::Reporter::SQLBuilder::Input::Transform::Bare;
284 my $val = $self->{params};
285 $val = $$val[0] if (ref($val));
287 $val =~ s/\\/\\\\/go;
294 #-------------------------------------------------------------------------------------------------
295 package OpenILS::Reporter::SQLBuilder::Input::Transform::relative_year;
300 my $val = $self->{params};
301 $val = $$val[0] if (ref($val));
303 $val =~ s/\\/\\\\/go;
306 return "EXTRACT(YEAR FROM NOW() + '$val years')";
310 #-------------------------------------------------------------------------------------------------
311 package OpenILS::Reporter::SQLBuilder::Input::Transform::relative_month;
316 my $val = $self->{params};
317 $val = $$val[0] if (ref($val));
319 $val =~ s/\\/\\\\/go;
322 return "EXTRACT(YEAR FROM NOW() + '$val months')" .
323 " || '-' || LPAD(EXTRACT(MONTH FROM NOW() + '$val months'),2,'0')";
327 #-------------------------------------------------------------------------------------------------
328 package OpenILS::Reporter::SQLBuilder::Input::Transform::relative_date;
333 my $val = $self->{params};
334 $val = $$val[0] if (ref($val));
336 $val =~ s/\\/\\\\/go;
339 return "DATE(NOW() + '$val days')";
343 #-------------------------------------------------------------------------------------------------
344 package OpenILS::Reporter::SQLBuilder::Input::Transform::relative_week;
349 my $val = $self->{params};
350 $val = $$val[0] if (ref($val));
352 $val =~ s/\\/\\\\/go;
355 return "EXTRACT(WEEK FROM NOW() + '$val weeks')";
359 #-------------------------------------------------------------------------------------------------
360 package OpenILS::Reporter::SQLBuilder::Column;
361 use base qw/OpenILS::Reporter::SQLBuilder/;
365 my $self = $class->SUPER::new;
367 my $col_data = shift;
368 $self->{_relation} = $col_data->{relation};
369 $self->{_column} = $col_data->{column};
371 $self->{_aggregate} = $col_data->{aggregate};
373 if (ref($self->{_column})) {
374 my $trans = $self->{_column}->{transform} || 'Bare';
375 my $pkg = "OpenILS::Reporter::SQLBuilder::Column::Transform::$trans";
376 if (UNIVERSAL::can($pkg => 'toSQL')) {
377 $self->{_transform} = $trans;
379 $self->{_transform} = 'GenericTransform';
382 $self->{_transform} = 'Bare';
391 if (ref($self->{_column})) {
392 return $self->{_column}->{colname};
394 return $self->{_column};
400 my $type = $self->{_transform};
401 return $self->{_sql} if ($self->{_sql});
402 my $toSQL = "OpenILS::Reporter::SQLBuilder::Column::Transform::${type}::toSQL";
403 return $self->{_sql} = $self->$toSQL;
408 my $type = $self->{_transform};
409 my $is_agg = "OpenILS::Reporter::SQLBuilder::Column::Transform::${type}::is_aggregate";
410 return $self->$is_agg;
414 #-------------------------------------------------------------------------------------------------
415 package OpenILS::Reporter::SQLBuilder::Column::OrderBy;
416 use base qw/OpenILS::Reporter::SQLBuilder::Column/;
420 my $self = $class->SUPER::new(@_);
422 my $col_data = shift;
423 $self->{_direction} = $col_data->{direction} || 'ascending';
429 my $dir = ($self->{_direction} =~ /^d/oi) ? 'DESC' : 'ASC';
430 return $self->{_sql} if ($self->{_sql});
431 return $self->{_sql} = $self->SUPER::toSQL . " $dir";
435 #-------------------------------------------------------------------------------------------------
436 package OpenILS::Reporter::SQLBuilder::Column::Select;
437 use base qw/OpenILS::Reporter::SQLBuilder::Column/;
441 my $self = $class->SUPER::new(@_);
443 my $col_data = shift;
444 $self->{_alias} = $col_data->{alias} || $self->name;
450 return $self->{_sql} if ($self->{_sql});
451 return $self->{_sql} = $self->SUPER::toSQL . ' AS "' . $self->resolve_param( $self->{_alias} ) . '"';
455 #-------------------------------------------------------------------------------------------------
456 package OpenILS::Reporter::SQLBuilder::Column::Transform::GenericTransform;
460 my $name = $self->name;
461 my ($func) = keys %{ $self->{_column} };
464 @params = @{ $self->resolve_param( $self->{_column}->{params} ) } if ($self->{_column}->{params});
466 my $sql = $func . '("' . $self->{_relation} . '"."' . $self->name . '"';
467 $sql .= ",'" . join("','", @params) . "'" if (@params);
473 sub is_aggregate { return $self->{_aggregate} }
475 #-------------------------------------------------------------------------------------------------
476 package OpenILS::Reporter::SQLBuilder::Column::Transform::Bare;
480 return '"' . $self->{_relation} . '"."' . $self->name . '"';
483 sub is_aggregate { return 0 }
485 #-------------------------------------------------------------------------------------------------
486 package OpenILS::Reporter::SQLBuilder::Column::Transform::substring;
490 my $params = $self->resolve_param( $self->{_column}->{params} );
491 my $start = $$params[0];
492 my $len = $$params[1];
493 return 'SUBSTRING("' . $self->{_relation} . '"."' . $self->name . "\",$start,$len)";
496 sub is_aggregate { return 0 }
499 #-------------------------------------------------------------------------------------------------
500 package OpenILS::Reporter::SQLBuilder::Column::Transform::day_name;
504 return 'TO_CHAR("' . $self->{_relation} . '"."' . $self->name . '", \'Day\')';
507 sub is_aggregate { return 0 }
510 #-------------------------------------------------------------------------------------------------
511 package OpenILS::Reporter::SQLBuilder::Column::Transform::month_name;
515 return 'TO_CHAR("' . $self->{_relation} . '"."' . $self->name . '", \'Month\')';
518 sub is_aggregate { return 0 }
521 #-------------------------------------------------------------------------------------------------
522 package OpenILS::Reporter::SQLBuilder::Column::Transform::doy;
526 return 'EXTRACT(DOY FROM "' . $self->{_relation} . '"."' . $self->name . '")';
529 sub is_aggregate { return 0 }
532 #-------------------------------------------------------------------------------------------------
533 package OpenILS::Reporter::SQLBuilder::Column::Transform::woy;
537 return 'EXTRACT(WEEK FROM "' . $self->{_relation} . '"."' . $self->name . '")';
540 sub is_aggregate { return 0 }
543 #-------------------------------------------------------------------------------------------------
544 package OpenILS::Reporter::SQLBuilder::Column::Transform::moy;
548 return 'EXTRACT(MONTH FROM "' . $self->{_relation} . '"."' . $self->name . '")';
551 sub is_aggregate { return 0 }
554 #-------------------------------------------------------------------------------------------------
555 package OpenILS::Reporter::SQLBuilder::Column::Transform::qoy;
559 return 'EXTRACT(QUARTER FROM "' . $self->{_relation} . '"."' . $self->name . '")';
562 sub is_aggregate { return 0 }
565 #-------------------------------------------------------------------------------------------------
566 package OpenILS::Reporter::SQLBuilder::Column::Transform::dom;
570 return 'EXTRACT(DAY FROM "' . $self->{_relation} . '"."' . $self->name . '")';
573 sub is_aggregate { return 0 }
576 #-------------------------------------------------------------------------------------------------
577 package OpenILS::Reporter::SQLBuilder::Column::Transform::dow;
581 return 'EXTRACT(DOW FROM "' . $self->{_relation} . '"."' . $self->name . '")';
584 sub is_aggregate { return 0 }
587 #-------------------------------------------------------------------------------------------------
588 package OpenILS::Reporter::SQLBuilder::Column::Transform::year_trunc;
592 return 'EXTRACT(YEAR FROM "' . $self->{_relation} . '"."' . $self->name . '")';
595 sub is_aggregate { return 0 }
598 #-------------------------------------------------------------------------------------------------
599 package OpenILS::Reporter::SQLBuilder::Column::Transform::month_trunc;
603 return 'EXTRACT(YEAR FROM "' . $self->{_relation} . '"."' . $self->name . '")' .
604 ' || \'-\' || LPAD(EXTRACT(MONTH FROM "' . $self->{_relation} . '"."' . $self->name . '"),2,\'0\')';
607 sub is_aggregate { return 0 }
610 #-------------------------------------------------------------------------------------------------
611 package OpenILS::Reporter::SQLBuilder::Column::Transform::quarter;
615 return 'EXTRACT(YEAR FROM "' . $self->{_relation} . '"."' . $self->name . '")' .
616 ' || \'-Q\' || EXTRACT(QUARTER FROM "' . $self->{_relation} . '"."' . $self->name . '")';
619 sub is_aggregate { return 0 }
622 #-------------------------------------------------------------------------------------------------
623 package OpenILS::Reporter::SQLBuilder::Column::Transform::months_ago;
627 return 'EXTRACT(MONTH FROM AGE(NOW(),"' . $self->{_relation} . '"."' . $self->name . '"))';
630 sub is_aggregate { return 0 }
633 #-------------------------------------------------------------------------------------------------
634 package OpenILS::Reporter::SQLBuilder::Column::Transform::quarters_ago;
638 return 'EXTRACT(QUARTER FROM AGE(NOW(),"' . $self->{_relation} . '"."' . $self->name . '"))';
641 sub is_aggregate { return 0 }
644 #-------------------------------------------------------------------------------------------------
645 package OpenILS::Reporter::SQLBuilder::Column::Transform::age;
649 return 'AGE(NOW(),"' . $self->{_relation} . '"."' . $self->name . '")';
652 sub is_aggregate { return 0 }
655 #-------------------------------------------------------------------------------------------------
656 package OpenILS::Reporter::SQLBuilder::Column::Transform::first;
660 return 'FIRST("' . $self->{_relation} . '"."' . $self->name . '")';
663 sub is_aggregate { return 1 }
666 #-------------------------------------------------------------------------------------------------
667 package OpenILS::Reporter::SQLBuilder::Column::Transform::last;
671 return 'LAST("' . $self->{_relation} . '"."' . $self->name . '")';
674 sub is_aggregate { return 1 }
677 #-------------------------------------------------------------------------------------------------
678 package OpenILS::Reporter::SQLBuilder::Column::Transform::min;
682 return 'MIN("' . $self->{_relation} . '"."' . $self->name . '")';
685 sub is_aggregate { return 1 }
688 #-------------------------------------------------------------------------------------------------
689 package OpenILS::Reporter::SQLBuilder::Column::Transform::max;
693 return 'MAX("' . $self->{_relation} . '"."' . $self->name . '")';
696 sub is_aggregate { return 1 }
699 #-------------------------------------------------------------------------------------------------
700 package OpenILS::Reporter::SQLBuilder::Column::Transform::count;
704 return 'COUNT("' . $self->{_relation} . '"."' . $self->name . '")';
707 sub is_aggregate { return 1 }
710 #-------------------------------------------------------------------------------------------------
711 package OpenILS::Reporter::SQLBuilder::Column::Transform::count_distinct;
715 return 'COUNT(DISTINCT "' . $self->{_relation} . '"."' . $self->name . '")';
718 sub is_aggregate { return 1 }
721 #-------------------------------------------------------------------------------------------------
722 package OpenILS::Reporter::SQLBuilder::Column::Transform::sum;
726 return 'SUM("' . $self->{_relation} . '"."' . $self->name . '")';
729 sub is_aggregate { return 1 }
732 #-------------------------------------------------------------------------------------------------
733 package OpenILS::Reporter::SQLBuilder::Column::Transform::average;
737 return 'AVG("' . $self->{_relation} . '"."' . $self->name . '")';
740 sub is_aggregate { return 1 }
743 #-------------------------------------------------------------------------------------------------
744 package OpenILS::Reporter::SQLBuilder::Column::Where;
745 use base qw/OpenILS::Reporter::SQLBuilder::Column/;
749 my $self = $class->SUPER::new(@_);
751 my $col_data = shift;
752 $self->{_condition} = $col_data->{condition};
757 sub _flesh_conditions {
759 $cond = [$cond] unless (ref($cond) eq 'ARRAY');
763 push @out, OpenILS::Reporter::SQLBuilder::Input->new( $c );
772 return $self->{_sql} if ($self->{_sql});
773 my $sql = $self->SUPER::toSQL;
775 my ($op) = keys %{ $self->{_condition} };
776 my $val = _flesh_conditions( $self->resolve_param( $self->{_condition}->{$op} ) );
778 if (lc($op) eq 'in') {
779 $sql .= " IN (". join(",", map { $_->toSQL } @$val).")";
780 } elsif (lc($op) eq 'not in') {
781 $sql .= " NOT IN (". join(",", map { $_->toSQL } @$val).")";
782 } elsif (lc($op) eq 'between') {
783 $sql .= " BETWEEN ". join(" AND ", map { $_->toSQL } @$val);
784 } elsif (lc($op) eq 'not between') {
785 $sql .= " NOT BETWEEN ". join(" AND ", map { $_->toSQL } @$val);
787 $val = $$val[0] if (ref($val) eq 'ARRAY');
788 $sql .= " $op " . $val->toSQL;
791 return $self->{_sql} = $sql;
795 #-------------------------------------------------------------------------------------------------
796 package OpenILS::Reporter::SQLBuilder::Column::Having;
797 use base qw/OpenILS::Reporter::SQLBuilder::Column::Where/;
799 #-------------------------------------------------------------------------------------------------
800 package OpenILS::Reporter::SQLBuilder::Relation;
801 use base qw/OpenILS::Reporter::SQLBuilder/;
805 $self = $self->SUPER::new if (!ref($self));
807 my $rel_data = shift;
809 $self->{_table} = $rel_data->{table};
810 $self->{_alias} = $rel_data->{alias} || $self->name;
812 $self->{_columns} = [];
814 if ($rel_data->{join}) {
816 $_ => OpenILS::Reporter::SQLBuilder::Relation->parse( $rel_data->{join}->{$_} ) => $rel_data->{join}->{$_}->{key}
817 ) for ( keys %{ $rel_data->{join} } );
827 push @{ $self->{_columns} }, $col;
833 return (grep { $_->name eq $col} @{ $self->{_columns} })[0];
842 if (ref($col) eq 'OpenILS::Reporter::SQLBuilder::Join') {
843 push @{ $self->{_join} }, $col;
845 push @{ $self->{_join} }, OpenILS::Reporter::SQLBuilder::Join->build( $self => $col, $frel => $fkey );
854 $self->{_is_join} = $j if ($j);
855 return $self->{_is_join};
860 return $self->{_sql} if ($self->{_sql});
862 my $sql = $self->{_table} .' AS "'. $self->{_alias} .'"';
864 if (!$self->is_join) {
865 for my $j ( @{ $self->{_join} } ) {
870 return $self->{_sql} = $sql;
873 #-------------------------------------------------------------------------------------------------
874 package OpenILS::Reporter::SQLBuilder::Join;
875 use base qw/OpenILS::Reporter::SQLBuilder/;
879 $self = $self->SUPER::new if (!ref($self));
881 $self->{_left_rel} = shift;
882 $self->{_left_col} = shift;
884 $self->{_right_rel} = shift;
885 $self->{_right_col} = shift;
887 $self->{_right_rel}->is_join(1);
894 return $self->{_sql} if ($self->{_sql});
896 my $sql = "\n\tJOIN " . $self->{_right_rel}->toSQL .
897 ' ON ("' . $self->{_left_rel}->{_alias} . '"."' . $self->{_left_col} .
898 '" = "' . $self->{_right_rel}->{_alias} . '"."' . $self->{_right_col} . '")';
900 $sql .= $_->toSQL for (@{ $self->{_right_rel}->{_join} });
902 return $self->{_sql} = $sql;