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

One of the problems I frequently face in development for
database-driven applications is the inability to efficiently reuse data
retrieval logic. That is, how do I reuse a SQL statement, but slightly
modify a single part of it? How do I efficiently abstract the database
as much as possible?

I've tried a number of "solutions" to these problems, but my methods
usually fell short. With DBIx::Abstract, DBIx::SQLEngine, or
SQL::Interpolate you're forcses to use vague data structures. At times
SQL can be quite complex, and remembering tiny little differences in
data structures used in those modules can be a pain. In some of these
modules you might also have to write a bit of SQL. The problem with
that is, as soon as you use a string constant, you lose flexibility and
most likely the ability to modify your query at a later time.
Class::DBI does a nice job hiding the database layer, but at some point
you have to start writing SQL because it doesn't support mechanisms to
efficiently hide and reuse query logic.

There are some philosphies which assert that SQL should not be mixed
with Perl (think phrasebooks), but this can't hold up for long in a
system that generates dynamic queries -- for example, dynamic reports
or searches. Other philosophies assert that programmers should never
write SQL and this should be left to a DBA -- this might be plausible,
but potentially very expensive or encounter a number of other problems.
I'm not here to argue any of this -- the problem at hand relates to a
programmer writing SQL, which is how many systems are written.

I have come up with a solution of my own that sounds good, but I
haven't tested it in a realworld application yet. What I'd really like
to know is if anyone else encounters similar problems and is looking
for a clean, simple, and lightweight solution to the above problems, or
have you already found a solution?

As for my solution, you can find a bit about it at
http://unf.be/~sili/projects/sqlbuilder/sql.html. This is a very rough
draft and I expect to rewrite and refine at some point, its only
purpose is to relay the concept. I am suggesting objects for everything
that is SQL. If this is accomplished, we can truly stop writing SQL and
start writing Perl. Objects can provide interfaces to the functionality
specified by SQL. It opens up possibilities because you no longer rely
on vague data structures, inflexible query strings, or the inability to
elagantly write SQL. At
http://unf.be/~sili/projects/sqlbuilder/SQL-Builder/lib/SQL/ Builder/
you can view the work I've done so far. I don't have any documentation
yet, but all of the objects are pretty simple and should be easy to
grasp.

What do you think of such a solution?

- sebastian

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

On Sat, 2 Jul 2005 18:23:36 +1000, sebastian wrote:

Hi Sebastian

It's fascinating that so many programmers have tried to come up with a=
solution
to this 'problem'.

There seem to be several explanations for this:

a) They are solving the wrong problem

b) They overlook other people's solutions, if any

c) They solve part of the problem and then give up

d) Etc

(a) is the saddest, because it suggests they don't understand the problem,=
and
hence should not have developed their solution

(b) is sometimes due to the problem of finding other solutions

(c) shows that the solution can soon become worse than the problem. This too=
is
sad

Looking at your code
http://unf.be/~sili/projects/sqlbuilder/sql.html
what /I/ see is a vast amount of code instead of 2 or 3 lines of SQL.

