1 package SQL::Abstract; # see doc at end of file
3 # LDNOTE : this code is heavy refactoring from original SQLA.
4 # Several design decisions will need discussion during
5 # the test / diffusion / acceptance phase; those are marked with flag
6 # 'LDNOTE' (note by laurent.dami AT free.fr)
14 #======================================================================
16 #======================================================================
18 our $VERSION = '1.71';
20 # This would confuse some packagers
21 $VERSION = eval $VERSION if $VERSION =~ /_/; # numify for warning-free dev releases
25 # special operators (-in, -between). May be extended/overridden by user.
26 # See section WHERE: BUILTIN SPECIAL OPERATORS below for implementation
27 my @BUILTIN_SPECIAL_OPS = (
28 {regex => qr/^ (?: not \s )? between $/ix, handler => '_where_field_BETWEEN'},
29 {regex => qr/^ (?: not \s )? in $/ix, handler => '_where_field_IN'},
32 # unaryish operators - key maps to handler
33 my @BUILTIN_UNARY_OPS = (
34 # the digits are backcompat stuff
35 { regex => qr/^ and (?: [_\s]? \d+ )? $/xi, handler => '_where_op_ANDOR' },
36 { regex => qr/^ or (?: [_\s]? \d+ )? $/xi, handler => '_where_op_ANDOR' },
37 { regex => qr/^ nest (?: [_\s]? \d+ )? $/xi, handler => '_where_op_NEST' },
38 { regex => qr/^ (?: not \s )? bool $/xi, handler => '_where_op_BOOL' },
41 #======================================================================
42 # DEBUGGING AND ERROR REPORTING
43 #======================================================================
46 return unless $_[0]->{debug}; shift; # a little faster
47 my $func = (caller(1))[3];
48 warn "[$func] ", @_, "\n";
52 my($func) = (caller(1))[3];
53 carp "[$func] Warning: ", @_;
57 my($func) = (caller(1))[3];
58 croak "[$func] Fatal: ", @_;
62 #======================================================================
64 #======================================================================
68 my $class = ref($self) || $self;
69 my %opt = (ref $_[0] eq 'HASH') ? %{$_[0]} : @_;
71 # choose our case by keeping an option around
72 delete $opt{case} if $opt{case} && $opt{case} ne 'lower';
74 # default logic for interpreting arrayrefs
75 $opt{logic} = $opt{logic} ? uc $opt{logic} : 'OR';
77 # how to return bind vars
78 # LDNOTE: changed nwiger code : why this 'delete' ??
79 # $opt{bindtype} ||= delete($opt{bind_type}) || 'normal';
80 $opt{bindtype} ||= 'normal';
82 # default comparison is "=", but can be overridden
85 # try to recognize which are the 'equality' and 'unequality' ops
86 # (temporary quickfix, should go through a more seasoned API)
87 $opt{equality_op} = qr/^(\Q$opt{cmp}\E|is|(is\s+)?like)$/i;
88 $opt{inequality_op} = qr/^(!=|<>|(is\s+)?not(\s+like)?)$/i;
91 $opt{sqltrue} ||= '1=1';
92 $opt{sqlfalse} ||= '0=1';
95 $opt{special_ops} ||= [];
96 push @{$opt{special_ops}}, @BUILTIN_SPECIAL_OPS;
99 $opt{unary_ops} ||= [];
100 push @{$opt{unary_ops}}, @BUILTIN_UNARY_OPS;
102 return bless \%opt, $class;
107 #======================================================================
109 #======================================================================
113 my $table = $self->_table(shift);
114 my $data = shift || return;
117 my $method = $self->_METHOD_FOR_refkind("_insert", $data);
118 my ($sql, @bind) = $self->$method($data);
119 $sql = join " ", $self->_sqlcase('insert into'), $table, $sql;
121 if ($options->{returning}) {
122 my ($s, @b) = $self->_insert_returning ($options);
127 return wantarray ? ($sql, @bind) : $sql;
130 sub _insert_returning {
131 my ($self, $options) = @_;
133 my $f = $options->{returning};
135 my $fieldlist = $self->_SWITCH_refkind($f, {
136 ARRAYREF => sub {join ', ', map { $self->_quote($_) } @$f;},
137 SCALAR => sub {$self->_quote($f)},
138 SCALARREF => sub {$$f},
140 return $self->_sqlcase(' returning ') . $fieldlist;
143 sub _insert_HASHREF { # explicit list of fields and then values
144 my ($self, $data) = @_;
146 my @fields = sort keys %$data;
148 my ($sql, @bind) = $self->_insert_values($data);
151 $_ = $self->_quote($_) foreach @fields;
152 $sql = "( ".join(", ", @fields).") ".$sql;
154 return ($sql, @bind);
157 sub _insert_ARRAYREF { # just generate values(?,?) part (no list of fields)
158 my ($self, $data) = @_;
160 # no names (arrayref) so can't generate bindtype
161 $self->{bindtype} ne 'columns'
162 or belch "can't do 'columns' bindtype when called with arrayref";
164 # fold the list of values into a hash of column name - value pairs
165 # (where the column names are artificially generated, and their
166 # lexicographical ordering keep the ordering of the original list)
167 my $i = "a"; # incremented values will be in lexicographical order
168 my $data_in_hash = { map { ($i++ => $_) } @$data };
170 return $self->_insert_values($data_in_hash);
173 sub _insert_ARRAYREFREF { # literal SQL with bind
174 my ($self, $data) = @_;
176 my ($sql, @bind) = @${$data};
177 $self->_assert_bindval_matches_bindtype(@bind);
179 return ($sql, @bind);
183 sub _insert_SCALARREF { # literal SQL without bind
184 my ($self, $data) = @_;
190 my ($self, $data) = @_;
192 my (@values, @all_bind);
193 foreach my $column (sort keys %$data) {
194 my $v = $data->{$column};
196 $self->_SWITCH_refkind($v, {
199 if ($self->{array_datatypes}) { # if array datatype are activated
201 push @all_bind, $self->_bindtype($column, $v);
203 else { # else literal SQL with bind
204 my ($sql, @bind) = @$v;
205 $self->_assert_bindval_matches_bindtype(@bind);
207 push @all_bind, @bind;
211 ARRAYREFREF => sub { # literal SQL with bind
212 my ($sql, @bind) = @${$v};
213 $self->_assert_bindval_matches_bindtype(@bind);
215 push @all_bind, @bind;
218 # THINK : anything useful to do with a HASHREF ?
219 HASHREF => sub { # (nothing, but old SQLA passed it through)
220 #TODO in SQLA >= 2.0 it will die instead
221 belch "HASH ref as bind value in insert is not supported";
223 push @all_bind, $self->_bindtype($column, $v);
226 SCALARREF => sub { # literal SQL without bind
230 SCALAR_or_UNDEF => sub {
232 push @all_bind, $self->_bindtype($column, $v);
239 my $sql = $self->_sqlcase('values')." ( ".join(", ", @values)." )";
240 return ($sql, @all_bind);
245 #======================================================================
247 #======================================================================
252 my $table = $self->_table(shift);
253 my $data = shift || return;
256 # first build the 'SET' part of the sql statement
257 my (@set, @all_bind);
258 puke "Unsupported data type specified to \$sql->update"
259 unless ref $data eq 'HASH';
261 for my $k (sort keys %$data) {
264 my $label = $self->_quote($k);
266 $self->_SWITCH_refkind($v, {
268 if ($self->{array_datatypes}) { # array datatype
269 push @set, "$label = ?";
270 push @all_bind, $self->_bindtype($k, $v);
272 else { # literal SQL with bind
273 my ($sql, @bind) = @$v;
274 $self->_assert_bindval_matches_bindtype(@bind);
275 push @set, "$label = $sql";
276 push @all_bind, @bind;
279 ARRAYREFREF => sub { # literal SQL with bind
280 my ($sql, @bind) = @${$v};
281 $self->_assert_bindval_matches_bindtype(@bind);
282 push @set, "$label = $sql";
283 push @all_bind, @bind;
285 SCALARREF => sub { # literal SQL without bind
286 push @set, "$label = $$v";
289 my ($op, $arg, @rest) = %$v;
291 puke 'Operator calls in update must be in the form { -op => $arg }'
292 if (@rest or not $op =~ /^\-(.+)/);
294 local $self->{_nested_func_lhs} = $k;
295 my ($sql, @bind) = $self->_where_unary_op ($1, $arg);
297 push @set, "$label = $sql";
298 push @all_bind, @bind;
300 SCALAR_or_UNDEF => sub {
301 push @set, "$label = ?";
302 push @all_bind, $self->_bindtype($k, $v);
308 my $sql = $self->_sqlcase('update') . " $table " . $self->_sqlcase('set ')
312 my($where_sql, @where_bind) = $self->where($where);
314 push @all_bind, @where_bind;
317 return wantarray ? ($sql, @all_bind) : $sql;
323 #======================================================================
325 #======================================================================
330 my $table = $self->_table(shift);
331 my $fields = shift || '*';
335 my($where_sql, @bind) = $self->where($where, $order);
337 my $f = (ref $fields eq 'ARRAY') ? join ', ', map { $self->_quote($_) } @$fields
339 my $sql = join(' ', $self->_sqlcase('select'), $f,
340 $self->_sqlcase('from'), $table)
343 return wantarray ? ($sql, @bind) : $sql;
346 #======================================================================
348 #======================================================================
353 my $table = $self->_table(shift);
357 my($where_sql, @bind) = $self->where($where);
358 my $sql = $self->_sqlcase('delete from') . " $table" . $where_sql;
360 return wantarray ? ($sql, @bind) : $sql;
364 #======================================================================
366 #======================================================================
370 # Finally, a separate routine just to handle WHERE clauses
372 my ($self, $where, $order) = @_;
375 my ($sql, @bind) = $self->_recurse_where($where);
376 $sql = $sql ? $self->_sqlcase(' where ') . "( $sql )" : '';
380 $sql .= $self->_order_by($order);
383 return wantarray ? ($sql, @bind) : $sql;
388 my ($self, $where, $logic) = @_;
390 # dispatch on appropriate method according to refkind of $where
391 my $method = $self->_METHOD_FOR_refkind("_where", $where);
393 my ($sql, @bind) = $self->$method($where, $logic);
395 # DBIx::Class directly calls _recurse_where in scalar context, so
396 # we must implement it, even if not in the official API
397 return wantarray ? ($sql, @bind) : $sql;
402 #======================================================================
403 # WHERE: top-level ARRAYREF
404 #======================================================================
407 sub _where_ARRAYREF {
408 my ($self, $where, $logic) = @_;
410 $logic = uc($logic || $self->{logic});
411 $logic eq 'AND' or $logic eq 'OR' or puke "unknown logic: $logic";
413 my @clauses = @$where;
415 my (@sql_clauses, @all_bind);
416 # need to use while() so can shift() for pairs
417 while (my $el = shift @clauses) {
419 # switch according to kind of $el and get corresponding ($sql, @bind)
420 my ($sql, @bind) = $self->_SWITCH_refkind($el, {
422 # skip empty elements, otherwise get invalid trailing AND stuff
423 ARRAYREF => sub {$self->_recurse_where($el) if @$el},
427 $self->_assert_bindval_matches_bindtype(@b);
431 HASHREF => sub {$self->_recurse_where($el, 'and') if %$el},
432 # LDNOTE : previous SQLA code for hashrefs was creating a dirty
433 # side-effect: the first hashref within an array would change
434 # the global logic to 'AND'. So [ {cond1, cond2}, [cond3, cond4] ]
435 # was interpreted as "(cond1 AND cond2) OR (cond3 AND cond4)",
436 # whereas it should be "(cond1 AND cond2) OR (cond3 OR cond4)".
438 SCALARREF => sub { ($$el); },
440 SCALAR => sub {# top-level arrayref with scalars, recurse in pairs
441 $self->_recurse_where({$el => shift(@clauses)})},
443 UNDEF => sub {puke "not supported : UNDEF in arrayref" },
447 push @sql_clauses, $sql;
448 push @all_bind, @bind;
452 return $self->_join_sql_clauses($logic, \@sql_clauses, \@all_bind);
455 #======================================================================
456 # WHERE: top-level ARRAYREFREF
457 #======================================================================
459 sub _where_ARRAYREFREF {
460 my ($self, $where) = @_;
461 my ($sql, @bind) = @$$where;
462 $self->_assert_bindval_matches_bindtype(@bind);
463 return ($sql, @bind);
466 #======================================================================
467 # WHERE: top-level HASHREF
468 #======================================================================
471 my ($self, $where) = @_;
472 my (@sql_clauses, @all_bind);
474 for my $k (sort keys %$where) {
475 my $v = $where->{$k};
477 # ($k => $v) is either a special unary op or a regular hashpair
478 my ($sql, @bind) = do {
480 # put the operator in canonical form
482 $op = substr $op, 1; # remove initial dash
483 $op =~ s/^\s+|\s+$//g;# remove leading/trailing space
484 $op =~ s/\s+/ /g; # compress whitespace
486 # so that -not_foo works correctly
487 $op =~ s/^not_/NOT /i;
489 $self->_debug("Unary OP(-$op) within hashref, recursing...");
490 my ($s, @b) = $self->_where_unary_op ($op, $v);
492 # top level vs nested
493 # we assume that handled unary ops will take care of their ()s
495 List::Util::first {$op =~ $_->{regex}} @{$self->{unary_ops}}
497 defined($self->{_nested_func_lhs}) && ($self->{_nested_func_lhs} eq $k)
502 my $method = $self->_METHOD_FOR_refkind("_where_hashpair", $v);
503 $self->$method($k, $v);
507 push @sql_clauses, $sql;
508 push @all_bind, @bind;
511 return $self->_join_sql_clauses('and', \@sql_clauses, \@all_bind);
514 sub _where_unary_op {
515 my ($self, $op, $rhs) = @_;
517 if (my $op_entry = List::Util::first {$op =~ $_->{regex}} @{$self->{unary_ops}}) {
518 my $handler = $op_entry->{handler};
520 if (not ref $handler) {
521 if ($op =~ s/ [_\s]? \d+ $//x ) {
522 belch 'Use of [and|or|nest]_N modifiers is deprecated and will be removed in SQLA v2.0. '
523 . "You probably wanted ...-and => [ -$op => COND1, -$op => COND2 ... ]";
525 return $self->$handler ($op, $rhs);
527 elsif (ref $handler eq 'CODE') {
528 return $handler->($self, $op, $rhs);
531 puke "Illegal handler for operator $op - expecting a method name or a coderef";
535 $self->debug("Generic unary OP: $op - recursing as function");
537 my ($sql, @bind) = $self->_SWITCH_refkind ($rhs, {
539 puke "Illegal use of top-level '$op'"
540 unless $self->{_nested_func_lhs};
543 $self->_convert('?'),
544 $self->_bindtype($self->{_nested_func_lhs}, $rhs)
548 $self->_recurse_where ($rhs)
552 $sql = sprintf ('%s %s',
553 $self->_sqlcase($op),
557 return ($sql, @bind);
560 sub _where_op_ANDOR {
561 my ($self, $op, $v) = @_;
563 $self->_SWITCH_refkind($v, {
565 return $self->_where_ARRAYREF($v, $op);
569 return ( $op =~ /^or/i )
570 ? $self->_where_ARRAYREF( [ map { $_ => $v->{$_} } ( sort keys %$v ) ], $op )
571 : $self->_where_HASHREF($v);
575 puke "-$op => \\\$scalar makes little sense, use " .
577 ? '[ \$scalar, \%rest_of_conditions ] instead'
578 : '-and => [ \$scalar, \%rest_of_conditions ] instead'
583 puke "-$op => \\[...] makes little sense, use " .
585 ? '[ \[...], \%rest_of_conditions ] instead'
586 : '-and => [ \[...], \%rest_of_conditions ] instead'
590 SCALAR => sub { # permissively interpreted as SQL
591 puke "-$op => \$value makes little sense, use -bool => \$value instead";
595 puke "-$op => undef not supported";
601 my ($self, $op, $v) = @_;
603 $self->_SWITCH_refkind($v, {
605 SCALAR => sub { # permissively interpreted as SQL
606 belch "literal SQL should be -nest => \\'scalar' "
607 . "instead of -nest => 'scalar' ";
612 puke "-$op => undef not supported";
616 $self->_recurse_where ($v);
624 my ($self, $op, $v) = @_;
626 my ($s, @b) = $self->_SWITCH_refkind($v, {
627 SCALAR => sub { # interpreted as SQL column
628 $self->_convert($self->_quote($v));
632 puke "-$op => undef not supported";
636 $self->_recurse_where ($v);
640 $s = "(NOT $s)" if $op =~ /^not/i;
645 sub _where_hashpair_ARRAYREF {
646 my ($self, $k, $v) = @_;
649 my @v = @$v; # need copy because of shift below
650 $self->_debug("ARRAY($k) means distribute over elements");
652 # put apart first element if it is an operator (-and, -or)
654 (defined $v[0] && $v[0] =~ /^ - (?: AND|OR ) $/ix)
658 my @distributed = map { {$k => $_} } @v;
661 $self->_debug("OP($op) reinjected into the distributed array");
662 unshift @distributed, $op;
665 my $logic = $op ? substr($op, 1) : '';
667 return $self->_recurse_where(\@distributed, $logic);
670 # LDNOTE : not sure of this one. What does "distribute over nothing" mean?
671 $self->_debug("empty ARRAY($k) means 0=1");
672 return ($self->{sqlfalse});
676 sub _where_hashpair_HASHREF {
677 my ($self, $k, $v, $logic) = @_;
680 local $self->{_nested_func_lhs} = $self->{_nested_func_lhs};
682 my ($all_sql, @all_bind);
684 for my $orig_op (sort keys %$v) {
685 my $val = $v->{$orig_op};
687 # put the operator in canonical form
690 # FIXME - we need to phase out dash-less ops
691 $op =~ s/^-//; # remove possible initial dash
692 $op =~ s/^\s+|\s+$//g;# remove leading/trailing space
693 $op =~ s/\s+/ /g; # compress whitespace
695 # so that -not_foo works correctly
696 $op =~ s/^not_/NOT /i;
700 # CASE: col-value logic modifiers
701 if ( $orig_op =~ /^ \- (and|or) $/xi ) {
702 ($sql, @bind) = $self->_where_hashpair_HASHREF($k, $val, $1);
704 # CASE: special operators like -in or -between
705 elsif ( my $special_op = List::Util::first {$op =~ $_->{regex}} @{$self->{special_ops}} ) {
706 my $handler = $special_op->{handler};
708 puke "No handler supplied for special operator $orig_op";
710 elsif (not ref $handler) {
711 ($sql, @bind) = $self->$handler ($k, $op, $val);
713 elsif (ref $handler eq 'CODE') {
714 ($sql, @bind) = $handler->($self, $k, $op, $val);
717 puke "Illegal handler for special operator $orig_op - expecting a method name or a coderef";
721 $self->_SWITCH_refkind($val, {
723 ARRAYREF => sub { # CASE: col => {op => \@vals}
724 ($sql, @bind) = $self->_where_field_op_ARRAYREF($k, $op, $val);
727 ARRAYREFREF => sub { # CASE: col => {op => \[$sql, @bind]} (literal SQL with bind)
728 my ($sub_sql, @sub_bind) = @$$val;
729 $self->_assert_bindval_matches_bindtype(@sub_bind);
730 $sql = join ' ', $self->_convert($self->_quote($k)),
731 $self->_sqlcase($op),
736 UNDEF => sub { # CASE: col => {op => undef} : sql "IS (NOT)? NULL"
737 my $is = ($op =~ $self->{equality_op}) ? 'is' :
738 ($op =~ $self->{inequality_op}) ? 'is not' :
739 puke "unexpected operator '$orig_op' with undef operand";
740 $sql = $self->_quote($k) . $self->_sqlcase(" $is null");
743 FALLBACK => sub { # CASE: col => {op/func => $stuff}
745 # retain for proper column type bind
746 $self->{_nested_func_lhs} ||= $k;
748 ($sql, @bind) = $self->_where_unary_op ($op, $val);
751 $self->_convert($self->_quote($k)),
752 $self->{_nested_func_lhs} eq $k ? $sql : "($sql)", # top level vs nested
758 ($all_sql) = (defined $all_sql and $all_sql) ? $self->_join_sql_clauses($logic, [$all_sql, $sql], []) : $sql;
759 push @all_bind, @bind;
761 return ($all_sql, @all_bind);
766 sub _where_field_op_ARRAYREF {
767 my ($self, $k, $op, $vals) = @_;
769 my @vals = @$vals; #always work on a copy
772 $self->_debug(sprintf '%s means multiple elements: [ %s ]',
774 join (', ', map { defined $_ ? "'$_'" : 'NULL' } @vals ),
777 # see if the first element is an -and/-or op
779 if (defined $vals[0] && $vals[0] =~ /^ - ( AND|OR ) $/ix) {
784 # distribute $op over each remaining member of @vals, append logic if exists
785 return $self->_recurse_where([map { {$k => {$op, $_}} } @vals], $logic);
787 # LDNOTE : had planned to change the distribution logic when
788 # $op =~ $self->{inequality_op}, because of Morgan laws :
789 # with {field => {'!=' => [22, 33]}}, it would be ridiculous to generate
790 # WHERE field != 22 OR field != 33 : the user probably means
791 # WHERE field != 22 AND field != 33.
792 # To do this, replace the above to roughly :
793 # my $logic = ($op =~ $self->{inequality_op}) ? 'AND' : 'OR';
794 # return $self->_recurse_where([map { {$k => {$op, $_}} } @vals], $logic);
798 # try to DWIM on equality operators
799 # LDNOTE : not 100% sure this is the correct thing to do ...
800 return ($self->{sqlfalse}) if $op =~ $self->{equality_op};
801 return ($self->{sqltrue}) if $op =~ $self->{inequality_op};
804 puke "operator '$op' applied on an empty array (field '$k')";
809 sub _where_hashpair_SCALARREF {
810 my ($self, $k, $v) = @_;
811 $self->_debug("SCALAR($k) means literal SQL: $$v");
812 my $sql = $self->_quote($k) . " " . $$v;
816 # literal SQL with bind
817 sub _where_hashpair_ARRAYREFREF {
818 my ($self, $k, $v) = @_;
819 $self->_debug("REF($k) means literal SQL: @${$v}");
820 my ($sql, @bind) = @$$v;
821 $self->_assert_bindval_matches_bindtype(@bind);
822 $sql = $self->_quote($k) . " " . $sql;
823 return ($sql, @bind );
826 # literal SQL without bind
827 sub _where_hashpair_SCALAR {
828 my ($self, $k, $v) = @_;
829 $self->_debug("NOREF($k) means simple key=val: $k $self->{cmp} $v");
830 my $sql = join ' ', $self->_convert($self->_quote($k)),
831 $self->_sqlcase($self->{cmp}),
832 $self->_convert('?');
833 my @bind = $self->_bindtype($k, $v);
834 return ( $sql, @bind);
838 sub _where_hashpair_UNDEF {
839 my ($self, $k, $v) = @_;
840 $self->_debug("UNDEF($k) means IS NULL");
841 my $sql = $self->_quote($k) . $self->_sqlcase(' is null');
845 #======================================================================
846 # WHERE: TOP-LEVEL OTHERS (SCALARREF, SCALAR, UNDEF)
847 #======================================================================
850 sub _where_SCALARREF {
851 my ($self, $where) = @_;
854 $self->_debug("SCALAR(*top) means literal SQL: $$where");
860 my ($self, $where) = @_;
863 $self->_debug("NOREF(*top) means literal SQL: $where");
874 #======================================================================
875 # WHERE: BUILTIN SPECIAL OPERATORS (-in, -between)
876 #======================================================================
879 sub _where_field_BETWEEN {
880 my ($self, $k, $op, $vals) = @_;
882 my ($label, $and, $placeholder);
883 $label = $self->_convert($self->_quote($k));
884 $and = ' ' . $self->_sqlcase('and') . ' ';
885 $placeholder = $self->_convert('?');
886 $op = $self->_sqlcase($op);
888 my ($clause, @bind) = $self->_SWITCH_refkind($vals, {
890 my ($s, @b) = @$$vals;
891 $self->_assert_bindval_matches_bindtype(@b);
898 puke "special op 'between' accepts an arrayref with exactly two values"
901 my (@all_sql, @all_bind);
902 foreach my $val (@$vals) {
903 my ($sql, @bind) = $self->_SWITCH_refkind($val, {
905 return ($placeholder, $self->_bindtype($k, $val) );
911 my ($sql, @bind) = @$$val;
912 $self->_assert_bindval_matches_bindtype(@bind);
913 return ($sql, @bind);
916 my ($func, $arg, @rest) = %$val;
917 puke ("Only simple { -func => arg } functions accepted as sub-arguments to BETWEEN")
918 if (@rest or $func !~ /^ \- (.+)/x);
919 local $self->{_nested_func_lhs} = $k;
920 $self->_where_unary_op ($1 => $arg);
924 push @all_bind, @bind;
928 (join $and, @all_sql),
933 puke "special op 'between' accepts an arrayref with two values, or a single literal scalarref/arrayref-ref";
937 my $sql = "( $label $op $clause )";
942 sub _where_field_IN {
943 my ($self, $k, $op, $vals) = @_;
945 # backwards compatibility : if scalar, force into an arrayref
946 $vals = [$vals] if defined $vals && ! ref $vals;
948 my ($label) = $self->_convert($self->_quote($k));
949 my ($placeholder) = $self->_convert('?');
950 $op = $self->_sqlcase($op);
952 my ($sql, @bind) = $self->_SWITCH_refkind($vals, {
953 ARRAYREF => sub { # list of choices
954 if (@$vals) { # nonempty list
955 my (@all_sql, @all_bind);
957 for my $val (@$vals) {
958 my ($sql, @bind) = $self->_SWITCH_refkind($val, {
960 return ($placeholder, $val);
966 my ($sql, @bind) = @$$val;
967 $self->_assert_bindval_matches_bindtype(@bind);
968 return ($sql, @bind);
971 my ($func, $arg, @rest) = %$val;
972 puke ("Only simple { -func => arg } functions accepted as sub-arguments to IN")
973 if (@rest or $func !~ /^ \- (.+)/x);
974 local $self->{_nested_func_lhs} = $k;
975 $self->_where_unary_op ($1 => $arg);
978 return $self->_sqlcase('null');
982 push @all_bind, @bind;
986 sprintf ('%s %s ( %s )',
989 join (', ', @all_sql)
991 $self->_bindtype($k, @all_bind),
994 else { # empty list : some databases won't understand "IN ()", so DWIM
995 my $sql = ($op =~ /\bnot\b/i) ? $self->{sqltrue} : $self->{sqlfalse};
1000 SCALARREF => sub { # literal SQL
1001 my $sql = $self->_open_outer_paren ($$vals);
1002 return ("$label $op ( $sql )");
1004 ARRAYREFREF => sub { # literal SQL with bind
1005 my ($sql, @bind) = @$$vals;
1006 $self->_assert_bindval_matches_bindtype(@bind);
1007 $sql = $self->_open_outer_paren ($sql);
1008 return ("$label $op ( $sql )", @bind);
1012 puke "special op 'in' requires an arrayref (or scalarref/arrayref-ref)";
1016 return ($sql, @bind);
1019 # Some databases (SQLite) treat col IN (1, 2) different from
1020 # col IN ( (1, 2) ). Use this to strip all outer parens while
1021 # adding them back in the corresponding method
1022 sub _open_outer_paren {
1023 my ($self, $sql) = @_;
1024 $sql = $1 while $sql =~ /^ \s* \( (.*) \) \s* $/xs;
1029 #======================================================================
1031 #======================================================================
1034 my ($self, $arg) = @_;
1037 for my $c ($self->_order_by_chunks ($arg) ) {
1038 $self->_SWITCH_refkind ($c, {
1039 SCALAR => sub { push @sql, $c },
1040 ARRAYREF => sub { push @sql, shift @$c; push @bind, @$c },
1046 $self->_sqlcase(' order by'),
1052 return wantarray ? ($sql, @bind) : $sql;
1055 sub _order_by_chunks {
1056 my ($self, $arg) = @_;
1058 return $self->_SWITCH_refkind($arg, {
1061 map { $self->_order_by_chunks ($_ ) } @$arg;
1064 ARRAYREFREF => sub {
1065 my ($s, @b) = @$$arg;
1066 $self->_assert_bindval_matches_bindtype(@b);
1070 SCALAR => sub {$self->_quote($arg)},
1072 UNDEF => sub {return () },
1074 SCALARREF => sub {$$arg}, # literal SQL, no quoting
1077 # get first pair in hash
1078 my ($key, $val, @rest) = %$arg;
1080 return () unless $key;
1082 if ( @rest or not $key =~ /^-(desc|asc)/i ) {
1083 puke "hash passed to _order_by must have exactly one key (-desc or -asc)";
1089 for my $c ($self->_order_by_chunks ($val)) {
1092 $self->_SWITCH_refkind ($c, {
1097 ($sql, @bind) = @$c;
1101 $sql = $sql . ' ' . $self->_sqlcase($direction);
1103 push @ret, [ $sql, @bind];
1112 #======================================================================
1113 # DATASOURCE (FOR NOW, JUST PLAIN TABLE OR LIST OF TABLES)
1114 #======================================================================
1119 $self->_SWITCH_refkind($from, {
1120 ARRAYREF => sub {join ', ', map { $self->_quote($_) } @$from;},
1121 SCALAR => sub {$self->_quote($from)},
1122 SCALARREF => sub {$$from},
1123 ARRAYREFREF => sub {join ', ', @$from;},
1128 #======================================================================
1130 #======================================================================
1132 # highly optimized, as it's called way too often
1134 # my ($self, $label) = @_;
1136 return '' unless defined $_[1];
1137 return ${$_[1]} if ref($_[1]) eq 'SCALAR';
1139 return $_[1] unless $_[0]->{quote_char};
1141 my $qref = ref $_[0]->{quote_char};
1144 ($l, $r) = ( $_[0]->{quote_char}, $_[0]->{quote_char} );
1146 elsif ($qref eq 'ARRAY') {
1147 ($l, $r) = @{$_[0]->{quote_char}};
1150 puke "Unsupported quote_char format: $_[0]->{quote_char}";
1153 # parts containing * are naturally unquoted
1154 return join( $_[0]->{name_sep}||'', map
1155 { $_ eq '*' ? $_ : $l . $_ . $r }
1156 ( $_[0]->{name_sep} ? split (/\Q$_[0]->{name_sep}\E/, $_[1] ) : $_[1] )
1161 # Conversion, if applicable
1163 #my ($self, $arg) = @_;
1165 # LDNOTE : modified the previous implementation below because
1166 # it was not consistent : the first "return" is always an array,
1167 # the second "return" is context-dependent. Anyway, _convert
1168 # seems always used with just a single argument, so make it a
1170 # return @_ unless $self->{convert};
1171 # my $conv = $self->_sqlcase($self->{convert});
1172 # my @ret = map { $conv.'('.$_.')' } @_;
1173 # return wantarray ? @ret : $ret[0];
1174 if ($_[0]->{convert}) {
1175 return $_[0]->_sqlcase($_[0]->{convert}) .'(' . $_[1] . ')';
1182 #my ($self, $col, @vals) = @_;
1184 #LDNOTE : changed original implementation below because it did not make
1185 # sense when bindtype eq 'columns' and @vals > 1.
1186 # return $self->{bindtype} eq 'columns' ? [ $col, @vals ] : @vals;
1188 # called often - tighten code
1189 return $_[0]->{bindtype} eq 'columns'
1190 ? map {[$_[1], $_]} @_[2 .. $#_]
1195 # Dies if any element of @bind is not in [colname => value] format
1196 # if bindtype is 'columns'.
1197 sub _assert_bindval_matches_bindtype {
1198 # my ($self, @bind) = @_;
1200 if ($self->{bindtype} eq 'columns') {
1202 if (!defined $_ || ref($_) ne 'ARRAY' || @$_ != 2) {
1203 puke "bindtype 'columns' selected, you need to pass: [column_name => bind_value]"
1209 sub _join_sql_clauses {
1210 my ($self, $logic, $clauses_aref, $bind_aref) = @_;
1212 if (@$clauses_aref > 1) {
1213 my $join = " " . $self->_sqlcase($logic) . " ";
1214 my $sql = '( ' . join($join, @$clauses_aref) . ' )';
1215 return ($sql, @$bind_aref);
1217 elsif (@$clauses_aref) {
1218 return ($clauses_aref->[0], @$bind_aref); # no parentheses
1221 return (); # if no SQL, ignore @$bind_aref
1226 # Fix SQL case, if so requested
1228 # LDNOTE: if $self->{case} is true, then it contains 'lower', so we
1229 # don't touch the argument ... crooked logic, but let's not change it!
1230 return $_[0]->{case} ? $_[1] : uc($_[1]);
1234 #======================================================================
1235 # DISPATCHING FROM REFKIND
1236 #======================================================================
1239 my ($self, $data) = @_;
1241 return 'UNDEF' unless defined $data;
1243 # blessed objects are treated like scalars
1244 my $ref = (Scalar::Util::blessed $data) ? '' : ref $data;
1246 return 'SCALAR' unless $ref;
1249 while ($ref eq 'REF') {
1251 $ref = (Scalar::Util::blessed $data) ? '' : ref $data;
1255 return ($ref||'SCALAR') . ('REF' x $n_steps);
1259 my ($self, $data) = @_;
1260 my @try = ($self->_refkind($data));
1261 push @try, 'SCALAR_or_UNDEF' if $try[0] eq 'SCALAR' || $try[0] eq 'UNDEF';
1262 push @try, 'FALLBACK';
1266 sub _METHOD_FOR_refkind {
1267 my ($self, $meth_prefix, $data) = @_;
1270 for (@{$self->_try_refkind($data)}) {
1271 $method = $self->can($meth_prefix."_".$_)
1275 return $method || puke "cannot dispatch on '$meth_prefix' for ".$self->_refkind($data);
1279 sub _SWITCH_refkind {
1280 my ($self, $data, $dispatch_table) = @_;
1283 for (@{$self->_try_refkind($data)}) {
1284 $coderef = $dispatch_table->{$_}
1288 puke "no dispatch entry for ".$self->_refkind($data)
1297 #======================================================================
1298 # VALUES, GENERATE, AUTOLOAD
1299 #======================================================================
1301 # LDNOTE: original code from nwiger, didn't touch code in that section
1302 # I feel the AUTOLOAD stuff should not be the default, it should
1303 # only be activated on explicit demand by user.
1307 my $data = shift || return;
1308 puke "Argument to ", __PACKAGE__, "->values must be a \\%hash"
1309 unless ref $data eq 'HASH';
1312 foreach my $k ( sort keys %$data ) {
1313 my $v = $data->{$k};
1314 $self->_SWITCH_refkind($v, {
1316 if ($self->{array_datatypes}) { # array datatype
1317 push @all_bind, $self->_bindtype($k, $v);
1319 else { # literal SQL with bind
1320 my ($sql, @bind) = @$v;
1321 $self->_assert_bindval_matches_bindtype(@bind);
1322 push @all_bind, @bind;
1325 ARRAYREFREF => sub { # literal SQL with bind
1326 my ($sql, @bind) = @${$v};
1327 $self->_assert_bindval_matches_bindtype(@bind);
1328 push @all_bind, @bind;
1330 SCALARREF => sub { # literal SQL without bind
1332 SCALAR_or_UNDEF => sub {
1333 push @all_bind, $self->_bindtype($k, $v);
1344 my(@sql, @sqlq, @sqlv);
1348 if ($ref eq 'HASH') {
1349 for my $k (sort keys %$_) {
1352 my $label = $self->_quote($k);
1353 if ($r eq 'ARRAY') {
1354 # literal SQL with bind
1355 my ($sql, @bind) = @$v;
1356 $self->_assert_bindval_matches_bindtype(@bind);
1357 push @sqlq, "$label = $sql";
1359 } elsif ($r eq 'SCALAR') {
1360 # literal SQL without bind
1361 push @sqlq, "$label = $$v";
1363 push @sqlq, "$label = ?";
1364 push @sqlv, $self->_bindtype($k, $v);
1367 push @sql, $self->_sqlcase('set'), join ', ', @sqlq;
1368 } elsif ($ref eq 'ARRAY') {
1369 # unlike insert(), assume these are ONLY the column names, i.e. for SQL
1372 if ($r eq 'ARRAY') { # literal SQL with bind
1373 my ($sql, @bind) = @$v;
1374 $self->_assert_bindval_matches_bindtype(@bind);
1377 } elsif ($r eq 'SCALAR') { # literal SQL without bind
1378 # embedded literal SQL
1385 push @sql, '(' . join(', ', @sqlq) . ')';
1386 } elsif ($ref eq 'SCALAR') {
1390 # strings get case twiddled
1391 push @sql, $self->_sqlcase($_);
1395 my $sql = join ' ', @sql;
1397 # this is pretty tricky
1398 # if ask for an array, return ($stmt, @bind)
1399 # otherwise, s/?/shift @sqlv/ to put it inline
1401 return ($sql, @sqlv);
1403 1 while $sql =~ s/\?/my $d = shift(@sqlv);
1404 ref $d ? $d->[1] : $d/e;
1413 # This allows us to check for a local, then _form, attr
1415 my($name) = $AUTOLOAD =~ /.*::(.+)/;
1416 return $self->generate($name, @_);
1427 SQL::Abstract - Generate SQL from Perl data structures
1433 my $sql = SQL::Abstract->new;
1435 my($stmt, @bind) = $sql->select($table, \@fields, \%where, \@order);
1437 my($stmt, @bind) = $sql->insert($table, \%fieldvals || \@values);
1439 my($stmt, @bind) = $sql->update($table, \%fieldvals, \%where);
1441 my($stmt, @bind) = $sql->delete($table, \%where);
1443 # Then, use these in your DBI statements
1444 my $sth = $dbh->prepare($stmt);
1445 $sth->execute(@bind);
1447 # Just generate the WHERE clause
1448 my($stmt, @bind) = $sql->where(\%where, \@order);
1450 # Return values in the same order, for hashed queries
1451 # See PERFORMANCE section for more details
1452 my @bind = $sql->values(\%fieldvals);
1456 This module was inspired by the excellent L<DBIx::Abstract>.
1457 However, in using that module I found that what I really wanted
1458 to do was generate SQL, but still retain complete control over my
1459 statement handles and use the DBI interface. So, I set out to
1460 create an abstract SQL generation module.
1462 While based on the concepts used by L<DBIx::Abstract>, there are
1463 several important differences, especially when it comes to WHERE
1464 clauses. I have modified the concepts used to make the SQL easier
1465 to generate from Perl data structures and, IMO, more intuitive.
1466 The underlying idea is for this module to do what you mean, based
1467 on the data structures you provide it. The big advantage is that
1468 you don't have to modify your code every time your data changes,
1469 as this module figures it out.
1471 To begin with, an SQL INSERT is as easy as just specifying a hash
1472 of C<key=value> pairs:
1475 name => 'Jimbo Bobson',
1476 phone => '123-456-7890',
1477 address => '42 Sister Lane',
1478 city => 'St. Louis',
1479 state => 'Louisiana',
1482 The SQL can then be generated with this:
1484 my($stmt, @bind) = $sql->insert('people', \%data);
1486 Which would give you something like this:
1488 $stmt = "INSERT INTO people
1489 (address, city, name, phone, state)
1490 VALUES (?, ?, ?, ?, ?)";
1491 @bind = ('42 Sister Lane', 'St. Louis', 'Jimbo Bobson',
1492 '123-456-7890', 'Louisiana');
1494 These are then used directly in your DBI code:
1496 my $sth = $dbh->prepare($stmt);
1497 $sth->execute(@bind);
1499 =head2 Inserting and Updating Arrays
1501 If your database has array types (like for example Postgres),
1502 activate the special option C<< array_datatypes => 1 >>
1503 when creating the C<SQL::Abstract> object.
1504 Then you may use an arrayref to insert and update database array types:
1506 my $sql = SQL::Abstract->new(array_datatypes => 1);
1508 planets => [qw/Mercury Venus Earth Mars/]
1511 my($stmt, @bind) = $sql->insert('solar_system', \%data);
1515 $stmt = "INSERT INTO solar_system (planets) VALUES (?)"
1517 @bind = (['Mercury', 'Venus', 'Earth', 'Mars']);
1520 =head2 Inserting and Updating SQL
1522 In order to apply SQL functions to elements of your C<%data> you may
1523 specify a reference to an arrayref for the given hash value. For example,
1524 if you need to execute the Oracle C<to_date> function on a value, you can
1525 say something like this:
1529 date_entered => \["to_date(?,'MM/DD/YYYY')", "03/02/2003"],
1532 The first value in the array is the actual SQL. Any other values are
1533 optional and would be included in the bind values array. This gives
1536 my($stmt, @bind) = $sql->insert('people', \%data);
1538 $stmt = "INSERT INTO people (name, date_entered)
1539 VALUES (?, to_date(?,'MM/DD/YYYY'))";
1540 @bind = ('Bill', '03/02/2003');
1542 An UPDATE is just as easy, all you change is the name of the function:
1544 my($stmt, @bind) = $sql->update('people', \%data);
1546 Notice that your C<%data> isn't touched; the module will generate
1547 the appropriately quirky SQL for you automatically. Usually you'll
1548 want to specify a WHERE clause for your UPDATE, though, which is
1549 where handling C<%where> hashes comes in handy...
1551 =head2 Complex where statements
1553 This module can generate pretty complicated WHERE statements
1554 easily. For example, simple C<key=value> pairs are taken to mean
1555 equality, and if you want to see if a field is within a set
1556 of values, you can use an arrayref. Let's say we wanted to
1557 SELECT some data based on this criteria:
1560 requestor => 'inna',
1561 worker => ['nwiger', 'rcwe', 'sfz'],
1562 status => { '!=', 'completed' }
1565 my($stmt, @bind) = $sql->select('tickets', '*', \%where);
1567 The above would give you something like this:
1569 $stmt = "SELECT * FROM tickets WHERE
1570 ( requestor = ? ) AND ( status != ? )
1571 AND ( worker = ? OR worker = ? OR worker = ? )";
1572 @bind = ('inna', 'completed', 'nwiger', 'rcwe', 'sfz');
1574 Which you could then use in DBI code like so:
1576 my $sth = $dbh->prepare($stmt);
1577 $sth->execute(@bind);
1583 The functions are simple. There's one for each major SQL operation,
1584 and a constructor you use first. The arguments are specified in a
1585 similar order to each function (table, then fields, then a where
1586 clause) to try and simplify things.
1591 =head2 new(option => 'value')
1593 The C<new()> function takes a list of options and values, and returns
1594 a new B<SQL::Abstract> object which can then be used to generate SQL
1595 through the methods below. The options accepted are:
1601 If set to 'lower', then SQL will be generated in all lowercase. By
1602 default SQL is generated in "textbook" case meaning something like:
1604 SELECT a_field FROM a_table WHERE some_field LIKE '%someval%'
1606 Any setting other than 'lower' is ignored.
1610 This determines what the default comparison operator is. By default
1611 it is C<=>, meaning that a hash like this:
1613 %where = (name => 'nwiger', email => 'nate@wiger.org');
1615 Will generate SQL like this:
1617 WHERE name = 'nwiger' AND email = 'nate@wiger.org'
1619 However, you may want loose comparisons by default, so if you set
1620 C<cmp> to C<like> you would get SQL such as:
1622 WHERE name like 'nwiger' AND email like 'nate@wiger.org'
1624 You can also override the comparsion on an individual basis - see
1625 the huge section on L</"WHERE CLAUSES"> at the bottom.
1627 =item sqltrue, sqlfalse
1629 Expressions for inserting boolean values within SQL statements.
1630 By default these are C<1=1> and C<1=0>. They are used
1631 by the special operators C<-in> and C<-not_in> for generating
1632 correct SQL even when the argument is an empty array (see below).
1636 This determines the default logical operator for multiple WHERE
1637 statements in arrays or hashes. If absent, the default logic is "or"
1638 for arrays, and "and" for hashes. This means that a WHERE
1642 event_date => {'>=', '2/13/99'},
1643 event_date => {'<=', '4/24/03'},
1646 will generate SQL like this:
1648 WHERE event_date >= '2/13/99' OR event_date <= '4/24/03'
1650 This is probably not what you want given this query, though (look
1651 at the dates). To change the "OR" to an "AND", simply specify:
1653 my $sql = SQL::Abstract->new(logic => 'and');
1655 Which will change the above C<WHERE> to:
1657 WHERE event_date >= '2/13/99' AND event_date <= '4/24/03'
1659 The logic can also be changed locally by inserting
1660 a modifier in front of an arrayref :
1662 @where = (-and => [event_date => {'>=', '2/13/99'},
1663 event_date => {'<=', '4/24/03'} ]);
1665 See the L</"WHERE CLAUSES"> section for explanations.
1669 This will automatically convert comparisons using the specified SQL
1670 function for both column and value. This is mostly used with an argument
1671 of C<upper> or C<lower>, so that the SQL will have the effect of
1672 case-insensitive "searches". For example, this:
1674 $sql = SQL::Abstract->new(convert => 'upper');
1675 %where = (keywords => 'MaKe iT CAse inSeNSItive');
1677 Will turn out the following SQL:
1679 WHERE upper(keywords) like upper('MaKe iT CAse inSeNSItive')
1681 The conversion can be C<upper()>, C<lower()>, or any other SQL function
1682 that can be applied symmetrically to fields (actually B<SQL::Abstract> does
1683 not validate this option; it will just pass through what you specify verbatim).
1687 This is a kludge because many databases suck. For example, you can't
1688 just bind values using DBI's C<execute()> for Oracle C<CLOB> or C<BLOB> fields.
1689 Instead, you have to use C<bind_param()>:
1691 $sth->bind_param(1, 'reg data');
1692 $sth->bind_param(2, $lots, {ora_type => ORA_CLOB});
1694 The problem is, B<SQL::Abstract> will normally just return a C<@bind> array,
1695 which loses track of which field each slot refers to. Fear not.
1697 If you specify C<bindtype> in new, you can determine how C<@bind> is returned.
1698 Currently, you can specify either C<normal> (default) or C<columns>. If you
1699 specify C<columns>, you will get an array that looks like this:
1701 my $sql = SQL::Abstract->new(bindtype => 'columns');
1702 my($stmt, @bind) = $sql->insert(...);
1705 [ 'column1', 'value1' ],
1706 [ 'column2', 'value2' ],
1707 [ 'column3', 'value3' ],
1710 You can then iterate through this manually, using DBI's C<bind_param()>.
1712 $sth->prepare($stmt);
1715 my($col, $data) = @$_;
1716 if ($col eq 'details' || $col eq 'comments') {
1717 $sth->bind_param($i, $data, {ora_type => ORA_CLOB});
1718 } elsif ($col eq 'image') {
1719 $sth->bind_param($i, $data, {ora_type => ORA_BLOB});
1721 $sth->bind_param($i, $data);
1725 $sth->execute; # execute without @bind now
1727 Now, why would you still use B<SQL::Abstract> if you have to do this crap?
1728 Basically, the advantage is still that you don't have to care which fields
1729 are or are not included. You could wrap that above C<for> loop in a simple
1730 sub called C<bind_fields()> or something and reuse it repeatedly. You still
1731 get a layer of abstraction over manual SQL specification.
1733 Note that if you set L</bindtype> to C<columns>, the C<\[$sql, @bind]>
1734 construct (see L</Literal SQL with placeholders and bind values (subqueries)>)
1735 will expect the bind values in this format.
1739 This is the character that a table or column name will be quoted
1740 with. By default this is an empty string, but you could set it to
1741 the character C<`>, to generate SQL like this:
1743 SELECT `a_field` FROM `a_table` WHERE `some_field` LIKE '%someval%'
1745 Alternatively, you can supply an array ref of two items, the first being the left
1746 hand quote character, and the second the right hand quote character. For
1747 example, you could supply C<['[',']']> for SQL Server 2000 compliant quotes
1748 that generates SQL like this:
1750 SELECT [a_field] FROM [a_table] WHERE [some_field] LIKE '%someval%'
1752 Quoting is useful if you have tables or columns names that are reserved
1753 words in your database's SQL dialect.
1757 This is the character that separates a table and column name. It is
1758 necessary to specify this when the C<quote_char> option is selected,
1759 so that tables and column names can be individually quoted like this:
1761 SELECT `table`.`one_field` FROM `table` WHERE `table`.`other_field` = 1
1763 =item array_datatypes
1765 When this option is true, arrayrefs in INSERT or UPDATE are
1766 interpreted as array datatypes and are passed directly
1768 When this option is false, arrayrefs are interpreted
1769 as literal SQL, just like refs to arrayrefs
1770 (but this behavior is for backwards compatibility; when writing
1771 new queries, use the "reference to arrayref" syntax
1777 Takes a reference to a list of "special operators"
1778 to extend the syntax understood by L<SQL::Abstract>.
1779 See section L</"SPECIAL OPERATORS"> for details.
1783 Takes a reference to a list of "unary operators"
1784 to extend the syntax understood by L<SQL::Abstract>.
1785 See section L</"UNARY OPERATORS"> for details.
1791 =head2 insert($table, \@values || \%fieldvals, \%options)
1793 This is the simplest function. You simply give it a table name
1794 and either an arrayref of values or hashref of field/value pairs.
1795 It returns an SQL INSERT statement and a list of bind values.
1796 See the sections on L</"Inserting and Updating Arrays"> and
1797 L</"Inserting and Updating SQL"> for information on how to insert
1798 with those data types.
1800 The optional C<\%options> hash reference may contain additional
1801 options to generate the insert SQL. Currently supported options
1808 Takes either a scalar of raw SQL fields, or an array reference of
1809 field names, and adds on an SQL C<RETURNING> statement at the end.
1810 This allows you to return data generated by the insert statement
1811 (such as row IDs) without performing another C<SELECT> statement.
1812 Note, however, this is not part of the SQL standard and may not
1813 be supported by all database engines.
1817 =head2 update($table, \%fieldvals, \%where)
1819 This takes a table, hashref of field/value pairs, and an optional
1820 hashref L<WHERE clause|/WHERE CLAUSES>. It returns an SQL UPDATE function and a list
1822 See the sections on L</"Inserting and Updating Arrays"> and
1823 L</"Inserting and Updating SQL"> for information on how to insert
1824 with those data types.
1826 =head2 select($source, $fields, $where, $order)
1828 This returns a SQL SELECT statement and associated list of bind values, as
1829 specified by the arguments :
1835 Specification of the 'FROM' part of the statement.
1836 The argument can be either a plain scalar (interpreted as a table
1837 name, will be quoted), or an arrayref (interpreted as a list
1838 of table names, joined by commas, quoted), or a scalarref
1839 (literal table name, not quoted), or a ref to an arrayref
1840 (list of literal table names, joined by commas, not quoted).
1844 Specification of the list of fields to retrieve from
1846 The argument can be either an arrayref (interpreted as a list
1847 of field names, will be joined by commas and quoted), or a
1848 plain scalar (literal SQL, not quoted).
1849 Please observe that this API is not as flexible as for
1850 the first argument C<$table>, for backwards compatibility reasons.
1854 Optional argument to specify the WHERE part of the query.
1855 The argument is most often a hashref, but can also be
1856 an arrayref or plain scalar --
1857 see section L<WHERE clause|/"WHERE CLAUSES"> for details.
1861 Optional argument to specify the ORDER BY part of the query.
1862 The argument can be a scalar, a hashref or an arrayref
1863 -- see section L<ORDER BY clause|/"ORDER BY CLAUSES">
1869 =head2 delete($table, \%where)
1871 This takes a table name and optional hashref L<WHERE clause|/WHERE CLAUSES>.
1872 It returns an SQL DELETE statement and list of bind values.
1874 =head2 where(\%where, \@order)
1876 This is used to generate just the WHERE clause. For example,
1877 if you have an arbitrary data structure and know what the
1878 rest of your SQL is going to look like, but want an easy way
1879 to produce a WHERE clause, use this. It returns an SQL WHERE
1880 clause and list of bind values.
1883 =head2 values(\%data)
1885 This just returns the values from the hash C<%data>, in the same
1886 order that would be returned from any of the other above queries.
1887 Using this allows you to markedly speed up your queries if you
1888 are affecting lots of rows. See below under the L</"PERFORMANCE"> section.
1890 =head2 generate($any, 'number', $of, \@data, $struct, \%types)
1892 Warning: This is an experimental method and subject to change.
1894 This returns arbitrarily generated SQL. It's a really basic shortcut.
1895 It will return two different things, depending on return context:
1897 my($stmt, @bind) = $sql->generate('create table', \$table, \@fields);
1898 my $stmt_and_val = $sql->generate('create table', \$table, \@fields);
1900 These would return the following:
1902 # First calling form
1903 $stmt = "CREATE TABLE test (?, ?)";
1904 @bind = (field1, field2);
1906 # Second calling form
1907 $stmt_and_val = "CREATE TABLE test (field1, field2)";
1909 Depending on what you're trying to do, it's up to you to choose the correct
1910 format. In this example, the second form is what you would want.
1914 $sql->generate('alter session', { nls_date_format => 'MM/YY' });
1918 ALTER SESSION SET nls_date_format = 'MM/YY'
1920 You get the idea. Strings get their case twiddled, but everything
1921 else remains verbatim.
1926 =head1 WHERE CLAUSES
1930 This module uses a variation on the idea from L<DBIx::Abstract>. It
1931 is B<NOT>, repeat I<not> 100% compatible. B<The main logic of this
1932 module is that things in arrays are OR'ed, and things in hashes
1935 The easiest way to explain is to show lots of examples. After
1936 each C<%where> hash shown, it is assumed you used:
1938 my($stmt, @bind) = $sql->where(\%where);
1940 However, note that the C<%where> hash can be used directly in any
1941 of the other functions as well, as described above.
1943 =head2 Key-value pairs
1945 So, let's get started. To begin, a simple hash:
1949 status => 'completed'
1952 Is converted to SQL C<key = val> statements:
1954 $stmt = "WHERE user = ? AND status = ?";
1955 @bind = ('nwiger', 'completed');
1957 One common thing I end up doing is having a list of values that
1958 a field can be in. To do this, simply specify a list inside of
1963 status => ['assigned', 'in-progress', 'pending'];
1966 This simple code will create the following:
1968 $stmt = "WHERE user = ? AND ( status = ? OR status = ? OR status = ? )";
1969 @bind = ('nwiger', 'assigned', 'in-progress', 'pending');
1971 A field associated to an empty arrayref will be considered a
1972 logical false and will generate 0=1.
1974 =head2 Tests for NULL values
1976 If the value part is C<undef> then this is converted to SQL <IS NULL>
1985 $stmt = "WHERE user = ? AND status IS NULL";
1988 =head2 Specific comparison operators
1990 If you want to specify a different type of operator for your comparison,
1991 you can use a hashref for a given column:
1995 status => { '!=', 'completed' }
1998 Which would generate:
2000 $stmt = "WHERE user = ? AND status != ?";
2001 @bind = ('nwiger', 'completed');
2003 To test against multiple values, just enclose the values in an arrayref:
2005 status => { '=', ['assigned', 'in-progress', 'pending'] };
2007 Which would give you:
2009 "WHERE status = ? OR status = ? OR status = ?"
2012 The hashref can also contain multiple pairs, in which case it is expanded
2013 into an C<AND> of its elements:
2017 status => { '!=', 'completed', -not_like => 'pending%' }
2020 # Or more dynamically, like from a form
2021 $where{user} = 'nwiger';
2022 $where{status}{'!='} = 'completed';
2023 $where{status}{'-not_like'} = 'pending%';
2025 # Both generate this
2026 $stmt = "WHERE user = ? AND status != ? AND status NOT LIKE ?";
2027 @bind = ('nwiger', 'completed', 'pending%');
2030 To get an OR instead, you can combine it with the arrayref idea:
2034 priority => [ {'=', 2}, {'!=', 1} ]
2037 Which would generate:
2039 $stmt = "WHERE user = ? AND priority = ? OR priority != ?";
2040 @bind = ('nwiger', '2', '1');
2042 If you want to include literal SQL (with or without bind values), just use a
2043 scalar reference or array reference as the value:
2046 date_entered => { '>' => \["to_date(?, 'MM/DD/YYYY')", "11/26/2008"] },
2047 date_expires => { '<' => \"now()" }
2050 Which would generate:
2052 $stmt = "WHERE date_entered > "to_date(?, 'MM/DD/YYYY') AND date_expires < now()";
2053 @bind = ('11/26/2008');
2056 =head2 Logic and nesting operators
2058 In the example above,
2059 there is a subtle trap if you want to say something like
2060 this (notice the C<AND>):
2062 WHERE priority != ? AND priority != ?
2064 Because, in Perl you I<can't> do this:
2066 priority => { '!=', 2, '!=', 1 }
2068 As the second C<!=> key will obliterate the first. The solution
2069 is to use the special C<-modifier> form inside an arrayref:
2071 priority => [ -and => {'!=', 2},
2075 Normally, these would be joined by C<OR>, but the modifier tells it
2076 to use C<AND> instead. (Hint: You can use this in conjunction with the
2077 C<logic> option to C<new()> in order to change the way your queries
2078 work by default.) B<Important:> Note that the C<-modifier> goes
2079 B<INSIDE> the arrayref, as an extra first element. This will
2080 B<NOT> do what you think it might:
2082 priority => -and => [{'!=', 2}, {'!=', 1}] # WRONG!
2084 Here is a quick list of equivalencies, since there is some overlap:
2087 status => {'!=', 'completed', 'not like', 'pending%' }
2088 status => [ -and => {'!=', 'completed'}, {'not like', 'pending%'}]
2091 status => {'=', ['assigned', 'in-progress']}
2092 status => [ -or => {'=', 'assigned'}, {'=', 'in-progress'}]
2093 status => [ {'=', 'assigned'}, {'=', 'in-progress'} ]
2097 =head2 Special operators : IN, BETWEEN, etc.
2099 You can also use the hashref format to compare a list of fields using the
2100 C<IN> comparison operator, by specifying the list as an arrayref:
2103 status => 'completed',
2104 reportid => { -in => [567, 2335, 2] }
2107 Which would generate:
2109 $stmt = "WHERE status = ? AND reportid IN (?,?,?)";
2110 @bind = ('completed', '567', '2335', '2');
2112 The reverse operator C<-not_in> generates SQL C<NOT IN> and is used in
2115 If the argument to C<-in> is an empty array, 'sqlfalse' is generated
2116 (by default : C<1=0>). Similarly, C<< -not_in => [] >> generates
2117 'sqltrue' (by default : C<1=1>).
2119 In addition to the array you can supply a chunk of literal sql or
2120 literal sql with bind:
2123 customer => { -in => \[
2124 'SELECT cust_id FROM cust WHERE balance > ?',
2127 status => { -in => \'SELECT status_codes FROM states' },
2133 customer IN ( SELECT cust_id FROM cust WHERE balance > ? )
2134 AND status IN ( SELECT status_codes FROM states )
2140 Another pair of operators is C<-between> and C<-not_between>,
2141 used with an arrayref of two values:
2145 completion_date => {
2146 -not_between => ['2002-10-01', '2003-02-06']
2152 WHERE user = ? AND completion_date NOT BETWEEN ( ? AND ? )
2154 Just like with C<-in> all plausible combinations of literal SQL
2158 start0 => { -between => [ 1, 2 ] },
2159 start1 => { -between => \["? AND ?", 1, 2] },
2160 start2 => { -between => \"lower(x) AND upper(y)" },
2161 start3 => { -between => [
2163 \["upper(?)", 'stuff' ],
2170 ( start0 BETWEEN ? AND ? )
2171 AND ( start1 BETWEEN ? AND ? )
2172 AND ( start2 BETWEEN lower(x) AND upper(y) )
2173 AND ( start3 BETWEEN lower(x) AND upper(?) )
2175 @bind = (1, 2, 1, 2, 'stuff');
2178 These are the two builtin "special operators"; but the
2179 list can be expanded : see section L</"SPECIAL OPERATORS"> below.
2181 =head2 Unary operators: bool
2183 If you wish to test against boolean columns or functions within your
2184 database you can use the C<-bool> and C<-not_bool> operators. For
2185 example to test the column C<is_user> being true and the column
2186 C<is_enabled> being false you would use:-
2190 -not_bool => 'is_enabled',
2195 WHERE is_user AND NOT is_enabled
2197 If a more complex combination is required, testing more conditions,
2198 then you should use the and/or operators:-
2205 -not_bool => 'four',
2211 WHERE one AND two AND three AND NOT four
2214 =head2 Nested conditions, -and/-or prefixes
2216 So far, we've seen how multiple conditions are joined with a top-level
2217 C<AND>. We can change this by putting the different conditions we want in
2218 hashes and then putting those hashes in an array. For example:
2223 status => { -like => ['pending%', 'dispatched'] },
2227 status => 'unassigned',
2231 This data structure would create the following:
2233 $stmt = "WHERE ( user = ? AND ( status LIKE ? OR status LIKE ? ) )
2234 OR ( user = ? AND status = ? ) )";
2235 @bind = ('nwiger', 'pending', 'dispatched', 'robot', 'unassigned');
2238 Clauses in hashrefs or arrayrefs can be prefixed with an C<-and> or C<-or>
2239 to change the logic inside :
2245 -and => [ workhrs => {'>', 20}, geo => 'ASIA' ],
2246 -or => { workhrs => {'<', 50}, geo => 'EURO' },
2253 WHERE ( user = ? AND (
2254 ( workhrs > ? AND geo = ? )
2255 OR ( workhrs < ? OR geo = ? )
2258 =head2 Algebraic inconsistency, for historical reasons
2260 C<Important note>: when connecting several conditions, the C<-and->|C<-or>
2261 operator goes C<outside> of the nested structure; whereas when connecting
2262 several constraints on one column, the C<-and> operator goes
2263 C<inside> the arrayref. Here is an example combining both features :
2266 -and => [a => 1, b => 2],
2267 -or => [c => 3, d => 4],
2268 e => [-and => {-like => 'foo%'}, {-like => '%bar'} ]
2273 WHERE ( ( ( a = ? AND b = ? )
2274 OR ( c = ? OR d = ? )
2275 OR ( e LIKE ? AND e LIKE ? ) ) )
2277 This difference in syntax is unfortunate but must be preserved for
2278 historical reasons. So be careful : the two examples below would
2279 seem algebraically equivalent, but they are not
2281 {col => [-and => {-like => 'foo%'}, {-like => '%bar'}]}
2282 # yields : WHERE ( ( col LIKE ? AND col LIKE ? ) )
2284 [-and => {col => {-like => 'foo%'}, {col => {-like => '%bar'}}]]
2285 # yields : WHERE ( ( col LIKE ? OR col LIKE ? ) )
2290 Finally, sometimes only literal SQL will do. If you want to include
2291 literal SQL verbatim, you can specify it as a scalar reference, namely:
2293 my $inn = 'is Not Null';
2295 priority => { '<', 2 },
2301 $stmt = "WHERE priority < ? AND requestor is Not Null";
2304 Note that in this example, you only get one bind parameter back, since
2305 the verbatim SQL is passed as part of the statement.
2307 Of course, just to prove a point, the above can also be accomplished
2311 priority => { '<', 2 },
2312 requestor => { '!=', undef },
2318 Conditions on boolean columns can be expressed in the same way, passing
2319 a reference to an empty string, however using liternal SQL in this way
2320 is deprecated - the preferred method is to use the boolean operators -
2321 see L</"Unary operators: bool"> :
2324 priority => { '<', 2 },
2330 $stmt = "WHERE priority < ? AND is_ready";
2333 Literal SQL is also the only way to compare 2 columns to one another:
2336 priority => { '<', 2 },
2337 requestor => \'= submittor'
2342 $stmt = "WHERE priority < ? AND requestor = submitter";
2345 =head2 Literal SQL with placeholders and bind values (subqueries)
2347 If the literal SQL to be inserted has placeholders and bind values,
2348 use a reference to an arrayref (yes this is a double reference --
2349 not so common, but perfectly legal Perl). For example, to find a date
2350 in Postgres you can use something like this:
2353 date_column => \[q/= date '2008-09-30' - ?::integer/, 10/]
2358 $stmt = "WHERE ( date_column = date '2008-09-30' - ?::integer )"
2361 Note that you must pass the bind values in the same format as they are returned
2362 by L</where>. That means that if you set L</bindtype> to C<columns>, you must
2363 provide the bind values in the C<< [ column_meta => value ] >> format, where
2364 C<column_meta> is an opaque scalar value; most commonly the column name, but
2365 you can use any scalar value (including references and blessed references),
2366 L<SQL::Abstract> will simply pass it through intact. So if C<bindtype> is set
2367 to C<columns> the above example will look like:
2370 date_column => \[q/= date '2008-09-30' - ?::integer/, [ dummy => 10 ]/]
2373 Literal SQL is especially useful for nesting parenthesized clauses in the
2374 main SQL query. Here is a first example :
2376 my ($sub_stmt, @sub_bind) = ("SELECT c1 FROM t1 WHERE c2 < ? AND c3 LIKE ?",
2380 bar => \["IN ($sub_stmt)" => @sub_bind],
2385 $stmt = "WHERE (foo = ? AND bar IN (SELECT c1 FROM t1
2386 WHERE c2 < ? AND c3 LIKE ?))";
2387 @bind = (1234, 100, "foo%");
2389 Other subquery operators, like for example C<"E<gt> ALL"> or C<"NOT IN">,
2390 are expressed in the same way. Of course the C<$sub_stmt> and
2391 its associated bind values can be generated through a former call
2394 my ($sub_stmt, @sub_bind)
2395 = $sql->select("t1", "c1", {c2 => {"<" => 100},
2396 c3 => {-like => "foo%"}});
2399 bar => \["> ALL ($sub_stmt)" => @sub_bind],
2402 In the examples above, the subquery was used as an operator on a column;
2403 but the same principle also applies for a clause within the main C<%where>
2404 hash, like an EXISTS subquery :
2406 my ($sub_stmt, @sub_bind)
2407 = $sql->select("t1", "*", {c1 => 1, c2 => \"> t0.c0"});
2408 my %where = ( -and => [
2410 \["EXISTS ($sub_stmt)" => @sub_bind],
2415 $stmt = "WHERE (foo = ? AND EXISTS (SELECT * FROM t1
2416 WHERE c1 = ? AND c2 > t0.c0))";
2420 Observe that the condition on C<c2> in the subquery refers to
2421 column C<t0.c0> of the main query : this is I<not> a bind
2422 value, so we have to express it through a scalar ref.
2423 Writing C<< c2 => {">" => "t0.c0"} >> would have generated
2424 C<< c2 > ? >> with bind value C<"t0.c0"> ... not exactly
2425 what we wanted here.
2427 Finally, here is an example where a subquery is used
2428 for expressing unary negation:
2430 my ($sub_stmt, @sub_bind)
2431 = $sql->where({age => [{"<" => 10}, {">" => 20}]});
2432 $sub_stmt =~ s/^ where //i; # don't want "WHERE" in the subclause
2434 lname => {like => '%son%'},
2435 \["NOT ($sub_stmt)" => @sub_bind],
2440 $stmt = "lname LIKE ? AND NOT ( age < ? OR age > ? )"
2441 @bind = ('%son%', 10, 20)
2447 These pages could go on for a while, since the nesting of the data
2448 structures this module can handle are pretty much unlimited (the
2449 module implements the C<WHERE> expansion as a recursive function
2450 internally). Your best bet is to "play around" with the module a
2451 little to see how the data structures behave, and choose the best
2452 format for your data based on that.
2454 And of course, all the values above will probably be replaced with
2455 variables gotten from forms or the command line. After all, if you
2456 knew everything ahead of time, you wouldn't have to worry about
2457 dynamically-generating SQL and could just hardwire it into your
2463 =head1 ORDER BY CLAUSES
2465 Some functions take an order by clause. This can either be a scalar (just a
2466 column name,) a hash of C<< { -desc => 'col' } >> or C<< { -asc => 'col' } >>,
2467 or an array of either of the two previous forms. Examples:
2469 Given | Will Generate
2470 ----------------------------------------------------------
2472 \'colA DESC' | ORDER BY colA DESC
2474 'colA' | ORDER BY colA
2476 [qw/colA colB/] | ORDER BY colA, colB
2478 {-asc => 'colA'} | ORDER BY colA ASC
2480 {-desc => 'colB'} | ORDER BY colB DESC
2482 ['colA', {-asc => 'colB'}] | ORDER BY colA, colB ASC
2484 { -asc => [qw/colA colB/] } | ORDER BY colA ASC, colB ASC
2487 { -asc => 'colA' }, | ORDER BY colA ASC, colB DESC,
2488 { -desc => [qw/colB/], | colC ASC, colD ASC
2489 { -asc => [qw/colC colD/],|
2491 ===========================================================
2495 =head1 SPECIAL OPERATORS
2497 my $sqlmaker = SQL::Abstract->new(special_ops => [
2501 my ($self, $field, $op, $arg) = @_;
2507 handler => 'method_name',
2511 A "special operator" is a SQL syntactic clause that can be
2512 applied to a field, instead of a usual binary operator.
2515 WHERE field IN (?, ?, ?)
2516 WHERE field BETWEEN ? AND ?
2517 WHERE MATCH(field) AGAINST (?, ?)
2519 Special operators IN and BETWEEN are fairly standard and therefore
2520 are builtin within C<SQL::Abstract> (as the overridable methods
2521 C<_where_field_IN> and C<_where_field_BETWEEN>). For other operators,
2522 like the MATCH .. AGAINST example above which is specific to MySQL,
2523 you can write your own operator handlers - supply a C<special_ops>
2524 argument to the C<new> method. That argument takes an arrayref of
2525 operator definitions; each operator definition is a hashref with two
2532 the regular expression to match the operator
2536 Either a coderef or a plain scalar method name. In both cases
2537 the expected return is C<< ($sql, @bind) >>.
2539 When supplied with a method name, it is simply called on the
2540 L<SQL::Abstract/> object as:
2542 $self->$method_name ($field, $op, $arg)
2546 $op is the part that matched the handler regex
2547 $field is the LHS of the operator
2550 When supplied with a coderef, it is called as:
2552 $coderef->($self, $field, $op, $arg)
2557 For example, here is an implementation
2558 of the MATCH .. AGAINST syntax for MySQL
2560 my $sqlmaker = SQL::Abstract->new(special_ops => [
2562 # special op for MySql MATCH (field) AGAINST(word1, word2, ...)
2563 {regex => qr/^match$/i,
2565 my ($self, $field, $op, $arg) = @_;
2566 $arg = [$arg] if not ref $arg;
2567 my $label = $self->_quote($field);
2568 my ($placeholder) = $self->_convert('?');
2569 my $placeholders = join ", ", (($placeholder) x @$arg);
2570 my $sql = $self->_sqlcase('match') . " ($label) "
2571 . $self->_sqlcase('against') . " ($placeholders) ";
2572 my @bind = $self->_bindtype($field, @$arg);
2573 return ($sql, @bind);
2580 =head1 UNARY OPERATORS
2582 my $sqlmaker = SQL::Abstract->new(unary_ops => [
2586 my ($self, $op, $arg) = @_;
2592 handler => 'method_name',
2596 A "unary operator" is a SQL syntactic clause that can be
2597 applied to a field - the operator goes before the field
2599 You can write your own operator handlers - supply a C<unary_ops>
2600 argument to the C<new> method. That argument takes an arrayref of
2601 operator definitions; each operator definition is a hashref with two
2608 the regular expression to match the operator
2612 Either a coderef or a plain scalar method name. In both cases
2613 the expected return is C<< $sql >>.
2615 When supplied with a method name, it is simply called on the
2616 L<SQL::Abstract/> object as:
2618 $self->$method_name ($op, $arg)
2622 $op is the part that matched the handler regex
2623 $arg is the RHS or argument of the operator
2625 When supplied with a coderef, it is called as:
2627 $coderef->($self, $op, $arg)
2635 Thanks to some benchmarking by Mark Stosberg, it turns out that
2636 this module is many orders of magnitude faster than using C<DBIx::Abstract>.
2637 I must admit this wasn't an intentional design issue, but it's a
2638 byproduct of the fact that you get to control your C<DBI> handles
2641 To maximize performance, use a code snippet like the following:
2643 # prepare a statement handle using the first row
2644 # and then reuse it for the rest of the rows
2646 for my $href (@array_of_hashrefs) {
2647 $stmt ||= $sql->insert('table', $href);
2648 $sth ||= $dbh->prepare($stmt);
2649 $sth->execute($sql->values($href));
2652 The reason this works is because the keys in your C<$href> are sorted
2653 internally by B<SQL::Abstract>. Thus, as long as your data retains
2654 the same structure, you only have to generate the SQL the first time
2655 around. On subsequent queries, simply use the C<values> function provided
2656 by this module to return your values in the correct order.
2658 However this depends on the values having the same type - if, for
2659 example, the values of a where clause may either have values
2660 (resulting in sql of the form C<column = ?> with a single bind
2661 value), or alternatively the values might be C<undef> (resulting in
2662 sql of the form C<column IS NULL> with no bind value) then the
2663 caching technique suggested will not work.
2667 If you use my C<CGI::FormBuilder> module at all, you'll hopefully
2668 really like this part (I do, at least). Building up a complex query
2669 can be as simple as the following:
2673 use CGI::FormBuilder;
2676 my $form = CGI::FormBuilder->new(...);
2677 my $sql = SQL::Abstract->new;
2679 if ($form->submitted) {
2680 my $field = $form->field;
2681 my $id = delete $field->{id};
2682 my($stmt, @bind) = $sql->update('table', $field, {id => $id});
2685 Of course, you would still have to connect using C<DBI> to run the
2686 query, but the point is that if you make your form look like your
2687 table, the actual query script can be extremely simplistic.
2689 If you're B<REALLY> lazy (I am), check out C<HTML::QuickTable> for
2690 a fast interface to returning and formatting data. I frequently
2691 use these three modules together to write complex database query
2692 apps in under 50 lines.
2698 =item * gitweb: L<http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=dbsrgits/DBIx-Class.git>
2700 =item * git: L<git://git.shadowcat.co.uk/dbsrgits/DBIx-Class.git>
2706 Version 1.50 was a major internal refactoring of C<SQL::Abstract>.
2707 Great care has been taken to preserve the I<published> behavior
2708 documented in previous versions in the 1.* family; however,
2709 some features that were previously undocumented, or behaved
2710 differently from the documentation, had to be changed in order
2711 to clarify the semantics. Hence, client code that was relying
2712 on some dark areas of C<SQL::Abstract> v1.*
2713 B<might behave differently> in v1.50.
2715 The main changes are :
2721 support for literal SQL through the C<< \ [$sql, bind] >> syntax.
2725 support for the { operator => \"..." } construct (to embed literal SQL)
2729 support for the { operator => \["...", @bind] } construct (to embed literal SQL with bind values)
2733 optional support for L<array datatypes|/"Inserting and Updating Arrays">
2737 defensive programming : check arguments
2741 fixed bug with global logic, which was previously implemented
2742 through global variables yielding side-effects. Prior versions would
2743 interpret C<< [ {cond1, cond2}, [cond3, cond4] ] >>
2744 as C<< "(cond1 AND cond2) OR (cond3 AND cond4)" >>.
2745 Now this is interpreted
2746 as C<< "(cond1 AND cond2) OR (cond3 OR cond4)" >>.
2751 fixed semantics of _bindtype on array args
2755 dropped the C<_anoncopy> of the %where tree. No longer necessary,
2756 we just avoid shifting arrays within that tree.
2760 dropped the C<_modlogic> function
2766 =head1 ACKNOWLEDGEMENTS
2768 There are a number of individuals that have really helped out with
2769 this module. Unfortunately, most of them submitted bugs via CPAN
2770 so I have no idea who they are! But the people I do know are:
2772 Ash Berlin (order_by hash term support)
2773 Matt Trout (DBIx::Class support)
2774 Mark Stosberg (benchmarking)
2775 Chas Owens (initial "IN" operator support)
2776 Philip Collins (per-field SQL functions)
2777 Eric Kolve (hashref "AND" support)
2778 Mike Fragassi (enhancements to "BETWEEN" and "LIKE")
2779 Dan Kubb (support for "quote_char" and "name_sep")
2780 Guillermo Roditi (patch to cleanup "IN" and "BETWEEN", fix and tests for _order_by)
2781 Laurent Dami (internal refactoring, extensible list of special operators, literal SQL)
2782 Norbert Buchmuller (support for literal SQL in hashpair, misc. fixes & tests)
2783 Peter Rabbitson (rewrite of SQLA::Test, misc. fixes & tests)
2784 Oliver Charles (support for "RETURNING" after "INSERT")
2790 L<DBIx::Class>, L<DBIx::Abstract>, L<CGI::FormBuilder>, L<HTML::QuickTable>.
2794 Copyright (c) 2001-2007 Nathan Wiger <nwiger@cpan.org>. All Rights Reserved.
2796 This module is actively maintained by Matt Trout <mst@shadowcatsystems.co.uk>
2798 For support, your best bet is to try the C<DBIx::Class> users mailing list.
2799 While not an official support venue, C<DBIx::Class> makes heavy use of
2800 C<SQL::Abstract>, and as such list members there are very familiar with
2801 how to create queries.
2805 This module is free software; you may copy this under the same
2806 terms as perl itself (either the GNU General Public License or
2807 the Artistic License)