Reusing SQL data-related logic, replacing weird modules and improving Class::DBI - Page 2

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View

Re: Reusing SQL data-related logic, replacing weird modules and improving Class::DBI

First, I never stated this was an end-all solution to all problems
faced with SQL or data retrieval, it will hopefully be a means to
building a sturdy and flexible foundation for ideas such as SQL
abstraction and modules like Class::DBI to build a more flexible
foundation. I will be following up more on this with my article

"Yes. Yes, much better. Absolute control"

In your examples above, you're not far off base with what is
potentially necessary. If building SQL queries from the ground up,
starting at the bit level were necessary and solved the problem at
hand, then I would certainly do it. I think you're ignoring a couple of
things: the problem and the solution. I also believe you're being a bit
sensational and rude. I'm glad you have the comfort and the anonymity
of the Internet and hope you do not act as you have with others in

The proposed solution breaks down the problem to a level that is
relevant. Modifying bits directly is not necessary, so I won't do it.
Regarding the confusion of your DBAs, I'm not sure what you mean. Any
DBA I know has never looked at my code, they look at the result: SQL.

"Yes, generating LCD SQL for the $20,000,000 data warehouse
can't help but be a better solution.
Certainly a better use of enterprise resources.
"Yes, lets buy this $20,000,000
data warehouse with massive analytical capability...and then just use
as an indexed journaled filesystem! Brilliant!".

And of course, no org would think of questioning such code before
it for the $20,000,000 DW. Enterprise data stewards would just assume
it produces the right output! "

I'm not sure where you're going with this. I sense it's just more

"Sure it can. See the above. So can bits. So can quarks! Then we'll
have ultimate
control: just build a few mesons, and, with enough
effort, we may be able to get protons, neutrons, and electons.
After that, the world is our oyster! Oooh, wait, what about wavelets...

Like I said, I'm not proposing an end-all solution, simply a base for
building whatever follows so that is meets more needs. Again, I'll be
revising my article to illustrate problems with existing solutions.

"Over what ? ASCII ? UTF8 ? Does your solution magically cause
your DBA staff to disappear ? Do the bits magically roll
off the drive faster ?

So now, instead of writing a single, simple SQL statement that
my DBA's can look at and say "Yes, its good" or "No, thats crap",
I can write some pile of whateverthehellthatis, assuming you've
*fully* mapped the behaviors - including errorcode mappings -
of Firebird, Postgres, Oracle, Ingres, Netezza, DB2, Informix,
Sybase, SQL Server, Teradata, MySQL, SQLite,... can't recall the other
30+ SQL
databases). Then my DBAs will be able to say "whatthe f*ck is that ?"
every time I have a query problem. I win! "

I believe I can add control to SQL. As an object, you can easily modify
an existing query whereas with a string or vague data structure, it
might not be so easy or even possible. You agreed that string
themselves don't mean much and that using an object was a way to add

""Clean interface" ? Buddha, you really need to lay off the crack...

I'd suggest you look at Class::DBI. While I'm an SQL user myself,
for a decent alternative, Class::DBI is the bee's knees. Simple in
elegance, elegant in its simplicity. Maybe Perl at its best. "

Yes, a clean interface to writing SQL and repesenting SQL objects,
while providing flexibility. Class::DBI is nice but has its limitations
regarding SQL reuse and the ability to be dynamic. I'll be revising my
article and posting it shortly for your viewing. It will illustrate
some of its capabilities.

"Probably because they've tried and failed. You'll have to trust me,
I know that *major* companies have attempted this since the late 80's,
and gave up. (Look at all the i/f's MSFT has spewed over the years)
While I don't doubt that you can produce *something* in perl, it
sure as hell won't be everything. (Lots of luck imposing Oracle
behavior on a MySQL dbms, or vice versa). "

Please, share some of the companies that have attempted to do what I am
doing. Share what they tried and anything else that's relevant, it will
help me rework the problem. Imposing Oracle behavior on MySQL or vic.
vis., that's not at all what I'm trying to do, so I'm not sure what you

"Yes, see the bit level "control" above. Additionally, mine is more
Why, I can even parse perl with it! Why don't we go with it ? (you're
welcome to use it, I'll be happy to grant you full copyright). "

If your solution was the right solution and actively solved problems
faced then we would use it. You have yet to provide real or substantial
reasoning for why my efforts have failed. So far, you've only been

"At worst, you've found *yet another* excuse for lazy hackers to avoid
(a) learning SQL and (b) learning data modelling. "