So, I ask: Have you really gained anything? My view is: absolutely not :-(.

This begs the question: Why do so many attempts fail?

My answer is: SQL is a string, and no more than that.

It's /not/ complex like a car or something, which would justify regarding it=
as
an object which can be decomposed.

And by that I mean SQL does not have the internal complexity which justifies=

trying to decompose it into elements whose combined complexity, in turn,
justifies wrapping a class around it.

The various clauses involved in building up a string into an SQL statement
mislead people into thinking that the complexity of the clauses justify=
turning
the string into an object.

And the fact that in some languages a string can be an object sets a trap=
for
people, who again think SQL can be forced to become an object.

But this time the explanation is that the strings referred to in the=
previous
sentence are complete(d), whereas you are trying to make each /component/ of=
the
string an attribute of the object, and to make each something to be=
manipulated
independently of the final string, in order to build up that final string.

You've added the complexity all right, but gained nothing in return.

Sorry, but I am convinced your solution to this 'problem' has already=
failed.

However, don't be discouraged. We all try things which don't work out. You
should have seen some of the rubbish I wrote when I was learning Perl [1].

It's called experimentation, in exactly the same way Perl V 4 was an=
experiment
which lead to V 5, and as too V 5 is an on-going experiment which will lead=
one
fine - but far distant - day to V 6 being as wide-spread in production as V=
5 is
today.

[1] The worst, perhaps, was when I didn't know '~' was an alias for 'home
directory' in filenames under Unix. But I prefer to draw a veil over the
details...

[PS: Don't be surprised when I turn this post into an article on my web=
site.]

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

Ron,

> So, I ask: Have you really gained anything? My view is: absolutely not :-(.
>
> This begs the question: Why do so many attempts fail?
>
> My answer is: SQL is a string, and no more than that.
>
> It's /not/ complex like a car or something, which would justify regarding it as
> an object which can be decomposed.

Some SQL dialects are almost turing-complete and you want to tell us SQL
isn't complex?? You seem to mix up the complexity of the SQL-language
with the complextity of "programs" written in SQL.

>
> And by that I mean SQL does not have the internal complexity which justifies
> trying to decompose it into elements whose combined complexity, in turn,
> justifies wrapping a class around it.
>
> The various clauses involved in building up a string into an SQL statement
> mislead people into thinking that the complexity of the clauses justify turning
> the string into an object.
>
> And the fact that in some languages a string can be an object sets a trap for
> people, who again think SQL can be forced to become an object.
>
> But this time the explanation is that the strings referred to in the previous
> sentence are complete(d), whereas you are trying to make each /component/ of the
> string an attribute of the object, and to make each something to be manipulated
> independently of the final string, in order to build up that final string.
>
> You've added the complexity all right, but gained nothing in return.

Sebastian's solution has at least 2 worthwhile pros:

1) code written using his solution is SQL dialect invariant,
2) query objects have a state, which is a big gain compared to a
almost stateless SQL statement.

Rob

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

Hi Ron,

Yes, some of the code that I have written could be easily replaced by a
few lines of SQL, and this may improve clarity and amount of code. Once
you have strigified a query, however, you have a virtually useless
piece of data -- without a parser (which is that your DBMS is for), the
string has no meaning. Essentially, your query is immutable. Going back
to the example, don't you notice that if I had tried to modify an
already-created SQL statement, I would have a hard time doing so, and
that by using some sort of object, each piece of a query has well
defined behaviors and is very controllable?

"It's /not/ complex like a car or something, which would justify
regarding it as
an object which can be decomposed. "

I disagree - SQL can be very complex. Consider any data warehouse or
reporting system. That said, there is justification for regarding a
SQL query as an object which can be decomposed.

"The various clauses involved in building up a string into an SQL
statement
mislead people into thinking that the complexity of the clauses justify
turning
the string into an object."

You're right, the complexity of a clause doesn't necessarily justify
turning a string into an object, but the need for state and the ability
to mutate or change a part of a query is, which is a main point of
writing SQL::Builder.

"And the fact that in some languages a string can be an object sets a
trap for
people, who again think SQL can be forced to become an object."

Why can't SQL be an object?

"You've added the complexity all right, but gained nothing in return."

I don't believe I've added complexity, I believe I've added *control*.
And again, this is what there is to gain: a) query reuse b) query
flexibility c) clean interfaces to writing queries dynamically. See
what Robert Jordan said.

Regarding the "failure" of such an attempt by other people and/or
myself, I would like you to please point me to someone who is
attempting to add the control that I am, because I have found none. At
best, I have found mechanisms which allow me to control vague data
structures, but never control SQL at such a granular level as I am
attempting to do.

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=
to
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 and improvingClass::DBI

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

[ OK, you asked for it.... ]

Apparently, Buddha's head is full of smoke...

[ Warning: better put on your asbestos suits ...

I usually avoid flamewars, and this sort of thing is usually reserved
for c.l.p.misc, but this sh*t has been popping up all over
the place lately, and I (for one) have had my fill... ]

sebastian wrote:
> Hi Ron,
>
> Yes, some of the code that I have written could be easily replaced by a
> few lines of SQL, and this may improve clarity and amount of code. Once
> you have strigified a query, however, you have a virtually useless
> piece of data -- without a parser (which is that your DBMS is for), the
> string has no meaning. Essentially, your query is immutable. Going back
> to the example, don't you notice that if I had tried to modify an
> already-created SQL statement, I would have a hard time doing so, and
> that by using some sort of object, each piece of a query has well
> defined behaviors and is very controllable?

