Страницы справочника OS X

Эта страница руководства для  версии 10.9 Mac OS X

Читать страницы руководства

SQL::Abstract(3)                     User Contributed Perl Documentation                    SQL::Abstract(3)



NAME
       SQL::Abstract - Generate SQL from Perl data structures

SYNOPSIS
           use SQL::Abstract;

           my $sql = SQL::Abstract->new;

           my($stmt, @bind) = $sql->select($table, \@fields, \%where, \@order);

           my($stmt, @bind) = $sql->insert($table, \%fieldvals || \@values);

           my($stmt, @bind) = $sql->update($table, \%fieldvals, \%where);

           my($stmt, @bind) = $sql->delete($table, \%where);

           # Then, use these in your DBI statements
           my $sth = $dbh->prepare($stmt);
           $sth->execute(@bind);

           # Just generate the WHERE clause
           my($stmt, @bind) = $sql->where(\%where, \@order);

           # Return values in the same order, for hashed queries
           # See PERFORMANCE section for more details
           my @bind = $sql->values(\%fieldvals);

DESCRIPTION
       This module was inspired by the excellent DBIx::Abstract.  However, in using that module I found that
       what I really wanted to do was generate SQL, but still retain complete control over my statement
       handles and use the DBI interface. So, I set out to create an abstract SQL generation module.

       While based on the concepts used by DBIx::Abstract, there are several important differences,
       especially when it comes to WHERE clauses. I have modified the concepts used to make the SQL easier
       to generate from Perl data structures and, IMO, more intuitive.  The underlying idea is for this
       module to do what you mean, based on the data structures you provide it. The big advantage is that
       you don't have to modify your code every time your data changes, as this module figures it out.

       To begin with, an SQL INSERT is as easy as just specifying a hash of "key=value" pairs:

           my %data = (
               name => 'Jimbo Bobson',
               phone => '123-456-7890',
               address => '42 Sister Lane',
               city => 'St. Louis',
               state => 'Louisiana',
           );

       The SQL can then be generated with this:

           my($stmt, @bind) = $sql->insert('people', \%data);

       Which would give you something like this:

           $stmt = "INSERT INTO people
                           (address, city, name, phone, state)
                           VALUES (?, ?, ?, ?, ?)";
           @bind = ('42 Sister Lane', 'St. Louis', 'Jimbo Bobson',
                    '123-456-7890', 'Louisiana');

       These are then used directly in your DBI code:

           my $sth = $dbh->prepare($stmt);
           $sth->execute(@bind);

   Inserting and Updating Arrays
       If your database has array types (like for example Postgres), activate the special option
       "array_datatypes => 1" when creating the "SQL::Abstract" object.  Then you may use an arrayref to
       insert and update database array types:

           my $sql = SQL::Abstract->new(array_datatypes => 1);
           my %data = (
               planets => [qw/Mercury Venus Earth Mars/]
           );

           my($stmt, @bind) = $sql->insert('solar_system', \%data);

       This results in:

           $stmt = "INSERT INTO solar_system (planets) VALUES (?)"

           @bind = (['Mercury', 'Venus', 'Earth', 'Mars']);

   Inserting and Updating SQL
       In order to apply SQL functions to elements of your %data you may specify a reference to an arrayref
       for the given hash value. For example, if you need to execute the Oracle "to_date" function on a
       value, you can say something like this:

           my %data = (
               name => 'Bill',
               date_entered => \["to_date(?,'MM/DD/YYYY')", "03/02/2003"],
           );

       The first value in the array is the actual SQL. Any other values are optional and would be included
       in the bind values array. This gives you:

           my($stmt, @bind) = $sql->insert('people', \%data);

           $stmt = "INSERT INTO people (name, date_entered)
                       VALUES (?, to_date(?,'MM/DD/YYYY'))";
           @bind = ('Bill', '03/02/2003');

       An UPDATE is just as easy, all you change is the name of the function:

           my($stmt, @bind) = $sql->update('people', \%data);

       Notice that your %data isn't touched; the module will generate the appropriately quirky SQL for you
       automatically. Usually you'll want to specify a WHERE clause for your UPDATE, though, which is where
       handling %where hashes comes in handy...

   Complex where statements
       This module can generate pretty complicated WHERE statements easily. For example, simple "key=value"
       pairs are taken to mean equality, and if you want to see if a field is within a set of values, you
       can use an arrayref. Let's say we wanted to SELECT some data based on this criteria:

           my %where = (
              requestor => 'inna',
              worker => ['nwiger', 'rcwe', 'sfz'],
              status => { '!=', 'completed' }
           );

           my($stmt, @bind) = $sql->select('tickets', '*', \%where);

       The above would give you something like this:

           $stmt = "SELECT * FROM tickets WHERE
                       ( requestor = ? ) AND ( status != ? )
                       AND ( worker = ? OR worker = ? OR worker = ? )";
           @bind = ('inna', 'completed', 'nwiger', 'rcwe', 'sfz');

       Which you could then use in DBI code like so:

           my $sth = $dbh->prepare($stmt);
           $sth->execute(@bind);

       Easy, eh?