I don't see what any of this has to do with people learning SQL or
learning to model data. Please have a look at some of the code I've
written and note that it without SQL knowledge you will not be able to
use it. And if this is what I have done "at worst," please share with
us what is has done at best.

Thank you for your input, it's greatly appreciated.

- sebastian

Re: Reusing SQL data-related logic, replacing weird modules and improving Class::DBI

I have updated an article which I think better summarizes the problem
and my proposed solution. Please advise:

- sebastian

Re: Reusing SQL data-related logic, replacing weird modules and improving Class::DBI

On Mon, 4 Jul 2005 17:37:47 +1000, sebastian wrote:

Hi Sebastian

Actually, something similar come up today in the DBI users mailing list. Do=

Here's a /tiny/ bit of Sam Vilain's post: "Death to SQL templating=

Well, OK, I admit I picked the eyes out of it.

Don't take this too personally! The message really: This problem is a /lot/=
complex that it seems.

And as for Dean's response: Wow! But he must know something, since he=
(I guess it was him) extensions to SQL so a select statement would return an=
image which is fabricated on-the-fly. See DBIx::Chart (in CPAN). Very=
Bloody clever, actually. I wish I'd thought of that.

Re: Reusing SQL data-related logic, replacing weird modules and improving Class::DBI

I agree with your overall goal, but I'm not sure how widely useful the
synax you're suggesting will be.

Have you considered putting your efforts into enhancing one of the
existing solutions in this field, rather than starting another one from