Yes, I agree, for instance, take the sentance:

"Its hot outside today".

By converting to an object, I can implement something like

my $sentance{$woparole} = Sentence->new(
Subject => 'outside',
Temporality => 'today', # or perhaps 1120435319 ?
Condition => 'hot', # or perhaps 97F or maybe 32C ? or maybe 267K ?
Area => 'here' # or perhaps 57.456567L123.4567L ?
);

Yes, much more meaningful. And I've got control!

Better still:

my $query = Query->new(
FirstLetter => 's',
SecondLetter => 'e',
ThirdLetter => 'l',
FourthLetter => 'e',
FifthLetter => 'c',
SixthLetter => 't',
SeventhLetter => ' ',
EightLetter => 't',
NinthLetter => 'i',
TenthLetter => 'm',
EleventhLetter => 'e'
) || die "cuz this is complete nonsense!!!!";

Wow, that's really control!

However, ultimately, we'll want to decompose this potentially
incompatible (due to I18N issues) query to the bare minimum

my $query = Bits->new(
'1', '0', '1', '0', '1', '1',....);

Yes. Yes, much better. Absolute control. And my DBA staff will
be much more able to understand my queries that way. Yes. And I'm sure
it will perform at optimium efficiency, after all its just bits!
And I've got control!

BTW: when you develop a full error code mapping between
all those popular databases out there, be sure and let us know.
After all, when you succeed in solving your (purported) problem,
you still have to deal with the same error coming from different DBMS's
(lots of luck relying on SQLSTATE). Since you feel capable of solving
a problem that several major corp's have spent millions of $$$ and
hundreds of manyears trying to solve, I don't think you'll
have any trouble filling that big hole.

>
> "It's /not/ complex like a car or something, which would justify
> regarding it as
> an object which can be decomposed. "
>
> I disagree - SQL can be very complex. Consider any data warehouse or
> reporting system. That said, there is justification for regarding a
> SQL query as an object which can be decomposed.

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 it
as an indexed journaled filesystem! Brilliant!".

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

>
> "The various clauses involved in building up a string into an SQL
> statement
> mislead people into thinking that the complexity of the clauses justify
> turning
> the string into an object."
>
> You're right, the complexity of a clause doesn't necessarily justify
> turning a string into an object, but the need for state and the ability
> to mutate or change a part of a query is, which is a main point of
> writing SQL::Builder.

"Sorry, Larry, all that work implementing regex's was pointless. We've
got SQL::Builder now!"

>
> "And the fact that in some languages a string can be an object sets a
> trap for
> people, who again think SQL can be forced to become an object."
>
> Why can't SQL be an object?

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...

>
> "You've added the complexity all right, but gained nothing in return."
>
> I don't believe I've added complexity,

"And the Clueless Developer of the Year Award goes to...."

Seriously, Sebastian, its time for some R&R. Spend a week or 2 away
from the keyboard (I'd be happy to suggest some pleasant getaways).
When you come back to work, you'll be "OMG, WTF was I thinking...".
Its happened to the best of us...

> I believe I've added *control*.

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!

>
> And again, this is what there is to gain: a) query reuse b) query
> flexibility c) clean interfaces to writing queries dynamically. See
> what Robert Jordan said.

"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.

>
> Regarding the "failure" of such an attempt by other people and/or
> myself, I would like you to please point me to someone who is
> attempting to add the control that I am, because I have found none.

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).

> At best, I have found mechanisms which allow me to control vague data
> structures, but never control SQL at such a granular level as I am
> attempting to do.
>

Yes, see the bit level "control" above. Additionally, mine is more general.
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).

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

Here's a good starting point for your efforts:

http://search.cpan.org/~dconway/Acme-Bleach-1.12/lib/Acme/Bl each.pm

Good luck!

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
revision.

"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
life.

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
it
as an indexed journaled filesystem! Brilliant!".

And of course, no org would think of questioning such code before
deploying
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
sensationalism.

"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
control.

""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
mean.

"Yes, see the bit level "control" above. Additionally, mine is more
general.
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
flaming.

"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:

http://unf.be/~sili/projects/sqlbuilder/sql.html

- 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=
you
subscribe to that? See Message-ID: <42C8C19A.4030702@vilain.net>.

List-Subscribe:

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

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/=
more
complex that it seems.

