by [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:
ftp://thrush.omix.com/pub/perl/modules/SQL-Builder-2.002.tar.gz
Attached is the perldoc. Thanks!
--
-Zenin
ze...@archive.rhps.org
NAME
SQL::Builder - OO interface for creating simple SQL statements
SYNOPSIS
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
$statement->Clear;
## Same as $statement = $statement->new(), but faster (maybe)
$statement->ClearTables;
$statement->ClearColumns;
$statement->ClearValues;
$statement->ClearWhere;
$statement->ClearOrderBy;
$statement->ClearSomethingElse; ## Clears user defined things
$insertQuery = $statement->insert();
$updateQuery = $statement->update();
$selectQuery = $statement->select();
$deleteQuery = $statement->delete();
DESCRIPTION
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.
METHODS
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 (
'tablename',
)->Columns (
'SOME', 'COLUMN_NAMES', 'foo', 'bar'
)->Values (
'some', 'values', 69, 42
)->Where (
'foo = 12'
)->update;
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',
)->update;
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',
)->update
) 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;
$sth->execute()
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
exception.
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
list.
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:
$statement->ClearConnectBy();
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:
CONNECT BY
SOME,
COLUMNS,
TO,
USE
Suggestions on how to better this extension interface are welcome.
ERROR HANDLING
I throw confess() (die) exceptions if I'm unhappy, deal with it by
eval()ing me and checking for $@.
HISTORY
$Log: Builder.pm,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.
TODO
Better support for where querys.
Some better method for embedding querys within querys.
AUTHOR
Zenin <ze...@archive.rhps.org>
aka Byron Brummer <by...@omix.com>
SEE ALSO
perl, DBI, SQL::Statement