1SQL::Abstract::Classic(U3s)er Contributed Perl DocumentatSiQoLn::Abstract::Classic(3)
2
6 SQL::Abstract::Classic - Generate SQL from Perl data structures
7
9 use SQL::Abstract::Classic;
10 my $sql = SQL::Abstract::Classic->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 is nearly identical to SQL::Abstract v1.81
34 <https://metacpan.org/release/RIBASUSHI/SQL-Abstract-1.81>. A recent
35 flurry of activity on the original SQL::Abstract namespace risks
36 leaving downstream users without a way to opt out of impending
37 developments. Therefore this module exists to preserve the ability of
38 users to opt into the new way of doing things according to their own
39 schedules.
40
42 This module was inspired by the excellent DBIx::Abstract. However, in
43 using that module I found that what I really wanted to do was generate
44 SQL, but still retain complete control over my statement handles and
45 use the DBI interface. So, I set out to create an abstract SQL
46 generation module.
47 While based on the concepts used by DBIx::Abstract, there are several
49 important differences, especially when it comes to WHERE clauses. I
50 have modified the concepts used to make the SQL easier to generate from
51 Perl data structures and, IMO, more intuitive. The underlying idea is
52 for this module to do what you mean, based on the data structures you
53 provide it. The big advantage is that you don't have to modify your
54 code every time your data changes, as this module figures it out.
55 To begin with, an SQL INSERT is as easy as just specifying a hash of
57 "key=value" pairs:
58 my %data = (
60 name => 'Jimbo Bobson',
61 phone => '123-456-7890',
62 address => '42 Sister Lane',
63 city => 'St. Louis',
64 state => 'Louisiana',
65 );
66 The SQL can then be generated with this:
68 my($stmt, @bind) = $sql->insert('people', \%data);
70 Which would give you something like this:
72 $stmt = "INSERT INTO people
74 (address, city, name, phone, state)
75 VALUES (?, ?, ?, ?, ?)";
76 @bind = ('42 Sister Lane', 'St. Louis', 'Jimbo Bobson',
77 '123-456-7890', 'Louisiana');
78 These are then used directly in your DBI code:
80 my $sth = $dbh->prepare($stmt);
82 $sth->execute(@bind);
83 Inserting and Updating Arrays
85 If your database has array types (like for example Postgres), activate
86 the special option "array_datatypes => 1" when creating the
87 "SQL::Abstract::Classic" object. Then you may use an arrayref to
88 insert and update database array types:
89 my $sql = SQL::Abstract::Classic->new(array_datatypes => 1);
91 my %data = (
92 planets => [qw/Mercury Venus Earth Mars/]
93 );
94 my($stmt, @bind) = $sql->insert('solar_system', \%data);
96 This results in:
98 $stmt = "INSERT INTO solar_system (planets) VALUES (?)"
100 @bind = (['Mercury', 'Venus', 'Earth', 'Mars']);
102 Inserting and Updating SQL
104 In order to apply SQL functions to elements of your %data you may
105 specify a reference to an arrayref for the given hash value. For
106 example, if you need to execute the Oracle "to_date" function on a
107 value, you can say something like this:
108 my %data = (
110 name => 'Bill',
111 date_entered => \[ "to_date(?,'MM/DD/YYYY')", "03/02/2003" ],
112 );
113 The first value in the array is the actual SQL. Any other values are
115 optional and would be included in the bind values array. This gives
116 you:
117 my($stmt, @bind) = $sql->insert('people', \%data);
119 $stmt = "INSERT INTO people (name, date_entered)
121 VALUES (?, to_date(?,'MM/DD/YYYY'))";
122 @bind = ('Bill', '03/02/2003');
123 An UPDATE is just as easy, all you change is the name of the function:
125 my($stmt, @bind) = $sql->update('people', \%data);
127 Notice that your %data isn't touched; the module will generate the
129 appropriately quirky SQL for you automatically. Usually you'll want to
130 specify a WHERE clause for your UPDATE, though, which is where handling
131 %where hashes comes in handy...
132 Complex where statements
134 This module can generate pretty complicated WHERE statements easily.
135 For example, simple "key=value" pairs are taken to mean equality, and
136 if you want to see if a field is within a set of values, you can use an
137 arrayref. Let's say we wanted to SELECT some data based on this
138 criteria:
139 my %where = (
141 requestor => 'inna',
142 worker => ['nwiger', 'rcwe', 'sfz'],
143 status => { '!=', 'completed' }
144 );
145 my($stmt, @bind) = $sql->select('tickets', '*', \%where);
147 The above would give you something like this:
149 $stmt = "SELECT * FROM tickets WHERE
151 ( requestor = ? ) AND ( status != ? )
152 AND ( worker = ? OR worker = ? OR worker = ? )";
153 @bind = ('inna', 'completed', 'nwiger', 'rcwe', 'sfz');
154 Which you could then use in DBI code like so:
156 my $sth = $dbh->prepare($stmt);
158 $sth->execute(@bind);
159 Easy, eh?
161
163 The methods are simple. There's one for every major SQL operation, and
164 a constructor you use first. The arguments are specified in a similar
165 order for each method (table, then fields, then a where clause) to try
166 and simplify things.
167 new(option => 'value')
169 The "new()" function takes a list of options and values, and returns a
170 new SQL::Abstract::Classic object which can then be used to generate
171 SQL through the methods below. The options accepted are:
172 case
174 If set to 'lower', then SQL will be generated in all lowercase. By
175 default SQL is generated in "textbook" case meaning something like:
176 SELECT a_field FROM a_table WHERE some_field LIKE '%someval%'
178 Any setting other than 'lower' is ignored.
180 cmp This determines what the default comparison operator is. By default
182 it is "=", meaning that a hash like this:
183 %where = (name => 'nwiger', email => 'nate@wiger.org');
185 Will generate SQL like this:
187 WHERE name = 'nwiger' AND email = 'nate@wiger.org'
189 However, you may want loose comparisons by default, so if you set
191 "cmp" to "like" you would get SQL such as:
192 WHERE name like 'nwiger' AND email like 'nate@wiger.org'
194 You can also override the comparison on an individual basis - see
196 the huge section on "WHERE CLAUSES" at the bottom.
197 sqltrue, sqlfalse
199 Expressions for inserting boolean values within SQL statements. By
200 default these are "1=1" and "1=0". They are used by the special
201 operators "-in" and "-not_in" for generating correct SQL even when
202 the argument is an empty array (see below).
203 logic
205 This determines the default logical operator for multiple WHERE
206 statements in arrays or hashes. If absent, the default logic is
207 "or" for arrays, and "and" for hashes. This means that a WHERE
208 array of the form:
209 @where = (
211 event_date => {'>=', '2/13/99'},
212 event_date => {'<=', '4/24/03'},
213 );
214 will generate SQL like this:
216 WHERE event_date >= '2/13/99' OR event_date <= '4/24/03'
218 This is probably not what you want given this query, though (look
220 at the dates). To change the "OR" to an "AND", simply specify:
221 my $sql = SQL::Abstract::Classic->new(logic => 'and');
223 Which will change the above "WHERE" to:
225 WHERE event_date >= '2/13/99' AND event_date <= '4/24/03'
227 The logic can also be changed locally by inserting a modifier in
229 front of an arrayref:
230 @where = (-and => [event_date => {'>=', '2/13/99'},
232 event_date => {'<=', '4/24/03'} ]);
233 See the "WHERE CLAUSES" section for explanations.
235 convert
237 This will automatically convert comparisons using the specified SQL
238 function for both column and value. This is mostly used with an
239 argument of "upper" or "lower", so that the SQL will have the
240 effect of case-insensitive "searches". For example, this:
241 $sql = SQL::Abstract::Classic->new(convert => 'upper');
243 %where = (keywords => 'MaKe iT CAse inSeNSItive');
244 Will turn out the following SQL:
246 WHERE upper(keywords) like upper('MaKe iT CAse inSeNSItive')
248 The conversion can be "upper()", "lower()", or any other SQL
250 function that can be applied symmetrically to fields (actually
251 SQL::Abstract::Classic does not validate this option; it will just
252 pass through what you specify verbatim).
253 bindtype
255 This is a kludge because many databases suck. For example, you
256 can't just bind values using DBI's "execute()" for Oracle "CLOB" or
257 "BLOB" fields. Instead, you have to use "bind_param()":
258 $sth->bind_param(1, 'reg data');
260 $sth->bind_param(2, $lots, {ora_type => ORA_CLOB});
261 The problem is, SQL::Abstract::Classic will normally just return a
263 @bind array, which loses track of which field each slot refers to.
264 Fear not.
265 If you specify "bindtype" in new, you can determine how @bind is
267 returned. Currently, you can specify either "normal" (default) or
268 "columns". If you specify "columns", you will get an array that
269 looks like this:
270 my $sql = SQL::Abstract::Classic->new(bindtype => 'columns');
272 my($stmt, @bind) = $sql->insert(...);
273 @bind = (
275 [ 'column1', 'value1' ],
276 [ 'column2', 'value2' ],
277 [ 'column3', 'value3' ],
278 );
279 You can then iterate through this manually, using DBI's
281 "bind_param()".
282 $sth->prepare($stmt);
284 my $i = 1;
285 for (@bind) {
286 my($col, $data) = @$_;
287 if ($col eq 'details' || $col eq 'comments') {
288 $sth->bind_param($i, $data, {ora_type => ORA_CLOB});
289 } elsif ($col eq 'image') {
290 $sth->bind_param($i, $data, {ora_type => ORA_BLOB});
291 } else {
292 $sth->bind_param($i, $data);
293 }
294 $i++;
295 }
296 $sth->execute; # execute without @bind now
297 Now, why would you still use SQL::Abstract::Classic if you have to
299 do this crap? Basically, the advantage is still that you don't
300 have to care which fields are or are not included. You could wrap
301 that above "for" loop in a simple sub called "bind_fields()" or
302 something and reuse it repeatedly. You still get a layer of
303 abstraction over manual SQL specification.
304 Note that if you set "bindtype" to "columns", the "\[ $sql, @bind
306 ]" construct (see "Literal SQL with placeholders and bind values
307 (subqueries)") will expect the bind values in this format.
308 quote_char
310 This is the character that a table or column name will be quoted
311 with. By default this is an empty string, but you could set it to
312 the character "`", to generate SQL like this:
313 SELECT `a_field` FROM `a_table` WHERE `some_field` LIKE '%someval%'
315 Alternatively, you can supply an array ref of two items, the first
317 being the left hand quote character, and the second the right hand
318 quote character. For example, you could supply "['[',']']" for SQL
319 Server 2000 compliant quotes that generates SQL like this:
320 SELECT [a_field] FROM [a_table] WHERE [some_field] LIKE '%someval%'
322 Quoting is useful if you have tables or columns names that are
324 reserved words in your database's SQL dialect.
325 escape_char
327 This is the character that will be used to escape "quote_char"s
328 appearing in an identifier before it has been quoted.
329 The parameter default in case of a single "quote_char" character is
331 the quote character itself.
332 When opening-closing-style quoting is used ("quote_char" is an
334 arrayref) this parameter defaults to the closing (right)
335 "quote_char". Occurrences of the opening (left) "quote_char" within
336 the identifier are currently left untouched. The default for
337 opening-closing-style quotes may change in future versions, thus
338 you are strongly encouraged to specify the escape character
339 explicitly.
340 name_sep
342 This is the character that separates a table and column name. It
343 is necessary to specify this when the "quote_char" option is
344 selected, so that tables and column names can be individually
345 quoted like this:
346 SELECT `table`.`one_field` FROM `table` WHERE `table`.`other_field` = 1
348 injection_guard
350 A regular expression "qr/.../" that is applied to any "-function"
351 and unquoted column name specified in a query structure. This is a
352 safety mechanism to avoid injection attacks when mishandling user
353 input e.g.:
354 my %condition_as_column_value_pairs = get_values_from_user();
356 $sqla->select( ... , \%condition_as_column_value_pairs );
357 If the expression matches an exception is thrown. Note that literal
359 SQL supplied via "\'...'" or "\['...']" is not checked in any way.
360 Defaults to checking for ";" and the "GO" keyword (TransactSQL)
362 array_datatypes
364 When this option is true, arrayrefs in INSERT or UPDATE are
365 interpreted as array datatypes and are passed directly to the DBI
366 layer. When this option is false, arrayrefs are interpreted as
367 literal SQL, just like refs to arrayrefs (but this behavior is for
368 backwards compatibility; when writing new queries, use the
369 "reference to arrayref" syntax for literal SQL).
370 special_ops
372 Takes a reference to a list of "special operators" to extend the
373 syntax understood by SQL::Abstract::Classic. See section "SPECIAL
374 OPERATORS" for details.
375 unary_ops
377 Takes a reference to a list of "unary operators" to extend the
378 syntax understood by SQL::Abstract::Classic. See section "UNARY
379 OPERATORS" for details.
380 insert($table, \@values || \%fieldvals, \%options)
382 This is the simplest function. You simply give it a table name and
383 either an arrayref of values or hashref of field/value pairs. It
384 returns an SQL INSERT statement and a list of bind values. See the
385 sections on "Inserting and Updating Arrays" and "Inserting and Updating
386 SQL" for information on how to insert with those data types.
387 The optional "\%options" hash reference may contain additional options
389 to generate the insert SQL. Currently supported options are:
390 returning
392 Takes either a scalar of raw SQL fields, or an array reference of
393 field names, and adds on an SQL "RETURNING" statement at the end.
394 This allows you to return data generated by the insert statement
395 (such as row IDs) without performing another "SELECT" statement.
396 Note, however, this is not part of the SQL standard and may not be
397 supported by all database engines.
398 update($table, \%fieldvals, \%where)
400 This takes a table, hashref of field/value pairs, and an optional
401 hashref WHERE clause. It returns an SQL UPDATE function and a list of
402 bind values. See the sections on "Inserting and Updating Arrays" and
403 "Inserting and Updating SQL" for information on how to insert with
404 those data types.
405 select($source, $fields, $where, $order)
407 This returns a SQL SELECT statement and associated list of bind values,
408 as specified by the arguments:
409 $source
411 Specification of the 'FROM' part of the statement. The argument
412 can be either a plain scalar (interpreted as a table name, will be
413 quoted), or an arrayref (interpreted as a list of table names,
414 joined by commas, quoted), or a scalarref (literal SQL, not
415 quoted).
416 $fields
418 Specification of the list of fields to retrieve from the source.
419 The argument can be either an arrayref (interpreted as a list of
420 field names, will be joined by commas and quoted), or a plain
421 scalar (literal SQL, not quoted). Please observe that this API is
422 not as flexible as that of the first argument $source, for
423 backwards compatibility reasons.
424 $where
426 Optional argument to specify the WHERE part of the query. The
427 argument is most often a hashref, but can also be an arrayref or
428 plain scalar -- see section WHERE clause for details.
429 $order
431 Optional argument to specify the ORDER BY part of the query. The
432 argument can be a scalar, a hashref or an arrayref -- see section
433 ORDER BY clause for details.
434 delete($table, \%where)
436 This takes a table name and optional hashref WHERE clause. It returns
437 an SQL DELETE statement and list of bind values.
438 where(\%where, $order)
440 This is used to generate just the WHERE clause. For example, if you
441 have an arbitrary data structure and know what the rest of your SQL is
442 going to look like, but want an easy way to produce a WHERE clause, use
443 this. It returns an SQL WHERE clause and list of bind values.
444 values(\%data)
446 This just returns the values from the hash %data, in the same order
447 that would be returned from any of the other above queries. Using this
448 allows you to markedly speed up your queries if you are affecting lots
449 of rows. See below under the "PERFORMANCE" section.
450 generate($any, 'number', $of, \@data, $struct, \%types)
452 Warning: This is an experimental method and subject to change.
453 This returns arbitrarily generated SQL. It's a really basic shortcut.
455 It will return two different things, depending on return context:
456 my($stmt, @bind) = $sql->generate('create table', \$table, \@fields);
458 my $stmt_and_val = $sql->generate('create table', \$table, \@fields);
459 These would return the following:
461 # First calling form
463 $stmt = "CREATE TABLE test (?, ?)";
464 @bind = (field1, field2);
465 # Second calling form
467 $stmt_and_val = "CREATE TABLE test (field1, field2)";
468 Depending on what you're trying to do, it's up to you to choose the
470 correct format. In this example, the second form is what you would
471 want.
472 By the same token:
474 $sql->generate('alter session', { nls_date_format => 'MM/YY' });
476 Might give you:
478 ALTER SESSION SET nls_date_format = 'MM/YY'
480 You get the idea. Strings get their case twiddled, but everything else
482 remains verbatim.
483
485 Introduction
486 This module uses a variation on the idea from DBIx::Abstract. It is
487 NOT, repeat not 100% compatible. The main logic of this module is that
488 things in arrays are OR'ed, and things in hashes are AND'ed.
489 The easiest way to explain is to show lots of examples. After each
491 %where hash shown, it is assumed you used:
492 my($stmt, @bind) = $sql->where(\%where);
494 However, note that the %where hash can be used directly in any of the
496 other functions as well, as described above.
497 Key-value pairs
499 So, let's get started. To begin, a simple hash:
500 my %where = (
502 user => 'nwiger',
503 status => 'completed'
504 );
505 Is converted to SQL "key = val" statements:
507 $stmt = "WHERE user = ? AND status = ?";
509 @bind = ('nwiger', 'completed');
510 One common thing I end up doing is having a list of values that a field
512 can be in. To do this, simply specify a list inside of an arrayref:
513 my %where = (
515 user => 'nwiger',
516 status => ['assigned', 'in-progress', 'pending'];
517 );
518 This simple code will create the following:
520 $stmt = "WHERE user = ? AND ( status = ? OR status = ? OR status = ? )";
522 @bind = ('nwiger', 'assigned', 'in-progress', 'pending');
523 A field associated to an empty arrayref will be considered a logical
525 false and will generate 0=1.
526 Tests for NULL values
528 If the value part is "undef" then this is converted to SQL <IS NULL>
529 my %where = (
531 user => 'nwiger',
532 status => undef,
533 );
534 becomes:
536 $stmt = "WHERE user = ? AND status IS NULL";
538 @bind = ('nwiger');
539 To test if a column IS NOT NULL:
541 my %where = (
543 user => 'nwiger',
544 status => { '!=', undef },
545 );
546 Specific comparison operators
548 If you want to specify a different type of operator for your
549 comparison, you can use a hashref for a given column:
550 my %where = (
552 user => 'nwiger',
553 status => { '!=', 'completed' }
554 );
555 Which would generate:
557 $stmt = "WHERE user = ? AND status != ?";
559 @bind = ('nwiger', 'completed');
560 To test against multiple values, just enclose the values in an
562 arrayref:
563 status => { '=', ['assigned', 'in-progress', 'pending'] };
565 Which would give you:
567 "WHERE status = ? OR status = ? OR status = ?"
569 The hashref can also contain multiple pairs, in which case it is
571 expanded into an "AND" of its elements:
572 my %where = (
574 user => 'nwiger',
575 status => { '!=', 'completed', -not_like => 'pending%' }
576 );
577 # Or more dynamically, like from a form
579 $where{user} = 'nwiger';
580 $where{status}{'!='} = 'completed';
581 $where{status}{'-not_like'} = 'pending%';
582 # Both generate this
584 $stmt = "WHERE user = ? AND status != ? AND status NOT LIKE ?";
585 @bind = ('nwiger', 'completed', 'pending%');
586 To get an OR instead, you can combine it with the arrayref idea:
588 my %where => (
590 user => 'nwiger',
591 priority => [ { '=', 2 }, { '>', 5 } ]
592 );
593 Which would generate:
595 $stmt = "WHERE ( priority = ? OR priority > ? ) AND user = ?";
597 @bind = ('2', '5', 'nwiger');
598 If you want to include literal SQL (with or without bind values), just
600 use a scalar reference or reference to an arrayref as the value:
601 my %where = (
603 date_entered => { '>' => \["to_date(?, 'MM/DD/YYYY')", "11/26/2008"] },
604 date_expires => { '<' => \"now()" }
605 );
606 Which would generate:
608 $stmt = "WHERE date_entered > to_date(?, 'MM/DD/YYYY') AND date_expires < now()";
610 @bind = ('11/26/2008');
611 Logic and nesting operators
613 In the example above, there is a subtle trap if you want to say
614 something like this (notice the "AND"):
615 WHERE priority != ? AND priority != ?
617 Because, in Perl you can't do this:
619 priority => { '!=' => 2, '!=' => 1 }
621 As the second "!=" key will obliterate the first. The solution is to
623 use the special "-modifier" form inside an arrayref:
624 priority => [ -and => {'!=', 2},
626 {'!=', 1} ]
627 Normally, these would be joined by "OR", but the modifier tells it to
629 use "AND" instead. (Hint: You can use this in conjunction with the
630 "logic" option to "new()" in order to change the way your queries work
631 by default.) Important: Note that the "-modifier" goes INSIDE the
632 arrayref, as an extra first element. This will NOT do what you think it
633 might:
634 priority => -and => [{'!=', 2}, {'!=', 1}] # WRONG!
636 Here is a quick list of equivalencies, since there is some overlap:
638 # Same
640 status => {'!=', 'completed', 'not like', 'pending%' }
641 status => [ -and => {'!=', 'completed'}, {'not like', 'pending%'}]
642 # Same
644 status => {'=', ['assigned', 'in-progress']}
645 status => [ -or => {'=', 'assigned'}, {'=', 'in-progress'}]
646 status => [ {'=', 'assigned'}, {'=', 'in-progress'} ]
647 Special operators: IN, BETWEEN, etc.
649 You can also use the hashref format to compare a list of fields using
650 the "IN" comparison operator, by specifying the list as an arrayref:
651 my %where = (
653 status => 'completed',
654 reportid => { -in => [567, 2335, 2] }
655 );
656 Which would generate:
658 $stmt = "WHERE status = ? AND reportid IN (?,?,?)";
660 @bind = ('completed', '567', '2335', '2');
661 The reverse operator "-not_in" generates SQL "NOT IN" and is used in
663 the same way.
664 If the argument to "-in" is an empty array, 'sqlfalse' is generated (by
666 default: "1=0"). Similarly, "-not_in => []" generates 'sqltrue' (by
667 default: "1=1").
668 In addition to the array you can supply a chunk of literal sql or
670 literal sql with bind:
671 my %where = {
673 customer => { -in => \[
674 'SELECT cust_id FROM cust WHERE balance > ?',
675 2000,
676 ],
677 status => { -in => \'SELECT status_codes FROM states' },
678 };
679 would generate:
681 $stmt = "WHERE (
683 customer IN ( SELECT cust_id FROM cust WHERE balance > ? )
684 AND status IN ( SELECT status_codes FROM states )
685 )";
686 @bind = ('2000');
687 Finally, if the argument to "-in" is not a reference, it will be
689 treated as a single-element array.
690 Another pair of operators is "-between" and "-not_between", used with
692 an arrayref of two values:
693 my %where = (
695 user => 'nwiger',
696 completion_date => {
697 -not_between => ['2002-10-01', '2003-02-06']
698 }
699 );
700 Would give you:
702 WHERE user = ? AND completion_date NOT BETWEEN ( ? AND ? )
704 Just like with "-in" all plausible combinations of literal SQL are
706 possible:
707 my %where = {
709 start0 => { -between => [ 1, 2 ] },
710 start1 => { -between => \["? AND ?", 1, 2] },
711 start2 => { -between => \"lower(x) AND upper(y)" },
712 start3 => { -between => [
713 \"lower(x)",
714 \["upper(?)", 'stuff' ],
715 ] },
716 };
717 Would give you:
719 $stmt = "WHERE (
721 ( start0 BETWEEN ? AND ? )
722 AND ( start1 BETWEEN ? AND ? )
723 AND ( start2 BETWEEN lower(x) AND upper(y) )
724 AND ( start3 BETWEEN lower(x) AND upper(?) )
725 )";
726 @bind = (1, 2, 1, 2, 'stuff');
727 These are the two builtin "special operators"; but the list can be
729 expanded: see section "SPECIAL OPERATORS" below.
730 Unary operators: bool
732 If you wish to test against boolean columns or functions within your
733 database you can use the "-bool" and "-not_bool" operators. For example
734 to test the column "is_user" being true and the column "is_enabled"
735 being false you would use:-
736 my %where = (
738 -bool => 'is_user',
739 -not_bool => 'is_enabled',
740 );
741 Would give you:
743 WHERE is_user AND NOT is_enabled
745 If a more complex combination is required, testing more conditions,
747 then you should use the and/or operators:-
748 my %where = (
750 -and => [
751 -bool => 'one',
752 -not_bool => { two=> { -rlike => 'bar' } },
753 -not_bool => { three => [ { '=', 2 }, { '>', 5 } ] },
754 ],
755 );
756 Would give you:
758 WHERE
760 one
761 AND
762 (NOT two RLIKE ?)
763 AND
764 (NOT ( three = ? OR three > ? ))
765 Nested conditions, -and/-or prefixes
767 So far, we've seen how multiple conditions are joined with a top-level
768 "AND". We can change this by putting the different conditions we want
769 in hashes and then putting those hashes in an array. For example:
770 my @where = (
772 {
773 user => 'nwiger',
774 status => { -like => ['pending%', 'dispatched'] },
775 },
776 {
777 user => 'robot',
778 status => 'unassigned',
779 }
780 );
781 This data structure would create the following:
783 $stmt = "WHERE ( user = ? AND ( status LIKE ? OR status LIKE ? ) )
785 OR ( user = ? AND status = ? ) )";
786 @bind = ('nwiger', 'pending', 'dispatched', 'robot', 'unassigned');
787 Clauses in hashrefs or arrayrefs can be prefixed with an "-and" or
789 "-or" to change the logic inside:
790 my @where = (
792 -and => [
793 user => 'nwiger',
794 [
795 -and => [ workhrs => {'>', 20}, geo => 'ASIA' ],
796 -or => { workhrs => {'<', 50}, geo => 'EURO' },
797 ],
798 ],
799 );
800 That would yield:
802 $stmt = "WHERE ( user = ?
804 AND ( ( workhrs > ? AND geo = ? )
805 OR ( workhrs < ? OR geo = ? ) ) )";
806 @bind = ('nwiger', '20', 'ASIA', '50', 'EURO');
807 Algebraic inconsistency, for historical reasons
809 "Important note": when connecting several conditions, the "-and-"|"-or"
811 operator goes "outside" of the nested structure; whereas when
812 connecting several constraints on one column, the "-and" operator goes
813 "inside" the arrayref. Here is an example combining both features:
814 my @where = (
816 -and => [a => 1, b => 2],
817 -or => [c => 3, d => 4],
818 e => [-and => {-like => 'foo%'}, {-like => '%bar'} ]
819 )
820 yielding
822 WHERE ( ( ( a = ? AND b = ? )
824 OR ( c = ? OR d = ? )
825 OR ( e LIKE ? AND e LIKE ? ) ) )
826 This difference in syntax is unfortunate but must be preserved for
828 historical reasons. So be careful: the two examples below would seem
829 algebraically equivalent, but they are not
830 { col => [ -and =>
832 { -like => 'foo%' },
833 { -like => '%bar' },
834 ] }
835 # yields: WHERE ( ( col LIKE ? AND col LIKE ? ) )
836 [ -and =>
838 { col => { -like => 'foo%' } },
839 { col => { -like => '%bar' } },
840 ]
841 # yields: WHERE ( ( col LIKE ? OR col LIKE ? ) )
842 Literal SQL and value type operators
844 The basic premise of SQL::Abstract::Classic is that in WHERE
845 specifications the "left side" is a column name and the "right side" is
846 a value (normally rendered as a placeholder). This holds true for both
847 hashrefs and arrayref pairs as you see in the "WHERE CLAUSES" examples
848 above. Sometimes it is necessary to alter this behavior. There are
849 several ways of doing so.
850 -ident
852 This is a virtual operator that signals the string to its right side is
854 an identifier (a column name) and not a value. For example to compare
855 two columns you would write:
856 my %where = (
858 priority => { '<', 2 },
859 requestor => { -ident => 'submitter' },
860 );
861 which creates:
863 $stmt = "WHERE priority < ? AND requestor = submitter";
865 @bind = ('2');
866 If you are maintaining legacy code you may see a different construct as
868 described in "Deprecated usage of Literal SQL", please use "-ident" in
869 new code.
870 -value
872 This is a virtual operator that signals that the construct to its right
874 side is a value to be passed to DBI. This is for example necessary when
875 you want to write a where clause against an array (for RDBMS that
876 support such datatypes). For example:
877 my %where = (
879 array => { -value => [1, 2, 3] }
880 );
881 will result in:
883 $stmt = 'WHERE array = ?';
885 @bind = ([1, 2, 3]);
886 Note that if you were to simply say:
888 my %where = (
890 array => [1, 2, 3]
891 );
892 the result would probably not be what you wanted:
894 $stmt = 'WHERE array = ? OR array = ? OR array = ?';
896 @bind = (1, 2, 3);
897 Literal SQL
899 Finally, sometimes only literal SQL will do. To include a random
901 snippet of SQL verbatim, you specify it as a scalar reference. Consider
902 this only as a last resort. Usually there is a better way. For example:
903 my %where = (
905 priority => { '<', 2 },
906 requestor => { -in => \'(SELECT name FROM hitmen)' },
907 );
908 Would create:
910 $stmt = "WHERE priority < ? AND requestor IN (SELECT name FROM hitmen)"
912 @bind = (2);
913 Note that in this example, you only get one bind parameter back, since
915 the verbatim SQL is passed as part of the statement.
916 CAVEAT
918 Never use untrusted input as a literal SQL argument - this is a massive
920 security risk (there is no way to check literal snippets for SQL
921 injections and other nastyness). If you need to deal with untrusted input
922 use literal SQL with placeholders as described next.
923 Literal SQL with placeholders and bind values (subqueries)
925 If the literal SQL to be inserted has placeholders and bind values, use
927 a reference to an arrayref (yes this is a double reference -- not so
928 common, but perfectly legal Perl). For example, to find a date in
929 Postgres you can use something like this:
930 my %where = (
932 date_column => \[ "= date '2008-09-30' - ?::integer", 10 ]
933 )
934 This would create:
936 $stmt = "WHERE ( date_column = date '2008-09-30' - ?::integer )"
938 @bind = ('10');
939 Note that you must pass the bind values in the same format as they are
941 returned by where. This means that if you set "bindtype" to "columns",
942 you must provide the bind values in the "[ column_meta => value ]"
943 format, where "column_meta" is an opaque scalar value; most commonly
944 the column name, but you can use any scalar value (including references
945 and blessed references), SQL::Abstract::Classic will simply pass it
946 through intact. So if "bindtype" is set to "columns" the above example
947 will look like:
948 my %where = (
950 date_column => \[ "= date '2008-09-30' - ?::integer", [ {} => 10 ] ]
951 )
952 Literal SQL is especially useful for nesting parenthesized clauses in
954 the main SQL query. Here is a first example:
955 my ($sub_stmt, @sub_bind) = ("SELECT c1 FROM t1 WHERE c2 < ? AND c3 LIKE ?",
957 100, "foo%");
958 my %where = (
959 foo => 1234,
960 bar => \["IN ($sub_stmt)" => @sub_bind],
961 );
962 This yields:
964 $stmt = "WHERE (foo = ? AND bar IN (SELECT c1 FROM t1
966 WHERE c2 < ? AND c3 LIKE ?))";
967 @bind = (1234, 100, "foo%");
968 Other subquery operators, like for example "> ALL" or "NOT IN", are
970 expressed in the same way. Of course the $sub_stmt and its associated
971 bind values can be generated through a former call to "select()" :
972 my ($sub_stmt, @sub_bind)
974 = $sql->select("t1", "c1", {c2 => {"<" => 100},
975 c3 => {-like => "foo%"}});
976 my %where = (
977 foo => 1234,
978 bar => \["> ALL ($sub_stmt)" => @sub_bind],
979 );
980 In the examples above, the subquery was used as an operator on a
982 column; but the same principle also applies for a clause within the
983 main %where hash, like an EXISTS subquery:
984 my ($sub_stmt, @sub_bind)
986 = $sql->select("t1", "*", {c1 => 1, c2 => \"> t0.c0"});
987 my %where = ( -and => [
988 foo => 1234,
989 \["EXISTS ($sub_stmt)" => @sub_bind],
990 ]);
991 which yields
993 $stmt = "WHERE (foo = ? AND EXISTS (SELECT * FROM t1
995 WHERE c1 = ? AND c2 > t0.c0))";
996 @bind = (1234, 1);
997 Observe that the condition on "c2" in the subquery refers to column
999 "t0.c0" of the main query: this is not a bind value, so we have to
1000 express it through a scalar ref. Writing "c2 => {">" => "t0.c0"}"
1001 would have generated "c2 > ?" with bind value "t0.c0" ... not exactly
1002 what we wanted here.
1003 Finally, here is an example where a subquery is used for expressing
1005 unary negation:
1006 my ($sub_stmt, @sub_bind)
1008 = $sql->where({age => [{"<" => 10}, {">" => 20}]});
1009 $sub_stmt =~ s/^ where //i; # don't want "WHERE" in the subclause
1010 my %where = (
1011 lname => {like => '%son%'},
1012 \["NOT ($sub_stmt)" => @sub_bind],
1013 );
1014 This yields
1016 $stmt = "lname LIKE ? AND NOT ( age < ? OR age > ? )"
1018 @bind = ('%son%', 10, 20)
1019 Deprecated usage of Literal SQL
1021 Below are some examples of archaic use of literal SQL. It is shown only
1023 as reference for those who deal with legacy code. Each example has a
1024 much better, cleaner and safer alternative that users should opt for in
1025 new code.
1026 •
1028 my %where = ( requestor => \'IS NOT NULL' )
1031 $stmt = "WHERE requestor IS NOT NULL"
1033 This used to be the way of generating NULL comparisons, before the
1035 handling of "undef" got formalized. For new code please use the
1036 superior syntax as described in "Tests for NULL values".
1037 •
1039 my %where = ( requestor => \'= submitter' )
1042 $stmt = "WHERE requestor = submitter"
1044 This used to be the only way to compare columns. Use the superior
1046 "-ident" method for all new code. For example an identifier
1047 declared in such a way will be properly quoted if "quote_char" is
1048 properly set, while the legacy form will remain as supplied.
1049 •
1051 my %where = ( is_ready => \"", completed => { '>', '2012-12-21' } )
1054 $stmt = "WHERE completed > ? AND is_ready"
1056 @bind = ('2012-12-21')
1057 Using an empty string literal used to be the only way to express a
1059 boolean. For all new code please use the much more readable -bool
1060 operator.
1061 Conclusion
1063 These pages could go on for a while, since the nesting of the data
1064 structures this module can handle are pretty much unlimited (the module
1065 implements the "WHERE" expansion as a recursive function internally).
1066 Your best bet is to "play around" with the module a little to see how
1067 the data structures behave, and choose the best format for your data
1068 based on that.
1069 And of course, all the values above will probably be replaced with
1071 variables gotten from forms or the command line. After all, if you knew
1072 everything ahead of time, you wouldn't have to worry about dynamically-
1073 generating SQL and could just hardwire it into your script.
1074
1076 Some functions take an order by clause. This can either be a scalar
1077 (just a column name), a hashref of "{ -desc => 'col' }" or "{ -asc =>
1078 'col' }", a scalarref, an arrayref-ref, or an arrayref of any of the
1079 previous forms. Examples:
1080 Given | Will Generate
1082 ---------------------------------------------------------------
1083 |
1084 'colA' | ORDER BY colA
1085 |
1086 [qw/colA colB/] | ORDER BY colA, colB
1087 |
1088 {-asc => 'colA'} | ORDER BY colA ASC
1089 |
1090 {-desc => 'colB'} | ORDER BY colB DESC
1091 |
1092 ['colA', {-asc => 'colB'}] | ORDER BY colA, colB ASC
1093 |
1094 { -asc => [qw/colA colB/] } | ORDER BY colA ASC, colB ASC
1095 |
1096 \'colA DESC' | ORDER BY colA DESC
1097 |
1098 \[ 'FUNC(colA, ?)', $x ] | ORDER BY FUNC(colA, ?)
1099 | /* ...with $x bound to ? */
1100 |
1101 [ | ORDER BY
1102 { -asc => 'colA' }, | colA ASC,
1103 { -desc => [qw/colB/] }, | colB DESC,
1104 { -asc => [qw/colC colD/] },| colC ASC, colD ASC,
1105 \'colE DESC', | colE DESC,
1106 \[ 'FUNC(colF, ?)', $x ], | FUNC(colF, ?)
1107 ] | /* ...with $x bound to ? */
1108 ===============================================================
1109
1111 my $sqlmaker = SQL::Abstract::Classic->new(special_ops => [
1112 {
1113 regex => qr/.../,
1114 handler => sub {
1115 my ($self, $field, $op, $arg) = @_;
1116 ...
1117 },
1118 },
1119 {
1120 regex => qr/.../,
1121 handler => 'method_name',
1122 },
1123 ]);
1124 A "special operator" is a SQL syntactic clause that can be applied to a
1126 field, instead of a usual binary operator. For example:
1127 WHERE field IN (?, ?, ?)
1129 WHERE field BETWEEN ? AND ?
1130 WHERE MATCH(field) AGAINST (?, ?)
1131 Special operators IN and BETWEEN are fairly standard and therefore are
1133 builtin within "SQL::Abstract::Classic" (as the overridable methods
1134 "_where_field_IN" and "_where_field_BETWEEN"). For other operators,
1135 like the MATCH .. AGAINST example above which is specific to MySQL, you
1136 can write your own operator handlers - supply a "special_ops" argument
1137 to the "new" method. That argument takes an arrayref of operator
1138 definitions; each operator definition is a hashref with two entries:
1139 regex
1141 the regular expression to match the operator
1142 handler
1144 Either a coderef or a plain scalar method name. In both cases the
1145 expected return is "($sql, @bind)".
1146 When supplied with a method name, it is simply called on the
1148 SQL::Abstract::Classic object as:
1149 $self->$method_name ($field, $op, $arg)
1151 Where:
1153 $field is the LHS of the operator
1155 $op is the part that matched the handler regex
1156 $arg is the RHS
1157 When supplied with a coderef, it is called as:
1159 $coderef->($self, $field, $op, $arg)
1161 For example, here is an implementation of the MATCH .. AGAINST syntax
1163 for MySQL
1164 my $sqlmaker = SQL::Abstract::Classic->new(special_ops => [
1166 # special op for MySql MATCH (field) AGAINST(word1, word2, ...)
1168 {regex => qr/^match$/i,
1169 handler => sub {
1170 my ($self, $field, $op, $arg) = @_;
1171 $arg = [$arg] if not ref $arg;
1172 my $label = $self->_quote($field);
1173 my ($placeholder) = $self->_convert('?');
1174 my $placeholders = join ", ", (($placeholder) x @$arg);
1175 my $sql = $self->_sqlcase('match') . " ($label) "
1176 . $self->_sqlcase('against') . " ($placeholders) ";
1177 my @bind = $self->_bindtype($field, @$arg);
1178 return ($sql, @bind);
1179 }
1180 },
1181 ]);
1183
1185 my $sqlmaker = SQL::Abstract::Classic->new(unary_ops => [
1186 {
1187 regex => qr/.../,
1188 handler => sub {
1189 my ($self, $op, $arg) = @_;
1190 ...
1191 },
1192 },
1193 {
1194 regex => qr/.../,
1195 handler => 'method_name',
1196 },
1197 ]);
1198 A "unary operator" is a SQL syntactic clause that can be applied to a
1200 field - the operator goes before the field
1201 You can write your own operator handlers - supply a "unary_ops"
1203 argument to the "new" method. That argument takes an arrayref of
1204 operator definitions; each operator definition is a hashref with two
1205 entries:
1206 regex
1208 the regular expression to match the operator
1209 handler
1211 Either a coderef or a plain scalar method name. In both cases the
1212 expected return is $sql.
1213 When supplied with a method name, it is simply called on the
1215 SQL::Abstract::Classic object as:
1216 $self->$method_name ($op, $arg)
1218 Where:
1220 $op is the part that matched the handler regex
1222 $arg is the RHS or argument of the operator
1223 When supplied with a coderef, it is called as:
1225 $coderef->($self, $op, $arg)
1227
1229 Thanks to some benchmarking by Mark Stosberg, it turns out that this
1230 module is many orders of magnitude faster than using "DBIx::Abstract".
1231 I must admit this wasn't an intentional design issue, but it's a
1232 byproduct of the fact that you get to control your "DBI" handles
1233 yourself.
1234 To maximize performance, use a code snippet like the following:
1236 # prepare a statement handle using the first row
1238 # and then reuse it for the rest of the rows
1239 my($sth, $stmt);
1240 for my $href (@array_of_hashrefs) {
1241 $stmt ||= $sql->insert('table', $href);
1242 $sth ||= $dbh->prepare($stmt);
1243 $sth->execute($sql->values($href));
1244 }
1245 The reason this works is because the keys in your $href are sorted
1247 internally by SQL::Abstract::Classic. Thus, as long as your data
1248 retains the same structure, you only have to generate the SQL the first
1249 time around. On subsequent queries, simply use the "values" function
1250 provided by this module to return your values in the correct order.
1251 However this depends on the values having the same type - if, for
1253 example, the values of a where clause may either have values (resulting
1254 in sql of the form "column = ?" with a single bind value), or
1255 alternatively the values might be "undef" (resulting in sql of the form
1256 "column IS NULL" with no bind value) then the caching technique
1257 suggested will not work.
1258
1260 If you use my "CGI::FormBuilder" module at all, you'll hopefully really
1261 like this part (I do, at least). Building up a complex query can be as
1262 simple as the following:
1263 #!/usr/bin/perl
1265 use warnings;
1267 use strict;
1268 use CGI::FormBuilder;
1270 use SQL::Abstract::Classic;
1271 my $form = CGI::FormBuilder->new(...);
1273 my $sql = SQL::Abstract::Classic->new;
1274 if ($form->submitted) {
1276 my $field = $form->field;
1277 my $id = delete $field->{id};
1278 my($stmt, @bind) = $sql->update('table', $field, {id => $id});
1279 }
1280 Of course, you would still have to connect using "DBI" to run the
1282 query, but the point is that if you make your form look like your
1283 table, the actual query script can be extremely simplistic.
1284 If you're REALLY lazy (I am), check out "HTML::QuickTable" for a fast
1286 interface to returning and formatting data. I frequently use these
1287 three modules together to write complex database query apps in under 50
1288 lines.
1289
1291 There are a number of individuals that have really helped out with this
1292 module. Unfortunately, most of them submitted bugs via CPAN so I have
1293 no idea who they are! But the people I do know are:
1294 Ash Berlin (order_by hash term support)
1296 Matt Trout (DBIx::Class support)
1297 Mark Stosberg (benchmarking)
1298 Chas Owens (initial "IN" operator support)
1299 Philip Collins (per-field SQL functions)
1300 Eric Kolve (hashref "AND" support)
1301 Mike Fragassi (enhancements to "BETWEEN" and "LIKE")
1302 Dan Kubb (support for "quote_char" and "name_sep")
1303 Guillermo Roditi (patch to cleanup "IN" and "BETWEEN", fix and tests for _order_by)
1304 Laurent Dami (internal refactoring, extensible list of special operators, literal SQL)
1305 Norbert Buchmuller (support for literal SQL in hashpair, misc. fixes & tests)
1306 Peter Rabbitson (rewrite of SQLA::Test, misc. fixes & tests)
1307 Oliver Charles (support for "RETURNING" after "INSERT")
1308 Thanks!
1310
1312 SQL::Abstract, DBIx::Class, DBIx::Abstract, CGI::FormBuilder,
1313 HTML::QuickTable.
1314
1316 Copyright (c) 2001-2007 Nathan Wiger <nwiger@cpan.org>. All Rights
1317 Reserved.
1318
1320 This module is free software; you may copy this under the same terms as
1321 perl itself (either the GNU General Public License or the Artistic
1322 License)
1323perl v5.36.0 2022-07-22 SQL::Abstract::Classic(3)