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} );
67 #-------------------------------------------------------------------------------------------------
68 package OpenILS::Reporter::SQLBuilder::ResultSet;
69 use base qw/OpenILS::Reporter::SQLBuilder/;
74 $self->{_is_subquery} = $flag if (defined $flag);
75 return $self->{_is_subquery};
78 sub set_subquery_alias {
81 $self->{_alias} = $alias if (defined $alias);
89 $self->{_select} = [];
91 return $self unless (@cols && defined($cols[0]));
92 @cols = @{ $cols[0] } if (@cols == 1 && ref($cols[0]) eq 'ARRAY');
94 push @{ $self->{_select} }, map { OpenILS::Reporter::SQLBuilder::Column::Select->new( $_ )->set_builder( $self->builder ) } @cols;
103 $self->{_from} = OpenILS::Reporter::SQLBuilder::Relation->parse( $f );
112 $self->{_where} = [];
114 return $self unless (@cols && defined($cols[0]));
115 @cols = @{ $cols[0] } if (@cols == 1 && ref($cols[0]) eq 'ARRAY');
117 push @{ $self->{_where} }, map { OpenILS::Reporter::SQLBuilder::Column::Where->new( $_ )->set_builder( $self->builder ) } @cols;
126 $self->{_having} = [];
128 return $self unless (@cols && defined($cols[0]));
129 @cols = @{ $cols[0] } if (@cols == 1 && ref($cols[0]) eq 'ARRAY');
131 push @{ $self->{_having} }, map { OpenILS::Reporter::SQLBuilder::Column::Having->new( $_ )->set_builder( $self->builder ) } @cols;
140 $self->{_order_by} = [];
142 return $self unless (@cols && defined($cols[0]));
143 @cols = @{ $cols[0] } if (@cols == 1 && ref($cols[0]) eq 'ARRAY');
145 push @{ $self->{_order_by} }, map { OpenILS::Reporter::SQLBuilder::Column::OrderBy->new( $_ )->set_builder( $self->builder ) } @cols;
153 return $self->{_sql} if ($self->{_sql});
157 if ($self->is_subquery) {
161 $sql .= "SELECT\t" . join(",\n\t", map { $_->toSQL } @{ $self->{_select} }) . "\n" if (@{ $self->{_select} });
162 $sql .= " FROM\t" . $self->{_from}->toSQL . "\n" if ($self->{_from});
163 $sql .= " WHERE\t" . join("\n\tAND ", map { $_->toSQL } @{ $self->{_where} }) . "\n" if (@{ $self->{_where} });
167 for my $c ( @{ $self->{_select} } ) {
168 push @group_by, $gcount if (!$c->is_aggregate);
172 $sql .= ' GROUP BY ' . join(', ', @group_by) . "\n" if (@group_by);
173 $sql .= " HAVING " . join("\n\tAND ", map { $_->toSQL } @{ $self->{_having} }) . "\n" if (@{ $self->{_having} });
174 $sql .= ' ORDER BY ' . join(', ', map { $_->toSQL } @{ $self->{_order_by} }) . "\n" if (@{ $self->{_order_by} });
176 if ($self->is_subquery) {
177 $sql .= ') '. $self->{_alias} . "\n";
180 return $self->{_sql} = $sql;
184 #-------------------------------------------------------------------------------------------------
185 package OpenILS::Reporter::SQLBuilder::Input;
186 use base qw/OpenILS::Reporter::SQLBuilder/;
190 my $self = $class->SUPER::new;
192 my $col_data = shift;
194 if (ref($col_data)) {
195 $self->{params} = $col_data->{params};
196 my $trans = $col_data->{transform} || 'Bare';
197 my $pkg = "OpenILS::Reporter::SQLBuilder::Input::Transform::$trans";
198 if (UNIVERSAL::can($pkg => 'toSQL')) {
199 $self->{_transform} = $trans;
201 $self->{_transform} = 'GenericTransform';
204 $self->{_transform} = 'Bare';
205 $self->{params} = $col_data;
214 my $type = $self->{_transform};
215 return $self->{_sql} if ($self->{_sql});
216 my $toSQL = "OpenILS::Reporter::SQLBuilder::Input::Transform::${type}::toSQL";
217 return $self->{_sql} = $self->$toSQL;
221 #-------------------------------------------------------------------------------------------------
222 package OpenILS::Reporter::SQLBuilder::Input::Transform::Bare;
227 my $val = $self->{params};
228 $val = $$val[0] if (ref($val));
230 $val =~ s/\\/\\\\/go;
237 #-------------------------------------------------------------------------------------------------
238 package OpenILS::Reporter::SQLBuilder::Input::Transform::relative_year;
243 my $val = $self->{params};
244 $val = $$val[0] if (ref($val));
246 $val =~ s/\\/\\\\/go;
249 return "EXTRACT(YEAR FROM NOW() + '$val years')";
253 #-------------------------------------------------------------------------------------------------
254 package OpenILS::Reporter::SQLBuilder::Input::Transform::relative_month;
259 my $val = $self->{params};
260 $val = $$val[0] if (ref($val));
262 $val =~ s/\\/\\\\/go;
265 return "EXTRACT(YEAR FROM NOW() + '$val months')" .
266 " || '-' || LPAD(EXTRACT(MONTH FROM NOW() + '$val months'),2,'0')";
270 #-------------------------------------------------------------------------------------------------
271 package OpenILS::Reporter::SQLBuilder::Input::Transform::relative_date;
276 my $val = $self->{params};
277 $val = $$val[0] if (ref($val));
279 $val =~ s/\\/\\\\/go;
282 return "DATE(NOW() + '$val days')";
286 #-------------------------------------------------------------------------------------------------
287 package OpenILS::Reporter::SQLBuilder::Input::Transform::relative_week;
292 my $val = $self->{params};
293 $val = $$val[0] if (ref($val));
295 $val =~ s/\\/\\\\/go;
298 return "EXTRACT(WEEK FROM NOW() + '$val weeks')";
302 #-------------------------------------------------------------------------------------------------
303 package OpenILS::Reporter::SQLBuilder::Column;
304 use base qw/OpenILS::Reporter::SQLBuilder/;
308 my $self = $class->SUPER::new;
310 my $col_data = shift;
311 $self->{_relation} = $col_data->{relation};
312 $self->{_column} = $col_data->{column};
314 $self->{_aggregate} = $col_data->{aggregate};
316 if (ref($self->{_column})) {
317 my $trans = $self->{_column}->{transform} || 'Bare';
318 my $pkg = "OpenILS::Reporter::SQLBuilder::Column::Transform::$trans";
319 if (UNIVERSAL::can($pkg => 'toSQL')) {
320 $self->{_transform} = $trans;
322 $self->{_transform} = 'GenericTransform';
325 $self->{_transform} = 'Bare';
334 if (ref($self->{_column})) {
335 return $self->{_column}->{colname};
337 return $self->{_column};
343 my $type = $self->{_transform};
344 return $self->{_sql} if ($self->{_sql});
345 my $toSQL = "OpenILS::Reporter::SQLBuilder::Column::Transform::${type}::toSQL";
346 return $self->{_sql} = $self->$toSQL;
351 my $type = $self->{_transform};
352 my $is_agg = "OpenILS::Reporter::SQLBuilder::Column::Transform::${type}::is_aggregate";
353 return $self->$is_agg;
357 #-------------------------------------------------------------------------------------------------
358 package OpenILS::Reporter::SQLBuilder::Column::OrderBy;
359 use base qw/OpenILS::Reporter::SQLBuilder::Column/;
363 my $self = $class->SUPER::new(@_);
365 my $col_data = shift;
366 $self->{_direction} = $col_data->{direction} || 'ascending';
372 my $dir = ($self->{_direction} =~ /^d/oi) ? 'DESC' : 'ASC';
373 return $self->{_sql} if ($self->{_sql});
374 return $self->{_sql} = $self->SUPER::toSQL . " $dir";
378 #-------------------------------------------------------------------------------------------------
379 package OpenILS::Reporter::SQLBuilder::Column::Select;
380 use base qw/OpenILS::Reporter::SQLBuilder::Column/;
384 my $self = $class->SUPER::new(@_);
386 my $col_data = shift;
387 $self->{_alias} = $col_data->{alias} || $self->name;
393 return $self->{_sql} if ($self->{_sql});
394 return $self->{_sql} = $self->SUPER::toSQL . ' AS "' . $self->resolve_param( $self->{_alias} ) . '"';
398 #-------------------------------------------------------------------------------------------------
399 package OpenILS::Reporter::SQLBuilder::Column::Transform::GenericTransform;
403 my $name = $self->name;
404 my ($func) = keys %{ $self->{_column} };
407 @params = @{ $self->resolve_param( $self->{_column}->{params} ) } if ($self->{_column}->{params});
409 my $sql = $func . '("' . $self->{_relation} . '"."' . $self->name . '"';
410 $sql .= ",'" . join("','", @params) . "'" if (@params);
416 sub is_aggregate { return $self->{_aggregate} }
418 #-------------------------------------------------------------------------------------------------
419 package OpenILS::Reporter::SQLBuilder::Column::Transform::Bare;
423 return '"' . $self->{_relation} . '"."' . $self->name . '"';
426 sub is_aggregate { return 0 }
428 #-------------------------------------------------------------------------------------------------
429 package OpenILS::Reporter::SQLBuilder::Column::Transform::substring;
433 my $params = $self->resolve_param( $self->{_column}->{params} );
434 my $start = $$params[0];
435 my $len = $$params[1];
436 return 'SUBSTRING("' . $self->{_relation} . '"."' . $self->name . "\",$start,$len)";
439 sub is_aggregate { return 0 }
442 #-------------------------------------------------------------------------------------------------
443 package OpenILS::Reporter::SQLBuilder::Column::Transform::day_name;
447 return 'TO_CHAR("' . $self->{_relation} . '"."' . $self->name . '", \'Day\')';
450 sub is_aggregate { return 0 }
453 #-------------------------------------------------------------------------------------------------
454 package OpenILS::Reporter::SQLBuilder::Column::Transform::month_name;
458 return 'TO_CHAR("' . $self->{_relation} . '"."' . $self->name . '", \'Month\')';
461 sub is_aggregate { return 0 }
464 #-------------------------------------------------------------------------------------------------
465 package OpenILS::Reporter::SQLBuilder::Column::Transform::doy;
469 return 'EXTRACT(DOY FROM "' . $self->{_relation} . '"."' . $self->name . '")';
472 sub is_aggregate { return 0 }
475 #-------------------------------------------------------------------------------------------------
476 package OpenILS::Reporter::SQLBuilder::Column::Transform::woy;
480 return 'EXTRACT(WEEK FROM "' . $self->{_relation} . '"."' . $self->name . '")';
483 sub is_aggregate { return 0 }
486 #-------------------------------------------------------------------------------------------------
487 package OpenILS::Reporter::SQLBuilder::Column::Transform::moy;
491 return 'EXTRACT(MONTH FROM "' . $self->{_relation} . '"."' . $self->name . '")';
494 sub is_aggregate { return 0 }
497 #-------------------------------------------------------------------------------------------------
498 package OpenILS::Reporter::SQLBuilder::Column::Transform::qoy;
502 return 'EXTRACT(QUARTER FROM "' . $self->{_relation} . '"."' . $self->name . '")';
505 sub is_aggregate { return 0 }
508 #-------------------------------------------------------------------------------------------------
509 package OpenILS::Reporter::SQLBuilder::Column::Transform::dom;
513 return 'EXTRACT(DAY FROM "' . $self->{_relation} . '"."' . $self->name . '")';
516 sub is_aggregate { return 0 }
519 #-------------------------------------------------------------------------------------------------
520 package OpenILS::Reporter::SQLBuilder::Column::Transform::dow;
524 return 'EXTRACT(DOW FROM "' . $self->{_relation} . '"."' . $self->name . '")';
527 sub is_aggregate { return 0 }
530 #-------------------------------------------------------------------------------------------------
531 package OpenILS::Reporter::SQLBuilder::Column::Transform::year_trunc;
535 return 'EXTRACT(YEAR FROM "' . $self->{_relation} . '"."' . $self->name . '")';
538 sub is_aggregate { return 0 }
541 #-------------------------------------------------------------------------------------------------
542 package OpenILS::Reporter::SQLBuilder::Column::Transform::month_trunc;
546 return 'EXTRACT(YEAR FROM "' . $self->{_relation} . '"."' . $self->name . '")' .
547 ' || \'-\' || LPAD(EXTRACT(MONTH FROM "' . $self->{_relation} . '"."' . $self->name . '"),2,\'0\')';
550 sub is_aggregate { return 0 }
553 #-------------------------------------------------------------------------------------------------
554 package OpenILS::Reporter::SQLBuilder::Column::Transform::quarter;
558 return 'EXTRACT(YEAR FROM "' . $self->{_relation} . '"."' . $self->name . '")' .
559 ' || \'-Q\' || EXTRACT(QUARTER FROM "' . $self->{_relation} . '"."' . $self->name . '")';
562 sub is_aggregate { return 0 }
565 #-------------------------------------------------------------------------------------------------
566 package OpenILS::Reporter::SQLBuilder::Column::Transform::months_ago;
570 return 'EXTRACT(MONTH FROM AGE(NOW(),"' . $self->{_relation} . '"."' . $self->name . '"))';
573 sub is_aggregate { return 0 }
576 #-------------------------------------------------------------------------------------------------
577 package OpenILS::Reporter::SQLBuilder::Column::Transform::quarters_ago;
581 return 'EXTRACT(QUARTER FROM AGE(NOW(),"' . $self->{_relation} . '"."' . $self->name . '"))';
584 sub is_aggregate { return 0 }
587 #-------------------------------------------------------------------------------------------------
588 package OpenILS::Reporter::SQLBuilder::Column::Transform::age;
592 return 'AGE(NOW(),"' . $self->{_relation} . '"."' . $self->name . '")';
595 sub is_aggregate { return 0 }
598 #-------------------------------------------------------------------------------------------------
599 package OpenILS::Reporter::SQLBuilder::Column::Transform::first;
603 return 'FIRST("' . $self->{_relation} . '"."' . $self->name . '")';
606 sub is_aggregate { return 1 }
609 #-------------------------------------------------------------------------------------------------
610 package OpenILS::Reporter::SQLBuilder::Column::Transform::last;
614 return 'LAST("' . $self->{_relation} . '"."' . $self->name . '")';
617 sub is_aggregate { return 1 }
620 #-------------------------------------------------------------------------------------------------
621 package OpenILS::Reporter::SQLBuilder::Column::Transform::min;
625 return 'MIN("' . $self->{_relation} . '"."' . $self->name . '")';
628 sub is_aggregate { return 1 }
631 #-------------------------------------------------------------------------------------------------
632 package OpenILS::Reporter::SQLBuilder::Column::Transform::max;
636 return 'MAX("' . $self->{_relation} . '"."' . $self->name . '")';
639 sub is_aggregate { return 1 }
642 #-------------------------------------------------------------------------------------------------
643 package OpenILS::Reporter::SQLBuilder::Column::Transform::count;
647 return 'COUNT("' . $self->{_relation} . '"."' . $self->name . '")';
650 sub is_aggregate { return 1 }
653 #-------------------------------------------------------------------------------------------------
654 package OpenILS::Reporter::SQLBuilder::Column::Transform::count_distinct;
658 return 'COUNT(DISTINCT "' . $self->{_relation} . '"."' . $self->name . '")';
661 sub is_aggregate { return 1 }
664 #-------------------------------------------------------------------------------------------------
665 package OpenILS::Reporter::SQLBuilder::Column::Transform::sum;
669 return 'SUM("' . $self->{_relation} . '"."' . $self->name . '")';
672 sub is_aggregate { return 1 }
675 #-------------------------------------------------------------------------------------------------
676 package OpenILS::Reporter::SQLBuilder::Column::Transform::average;
680 return 'AVG("' . $self->{_relation} . '"."' . $self->name . '")';
683 sub is_aggregate { return 1 }
686 #-------------------------------------------------------------------------------------------------
687 package OpenILS::Reporter::SQLBuilder::Column::Where;
688 use base qw/OpenILS::Reporter::SQLBuilder::Column/;
692 my $self = $class->SUPER::new(@_);
694 my $col_data = shift;
695 $self->{_condition} = $col_data->{condition};
700 sub _flesh_conditions {
702 $cond = [$cond] unless (ref($cond) eq 'ARRAY');
706 push @out, OpenILS::Reporter::SQLBuilder::Input->new( $c );
715 return $self->{_sql} if ($self->{_sql});
716 my $sql = $self->SUPER::toSQL;
718 my ($op) = keys %{ $self->{_condition} };
719 my $val = _flesh_conditions( $self->resolve_param( $self->{_condition}->{$op} ) );
721 if (lc($op) eq 'in') {
722 $sql .= " IN (". join(",", map { $_->toSQL } @$val).")";
723 } elsif (lc($op) eq 'not in') {
724 $sql .= " NOT IN (". join(",", map { $_->toSQL } @$val).")";
725 } elsif (lc($op) eq 'between') {
726 $sql .= " BETWEEN ". join(" AND ", map { $_->toSQL } @$val);
727 } elsif (lc($op) eq 'not between') {
728 $sql .= " NOT BETWEEN ". join(" AND ", map { $_->toSQL } @$val);
730 $val = $$val[0] if (ref($val) eq 'ARRAY');
731 $sql .= " $op " . $val->toSQL;
734 return $self->{_sql} = $sql;
738 #-------------------------------------------------------------------------------------------------
739 package OpenILS::Reporter::SQLBuilder::Column::Having;
740 use base qw/OpenILS::Reporter::SQLBuilder::Column::Where/;
742 #-------------------------------------------------------------------------------------------------
743 package OpenILS::Reporter::SQLBuilder::Relation;
744 use base qw/OpenILS::Reporter::SQLBuilder/;
748 $self = $self->SUPER::new if (!ref($self));
750 my $rel_data = shift;
752 $self->{_table} = $rel_data->{table};
753 $self->{_alias} = $rel_data->{alias} || $self->name;
755 $self->{_columns} = [];
757 if ($rel_data->{join}) {
759 $_ => OpenILS::Reporter::SQLBuilder::Relation->parse( $rel_data->{join}->{$_} ) => $rel_data->{join}->{$_}->{key}
760 ) for ( keys %{ $rel_data->{join} } );
770 push @{ $self->{_columns} }, $col;
776 return (grep { $_->name eq $col} @{ $self->{_columns} })[0];
785 if (ref($col) eq 'OpenILS::Reporter::SQLBuilder::Join') {
786 push @{ $self->{_join} }, $col;
788 push @{ $self->{_join} }, OpenILS::Reporter::SQLBuilder::Join->build( $self => $col, $frel => $fkey );
797 $self->{_is_join} = $j if ($j);
798 return $self->{_is_join};
803 return $self->{_sql} if ($self->{_sql});
805 my $sql = $self->{_table} .' AS "'. $self->{_alias} .'"';
807 if (!$self->is_join) {
808 for my $j ( @{ $self->{_join} } ) {
813 return $self->{_sql} = $sql;
816 #-------------------------------------------------------------------------------------------------
817 package OpenILS::Reporter::SQLBuilder::Join;
818 use base qw/OpenILS::Reporter::SQLBuilder/;
822 $self = $self->SUPER::new if (!ref($self));
824 $self->{_left_rel} = shift;
825 $self->{_left_col} = shift;
827 $self->{_right_rel} = shift;
828 $self->{_right_col} = shift;
830 $self->{_right_rel}->is_join(1);
837 return $self->{_sql} if ($self->{_sql});
839 my $sql = "\n\tJOIN " . $self->{_right_rel}->toSQL .
840 ' ON ("' . $self->{_left_rel}->{_alias} . '"."' . $self->{_left_col} .
841 '" = "' . $self->{_right_rel}->{_alias} . '"."' . $self->{_right_col} . '")';
843 $sql .= $_->toSQL for (@{ $self->{_right_rel}->{_join} });
845 return $self->{_sql} = $sql;