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('?'),
204 sub _datetime_sql { "STRFTIME('$part_map{$_[1]}', $_[2])" }
207 sub _datetime_diff_sql {
208 my ($self, $part, $left, $right) = @_;
210 $self->_datetime_sql($part, $left)
212 $self->_datetime_sql($part, $right)
216 sub _where_op_GET_DATETIME {
231 croak 'args to -dt_get must be an arrayref' unless ref $vals eq 'ARRAY';
232 croak 'first arg to -dt_get must be a scalar' unless !ref $vals->[0];
234 my $part = $vals->[0];
235 my $val = $vals->[1];
237 my ($sql, @bind) = $self->_SWITCH_refkind($val, {
239 return ($self->_convert('?'), $self->_bindtype($k, $val) );
245 my ($sql, @bind) = @$$val;
246 $self->_assert_bindval_matches_bindtype(@bind);
247 return ($sql, @bind);
250 my $method = $self->_METHOD_FOR_refkind("_where_hashpair", $val);
251 $self->$method('', $val);
255 return $self->_datetime_sql($part, $sql), @bind;
258 for my $part (qw(month day year)) {
260 my $name = '_where_op_GET_DATETIME_' . uc($part);
261 *{$name} = subname "DBIx::Class::SQLMaker::$name", sub {
263 my ($op, $rhs) = splice @_, -2;
267 return $self->_where_op_GET_DATETIME($op, $lhs, [$part, $rhs])
271 sub _where_op_DIFF_DATETIME {
286 croak 'args to -dt_diff must be an arrayref' unless ref $vals eq 'ARRAY';
287 croak 'first arg to -dt_diff must be a scalar' unless !ref $vals->[0];
288 croak '-dt_diff must have two more arguments' unless scalar @$vals == 3;
290 my ($part, @val) = @$vals;
291 my $placeholder = $self->_convert('?');
293 my (@all_sql, @all_bind);
294 foreach my $val (@val) {
295 my ($sql, @bind) = $self->_SWITCH_refkind($val, {
297 return ($placeholder, $self->_bindtype($k, $val) );
303 my ($sql, @bind) = @$$val;
304 $self->_assert_bindval_matches_bindtype(@bind);
305 return ($sql, @bind);
308 my $method = $self->_METHOD_FOR_refkind("_where_hashpair", $val);
309 $self->$method('', $val);
313 push @all_bind, @bind;
316 return $self->_datetime_diff_sql($part, $all_sql[0], $all_sql[1]), @all_bind
319 sub _where_op_VALUE {
321 my ($op, $rhs) = splice @_, -2;
323 # in case we are called as a top level special op (no '=')
327 ($lhs || $self->{_nested_func_lhs} || $self->throw_exception("Unable to find bindtype for -value $rhs") ),
333 $self->_convert($self->_quote($lhs)) . ' = ' . $self->_convert('?'),
337 $self->_convert('?'),
344 carp_unique ("-nest in search conditions is deprecated, you most probably wanted:\n"
345 .q|{..., -and => [ \%cond0, \@cond1, \'cond2', \[ 'cond3', [ col => bind ] ], etc. ], ... }|
348 shift->next::method(@_);
351 # Handle limit-dialect selection
353 my ($self, $table, $fields, $where, $rs_attrs, $limit, $offset) = @_;
356 $fields = $self->_recurse_fields($fields);
358 if (defined $offset) {
359 $self->throw_exception('A supplied offset must be a non-negative integer')
360 if ( $offset =~ /\D/ or $offset < 0 );
364 if (defined $limit) {
365 $self->throw_exception('A supplied limit must be a positive integer')
366 if ( $limit =~ /\D/ or $limit <= 0 );
369 $limit = $self->__max_int;
375 # this is legacy code-flow from SQLA::Limit, it is not set in stone
377 ($sql, @bind) = $self->next::method ($table, $fields, $where);
380 $self->can ('emulate_limit') # also backcompat hook from SQLA::Limit
383 my $dialect = $self->limit_dialect
384 or $self->throw_exception( "Unable to generate SQL-limit - no limit dialect specified on $self, and no emulate_limit method found" );
385 $self->can ("_$dialect")
386 or $self->throw_exception(__PACKAGE__ . " does not implement the requested dialect '$dialect'");
390 $sql = $self->$limiter (
392 { %{$rs_attrs||{}}, _selector_sql => $fields },
398 ($sql, @bind) = $self->next::method ($table, $fields, $where, $rs_attrs);
401 push @{$self->{where_bind}}, @bind;
403 # this *must* be called, otherwise extra binds will remain in the sql-maker
404 my @all_bind = $self->_assemble_binds;
406 $sql .= $self->_lock_select ($rs_attrs->{for})
409 return wantarray ? ($sql, @all_bind) : $sql;
412 sub _assemble_binds {
414 return map { @{ (delete $self->{"${_}_bind"}) || [] } } (qw/select from where group having order limit/);
418 update => 'FOR UPDATE',
419 shared => 'FOR SHARE',
422 my ($self, $type) = @_;
423 my $sql = $for_syntax->{$type} || $self->throw_exception( "Unknown SELECT .. FOR type '$type' requested" );
427 # Handle default inserts
429 # optimized due to hotttnesss
430 # my ($self, $table, $data, $options) = @_;
432 # SQLA will emit INSERT INTO $table ( ) VALUES ( )
433 # which is sadly understood only by MySQL. Change default behavior here,
434 # until SQLA2 comes with proper dialect support
435 if (! $_[2] or (ref $_[2] eq 'HASH' and !keys %{$_[2]} ) ) {
438 'INSERT INTO %s DEFAULT VALUES', $_[0]->_quote($_[1])
441 if ( ($_[3]||{})->{returning} ) {
443 ($s, @bind) = $_[0]->_insert_returning ($_[3]);
447 return ($sql, @bind);
453 sub _recurse_fields {
454 my ($self, $fields, $depth) = @_;
456 my $ref = ref $fields;
457 return $self->_quote($fields) unless $ref;
458 return $$fields if $ref eq 'SCALAR';
460 if ($ref eq 'ARRAY') {
461 return join(', ', map { $self->_recurse_fields($_, $depth + 1) } @$fields)
464 my ($sql, @bind) = $self->_recurse_where({@$fields});
466 push @{$self->{select_bind}}, @bind;
469 elsif ($ref eq 'HASH') {
470 my %hash = %$fields; # shallow copy
472 my $as = delete $hash{-as}; # if supplied
474 my ($func, $args, @toomany) = %hash;
476 # there should be only one pair
478 $self->throw_exception( "Malformed select argument - too many keys in hash: " . join (',', keys %$fields ) );
481 if (lc ($func) eq 'distinct' && ref $args eq 'ARRAY' && @$args > 1) {
482 $self->throw_exception (
483 'The select => { distinct => ... } syntax is not supported for multiple columns.'
484 .' Instead please use { group_by => [ qw/' . (join ' ', @$args) . '/ ] }'
485 .' or { select => [ qw/' . (join ' ', @$args) . '/ ], distinct => 1 }'
489 my $select = sprintf ('%s( %s )%s',
490 $self->_sqlcase($func),
491 $self->_recurse_fields($args, $depth + 1),
493 ? sprintf (' %s %s', $self->_sqlcase('as'), $self->_quote ($as) )
499 # Is the second check absolutely necessary?
500 elsif ( $ref eq 'REF' and ref($$fields) eq 'ARRAY' ) {
501 push @{$self->{select_bind}}, @{$$fields}[1..$#$$fields];
502 return $$fields->[0];
505 $self->throw_exception( $ref . qq{ unexpected in _recurse_fields()} );
510 # this used to be a part of _order_by but is broken out for clarity.
511 # What we have been doing forever is hijacking the $order arg of
512 # SQLA::select to pass in arbitrary pieces of data (first the group_by,
513 # then pretty much the entire resultset attr-hash, as more and more
514 # things in the SQLA space need to have mopre info about the $rs they
515 # create SQL for. The alternative would be to keep expanding the
516 # signature of _select with more and more positional parameters, which
517 # is just gross. All hail SQLA2!
518 sub _parse_rs_attrs {
519 my ($self, $arg) = @_;
523 if ($arg->{group_by}) {
524 # horible horrible, waiting for refactor
525 local $self->{select_bind};
526 if (my $g = $self->_recurse_fields($arg->{group_by}) ) {
527 $sql .= $self->_sqlcase(' group by ') . $g;
528 push @{$self->{group_bind} ||= []}, @{$self->{select_bind}||[]};
532 if (defined $arg->{having}) {
533 my ($frag, @bind) = $self->_recurse_where($arg->{having});
534 push(@{$self->{having_bind}}, @bind);
535 $sql .= $self->_sqlcase(' having ') . $frag;
538 if (defined $arg->{order_by}) {
539 $sql .= $self->_order_by ($arg->{order_by});
546 my ($self, $arg) = @_;
548 # check that we are not called in legacy mode (order_by as 4th argument)
549 if (ref $arg eq 'HASH' and not grep { $_ =~ /^-(?:desc|asc)/i } keys %$arg ) {
550 return $self->_parse_rs_attrs ($arg);
553 my ($sql, @bind) = $self->next::method($arg);
554 push @{$self->{order_bind}}, @bind;
560 # optimized due to hotttnesss
561 # my ($self, $from) = @_;
562 if (my $ref = ref $_[1] ) {
563 if ($ref eq 'ARRAY') {
564 return $_[0]->_recurse_from(@{$_[1]});
566 elsif ($ref eq 'HASH') {
567 return $_[0]->_recurse_from($_[1]);
569 elsif ($ref eq 'REF' && ref ${$_[1]} eq 'ARRAY') {
570 my ($sql, @bind) = @{ ${$_[1]} };
571 push @{$_[0]->{from_bind}}, @bind;
575 return $_[0]->next::method ($_[1]);
578 sub _generate_join_clause {
579 my ($self, $join_type) = @_;
581 $join_type = $self->{_default_jointype}
582 if ! defined $join_type;
584 return sprintf ('%s JOIN ',
585 $join_type ? $self->_sqlcase($join_type) : ''
595 # $_[1] gets set to "op"
600 # $_[2] gets set to "op"
604 my $label = $self->_convert($self->_quote($k));
605 my $placeholder = $self->_convert('?');
607 croak '-func must be an array' unless ref $vals eq 'ARRAY';
608 croak 'first arg for -func must be a scalar' unless !ref $vals->[0];
610 my ($func,@rest_of_vals) = @$vals;
612 $self->_assert_pass_injection_guard($func);
614 my (@all_sql, @all_bind);
615 foreach my $val (@rest_of_vals) {
616 my ($sql, @bind) = $self->_SWITCH_refkind($val, {
618 return ($placeholder, $self->_bindtype($k, $val) );
624 my ($sql, @bind) = @$$val;
625 $self->_assert_bindval_matches_bindtype(@bind);
626 return ($sql, @bind);
629 my $method = $self->_METHOD_FOR_refkind("_where_hashpair", $val);
630 $self->$method('', $val);
634 push @all_bind, @bind;
637 my ($clause, @bind) = ("$func(" . (join ",", @all_sql) . ")", @all_bind);
639 my $sql = $k ? "( $label = $clause )" : "( $clause )";
649 # $_[1] gets set to "op"
654 # $_[2] gets set to "op"
658 my $label = $self->_convert($self->_quote($k));
659 my $placeholder = $self->_convert('?');
661 croak 'argument to -op must be an arrayref' unless ref $vals eq 'ARRAY';
662 croak 'first arg for -op must be a scalar' unless !ref $vals->[0];
664 my ($op, @rest_of_vals) = @$vals;
666 $self->_assert_pass_injection_guard($op);
668 my (@all_sql, @all_bind);
669 foreach my $val (@rest_of_vals) {
670 my ($sql, @bind) = $self->_SWITCH_refkind($val, {
672 return ($placeholder, $self->_bindtype($k, $val) );
678 my ($sql, @bind) = @$$val;
679 $self->_assert_bindval_matches_bindtype(@bind);
680 return ($sql, @bind);
683 my $method = $self->_METHOD_FOR_refkind("_where_hashpair", $val);
684 $self->$method('', $val);
688 push @all_bind, @bind;
691 my ($clause, @bind) = ((join " $op ", @all_sql), @all_bind);
693 my $sql = $k ? "( $label = $clause )" : "( $clause )";
700 return join (' ', $self->_gen_from_blocks(@_) );
703 sub _gen_from_blocks {
704 my ($self, $from, @joins) = @_;
706 my @fchunks = $self->_from_chunk_to_sql($from);
711 # check whether a join type exists
712 my $to_jt = ref($to) eq 'ARRAY' ? $to->[0] : $to;
714 if (ref($to_jt) eq 'HASH' and defined($to_jt->{-join_type})) {
715 $join_type = $to_jt->{-join_type};
716 $join_type =~ s/^\s+ | \s+$//xg;
719 my @j = $self->_generate_join_clause( $join_type );
721 if (ref $to eq 'ARRAY') {
722 push(@j, '(', $self->_recurse_from(@$to), ')');
725 push(@j, $self->_from_chunk_to_sql($to));
728 my ($sql, @bind) = $self->_join_condition($on);
729 push(@j, ' ON ', $sql);
730 push @{$self->{from_bind}}, @bind;
732 push @fchunks, join '', @j;
738 sub _from_chunk_to_sql {
739 my ($self, $fromspec) = @_;
741 return join (' ', $self->_SWITCH_refkind($fromspec, {
746 push @{$self->{from_bind}}, @{$$fromspec}[1..$#$$fromspec];
750 my ($as, $table, $toomuch) = ( map
751 { $_ => $fromspec->{$_} }
752 ( grep { $_ !~ /^\-/ } keys %$fromspec )
755 $self->throw_exception( "Only one table/as pair expected in from-spec but an exra '$toomuch' key present" )
758 ($self->_from_chunk_to_sql($table), $self->_quote($as) );
761 $self->_quote($fromspec);
766 sub _join_condition {
767 my ($self, $cond) = @_;
769 # Backcompat for the old days when a plain hashref
770 # { 't1.col1' => 't2.col2' } meant ON t1.col1 = t2.col2
771 # Once things settle we should start warning here so that
772 # folks unroll their hacks
778 (keys %$cond)[0] =~ /\./
780 ! ref ( (values %$cond)[0] )
782 $cond = { keys %$cond => { -ident => values %$cond } }
784 elsif ( ref $cond eq 'ARRAY' ) {
785 # do our own ORing so that the hashref-shim above is invoked
788 foreach my $c (@$cond) {
789 my ($sql, @bind) = $self->_join_condition($c);
793 return join(' OR ', @parts), @binds;
796 return $self->_recurse_where($cond);
805 Used to explicitly specify an SQL identifier. Takes a plain string as value
806 which is then invariably treated as a column name (and is being properly
807 quoted if quoting has been requested). Most useful for comparison of two
811 priority => { '<', 2 },
812 requestor => { -ident => 'submitter' }
817 $stmt = 'WHERE "priority" < ? AND "requestor" = "submitter"';
822 The -value operator signals that the argument to the right is a raw bind value.
823 It will be passed straight to DBI, without invoking any of the SQL::Abstract
824 condition-parsing logic. This allows you to, for example, pass an array as a
825 column value for databases that support array datatypes, e.g.:
828 array => { -value => [1, 2, 3] }
833 $stmt = 'WHERE array = ?';
838 See L<DBIx::Class/CONTRIBUTORS>.
842 You may distribute this code under the same terms as Perl itself.