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($table, \@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 functions are simple. There's one for each major SQL operation, and
155 a constructor you use first. The arguments are specified in a similar
156 order to each function (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 comparsion 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 name_sep
318 This is the character that separates a table and column name. It
319 is necessary to specify this when the "quote_char" option is
320 selected, so that tables and column names can be individually
321 quoted like this:
322 SELECT `table`.`one_field` FROM `table` WHERE `table`.`other_field` = 1
324 array_datatypes
326 When this option is true, arrayrefs in INSERT or UPDATE are
327 interpreted as array datatypes and are passed directly to the DBI
328 layer. When this option is false, arrayrefs are interpreted as
329 literal SQL, just like refs to arrayrefs (but this behavior is for
330 backwards compatibility; when writing new queries, use the
331 "reference to arrayref" syntax for literal SQL).
332 special_ops
334 Takes a reference to a list of "special operators" to extend the
335 syntax understood by SQL::Abstract. See section "SPECIAL
336 OPERATORS" for details.
337 unary_ops
339 Takes a reference to a list of "unary operators" to extend the
340 syntax understood by SQL::Abstract. See section "UNARY OPERATORS"
341 for details.
342 insert($table, \@values || \%fieldvals, \%options)
344 This is the simplest function. You simply give it a table name and
345 either an arrayref of values or hashref of field/value pairs. It
346 returns an SQL INSERT statement and a list of bind values. See the
347 sections on "Inserting and Updating Arrays" and "Inserting and Updating
348 SQL" for information on how to insert with those data types.
349 The optional "\%options" hash reference may contain additional options
351 to generate the insert SQL. Currently supported options are:
352 returning
354 Takes either a scalar of raw SQL fields, or an array reference of
355 field names, and adds on an SQL "RETURNING" statement at the end.
356 This allows you to return data generated by the insert statement
357 (such as row IDs) without performing another "SELECT" statement.
358 Note, however, this is not part of the SQL standard and may not be
359 supported by all database engines.
360 update($table, \%fieldvals, \%where)
362 This takes a table, hashref of field/value pairs, and an optional
363 hashref WHERE clause. It returns an SQL UPDATE function and a list of
364 bind values. See the sections on "Inserting and Updating Arrays" and
365 "Inserting and Updating SQL" for information on how to insert with
366 those data types.
367 select($source, $fields, $where, $order)
369 This returns a SQL SELECT statement and associated list of bind values,
370 as specified by the arguments :
371 $source
373 Specification of the 'FROM' part of the statement. The argument
374 can be either a plain scalar (interpreted as a table name, will be
375 quoted), or an arrayref (interpreted as a list of table names,
376 joined by commas, quoted), or a scalarref (literal table name, not
377 quoted), or a ref to an arrayref (list of literal table names,
378 joined by commas, not quoted).
379 $fields
381 Specification of the list of fields to retrieve from the source.
382 The argument can be either an arrayref (interpreted as a list of
383 field names, will be joined by commas and quoted), or a plain
384 scalar (literal SQL, not quoted). Please observe that this API is
385 not as flexible as for the first argument $table, for backwards
386 compatibility reasons.
387 $where
389 Optional argument to specify the WHERE part of the query. The
390 argument is most often a hashref, but can also be an arrayref or
391 plain scalar -- see section WHERE clause for details.
392 $order
394 Optional argument to specify the ORDER BY part of the query. The
395 argument can be a scalar, a hashref or an arrayref -- see section
396 ORDER BY clause for details.
397 delete($table, \%where)
399 This takes a table name and optional hashref WHERE clause. It returns
400 an SQL DELETE statement and list of bind values.
401 where(\%where, \@order)
403 This is used to generate just the WHERE clause. For example, if you
404 have an arbitrary data structure and know what the rest of your SQL is
405 going to look like, but want an easy way to produce a WHERE clause, use
406 this. It returns an SQL WHERE clause and list of bind values.
407 values(\%data)
409 This just returns the values from the hash %data, in the same order
410 that would be returned from any of the other above queries. Using this
411 allows you to markedly speed up your queries if you are affecting lots
412 of rows. See below under the "PERFORMANCE" section.
413 generate($any, 'number', $of, \@data, $struct, \%types)
415 Warning: This is an experimental method and subject to change.
416 This returns arbitrarily generated SQL. It's a really basic shortcut.
418 It will return two different things, depending on return context:
419 my($stmt, @bind) = $sql->generate('create table', \$table, \@fields);
421 my $stmt_and_val = $sql->generate('create table', \$table, \@fields);
422 These would return the following:
424 # First calling form
426 $stmt = "CREATE TABLE test (?, ?)";
427 @bind = (field1, field2);
428 # Second calling form
430 $stmt_and_val = "CREATE TABLE test (field1, field2)";
431 Depending on what you're trying to do, it's up to you to choose the
433 correct format. In this example, the second form is what you would
434 want.
435 By the same token:
437 $sql->generate('alter session', { nls_date_format => 'MM/YY' });
439 Might give you:
441 ALTER SESSION SET nls_date_format = 'MM/YY'
443 You get the idea. Strings get their case twiddled, but everything else
445 remains verbatim.
446
448 Introduction
449 This module uses a variation on the idea from DBIx::Abstract. It is
450 NOT, repeat not 100% compatible. The main logic of this module is that
451 things in arrays are OR'ed, and things in hashes are AND'ed.
452 The easiest way to explain is to show lots of examples. After each
454 %where hash shown, it is assumed you used:
455 my($stmt, @bind) = $sql->where(\%where);
457 However, note that the %where hash can be used directly in any of the
459 other functions as well, as described above.
460 Key-value pairs
462 So, let's get started. To begin, a simple hash:
463 my %where = (
465 user => 'nwiger',
466 status => 'completed'
467 );
468 Is converted to SQL "key = val" statements:
470 $stmt = "WHERE user = ? AND status = ?";
472 @bind = ('nwiger', 'completed');
473 One common thing I end up doing is having a list of values that a field
475 can be in. To do this, simply specify a list inside of an arrayref:
476 my %where = (
478 user => 'nwiger',
479 status => ['assigned', 'in-progress', 'pending'];
480 );
481 This simple code will create the following:
483 $stmt = "WHERE user = ? AND ( status = ? OR status = ? OR status = ? )";
485 @bind = ('nwiger', 'assigned', 'in-progress', 'pending');
486 A field associated to an empty arrayref will be considered a logical
488 false and will generate 0=1.
489 Specific comparison operators
491 If you want to specify a different type of operator for your
492 comparison, you can use a hashref for a given column:
493 my %where = (
495 user => 'nwiger',
496 status => { '!=', 'completed' }
497 );
498 Which would generate:
500 $stmt = "WHERE user = ? AND status != ?";
502 @bind = ('nwiger', 'completed');
503 To test against multiple values, just enclose the values in an
505 arrayref:
506 status => { '=', ['assigned', 'in-progress', 'pending'] };
508 Which would give you:
510 "WHERE status = ? OR status = ? OR status = ?"
512 The hashref can also contain multiple pairs, in which case it is
514 expanded into an "AND" of its elements:
515 my %where = (
517 user => 'nwiger',
518 status => { '!=', 'completed', -not_like => 'pending%' }
519 );
520 # Or more dynamically, like from a form
522 $where{user} = 'nwiger';
523 $where{status}{'!='} = 'completed';
524 $where{status}{'-not_like'} = 'pending%';
525 # Both generate this
527 $stmt = "WHERE user = ? AND status != ? AND status NOT LIKE ?";
528 @bind = ('nwiger', 'completed', 'pending%');
529 To get an OR instead, you can combine it with the arrayref idea:
531 my %where => (
533 user => 'nwiger',
534 priority => [ {'=', 2}, {'!=', 1} ]
535 );
536 Which would generate:
538 $stmt = "WHERE user = ? AND priority = ? OR priority != ?";
540 @bind = ('nwiger', '2', '1');
541 If you want to include literal SQL (with or without bind values), just
543 use a scalar reference or array reference as the value:
544 my %where = (
546 date_entered => { '>' => \["to_date(?, 'MM/DD/YYYY')", "11/26/2008"] },
547 date_expires => { '<' => \"now()" }
548 );
549 Which would generate:
551 $stmt = "WHERE date_entered > "to_date(?, 'MM/DD/YYYY') AND date_expires < now()";
553 @bind = ('11/26/2008');
554 Logic and nesting operators
556 In the example above, there is a subtle trap if you want to say
557 something like this (notice the "AND"):
558 WHERE priority != ? AND priority != ?
560 Because, in Perl you can't do this:
562 priority => { '!=', 2, '!=', 1 }
564 As the second "!=" key will obliterate the first. The solution is to
566 use the special "-modifier" form inside an arrayref:
567 priority => [ -and => {'!=', 2},
569 {'!=', 1} ]
570 Normally, these would be joined by "OR", but the modifier tells it to
572 use "AND" instead. (Hint: You can use this in conjunction with the
573 "logic" option to "new()" in order to change the way your queries work
574 by default.) Important: Note that the "-modifier" goes INSIDE the
575 arrayref, as an extra first element. This will NOT do what you think it
576 might:
577 priority => -and => [{'!=', 2}, {'!=', 1}] # WRONG!
579 Here is a quick list of equivalencies, since there is some overlap:
581 # Same
583 status => {'!=', 'completed', 'not like', 'pending%' }
584 status => [ -and => {'!=', 'completed'}, {'not like', 'pending%'}]
585 # Same
587 status => {'=', ['assigned', 'in-progress']}
588 status => [ -or => {'=', 'assigned'}, {'=', 'in-progress'}]
589 status => [ {'=', 'assigned'}, {'=', 'in-progress'} ]
590 Special operators : IN, BETWEEN, etc.
592 You can also use the hashref format to compare a list of fields using
593 the "IN" comparison operator, by specifying the list as an arrayref:
594 my %where = (
596 status => 'completed',
597 reportid => { -in => [567, 2335, 2] }
598 );
599 Which would generate:
601 $stmt = "WHERE status = ? AND reportid IN (?,?,?)";
603 @bind = ('completed', '567', '2335', '2');
604 The reverse operator "-not_in" generates SQL "NOT IN" and is used in
606 the same way.
607 If the argument to "-in" is an empty array, 'sqlfalse' is generated (by
609 default : "1=0"). Similarly, "-not_in => []" generates 'sqltrue' (by
610 default : "1=1").
611 In addition to the array you can supply a chunk of literal sql or
613 literal sql with bind:
614 my %where = {
616 customer => { -in => \[
617 'SELECT cust_id FROM cust WHERE balance > ?',
618 2000,
619 ],
620 status => { -in => \'SELECT status_codes FROM states' },
621 };
622 would generate:
624 $stmt = "WHERE (
626 customer IN ( SELECT cust_id FROM cust WHERE balance > ? )
627 AND status IN ( SELECT status_codes FROM states )
628 )";
629 @bind = ('2000');
630 Another pair of operators is "-between" and "-not_between", used with
632 an arrayref of two values:
633 my %where = (
635 user => 'nwiger',
636 completion_date => {
637 -not_between => ['2002-10-01', '2003-02-06']
638 }
639 );
640 Would give you:
642 WHERE user = ? AND completion_date NOT BETWEEN ( ? AND ? )
644 Just like with "-in" all plausible combinations of literal SQL are
646 possible:
647 my %where = {
649 start0 => { -between => [ 1, 2 ] },
650 start1 => { -between => \["? AND ?", 1, 2] },
651 start2 => { -between => \"lower(x) AND upper(y)" },
652 start3 => { -between => [
653 \"lower(x)",
654 \["upper(?)", 'stuff' ],
655 ] },
656 };
657 Would give you:
659 $stmt = "WHERE (
661 ( start0 BETWEEN ? AND ? )
662 AND ( start1 BETWEEN ? AND ? )
663 AND ( start2 BETWEEN lower(x) AND upper(y) )
664 AND ( start3 BETWEEN lower(x) AND upper(?) )
665 )";
666 @bind = (1, 2, 1, 2, 'stuff');
667 These are the two builtin "special operators"; but the list can be
669 expanded : see section "SPECIAL OPERATORS" below.
670 Unary operators: bool
672 If you wish to test against boolean columns or functions within your
673 database you can use the "-bool" and "-not_bool" operators. For example
674 to test the column "is_user" being true and the column <is_enabled>
675 being false you would use:-
676 my %where = (
678 -bool => 'is_user',
679 -not_bool => 'is_enabled',
680 );
681 Would give you:
683 WHERE is_user AND NOT is_enabled
685 If a more complex combination is required, testing more conditions,
687 then you should use the and/or operators:-
688 my %where = (
690 -and => [
691 -bool => 'one',
692 -bool => 'two',
693 -bool => 'three',
694 -not_bool => 'four',
695 ],
696 );
697 Would give you:
699 WHERE one AND two AND three AND NOT four
701 Nested conditions, -and/-or prefixes
703 So far, we've seen how multiple conditions are joined with a top-level
704 "AND". We can change this by putting the different conditions we want
705 in hashes and then putting those hashes in an array. For example:
706 my @where = (
708 {
709 user => 'nwiger',
710 status => { -like => ['pending%', 'dispatched'] },
711 },
712 {
713 user => 'robot',
714 status => 'unassigned',
715 }
716 );
717 This data structure would create the following:
719 $stmt = "WHERE ( user = ? AND ( status LIKE ? OR status LIKE ? ) )
721 OR ( user = ? AND status = ? ) )";
722 @bind = ('nwiger', 'pending', 'dispatched', 'robot', 'unassigned');
723 There is also a special "-nest" operator which adds an additional set
725 of parens, to create a subquery. For example, to get something like
726 this:
727 $stmt = "WHERE user = ? AND ( workhrs > ? OR geo = ? )";
729 @bind = ('nwiger', '20', 'ASIA');
730 You would do:
732 my %where = (
734 user => 'nwiger',
735 -nest => [ workhrs => {'>', 20}, geo => 'ASIA' ],
736 );
737 Finally, clauses in hashrefs or arrayrefs can be prefixed with an
739 "-and" or "-or" to change the logic inside :
740 my @where = (
742 -and => [
743 user => 'nwiger',
744 -nest => [
745 -and => [workhrs => {'>', 20}, geo => 'ASIA' ],
746 -and => [workhrs => {'<', 50}, geo => 'EURO' ]
747 ],
748 ],
749 );
750 That would yield:
752 WHERE ( user = ? AND
754 ( ( workhrs > ? AND geo = ? )
755 OR ( workhrs < ? AND geo = ? ) ) )
756 Algebraic inconsistency, for historical reasons
758 "Important note": when connecting several conditions, the "-and-"|"-or"
759 operator goes "outside" of the nested structure; whereas when
760 connecting several constraints on one column, the "-and" operator goes
761 "inside" the arrayref. Here is an example combining both features :
762 my @where = (
764 -and => [a => 1, b => 2],
765 -or => [c => 3, d => 4],
766 e => [-and => {-like => 'foo%'}, {-like => '%bar'} ]
767 )
768 yielding
770 WHERE ( ( ( a = ? AND b = ? )
772 OR ( c = ? OR d = ? )
773 OR ( e LIKE ? AND e LIKE ? ) ) )
774 This difference in syntax is unfortunate but must be preserved for
776 historical reasons. So be careful : the two examples below would seem
777 algebraically equivalent, but they are not
778 {col => [-and => {-like => 'foo%'}, {-like => '%bar'}]}
780 # yields : WHERE ( ( col LIKE ? AND col LIKE ? ) )
781 [-and => {col => {-like => 'foo%'}, {col => {-like => '%bar'}}]]
783 # yields : WHERE ( ( col LIKE ? OR col LIKE ? ) )
784 Literal SQL
786 Finally, sometimes only literal SQL will do. If you want to include
787 literal SQL verbatim, you can specify it as a scalar reference, namely:
788 my $inn = 'is Not Null';
790 my %where = (
791 priority => { '<', 2 },
792 requestor => \$inn
793 );
794 This would create:
796 $stmt = "WHERE priority < ? AND requestor is Not Null";
798 @bind = ('2');
799 Note that in this example, you only get one bind parameter back, since
801 the verbatim SQL is passed as part of the statement.
802 Of course, just to prove a point, the above can also be accomplished
804 with this:
805 my %where = (
807 priority => { '<', 2 },
808 requestor => { '!=', undef },
809 );
810 TMTOWTDI
812 Conditions on boolean columns can be expressed in the same way, passing
814 a reference to an empty string, however using liternal SQL in this way
815 is deprecated - the preferred method is to use the boolean operators -
816 see "Unary operators: bool" :
817 my %where = (
819 priority => { '<', 2 },
820 is_ready => \"";
821 );
822 which yields
824 $stmt = "WHERE priority < ? AND is_ready";
826 @bind = ('2');
827 Literal SQL with placeholders and bind values (subqueries)
829 If the literal SQL to be inserted has placeholders and bind values, use
830 a reference to an arrayref (yes this is a double reference -- not so
831 common, but perfectly legal Perl). For example, to find a date in
832 Postgres you can use something like this:
833 my %where = (
835 date_column => \[q/= date '2008-09-30' - ?::integer/, 10/]
836 )
837 This would create:
839 $stmt = "WHERE ( date_column = date '2008-09-30' - ?::integer )"
841 @bind = ('10');
842 Note that you must pass the bind values in the same format as they are
844 returned by "where". That means that if you set "bindtype" to
845 "columns", you must provide the bind values in the "[ column_meta =>
846 value ]" format, where "column_meta" is an opaque scalar value; most
847 commonly the column name, but you can use any scalar value (including
848 references and blessed references), SQL::Abstract will simply pass it
849 through intact. So if "bindtype" is set to "columns" the above example
850 will look like:
851 my %where = (
853 date_column => \[q/= date '2008-09-30' - ?::integer/, [ dummy => 10 ]/]
854 )
855 Literal SQL is especially useful for nesting parenthesized clauses in
857 the main SQL query. Here is a first example :
858 my ($sub_stmt, @sub_bind) = ("SELECT c1 FROM t1 WHERE c2 < ? AND c3 LIKE ?",
860 100, "foo%");
861 my %where = (
862 foo => 1234,
863 bar => \["IN ($sub_stmt)" => @sub_bind],
864 );
865 This yields :
867 $stmt = "WHERE (foo = ? AND bar IN (SELECT c1 FROM t1
869 WHERE c2 < ? AND c3 LIKE ?))";
870 @bind = (1234, 100, "foo%");
871 Other subquery operators, like for example "> ALL" or "NOT IN", are
873 expressed in the same way. Of course the $sub_stmt and its associated
874 bind values can be generated through a former call to "select()" :
875 my ($sub_stmt, @sub_bind)
877 = $sql->select("t1", "c1", {c2 => {"<" => 100},
878 c3 => {-like => "foo%"}});
879 my %where = (
880 foo => 1234,
881 bar => \["> ALL ($sub_stmt)" => @sub_bind],
882 );
883 In the examples above, the subquery was used as an operator on a
885 column; but the same principle also applies for a clause within the
886 main %where hash, like an EXISTS subquery :
887 my ($sub_stmt, @sub_bind)
889 = $sql->select("t1", "*", {c1 => 1, c2 => \"> t0.c0"});
890 my %where = (
891 foo => 1234,
892 -nest => \["EXISTS ($sub_stmt)" => @sub_bind],
893 );
894 which yields
896 $stmt = "WHERE (foo = ? AND EXISTS (SELECT * FROM t1
898 WHERE c1 = ? AND c2 > t0.c0))";
899 @bind = (1234, 1);
900 Observe that the condition on "c2" in the subquery refers to column
902 "t0.c0" of the main query : this is not a bind value, so we have to
903 express it through a scalar ref. Writing "c2 => {">" => "t0.c0"}"
904 would have generated "c2 > ?" with bind value "t0.c0" ... not exactly
905 what we wanted here.
906 Another use of the subquery technique is when some SQL clauses need
908 parentheses, as it often occurs with some proprietary SQL extensions
909 like for example fulltext expressions, geospatial expressions, NATIVE
910 clauses, etc. Here is an example of a fulltext query in MySQL :
911 my %where = (
913 -nest => \["MATCH (col1, col2) AGAINST (?)" => qw/apples/]
914 );
915 Finally, here is an example where a subquery is used for expressing
917 unary negation:
918 my ($sub_stmt, @sub_bind)
920 = $sql->where({age => [{"<" => 10}, {">" => 20}]});
921 $sub_stmt =~ s/^ where //i; # don't want "WHERE" in the subclause
922 my %where = (
923 lname => {like => '%son%'},
924 -nest => \["NOT ($sub_stmt)" => @sub_bind],
925 );
926 This yields
928 $stmt = "lname LIKE ? AND NOT ( age < ? OR age > ? )"
930 @bind = ('%son%', 10, 20)
931 Conclusion
933 These pages could go on for a while, since the nesting of the data
934 structures this module can handle are pretty much unlimited (the module
935 implements the "WHERE" expansion as a recursive function internally).
936 Your best bet is to "play around" with the module a little to see how
937 the data structures behave, and choose the best format for your data
938 based on that.
939 And of course, all the values above will probably be replaced with
941 variables gotten from forms or the command line. After all, if you knew
942 everything ahead of time, you wouldn't have to worry about dynamically-
943 generating SQL and could just hardwire it into your script.
944
946 Some functions take an order by clause. This can either be a scalar
947 (just a column name,) a hash of "{ -desc => 'col' }" or "{ -asc =>
948 'col' }", or an array of either of the two previous forms. Examples:
949 Given | Will Generate
951 ----------------------------------------------------------
952 |
953 \'colA DESC' | ORDER BY colA DESC
954 |
955 'colA' | ORDER BY colA
956 |
957 [qw/colA colB/] | ORDER BY colA, colB
958 |
959 {-asc => 'colA'} | ORDER BY colA ASC
960 |
961 {-desc => 'colB'} | ORDER BY colB DESC
962 |
963 ['colA', {-asc => 'colB'}] | ORDER BY colA, colB ASC
964 |
965 { -asc => [qw/colA colB/] } | ORDER BY colA ASC, colB ASC
966 |
967 [ |
968 { -asc => 'colA' }, | ORDER BY colA ASC, colB DESC,
969 { -desc => [qw/colB/], | colC ASC, colD ASC
970 { -asc => [qw/colC colD/],|
971 ] |
972 ===========================================================
973
975 my $sqlmaker = SQL::Abstract->new(special_ops => [
976 {
977 regex => qr/.../,
978 handler => sub {
979 my ($self, $field, $op, $arg) = @_;
980 ...
981 },
982 },
983 {
984 regex => qr/.../,
985 handler => 'method_name',
986 },
987 ]);
988 A "special operator" is a SQL syntactic clause that can be applied to a
990 field, instead of a usual binary operator. For example :
991 WHERE field IN (?, ?, ?)
993 WHERE field BETWEEN ? AND ?
994 WHERE MATCH(field) AGAINST (?, ?)
995 Special operators IN and BETWEEN are fairly standard and therefore are
997 builtin within "SQL::Abstract" (as the overridable methods
998 "_where_field_IN" and "_where_field_BETWEEN"). For other operators,
999 like the MATCH .. AGAINST example above which is specific to MySQL, you
1000 can write your own operator handlers - supply a "special_ops" argument
1001 to the "new" method. That argument takes an arrayref of operator
1002 definitions; each operator definition is a hashref with two entries:
1003 regex
1005 the regular expression to match the operator
1006 handler
1008 Either a coderef or a plain scalar method name. In both cases the
1009 expected return is "($sql, @bind)".
1010 When supplied with a method name, it is simply called on the "" in
1012 SQL::Abstract object as:
1013 $self->$method_name ($field, $op, $arg)
1015 Where:
1017 $op is the part that matched the handler regex
1019 $field is the LHS of the operator
1020 $arg is the RHS
1021 When supplied with a coderef, it is called as:
1023 $coderef->($self, $field, $op, $arg)
1025 For example, here is an implementation of the MATCH .. AGAINST syntax
1027 for MySQL
1028 my $sqlmaker = SQL::Abstract->new(special_ops => [
1030 # special op for MySql MATCH (field) AGAINST(word1, word2, ...)
1032 {regex => qr/^match$/i,
1033 handler => sub {
1034 my ($self, $field, $op, $arg) = @_;
1035 $arg = [$arg] if not ref $arg;
1036 my $label = $self->_quote($field);
1037 my ($placeholder) = $self->_convert('?');
1038 my $placeholders = join ", ", (($placeholder) x @$arg);
1039 my $sql = $self->_sqlcase('match') . " ($label) "
1040 . $self->_sqlcase('against') . " ($placeholders) ";
1041 my @bind = $self->_bindtype($field, @$arg);
1042 return ($sql, @bind);
1043 }
1044 },
1045 ]);
1047
1049 my $sqlmaker = SQL::Abstract->new(unary_ops => [
1050 {
1051 regex => qr/.../,
1052 handler => sub {
1053 my ($self, $op, $arg) = @_;
1054 ...
1055 },
1056 },
1057 {
1058 regex => qr/.../,
1059 handler => 'method_name',
1060 },
1061 ]);
1062 A "unary operator" is a SQL syntactic clause that can be applied to a
1064 field - the operator goes before the field
1065 You can write your own operator handlers - supply a "unary_ops"
1067 argument to the "new" method. That argument takes an arrayref of
1068 operator definitions; each operator definition is a hashref with two
1069 entries:
1070 regex
1072 the regular expression to match the operator
1073 handler
1075 Either a coderef or a plain scalar method name. In both cases the
1076 expected return is $sql.
1077 When supplied with a method name, it is simply called on the "" in
1079 SQL::Abstract object as:
1080 $self->$method_name ($op, $arg)
1082 Where:
1084 $op is the part that matched the handler regex
1086 $arg is the RHS or argument of the operator
1087 When supplied with a coderef, it is called as:
1089 $coderef->($self, $op, $arg)
1091
1093 Thanks to some benchmarking by Mark Stosberg, it turns out that this
1094 module is many orders of magnitude faster than using "DBIx::Abstract".
1095 I must admit this wasn't an intentional design issue, but it's a
1096 byproduct of the fact that you get to control your "DBI" handles
1097 yourself.
1098 To maximize performance, use a code snippet like the following:
1100 # prepare a statement handle using the first row
1102 # and then reuse it for the rest of the rows
1103 my($sth, $stmt);
1104 for my $href (@array_of_hashrefs) {
1105 $stmt ||= $sql->insert('table', $href);
1106 $sth ||= $dbh->prepare($stmt);
1107 $sth->execute($sql->values($href));
1108 }
1109 The reason this works is because the keys in your $href are sorted
1111 internally by SQL::Abstract. Thus, as long as your data retains the
1112 same structure, you only have to generate the SQL the first time
1113 around. On subsequent queries, simply use the "values" function
1114 provided by this module to return your values in the correct order.
1115
1117 If you use my "CGI::FormBuilder" module at all, you'll hopefully really
1118 like this part (I do, at least). Building up a complex query can be as
1119 simple as the following:
1120 #!/usr/bin/perl
1122 use CGI::FormBuilder;
1124 use SQL::Abstract;
1125 my $form = CGI::FormBuilder->new(...);
1127 my $sql = SQL::Abstract->new;
1128 if ($form->submitted) {
1130 my $field = $form->field;
1131 my $id = delete $field->{id};
1132 my($stmt, @bind) = $sql->update('table', $field, {id => $id});
1133 }
1134 Of course, you would still have to connect using "DBI" to run the
1136 query, but the point is that if you make your form look like your
1137 table, the actual query script can be extremely simplistic.
1138 If you're REALLY lazy (I am), check out "HTML::QuickTable" for a fast
1140 interface to returning and formatting data. I frequently use these
1141 three modules together to write complex database query apps in under 50
1142 lines.
1143
1145 Version 1.50 was a major internal refactoring of "SQL::Abstract".
1146 Great care has been taken to preserve the published behavior documented
1147 in previous versions in the 1.* family; however, some features that
1148 were previously undocumented, or behaved differently from the
1149 documentation, had to be changed in order to clarify the semantics.
1150 Hence, client code that was relying on some dark areas of
1151 "SQL::Abstract" v1.* might behave differently in v1.50.
1152 The main changes are :
1154 · support for literal SQL through the "\ [$sql, bind]" syntax.
1156 · support for the { operator => \"..." } construct (to embed literal
1158 SQL)
1159 · support for the { operator => \["...", @bind] } construct (to embed
1161 literal SQL with bind values)
1162 · optional support for array datatypes
1164 · defensive programming : check arguments
1166 · fixed bug with global logic, which was previously implemented
1168 through global variables yielding side-effects. Prior versions
1169 would interpret "[ {cond1, cond2}, [cond3, cond4] ]" as "(cond1 AND
1170 cond2) OR (cond3 AND cond4)". Now this is interpreted as "(cond1
1171 AND cond2) OR (cond3 OR cond4)".
1172 · fixed semantics of _bindtype on array args
1174 · dropped the "_anoncopy" of the %where tree. No longer necessary, we
1176 just avoid shifting arrays within that tree.
1177 · dropped the "_modlogic" function
1179
1181 There are a number of individuals that have really helped out with this
1182 module. Unfortunately, most of them submitted bugs via CPAN so I have
1183 no idea who they are! But the people I do know are:
1184 Ash Berlin (order_by hash term support)
1186 Matt Trout (DBIx::Class support)
1187 Mark Stosberg (benchmarking)
1188 Chas Owens (initial "IN" operator support)
1189 Philip Collins (per-field SQL functions)
1190 Eric Kolve (hashref "AND" support)
1191 Mike Fragassi (enhancements to "BETWEEN" and "LIKE")
1192 Dan Kubb (support for "quote_char" and "name_sep")
1193 Guillermo Roditi (patch to cleanup "IN" and "BETWEEN", fix and tests for _order_by)
1194 Laurent Dami (internal refactoring, multiple -nest, extensible list of special operators, literal SQL)
1195 Norbert Buchmuller (support for literal SQL in hashpair, misc. fixes & tests)
1196 Peter Rabbitson (rewrite of SQLA::Test, misc. fixes & tests)
1197 Oliver Charles (support for "RETURNING" after "INSERT")
1198 Thanks!
1200
1202 DBIx::Class, DBIx::Abstract, CGI::FormBuilder, HTML::QuickTable.
1203
1205 Copyright (c) 2001-2007 Nathan Wiger <nwiger@cpan.org>. All Rights
1206 Reserved.
1207 This module is actively maintained by Matt Trout
1209 <mst@shadowcatsystems.co.uk>
1210 For support, your best bet is to try the "DBIx::Class" users mailing
1212 list. While not an official support venue, "DBIx::Class" makes heavy
1213 use of "SQL::Abstract", and as such list members there are very
1214 familiar with how to create queries.
1215
1217 This module is free software; you may copy this under the same terms as
1218 perl itself (either the GNU General Public License or the Artistic
1219 License)
1220perl v5.12.1 2010-05-31 SQL::Abstract(3)