1 package SQL::Abstract; # see doc at end of file
3 use SQL::Abstract::_TempExtlib;
8 use Module::Runtime qw(use_module);
12 # DO NOT INCREMENT TO 2.0 WITHOUT COORDINATING WITH mst OR ribasushi
13 our $VERSION = '1.99_01';
14 # DO NOT INCREMENT TO 2.0 WITHOUT COORDINATING WITH mst OR ribasushi
16 # This would confuse some packagers
17 $VERSION = eval $VERSION if $VERSION =~ /_/; # numify for warning-free dev releases
20 my($func) = (caller(1))[3];
21 Carp::carp "[$func] Warning: ", @_;
25 my($func) = (caller(1))[3];
26 Carp::croak "[$func] Fatal: ", @_;
29 # original SQLA treated anything false as "use the default"
30 # in addition a lot of CPAN seems to supply undef's for "use the default"
31 # (say hi to Class::DBI::Sweet)
34 my $args = { ref $_[0] eq 'HASH' ? %{$_[0]} : @_ };
36 defined $args->{$_} or delete $args->{$_}
42 # many subclasses on CPAN assume they can dump a bunch of extra new()
43 # parameters, and then get back at them via $obj->{foo}. YAY
44 # (Class::DBI::Sweet says hi back)
46 my ($self, $args) = @_;
47 %{$self} = (%$args, %$self);
51 has converter => (is => 'lazy', clearer => 'clear_converter');
54 is => 'ro', coerce => sub { $_[0] eq 'lower' ? 'lower' : undef }
58 is => 'ro', coerce => sub { uc($_[0]) }, default => sub { 'OR' }
62 is => 'ro', default => sub { 'normal' }
65 has cmp => (is => 'ro', default => sub { '=' });
67 has sqltrue => (is => 'ro', default => sub { '1=1' });
68 has sqlfalse => (is => 'ro', default => sub { '0=1' });
70 has special_ops => (is => 'ro', default => sub { [] });
71 has unary_ops => (is => 'ro', default => sub { [] });
74 # need to guard against ()'s in column names too, but this will break tons of
75 # hacks... ideas anyone?
77 has injection_guard => (
88 has renderer => (is => 'lazy', clearer => 'clear_renderer');
91 is => 'rw', default => sub { '.' },
93 $_[0]->clear_renderer;
94 $_[0]->clear_converter;
101 $_[0]->clear_renderer;
102 $_[0]->clear_converter;
106 has collapse_aliases => (
111 has always_quote => (
112 is => 'rw', default => sub { 1 },
114 $_[0]->clear_renderer;
115 $_[0]->clear_converter;
119 has convert => (is => 'ro');
121 has array_datatypes => (is => 'ro');
123 has converter_class => (
124 is => 'rw', lazy => 1, builder => '_build_converter_class',
125 trigger => sub { shift->clear_converter },
128 sub _build_converter_class {
129 use_module('SQL::Abstract::Converter')
132 has renderer_class => (
133 is => 'rw', lazy => 1, clearer => 1, builder => 1,
134 trigger => sub { shift->clear_renderer },
137 after clear_renderer_class => sub { shift->clear_renderer };
139 sub _build_renderer_class {
141 my ($class, @roles) = (
142 $self->_build_base_renderer_class, $self->_build_renderer_roles
144 return $class unless @roles;
145 return use_module('Moo::Role')->create_class_with_roles($class, @roles);
148 sub _build_base_renderer_class {
149 use_module('Data::Query::Renderer::SQL::Naive')
152 sub _build_renderer_roles { () }
154 sub _converter_args {
156 Scalar::Util::weaken($self);
159 sqla_instance => $self,
160 lower_case => $self->case,
161 default_logic => $self->logic,
162 bind_meta => not($self->bindtype eq 'normal'),
163 identifier_sep => $self->name_sep,
164 (map +($_ => $self->$_), qw(
165 cmp sqltrue sqlfalse injection_guard convert array_datatypes
169 my $sub = $_->{handler};
172 handler => sub { $self->$sub(@_) }
174 } @{$self->special_ops}
176 renderer_will_quote => (
177 defined($self->quote_char) and $self->always_quote
180 legacy_convert_handler => ($self->can('_convert') != \&_convert) ? 1 : 0,
184 sub _build_converter {
186 $self->converter_class->new($self->_converter_args);
192 for ($self->quote_char) {
193 $chars = defined() ? (ref() ? $_ : [$_]) : ['',''];
196 quote_chars => $chars, always_quote => $self->always_quote,
197 identifier_sep => $self->name_sep,
198 collapse_aliases => $self->collapse_aliases,
199 ($self->case ? (lc_keywords => 1) : ()), # always 'lower' if it exists
203 sub _build_renderer {
205 $self->renderer_class->new($self->_renderer_args);
209 my ($self, $dq) = @_;
213 my ($sql, @bind) = @{$self->renderer->render($dq)};
215 ($self->{bindtype} eq 'normal'
216 ? ($sql, map $_->{value}, @bind)
217 : ($sql, map [ $_->{value_meta}, $_->{value} ], @bind)
223 my ($self, $type, @args) = @_;
224 $self->_render_dq($self->converter->${\"_${type}_to_dq"}(@args));
227 sub insert { shift->_render_sqla(insert => @_) }
229 sub update { shift->_render_sqla(update => @_) }
231 sub select { shift->_render_sqla(select => @_) }
233 sub delete { shift->_render_sqla(delete => @_) }
236 my ($self, $where, $order) = @_;
242 ($sql, @bind) = $self->_recurse_where($where) if defined($where);
243 $sql = $sql ? $self->_sqlcase(' where ') . "( $sql )" : '';
247 $sql .= $self->_order_by($order);
250 return wantarray ? ($sql, @bind) : $sql;
253 sub _recurse_where { shift->_render_sqla(where => @_) }
256 my ($self, $arg) = @_;
257 if (my $dq = $self->converter->_order_by_to_dq($arg)) {
258 # SQLA generates ' ORDER BY foo'. The hilarity.
260 ? do { my @r = $self->_render_dq($dq); $r[0] = ' '.$r[0]; @r }
261 : ' '.$self->_render_dq($dq);
267 # highly optimized, as it's called way too often
269 # my ($self, $label) = @_;
271 return '' unless defined $_[1];
272 return ${$_[1]} if ref($_[1]) eq 'SCALAR';
274 unless ($_[0]->{quote_char}) {
275 $_[0]->_assert_pass_injection_guard($_[1]);
279 my $qref = ref $_[0]->{quote_char};
282 ($l, $r) = ( $_[0]->{quote_char}, $_[0]->{quote_char} );
284 elsif ($qref eq 'ARRAY') {
285 ($l, $r) = @{$_[0]->{quote_char}};
288 puke "Unsupported quote_char format: $_[0]->{quote_char}";
291 # parts containing * are naturally unquoted
292 return join( $_[0]->{name_sep}||'', map
293 { $_ eq '*' ? $_ : $l . $_ . $r }
294 ( $_[0]->{name_sep} ? split (/\Q$_[0]->{name_sep}\E/, $_[1] ) : $_[1] )
298 sub _assert_pass_injection_guard {
299 if ($_[1] =~ $_[0]->{injection_guard}) {
300 my $class = ref $_[0];
301 die "Possible SQL injection attempt '$_[1]'. If this is indeed a part of "
302 . "the desired SQL use literal SQL ( \'...' or \[ '...' ] ) or supply "
303 . "your own {injection_guard} attribute to ${class}->new()"
307 # Conversion, if applicable
309 #my ($self, $arg) = @_;
310 if ($_[0]->{convert}) {
311 return $_[0]->_sqlcase($_[0]->{convert}) .'(' . $_[1] . ')';
318 #my ($self, $col, @vals) = @_;
319 # called often - tighten code
320 return $_[0]->{bindtype} eq 'columns'
321 ? map {[$_[1], $_]} @_[2 .. $#_]
326 # Dies if any element of @bind is not in [colname => value] format
327 # if bindtype is 'columns'.
328 sub _assert_bindval_matches_bindtype {
329 # my ($self, @bind) = @_;
331 if ($self->{bindtype} eq 'columns') {
333 if (!defined $_ || ref($_) ne 'ARRAY' || @$_ != 2) {
334 puke "bindtype 'columns' selected, you need to pass: [column_name => bind_value]"
340 # Fix SQL case, if so requested
342 # LDNOTE: if $self->{case} is true, then it contains 'lower', so we
343 # don't touch the argument ... crooked logic, but let's not change it!
344 return $_[0]->{case} ? $_[1] : uc($_[1]);
349 my $data = shift || return;
350 puke "Argument to ", __PACKAGE__, "->values must be a \\%hash"
351 unless ref $data eq 'HASH';
354 foreach my $k ( sort keys %$data ) {
356 local our $Cur_Col_Meta = $k;
357 my ($sql, @bind) = $self->_render_sqla(
360 push @all_bind, @bind;
369 my(@sql, @sqlq, @sqlv);
373 if ($ref eq 'HASH') {
374 for my $k (sort keys %$_) {
377 my $label = $self->_quote($k);
379 # literal SQL with bind
380 my ($sql, @bind) = @$v;
381 $self->_assert_bindval_matches_bindtype(@bind);
382 push @sqlq, "$label = $sql";
384 } elsif ($r eq 'SCALAR') {
385 # literal SQL without bind
386 push @sqlq, "$label = $$v";
388 push @sqlq, "$label = ?";
389 push @sqlv, $self->_bindtype($k, $v);
392 push @sql, $self->_sqlcase('set'), join ', ', @sqlq;
393 } elsif ($ref eq 'ARRAY') {
394 # unlike insert(), assume these are ONLY the column names, i.e. for SQL
397 if ($r eq 'ARRAY') { # literal SQL with bind
398 my ($sql, @bind) = @$v;
399 $self->_assert_bindval_matches_bindtype(@bind);
402 } elsif ($r eq 'SCALAR') { # literal SQL without bind
403 # embedded literal SQL
410 push @sql, '(' . join(', ', @sqlq) . ')';
411 } elsif ($ref eq 'SCALAR') {
415 # strings get case twiddled
416 push @sql, $self->_sqlcase($_);
420 my $sql = join ' ', @sql;
422 # this is pretty tricky
423 # if ask for an array, return ($stmt, @bind)
424 # otherwise, s/?/shift @sqlv/ to put it inline
426 return ($sql, @sqlv);
428 1 while $sql =~ s/\?/my $d = shift(@sqlv);
429 ref $d ? $d->[1] : $d/e;
441 SQL::Abstract - Generate SQL from Perl data structures
447 my $sql = SQL::Abstract->new;
449 my($stmt, @bind) = $sql->select($source, \@fields, \%where, \@order);
451 my($stmt, @bind) = $sql->insert($table, \%fieldvals || \@values);
453 my($stmt, @bind) = $sql->update($table, \%fieldvals, \%where);
455 my($stmt, @bind) = $sql->delete($table, \%where);
457 # Then, use these in your DBI statements
458 my $sth = $dbh->prepare($stmt);
459 $sth->execute(@bind);
461 # Just generate the WHERE clause
462 my($stmt, @bind) = $sql->where(\%where, \@order);
464 # Return values in the same order, for hashed queries
465 # See PERFORMANCE section for more details
466 my @bind = $sql->values(\%fieldvals);
470 This module was inspired by the excellent L<DBIx::Abstract>.
471 However, in using that module I found that what I really wanted
472 to do was generate SQL, but still retain complete control over my
473 statement handles and use the DBI interface. So, I set out to
474 create an abstract SQL generation module.
476 While based on the concepts used by L<DBIx::Abstract>, there are
477 several important differences, especially when it comes to WHERE
478 clauses. I have modified the concepts used to make the SQL easier
479 to generate from Perl data structures and, IMO, more intuitive.
480 The underlying idea is for this module to do what you mean, based
481 on the data structures you provide it. The big advantage is that
482 you don't have to modify your code every time your data changes,
483 as this module figures it out.
485 To begin with, an SQL INSERT is as easy as just specifying a hash
486 of C<key=value> pairs:
489 name => 'Jimbo Bobson',
490 phone => '123-456-7890',
491 address => '42 Sister Lane',
493 state => 'Louisiana',
496 The SQL can then be generated with this:
498 my($stmt, @bind) = $sql->insert('people', \%data);
500 Which would give you something like this:
502 $stmt = "INSERT INTO people
503 (address, city, name, phone, state)
504 VALUES (?, ?, ?, ?, ?)";
505 @bind = ('42 Sister Lane', 'St. Louis', 'Jimbo Bobson',
506 '123-456-7890', 'Louisiana');
508 These are then used directly in your DBI code:
510 my $sth = $dbh->prepare($stmt);
511 $sth->execute(@bind);
513 =head2 Inserting and Updating Arrays
515 If your database has array types (like for example Postgres),
516 activate the special option C<< array_datatypes => 1 >>
517 when creating the C<SQL::Abstract> object.
518 Then you may use an arrayref to insert and update database array types:
520 my $sql = SQL::Abstract->new(array_datatypes => 1);
522 planets => [qw/Mercury Venus Earth Mars/]
525 my($stmt, @bind) = $sql->insert('solar_system', \%data);
529 $stmt = "INSERT INTO solar_system (planets) VALUES (?)"
531 @bind = (['Mercury', 'Venus', 'Earth', 'Mars']);
534 =head2 Inserting and Updating SQL
536 In order to apply SQL functions to elements of your C<%data> you may
537 specify a reference to an arrayref for the given hash value. For example,
538 if you need to execute the Oracle C<to_date> function on a value, you can
539 say something like this:
543 date_entered => \["to_date(?,'MM/DD/YYYY')", "03/02/2003"],
546 The first value in the array is the actual SQL. Any other values are
547 optional and would be included in the bind values array. This gives
550 my($stmt, @bind) = $sql->insert('people', \%data);
552 $stmt = "INSERT INTO people (name, date_entered)
553 VALUES (?, to_date(?,'MM/DD/YYYY'))";
554 @bind = ('Bill', '03/02/2003');
556 An UPDATE is just as easy, all you change is the name of the function:
558 my($stmt, @bind) = $sql->update('people', \%data);
560 Notice that your C<%data> isn't touched; the module will generate
561 the appropriately quirky SQL for you automatically. Usually you'll
562 want to specify a WHERE clause for your UPDATE, though, which is
563 where handling C<%where> hashes comes in handy...
565 =head2 Complex where statements
567 This module can generate pretty complicated WHERE statements
568 easily. For example, simple C<key=value> pairs are taken to mean
569 equality, and if you want to see if a field is within a set
570 of values, you can use an arrayref. Let's say we wanted to
571 SELECT some data based on this criteria:
575 worker => ['nwiger', 'rcwe', 'sfz'],
576 status => { '!=', 'completed' }
579 my($stmt, @bind) = $sql->select('tickets', '*', \%where);
581 The above would give you something like this:
583 $stmt = "SELECT * FROM tickets WHERE
584 ( requestor = ? ) AND ( status != ? )
585 AND ( worker = ? OR worker = ? OR worker = ? )";
586 @bind = ('inna', 'completed', 'nwiger', 'rcwe', 'sfz');
588 Which you could then use in DBI code like so:
590 my $sth = $dbh->prepare($stmt);
591 $sth->execute(@bind);
597 The functions are simple. There's one for each major SQL operation,
598 and a constructor you use first. The arguments are specified in a
599 similar order to each function (table, then fields, then a where
600 clause) to try and simplify things.
605 =head2 new(option => 'value')
607 The C<new()> function takes a list of options and values, and returns
608 a new B<SQL::Abstract> object which can then be used to generate SQL
609 through the methods below. The options accepted are:
615 If set to 'lower', then SQL will be generated in all lowercase. By
616 default SQL is generated in "textbook" case meaning something like:
618 SELECT a_field FROM a_table WHERE some_field LIKE '%someval%'
620 Any setting other than 'lower' is ignored.
624 This determines what the default comparison operator is. By default
625 it is C<=>, meaning that a hash like this:
627 %where = (name => 'nwiger', email => 'nate@wiger.org');
629 Will generate SQL like this:
631 WHERE name = 'nwiger' AND email = 'nate@wiger.org'
633 However, you may want loose comparisons by default, so if you set
634 C<cmp> to C<like> you would get SQL such as:
636 WHERE name like 'nwiger' AND email like 'nate@wiger.org'
638 You can also override the comparison on an individual basis - see
639 the huge section on L</"WHERE CLAUSES"> at the bottom.
641 =item sqltrue, sqlfalse
643 Expressions for inserting boolean values within SQL statements.
644 By default these are C<1=1> and C<1=0>. They are used
645 by the special operators C<-in> and C<-not_in> for generating
646 correct SQL even when the argument is an empty array (see below).
650 This determines the default logical operator for multiple WHERE
651 statements in arrays or hashes. If absent, the default logic is "or"
652 for arrays, and "and" for hashes. This means that a WHERE
656 event_date => {'>=', '2/13/99'},
657 event_date => {'<=', '4/24/03'},
660 will generate SQL like this:
662 WHERE event_date >= '2/13/99' OR event_date <= '4/24/03'
664 This is probably not what you want given this query, though (look
665 at the dates). To change the "OR" to an "AND", simply specify:
667 my $sql = SQL::Abstract->new(logic => 'and');
669 Which will change the above C<WHERE> to:
671 WHERE event_date >= '2/13/99' AND event_date <= '4/24/03'
673 The logic can also be changed locally by inserting
674 a modifier in front of an arrayref :
676 @where = (-and => [event_date => {'>=', '2/13/99'},
677 event_date => {'<=', '4/24/03'} ]);
679 See the L</"WHERE CLAUSES"> section for explanations.
683 This will automatically convert comparisons using the specified SQL
684 function for both column and value. This is mostly used with an argument
685 of C<upper> or C<lower>, so that the SQL will have the effect of
686 case-insensitive "searches". For example, this:
688 $sql = SQL::Abstract->new(convert => 'upper');
689 %where = (keywords => 'MaKe iT CAse inSeNSItive');
691 Will turn out the following SQL:
693 WHERE upper(keywords) like upper('MaKe iT CAse inSeNSItive')
695 The conversion can be C<upper()>, C<lower()>, or any other SQL function
696 that can be applied symmetrically to fields (actually B<SQL::Abstract> does
697 not validate this option; it will just pass through what you specify verbatim).
701 This is a kludge because many databases suck. For example, you can't
702 just bind values using DBI's C<execute()> for Oracle C<CLOB> or C<BLOB> fields.
703 Instead, you have to use C<bind_param()>:
705 $sth->bind_param(1, 'reg data');
706 $sth->bind_param(2, $lots, {ora_type => ORA_CLOB});
708 The problem is, B<SQL::Abstract> will normally just return a C<@bind> array,
709 which loses track of which field each slot refers to. Fear not.
711 If you specify C<bindtype> in new, you can determine how C<@bind> is returned.
712 Currently, you can specify either C<normal> (default) or C<columns>. If you
713 specify C<columns>, you will get an array that looks like this:
715 my $sql = SQL::Abstract->new(bindtype => 'columns');
716 my($stmt, @bind) = $sql->insert(...);
719 [ 'column1', 'value1' ],
720 [ 'column2', 'value2' ],
721 [ 'column3', 'value3' ],
724 You can then iterate through this manually, using DBI's C<bind_param()>.
726 $sth->prepare($stmt);
729 my($col, $data) = @$_;
730 if ($col eq 'details' || $col eq 'comments') {
731 $sth->bind_param($i, $data, {ora_type => ORA_CLOB});
732 } elsif ($col eq 'image') {
733 $sth->bind_param($i, $data, {ora_type => ORA_BLOB});
735 $sth->bind_param($i, $data);
739 $sth->execute; # execute without @bind now
741 Now, why would you still use B<SQL::Abstract> if you have to do this crap?
742 Basically, the advantage is still that you don't have to care which fields
743 are or are not included. You could wrap that above C<for> loop in a simple
744 sub called C<bind_fields()> or something and reuse it repeatedly. You still
745 get a layer of abstraction over manual SQL specification.
747 Note that if you set L</bindtype> to C<columns>, the C<\[$sql, @bind]>
748 construct (see L</Literal SQL with placeholders and bind values (subqueries)>)
749 will expect the bind values in this format.
753 This is the character that a table or column name will be quoted
754 with. By default this is an empty string, but you could set it to
755 the character C<`>, to generate SQL like this:
757 SELECT `a_field` FROM `a_table` WHERE `some_field` LIKE '%someval%'
759 Alternatively, you can supply an array ref of two items, the first being the left
760 hand quote character, and the second the right hand quote character. For
761 example, you could supply C<['[',']']> for SQL Server 2000 compliant quotes
762 that generates SQL like this:
764 SELECT [a_field] FROM [a_table] WHERE [some_field] LIKE '%someval%'
766 Quoting is useful if you have tables or columns names that are reserved
767 words in your database's SQL dialect.
771 This is the character that separates a table and column name. It is
772 necessary to specify this when the C<quote_char> option is selected,
773 so that tables and column names can be individually quoted like this:
775 SELECT `table`.`one_field` FROM `table` WHERE `table`.`other_field` = 1
777 =item injection_guard
779 A regular expression C<qr/.../> that is applied to any C<-function> and unquoted
780 column name specified in a query structure. This is a safety mechanism to avoid
781 injection attacks when mishandling user input e.g.:
783 my %condition_as_column_value_pairs = get_values_from_user();
784 $sqla->select( ... , \%condition_as_column_value_pairs );
786 If the expression matches an exception is thrown. Note that literal SQL
787 supplied via C<\'...'> or C<\['...']> is B<not> checked in any way.
789 Defaults to checking for C<;> and the C<GO> keyword (TransactSQL)
791 =item array_datatypes
793 When this option is true, arrayrefs in INSERT or UPDATE are
794 interpreted as array datatypes and are passed directly
796 When this option is false, arrayrefs are interpreted
797 as literal SQL, just like refs to arrayrefs
798 (but this behavior is for backwards compatibility; when writing
799 new queries, use the "reference to arrayref" syntax
805 Takes a reference to a list of "special operators"
806 to extend the syntax understood by L<SQL::Abstract>.
807 See section L</"SPECIAL OPERATORS"> for details.
811 Takes a reference to a list of "unary operators"
812 to extend the syntax understood by L<SQL::Abstract>.
813 See section L</"UNARY OPERATORS"> for details.
819 =head2 insert($table, \@values || \%fieldvals, \%options)
821 This is the simplest function. You simply give it a table name
822 and either an arrayref of values or hashref of field/value pairs.
823 It returns an SQL INSERT statement and a list of bind values.
824 See the sections on L</"Inserting and Updating Arrays"> and
825 L</"Inserting and Updating SQL"> for information on how to insert
826 with those data types.
828 The optional C<\%options> hash reference may contain additional
829 options to generate the insert SQL. Currently supported options
836 Takes either a scalar of raw SQL fields, or an array reference of
837 field names, and adds on an SQL C<RETURNING> statement at the end.
838 This allows you to return data generated by the insert statement
839 (such as row IDs) without performing another C<SELECT> statement.
840 Note, however, this is not part of the SQL standard and may not
841 be supported by all database engines.
845 =head2 update($table, \%fieldvals, \%where)
847 This takes a table, hashref of field/value pairs, and an optional
848 hashref L<WHERE clause|/WHERE CLAUSES>. It returns an SQL UPDATE function and a list
850 See the sections on L</"Inserting and Updating Arrays"> and
851 L</"Inserting and Updating SQL"> for information on how to insert
852 with those data types.
854 =head2 select($source, $fields, $where, $order)
856 This returns a SQL SELECT statement and associated list of bind values, as
857 specified by the arguments :
863 Specification of the 'FROM' part of the statement.
864 The argument can be either a plain scalar (interpreted as a table
865 name, will be quoted), or an arrayref (interpreted as a list
866 of table names, joined by commas, quoted), or a scalarref
867 (literal table name, not quoted), or a ref to an arrayref
868 (list of literal table names, joined by commas, not quoted).
872 Specification of the list of fields to retrieve from
874 The argument can be either an arrayref (interpreted as a list
875 of field names, will be joined by commas and quoted), or a
876 plain scalar (literal SQL, not quoted).
877 Please observe that this API is not as flexible as that of
878 the first argument C<$source>, for backwards compatibility reasons.
882 Optional argument to specify the WHERE part of the query.
883 The argument is most often a hashref, but can also be
884 an arrayref or plain scalar --
885 see section L<WHERE clause|/"WHERE CLAUSES"> for details.
889 Optional argument to specify the ORDER BY part of the query.
890 The argument can be a scalar, a hashref or an arrayref
891 -- see section L<ORDER BY clause|/"ORDER BY CLAUSES">
897 =head2 delete($table, \%where)
899 This takes a table name and optional hashref L<WHERE clause|/WHERE CLAUSES>.
900 It returns an SQL DELETE statement and list of bind values.
902 =head2 where(\%where, \@order)
904 This is used to generate just the WHERE clause. For example,
905 if you have an arbitrary data structure and know what the
906 rest of your SQL is going to look like, but want an easy way
907 to produce a WHERE clause, use this. It returns an SQL WHERE
908 clause and list of bind values.
911 =head2 values(\%data)
913 This just returns the values from the hash C<%data>, in the same
914 order that would be returned from any of the other above queries.
915 Using this allows you to markedly speed up your queries if you
916 are affecting lots of rows. See below under the L</"PERFORMANCE"> section.
918 =head2 generate($any, 'number', $of, \@data, $struct, \%types)
920 Warning: This is an experimental method and subject to change.
922 This returns arbitrarily generated SQL. It's a really basic shortcut.
923 It will return two different things, depending on return context:
925 my($stmt, @bind) = $sql->generate('create table', \$table, \@fields);
926 my $stmt_and_val = $sql->generate('create table', \$table, \@fields);
928 These would return the following:
931 $stmt = "CREATE TABLE test (?, ?)";
932 @bind = (field1, field2);
934 # Second calling form
935 $stmt_and_val = "CREATE TABLE test (field1, field2)";
937 Depending on what you're trying to do, it's up to you to choose the correct
938 format. In this example, the second form is what you would want.
942 $sql->generate('alter session', { nls_date_format => 'MM/YY' });
946 ALTER SESSION SET nls_date_format = 'MM/YY'
948 You get the idea. Strings get their case twiddled, but everything
949 else remains verbatim.
955 This module uses a variation on the idea from L<DBIx::Abstract>. It
956 is B<NOT>, repeat I<not> 100% compatible. B<The main logic of this
957 module is that things in arrays are OR'ed, and things in hashes
960 The easiest way to explain is to show lots of examples. After
961 each C<%where> hash shown, it is assumed you used:
963 my($stmt, @bind) = $sql->where(\%where);
965 However, note that the C<%where> hash can be used directly in any
966 of the other functions as well, as described above.
968 =head2 Key-value pairs
970 So, let's get started. To begin, a simple hash:
974 status => 'completed'
977 Is converted to SQL C<key = val> statements:
979 $stmt = "WHERE user = ? AND status = ?";
980 @bind = ('nwiger', 'completed');
982 One common thing I end up doing is having a list of values that
983 a field can be in. To do this, simply specify a list inside of
988 status => ['assigned', 'in-progress', 'pending'];
991 This simple code will create the following:
993 $stmt = "WHERE user = ? AND ( status = ? OR status = ? OR status = ? )";
994 @bind = ('nwiger', 'assigned', 'in-progress', 'pending');
996 A field associated to an empty arrayref will be considered a
997 logical false and will generate 0=1.
999 =head2 Tests for NULL values
1001 If the value part is C<undef> then this is converted to SQL <IS NULL>
1010 $stmt = "WHERE user = ? AND status IS NULL";
1013 To test if a column IS NOT NULL:
1017 status => { '!=', undef },
1020 =head2 Specific comparison operators
1022 If you want to specify a different type of operator for your comparison,
1023 you can use a hashref for a given column:
1027 status => { '!=', 'completed' }
1030 Which would generate:
1032 $stmt = "WHERE user = ? AND status != ?";
1033 @bind = ('nwiger', 'completed');
1035 To test against multiple values, just enclose the values in an arrayref:
1037 status => { '=', ['assigned', 'in-progress', 'pending'] };
1039 Which would give you:
1041 "WHERE status = ? OR status = ? OR status = ?"
1044 The hashref can also contain multiple pairs, in which case it is expanded
1045 into an C<AND> of its elements:
1049 status => { '!=', 'completed', -not_like => 'pending%' }
1052 # Or more dynamically, like from a form
1053 $where{user} = 'nwiger';
1054 $where{status}{'!='} = 'completed';
1055 $where{status}{'-not_like'} = 'pending%';
1057 # Both generate this
1058 $stmt = "WHERE user = ? AND status != ? AND status NOT LIKE ?";
1059 @bind = ('nwiger', 'completed', 'pending%');
1062 To get an OR instead, you can combine it with the arrayref idea:
1066 priority => [ { '=', 2 }, { '>', 5 } ]
1069 Which would generate:
1071 $stmt = "WHERE ( priority = ? OR priority > ? ) AND user = ?";
1072 @bind = ('2', '5', 'nwiger');
1074 If you want to include literal SQL (with or without bind values), just use a
1075 scalar reference or array reference as the value:
1078 date_entered => { '>' => \["to_date(?, 'MM/DD/YYYY')", "11/26/2008"] },
1079 date_expires => { '<' => \"now()" }
1082 Which would generate:
1084 $stmt = "WHERE date_entered > "to_date(?, 'MM/DD/YYYY') AND date_expires < now()";
1085 @bind = ('11/26/2008');
1088 =head2 Logic and nesting operators
1090 In the example above,
1091 there is a subtle trap if you want to say something like
1092 this (notice the C<AND>):
1094 WHERE priority != ? AND priority != ?
1096 Because, in Perl you I<can't> do this:
1098 priority => { '!=', 2, '!=', 1 }
1100 As the second C<!=> key will obliterate the first. The solution
1101 is to use the special C<-modifier> form inside an arrayref:
1103 priority => [ -and => {'!=', 2},
1107 Normally, these would be joined by C<OR>, but the modifier tells it
1108 to use C<AND> instead. (Hint: You can use this in conjunction with the
1109 C<logic> option to C<new()> in order to change the way your queries
1110 work by default.) B<Important:> Note that the C<-modifier> goes
1111 B<INSIDE> the arrayref, as an extra first element. This will
1112 B<NOT> do what you think it might:
1114 priority => -and => [{'!=', 2}, {'!=', 1}] # WRONG!
1116 Here is a quick list of equivalencies, since there is some overlap:
1119 status => {'!=', 'completed', 'not like', 'pending%' }
1120 status => [ -and => {'!=', 'completed'}, {'not like', 'pending%'}]
1123 status => {'=', ['assigned', 'in-progress']}
1124 status => [ -or => {'=', 'assigned'}, {'=', 'in-progress'}]
1125 status => [ {'=', 'assigned'}, {'=', 'in-progress'} ]
1129 =head2 Special operators : IN, BETWEEN, etc.
1131 You can also use the hashref format to compare a list of fields using the
1132 C<IN> comparison operator, by specifying the list as an arrayref:
1135 status => 'completed',
1136 reportid => { -in => [567, 2335, 2] }
1139 Which would generate:
1141 $stmt = "WHERE status = ? AND reportid IN (?,?,?)";
1142 @bind = ('completed', '567', '2335', '2');
1144 The reverse operator C<-not_in> generates SQL C<NOT IN> and is used in
1147 If the argument to C<-in> is an empty array, 'sqlfalse' is generated
1148 (by default : C<1=0>). Similarly, C<< -not_in => [] >> generates
1149 'sqltrue' (by default : C<1=1>).
1151 In addition to the array you can supply a chunk of literal sql or
1152 literal sql with bind:
1155 customer => { -in => \[
1156 'SELECT cust_id FROM cust WHERE balance > ?',
1159 status => { -in => \'SELECT status_codes FROM states' },
1165 customer IN ( SELECT cust_id FROM cust WHERE balance > ? )
1166 AND status IN ( SELECT status_codes FROM states )
1170 Finally, if the argument to C<-in> is not a reference, it will be
1171 treated as a single-element array.
1173 Another pair of operators is C<-between> and C<-not_between>,
1174 used with an arrayref of two values:
1178 completion_date => {
1179 -not_between => ['2002-10-01', '2003-02-06']
1185 WHERE user = ? AND completion_date NOT BETWEEN ( ? AND ? )
1187 Just like with C<-in> all plausible combinations of literal SQL
1191 start0 => { -between => [ 1, 2 ] },
1192 start1 => { -between => \["? AND ?", 1, 2] },
1193 start2 => { -between => \"lower(x) AND upper(y)" },
1194 start3 => { -between => [
1196 \["upper(?)", 'stuff' ],
1203 ( start0 BETWEEN ? AND ? )
1204 AND ( start1 BETWEEN ? AND ? )
1205 AND ( start2 BETWEEN lower(x) AND upper(y) )
1206 AND ( start3 BETWEEN lower(x) AND upper(?) )
1208 @bind = (1, 2, 1, 2, 'stuff');
1211 These are the two builtin "special operators"; but the
1212 list can be expanded : see section L</"SPECIAL OPERATORS"> below.
1214 =head2 Unary operators: bool
1216 If you wish to test against boolean columns or functions within your
1217 database you can use the C<-bool> and C<-not_bool> operators. For
1218 example to test the column C<is_user> being true and the column
1219 C<is_enabled> being false you would use:-
1223 -not_bool => 'is_enabled',
1228 WHERE is_user AND NOT is_enabled
1230 If a more complex combination is required, testing more conditions,
1231 then you should use the and/or operators:-
1236 -not_bool => { two=> { -rlike => 'bar' } },
1237 -not_bool => { three => [ { '=', 2 }, { '>', 5 } ] },
1248 (NOT ( three = ? OR three > ? ))
1251 =head2 Nested conditions, -and/-or prefixes
1253 So far, we've seen how multiple conditions are joined with a top-level
1254 C<AND>. We can change this by putting the different conditions we want in
1255 hashes and then putting those hashes in an array. For example:
1260 status => { -like => ['pending%', 'dispatched'] },
1264 status => 'unassigned',
1268 This data structure would create the following:
1270 $stmt = "WHERE ( user = ? AND ( status LIKE ? OR status LIKE ? ) )
1271 OR ( user = ? AND status = ? ) )";
1272 @bind = ('nwiger', 'pending', 'dispatched', 'robot', 'unassigned');
1275 Clauses in hashrefs or arrayrefs can be prefixed with an C<-and> or C<-or>
1276 to change the logic inside :
1282 -and => [ workhrs => {'>', 20}, geo => 'ASIA' ],
1283 -or => { workhrs => {'<', 50}, geo => 'EURO' },
1290 WHERE ( user = ? AND (
1291 ( workhrs > ? AND geo = ? )
1292 OR ( workhrs < ? OR geo = ? )
1295 =head3 Algebraic inconsistency, for historical reasons
1297 C<Important note>: when connecting several conditions, the C<-and->|C<-or>
1298 operator goes C<outside> of the nested structure; whereas when connecting
1299 several constraints on one column, the C<-and> operator goes
1300 C<inside> the arrayref. Here is an example combining both features :
1303 -and => [a => 1, b => 2],
1304 -or => [c => 3, d => 4],
1305 e => [-and => {-like => 'foo%'}, {-like => '%bar'} ]
1310 WHERE ( ( ( a = ? AND b = ? )
1311 OR ( c = ? OR d = ? )
1312 OR ( e LIKE ? AND e LIKE ? ) ) )
1314 This difference in syntax is unfortunate but must be preserved for
1315 historical reasons. So be careful : the two examples below would
1316 seem algebraically equivalent, but they are not
1318 {col => [-and => {-like => 'foo%'}, {-like => '%bar'}]}
1319 # yields : WHERE ( ( col LIKE ? AND col LIKE ? ) )
1321 [-and => {col => {-like => 'foo%'}, {col => {-like => '%bar'}}]]
1322 # yields : WHERE ( ( col LIKE ? OR col LIKE ? ) )
1325 =head2 Literal SQL and value type operators
1327 The basic premise of SQL::Abstract is that in WHERE specifications the "left
1328 side" is a column name and the "right side" is a value (normally rendered as
1329 a placeholder). This holds true for both hashrefs and arrayref pairs as you
1330 see in the L</WHERE CLAUSES> examples above. Sometimes it is necessary to
1331 alter this behavior. There are several ways of doing so.
1335 This is a virtual operator that signals the string to its right side is an
1336 identifier (a column name) and not a value. For example to compare two
1337 columns you would write:
1340 priority => { '<', 2 },
1341 requestor => { -ident => 'submitter' },
1346 $stmt = "WHERE priority < ? AND requestor = submitter";
1349 If you are maintaining legacy code you may see a different construct as
1350 described in L</Deprecated usage of Literal SQL>, please use C<-ident> in new
1355 This is a virtual operator that signals that the construct to its right side
1356 is a value to be passed to DBI. This is for example necessary when you want
1357 to write a where clause against an array (for RDBMS that support such
1358 datatypes). For example:
1361 array => { -value => [1, 2, 3] }
1366 $stmt = 'WHERE array = ?';
1367 @bind = ([1, 2, 3]);
1369 Note that if you were to simply say:
1375 the result would probably not be what you wanted:
1377 $stmt = 'WHERE array = ? OR array = ? OR array = ?';
1382 Finally, sometimes only literal SQL will do. To include a random snippet
1383 of SQL verbatim, you specify it as a scalar reference. Consider this only
1384 as a last resort. Usually there is a better way. For example:
1387 priority => { '<', 2 },
1388 requestor => { -in => \'(SELECT name FROM hitmen)' },
1393 $stmt = "WHERE priority < ? AND requestor IN (SELECT name FROM hitmen)"
1396 Note that in this example, you only get one bind parameter back, since
1397 the verbatim SQL is passed as part of the statement.
1401 Never use untrusted input as a literal SQL argument - this is a massive
1402 security risk (there is no way to check literal snippets for SQL
1403 injections and other nastyness). If you need to deal with untrusted input
1404 use literal SQL with placeholders as described next.
1406 =head3 Literal SQL with placeholders and bind values (subqueries)
1408 If the literal SQL to be inserted has placeholders and bind values,
1409 use a reference to an arrayref (yes this is a double reference --
1410 not so common, but perfectly legal Perl). For example, to find a date
1411 in Postgres you can use something like this:
1414 date_column => \[q/= date '2008-09-30' - ?::integer/, 10/]
1419 $stmt = "WHERE ( date_column = date '2008-09-30' - ?::integer )"
1422 Note that you must pass the bind values in the same format as they are returned
1423 by L</where>. That means that if you set L</bindtype> to C<columns>, you must
1424 provide the bind values in the C<< [ column_meta => value ] >> format, where
1425 C<column_meta> is an opaque scalar value; most commonly the column name, but
1426 you can use any scalar value (including references and blessed references),
1427 L<SQL::Abstract> will simply pass it through intact. So if C<bindtype> is set
1428 to C<columns> the above example will look like:
1431 date_column => \[q/= date '2008-09-30' - ?::integer/, [ dummy => 10 ]/]
1434 Literal SQL is especially useful for nesting parenthesized clauses in the
1435 main SQL query. Here is a first example :
1437 my ($sub_stmt, @sub_bind) = ("SELECT c1 FROM t1 WHERE c2 < ? AND c3 LIKE ?",
1441 bar => \["IN ($sub_stmt)" => @sub_bind],
1446 $stmt = "WHERE (foo = ? AND bar IN (SELECT c1 FROM t1
1447 WHERE c2 < ? AND c3 LIKE ?))";
1448 @bind = (1234, 100, "foo%");
1450 Other subquery operators, like for example C<"E<gt> ALL"> or C<"NOT IN">,
1451 are expressed in the same way. Of course the C<$sub_stmt> and
1452 its associated bind values can be generated through a former call
1455 my ($sub_stmt, @sub_bind)
1456 = $sql->select("t1", "c1", {c2 => {"<" => 100},
1457 c3 => {-like => "foo%"}});
1460 bar => \["> ALL ($sub_stmt)" => @sub_bind],
1463 In the examples above, the subquery was used as an operator on a column;
1464 but the same principle also applies for a clause within the main C<%where>
1465 hash, like an EXISTS subquery :
1467 my ($sub_stmt, @sub_bind)
1468 = $sql->select("t1", "*", {c1 => 1, c2 => \"> t0.c0"});
1469 my %where = ( -and => [
1471 \["EXISTS ($sub_stmt)" => @sub_bind],
1476 $stmt = "WHERE (foo = ? AND EXISTS (SELECT * FROM t1
1477 WHERE c1 = ? AND c2 > t0.c0))";
1481 Observe that the condition on C<c2> in the subquery refers to
1482 column C<t0.c0> of the main query : this is I<not> a bind
1483 value, so we have to express it through a scalar ref.
1484 Writing C<< c2 => {">" => "t0.c0"} >> would have generated
1485 C<< c2 > ? >> with bind value C<"t0.c0"> ... not exactly
1486 what we wanted here.
1488 Finally, here is an example where a subquery is used
1489 for expressing unary negation:
1491 my ($sub_stmt, @sub_bind)
1492 = $sql->where({age => [{"<" => 10}, {">" => 20}]});
1493 $sub_stmt =~ s/^ where //i; # don't want "WHERE" in the subclause
1495 lname => {like => '%son%'},
1496 \["NOT ($sub_stmt)" => @sub_bind],
1501 $stmt = "lname LIKE ? AND NOT ( age < ? OR age > ? )"
1502 @bind = ('%son%', 10, 20)
1504 =head3 Deprecated usage of Literal SQL
1506 Below are some examples of archaic use of literal SQL. It is shown only as
1507 reference for those who deal with legacy code. Each example has a much
1508 better, cleaner and safer alternative that users should opt for in new code.
1514 my %where = ( requestor => \'IS NOT NULL' )
1516 $stmt = "WHERE requestor IS NOT NULL"
1518 This used to be the way of generating NULL comparisons, before the handling
1519 of C<undef> got formalized. For new code please use the superior syntax as
1520 described in L</Tests for NULL values>.
1524 my %where = ( requestor => \'= submitter' )
1526 $stmt = "WHERE requestor = submitter"
1528 This used to be the only way to compare columns. Use the superior L</-ident>
1529 method for all new code. For example an identifier declared in such a way
1530 will be properly quoted if L</quote_char> is properly set, while the legacy
1531 form will remain as supplied.
1535 my %where = ( is_ready => \"", completed => { '>', '2012-12-21' } )
1537 $stmt = "WHERE completed > ? AND is_ready"
1538 @bind = ('2012-12-21')
1540 Using an empty string literal used to be the only way to express a boolean.
1541 For all new code please use the much more readable
1542 L<-bool|/Unary operators: bool> operator.
1548 These pages could go on for a while, since the nesting of the data
1549 structures this module can handle are pretty much unlimited (the
1550 module implements the C<WHERE> expansion as a recursive function
1551 internally). Your best bet is to "play around" with the module a
1552 little to see how the data structures behave, and choose the best
1553 format for your data based on that.
1555 And of course, all the values above will probably be replaced with
1556 variables gotten from forms or the command line. After all, if you
1557 knew everything ahead of time, you wouldn't have to worry about
1558 dynamically-generating SQL and could just hardwire it into your
1561 =head1 ORDER BY CLAUSES
1563 Some functions take an order by clause. This can either be a scalar (just a
1564 column name,) a hash of C<< { -desc => 'col' } >> or C<< { -asc => 'col' } >>,
1565 or an array of either of the two previous forms. Examples:
1567 Given | Will Generate
1568 ----------------------------------------------------------
1570 \'colA DESC' | ORDER BY colA DESC
1572 'colA' | ORDER BY colA
1574 [qw/colA colB/] | ORDER BY colA, colB
1576 {-asc => 'colA'} | ORDER BY colA ASC
1578 {-desc => 'colB'} | ORDER BY colB DESC
1580 ['colA', {-asc => 'colB'}] | ORDER BY colA, colB ASC
1582 { -asc => [qw/colA colB/] } | ORDER BY colA ASC, colB ASC
1585 { -asc => 'colA' }, | ORDER BY colA ASC, colB DESC,
1586 { -desc => [qw/colB/], | colC ASC, colD ASC
1587 { -asc => [qw/colC colD/],|
1589 ===========================================================
1593 =head1 SPECIAL OPERATORS
1595 my $sqlmaker = SQL::Abstract->new(special_ops => [
1599 my ($self, $field, $op, $arg) = @_;
1605 handler => 'method_name',
1609 A "special operator" is a SQL syntactic clause that can be
1610 applied to a field, instead of a usual binary operator.
1613 WHERE field IN (?, ?, ?)
1614 WHERE field BETWEEN ? AND ?
1615 WHERE MATCH(field) AGAINST (?, ?)
1617 Special operators IN and BETWEEN are fairly standard and therefore
1618 are builtin within C<SQL::Abstract> (as the overridable methods
1619 C<_where_field_IN> and C<_where_field_BETWEEN>). For other operators,
1620 like the MATCH .. AGAINST example above which is specific to MySQL,
1621 you can write your own operator handlers - supply a C<special_ops>
1622 argument to the C<new> method. That argument takes an arrayref of
1623 operator definitions; each operator definition is a hashref with two
1630 the regular expression to match the operator
1634 Either a coderef or a plain scalar method name. In both cases
1635 the expected return is C<< ($sql, @bind) >>.
1637 When supplied with a method name, it is simply called on the
1638 L<SQL::Abstract/> object as:
1640 $self->$method_name ($field, $op, $arg)
1644 $op is the part that matched the handler regex
1645 $field is the LHS of the operator
1648 When supplied with a coderef, it is called as:
1650 $coderef->($self, $field, $op, $arg)
1655 For example, here is an implementation
1656 of the MATCH .. AGAINST syntax for MySQL
1658 my $sqlmaker = SQL::Abstract->new(special_ops => [
1660 # special op for MySql MATCH (field) AGAINST(word1, word2, ...)
1661 {regex => qr/^match$/i,
1663 my ($self, $field, $op, $arg) = @_;
1664 $arg = [$arg] if not ref $arg;
1665 my $label = $self->_quote($field);
1666 my ($placeholder) = $self->_convert('?');
1667 my $placeholders = join ", ", (($placeholder) x @$arg);
1668 my $sql = $self->_sqlcase('match') . " ($label) "
1669 . $self->_sqlcase('against') . " ($placeholders) ";
1670 my @bind = $self->_bindtype($field, @$arg);
1671 return ($sql, @bind);
1678 =head1 UNARY OPERATORS
1680 my $sqlmaker = SQL::Abstract->new(unary_ops => [
1684 my ($self, $op, $arg) = @_;
1690 handler => 'method_name',
1694 A "unary operator" is a SQL syntactic clause that can be
1695 applied to a field - the operator goes before the field
1697 You can write your own operator handlers - supply a C<unary_ops>
1698 argument to the C<new> method. That argument takes an arrayref of
1699 operator definitions; each operator definition is a hashref with two
1706 the regular expression to match the operator
1710 Either a coderef or a plain scalar method name. In both cases
1711 the expected return is C<< $sql >>.
1713 When supplied with a method name, it is simply called on the
1714 L<SQL::Abstract/> object as:
1716 $self->$method_name ($op, $arg)
1720 $op is the part that matched the handler regex
1721 $arg is the RHS or argument of the operator
1723 When supplied with a coderef, it is called as:
1725 $coderef->($self, $op, $arg)
1733 Thanks to some benchmarking by Mark Stosberg, it turns out that
1734 this module is many orders of magnitude faster than using C<DBIx::Abstract>.
1735 I must admit this wasn't an intentional design issue, but it's a
1736 byproduct of the fact that you get to control your C<DBI> handles
1739 To maximize performance, use a code snippet like the following:
1741 # prepare a statement handle using the first row
1742 # and then reuse it for the rest of the rows
1744 for my $href (@array_of_hashrefs) {
1745 $stmt ||= $sql->insert('table', $href);
1746 $sth ||= $dbh->prepare($stmt);
1747 $sth->execute($sql->values($href));
1750 The reason this works is because the keys in your C<$href> are sorted
1751 internally by B<SQL::Abstract>. Thus, as long as your data retains
1752 the same structure, you only have to generate the SQL the first time
1753 around. On subsequent queries, simply use the C<values> function provided
1754 by this module to return your values in the correct order.
1756 However this depends on the values having the same type - if, for
1757 example, the values of a where clause may either have values
1758 (resulting in sql of the form C<column = ?> with a single bind
1759 value), or alternatively the values might be C<undef> (resulting in
1760 sql of the form C<column IS NULL> with no bind value) then the
1761 caching technique suggested will not work.
1765 If you use my C<CGI::FormBuilder> module at all, you'll hopefully
1766 really like this part (I do, at least). Building up a complex query
1767 can be as simple as the following:
1774 use CGI::FormBuilder;
1777 my $form = CGI::FormBuilder->new(...);
1778 my $sql = SQL::Abstract->new;
1780 if ($form->submitted) {
1781 my $field = $form->field;
1782 my $id = delete $field->{id};
1783 my($stmt, @bind) = $sql->update('table', $field, {id => $id});
1786 Of course, you would still have to connect using C<DBI> to run the
1787 query, but the point is that if you make your form look like your
1788 table, the actual query script can be extremely simplistic.
1790 If you're B<REALLY> lazy (I am), check out C<HTML::QuickTable> for
1791 a fast interface to returning and formatting data. I frequently
1792 use these three modules together to write complex database query
1793 apps in under 50 lines.
1799 =item * gitweb: L<http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=dbsrgits/SQL-Abstract.git>
1801 =item * git: L<git://git.shadowcat.co.uk/dbsrgits/SQL-Abstract.git>
1807 Version 1.50 was a major internal refactoring of C<SQL::Abstract>.
1808 Great care has been taken to preserve the I<published> behavior
1809 documented in previous versions in the 1.* family; however,
1810 some features that were previously undocumented, or behaved
1811 differently from the documentation, had to be changed in order
1812 to clarify the semantics. Hence, client code that was relying
1813 on some dark areas of C<SQL::Abstract> v1.*
1814 B<might behave differently> in v1.50.
1816 The main changes are :
1822 support for literal SQL through the C<< \ [$sql, bind] >> syntax.
1826 support for the { operator => \"..." } construct (to embed literal SQL)
1830 support for the { operator => \["...", @bind] } construct (to embed literal SQL with bind values)
1834 optional support for L<array datatypes|/"Inserting and Updating Arrays">
1838 defensive programming : check arguments
1842 fixed bug with global logic, which was previously implemented
1843 through global variables yielding side-effects. Prior versions would
1844 interpret C<< [ {cond1, cond2}, [cond3, cond4] ] >>
1845 as C<< "(cond1 AND cond2) OR (cond3 AND cond4)" >>.
1846 Now this is interpreted
1847 as C<< "(cond1 AND cond2) OR (cond3 OR cond4)" >>.
1852 fixed semantics of _bindtype on array args
1856 dropped the C<_anoncopy> of the %where tree. No longer necessary,
1857 we just avoid shifting arrays within that tree.
1861 dropped the C<_modlogic> function
1865 =head1 ACKNOWLEDGEMENTS
1867 There are a number of individuals that have really helped out with
1868 this module. Unfortunately, most of them submitted bugs via CPAN
1869 so I have no idea who they are! But the people I do know are:
1871 Ash Berlin (order_by hash term support)
1872 Matt Trout (DBIx::Class support)
1873 Mark Stosberg (benchmarking)
1874 Chas Owens (initial "IN" operator support)
1875 Philip Collins (per-field SQL functions)
1876 Eric Kolve (hashref "AND" support)
1877 Mike Fragassi (enhancements to "BETWEEN" and "LIKE")
1878 Dan Kubb (support for "quote_char" and "name_sep")
1879 Guillermo Roditi (patch to cleanup "IN" and "BETWEEN", fix and tests for _order_by)
1880 Laurent Dami (internal refactoring, extensible list of special operators, literal SQL)
1881 Norbert Buchmuller (support for literal SQL in hashpair, misc. fixes & tests)
1882 Peter Rabbitson (rewrite of SQLA::Test, misc. fixes & tests)
1883 Oliver Charles (support for "RETURNING" after "INSERT")
1889 L<DBIx::Class>, L<DBIx::Abstract>, L<CGI::FormBuilder>, L<HTML::QuickTable>.
1893 Copyright (c) 2001-2007 Nathan Wiger <nwiger@cpan.org>. All Rights Reserved.
1895 This module is actively maintained by Matt Trout <mst@shadowcatsystems.co.uk>
1897 For support, your best bet is to try the C<DBIx::Class> users mailing list.
1898 While not an official support venue, C<DBIx::Class> makes heavy use of
1899 C<SQL::Abstract>, and as such list members there are very familiar with
1900 how to create queries.
1904 This module is free software; you may copy this under the same
1905 terms as perl itself (either the GNU General Public License or
1906 the Artistic License)