[Zope] storyserver database access

[Christopher Petrilli]

On 12/9/99 3:57 AM, Martin van Nijnatten at euroibc@solair1.inter.NL.net
wrote:

> I have been told:
> "As you know Vignette Story Server uses TCL/Ticle as its Proprietary
> Scripting Language & which allows
> PL/SQL Procedure Calls to the Oracle DB, Since the latest version of
> PL/SQL supports the Virtual
> Table/Row manipulation, which reduces the I/O time consumtion & deadlock
> Situations, also PL/SQL
> procedure allows higher volume of data manipulation with in the Cursors
> itself"

I honestly know *nothing* about using the virtual table/row manipulations in
Oracle 8 (nor much of the other wizz-bang-new-features), however I can
address our plans for dealing with stored procedures.

First, a bit of background.

Stored procedures (in all modern databases) are implemented in a superset of
SQL that provides for more functionality.  SQL99 calls these "SQL-Invoked
Routines".  One of he benefits of most implementations (but not necessarily
all) is that they have gone through the parse and query optimizer machinery,
which is what consumes as much as 90% of the resources in a query.

So the primary benefit of using stored procedures is that you can reduce
your query time by a huge amount.  Also, it lets you focus controls across
applications.  Some environments *only* allow access via stored procedures
so as to provide even more complex security restrictions.

So why doesn't Zope handle it today?

Well, mostly it has to do with the fact that stored procedures (until SQL99)
were not standardized.  They're still not in reality.  This creates a
complex problem, and one that many people have simply avoided.  This is
changing however.

What are we planning?

Well, since SQL-related things are my domain, and we've done a lot of
thinking about this, here's basically where we intend to go when we have the
resources/customer requirement.  We'd obviously entertain other people doing
this for us :-)

What we have in mind is something like a "SQL Stored Procedure Method" (yes
I know the replication of terms is difficult, we'll find some better name
I'm sure).  What it provides is a SQL Method-esque interface to stored
procedures, with the same kind of control and results.  You would define the
name of the stored procedure, and it would introspect into the database to
provide in/out parameters (there's a difference).  It might then provide an
interface to bind parameters from one namespace to another.  This is not as
easy as it looks, but is worth the effort to automate.

At this point, you would be able to call them as with any other method.

What's it going to take?

Well, it means coming up with an abstract (SQL99 derived) interface that
works with all major databases that provide SPs.  Once this is done, we need
an interface to introspect into the database.  This becomes part of the DA
API.  Once that's done, it's a "simple matter of programming".

Hopefully this gives some insight.
Chris
-- 
| Christopher Petrilli        Python Powered        Digital Creations, Inc.
| petrilli@digicool.com                             http://www.digicool.com