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

[Mark Lawrence]

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

Believe me, I would have much preferred sending patches (which I have
done for other CPAN modules) than start over from scratch, but nothing
I've found comes close to doing what SQL::DB does.

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

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

Regards,
Mark.

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

-- 
Mark Lawrence