FUNCTIONS
       The functions are simple. There's one for each major SQL operation, and a constructor you use first.
       The arguments are specified in a similar order to each function (table, then fields, then a where
       clause) to try and simplify things.

   new(option => 'value')
       The "new()" function takes a list of options and values, and returns a new SQL::Abstract object which
       can then be used to generate SQL through the methods below. The options accepted are:

       case
           If set to 'lower', then SQL will be generated in all lowercase. By default SQL is generated in
           "textbook" case meaning something like:

               SELECT a_field FROM a_table WHERE some_field LIKE '%someval%'

           Any setting other than 'lower' is ignored.

       cmp This determines what the default comparison operator is. By default it is "=", meaning that a
           hash like this:

               %where = (name => 'nwiger', email => 'nate@wiger.org');

           Will generate SQL like this:

               WHERE name = 'nwiger' AND email = 'nate@wiger.org'

           However, you may want loose comparisons by default, so if you set "cmp" to "like" you would get
           SQL such as:

               WHERE name like 'nwiger' AND email like 'nate@wiger.org'

           You can also override the comparsion on an individual basis - see the huge section on "WHERE
           CLAUSES" at the bottom.

       sqltrue, sqlfalse
           Expressions for inserting boolean values within SQL statements.  By default these are "1=1" and
           "1=0". They are used by the special operators "-in" and "-not_in" for generating correct SQL even
           when the argument is an empty array (see below).

       logic
           This determines the default logical operator for multiple WHERE statements in arrays or hashes.
           If absent, the default logic is "or" for arrays, and "and" for hashes. This means that a WHERE
           array of the form:

               @where = (
                   event_date => {'>=', '2/13/99'},
                   event_date => {'<=', '4/24/03'},
               );

           will generate SQL like this:

               WHERE event_date >= '2/13/99' OR event_date <= '4/24/03'

           This is probably not what you want given this query, though (look at the dates). To change the
           "OR" to an "AND", simply specify:

               my $sql = SQL::Abstract->new(logic => 'and');

           Which will change the above "WHERE" to:

               WHERE event_date >= '2/13/99' AND event_date <= '4/24/03'

           The logic can also be changed locally by inserting a modifier in front of an arrayref :

               @where = (-and => [event_date => {'>=', '2/13/99'},
                                  event_date => {'<=', '4/24/03'} ]);

           See the "WHERE CLAUSES" section for explanations.

       convert
           This will automatically convert comparisons using the specified SQL function for both column and
           value. This is mostly used with an argument of "upper" or "lower", so that the SQL will have the
           effect of case-insensitive "searches". For example, this:

               $sql = SQL::Abstract->new(convert => 'upper');
               %where = (keywords => 'MaKe iT CAse inSeNSItive');

           Will turn out the following SQL:

               WHERE upper(keywords) like upper('MaKe iT CAse inSeNSItive')

           The conversion can be "upper()", "lower()", or any other SQL function that can be applied
           symmetrically to fields (actually SQL::Abstract does not validate this option; it will just pass
           through what you specify verbatim).

       bindtype
           This is a kludge because many databases suck. For example, you can't just bind values using DBI's
           "execute()" for Oracle "CLOB" or "BLOB" fields.  Instead, you have to use "bind_param()":

               $sth->bind_param(1, 'reg data');
               $sth->bind_param(2, $lots, {ora_type => ORA_CLOB});

           The problem is, SQL::Abstract will normally just return a @bind array, which loses track of which
           field each slot refers to. Fear not.

           If you specify "bindtype" in new, you can determine how @bind is returned.  Currently, you can
           specify either "normal" (default) or "columns". If you specify "columns", you will get an array
           that looks like this:

               my $sql = SQL::Abstract->new(bindtype => 'columns');
               my($stmt, @bind) = $sql->insert(...);

               @bind = (
                   [ 'column1', 'value1' ],
                   [ 'column2', 'value2' ],
                   [ 'column3', 'value3' ],
               );

           You can then iterate through this manually, using DBI's "bind_param()".

               $sth->prepare($stmt);
               my $i = 1;
               for (@bind) {
                   my($col, $data) = @$_;
                   if ($col eq 'details' || $col eq 'comments') {
                       $sth->bind_param($i, $data, {ora_type => ORA_CLOB});
                   } elsif ($col eq 'image') {
                       $sth->bind_param($i, $data, {ora_type => ORA_BLOB});
                   } else {
                       $sth->bind_param($i, $data);
                   }
                   $i++;
               }
               $sth->execute;      # execute without @bind now

           Now, why would you still use SQL::Abstract if you have to do this crap?  Basically, the advantage
           is still that you don't have to care which fields are or are not included. You could wrap that
           above "for" loop in a simple sub called "bind_fields()" or something and reuse it repeatedly. You
           still get a layer of abstraction over manual SQL specification.

           Note that if you set "bindtype" to "columns", the "\[$sql, @bind]" construct (see "Literal SQL
           with placeholders and bind values (subqueries)") will expect the bind values in this format.

       quote_char
           This is the character that a table or column name will be quoted with.  By default this is an
           empty string, but you could set it to the character "`", to generate SQL like this:

             SELECT `a_field` FROM `a_table` WHERE `some_field` LIKE '%someval%'

           Alternatively, you can supply an array ref of two items, the first being the left hand quote
           character, and the second the right hand quote character. For example, you could supply
           "['[',']']" for SQL Server 2000 compliant quotes that generates SQL like this:

             SELECT [a_field] FROM [a_table] WHERE [some_field] LIKE '%someval%'

           Quoting is useful if you have tables or columns names that are reserved words in your database's
           SQL dialect.

       name_sep
           This is the character that separates a table and column name.  It is necessary to specify this
           when the "quote_char" option is selected, so that tables and column names can be individually
           quoted like this:

             SELECT `table`.`one_field` FROM `table` WHERE `table`.`other_field` = 1

       injection_guard
           A regular expression "qr/.../" that is applied to any "-function" and unquoted column name
           specified in a query structure. This is a safety mechanism to avoid injection attacks when
           mishandling user input e.g.:

             my %condition_as_column_value_pairs = get_values_from_user();
             $sqla->select( ... , \%condition_as_column_value_pairs );

           If the expression matches an exception is thrown. Note that literal SQL supplied via "\'...'" or
           "\['...']" is not checked in any way.

           Defaults to checking for ";" and the "GO" keyword (TransactSQL)

       array_datatypes
           When this option is true, arrayrefs in INSERT or UPDATE are interpreted as array datatypes and
           are passed directly to the DBI layer.  When this option is false, arrayrefs are interpreted as
           literal SQL, just like refs to arrayrefs (but this behavior is for backwards compatibility; when
           writing new queries, use the "reference to arrayref" syntax for literal SQL).

       special_ops
           Takes a reference to a list of "special operators" to extend the syntax understood by
           SQL::Abstract.  See section "SPECIAL OPERATORS" for details.

       unary_ops
           Takes a reference to a list of "unary operators" to extend the syntax understood by
           SQL::Abstract.  See section "UNARY OPERATORS" for details.

   insert($table, \@values || \%fieldvals, \%options)
       This is the simplest function. You simply give it a table name and either an arrayref of values or
       hashref of field/value pairs.  It returns an SQL INSERT statement and a list of bind values.  See the
       sections on "Inserting and Updating Arrays" and "Inserting and Updating SQL" for information on how
       to insert with those data types.

       The optional "\%options" hash reference may contain additional options to generate the insert SQL.
       Currently supported options are:

       returning
           Takes either a scalar of raw SQL fields, or an array reference of field names, and adds on an SQL
           "RETURNING" statement at the end.  This allows you to return data generated by the insert
           statement (such as row IDs) without performing another "SELECT" statement.  Note, however, this
           is not part of the SQL standard and may not be supported by all database engines.

   update($table, \%fieldvals, \%where)
       This takes a table, hashref of field/value pairs, and an optional hashref WHERE clause. It returns an
       SQL UPDATE function and a list of bind values.  See the sections on "Inserting and Updating Arrays"
       and "Inserting and Updating SQL" for information on how to insert with those data types.

   select($source, $fields, $where, $order)
       This returns a SQL SELECT statement and associated list of bind values, as specified by the arguments
       :

       $source
           Specification of the 'FROM' part of the statement.  The argument can be either a plain scalar
           (interpreted as a table name, will be quoted), or an arrayref (interpreted as a list of table
           names, joined by commas, quoted), or a scalarref (literal table name, not quoted), or a ref to an
           arrayref (list of literal table names, joined by commas, not quoted).

       $fields
           Specification of the list of fields to retrieve from the source.  The argument can be either an
           arrayref (interpreted as a list of field names, will be joined by commas and quoted), or a plain
           scalar (literal SQL, not quoted).  Please observe that this API is not as flexible as for the
           first argument $table, for backwards compatibility reasons.

       $where
           Optional argument to specify the WHERE part of the query.  The argument is most often a hashref,
           but can also be an arrayref or plain scalar -- see section WHERE clause for details.

       $order
           Optional argument to specify the ORDER BY part of the query.  The argument can be a scalar, a
           hashref or an arrayref -- see section ORDER BY clause for details.

   delete($table, \%where)
       This takes a table name and optional hashref WHERE clause.  It returns an SQL DELETE statement and
       list of bind values.

   where(\%where, \@order)
       This is used to generate just the WHERE clause. For example, if you have an arbitrary data structure
       and know what the rest of your SQL is going to look like, but want an easy way to produce a WHERE
       clause, use this. It returns an SQL WHERE clause and list of bind values.

   values(\%data)
       This just returns the values from the hash %data, in the same order that would be returned from any
       of the other above queries.  Using this allows you to markedly speed up your queries if you are
       affecting lots of rows. See below under the "PERFORMANCE" section.

   generate($any, 'number', $of, \@data, $struct, \%types)
       Warning: This is an experimental method and subject to change.

       This returns arbitrarily generated SQL. It's a really basic shortcut.  It will return two different
       things, depending on return context:

           my($stmt, @bind) = $sql->generate('create table', \$table, \@fields);
           my $stmt_and_val = $sql->generate('create table', \$table, \@fields);

       These would return the following:

           # First calling form
           $stmt = "CREATE TABLE test (?, ?)";
           @bind = (field1, field2);

           # Second calling form
           $stmt_and_val = "CREATE TABLE test (field1, field2)";

       Depending on what you're trying to do, it's up to you to choose the correct format. In this example,
       the second form is what you would want.

       By the same token:

           $sql->generate('alter session', { nls_date_format => 'MM/YY' });

       Might give you:

           ALTER SESSION SET nls_date_format = 'MM/YY'

       You get the idea. Strings get their case twiddled, but everything else remains verbatim.

WHERE CLAUSES
   Introduction
       This module uses a variation on the idea from DBIx::Abstract. It is NOT, repeat not 100% compatible.
       The main logic of this module is that things in arrays are OR'ed, and things in hashes are AND'ed.

       The easiest way to explain is to show lots of examples. After each %where hash shown, it is assumed
       you used:

           my($stmt, @bind) = $sql->where(\%where);

       However, note that the %where hash can be used directly in any of the other functions as well, as
       described above.

   Key-value pairs
       So, let's get started. To begin, a simple hash:

           my %where  = (
               user   => 'nwiger',
               status => 'completed'
           );

       Is converted to SQL "key = val" statements:

           $stmt = "WHERE user = ? AND status = ?";
           @bind = ('nwiger', 'completed');

       One common thing I end up doing is having a list of values that a field can be in. To do this, simply
       specify a list inside of an arrayref:

           my %where  = (
               user   => 'nwiger',
               status => ['assigned', 'in-progress', 'pending'];
           );

       This simple code will create the following:

           $stmt = "WHERE user = ? AND ( status = ? OR status = ? OR status = ? )";
           @bind = ('nwiger', 'assigned', 'in-progress', 'pending');

       A field associated to an empty arrayref will be considered a logical false and will generate 0=1.

   Tests for NULL values
       If the value part is "undef" then this is converted to SQL <IS NULL>

           my %where  = (
               user   => 'nwiger',
               status => undef,
           );

       becomes:

           $stmt = "WHERE user = ? AND status IS NULL";
           @bind = ('nwiger');

       To test if a column IS NOT NULL:

           my %where  = (
               user   => 'nwiger',
               status => { '!=', undef },
           );

   Specific comparison operators
       If you want to specify a different type of operator for your comparison, you can use a hashref for a
       given column:

           my %where  = (
               user   => 'nwiger',
               status => { '!=', 'completed' }
           );

       Which would generate:

           $stmt = "WHERE user = ? AND status != ?";
           @bind = ('nwiger', 'completed');

       To test against multiple values, just enclose the values in an arrayref:

           status => { '=', ['assigned', 'in-progress', 'pending'] };

       Which would give you:

           "WHERE status = ? OR status = ? OR status = ?"

       The hashref can also contain multiple pairs, in which case it is expanded into an "AND" of its
       elements:

           my %where  = (
               user   => 'nwiger',
               status => { '!=', 'completed', -not_like => 'pending%' }
           );

           # Or more dynamically, like from a form
           $where{user} = 'nwiger';
           $where{status}{'!='} = 'completed';
           $where{status}{'-not_like'} = 'pending%';

           # Both generate this
           $stmt = "WHERE user = ? AND status != ? AND status NOT LIKE ?";
           @bind = ('nwiger', 'completed', 'pending%');

       To get an OR instead, you can combine it with the arrayref idea:

           my %where => (
                user => 'nwiger',
                priority => [ { '=', 2 }, { '>', 5 } ]
           );

       Which would generate:

           $stmt = "WHERE ( priority = ? OR priority > ? ) AND user = ?";
           @bind = ('2', '5', 'nwiger');

       If you want to include literal SQL (with or without bind values), just use a scalar reference or
       array reference as the value:

           my %where  = (
               date_entered => { '>' => \["to_date(?, 'MM/DD/YYYY')", "11/26/2008"] },
               date_expires => { '<' => \"now()" }
           );

       Which would generate:

           $stmt = "WHERE date_entered > "to_date(?, 'MM/DD/YYYY') AND date_expires < now()";
           @bind = ('11/26/2008');

   Logic and nesting operators
       In the example above, there is a subtle trap if you want to say something like this (notice the
       "AND"):

           WHERE priority != ? AND priority != ?

       Because, in Perl you can't do this:

           priority => { '!=', 2, '!=', 1 }

       As the second "!=" key will obliterate the first. The solution is to use the special "-modifier" form
       inside an arrayref:

           priority => [ -and => {'!=', 2},
                                 {'!=', 1} ]

       Normally, these would be joined by "OR", but the modifier tells it to use "AND" instead. (Hint: You
       can use this in conjunction with the "logic" option to "new()" in order to change the way your
       queries work by default.) Important: Note that the "-modifier" goes INSIDE the arrayref, as an extra
       first element. This will NOT do what you think it might:

           priority => -and => [{'!=', 2}, {'!=', 1}]   # WRONG!

       Here is a quick list of equivalencies, since there is some overlap:

           # Same
           status => {'!=', 'completed', 'not like', 'pending%' }
           status => [ -and => {'!=', 'completed'}, {'not like', 'pending%'}]

           # Same
           status => {'=', ['assigned', 'in-progress']}
           status => [ -or => {'=', 'assigned'}, {'=', 'in-progress'}]
           status => [ {'=', 'assigned'}, {'=', 'in-progress'} ]

   Special operators : IN, BETWEEN, etc.
       You can also use the hashref format to compare a list of fields using the "IN" comparison operator,
       by specifying the list as an arrayref:

           my %where  = (
               status   => 'completed',
               reportid => { -in => [567, 2335, 2] }
           );

       Which would generate:

           $stmt = "WHERE status = ? AND reportid IN (?,?,?)";
           @bind = ('completed', '567', '2335', '2');

       The reverse operator "-not_in" generates SQL "NOT IN" and is used in the same way.

       If the argument to "-in" is an empty array, 'sqlfalse' is generated (by default : "1=0"). Similarly,
       "-not_in => []" generates 'sqltrue' (by default : "1=1").

       In addition to the array you can supply a chunk of literal sql or literal sql with bind:

           my %where = {
             customer => { -in => \[
               'SELECT cust_id FROM cust WHERE balance > ?',
               2000,
             ],
             status => { -in => \'SELECT status_codes FROM states' },
           };

       would generate:

           $stmt = "WHERE (
                 customer IN ( SELECT cust_id FROM cust WHERE balance > ? )
             AND status IN ( SELECT status_codes FROM states )
           )";
           @bind = ('2000');

       Another pair of operators is "-between" and "-not_between", used with an arrayref of two values:

           my %where  = (
               user   => 'nwiger',
               completion_date => {
                  -not_between => ['2002-10-01', '2003-02-06']
               }
           );

       Would give you:

           WHERE user = ? AND completion_date NOT BETWEEN ( ? AND ? )

       Just like with "-in" all plausible combinations of literal SQL are possible:

           my %where = {
             start0 => { -between => [ 1, 2 ] },
             start1 => { -between => \["? AND ?", 1, 2] },
             start2 => { -between => \"lower(x) AND upper(y)" },
             start3 => { -between => [
               \"lower(x)",
               \["upper(?)", 'stuff' ],
             ] },
           };

       Would give you:

           $stmt = "WHERE (
                 ( start0 BETWEEN ? AND ?                )
             AND ( start1 BETWEEN ? AND ?                )
             AND ( start2 BETWEEN lower(x) AND upper(y)  )
             AND ( start3 BETWEEN lower(x) AND upper(?)  )
           )";
           @bind = (1, 2, 1, 2, 'stuff');

       These are the two builtin "special operators"; but the list can be expanded : see section "SPECIAL
       OPERATORS" below.

   Unary operators: bool
       If you wish to test against boolean columns or functions within your database you can use the "-bool"
       and "-not_bool" operators. For example to test the column "is_user" being true and the column
       "is_enabled" being false you would use:-my use:my

           my %where  = (
               -bool       => 'is_user',
               -not_bool   => 'is_enabled',
           );

       Would give you:

           WHERE is_user AND NOT is_enabled

       If a more complex combination is required, testing more conditions, then you should use the and/or
       operators:-my operators:my

           my %where  = (
               -and           => [
                   -bool      => 'one',
                   -bool      => 'two',
                   -bool      => 'three',
                   -not_bool  => 'four',
               ],
           );

       Would give you:

           WHERE one AND two AND three AND NOT four

   Nested conditions, -and/-or prefixes
       So far, we've seen how multiple conditions are joined with a top-level "AND".  We can change this by
       putting the different conditions we want in hashes and then putting those hashes in an array. For
       example:

           my @where = (
               {
                   user   => 'nwiger',
                   status => { -like => ['pending%', 'dispatched'] },
               },
               {
                   user   => 'robot',
                   status => 'unassigned',
               }
           );

       This data structure would create the following:

           $stmt = "WHERE ( user = ? AND ( status LIKE ? OR status LIKE ? ) )
                       OR ( user = ? AND status = ? ) )";
           @bind = ('nwiger', 'pending', 'dispatched', 'robot', 'unassigned');

       Clauses in hashrefs or arrayrefs can be prefixed with an "-and" or "-or" to change the logic inside :

           my @where = (
                -and => [
                   user => 'nwiger',
                   [
                       -and => [ workhrs => {'>', 20}, geo => 'ASIA' ],
                       -or => { workhrs => {'<', 50}, geo => 'EURO' },
                   ],
               ],
           );

       That would yield:

           WHERE ( user = ? AND (
                      ( workhrs > ? AND geo = ? )
                   OR ( workhrs < ? OR geo = ? )
                 ) )

       Algebraic inconsistency, for historical reasons

       "Important note": when connecting several conditions, the "-and-"|"-or" operator goes "outside" of
       the nested structure; whereas when connecting several constraints on one column, the "-and" operator
       goes "inside" the arrayref. Here is an example combining both features :

          my @where = (
            -and => [a => 1, b => 2],
            -or  => [c => 3, d => 4],
             e   => [-and => {-like => 'foo%'}, {-like => '%bar'} ]
          )

       yielding

         WHERE ( (    ( a = ? AND b = ? )
                   OR ( c = ? OR d = ? )
                   OR ( e LIKE ? AND e LIKE ? ) ) )

       This difference in syntax is unfortunate but must be preserved for historical reasons. So be careful
       : the two examples below would seem algebraically equivalent, but they are not

         {col => [-and => {-like => 'foo%'}, {-like => '%bar'}]}
         # yields : WHERE ( ( col LIKE ? AND col LIKE ? ) )

         [-and => {col => {-like => 'foo%'}, {col => {-like => '%bar'}}]]
         # yields : WHERE ( ( col LIKE ? OR col LIKE ? ) )

   Literal SQL and value type operators
       The basic premise of SQL::Abstract is that in WHERE specifications the "left side" is a column name
       and the "right side" is a value (normally rendered as a placeholder). This holds true for both
       hashrefs and arrayref pairs as you see in the "WHERE CLAUSES" examples above. Sometimes it is
       necessary to alter this behavior. There are several ways of doing so.

       -ident

       This is a virtual operator that signals the string to its right side is an identifier (a column name)
       and not a value. For example to compare two columns you would write:

           my %where = (
               priority => { '<', 2 },
               requestor => { -ident => 'submitter' },
           );

       which creates:

           $stmt = "WHERE priority < ? AND requestor = submitter";
           @bind = ('2');

       If you are maintaining legacy code you may see a different construct as described in "Deprecated
       usage of Literal SQL", please use "-ident" in new code.

       -value

       This is a virtual operator that signals that the construct to its right side is a value to be passed
       to DBI. This is for example necessary when you want to write a where clause against an array (for
       RDBMS that support such datatypes). For example:

           my %where = (
               array => { -value => [1, 2, 3] }
           );

       will result in:

           $stmt = 'WHERE array = ?';
           @bind = ([1, 2, 3]);

       Note that if you were to simply say:

           my %where = (
               array => [1, 2, 3]
           );

       the result would porbably be not what you wanted:

           $stmt = 'WHERE array = ? OR array = ? OR array = ?';
           @bind = (1, 2, 3);

       Literal SQL

       Finally, sometimes only literal SQL will do. To include a random snippet of SQL verbatim, you specify
       it as a scalar reference. Consider this only as a last resort. Usually there is a better way. For
       example:

           my %where = (
               priority => { '<', 2 },
               requestor => { -in => \'(SELECT name FROM hitmen)' },
           );

       Would create:

           $stmt = "WHERE priority < ? AND requestor IN (SELECT name FROM hitmen)"
           @bind = (2);

       Note that in this example, you only get one bind parameter back, since the verbatim SQL is passed as
       part of the statement.

       CAVEAT

         Never use untrusted input as a literal SQL argument - this is a massive
         security risk (there is no way to check literal snippets for SQL
         injections and other nastyness). If you need to deal with untrusted input
         use literal SQL with placeholders as described next.

       Literal SQL with placeholders and bind values (subqueries)

       If the literal SQL to be inserted has placeholders and bind values, use a reference to an arrayref
       (yes this is a double reference -- not so common, but perfectly legal Perl). For example, to find a
       date in Postgres you can use something like this:

           my %where = (
              date_column => \[q/= date '2008-09-30' - ?::integer/, 10/]
           )

       This would create:

           $stmt = "WHERE ( date_column = date '2008-09-30' - ?::integer )"
           @bind = ('10');

       Note that you must pass the bind values in the same format as they are returned by "where". That
       means that if you set "bindtype" to "columns", you must provide the bind values in the "[ column_meta
       => value ]" format, where "column_meta" is an opaque scalar value; most commonly the column name, but
       you can use any scalar value (including references and blessed references), SQL::Abstract will simply
       pass it through intact. So if "bindtype" is set to "columns" the above example will look like:

           my %where = (
              date_column => \[q/= date '2008-09-30' - ?::integer/, [ dummy => 10 ]/]
           )

       Literal SQL is especially useful for nesting parenthesized clauses in the main SQL query. Here is a
       first example :

         my ($sub_stmt, @sub_bind) = ("SELECT c1 FROM t1 WHERE c2 < ? AND c3 LIKE ?",
                                      100, "foo%");
         my %where = (
           foo => 1234,
           bar => \["IN ($sub_stmt)" => @sub_bind],
         );

       This yields :

         $stmt = "WHERE (foo = ? AND bar IN (SELECT c1 FROM t1
                                                    WHERE c2 < ? AND c3 LIKE ?))";
         @bind = (1234, 100, "foo%");

       Other subquery operators, like for example "> ALL" or "NOT IN", are expressed in the same way. Of
       course the $sub_stmt and its associated bind values can be generated through a former call to
       "select()" :

         my ($sub_stmt, @sub_bind)
            = $sql->select("t1", "c1", {c2 => {"<" => 100},
                                        c3 => {-like => "foo%"}});
         my %where = (
           foo => 1234,
           bar => \["> ALL ($sub_stmt)" => @sub_bind],
         );

       In the examples above, the subquery was used as an operator on a column; but the same principle also
       applies for a clause within the main %where hash, like an EXISTS subquery :

         my ($sub_stmt, @sub_bind)
            = $sql->select("t1", "*", {c1 => 1, c2 => \"> t0.c0"});
         my %where = ( -and => [
           foo   => 1234,
           \["EXISTS ($sub_stmt)" => @sub_bind],
         ]);

       which yields

         $stmt = "WHERE (foo = ? AND EXISTS (SELECT * FROM t1
                                               WHERE c1 = ? AND c2 > t0.c0))";
         @bind = (1234, 1);

       Observe that the condition on "c2" in the subquery refers to column "t0.c0" of the main query : this
       is not a bind value, so we have to express it through a scalar ref.  Writing "c2 => {">" => "t0.c0"}"
       would have generated "c2 > ?" with bind value "t0.c0" ... not exactly what we wanted here.

       Finally, here is an example where a subquery is used for expressing unary negation:

         my ($sub_stmt, @sub_bind)
            = $sql->where({age => [{"<" => 10}, {">" => 20}]});
         $sub_stmt =~ s/^ where //i; # don't want "WHERE" in the subclause
         my %where = (
               lname  => {like => '%son%'},
               \["NOT ($sub_stmt)" => @sub_bind],
           );

       This yields

         $stmt = "lname LIKE ? AND NOT ( age < ? OR age > ? )"
         @bind = ('%son%', 10, 20)

       Deprecated usage of Literal SQL

       Below are some examples of archaic use of literal SQL. It is shown only as reference for those who
       deal with legacy code. Each example has a much better, cleaner and safer alternative that users
       should opt for in new code.

       •

               my %where = ( requestor => \'IS NOT NULL' )

               $stmt = "WHERE requestor IS NOT NULL"

           This used to be the way of generating NULL comparisons, before the handling of "undef" got
           formalized. For new code please use the superior syntax as described in "Tests for NULL values".

       •

               my %where = ( requestor => \'= submitter' )

               $stmt = "WHERE requestor = submitter"

           This used to be the only way to compare columns. Use the superior "-ident" method for all new
           code. For example an identifier declared in such a way will be properly quoted if "quote_char" is
           properly set, while the legacy form will remain as supplied.

       •

               my %where = ( is_ready  => \"", completed => { '>', '2012-12-21' } )

               $stmt = "WHERE completed > ? AND is_ready"
               @bind = ('2012-12-21')

           Using an empty string literal used to be the only way to express a boolean.  For all new code
           please use the much more readable -bool operator.

   Conclusion
       These pages could go on for a while, since the nesting of the data structures this module can handle
       are pretty much unlimited (the module implements the "WHERE" expansion as a recursive function
       internally). Your best bet is to "play around" with the module a little to see how the data
       structures behave, and choose the best format for your data based on that.

       And of course, all the values above will probably be replaced with variables gotten from forms or the
       command line. After all, if you knew everything ahead of time, you wouldn't have to worry about
       dynamically-generating SQL and could just hardwire it into your script.

