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
554   is_undef_value
555       Tests for undef, whether expanded or not.
556

WHERE CLAUSES

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

ORDER BY CLAUSES

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

OLD EXTENSION SYSTEM

1184   SPECIAL OPERATORS
1185         my $sqlmaker = SQL::Abstract->new(special_ops => [
1186            {
1187             regex => qr/.../,
1188             handler => sub {
1189               my ($self, $field, $op, $arg) = @_;
1190               ...
1191             },
1192            },
1193            {
1194             regex => qr/.../,
1195             handler => 'method_name',
1196            },
1197          ]);
1198
1199       A "special operator" is a SQL syntactic clause that can be applied to a
1200       field, instead of a usual binary operator.  For example:
1201
1202          WHERE field IN (?, ?, ?)
1203          WHERE field BETWEEN ? AND ?
1204          WHERE MATCH(field) AGAINST (?, ?)
1205
1206       Special operators IN and BETWEEN are fairly standard and therefore are
1207       builtin within "SQL::Abstract" (as the overridable methods
1208       "_where_field_IN" and "_where_field_BETWEEN"). For other operators,
1209       like the MATCH .. AGAINST example above which is specific to MySQL, you
1210       can write your own operator handlers - supply a "special_ops" argument
1211       to the "new" method. That argument takes an arrayref of operator
1212       definitions; each operator definition is a hashref with two entries:
1213
1214       regex
1215           the regular expression to match the operator
1216
1217       handler
1218           Either a coderef or a plain scalar method name. In both cases the
1219           expected return is "($sql, @bind)".
1220
1221           When supplied with a method name, it is simply called on the
1222           SQL::Abstract object as:
1223
1224            $self->$method_name($field, $op, $arg)
1225
1226            Where:
1227
1228             $field is the LHS of the operator
1229             $op is the part that matched the handler regex
1230             $arg is the RHS
1231
1232           When supplied with a coderef, it is called as:
1233
1234            $coderef->($self, $field, $op, $arg)
1235
1236       For example, here is an implementation of the MATCH .. AGAINST syntax
1237       for MySQL
1238
1239         my $sqlmaker = SQL::Abstract->new(special_ops => [
1240
1241           # special op for MySql MATCH (field) AGAINST(word1, word2, ...)
1242           {regex => qr/^match$/i,
1243            handler => sub {
1244              my ($self, $field, $op, $arg) = @_;
1245              $arg = [$arg] if not ref $arg;
1246              my $label         = $self->_quote($field);
1247              my ($placeholder) = $self->_convert('?');
1248              my $placeholders  = join ", ", (($placeholder) x @$arg);
1249              my $sql           = $self->_sqlcase('match') . " ($label) "
1250                                . $self->_sqlcase('against') . " ($placeholders) ";
1251              my @bind = $self->_bindtype($field, @$arg);
1252              return ($sql, @bind);
1253              }
1254            },
1255
1256         ]);
1257
1258   UNARY OPERATORS
1259         my $sqlmaker = SQL::Abstract->new(unary_ops => [
1260            {
1261             regex => qr/.../,
1262             handler => sub {
1263               my ($self, $op, $arg) = @_;
1264               ...
1265             },
1266            },
1267            {
1268             regex => qr/.../,
1269             handler => 'method_name',
1270            },
1271          ]);
1272
1273       A "unary operator" is a SQL syntactic clause that can be applied to a
1274       field - the operator goes before the field
1275
1276       You can write your own operator handlers - supply a "unary_ops"
1277       argument to the "new" method. That argument takes an arrayref of
1278       operator definitions; each operator definition is a hashref with two
1279       entries:
1280
1281       regex
1282           the regular expression to match the operator
1283
1284       handler
1285           Either a coderef or a plain scalar method name. In both cases the
1286           expected return is $sql.
1287
1288           When supplied with a method name, it is simply called on the
1289           SQL::Abstract object as:
1290
1291            $self->$method_name($op, $arg)
1292
1293            Where:
1294
1295             $op is the part that matched the handler regex
1296             $arg is the RHS or argument of the operator
1297
1298           When supplied with a coderef, it is called as:
1299
1300            $coderef->($self, $op, $arg)
1301

