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

    In addition, you can apply SQL functions to elements of your %data by
    specifying 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...

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

    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.

    logic
        This determines the default logical operator for multiple WHERE
        statements in arrays. By default it is "or", meaning 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'

    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.

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

        This is useful if you have tables or columns 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

  insert($table, \@values || \%fieldvals)
    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.

  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.

  select($table, \@fields, \%where, \@order)
    This takes a table, arrayref of fields (or '*'), optional hashref WHERE
    clause, and optional arrayref order by, and returns the corresponding
    SQL SELECT statement and list of bind values.

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

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

    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 != ?"

    But, this is probably not what you want in this case (look at it). So
    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}, {'!=', 1} ]
        );

    Which would generate:

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

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

    In addition to "-and" and "-or", there is also a special "-nest"
    operator which adds an additional set of parens, to create a subquery.
    For example, to get something like this:

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

    You would do:

        my %where = (
             user => 'nwiger',
            -nest => [ workhrs => {'>', 20}, geo => 'ASIA' ],
        );

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

    You can use this same format to use other grouping functions, such as
    "BETWEEN", "SOME", and so forth. For example:

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

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

    This can be combined with the "-nest" operator to properly group SQL
    statements:

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

    That would yield:

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

    Finally, sometimes only literal SQL will do. If you want to include
    literal SQL verbatim, you can specify it as a scalar reference, namely:

        my $inn = 'is Not Null';
        my %where = (
            priority => { '<', 2 },
            requestor => \$inn
        );

    This would create:

        $stmt = "WHERE priority < ? AND requestor is Not Null";
        @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.

    Of course, just to prove a point, the above can also be accomplished
    with this:

        my %where = (
            priority  => { '<', 2 },
            requestor => { '!=', undef },
        );

    TMTOWTDI.

    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.

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.

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.

NOTES
    There is not (yet) any explicit support for SQL compound logic
    statements like "AND NOT". Instead, just do the de Morgan's law
    transformations yourself. For example, this:

      "lname LIKE '%son%' AND NOT ( age < 10 OR age > 20 )"

    Becomes:

      "lname LIKE '%son%' AND ( age >= 10 AND age <= 20 )"

    With the corresponding %where hash:

        %where = (
            lname => {like => '%son%'},
            age   => [-and => {'>=', 10}, {'<=', 20}],
        );

    Again, remember that the "-and" goes *inside* the arrayref.

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:

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

    Thanks!

BUGS
    If found, please DO NOT submit anything via "rt.cpan.org" - that just
    causes me a ton of work. Email me a patch (or script demonstrating the
    problem) to the below address, and include the VERSION string you'll be
    seeing shortly.

SEE ALSO
    DBIx::Abstract, DBI, CGI::FormBuilder, HTML::QuickTable

VERSION
    $Id: Abstract.pm,v 1.21 2006/03/08 01:27:56 nwiger Exp $

AUTHOR
    Copyright (c) 2001-2005 Nathan Wiger <nate@sun.com>. All Rights
    Reserved.

    This module is free software; you may copy this under the terms of the
    GNU General Public License, or the Artistic License, copies of which
    should have accompanied your Perl kit.