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

[Matt S Trout]

On Thu, Sep 06, 2007 at 02:25:44PM +0200, Mark Lawrence wrote:
> > What justification is there for re-inventing SQL::Abstract and SQL::Translator
> > rather than sending patches? I like the interface but I don't see the
> > advantage of re-inventing all the underlying code when there's already good
> 
> I think there are at least two good reasons for having something other
> than SQL::Abstract[1]
> 
> The first you have already mentioned - the interface. The basis of
> SQL::Abstract is that it converts data structures into queries. So
> you are forced to deconstruct your logic just so that SQL::Abstract can
> reconstruct it again for the database?
> 
> The perldoc for SQL::Abstract itself shows just how difficult this is
> even for relatively simple cases:
> 
>     %where = (
>         lname => {like => '%son%'},
>         age   => [-and => {'>=', 10}, {'<=', 20}],
>     );
> 
>     Again, remember that the -and goes inside the arrayref.
> 
> There is another *implicit* AND between the two conditions. At first
> (and second glance) this is very different from the actual intended SQL,
> not to mention the complexity of other artifical constructs like -nest.
> A much better mapping can be obtained with Perl's built-in logic operators.
> 
> 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.

> UNIONs? SQL is simply not quite as
> structured (if you'll pardon the pun) as SQL::Abstract forces callers to
> be.
> 
> 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.

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

> > 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 ... :)
 
> [1] I don't know SQL::Translator so can't comment there. However it
> looks to me like it does things backwards. SQL into Perl classes?
> Perl classes are nearly always a superset of the SQL in terms of of
> columns and object functionality, so to me it makes more sense to go
> in the other direction.

Look at how DBIx::Class uses it.

-- 
      Matt S Trout       Need help with your Catalyst or DBIx::Class project?
   Technical Director    Want a managed development or deployment platform?
 Shadowcat Systems Ltd.  Contact mst (at) shadowcatsystems.co.uk for a quote
http://chainsawblues.vox.com/                    http://www.shadowcat.co.uk/