1 package OpenILS::Reporter::SQLBuilder;
5 $class = ref($class) || $class;
7 return bless { _sql => undef } => $class;
13 $self->{_params} = $p;
19 return $self->{_builder}->{_params}->{$p};
24 $self->{_builder} = shift;
32 if ($val =~ /^::(.+)$/o) {
33 return $self->get_param($1);
43 $self->set_select( $report->{select} );
44 $self->set_from( $report->{from} );
45 $self->set_where( $report->{where} );
54 $self->{_select} = [];
56 @cols = @{ $cols[0] } if (@cols == 1 && ref($cols[0]) eq 'ARRAY');
58 push @{ $self->{_select} }, map { OpenILS::Reporter::SQLBuilder::Column::Select->new( $_ )->set_builder( $self ) } @cols;
67 $self->{_from} = OpenILS::Reporter::SQLBuilder::Relation->parse( $f );
78 @cols = @{ $cols[0] } if (@cols == 1 && ref($cols[0]) eq 'ARRAY');
80 push @{ $self->{_where} }, map { OpenILS::Reporter::SQLBuilder::Column::Where->new( $_ )->set_builder( $self ) } @cols;
88 my $sql = "SELECT\t" . join(",\n\t", map { $_->toSQL } @{ $self->{_select} }) . "\n";
90 $sql .= " FROM\t" . $self->{_from}->toSQL . "\n";
92 $sql .= " WHERE\t" . join("\n\tAND ", map { $_->toSQL } @{ $self->{_where} }) . "\n";
96 for my $c ( @{ $self->{_select} } ) {
97 push @group_by, $gcount if (!$c->is_aggregate);
101 $sql .= ' GROUP BY ' . join(', ', @group_by) . "\n" if (@group_by);
102 $sql .= ' ORDER BY ' . join(', ', 1 .. scalar(@{ $self->{_select} })) . "\n";
108 package OpenILS::Reporter::SQLBuilder::Column;
109 use base qw/OpenILS::Reporter::SQLBuilder/;
113 my $self = $class->SUPER::new;
115 my $col_data = shift;
116 $self->{_relation} = $col_data->{relation};
117 $self->{_column} = $col_data->{column};
124 if (ref($self->{_column})) {
125 my ($k) = keys %{$self->{_column}};
126 if (ref($self->{_column}->{$k})) {
127 return $self->resolve_param( $self->{_column}->{$k}->[0] );
129 return $self->resolve_param( $self->{_column}->{$k} );
132 return $self->resolve_param( $self->{_column} );
137 package OpenILS::Reporter::SQLBuilder::Column::Select;
138 use base qw/OpenILS::Reporter::SQLBuilder::Column/;
142 my $self = $class->SUPER::new(@_);
144 my $col_data = shift;
145 $self->{_alias} = $col_data->{alias};
146 $self->{_aggregate} = $col_data->{aggregate};
148 if (ref($self->{_column})) {
149 my $pkg = 'OpenILS::Reporter::SQLBuilder::Column::Select::Transform::' . (keys %{ $self->{_column} })[0];
150 if (UNIVERSAL::can($pkg => 'toSQL')) {
153 bless $self => 'OpenILS::Reporter::SQLBuilder::Column::Select::GenericTransform';
156 bless $self => 'OpenILS::Reporter::SQLBuilder::Column::Select::Bare';
163 package OpenILS::Reporter::SQLBuilder::Column::Select::GenericTransform;
164 use base qw/OpenILS::Reporter::SQLBuilder::Column::Select/;
168 my $name = $self->name;
169 my ($func) = keys %{ $self->{_column} };
172 @params = @{ $self->{_column}->{$func} } if (ref($self->{_column}->{$func}));
174 my $sql = $func . '("' . $self->{_relation} . '"."' . $self->name;
176 $sql .= ',' . join(',', @params) if (@params);
178 $sql .= '") AS "' . $self->resolve_param( $self->{_alias} ) . '"';
183 sub is_aggregate { return $self->{_aggregate} }
186 package OpenILS::Reporter::SQLBuilder::Column::Select::Bare;
187 use base qw/OpenILS::Reporter::SQLBuilder::Column::Select/;
191 return '"' . $self->{_relation} . '"."' . $self->name .
192 '" AS "' . $self->resolve_param( $self->{_alias} ) . '"';
195 sub is_aggregate { return 0 }
198 package OpenILS::Reporter::SQLBuilder::Column::Select::Transform::count;
199 use base qw/OpenILS::Reporter::SQLBuilder::Column::Select/;
203 return 'COUNT("' . $self->{_relation} . '"."' . $self->name .
204 '") AS "' . $self->resolve_param( $self->{_alias} ) . '"';
207 sub is_aggregate { return 1 }
210 package OpenILS::Reporter::SQLBuilder::Column::Select::Transform::count_distinct;
211 use base qw/OpenILS::Reporter::SQLBuilder::Column::Select/;
215 return 'COUNT(DISTINCT "' . $self->{_relation} . '"."' . $self->name .
216 '") AS "' . $self->resolve_param( $self->{_alias} ) . '"';
219 sub is_aggregate { return 1 }
222 package OpenILS::Reporter::SQLBuilder::Column::Select::Transform::sum;
223 use base qw/OpenILS::Reporter::SQLBuilder::Column::Select/;
227 return 'SUM("' . $self->{_relation} . '"."' . $self->name .
228 '") AS "' . $self->resolve_param( $self->{_alias} ) . '"';
231 sub is_aggregate { return 1 }
234 package OpenILS::Reporter::SQLBuilder::Column::Select::Transform::average;
235 use base qw/OpenILS::Reporter::SQLBuilder::Column::Select/;
239 return 'AVG("' . $self->{_relation} . '"."' . $self->name .
240 '") AS "' . $self->resolve_param( $self->{_alias} ) . '"';
243 sub is_aggregate { return 1 }
246 package OpenILS::Reporter::SQLBuilder::Column::Where;
247 use base qw/OpenILS::Reporter::SQLBuilder::Column/;
251 my $self = $class->SUPER::new(@_);
253 my $col_data = shift;
254 $self->{_condition} = $col_data->{condition};
262 my $sql = '"' . $self->{_relation} . '"."' . $self->name . '"';
263 my ($op) = keys %{ $self->{_condition} };
264 my $val = $self->resolve_param( values %{ $self->{_condition} } );
266 if (lc($op) eq 'in') {
267 $val = [$val] unless (ref($val));
268 $sql .= " IN ('". join("','", map { $_ =~ s/'/\\'/go; $_ =~ s/\\/\\\\/go; $_ } @$val)."')";
269 } elsif (lc($op) eq 'not in') {
270 $val = [$val] unless (ref($val));
271 $sql .= " NOT IN ('". join("','", map { $_ =~ s/'/\\'/go; $_ =~ s/\\/\\\\/go; $_ } @$val)."')";
272 } elsif (lc($op) eq 'between') {
273 $val = [$val] unless (ref($val));
274 $sql .= " BETWEEN '". join("' AND '", map { $_ =~ s/'/\\'/go; $_ =~ s/\\/\\\\/go; $_ } @$val)."'";
275 } elsif (lc($op) eq 'not between') {
276 $val = [$val] unless (ref($val));
277 $sql .= " NOT BETWEEN '". join("' AND '", map { $_ =~ s/'/\\'/go; $_ =~ s/\\/\\\\/go; $_ } @$val)."'";
279 $val =~ s/'/\\'/go; $val =~ s/\\/\\\\/go;
280 $sql .= " $op '$val'";
287 package OpenILS::Reporter::SQLBuilder::Relation;
288 use base qw/OpenILS::Reporter::SQLBuilder/;
292 $self = $self->SUPER::new if (!ref($self));
294 my $rel_data = shift;
296 $self->{_table} = $rel_data->{table};
297 $self->{_alias} = $rel_data->{alias};
299 $self->{_columns} = [];
301 if ($rel_data->{join}) {
303 $_ => OpenILS::Reporter::SQLBuilder::Relation->parse( $rel_data->{join}->{$_} ) => $rel_data->{join}->{$_}->{key}
304 ) for ( keys %{ $rel_data->{join} } );
314 push @{ $self->{_columns} }, $col;
320 return (grep { $_->name eq $col} @{ $self->{_columns} })[0];
329 if (ref($col) eq 'OpenILS::Reporter::SQLBuilder::Join') {
330 push @{ $self->{_join} }, $col;
332 push @{ $self->{_join} }, OpenILS::Reporter::SQLBuilder::Join->build( $self => $col, $frel => $fkey );
341 $self->{_is_join} = $j if ($j);
342 return $self->{_is_join};
347 my $sql = $self->{_table} .' AS "'. $self->{_alias} .'"';
349 if (!$self->is_join) {
350 for my $j ( @{ $self->{_join} } ) {
358 package OpenILS::Reporter::SQLBuilder::Join;
359 use base qw/OpenILS::Reporter::SQLBuilder/;
363 $self = $self->SUPER::new if (!ref($self));
365 $self->{_left_rel} = shift;
366 $self->{_left_col} = shift;
368 $self->{_right_rel} = shift;
369 $self->{_right_col} = shift;
371 $self->{_right_rel}->is_join(1);
378 my $sql = "\n\tJOIN " . $self->{_right_rel}->toSQL .
379 ' ON ("' . $self->{_left_rel}->{_alias} . '"."' . $self->{_left_col} .
380 '" = "' . $self->{_right_rel}->{_alias} . '"."' . $self->{_right_col} . '")';
382 $sql .= $_->toSQL for (@{ $self->{_right_rel}->{_join} });