1SQL::Abstract(3)      User Contributed Perl Documentation     SQL::Abstract(3)
2
3
4

NAME

6       SQL::Abstract - Generate SQL from Perl data structures
7

SYNOPSIS

9           use SQL::Abstract;
10
11           my $sql = SQL::Abstract->new;
12
13           my($stmt, @bind) = $sql->select($source, \@fields, \%where, $order);
14
15           my($stmt, @bind) = $sql->insert($table, \%fieldvals || \@values);
16
17           my($stmt, @bind) = $sql->update($table, \%fieldvals, \%where);
18
19           my($stmt, @bind) = $sql->delete($table, \%where);
20
21           # Then, use these in your DBI statements
22           my $sth = $dbh->prepare($stmt);
23           $sth->execute(@bind);
24
25           # Just generate the WHERE clause
26           my($stmt, @bind) = $sql->where(\%where, $order);
27
28           # Return values in the same order, for hashed queries
29           # See PERFORMANCE section for more details
30           my @bind = $sql->values(\%fieldvals);
31

DESCRIPTION

33       This module was inspired by the excellent DBIx::Abstract.  However, in
34       using that module I found that what I really wanted to do was generate
35       SQL, but still retain complete control over my statement handles and
36       use the DBI interface. So, I set out to create an abstract SQL
37       generation module.
38
39       While based on the concepts used by DBIx::Abstract, there are several
40       important differences, especially when it comes to WHERE clauses. I
41       have modified the concepts used to make the SQL easier to generate from
42       Perl data structures and, IMO, more intuitive.  The underlying idea is
43       for this module to do what you mean, based on the data structures you
44       provide it. The big advantage is that you don't have to modify your
45       code every time your data changes, as this module figures it out.
46
47       To begin with, an SQL INSERT is as easy as just specifying a hash of
48       "key=value" pairs:
49
50           my %data = (
51               name => 'Jimbo Bobson',
52               phone => '123-456-7890',
53               address => '42 Sister Lane',
54               city => 'St. Louis',
55               state => 'Louisiana',
56           );
57
58       The SQL can then be generated with this:
59
60           my($stmt, @bind) = $sql->insert('people', \%data);
61
62       Which would give you something like this:
63
64           $stmt = "INSERT INTO people
65                           (address, city, name, phone, state)
66                           VALUES (?, ?, ?, ?, ?)";
67           @bind = ('42 Sister Lane', 'St. Louis', 'Jimbo Bobson',
68                    '123-456-7890', 'Louisiana');
69
70       These are then used directly in your DBI code:
71
72           my $sth = $dbh->prepare($stmt);
73           $sth->execute(@bind);
74
75   Inserting and Updating Arrays
76       If your database has array types (like for example Postgres), activate
77       the special option "array_datatypes => 1" when creating the
78       "SQL::Abstract" object.  Then you may use an arrayref to insert and
79       update database array types:
80
81           my $sql = SQL::Abstract->new(array_datatypes => 1);
82           my %data = (
83               planets => [qw/Mercury Venus Earth Mars/]
84           );
85
86           my($stmt, @bind) = $sql->insert('solar_system', \%data);
87
88       This results in:
89
90           $stmt = "INSERT INTO solar_system (planets) VALUES (?)"
91
92           @bind = (['Mercury', 'Venus', 'Earth', 'Mars']);
93
94   Inserting and Updating SQL
95       In order to apply SQL functions to elements of your %data you may
96       specify a reference to an arrayref for the given hash value. For
97       example, if you need to execute the Oracle "to_date" function on a
98       value, you can say something like this:
99
100           my %data = (
101               name => 'Bill',
102               date_entered => \[ "to_date(?,'MM/DD/YYYY')", "03/02/2003" ],
103           );
104
105       The first value in the array is the actual SQL. Any other values are
106       optional and would be included in the bind values array. This gives
107       you:
108
109           my($stmt, @bind) = $sql->insert('people', \%data);
110
111           $stmt = "INSERT INTO people (name, date_entered)
112                       VALUES (?, to_date(?,'MM/DD/YYYY'))";
113           @bind = ('Bill', '03/02/2003');
114
115       An UPDATE is just as easy, all you change is the name of the function:
116
117           my($stmt, @bind) = $sql->update('people', \%data);
118
119       Notice that your %data isn't touched; the module will generate the
120       appropriately quirky SQL for you automatically. Usually you'll want to
121       specify a WHERE clause for your UPDATE, though, which is where handling
122       %where hashes comes in handy...
123
124   Complex where statements
125       This module can generate pretty complicated WHERE statements easily.
126       For example, simple "key=value" pairs are taken to mean equality, and
127       if you want to see if a field is within a set of values, you can use an
128       arrayref. Let's say we wanted to SELECT some data based on this
129       criteria:
130
131           my %where = (
132              requestor => 'inna',
133              worker => ['nwiger', 'rcwe', 'sfz'],
134              status => { '!=', 'completed' }
135           );
136
137           my($stmt, @bind) = $sql->select('tickets', '*', \%where);
138
139       The above would give you something like this:
140
141           $stmt = "SELECT * FROM tickets WHERE
142                       ( requestor = ? ) AND ( status != ? )
143                       AND ( worker = ? OR worker = ? OR worker = ? )";
144           @bind = ('inna', 'completed', 'nwiger', 'rcwe', 'sfz');
145
146       Which you could then use in DBI code like so:
147
148           my $sth = $dbh->prepare($stmt);
149           $sth->execute(@bind);
150
151       Easy, eh?
152

METHODS

