1 package DBIx::Class::SQLMaker;
8 DBIx::Class::SQLMaker - An SQL::Abstract-based SQL maker class
12 This module is a subclass of L<SQL::Abstract> and includes a number of
13 DBIC-specific workarounds, not yet suitable for inclusion into the
14 L<SQL::Abstract> core. It also provides all (and more than) the functionality
15 of L<SQL::Abstract::Limit>, see L<DBIx::Class::SQLMaker::LimitDialects> for
18 Currently the enhancements to L<SQL::Abstract> are:
22 =item * Support for C<JOIN> statements (via extended C<table/from> support)
24 =item * Support of functions in C<SELECT> lists
26 =item * C<GROUP BY>/C<HAVING> support (via extensions to the order_by parameter)
28 =item * Support of C<...FOR UPDATE> type of select statement modifiers
30 =item * The L</-ident> operator
32 =item * The L</-value> operator
36 Another operator is C<-func> that allows you to call SQL functions with
37 arguments. It receives an array reference containing the function name
38 as the 0th argument and the other arguments being its parameters. For example:
41 -func => ['substr', 'Hello', 50, 5],
46 $stmt = "WHERE (substr(?,?,?))";
47 @bind = ("Hello", 50, 5);
49 Yet another operator is C<-op> that allows you to use SQL operators. It
50 receives an array reference containing the operator 0th argument and the other
51 arguments being its operands. For example:
54 foo => { -op => ['+', \'bar', 50, 5] },
59 $stmt = "WHERE (foo = bar + ? + ?)";
65 DBIx::Class::SQLMaker::LimitDialects
71 use Sub::Name 'subname';
72 use DBIx::Class::Carp;
73 use DBIx::Class::Exception;
76 __PACKAGE__->mk_group_accessors (simple => qw/quote_char name_sep limit_dialect datetime_parser/);
78 # for when I need a normalized l/r pair
81 { defined $_ ? $_ : '' }
82 ( ref $_[0]->{quote_char} ? (@{$_[0]->{quote_char}}) : ( ($_[0]->{quote_char}) x 2 ) )
86 # FIXME when we bring in the storage weaklink, check its schema
87 # weaklink and channel through $schema->throw_exception
88 sub throw_exception { DBIx::Class::Exception->throw($_[1]) }
91 # reinstall the belch()/puke() functions of SQL::Abstract with custom versions
92 # that use DBIx::Class::Carp/DBIx::Class::Exception instead of plain Carp
93 no warnings qw/redefine/;
95 *SQL::Abstract::belch = subname 'SQL::Abstract::belch' => sub (@) {
96 my($func) = (caller(1))[3];
97 carp "[$func] Warning: ", @_;
100 *SQL::Abstract::puke = subname 'SQL::Abstract::puke' => sub (@) {
101 my($func) = (caller(1))[3];
102 __PACKAGE__->throw_exception("[$func] Fatal: " . join ('', @_));
105 # Current SQLA pollutes its namespace - clean for the time being
106 namespace::clean->clean_subroutines(qw/SQL::Abstract carp croak confess/);
109 # the "oh noes offset/top without limit" constant
110 # limited to 31 bits for sanity (and consistency,
111 # since it may be handed to the like of sprintf %u)
113 # Also *some* builds of SQLite fail the test
114 # some_column BETWEEN ? AND ?: 1, 4294967295
115 # with the proper integer bind attrs
117 # Implemented as a method, since ::Storage::DBI also
118 # refers to it (i.e. for the case of software_limit or
119 # as the value to abuse with MSSQL ordered subqueries)
120 sub __max_int () { 0x7FFFFFFF };
122 # poor man's de-qualifier
124 $_[0]->next::method( ( $_[0]{_dequalify_idents} and ! ref $_[1] )
125 ? $_[1] =~ / ([^\.]+) $ /x
131 my $self = shift->next::method(@_);
133 # use the same coderefs, they are prepared to handle both cases
134 my @extra_dbic_syntax = (
135 { regex => qr/^ ident $/xi, handler => '_where_op_IDENT' },
136 { regex => qr/^ value $/xi, handler => '_where_op_VALUE' },
137 { regex => qr/^ func $/ix, handler => '_where_op_FUNC' },
138 { regex => qr/^ op $/ix, handler => '_where_op_OP' },
139 { regex => qr/^ dt $/xi, handler => '_where_op_CONVERT_DATETIME' },
140 { regex => qr/^ dt_get $/xi, handler => '_where_op_GET_DATETIME' },
141 { regex => qr/^ dt_diff $/xi, handler => '_where_op_DIFF_DATETIME' },
142 map +{ regex => qr/^ dt_$_ $/xi, handler => '_where_op_GET_DATETIME_'.uc($_) },
146 push @{$self->{special_ops}}, @extra_dbic_syntax;
147 push @{$self->{unary_ops}}, @extra_dbic_syntax;
152 sub _where_op_IDENT {
154 my ($op, $rhs) = splice @_, -2;
156 $self->throw_exception("-$op takes a single scalar argument (a quotable identifier)");
159 # in case we are called as a top level special op (no '=')
162 $_ = $self->_convert($self->_quote($_)) for ($lhs, $rhs);
170 sub _where_op_CONVERT_DATETIME {
172 my ($op, $rhs) = splice @_, -2;
173 croak "-$op takes a DateTime only" unless ref $rhs && $rhs->isa('DateTime');
175 # in case we are called as a top level special op (no '=')
178 $rhs = $self->datetime_parser->format_datetime($rhs);
181 ($lhs || $self->{_nested_func_lhs} || croak "Unable to find bindtype for -value $rhs"),
187 $self->_convert($self->_quote($lhs)) . ' = ' . $self->_convert('?'),
191 $self->_convert('?'),
197 sub _unsupported_date_extraction {
198 "date part extraction not supported for part \"$_[1]\" with database \"$_[2]\""
201 sub _unsupported_date_diff {
202 "date diff not supported for part \"$_[1]\" with database \"$_[2]\""
205 sub _datetime_sql { die 'date part extraction not implemented for this database' }
207 sub _datetime_diff_sql { die 'date diffing not implemented for this database' }
209 sub _where_op_GET_DATETIME {
224 croak 'args to -dt_get must be an arrayref' unless ref $vals eq 'ARRAY';
225 croak 'first arg to -dt_get must be a scalar' unless !ref $vals->[0];
227 my $part = $vals->[0];
228 my $val = $vals->[1];
230 my ($sql, @bind) = $self->_SWITCH_refkind($val, {
232 return ($self->_convert('?'), $self->_bindtype($k, $val) );
238 my ($sql, @bind) = @$$val;
239 $self->_assert_bindval_matches_bindtype(@bind);
240 return ($sql, @bind);
243 my $method = $self->_METHOD_FOR_refkind("_where_hashpair", $val);
244 $self->$method('', $val);
248 return $self->_datetime_sql($part, $sql), @bind;
251 for my $part (qw(month day year)) {
253 my $name = '_where_op_GET_DATETIME_' . uc($part);
254 *{$name} = subname "DBIx::Class::SQLMaker::$name", sub {
256 my ($op, $rhs) = splice @_, -2;
260 return $self->_where_op_GET_DATETIME($op, $lhs, [$part, $rhs])
264 sub _where_op_DIFF_DATETIME {
279 croak 'args to -dt_diff must be an arrayref' unless ref $vals eq 'ARRAY';
280 croak 'first arg to -dt_diff must be a scalar' unless !ref $vals->[0];
281 croak '-dt_diff must have two more arguments' unless scalar @$vals == 3;
283 my ($part, @val) = @$vals;
284 my $placeholder = $self->_convert('?');
286 my (@all_sql, @all_bind);
287 foreach my $val (@val) {
288 my ($sql, @bind) = $self->_SWITCH_refkind($val, {
290 return ($placeholder, $self->_bindtype($k, $val) );
296 my ($sql, @bind) = @$$val;
297 $self->_assert_bindval_matches_bindtype(@bind);
298 return ($sql, @bind);
301 my $method = $self->_METHOD_FOR_refkind("_where_hashpair", $val);
302 $self->$method('', $val);
306 push @all_bind, @bind;
309 return $self->_datetime_diff_sql($part, $all_sql[0], $all_sql[1]), @all_bind
312 sub _where_op_VALUE {
314 my ($op, $rhs) = splice @_, -2;
316 # in case we are called as a top level special op (no '=')
320 ($lhs || $self->{_nested_func_lhs} || $self->throw_exception("Unable to find bindtype for -value $rhs") ),
326 $self->_convert($self->_quote($lhs)) . ' = ' . $self->_convert('?'),
330 $self->_convert('?'),
337 carp_unique ("-nest in search conditions is deprecated, you most probably wanted:\n"
338 .q|{..., -and => [ \%cond0, \@cond1, \'cond2', \[ 'cond3', [ col => bind ] ], etc. ], ... }|
341 shift->next::method(@_);
344 # Handle limit-dialect selection
346 my ($self, $table, $fields, $where, $rs_attrs, $limit, $offset) = @_;
349 $fields = $self->_recurse_fields($fields);
351 if (defined $offset) {
352 $self->throw_exception('A supplied offset must be a non-negative integer')
353 if ( $offset =~ /\D/ or $offset < 0 );
357 if (defined $limit) {
358 $self->throw_exception('A supplied limit must be a positive integer')
359 if ( $limit =~ /\D/ or $limit <= 0 );
362 $limit = $self->__max_int;
368 # this is legacy code-flow from SQLA::Limit, it is not set in stone
370 ($sql, @bind) = $self->next::method ($table, $fields, $where);
373 $self->can ('emulate_limit') # also backcompat hook from SQLA::Limit
376 my $dialect = $self->limit_dialect
377 or $self->throw_exception( "Unable to generate SQL-limit - no limit dialect specified on $self, and no emulate_limit method found" );
378 $self->can ("_$dialect")
379 or $self->throw_exception(__PACKAGE__ . " does not implement the requested dialect '$dialect'");
383 $sql = $self->$limiter (
385 { %{$rs_attrs||{}}, _selector_sql => $fields },
391 ($sql, @bind) = $self->next::method ($table, $fields, $where, $rs_attrs);
394 push @{$self->{where_bind}}, @bind;
396 # this *must* be called, otherwise extra binds will remain in the sql-maker
397 my @all_bind = $self->_assemble_binds;
399 $sql .= $self->_lock_select ($rs_attrs->{for})
402 return wantarray ? ($sql, @all_bind) : $sql;
405 sub _assemble_binds {
407 return map { @{ (delete $self->{"${_}_bind"}) || [] } } (qw/select from where group having order limit/);
411 update => 'FOR UPDATE',
412 shared => 'FOR SHARE',
415 my ($self, $type) = @_;
416 my $sql = $for_syntax->{$type} || $self->throw_exception( "Unknown SELECT .. FOR type '$type' requested" );
420 # Handle default inserts
422 # optimized due to hotttnesss
423 # my ($self, $table, $data, $options) = @_;
425 # SQLA will emit INSERT INTO $table ( ) VALUES ( )
426 # which is sadly understood only by MySQL. Change default behavior here,
427 # until SQLA2 comes with proper dialect support
428 if (! $_[2] or (ref $_[2] eq 'HASH' and !keys %{$_[2]} ) ) {
431 'INSERT INTO %s DEFAULT VALUES', $_[0]->_quote($_[1])
434 if ( ($_[3]||{})->{returning} ) {
436 ($s, @bind) = $_[0]->_insert_returning ($_[3]);
440 return ($sql, @bind);
446 sub _recurse_fields {
447 my ($self, $fields, $depth) = @_;
449 my $ref = ref $fields;
450 return $self->_quote($fields) unless $ref;
451 return $$fields if $ref eq 'SCALAR';
453 if ($ref eq 'ARRAY') {
454 return join(', ', map { $self->_recurse_fields($_, $depth + 1) } @$fields)
457 my ($sql, @bind) = $self->_recurse_where({@$fields});
459 push @{$self->{select_bind}}, @bind;
462 elsif ($ref eq 'HASH') {
463 my %hash = %$fields; # shallow copy
465 my $as = delete $hash{-as}; # if supplied
467 my ($func, $args, @toomany) = %hash;
469 # there should be only one pair
471 $self->throw_exception( "Malformed select argument - too many keys in hash: " . join (',', keys %$fields ) );
474 if (lc ($func) eq 'distinct' && ref $args eq 'ARRAY' && @$args > 1) {
475 $self->throw_exception (
476 'The select => { distinct => ... } syntax is not supported for multiple columns.'
477 .' Instead please use { group_by => [ qw/' . (join ' ', @$args) . '/ ] }'
478 .' or { select => [ qw/' . (join ' ', @$args) . '/ ], distinct => 1 }'
482 my $select = sprintf ('%s( %s )%s',
483 $self->_sqlcase($func),
484 $self->_recurse_fields($args, $depth + 1),
486 ? sprintf (' %s %s', $self->_sqlcase('as'), $self->_quote ($as) )
492 # Is the second check absolutely necessary?
493 elsif ( $ref eq 'REF' and ref($$fields) eq 'ARRAY' ) {
494 push @{$self->{select_bind}}, @{$$fields}[1..$#$$fields];
495 return $$fields->[0];
498 $self->throw_exception( $ref . qq{ unexpected in _recurse_fields()} );
503 # this used to be a part of _order_by but is broken out for clarity.
504 # What we have been doing forever is hijacking the $order arg of
505 # SQLA::select to pass in arbitrary pieces of data (first the group_by,
506 # then pretty much the entire resultset attr-hash, as more and more
507 # things in the SQLA space need to have mopre info about the $rs they
508 # create SQL for. The alternative would be to keep expanding the
509 # signature of _select with more and more positional parameters, which
510 # is just gross. All hail SQLA2!
511 sub _parse_rs_attrs {
512 my ($self, $arg) = @_;
516 if ($arg->{group_by}) {
517 # horible horrible, waiting for refactor
518 local $self->{select_bind};
519 if (my $g = $self->_recurse_fields($arg->{group_by}) ) {
520 $sql .= $self->_sqlcase(' group by ') . $g;
521 push @{$self->{group_bind} ||= []}, @{$self->{select_bind}||[]};
525 if (defined $arg->{having}) {
526 my ($frag, @bind) = $self->_recurse_where($arg->{having});
527 push(@{$self->{having_bind}}, @bind);
528 $sql .= $self->_sqlcase(' having ') . $frag;
531 if (defined $arg->{order_by}) {
532 $sql .= $self->_order_by ($arg->{order_by});
539 my ($self, $arg) = @_;
541 # check that we are not called in legacy mode (order_by as 4th argument)
542 if (ref $arg eq 'HASH' and not grep { $_ =~ /^-(?:desc|asc)/i } keys %$arg ) {
543 return $self->_parse_rs_attrs ($arg);
546 my ($sql, @bind) = $self->next::method($arg);
547 push @{$self->{order_bind}}, @bind;
553 # optimized due to hotttnesss
554 # my ($self, $from) = @_;
555 if (my $ref = ref $_[1] ) {
556 if ($ref eq 'ARRAY') {
557 return $_[0]->_recurse_from(@{$_[1]});
559 elsif ($ref eq 'HASH') {
560 return $_[0]->_recurse_from($_[1]);
562 elsif ($ref eq 'REF' && ref ${$_[1]} eq 'ARRAY') {
563 my ($sql, @bind) = @{ ${$_[1]} };
564 push @{$_[0]->{from_bind}}, @bind;
568 return $_[0]->next::method ($_[1]);
571 sub _generate_join_clause {
572 my ($self, $join_type) = @_;
574 $join_type = $self->{_default_jointype}
575 if ! defined $join_type;
577 return sprintf ('%s JOIN ',
578 $join_type ? $self->_sqlcase($join_type) : ''
588 # $_[1] gets set to "op"
593 # $_[2] gets set to "op"
597 my $label = $self->_convert($self->_quote($k));
598 my $placeholder = $self->_convert('?');
600 croak '-func must be an array' unless ref $vals eq 'ARRAY';
601 croak 'first arg for -func must be a scalar' unless !ref $vals->[0];
603 my ($func,@rest_of_vals) = @$vals;
605 $self->_assert_pass_injection_guard($func);
607 my (@all_sql, @all_bind);
608 foreach my $val (@rest_of_vals) {
609 my ($sql, @bind) = $self->_SWITCH_refkind($val, {
611 return ($placeholder, $self->_bindtype($k, $val) );
617 my ($sql, @bind) = @$$val;
618 $self->_assert_bindval_matches_bindtype(@bind);
619 return ($sql, @bind);
622 my $method = $self->_METHOD_FOR_refkind("_where_hashpair", $val);
623 $self->$method('', $val);
627 push @all_bind, @bind;
630 my ($clause, @bind) = ("$func(" . (join ",", @all_sql) . ")", @all_bind);
632 my $sql = $k ? "( $label = $clause )" : "( $clause )";
642 # $_[1] gets set to "op"
647 # $_[2] gets set to "op"
651 my $label = $self->_convert($self->_quote($k));
652 my $placeholder = $self->_convert('?');
654 croak 'argument to -op must be an arrayref' unless ref $vals eq 'ARRAY';
655 croak 'first arg for -op must be a scalar' unless !ref $vals->[0];
657 my ($op, @rest_of_vals) = @$vals;
659 $self->_assert_pass_injection_guard($op);
661 my (@all_sql, @all_bind);
662 foreach my $val (@rest_of_vals) {
663 my ($sql, @bind) = $self->_SWITCH_refkind($val, {
665 return ($placeholder, $self->_bindtype($k, $val) );
671 my ($sql, @bind) = @$$val;
672 $self->_assert_bindval_matches_bindtype(@bind);
673 return ($sql, @bind);
676 my $method = $self->_METHOD_FOR_refkind("_where_hashpair", $val);
677 $self->$method('', $val);
681 push @all_bind, @bind;
684 my ($clause, @bind) = ((join " $op ", @all_sql), @all_bind);
686 my $sql = $k ? "( $label = $clause )" : "( $clause )";
693 return join (' ', $self->_gen_from_blocks(@_) );
696 sub _gen_from_blocks {
697 my ($self, $from, @joins) = @_;
699 my @fchunks = $self->_from_chunk_to_sql($from);
704 # check whether a join type exists
705 my $to_jt = ref($to) eq 'ARRAY' ? $to->[0] : $to;
707 if (ref($to_jt) eq 'HASH' and defined($to_jt->{-join_type})) {
708 $join_type = $to_jt->{-join_type};
709 $join_type =~ s/^\s+ | \s+$//xg;
712 my @j = $self->_generate_join_clause( $join_type );
714 if (ref $to eq 'ARRAY') {
715 push(@j, '(', $self->_recurse_from(@$to), ')');
718 push(@j, $self->_from_chunk_to_sql($to));
721 my ($sql, @bind) = $self->_join_condition($on);
722 push(@j, ' ON ', $sql);
723 push @{$self->{from_bind}}, @bind;
725 push @fchunks, join '', @j;
731 sub _from_chunk_to_sql {
732 my ($self, $fromspec) = @_;
734 return join (' ', $self->_SWITCH_refkind($fromspec, {
739 push @{$self->{from_bind}}, @{$$fromspec}[1..$#$$fromspec];
743 my ($as, $table, $toomuch) = ( map
744 { $_ => $fromspec->{$_} }
745 ( grep { $_ !~ /^\-/ } keys %$fromspec )
748 $self->throw_exception( "Only one table/as pair expected in from-spec but an exra '$toomuch' key present" )
751 ($self->_from_chunk_to_sql($table), $self->_quote($as) );
754 $self->_quote($fromspec);
759 sub _join_condition {
760 my ($self, $cond) = @_;
762 # Backcompat for the old days when a plain hashref
763 # { 't1.col1' => 't2.col2' } meant ON t1.col1 = t2.col2
764 # Once things settle we should start warning here so that
765 # folks unroll their hacks
771 (keys %$cond)[0] =~ /\./
773 ! ref ( (values %$cond)[0] )
775 $cond = { keys %$cond => { -ident => values %$cond } }
777 elsif ( ref $cond eq 'ARRAY' ) {
778 # do our own ORing so that the hashref-shim above is invoked
781 foreach my $c (@$cond) {
782 my ($sql, @bind) = $self->_join_condition($c);
786 return join(' OR ', @parts), @binds;
789 return $self->_recurse_where($cond);
798 Used to explicitly specify an SQL identifier. Takes a plain string as value
799 which is then invariably treated as a column name (and is being properly
800 quoted if quoting has been requested). Most useful for comparison of two
804 priority => { '<', 2 },
805 requestor => { -ident => 'submitter' }
810 $stmt = 'WHERE "priority" < ? AND "requestor" = "submitter"';
815 The -value operator signals that the argument to the right is a raw bind value.
816 It will be passed straight to DBI, without invoking any of the SQL::Abstract
817 condition-parsing logic. This allows you to, for example, pass an array as a
818 column value for databases that support array datatypes, e.g.:
821 array => { -value => [1, 2, 3] }
826 $stmt = 'WHERE array = ?';
831 See L<DBIx::Class/CONTRIBUTORS>.
835 You may distribute this code under the same terms as Perl itself.