[CDBI] SQL::Builder propaganda review

Sebastian buddhahead at gmail.com
Sat Oct 22 12:43:38 BST 2005


For those that don't know, SQL::Builder is a collection of modules for
SQL manipulation. It's not "yet another SQL abstraction module" or ORM
so please read some of what I say before flaming. Currently it's under
development, but it's on its way. My next release (0.03alpha) next
week will have documentation and more complete functionality. The
source is available at

Anyhow, I've been revising the documentation and would like to get
some feedback on the following bit of propaganda extracted from
SQL::Builder's pod. Thanks in advance

=head1 NAME

SQL::Builder - a structured SQL manipulation interface

=head1 VERSION

Version 0.03 ALPHA - this software isn't production-ready yet and the API is
likely to change. Some methods and functionality aren't documented completely
and some documentation exists without the functionality. USE AT YOUR OWN RISK



SQL::Builder is a collection of modules sharing common interfaces for SQL
manipulation with the goal of providing maximum reuse and scalability. It is
not specifically a SQL abstraction (although it does sort of abstract it out
for you in some cases), but a structured interface for its manipulation. Because
SQL::Builder is a stateful interface, one's SQL is as portable as they write it,
with the possibility to traverse a SQL construct at runtime and, for example,
convert instances of MySQL's OR operator ("||") with an appropriate "OR", or to
replace the standard concatenation operator ("||"), to MySQL's "CONCAT".

SQL::Builder's goal is not SQL portability, object-relation mapping, or
abstraction. SQL::Builder would hopefully provide a solid foundation for all of
these ideas.


I had spent a lot of time trying to gather my reasoning for developing
SQL::Builder and why it's better than any current SQL abstraction/generation
mechanism I've encountered, but it turns out that SQL::Builder is nothing
special and the concepts on which it is based are not new. My
reasoning/advocation for SQL::Builder bottom-lines reasoning for object-oriented
programming: granularity, encapsulation, abstraction, etc.

SQL::Builder is a collection of granular objects that share a common base,
which allow them to interact easily, consitently, and flexibly without the
"overhead" (problems) created by certain abstraction/generation modules. I hope
that at some point, ORMs, SQL abstractions, et. al. will
utilize SQL::Builder as a base for their provided functionality. Building
components with consistent and meaningful interfaces from the ground up is vital
for any system that needs to work.

I will outline some of the problems with existing SQL abstraction/generation
mechanisms that are solved by SQL::Builder.

=head2 String Manipulation

Building queries dynamically based on user data (or similar) is a nasty job and
often results in difficult-to-debug, unmaintainable code. The need to manipulate
strings based on a variety of criteria will clutter code and obfuscate it

SQL phrasebooks/templates are the worst; they should be avoided like a plague.
Their initial requirements usually appear basic, but user demands change, so the
code changes. Joe Coder usually adds arguments to a function to control its
return, essentially trying to harness the power of SQL in a single function.
Then he does it again... and again. join(), map(), keys(), etc are applied
liberally until the job is done. What's left is code like this:
	# taken from SQL::Abstract::select()

	my $f = (ref $fields eq 'ARRAY') ? join ', ', map { $self->_quote($_)
} @$fields : $fields;
	my $sql = join ' ', $self->_sqlcase('select'), $f,
$self->_sqlcase('from'), $table;

and it's everywhere. Using functions and objects to hide the work will help
with this process, but they must be granular to be effective on a large scale

=head2 Basic Data Structures

The smart programmers will often develop or use a library like DBIx::Abstract
because it helps reduce the amount of string manipulation and allows SQL to
built a little bit faster and cleaner. The typical interface (taken from
DBIx::Abstract) usually looks something like:



Basically we pass "vague data structures" which represent some piece of SQL.
This is a start, but the effort is incomplete. In the process of building a
query, the programmer is required to maintain the list of columns in a SELECT,
the tables it JOINs, and the order in which results are displayed. The code is
usually something like:

	if(foo())	{

		push @cols, "username"
		push @joins, "users";

		$users_was_joined = 1;

	if(bar())	{
		if(!$users_was_joined)	{

			push @joins, "users";

		push @cols, "birthdate";
		push @cols, "zipcode";

	$dbh->select(\@cols, \@tables, \@joins ...

The dirty job of string manipulation has mostly (note I said
"mostly": at some point modules like DBIx::Abstract break and force one to write
SQL - consider writing a query to SELECT MAX(...)) been cleaned up, but the
messy job of maintaing state of our query is still there. Joe Coder needs to
keep track of all his columns, joins, and WHERE clause manually until he can
hand them off to select().

The intelligent solution here would be to turn the arguments into object
attributes and build intelligent methods around them. This can't all be in one
class, though; it would hurt reusability and flexibility. Components need to be
granular and stateful.

=head2 Query Objects

Once Joe Coder realizes that objects will solve many of his problems, he can't
just code up an object to represent a SELECT statement, another for UPDATE, and
yet another for DELETE. These operations/objects have way too much in common and
require too much functionality to be sloppily placed in so few classes. SQL
statements should be built from the ground up and given common interfaces where
possible. The goal is to obtain perfect granularity so that larger objects can
be composed as necessary without decreasing flexibility or
repeating/copying (not
reusing) code. Without common interfaces, these objects won't fit together

=head2 ORM / Object Modeling

The ORM hype/buzz/movement is a movement in the right direction, but
they (like the
aforementioned) have serious problems. In a simple system, these ORMs do a good
job abstracting SQL and building relationships, but at some point the complexity
of relationships may forces a programmer to write SQL. This is especially
problematic because these ORMs build SQL using one of the aforementioned broken
implementations. Reusing logic in these ORMs is often difficult or
impossible. If, at the point an ORM fails, one can easily utilize existing logic
to inifnitely reuse and abstract it while maintaining state of it, a
flexible and
scalable system is possible.

More information about the ClassDBI mailing list