New Interface for Calling Database functions

Scheduled for OpenACS 5.10.1, there is a new interface available that provides several improvements over the current solutions:

The new interface ...

• is significantly faster than the existing official OpenACS interface (package_exec_plsql) and easier to use by supporting a standard calling interface (not the special "var_list" which has to be assembled for package_exec_plsql)
• is about the same performance as the "::xo::db::sql::*" interface without sharing its disadvantages (being a separate pattern, just one backend, ...)
• is more secure (thorough argument checking on the Tcl and bind-vars level), and
• more flexible (works with multiple databases and multiple driver types), and
• much more feature-complete (e.g. support for functions returning tables, etc.)

For more details, check below. The plan is to replace the "::xo::db::sql::*" interface in a first step and replace calls to package_exec_plsql in the supported packages, and to mark the obsolete functions as deprecated. In the near future, more features of the xo::db interface will be added. The plain ugly old interface of OpenACS of calling db-functions where the highest priority.

Implemented features:

• SQL function can be called for multiple database connection types (driver types nsdb and nsdbi, backend types PostgreSQL and Oracle) with less overhead (in essence, a value added, better performing replacement of "package_exec_plsql"). The  interface provided via xo::db supported just a single combination of the above.

• Support for PostgreSQL and Oracle

• Ability to talk to multiple databases from the same OpenACS instance. These databases can be

  • multiple databases of the same driver and backend  (e.g. multiple PostgreSQL databases)
  • multiple databases using different drivers (e.g. nsdb and nsdbi)
  • multiple databases with different backends vendors (e.g. PostgreSQL and Oracle).

  The selection of the backed happens of via the standard "-dbn" parameter. For the nsdb driver, one can use  e.g., "-dbn legacy", an example for nsdbi is "-dbn dbi1",  where the value provided via "-dbn" is passed for uniformity to the "-db" option of the nsdbi API.

• Support for functions returning composite SQL types (SQL type "record" in PostgreSQL or "table" in Oracle). Results are returned as lists of lists.  This feature is implemented and tested for PostgreSQL connected via nsdb and nsdbi drivers and Oracle via nsdb.

• SQL functions returning "void" can be called as well. In the Oracle cases, these are "procedures" which have to be called differently.

• Additional (Tcl-level) argument checking is provided before calling into SQL for improved security and documentation. The API handles among other types the SQL types "integer" and "bigint".

• Arguments of SQL functions are passed to the database via bind variables (implemented for PostgreSQL with nsdb and nsdbi). This is a security improvement over the previous  implementation in xotcl-core.

• Standard default handling (argument default values like for all Tcl procs, although the way how defaults are handled is different in PostgreSQL and Oracle).

• Optional arguments passed in as empty strings are treated as NULL values (standard behavior in OpenACS)

• Dropped shortcomings of xo::db interface (naming and calling conflicts).

• Independent of xotcl-core

 Possible further extensions:

• Argument types could/should be displayed in the API browser (general feature request, not specific to the new DB interface)

Usage:

At startup, a single database interface is creates, which is named "::acs::dc", which takes the parameters from the default  setup of the OpenACS configuration.

 The following command creates a second database interface based on the "nsdbi" driver and define the interface stubs for the nsdbi driver.

::acs::db::require_dc -driver nsdbi -name ::acs::dc2
::acs::dc2 create_db_function_interface

For specifying a different backend, one could use e.g.:

::acs::db::require_dc -backend oracle -name ::acs::dc3

SQL query to a second DB (db pool called "legacy") via nsdb interface:

::acs::dc list_of_lists -dbn legacy get_list {select 1 from dual}

 Call database function from a second DB (db pool called "legacy") via nsdb driver:

::acs::dc call content_item get_latest_revision -dbn legacy -item_id 33357

 Call database function from a second DB via nsdbi driver named "dbi1"

::acs::dc2 call content_item get_latest_revision -dbn dbi1 -item_id 33357

 Return multiple records via nsdb

::acs::dc call content_item get_children -item_id 169303

 Return multiple records via nsdbi

::acs::dc2 call content_item get_children -item_id 169303

 Some calls with performance data (for ds/shell):

lappend _ [package_exec_plsql -var_list {{item_id 33357}} content_item get_latest_revision]
lappend _ [::xo::db::sql::content_item get_latest_revision -item_id 33357]
lappend _ [::acs::dc call content_item get_latest_revision -item_id 33357]
lappend _ [::acs::dc2 call content_item get_latest_revision -item_id 33357]

lappend _ [time {package_exec_plsql -var_list {{item_id 33357}} content_item get_latest_revision} 1000]
lappend _ [time {::xo::db::sql::content_item get_latest_revision -item_id 33357} 1000]
lappend _ [time {::acs::dc call content_item get_latest_revision -item_id 33357} 1000]
lappend _ [time {::acs::dc2 call content_item get_latest_revision -item_id 33357} 1000]
join $_ \n

The results as displayed by ds/shell

33358
33358
33358
33358
402.096544 microseconds per iteration
190.805835 microseconds per iteration
187.885927 microseconds per iteration
171.99336300000002 microseconds per iteration