db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From David Van Couvering <David.Vancouver...@Sun.COM>
Subject Re: Simple mechanism to add SQL functions to Derby
Date Tue, 26 Jul 2005 17:29:57 GMT
Looks very useful! 


Daniel John Debrunner wrote:

>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