For what it's worth, in DBIx::SQLEngine I do support generating and
returning SQL based on hashes of query fragments:

  my $sqldb = DBIx::SQLEngine->new( $dbh_or_dsn );

  my $sql = $sqldb->sql_select(
    tables => "employees",
    columns => [ '*', 'MD5(ssn)' ],
    where => { active => 1, fulltime => 1 },
    order => 'name DESC',

It's generally easy to build those fragments up programatically, but
patches would be gladly accepted if you see ways to improve things.

There's also a mechanism to define queries before calling them:

    'get_employees' => {
      tables => "employees"
    'get_fulltime_employees' => {
      tables => "employees",
      where => { active => 1, fulltime => 1 },
    'get_fulltime_employees_with_md5social' => {
      tables => "employees",
      columns => [ '*', 'MD5(ssn)' ],
      where => { active => 1, fulltime => 1 },
    'get_fulltime_employees_with_md5social_born_after' => {
      tables => "employees",
      columns => [ '*', 'MD5(ssn)' ],
      where => [ 'active = 1 and fulltime = 1 and birthdate > ?', $1 ],
    'get_all_with_md5social_born_after' => {
      tables => "employees",
      columns => [ '*', 'MD5(ssn)' ],
      where => [ 'birthdate > ?', $1 ],

  $results =  $sqldb->fetch_named_query( 'get_employees' );

  $results =  $sqldb->fetch_named_query(
'get_all_with_md5social_born_after', $date );

  $results =  $sqldb->fetch_select(
$date ), order => 'name DESC' );

Looking at that, I can imagine a few easy adjustments to allow the
definitions to build on or include from one another...

    'get_employees' => {
      tables => "employees"
    'get_fulltime_employees' => {
      extends => "get_employees",
      where => { active => 1, fulltime => 1 },
    'get_all_with_md5social' => {
      extends => "get_employees",
      columns => [ '*', 'MD5(ssn)' ],
    'get_all_born_after' => {
      extends => "get_employees",
      where => [ 'birthdate > ?', $1 ],
    'get_fulltime_employees_with_md5social' => {
      extends => [ "get_fulltime_employees", "get_all_with_md5social"
    'get_all_with_md5social_born_after' => {
      extends => [ "get_all_with_md5social", "get_all_born_after" ],
    'get_fulltime_employees_with_md5social_born_after' => {
      extends => ["get_fulltime_employees_with_md5social",

Would that address the need you've described?

Re: Reusing SQL data-related logic, replacing weird modules and improving Class::DBI

Hi m.cavalletto,

sorry for the late response, I've been busy with things. I agree that
the syntax providing so far is questional and I am currently making a
large effort to provide more succinct interfaces to SQL construction.
One of the problems I am facing is allowing the user to do this while
maintaining state without context-specific structures. So far I'm
adding a quick() method to each of my SQL::Builder objects that accepts
a list of structures and returns a list of appropriate objects. I still
need to test it all and make sure it's actually a nicer interface.
We'll see how it goes...

Modifying an existing project was my first consideration, but I
expected severe  road blocks. In my initial discussions, people
objected to the idea. I'm not sure if this was because I did a poor job
discussing the purpose and the application, or because I was in fact
doing something stupid. The strange part was that no one seemed to be
providing much valid reasoning for me *not* to do something like
SQL::Builder. Some said it has already been done, but I have been
unable to find anyone who's taken my approach or write software that's
capable of doing what I was suggesting, or that didn't suffer from
problems I described. My post to the CDBI lists was mostly shunned, I
didn't discuss it much there because I'm sure it would have led to a
flame war.

I am proposing changes that would modify much of the underlying
structure of existing projects. Authors probably wouldn't support such
a thing because "it's bad," unreasonable, or they just don't see the
purpose. Even non- module authors don't seem to get the purpose --
suggesting SQL::Builder-like functionality to existing projects
probably won't go too well.

To answer your question about meeting my needs (the "extends" command),
the answer is "yes and no." It does allow you to builder queries upon
one another which is very cool, but I would like to see more control
and statefulness (I don't think that's a word, but I can't think of a
better one). For example, I might want to conditionally build a query
by considering what's already been defined.

Suppose I was writing a system that would allow me to write portable
queries. For example, MySQL does not support || as the concatention
operator, while others might. I could write a database independent
interface and assert that if "CONCAT(col1, col2, col3)" would act as
"col1 || col2 || col3" in Postgresql, and as "CONCAT(col1, col2, col3)"
in mysql, how would i go about writing code to automatically convert
"CONCAT(col1, col2, col3)" to "col1 || col2 || col3" in a reasonable
manner? I'd have to maintain some information about the structure. If
the user was somehow generating a SQL function object which contained
relevant information (the function name and its arguments), we could do
so very easily. While there are ways to do this with existing systems,
we should consider important factors about the functionality: is it
reusable? easy to maintain? flexible?

So would it be possible to add such functionality to DBIx::SQLEngine --
the ability to analyze the parts of an existing query and identify each
one and potentially modify it? You might be thinking that performing
such operations in a system isn't reasonable and may be indicative of
larger problems, but I don't think its much of our concern.
SQL::Builder (or whatever module implements the suggested
functionality) should hopefully make easy things easy and difficult
things possible.

I look forward to discussing this with you further and I'm glad to see
you agree with the overall goal -- this means I'm not the only crazy
person around here :p

- sebastian wrote:
Quoted text here. Click to load it

Re: Reusing SQL data-related logic, replacing weird modules and improving Class::DBI

Quoted text here. Click to load it

Again, I'd urge you to build on one of the existing efforts in this

Quoted text here. Click to load it

Yes -- in fact, it's already doing some of the basics of this. For
example, the DBIx::SQLEngine interface already supports portability
adaptors for joins, limits, unions, and other non-standard features.

Each query is built up from data structures into a SQL string and
parameters by a Driver object with automatic subclassing based on DBD
type. Applications can assemble, review, and modify a query as a hash
of parameters, and then send it off to the driver to get turned into a
locally ideosyncratic SQL string.

(Of course, applications can define their own SQL in some other way and
pass it through without any modification; the translation process
typically only applies when you've asked the driver to generate queries
from parameters.)

I'd be entirely open to a DBIx::SQLEngine patch that automatically
rewrote "CONCAT(col1, col2)" to "col1 || col2" when buidling queries
for Postgres. (I think this could be done in the sql_select and
sql_where methods of the DBIx::SQLEngine::Driver::Pg class.)

Generalizing from that, it should be reasonably straightforward to
build a table of various common functions with indications of whether
they're supported and what syntax is required on various platforms.

Ultimately, some functions may not be supported on some drivers --
you'll never be able to make DBD::CSV act like Postgres and Oracle --
but you can provide a capability interface that reports whether or not
a given feature is available, and provide a helpful error message if an
application tries to use a feature not supported on a given platform.

Patches welcome...

On the other hand, if you find DBIx::SQLEngine's flexible options
procesing insufficiently rigorous, try taking a look at Rosetta and
SQL::Routine. Like your proposed syntax, they're focused on building
and maintaining trees representing the structure of various queries...
I think the result is a little too unwieldy for my purposes, but you
might find it useful.


Re: Reusing SQL data-related logic, replacing weird modules and improving Class::DBI

Quoted text here. Click to load it

I'm all for building up Simon's SQLEngine. His approach is
built on the rock-solid technology of Class::MakeMethods, the cadillac
of method-making modules. Can we get together on a sourceforge mailing
list to discuss this further?

    Carter's Compass: I know I'm on the right track when,
       by deleting something, I'm adding functionality.

Re: Reusing SQL data-related logic, replacing weird modules and improving Class::DBI

Hello Sebastian,

I'm the author of the SQL::Interpolate module you mentioned.  The
design choices relating to your module are interesting and I could add
a few comments:

sebastian wrote:
Quoted text here. Click to load it

I'm not sure that SQL::Interpolate should be lumped with the other
modules as such.

SQL::Interpolate is not designed to abstract away SQL (like
SQL::Abstract).  Rather, it is designed specifically to simplify the
interpolation of variables into SQL statements (via bind values).  It
operates at the boundaries between raw SQL and Perl variables.

SQL::Interpolate's central function is sql_interp().  This is a simple
function that processes an "interpolation list," which is simply a
sequential list of SQL fragments and references to variables
interpolated between them.  The interpolation list is a reusable,
mutable component: you can concatenate them, nest them in each other
(via macros) to form tree structures, and modify them if you like
(though it's typically less structured than your OO approach).  At any
time, sql_interp() can serialize the interpolation list into a
conventional SQL string and list of bind values for execution by DBI.

Indeed, SQL::Interpolate does embed some meaning into the Perl hashref
and arrayref syntax for convenience, but the module intentionally keeps
this minimal and in fact optional.  The "Design" section of the POD on
the latest version (0.31) clarifies some of these things: .

In the below code, I've taken the "small example" you had and have
rewritten it using SQL::Interpolate.  I believe much of the same effect
is preserved.  It uses a helper object ($q) to impose the additional
structure you had on the query (there are other ways too).

use strict;
use SQL::Interpolate qw(:all);
use SQL::Interpolate::Macro qw(:all);
use Data::Dumper;


sub QSelect::new {
    return bless {tables => [], where => []}, $_[0];
sub QSelect::expand {
    my $q = shift;
    return (
        "SELECT * FROM", join(',', @}),
        "WHERE", sql_and(@}) );

sub base_query {
    my $q = shift || QSelect->new();
    push @}, 'perl_users';
    return $q;
sub smart_people_report {
    my $q = shift || base_query();
    push @}, sql_fragment("iq >", $ROOM_TEMPERATURE);
    return $q;
sub active_report {
    my $q = shift || smart_people_report();
    push @}, "last_active > CURRENT_TIME - '15 minutes'";
    return $q;

my $q = base_query();

my($sql, @bind) = sql_interp $q->expand();
print Dumper($sql, \@bind);

# $VAR1 = 'SELECT * FROM perl_users WHERE ( ( iq > ? ) AND (
last_active > CURRENT_TIME - '15 minutes' ) )';
# $VAR2 = [
#          25
#        ];

I saw some of the previous postings object to splitting the SQL string
into a tree of many small objects.  I agree that it is not worthwhile
to do this without reason.  For example, I left "last_active >
CURRENT_TIME - '15 minutes'" as a plain SQL string above rather than
opting to decompose it further.  In SQL::Interpolate, one typically
only splits the SQL string at the boundaries between SQL and Perl
variables (but sometimes it is useful to split it at other places as

best regards--davidm

Re: Reusing SQL data-related logic, replacing weird modules and improving Class::DBI

Quoted text here. Click to load it

I'm very interested in SQLEngine as an alternative to Class::DBI,
mainly because it has better support across databases, but you seem
personally busy and there is no public mailing list for interested
parties to share results on.

And the last thing I need is to be stranded without the help of the
primary developer or community.

For example, one of the gotchas of Class::DBI is that percent-signs in
queries must be doubled because of the underlying system that stores
the SQL strings... I was able to post to the mailing list and get
feedback from a number of people quickly on what might've been a
showstopper otherwise.

    Carter's Compass: I know I'm on the right track when,
       by deleting something, I'm adding functionality.

Re: Reusing SQL data-related logic, replacing weird modules and improving Class::DBI

On Mon, 4 Jul 2005 09:23:04 +1000, sebastian wrote:

Hi Sebastian

I replied in order to help the discussion along, and it worked!

Getting you to spell out your thinking helps us all understand how you got=
where you is :-).

Re: Reusing SQL data-related logic, replacing weird modules and improving Class::DBI

So, having viewed my response, do you have any more input or criticism?

Re: Reusing SQL data-related logic, replacing weird modules andimproving Class::DBI

Quoted text here. Click to load it

Think hard about this assertion. It may be more time-consuming to
build such a system and it will be more time-consuming should anything
change about the data model. But it is not impossible and perhaps the
speed boost from mapping the entire front-end to static queries is
worth the time-consumption.

I know of one major shopping comparison website that does this.

    Carter's Compass: I know I'm on the right track when,
       by deleting something, I'm adding functionality.

Site Timeline