FarragoSQLJDeploymentDescriptors

From Eigenpedia

Jump to: navigation, search

Contents

SQLJ Deployment Descriptors

As of Eigenbase release 0.9.3, Farrago (and LucidDB) supports deploying (or undeploying) functions and as part of the installation or removal of JARs. This allows for the packager of a jar that contains UDRs (proc, tcn, tbl fcn) to provide the DDL necessary to install / remove the functions as part of a jar deployment.

Installation Example

Without deployment descriptors, installing a UDR would involve two steps:

  1. Execution of a CREATE JAR statement
  2. Execution of a CREATE FUNCTION (or PROCEDURE) statement

Using our existing example in LucidDbUdfJavaHowto, that looks like: 

-- Note the 0 as the last argument.  This indicates to not "deploy" as part of the installation of this jar
call sqlj.install_jar('file:${FARRAGO_HOME}/plugin/regex-udf.jar','udf_jar',0);

create function pattern_match(input varchar(1024), pattern varchar(1024))
returns boolean
language java
no sql
external name 'regex.udf_jar:com.yoyodyne.RegexMatchUdf.execute';

With deployment descriptors, the person who packages the jar can provide the CREATE / DROP routine statements that will be executed as part of the installation of the JAR.

In our existing example, if the packager of regex-udf.jar included a deployment descriptor, the user can install the jar and create the funtion in a single statement: 

-- The 1 indicates to execute any deployment descriptor INSTALL actions
call sqlj.install_jar('file:${FARRAGO_HOME}/plugin/regex-udf.jar','udf_jar',1);

Removal Example

Without deployment descriptors, removing a jar which provided the example pattern_match function, would include two statements.

  1. Drop of the function
  2. Drop of the jar 
DROP FUNCTION pattern_match CASCADE;
CALL sqlj.remove_jar ( 'udf-jar', 0 );

With a remove action defined in a descriptor, the function and the jar can be removed using the undeployment option number 1 

-- The 1 indicates to execute any deployment descriptor REMOVE actions
CALL sqlj.remove_jar ( 'udf-jar', 1 );

Manifest Headers

When deploying the JAR, Farrago will review the jar manifest to determine if there are one or more descriptor files in the jar. The SQLJ standard specifies the exact format for how to identify which files in a jar are descriptor files. 

Manifest-Version: 1.0
Created-By: 14.2-b01 (Sun Microsystems Inc.)

Name: deploy.txt
SQLJDeploymentDescriptor: TRUE

Name: deploy2.txt
SQLJDeploymentDescriptor: TRUE

The files deploy.txt and deploy2.txt, both in the root of the jar, will be read and parsed for INSTALL and REMOVE actions. These actions will be executed as an entire block and either all will succeed or the entire operation is cancelled.

Deployment Descriptor Files

The format for the SQLDescriptor files is documented in ISO/IEC 9075-13:2003 (E) section 12.2. In general, they are a file with INSTALL and REMOVE actions. Actions should only contain the following commands:

  • CREATE / DROP ROUTINE (proc/function) with implementation Java
  • CREATE / DROP TYPE
  • GRANT / REVOKE on ROUTINE
  • GRANT / REVOKE on TYPE

NOTE: See FUTURE WORK section for specifics on the current Eigenbase implementation. Any additional statements (create foreign server) should be done in implementor blocks.

Example

The following is an example deploy.txt that implements does our create / drop function for our pattern_match / udf_jar. 

SQLActions[ ] = {
  "BEGIN INSTALL
    create function PATTERN_MATCH(input varchar(1024), pattern varchar(1024))
    returns boolean
    language java
    no sql
    external name 'thisjar:com.yoyodyne.RegexMatchUdf.execute';
  END INSTALL",
  "BEGIN REMOVE
    DROP FUNCTION \"PATTERN_MATCH\" CASCADE;
  END REMOVE"
}


thisjar

thisjar is a special token that will be replaced with the name of the jar installed. It allows the descriptor to be unconcerned with the name the caller of INSTALL_JAR() has given the jar. In general, deployment descriptors should never reference any jar name directly, but rather always use thisjar.

unspecified schema

If no schema is specified for the CREATE/DROP routines, any objects being created or dropped are done so in the same schema as the jar. 

set schema 'APPLIB';
call sqlj.install_jar('file:${FARRAGO_HOME}/plugin/regex-udf.jar','"s1"."udf_jar"',1);
-- Works since installed into s1
select "s1".PATTERN_MATCH (c1, c2) from (values ('blah', 'b*');
-- Does not work since session schema is ignored
select APPLIB.PATTERN_MATCH (c1, c2) from (values ('blah', 'b*');

--Ngoodman 19:08, 9 June 2010 (EDT) Is that right? Can fully schema jar locations be used like above?

NOTE: Deployment descriptors can install into a specific schema by giving fully qualified (APPLIB.PATTERN_MATCH) routine definitions

Background and Resources

Known Issues

Future Work

  • Implementor blocks (JIRA)
  • Restrict SQL to only SQLJ allowed statements (JIRA)
Retrieved from "http://pub.eigenbase.org/wiki/FarragoSQLJDeploymentDescriptors"
Personal tools