ORDER BY CLAUSES
       Some functions take an order by clause. This can either be a scalar (just a column name,) a hash of
       "{ -desc => 'col' }" or "{ -asc => 'col' }", or an array of either of the two previous forms.
       Examples:

                      Given            |         Will Generate
           ----------------------------------------------------------| ---------------------------------------------------------|
                                       |
           \'colA DESC'                | ORDER BY colA DESC
                                       |
           'colA'                      | ORDER BY colA
                                       |
           [qw/colA colB/]             | ORDER BY colA, colB
                                       |
           {-asc  => 'colA'}           | ORDER BY colA ASC
                                       |
           {-desc => 'colB'}           | ORDER BY colB DESC
                                       |
           ['colA', {-asc => 'colB'}]  | ORDER BY colA, colB ASC
                                       |
           { -asc => [qw/colA colB/] } | ORDER BY colA ASC, colB ASC
                                       |
           [                           |
             { -asc => 'colA' },       | ORDER BY colA ASC, colB DESC,
             { -desc => [qw/colB/],    |          colC ASC, colD ASC
             { -asc => [qw/colC colD/],|
           ]                           |
           ===========================================================

SPECIAL OPERATORS
         my $sqlmaker = SQL::Abstract->new(special_ops => [
            {
             regex => qr/.../,
             handler => sub {
               my ($self, $field, $op, $arg) = @_;
               ...
             },
            },
            {
             regex => qr/.../,
             handler => 'method_name',
            },
          ]);

       A "special operator" is a SQL syntactic clause that can be applied to a field, instead of a usual
       binary operator.  For example :

          WHERE field IN (?, ?, ?)
          WHERE field BETWEEN ? AND ?
          WHERE MATCH(field) AGAINST (?, ?)

       Special operators IN and BETWEEN are fairly standard and therefore are builtin within "SQL::Abstract"
       (as the overridable methods "_where_field_IN" and "_where_field_BETWEEN"). For other operators, like
       the MATCH .. AGAINST example above which is specific to MySQL, you can write your own operator
       handlers - supply a "special_ops" argument to the "new" method. That argument takes an arrayref of
       operator definitions; each operator definition is a hashref with two entries:

       regex
           the regular expression to match the operator

       handler
           Either a coderef or a plain scalar method name. In both cases the expected return is "($sql,
           @bind)".

           When supplied with a method name, it is simply called on the "" in SQL::Abstract object as:

            $self->$method_name ($field, $op, $arg)

            Where:

             $op is the part that matched the handler regex
             $field is the LHS of the operator
             $arg is the RHS

           When supplied with a coderef, it is called as:

            $coderef->($self, $field, $op, $arg)

       For example, here is an implementation of the MATCH .. AGAINST syntax for MySQL

         my $sqlmaker = SQL::Abstract->new(special_ops => [

           # special op for MySql MATCH (field) AGAINST(word1, word2, ...)
           {regex => qr/^match$/i,
            handler => sub {
              my ($self, $field, $op, $arg) = @_;
              $arg = [$arg] if not ref $arg;
              my $label         = $self->_quote($field);
              my ($placeholder) = $self->_convert('?');
              my $placeholders  = join ", ", (($placeholder) x @$arg);
              my $sql           = $self->_sqlcase('match') . " ($label) "
                                . $self->_sqlcase('against') . " ($placeholders) ";
              my @bind = $self->_bindtype($field, @$arg);
              return ($sql, @bind);
              }
            },

         ]);

UNARY OPERATORS
         my $sqlmaker = SQL::Abstract->new(unary_ops => [
            {
             regex => qr/.../,
             handler => sub {
               my ($self, $op, $arg) = @_;
               ...
             },
            },
            {
             regex => qr/.../,
             handler => 'method_name',
            },
          ]);

       A "unary operator" is a SQL syntactic clause that can be applied to a field - the operator goes
       before the field

       You can write your own operator handlers - supply a "unary_ops" argument to the "new" method. That
       argument takes an arrayref of operator definitions; each operator definition is a hashref with two
       entries:

       regex
           the regular expression to match the operator

       handler
           Either a coderef or a plain scalar method name. In both cases the expected return is $sql.

           When supplied with a method name, it is simply called on the "" in SQL::Abstract object as:

            $self->$method_name ($op, $arg)

            Where:

             $op is the part that matched the handler regex
             $arg is the RHS or argument of the operator

           When supplied with a coderef, it is called as:

            $coderef->($self, $op, $arg)

PERFORMANCE
       Thanks to some benchmarking by Mark Stosberg, it turns out that this module is many orders of
       magnitude faster than using "DBIx::Abstract".  I must admit this wasn't an intentional design issue,
       but it's a byproduct of the fact that you get to control your "DBI" handles yourself.

       To maximize performance, use a code snippet like the following:

           # prepare a statement handle using the first row
           # and then reuse it for the rest of the rows
           my($sth, $stmt);
           for my $href (@array_of_hashrefs) {
               $stmt ||= $sql->insert('table', $href);
               $sth  ||= $dbh->prepare($stmt);
               $sth->execute($sql->values($href));
           }

       The reason this works is because the keys in your $href are sorted internally by SQL::Abstract. Thus,
       as long as your data retains the same structure, you only have to generate the SQL the first time
       around. On subsequent queries, simply use the "values" function provided by this module to return
       your values in the correct order.

       However this depends on the values having the same type - if, for example, the values of a where
       clause may either have values (resulting in sql of the form "column = ?" with a single bind value),
       or alternatively the values might be "undef" (resulting in sql of the form "column IS NULL" with no
       bind value) then the caching technique suggested will not work.

FORMBUILDER
       If you use my "CGI::FormBuilder" module at all, you'll hopefully really like this part (I do, at
       least). Building up a complex query can be as simple as the following:

           #!/usr/bin/perl

           use CGI::FormBuilder;
           use SQL::Abstract;

           my $form = CGI::FormBuilder->new(...);
           my $sql  = SQL::Abstract->new;

           if ($form->submitted) {
               my $field = $form->field;
               my $id = delete $field->{id};
               my($stmt, @bind) = $sql->update('table', $field, {id => $id});
           }

       Of course, you would still have to connect using "DBI" to run the query, but the point is that if you
       make your form look like your table, the actual query script can be extremely simplistic.

       If you're REALLY lazy (I am), check out "HTML::QuickTable" for a fast interface to returning and
       formatting data. I frequently use these three modules together to write complex database query apps
       in under 50 lines.

