Beta: SQL::Builder -- an OO SQL builder Perl module

Beta: SQL::Builder -- an OO SQL builder Perl module

Post by Zeni » Sat, 04 Jul 1998 04:00:00

[posted & mailed]

SQL::Builder is an OO SQL statement builder for Perl.  I'd like any feed
back I can get as I'm not completely familiar with all the quirks SQL has.
I'm especially interested in comments about the interface.

The module is available from:

Attached is the perldoc.  Thanks!


    SQL::Builder - OO interface for creating simple SQL statements

      use SQL::Builder;

      my $statement = new SQL::Builder;

      my $statement = new SQL::Builder (
          Tables        => 'table_name',
          Columns       => [ qw(foo bar this that test) ],
          Values        => [ 42, 'foobar' ],
          BareValues    => 'RowNum',
          QuotedValues  => [ 42, 35 ],
          Where         => 'foo = 10',
          Where         => 'that != NULL'
          Where         => 'or 1 = 2',
          OrderBy       => [ 'test', 'bar' ],
          AddSomethingElse => 'extra_stuff',  # user thingy (see EXTENSIONS)

      ## All data (columns, values, wheres, etc) guaranteed to be added
      ## in the order given, even when given in a hash style as above.

      $statement->Tables ('mytable'); # FROM mytable
      $statement->Tables ('foobar');  # FROM mytable, foobar

      @tables = $statement->GetTables();

      $statement->Columns ('FOO', 'BAR'); # SELECT FOO, BAR
      @columns = $statement->GetColumns();

      ## Auto-detect value types.  Strings get single quoted (and quote
      ## excaped), numbers are left bare, and undef values are considered
      ## SQL NULL
      $statement->Values ("foo", '42', undef); # VALUES ('foo', 42, NULL)

      ## Values that should never be quoted.  We do single quote escapes though
      $statement->BareValues (
          "foo'bar", 10, SysDate, "?", undef
      ## VALUES ('foo''bar', 10, SysDate, ?, NULL)

      ## Values that should always be quoted.  Use of undef for NULL
      ## is not valid for this method and throws an exception.
      $statement->QuotedValues (10, "foobar"); # ('10', 'foobar');

      @values = $statement->GetValues();
      ## NOTE: This is a list of the values in modified form.  Quotes have
      ## been excaped, strings quoted, etc.

      ## This needs some work, but for now this is what you get.
      $statement->Where ("foo > 12");
      $statement->Where ("bar != NULL");  ## Defaults to "and"
      $statement->Where ("OR dog == 'cat'");

      # WHERE
      #             foo > 12
      #         AND bar != NULL
      #         OR dog == 'cat'

      ## Note how it defaults to AND if no AND or OR is given

      $statement->OrderBy ('FOO', 'BAR'); # ORDER BY FOO, BAR

      ## Same as $statement = $statement->new(), but faster (maybe)

      $statement->ClearSomethingElse;  ## Clears user defined things

      $insertQuery = $statement->insert();

      $updateQuery = $statement->update();

      $selectQuery = $statement->select();

      $deleteQuery = $statement->delete();

    This is my attempt to create an clean, easy to read, object oriented way
    to build SQL statements.

    This module is designed to make dynamically constructing simple SQL
    statements simple and much less error-prone. Common mundane tasks such
    as to do an insert or update are reduced to calling the update() or
    insert() method and letting SQL::Builder figure out how to do "set this
    = 'that'" vs "(this) values ('that')", etc.

    This is not meant as the end-all, be-all SQL builder of all time. It's
    meant to make common jobs easy and hard jobs easier. Along those lines,
    I'm very open to suggestions on how to improve/extend the interface for
    more complex querys, but I'm very strongly set that the base interface
    be as simple and efficient to use as posible.

    With the exceptions of the insert(), update(), select(), and delete()
    all methods return the object they were called off of. This allows one
    to string method calls together, even to the point of never actually
    assigning the object to an identifier:

      my $updateQuery = SQL::Builder->new()->Tables (
      )->Columns (
          'SOME',   'COLUMN_NAMES', 'foo',  'bar'
      )->Values (
          'some',   'values',       69,     42
      )->Where (
          'foo = 12'

    Although, since new() can now take a list of all this in a much cleaner
    way, this type of usage is a bit outdated and would probably be much
    better done as:

      my $updateQuery = SQL::Builder->new (
          Tables   => 'tablename',
          Columns => [qw( SOME COLUMN_NAMES foo bar )],
          Values  => [ 'some', 'values', 69, 42 ],
          Where   => 'foo = 12',

    Or, if you don't want to create a SQL string variable at all:

      my $sth = $dbh->prepare (
          SQL::Builder->new (
              Tables   => 'tablename',
              Columns => [qw( SOME COLUMN_NAMES foo bar )],
              Values  => [ 'some', 'values', 69, 42 ],
              Where   => 'foo = 12',
      ) or die $dbh->errstr();

    The real power though, comes in building the actual object first, and
    deside later what (insert, update, delete, etc) you want to do with it:

      my $query = SQL::Builder->new (
          Tables   => 'tablename',
          Columns => [qw( SOME COLUMN_NAMES FOO BAR )],
          Values  => [ 'some', 'values', 69, 42 ],
          Where   => 'foo = 12',

      shouldWeInsert()   ## Some test to see if we have a record yet or not
          ? $sth = $dbh->prepare ( $query->insert ) or die $dbh->errstr
          : $sth = $dbh->prepare ( $query->update ) or die $dbh->errstr;
          or die $dbh->errstr;

    The available methods are:

    Tables (TABLE_NAMES)
        Takes a list of table names which will be appended to any current
        list. Use ClearTables() to reset the list. If you're going to use
        aliases, put them into the string such as:

          $statement->Tables ('f foo', 'b bar');

    Columns (COLUMN_NAMES)
        Takes a list of column names which will be appended to any current
        list. Use ClearColumns() to reset the list.

    Values (VALUES)
        Takes a list of values which will be appended to any current list.
        This method does a "best guess" method to decide if the value should
        be quoted (such as a string) or not (such as a number). Passing
        undef is changed to a SQL NULL. It can sometimes fail because the
        value could actually be a special column name such as Oracle's
        SysDate that can not be quoted, or you could be trying to insert a
        numeric string into a non-numeric field and the database won't do
        the conversion for you so it must be quoted. When in doubt, force
        bare or quoted use by calling BareValues() and QuotedValues(), at
        least for these special cases.

        This method will also SQL escape your single quotes for you.

        Note: Using Oracle this is a pretty safe method because it's much
        smarter about "doing the right thing" even if it's not quoted
        "correctly". Other systems like Informix will flip however, if you
        mis-quote numeric values...

        use ClearValues() to reset the values list.

    BareValues (VALUES)
        Takes a list of values which will be appended to any current list.
        These values will never be quoted. Passing undef will be changed to
        the string 'NULL'.

        use ClearValues() to reset the values list.

    QuotedValues (VALUES)
        Takes a list of values which will be appended to any current list.
        These values are always quoted. Passing undef will throw an

        use ClearValues() to reset the values list.

    Where (WHERE_CLAUSE)
        Adds a where clause to the current stack of where clauses. If there
        is no "and" or "or" at the beginning of the clause it will prepend
        an "and" on for you (only on the 2nd and higher clauses of course).
        Multilevel where clauses are not well supported, but you can use
        them if you put it all into one string for Where(). Example:

          $statement->Where("(this = 'that' AND foo = 'bar')");
          $statement->Where("OR foo != NULL");

          ## Produces
          # WHERE
          #        (this = 'that' AND foo = 'bar')
          #        OR foo != NULL

        use ClearWhere() to reset the where clause stack.

    OrderBy (COLUMN_NAMES)
        Takes a list of column names which will be appended to any current

        use ClearOrderBy() to reset the order by list.

    With all the different modifiers available (most of which I fully admit
    to not even knowing exist), it would be difficult to handle them all.
    So, we let you use them via an auto-loaded interface.

    How this works. If you have a modifier "CONNECT BY", convert it first to
    mixed case so it's "ConnectBy". Then, add the string "Add" to it, so we
    have "AddConnectBy". Use this as a method name:

      $statement->AddConnectBy ('SOME', 'COLUMNS', 'TO', 'USE');

    Like most of the other methods, this appends these values to the
    "CONNECT BY" list. Just replace the "Add" with "Clear" like this:


    These extra modifiers are all added to the end of the rest of the query.
    The values used are also not quoted, however they are divided with
    commas and whitespace, such that this example will produce:


    Suggestions on how to better this extension interface are welcome.

    I throw confess() (die) exceptions if I'm unhappy, deal with it by
    eval()ing me and checking for $@.

     $Log:,v $
     Revision 2.2  1998/07/03 12:41:38  byron
            -Lots of clean up for general release

     Revision 2.1  1998/05/28 12:35:28  byron
            -Cleaned up the interface a bit (it's a new name, I can do that now :-)
            -Added lots of docs
            -Fixed my email address

     Revision 2.0  1998/05/25 17:28:21  byron
            -Moved name from SQL::Statement to SQL::Builder

     Revision 1.18  1998/03/30 12:48:16  byron
            -Cosmetic changes.
            -Renamed module to SQL::Statement after learning that the DBI::*
             space was reserved.

     Revision 1.17  1998/02/02 22:14:23  byron
            -Added "dynamic" support for misc SQL calls

     Revision 1.16  1998/01/12 14:59:15  byron
            -Added ORDER BY support to SELECT statements

     Revision 1.15  1998/01/11 02:12:36  byron
            -More null value fixes.
            -Now uses the value NULL when passed a null string

     Revision 1.14  1998/01/11 00:02:45  byron
            -Handles undefined values more gracefully

     Revision 1.13  1998/01/08 15:05:50  byron
            -Added AddNumericValues() and AddStringValues() to allow for
             explicit non-quoted and quoted values respectively.

     Revision 1.12  1998/01/08 14:56:28  byron
            -Removed numaric non-quoting code from methods and moved it into
             AddValues.  This will allow for new methods to be made such as
             AddNumaricValues(), AddDateValues(), etc.

     Revision 1.10  1998/01/07 14:31:17  byron
            -Changed all data access methods to return the object they are called
             from to allow method calls to be strung off each other.

     Revision 1.9  1997/12/19 02:20:08  byron
            -Some documentation additions
            -Predeclared object fields so I don't have to keep testing them
             to see if they are defined already.
            -Made some exception messages cleaner
            -Fixed many bugs
            -Removed strict package requirement for embeded select statements so
             we can be sub-classed easier. -But you didn't see this because it's
             still buggy and undocumented, so don't use it!

     Revision 1.8  1997/12/18 23:24:51  byron
            -Fixed bug in Clear* methods

     Revision 1.7  1997/12/18 23:07:42  byron
            -Added perl 5.003 support

     Revision 1.6  1997/12/08 06:48:00  byron
            -Documentation changes

     Revision 1.4  1997/12/08 06:45:55  byron
            -Modified update() formating

     Revision 1.3  1997/12/08 06:01:39  byron
            -Documentation changes

     Revision 1.2  1997/12/08 05:59:59  byron
            -Moved each query type out of it's own package and back into the
             main one.
            -Moved where clause builder into it's own (internal) method.

    Better support for where querys.

    Some better method for embedding querys within querys.

    Zenin <>

    aka Byron Brummer <>

    perl, DBI, SQL::Statement