Proposal to support functions and procedures writen in SQL by compiling them to server-side JDBC Java byte code and then executing them using the existing Java routine support. Functionality could then be extended to SQL/PSM with control statements.

Initially discussed on derby-dev mailing list under this thread. Please continue any discussion on the mailing list.

http://mail-archives.apache.org/mod_mbox/db-derby-dev/200510.mbox/%3c43448C37.4040909@debrunners.com%3e

This page will be updated to summarize the discussion.


Outline of Approach

The approach proposed would be to basically re-write the SQL code in the procedure or function as server side JDBC/Java byte code and then use the existing Java routine support to execute it. That is the create SQL function or procedure would:

  1. validate the SQL routine body
  2. convert the SQL routine body to server-side JDBC byte code in a new routine class with a single public static method for the entry point
  3. define the SQL routine to use that class and method just like a Java routine
  4. store the generated routine class in the database

Then at execute routine time, Derby would ensure the routine class is loaded before executing the routine

From a high-level it seems most of the pieces are already in Derby:


Raw Prototype

So I spent a few hours hacking up some code, and managed to get a simple example working, a SQL function with no parameters.

ij> create function ct() returns TIME language SQL
parameter style java external name 'xxx.yyy'
return current_time;

ij> values ct();
1
-------
15:20:00

(the parameter style and external name are there but ignored, that's just more parser work).

The generated method has this signature:

public static java.sql.Time
org.apache.derby.psm.APP.CT.SQL051005031910950.psm_sql() throws
java.sql.SQLException

Here's the decompiled version of the generated routine byte code:

/* SQL051005034613770 - Decompiled by JODE
 * Visit http://jode.sourceforge.net/
 */
package org.apache.derby.psm.APP.CT;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Time;

public final class SQL051005034613770
{
    private Connection conn;

    private SQL051005034613770() {
        /* empty */
    }

    private Time _psm_sql() throws SQLException {
        Connection connection
            = DriverManager.getConnection("jdbc:default:connection");
        SQL051005034613770 sql051005034613770_0_ = this;
        sql051005034613770_0_.conn = connection;
        java.sql.ResultSet resultset
            = conn.createStatement().executeQuery("VALUES current_time");
        resultset.next();
        Time time = resultset.getTime(1);
        resultset.close();
        conn.close();
        return time;
    }

    public static Time psm_sql() throws SQLException {
        return new SQL051005034613770()._psm_sql();
    }
}

Possible storage of the generated class in SYSSTATEMENTS. This table already supports the storage of generated classes for stored prepared statements.

Column

type

nullable

use for PSM

STMTID

CHAR(36)

false

unique identifier for the statement (?)

STMTNAME

VARCHAR(128)

false

name of the routine (?)

SCHEMAID

CHAR(36)

false

the schema in which the routine resides (?)

TYPE

CHAR(1)

false

'P' procedure, 'F' function

VALID

BOOLEAN

false

TRUE if valid, FALSE if invalid (?)

TEXT

LONG VARCHAR

false

text of the create routine statement

LASTCOMPILED

TIMESTAMP

true

time that the routine was compiled

COMPILATION SCHEMAID

CHAR(36)

false

id of the schema containing the statement (?)

USINGTEXT

LONG VARCHAR

true

NULL

CONSTANTSTATE (hidden)

Derby Prepared Statement (plan)

??

Bytecode of generated routine

INITIALLY_COMPILABLE (hidden)

BOOLEAN

true

True or Null

* Issue with existing index on STMTNAME,SCHEMAID. Would conflict name space with statements (which only currently exist for JDBC metadata statements). Could upgrade index to include TYPE.

SqlPsmSupport (last edited 2009-09-20 22:12:06 by localhost)