[Dbix-class] retrieve_from_sql

[Matt S Trout]

On Mon, Aug 01, 2005 at 09:09:40PM +0200, Emanuele Zeppieri wrote:
> > > 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.

Ok, that's fair enough. I'm a great believer in "let the user get at the
raw stuff if they want" but also in "but make sure they know that's what
they're doing and the downsides are their problem" - hence wanting to mark
it non-core at some stage.
 
> > 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'}
> 			]
> 		]
> 	);

I do see your point here, but I think I still prefer building it up as
a logical query myself. Plus expr syntax would allow

my @stuff = Foo->search(expr sub { 
               (($obj->f1 != $v1 & $obj->f2 != $v2) | $obj->f3 != $v3) });

which is arguably just as clear as the plain SQL.
 
> 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 ;-)

Ok, you seem to have this assumption all the way through what you're saying
(and it's at least in part my fault) so I'm going to say this here once:

We support an Abstract-like syntax. We do *not* use SQL::Abstract directly
to do this. All these features will need to be added to the Abstract-like
syntax we support before we're done.

Also, skim the docs for Class::DBI::Sweet - it already does JOINS within
Abstract syntax, and the code is mine. Most of the hooks for the same thing
are already in DBIx::Class - so expect join-on-search and prefetch to
turn up fairly soon, then aggregates and subqueries later.

Maybe you could provide some use cases for joins for me to incorporate
into my planning rather than just telling me a module we aren't using
doesn't support them? :)

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

No, I was suggestion you could write query assembly code to generate
really complicated queries in a more modular way.

There is no *have to* here, it's a matter of taste. DBIx::Class already
does this internally - the condition given in an ->add_relationship is
composed with the user search condition when you do

$obj->search_related(...);

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

It also makes composing query fragments and abstracting joins out of
queries much easier, and provides a host of other programmatic manipulation
possibilities.

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

Rubbish. If you express them as static SQL most of the join inference stuff
won't work and in order to map it usefully to returned objects then you're
going to have to supply almost as much metadata to the result->object
inflation stuff that it'll be vastly complicated.

In any case, 99% of set_sqls I've seen could be expressed far more cleanly
as a couple of ->add_relationship calls and a view definition; you'll see
what I mean when I write the view code.

-- 
     Matt S Trout           Website: http://www.shadowcatsystems.co.uk
  Technical Director        E-mail:  mst (at) shadowcatsystems.co.uk
Shadowcat Systems Ltd.