db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (JIRA)" <j...@apache.org>
Subject [jira] Updated: (DERBY-3652) Derby does not follow the SQL Standard when trying to map SQL routines to Java methods.
Date Mon, 23 Jun 2008 13:44:45 GMT

     [ https://issues.apache.org/jira/browse/DERBY-3652?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Rick Hillegas updated DERBY-3652:
---------------------------------

    Attachment: derby-3652-10-aa-SignatureChecker.diff

Attaching derby-3652-10-aa-SignatureChecker.diff. This creates a lint tool, SignatureChecker
and puts it in derbytools.jar. The tool walks all of the user-coded functions and procedures
which have been registered in a database and verifies whether they can be matched to Java
methods. I have successfully run the tool on Java 5 and on Java ME.

I think that the tool is useful just by itself because it gives customers a test-time check
for signature mismatches. That seems to me to be an improvement over the run-time checks which
customers rely on today.

I also think it would be good to recommend that customers run this tool on their databases
(in soft-upgrade mode) to help verify that their routines have not been orphaned by enforcement
of the ANSI signature checking rules.

Here's the usage diagnostic in English:

"Usage if running on J2SE:
 
 	java org.apache.derby.tools.SignatureChecker  CONNECTION_URL_TO_DATABASE
 
Usage if running on J2ME:
 
 	java org.apache.derby.tools.SignatureChecker DATABASE_NAME"

You can also run it using the derbyrun shortcut. E.g.:

       java -jar trunk/jars/sane/derbyrun.jar SignatureChecker "jdbc:derby:derby10.5" 

Here's what a sample run looks like:

Found a matching method for: "APP"."DOINSERT"(  )
Found a matching method for: "APP"."DOINSERTANDCOMMIT"(  )
Found a matching method for: "APP"."APPENDFOOANDBAR"(  VARCHAR  )
Unresolvable routine: "APP"."IDONTEXIST"(  VARCHAR ,  INTEGER  ). Detailed reason: No method
was found that matched the method call z.iDontExist(java.lang.String, int), tried all combinations
of object and primitive types and any possible type conversion for any  parameters the method
call may have. The method might exist but it is not public and/or static, or the parameter
types are not method invocation convertible.
Found a matching method for: "APP"."RUNDDL"(  VARCHAR  )
Unresolvable routine: "APP"."TABFUNCDOESNTEXIST"(  VARCHAR ,  BIGINT  ). Detailed reason:
No method was found that matched the method call org.apache.derbyTesting.functionTests.tests.lang.TableFunctionTest.appendFooAndBar(java.lang.String,
long), tried all combinations of object and primitive types and any possible type conversion
for any  parameters the method call may have. The method might exist but it is not public
and/or static, or the parameter types are not method invocation convertible.



Touches the following files:

A      java/tools/org/apache/derby/tools/SignatureChecker.java

The guts of the tool. Various lookups are indirected through reflection so that the tool can
be agnostic about whether it is running on J2SE or J2ME. In particular, the function-lookup
logic depends on reflection--because the appropriate DatabaseMetaData methods don't appear
in the JDBC interface until Java 6, even though implementations of the methods appear in our
JSR169 client.

M      java/tools/org/apache/derby/iapi/tools/run.java

Wires the new tool into the derbyrun logic.

M      java/tools/org/apache/derby/loc/toolsmessages.properties

New localizable diagnostics.

M      tools/jar/tools.properties

Wires the tool into derbytools.jar.




> Derby does not follow the SQL Standard when trying to map SQL routines to Java methods.
> ---------------------------------------------------------------------------------------
>
>                 Key: DERBY-3652
>                 URL: https://issues.apache.org/jira/browse/DERBY-3652
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.5.0.0
>            Reporter: Rick Hillegas
>         Attachments: badsignatures.sql, derby-3652-01-aa-mixTypesOnFirstPass.diff, derby-3652-01-ab-mixTypesOnFirstPass.diff,
derby-3652-01-ac-mixTypesOnFirstPass.diff, derby-3652-01-ad-mixTypesOnFirstPass.diff, derby-3652-02-aa-dontWidenExceptForSmalllint.diff,
derby-3652-02-ab-dontWidenExceptForSmalllint.diff, derby-3652-03-aa-dontWidenSmallint.diff,
derby-3652-03-ab-dontWidenSmallint.diff, derby-3652-04-aa-deprecateJavaRules.diff, derby-3652-05-aa-moreTests.diff,
derby-3652-06-aa-dontWidenString.diff, derby-3652-07-aa-dontWidenBigDecimal.diff, derby-3652-08-aa-dontWidenAtAll.diff,
derby-3652-09-aa-mixedTypes.diff, derby-3652-10-aa-SignatureChecker.diff, derby-3652-badmatches.diff,
SignatureMapping.html, SignatureMapping.html, SignatureMapping.html, SignatureProblems.java,
signatureProblems.sql
>
>
> I have only tested this in the 10.5 trunk. However, I suspect that this affects all previous
releases of Derby as well.
> In resolving method signatures for function/procedure invocations, the SQL standard makes
the following definitions in part 13, section 4.5 (parameter mapping). These definitions,
in turn, refer to tables B-1 and B-3 in JDBC 3.0 Specification, Final Release, October 2001
([JDBC]).
>     * Simply mappable - This refers to the correspondence of SQL and Java types described
in [JDBC] table B-1. This is the table which defines the mapping of SQL types to Java primitives.
>     * Object mappable - This refers to the correspondence of SQL and Java types described
in [JDBC] table B-3. This is the table which defines the mapping of SQL types to Java wrapper
objects.
>     * Output mappable - For OUT and INOUT parameters, this refers to a single element
array whose cell is simply mappable or object mappable. E.g. Integer[] or float[].
>     * Mappable - This means simply, object, or output mappable.
>     * Result set mappable - This means a single element array whose cell is a type which
implements either java.sql.ResultSet or sqlj.runtime.ResultSetIterator.
> Putting all of this together, section 4.5 continues:
>     "A Java method with M parameters is mappable (to SQL) if and only if, for some N,
0 (zero) <= N <= M, the data types of the first N parameters are mappable, the last
M - N parameters are result set mappable, and the result type is either simply mappable, object
mappable, or void."
> Section 8.6 gives more detailed rules, but they are hard to follow. According to section
8.6, when resolving a routine invocation, Derby should expect to find one and only one static
mappable method with the expected external name (Java class + method name).
> I believe that this is a fair description of the rules. This, at least, is what some
other databases appear to do. See, for instance, http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.java/html/java/java126.htm
and http://www.service-architecture.com/database/articles/mapping_sql_and_java_data_types.html
> We do not have a regression test which verifies that Derby applies the SQL standard resolution
rules. There may be several divergences from the standard. This JIRA is a place to track those
discrepancies. Here is one that I have noticed:
> The following SQL signature
> ( a int ) returns int
> should be mappable to any of the following Java signatures
> public static int f( int a )
> public static int f( Integer a )
> public static Integer f( int a )
> public static Integer f( Integer a )
> However, I observe that Derby is only able to resolve the first and third signatures
(the ones with primitive arguments). I will attach a test case showing this problem.
> I will also attach an html table summarizing the simply and object mappable rules.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message