SQL::Abstract - Generate SQL from Perl data structures

        use SQL::Abstract;

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

        my($stmt, @bind) = $sql->select($source, \@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);

        # 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);

    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

    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);

  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

        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);

    Easy, eh?

    The methods are simple. There's one for every major SQL operation, and a
    constructor you use first. The arguments are specified in a similar
    order for each method (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:

        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 => '');

        Will generate SQL like this:

            WHERE name = 'nwiger' AND email = ''

        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 ''

        You can also override the comparison 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).

        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.

        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).

        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

        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

            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);
            $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.

        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.

        This is the character that will be used to escape "quote_char"s
        appearing in an identifier before it has been quoted.

        The parameter default in case of a single "quote_char" character is
        the quote character itself.

        When opening-closing-style quoting is used ("quote_char" is an
        arrayref) this parameter defaults to the closing (right)
        "quote_char". Occurrences of the opening (left) "quote_char" within
        the identifier are currently left untouched. The default for
        opening-closing-style quotes may change in future versions, thus you
        are strongly encouraged to specify the escape character explicitly.

        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

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

        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)

        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).

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

        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:

        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, \%options)
    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.

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

        See the "returning" option to insert.

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

        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 SQL, not quoted).

        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 that of the first argument $source, for backwards
        compatibility reasons.

        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.

        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, \%options)
    This takes a table name and optional hashref WHERE clause. It returns an
    SQL DELETE statement and list of bind values.

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

        See the "returning" option to insert.

  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.

    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.

    Determines if the supplied argument is a plain value as understood by
    this module:

    *   The value is "undef"

    *   The value is a non-reference

    *   The value is an object with stringification overloading

    *   The value is of the form "{ -value => $anything }"

    On failure returns "undef", on success returns a scalar reference to the
    original supplied argument.

    *   Note

        The stringification overloading detection is rather advanced: it
        takes into consideration not only the presence of a "" overload, but
        if that fails also checks for enabled autogenerated versions of "",
        based on either "0+" or "bool".

        Unfortunately testing in the field indicates that this detection may
        tickle a latent bug in perl versions before 5.018, but only when
        very large numbers of stringifying objects are involved. At the time
        of writing ( Sep 2014 ) there is no clear explanation of the direct
        cause, nor is there a manageably small test case that reliably
        reproduces the problem.

        If you encounter any of the following exceptions in random places
        within your application stack - this module may be to blame:

          Operation "ne": no method found,
            left argument in overloaded package <something>,
            right argument in overloaded package <something>

        or perhaps even

          Stub found while resolving method "???" overloading """" in package <something>

        If you fall victim to the above - please attempt to reduce the
        problem to something that could be sent to the SQL::Abstract
        developers (either publicly or privately). As a workaround in the
        meantime you can set
        value, which will most likely eliminate your problem (at the expense
        of not being able to properly detect exotic forms of

        This notice and environment variable will be removed in a future
        version, as soon as the underlying problem is found and a reliable
        workaround is devised.

    Determines if the supplied argument is a literal value as understood by
    this module:

    *   "\$sql_string"

    *   "\[ $sql_string, @bind_values ]"

    On failure returns "undef", on success returns an array reference
    containing the unpacked version of the supplied literal SQL and bind

    Tests for undef, whether expanded or not.

    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,


        $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 reference to an arrayref 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

        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 > ?',
          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');

    Finally, if the argument to "-in" is not a reference, it will be treated
    as a single-element array.

    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

        my %where = {
          start0 => { -between => [ 1, 2 ] },
          start1 => { -between => \["? AND ?", 1, 2] },
          start2 => { -between => \"lower(x) AND upper(y)" },
          start3 => { -between => [
            \["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 %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 %where  = (
            -and           => [
                -bool      => 'one',
                -not_bool  => { two=> { -rlike => 'bar' } },
                -not_bool  => { three => [ { '=', 2 }, { '>', 5 } ] },

    Would give you:

          (NOT two RLIKE ?)
          (NOT ( three = ? OR three > ? ))

  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:

        $stmt = "WHERE ( user = ?
                   AND ( ( workhrs > ? AND geo = ? )
                      OR ( workhrs < ? OR geo = ? ) ) )";
        @bind = ('nwiger', '20', 'ASIA', '50', 'EURO');

   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'} ]


      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.

    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.

    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 probably not be 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.

      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 => \[ "= 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. This 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 => \[ "= date '2008-09-30' - ?::integer", [ {} => 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

    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.

    Some functions take an order by clause. This can either be a scalar
    (just a column name), a hashref of "{ -desc => 'col' }" or "{ -asc =>
    'col' }", a scalarref, an arrayref-ref, or an arrayref of any of the
    previous forms. Examples:

                   Given              |         Will Generate
        '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
        \'colA DESC'                  | ORDER BY colA DESC
        \[ 'FUNC(colA, ?)', $x ]      | ORDER BY FUNC(colA, ?)
                                      |   /* ...with $x bound to ? */
        [                             | ORDER BY
          { -asc => 'colA' },         |     colA ASC,
          { -desc => [qw/colB/] },    |     colB DESC,
          { -asc => [qw/colC colD/] },|     colC ASC, colD ASC,
          \'colE DESC',               |     colE DESC,
          \[ 'FUNC(colF, ?)', $x ],   |     FUNC(colF, ?)
        ]                             |   /* ...with $x bound to ? */

      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:

        the regular expression to match the operator

        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
        SQL::Abstract object as:

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


          $field is the LHS of the operator
          $op is the part that matched the handler regex
          $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);


      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:

        the regular expression to match the operator

        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
        SQL::Abstract object as:

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


          $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)

    See SQL::Abstract::Reference for the "expr" versus "aqt" concept and an
    explanation of what the below extensions are extending.


    Enables plugin SQL::Abstract::Plugin::Foo.

      my ($sql, @bind) = $sqla->render_expr($expr);

    Use this if you may be rendering a top level statement so e.g. a SELECT
    query doesn't get wrapped in parens

      my ($sql, @bind) = $sqla->render_statement($expr);

    Expression expansion with optional default for scalars.

      my $aqt = $self->expand_expr($expr);
      my $aqt = $self->expand_expr($expr, -ident);

    Top level means avoid parens on statement AQT.

      my $res = $self->render_aqt($aqt, $top_level);
      my ($sql, @bind) = @$res;

    Similar to join() but will render hashrefs as nodes for both join and
    parts, and treats arrayref as a nested "[ $join, @parts ]" structure.

      my $part = $self->join_query_parts($join, @parts);

      my $sqla2 = $sqla->clone;

    Performs a semi-shallow copy such that extension methods won't leak
    state but excessive depth is avoided.

      $sqla->expander('name' => sub { ... });
      $sqla->expanders('name1' => sub { ... }, 'name2' => sub { ... });

      my @names = $sqla->expander_list;

      $sqla->wrap_expander('name' => sub { my ($orig) = @_; sub { ... } });
        'name1' => sub { my ($orig1) = @_; sub { ... } },
        'name2' => sub { my ($orig2) = @_; sub { ... } },

      $sqla->renderer('name' => sub { ... });
      $sqla->renderers('name1' => sub { ... }, 'name2' => sub { ... });

      my @names = $sqla->renderer_list;

      $sqla->wrap_renderer('name' => sub { my ($orig) = @_; sub { ... } });
        'name1' => sub { my ($orig1) = @_; sub { ... } },
        'name2' => sub { my ($orig2) = @_; sub { ... } },

      my @clauses = $sqla->clauses_of('select');
      $sqla->clauses_of(select => \@new_clauses);
      $sqla->clauses_of(select => sub {
        my (undef, @old_clauses) = @_;
        return @new_clauses;

      my @list = $sqla->statement_list;

      my $exp = $sqla->make_unop_expander(sub { ... });

    If the op is found as a binop, assumes it wants a default comparison, so
    the inner expander sub can reliably operate as

      sub { my ($self, $name, $body) = @_; ... }

      my $exp = $sqla->make_binop_expander(sub { ... });

    If the op is found as a unop, assumes the value will be an arrayref with
    the LHS as the first entry, and converts that to an ident node if it's a
    simple scalar. So the inner expander sub looks like

      sub {
        my ($self, $name, $body, $k) = @_;
        { -blah => [ map $self->expand_expr($_), $k, $body ] }

    The above methods operate exactly like the op_ versions but wrap the
    coderef using the appropriate make_ method first.

    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);

    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.

    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:


        use warnings;
        use strict;

        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.

    Contributions are always welcome, in all usable forms (we especially
    welcome documentation improvements). The delivery methods include git-
    or unified-diff formatted patches, GitHub pull requests, or plain bug
    reports either via RT or the Mailing list. Contributors are generally
    granted full access to the official repository after their first several
    patches pass successful review.

    This project is maintained in a git repository. The code and related
    tools are accessible at the following locations:

    *   Official repo: <git://>

    *   Official gitweb:

    *   GitHub mirror: <>

    *   Authorized committers:

    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

    *   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

    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")


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

    Copyright (c) 2001-2007 Nathan Wiger <>. All Rights

    This module is actively maintained by Matt Trout

    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.

    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