154       The methods are simple. There's one for every major SQL operation, and
155       a constructor you use first. The arguments are specified in a similar
156       order for each method (table, then fields, then a where clause) to try
157       and simplify things.
158
159   new(option => 'value')
160       The "new()" function takes a list of options and values, and returns a
161       new SQL::Abstract object which can then be used to generate SQL through
162       the methods below. The options accepted are:
163
164       case
165           If set to 'lower', then SQL will be generated in all lowercase. By
166           default SQL is generated in "textbook" case meaning something like:
167
168               SELECT a_field FROM a_table WHERE some_field LIKE '%someval%'
169
170           Any setting other than 'lower' is ignored.
171
172       cmp This determines what the default comparison operator is. By default
173           it is "=", meaning that a hash like this:
174
175               %where = (name => 'nwiger', email => 'nate@wiger.org');
176
177           Will generate SQL like this:
178
179               WHERE name = 'nwiger' AND email = 'nate@wiger.org'
180
181           However, you may want loose comparisons by default, so if you set
182           "cmp" to "like" you would get SQL such as:
183
184               WHERE name like 'nwiger' AND email like 'nate@wiger.org'
185
186           You can also override the comparison on an individual basis - see
187           the huge section on "WHERE CLAUSES" at the bottom.
188
189       sqltrue, sqlfalse
190           Expressions for inserting boolean values within SQL statements.  By
191           default these are "1=1" and "1=0". They are used by the special
192           operators "-in" and "-not_in" for generating correct SQL even when
193           the argument is an empty array (see below).
194
195       logic
196           This determines the default logical operator for multiple WHERE
197           statements in arrays or hashes. If absent, the default logic is
198           "or" for arrays, and "and" for hashes. This means that a WHERE
199           array of the form:
200
201               @where = (
202                   event_date => {'>=', '2/13/99'},
203                   event_date => {'<=', '4/24/03'},
204               );
205
206           will generate SQL like this:
207
208               WHERE event_date >= '2/13/99' OR event_date <= '4/24/03'
209
210           This is probably not what you want given this query, though (look
211           at the dates). To change the "OR" to an "AND", simply specify:
212
213               my $sql = SQL::Abstract->new(logic => 'and');
214
215           Which will change the above "WHERE" to:
216
217               WHERE event_date >= '2/13/99' AND event_date <= '4/24/03'
218
219           The logic can also be changed locally by inserting a modifier in
220           front of an arrayref:
221
222               @where = (-and => [event_date => {'>=', '2/13/99'},
223                                  event_date => {'<=', '4/24/03'} ]);
224
225           See the "WHERE CLAUSES" section for explanations.
226
227       convert
228           This will automatically convert comparisons using the specified SQL
229           function for both column and value. This is mostly used with an
230           argument of "upper" or "lower", so that the SQL will have the
231           effect of case-insensitive "searches". For example, this:
232
233               $sql = SQL::Abstract->new(convert => 'upper');
234               %where = (keywords => 'MaKe iT CAse inSeNSItive');
235
236           Will turn out the following SQL:
237
238               WHERE upper(keywords) like upper('MaKe iT CAse inSeNSItive')
239
240           The conversion can be "upper()", "lower()", or any other SQL
241           function that can be applied symmetrically to fields (actually
242           SQL::Abstract does not validate this option; it will just pass
243           through what you specify verbatim).
244
245       bindtype
246           This is a kludge because many databases suck. For example, you
247           can't just bind values using DBI's "execute()" for Oracle "CLOB" or
248           "BLOB" fields.  Instead, you have to use "bind_param()":
249
250               $sth->bind_param(1, 'reg data');
251               $sth->bind_param(2, $lots, {ora_type => ORA_CLOB});
252
253           The problem is, SQL::Abstract will normally just return a @bind
254           array, which loses track of which field each slot refers to. Fear
255           not.
256
257           If you specify "bindtype" in new, you can determine how @bind is
258           returned.  Currently, you can specify either "normal" (default) or
259           "columns". If you specify "columns", you will get an array that
260           looks like this:
261
262               my $sql = SQL::Abstract->new(bindtype => 'columns');
263               my($stmt, @bind) = $sql->insert(...);
264
265               @bind = (
266                   [ 'column1', 'value1' ],
267                   [ 'column2', 'value2' ],
268                   [ 'column3', 'value3' ],
269               );
270
271           You can then iterate through this manually, using DBI's
272           "bind_param()".
273
274               $sth->prepare($stmt);
275               my $i = 1;
276               for (@bind) {
277                   my($col, $data) = @$_;
278                   if ($col eq 'details' || $col eq 'comments') {
279                       $sth->bind_param($i, $data, {ora_type => ORA_CLOB});
280                   } elsif ($col eq 'image') {
281                       $sth->bind_param($i, $data, {ora_type => ORA_BLOB});
282                   } else {
283                       $sth->bind_param($i, $data);
284                   }
285                   $i++;
286               }
287               $sth->execute;      # execute without @bind now
288
289           Now, why would you still use SQL::Abstract if you have to do this
290           crap?  Basically, the advantage is still that you don't have to
291           care which fields are or are not included. You could wrap that
292           above "for" loop in a simple sub called "bind_fields()" or
293           something and reuse it repeatedly. You still get a layer of
294           abstraction over manual SQL specification.
295
296           Note that if you set "bindtype" to "columns", the "\[ $sql, @bind
297           ]" construct (see "Literal SQL with placeholders and bind values
298           (subqueries)") will expect the bind values in this format.
299
300       quote_char
301           This is the character that a table or column name will be quoted
302           with.  By default this is an empty string, but you could set it to
303           the character "`", to generate SQL like this:
304
305             SELECT `a_field` FROM `a_table` WHERE `some_field` LIKE '%someval%'
306
307           Alternatively, you can supply an array ref of two items, the first
308           being the left hand quote character, and the second the right hand
309           quote character. For example, you could supply "['[',']']" for SQL
310           Server 2000 compliant quotes that generates SQL like this:
311
312             SELECT [a_field] FROM [a_table] WHERE [some_field] LIKE '%someval%'
313
314           Quoting is useful if you have tables or columns names that are
315           reserved words in your database's SQL dialect.
316
317       escape_char
318           This is the character that will be used to escape "quote_char"s
319           appearing in an identifier before it has been quoted.
320
321           The parameter default in case of a single "quote_char" character is
322           the quote character itself.
323
324           When opening-closing-style quoting is used ("quote_char" is an
325           arrayref) this parameter defaults to the closing (right)
326           "quote_char". Occurrences of the opening (left) "quote_char" within
327           the identifier are currently left untouched. The default for
328           opening-closing-style quotes may change in future versions, thus
329           you are strongly encouraged to specify the escape character
330           explicitly.
331
332       name_sep
333           This is the character that separates a table and column name.  It
334           is necessary to specify this when the "quote_char" option is
335           selected, so that tables and column names can be individually
336           quoted like this:
337
338             SELECT `table`.`one_field` FROM `table` WHERE `table`.`other_field` = 1
339
340       injection_guard
341           A regular expression "qr/.../" that is applied to any "-function"
342           and unquoted column name specified in a query structure. This is a
343           safety mechanism to avoid injection attacks when mishandling user
344           input e.g.:
345
346             my %condition_as_column_value_pairs = get_values_from_user();
347             $sqla->select( ... , \%condition_as_column_value_pairs );
348
349           If the expression matches an exception is thrown. Note that literal
350           SQL supplied via "\'...'" or "\['...']" is not checked in any way.
351
352           Defaults to checking for ";" and the "GO" keyword (TransactSQL)
353
354       array_datatypes
355           When this option is true, arrayrefs in INSERT or UPDATE are
356           interpreted as array datatypes and are passed directly to the DBI
357           layer.  When this option is false, arrayrefs are interpreted as
358           literal SQL, just like refs to arrayrefs (but this behavior is for
359           backwards compatibility; when writing new queries, use the
360           "reference to arrayref" syntax for literal SQL).
361
362       special_ops
363           Takes a reference to a list of "special operators" to extend the
364           syntax understood by SQL::Abstract.  See section "SPECIAL
365           OPERATORS" for details.
366
367       unary_ops
368           Takes a reference to a list of "unary operators" to extend the
369           syntax understood by SQL::Abstract.  See section "UNARY OPERATORS"
370           for details.
371
372   insert($table, \@values || \%fieldvals, \%options)
373       This is the simplest function. You simply give it a table name and
374       either an arrayref of values or hashref of field/value pairs.  It
375       returns an SQL INSERT statement and a list of bind values.  See the
376       sections on "Inserting and Updating Arrays" and "Inserting and Updating
377       SQL" for information on how to insert with those data types.
378
379       The optional "\%options" hash reference may contain additional options
380       to generate the insert SQL. Currently supported options are:
381
382       returning
383           Takes either a scalar of raw SQL fields, or an array reference of
384           field names, and adds on an SQL "RETURNING" statement at the end.
385           This allows you to return data generated by the insert statement
386           (such as row IDs) without performing another "SELECT" statement.
387           Note, however, this is not part of the SQL standard and may not be
388           supported by all database engines.
389
390   update($table, \%fieldvals, \%where, \%options)
391       This takes a table, hashref of field/value pairs, and an optional
392       hashref WHERE clause. It returns an SQL UPDATE function and a list of
393       bind values.  See the sections on "Inserting and Updating Arrays" and
394       "Inserting and Updating SQL" for information on how to insert with
395       those data types.
396
397       The optional "\%options" hash reference may contain additional options
398       to generate the update SQL. Currently supported options are:
399
400       returning
401           See the "returning" option to insert.
402
403   select($source, $fields, $where, $order)
404       This returns a SQL SELECT statement and associated list of bind values,
405       as specified by the arguments:
406
407       $source
408           Specification of the 'FROM' part of the statement.  The argument
409           can be either a plain scalar (interpreted as a table name, will be
410           quoted), or an arrayref (interpreted as a list of table names,
411           joined by commas, quoted), or a scalarref (literal SQL, not
412           quoted).
413
414       $fields
415           Specification of the list of fields to retrieve from the source.
416           The argument can be either an arrayref (interpreted as a list of
417           field names, will be joined by commas and quoted), or a plain
418           scalar (literal SQL, not quoted).  Please observe that this API is
419           not as flexible as that of the first argument $source, for
420           backwards compatibility reasons.
421
422       $where
423           Optional argument to specify the WHERE part of the query.  The
424           argument is most often a hashref, but can also be an arrayref or
425           plain scalar -- see section WHERE clause for details.
426
427       $order
428           Optional argument to specify the ORDER BY part of the query.  The
429           argument can be a scalar, a hashref or an arrayref -- see section
430           ORDER BY clause for details.
431
432   delete($table, \%where, \%options)
433       This takes a table name and optional hashref WHERE clause.  It returns
434       an SQL DELETE statement and list of bind values.
435
436       The optional "\%options" hash reference may contain additional options
437       to generate the delete SQL. Currently supported options are:
438
439       returning
440           See the "returning" option to insert.
441
442   where(\%where, $order)
443       This is used to generate just the WHERE clause. For example, if you
444       have an arbitrary data structure and know what the rest of your SQL is
445       going to look like, but want an easy way to produce a WHERE clause, use
446       this. It returns an SQL WHERE clause and list of bind values.
447
448   values(\%data)
449       This just returns the values from the hash %data, in the same order
450       that would be returned from any of the other above queries.  Using this
451       allows you to markedly speed up your queries if you are affecting lots
452       of rows. See below under the "PERFORMANCE" section.
453
454   generate($any, 'number', $of, \@data, $struct, \%types)
455       Warning: This is an experimental method and subject to change.
456
457       This returns arbitrarily generated SQL. It's a really basic shortcut.
458       It will return two different things, depending on return context:
459
460           my($stmt, @bind) = $sql->generate('create table', \$table, \@fields);
461           my $stmt_and_val = $sql->generate('create table', \$table, \@fields);
462
463       These would return the following:
464
465           # First calling form
466           $stmt = "CREATE TABLE test (?, ?)";
467           @bind = (field1, field2);
468
469           # Second calling form
470           $stmt_and_val = "CREATE TABLE test (field1, field2)";
471
472       Depending on what you're trying to do, it's up to you to choose the
473       correct format. In this example, the second form is what you would
474       want.
475
476       By the same token:
477
478           $sql->generate('alter session', { nls_date_format => 'MM/YY' });
479
480       Might give you:
481
482           ALTER SESSION SET nls_date_format = 'MM/YY'
483
484       You get the idea. Strings get their case twiddled, but everything else
485       remains verbatim.
486

