1 package SQL::Abstract; # see doc at end of file
9 #======================================================================
11 #======================================================================
13 our $VERSION = '1.74';
15 # This would confuse some packagers
16 $VERSION = eval $VERSION if $VERSION =~ /_/; # numify for warning-free dev releases
20 # special operators (-in, -between). May be extended/overridden by user.
21 # See section WHERE: BUILTIN SPECIAL OPERATORS below for implementation
22 my @BUILTIN_SPECIAL_OPS = (
23 {regex => qr/^ (?: not \s )? between $/ix, handler => '_where_field_BETWEEN'},
24 {regex => qr/^ (?: not \s )? in $/ix, handler => '_where_field_IN'},
25 {regex => qr/^ ident $/ix, handler => '_where_op_IDENT'},
26 {regex => qr/^ value $/ix, handler => '_where_op_VALUE'},
29 # unaryish operators - key maps to handler
30 my @BUILTIN_UNARY_OPS = (
31 # the digits are backcompat stuff
32 { regex => qr/^ and (?: [_\s]? \d+ )? $/xi, handler => '_where_op_ANDOR' },
33 { regex => qr/^ or (?: [_\s]? \d+ )? $/xi, handler => '_where_op_ANDOR' },
34 { regex => qr/^ nest (?: [_\s]? \d+ )? $/xi, handler => '_where_op_NEST' },
35 { regex => qr/^ (?: not \s )? bool $/xi, handler => '_where_op_BOOL' },
36 { regex => qr/^ ident $/xi, handler => '_where_op_IDENT' },
37 { regex => qr/^ value $/ix, handler => '_where_op_VALUE' },
40 #======================================================================
41 # DEBUGGING AND ERROR REPORTING
42 #======================================================================
45 return unless $_[0]->{debug}; shift; # a little faster
46 my $func = (caller(1))[3];
47 warn "[$func] ", @_, "\n";
51 my($func) = (caller(1))[3];
52 Carp::carp "[$func] Warning: ", @_;
56 my($func) = (caller(1))[3];
57 Carp::croak "[$func] Fatal: ", @_;
61 #======================================================================
63 #======================================================================
67 my $class = ref($self) || $self;
68 my %opt = (ref $_[0] eq 'HASH') ? %{$_[0]} : @_;
70 # choose our case by keeping an option around
71 delete $opt{case} if $opt{case} && $opt{case} ne 'lower';
73 # default logic for interpreting arrayrefs
74 $opt{logic} = $opt{logic} ? uc $opt{logic} : 'OR';
76 # how to return bind vars
77 $opt{bindtype} ||= 'normal';
79 # default comparison is "=", but can be overridden
82 # try to recognize which are the 'equality' and 'inequality' ops
83 # (temporary quickfix, should go through a more seasoned API)
84 $opt{equality_op} = qr/^(\Q$opt{cmp}\E|is|(is\s+)?like)$/i;
85 $opt{inequality_op} = qr/^(!=|<>|(is\s+)?not(\s+like)?)$/i;
88 $opt{sqltrue} ||= '1=1';
89 $opt{sqlfalse} ||= '0=1';
92 $opt{special_ops} ||= [];
93 # regexes are applied in order, thus push after user-defines
94 push @{$opt{special_ops}}, @BUILTIN_SPECIAL_OPS;
97 $opt{unary_ops} ||= [];
98 push @{$opt{unary_ops}}, @BUILTIN_UNARY_OPS;
100 # rudimentary sanity-check for user supplied bits treated as functions/operators
101 # If a purported function matches this regular expression, an exception is thrown.
102 # Literal SQL is *NOT* subject to this check, only functions (and column names
103 # when quoting is not in effect)
106 # need to guard against ()'s in column names too, but this will break tons of
107 # hacks... ideas anyone?
108 $opt{injection_guard} ||= qr/
114 return bless \%opt, $class;
118 sub _assert_pass_injection_guard {
119 if ($_[1] =~ $_[0]->{injection_guard}) {
120 my $class = ref $_[0];
121 puke "Possible SQL injection attempt '$_[1]'. If this is indeed a part of the "
122 . "desired SQL use literal SQL ( \'...' or \[ '...' ] ) or supply your own "
123 . "{injection_guard} attribute to ${class}->new()"
128 #======================================================================
130 #======================================================================
134 my $table = $self->_table(shift);
135 my $data = shift || return;
138 my $method = $self->_METHOD_FOR_refkind("_insert", $data);
139 my ($sql, @bind) = $self->$method($data);
140 $sql = join " ", $self->_sqlcase('insert into'), $table, $sql;
142 if ($options->{returning}) {
143 my ($s, @b) = $self->_insert_returning ($options);
148 return wantarray ? ($sql, @bind) : $sql;
151 sub _insert_returning {
152 my ($self, $options) = @_;
154 my $f = $options->{returning};
156 my $fieldlist = $self->_SWITCH_refkind($f, {
157 ARRAYREF => sub {join ', ', map { $self->_quote($_) } @$f;},
158 SCALAR => sub {$self->_quote($f)},
159 SCALARREF => sub {$$f},
161 return $self->_sqlcase(' returning ') . $fieldlist;
164 sub _insert_HASHREF { # explicit list of fields and then values
165 my ($self, $data) = @_;
167 my @fields = sort keys %$data;
169 my ($sql, @bind) = $self->_insert_values($data);
172 $_ = $self->_quote($_) foreach @fields;
173 $sql = "( ".join(", ", @fields).") ".$sql;
175 return ($sql, @bind);
178 sub _insert_ARRAYREF { # just generate values(?,?) part (no list of fields)
179 my ($self, $data) = @_;
181 # no names (arrayref) so can't generate bindtype
182 $self->{bindtype} ne 'columns'
183 or belch "can't do 'columns' bindtype when called with arrayref";
185 # fold the list of values into a hash of column name - value pairs
186 # (where the column names are artificially generated, and their
187 # lexicographical ordering keep the ordering of the original list)
188 my $i = "a"; # incremented values will be in lexicographical order
189 my $data_in_hash = { map { ($i++ => $_) } @$data };
191 return $self->_insert_values($data_in_hash);
194 sub _insert_ARRAYREFREF { # literal SQL with bind
195 my ($self, $data) = @_;
197 my ($sql, @bind) = @${$data};
198 $self->_assert_bindval_matches_bindtype(@bind);
200 return ($sql, @bind);
204 sub _insert_SCALARREF { # literal SQL without bind
205 my ($self, $data) = @_;
211 my ($self, $data) = @_;
213 my (@values, @all_bind);
214 foreach my $column (sort keys %$data) {
215 my $v = $data->{$column};
217 $self->_SWITCH_refkind($v, {
220 if ($self->{array_datatypes}) { # if array datatype are activated
222 push @all_bind, $self->_bindtype($column, $v);
224 else { # else literal SQL with bind
225 my ($sql, @bind) = @$v;
226 $self->_assert_bindval_matches_bindtype(@bind);
228 push @all_bind, @bind;
232 ARRAYREFREF => sub { # literal SQL with bind
233 my ($sql, @bind) = @${$v};
234 $self->_assert_bindval_matches_bindtype(@bind);
236 push @all_bind, @bind;
239 # THINK : anything useful to do with a HASHREF ?
240 HASHREF => sub { # (nothing, but old SQLA passed it through)
241 #TODO in SQLA >= 2.0 it will die instead
242 belch "HASH ref as bind value in insert is not supported";
244 push @all_bind, $self->_bindtype($column, $v);
247 SCALARREF => sub { # literal SQL without bind
251 SCALAR_or_UNDEF => sub {
253 push @all_bind, $self->_bindtype($column, $v);
260 my $sql = $self->_sqlcase('values')." ( ".join(", ", @values)." )";
261 return ($sql, @all_bind);
266 #======================================================================
268 #======================================================================
273 my $table = $self->_table(shift);
274 my $data = shift || return;
277 # first build the 'SET' part of the sql statement
278 my (@set, @all_bind);
279 puke "Unsupported data type specified to \$sql->update"
280 unless ref $data eq 'HASH';
282 for my $k (sort keys %$data) {
285 my $label = $self->_quote($k);
287 $self->_SWITCH_refkind($v, {
289 if ($self->{array_datatypes}) { # array datatype
290 push @set, "$label = ?";
291 push @all_bind, $self->_bindtype($k, $v);
293 else { # literal SQL with bind
294 my ($sql, @bind) = @$v;
295 $self->_assert_bindval_matches_bindtype(@bind);
296 push @set, "$label = $sql";
297 push @all_bind, @bind;
300 ARRAYREFREF => sub { # literal SQL with bind
301 my ($sql, @bind) = @${$v};
302 $self->_assert_bindval_matches_bindtype(@bind);
303 push @set, "$label = $sql";
304 push @all_bind, @bind;
306 SCALARREF => sub { # literal SQL without bind
307 push @set, "$label = $$v";
310 my ($op, $arg, @rest) = %$v;
312 puke 'Operator calls in update must be in the form { -op => $arg }'
313 if (@rest or not $op =~ /^\-(.+)/);
315 local $self->{_nested_func_lhs} = $k;
316 my ($sql, @bind) = $self->_where_unary_op ($1, $arg);
318 push @set, "$label = $sql";
319 push @all_bind, @bind;
321 SCALAR_or_UNDEF => sub {
322 push @set, "$label = ?";
323 push @all_bind, $self->_bindtype($k, $v);
329 my $sql = $self->_sqlcase('update') . " $table " . $self->_sqlcase('set ')
333 my($where_sql, @where_bind) = $self->where($where);
335 push @all_bind, @where_bind;
338 return wantarray ? ($sql, @all_bind) : $sql;
344 #======================================================================
346 #======================================================================
351 my $table = $self->_table(shift);
352 my $fields = shift || '*';
356 my($where_sql, @bind) = $self->where($where, $order);
358 my $f = (ref $fields eq 'ARRAY') ? join ', ', map { $self->_quote($_) } @$fields
360 my $sql = join(' ', $self->_sqlcase('select'), $f,
361 $self->_sqlcase('from'), $table)
364 return wantarray ? ($sql, @bind) : $sql;
367 #======================================================================
369 #======================================================================
374 my $table = $self->_table(shift);
378 my($where_sql, @bind) = $self->where($where);
379 my $sql = $self->_sqlcase('delete from') . " $table" . $where_sql;
381 return wantarray ? ($sql, @bind) : $sql;
385 #======================================================================
387 #======================================================================
391 # Finally, a separate routine just to handle WHERE clauses
393 my ($self, $where, $order) = @_;
396 my ($sql, @bind) = $self->_recurse_where($where);
397 $sql = $sql ? $self->_sqlcase(' where ') . "( $sql )" : '';
401 $sql .= $self->_order_by($order);
404 return wantarray ? ($sql, @bind) : $sql;
409 my ($self, $where, $logic) = @_;
411 # dispatch on appropriate method according to refkind of $where
412 my $method = $self->_METHOD_FOR_refkind("_where", $where);
414 my ($sql, @bind) = $self->$method($where, $logic);
416 # DBIx::Class directly calls _recurse_where in scalar context, so
417 # we must implement it, even if not in the official API
418 return wantarray ? ($sql, @bind) : $sql;
423 #======================================================================
424 # WHERE: top-level ARRAYREF
425 #======================================================================
428 sub _where_ARRAYREF {
429 my ($self, $where, $logic) = @_;
431 $logic = uc($logic || $self->{logic});
432 $logic eq 'AND' or $logic eq 'OR' or puke "unknown logic: $logic";
434 my @clauses = @$where;
436 my (@sql_clauses, @all_bind);
437 # need to use while() so can shift() for pairs
438 while (my $el = shift @clauses) {
440 # switch according to kind of $el and get corresponding ($sql, @bind)
441 my ($sql, @bind) = $self->_SWITCH_refkind($el, {
443 # skip empty elements, otherwise get invalid trailing AND stuff
444 ARRAYREF => sub {$self->_recurse_where($el) if @$el},
448 $self->_assert_bindval_matches_bindtype(@b);
452 HASHREF => sub {$self->_recurse_where($el, 'and') if %$el},
454 SCALARREF => sub { ($$el); },
456 SCALAR => sub {# top-level arrayref with scalars, recurse in pairs
457 $self->_recurse_where({$el => shift(@clauses)})},
459 UNDEF => sub {puke "not supported : UNDEF in arrayref" },
463 push @sql_clauses, $sql;
464 push @all_bind, @bind;
468 return $self->_join_sql_clauses($logic, \@sql_clauses, \@all_bind);
471 #======================================================================
472 # WHERE: top-level ARRAYREFREF
473 #======================================================================
475 sub _where_ARRAYREFREF {
476 my ($self, $where) = @_;
477 my ($sql, @bind) = @$$where;
478 $self->_assert_bindval_matches_bindtype(@bind);
479 return ($sql, @bind);
482 #======================================================================
483 # WHERE: top-level HASHREF
484 #======================================================================
487 my ($self, $where) = @_;
488 my (@sql_clauses, @all_bind);
490 for my $k (sort keys %$where) {
491 my $v = $where->{$k};
493 # ($k => $v) is either a special unary op or a regular hashpair
494 my ($sql, @bind) = do {
496 # put the operator in canonical form
498 $op = substr $op, 1; # remove initial dash
499 $op =~ s/^\s+|\s+$//g;# remove leading/trailing space
500 $op =~ s/\s+/ /g; # compress whitespace
502 # so that -not_foo works correctly
503 $op =~ s/^not_/NOT /i;
505 $self->_debug("Unary OP(-$op) within hashref, recursing...");
506 my ($s, @b) = $self->_where_unary_op ($op, $v);
508 # top level vs nested
509 # we assume that handled unary ops will take care of their ()s
511 List::Util::first {$op =~ $_->{regex}} @{$self->{unary_ops}}
513 defined($self->{_nested_func_lhs}) && ($self->{_nested_func_lhs} eq $k)
518 my $method = $self->_METHOD_FOR_refkind("_where_hashpair", $v);
519 $self->$method($k, $v);
523 push @sql_clauses, $sql;
524 push @all_bind, @bind;
527 return $self->_join_sql_clauses('and', \@sql_clauses, \@all_bind);
530 sub _where_unary_op {
531 my ($self, $op, $rhs) = @_;
533 if (my $op_entry = List::Util::first {$op =~ $_->{regex}} @{$self->{unary_ops}}) {
534 my $handler = $op_entry->{handler};
536 if (not ref $handler) {
537 if ($op =~ s/ [_\s]? \d+ $//x ) {
538 belch 'Use of [and|or|nest]_N modifiers is deprecated and will be removed in SQLA v2.0. '
539 . "You probably wanted ...-and => [ -$op => COND1, -$op => COND2 ... ]";
541 return $self->$handler ($op, $rhs);
543 elsif (ref $handler eq 'CODE') {
544 return $handler->($self, $op, $rhs);
547 puke "Illegal handler for operator $op - expecting a method name or a coderef";
551 $self->_debug("Generic unary OP: $op - recursing as function");
553 $self->_assert_pass_injection_guard($op);
555 my ($sql, @bind) = $self->_SWITCH_refkind ($rhs, {
557 puke "Illegal use of top-level '$op'"
558 unless $self->{_nested_func_lhs};
561 $self->_convert('?'),
562 $self->_bindtype($self->{_nested_func_lhs}, $rhs)
566 $self->_recurse_where ($rhs)
570 $sql = sprintf ('%s %s',
571 $self->_sqlcase($op),
575 return ($sql, @bind);
578 sub _where_op_ANDOR {
579 my ($self, $op, $v) = @_;
581 $self->_SWITCH_refkind($v, {
583 return $self->_where_ARRAYREF($v, $op);
587 return ( $op =~ /^or/i )
588 ? $self->_where_ARRAYREF( [ map { $_ => $v->{$_} } ( sort keys %$v ) ], $op )
589 : $self->_where_HASHREF($v);
593 puke "-$op => \\\$scalar makes little sense, use " .
595 ? '[ \$scalar, \%rest_of_conditions ] instead'
596 : '-and => [ \$scalar, \%rest_of_conditions ] instead'
601 puke "-$op => \\[...] makes little sense, use " .
603 ? '[ \[...], \%rest_of_conditions ] instead'
604 : '-and => [ \[...], \%rest_of_conditions ] instead'
608 SCALAR => sub { # permissively interpreted as SQL
609 puke "-$op => \$value makes little sense, use -bool => \$value instead";
613 puke "-$op => undef not supported";
619 my ($self, $op, $v) = @_;
621 $self->_SWITCH_refkind($v, {
623 SCALAR => sub { # permissively interpreted as SQL
624 belch "literal SQL should be -nest => \\'scalar' "
625 . "instead of -nest => 'scalar' ";
630 puke "-$op => undef not supported";
634 $self->_recurse_where ($v);
642 my ($self, $op, $v) = @_;
644 my ($s, @b) = $self->_SWITCH_refkind($v, {
645 SCALAR => sub { # interpreted as SQL column
646 $self->_convert($self->_quote($v));
650 puke "-$op => undef not supported";
654 $self->_recurse_where ($v);
658 $s = "(NOT $s)" if $op =~ /^not/i;
663 sub _where_op_IDENT {
665 my ($op, $rhs) = splice @_, -2;
667 puke "-$op takes a single scalar argument (a quotable identifier)";
670 # in case we are called as a top level special op (no '=')
673 $_ = $self->_convert($self->_quote($_)) for ($lhs, $rhs);
681 sub _where_op_VALUE {
683 my ($op, $rhs) = splice @_, -2;
685 # in case we are called as a top level special op (no '=')
690 ($lhs || $self->{_nested_func_lhs}),
697 $self->_convert($self->_quote($lhs)) . ' = ' . $self->_convert('?'),
701 $self->_convert('?'),
707 sub _where_hashpair_ARRAYREF {
708 my ($self, $k, $v) = @_;
711 my @v = @$v; # need copy because of shift below
712 $self->_debug("ARRAY($k) means distribute over elements");
714 # put apart first element if it is an operator (-and, -or)
716 (defined $v[0] && $v[0] =~ /^ - (?: AND|OR ) $/ix)
720 my @distributed = map { {$k => $_} } @v;
723 $self->_debug("OP($op) reinjected into the distributed array");
724 unshift @distributed, $op;
727 my $logic = $op ? substr($op, 1) : '';
729 return $self->_recurse_where(\@distributed, $logic);
732 $self->_debug("empty ARRAY($k) means 0=1");
733 return ($self->{sqlfalse});
737 sub _where_hashpair_HASHREF {
738 my ($self, $k, $v, $logic) = @_;
741 local $self->{_nested_func_lhs} = $self->{_nested_func_lhs};
743 my ($all_sql, @all_bind);
745 for my $orig_op (sort keys %$v) {
746 my $val = $v->{$orig_op};
748 # put the operator in canonical form
751 # FIXME - we need to phase out dash-less ops
752 $op =~ s/^-//; # remove possible initial dash
753 $op =~ s/^\s+|\s+$//g;# remove leading/trailing space
754 $op =~ s/\s+/ /g; # compress whitespace
756 $self->_assert_pass_injection_guard($op);
758 # so that -not_foo works correctly
759 $op =~ s/^not_/NOT /i;
763 # CASE: col-value logic modifiers
764 if ( $orig_op =~ /^ \- (and|or) $/xi ) {
765 ($sql, @bind) = $self->_where_hashpair_HASHREF($k, $val, $1);
767 # CASE: special operators like -in or -between
768 elsif ( my $special_op = List::Util::first {$op =~ $_->{regex}} @{$self->{special_ops}} ) {
769 my $handler = $special_op->{handler};
771 puke "No handler supplied for special operator $orig_op";
773 elsif (not ref $handler) {
774 ($sql, @bind) = $self->$handler ($k, $op, $val);
776 elsif (ref $handler eq 'CODE') {
777 ($sql, @bind) = $handler->($self, $k, $op, $val);
780 puke "Illegal handler for special operator $orig_op - expecting a method name or a coderef";
784 $self->_SWITCH_refkind($val, {
786 ARRAYREF => sub { # CASE: col => {op => \@vals}
787 ($sql, @bind) = $self->_where_field_op_ARRAYREF($k, $op, $val);
790 ARRAYREFREF => sub { # CASE: col => {op => \[$sql, @bind]} (literal SQL with bind)
791 my ($sub_sql, @sub_bind) = @$$val;
792 $self->_assert_bindval_matches_bindtype(@sub_bind);
793 $sql = join ' ', $self->_convert($self->_quote($k)),
794 $self->_sqlcase($op),
799 UNDEF => sub { # CASE: col => {op => undef} : sql "IS (NOT)? NULL"
800 my $is = ($op =~ $self->{equality_op}) ? 'is' :
801 ($op =~ $self->{inequality_op}) ? 'is not' :
802 puke "unexpected operator '$orig_op' with undef operand";
803 $sql = $self->_quote($k) . $self->_sqlcase(" $is null");
806 FALLBACK => sub { # CASE: col => {op/func => $stuff}
808 # retain for proper column type bind
809 $self->{_nested_func_lhs} ||= $k;
811 ($sql, @bind) = $self->_where_unary_op ($op, $val);
814 $self->_convert($self->_quote($k)),
815 $self->{_nested_func_lhs} eq $k ? $sql : "($sql)", # top level vs nested
821 ($all_sql) = (defined $all_sql and $all_sql) ? $self->_join_sql_clauses($logic, [$all_sql, $sql], []) : $sql;
822 push @all_bind, @bind;
824 return ($all_sql, @all_bind);
829 sub _where_field_op_ARRAYREF {
830 my ($self, $k, $op, $vals) = @_;
832 my @vals = @$vals; #always work on a copy
835 $self->_debug(sprintf '%s means multiple elements: [ %s ]',
837 join (', ', map { defined $_ ? "'$_'" : 'NULL' } @vals ),
840 # see if the first element is an -and/-or op
842 if (defined $vals[0] && $vals[0] =~ /^ - ( AND|OR ) $/ix) {
847 # distribute $op over each remaining member of @vals, append logic if exists
848 return $self->_recurse_where([map { {$k => {$op, $_}} } @vals], $logic);
850 # LDNOTE : had planned to change the distribution logic when
851 # $op =~ $self->{inequality_op}, because of Morgan laws :
852 # with {field => {'!=' => [22, 33]}}, it would be ridiculous to generate
853 # WHERE field != 22 OR field != 33 : the user probably means
854 # WHERE field != 22 AND field != 33.
855 # To do this, replace the above to roughly :
856 # my $logic = ($op =~ $self->{inequality_op}) ? 'AND' : 'OR';
857 # return $self->_recurse_where([map { {$k => {$op, $_}} } @vals], $logic);
861 # try to DWIM on equality operators
862 # LDNOTE : not 100% sure this is the correct thing to do ...
863 return ($self->{sqlfalse}) if $op =~ $self->{equality_op};
864 return ($self->{sqltrue}) if $op =~ $self->{inequality_op};
867 puke "operator '$op' applied on an empty array (field '$k')";
872 sub _where_hashpair_SCALARREF {
873 my ($self, $k, $v) = @_;
874 $self->_debug("SCALAR($k) means literal SQL: $$v");
875 my $sql = $self->_quote($k) . " " . $$v;
879 # literal SQL with bind
880 sub _where_hashpair_ARRAYREFREF {
881 my ($self, $k, $v) = @_;
882 $self->_debug("REF($k) means literal SQL: @${$v}");
883 my ($sql, @bind) = @$$v;
884 $self->_assert_bindval_matches_bindtype(@bind);
885 $sql = $self->_quote($k) . " " . $sql;
886 return ($sql, @bind );
889 # literal SQL without bind
890 sub _where_hashpair_SCALAR {
891 my ($self, $k, $v) = @_;
892 $self->_debug("NOREF($k) means simple key=val: $k $self->{cmp} $v");
893 my $sql = join ' ', $self->_convert($self->_quote($k)),
894 $self->_sqlcase($self->{cmp}),
895 $self->_convert('?');
896 my @bind = $self->_bindtype($k, $v);
897 return ( $sql, @bind);
901 sub _where_hashpair_UNDEF {
902 my ($self, $k, $v) = @_;
903 $self->_debug("UNDEF($k) means IS NULL");
904 my $sql = $self->_quote($k) . $self->_sqlcase(' is null');
908 #======================================================================
909 # WHERE: TOP-LEVEL OTHERS (SCALARREF, SCALAR, UNDEF)
910 #======================================================================
913 sub _where_SCALARREF {
914 my ($self, $where) = @_;
917 $self->_debug("SCALAR(*top) means literal SQL: $$where");
923 my ($self, $where) = @_;
926 $self->_debug("NOREF(*top) means literal SQL: $where");
937 #======================================================================
938 # WHERE: BUILTIN SPECIAL OPERATORS (-in, -between)
939 #======================================================================
942 sub _where_field_BETWEEN {
943 my ($self, $k, $op, $vals) = @_;
945 my ($label, $and, $placeholder);
946 $label = $self->_convert($self->_quote($k));
947 $and = ' ' . $self->_sqlcase('and') . ' ';
948 $placeholder = $self->_convert('?');
949 $op = $self->_sqlcase($op);
951 my $invalid_args = "Operator '$op' requires either an arrayref with two defined values or expressions, or a single literal scalarref/arrayref-ref";
953 my ($clause, @bind) = $self->_SWITCH_refkind($vals, {
955 my ($s, @b) = @$$vals;
956 $self->_assert_bindval_matches_bindtype(@b);
963 puke $invalid_args if @$vals != 2;
965 my (@all_sql, @all_bind);
966 foreach my $val (@$vals) {
967 my ($sql, @bind) = $self->_SWITCH_refkind($val, {
969 return ($placeholder, $self->_bindtype($k, $val) );
975 my ($sql, @bind) = @$$val;
976 $self->_assert_bindval_matches_bindtype(@bind);
977 return ($sql, @bind);
980 my ($func, $arg, @rest) = %$val;
981 puke ("Only simple { -func => arg } functions accepted as sub-arguments to BETWEEN")
982 if (@rest or $func !~ /^ \- (.+)/x);
983 local $self->{_nested_func_lhs} = $k;
984 $self->_where_unary_op ($1 => $arg);
991 push @all_bind, @bind;
995 (join $and, @all_sql),
1004 my $sql = "( $label $op $clause )";
1005 return ($sql, @bind)
1009 sub _where_field_IN {
1010 my ($self, $k, $op, $vals) = @_;
1012 # backwards compatibility : if scalar, force into an arrayref
1013 $vals = [$vals] if defined $vals && ! ref $vals;
1015 my ($label) = $self->_convert($self->_quote($k));
1016 my ($placeholder) = $self->_convert('?');
1017 $op = $self->_sqlcase($op);
1019 my ($sql, @bind) = $self->_SWITCH_refkind($vals, {
1020 ARRAYREF => sub { # list of choices
1021 if (@$vals) { # nonempty list
1022 my (@all_sql, @all_bind);
1024 for my $val (@$vals) {
1025 my ($sql, @bind) = $self->_SWITCH_refkind($val, {
1027 return ($placeholder, $val);
1032 ARRAYREFREF => sub {
1033 my ($sql, @bind) = @$$val;
1034 $self->_assert_bindval_matches_bindtype(@bind);
1035 return ($sql, @bind);
1038 my ($func, $arg, @rest) = %$val;
1039 puke ("Only simple { -func => arg } functions accepted as sub-arguments to IN")
1040 if (@rest or $func !~ /^ \- (.+)/x);
1041 local $self->{_nested_func_lhs} = $k;
1042 $self->_where_unary_op ($1 => $arg);
1046 'SQL::Abstract before v1.75 used to generate incorrect SQL when the '
1047 . "-$op operator was given an undef-containing list: !!!AUDIT YOUR CODE "
1048 . 'AND DATA!!! (the upcoming Data::Query-based version of SQL::Abstract '
1049 . 'will emit the logically correct SQL instead of raising this exception)'
1053 push @all_sql, $sql;
1054 push @all_bind, @bind;
1058 sprintf ('%s %s ( %s )',
1061 join (', ', @all_sql)
1063 $self->_bindtype($k, @all_bind),
1066 else { # empty list : some databases won't understand "IN ()", so DWIM
1067 my $sql = ($op =~ /\bnot\b/i) ? $self->{sqltrue} : $self->{sqlfalse};
1072 SCALARREF => sub { # literal SQL
1073 my $sql = $self->_open_outer_paren ($$vals);
1074 return ("$label $op ( $sql )");
1076 ARRAYREFREF => sub { # literal SQL with bind
1077 my ($sql, @bind) = @$$vals;
1078 $self->_assert_bindval_matches_bindtype(@bind);
1079 $sql = $self->_open_outer_paren ($sql);
1080 return ("$label $op ( $sql )", @bind);
1084 puke "Argument passed to the '$op' operator can not be undefined";
1088 puke "special op $op requires an arrayref (or scalarref/arrayref-ref)";
1092 return ($sql, @bind);
1095 # Some databases (SQLite) treat col IN (1, 2) different from
1096 # col IN ( (1, 2) ). Use this to strip all outer parens while
1097 # adding them back in the corresponding method
1098 sub _open_outer_paren {
1099 my ($self, $sql) = @_;
1100 $sql = $1 while $sql =~ /^ \s* \( (.*) \) \s* $/xs;
1105 #======================================================================
1107 #======================================================================
1110 my ($self, $arg) = @_;
1113 for my $c ($self->_order_by_chunks ($arg) ) {
1114 $self->_SWITCH_refkind ($c, {
1115 SCALAR => sub { push @sql, $c },
1116 ARRAYREF => sub { push @sql, shift @$c; push @bind, @$c },
1122 $self->_sqlcase(' order by'),
1128 return wantarray ? ($sql, @bind) : $sql;
1131 sub _order_by_chunks {
1132 my ($self, $arg) = @_;
1134 return $self->_SWITCH_refkind($arg, {
1137 map { $self->_order_by_chunks ($_ ) } @$arg;
1140 ARRAYREFREF => sub {
1141 my ($s, @b) = @$$arg;
1142 $self->_assert_bindval_matches_bindtype(@b);
1146 SCALAR => sub {$self->_quote($arg)},
1148 UNDEF => sub {return () },
1150 SCALARREF => sub {$$arg}, # literal SQL, no quoting
1153 # get first pair in hash
1154 my ($key, $val, @rest) = %$arg;
1156 return () unless $key;
1158 if ( @rest or not $key =~ /^-(desc|asc)/i ) {
1159 puke "hash passed to _order_by must have exactly one key (-desc or -asc)";
1165 for my $c ($self->_order_by_chunks ($val)) {
1168 $self->_SWITCH_refkind ($c, {
1173 ($sql, @bind) = @$c;
1177 $sql = $sql . ' ' . $self->_sqlcase($direction);
1179 push @ret, [ $sql, @bind];
1188 #======================================================================
1189 # DATASOURCE (FOR NOW, JUST PLAIN TABLE OR LIST OF TABLES)
1190 #======================================================================
1195 $self->_SWITCH_refkind($from, {
1196 ARRAYREF => sub {join ', ', map { $self->_quote($_) } @$from;},
1197 SCALAR => sub {$self->_quote($from)},
1198 SCALARREF => sub {$$from},
1203 #======================================================================
1205 #======================================================================
1207 # highly optimized, as it's called way too often
1209 # my ($self, $label) = @_;
1211 return '' unless defined $_[1];
1212 return ${$_[1]} if ref($_[1]) eq 'SCALAR';
1214 unless ($_[0]->{quote_char}) {
1215 $_[0]->_assert_pass_injection_guard($_[1]);
1219 my $qref = ref $_[0]->{quote_char};
1222 ($l, $r) = ( $_[0]->{quote_char}, $_[0]->{quote_char} );
1224 elsif ($qref eq 'ARRAY') {
1225 ($l, $r) = @{$_[0]->{quote_char}};
1228 puke "Unsupported quote_char format: $_[0]->{quote_char}";
1231 # parts containing * are naturally unquoted
1232 return join( $_[0]->{name_sep}||'', map
1233 { $_ eq '*' ? $_ : $l . $_ . $r }
1234 ( $_[0]->{name_sep} ? split (/\Q$_[0]->{name_sep}\E/, $_[1] ) : $_[1] )
1239 # Conversion, if applicable
1241 #my ($self, $arg) = @_;
1242 if ($_[0]->{convert}) {
1243 return $_[0]->_sqlcase($_[0]->{convert}) .'(' . $_[1] . ')';
1250 #my ($self, $col, @vals) = @_;
1251 # called often - tighten code
1252 return $_[0]->{bindtype} eq 'columns'
1253 ? map {[$_[1], $_]} @_[2 .. $#_]
1258 # Dies if any element of @bind is not in [colname => value] format
1259 # if bindtype is 'columns'.
1260 sub _assert_bindval_matches_bindtype {
1261 # my ($self, @bind) = @_;
1263 if ($self->{bindtype} eq 'columns') {
1265 if (!defined $_ || ref($_) ne 'ARRAY' || @$_ != 2) {
1266 puke "bindtype 'columns' selected, you need to pass: [column_name => bind_value]"
1272 sub _join_sql_clauses {
1273 my ($self, $logic, $clauses_aref, $bind_aref) = @_;
1275 if (@$clauses_aref > 1) {
1276 my $join = " " . $self->_sqlcase($logic) . " ";
1277 my $sql = '( ' . join($join, @$clauses_aref) . ' )';
1278 return ($sql, @$bind_aref);
1280 elsif (@$clauses_aref) {
1281 return ($clauses_aref->[0], @$bind_aref); # no parentheses
1284 return (); # if no SQL, ignore @$bind_aref
1289 # Fix SQL case, if so requested
1291 # LDNOTE: if $self->{case} is true, then it contains 'lower', so we
1292 # don't touch the argument ... crooked logic, but let's not change it!
1293 return $_[0]->{case} ? $_[1] : uc($_[1]);
1297 #======================================================================
1298 # DISPATCHING FROM REFKIND
1299 #======================================================================
1302 my ($self, $data) = @_;
1304 return 'UNDEF' unless defined $data;
1306 # blessed objects are treated like scalars
1307 my $ref = (Scalar::Util::blessed $data) ? '' : ref $data;
1309 return 'SCALAR' unless $ref;
1312 while ($ref eq 'REF') {
1314 $ref = (Scalar::Util::blessed $data) ? '' : ref $data;
1318 return ($ref||'SCALAR') . ('REF' x $n_steps);
1322 my ($self, $data) = @_;
1323 my @try = ($self->_refkind($data));
1324 push @try, 'SCALAR_or_UNDEF' if $try[0] eq 'SCALAR' || $try[0] eq 'UNDEF';
1325 push @try, 'FALLBACK';
1329 sub _METHOD_FOR_refkind {
1330 my ($self, $meth_prefix, $data) = @_;
1333 for (@{$self->_try_refkind($data)}) {
1334 $method = $self->can($meth_prefix."_".$_)
1338 return $method || puke "cannot dispatch on '$meth_prefix' for ".$self->_refkind($data);
1342 sub _SWITCH_refkind {
1343 my ($self, $data, $dispatch_table) = @_;
1346 for (@{$self->_try_refkind($data)}) {
1347 $coderef = $dispatch_table->{$_}
1351 puke "no dispatch entry for ".$self->_refkind($data)
1360 #======================================================================
1361 # VALUES, GENERATE, AUTOLOAD
1362 #======================================================================
1364 # LDNOTE: original code from nwiger, didn't touch code in that section
1365 # I feel the AUTOLOAD stuff should not be the default, it should
1366 # only be activated on explicit demand by user.
1370 my $data = shift || return;
1371 puke "Argument to ", __PACKAGE__, "->values must be a \\%hash"
1372 unless ref $data eq 'HASH';
1375 foreach my $k ( sort keys %$data ) {
1376 my $v = $data->{$k};
1377 $self->_SWITCH_refkind($v, {
1379 if ($self->{array_datatypes}) { # array datatype
1380 push @all_bind, $self->_bindtype($k, $v);
1382 else { # literal SQL with bind
1383 my ($sql, @bind) = @$v;
1384 $self->_assert_bindval_matches_bindtype(@bind);
1385 push @all_bind, @bind;
1388 ARRAYREFREF => sub { # literal SQL with bind
1389 my ($sql, @bind) = @${$v};
1390 $self->_assert_bindval_matches_bindtype(@bind);
1391 push @all_bind, @bind;
1393 SCALARREF => sub { # literal SQL without bind
1395 SCALAR_or_UNDEF => sub {
1396 push @all_bind, $self->_bindtype($k, $v);
1407 my(@sql, @sqlq, @sqlv);
1411 if ($ref eq 'HASH') {
1412 for my $k (sort keys %$_) {
1415 my $label = $self->_quote($k);
1416 if ($r eq 'ARRAY') {
1417 # literal SQL with bind
1418 my ($sql, @bind) = @$v;
1419 $self->_assert_bindval_matches_bindtype(@bind);
1420 push @sqlq, "$label = $sql";
1422 } elsif ($r eq 'SCALAR') {
1423 # literal SQL without bind
1424 push @sqlq, "$label = $$v";
1426 push @sqlq, "$label = ?";
1427 push @sqlv, $self->_bindtype($k, $v);
1430 push @sql, $self->_sqlcase('set'), join ', ', @sqlq;
1431 } elsif ($ref eq 'ARRAY') {
1432 # unlike insert(), assume these are ONLY the column names, i.e. for SQL
1435 if ($r eq 'ARRAY') { # literal SQL with bind
1436 my ($sql, @bind) = @$v;
1437 $self->_assert_bindval_matches_bindtype(@bind);
1440 } elsif ($r eq 'SCALAR') { # literal SQL without bind
1441 # embedded literal SQL
1448 push @sql, '(' . join(', ', @sqlq) . ')';
1449 } elsif ($ref eq 'SCALAR') {
1453 # strings get case twiddled
1454 push @sql, $self->_sqlcase($_);
1458 my $sql = join ' ', @sql;
1460 # this is pretty tricky
1461 # if ask for an array, return ($stmt, @bind)
1462 # otherwise, s/?/shift @sqlv/ to put it inline
1464 return ($sql, @sqlv);
1466 1 while $sql =~ s/\?/my $d = shift(@sqlv);
1467 ref $d ? $d->[1] : $d/e;
1476 # This allows us to check for a local, then _form, attr
1478 my($name) = $AUTOLOAD =~ /.*::(.+)/;
1479 return $self->generate($name, @_);
1490 SQL::Abstract - Generate SQL from Perl data structures
1496 my $sql = SQL::Abstract->new;
1498 my($stmt, @bind) = $sql->select($source, \@fields, \%where, \@order);
1500 my($stmt, @bind) = $sql->insert($table, \%fieldvals || \@values);
1502 my($stmt, @bind) = $sql->update($table, \%fieldvals, \%where);
1504 my($stmt, @bind) = $sql->delete($table, \%where);
1506 # Then, use these in your DBI statements
1507 my $sth = $dbh->prepare($stmt);
1508 $sth->execute(@bind);
1510 # Just generate the WHERE clause
1511 my($stmt, @bind) = $sql->where(\%where, \@order);
1513 # Return values in the same order, for hashed queries
1514 # See PERFORMANCE section for more details
1515 my @bind = $sql->values(\%fieldvals);
1519 This module was inspired by the excellent L<DBIx::Abstract>.
1520 However, in using that module I found that what I really wanted
1521 to do was generate SQL, but still retain complete control over my
1522 statement handles and use the DBI interface. So, I set out to
1523 create an abstract SQL generation module.
1525 While based on the concepts used by L<DBIx::Abstract>, there are
1526 several important differences, especially when it comes to WHERE
1527 clauses. I have modified the concepts used to make the SQL easier
1528 to generate from Perl data structures and, IMO, more intuitive.
1529 The underlying idea is for this module to do what you mean, based
1530 on the data structures you provide it. The big advantage is that
1531 you don't have to modify your code every time your data changes,
1532 as this module figures it out.
1534 To begin with, an SQL INSERT is as easy as just specifying a hash
1535 of C<key=value> pairs:
1538 name => 'Jimbo Bobson',
1539 phone => '123-456-7890',
1540 address => '42 Sister Lane',
1541 city => 'St. Louis',
1542 state => 'Louisiana',
1545 The SQL can then be generated with this:
1547 my($stmt, @bind) = $sql->insert('people', \%data);
1549 Which would give you something like this:
1551 $stmt = "INSERT INTO people
1552 (address, city, name, phone, state)
1553 VALUES (?, ?, ?, ?, ?)";
1554 @bind = ('42 Sister Lane', 'St. Louis', 'Jimbo Bobson',
1555 '123-456-7890', 'Louisiana');
1557 These are then used directly in your DBI code:
1559 my $sth = $dbh->prepare($stmt);
1560 $sth->execute(@bind);
1562 =head2 Inserting and Updating Arrays
1564 If your database has array types (like for example Postgres),
1565 activate the special option C<< array_datatypes => 1 >>
1566 when creating the C<SQL::Abstract> object.
1567 Then you may use an arrayref to insert and update database array types:
1569 my $sql = SQL::Abstract->new(array_datatypes => 1);
1571 planets => [qw/Mercury Venus Earth Mars/]
1574 my($stmt, @bind) = $sql->insert('solar_system', \%data);
1578 $stmt = "INSERT INTO solar_system (planets) VALUES (?)"
1580 @bind = (['Mercury', 'Venus', 'Earth', 'Mars']);
1583 =head2 Inserting and Updating SQL
1585 In order to apply SQL functions to elements of your C<%data> you may
1586 specify a reference to an arrayref for the given hash value. For example,
1587 if you need to execute the Oracle C<to_date> function on a value, you can
1588 say something like this:
1592 date_entered => \["to_date(?,'MM/DD/YYYY')", "03/02/2003"],
1595 The first value in the array is the actual SQL. Any other values are
1596 optional and would be included in the bind values array. This gives
1599 my($stmt, @bind) = $sql->insert('people', \%data);
1601 $stmt = "INSERT INTO people (name, date_entered)
1602 VALUES (?, to_date(?,'MM/DD/YYYY'))";
1603 @bind = ('Bill', '03/02/2003');
1605 An UPDATE is just as easy, all you change is the name of the function:
1607 my($stmt, @bind) = $sql->update('people', \%data);
1609 Notice that your C<%data> isn't touched; the module will generate
1610 the appropriately quirky SQL for you automatically. Usually you'll
1611 want to specify a WHERE clause for your UPDATE, though, which is
1612 where handling C<%where> hashes comes in handy...
1614 =head2 Complex where statements
1616 This module can generate pretty complicated WHERE statements
1617 easily. For example, simple C<key=value> pairs are taken to mean
1618 equality, and if you want to see if a field is within a set
1619 of values, you can use an arrayref. Let's say we wanted to
1620 SELECT some data based on this criteria:
1623 requestor => 'inna',
1624 worker => ['nwiger', 'rcwe', 'sfz'],
1625 status => { '!=', 'completed' }
1628 my($stmt, @bind) = $sql->select('tickets', '*', \%where);
1630 The above would give you something like this:
1632 $stmt = "SELECT * FROM tickets WHERE
1633 ( requestor = ? ) AND ( status != ? )
1634 AND ( worker = ? OR worker = ? OR worker = ? )";
1635 @bind = ('inna', 'completed', 'nwiger', 'rcwe', 'sfz');
1637 Which you could then use in DBI code like so:
1639 my $sth = $dbh->prepare($stmt);
1640 $sth->execute(@bind);
1646 The functions are simple. There's one for each major SQL operation,
1647 and a constructor you use first. The arguments are specified in a
1648 similar order to each function (table, then fields, then a where
1649 clause) to try and simplify things.
1654 =head2 new(option => 'value')
1656 The C<new()> function takes a list of options and values, and returns
1657 a new B<SQL::Abstract> object which can then be used to generate SQL
1658 through the methods below. The options accepted are:
1664 If set to 'lower', then SQL will be generated in all lowercase. By
1665 default SQL is generated in "textbook" case meaning something like:
1667 SELECT a_field FROM a_table WHERE some_field LIKE '%someval%'
1669 Any setting other than 'lower' is ignored.
1673 This determines what the default comparison operator is. By default
1674 it is C<=>, meaning that a hash like this:
1676 %where = (name => 'nwiger', email => 'nate@wiger.org');
1678 Will generate SQL like this:
1680 WHERE name = 'nwiger' AND email = 'nate@wiger.org'
1682 However, you may want loose comparisons by default, so if you set
1683 C<cmp> to C<like> you would get SQL such as:
1685 WHERE name like 'nwiger' AND email like 'nate@wiger.org'
1687 You can also override the comparison on an individual basis - see
1688 the huge section on L</"WHERE CLAUSES"> at the bottom.
1690 =item sqltrue, sqlfalse
1692 Expressions for inserting boolean values within SQL statements.
1693 By default these are C<1=1> and C<1=0>. They are used
1694 by the special operators C<-in> and C<-not_in> for generating
1695 correct SQL even when the argument is an empty array (see below).
1699 This determines the default logical operator for multiple WHERE
1700 statements in arrays or hashes. If absent, the default logic is "or"
1701 for arrays, and "and" for hashes. This means that a WHERE
1705 event_date => {'>=', '2/13/99'},
1706 event_date => {'<=', '4/24/03'},
1709 will generate SQL like this:
1711 WHERE event_date >= '2/13/99' OR event_date <= '4/24/03'
1713 This is probably not what you want given this query, though (look
1714 at the dates). To change the "OR" to an "AND", simply specify:
1716 my $sql = SQL::Abstract->new(logic => 'and');
1718 Which will change the above C<WHERE> to:
1720 WHERE event_date >= '2/13/99' AND event_date <= '4/24/03'
1722 The logic can also be changed locally by inserting
1723 a modifier in front of an arrayref :
1725 @where = (-and => [event_date => {'>=', '2/13/99'},
1726 event_date => {'<=', '4/24/03'} ]);
1728 See the L</"WHERE CLAUSES"> section for explanations.
1732 This will automatically convert comparisons using the specified SQL
1733 function for both column and value. This is mostly used with an argument
1734 of C<upper> or C<lower>, so that the SQL will have the effect of
1735 case-insensitive "searches". For example, this:
1737 $sql = SQL::Abstract->new(convert => 'upper');
1738 %where = (keywords => 'MaKe iT CAse inSeNSItive');
1740 Will turn out the following SQL:
1742 WHERE upper(keywords) like upper('MaKe iT CAse inSeNSItive')
1744 The conversion can be C<upper()>, C<lower()>, or any other SQL function
1745 that can be applied symmetrically to fields (actually B<SQL::Abstract> does
1746 not validate this option; it will just pass through what you specify verbatim).
1750 This is a kludge because many databases suck. For example, you can't
1751 just bind values using DBI's C<execute()> for Oracle C<CLOB> or C<BLOB> fields.
1752 Instead, you have to use C<bind_param()>:
1754 $sth->bind_param(1, 'reg data');
1755 $sth->bind_param(2, $lots, {ora_type => ORA_CLOB});
1757 The problem is, B<SQL::Abstract> will normally just return a C<@bind> array,
1758 which loses track of which field each slot refers to. Fear not.
1760 If you specify C<bindtype> in new, you can determine how C<@bind> is returned.
1761 Currently, you can specify either C<normal> (default) or C<columns>. If you
1762 specify C<columns>, you will get an array that looks like this:
1764 my $sql = SQL::Abstract->new(bindtype => 'columns');
1765 my($stmt, @bind) = $sql->insert(...);
1768 [ 'column1', 'value1' ],
1769 [ 'column2', 'value2' ],
1770 [ 'column3', 'value3' ],
1773 You can then iterate through this manually, using DBI's C<bind_param()>.
1775 $sth->prepare($stmt);
1778 my($col, $data) = @$_;
1779 if ($col eq 'details' || $col eq 'comments') {
1780 $sth->bind_param($i, $data, {ora_type => ORA_CLOB});
1781 } elsif ($col eq 'image') {
1782 $sth->bind_param($i, $data, {ora_type => ORA_BLOB});
1784 $sth->bind_param($i, $data);
1788 $sth->execute; # execute without @bind now
1790 Now, why would you still use B<SQL::Abstract> if you have to do this crap?
1791 Basically, the advantage is still that you don't have to care which fields
1792 are or are not included. You could wrap that above C<for> loop in a simple
1793 sub called C<bind_fields()> or something and reuse it repeatedly. You still
1794 get a layer of abstraction over manual SQL specification.
1796 Note that if you set L</bindtype> to C<columns>, the C<\[$sql, @bind]>
1797 construct (see L</Literal SQL with placeholders and bind values (subqueries)>)
1798 will expect the bind values in this format.
1802 This is the character that a table or column name will be quoted
1803 with. By default this is an empty string, but you could set it to
1804 the character C<`>, to generate SQL like this:
1806 SELECT `a_field` FROM `a_table` WHERE `some_field` LIKE '%someval%'
1808 Alternatively, you can supply an array ref of two items, the first being the left
1809 hand quote character, and the second the right hand quote character. For
1810 example, you could supply C<['[',']']> for SQL Server 2000 compliant quotes
1811 that generates SQL like this:
1813 SELECT [a_field] FROM [a_table] WHERE [some_field] LIKE '%someval%'
1815 Quoting is useful if you have tables or columns names that are reserved
1816 words in your database's SQL dialect.
1820 This is the character that separates a table and column name. It is
1821 necessary to specify this when the C<quote_char> option is selected,
1822 so that tables and column names can be individually quoted like this:
1824 SELECT `table`.`one_field` FROM `table` WHERE `table`.`other_field` = 1
1826 =item injection_guard
1828 A regular expression C<qr/.../> that is applied to any C<-function> and unquoted
1829 column name specified in a query structure. This is a safety mechanism to avoid
1830 injection attacks when mishandling user input e.g.:
1832 my %condition_as_column_value_pairs = get_values_from_user();
1833 $sqla->select( ... , \%condition_as_column_value_pairs );
1835 If the expression matches an exception is thrown. Note that literal SQL
1836 supplied via C<\'...'> or C<\['...']> is B<not> checked in any way.
1838 Defaults to checking for C<;> and the C<GO> keyword (TransactSQL)
1840 =item array_datatypes
1842 When this option is true, arrayrefs in INSERT or UPDATE are
1843 interpreted as array datatypes and are passed directly
1845 When this option is false, arrayrefs are interpreted
1846 as literal SQL, just like refs to arrayrefs
1847 (but this behavior is for backwards compatibility; when writing
1848 new queries, use the "reference to arrayref" syntax
1854 Takes a reference to a list of "special operators"
1855 to extend the syntax understood by L<SQL::Abstract>.
1856 See section L</"SPECIAL OPERATORS"> for details.
1860 Takes a reference to a list of "unary operators"
1861 to extend the syntax understood by L<SQL::Abstract>.
1862 See section L</"UNARY OPERATORS"> for details.
1868 =head2 insert($table, \@values || \%fieldvals, \%options)
1870 This is the simplest function. You simply give it a table name
1871 and either an arrayref of values or hashref of field/value pairs.
1872 It returns an SQL INSERT statement and a list of bind values.
1873 See the sections on L</"Inserting and Updating Arrays"> and
1874 L</"Inserting and Updating SQL"> for information on how to insert
1875 with those data types.
1877 The optional C<\%options> hash reference may contain additional
1878 options to generate the insert SQL. Currently supported options
1885 Takes either a scalar of raw SQL fields, or an array reference of
1886 field names, and adds on an SQL C<RETURNING> statement at the end.
1887 This allows you to return data generated by the insert statement
1888 (such as row IDs) without performing another C<SELECT> statement.
1889 Note, however, this is not part of the SQL standard and may not
1890 be supported by all database engines.
1894 =head2 update($table, \%fieldvals, \%where)
1896 This takes a table, hashref of field/value pairs, and an optional
1897 hashref L<WHERE clause|/WHERE CLAUSES>. It returns an SQL UPDATE function and a list
1899 See the sections on L</"Inserting and Updating Arrays"> and
1900 L</"Inserting and Updating SQL"> for information on how to insert
1901 with those data types.
1903 =head2 select($source, $fields, $where, $order)
1905 This returns a SQL SELECT statement and associated list of bind values, as
1906 specified by the arguments :
1912 Specification of the 'FROM' part of the statement.
1913 The argument can be either a plain scalar (interpreted as a table
1914 name, will be quoted), or an arrayref (interpreted as a list
1915 of table names, joined by commas, quoted), or a scalarref
1916 (literal table name, not quoted), or a ref to an arrayref
1917 (list of literal table names, joined by commas, not quoted).
1921 Specification of the list of fields to retrieve from
1923 The argument can be either an arrayref (interpreted as a list
1924 of field names, will be joined by commas and quoted), or a
1925 plain scalar (literal SQL, not quoted).
1926 Please observe that this API is not as flexible as that of
1927 the first argument C<$source>, for backwards compatibility reasons.
1931 Optional argument to specify the WHERE part of the query.
1932 The argument is most often a hashref, but can also be
1933 an arrayref or plain scalar --
1934 see section L<WHERE clause|/"WHERE CLAUSES"> for details.
1938 Optional argument to specify the ORDER BY part of the query.
1939 The argument can be a scalar, a hashref or an arrayref
1940 -- see section L<ORDER BY clause|/"ORDER BY CLAUSES">
1946 =head2 delete($table, \%where)
1948 This takes a table name and optional hashref L<WHERE clause|/WHERE CLAUSES>.
1949 It returns an SQL DELETE statement and list of bind values.
1951 =head2 where(\%where, \@order)
1953 This is used to generate just the WHERE clause. For example,
1954 if you have an arbitrary data structure and know what the
1955 rest of your SQL is going to look like, but want an easy way
1956 to produce a WHERE clause, use this. It returns an SQL WHERE
1957 clause and list of bind values.
1960 =head2 values(\%data)
1962 This just returns the values from the hash C<%data>, in the same
1963 order that would be returned from any of the other above queries.
1964 Using this allows you to markedly speed up your queries if you
1965 are affecting lots of rows. See below under the L</"PERFORMANCE"> section.
1967 =head2 generate($any, 'number', $of, \@data, $struct, \%types)
1969 Warning: This is an experimental method and subject to change.
1971 This returns arbitrarily generated SQL. It's a really basic shortcut.
1972 It will return two different things, depending on return context:
1974 my($stmt, @bind) = $sql->generate('create table', \$table, \@fields);
1975 my $stmt_and_val = $sql->generate('create table', \$table, \@fields);
1977 These would return the following:
1979 # First calling form
1980 $stmt = "CREATE TABLE test (?, ?)";
1981 @bind = (field1, field2);
1983 # Second calling form
1984 $stmt_and_val = "CREATE TABLE test (field1, field2)";
1986 Depending on what you're trying to do, it's up to you to choose the correct
1987 format. In this example, the second form is what you would want.
1991 $sql->generate('alter session', { nls_date_format => 'MM/YY' });
1995 ALTER SESSION SET nls_date_format = 'MM/YY'
1997 You get the idea. Strings get their case twiddled, but everything
1998 else remains verbatim.
2000 =head1 WHERE CLAUSES
2004 This module uses a variation on the idea from L<DBIx::Abstract>. It
2005 is B<NOT>, repeat I<not> 100% compatible. B<The main logic of this
2006 module is that things in arrays are OR'ed, and things in hashes
2009 The easiest way to explain is to show lots of examples. After
2010 each C<%where> hash shown, it is assumed you used:
2012 my($stmt, @bind) = $sql->where(\%where);
2014 However, note that the C<%where> hash can be used directly in any
2015 of the other functions as well, as described above.
2017 =head2 Key-value pairs
2019 So, let's get started. To begin, a simple hash:
2023 status => 'completed'
2026 Is converted to SQL C<key = val> statements:
2028 $stmt = "WHERE user = ? AND status = ?";
2029 @bind = ('nwiger', 'completed');
2031 One common thing I end up doing is having a list of values that
2032 a field can be in. To do this, simply specify a list inside of
2037 status => ['assigned', 'in-progress', 'pending'];
2040 This simple code will create the following:
2042 $stmt = "WHERE user = ? AND ( status = ? OR status = ? OR status = ? )";
2043 @bind = ('nwiger', 'assigned', 'in-progress', 'pending');
2045 A field associated to an empty arrayref will be considered a
2046 logical false and will generate 0=1.
2048 =head2 Tests for NULL values
2050 If the value part is C<undef> then this is converted to SQL <IS NULL>
2059 $stmt = "WHERE user = ? AND status IS NULL";
2062 To test if a column IS NOT NULL:
2066 status => { '!=', undef },
2069 =head2 Specific comparison operators
2071 If you want to specify a different type of operator for your comparison,
2072 you can use a hashref for a given column:
2076 status => { '!=', 'completed' }
2079 Which would generate:
2081 $stmt = "WHERE user = ? AND status != ?";
2082 @bind = ('nwiger', 'completed');
2084 To test against multiple values, just enclose the values in an arrayref:
2086 status => { '=', ['assigned', 'in-progress', 'pending'] };
2088 Which would give you:
2090 "WHERE status = ? OR status = ? OR status = ?"
2093 The hashref can also contain multiple pairs, in which case it is expanded
2094 into an C<AND> of its elements:
2098 status => { '!=', 'completed', -not_like => 'pending%' }
2101 # Or more dynamically, like from a form
2102 $where{user} = 'nwiger';
2103 $where{status}{'!='} = 'completed';
2104 $where{status}{'-not_like'} = 'pending%';
2106 # Both generate this
2107 $stmt = "WHERE user = ? AND status != ? AND status NOT LIKE ?";
2108 @bind = ('nwiger', 'completed', 'pending%');
2111 To get an OR instead, you can combine it with the arrayref idea:
2115 priority => [ { '=', 2 }, { '>', 5 } ]
2118 Which would generate:
2120 $stmt = "WHERE ( priority = ? OR priority > ? ) AND user = ?";
2121 @bind = ('2', '5', 'nwiger');
2123 If you want to include literal SQL (with or without bind values), just use a
2124 scalar reference or array reference as the value:
2127 date_entered => { '>' => \["to_date(?, 'MM/DD/YYYY')", "11/26/2008"] },
2128 date_expires => { '<' => \"now()" }
2131 Which would generate:
2133 $stmt = "WHERE date_entered > "to_date(?, 'MM/DD/YYYY') AND date_expires < now()";
2134 @bind = ('11/26/2008');
2137 =head2 Logic and nesting operators
2139 In the example above,
2140 there is a subtle trap if you want to say something like
2141 this (notice the C<AND>):
2143 WHERE priority != ? AND priority != ?
2145 Because, in Perl you I<can't> do this:
2147 priority => { '!=', 2, '!=', 1 }
2149 As the second C<!=> key will obliterate the first. The solution
2150 is to use the special C<-modifier> form inside an arrayref:
2152 priority => [ -and => {'!=', 2},
2156 Normally, these would be joined by C<OR>, but the modifier tells it
2157 to use C<AND> instead. (Hint: You can use this in conjunction with the
2158 C<logic> option to C<new()> in order to change the way your queries
2159 work by default.) B<Important:> Note that the C<-modifier> goes
2160 B<INSIDE> the arrayref, as an extra first element. This will
2161 B<NOT> do what you think it might:
2163 priority => -and => [{'!=', 2}, {'!=', 1}] # WRONG!
2165 Here is a quick list of equivalencies, since there is some overlap:
2168 status => {'!=', 'completed', 'not like', 'pending%' }
2169 status => [ -and => {'!=', 'completed'}, {'not like', 'pending%'}]
2172 status => {'=', ['assigned', 'in-progress']}
2173 status => [ -or => {'=', 'assigned'}, {'=', 'in-progress'}]
2174 status => [ {'=', 'assigned'}, {'=', 'in-progress'} ]
2178 =head2 Special operators : IN, BETWEEN, etc.
2180 You can also use the hashref format to compare a list of fields using the
2181 C<IN> comparison operator, by specifying the list as an arrayref:
2184 status => 'completed',
2185 reportid => { -in => [567, 2335, 2] }
2188 Which would generate:
2190 $stmt = "WHERE status = ? AND reportid IN (?,?,?)";
2191 @bind = ('completed', '567', '2335', '2');
2193 The reverse operator C<-not_in> generates SQL C<NOT IN> and is used in
2196 If the argument to C<-in> is an empty array, 'sqlfalse' is generated
2197 (by default : C<1=0>). Similarly, C<< -not_in => [] >> generates
2198 'sqltrue' (by default : C<1=1>).
2200 In addition to the array you can supply a chunk of literal sql or
2201 literal sql with bind:
2204 customer => { -in => \[
2205 'SELECT cust_id FROM cust WHERE balance > ?',
2208 status => { -in => \'SELECT status_codes FROM states' },
2214 customer IN ( SELECT cust_id FROM cust WHERE balance > ? )
2215 AND status IN ( SELECT status_codes FROM states )
2219 Finally, if the argument to C<-in> is not a reference, it will be
2220 treated as a single-element array.
2222 Another pair of operators is C<-between> and C<-not_between>,
2223 used with an arrayref of two values:
2227 completion_date => {
2228 -not_between => ['2002-10-01', '2003-02-06']
2234 WHERE user = ? AND completion_date NOT BETWEEN ( ? AND ? )
2236 Just like with C<-in> all plausible combinations of literal SQL
2240 start0 => { -between => [ 1, 2 ] },
2241 start1 => { -between => \["? AND ?", 1, 2] },
2242 start2 => { -between => \"lower(x) AND upper(y)" },
2243 start3 => { -between => [
2245 \["upper(?)", 'stuff' ],
2252 ( start0 BETWEEN ? AND ? )
2253 AND ( start1 BETWEEN ? AND ? )
2254 AND ( start2 BETWEEN lower(x) AND upper(y) )
2255 AND ( start3 BETWEEN lower(x) AND upper(?) )
2257 @bind = (1, 2, 1, 2, 'stuff');
2260 These are the two builtin "special operators"; but the
2261 list can be expanded : see section L</"SPECIAL OPERATORS"> below.
2263 =head2 Unary operators: bool
2265 If you wish to test against boolean columns or functions within your
2266 database you can use the C<-bool> and C<-not_bool> operators. For
2267 example to test the column C<is_user> being true and the column
2268 C<is_enabled> being false you would use:-
2272 -not_bool => 'is_enabled',
2277 WHERE is_user AND NOT is_enabled
2279 If a more complex combination is required, testing more conditions,
2280 then you should use the and/or operators:-
2285 -not_bool => { two=> { -rlike => 'bar' } },
2286 -not_bool => { three => [ { '=', 2 }, { '>', 5 } ] },
2297 (NOT ( three = ? OR three > ? ))
2300 =head2 Nested conditions, -and/-or prefixes
2302 So far, we've seen how multiple conditions are joined with a top-level
2303 C<AND>. We can change this by putting the different conditions we want in
2304 hashes and then putting those hashes in an array. For example:
2309 status => { -like => ['pending%', 'dispatched'] },
2313 status => 'unassigned',
2317 This data structure would create the following:
2319 $stmt = "WHERE ( user = ? AND ( status LIKE ? OR status LIKE ? ) )
2320 OR ( user = ? AND status = ? ) )";
2321 @bind = ('nwiger', 'pending', 'dispatched', 'robot', 'unassigned');
2324 Clauses in hashrefs or arrayrefs can be prefixed with an C<-and> or C<-or>
2325 to change the logic inside :
2331 -and => [ workhrs => {'>', 20}, geo => 'ASIA' ],
2332 -or => { workhrs => {'<', 50}, geo => 'EURO' },
2339 WHERE ( user = ? AND (
2340 ( workhrs > ? AND geo = ? )
2341 OR ( workhrs < ? OR geo = ? )
2344 =head3 Algebraic inconsistency, for historical reasons
2346 C<Important note>: when connecting several conditions, the C<-and->|C<-or>
2347 operator goes C<outside> of the nested structure; whereas when connecting
2348 several constraints on one column, the C<-and> operator goes
2349 C<inside> the arrayref. Here is an example combining both features :
2352 -and => [a => 1, b => 2],
2353 -or => [c => 3, d => 4],
2354 e => [-and => {-like => 'foo%'}, {-like => '%bar'} ]
2359 WHERE ( ( ( a = ? AND b = ? )
2360 OR ( c = ? OR d = ? )
2361 OR ( e LIKE ? AND e LIKE ? ) ) )
2363 This difference in syntax is unfortunate but must be preserved for
2364 historical reasons. So be careful : the two examples below would
2365 seem algebraically equivalent, but they are not
2367 {col => [-and => {-like => 'foo%'}, {-like => '%bar'}]}
2368 # yields : WHERE ( ( col LIKE ? AND col LIKE ? ) )
2370 [-and => {col => {-like => 'foo%'}, {col => {-like => '%bar'}}]]
2371 # yields : WHERE ( ( col LIKE ? OR col LIKE ? ) )
2374 =head2 Literal SQL and value type operators
2376 The basic premise of SQL::Abstract is that in WHERE specifications the "left
2377 side" is a column name and the "right side" is a value (normally rendered as
2378 a placeholder). This holds true for both hashrefs and arrayref pairs as you
2379 see in the L</WHERE CLAUSES> examples above. Sometimes it is necessary to
2380 alter this behavior. There are several ways of doing so.
2384 This is a virtual operator that signals the string to its right side is an
2385 identifier (a column name) and not a value. For example to compare two
2386 columns you would write:
2389 priority => { '<', 2 },
2390 requestor => { -ident => 'submitter' },
2395 $stmt = "WHERE priority < ? AND requestor = submitter";
2398 If you are maintaining legacy code you may see a different construct as
2399 described in L</Deprecated usage of Literal SQL>, please use C<-ident> in new
2404 This is a virtual operator that signals that the construct to its right side
2405 is a value to be passed to DBI. This is for example necessary when you want
2406 to write a where clause against an array (for RDBMS that support such
2407 datatypes). For example:
2410 array => { -value => [1, 2, 3] }
2415 $stmt = 'WHERE array = ?';
2416 @bind = ([1, 2, 3]);
2418 Note that if you were to simply say:
2424 the result would probably not be what you wanted:
2426 $stmt = 'WHERE array = ? OR array = ? OR array = ?';
2431 Finally, sometimes only literal SQL will do. To include a random snippet
2432 of SQL verbatim, you specify it as a scalar reference. Consider this only
2433 as a last resort. Usually there is a better way. For example:
2436 priority => { '<', 2 },
2437 requestor => { -in => \'(SELECT name FROM hitmen)' },
2442 $stmt = "WHERE priority < ? AND requestor IN (SELECT name FROM hitmen)"
2445 Note that in this example, you only get one bind parameter back, since
2446 the verbatim SQL is passed as part of the statement.
2450 Never use untrusted input as a literal SQL argument - this is a massive
2451 security risk (there is no way to check literal snippets for SQL
2452 injections and other nastyness). If you need to deal with untrusted input
2453 use literal SQL with placeholders as described next.
2455 =head3 Literal SQL with placeholders and bind values (subqueries)
2457 If the literal SQL to be inserted has placeholders and bind values,
2458 use a reference to an arrayref (yes this is a double reference --
2459 not so common, but perfectly legal Perl). For example, to find a date
2460 in Postgres you can use something like this:
2463 date_column => \[q/= date '2008-09-30' - ?::integer/, 10/]
2468 $stmt = "WHERE ( date_column = date '2008-09-30' - ?::integer )"
2471 Note that you must pass the bind values in the same format as they are returned
2472 by L</where>. That means that if you set L</bindtype> to C<columns>, you must
2473 provide the bind values in the C<< [ column_meta => value ] >> format, where
2474 C<column_meta> is an opaque scalar value; most commonly the column name, but
2475 you can use any scalar value (including references and blessed references),
2476 L<SQL::Abstract> will simply pass it through intact. So if C<bindtype> is set
2477 to C<columns> the above example will look like:
2480 date_column => \[q/= date '2008-09-30' - ?::integer/, [ dummy => 10 ]/]
2483 Literal SQL is especially useful for nesting parenthesized clauses in the
2484 main SQL query. Here is a first example :
2486 my ($sub_stmt, @sub_bind) = ("SELECT c1 FROM t1 WHERE c2 < ? AND c3 LIKE ?",
2490 bar => \["IN ($sub_stmt)" => @sub_bind],
2495 $stmt = "WHERE (foo = ? AND bar IN (SELECT c1 FROM t1
2496 WHERE c2 < ? AND c3 LIKE ?))";
2497 @bind = (1234, 100, "foo%");
2499 Other subquery operators, like for example C<"E<gt> ALL"> or C<"NOT IN">,
2500 are expressed in the same way. Of course the C<$sub_stmt> and
2501 its associated bind values can be generated through a former call
2504 my ($sub_stmt, @sub_bind)
2505 = $sql->select("t1", "c1", {c2 => {"<" => 100},
2506 c3 => {-like => "foo%"}});
2509 bar => \["> ALL ($sub_stmt)" => @sub_bind],
2512 In the examples above, the subquery was used as an operator on a column;
2513 but the same principle also applies for a clause within the main C<%where>
2514 hash, like an EXISTS subquery :
2516 my ($sub_stmt, @sub_bind)
2517 = $sql->select("t1", "*", {c1 => 1, c2 => \"> t0.c0"});
2518 my %where = ( -and => [
2520 \["EXISTS ($sub_stmt)" => @sub_bind],
2525 $stmt = "WHERE (foo = ? AND EXISTS (SELECT * FROM t1
2526 WHERE c1 = ? AND c2 > t0.c0))";
2530 Observe that the condition on C<c2> in the subquery refers to
2531 column C<t0.c0> of the main query : this is I<not> a bind
2532 value, so we have to express it through a scalar ref.
2533 Writing C<< c2 => {">" => "t0.c0"} >> would have generated
2534 C<< c2 > ? >> with bind value C<"t0.c0"> ... not exactly
2535 what we wanted here.
2537 Finally, here is an example where a subquery is used
2538 for expressing unary negation:
2540 my ($sub_stmt, @sub_bind)
2541 = $sql->where({age => [{"<" => 10}, {">" => 20}]});
2542 $sub_stmt =~ s/^ where //i; # don't want "WHERE" in the subclause
2544 lname => {like => '%son%'},
2545 \["NOT ($sub_stmt)" => @sub_bind],
2550 $stmt = "lname LIKE ? AND NOT ( age < ? OR age > ? )"
2551 @bind = ('%son%', 10, 20)
2553 =head3 Deprecated usage of Literal SQL
2555 Below are some examples of archaic use of literal SQL. It is shown only as
2556 reference for those who deal with legacy code. Each example has a much
2557 better, cleaner and safer alternative that users should opt for in new code.
2563 my %where = ( requestor => \'IS NOT NULL' )
2565 $stmt = "WHERE requestor IS NOT NULL"
2567 This used to be the way of generating NULL comparisons, before the handling
2568 of C<undef> got formalized. For new code please use the superior syntax as
2569 described in L</Tests for NULL values>.
2573 my %where = ( requestor => \'= submitter' )
2575 $stmt = "WHERE requestor = submitter"
2577 This used to be the only way to compare columns. Use the superior L</-ident>
2578 method for all new code. For example an identifier declared in such a way
2579 will be properly quoted if L</quote_char> is properly set, while the legacy
2580 form will remain as supplied.
2584 my %where = ( is_ready => \"", completed => { '>', '2012-12-21' } )
2586 $stmt = "WHERE completed > ? AND is_ready"
2587 @bind = ('2012-12-21')
2589 Using an empty string literal used to be the only way to express a boolean.
2590 For all new code please use the much more readable
2591 L<-bool|/Unary operators: bool> operator.
2597 These pages could go on for a while, since the nesting of the data
2598 structures this module can handle are pretty much unlimited (the
2599 module implements the C<WHERE> expansion as a recursive function
2600 internally). Your best bet is to "play around" with the module a
2601 little to see how the data structures behave, and choose the best
2602 format for your data based on that.
2604 And of course, all the values above will probably be replaced with
2605 variables gotten from forms or the command line. After all, if you
2606 knew everything ahead of time, you wouldn't have to worry about
2607 dynamically-generating SQL and could just hardwire it into your
2610 =head1 ORDER BY CLAUSES
2612 Some functions take an order by clause. This can either be a scalar (just a
2613 column name,) a hash of C<< { -desc => 'col' } >> or C<< { -asc => 'col' } >>,
2614 or an array of either of the two previous forms. Examples:
2616 Given | Will Generate
2617 ----------------------------------------------------------
2619 \'colA DESC' | ORDER BY colA DESC
2621 'colA' | ORDER BY colA
2623 [qw/colA colB/] | ORDER BY colA, colB
2625 {-asc => 'colA'} | ORDER BY colA ASC
2627 {-desc => 'colB'} | ORDER BY colB DESC
2629 ['colA', {-asc => 'colB'}] | ORDER BY colA, colB ASC
2631 { -asc => [qw/colA colB/] } | ORDER BY colA ASC, colB ASC
2634 { -asc => 'colA' }, | ORDER BY colA ASC, colB DESC,
2635 { -desc => [qw/colB/], | colC ASC, colD ASC
2636 { -asc => [qw/colC colD/],|
2638 ===========================================================
2642 =head1 SPECIAL OPERATORS
2644 my $sqlmaker = SQL::Abstract->new(special_ops => [
2648 my ($self, $field, $op, $arg) = @_;
2654 handler => 'method_name',
2658 A "special operator" is a SQL syntactic clause that can be
2659 applied to a field, instead of a usual binary operator.
2662 WHERE field IN (?, ?, ?)
2663 WHERE field BETWEEN ? AND ?
2664 WHERE MATCH(field) AGAINST (?, ?)
2666 Special operators IN and BETWEEN are fairly standard and therefore
2667 are builtin within C<SQL::Abstract> (as the overridable methods
2668 C<_where_field_IN> and C<_where_field_BETWEEN>). For other operators,
2669 like the MATCH .. AGAINST example above which is specific to MySQL,
2670 you can write your own operator handlers - supply a C<special_ops>
2671 argument to the C<new> method. That argument takes an arrayref of
2672 operator definitions; each operator definition is a hashref with two
2679 the regular expression to match the operator
2683 Either a coderef or a plain scalar method name. In both cases
2684 the expected return is C<< ($sql, @bind) >>.
2686 When supplied with a method name, it is simply called on the
2687 L<SQL::Abstract/> object as:
2689 $self->$method_name ($field, $op, $arg)
2693 $op is the part that matched the handler regex
2694 $field is the LHS of the operator
2697 When supplied with a coderef, it is called as:
2699 $coderef->($self, $field, $op, $arg)
2704 For example, here is an implementation
2705 of the MATCH .. AGAINST syntax for MySQL
2707 my $sqlmaker = SQL::Abstract->new(special_ops => [
2709 # special op for MySql MATCH (field) AGAINST(word1, word2, ...)
2710 {regex => qr/^match$/i,
2712 my ($self, $field, $op, $arg) = @_;
2713 $arg = [$arg] if not ref $arg;
2714 my $label = $self->_quote($field);
2715 my ($placeholder) = $self->_convert('?');
2716 my $placeholders = join ", ", (($placeholder) x @$arg);
2717 my $sql = $self->_sqlcase('match') . " ($label) "
2718 . $self->_sqlcase('against') . " ($placeholders) ";
2719 my @bind = $self->_bindtype($field, @$arg);
2720 return ($sql, @bind);
2727 =head1 UNARY OPERATORS
2729 my $sqlmaker = SQL::Abstract->new(unary_ops => [
2733 my ($self, $op, $arg) = @_;
2739 handler => 'method_name',
2743 A "unary operator" is a SQL syntactic clause that can be
2744 applied to a field - the operator goes before the field
2746 You can write your own operator handlers - supply a C<unary_ops>
2747 argument to the C<new> method. That argument takes an arrayref of
2748 operator definitions; each operator definition is a hashref with two
2755 the regular expression to match the operator
2759 Either a coderef or a plain scalar method name. In both cases
2760 the expected return is C<< $sql >>.
2762 When supplied with a method name, it is simply called on the
2763 L<SQL::Abstract/> object as:
2765 $self->$method_name ($op, $arg)
2769 $op is the part that matched the handler regex
2770 $arg is the RHS or argument of the operator
2772 When supplied with a coderef, it is called as:
2774 $coderef->($self, $op, $arg)
2782 Thanks to some benchmarking by Mark Stosberg, it turns out that
2783 this module is many orders of magnitude faster than using C<DBIx::Abstract>.
2784 I must admit this wasn't an intentional design issue, but it's a
2785 byproduct of the fact that you get to control your C<DBI> handles
2788 To maximize performance, use a code snippet like the following:
2790 # prepare a statement handle using the first row
2791 # and then reuse it for the rest of the rows
2793 for my $href (@array_of_hashrefs) {
2794 $stmt ||= $sql->insert('table', $href);
2795 $sth ||= $dbh->prepare($stmt);
2796 $sth->execute($sql->values($href));
2799 The reason this works is because the keys in your C<$href> are sorted
2800 internally by B<SQL::Abstract>. Thus, as long as your data retains
2801 the same structure, you only have to generate the SQL the first time
2802 around. On subsequent queries, simply use the C<values> function provided
2803 by this module to return your values in the correct order.
2805 However this depends on the values having the same type - if, for
2806 example, the values of a where clause may either have values
2807 (resulting in sql of the form C<column = ?> with a single bind
2808 value), or alternatively the values might be C<undef> (resulting in
2809 sql of the form C<column IS NULL> with no bind value) then the
2810 caching technique suggested will not work.
2814 If you use my C<CGI::FormBuilder> module at all, you'll hopefully
2815 really like this part (I do, at least). Building up a complex query
2816 can be as simple as the following:
2823 use CGI::FormBuilder;
2826 my $form = CGI::FormBuilder->new(...);
2827 my $sql = SQL::Abstract->new;
2829 if ($form->submitted) {
2830 my $field = $form->field;
2831 my $id = delete $field->{id};
2832 my($stmt, @bind) = $sql->update('table', $field, {id => $id});
2835 Of course, you would still have to connect using C<DBI> to run the
2836 query, but the point is that if you make your form look like your
2837 table, the actual query script can be extremely simplistic.
2839 If you're B<REALLY> lazy (I am), check out C<HTML::QuickTable> for
2840 a fast interface to returning and formatting data. I frequently
2841 use these three modules together to write complex database query
2842 apps in under 50 lines.
2848 =item * gitweb: L<http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=dbsrgits/SQL-Abstract.git>
2850 =item * git: L<git://git.shadowcat.co.uk/dbsrgits/SQL-Abstract.git>
2856 Version 1.50 was a major internal refactoring of C<SQL::Abstract>.
2857 Great care has been taken to preserve the I<published> behavior
2858 documented in previous versions in the 1.* family; however,
2859 some features that were previously undocumented, or behaved
2860 differently from the documentation, had to be changed in order
2861 to clarify the semantics. Hence, client code that was relying
2862 on some dark areas of C<SQL::Abstract> v1.*
2863 B<might behave differently> in v1.50.
2865 The main changes are :
2871 support for literal SQL through the C<< \ [$sql, bind] >> syntax.
2875 support for the { operator => \"..." } construct (to embed literal SQL)
2879 support for the { operator => \["...", @bind] } construct (to embed literal SQL with bind values)
2883 optional support for L<array datatypes|/"Inserting and Updating Arrays">
2887 defensive programming : check arguments
2891 fixed bug with global logic, which was previously implemented
2892 through global variables yielding side-effects. Prior versions would
2893 interpret C<< [ {cond1, cond2}, [cond3, cond4] ] >>
2894 as C<< "(cond1 AND cond2) OR (cond3 AND cond4)" >>.
2895 Now this is interpreted
2896 as C<< "(cond1 AND cond2) OR (cond3 OR cond4)" >>.
2901 fixed semantics of _bindtype on array args
2905 dropped the C<_anoncopy> of the %where tree. No longer necessary,
2906 we just avoid shifting arrays within that tree.
2910 dropped the C<_modlogic> function
2914 =head1 ACKNOWLEDGEMENTS
2916 There are a number of individuals that have really helped out with
2917 this module. Unfortunately, most of them submitted bugs via CPAN
2918 so I have no idea who they are! But the people I do know are:
2920 Ash Berlin (order_by hash term support)
2921 Matt Trout (DBIx::Class support)
2922 Mark Stosberg (benchmarking)
2923 Chas Owens (initial "IN" operator support)
2924 Philip Collins (per-field SQL functions)
2925 Eric Kolve (hashref "AND" support)
2926 Mike Fragassi (enhancements to "BETWEEN" and "LIKE")
2927 Dan Kubb (support for "quote_char" and "name_sep")
2928 Guillermo Roditi (patch to cleanup "IN" and "BETWEEN", fix and tests for _order_by)
2929 Laurent Dami (internal refactoring, extensible list of special operators, literal SQL)
2930 Norbert Buchmuller (support for literal SQL in hashpair, misc. fixes & tests)
2931 Peter Rabbitson (rewrite of SQLA::Test, misc. fixes & tests)
2932 Oliver Charles (support for "RETURNING" after "INSERT")
2938 L<DBIx::Class>, L<DBIx::Abstract>, L<CGI::FormBuilder>, L<HTML::QuickTable>.
2942 Copyright (c) 2001-2007 Nathan Wiger <nwiger@cpan.org>. All Rights Reserved.
2944 This module is actively maintained by Matt Trout <mst@shadowcatsystems.co.uk>
2946 For support, your best bet is to try the C<DBIx::Class> users mailing list.
2947 While not an official support venue, C<DBIx::Class> makes heavy use of
2948 C<SQL::Abstract>, and as such list members there are very familiar with
2949 how to create queries.
2953 This module is free software; you may copy this under the same
2954 terms as perl itself (either the GNU General Public License or
2955 the Artistic License)