And as for Dean's response: Wow! But he must know something, since he=
invented
(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=
clever.
Bloody clever, actually. I wish I'd thought of that.

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

On Mon, 4 Jul 2005 00:46:13 +1000, Robert Jordan wrote:

Hi Robert

> Some SQL dialects are almost turing-complete and you want to tell
> us SQL isn't complex?? You seem to mix up the complexity of the SQL-
> language with the complextity of "programs" written in SQL.

But we're talking about 2 different things!

English is complex too, more so that SQL, now that it (English) has over
1,100,000 words.

A class to handle an object which is an SQL or English sentence will just be=
too
complex to justify the /approach/. I'll say it again, it's possible (I=
guess),
but how long will it take (human time), how much code will it take, etc,=
etc?

Look at the sheer volume of DBI-related code on CPAN. Are those authors all=

idiots, that they have overlooked or failed to design/write a little module=
to
solve this problem?

Sorry, but I remain convinced you'd be better off spending your time=
elsewhere.

The fact the Sebastian and others are prepared to contribute to Perl via=
modules
on CPAN is something I, and many others, appreciate.

It's the choice of problem which we think is misguided.

Next!

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

I think you should have another glance at
http://unf.be/~sili/projects/sqlbuilder/sql.html considering I've made
some updates. It doesn't seem like I've been clear or done a good job
conveying my purpose.

SQL, the syntax, is not complex. I am only proposing a set of objects
which allow one to manipulate it as necessary without imposing any
vendor-specific methods. I wish to be able to manipulate SQL any way I
want and keep control over it. Regarding the complexity of
representation, you are free to browse the code I have written so see
what I mean by "it is not complex." I think I have written enough code
that one could start experimenting with it.
http://unf.be/~sili/projects/sqlbuilder/SQL-Builder/lib/sele ct_test.txt
has a working exmaple proving SQL is not too complex to represent.
There is very little code required to do the job and I am nearing
completion and have only been playing in my spare time for that least
week or two.

No, the DBI (and related) module authors are not idiots, but I do
believe many of them did in fact overlook a little module to solve this
problem. Please see sql.html for more on this. I believe that
SQL::Builder could provide a more solid foundation for reasons
illustrated in the article.

Do you use IRC or anything like that? I'd like to chat with you there
if possible.

- sebastian

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

Hi Ron,

> Look at the sheer volume of DBI-related code on CPAN. Are those authors all
> idiots, that they have overlooked or failed to design/write a little module to
> solve this problem?

The fact that a lot of CPAN authors did't came up with a solution
for this problem doesn't mean that the problem is unsolvable.

Look, where is the difference between

1) generating HTML using HTML::TreeBuilder or CGI.pm or ... (there
must be at least 10 other modules for this problem ;-)

2) generating SQL using SQL::Builder or other similar module.

3) (abstract) generating code for language L using an AST-like
approach.

4) (abstract) generating code for languages L(1)...L(n) using
an AST-like approach for the language L and backend drivers
for L(1)...L(n).

> Sorry, but I remain convinced you'd be better off spending your time elsewhere.
>
> The fact the Sebastian and others are prepared to contribute to Perl via modules
> on CPAN is something I, and many others, appreciate.
>
> It's the choice of problem which we think is misguided.

I really appreciate the warning you (as a CPAN author) gave him,
but I won't say he is misguided.

Rob

ps: just a shy question: you also don't like O/R mappings, isn't it? :-)

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

On Mon, 4 Jul 2005 20:03:16 +1000, sebastian wrote:

Hi Sebastian

> No, the DBI (and related) module authors are not idiots, but I do

I phrased that badly.

> Do you use IRC or anything like that? I'd like to chat with you
> there if possible.

Nope. Sorry.

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

On Mon, 4 Jul 2005 20:47:16 +1000, Robert Jordan wrote:

Hi Rob

>> Look at the sheer volume of DBI-related code on CPAN. Are those
>> authors all idiots, that they have overlooked or failed to
>> design/write a little module to solve this problem?

Well, I should have phrases that differently :-).

But I think the point I was trying to make remains valid.

> The fact that a lot of CPAN authors did't came up with a solution
> for this problem doesn't mean that the problem is unsolvable.

Very true.

> Look, where is the difference between
> 1) generating HTML using HTML::TreeBuilder or CGI.pm or ... (there
> must be at least 10 other modules for this problem ;-)

