[Dbix-class] retrieve_from_sql

Emanuele Zeppieri ema_zep at libero.it
Mon Aug 1 21:09:40 CEST 2005


> -----Original Message-----
> From: dbix-class-bounces at lists.rawmode.org 
> [mailto:dbix-class-bounces at lists.rawmode.org] On Behalf Of 
> Matt S Trout
> Sent: Monday, August 01, 2005 4:08 PM
> To: dbix-class at lists.rawmode.org
> Subject: Re: [Dbix-class] retrieve_from_sql
> 
>
> > On Mon, Aug 01, 2005 at 11:43:20AM +0200, Emanuele Zeppieri wrote:
> > Leave the user the opportunity to resort to raw SQL if she/he wants,
> > please.
> 
> The opportunity where the storage engine supports it, yes. 
> The guarantee that
> every storage engine will I don't want to make because it's 
> IMO a rather heavy
> restriction to what can be achieved.

Of course I didn't expect you to provide an SQL front-end for every
possible storage back-end supported! ;-)
I was just referring to the the /straight DBI backend/, and when you
said "abstract away the database layer entirely" I thought you were
including also it.
I misinterpreted your words, sorry.

> > Nor SQL::Abstract can satisfy any need about SQL queries: 
> > it can become
> > extremely convoluted for complex queries.
> 
> [...]
> 
> Also, I'm not sure it gets that convoluted if you modularise 
> your query
> assembly (which DBIx::Class will be helping you with more 
> than a little :).
> 
> Can you provide me with a concrete example of where 
> SQL::Abstract is less
> legible than writing the SQL yourself? Hard use cases are 
> always welcome :)

It is not a matter of legibility but, as you are talking about
legibility, one could easily state that SQL::Abstract is always *less*
legible, because it uses a functional-like prefix syntax instead of the
infix SQL syntax, if not other.

For example for me this simple SQL string:

	$where = 'f1<>? and f2<>? or f3<>?'
	#OK, the values are somewhere else

it's always more /legible/ than its SQL::Abstract equivalent, either in
this form:

	@where = (
		{
			f1 => {'!=', 'v1'},
			f2 => {'!=', 'v2'}
		},
		{
			f3 => {'!=', 'v3'}
		}
	);

and in this form:

	@where = (
		-or => [
			f3 => {'!=', 'v3'},
			-and => [
				f1 => {'!=', 'v1'},
				f2 => {'!=', 'v2'}
			]
		]
	);

Another example is an SQL query with a subquery, like this:

	SELECT f1 FROM t1
	WHERE
		EXISTS (SELECT f3 FROM t2 WHERE f3=t1.f3 OR f3=?)
		OR f2 <> ?

for which I don't even see any pure-SQL::Abstract counterpart.
SQL JOINs (with the ON clause) is another thing SQL::Abstract can't
handle (now think of a combination of subqueries and JOINs ;-)
Then, if to be able to express my queries with SQL::Abstract I have to
"modularise" them as you said (if I understand correctly what you mean),
this only adds an extra unnecessary level of intricacy.

So, SQL::Abstract is great, but *not* because it is more /legible/ than
raw SQL, but because it lets you (quite easily) encapsulate the SQL
queries into Perl data structures, thus making the generation of the
queries from your Perl code much easier.
Therefore this means that SQL::Abstract is useful especially when the
queries are dynamically created by your code (a typical such usage
scenario happens with advanced user search forms, which let the user
customize the search by adding fields and search criteria etc.).

In other scenarios such as an application CDBI classes instead, the
queries (used for example in retrieve_from_sql() and/or set_sql() ) are
mainly /static/, so you hardly get any advantage by expressing them with
SQL::Abstract instead of raw SQL, not to mention the cases where
SQL::Abstract can't simply express them.
Also consider that once encapsulated in an application CDBI-class, the
SQL does not pollute your Perl code at all, since the CDBI-class
definition is just a mere separated kinda /configuration/ file (or
package, at least).

That's why I think it's a good idea to permit the user to resort to raw
SQL (if the storage back-end supports it, of course ;-)

Ciao,
Emanuele.




More information about the Dbix-class mailing list