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 genera‐
37 tion 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 In addition, you can apply SQL functions to elements of your %data by
76 specifying an arrayref for the given hash value. For example, if you
77 need to execute the Oracle "to_date" function on a value, you can say
78 something like this:
79 my %data = (
81 name => 'Bill',
82 date_entered => ["to_date(?,'MM/DD/YYYY')", "03/02/2003"],
83 );
84 The first value in the array is the actual SQL. Any other values are
86 optional and would be included in the bind values array. This gives
87 you:
88 my($stmt, @bind) = $sql->insert('people', \%data);
90 $stmt = "INSERT INTO people (name, date_entered)
92 VALUES (?, to_date(?,'MM/DD/YYYY'))";
93 @bind = ('Bill', '03/02/2003');
94 An UPDATE is just as easy, all you change is the name of the function:
96 my($stmt, @bind) = $sql->update('people', \%data);
98 Notice that your %data isn't touched; the module will generate the
100 appropriately quirky SQL for you automatically. Usually you'll want to
101 specify a WHERE clause for your UPDATE, though, which is where handling
102 %where hashes comes in handy...
103 This module can generate pretty complicated WHERE statements easily.
105 For example, simple "key=value" pairs are taken to mean equality, and
106 if you want to see if a field is within a set of values, you can use an
107 arrayref. Let's say we wanted to SELECT some data based on this crite‐
108 ria:
109 my %where = (
111 requestor => 'inna',
112 worker => ['nwiger', 'rcwe', 'sfz'],
113 status => { '!=', 'completed' }
114 );
115 my($stmt, @bind) = $sql->select('tickets', '*', \%where);
117 The above would give you something like this:
119 $stmt = "SELECT * FROM tickets WHERE
121 ( requestor = ? ) AND ( status != ? )
122 AND ( worker = ? OR worker = ? OR worker = ? )";
123 @bind = ('inna', 'completed', 'nwiger', 'rcwe', 'sfz');
124 Which you could then use in DBI code like so:
126 my $sth = $dbh->prepare($stmt);
128 $sth->execute(@bind);
129 Easy, eh?
131
133 The functions are simple. There's one for each major SQL operation, and
134 a constructor you use first. The arguments are specified in a similar
135 order to each function (table, then fields, then a where clause) to try
136 and simplify things.
137 new(option => 'value')
139 The "new()" function takes a list of options and values, and returns a
141 new SQL::Abstract object which can then be used to generate SQL through
142 the methods below. The options accepted are:
143 case
145 If set to 'lower', then SQL will be generated in all lowercase. By
146 default SQL is generated in "textbook" case meaning something like:
147 SELECT a_field FROM a_table WHERE some_field LIKE '%someval%'
149 cmp This determines what the default comparison operator is. By default
151 it is "=", meaning that a hash like this:
152 %where = (name => 'nwiger', email => 'nate@wiger.org');
154 Will generate SQL like this:
156 WHERE name = 'nwiger' AND email = 'nate@wiger.org'
158 However, you may want loose comparisons by default, so if you set
160 "cmp" to "like" you would get SQL such as:
161 WHERE name like 'nwiger' AND email like 'nate@wiger.org'
163 You can also override the comparsion on an individual basis - see
165 the huge section on "WHERE CLAUSES" at the bottom.
166 logic
168 This determines the default logical operator for multiple WHERE
169 statements in arrays. By default it is "or", meaning that a WHERE
170 array of the form:
171 @where = (
173 event_date => {'>=', '2/13/99'},
174 event_date => {'<=', '4/24/03'},
175 );
176 Will generate SQL like this:
178 WHERE event_date >= '2/13/99' OR event_date <= '4/24/03'
180 This is probably not what you want given this query, though (look
182 at the dates). To change the "OR" to an "AND", simply specify:
183 my $sql = SQL::Abstract->new(logic => 'and');
185 Which will change the above "WHERE" to:
187 WHERE event_date >= '2/13/99' AND event_date <= '4/24/03'
189 convert
191 This will automatically convert comparisons using the specified SQL
192 function for both column and value. This is mostly used with an
193 argument of "upper" or "lower", so that the SQL will have the
194 effect of case-insensitive "searches". For example, this:
195 $sql = SQL::Abstract->new(convert => 'upper');
197 %where = (keywords => 'MaKe iT CAse inSeNSItive');
198 Will turn out the following SQL:
200 WHERE upper(keywords) like upper('MaKe iT CAse inSeNSItive')
202 The conversion can be "upper()", "lower()", or any other SQL func‐
204 tion that can be applied symmetrically to fields (actually
205 SQL::Abstract does not validate this option; it will just pass
206 through what you specify verbatim).
207 bindtype
209 This is a kludge because many databases suck. For example, you
210 can't just bind values using DBI's "execute()" for Oracle "CLOB" or
211 "BLOB" fields. Instead, you have to use "bind_param()":
212 $sth->bind_param(1, 'reg data');
214 $sth->bind_param(2, $lots, {ora_type => ORA_CLOB});
215 The problem is, SQL::Abstract will normally just return a @bind
217 array, which loses track of which field each slot refers to. Fear
218 not.
219 If you specify "bindtype" in new, you can determine how @bind is
221 returned. Currently, you can specify either "normal" (default) or
222 "columns". If you specify "columns", you will get an array that
223 looks like this:
224 my $sql = SQL::Abstract->new(bindtype => 'columns');
226 my($stmt, @bind) = $sql->insert(...);
227 @bind = (
229 [ 'column1', 'value1' ],
230 [ 'column2', 'value2' ],
231 [ 'column3', 'value3' ],
232 );
233 You can then iterate through this manually, using DBI's
235 "bind_param()".
236 $sth->prepare($stmt);
238 my $i = 1;
239 for (@bind) {
240 my($col, $data) = @$_;
241 if ($col eq 'details' ⎪⎪ $col eq 'comments') {
242 $sth->bind_param($i, $data, {ora_type => ORA_CLOB});
243 } elsif ($col eq 'image') {
244 $sth->bind_param($i, $data, {ora_type => ORA_BLOB});
245 } else {
246 $sth->bind_param($i, $data);
247 }
248 $i++;
249 }
250 $sth->execute; # execute without @bind now
251 Now, why would you still use SQL::Abstract if you have to do this
253 crap? Basically, the advantage is still that you don't have to
254 care which fields are or are not included. You could wrap that
255 above "for" loop in a simple sub called "bind_fields()" or some‐
256 thing and reuse it repeatedly. You still get a layer of abstraction
257 over manual SQL specification.
258 quote_char
260 This is the character that a table or column name will be quoted
261 with. By default this is an empty string, but you could set it to
262 the character "`", to generate SQL like this:
263 SELECT `a_field` FROM `a_table` WHERE `some_field` LIKE '%someval%'
265 This is useful if you have tables or columns that are reserved
267 words in your database's SQL dialect.
268 name_sep
270 This is the character that separates a table and column name. It
271 is necessary to specify this when the "quote_char" option is
272 selected, so that tables and column names can be individually
273 quoted like this:
274 SELECT `table`.`one_field` FROM `table` WHERE `table`.`other_field` = 1
276 insert($table, \@values ⎪⎪ \%fieldvals)
278 This is the simplest function. You simply give it a table name and
280 either an arrayref of values or hashref of field/value pairs. It
281 returns an SQL INSERT statement and a list of bind values.
282 update($table, \%fieldvals, \%where)
284 This takes a table, hashref of field/value pairs, and an optional
286 hashref WHERE clause. It returns an SQL UPDATE function and a list of
287 bind values.
288 select($table, \@fields, \%where, \@order)
290 This takes a table, arrayref of fields (or '*'), optional hashref WHERE
292 clause, and optional arrayref order by, and returns the corresponding
293 SQL SELECT statement and list of bind values.
294 delete($table, \%where)
296 This takes a table name and optional hashref WHERE clause. It returns
298 an SQL DELETE statement and list of bind values.
299 where(\%where, \@order)
301 This is used to generate just the WHERE clause. For example, if you
303 have an arbitrary data structure and know what the rest of your SQL is
304 going to look like, but want an easy way to produce a WHERE clause, use
305 this. It returns an SQL WHERE clause and list of bind values.
306 values(\%data)
308 This just returns the values from the hash %data, in the same order
310 that would be returned from any of the other above queries. Using this
311 allows you to markedly speed up your queries if you are affecting lots
312 of rows. See below under the "PERFORMANCE" section.
313 generate($any, 'number', $of, \@data, $struct, \%types)
315 Warning: This is an experimental method and subject to change.
317 This returns arbitrarily generated SQL. It's a really basic shortcut.
319 It will return two different things, depending on return context:
320 my($stmt, @bind) = $sql->generate('create table', \$table, \@fields);
322 my $stmt_and_val = $sql->generate('create table', \$table, \@fields);
323 These would return the following:
325 # First calling form
327 $stmt = "CREATE TABLE test (?, ?)";
328 @bind = (field1, field2);
329 # Second calling form
331 $stmt_and_val = "CREATE TABLE test (field1, field2)";
332 Depending on what you're trying to do, it's up to you to choose the
334 correct format. In this example, the second form is what you would
335 want.
336 By the same token:
338 $sql->generate('alter session', { nls_date_format => 'MM/YY' });
340 Might give you:
342 ALTER SESSION SET nls_date_format = 'MM/YY'
344 You get the idea. Strings get their case twiddled, but everything else
346 remains verbatim.
347
349 This module uses a variation on the idea from DBIx::Abstract. It is
350 NOT, repeat not 100% compatible. The main logic of this module is that
351 things in arrays are OR'ed, and things in hashes are AND'ed.
352 The easiest way to explain is to show lots of examples. After each
354 %where hash shown, it is assumed you used:
355 my($stmt, @bind) = $sql->where(\%where);
357 However, note that the %where hash can be used directly in any of the
359 other functions as well, as described above.
360 So, let's get started. To begin, a simple hash:
362 my %where = (
364 user => 'nwiger',
365 status => 'completed'
366 );
367 Is converted to SQL "key = val" statements:
369 $stmt = "WHERE user = ? AND status = ?";
371 @bind = ('nwiger', 'completed');
372 One common thing I end up doing is having a list of values that a field
374 can be in. To do this, simply specify a list inside of an arrayref:
375 my %where = (
377 user => 'nwiger',
378 status => ['assigned', 'in-progress', 'pending'];
379 );
380 This simple code will create the following:
382 $stmt = "WHERE user = ? AND ( status = ? OR status = ? OR status = ? )";
384 @bind = ('nwiger', 'assigned', 'in-progress', 'pending');
385 If you want to specify a different type of operator for your compari‐
387 son, you can use a hashref for a given column:
388 my %where = (
390 user => 'nwiger',
391 status => { '!=', 'completed' }
392 );
393 Which would generate:
395 $stmt = "WHERE user = ? AND status != ?";
397 @bind = ('nwiger', 'completed');
398 To test against multiple values, just enclose the values in an
400 arrayref:
401 status => { '!=', ['assigned', 'in-progress', 'pending'] };
403 Which would give you:
405 "WHERE status != ? OR status != ? OR status != ?"
407 But, this is probably not what you want in this case (look at it). So
409 the hashref can also contain multiple pairs, in which case it is
410 expanded into an "AND" of its elements:
411 my %where = (
413 user => 'nwiger',
414 status => { '!=', 'completed', -not_like => 'pending%' }
415 );
416 # Or more dynamically, like from a form
418 $where{user} = 'nwiger';
419 $where{status}{'!='} = 'completed';
420 $where{status}{'-not_like'} = 'pending%';
421 # Both generate this
423 $stmt = "WHERE user = ? AND status != ? AND status NOT LIKE ?";
424 @bind = ('nwiger', 'completed', 'pending%');
425 To get an OR instead, you can combine it with the arrayref idea:
427 my %where => (
429 user => 'nwiger',
430 priority => [ {'=', 2}, {'!=', 1} ]
431 );
432 Which would generate:
434 $stmt = "WHERE user = ? AND priority = ? OR priority != ?";
436 @bind = ('nwiger', '2', '1');
437 However, there is a subtle trap if you want to say something like this
439 (notice the "AND"):
440 WHERE priority != ? AND priority != ?
442 Because, in Perl you can't do this:
444 priority => { '!=', 2, '!=', 1 }
446 As the second "!=" key will obliterate the first. The solution is to
448 use the special "-modifier" form inside an arrayref:
449 priority => [ -and => {'!=', 2}, {'!=', 1} ]
451 Normally, these would be joined by "OR", but the modifier tells it to
453 use "AND" instead. (Hint: You can use this in conjunction with the
454 "logic" option to "new()" in order to change the way your queries work
455 by default.) Important: Note that the "-modifier" goes INSIDE the
456 arrayref, as an extra first element. This will NOT do what you think it
457 might:
458 priority => -and => [{'!=', 2}, {'!=', 1}] # WRONG!
460 Here is a quick list of equivalencies, since there is some overlap:
462 # Same
464 status => {'!=', 'completed', 'not like', 'pending%' }
465 status => [ -and => {'!=', 'completed'}, {'not like', 'pending%'}]
466 # Same
468 status => {'=', ['assigned', 'in-progress']}
469 status => [ -or => {'=', 'assigned'}, {'=', 'in-progress'}]
470 status => [ {'=', 'assigned'}, {'=', 'in-progress'} ]
471 In addition to "-and" and "-or", there is also a special "-nest" opera‐
473 tor which adds an additional set of parens, to create a subquery. For
474 example, to get something like this:
475 $stmt = WHERE user = ? AND ( workhrs > ? OR geo = ? )
477 @bind = ('nwiger', '20', 'ASIA');
478 You would do:
480 my %where = (
482 user => 'nwiger',
483 -nest => [ workhrs => {'>', 20}, geo => 'ASIA' ],
484 );
485 You can also use the hashref format to compare a list of fields using
487 the "IN" comparison operator, by specifying the list as an arrayref:
488 my %where = (
490 status => 'completed',
491 reportid => { -in => [567, 2335, 2] }
492 );
493 Which would generate:
495 $stmt = "WHERE status = ? AND reportid IN (?,?,?)";
497 @bind = ('completed', '567', '2335', '2');
498 You can use this same format to use other grouping functions, such as
500 "BETWEEN", "SOME", and so forth. For example:
501 my %where = (
503 user => 'nwiger',
504 completion_date => {
505 -not_between => ['2002-10-01', '2003-02-06']
506 }
507 );
508 Would give you:
510 WHERE user = ? AND completion_date NOT BETWEEN ( ? AND ? )
512 So far, we've seen how multiple conditions are joined with a top-level
514 "AND". We can change this by putting the different conditions we want
515 in hashes and then putting those hashes in an array. For example:
516 my @where = (
518 {
519 user => 'nwiger',
520 status => { -like => ['pending%', 'dispatched'] },
521 },
522 {
523 user => 'robot',
524 status => 'unassigned',
525 }
526 );
527 This data structure would create the following:
529 $stmt = "WHERE ( user = ? AND ( status LIKE ? OR status LIKE ? ) )
531 OR ( user = ? AND status = ? ) )";
532 @bind = ('nwiger', 'pending', 'dispatched', 'robot', 'unassigned');
533 This can be combined with the "-nest" operator to properly group SQL
535 statements:
536 my @where = (
538 -and => [
539 user => 'nwiger',
540 -nest => [
541 -and => [workhrs => {'>', 20}, geo => 'ASIA' ],
542 -and => [workhrs => {'<', 50}, geo => 'EURO' ]
543 ],
544 ],
545 );
546 That would yield:
548 WHERE ( user = ? AND
550 ( ( workhrs > ? AND geo = ? )
551 OR ( workhrs < ? AND geo = ? ) ) )
552 Finally, sometimes only literal SQL will do. If you want to include
554 literal SQL verbatim, you can specify it as a scalar reference, namely:
555 my $inn = 'is Not Null';
557 my %where = (
558 priority => { '<', 2 },
559 requestor => \$inn
560 );
561 This would create:
563 $stmt = "WHERE priority < ? AND requestor is Not Null";
565 @bind = ('2');
566 Note that in this example, you only get one bind parameter back, since
568 the verbatim SQL is passed as part of the statement.
569 Of course, just to prove a point, the above can also be accomplished
571 with this:
572 my %where = (
574 priority => { '<', 2 },
575 requestor => { '!=', undef },
576 );
577 TMTOWTDI.
579 These pages could go on for a while, since the nesting of the data
581 structures this module can handle are pretty much unlimited (the module
582 implements the "WHERE" expansion as a recursive function internally).
583 Your best bet is to "play around" with the module a little to see how
584 the data structures behave, and choose the best format for your data
585 based on that.
586 And of course, all the values above will probably be replaced with
588 variables gotten from forms or the command line. After all, if you knew
589 everything ahead of time, you wouldn't have to worry about dynamically-
590 generating SQL and could just hardwire it into your script.
591
593 Thanks to some benchmarking by Mark Stosberg, it turns out that this
594 module is many orders of magnitude faster than using "DBIx::Abstract".
595 I must admit this wasn't an intentional design issue, but it's a
596 byproduct of the fact that you get to control your "DBI" handles your‐
597 self.
598 To maximize performance, use a code snippet like the following:
600 # prepare a statement handle using the first row
602 # and then reuse it for the rest of the rows
603 my($sth, $stmt);
604 for my $href (@array_of_hashrefs) {
605 $stmt ⎪⎪= $sql->insert('table', $href);
606 $sth ⎪⎪= $dbh->prepare($stmt);
607 $sth->execute($sql->values($href));
608 }
609 The reason this works is because the keys in your $href are sorted
611 internally by SQL::Abstract. Thus, as long as your data retains the
612 same structure, you only have to generate the SQL the first time
613 around. On subsequent queries, simply use the "values" function pro‐
614 vided by this module to return your values in the correct order.
615
617 If you use my "CGI::FormBuilder" module at all, you'll hopefully really
618 like this part (I do, at least). Building up a complex query can be as
619 simple as the following:
620 #!/usr/bin/perl
622 use CGI::FormBuilder;
624 use SQL::Abstract;
625 my $form = CGI::FormBuilder->new(...);
627 my $sql = SQL::Abstract->new;
628 if ($form->submitted) {
630 my $field = $form->field;
631 my $id = delete $field->{id};
632 my($stmt, @bind) = $sql->update('table', $field, {id => $id});
633 }
634 Of course, you would still have to connect using "DBI" to run the
636 query, but the point is that if you make your form look like your ta‐
637 ble, the actual query script can be extremely simplistic.
638 If you're REALLY lazy (I am), check out "HTML::QuickTable" for a fast
640 interface to returning and formatting data. I frequently use these
641 three modules together to write complex database query apps in under 50
642 lines.
643
645 There is not (yet) any explicit support for SQL compound logic state‐
646 ments like "AND NOT". Instead, just do the de Morgan's law transforma‐
647 tions yourself. For example, this:
648 "lname LIKE '%son%' AND NOT ( age < 10 OR age > 20 )"
650 Becomes:
652 "lname LIKE '%son%' AND ( age >= 10 AND age <= 20 )"
654 With the corresponding %where hash:
656 %where = (
658 lname => {like => '%son%'},
659 age => [-and => {'>=', 10}, {'<=', 20}],
660 );
661 Again, remember that the "-and" goes inside the arrayref.
663
665 There are a number of individuals that have really helped out with this
666 module. Unfortunately, most of them submitted bugs via CPAN so I have
667 no idea who they are! But the people I do know are:
668 Mark Stosberg (benchmarking)
670 Chas Owens (initial "IN" operator support)
671 Philip Collins (per-field SQL functions)
672 Eric Kolve (hashref "AND" support)
673 Mike Fragassi (enhancements to "BETWEEN" and "LIKE")
674 Dan Kubb (support for "quote_char" and "name_sep")
675 Matt Trout (DBIx::Class support)
676 Thanks!
678
680 If found, please DO NOT submit anything via "rt.cpan.org" - that just
681 causes me a ton of work. Email me a patch (or script demonstrating the
682 problem) to the below address, and include the VERSION you're using.
683
685 DBIx::Abstract, DBI, CGI::FormBuilder, HTML::QuickTable
686
688 Copyright (c) 2001-2006 Nathan Wiger <nwiger@cpan.org>. All Rights
689 Reserved.
690 For support, your best bet is to try the "DBIx::Class" users mailing
692 list. While not an official support venue, "DBIx::Class" makes heavy
693 use of "SQL::Abstract", and as such list members there are very famil‐
694 iar with how to create queries.
695 This module is free software; you may copy this under the terms of the
697 GNU General Public License, or the Artistic License, copies of which
698 should have accompanied your Perl kit.
699perl v5.8.8 2006-11-30 SQL::Abstract(3)