NEW METHODS (EXPERIMENTAL)

1303       See SQL::Abstract::Reference for the "expr" versus "aqt" concept and an
1304       explanation of what the below extensions are extending.
1305
1306   plugin
1307         $sqla->plugin('+Foo');
1308
1309       Enables plugin SQL::Abstract::Plugin::Foo.
1310
1311   render_expr
1312         my ($sql, @bind) = $sqla->render_expr($expr);
1313
1314   render_statement
1315       Use this if you may be rendering a top level statement so e.g. a SELECT
1316       query doesn't get wrapped in parens
1317
1318         my ($sql, @bind) = $sqla->render_statement($expr);
1319
1320   expand_expr
1321       Expression expansion with optional default for scalars.
1322
1323         my $aqt = $self->expand_expr($expr);
1324         my $aqt = $self->expand_expr($expr, -ident);
1325
1326   render_aqt
1327       Top level means avoid parens on statement AQT.
1328
1329         my $res = $self->render_aqt($aqt, $top_level);
1330         my ($sql, @bind) = @$res;
1331
1332   join_query_parts
1333       Similar to join() but will render hashrefs as nodes for both join and
1334       parts, and treats arrayref as a nested "[ $join, @parts ]" structure.
1335
1336         my $part = $self->join_query_parts($join, @parts);
1337

NEW EXTENSION SYSTEM

1339   clone
1340         my $sqla2 = $sqla->clone;
1341
1342       Performs a semi-shallow copy such that extension methods won't leak
1343       state but excessive depth is avoided.
1344
1345   expander
1346   expanders
1347   op_expander
1348   op_expanders
1349   clause_expander
1350   clause_expanders
1351         $sqla->expander('name' => sub { ... });
1352         $sqla->expanders('name1' => sub { ... }, 'name2' => sub { ... });
1353
1354   expander_list
1355   op_expander_list
1356   clause_expander_list
1357         my @names = $sqla->expander_list;
1358
1359   wrap_expander
1360   wrap_expanders
1361   wrap_op_expander
1362   wrap_op_expanders
1363   wrap_clause_expander
1364   wrap_clause_expanders
1365         $sqla->wrap_expander('name' => sub { my ($orig) = @_; sub { ... } });
1366         $sqla->wrap_expanders(
1367           'name1' => sub { my ($orig1) = @_; sub { ... } },
1368           'name2' => sub { my ($orig2) = @_; sub { ... } },
1369         );
1370
1371   renderer
1372   renderers
1373   op_renderer
1374   op_renderers
1375   clause_renderer
1376   clause_renderers
1377         $sqla->renderer('name' => sub { ... });
1378         $sqla->renderers('name1' => sub { ... }, 'name2' => sub { ... });
1379
1380   renderer_list
1381   op_renderer_list
1382   clause_renderer_list
1383         my @names = $sqla->renderer_list;
1384
1385   wrap_renderer
1386   wrap_renderers
1387   wrap_op_renderer
1388   wrap_op_renderers
1389   wrap_clause_renderer
1390   wrap_clause_renderers
1391         $sqla->wrap_renderer('name' => sub { my ($orig) = @_; sub { ... } });
1392         $sqla->wrap_renderers(
1393           'name1' => sub { my ($orig1) = @_; sub { ... } },
1394           'name2' => sub { my ($orig2) = @_; sub { ... } },
1395         );
1396
1397   clauses_of
1398         my @clauses = $sqla->clauses_of('select');
1399         $sqla->clauses_of(select => \@new_clauses);
1400         $sqla->clauses_of(select => sub {
1401           my (undef, @old_clauses) = @_;
1402           ...
1403           return @new_clauses;
1404         });
1405
1406   statement_list
1407         my @list = $sqla->statement_list;
1408
1409   make_unop_expander
1410         my $exp = $sqla->make_unop_expander(sub { ... });
1411
1412       If the op is found as a binop, assumes it wants a default comparison,
1413       so the inner expander sub can reliably operate as
1414
1415         sub { my ($self, $name, $body) = @_; ... }
1416
1417   make_binop_expander
1418         my $exp = $sqla->make_binop_expander(sub { ... });
1419
1420       If the op is found as a unop, assumes the value will be an arrayref
1421       with the LHS as the first entry, and converts that to an ident node if
1422       it's a simple scalar. So the inner expander sub looks like
1423
1424         sub {
1425           my ($self, $name, $body, $k) = @_;
1426           { -blah => [ map $self->expand_expr($_), $k, $body ] }
1427         }
1428
1429   unop_expander
1430   unop_expanders
1431   binop_expander
1432   binop_expanders
1433       The above methods operate exactly like the op_ versions but wrap the
1434       coderef using the appropriate make_ method first.
1435

