Fixed behaviour of 'literal SQL with bind' feature (ie. \[$sql, @bind]) to expect...
[dbsrgits/SQL-Abstract.git] / lib / SQL / Abstract.pm
CommitLineData
96449e8e 1package SQL::Abstract; # see doc at end of file
2
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)
7
8use Carp;
9use strict;
10use warnings;
fffe6900 11use List::Util qw/first/;
12use Scalar::Util qw/blessed/;
96449e8e 13
14#======================================================================
15# GLOBALS
16#======================================================================
17
190bbeb7 18our $VERSION = '1.49_02';
7479e27e 19$VERSION = eval $VERSION; # numify for warning-free dev releases
20
96449e8e 21
22our $AUTOLOAD;
23
24# special operators (-in, -between). May be extended/overridden by user.
25# See section WHERE: BUILTIN SPECIAL OPERATORS below for implementation
26my @BUILTIN_SPECIAL_OPS = (
27 {regex => qr/^(not )?between$/i, handler => \&_where_field_BETWEEN},
28 {regex => qr/^(not )?in$/i, handler => \&_where_field_IN},
29);
30
31#======================================================================
32# DEBUGGING AND ERROR REPORTING
33#======================================================================
34
35sub _debug {
36 return unless $_[0]->{debug}; shift; # a little faster
37 my $func = (caller(1))[3];
38 warn "[$func] ", @_, "\n";
39}
40
41sub belch (@) {
42 my($func) = (caller(1))[3];
43 carp "[$func] Warning: ", @_;
44}
45
46sub puke (@) {
47 my($func) = (caller(1))[3];
48 croak "[$func] Fatal: ", @_;
49}
50
51
52#======================================================================
53# NEW
54#======================================================================
55
56sub new {
57 my $self = shift;
58 my $class = ref($self) || $self;
59 my %opt = (ref $_[0] eq 'HASH') ? %{$_[0]} : @_;
60
61 # choose our case by keeping an option around
62 delete $opt{case} if $opt{case} && $opt{case} ne 'lower';
63
64 # default logic for interpreting arrayrefs
65 $opt{logic} = uc $opt{logic} || 'OR';
66
67 # how to return bind vars
68 # LDNOTE: changed nwiger code : why this 'delete' ??
69 # $opt{bindtype} ||= delete($opt{bind_type}) || 'normal';
70 $opt{bindtype} ||= 'normal';
71
72 # default comparison is "=", but can be overridden
73 $opt{cmp} ||= '=';
74
75 # try to recognize which are the 'equality' and 'unequality' ops
76 # (temporary quickfix, should go through a more seasoned API)
77 $opt{equality_op} = qr/^(\Q$opt{cmp}\E|is|(is\s+)?like)$/i;
78 $opt{inequality_op} = qr/^(!=|<>|(is\s+)?not(\s+like)?)$/i;
79
80 # SQL booleans
81 $opt{sqltrue} ||= '1=1';
82 $opt{sqlfalse} ||= '0=1';
83
84 # special operators
85 $opt{special_ops} ||= [];
86 push @{$opt{special_ops}}, @BUILTIN_SPECIAL_OPS;
87
88 return bless \%opt, $class;
89}
90
91
92
93#======================================================================
94# INSERT methods
95#======================================================================
96
97sub insert {
98 my $self = shift;
99 my $table = $self->_table(shift);
100 my $data = shift || return;
101
102 my $method = $self->_METHOD_FOR_refkind("_insert", $data);
103 my ($sql, @bind) = $self->$method($data);
104 $sql = join " ", $self->_sqlcase('insert into'), $table, $sql;
105 return wantarray ? ($sql, @bind) : $sql;
106}
107
108sub _insert_HASHREF { # explicit list of fields and then values
109 my ($self, $data) = @_;
110
111 my @fields = sort keys %$data;
112
fe3ae272 113 my ($sql, @bind) = $self->_insert_values($data);
96449e8e 114
115 # assemble SQL
116 $_ = $self->_quote($_) foreach @fields;
117 $sql = "( ".join(", ", @fields).") ".$sql;
118
119 return ($sql, @bind);
120}
121
122sub _insert_ARRAYREF { # just generate values(?,?) part (no list of fields)
123 my ($self, $data) = @_;
124
125 # no names (arrayref) so can't generate bindtype
126 $self->{bindtype} ne 'columns'
127 or belch "can't do 'columns' bindtype when called with arrayref";
128
fe3ae272 129 # fold the list of values into a hash of column name - value pairs
130 # (where the column names are artificially generated, and their
131 # lexicographical ordering keep the ordering of the original list)
132 my $i = "a"; # incremented values will be in lexicographical order
133 my $data_in_hash = { map { ($i++ => $_) } @$data };
134
135 return $self->_insert_values($data_in_hash);
136}
137
138sub _insert_ARRAYREFREF { # literal SQL with bind
139 my ($self, $data) = @_;
140
141 my ($sql, @bind) = @${$data};
142 $self->_assert_bindval_matches_bindtype(@bind);
143
144 return ($sql, @bind);
145}
146
147
148sub _insert_SCALARREF { # literal SQL without bind
149 my ($self, $data) = @_;
150
151 return ($$data);
152}
153
154sub _insert_values {
155 my ($self, $data) = @_;
156
96449e8e 157 my (@values, @all_bind);
fe3ae272 158 foreach my $column (sort keys %$data) {
159 my $v = $data->{$column};
96449e8e 160
161 $self->_SWITCH_refkind($v, {
162
163 ARRAYREF => sub {
164 if ($self->{array_datatypes}) { # if array datatype are activated
165 push @values, '?';
fe3ae272 166 push @all_bind, $self->_bindtype($column, $v);
96449e8e 167 }
168 else { # else literal SQL with bind
169 my ($sql, @bind) = @$v;
fe3ae272 170 $self->_assert_bindval_matches_bindtype(@bind);
96449e8e 171 push @values, $sql;
172 push @all_bind, @bind;
173 }
174 },
175
176 ARRAYREFREF => sub { # literal SQL with bind
177 my ($sql, @bind) = @${$v};
fe3ae272 178 $self->_assert_bindval_matches_bindtype(@bind);
96449e8e 179 push @values, $sql;
180 push @all_bind, @bind;
181 },
182
183 # THINK : anything useful to do with a HASHREF ?
5db47f9f 184 HASHREF => sub { # (nothing, but old SQLA passed it through)
185 #TODO in SQLA >= 2.0 it will die instead
186 belch "HASH ref as bind value in insert is not supported";
187 push @values, '?';
fe3ae272 188 push @all_bind, $self->_bindtype($column, $v);
5db47f9f 189 },
96449e8e 190
191 SCALARREF => sub { # literal SQL without bind
192 push @values, $$v;
193 },
194
195 SCALAR_or_UNDEF => sub {
196 push @values, '?';
fe3ae272 197 push @all_bind, $self->_bindtype($column, $v);
96449e8e 198 },
199
200 });
201
202 }
203
204 my $sql = $self->_sqlcase('values')." ( ".join(", ", @values)." )";
205 return ($sql, @all_bind);
206}
207
208
96449e8e 209
210#======================================================================
211# UPDATE methods
212#======================================================================
213
214
215sub update {
216 my $self = shift;
217 my $table = $self->_table(shift);
218 my $data = shift || return;
219 my $where = shift;
220
221 # first build the 'SET' part of the sql statement
222 my (@set, @all_bind);
223 puke "Unsupported data type specified to \$sql->update"
224 unless ref $data eq 'HASH';
225
226 for my $k (sort keys %$data) {
227 my $v = $data->{$k};
228 my $r = ref $v;
229 my $label = $self->_quote($k);
230
231 $self->_SWITCH_refkind($v, {
232 ARRAYREF => sub {
233 if ($self->{array_datatypes}) { # array datatype
234 push @set, "$label = ?";
235 push @all_bind, $self->_bindtype($k, $v);
236 }
237 else { # literal SQL with bind
238 my ($sql, @bind) = @$v;
fe3ae272 239 $self->_assert_bindval_matches_bindtype(@bind);
96449e8e 240 push @set, "$label = $sql";
fe3ae272 241 push @all_bind, @bind;
96449e8e 242 }
243 },
244 ARRAYREFREF => sub { # literal SQL with bind
245 my ($sql, @bind) = @${$v};
fe3ae272 246 $self->_assert_bindval_matches_bindtype(@bind);
96449e8e 247 push @set, "$label = $sql";
fe3ae272 248 push @all_bind, @bind;
96449e8e 249 },
250 SCALARREF => sub { # literal SQL without bind
251 push @set, "$label = $$v";
252 },
253 SCALAR_or_UNDEF => sub {
254 push @set, "$label = ?";
255 push @all_bind, $self->_bindtype($k, $v);
256 },
257 });
258 }
259
260 # generate sql
261 my $sql = $self->_sqlcase('update') . " $table " . $self->_sqlcase('set ')
262 . join ', ', @set;
263
264 if ($where) {
265 my($where_sql, @where_bind) = $self->where($where);
266 $sql .= $where_sql;
267 push @all_bind, @where_bind;
268 }
269
270 return wantarray ? ($sql, @all_bind) : $sql;
271}
272
273
274
275
276#======================================================================
277# SELECT
278#======================================================================
279
280
281sub select {
282 my $self = shift;
283 my $table = $self->_table(shift);
284 my $fields = shift || '*';
285 my $where = shift;
286 my $order = shift;
287
288 my($where_sql, @bind) = $self->where($where, $order);
289
290 my $f = (ref $fields eq 'ARRAY') ? join ', ', map { $self->_quote($_) } @$fields
291 : $fields;
292 my $sql = join(' ', $self->_sqlcase('select'), $f,
293 $self->_sqlcase('from'), $table)
294 . $where_sql;
295
296 return wantarray ? ($sql, @bind) : $sql;
297}
298
299#======================================================================
300# DELETE
301#======================================================================
302
303
304sub delete {
305 my $self = shift;
306 my $table = $self->_table(shift);
307 my $where = shift;
308
309
310 my($where_sql, @bind) = $self->where($where);
311 my $sql = $self->_sqlcase('delete from') . " $table" . $where_sql;
312
313 return wantarray ? ($sql, @bind) : $sql;
314}
315
316
317#======================================================================
318# WHERE: entry point
319#======================================================================
320
321
322
323# Finally, a separate routine just to handle WHERE clauses
324sub where {
325 my ($self, $where, $order) = @_;
326
327 # where ?
328 my ($sql, @bind) = $self->_recurse_where($where);
329 $sql = $sql ? $self->_sqlcase(' where ') . "( $sql )" : '';
330
331 # order by?
332 if ($order) {
333 $sql .= $self->_order_by($order);
334 }
335
336 return wantarray ? ($sql, @bind) : $sql;
337}
338
339
340sub _recurse_where {
341 my ($self, $where, $logic) = @_;
342
343 # dispatch on appropriate method according to refkind of $where
344 my $method = $self->_METHOD_FOR_refkind("_where", $where);
311b2151 345
346
347 my ($sql, @bind) = $self->$method($where, $logic);
348
349 # DBIx::Class directly calls _recurse_where in scalar context, so
350 # we must implement it, even if not in the official API
351 return wantarray ? ($sql, @bind) : $sql;
96449e8e 352}
353
354
355
356#======================================================================
357# WHERE: top-level ARRAYREF
358#======================================================================
359
360
361sub _where_ARRAYREF {
362 my ($self, $where, $logic) = @_;
363
364 $logic = uc($logic || $self->{logic});
365 $logic eq 'AND' or $logic eq 'OR' or puke "unknown logic: $logic";
366
367 my @clauses = @$where;
368
369 # if the array starts with [-and|or => ...], recurse with that logic
370 my $first = $clauses[0] || '';
371 if ($first =~ /^-(and|or)/i) {
372 $logic = $1;
373 shift @clauses;
374 return $self->_where_ARRAYREF(\@clauses, $logic);
375 }
376
377 #otherwise..
378 my (@sql_clauses, @all_bind);
379
380 # need to use while() so can shift() for pairs
381 while (my $el = shift @clauses) {
382
383 # switch according to kind of $el and get corresponding ($sql, @bind)
384 my ($sql, @bind) = $self->_SWITCH_refkind($el, {
385
386 # skip empty elements, otherwise get invalid trailing AND stuff
387 ARRAYREF => sub {$self->_recurse_where($el) if @$el},
388
389 HASHREF => sub {$self->_recurse_where($el, 'and') if %$el},
390 # LDNOTE : previous SQLA code for hashrefs was creating a dirty
391 # side-effect: the first hashref within an array would change
392 # the global logic to 'AND'. So [ {cond1, cond2}, [cond3, cond4] ]
393 # was interpreted as "(cond1 AND cond2) OR (cond3 AND cond4)",
394 # whereas it should be "(cond1 AND cond2) OR (cond3 OR cond4)".
395
396 SCALARREF => sub { ($$el); },
397
398 SCALAR => sub {# top-level arrayref with scalars, recurse in pairs
399 $self->_recurse_where({$el => shift(@clauses)})},
400
401 UNDEF => sub {puke "not supported : UNDEF in arrayref" },
402 });
403
4b7b6026 404 if ($sql) {
405 push @sql_clauses, $sql;
406 push @all_bind, @bind;
407 }
96449e8e 408 }
409
410 return $self->_join_sql_clauses($logic, \@sql_clauses, \@all_bind);
411}
412
413
414
415#======================================================================
416# WHERE: top-level HASHREF
417#======================================================================
418
419sub _where_HASHREF {
420 my ($self, $where) = @_;
421 my (@sql_clauses, @all_bind);
422
423 # LDNOTE : don't really know why we need to sort keys
424 for my $k (sort keys %$where) {
425 my $v = $where->{$k};
426
427 # ($k => $v) is either a special op or a regular hashpair
428 my ($sql, @bind) = ($k =~ /^-(.+)/) ? $self->_where_op_in_hash($1, $v)
429 : do {
430 my $method = $self->_METHOD_FOR_refkind("_where_hashpair", $v);
431 $self->$method($k, $v);
432 };
433
434 push @sql_clauses, $sql;
435 push @all_bind, @bind;
436 }
437
438 return $self->_join_sql_clauses('and', \@sql_clauses, \@all_bind);
439}
440
441
442sub _where_op_in_hash {
443 my ($self, $op, $v) = @_;
444
445 $op =~ /^(AND|OR|NEST)[_\d]*/i
446 or puke "unknown operator: -$op";
447 $op = uc($1); # uppercase, remove trailing digits
448 $self->_debug("OP(-$op) within hashref, recursing...");
449
450 $self->_SWITCH_refkind($v, {
451
452 ARRAYREF => sub {
453 # LDNOTE : should deprecate {-or => [...]} and {-and => [...]}
454 # because they are misleading; the only proper way would be
455 # -nest => [-or => ...], -nest => [-and ...]
456 return $self->_where_ARRAYREF($v, $op eq 'NEST' ? '' : $op);
457 },
458
459 HASHREF => sub {
460 if ($op eq 'OR') {
461 belch "-or => {...} should be -nest => [...]";
462 return $self->_where_ARRAYREF([%$v], 'OR');
463 }
464 else { # NEST | AND
465 return $self->_where_HASHREF($v);
466 }
467 },
468
469 SCALARREF => sub { # literal SQL
470 $op eq 'NEST'
471 or puke "-$op => \\\$scalar not supported, use -nest => ...";
472 return ($$v);
473 },
474
475 ARRAYREFREF => sub { # literal SQL
476 $op eq 'NEST'
477 or puke "-$op => \\[..] not supported, use -nest => ...";
478 return @{${$v}};
479 },
480
481 SCALAR => sub { # permissively interpreted as SQL
482 $op eq 'NEST'
483 or puke "-$op => 'scalar' not supported, use -nest => \\'scalar'";
484 belch "literal SQL should be -nest => \\'scalar' "
485 . "instead of -nest => 'scalar' ";
486 return ($v);
487 },
488
489 UNDEF => sub {
490 puke "-$op => undef not supported";
491 },
492 });
493}
494
495
496sub _where_hashpair_ARRAYREF {
497 my ($self, $k, $v) = @_;
498
499 if( @$v ) {
500 my @v = @$v; # need copy because of shift below
501 $self->_debug("ARRAY($k) means distribute over elements");
502
503 # put apart first element if it is an operator (-and, -or)
504 my $op = $v[0] =~ /^-/ ? shift @v : undef;
505 $self->_debug("OP($op) reinjected into the distributed array") if $op;
506
507 my @distributed = map { {$k => $_} } @v;
508 unshift @distributed, $op if $op;
509
510 return $self->_recurse_where(\@distributed);
511 }
512 else {
513 # LDNOTE : not sure of this one. What does "distribute over nothing" mean?
514 $self->_debug("empty ARRAY($k) means 0=1");
515 return ($self->{sqlfalse});
516 }
517}
518
519sub _where_hashpair_HASHREF {
520 my ($self, $k, $v) = @_;
521
522 my (@all_sql, @all_bind);
523
524 for my $op (sort keys %$v) {
525 my $val = $v->{$op};
526
527 # put the operator in canonical form
528 $op =~ s/^-//; # remove initial dash
529 $op =~ tr/_/ /; # underscores become spaces
530 $op =~ s/^\s+//; # no initial space
531 $op =~ s/\s+$//; # no final space
532 $op =~ s/\s+/ /; # multiple spaces become one
533
534 my ($sql, @bind);
535
536 # CASE: special operators like -in or -between
537 my $special_op = first {$op =~ $_->{regex}} @{$self->{special_ops}};
538 if ($special_op) {
539 ($sql, @bind) = $special_op->{handler}->($self, $k, $op, $val);
540 }
96449e8e 541 else {
cf838930 542 $self->_SWITCH_refkind($val, {
543
544 ARRAYREF => sub { # CASE: col => {op => \@vals}
545 ($sql, @bind) = $self->_where_field_op_ARRAYREF($k, $op, $val);
546 },
547
fe3ae272 548 SCALARREF => sub { # CASE: col => {op => \$scalar} (literal SQL without bind)
cf838930 549 $sql = join ' ', $self->_convert($self->_quote($k)),
550 $self->_sqlcase($op),
551 $$val;
552 },
553
fe3ae272 554 ARRAYREFREF => sub { # CASE: col => {op => \[$sql, @bind]} (literal SQL with bind)
b3be7bd0 555 my ($sub_sql, @sub_bind) = @$$val;
fe3ae272 556 $self->_assert_bindval_matches_bindtype(@sub_bind);
b3be7bd0 557 $sql = join ' ', $self->_convert($self->_quote($k)),
558 $self->_sqlcase($op),
559 $sub_sql;
fe3ae272 560 @bind = @sub_bind;
b3be7bd0 561 },
562
cf838930 563 UNDEF => sub { # CASE: col => {op => undef} : sql "IS (NOT)? NULL"
564 my $is = ($op =~ $self->{equality_op}) ? 'is' :
565 ($op =~ $self->{inequality_op}) ? 'is not' :
566 puke "unexpected operator '$op' with undef operand";
567 $sql = $self->_quote($k) . $self->_sqlcase(" $is null");
568 },
569
570 FALLBACK => sub { # CASE: col => {op => $scalar}
571 $sql = join ' ', $self->_convert($self->_quote($k)),
572 $self->_sqlcase($op),
573 $self->_convert('?');
574 @bind = $self->_bindtype($k, $val);
575 },
576 });
96449e8e 577 }
578
579 push @all_sql, $sql;
580 push @all_bind, @bind;
581 }
582
583 return $self->_join_sql_clauses('and', \@all_sql, \@all_bind);
584}
585
586
587
588sub _where_field_op_ARRAYREF {
589 my ($self, $k, $op, $vals) = @_;
590
591 if(@$vals) {
592 $self->_debug("ARRAY($vals) means multiple elements: [ @$vals ]");
593
594
595
596 # LDNOTE : change the distribution logic when
597 # $op =~ $self->{inequality_op}, because of Morgan laws :
598 # with {field => {'!=' => [22, 33]}}, it would be ridiculous to generate
599 # WHERE field != 22 OR field != 33 : the user probably means
600 # WHERE field != 22 AND field != 33.
601 my $logic = ($op =~ $self->{inequality_op}) ? 'AND' : 'OR';
602
603 # distribute $op over each member of @$vals
604 return $self->_recurse_where([map { {$k => {$op, $_}} } @$vals], $logic);
605
606 }
607 else {
608 # try to DWIM on equality operators
609 # LDNOTE : not 100% sure this is the correct thing to do ...
610 return ($self->{sqlfalse}) if $op =~ $self->{equality_op};
611 return ($self->{sqltrue}) if $op =~ $self->{inequality_op};
612
613 # otherwise
614 puke "operator '$op' applied on an empty array (field '$k')";
615 }
616}
617
618
619sub _where_hashpair_SCALARREF {
620 my ($self, $k, $v) = @_;
621 $self->_debug("SCALAR($k) means literal SQL: $$v");
622 my $sql = $self->_quote($k) . " " . $$v;
623 return ($sql);
624}
625
fe3ae272 626# literal SQL with bind
96449e8e 627sub _where_hashpair_ARRAYREFREF {
628 my ($self, $k, $v) = @_;
629 $self->_debug("REF($k) means literal SQL: @${$v}");
630 my ($sql, @bind) = @${$v};
fe3ae272 631 $self->_assert_bindval_matches_bindtype(@bind);
96449e8e 632 $sql = $self->_quote($k) . " " . $sql;
96449e8e 633 return ($sql, @bind );
634}
635
fe3ae272 636# literal SQL without bind
96449e8e 637sub _where_hashpair_SCALAR {
638 my ($self, $k, $v) = @_;
639 $self->_debug("NOREF($k) means simple key=val: $k $self->{cmp} $v");
640 my $sql = join ' ', $self->_convert($self->_quote($k)),
641 $self->_sqlcase($self->{cmp}),
642 $self->_convert('?');
643 my @bind = $self->_bindtype($k, $v);
644 return ( $sql, @bind);
645}
646
647
648sub _where_hashpair_UNDEF {
649 my ($self, $k, $v) = @_;
650 $self->_debug("UNDEF($k) means IS NULL");
651 my $sql = $self->_quote($k) . $self->_sqlcase(' is null');
652 return ($sql);
653}
654
655#======================================================================
656# WHERE: TOP-LEVEL OTHERS (SCALARREF, SCALAR, UNDEF)
657#======================================================================
658
659
660sub _where_SCALARREF {
661 my ($self, $where) = @_;
662
663 # literal sql
664 $self->_debug("SCALAR(*top) means literal SQL: $$where");
665 return ($$where);
666}
667
668
669sub _where_SCALAR {
670 my ($self, $where) = @_;
671
672 # literal sql
673 $self->_debug("NOREF(*top) means literal SQL: $where");
674 return ($where);
675}
676
677
678sub _where_UNDEF {
679 my ($self) = @_;
680 return ();
681}
682
683
684#======================================================================
685# WHERE: BUILTIN SPECIAL OPERATORS (-in, -between)
686#======================================================================
687
688
689sub _where_field_BETWEEN {
690 my ($self, $k, $op, $vals) = @_;
691
692 ref $vals eq 'ARRAY' && @$vals == 2
693 or puke "special op 'between' requires an arrayref of two values";
694
695 my ($label) = $self->_convert($self->_quote($k));
696 my ($placeholder) = $self->_convert('?');
697 my $and = $self->_sqlcase('and');
698 $op = $self->_sqlcase($op);
699
700 my $sql = "( $label $op $placeholder $and $placeholder )";
701 my @bind = $self->_bindtype($k, @$vals);
702 return ($sql, @bind)
703}
704
705
706sub _where_field_IN {
707 my ($self, $k, $op, $vals) = @_;
708
709 # backwards compatibility : if scalar, force into an arrayref
710 $vals = [$vals] if defined $vals && ! ref $vals;
711
96449e8e 712 my ($label) = $self->_convert($self->_quote($k));
713 my ($placeholder) = $self->_convert('?');
96449e8e 714 $op = $self->_sqlcase($op);
715
8a0d798a 716 my ($sql, @bind) = $self->_SWITCH_refkind($vals, {
717 ARRAYREF => sub { # list of choices
718 if (@$vals) { # nonempty list
719 my $placeholders = join ", ", (($placeholder) x @$vals);
720 my $sql = "$label $op ( $placeholders )";
721 my @bind = $self->_bindtype($k, @$vals);
96449e8e 722
8a0d798a 723 return ($sql, @bind);
724 }
725 else { # empty list : some databases won't understand "IN ()", so DWIM
726 my $sql = ($op =~ /\bnot\b/i) ? $self->{sqltrue} : $self->{sqlfalse};
727 return ($sql);
728 }
729 },
730
731 ARRAYREFREF => sub { # literal SQL with bind
732 my ($sql, @bind) = @$$vals;
fe3ae272 733 $self->_assert_bindval_matches_bindtype(@bind);
8a0d798a 734 return ("$label $op ( $sql )", @bind);
735 },
736
737 FALLBACK => sub {
738 puke "special op 'in' requires an arrayref (or arrayref-ref)";
739 },
740 });
741
742 return ($sql, @bind);
96449e8e 743}
744
745
746
747
748
749
750#======================================================================
751# ORDER BY
752#======================================================================
753
754sub _order_by {
755 my ($self, $arg) = @_;
756
757 # construct list of ordering instructions
758 my @order = $self->_SWITCH_refkind($arg, {
759
760 ARRAYREF => sub {
761 map {$self->_SWITCH_refkind($_, {
762 SCALAR => sub {$self->_quote($_)},
fffe6900 763 UNDEF => sub {},
96449e8e 764 SCALARREF => sub {$$_}, # literal SQL, no quoting
765 HASHREF => sub {$self->_order_by_hash($_)}
766 }) } @$arg;
767 },
768
769 SCALAR => sub {$self->_quote($arg)},
b6475fb1 770 UNDEF => sub {},
96449e8e 771 SCALARREF => sub {$$arg}, # literal SQL, no quoting
772 HASHREF => sub {$self->_order_by_hash($arg)},
773
774 });
775
776 # build SQL
777 my $order = join ', ', @order;
778 return $order ? $self->_sqlcase(' order by')." $order" : '';
779}
780
781
782sub _order_by_hash {
783 my ($self, $hash) = @_;
784
785 # get first pair in hash
786 my ($key, $val) = each %$hash;
787
788 # check if one pair was found and no other pair in hash
789 $key && !(each %$hash)
790 or puke "hash passed to _order_by must have exactly one key (-desc or -asc)";
791
792 my ($order) = ($key =~ /^-(desc|asc)/i)
793 or puke "invalid key in _order_by hash : $key";
794
795 return $self->_quote($val) ." ". $self->_sqlcase($order);
796}
797
798
799
800#======================================================================
801# DATASOURCE (FOR NOW, JUST PLAIN TABLE OR LIST OF TABLES)
802#======================================================================
803
804sub _table {
805 my $self = shift;
806 my $from = shift;
807 $self->_SWITCH_refkind($from, {
808 ARRAYREF => sub {join ', ', map { $self->_quote($_) } @$from;},
809 SCALAR => sub {$self->_quote($from)},
810 SCALARREF => sub {$$from},
811 ARRAYREFREF => sub {join ', ', @$from;},
812 });
813}
814
815
816#======================================================================
817# UTILITY FUNCTIONS
818#======================================================================
819
820sub _quote {
821 my $self = shift;
822 my $label = shift;
823
824 $label or puke "can't quote an empty label";
825
826 # left and right quote characters
827 my ($ql, $qr, @other) = $self->_SWITCH_refkind($self->{quote_char}, {
828 SCALAR => sub {($self->{quote_char}, $self->{quote_char})},
829 ARRAYREF => sub {@{$self->{quote_char}}},
830 UNDEF => sub {()},
831 });
832 not @other
833 or puke "quote_char must be an arrayref of 2 values";
834
835 # no quoting if no quoting chars
836 $ql or return $label;
837
838 # no quoting for literal SQL
839 return $$label if ref($label) eq 'SCALAR';
840
841 # separate table / column (if applicable)
842 my $sep = $self->{name_sep} || '';
843 my @to_quote = $sep ? split /\Q$sep\E/, $label : ($label);
844
845 # do the quoting, except for "*" or for `table`.*
846 my @quoted = map { $_ eq '*' ? $_: $ql.$_.$qr} @to_quote;
847
848 # reassemble and return.
849 return join $sep, @quoted;
850}
851
852
853# Conversion, if applicable
854sub _convert ($) {
855 my ($self, $arg) = @_;
856
857# LDNOTE : modified the previous implementation below because
858# it was not consistent : the first "return" is always an array,
859# the second "return" is context-dependent. Anyway, _convert
860# seems always used with just a single argument, so make it a
861# scalar function.
862# return @_ unless $self->{convert};
863# my $conv = $self->_sqlcase($self->{convert});
864# my @ret = map { $conv.'('.$_.')' } @_;
865# return wantarray ? @ret : $ret[0];
866 if ($self->{convert}) {
867 my $conv = $self->_sqlcase($self->{convert});
868 $arg = $conv.'('.$arg.')';
869 }
870 return $arg;
871}
872
873# And bindtype
874sub _bindtype (@) {
875 my $self = shift;
876 my($col, @vals) = @_;
877
878 #LDNOTE : changed original implementation below because it did not make
879 # sense when bindtype eq 'columns' and @vals > 1.
880# return $self->{bindtype} eq 'columns' ? [ $col, @vals ] : @vals;
881
882 return $self->{bindtype} eq 'columns' ? map {[$col, $_]} @vals : @vals;
883}
884
fe3ae272 885# Dies if any element of @bind is not in [colname => value] format
886# if bindtype is 'columns'.
887sub _assert_bindval_matches_bindtype {
888 my ($self, @bind) = @_;
889
890 if ($self->{bindtype} eq 'columns') {
891 foreach my $val (@bind) {
892 if (!defined $val || ref($val) ne 'ARRAY' || @$val != 2) {
893 die "bindtype 'columns' selected, you need to pass: [column_name => bind_value]"
894 }
895 }
896 }
897}
898
96449e8e 899sub _join_sql_clauses {
900 my ($self, $logic, $clauses_aref, $bind_aref) = @_;
901
902 if (@$clauses_aref > 1) {
903 my $join = " " . $self->_sqlcase($logic) . " ";
904 my $sql = '( ' . join($join, @$clauses_aref) . ' )';
905 return ($sql, @$bind_aref);
906 }
907 elsif (@$clauses_aref) {
908 return ($clauses_aref->[0], @$bind_aref); # no parentheses
909 }
910 else {
911 return (); # if no SQL, ignore @$bind_aref
912 }
913}
914
915
916# Fix SQL case, if so requested
917sub _sqlcase {
918 my $self = shift;
919
920 # LDNOTE: if $self->{case} is true, then it contains 'lower', so we
921 # don't touch the argument ... crooked logic, but let's not change it!
922 return $self->{case} ? $_[0] : uc($_[0]);
923}
924
925
926#======================================================================
927# DISPATCHING FROM REFKIND
928#======================================================================
929
930sub _refkind {
931 my ($self, $data) = @_;
932 my $suffix = '';
933 my $ref;
90aab162 934 my $n_steps = 0;
96449e8e 935
96449e8e 936 while (1) {
90aab162 937 # blessed objects are treated like scalars
938 $ref = (blessed $data) ? '' : ref $data;
939 $n_steps += 1 if $ref;
940 last if $ref ne 'REF';
96449e8e 941 $data = $$data;
942 }
943
90aab162 944 my $base = $ref || (defined $data ? 'SCALAR' : 'UNDEF');
945
946 return $base . ('REF' x $n_steps);
96449e8e 947}
948
90aab162 949
950
96449e8e 951sub _try_refkind {
952 my ($self, $data) = @_;
953 my @try = ($self->_refkind($data));
954 push @try, 'SCALAR_or_UNDEF' if $try[0] eq 'SCALAR' || $try[0] eq 'UNDEF';
955 push @try, 'FALLBACK';
956 return @try;
957}
958
959sub _METHOD_FOR_refkind {
960 my ($self, $meth_prefix, $data) = @_;
961 my $method = first {$_} map {$self->can($meth_prefix."_".$_)}
962 $self->_try_refkind($data)
963 or puke "cannot dispatch on '$meth_prefix' for ".$self->_refkind($data);
964 return $method;
965}
966
967
968sub _SWITCH_refkind {
969 my ($self, $data, $dispatch_table) = @_;
970
971 my $coderef = first {$_} map {$dispatch_table->{$_}}
972 $self->_try_refkind($data)
973 or puke "no dispatch entry for ".$self->_refkind($data);
974 $coderef->();
975}
976
977
978
979
980#======================================================================
981# VALUES, GENERATE, AUTOLOAD
982#======================================================================
983
984# LDNOTE: original code from nwiger, didn't touch code in that section
985# I feel the AUTOLOAD stuff should not be the default, it should
986# only be activated on explicit demand by user.
987
988sub values {
989 my $self = shift;
990 my $data = shift || return;
991 puke "Argument to ", __PACKAGE__, "->values must be a \\%hash"
992 unless ref $data eq 'HASH';
993 return map { $self->_bindtype($_, $data->{$_}) } sort keys %$data;
994}
995
996sub generate {
997 my $self = shift;
998
999 my(@sql, @sqlq, @sqlv);
1000
1001 for (@_) {
1002 my $ref = ref $_;
1003 if ($ref eq 'HASH') {
1004 for my $k (sort keys %$_) {
1005 my $v = $_->{$k};
1006 my $r = ref $v;
1007 my $label = $self->_quote($k);
1008 if ($r eq 'ARRAY') {
fe3ae272 1009 # literal SQL with bind
1010 my ($sql, @bind) = @$v;
1011 $self->_assert_bindval_matches_bindtype(@bind);
96449e8e 1012 push @sqlq, "$label = $sql";
fe3ae272 1013 push @sqlv, @bind;
96449e8e 1014 } elsif ($r eq 'SCALAR') {
fe3ae272 1015 # literal SQL without bind
96449e8e 1016 push @sqlq, "$label = $$v";
1017 } else {
1018 push @sqlq, "$label = ?";
1019 push @sqlv, $self->_bindtype($k, $v);
1020 }
1021 }
1022 push @sql, $self->_sqlcase('set'), join ', ', @sqlq;
1023 } elsif ($ref eq 'ARRAY') {
1024 # unlike insert(), assume these are ONLY the column names, i.e. for SQL
1025 for my $v (@$_) {
1026 my $r = ref $v;
fe3ae272 1027 if ($r eq 'ARRAY') { # literal SQL with bind
1028 my ($sql, @bind) = @$v;
1029 $self->_assert_bindval_matches_bindtype(@bind);
1030 push @sqlq, $sql;
1031 push @sqlv, @bind;
1032 } elsif ($r eq 'SCALAR') { # literal SQL without bind
96449e8e 1033 # embedded literal SQL
1034 push @sqlq, $$v;
1035 } else {
1036 push @sqlq, '?';
1037 push @sqlv, $v;
1038 }
1039 }
1040 push @sql, '(' . join(', ', @sqlq) . ')';
1041 } elsif ($ref eq 'SCALAR') {
1042 # literal SQL
1043 push @sql, $$_;
1044 } else {
1045 # strings get case twiddled
1046 push @sql, $self->_sqlcase($_);
1047 }
1048 }
1049
1050 my $sql = join ' ', @sql;
1051
1052 # this is pretty tricky
1053 # if ask for an array, return ($stmt, @bind)
1054 # otherwise, s/?/shift @sqlv/ to put it inline
1055 if (wantarray) {
1056 return ($sql, @sqlv);
1057 } else {
1058 1 while $sql =~ s/\?/my $d = shift(@sqlv);
1059 ref $d ? $d->[1] : $d/e;
1060 return $sql;
1061 }
1062}
1063
1064
1065sub DESTROY { 1 }
1066
1067sub AUTOLOAD {
1068 # This allows us to check for a local, then _form, attr
1069 my $self = shift;
1070 my($name) = $AUTOLOAD =~ /.*::(.+)/;
1071 return $self->generate($name, @_);
1072}
1073
10741;
1075
1076
1077
1078__END__
32eab2da 1079
1080=head1 NAME
1081
1082SQL::Abstract - Generate SQL from Perl data structures
1083
1084=head1 SYNOPSIS
1085
1086 use SQL::Abstract;
1087
1088 my $sql = SQL::Abstract->new;
1089
1090 my($stmt, @bind) = $sql->select($table, \@fields, \%where, \@order);
1091
1092 my($stmt, @bind) = $sql->insert($table, \%fieldvals || \@values);
1093
1094 my($stmt, @bind) = $sql->update($table, \%fieldvals, \%where);
1095
1096 my($stmt, @bind) = $sql->delete($table, \%where);
1097
1098 # Then, use these in your DBI statements
1099 my $sth = $dbh->prepare($stmt);
1100 $sth->execute(@bind);
1101
1102 # Just generate the WHERE clause
abe72f94 1103 my($stmt, @bind) = $sql->where(\%where, \@order);
32eab2da 1104
1105 # Return values in the same order, for hashed queries
1106 # See PERFORMANCE section for more details
1107 my @bind = $sql->values(\%fieldvals);
1108
1109=head1 DESCRIPTION
1110
1111This module was inspired by the excellent L<DBIx::Abstract>.
1112However, in using that module I found that what I really wanted
1113to do was generate SQL, but still retain complete control over my
1114statement handles and use the DBI interface. So, I set out to
1115create an abstract SQL generation module.
1116
1117While based on the concepts used by L<DBIx::Abstract>, there are
1118several important differences, especially when it comes to WHERE
1119clauses. I have modified the concepts used to make the SQL easier
1120to generate from Perl data structures and, IMO, more intuitive.
1121The underlying idea is for this module to do what you mean, based
1122on the data structures you provide it. The big advantage is that
1123you don't have to modify your code every time your data changes,
1124as this module figures it out.
1125
1126To begin with, an SQL INSERT is as easy as just specifying a hash
1127of C<key=value> pairs:
1128
1129 my %data = (
1130 name => 'Jimbo Bobson',
1131 phone => '123-456-7890',
1132 address => '42 Sister Lane',
1133 city => 'St. Louis',
1134 state => 'Louisiana',
1135 );
1136
1137The SQL can then be generated with this:
1138
1139 my($stmt, @bind) = $sql->insert('people', \%data);
1140
1141Which would give you something like this:
1142
1143 $stmt = "INSERT INTO people
1144 (address, city, name, phone, state)
1145 VALUES (?, ?, ?, ?, ?)";
1146 @bind = ('42 Sister Lane', 'St. Louis', 'Jimbo Bobson',
1147 '123-456-7890', 'Louisiana');
1148
1149These are then used directly in your DBI code:
1150
1151 my $sth = $dbh->prepare($stmt);
1152 $sth->execute(@bind);
1153
96449e8e 1154=head2 Inserting and Updating Arrays
1155
1156If your database has array types (like for example Postgres),
1157activate the special option C<< array_datatypes => 1 >>
1158when creating the C<SQL::Abstract> object.
1159Then you may use an arrayref to insert and update database array types:
1160
1161 my $sql = SQL::Abstract->new(array_datatypes => 1);
1162 my %data = (
1163 planets => [qw/Mercury Venus Earth Mars/]
1164 );
1165
1166 my($stmt, @bind) = $sql->insert('solar_system', \%data);
1167
1168This results in:
1169
1170 $stmt = "INSERT INTO solar_system (planets) VALUES (?)"
1171
1172 @bind = (['Mercury', 'Venus', 'Earth', 'Mars']);
1173
1174
1175=head2 Inserting and Updating SQL
1176
1177In order to apply SQL functions to elements of your C<%data> you may
1178specify a reference to an arrayref for the given hash value. For example,
1179if you need to execute the Oracle C<to_date> function on a value, you can
1180say something like this:
32eab2da 1181
1182 my %data = (
1183 name => 'Bill',
96449e8e 1184 date_entered => \["to_date(?,'MM/DD/YYYY')", "03/02/2003"],
32eab2da 1185 );
1186
1187The first value in the array is the actual SQL. Any other values are
1188optional and would be included in the bind values array. This gives
1189you:
1190
1191 my($stmt, @bind) = $sql->insert('people', \%data);
1192
1193 $stmt = "INSERT INTO people (name, date_entered)
1194 VALUES (?, to_date(?,'MM/DD/YYYY'))";
1195 @bind = ('Bill', '03/02/2003');
1196
1197An UPDATE is just as easy, all you change is the name of the function:
1198
1199 my($stmt, @bind) = $sql->update('people', \%data);
1200
1201Notice that your C<%data> isn't touched; the module will generate
1202the appropriately quirky SQL for you automatically. Usually you'll
1203want to specify a WHERE clause for your UPDATE, though, which is
1204where handling C<%where> hashes comes in handy...
1205
96449e8e 1206=head2 Complex where statements
1207
32eab2da 1208This module can generate pretty complicated WHERE statements
1209easily. For example, simple C<key=value> pairs are taken to mean
1210equality, and if you want to see if a field is within a set
1211of values, you can use an arrayref. Let's say we wanted to
1212SELECT some data based on this criteria:
1213
1214 my %where = (
1215 requestor => 'inna',
1216 worker => ['nwiger', 'rcwe', 'sfz'],
1217 status => { '!=', 'completed' }
1218 );
1219
1220 my($stmt, @bind) = $sql->select('tickets', '*', \%where);
1221
1222The above would give you something like this:
1223
1224 $stmt = "SELECT * FROM tickets WHERE
1225 ( requestor = ? ) AND ( status != ? )
1226 AND ( worker = ? OR worker = ? OR worker = ? )";
1227 @bind = ('inna', 'completed', 'nwiger', 'rcwe', 'sfz');
1228
1229Which you could then use in DBI code like so:
1230
1231 my $sth = $dbh->prepare($stmt);
1232 $sth->execute(@bind);
1233
1234Easy, eh?
1235
1236=head1 FUNCTIONS
1237
1238The functions are simple. There's one for each major SQL operation,
1239and a constructor you use first. The arguments are specified in a
1240similar order to each function (table, then fields, then a where
1241clause) to try and simplify things.
1242
83cab70b 1243
83cab70b 1244
32eab2da 1245
1246=head2 new(option => 'value')
1247
1248The C<new()> function takes a list of options and values, and returns
1249a new B<SQL::Abstract> object which can then be used to generate SQL
1250through the methods below. The options accepted are:
1251
1252=over
1253
1254=item case
1255
1256If set to 'lower', then SQL will be generated in all lowercase. By
1257default SQL is generated in "textbook" case meaning something like:
1258
1259 SELECT a_field FROM a_table WHERE some_field LIKE '%someval%'
1260
96449e8e 1261Any setting other than 'lower' is ignored.
1262
32eab2da 1263=item cmp
1264
1265This determines what the default comparison operator is. By default
1266it is C<=>, meaning that a hash like this:
1267
1268 %where = (name => 'nwiger', email => 'nate@wiger.org');
1269
1270Will generate SQL like this:
1271
1272 WHERE name = 'nwiger' AND email = 'nate@wiger.org'
1273
1274However, you may want loose comparisons by default, so if you set
1275C<cmp> to C<like> you would get SQL such as:
1276
1277 WHERE name like 'nwiger' AND email like 'nate@wiger.org'
1278
1279You can also override the comparsion on an individual basis - see
1280the huge section on L</"WHERE CLAUSES"> at the bottom.
1281
96449e8e 1282=item sqltrue, sqlfalse
1283
1284Expressions for inserting boolean values within SQL statements.
1285By default these are C<1=1> and C<1=0>.
1286
32eab2da 1287=item logic
1288
1289This determines the default logical operator for multiple WHERE
1290statements in arrays. By default it is "or", meaning that a WHERE
1291array of the form:
1292
1293 @where = (
1294 event_date => {'>=', '2/13/99'},
1295 event_date => {'<=', '4/24/03'},
1296 );
1297
1298Will generate SQL like this:
1299
1300 WHERE event_date >= '2/13/99' OR event_date <= '4/24/03'
1301
1302This is probably not what you want given this query, though (look
1303at the dates). To change the "OR" to an "AND", simply specify:
1304
1305 my $sql = SQL::Abstract->new(logic => 'and');
1306
1307Which will change the above C<WHERE> to:
1308
1309 WHERE event_date >= '2/13/99' AND event_date <= '4/24/03'
1310
96449e8e 1311The logic can also be changed locally by inserting
1312an extra first element in the array :
1313
1314 @where = (-and => event_date => {'>=', '2/13/99'},
1315 event_date => {'<=', '4/24/03'} );
1316
1317See the L</"WHERE CLAUSES"> section for explanations.
1318
32eab2da 1319=item convert
1320
1321This will automatically convert comparisons using the specified SQL
1322function for both column and value. This is mostly used with an argument
1323of C<upper> or C<lower>, so that the SQL will have the effect of
1324case-insensitive "searches". For example, this:
1325
1326 $sql = SQL::Abstract->new(convert => 'upper');
1327 %where = (keywords => 'MaKe iT CAse inSeNSItive');
1328
1329Will turn out the following SQL:
1330
1331 WHERE upper(keywords) like upper('MaKe iT CAse inSeNSItive')
1332
1333The conversion can be C<upper()>, C<lower()>, or any other SQL function
1334that can be applied symmetrically to fields (actually B<SQL::Abstract> does
1335not validate this option; it will just pass through what you specify verbatim).
1336
1337=item bindtype
1338
1339This is a kludge because many databases suck. For example, you can't
1340just bind values using DBI's C<execute()> for Oracle C<CLOB> or C<BLOB> fields.
1341Instead, you have to use C<bind_param()>:
1342
1343 $sth->bind_param(1, 'reg data');
1344 $sth->bind_param(2, $lots, {ora_type => ORA_CLOB});
1345
1346The problem is, B<SQL::Abstract> will normally just return a C<@bind> array,
1347which loses track of which field each slot refers to. Fear not.
1348
1349If you specify C<bindtype> in new, you can determine how C<@bind> is returned.
1350Currently, you can specify either C<normal> (default) or C<columns>. If you
1351specify C<columns>, you will get an array that looks like this:
1352
1353 my $sql = SQL::Abstract->new(bindtype => 'columns');
1354 my($stmt, @bind) = $sql->insert(...);
1355
1356 @bind = (
1357 [ 'column1', 'value1' ],
1358 [ 'column2', 'value2' ],
1359 [ 'column3', 'value3' ],
1360 );
1361
1362You can then iterate through this manually, using DBI's C<bind_param()>.
e3f9dff4 1363
32eab2da 1364 $sth->prepare($stmt);
1365 my $i = 1;
1366 for (@bind) {
1367 my($col, $data) = @$_;
1368 if ($col eq 'details' || $col eq 'comments') {
1369 $sth->bind_param($i, $data, {ora_type => ORA_CLOB});
1370 } elsif ($col eq 'image') {
1371 $sth->bind_param($i, $data, {ora_type => ORA_BLOB});
1372 } else {
1373 $sth->bind_param($i, $data);
1374 }
1375 $i++;
1376 }
1377 $sth->execute; # execute without @bind now
1378
1379Now, why would you still use B<SQL::Abstract> if you have to do this crap?
1380Basically, the advantage is still that you don't have to care which fields
1381are or are not included. You could wrap that above C<for> loop in a simple
1382sub called C<bind_fields()> or something and reuse it repeatedly. You still
1383get a layer of abstraction over manual SQL specification.
1384
1385=item quote_char
1386
1387This is the character that a table or column name will be quoted
1388with. By default this is an empty string, but you could set it to
1389the character C<`>, to generate SQL like this:
1390
1391 SELECT `a_field` FROM `a_table` WHERE `some_field` LIKE '%someval%'
1392
96449e8e 1393Alternatively, you can supply an array ref of two items, the first being the left
1394hand quote character, and the second the right hand quote character. For
1395example, you could supply C<['[',']']> for SQL Server 2000 compliant quotes
1396that generates SQL like this:
1397
1398 SELECT [a_field] FROM [a_table] WHERE [some_field] LIKE '%someval%'
1399
1400Quoting is useful if you have tables or columns names that are reserved
1401words in your database's SQL dialect.
32eab2da 1402
1403=item name_sep
1404
1405This is the character that separates a table and column name. It is
1406necessary to specify this when the C<quote_char> option is selected,
1407so that tables and column names can be individually quoted like this:
1408
1409 SELECT `table`.`one_field` FROM `table` WHERE `table`.`other_field` = 1
1410
96449e8e 1411=item array_datatypes
32eab2da 1412
96449e8e 1413When this option is true, arrayrefs in INSERT or UPDATE are
1414interpreted as array datatypes and are passed directly
1415to the DBI layer.
1416When this option is false, arrayrefs are interpreted
1417as literal SQL, just like refs to arrayrefs
1418(but this behavior is for backwards compatibility; when writing
1419new queries, use the "reference to arrayref" syntax
1420for literal SQL).
32eab2da 1421
32eab2da 1422
96449e8e 1423=item special_ops
32eab2da 1424
96449e8e 1425Takes a reference to a list of "special operators"
1426to extend the syntax understood by L<SQL::Abstract>.
1427See section L</"SPECIAL OPERATORS"> for details.
32eab2da 1428
32eab2da 1429
32eab2da 1430
96449e8e 1431=back
32eab2da 1432
1433=head2 insert($table, \@values || \%fieldvals)
1434
1435This is the simplest function. You simply give it a table name
1436and either an arrayref of values or hashref of field/value pairs.
1437It returns an SQL INSERT statement and a list of bind values.
96449e8e 1438See the sections on L</"Inserting and Updating Arrays"> and
1439L</"Inserting and Updating SQL"> for information on how to insert
1440with those data types.
32eab2da 1441
1442=head2 update($table, \%fieldvals, \%where)
1443
1444This takes a table, hashref of field/value pairs, and an optional
86298391 1445hashref L<WHERE clause|/WHERE CLAUSES>. It returns an SQL UPDATE function and a list
32eab2da 1446of bind values.
96449e8e 1447See the sections on L</"Inserting and Updating Arrays"> and
1448L</"Inserting and Updating SQL"> for information on how to insert
1449with those data types.
32eab2da 1450
96449e8e 1451=head2 select($source, $fields, $where, $order)
32eab2da 1452
96449e8e 1453This returns a SQL SELECT statement and associated list of bind values, as
1454specified by the arguments :
32eab2da 1455
96449e8e 1456=over
32eab2da 1457
96449e8e 1458=item $source
32eab2da 1459
96449e8e 1460Specification of the 'FROM' part of the statement.
1461The argument can be either a plain scalar (interpreted as a table
1462name, will be quoted), or an arrayref (interpreted as a list
1463of table names, joined by commas, quoted), or a scalarref
1464(literal table name, not quoted), or a ref to an arrayref
1465(list of literal table names, joined by commas, not quoted).
32eab2da 1466
96449e8e 1467=item $fields
32eab2da 1468
96449e8e 1469Specification of the list of fields to retrieve from
1470the source.
1471The argument can be either an arrayref (interpreted as a list
1472of field names, will be joined by commas and quoted), or a
1473plain scalar (literal SQL, not quoted).
1474Please observe that this API is not as flexible as for
e3f9dff4 1475the first argument C<$table>, for backwards compatibility reasons.
32eab2da 1476
96449e8e 1477=item $where
32eab2da 1478
96449e8e 1479Optional argument to specify the WHERE part of the query.
1480The argument is most often a hashref, but can also be
1481an arrayref or plain scalar --
1482see section L<WHERE clause|/"WHERE CLAUSES"> for details.
32eab2da 1483
96449e8e 1484=item $order
32eab2da 1485
96449e8e 1486Optional argument to specify the ORDER BY part of the query.
1487The argument can be a scalar, a hashref or an arrayref
1488-- see section L<ORDER BY clause|/"ORDER BY CLAUSES">
1489for details.
32eab2da 1490
96449e8e 1491=back
32eab2da 1492
32eab2da 1493
1494=head2 delete($table, \%where)
1495
86298391 1496This takes a table name and optional hashref L<WHERE clause|/WHERE CLAUSES>.
32eab2da 1497It returns an SQL DELETE statement and list of bind values.
1498
32eab2da 1499=head2 where(\%where, \@order)
1500
1501This is used to generate just the WHERE clause. For example,
1502if you have an arbitrary data structure and know what the
1503rest of your SQL is going to look like, but want an easy way
1504to produce a WHERE clause, use this. It returns an SQL WHERE
1505clause and list of bind values.
1506
32eab2da 1507
1508=head2 values(\%data)
1509
1510This just returns the values from the hash C<%data>, in the same
1511order that would be returned from any of the other above queries.
1512Using this allows you to markedly speed up your queries if you
1513are affecting lots of rows. See below under the L</"PERFORMANCE"> section.
1514
32eab2da 1515=head2 generate($any, 'number', $of, \@data, $struct, \%types)
1516
1517Warning: This is an experimental method and subject to change.
1518
1519This returns arbitrarily generated SQL. It's a really basic shortcut.
1520It will return two different things, depending on return context:
1521
1522 my($stmt, @bind) = $sql->generate('create table', \$table, \@fields);
1523 my $stmt_and_val = $sql->generate('create table', \$table, \@fields);
1524
1525These would return the following:
1526
1527 # First calling form
1528 $stmt = "CREATE TABLE test (?, ?)";
1529 @bind = (field1, field2);
1530
1531 # Second calling form
1532 $stmt_and_val = "CREATE TABLE test (field1, field2)";
1533
1534Depending on what you're trying to do, it's up to you to choose the correct
1535format. In this example, the second form is what you would want.
1536
1537By the same token:
1538
1539 $sql->generate('alter session', { nls_date_format => 'MM/YY' });
1540
1541Might give you:
1542
1543 ALTER SESSION SET nls_date_format = 'MM/YY'
1544
1545You get the idea. Strings get their case twiddled, but everything
1546else remains verbatim.
1547
32eab2da 1548
32eab2da 1549
32eab2da 1550
1551=head1 WHERE CLAUSES
1552
96449e8e 1553=head2 Introduction
1554
32eab2da 1555This module uses a variation on the idea from L<DBIx::Abstract>. It
1556is B<NOT>, repeat I<not> 100% compatible. B<The main logic of this
1557module is that things in arrays are OR'ed, and things in hashes
1558are AND'ed.>
1559
1560The easiest way to explain is to show lots of examples. After
1561each C<%where> hash shown, it is assumed you used:
1562
1563 my($stmt, @bind) = $sql->where(\%where);
1564
1565However, note that the C<%where> hash can be used directly in any
1566of the other functions as well, as described above.
1567
96449e8e 1568=head2 Key-value pairs
1569
32eab2da 1570So, let's get started. To begin, a simple hash:
1571
1572 my %where = (
1573 user => 'nwiger',
1574 status => 'completed'
1575 );
1576
1577Is converted to SQL C<key = val> statements:
1578
1579 $stmt = "WHERE user = ? AND status = ?";
1580 @bind = ('nwiger', 'completed');
1581
1582One common thing I end up doing is having a list of values that
1583a field can be in. To do this, simply specify a list inside of
1584an arrayref:
1585
1586 my %where = (
1587 user => 'nwiger',
1588 status => ['assigned', 'in-progress', 'pending'];
1589 );
1590
1591This simple code will create the following:
1592
1593 $stmt = "WHERE user = ? AND ( status = ? OR status = ? OR status = ? )";
1594 @bind = ('nwiger', 'assigned', 'in-progress', 'pending');
1595
96449e8e 1596An empty arrayref will be considered a logical false and
8a68b5be 1597will generate 0=1.
1598
96449e8e 1599=head2 Key-value pairs
1600
32eab2da 1601If you want to specify a different type of operator for your comparison,
1602you can use a hashref for a given column:
1603
1604 my %where = (
1605 user => 'nwiger',
1606 status => { '!=', 'completed' }
1607 );
1608
1609Which would generate:
1610
1611 $stmt = "WHERE user = ? AND status != ?";
1612 @bind = ('nwiger', 'completed');
1613
1614To test against multiple values, just enclose the values in an arrayref:
1615
1616 status => { '!=', ['assigned', 'in-progress', 'pending'] };
1617
1618Which would give you:
1619
96449e8e 1620 "WHERE status != ? AND status != ? AND status != ?"
32eab2da 1621
96449e8e 1622Notice that since the operator was recognized as being a 'negative'
1623operator, the arrayref was interpreted with 'AND' logic (because
1624of Morgan's laws). By contrast, the reverse
1625
1626 status => { '=', ['assigned', 'in-progress', 'pending'] };
1627
1628would generate :
1629
1630 "WHERE status = ? OR status = ? OR status = ?"
1631
1632
1633The hashref can also contain multiple pairs, in which case it is expanded
32eab2da 1634into an C<AND> of its elements:
1635
1636 my %where = (
1637 user => 'nwiger',
1638 status => { '!=', 'completed', -not_like => 'pending%' }
1639 );
1640
1641 # Or more dynamically, like from a form
1642 $where{user} = 'nwiger';
1643 $where{status}{'!='} = 'completed';
1644 $where{status}{'-not_like'} = 'pending%';
1645
1646 # Both generate this
1647 $stmt = "WHERE user = ? AND status != ? AND status NOT LIKE ?";
1648 @bind = ('nwiger', 'completed', 'pending%');
1649
96449e8e 1650
32eab2da 1651To get an OR instead, you can combine it with the arrayref idea:
1652
1653 my %where => (
1654 user => 'nwiger',
1655 priority => [ {'=', 2}, {'!=', 1} ]
1656 );
1657
1658Which would generate:
1659
1660 $stmt = "WHERE user = ? AND priority = ? OR priority != ?";
1661 @bind = ('nwiger', '2', '1');
1662
44b9e502 1663If you want to include literal SQL (with or without bind values), just use a
1664scalar reference or array reference as the value:
1665
1666 my %where = (
1667 date_entered => { '>' => \["to_date(?, 'MM/DD/YYYY')", "11/26/2008"] },
1668 date_expires => { '<' => \"now()" }
1669 );
1670
1671Which would generate:
1672
1673 $stmt = "WHERE date_entered > "to_date(?, 'MM/DD/YYYY') AND date_expires < now()";
1674 @bind = ('11/26/2008');
1675
96449e8e 1676
1677=head2 Logic and nesting operators
1678
1679In the example above,
1680there is a subtle trap if you want to say something like
32eab2da 1681this (notice the C<AND>):
1682
1683 WHERE priority != ? AND priority != ?
1684
1685Because, in Perl you I<can't> do this:
1686
1687 priority => { '!=', 2, '!=', 1 }
1688
1689As the second C<!=> key will obliterate the first. The solution
1690is to use the special C<-modifier> form inside an arrayref:
1691
96449e8e 1692 priority => [ -and => {'!=', 2},
1693 {'!=', 1} ]
1694
32eab2da 1695
1696Normally, these would be joined by C<OR>, but the modifier tells it
1697to use C<AND> instead. (Hint: You can use this in conjunction with the
1698C<logic> option to C<new()> in order to change the way your queries
1699work by default.) B<Important:> Note that the C<-modifier> goes
1700B<INSIDE> the arrayref, as an extra first element. This will
1701B<NOT> do what you think it might:
1702
1703 priority => -and => [{'!=', 2}, {'!=', 1}] # WRONG!
1704
1705Here is a quick list of equivalencies, since there is some overlap:
1706
1707 # Same
1708 status => {'!=', 'completed', 'not like', 'pending%' }
1709 status => [ -and => {'!=', 'completed'}, {'not like', 'pending%'}]
1710
1711 # Same
1712 status => {'=', ['assigned', 'in-progress']}
1713 status => [ -or => {'=', 'assigned'}, {'=', 'in-progress'}]
1714 status => [ {'=', 'assigned'}, {'=', 'in-progress'} ]
1715
1716In addition to C<-and> and C<-or>, there is also a special C<-nest>
1717operator which adds an additional set of parens, to create a subquery.
1718For example, to get something like this:
1719
86298391 1720 $stmt = "WHERE user = ? AND ( workhrs > ? OR geo = ? )";
32eab2da 1721 @bind = ('nwiger', '20', 'ASIA');
1722
1723You would do:
1724
1725 my %where = (
1726 user => 'nwiger',
1727 -nest => [ workhrs => {'>', 20}, geo => 'ASIA' ],
1728 );
1729
e3f9dff4 1730If you need several nested subexpressions, you can number
1731the C<-nest> branches :
1732
1733 my %where = (
1734 user => 'nwiger',
1735 -nest1 => ...,
1736 -nest2 => ...,
1737 ...
1738 );
1739
1740
96449e8e 1741=head2 Special operators : IN, BETWEEN, etc.
1742
32eab2da 1743You can also use the hashref format to compare a list of fields using the
1744C<IN> comparison operator, by specifying the list as an arrayref:
1745
1746 my %where = (
1747 status => 'completed',
1748 reportid => { -in => [567, 2335, 2] }
1749 );
1750
1751Which would generate:
1752
1753 $stmt = "WHERE status = ? AND reportid IN (?,?,?)";
1754 @bind = ('completed', '567', '2335', '2');
1755
96449e8e 1756The reverse operator C<-not_in> generates SQL C<NOT IN> and is used in
1757the same way.
1758
1759Another pair of operators is C<-between> and C<-not_between>,
1760used with an arrayref of two values:
32eab2da 1761
1762 my %where = (
1763 user => 'nwiger',
1764 completion_date => {
1765 -not_between => ['2002-10-01', '2003-02-06']
1766 }
1767 );
1768
1769Would give you:
1770
1771 WHERE user = ? AND completion_date NOT BETWEEN ( ? AND ? )
1772
96449e8e 1773These are the two builtin "special operators"; but the
1774list can be expanded : see section L</"SPECIAL OPERATORS"> below.
1775
1776=head2 Nested conditions
1777
32eab2da 1778So far, we've seen how multiple conditions are joined with a top-level
1779C<AND>. We can change this by putting the different conditions we want in
1780hashes and then putting those hashes in an array. For example:
1781
1782 my @where = (
1783 {
1784 user => 'nwiger',
1785 status => { -like => ['pending%', 'dispatched'] },
1786 },
1787 {
1788 user => 'robot',
1789 status => 'unassigned',
1790 }
1791 );
1792
1793This data structure would create the following:
1794
1795 $stmt = "WHERE ( user = ? AND ( status LIKE ? OR status LIKE ? ) )
1796 OR ( user = ? AND status = ? ) )";
1797 @bind = ('nwiger', 'pending', 'dispatched', 'robot', 'unassigned');
1798
1799This can be combined with the C<-nest> operator to properly group
1800SQL statements:
1801
1802 my @where = (
1803 -and => [
1804 user => 'nwiger',
1805 -nest => [
d2a8fe1a 1806 ["-and", workhrs => {'>', 20}, geo => 'ASIA' ],
1807 ["-and", workhrs => {'<', 50}, geo => 'EURO' ]
32eab2da 1808 ],
1809 ],
1810 );
1811
1812That would yield:
1813
1814 WHERE ( user = ? AND
1815 ( ( workhrs > ? AND geo = ? )
1816 OR ( workhrs < ? AND geo = ? ) ) )
1817
96449e8e 1818=head2 Literal SQL
1819
32eab2da 1820Finally, sometimes only literal SQL will do. If you want to include
1821literal SQL verbatim, you can specify it as a scalar reference, namely:
1822
1823 my $inn = 'is Not Null';
1824 my %where = (
1825 priority => { '<', 2 },
1826 requestor => \$inn
1827 );
1828
1829This would create:
1830
1831 $stmt = "WHERE priority < ? AND requestor is Not Null";
1832 @bind = ('2');
1833
1834Note that in this example, you only get one bind parameter back, since
1835the verbatim SQL is passed as part of the statement.
1836
1837Of course, just to prove a point, the above can also be accomplished
1838with this:
1839
1840 my %where = (
1841 priority => { '<', 2 },
1842 requestor => { '!=', undef },
1843 );
1844
96449e8e 1845
32eab2da 1846TMTOWTDI.
1847
96449e8e 1848Conditions on boolean columns can be expressed in the
1849same way, passing a reference to an empty string :
1850
1851 my %where = (
1852 priority => { '<', 2 },
1853 is_ready => \"";
1854 );
1855
1856which yields
1857
1858 $stmt = "WHERE priority < ? AND is_ready";
1859 @bind = ('2');
1860
1861
1862=head2 Literal SQL with placeholders and bind values (subqueries)
1863
1864If the literal SQL to be inserted has placeholders and bind values,
1865use a reference to an arrayref (yes this is a double reference --
1866not so common, but perfectly legal Perl). For example, to find a date
1867in Postgres you can use something like this:
1868
1869 my %where = (
1870 date_column => \[q/= date '2008-09-30' - ?::integer/, 10/]
1871 )
1872
1873This would create:
1874
d2a8fe1a 1875 $stmt = "WHERE ( date_column = date '2008-09-30' - ?::integer )"
96449e8e 1876 @bind = ('10');
1877
1878
1879Literal SQL is especially useful for nesting parenthesized clauses in the
1880main SQL query. Here is a first example :
1881
1882 my ($sub_stmt, @sub_bind) = ("SELECT c1 FROM t1 WHERE c2 < ? AND c3 LIKE ?",
1883 100, "foo%");
1884 my %where = (
1885 foo => 1234,
1886 bar => \["IN ($sub_stmt)" => @sub_bind],
1887 );
1888
1889This yields :
1890
1891 $stmt = "WHERE (foo = ? AND bar IN (SELECT c1 FROM t1
1892 WHERE c2 < ? AND c3 LIKE ?))";
1893 @bind = (1234, 100, "foo%");
1894
1895Other subquery operators, like for example C<"E<gt> ALL"> or C<"NOT IN">,
1896are expressed in the same way. Of course the C<$sub_stmt> and
1897its associated bind values can be generated through a former call
1898to C<select()> :
1899
1900 my ($sub_stmt, @sub_bind)
1901 = $sql->select("t1", "c1", {c2 => {"<" => 100},
1902 c3 => {-like => "foo%"}});
1903 my %where = (
1904 foo => 1234,
1905 bar => \["> ALL ($sub_stmt)" => @sub_bind],
1906 );
1907
1908In the examples above, the subquery was used as an operator on a column;
1909but the same principle also applies for a clause within the main C<%where>
1910hash, like an EXISTS subquery :
1911
1912 my ($sub_stmt, @sub_bind)
1913 = $sql->select("t1", "*", {c1 => 1, c2 => \"> t0.c0"});
1914 my %where = (
1915 foo => 1234,
1916 -nest => \["EXISTS ($sub_stmt)" => @sub_bind],
1917 );
1918
1919which yields
1920
1921 $stmt = "WHERE (foo = ? AND EXISTS (SELECT * FROM t1
1922 WHERE c1 = ? AND c2 > t0.c0))";
1923 @bind = (1234, 1);
1924
1925
1926Observe that the condition on C<c2> in the subquery refers to
1927column C<t0.c0> of the main query : this is I<not> a bind
1928value, so we have to express it through a scalar ref.
1929Writing C<< c2 => {">" => "t0.c0"} >> would have generated
1930C<< c2 > ? >> with bind value C<"t0.c0"> ... not exactly
1931what we wanted here.
1932
1933Another use of the subquery technique is when some SQL clauses need
1934parentheses, as it often occurs with some proprietary SQL extensions
1935like for example fulltext expressions, geospatial expressions,
1936NATIVE clauses, etc. Here is an example of a fulltext query in MySQL :
1937
1938 my %where = (
1939 -nest => \["MATCH (col1, col2) AGAINST (?)" => qw/apples/]
1940 );
1941
1942Finally, here is an example where a subquery is used
1943for expressing unary negation:
1944
1945 my ($sub_stmt, @sub_bind)
1946 = $sql->where({age => [{"<" => 10}, {">" => 20}]});
1947 $sub_stmt =~ s/^ where //i; # don't want "WHERE" in the subclause
1948 my %where = (
1949 lname => {like => '%son%'},
1950 -nest => \["NOT ($sub_stmt)" => @sub_bind],
1951 );
1952
1953This yields
1954
1955 $stmt = "lname LIKE ? AND NOT ( age < ? OR age > ? )"
1956 @bind = ('%son%', 10, 20)
1957
1958
1959
1960=head2 Conclusion
1961
32eab2da 1962These pages could go on for a while, since the nesting of the data
1963structures this module can handle are pretty much unlimited (the
1964module implements the C<WHERE> expansion as a recursive function
1965internally). Your best bet is to "play around" with the module a
1966little to see how the data structures behave, and choose the best
1967format for your data based on that.
1968
1969And of course, all the values above will probably be replaced with
1970variables gotten from forms or the command line. After all, if you
1971knew everything ahead of time, you wouldn't have to worry about
1972dynamically-generating SQL and could just hardwire it into your
1973script.
1974
96449e8e 1975
1976
1977
86298391 1978=head1 ORDER BY CLAUSES
1979
1980Some functions take an order by clause. This can either be a scalar (just a
1981column name,) a hash of C<< { -desc => 'col' } >> or C<< { -asc => 'col' } >>,
1cfa1db3 1982or an array of either of the two previous forms. Examples:
1983
1984 Given | Will Generate
1985 ----------------------------------------------------------
1986 \'colA DESC' | ORDER BY colA DESC
1987 'colA' | ORDER BY colA
1988 [qw/colA colB/] | ORDER BY colA, colB
1989 {-asc => 'colA'} | ORDER BY colA ASC
1990 {-desc => 'colB'} | ORDER BY colB DESC
1991 [ |
1992 {-asc => 'colA'}, | ORDER BY colA ASC, colB DESC
1993 {-desc => 'colB'} |
1994 ] |
1995 [colA => {-asc => 'colB'}] | ORDER BY colA, colB ASC
1996 ==========================================================
86298391 1997
96449e8e 1998
1999
2000=head1 SPECIAL OPERATORS
2001
e3f9dff4 2002 my $sqlmaker = SQL::Abstract->new(special_ops => [
2003 {regex => qr/.../,
2004 handler => sub {
2005 my ($self, $field, $op, $arg) = @_;
2006 ...
2007 },
2008 },
2009 ]);
2010
2011A "special operator" is a SQL syntactic clause that can be
2012applied to a field, instead of a usual binary operator.
2013For example :
2014
2015 WHERE field IN (?, ?, ?)
2016 WHERE field BETWEEN ? AND ?
2017 WHERE MATCH(field) AGAINST (?, ?)
96449e8e 2018
e3f9dff4 2019Special operators IN and BETWEEN are fairly standard and therefore
2020are builtin within C<SQL::Abstract>. For other operators,
2021like the MATCH .. AGAINST example above which is
2022specific to MySQL, you can write your own operator handlers :
2023supply a C<special_ops> argument to the C<new> method.
2024That argument takes an arrayref of operator definitions;
2025each operator definition is a hashref with two entries
96449e8e 2026
e3f9dff4 2027=over
2028
2029=item regex
2030
2031the regular expression to match the operator
96449e8e 2032
e3f9dff4 2033=item handler
2034
2035coderef that will be called when meeting that operator
2036in the input tree. The coderef will be called with
2037arguments C<< ($self, $field, $op, $arg) >>, and
2038should return a C<< ($sql, @bind) >> structure.
2039
2040=back
2041
2042For example, here is an implementation
2043of the MATCH .. AGAINST syntax for MySQL
2044
2045 my $sqlmaker = SQL::Abstract->new(special_ops => [
2046
2047 # special op for MySql MATCH (field) AGAINST(word1, word2, ...)
2048 {regex => qr/^match$/i,
2049 handler => sub {
2050 my ($self, $field, $op, $arg) = @_;
2051 $arg = [$arg] if not ref $arg;
2052 my $label = $self->_quote($field);
2053 my ($placeholder) = $self->_convert('?');
2054 my $placeholders = join ", ", (($placeholder) x @$arg);
2055 my $sql = $self->_sqlcase('match') . " ($label) "
2056 . $self->_sqlcase('against') . " ($placeholders) ";
2057 my @bind = $self->_bindtype($field, @$arg);
2058 return ($sql, @bind);
2059 }
2060 },
2061
2062 ]);
96449e8e 2063
2064
32eab2da 2065=head1 PERFORMANCE
2066
2067Thanks to some benchmarking by Mark Stosberg, it turns out that
2068this module is many orders of magnitude faster than using C<DBIx::Abstract>.
2069I must admit this wasn't an intentional design issue, but it's a
2070byproduct of the fact that you get to control your C<DBI> handles
2071yourself.
2072
2073To maximize performance, use a code snippet like the following:
2074
2075 # prepare a statement handle using the first row
2076 # and then reuse it for the rest of the rows
2077 my($sth, $stmt);
2078 for my $href (@array_of_hashrefs) {
2079 $stmt ||= $sql->insert('table', $href);
2080 $sth ||= $dbh->prepare($stmt);
2081 $sth->execute($sql->values($href));
2082 }
2083
2084The reason this works is because the keys in your C<$href> are sorted
2085internally by B<SQL::Abstract>. Thus, as long as your data retains
2086the same structure, you only have to generate the SQL the first time
2087around. On subsequent queries, simply use the C<values> function provided
2088by this module to return your values in the correct order.
2089
96449e8e 2090
32eab2da 2091=head1 FORMBUILDER
2092
2093If you use my C<CGI::FormBuilder> module at all, you'll hopefully
2094really like this part (I do, at least). Building up a complex query
2095can be as simple as the following:
2096
2097 #!/usr/bin/perl
2098
2099 use CGI::FormBuilder;
2100 use SQL::Abstract;
2101
2102 my $form = CGI::FormBuilder->new(...);
2103 my $sql = SQL::Abstract->new;
2104
2105 if ($form->submitted) {
2106 my $field = $form->field;
2107 my $id = delete $field->{id};
2108 my($stmt, @bind) = $sql->update('table', $field, {id => $id});
2109 }
2110
2111Of course, you would still have to connect using C<DBI> to run the
2112query, but the point is that if you make your form look like your
2113table, the actual query script can be extremely simplistic.
2114
2115If you're B<REALLY> lazy (I am), check out C<HTML::QuickTable> for
2116a fast interface to returning and formatting data. I frequently
2117use these three modules together to write complex database query
2118apps in under 50 lines.
2119
32eab2da 2120
96449e8e 2121=head1 CHANGES
2122
2123Version 1.50 was a major internal refactoring of C<SQL::Abstract>.
2124Great care has been taken to preserve the I<published> behavior
2125documented in previous versions in the 1.* family; however,
2126some features that were previously undocumented, or behaved
2127differently from the documentation, had to be changed in order
2128to clarify the semantics. Hence, client code that was relying
2129on some dark areas of C<SQL::Abstract> v1.*
2130B<might behave differently> in v1.50.
32eab2da 2131
d2a8fe1a 2132The main changes are :
2133
96449e8e 2134=over
32eab2da 2135
96449e8e 2136=item *
32eab2da 2137
96449e8e 2138support for literal SQL through the C<< \ [$sql, bind] >> syntax.
2139
2140=item *
2141
145fbfc8 2142support for the { operator => \"..." } construct (to embed literal SQL)
2143
2144=item *
2145
9c37b9c0 2146support for the { operator => \["...", @bind] } construct (to embed literal SQL with bind values)
2147
2148=item *
2149
96449e8e 2150added -nest1, -nest2 or -nest_1, -nest_2, ...
2151
2152=item *
2153
2154optional support for L<array datatypes|/"Inserting and Updating Arrays">
2155
2156=item *
2157
2158defensive programming : check arguments
2159
2160=item *
2161
2162fixed bug with global logic, which was previously implemented
2163through global variables yielding side-effects. Prior versons would
2164interpret C<< [ {cond1, cond2}, [cond3, cond4] ] >>
2165as C<< "(cond1 AND cond2) OR (cond3 AND cond4)" >>.
2166Now this is interpreted
2167as C<< "(cond1 AND cond2) OR (cond3 OR cond4)" >>.
2168
2169=item *
2170
2171C<-and> / C<-or> operators are no longer accepted
2172in the middle of an arrayref : they are
2173only admitted if in first position.
2174
2175=item *
2176
2177changed logic for distributing an op over arrayrefs
2178
2179=item *
2180
2181fixed semantics of _bindtype on array args
2182
2183=item *
2184
2185dropped the C<_anoncopy> of the %where tree. No longer necessary,
2186we just avoid shifting arrays within that tree.
2187
2188=item *
2189
2190dropped the C<_modlogic> function
2191
2192=back
32eab2da 2193
32eab2da 2194
32eab2da 2195
2196=head1 ACKNOWLEDGEMENTS
2197
2198There are a number of individuals that have really helped out with
2199this module. Unfortunately, most of them submitted bugs via CPAN
2200so I have no idea who they are! But the people I do know are:
2201
86298391 2202 Ash Berlin (order_by hash term support)
b643abe1 2203 Matt Trout (DBIx::Class support)
32eab2da 2204 Mark Stosberg (benchmarking)
2205 Chas Owens (initial "IN" operator support)
2206 Philip Collins (per-field SQL functions)
2207 Eric Kolve (hashref "AND" support)
2208 Mike Fragassi (enhancements to "BETWEEN" and "LIKE")
2209 Dan Kubb (support for "quote_char" and "name_sep")
f5aab26e 2210 Guillermo Roditi (patch to cleanup "IN" and "BETWEEN", fix and tests for _order_by)
96449e8e 2211 Laurent Dami (internal refactoring, multiple -nest, extensible list of special operators, literal SQL)
dbdf7648 2212 Norbert Buchmuller (support for literal SQL in hashpair, misc. fixes & tests)
32eab2da 2213
2214Thanks!
2215
32eab2da 2216=head1 SEE ALSO
2217
86298391 2218L<DBIx::Class>, L<DBIx::Abstract>, L<CGI::FormBuilder>, L<HTML::QuickTable>.
32eab2da 2219
32eab2da 2220=head1 AUTHOR
2221
b643abe1 2222Copyright (c) 2001-2007 Nathan Wiger <nwiger@cpan.org>. All Rights Reserved.
2223
2224This module is actively maintained by Matt Trout <mst@shadowcatsystems.co.uk>
32eab2da 2225
abe72f94 2226For support, your best bet is to try the C<DBIx::Class> users mailing list.
2227While not an official support venue, C<DBIx::Class> makes heavy use of
2228C<SQL::Abstract>, and as such list members there are very familiar with
2229how to create queries.
2230
32eab2da 2231This module is free software; you may copy this under the terms of
2232the GNU General Public License, or the Artistic License, copies of
2233which should have accompanied your Perl kit.
2234
2235=cut
2236