1 package SQL::Abstract::Converter;
6 use Data::Query::ExprHelpers;
10 has renderer_will_quote => (
18 has default_logic => (
19 is => 'ro', coerce => sub { uc($_[0]) }, default => sub { 'OR' }
23 is => 'ro', default => sub { 1 }
26 has cmp => (is => 'ro', default => sub { '=' });
28 has sqltrue => (is => 'ro', default => sub { '1=1' });
29 has sqlfalse => (is => 'ro', default => sub { '0=1' });
31 has special_ops => (is => 'ro', default => sub { [] });
33 # XXX documented but I don't current fail any tests not using it
34 has unary_ops => (is => 'ro', default => sub { [] });
36 has injection_guard => (
47 has identifier_sep => (
48 is => 'ro', default => sub { '.' },
51 has always_quote => (is => 'ro', default => sub { 1 });
53 has convert => (is => 'ro');
55 has array_datatypes => (is => 'ro');
58 my ($self, $literal) = @_;
60 ($literal, @bind) = @$literal if ref($literal) eq 'ARRAY';
61 Literal('SQL', $literal, [ $self->_bind_to_dq(@bind) ]);
65 my ($self, @bind) = @_;
69 $self->_assert_bindval_matches_bindtype(@bind);
70 map perl_scalar_value(reverse @$_), @bind
72 : map perl_scalar_value($_), @bind
76 my ($self, $value) = @_;
77 $self->_maybe_convert_dq(perl_scalar_value($value, our $Cur_Col_Meta));
81 my ($self, $ident) = @_;
82 $self->_assert_pass_injection_guard($ident)
83 unless $self->renderer_will_quote;
84 $self->_maybe_convert_dq(
86 if (my $sep = $self->identifier_sep) {
87 split /\Q$sep/, $ident
95 sub _maybe_convert_dq {
97 if (my $c = $self->{where_convert}) {
98 Operator({ 'SQL.Naive' => 'apply' }, [
99 Identifier($self->_sqlcase($c)),
109 my ($self, $op, @args) = @_;
110 $self->_assert_pass_injection_guard($op);
111 Operator({ 'SQL.Naive' => $op }, \@args);
114 sub _assert_pass_injection_guard {
115 if ($_[1] =~ $_[0]->{injection_guard}) {
116 my $class = ref $_[0];
117 die "Possible SQL injection attempt '$_[1]'. If this is indeed a part of the "
118 . "desired SQL use literal SQL ( \'...' or \[ '...' ] ) or supply your own "
119 . "{injection_guard} attribute to ${class}->new()"
124 my ($self, $table, $data, $options) = @_;
125 my (@names, @values);
126 if (ref($data) eq 'HASH') {
127 @names = sort keys %$data;
128 foreach my $k (@names) {
129 local our $Cur_Col_Meta = $k;
130 push @values, $self->_mutation_rhs_to_dq($data->{$k});
132 } elsif (ref($data) eq 'ARRAY') {
133 local our $Cur_Col_Meta;
134 @values = map $self->_mutation_rhs_to_dq($_), @$data;
136 die "Not handled yet";
139 if (my $r_source = $options->{returning}) {
141 map +(ref($_) ? $self->_expr_to_dq($_) : $self->_ident_to_dq($_)),
142 (ref($r_source) eq 'ARRAY' ? @$r_source : $r_source),
146 (@names ? ([ map $self->_ident_to_dq($_), @names ]) : undef),
148 $self->_table_to_dq($table),
149 ($returning ? ($returning) : undef),
153 sub _mutation_rhs_to_dq {
155 if (ref($v) eq 'ARRAY') {
156 if ($self->{array_datatypes}) {
157 return $self->_value_to_dq($v);
159 $v = \do { my $x = $v };
161 if (ref($v) eq 'HASH') {
162 my ($op, $arg, @rest) = %$v;
164 die 'Operator calls in update/insert must be in the form { -op => $arg }'
165 if (@rest or not $op =~ /^\-(.+)/);
167 return $self->_expr_to_dq($v);
171 my ($self, $table, $data, $where) = @_;
173 die "Unsupported data type specified to \$sql->update"
174 unless ref $data eq 'HASH';
178 foreach my $k (sort keys %$data) {
180 local our $Cur_Col_Meta = $k;
181 push @set, [ $self->_ident_to_dq($k), $self->_mutation_rhs_to_dq($v) ];
186 $self->_where_to_dq($where),
187 $self->_table_to_dq($table),
192 my ($self, $table, undef, $where) = @_;
194 my $source_dq = $self->_table_to_dq($table);
196 if (my $where_dq = $self->_where_to_dq($where)) {
197 $source_dq = Where($where_dq, $source_dq);
205 my ($table, $fields, $where, $order) = @_;
207 my $source_dq = $self->_source_to_dq(@_);
209 my $ordered_dq = do {
211 $self->_order_by_to_dq($order, undef, undef, $source_dq);
217 return $self->_select_select_to_dq($fields, $ordered_dq);
220 sub _select_select_to_dq {
221 my ($self, $fields, $from_dq) = @_;
226 $self->_select_field_list_to_dq($fields),
231 sub _select_field_list_to_dq {
232 my ($self, $fields) = @_;
233 [ map $self->_select_field_to_dq($_),
234 ref($fields) eq 'ARRAY' ? @$fields : $fields ];
237 sub _select_field_to_dq {
238 my ($self, $field) = @_;
239 if (my $ref = ref($field)) {
240 if ($ref eq 'REF' and ref($$field) eq 'HASH') {
243 return $self->_literal_to_dq($$field);
246 return $self->_ident_to_dq($field)
250 my ($self, $table, $where) = @_;
252 $self->_where_to_dq($where),
253 $self->_table_to_dq($table),
258 my ($self, $where, $logic) = @_;
260 return undef unless defined($where);
262 # if we're given a simple string assume it's a literal
263 return $self->_literal_to_dq($where) if !ref($where);
265 # turn the convert misfeature on - only used in WHERE clauses
266 local $self->{where_convert} = $self->convert;
268 return $self->_expr_to_dq($where, $logic);
272 my ($self, $where, $logic) = @_;
274 if (ref($where) eq 'ARRAY') {
275 return $self->_expr_to_dq_ARRAYREF($where, $logic);
276 } elsif (ref($where) eq 'HASH') {
277 return $self->_expr_to_dq_HASHREF($where, $logic);
279 ref($where) eq 'SCALAR'
280 or (ref($where) eq 'REF' and ref($$where) eq 'ARRAY')
282 return $self->_literal_to_dq($$where);
283 } elsif (ref($where) eq 'REF' and ref($$where) eq 'HASH') {
285 } elsif (!ref($where) or Scalar::Util::blessed($where)) {
286 return $self->_value_to_dq($where);
288 die "Can't handle $where";
291 sub _expr_to_dq_ARRAYREF {
292 my ($self, $where, $logic) = @_;
294 $logic = uc($logic || $self->default_logic || 'OR');
295 $logic eq 'AND' or $logic eq 'OR' or die "unknown logic: $logic";
297 return unless @$where;
299 my ($first, @rest) = @$where;
301 return $self->_expr_to_dq($first) unless @rest;
305 $self->_where_hashpair_to_dq($first => shift(@rest));
307 $self->_expr_to_dq($first);
311 return $self->_expr_to_dq_ARRAYREF(\@rest, $logic) unless $first_dq;
314 $logic, $first_dq, $self->_expr_to_dq_ARRAYREF(\@rest, $logic)
318 sub _expr_to_dq_HASHREF {
319 my ($self, $where, $logic) = @_;
321 $logic = uc($logic) if $logic;
324 $self->_where_hashpair_to_dq($_ => $where->{$_}, $logic)
327 return $dq[0] unless @dq > 1;
329 my $final = pop(@dq);
331 foreach my $dq (reverse @dq) {
332 $final = $self->_op_to_dq($logic||'AND', $dq, $final);
338 sub _where_to_dq_SCALAR {
339 shift->_value_to_dq(@_);
343 my ($self, $op, $v) = @_;
344 my @args = map $self->_expr_to_dq($_), (ref($v) eq 'ARRAY' ? @$v : $v);
346 # Ok. Welcome to stupid compat code land. An SQLA expr that would in the
347 # absence of this piece of crazy render to:
353 # { -a => { -b => { -c => $x } } }
355 # actually needs to render to:
359 # because SQL sucks, and databases are hateful, and SQLA is Just That DWIM.
361 # However, we don't want to catch 'A(x)' and turn it into 'A x'
363 # So the way we deal with this is to go through all our arguments, and
364 # then if the argument is -also- an apply, i.e. at least 'B', we check
365 # its arguments - and if there's only one of them, and that isn't an apply,
366 # then we convert to the bareword form. The end result should be:
369 # A( B( x ) ) -> A( B x )
370 # A( B( C( x ) ) ) -> A( B( C x ) )
371 # A( B( x + y ) ) -> A( B( x + y ) )
372 # A( B( x, y ) ) -> A( B( x, y ) )
374 # If this turns out not to be quite right, please add additional tests
375 # to either 01generate.t or 02where.t *and* update this comment.
377 foreach my $arg (@args) {
379 is_Operator($arg) and $arg->{operator}{'SQL.Naive'} eq 'apply'
380 and @{$arg->{args}} == 2 and !is_Operator($arg->{args}[1])
383 $arg->{operator}{'SQL.Naive'} = (shift @{$arg->{args}})->{elements}->[0];
386 $self->_assert_pass_injection_guard($op);
387 return $self->_op_to_dq(
388 apply => $self->_ident_to_dq($op), @args
392 sub _where_hashpair_to_dq {
393 my ($self, $k, $v, $logic) = @_;
395 if ($k =~ /^-(.*)/s) {
397 if ($op eq 'AND' or $op eq 'OR') {
398 return $self->_expr_to_dq($v, $op);
399 } elsif ($op eq 'NEST') {
400 return $self->_expr_to_dq($v);
401 } elsif ($op eq 'NOT') {
402 return $self->_op_to_dq(NOT => $self->_expr_to_dq($v));
403 } elsif ($op eq 'BOOL') {
404 return ref($v) ? $self->_expr_to_dq($v) : $self->_ident_to_dq($v);
405 } elsif ($op eq 'NOT_BOOL') {
406 return $self->_op_to_dq(
407 NOT => ref($v) ? $self->_expr_to_dq($v) : $self->_ident_to_dq($v)
409 } elsif ($op eq 'IDENT') {
410 return $self->_ident_to_dq($v);
411 } elsif ($op eq 'VALUE') {
412 return $self->_value_to_dq($v);
413 } elsif ($op =~ /^(?:AND|OR|NEST)_?\d+/) {
414 die "Use of [and|or|nest]_N modifiers is no longer supported";
416 return $self->_apply_to_dq($op, $v);
419 local our $Cur_Col_Meta = $k;
420 if (ref($v) eq 'ARRAY') {
422 return $self->_literal_to_dq($self->{sqlfalse});
423 } elsif (defined($v->[0]) && $v->[0] =~ /-(and|or)/i) {
424 return $self->_expr_to_dq_ARRAYREF([
425 map +{ $k => $_ }, @{$v}[1..$#$v]
428 return $self->_expr_to_dq_ARRAYREF([
429 map +{ $k => $_ }, @$v
431 } elsif (ref($v) eq 'SCALAR' or (ref($v) eq 'REF' and ref($$v) eq 'ARRAY')) {
432 return Literal('SQL', [ $self->_ident_to_dq($k), $self->_literal_to_dq($$v) ]);
434 my ($op, $rhs) = do {
435 if (ref($v) eq 'HASH') {
437 return $self->_expr_to_dq_ARRAYREF([
438 map +{ $k => { $_ => $v->{$_} } }, sort keys %$v
441 my ($op, $value) = %$v;
442 s/^-//, s/_/ /g for $op;
443 if ($op =~ /^(and|or)$/i) {
444 return $self->_expr_to_dq({ $k => $value }, $op);
446 my $special_op = List::Util::first {$op =~ $_->{regex}}
447 @{$self->{special_ops}}
449 return $self->_literal_to_dq(
450 [ $special_op->{handler}->($k, $op, $value) ]
452 } elsif ($op =~ /^(?:AND|OR|NEST)_?\d+$/i) {
453 die "Use of [and|or|nest]_N modifiers is no longer supported";
460 if ($op eq 'BETWEEN' or $op eq 'IN' or $op eq 'NOT IN' or $op eq 'NOT BETWEEN') {
461 if (ref($rhs) ne 'ARRAY') {
463 # have to add parens if none present because -in => \"SELECT ..."
464 # got documented. mst hates everything.
465 if (ref($rhs) eq 'SCALAR') {
467 1 while ($x =~ s/\A\s*\((.*)\)\s*\Z/$1/s);
470 my ($x, @rest) = @{$$rhs};
471 1 while ($x =~ s/\A\s*\((.*)\)\s*\Z/$1/s);
472 $rhs = \[ $x, @rest ];
475 return $self->_op_to_dq(
476 $op, $self->_ident_to_dq($k), $self->_literal_to_dq($$rhs)
479 return $self->_literal_to_dq($self->{sqlfalse}) unless @$rhs;
480 return $self->_op_to_dq(
481 $op, $self->_ident_to_dq($k), map $self->_expr_to_dq($_), @$rhs
483 } elsif ($op =~ s/^NOT (?!LIKE)//) {
484 return $self->_where_hashpair_to_dq(-not => { $k => { $op => $rhs } });
485 } elsif ($op eq 'IDENT') {
486 return $self->_op_to_dq(
487 $self->{cmp}, $self->_ident_to_dq($k), $self->_ident_to_dq($rhs)
489 } elsif ($op eq 'VALUE') {
490 return $self->_op_to_dq(
491 $self->{cmp}, $self->_ident_to_dq($k), $self->_value_to_dq($rhs)
493 } elsif (!defined($rhs)) {
495 if ($op eq '=' or $op eq 'LIKE') {
497 } elsif ($op eq '!=') {
500 die "Can't do undef -> NULL transform for operator ${op}";
503 return $self->_op_to_dq($null_op, $self->_ident_to_dq($k));
505 if (ref($rhs) eq 'ARRAY') {
507 return $self->_literal_to_dq(
508 $op eq '!=' ? $self->{sqltrue} : $self->{sqlfalse}
510 } elsif (defined($rhs->[0]) and $rhs->[0] =~ /^-(and|or)$/i) {
511 return $self->_expr_to_dq_ARRAYREF([
512 map +{ $k => { $op => $_ } }, @{$rhs}[1..$#$rhs]
514 } elsif ($op =~ /^-(?:AND|OR|NEST)_?\d+/) {
515 die "Use of [and|or|nest]_N modifiers is no longer supported";
517 return $self->_expr_to_dq_ARRAYREF([
518 map +{ $k => { $op => $_ } }, @$rhs
521 return $self->_op_to_dq(
522 $op, $self->_ident_to_dq($k), $self->_expr_to_dq($rhs)
527 sub _order_by_to_dq {
528 my ($self, $arg, $dir, $nulls, $from) = @_;
534 (defined($dir) ? (!!($dir =~ /desc/i)) : undef),
535 (defined($nulls) ? ($nulls =~ /first/i ? 1 : -1) : undef),
536 ($from ? ($from) : undef),
540 $dq->{by} = $self->_ident_to_dq($arg);
541 } elsif (ref($arg) eq 'ARRAY') {
543 local our $Order_Inner unless our $Order_Recursing;
544 local $Order_Recursing = 1;
546 foreach my $member (@$arg) {
548 my $next = $self->_order_by_to_dq($member, $dir, $nulls, $from);
550 $inner->{from} = $next if $inner;
551 $inner = $Order_Inner || $next;
553 $Order_Inner = $inner;
555 } elsif (ref($arg) eq 'REF' and ref($$arg) eq 'ARRAY') {
556 $dq->{by} = $self->_literal_to_dq($$arg);
557 } elsif (ref($arg) eq 'REF' and ref($$arg) eq 'HASH') {
559 } elsif (ref($arg) eq 'SCALAR') {
561 # < mst> right, but if it doesn't match that, it goes "ok, right, not sure,
562 # totally leaving this untouched as a literal"
563 # < mst> so I -think- it's relatively robust
564 # < ribasushi> right, it's relatively safe then
565 # < ribasushi> is this regex centralized?
566 # < mst> it only exists in _order_by_to_dq in SQL::Abstract::Converter
567 # < mst> it only exists because you were kind enough to support new
568 # dbihacks crack combined with old literal order_by crack
569 # < ribasushi> heh :)
571 # this should take into account our quote char and name sep
573 my $match_ident = '\w+(?:\.\w+)*';
575 if (my ($ident, $dir) = $$arg =~ /^(${match_ident})(?:\s+(desc|asc))?$/i) {
576 $dq->{by} = $self->_ident_to_dq($ident);
577 $dq->{reverse} = 1 if $dir and lc($dir) eq 'desc';
579 $dq->{by} = $self->_literal_to_dq($$arg);
581 } elsif (ref($arg) eq 'HASH') {
582 return () unless %$arg;
584 my ($direction, $val);
585 foreach my $key (keys %$arg) {
586 if ( $key =~ /^-(desc|asc)/i ) {
587 die "hash passed to _order_by_to_dq must have exactly one of -desc or -asc"
588 if defined $direction;
591 } elsif ($key =~ /^-nulls$/i) {
592 $nulls = $arg->{$key};
593 die "invalid value for -nulls" unless $nulls =~ /^(?:first|last)$/i;
595 die "invalid key in hash passed to _order_by_to_dq";
599 die "hash passed to _order_by_to_dq must have exactly one of -desc or -asc"
600 unless defined $direction;
602 return $self->_order_by_to_dq($val, $direction, $nulls, $from);
604 die "Can't handle $arg in _order_by_to_dq";
610 my ($self, $from) = @_;
611 if (ref($from) eq 'ARRAY') {
612 die "Empty FROM list" unless my @f = @$from;
613 my $dq = $self->_table_to_dq(shift @f);
614 while (my $x = shift @f) {
617 $self->_table_to_dq($x),
621 } elsif (ref($from) eq 'SCALAR' or (ref($from) eq 'REF')) {
622 $self->_literal_to_dq($$from);
624 $self->_ident_to_dq($from);
630 #my ($self, $col, @vals) = @_;
632 #LDNOTE : changed original implementation below because it did not make
633 # sense when bindtype eq 'columns' and @vals > 1.
634 # return $self->{bindtype} eq 'columns' ? [ $col, @vals ] : @vals;
636 # called often - tighten code
637 return $_[0]->bind_meta
638 ? map {[$_[1], $_]} @_[2 .. $#_]
643 # Dies if any element of @bind is not in [colname => value] format
644 # if bindtype is 'columns'.
645 sub _assert_bindval_matches_bindtype {
646 # my ($self, @bind) = @_;
648 if ($self->bind_meta) {
650 if (!defined $_ || ref($_) ne 'ARRAY' || @$_ != 2) {
651 die "bindtype 'columns' selected, you need to pass: [column_name => bind_value]"
657 # Fix SQL case, if so requested
659 return $_[0]->lower_case ? $_[1] : uc($_[1]);