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

SYNOPSIS
        use SQL::Abstract;

        my $sql = SQL::Abstract->new(case => 'lower', cmp => 'like');

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

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

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

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

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

DESCRIPTION
    This module was inspired by the excellent DBIx::Abstract. However, in
    using the module I found that what I 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.

    This module is based largely on DBIx::Abstract. However, 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.

    In particular, if you want to see if a field is within a set of values,
    you can use an arrayref. Let's look at a specific example:

        my $where = {
           worker => ['nwiger', 'bob', 'jim'],
           status => { '!=', 'completed' }
        };

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

    This would give you something like this:

        $stmt = "SELECT * FROM requests WHERE
                    ( ( worker = ? OR worker = ? OR worker = ? )
                       AND ( status != ? ) )";
        @bind = ('nwiger', 'bob', 'jim', 'completed');

    Which you could then use in DBI code:

        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.

  new(case => 'lower', cmp => 'like')
    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 comparion on an individual basis - see the
        huge section on "WHERE CLAUSES" at the bottom.

  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)
    This is used just to generate 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.

WHERE CLAUSES
    This module uses a variation on the idea from DBIx::Abstract. It is NOT,
    repeat *not* 100% compatible.

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

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

    Note this is not compatible with "DBIx::Abstract"

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

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

    Which would generate:

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

    So far, we've seen how multiple conditions are joined with "AND".
    However, 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 => ['pending', 'dispatched'],
            },
            {
                user   => 'robot',
                status => 'unassigned',
            }
        );

    This data structure would create the following:

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

    If you want to include plain SQL verbatim, you must specify it as a
    scalar reference, namely:

        my $inn = 'is not null';
        my %where = (
            pri  => { '>', 3 },
            name => \$inn
        );

    This would create:

        $stmt = "WHERE pri > ? AND name is not null";
        @bind = (3);

    Note you only get one bind parameter back.

SEE ALSO
    DBIx::Abstract, SQL::Statement

VERSION
    $Id: Abstract.pm,v 1.9 2002/08/29 18:04:35 nwiger Exp $

AUTHOR
    Copyright (c) 2001 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.