PERFORMANCE

1437       Thanks to some benchmarking by Mark Stosberg, it turns out that this
1438       module is many orders of magnitude faster than using "DBIx::Abstract".
1439       I must admit this wasn't an intentional design issue, but it's a
1440       byproduct of the fact that you get to control your "DBI" handles
1441       yourself.
1442
1443       To maximize performance, use a code snippet like the following:
1444
1445           # prepare a statement handle using the first row
1446           # and then reuse it for the rest of the rows
1447           my($sth, $stmt);
1448           for my $href (@array_of_hashrefs) {
1449               $stmt ||= $sql->insert('table', $href);
1450               $sth  ||= $dbh->prepare($stmt);
1451               $sth->execute($sql->values($href));
1452           }
1453
1454       The reason this works is because the keys in your $href are sorted
1455       internally by SQL::Abstract. Thus, as long as your data retains the
1456       same structure, you only have to generate the SQL the first time
1457       around. On subsequent queries, simply use the "values" function
1458       provided by this module to return your values in the correct order.
1459
1460       However this depends on the values having the same type - if, for
1461       example, the values of a where clause may either have values (resulting
1462       in sql of the form "column = ?" with a single bind value), or
1463       alternatively the values might be "undef" (resulting in sql of the form
1464       "column IS NULL" with no bind value) then the caching technique
1465       suggested will not work.
1466

FORMBUILDER

1468       If you use my "CGI::FormBuilder" module at all, you'll hopefully really
1469       like this part (I do, at least). Building up a complex query can be as
1470       simple as the following:
1471
1472           #!/usr/bin/perl
1473
1474           use warnings;
1475           use strict;
1476
1477           use CGI::FormBuilder;
1478           use SQL::Abstract;
1479
1480           my $form = CGI::FormBuilder->new(...);
1481           my $sql  = SQL::Abstract->new;
1482
1483           if ($form->submitted) {
1484               my $field = $form->field;
1485               my $id = delete $field->{id};
1486               my($stmt, @bind) = $sql->update('table', $field, {id => $id});
1487           }
1488
1489       Of course, you would still have to connect using "DBI" to run the
1490       query, but the point is that if you make your form look like your
1491       table, the actual query script can be extremely simplistic.
1492
1493       If you're REALLY lazy (I am), check out "HTML::QuickTable" for a fast
1494       interface to returning and formatting data. I frequently use these
1495       three modules together to write complex database query apps in under 50
1496       lines.
1497

HOW TO CONTRIBUTE

1499       Contributions are always welcome, in all usable forms (we especially
1500       welcome documentation improvements). The delivery methods include git-
1501       or unified-diff formatted patches, GitHub pull requests, or plain bug
1502       reports either via RT or the Mailing list. Contributors are generally
1503       granted full access to the official repository after their first
1504       several patches pass successful review.
1505
1506       This project is maintained in a git repository. The code and related
1507       tools are accessible at the following locations:
1508
1509       •   Official repo:
1510           <git://git.shadowcat.co.uk/dbsrgits/SQL-Abstract.git>
1511
1512       •   Official gitweb:
1513           <http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=dbsrgits/SQL-Abstract.git>
1514
1515       •   GitHub mirror: <https://github.com/dbsrgits/sql-abstract>
1516
1517       •   Authorized committers:
1518           <ssh://dbsrgits@git.shadowcat.co.uk/SQL-Abstract.git>
1519

