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 function
241 that can be applied symmetrically to fields (actually SQL::Abstract
242 does not validate this option; it will just pass through what you
243 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 something
293 and reuse it repeatedly. You still get a layer of abstraction over
294 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 is_undef_value
555 Tests for undef, whether expanded or not.
556
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 The easiest way to explain is to show lots of examples. After each
564 %where hash shown, it is assumed you used:
565 my($stmt, @bind) = $sql->where(\%where);
567 However, note that the %where hash can be used directly in any of the
569 other functions as well, as described above.
570 Key-value pairs
572 So, let's get started. To begin, a simple hash:
573 my %where = (
575 user => 'nwiger',
576 status => 'completed'
577 );
578 Is converted to SQL "key = val" statements:
580 $stmt = "WHERE user = ? AND status = ?";
582 @bind = ('nwiger', 'completed');
583 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 my %where = (
588 user => 'nwiger',
589 status => ['assigned', 'in-progress', 'pending'];
590 );
591 This simple code will create the following:
593 $stmt = "WHERE user = ? AND ( status = ? OR status = ? OR status = ? )";
595 @bind = ('nwiger', 'assigned', 'in-progress', 'pending');
596 A field associated to an empty arrayref will be considered a logical
598 false and will generate 0=1.
599 Tests for NULL values
601 If the value part is "undef" then this is converted to SQL <IS NULL>
602 my %where = (
604 user => 'nwiger',
605 status => undef,
606 );
607 becomes:
609 $stmt = "WHERE user = ? AND status IS NULL";
611 @bind = ('nwiger');
612 To test if a column IS NOT NULL:
614 my %where = (
616 user => 'nwiger',
617 status => { '!=', undef },
618 );
619 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 my %where = (
625 user => 'nwiger',
626 status => { '!=', 'completed' }
627 );
628 Which would generate:
630 $stmt = "WHERE user = ? AND status != ?";
632 @bind = ('nwiger', 'completed');
633 To test against multiple values, just enclose the values in an
635 arrayref:
636 status => { '=', ['assigned', 'in-progress', 'pending'] };
638 Which would give you:
640 "WHERE status = ? OR status = ? OR status = ?"
642 The hashref can also contain multiple pairs, in which case it is
644 expanded into an "AND" of its elements:
645 my %where = (
647 user => 'nwiger',
648 status => { '!=', 'completed', -not_like => 'pending%' }
649 );
650 # Or more dynamically, like from a form
652 $where{user} = 'nwiger';
653 $where{status}{'!='} = 'completed';
654 $where{status}{'-not_like'} = 'pending%';
655 # Both generate this
657 $stmt = "WHERE user = ? AND status != ? AND status NOT LIKE ?";
658 @bind = ('nwiger', 'completed', 'pending%');
659 To get an OR instead, you can combine it with the arrayref idea:
661 my %where => (
663 user => 'nwiger',
664 priority => [ { '=', 2 }, { '>', 5 } ]
665 );
666 Which would generate:
668 $stmt = "WHERE ( priority = ? OR priority > ? ) AND user = ?";
670 @bind = ('2', '5', 'nwiger');
671 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 my %where = (
676 date_entered => { '>' => \["to_date(?, 'MM/DD/YYYY')", "11/26/2008"] },
677 date_expires => { '<' => \"now()" }
678 );
679 Which would generate:
681 $stmt = "WHERE date_entered > to_date(?, 'MM/DD/YYYY') AND date_expires < now()";
683 @bind = ('11/26/2008');
684 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 WHERE priority != ? AND priority != ?
690 Because, in Perl you can't do this:
692 priority => { '!=' => 2, '!=' => 1 }
694 As the second "!=" key will obliterate the first. The solution is to
696 use the special "-modifier" form inside an arrayref:
697 priority => [ -and => {'!=', 2},
699 {'!=', 1} ]
700 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 by
704 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 priority => -and => [{'!=', 2}, {'!=', 1}] # WRONG!
709 Here is a quick list of equivalencies, since there is some overlap:
711 # Same
713 status => {'!=', 'completed', 'not like', 'pending%' }
714 status => [ -and => {'!=', 'completed'}, {'not like', 'pending%'}]
715 # Same
717 status => {'=', ['assigned', 'in-progress']}
718 status => [ -or => {'=', 'assigned'}, {'=', 'in-progress'}]
719 status => [ {'=', 'assigned'}, {'=', 'in-progress'} ]
720 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 my %where = (
726 status => 'completed',
727 reportid => { -in => [567, 2335, 2] }
728 );
729 Which would generate:
731 $stmt = "WHERE status = ? AND reportid IN (?,?,?)";
733 @bind = ('completed', '567', '2335', '2');
734 The reverse operator "-not_in" generates SQL "NOT IN" and is used in
736 the same way.
737 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 In addition to the array you can supply a chunk of literal sql or
743 literal sql with bind:
744 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 would generate:
754 $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 Finally, if the argument to "-in" is not a reference, it will be
762 treated as a single-element array.
763 Another pair of operators is "-between" and "-not_between", used with
765 an arrayref of two values:
766 my %where = (
768 user => 'nwiger',
769 completion_date => {
770 -not_between => ['2002-10-01', '2003-02-06']
771 }
772 );
773 Would give you:
775 WHERE user = ? AND completion_date NOT BETWEEN ( ? AND ? )
777 Just like with "-in" all plausible combinations of literal SQL are
779 possible:
780 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 Would give you:
792 $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 These are the two builtin "special operators"; but the list can be
802 expanded: see section "SPECIAL OPERATORS" below.
803 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 my %where = (
811 -bool => 'is_user',
812 -not_bool => 'is_enabled',
813 );
814 Would give you:
816 WHERE is_user AND NOT is_enabled
818 If a more complex combination is required, testing more conditions,
820 then you should use the and/or operators:-
821 my %where = (
823 -and => [
824 -bool => 'one',
825 -not_bool => { two=> { -rlike => 'bar' } },
826 -not_bool => { three => [ { '=', 2 }, { '>', 5 } ] },
827 ],
828 );
829 Would give you:
831 WHERE
833 one
834 AND
835 (NOT two RLIKE ?)
836 AND
837 (NOT ( three = ? OR three > ? ))
838 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 my @where = (
845 {
846 user => 'nwiger',
847 status => { -like => ['pending%', 'dispatched'] },
848 },
849 {
850 user => 'robot',
851 status => 'unassigned',
852 }
853 );
854 This data structure would create the following:
856 $stmt = "WHERE ( user = ? AND ( status LIKE ? OR status LIKE ? ) )
858 OR ( user = ? AND status = ? ) )";
859 @bind = ('nwiger', 'pending', 'dispatched', 'robot', 'unassigned');
860 Clauses in hashrefs or arrayrefs can be prefixed with an "-and" or
862 "-or" to change the logic inside:
863 my @where = (
865 -and => [
866 user => 'nwiger',
867 [
868 -and => [ workhrs => {'>', 20}, geo => 'ASIA' ],
869 -or => { workhrs => {'<', 50}, geo => 'EURO' },
870 ],
871 ],
872 );
873 That would yield:
875 $stmt = "WHERE ( user = ?
877 AND ( ( workhrs > ? AND geo = ? )
878 OR ( workhrs < ? OR geo = ? ) ) )";
879 @bind = ('nwiger', '20', 'ASIA', '50', 'EURO');
880 Algebraic inconsistency, for historical reasons
882 "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 my @where = (
889 -and => [a => 1, b => 2],
890 -or => [c => 3, d => 4],
891 e => [-and => {-like => 'foo%'}, {-like => '%bar'} ]
892 )
893 yielding
895 WHERE ( ( ( a = ? AND b = ? )
897 OR ( c = ? OR d = ? )
898 OR ( e LIKE ? AND e LIKE ? ) ) )
899 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 { col => [ -and =>
905 { -like => 'foo%' },
906 { -like => '%bar' },
907 ] }
908 # yields: WHERE ( ( col LIKE ? AND col LIKE ? ) )
909 [ -and =>
911 { col => { -like => 'foo%' } },
912 { col => { -like => '%bar' } },
913 ]
914 # yields: WHERE ( ( col LIKE ? OR col LIKE ? ) )
915 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 -ident
925 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 my %where = (
931 priority => { '<', 2 },
932 requestor => { -ident => 'submitter' },
933 );
934 which creates:
936 $stmt = "WHERE priority < ? AND requestor = submitter";
938 @bind = ('2');
939 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 -value
945 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 my %where = (
952 array => { -value => [1, 2, 3] }
953 );
954 will result in:
956 $stmt = 'WHERE array = ?';
958 @bind = ([1, 2, 3]);
959 Note that if you were to simply say:
961 my %where = (
963 array => [1, 2, 3]
964 );
965 the result would probably not be what you wanted:
967 $stmt = 'WHERE array = ? OR array = ? OR array = ?';
969 @bind = (1, 2, 3);
970 Literal SQL
972 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 my %where = (
978 priority => { '<', 2 },
979 requestor => { -in => \'(SELECT name FROM hitmen)' },
980 );
981 Would create:
983 $stmt = "WHERE priority < ? AND requestor IN (SELECT name FROM hitmen)"
985 @bind = (2);
986 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 CAVEAT
991 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 Literal SQL with placeholders and bind values (subqueries)
998 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 my %where = (
1005 date_column => \[ "= date '2008-09-30' - ?::integer", 10 ]
1006 )
1007 This would create:
1009 $stmt = "WHERE ( date_column = date '2008-09-30' - ?::integer )"
1011 @bind = ('10');
1012 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 my %where = (
1023 date_column => \[ "= date '2008-09-30' - ?::integer", [ {} => 10 ] ]
1024 )
1025 Literal SQL is especially useful for nesting parenthesized clauses in
1027 the main SQL query. Here is a first example:
1028 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 This yields:
1037 $stmt = "WHERE (foo = ? AND bar IN (SELECT c1 FROM t1
1039 WHERE c2 < ? AND c3 LIKE ?))";
1040 @bind = (1234, 100, "foo%");
1041 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 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 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 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 which yields
1066 $stmt = "WHERE (foo = ? AND EXISTS (SELECT * FROM t1
1068 WHERE c1 = ? AND c2 > t0.c0))";
1069 @bind = (1234, 1);
1070 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 Finally, here is an example where a subquery is used for expressing
1078 unary negation:
1079 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 This yields
1089 $stmt = "lname LIKE ? AND NOT ( age < ? OR age > ? )"
1091 @bind = ('%son%', 10, 20)
1092 Deprecated usage of Literal SQL
1094 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 •
1101 my %where = ( requestor => \'IS NOT NULL' )
1104 $stmt = "WHERE requestor IS NOT NULL"
1106 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 •
1112 my %where = ( requestor => \'= submitter' )
1115 $stmt = "WHERE requestor = submitter"
1117 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 •
1124 my %where = ( is_ready => \"", completed => { '>', '2012-12-21' } )
1127 $stmt = "WHERE completed > ? AND is_ready"
1129 @bind = ('2012-12-21')
1130 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 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 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
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 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
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 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 WHERE field IN (?, ?, ?)
1203 WHERE field BETWEEN ? AND ?
1204 WHERE MATCH(field) AGAINST (?, ?)
1205 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 regex
1215 the regular expression to match the operator
1216 handler
1218 Either a coderef or a plain scalar method name. In both cases the
1219 expected return is "($sql, @bind)".
1220 When supplied with a method name, it is simply called on the
1222 SQL::Abstract object as:
1223 $self->$method_name($field, $op, $arg)
1225 Where:
1227 $field is the LHS of the operator
1229 $op is the part that matched the handler regex
1230 $arg is the RHS
1231 When supplied with a coderef, it is called as:
1233 $coderef->($self, $field, $op, $arg)
1235 For example, here is an implementation of the MATCH .. AGAINST syntax
1237 for MySQL
1238 my $sqlmaker = SQL::Abstract->new(special_ops => [
1240 # 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 ]);
1257 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 A "unary operator" is a SQL syntactic clause that can be applied to a
1274 field - the operator goes before the field
1275 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 regex
1282 the regular expression to match the operator
1283 handler
1285 Either a coderef or a plain scalar method name. In both cases the
1286 expected return is $sql.
1287 When supplied with a method name, it is simply called on the
1289 SQL::Abstract object as:
1290 $self->$method_name($op, $arg)
1292 Where:
1294 $op is the part that matched the handler regex
1296 $arg is the RHS or argument of the operator
1297 When supplied with a coderef, it is called as:
1299 $coderef->($self, $op, $arg)
1301
1303 See SQL::Abstract::Reference for the "expr" versus "aqt" concept and an
1304 explanation of what the below extensions are extending.
1305 plugin
1307 $sqla->plugin('+Foo');
1308 Enables plugin SQL::Abstract::Plugin::Foo.
1310 render_expr
1312 my ($sql, @bind) = $sqla->render_expr($expr);
1313 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 my ($sql, @bind) = $sqla->render_statement($expr);
1319 expand_expr
1321 Expression expansion with optional default for scalars.
1322 my $aqt = $self->expand_expr($expr);
1324 my $aqt = $self->expand_expr($expr, -ident);
1325 render_aqt
1327 Top level means avoid parens on statement AQT.
1328 my $res = $self->render_aqt($aqt, $top_level);
1330 my ($sql, @bind) = @$res;
1331 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 my $part = $self->join_query_parts($join, @parts);
1337
1339 clone
1340 my $sqla2 = $sqla->clone;
1341 Performs a semi-shallow copy such that extension methods won't leak
1343 state but excessive depth is avoided.
1344 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 expander_list
1355 op_expander_list
1356 clause_expander_list
1357 my @names = $sqla->expander_list;
1358 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 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 renderer_list
1381 op_renderer_list
1382 clause_renderer_list
1383 my @names = $sqla->renderer_list;
1384 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 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 statement_list
1407 my @list = $sqla->statement_list;
1408 make_unop_expander
1410 my $exp = $sqla->make_unop_expander(sub { ... });
1411 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 sub { my ($self, $name, $body) = @_; ... }
1416 make_binop_expander
1418 my $exp = $sqla->make_binop_expander(sub { ... });
1419 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 sub {
1425 my ($self, $name, $body, $k) = @_;
1426 { -blah => [ map $self->expand_expr($_), $k, $body ] }
1427 }
1428 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
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 To maximize performance, use a code snippet like the following:
1444 # 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 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 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
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 #!/usr/bin/perl
1473 use warnings;
1475 use strict;
1476 use CGI::FormBuilder;
1478 use SQL::Abstract;
1479 my $form = CGI::FormBuilder->new(...);
1481 my $sql = SQL::Abstract->new;
1482 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 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 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
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 This project is maintained in a git repository. The code and related
1507 tools are accessible at the following locations:
1508 • Official repo:
1510 <git://git.shadowcat.co.uk/dbsrgits/SQL-Abstract.git>
1511 • Official gitweb:
1513 <http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=dbsrgits/SQL-Abstract.git>
1514 • GitHub mirror: <https://github.com/dbsrgits/sql-abstract>
1516 • Authorized committers:
1518 <ssh://dbsrgits@git.shadowcat.co.uk/SQL-Abstract.git>
1519
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 The main changes are:
1530 • support for literal SQL through the "\ [ $sql, @bind ]" syntax.
1532 • support for the { operator => \"..." } construct (to embed literal
1534 SQL)
1535 • support for the { operator => \["...", @bind] } construct (to embed
1537 literal SQL with bind values)
1538 • optional support for array datatypes
1540 • defensive programming: check arguments
1542 • 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 • fixed semantics of _bindtype on array args
1550 • dropped the "_anoncopy" of the %where tree. No longer necessary, we
1552 just avoid shifting arrays within that tree.
1553 • dropped the "_modlogic" function
1555
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 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 Thanks!
1576
1578 DBIx::Class, DBIx::Abstract, CGI::FormBuilder, HTML::QuickTable.
1579
1581 Copyright (c) 2001-2007 Nathan Wiger <nwiger@cpan.org>. All Rights
1582 Reserved.
1583 This module is actively maintained by Matt Trout
1585 <mst@shadowcatsystems.co.uk>
1586 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
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)
1596perl v5.36.0 2023-01-20 SQL::Abstract(3)