1SQL::Abstract(3) User Contributed Perl Documentation SQL::Abstract(3)
2
6 SQL::Abstract - Generate SQL from Perl data structures
7
9 use SQL::Abstract;
10 my $sql = SQL::Abstract->new;
12 my($stmt, @bind) = $sql->select($source, \@fields, \%where, $order);
14 my($stmt, @bind) = $sql->insert($table, \%fieldvals || \@values);
16 my($stmt, @bind) = $sql->update($table, \%fieldvals, \%where);
18 my($stmt, @bind) = $sql->delete($table, \%where);
20 # Then, use these in your DBI statements
22 my $sth = $dbh->prepare($stmt);
23 $sth->execute(@bind);
24 # Just generate the WHERE clause
26 my($stmt, @bind) = $sql->where(\%where, $order);
27 # Return values in the same order, for hashed queries
29 # See PERFORMANCE section for more details
30 my @bind = $sql->values(\%fieldvals);
31
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 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 To begin with, an SQL INSERT is as easy as just specifying a hash of
48 "key=value" pairs:
49 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 The SQL can then be generated with this:
59 my($stmt, @bind) = $sql->insert('people', \%data);
61 Which would give you something like this:
63 $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 These are then used directly in your DBI code:
71 my $sth = $dbh->prepare($stmt);
73 $sth->execute(@bind);
74 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 my $sql = SQL::Abstract->new(array_datatypes => 1);
82 my %data = (
83 planets => [qw/Mercury Venus Earth Mars/]
84 );
85 my($stmt, @bind) = $sql->insert('solar_system', \%data);
87 This results in:
89 $stmt = "INSERT INTO solar_system (planets) VALUES (?)"
91 @bind = (['Mercury', 'Venus', 'Earth', 'Mars']);
93 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 my %data = (
101 name => 'Bill',
102 date_entered => \[ "to_date(?,'MM/DD/YYYY')", "03/02/2003" ],
103 );
104 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 my($stmt, @bind) = $sql->insert('people', \%data);
110 $stmt = "INSERT INTO people (name, date_entered)
112 VALUES (?, to_date(?,'MM/DD/YYYY'))";
113 @bind = ('Bill', '03/02/2003');
114 An UPDATE is just as easy, all you change is the name of the function:
116 my($stmt, @bind) = $sql->update('people', \%data);
118 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 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 my %where = (
132 requestor => 'inna',
133 worker => ['nwiger', 'rcwe', 'sfz'],
134 status => { '!=', 'completed' }
135 );
136 my($stmt, @bind) = $sql->select('tickets', '*', \%where);
138 The above would give you something like this:
140 $stmt = "SELECT * FROM tickets WHERE
142 ( requestor = ? ) AND ( status != ? )
143 AND ( worker = ? OR worker = ? OR worker = ? )";
144 @bind = ('inna', 'completed', 'nwiger', 'rcwe', 'sfz');
145 Which you could then use in DBI code like so:
147 my $sth = $dbh->prepare($stmt);
149 $sth->execute(@bind);
150 Easy, eh?
152
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 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 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 SELECT a_field FROM a_table WHERE some_field LIKE '%someval%'
169 Any setting other than 'lower' is ignored.
171 cmp This determines what the default comparison operator is. By default
173 it is "=", meaning that a hash like this:
174 %where = (name => 'nwiger', email => 'nate@wiger.org');
176 Will generate SQL like this:
178 WHERE name = 'nwiger' AND email = 'nate@wiger.org'
180 However, you may want loose comparisons by default, so if you set
182 "cmp" to "like" you would get SQL such as:
183 WHERE name like 'nwiger' AND email like 'nate@wiger.org'
185 You can also override the comparison on an individual basis - see
187 the huge section on "WHERE CLAUSES" at the bottom.
188 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 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 @where = (
202 event_date => {'>=', '2/13/99'},
203 event_date => {'<=', '4/24/03'},
204 );
205 will generate SQL like this:
207 WHERE event_date >= '2/13/99' OR event_date <= '4/24/03'
209 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 my $sql = SQL::Abstract->new(logic => 'and');
214 Which will change the above "WHERE" to:
216 WHERE event_date >= '2/13/99' AND event_date <= '4/24/03'
218 The logic can also be changed locally by inserting a modifier in
220 front of an arrayref:
221 @where = (-and => [event_date => {'>=', '2/13/99'},
223 event_date => {'<=', '4/24/03'} ]);
224 See the "WHERE CLAUSES" section for explanations.
226 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 $sql = SQL::Abstract->new(convert => 'upper');
234 %where = (keywords => 'MaKe iT CAse inSeNSItive');
235 Will turn out the following SQL:
237 WHERE upper(keywords) like upper('MaKe iT CAse inSeNSItive')
239 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 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 $sth->bind_param(1, 'reg data');
251 $sth->bind_param(2, $lots, {ora_type => ORA_CLOB});
252 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 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 my $sql = SQL::Abstract->new(bindtype => 'columns');
263 my($stmt, @bind) = $sql->insert(...);
264 @bind = (
266 [ 'column1', 'value1' ],
267 [ 'column2', 'value2' ],
268 [ 'column3', 'value3' ],
269 );
270 You can then iterate through this manually, using DBI's
272 "bind_param()".
273 $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 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 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 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 SELECT `a_field` FROM `a_table` WHERE `some_field` LIKE '%someval%'
306 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 SELECT [a_field] FROM [a_table] WHERE [some_field] LIKE '%someval%'
313 Quoting is useful if you have tables or columns names that are
315 reserved words in your database's SQL dialect.
316 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 The parameter default in case of a single "quote_char" character is
322 the quote character itself.
323 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 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 SELECT `table`.`one_field` FROM `table` WHERE `table`.`other_field` = 1
339 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 my %condition_as_column_value_pairs = get_values_from_user();
347 $sqla->select( ... , \%condition_as_column_value_pairs );
348 If the expression matches an exception is thrown. Note that literal
350 SQL supplied via "\'...'" or "\['...']" is not checked in any way.
351 Defaults to checking for ";" and the "GO" keyword (TransactSQL)
353 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 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 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 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 The optional "\%options" hash reference may contain additional options
380 to generate the insert SQL. Currently supported options are:
381 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 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 The optional "\%options" hash reference may contain additional options
398 to generate the update SQL. Currently supported options are:
399 returning
401 See the "returning" option to insert.
402 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 $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 $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 $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 $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 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 The optional "\%options" hash reference may contain additional options
437 to generate the delete SQL. Currently supported options are:
438 returning
440 See the "returning" option to insert.
441 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 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 generate($any, 'number', $of, \@data, $struct, \%types)
455 Warning: This is an experimental method and subject to change.
456 This returns arbitrarily generated SQL. It's a really basic shortcut.
458 It will return two different things, depending on return context:
459 my($stmt, @bind) = $sql->generate('create table', \$table, \@fields);
461 my $stmt_and_val = $sql->generate('create table', \$table, \@fields);
462 These would return the following:
464 # First calling form
466 $stmt = "CREATE TABLE test (?, ?)";
467 @bind = (field1, field2);
468 # Second calling form
470 $stmt_and_val = "CREATE TABLE test (field1, field2)";
471 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 By the same token:
477 $sql->generate('alter session', { nls_date_format => 'MM/YY' });
479 Might give you:
481 ALTER SESSION SET nls_date_format = 'MM/YY'
483 You get the idea. Strings get their case twiddled, but everything else
485 remains verbatim.
486
488 is_plain_value
489 Determines if the supplied argument is a plain value as understood by
490 this module:
491 · The value is "undef"
493 · The value is a non-reference
495 · The value is an object with stringification overloading
497 · The value is of the form "{ -value => $anything }"
499 On failure returns "undef", on success returns a scalar reference to
501 the original supplied argument.
502 · Note
504 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 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 If you encounter any of the following exceptions in random places
518 within your application stack - this module may be to blame:
519 Operation "ne": no method found,
521 left argument in overloaded package <something>,
522 right argument in overloaded package <something>
523 or perhaps even
525 Stub found while resolving method "???" overloading """" in package <something>
527 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 (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 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 is_literal_value
543 Determines if the supplied argument is a literal value as understood by
544 this module:
545 · "\$sql_string"
547 · "\[ $sql_string, @bind_values ]"
549 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
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 The easiest way to explain is to show lots of examples. After each
561 %where hash shown, it is assumed you used:
562 my($stmt, @bind) = $sql->where(\%where);
564 However, note that the %where hash can be used directly in any of the
566 other functions as well, as described above.
567 Key-value pairs
569 So, let's get started. To begin, a simple hash:
570 my %where = (
572 user => 'nwiger',
573 status => 'completed'
574 );
575 Is converted to SQL "key = val" statements:
577 $stmt = "WHERE user = ? AND status = ?";
579 @bind = ('nwiger', 'completed');
580 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 my %where = (
585 user => 'nwiger',
586 status => ['assigned', 'in-progress', 'pending'];
587 );
588 This simple code will create the following:
590 $stmt = "WHERE user = ? AND ( status = ? OR status = ? OR status = ? )";
592 @bind = ('nwiger', 'assigned', 'in-progress', 'pending');
593 A field associated to an empty arrayref will be considered a logical
595 false and will generate 0=1.
596 Tests for NULL values
598 If the value part is "undef" then this is converted to SQL <IS NULL>
599 my %where = (
601 user => 'nwiger',
602 status => undef,
603 );
604 becomes:
606 $stmt = "WHERE user = ? AND status IS NULL";
608 @bind = ('nwiger');
609 To test if a column IS NOT NULL:
611 my %where = (
613 user => 'nwiger',
614 status => { '!=', undef },
615 );
616 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 my %where = (
622 user => 'nwiger',
623 status => { '!=', 'completed' }
624 );
625 Which would generate:
627 $stmt = "WHERE user = ? AND status != ?";
629 @bind = ('nwiger', 'completed');
630 To test against multiple values, just enclose the values in an
632 arrayref:
633 status => { '=', ['assigned', 'in-progress', 'pending'] };
635 Which would give you:
637 "WHERE status = ? OR status = ? OR status = ?"
639 The hashref can also contain multiple pairs, in which case it is
641 expanded into an "AND" of its elements:
642 my %where = (
644 user => 'nwiger',
645 status => { '!=', 'completed', -not_like => 'pending%' }
646 );
647 # Or more dynamically, like from a form
649 $where{user} = 'nwiger';
650 $where{status}{'!='} = 'completed';
651 $where{status}{'-not_like'} = 'pending%';
652 # Both generate this
654 $stmt = "WHERE user = ? AND status != ? AND status NOT LIKE ?";
655 @bind = ('nwiger', 'completed', 'pending%');
656 To get an OR instead, you can combine it with the arrayref idea:
658 my %where => (
660 user => 'nwiger',
661 priority => [ { '=', 2 }, { '>', 5 } ]
662 );
663 Which would generate:
665 $stmt = "WHERE ( priority = ? OR priority > ? ) AND user = ?";
667 @bind = ('2', '5', 'nwiger');
668 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 my %where = (
673 date_entered => { '>' => \["to_date(?, 'MM/DD/YYYY')", "11/26/2008"] },
674 date_expires => { '<' => \"now()" }
675 );
676 Which would generate:
678 $stmt = "WHERE date_entered > to_date(?, 'MM/DD/YYYY') AND date_expires < now()";
680 @bind = ('11/26/2008');
681 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 WHERE priority != ? AND priority != ?
687 Because, in Perl you can't do this:
689 priority => { '!=' => 2, '!=' => 1 }
691 As the second "!=" key will obliterate the first. The solution is to
693 use the special "-modifier" form inside an arrayref:
694 priority => [ -and => {'!=', 2},
696 {'!=', 1} ]
697 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 priority => -and => [{'!=', 2}, {'!=', 1}] # WRONG!
706 Here is a quick list of equivalencies, since there is some overlap:
708 # Same
710 status => {'!=', 'completed', 'not like', 'pending%' }
711 status => [ -and => {'!=', 'completed'}, {'not like', 'pending%'}]
712 # Same
714 status => {'=', ['assigned', 'in-progress']}
715 status => [ -or => {'=', 'assigned'}, {'=', 'in-progress'}]
716 status => [ {'=', 'assigned'}, {'=', 'in-progress'} ]
717 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 my %where = (
723 status => 'completed',
724 reportid => { -in => [567, 2335, 2] }
725 );
726 Which would generate:
728 $stmt = "WHERE status = ? AND reportid IN (?,?,?)";
730 @bind = ('completed', '567', '2335', '2');
731 The reverse operator "-not_in" generates SQL "NOT IN" and is used in
733 the same way.
734 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 In addition to the array you can supply a chunk of literal sql or
740 literal sql with bind:
741 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 would generate:
751 $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 Finally, if the argument to "-in" is not a reference, it will be
759 treated as a single-element array.
760 Another pair of operators is "-between" and "-not_between", used with
762 an arrayref of two values:
763 my %where = (
765 user => 'nwiger',
766 completion_date => {
767 -not_between => ['2002-10-01', '2003-02-06']
768 }
769 );
770 Would give you:
772 WHERE user = ? AND completion_date NOT BETWEEN ( ? AND ? )
774 Just like with "-in" all plausible combinations of literal SQL are
776 possible:
777 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 Would give you:
789 $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 These are the two builtin "special operators"; but the list can be
799 expanded: see section "SPECIAL OPERATORS" below.
800 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 my %where = (
808 -bool => 'is_user',
809 -not_bool => 'is_enabled',
810 );
811 Would give you:
813 WHERE is_user AND NOT is_enabled
815 If a more complex combination is required, testing more conditions,
817 then you should use the and/or operators:-
818 my %where = (
820 -and => [
821 -bool => 'one',
822 -not_bool => { two=> { -rlike => 'bar' } },
823 -not_bool => { three => [ { '=', 2 }, { '>', 5 } ] },
824 ],
825 );
826 Would give you:
828 WHERE
830 one
831 AND
832 (NOT two RLIKE ?)
833 AND
834 (NOT ( three = ? OR three > ? ))
835 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 my @where = (
842 {
843 user => 'nwiger',
844 status => { -like => ['pending%', 'dispatched'] },
845 },
846 {
847 user => 'robot',
848 status => 'unassigned',
849 }
850 );
851 This data structure would create the following:
853 $stmt = "WHERE ( user = ? AND ( status LIKE ? OR status LIKE ? ) )
855 OR ( user = ? AND status = ? ) )";
856 @bind = ('nwiger', 'pending', 'dispatched', 'robot', 'unassigned');
857 Clauses in hashrefs or arrayrefs can be prefixed with an "-and" or
859 "-or" to change the logic inside:
860 my @where = (
862 -and => [
863 user => 'nwiger',
864 [
865 -and => [ workhrs => {'>', 20}, geo => 'ASIA' ],
866 -or => { workhrs => {'<', 50}, geo => 'EURO' },
867 ],
868 ],
869 );
870 That would yield:
872 $stmt = "WHERE ( user = ?
874 AND ( ( workhrs > ? AND geo = ? )
875 OR ( workhrs < ? OR geo = ? ) ) )";
876 @bind = ('nwiger', '20', 'ASIA', '50', 'EURO');
877 Algebraic inconsistency, for historical reasons
879 "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 my @where = (
886 -and => [a => 1, b => 2],
887 -or => [c => 3, d => 4],
888 e => [-and => {-like => 'foo%'}, {-like => '%bar'} ]
889 )
890 yielding
892 WHERE ( ( ( a = ? AND b = ? )
894 OR ( c = ? OR d = ? )
895 OR ( e LIKE ? AND e LIKE ? ) ) )
896 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 { col => [ -and =>
902 { -like => 'foo%' },
903 { -like => '%bar' },
904 ] }
905 # yields: WHERE ( ( col LIKE ? AND col LIKE ? ) )
906 [ -and =>
908 { col => { -like => 'foo%' } },
909 { col => { -like => '%bar' } },
910 ]
911 # yields: WHERE ( ( col LIKE ? OR col LIKE ? ) )
912 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 -ident
922 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 my %where = (
928 priority => { '<', 2 },
929 requestor => { -ident => 'submitter' },
930 );
931 which creates:
933 $stmt = "WHERE priority < ? AND requestor = submitter";
935 @bind = ('2');
936 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 -value
942 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 my %where = (
949 array => { -value => [1, 2, 3] }
950 );
951 will result in:
953 $stmt = 'WHERE array = ?';
955 @bind = ([1, 2, 3]);
956 Note that if you were to simply say:
958 my %where = (
960 array => [1, 2, 3]
961 );
962 the result would probably not be what you wanted:
964 $stmt = 'WHERE array = ? OR array = ? OR array = ?';
966 @bind = (1, 2, 3);
967 Literal SQL
969 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 my %where = (
975 priority => { '<', 2 },
976 requestor => { -in => \'(SELECT name FROM hitmen)' },
977 );
978 Would create:
980 $stmt = "WHERE priority < ? AND requestor IN (SELECT name FROM hitmen)"
982 @bind = (2);
983 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 CAVEAT
988 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 Literal SQL with placeholders and bind values (subqueries)
995 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 my %where = (
1002 date_column => \[ "= date '2008-09-30' - ?::integer", 10 ]
1003 )
1004 This would create:
1006 $stmt = "WHERE ( date_column = date '2008-09-30' - ?::integer )"
1008 @bind = ('10');
1009 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 my %where = (
1020 date_column => \[ "= date '2008-09-30' - ?::integer", [ {} => 10 ] ]
1021 )
1022 Literal SQL is especially useful for nesting parenthesized clauses in
1024 the main SQL query. Here is a first example:
1025 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 This yields:
1034 $stmt = "WHERE (foo = ? AND bar IN (SELECT c1 FROM t1
1036 WHERE c2 < ? AND c3 LIKE ?))";
1037 @bind = (1234, 100, "foo%");
1038 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 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 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 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 which yields
1063 $stmt = "WHERE (foo = ? AND EXISTS (SELECT * FROM t1
1065 WHERE c1 = ? AND c2 > t0.c0))";
1066 @bind = (1234, 1);
1067 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 Finally, here is an example where a subquery is used for expressing
1075 unary negation:
1076 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 This yields
1086 $stmt = "lname LIKE ? AND NOT ( age < ? OR age > ? )"
1088 @bind = ('%son%', 10, 20)
1089 Deprecated usage of Literal SQL
1091 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 ·
1098 my %where = ( requestor => \'IS NOT NULL' )
1101 $stmt = "WHERE requestor IS NOT NULL"
1103 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 ·
1109 my %where = ( requestor => \'= submitter' )
1112 $stmt = "WHERE requestor = submitter"
1114 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 ·
1121 my %where = ( is_ready => \"", completed => { '>', '2012-12-21' } )
1124 $stmt = "WHERE completed > ? AND is_ready"
1126 @bind = ('2012-12-21')
1127 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 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 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
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 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
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 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 WHERE field IN (?, ?, ?)
1199 WHERE field BETWEEN ? AND ?
1200 WHERE MATCH(field) AGAINST (?, ?)
1201 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 regex
1211 the regular expression to match the operator
1212 handler
1214 Either a coderef or a plain scalar method name. In both cases the
1215 expected return is "($sql, @bind)".
1216 When supplied with a method name, it is simply called on the
1218 SQL::Abstract object as:
1219 $self->$method_name($field, $op, $arg)
1221 Where:
1223 $field is the LHS of the operator
1225 $op is the part that matched the handler regex
1226 $arg is the RHS
1227 When supplied with a coderef, it is called as:
1229 $coderef->($self, $field, $op, $arg)
1231 For example, here is an implementation of the MATCH .. AGAINST syntax
1233 for MySQL
1234 my $sqlmaker = SQL::Abstract->new(special_ops => [
1236 # 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 ]);
1253
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 A "unary operator" is a SQL syntactic clause that can be applied to a
1270 field - the operator goes before the field
1271 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 regex
1278 the regular expression to match the operator
1279 handler
1281 Either a coderef or a plain scalar method name. In both cases the
1282 expected return is $sql.
1283 When supplied with a method name, it is simply called on the
1285 SQL::Abstract object as:
1286 $self->$method_name($op, $arg)
1288 Where:
1290 $op is the part that matched the handler regex
1292 $arg is the RHS or argument of the operator
1293 When supplied with a coderef, it is called as:
1295 $coderef->($self, $op, $arg)
1297
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 To maximize performance, use a code snippet like the following:
1306 # 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 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 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
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 #!/usr/bin/perl
1335 use warnings;
1337 use strict;
1338 use CGI::FormBuilder;
1340 use SQL::Abstract;
1341 my $form = CGI::FormBuilder->new(...);
1343 my $sql = SQL::Abstract->new;
1344 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 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 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
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 This project is maintained in a git repository. The code and related
1369 tools are accessible at the following locations:
1370 · Official repo:
1372 <git://git.shadowcat.co.uk/dbsrgits/SQL-Abstract.git>
1373 · Official gitweb:
1375 <http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=dbsrgits/SQL-Abstract.git>
1376 · GitHub mirror: <https://github.com/dbsrgits/sql-abstract>
1378 · Authorized committers:
1380 <ssh://dbsrgits@git.shadowcat.co.uk/SQL-Abstract.git>
1381
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 The main changes are:
1392 · support for literal SQL through the "\ [ $sql, @bind ]" syntax.
1394 · support for the { operator => \"..." } construct (to embed literal
1396 SQL)
1397 · support for the { operator => \["...", @bind] } construct (to embed
1399 literal SQL with bind values)
1400 · optional support for array datatypes
1402 · defensive programming: check arguments
1404 · 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 · fixed semantics of _bindtype on array args
1412 · dropped the "_anoncopy" of the %where tree. No longer necessary, we
1414 just avoid shifting arrays within that tree.
1415 · dropped the "_modlogic" function
1417
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 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 Thanks!
1438
1440 DBIx::Class, DBIx::Abstract, CGI::FormBuilder, HTML::QuickTable.
1441
1443 Copyright (c) 2001-2007 Nathan Wiger <nwiger@cpan.org>. All Rights
1444 Reserved.
1445 This module is actively maintained by Matt Trout
1447 <mst@shadowcatsystems.co.uk>
1448 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
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)
1458perl v5.30.0 2019-07-26 SQL::Abstract(3)