[OPEN-ILS-DEV] OpenILS::Util::StoredProc

[Mike Rylander]

On Mon, Dec 20, 2010 at 12:04 PM, Galen Charlton <gmc at esilibrary.com> wrote:
> Hi,
>
> I propose that we carve out a namespace for Perl code to be used in stored procedures.  My idea is that OpenILS::Util::StoredProc (or whatever it gets called; I'm not entirely happy with that name) would be installed on Evergreen database servers and that most PL/PERL(U) functions would just be thin wrappers to routines defined in that module.  This would give us a few nice things:
>
> [1] It would allow us to remove at least two known cases of code duplication (naco_normalize and arguably entityize) and removes a vector for future code duplication.
> [2] It would make it easier to write test cases for Perl stored procedures, at least for functionality that doesn't actually care that it's running as a stored procedure.  This applies to most of the PL/Perl stored procedures currently in use, i.e., those that just manipulate strings or MARC::Record objects.
> [3] It reduces the possibility that the definition of a Perl stored procedure would vary between the SQL script that installs it and the schema update script(s).
>
> The trade-offs would be:
>
> [1] This adds another moving part during installation and upgrade, particularly for multi-server setups, and could lead to hard-to-debug problems if one forgets to update OpenILS::Util::StoredProc on the database server.
> [2] It adds another level of indirection to wade through during debugging.
>

I'm for it, for the reasons you state above.

As far as the namespacing is concerned, would there be objection to
putting it under OpenILS::Application::Storage::Driver::Pg, perhaps as
OpenILS::Application::Storage::Driver::Pg::StoredProcedures, and then
load the right module for testing based on the driver name
(defaulting, obviously, to "Pg") specified?  The tricks used in
O::A::Storage seem simple enough, IMO, to reuse in a test harness
(though that /does/ use on UNIVERSAL::require ... this can be worked
around).

The SQL files live in a similarly named hierarchy
(Open-ILS/src/sql/Pg/) and can contain directly the name of the
db-specific module under O::A::S::D -- they're already db-product
specific.

Of course, we /could/ start the debate about removing db product
abstraction, as I don't know of any other db that supports perl as a
stored proc language (to mention but one feature we depend on right
now, though far from the only), but that seems like a lot more work
than we want to get into today... :)

-- 
Mike Rylander
 | VP, Research and Design
 | Equinox Software, Inc. / The Evergreen Experts
 | phone:  1-877-OPEN-ILS (673-6457)
 | email:  miker at esilibrary.com
 | web:  http://www.esilibrary.com