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

[Emanuele Zeppieri]

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.

What criteria are you using to evaluate which mapping is better?

It seems that you are talking only about readability by humans, which is
the less important thing, really.
(If you want something readable by humans, just use straight SQL
strings: it's the best thing you can do, anything else will force you to
adopt some kind of mapping/translation.)

The SQL::Abstract mapping instead is *not* intended to be expressive for
humans (well, it's not its primary goal at least), it's intended mainly
to be easy to be manipulated *by code*.

It makes it easy to write code which creates SQL queries: for example,
it makes it trivial to write a program which generates the corresponding
query from a user interface (such as a typical search form for example).

It's easy just because manipulating Perl data-structures from Perl is
easy, direct, clean and error-proof.

On the other hand, I may be wrong (I'm just judging by your messages
here in the list) but it seems that the syntax you are proposing
requires instead string manipulations to automate the queries
generation, which is a thing that leads straight to unmaintainable code.

Cheers,
Emanuele.