REPO
       •   gitweb: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=dbsrgits/SQL-Abstract.git
           <http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=dbsrgits/SQL-Abstract.git>

       •   git: git://git.shadowcat.co.uk/dbsrgits/SQL-Abstract.git <git://git.shadowcat.co.uk/dbsrgits/SQL-
           Abstract.git>

CHANGES
       Version 1.50 was a major internal refactoring of "SQL::Abstract".  Great care has been taken to
       preserve the published behavior documented in previous versions in the 1.* family; however, some
       features that were previously undocumented, or behaved differently from the documentation, had to be
       changed in order to clarify the semantics. Hence, client code that was relying on some dark areas of
       "SQL::Abstract" v1.*  might behave differently in v1.50.

       The main changes are :

       •   support for literal SQL through the "\ [$sql, bind]" syntax.

       •   support for the { operator => \"..." } construct (to embed literal SQL)

       •   support for the { operator => \["...", @bind] } construct (to embed literal SQL with bind values)

       •   optional support for array datatypes

       •   defensive programming : check arguments

       •   fixed bug with global logic, which was previously implemented through global variables yielding
           side-effects. Prior versions would interpret "[ {cond1, cond2}, [cond3, cond4] ]" as "(cond1 AND
           cond2) OR (cond3 AND cond4)".  Now this is interpreted as "(cond1 AND cond2) OR (cond3 OR
           cond4)".

       •   fixed semantics of  _bindtype on array args

       •   dropped the "_anoncopy" of the %where tree. No longer necessary, we just avoid shifting arrays
           within that tree.

       •   dropped the "_modlogic" function

