db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Daniel John Debrunner <...@debrunners.com>
Subject Simple mechanism to add SQL functions to Derby
Date Mon, 25 Jul 2005 23:53:35 GMT

After Apachecon Europe I stayed an extra day and spent part of it
implementing an easy way to add simple functions to Derby. This is a
table driven approach, that dynamically creates functions from the table
into the SYSFUN schema. These functions are only represented in memory
(not as persistent entries in system catalog - SYSALIASES). Any function
call made from SQL that is not qualifed, e.g. COS(angle) will first look
for a function in the current schema (e.g. APP.COS) and if that doesn't
exist the function in SYSFUN will be looked for (e.g. SYSFUN.COS). I
added special code in the data dictionary to look in the in-memory table
if the schema is SYSFUN.

This simple approach is only for functions, only supports a single
parameter and makes the assumption that passing SQL NULL results in a
NULL (as in RETURNS NULL ON NULL INPUT). If it scratched someone's itch,
they could improve the scheme to handle multiple parameters or other
function options. These functions also do not appear through any JDBC
DatabaseMetadata calls, which could be an addtional piece of work if
someone wanted to do it.

The table used to specify functions is extracted below, adding these
thirteen functions only took a couple of minutes. Of course, adding
tests for these takes longer :-(.

Does this seem useful for Derby?


** SYSFUN functions
*[0] = FUNCTION name
*[1] = RETURNS type
*[2] = Java class
*[3] = method name
*[4] = parameter type (single parameter)
private static final String[][] SYSFUN_FUNCTIONS = {
{"ACOS", "DOUBLE", "java.lang.Math", "acos", "DOUBLE"},
{"ASIN", "DOUBLE", "java.lang.Math", "asin", "DOUBLE"},
{"ATAN", "DOUBLE", "java.lang.Math", "atan", "DOUBLE"},
{"COS", "DOUBLE", "java.lang.Math", "cos", "DOUBLE"},
{"SIN", "DOUBLE", "java.lang.Math", "sin", "DOUBLE"},
{"TAN", "DOUBLE", "java.lang.Math", "tan", "DOUBLE"},
{"DEGREES", "DOUBLE", "java.lang.Math", "toDegrees", "DOUBLE"},
{"RADIANS", "DOUBLE", "java.lang.Math", "toRadians", "DOUBLE"},
{"LN", "DOUBLE", "java.lang.Math", "log", "DOUBLE"},
{"EXP", "DOUBLE", "java.lang.Math", "exp", "DOUBLE"},
{"CEIL", "DOUBLE", "java.lang.Math", "ceil", "DOUBLE"},
{"CEILING", "DOUBLE", "java.lang.Math", "ceil", "DOUBLE"},
{"FLOOR", "DOUBLE", "java.lang.Math", "floor", "DOUBLE"},


View raw message