[Dbix-class] SQLA refactor: proposal

[Darren Duncan]

At 12:13 PM -0600 11/27/07, Wade.Stuart at fallon.com wrote:
>Darren Duncan <darren at DarrenDuncan.net> wrote on 11/26/2007 10:26:04 PM:
>  > I alternately or additionally recommend generating SQL stored
>  > procedures where it is reasonable to do so.
>
>I do hope you are not recommending any type of dynamically created stored
>procedure backend?  The idea of my ORM making fugly dynamic stored
>procedures all over the place makes me long for SQL + DBI.  As far as my
>view on it -- if I want to optimize something down to a SP I would create
>it by hand (usable in my perl app and other db apps) and then call it via
>DBIC's already exposed API.
>
>  > As for DBMSs without stored procedure support, or where you don't
>>  want to alter the pre-existing schema, well then there is the
>  > generated Perl subs solution that I previously mentioned.

No part of my suggestion involved "making fugly dynamic stored 
procedures all over the place".

When SQL stored procedures are to be generated and persistently 
stored in a database, that only would happen when you explicitly ask 
it to happen, as per deploying a schema / creating tables in a 
database.

Also, Muldis D would be defining the stored procedures in a 
deterministic fashion, so you can know in advance what the resulting 
SQL interface should be, so that the SP can be reusable with all db 
apps not just the Perl ones.  The SP structure is also fairly 
deterministic as well, since you explicitly choose the names for all 
your parameters and variables et al as well (and for the table 
generators, you also explicitly choose names for your foreign/etc 
constraints), like when writing the SQL yourself.  After all, a 
relational database is supposed to be application-agnostic, like a 
library, in the general case.

When you don't want to change your database schema, the generated 
stored procedures can be temporary and/or anonymous; Oracle at least 
supports anonymous versions in that they are as transient as 
extra-procedure statements that a client executes against a server, 
but having multiple statements.

And barring support for the latter, then as I first suggested, the 
procedure can be faked on the Perl side, as a Perl anonymous routine, 
and no DBMS schema changes happen at all, this being essentially what 
you prefer to do.

Now, my more specific recommendation on this regard is context 
specific.  Generally speaking, there are 2 kinds of SQL statements 
that one would feed to a DBMS.

One kind is one that is effectively part of an application's source 
code (or the source code shared by all database-using applications as 
per a library); this kind of SQL is generally known in advance, and 
would be the best candidate for rendering as actual SQL stored 
procedures where appropriate, and schema deployment or schema update 
time.  These are not generated from user input and you would 
generally be fine-tuning them a lot more, so they wouldn't be fugly.

The other kind is SQL that is generated based on runtime user input, 
and that SQL is only used temporarily then discarded.  The main 
example for this is when the application is a generic database access 
tool such as Toad or SQL*Plus et al.  In this case, I recommend just 
transient SPs or faking it in Perl, but no schema changes in any 
event.

I think most of the time users would not be affecting the structure 
of the SQL queries.

Note that I am assuming throughout this whole discourse that any SQL 
being generated makes use of bind parameters where the values used in 
it would vary based on user input, so that the same kind of query, 
aside from input values, would only be generated just once and 
reused.  Which is the proper way to do it.  This approach translates 
to a stored procedure, whether in SQL or in Perl, paradigm very well, 
in which the parameters of the stored procedure correspond to the 
bind parameters / what might be variable at runtime.

Obviously, the utility of using stored procedures et al, can be 
affected by some general design decisions of the Perl programmers; it 
won't work as well if they don't use SQL parameters, and use of those 
has always been a good idea even if no AST or ORM was in the tool 
chain.

So, I defend generating SQL stored procedures from ASTs when done for 
good reasons, but regardless I defend the users having a choice 
between doing that or faking it in Perl instead.  Flexibility is one 
name of the game.

One more thing.  When you have a good AST, and a good generator, it 
is perfectly fine to use that rather than hand-crafting SPs.  The 
analogy is like with language compilers.  Sure, lots of people can 
choose to write assembly language because they don't trust their C 
compilers et al to do a good enough job with tuning, but the vast 
majority of the time, the compiler will do it 99% good enough, or 
will typically do it better than a non-expert assembler, and 
certainly do it with less developer effort.  If hand-crafting SQL is 
still necessary for general (non-esoteric) situations, it is because 
the ASTs and generators aren't good enough, in general.

For debugging SQL output, I'm sure anything that's generated can also 
be pretty-printed ala Perltidy.

-- Darren Duncan