[Dbix-class] [OT][ANNOUNCE] SQL Generation with SQL::DB

[Mark Lawrence]

On Wed Sep 12, 2007 at 05:23:28PM +0100, Matt S Trout wrote:
> On Thu, Sep 06, 2007 at 02:25:44PM +0200, Mark Lawrence wrote:
> > 
> > The second reason is extensibility. Take for example
> > SQL::Abstract::select (to be fair, the other query types are ok):
> > 
> >   select($table, \@fields, \%where, \@order)
> > 
> > With such fixed position fields how can we extend this to handle
> > "FROM t1 AS n1,t2 AS n2" statements? Does the caller have to keep track
> > themselves of the table aliases? How does one handle multiple JOINs
> > with optional ON/USING conditions?
> 
> If you don't know how that works, you didn't read the DBIx::Class docs.

What does DBIx::Class have to do with this? I'm talking about
SQL::Abstract, and the only mention of further reading in the SQLA
documentation is DBIx::Abstract, which also doesn't appear to enable
multiple INNER/OUTER LEFT|RIGHT JOIN statements.

You've got some pretty high expectations for newcomers to SQLA if
they have to get all the way to DBIx::Class::Manual::Joining (or
maybe somewhere else I haven't been to yet), where one finds out
queries generated anyway don't even use JOIN. But that is all beside
my point.

My point is that having to force more levels of $table and \@field
values into the %where hash is not a clean extension to the API.
The 'hidden' definitions inside %where are second class citizens.
They are not visible at the time of the select(), and the implication
is that tables/fields are inside a WHERE (...) statement which is
misleading.

A four field fixed-position function prototype for select() simply does
not allow for what is possible with SQL. But this you know already.

> > As for not sending patches? The above limitations are in my opinion
> > fundamental. The amount of patching required would result in a completely
> > different API - effectively a new module with no good chance for
> > backwards compatibility.
> 
> Yes. I and the authors of a number of other ORMs have been working for some
> time (largely on this list) to work out an effective back-end explicit AST
> which the SQL::Abstract API can be targeted to so code can continue to be
> shared without being limited by the current API - one along the lines of
> the SQL::DB one but using optree introspection to avoid the horrible &/|
> hack you've carried over from Tangram is planned.
> 
> Also, your comment of "no good chance for backward compatibility" merely
> means -you- can't see a way to handle backcompat. I can :) - I think the
> existence of DBIx::Class::CDBICompat proves we're rather good at it, too.

Ok, badly phrased. My backcompat statement was meant to imply that
the internals of SQLA would have to be changed so much that the effort
would be far greater than the cost of starting from scratch. And that
was in response to your question about why not send patches. When creating
a new API it rarely makes sense to start with an old codebase (in the
same way that DBIC 0.01 wasn't a patch against CDBI).

For the record, I think the DBIx::Class backcompat efforts have been
great, which I took advantage of when I moved from CDBI to DBIC
(just before I moved from DBIC to Tangram, just after I toyed around
with RDBO, long after I stopped using hand-coded classes, ... ;-)

> > > prior art - and when the rest of the perl ORM/etc. community is finally
> > > moving towards standardising on these two and maintaining them together.
> > 
> > I was unaware that SQL::Abstract had become the defacto standard, so
> > you make a valid point with regards to disrupting the concentration of
> > effort, although there are also valid arguments to be made for
> > diversity.
> > 
> > However I do not intended for SQL::DB to replace any existing system,
> > but was posting it simply because I find it very suitable for the tasks
> > I commonly want to perform, and thought others may also. I am also
> > posting based on your invitation on the Tangram list to do so :-)
> 
> Sure. It's just a shame you didn't join this list earlier, you'd have seen
> the discussion where we started working on a common solution :)

I've had a bit of a look but I can't find any outcomes of the
discussions. Are there any design documents posted somewhere? Something
about what the AST structure looks like? Or maybe Darren's Muldis::DB is
intended to be the new 'it'? (By the way Darren, if you are listening,
I've read the docs three times through and still don't quite get what
'it' is. An Intro with _Examples_ is badly needed...)

If there is any kind of real standard (not a defacto one) please let me
know, as I like standards. Read them for breakfast. Dream at night of
all code conforming...

> > > How do you handle cases where you don't know the column list and need to
> > > just do a SELECT '*' or similar?
> > 
> > SQL::DB is based upon the table being modelled so the columns are always
> > known. The $ARow->_columns() method is the same as ($ARow->col1,
> > $ARow->col2, ...).
> > 
> > But to answer your question properly, there is no way at present to
> > generate a literal "SELECT '*'". In what situations do you need to
> > select all columns from a table with an unknown structure? Or is it
> > a question of efficiency?
> 
> It's a question of "somebody might need that so let's support it". Otherwise
> all that'll happen is somebody who needs that function will go off and ignore
> all the existing code and waste their time writing something new ... :)

Ah, that kind of situation. Sounds familiar. The next version of
SQL::DB::Schema should take scalars in all places.

Mark.
-- 
Mark Lawrence