ACKNOWLEDGEMENTS
       There are a number of individuals that have really helped out with this module. Unfortunately, most
       of them submitted bugs via CPAN so I have no idea who they are! But the people I do know are:

           Ash Berlin (order_by hash term support)
           Matt Trout (DBIx::Class support)
           Mark Stosberg (benchmarking)
           Chas Owens (initial "IN" operator support)
           Philip Collins (per-field SQL functions)
           Eric Kolve (hashref "AND" support)
           Mike Fragassi (enhancements to "BETWEEN" and "LIKE")
           Dan Kubb (support for "quote_char" and "name_sep")
           Guillermo Roditi (patch to cleanup "IN" and "BETWEEN", fix and tests for _order_by)
           Laurent Dami (internal refactoring, extensible list of special operators, literal SQL)
           Norbert Buchmuller (support for literal SQL in hashpair, misc. fixes & tests)
           Peter Rabbitson (rewrite of SQLA::Test, misc. fixes & tests)
           Oliver Charles (support for "RETURNING" after "INSERT")

       Thanks!

SEE ALSO
       DBIx::Class, DBIx::Abstract, CGI::FormBuilder, HTML::QuickTable.

AUTHOR
       Copyright (c) 2001-2007 Nathan Wiger <nwiger@cpan.org>. All Rights Reserved.

       This module is actively maintained by Matt Trout <mst@shadowcatsystems.co.uk>

       For support, your best bet is to try the "DBIx::Class" users mailing list.  While not an official
       support venue, "DBIx::Class" makes heavy use of "SQL::Abstract", and as such list members there are
       very familiar with how to create queries.

LICENSE
       This module is free software; you may copy this under the same terms as perl itself (either the GNU
       General Public License or the Artistic License)



perl v5.16.2                                     2012-07-10                                 SQL::Abstract(3)