Hmmm.

I've tried some of these modules, and now that you mention it, I think I=
have
stopped using them for the very same reason I'm uneasy with Sebastian's=
approach
- complexity.

Nowadays I write the HTML in it's own file, and use HTML::Template (5 stars)=
to
do macro expansion to populate fields within the HTML.

With SQL my solution is different, I use DBIx::SQLEngine (4 stars, no=
handling
of indexes), but these 2 solutions are simply different, and appropriately
different, ways of dealing with what I see are the problems.

> I really appreciate the warning you (as a CPAN author) gave him,
> but I won't say he is misguided.

Thanx. Well, we can agree to disagree.

> ps: just a shy question: you also don't like O/R mappings, isn't
> it? :-)

No, I'm just a slow adopter of new-fangled things.

Here's how I wrote code using /only/ CGI.pm, before I'd ever heard of fancy=

modules....

print=09$q -> header({type =3D> 'text/html;charset=3DISO-8859-1'}),
$q -> start_html(),
$q -> start_form({action =3D> $q -> url(), name =3D> 'cgi_bug'}),
'CGI.pm V ', $CGI::VERSION,
$q -> br(),
'Old value: ' . one($q -> param('fake_name'), '2nd param'),
$q -> br(),
'New value: ' . $q -> textfield({name =3D> 'real_name', size =3D> 20}),
$q -> br(),
$q -> submit(),
$q -> end_form(),
$q -> end_html();

See how this resembles Sebastian's code in that it assembles the result out=
of
little bits and pieces? But that's a software technology with a fine future=

behind it! I.e. it's history.

That's the joy and pain of programming - we're always having to learn new=
stuff.

Even today I'll be using Class::DBI for the first time in a real project...

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

Howdy Ron,

I'm having a hard time understanding the comparison of SQL::Builder to
CGI.pm. I agree with what you said about a result being built with bits
and pieces, but there are some key differences which I won't go into
because I've been repeating it over and over and is stated in the
article I wrote. On one note, about building with bits and pieces,
that's how good software begins - bottom-up, not top-down. SQL::Builder
can lay the foundation for future modules that can abstract out much of
the work you may see in my code examples. The problem with existing
modules is that they do not have flexible foundations. If they
incorporated SQL::Builder, they could always leave open the option to
use SQL::Builder when the the module is inept.

I appreciate your feedback and all, but I'm still not quite sure what
your objections are. I think one of the major points of SQL::Builder is
that it has not been tried before. No one has attempted to provide such
granular control to SQL. It seems that module authors too eagerly set
their sites on a goal (abstracting SQL or some such) and do whatever is
necessary to reach their immediate goal, neglecting the needs of
others. Their products are inflexible and too often improperly built.
They neglect to build a solid foundation for what they are doing. XML
has strctured interfaces to it, so why wouldn't it make sense to create
structured interfaces to SQL? Of course, using the proposed w3 DOM
model at times sucks, so people have made abstractions and various
utilities to make the job easier. One thing that some of those XML
modules provide that SQL abstraction implementations don't have, is the
underlying structures for manipulating data - in the case of SQL, it
would be SQL::Builder. That underlying structure makes them so much
more powerful for reasons illustrated in the article I wrote.

You said that SQL::Builder adds complexity, but I'm not sure what you
mean by this. SQL::Builder provides interfaces to manipulating SQL.
SQL::Builder, per se, is not complex. Its goal is to avoid complexity
by providing necessary interfaces for SQL manipulation and help
programmers avoid repeating themselves and mangling strings and vague
data structures.

- sebastian

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

On Tue, 5 Jul 2005 17:09:45 +1000, sebastian wrote:

Hi Sebastian

I think we could go on and on about this, without ever convincing the other=

party.

What's important? It's your idea and code, so do whatever you want.

If it turns out well, tell the world. If it doesn't, you will at least have=

learned much.

What we /don't/ have is something mathematically simple like

if (5 == 6) # My bias :-).
{
=09continue development
}
else
{
=09stop and make a cup of tea
}

What we do have is conflicting opinions. That's what makes it harder to=
explain
my unease.

One of my reasons for responding was to encourage you to clarify your ideas,=
and
I see you made such a comment on the site. Good!

