At 12:13 PM -0600 11/27/07, [EMAIL PROTECTED] wrote:
Darren Duncan <[EMAIL PROTECTED]> 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
_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/[EMAIL PROTECTED]