CHANGES

1521       Version 1.50 was a major internal refactoring of "SQL::Abstract".
1522       Great care has been taken to preserve the published behavior documented
1523       in previous versions in the 1.* family; however, some features that
1524       were previously undocumented, or behaved differently from the
1525       documentation, had to be changed in order to clarify the semantics.
1526       Hence, client code that was relying on some dark areas of
1527       "SQL::Abstract" v1.*  might behave differently in v1.50.
1528
1529       The main changes are:
1530
1531       •   support for literal SQL through the "\ [ $sql, @bind ]" syntax.
1532
1533       •   support for the { operator => \"..." } construct (to embed literal
1534           SQL)
1535
1536       •   support for the { operator => \["...", @bind] } construct (to embed
1537           literal SQL with bind values)
1538
1539       •   optional support for array datatypes
1540
1541       •   defensive programming: check arguments
1542
1543       •   fixed bug with global logic, which was previously implemented
1544           through global variables yielding side-effects. Prior versions
1545           would interpret "[ {cond1, cond2}, [cond3, cond4] ]" as "(cond1 AND
1546           cond2) OR (cond3 AND cond4)".  Now this is interpreted as "(cond1
1547           AND cond2) OR (cond3 OR cond4)".
1548
1549       •   fixed semantics of  _bindtype on array args
1550
1551       •   dropped the "_anoncopy" of the %where tree. No longer necessary, we
1552           just avoid shifting arrays within that tree.
1553
1554       •   dropped the "_modlogic" function
1555

ACKNOWLEDGEMENTS

1557       There are a number of individuals that have really helped out with this
1558       module. Unfortunately, most of them submitted bugs via CPAN so I have
1559       no idea who they are! But the people I do know are:
1560
1561           Ash Berlin (order_by hash term support)
1562           Matt Trout (DBIx::Class support)
1563           Mark Stosberg (benchmarking)
1564           Chas Owens (initial "IN" operator support)
1565           Philip Collins (per-field SQL functions)
1566           Eric Kolve (hashref "AND" support)
1567           Mike Fragassi (enhancements to "BETWEEN" and "LIKE")
1568           Dan Kubb (support for "quote_char" and "name_sep")
1569           Guillermo Roditi (patch to cleanup "IN" and "BETWEEN", fix and tests for _order_by)
1570           Laurent Dami (internal refactoring, extensible list of special operators, literal SQL)
1571           Norbert Buchmuller (support for literal SQL in hashpair, misc. fixes & tests)
1572           Peter Rabbitson (rewrite of SQLA::Test, misc. fixes & tests)
1573           Oliver Charles (support for "RETURNING" after "INSERT")
1574
1575       Thanks!
1576

SEE ALSO

1578       DBIx::Class, DBIx::Abstract, CGI::FormBuilder, HTML::QuickTable.
1579

AUTHOR

1581       Copyright (c) 2001-2007 Nathan Wiger <nwiger@cpan.org>. All Rights
1582       Reserved.
1583
1584       This module is actively maintained by Matt Trout
1585       <mst@shadowcatsystems.co.uk>
1586
1587       For support, your best bet is to try the "DBIx::Class" users mailing
1588       list.  While not an official support venue, "DBIx::Class" makes heavy
1589       use of "SQL::Abstract", and as such list members there are very
1590       familiar with how to create queries.
1591

LICENSE

1593       This module is free software; you may copy this under the same terms as
1594       perl itself (either the GNU General Public License or the Artistic
1595       License)
1596
1597
1598
1599perl v5.34.0                      2021-07-22                  SQL::Abstract(3)
Impressum