You mentioned DBIx::SQLEngine earlier, and others. What worries me is that
you've ended up with code which may have a different syntax than used by=
other
packages, but does not appear to offer any advantages (although I get the=
strong
impression that you're convinced it does off advantages).

Look again at your code:
sub get_fulltime_employees_with_md5social_born_after {
my $birthdate =3D shift;
my $q =3D get_fulltime_employees_with_md5social();

#qop returns an Operator object
#qtext returns a text object, mostly just needed when=
escaping
is necessary $q->where->list_push(
qop('>', qcol('birthdate'), qtext($birthdate))
); return $q;
}

I count 22 sub calls in there :-((((((((((((.

The fundamental question is: Why should I use this when I can use 1 line of=
SQL?

Exactly this is what I mean by your approach being complex.

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

Thanks for providing clearer reasoning. Now I can attempt to assimilate
you.

"Why should I use this when I can use 1 line of SQL?"

Good question! But it's not something I haven't already answered
several times, perhaps I wasn't clear in the article. Once you strigify
your query it becomes virtually immutable. Suppose you have the
following:


sub get_fulltime_employees_with_md5social_born_after {
my $birthdate = quote(shift);
return "
SELECT *, md5(ssn)
FROM employees
WHERE
birthdate > $birthdate
ORDER BY birthdate DESC
LIMIT 10
"
}

This function takes an argument and returns a string. What if I want to
sort by ASCending and LIMIT 50? I'm screwed. It is not possible to
safely modify the query without a parser which is a job for your
database vendor. In this case, I will probably end up copying the query
or modifying it to accept some argument to control sorting and the
LIMIT clause. Then I'll need query to modify it to do a join to get all
employees at offices in the 90210 zip code. Each time I modify this sub
I am affecting the performance of X queries. You fall into a trap, your
code becomes brittle, difficult to read, etc - bottom line is that your
code sucks. If you were to use a templating or SQL abstraction method
you fall into traps discussed in the article.

Of course, if your query is never going to change because demands never
change or the performance overhead is too high, then you have little
choice.

SQL::Builder is about providing mechanisms that allow you to make the
choices you want without worrying too much about regret and the ability
to change later. If my
get_fulltime_employees_with_md5social_born_after() were used, you could
change the ORDER BY or LIMIT clauses, you could add a join to it, you
could add or remove columns, you could use it in a union with another
query, etc.

"Exactly this is what I mean by your approach being complex."

I would much prefer the above method be used than using the stateless
and immutable query string if I had the choice. I believe if I had the
choice, using SQL::Builder would simplify the situation for all the
reasons I've been iterating. Using a SQL string as you might prefer is
what will complicate things; you will lose reusability and flexibility.

If you're only goal was to help discussion then you've accomplished it
and I am thankful. However, you've made other comments and I wouldn't
be doing everything I can if I didn't question your reasoning. Given
your reasoning and objection to sql-builder in your last post, I would
assume that the needs you've experienced are generally simple or not
likely to change. Either that, or you have no problem writing bad code.
If you don't care about reusability, flexibility, scalability, etc,
then SQL::Builder isn't for you. If you do care about those things,
then please share with us why SQL::Builder is not reusable or flexible.
You might consider using my article as a guideline for determining
these reasons, which may be difficult considering I've given reasons
why your suggested modules are inept. Either way it may make for good
discussion.

Thanks for your input

- sebastian

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

On Wed, 6 Jul 2005 09:50:34 +1000, sebastian wrote:

Hi Sebastian

You sound bitter, and that's not where I would want to end the discussion.

I hope that in time you'll see I'm just offering an opinion, and am not=
trying
to beat you in some sort of contest.

If you module goes on to bigger and brighter things, congratulations in=
advance
:).

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

Ron, sorry, I don't mean to sound bitter. I hope you don't take
offense. I am trying to assess your reasoning and determine its
validity, and also what you have to say about my reasoning.

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
scratch?

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:

$sqldb->define_named_queries(
'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(
$sqldb->interpret_named_query('get_all_with_md5social_born_a fter',
$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...

$sqldb->define_named_queries(
'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",
"get_all_born_after"]
},
);

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

m.cavalletto@gmail.com wrote:
> 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
> scratch?
>
> 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:
>
> $sqldb->define_named_queries(
> '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(
> $sqldb->interpret_named_query('get_all_with_md5social_born_a fter',
> $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...
>
> $sqldb->define_named_queries(
> '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",
> "get_all_born_after"]
> },
> );
>
> Would that address the need you've described?

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

On Thu, 7 Jul 2005 08:22:18 +1000, sebastian wrote:

Hi Sebastian

> Ron, sorry, I don't mean to sound bitter. I hope you don't take
> offense. I am trying to assess your reasoning and determine its
> validity, and also what you have to say about my reasoning.

Absolutely no offence taken here.

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

> Suppose I was writing a system that would allow me to write
> portable queries.

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

> 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?

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.

-Simon

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

"sebastian" writes:

> There are some philosphies which assert that SQL should not be mixed
> with Perl (think phrasebooks), but this can't hold up for long in a
> system that generates dynamic queries -- for example, dynamic reports
> or searches.

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.

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

Robert Jordan writes:

> Sebastian's solution has at least 2 worthwhile pros:
>
> 1) code written using his solution is SQL dialect invariant,

his code reminds me of Alzabo quite a bit

> > 2) query objects have a state, which is a big gain compared to a
> almost stateless SQL statement.

Can't a hash be modified later to make new statements? Isn't a hash
more perlish than a bunch of objects and methods.. ooops shouldn't
have asked that. :)


--
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 andimproving Class::DBI

Robert Jordan writes:

> Hi Ron,
>
>> Look at the sheer volume of DBI-related code on CPAN. Are those
>> authors all idiots, that they have overlooked or failed to
>> design/write a little module to solve this problem?
>
> The fact that a lot of CPAN authors did't came up with a solution
> for this problem doesn't mean that the problem is unsolvable.
>
> Look, where is the difference between
>
> 1) generating HTML using HTML::TreeBuilder or CGI.pm or ... (there
> must be at least 10 other modules for this problem ;-)

Either way a tree of computation is expressed. With TreeBuilder, it's
a data structure to be passed to new_from_LOL() with CGI.pm (or
HTML::Mason), you create a tree of API calls.

--
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 andimproving Class::DBI

Ron Savage writes:

> Nowadays I write the HTML in it's own file, and use HTML::Template
> (5 stars) to
> do macro expansion to populate fields within the HTML.

why not transform the tree without molesting your pristine data? try
HTML::Seamstress, XML::LibXML or some other non-invasive solution.

--
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 andimproving Class::DBI

"sebastian" writes:

> This function takes an argument and returns a string. What if I want to
> sort by ASCending and LIMIT 50?

any of the searchbuilders do that - Recordset, SearchBuilder, etc, etc.

> SQL::Builder is about providing mechanisms that allow you to make the
> choices you want without worrying too much about regret and the ability
> to change later. If my
> get_fulltime_employees_with_md5social_born_after() were used, you could
> change the ORDER BY or LIMIT clauses, you could add a join to it, you
> could add or remove columns, you could use it in a union with another
> query, etc.

DBIx::Recordset does all that and have for over 7 years...

> If you don't care about reusability, flexibility, scalability, etc,

What can't you represent about a query in a Perl nested hash? Drop the
object fanfare. It's slower and less direct.

--
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 andimproving Class::DBI

"m.cavalletto@gmail.com" writes:

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

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 andimproving Class::DBI

"m.cavalletto@gmail.com" writes:

> Patches welcome...

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:
> I've tried a number of "solutions" to these problems, but my methods
> usually fell short. With DBIx::Abstract, DBIx::SQLEngine, or
> SQL::Interpolate you're forcses to use vague data structures. At times
> SQL can be quite complex, and remembering tiny little differences in
> data structures used in those modules can be a pain. In some of these
> modules you might also have to write a bit of SQL. The problem with
> that is, as soon as you use a string constant, you lose flexibility and
> most likely the ability to modify your query at a later time.
> Class::DBI does a nice job hiding the database layer, but at some point
> you have to start writing SQL because it doesn't support mechanisms to
> efficiently hide and reuse query logic.

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:
http://search.cpan.org/dist/SQL-Interpolate/lib/SQL/Interpol ate.pm .

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;

my $ROOM_TEMPERATURE = 25;

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

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

my $q = base_query();
smart_people_report($q);
active_report($q);

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

# OUTPUT:
# $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
well).

best regards--davidm