EXPORTABLE FUNCTIONS

488   is_plain_value
489       Determines if the supplied argument is a plain value as understood by
490       this module:
491
492       ·   The value is "undef"
493
494       ·   The value is a non-reference
495
496       ·   The value is an object with stringification overloading
497
498       ·   The value is of the form "{ -value => $anything }"
499
500       On failure returns "undef", on success returns a scalar reference to
501       the original supplied argument.
502
503       ·   Note
504
505           The stringification overloading detection is rather advanced: it
506           takes into consideration not only the presence of a "" overload,
507           but if that fails also checks for enabled autogenerated versions of
508           "", based on either "0+" or "bool".
509
510           Unfortunately testing in the field indicates that this detection
511           may tickle a latent bug in perl versions before 5.018, but only
512           when very large numbers of stringifying objects are involved.  At
513           the time of writing ( Sep 2014 ) there is no clear explanation of
514           the direct cause, nor is there a manageably small test case that
515           reliably reproduces the problem.
516
517           If you encounter any of the following exceptions in random places
518           within your application stack - this module may be to blame:
519
520             Operation "ne": no method found,
521               left argument in overloaded package <something>,
522               right argument in overloaded package <something>
523
524           or perhaps even
525
526             Stub found while resolving method "???" overloading """" in package <something>
527
528           If you fall victim to the above - please attempt to reduce the
529           problem to something that could be sent to the SQL::Abstract
530           developers
531
532           (either publicly or privately). As a workaround in the meantime you
533           can set $ENV{SQLA_ISVALUE_IGNORE_AUTOGENERATED_STRINGIFICATION} to
534           a true value, which will most likely eliminate your problem (at the
535           expense of not being able to properly detect exotic forms of
536           stringification).
537
538           This notice and environment variable will be removed in a future
539           version, as soon as the underlying problem is found and a reliable
540           workaround is devised.
541
542   is_literal_value
543       Determines if the supplied argument is a literal value as understood by
544       this module:
545
546       ·   "\$sql_string"
547
548       ·   "\[ $sql_string, @bind_values ]"
549
550       On failure returns "undef", on success returns an array reference
551       containing the unpacked version of the supplied literal SQL and bind
552       values.
553

WHERE CLAUSES

555   Introduction
556       This module uses a variation on the idea from DBIx::Abstract. It is
557       NOT, repeat not 100% compatible. The main logic of this module is that
558       things in arrays are OR'ed, and things in hashes are AND'ed.
559
560       The easiest way to explain is to show lots of examples. After each
561       %where hash shown, it is assumed you used:
562
563           my($stmt, @bind) = $sql->where(\%where);
564
565       However, note that the %where hash can be used directly in any of the
566       other functions as well, as described above.
567
568   Key-value pairs
569       So, let's get started. To begin, a simple hash:
570
571           my %where  = (
572               user   => 'nwiger',
573               status => 'completed'
574           );
575
576       Is converted to SQL "key = val" statements:
577
578           $stmt = "WHERE user = ? AND status = ?";
579           @bind = ('nwiger', 'completed');
580
581       One common thing I end up doing is having a list of values that a field
582       can be in. To do this, simply specify a list inside of an arrayref:
583
584           my %where  = (
585               user   => 'nwiger',
586               status => ['assigned', 'in-progress', 'pending'];
587           );
588
589       This simple code will create the following:
590
591           $stmt = "WHERE user = ? AND ( status = ? OR status = ? OR status = ? )";
592           @bind = ('nwiger', 'assigned', 'in-progress', 'pending');
593
594       A field associated to an empty arrayref will be considered a logical
595       false and will generate 0=1.
596
597   Tests for NULL values
598       If the value part is "undef" then this is converted to SQL <IS NULL>
599
600           my %where  = (
601               user   => 'nwiger',
602               status => undef,
603           );
604
605       becomes:
606
607           $stmt = "WHERE user = ? AND status IS NULL";
608           @bind = ('nwiger');
609
610       To test if a column IS NOT NULL:
611
612           my %where  = (
613               user   => 'nwiger',
614               status => { '!=', undef },
615           );
616
617   Specific comparison operators
618       If you want to specify a different type of operator for your
619       comparison, you can use a hashref for a given column:
620
621           my %where  = (
622               user   => 'nwiger',
623               status => { '!=', 'completed' }
624           );
625
626       Which would generate:
627
628           $stmt = "WHERE user = ? AND status != ?";
629           @bind = ('nwiger', 'completed');
630
631       To test against multiple values, just enclose the values in an
632       arrayref:
633
634           status => { '=', ['assigned', 'in-progress', 'pending'] };
635
636       Which would give you:
637
638           "WHERE status = ? OR status = ? OR status = ?"
639
640       The hashref can also contain multiple pairs, in which case it is
641       expanded into an "AND" of its elements:
642
643           my %where  = (
644               user   => 'nwiger',
645               status => { '!=', 'completed', -not_like => 'pending%' }
646           );
647
648           # Or more dynamically, like from a form
649           $where{user} = 'nwiger';
650           $where{status}{'!='} = 'completed';
651           $where{status}{'-not_like'} = 'pending%';
652
653           # Both generate this
654           $stmt = "WHERE user = ? AND status != ? AND status NOT LIKE ?";
655           @bind = ('nwiger', 'completed', 'pending%');
656
657       To get an OR instead, you can combine it with the arrayref idea:
658
659           my %where => (
660                user => 'nwiger',
661                priority => [ { '=', 2 }, { '>', 5 } ]
662           );
663
664       Which would generate:
665
666           $stmt = "WHERE ( priority = ? OR priority > ? ) AND user = ?";
667           @bind = ('2', '5', 'nwiger');
668
669       If you want to include literal SQL (with or without bind values), just
670       use a scalar reference or reference to an arrayref as the value:
671
672           my %where  = (
673               date_entered => { '>' => \["to_date(?, 'MM/DD/YYYY')", "11/26/2008"] },
674               date_expires => { '<' => \"now()" }
675           );
676
677       Which would generate:
678
679           $stmt = "WHERE date_entered > to_date(?, 'MM/DD/YYYY') AND date_expires < now()";
680           @bind = ('11/26/2008');
681
682   Logic and nesting operators
683       In the example above, there is a subtle trap if you want to say
684       something like this (notice the "AND"):
685
686           WHERE priority != ? AND priority != ?
687
688       Because, in Perl you can't do this:
689
690           priority => { '!=' => 2, '!=' => 1 }
691
692       As the second "!=" key will obliterate the first. The solution is to
693       use the special "-modifier" form inside an arrayref:
694
695           priority => [ -and => {'!=', 2},
696                                 {'!=', 1} ]
697
698       Normally, these would be joined by "OR", but the modifier tells it to
699       use "AND" instead. (Hint: You can use this in conjunction with the
700       "logic" option to "new()" in order to change the way your queries work
701       by default.) Important: Note that the "-modifier" goes INSIDE the
702       arrayref, as an extra first element. This will NOT do what you think it
703       might:
704
705           priority => -and => [{'!=', 2}, {'!=', 1}]   # WRONG!
706
707       Here is a quick list of equivalencies, since there is some overlap:
708
709           # Same
710           status => {'!=', 'completed', 'not like', 'pending%' }
711           status => [ -and => {'!=', 'completed'}, {'not like', 'pending%'}]
712
713           # Same
714           status => {'=', ['assigned', 'in-progress']}
715           status => [ -or => {'=', 'assigned'}, {'=', 'in-progress'}]
716           status => [ {'=', 'assigned'}, {'=', 'in-progress'} ]
717
718   Special operators: IN, BETWEEN, etc.
719       You can also use the hashref format to compare a list of fields using
720       the "IN" comparison operator, by specifying the list as an arrayref:
721
722           my %where  = (
723               status   => 'completed',
724               reportid => { -in => [567, 2335, 2] }
725           );
726
727       Which would generate:
728
729           $stmt = "WHERE status = ? AND reportid IN (?,?,?)";
730           @bind = ('completed', '567', '2335', '2');
731
732       The reverse operator "-not_in" generates SQL "NOT IN" and is used in
733       the same way.
734
735       If the argument to "-in" is an empty array, 'sqlfalse' is generated (by
736       default: "1=0"). Similarly, "-not_in => []" generates 'sqltrue' (by
737       default: "1=1").
738
739       In addition to the array you can supply a chunk of literal sql or
740       literal sql with bind:
741
742           my %where = {
743             customer => { -in => \[
744               'SELECT cust_id FROM cust WHERE balance > ?',
745               2000,
746             ],
747             status => { -in => \'SELECT status_codes FROM states' },
748           };
749
750       would generate:
751
752           $stmt = "WHERE (
753                 customer IN ( SELECT cust_id FROM cust WHERE balance > ? )
754             AND status IN ( SELECT status_codes FROM states )
755           )";
756           @bind = ('2000');
757
758       Finally, if the argument to "-in" is not a reference, it will be
759       treated as a single-element array.
760
761       Another pair of operators is "-between" and "-not_between", used with
762       an arrayref of two values:
763
764           my %where  = (
765               user   => 'nwiger',
766               completion_date => {
767                  -not_between => ['2002-10-01', '2003-02-06']
768               }
769           );
770
771       Would give you:
772
773           WHERE user = ? AND completion_date NOT BETWEEN ( ? AND ? )
774
775       Just like with "-in" all plausible combinations of literal SQL are
776       possible:
777
778           my %where = {
779             start0 => { -between => [ 1, 2 ] },
780             start1 => { -between => \["? AND ?", 1, 2] },
781             start2 => { -between => \"lower(x) AND upper(y)" },
782             start3 => { -between => [
783               \"lower(x)",
784               \["upper(?)", 'stuff' ],
785             ] },
786           };
787
788       Would give you:
789
790           $stmt = "WHERE (
791                 ( start0 BETWEEN ? AND ?                )
792             AND ( start1 BETWEEN ? AND ?                )
793             AND ( start2 BETWEEN lower(x) AND upper(y)  )
794             AND ( start3 BETWEEN lower(x) AND upper(?)  )
795           )";
796           @bind = (1, 2, 1, 2, 'stuff');
797
798       These are the two builtin "special operators"; but the list can be
799       expanded: see section "SPECIAL OPERATORS" below.
800
801   Unary operators: bool
802       If you wish to test against boolean columns or functions within your
803       database you can use the "-bool" and "-not_bool" operators. For example
804       to test the column "is_user" being true and the column "is_enabled"
805       being false you would use:-
806
807           my %where  = (
808               -bool       => 'is_user',
809               -not_bool   => 'is_enabled',
810           );
811
812       Would give you:
813
814           WHERE is_user AND NOT is_enabled
815
816       If a more complex combination is required, testing more conditions,
817       then you should use the and/or operators:-
818
819           my %where  = (
820               -and           => [
821                   -bool      => 'one',
822                   -not_bool  => { two=> { -rlike => 'bar' } },
823                   -not_bool  => { three => [ { '=', 2 }, { '>', 5 } ] },
824               ],
825           );
826
827       Would give you:
828
829           WHERE
830             one
831               AND
832             (NOT two RLIKE ?)
833               AND
834             (NOT ( three = ? OR three > ? ))
835
836   Nested conditions, -and/-or prefixes
837       So far, we've seen how multiple conditions are joined with a top-level
838       "AND".  We can change this by putting the different conditions we want
839       in hashes and then putting those hashes in an array. For example:
840
841           my @where = (
842               {
843                   user   => 'nwiger',
844                   status => { -like => ['pending%', 'dispatched'] },
845               },
846               {
847                   user   => 'robot',
848                   status => 'unassigned',
849               }
850           );
851
852       This data structure would create the following:
853
854           $stmt = "WHERE ( user = ? AND ( status LIKE ? OR status LIKE ? ) )
855                       OR ( user = ? AND status = ? ) )";
856           @bind = ('nwiger', 'pending', 'dispatched', 'robot', 'unassigned');
857
858       Clauses in hashrefs or arrayrefs can be prefixed with an "-and" or
859       "-or" to change the logic inside:
860
861           my @where = (
862                -and => [
863                   user => 'nwiger',
864                   [
865                       -and => [ workhrs => {'>', 20}, geo => 'ASIA' ],
866                       -or => { workhrs => {'<', 50}, geo => 'EURO' },
867                   ],
868               ],
869           );
870
871       That would yield:
872
873           $stmt = "WHERE ( user = ?
874                      AND ( ( workhrs > ? AND geo = ? )
875                         OR ( workhrs < ? OR geo = ? ) ) )";
876           @bind = ('nwiger', '20', 'ASIA', '50', 'EURO');
877
878       Algebraic inconsistency, for historical reasons
879
880       "Important note": when connecting several conditions, the "-and-"|"-or"
881       operator goes "outside" of the nested structure; whereas when
882       connecting several constraints on one column, the "-and" operator goes
883       "inside" the arrayref. Here is an example combining both features:
884
885          my @where = (
886            -and => [a => 1, b => 2],
887            -or  => [c => 3, d => 4],
888             e   => [-and => {-like => 'foo%'}, {-like => '%bar'} ]
889          )
890
891       yielding
892
893         WHERE ( (    ( a = ? AND b = ? )
894                   OR ( c = ? OR d = ? )
895                   OR ( e LIKE ? AND e LIKE ? ) ) )
896
897       This difference in syntax is unfortunate but must be preserved for
898       historical reasons. So be careful: the two examples below would seem
899       algebraically equivalent, but they are not
900
901         { col => [ -and =>
902           { -like => 'foo%' },
903           { -like => '%bar' },
904         ] }
905         # yields: WHERE ( ( col LIKE ? AND col LIKE ? ) )
906
907         [ -and =>
908           { col => { -like => 'foo%' } },
909           { col => { -like => '%bar' } },
910         ]
911         # yields: WHERE ( ( col LIKE ? OR col LIKE ? ) )
912
913   Literal SQL and value type operators
914       The basic premise of SQL::Abstract is that in WHERE specifications the
915       "left side" is a column name and the "right side" is a value (normally
916       rendered as a placeholder). This holds true for both hashrefs and
917       arrayref pairs as you see in the "WHERE CLAUSES" examples above.
918       Sometimes it is necessary to alter this behavior. There are several
919       ways of doing so.
920
921       -ident
922
923       This is a virtual operator that signals the string to its right side is
924       an identifier (a column name) and not a value. For example to compare
925       two columns you would write:
926
927           my %where = (
928               priority => { '<', 2 },
929               requestor => { -ident => 'submitter' },
930           );
931
932       which creates:
933
934           $stmt = "WHERE priority < ? AND requestor = submitter";
935           @bind = ('2');
936
937       If you are maintaining legacy code you may see a different construct as
938       described in "Deprecated usage of Literal SQL", please use "-ident" in
939       new code.
940
941       -value
942
943       This is a virtual operator that signals that the construct to its right
944       side is a value to be passed to DBI. This is for example necessary when
945       you want to write a where clause against an array (for RDBMS that
946       support such datatypes). For example:
947
948           my %where = (
949               array => { -value => [1, 2, 3] }
950           );
951
952       will result in:
953
954           $stmt = 'WHERE array = ?';
955           @bind = ([1, 2, 3]);
956
957       Note that if you were to simply say:
958
959           my %where = (
960               array => [1, 2, 3]
961           );
962
963       the result would probably not be what you wanted:
964
965           $stmt = 'WHERE array = ? OR array = ? OR array = ?';
966           @bind = (1, 2, 3);
967
968       Literal SQL
969
970       Finally, sometimes only literal SQL will do. To include a random
971       snippet of SQL verbatim, you specify it as a scalar reference. Consider
972       this only as a last resort. Usually there is a better way. For example:
973
974           my %where = (
975               priority => { '<', 2 },
976               requestor => { -in => \'(SELECT name FROM hitmen)' },
977           );
978
979       Would create:
980
981           $stmt = "WHERE priority < ? AND requestor IN (SELECT name FROM hitmen)"
982           @bind = (2);
983
984       Note that in this example, you only get one bind parameter back, since
985       the verbatim SQL is passed as part of the statement.
986
987       CAVEAT
988
989         Never use untrusted input as a literal SQL argument - this is a massive
990         security risk (there is no way to check literal snippets for SQL
991         injections and other nastyness). If you need to deal with untrusted input
992         use literal SQL with placeholders as described next.
993
994       Literal SQL with placeholders and bind values (subqueries)
995
996       If the literal SQL to be inserted has placeholders and bind values, use
997       a reference to an arrayref (yes this is a double reference -- not so
998       common, but perfectly legal Perl). For example, to find a date in
999       Postgres you can use something like this:
1000
1001           my %where = (
1002              date_column => \[ "= date '2008-09-30' - ?::integer", 10 ]
1003           )
1004
1005       This would create:
1006
1007           $stmt = "WHERE ( date_column = date '2008-09-30' - ?::integer )"
1008           @bind = ('10');
1009
1010       Note that you must pass the bind values in the same format as they are
1011       returned by where. This means that if you set "bindtype" to "columns",
1012       you must provide the bind values in the "[ column_meta => value ]"
1013       format, where "column_meta" is an opaque scalar value; most commonly
1014       the column name, but you can use any scalar value (including references
1015       and blessed references), SQL::Abstract will simply pass it through
1016       intact. So if "bindtype" is set to "columns" the above example will
1017       look like:
1018
1019           my %where = (
1020              date_column => \[ "= date '2008-09-30' - ?::integer", [ {} => 10 ] ]
1021           )
1022
1023       Literal SQL is especially useful for nesting parenthesized clauses in
1024       the main SQL query. Here is a first example:
1025
1026         my ($sub_stmt, @sub_bind) = ("SELECT c1 FROM t1 WHERE c2 < ? AND c3 LIKE ?",
1027                                      100, "foo%");
1028         my %where = (
1029           foo => 1234,
1030           bar => \["IN ($sub_stmt)" => @sub_bind],
1031         );
1032
1033       This yields:
1034
1035         $stmt = "WHERE (foo = ? AND bar IN (SELECT c1 FROM t1
1036                                                    WHERE c2 < ? AND c3 LIKE ?))";
1037         @bind = (1234, 100, "foo%");
1038
1039       Other subquery operators, like for example "> ALL" or "NOT IN", are
1040       expressed in the same way. Of course the $sub_stmt and its associated
1041       bind values can be generated through a former call to "select()" :
1042
1043         my ($sub_stmt, @sub_bind)
1044            = $sql->select("t1", "c1", {c2 => {"<" => 100},
1045                                        c3 => {-like => "foo%"}});
1046         my %where = (
1047           foo => 1234,
1048           bar => \["> ALL ($sub_stmt)" => @sub_bind],
1049         );
1050
1051       In the examples above, the subquery was used as an operator on a
1052       column; but the same principle also applies for a clause within the
1053       main %where hash, like an EXISTS subquery:
1054
1055         my ($sub_stmt, @sub_bind)
1056            = $sql->select("t1", "*", {c1 => 1, c2 => \"> t0.c0"});
1057         my %where = ( -and => [
1058           foo   => 1234,
1059           \["EXISTS ($sub_stmt)" => @sub_bind],
1060         ]);
1061
1062       which yields
1063
1064         $stmt = "WHERE (foo = ? AND EXISTS (SELECT * FROM t1
1065                                               WHERE c1 = ? AND c2 > t0.c0))";
1066         @bind = (1234, 1);
1067
1068       Observe that the condition on "c2" in the subquery refers to column
1069       "t0.c0" of the main query: this is not a bind value, so we have to
1070       express it through a scalar ref.  Writing "c2 => {">" => "t0.c0"}"
1071       would have generated "c2 > ?" with bind value "t0.c0" ... not exactly
1072       what we wanted here.
1073
1074       Finally, here is an example where a subquery is used for expressing
1075       unary negation:
1076
1077         my ($sub_stmt, @sub_bind)
1078            = $sql->where({age => [{"<" => 10}, {">" => 20}]});
1079         $sub_stmt =~ s/^ where //i; # don't want "WHERE" in the subclause
1080         my %where = (
1081               lname  => {like => '%son%'},
1082               \["NOT ($sub_stmt)" => @sub_bind],
1083           );
1084
1085       This yields
1086
1087         $stmt = "lname LIKE ? AND NOT ( age < ? OR age > ? )"
1088         @bind = ('%son%', 10, 20)
1089
1090       Deprecated usage of Literal SQL
1091
1092       Below are some examples of archaic use of literal SQL. It is shown only
1093       as reference for those who deal with legacy code. Each example has a
1094       much better, cleaner and safer alternative that users should opt for in
1095       new code.
1096
1097       ·
1098
1099
1100               my %where = ( requestor => \'IS NOT NULL' )
1101
1102               $stmt = "WHERE requestor IS NOT NULL"
1103
1104           This used to be the way of generating NULL comparisons, before the
1105           handling of "undef" got formalized. For new code please use the
1106           superior syntax as described in "Tests for NULL values".
1107
1108       ·
1109
1110
1111               my %where = ( requestor => \'= submitter' )
1112
1113               $stmt = "WHERE requestor = submitter"
1114
1115           This used to be the only way to compare columns. Use the superior
1116           "-ident" method for all new code. For example an identifier
1117           declared in such a way will be properly quoted if "quote_char" is
1118           properly set, while the legacy form will remain as supplied.
1119
1120       ·
1121
1122
1123               my %where = ( is_ready  => \"", completed => { '>', '2012-12-21' } )
1124
1125               $stmt = "WHERE completed > ? AND is_ready"
1126               @bind = ('2012-12-21')
1127
1128           Using an empty string literal used to be the only way to express a
1129           boolean.  For all new code please use the much more readable -bool
1130           operator.
1131
1132   Conclusion
1133       These pages could go on for a while, since the nesting of the data
1134       structures this module can handle are pretty much unlimited (the module
1135       implements the "WHERE" expansion as a recursive function internally).
1136       Your best bet is to "play around" with the module a little to see how
1137       the data structures behave, and choose the best format for your data
1138       based on that.
1139
1140       And of course, all the values above will probably be replaced with
1141       variables gotten from forms or the command line. After all, if you knew
1142       everything ahead of time, you wouldn't have to worry about dynamically-
1143       generating SQL and could just hardwire it into your script.
1144

ORDER BY CLAUSES

1146       Some functions take an order by clause. This can either be a scalar
1147       (just a column name), a hashref of "{ -desc => 'col' }" or "{ -asc =>
1148       'col' }", a scalarref, an arrayref-ref, or an arrayref of any of the
1149       previous forms. Examples:
1150
1151                      Given              |         Will Generate
1152           ---------------------------------------------------------------
1153                                         |
1154           'colA'                        | ORDER BY colA
1155                                         |
1156           [qw/colA colB/]               | ORDER BY colA, colB
1157                                         |
1158           {-asc  => 'colA'}             | ORDER BY colA ASC
1159                                         |
1160           {-desc => 'colB'}             | ORDER BY colB DESC
1161                                         |
1162           ['colA', {-asc => 'colB'}]    | ORDER BY colA, colB ASC
1163                                         |
1164           { -asc => [qw/colA colB/] }   | ORDER BY colA ASC, colB ASC
1165                                         |
1166           \'colA DESC'                  | ORDER BY colA DESC
1167                                         |
1168           \[ 'FUNC(colA, ?)', $x ]      | ORDER BY FUNC(colA, ?)
1169                                         |   /* ...with $x bound to ? */
1170                                         |
1171           [                             | ORDER BY
1172             { -asc => 'colA' },         |     colA ASC,
1173             { -desc => [qw/colB/] },    |     colB DESC,
1174             { -asc => [qw/colC colD/] },|     colC ASC, colD ASC,
1175             \'colE DESC',               |     colE DESC,
1176             \[ 'FUNC(colF, ?)', $x ],   |     FUNC(colF, ?)
1177           ]                             |   /* ...with $x bound to ? */
1178           ===============================================================
1179

SPECIAL OPERATORS

1181         my $sqlmaker = SQL::Abstract->new(special_ops => [
1182            {
1183             regex => qr/.../,
1184             handler => sub {
1185               my ($self, $field, $op, $arg) = @_;
1186               ...
1187             },
1188            },
1189            {
1190             regex => qr/.../,
1191             handler => 'method_name',
1192            },
1193          ]);
1194
1195       A "special operator" is a SQL syntactic clause that can be applied to a
1196       field, instead of a usual binary operator.  For example:
1197
1198          WHERE field IN (?, ?, ?)
1199          WHERE field BETWEEN ? AND ?
1200          WHERE MATCH(field) AGAINST (?, ?)
1201
1202       Special operators IN and BETWEEN are fairly standard and therefore are
1203       builtin within "SQL::Abstract" (as the overridable methods
1204       "_where_field_IN" and "_where_field_BETWEEN"). For other operators,
1205       like the MATCH .. AGAINST example above which is specific to MySQL, you
1206       can write your own operator handlers - supply a "special_ops" argument
1207       to the "new" method. That argument takes an arrayref of operator
1208       definitions; each operator definition is a hashref with two entries:
1209
1210       regex
1211           the regular expression to match the operator
1212
1213       handler
1214           Either a coderef or a plain scalar method name. In both cases the
1215           expected return is "($sql, @bind)".
1216
1217           When supplied with a method name, it is simply called on the
1218           SQL::Abstract object as:
1219
1220            $self->$method_name($field, $op, $arg)
1221
1222            Where:
1223
1224             $field is the LHS of the operator
1225             $op is the part that matched the handler regex
1226             $arg is the RHS
1227
1228           When supplied with a coderef, it is called as:
1229
1230            $coderef->($self, $field, $op, $arg)
1231
1232       For example, here is an implementation of the MATCH .. AGAINST syntax
1233       for MySQL
1234
1235         my $sqlmaker = SQL::Abstract->new(special_ops => [
1236
1237           # special op for MySql MATCH (field) AGAINST(word1, word2, ...)
1238           {regex => qr/^match$/i,
1239            handler => sub {
1240              my ($self, $field, $op, $arg) = @_;
1241              $arg = [$arg] if not ref $arg;
1242              my $label         = $self->_quote($field);
1243              my ($placeholder) = $self->_convert('?');
1244              my $placeholders  = join ", ", (($placeholder) x @$arg);
1245              my $sql           = $self->_sqlcase('match') . " ($label) "
1246                                . $self->_sqlcase('against') . " ($placeholders) ";
1247              my @bind = $self->_bindtype($field, @$arg);
1248              return ($sql, @bind);
1249              }
1250            },
1251
1252         ]);
1253

UNARY OPERATORS

1255         my $sqlmaker = SQL::Abstract->new(unary_ops => [
1256            {
1257             regex => qr/.../,
1258             handler => sub {
1259               my ($self, $op, $arg) = @_;
1260               ...
1261             },
1262            },
1263            {
1264             regex => qr/.../,
1265             handler => 'method_name',
1266            },
1267          ]);
1268
1269       A "unary operator" is a SQL syntactic clause that can be applied to a
1270       field - the operator goes before the field
1271
1272       You can write your own operator handlers - supply a "unary_ops"
1273       argument to the "new" method. That argument takes an arrayref of
1274       operator definitions; each operator definition is a hashref with two
1275       entries:
1276
1277       regex
1278           the regular expression to match the operator
1279
1280       handler
1281           Either a coderef or a plain scalar method name. In both cases the
1282           expected return is $sql.
1283
1284           When supplied with a method name, it is simply called on the
1285           SQL::Abstract object as:
1286
1287            $self->$method_name($op, $arg)
1288
1289            Where:
1290
1291             $op is the part that matched the handler regex
1292             $arg is the RHS or argument of the operator
1293
1294           When supplied with a coderef, it is called as:
1295
1296            $coderef->($self, $op, $arg)
1297

PERFORMANCE

1299       Thanks to some benchmarking by Mark Stosberg, it turns out that this
1300       module is many orders of magnitude faster than using "DBIx::Abstract".
1301       I must admit this wasn't an intentional design issue, but it's a
1302       byproduct of the fact that you get to control your "DBI" handles
1303       yourself.
1304
1305       To maximize performance, use a code snippet like the following:
1306
1307           # prepare a statement handle using the first row
1308           # and then reuse it for the rest of the rows
1309           my($sth, $stmt);
1310           for my $href (@array_of_hashrefs) {
1311               $stmt ||= $sql->insert('table', $href);
1312               $sth  ||= $dbh->prepare($stmt);
1313               $sth->execute($sql->values($href));
1314           }
1315
1316       The reason this works is because the keys in your $href are sorted
1317       internally by SQL::Abstract. Thus, as long as your data retains the
1318       same structure, you only have to generate the SQL the first time
1319       around. On subsequent queries, simply use the "values" function
1320       provided by this module to return your values in the correct order.
1321
1322       However this depends on the values having the same type - if, for
1323       example, the values of a where clause may either have values (resulting
1324       in sql of the form "column = ?" with a single bind value), or
1325       alternatively the values might be "undef" (resulting in sql of the form
1326       "column IS NULL" with no bind value) then the caching technique
1327       suggested will not work.
1328

FORMBUILDER

1330       If you use my "CGI::FormBuilder" module at all, you'll hopefully really
1331       like this part (I do, at least). Building up a complex query can be as
1332       simple as the following:
1333
1334           #!/usr/bin/perl
1335
1336           use warnings;
1337           use strict;
1338
1339           use CGI::FormBuilder;
1340           use SQL::Abstract;
1341
1342           my $form = CGI::FormBuilder->new(...);
1343           my $sql  = SQL::Abstract->new;
1344
1345           if ($form->submitted) {
1346               my $field = $form->field;
1347               my $id = delete $field->{id};
1348               my($stmt, @bind) = $sql->update('table', $field, {id => $id});
1349           }
1350
1351       Of course, you would still have to connect using "DBI" to run the
1352       query, but the point is that if you make your form look like your
1353       table, the actual query script can be extremely simplistic.
1354
1355       If you're REALLY lazy (I am), check out "HTML::QuickTable" for a fast
1356       interface to returning and formatting data. I frequently use these
1357       three modules together to write complex database query apps in under 50
1358       lines.
1359

HOW TO CONTRIBUTE

1361       Contributions are always welcome, in all usable forms (we especially
1362       welcome documentation improvements). The delivery methods include git-
1363       or unified-diff formatted patches, GitHub pull requests, or plain bug
1364       reports either via RT or the Mailing list. Contributors are generally
1365       granted full access to the official repository after their first
1366       several patches pass successful review.
1367
1368       This project is maintained in a git repository. The code and related
1369       tools are accessible at the following locations:
1370
1371       ·   Official repo:
1372           <git://git.shadowcat.co.uk/dbsrgits/SQL-Abstract.git>
1373
1374       ·   Official gitweb:
1375           <http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=dbsrgits/SQL-Abstract.git>
1376
1377       ·   GitHub mirror: <https://github.com/dbsrgits/sql-abstract>
1378
1379       ·   Authorized committers:
1380           <ssh://dbsrgits@git.shadowcat.co.uk/SQL-Abstract.git>
1381

CHANGES

1383       Version 1.50 was a major internal refactoring of "SQL::Abstract".
1384       Great care has been taken to preserve the published behavior documented
1385       in previous versions in the 1.* family; however, some features that
1386       were previously undocumented, or behaved differently from the
1387       documentation, had to be changed in order to clarify the semantics.
1388       Hence, client code that was relying on some dark areas of
1389       "SQL::Abstract" v1.*  might behave differently in v1.50.
1390
1391       The main changes are:
1392
1393       ·   support for literal SQL through the "\ [ $sql, @bind ]" syntax.
1394
1395       ·   support for the { operator => \"..." } construct (to embed literal
1396           SQL)
1397
1398       ·   support for the { operator => \["...", @bind] } construct (to embed
1399           literal SQL with bind values)
1400
1401       ·   optional support for array datatypes
1402
1403       ·   defensive programming: check arguments
1404
1405       ·   fixed bug with global logic, which was previously implemented
1406           through global variables yielding side-effects. Prior versions
1407           would interpret "[ {cond1, cond2}, [cond3, cond4] ]" as "(cond1 AND
1408           cond2) OR (cond3 AND cond4)".  Now this is interpreted as "(cond1
1409           AND cond2) OR (cond3 OR cond4)".
1410
1411       ·   fixed semantics of  _bindtype on array args
1412
1413       ·   dropped the "_anoncopy" of the %where tree. No longer necessary, we
1414           just avoid shifting arrays within that tree.
1415
1416       ·   dropped the "_modlogic" function
1417

ACKNOWLEDGEMENTS

1419       There are a number of individuals that have really helped out with this
1420       module. Unfortunately, most of them submitted bugs via CPAN so I have
1421       no idea who they are! But the people I do know are:
1422
1423           Ash Berlin (order_by hash term support)
1424           Matt Trout (DBIx::Class support)
1425           Mark Stosberg (benchmarking)
1426           Chas Owens (initial "IN" operator support)
1427           Philip Collins (per-field SQL functions)
1428           Eric Kolve (hashref "AND" support)
1429           Mike Fragassi (enhancements to "BETWEEN" and "LIKE")
1430           Dan Kubb (support for "quote_char" and "name_sep")
1431           Guillermo Roditi (patch to cleanup "IN" and "BETWEEN", fix and tests for _order_by)
1432           Laurent Dami (internal refactoring, extensible list of special operators, literal SQL)
1433           Norbert Buchmuller (support for literal SQL in hashpair, misc. fixes & tests)
1434           Peter Rabbitson (rewrite of SQLA::Test, misc. fixes & tests)
1435           Oliver Charles (support for "RETURNING" after "INSERT")
1436
1437       Thanks!
1438

SEE ALSO

1440       DBIx::Class, DBIx::Abstract, CGI::FormBuilder, HTML::QuickTable.
1441

AUTHOR

1443       Copyright (c) 2001-2007 Nathan Wiger <nwiger@cpan.org>. All Rights
1444       Reserved.
1445
1446       This module is actively maintained by Matt Trout
1447       <mst@shadowcatsystems.co.uk>
1448
1449       For support, your best bet is to try the "DBIx::Class" users mailing
1450       list.  While not an official support venue, "DBIx::Class" makes heavy
1451       use of "SQL::Abstract", and as such list members there are very
1452       familiar with how to create queries.
1453

LICENSE

1455       This module is free software; you may copy this under the same terms as
1456       perl itself (either the GNU General Public License or the Artistic
1457       License)
1458
1459
1460
1461perl v5.30.0                      2019-07-26                  SQL::Abstract(3)
Impressum