[PATCH] Derby-107, Phase III

[Army <qo...@sbcglobal.net>]

For background on this patch and for the "Phase I" and "Phase II" patches that precede this one, please see the 
following emails:

http://mail-archives.eu.apache.org/mod_mbox/db-derby-dev/200502.mbox/%3c4202699E.3000009@golux.com%3e
http://mail-archives.eu.apache.org/mod_mbox/db-derby-dev/200502.mbox/%3c421240C1.6040806@sbcglobal.net%3e

*** NOTE TO COMMITTERS: *** This patch is BASED ON THE PHASE I AND PHASE II PATCHES.  Therefore, please do NOT commit 
this patch until after the Phase I and Phase II patches have been committed.  I posted the Phase I patch to derby-dev on 
Feb 3rd and the (revised) Phase II patch on February 15th; they can be referenced at the links given above.

I have run the derbyall test suite with these changes, using Windows with Sun JDK 1.4.2, and have included the relevant 
master updates as part of the patch.  I have also reworked the "metadata.java" test and extended it with a new test, 
"odbc_metadata.java", for purposes of verifying the ODBC metadata behavior.

----

Attached is a patch for Phase III (the final phase) of my proposal for fixing Derby-107:

"Phase III) Submit a patch that will automatically generate ODBC metadata statements at Derby build time, and that will 
add support for such metadata statements in the Derby engine (so that ODBC clients using Derby Network Server can use a 
Derby database)."
   -- Pasted from my first email referenced above.

Following is a detailed description of what all this patch does, for those interested.

I) New Files
------------

This patch adds the following new files to the Derby codeline:

1. java/build/org/apache/derbyBuild/ODBCMetadataGenerator.java

This is the primary class for generating ODBC-compliant metadata queries.  It reads the existing, JDBC metadata queries 
from "java/org/apache/derby/impl/jdbc/metadata.properties" and writes all of those queries back out to a new file called 
"odbc_metadata.properties".  Then, where appropriate, this class duplicates the JDBC queries and makes alterations to 
them in order to create ODBC-compliant versions.  These ODBC-compliant queries are written to odbc_metadata.properties, 
as well, so that in the end, ALL metadata queries (JDBC and OBDC alike) have been written to that output file.

At build time, an ant process executes this class and then clobbers the existing (JDBC only) version of 
metadata.properties (the copy in the CLASSES directory, not the copy in the source directory) with the contents of the 
new output file, "odbc_metadata.properties".  The result is that metadata.properties as it exists in the _build_ 
directory will contain both JDBC and, where needed, ODBC metadata queries, and both sets of queries will be loaded as 
internal prepared statements for the Derby engine at database creation time.

NOTE: This class is ONLY used at build time, and does NOT end up in the org.apache.derby.* package, so it will not be 
included in any Derby jar files.

2. java/build/org/apache/derbyBuild/odbcgen_fragments.properties

This is a helper file for the ODBCMetadataGenerator class.  This file contains SQL fragments that are specific to the 
ODBC metadata queries, and thus it does NOT (and probably shouldn't ever) contain any SQL that already exists in the 
source copy of "metadata.properties"--otherwise, we'd end up having to maintain two copies of the overlapping metadata 
SQL, which would defeat the whole purpose of automatic query generation.

SQL fragments in this file are pulled into the ODBC queries as part of the ODBCMetadataGenerator's work.  While the 
fragments in this file do eventually end up in the build copy of metadata.properties file, the file itself is ONLY used 
at build time, and so it does NOT end up in the org.apache.derby.* package.

3. java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/metadata_test.java

In order to avoid having redundant code, I created this abstract class to be used as the basis for JDBC and ODBC 
metadata tests.  This class contains the primary "runTest()" method that used to be in the old metadata.java class, 
modified to call two new abstract methods.  The old metadata.java class, then, extends this new class and implements the 
abstract methods in a way that is functionally equivalent to the old, JDBC-only metadata test.

4. java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/odbc_metadata.java

This is a new test, added to the suite for purposes of verifying ODBC metadata behavior.  It extends the new file 
metadata_test.java (mentioned above) and implements that class's abstract methods in a way that allows us to test the 
ODBC versions of the metadata queries.  This class does everything that is done for the "metadata.java" test, plus it 
performs some other ODBC compliance checks, so that any future changes which break compliance will (hopefully) be easily 
detected.

5. java/testing/org/apache/derbyTesting/functionTests/master/odbc_metadata.out

A new master file to correlate with the new odbc_metadata.java test described above.

6. java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/odbc_metadata.out

A new master file to correlate with the new odbc_metadata.java test described above.

II) Build Modifications
-----------------------

I modified two build.xml files as well as the tools/ant/properties/dir.properties file.  These modifications lead to the 
following additions to the build process:

1. When the java/build directory is built, ODBCMetadataGenerator is compiled into the classes/org/apache/derbyBuild 
directory, and the "odbcgen_fragments.properties" file is copied to that location, as well.

2. When the java/drda directory is built, all of the following things happen:

    -- The copy of metadata.properties in the OUTPUT (classes) directory is deleted (so that we can get a fresh copy).
    -- The SOURCE copy of metadata.properties is copied to the OUTPUT (classes) directory; this is our "fresh copy".
    -- The ODBCMetadataGenerator class executes, using the "fresh copy" of metadata.properties as input, and creating 
the odbc_metadata.properties file as output.
    -- The odbc_metadata.properties file is renamed to metadata.properties in the OUTPUT directory.

NOTE: The above things only happen if at least one of the following files has been modified: metadata.properties, 
ODBCMetadataGenerator.java, or odbcgen_fragments.properties.

III) Engine Changes for ODBC Metadata Support
---------------------------------------------

The changes described in I and II above allow Derby to automatically generate ODBC-compliant versions of the metadata 
queries at compile-time, so that, when the build is done, there are both JDBC metadata queries and ODBC metadata queries 
available in the system.  That done, the changes discussed in this section are what allow the Derby engine, at run-time, 
to determine which version of a query it needs to execute.

The type of result set required (JDBC or ODBC) is determined by an "OPTIONS" parameter that is passed into a set of 
Derby metadata system procedures.  If this OPTIONS parameter contains the exact string "DATATYPE='ODBC'", then the 
result set that is returned will comply with the ODBC specification (meaning that Derby will execute the ODBC version of 
the corresponding metadata query).  If that string doesn't exist in the OPTIONS parameter, the result set will default 
to JDBC.

Derby in embedded mode never passes "DATATYPE='ODBC'" as an OPTION to the system procedures.  However, if a user does an 
explicit CALL of the system procedure when running embedded Derby, and s/he passes "DATATYPE='ODBC'" as a parameter, 
then the result will in fact be ODBC compliant.  This is how the odbc_metadata.java test (mentioned above) works.

In server mode, it is the client who sends in the OPTION parameter.  For example,  both JCC (for JDBC clients) and the 
DB2 Runtime Client (for ODBC clients) pass OPTIONS parameters when calling the Derby system procedures, and those values 
are used to determine runtime behavior.  In this case, the client must pass in "DATATYPE='ODBC'" when it calls the Derby 
system procedure if it wants to retrieve metadata that complies with the ODBC specification.

The Derby class that processes the OPTIONS parameter is java/engine/org/apache/derby/catalog/SystemProcedures.java. 
Thus, it is in this class that we parse the OPTIONS parameter and search for the ODBC datatype indicator.  Based on 
whether or not the ODBC string was found, SystemProcedures either calls the normal, JDBC metadata methods as defined in 
java.sql.DatabaseMetadata, or else it calls new, Derby-only methods that I have added specifically for returning ODBC 
result sets.

In order to accommodate these new ODBC methods, I made the following changes to the Derby EmbedDatabaseMetaData class 
for each method that needs to support different result sets for JDBC and ODBC.  The running example here is "getColumns".

1. I moved the code in the existing method (ex. "getColumns()") into a new private method (ex. "doGetCols()") that takes 
as an additional parameter the name of the metadata query to execute.

2. I then changed the existing JDBC methods (ex. "getColumns()") to call the new private method and to pass in the 
regular, JDBC query name (in this case, "getColumns").

3.  Finally, I added a new method (ex. "getColumnsForODBC()") that also calls the new private method, but this method 
passes in the new ODBC query name (in this case, "odbc_getColumns").  It is this new method that will be called by 
SystemProcedures if the "DATATYPE='ODBC'" string is received as an OPTIONS parameter.

I made the above changes to EmbedDatabaseMetaData.java in order to avoid duplicate code as much as possible.

IV) Test Changes
----------------

As mentioned in section I above, I created a new test, "odbc_metadata.java", that will allow developers to verify that 
the changes they make haven't broken the ODBC query generation and/or execution process.  I added this new test to run 
as part of the following three suites: "derbylang", "derbynetmats", and "j9derbynetmats".

I hope that's enough detail for anyone interested ;)  If you want to learn more, feel free to review the attached patch. 
  And of course, if anyone has any issues/comments, please do let me know.

----

I have successfully applied this patch (AFTER applying the Phase I and Phase II patches) and have run the "derbyall" 
suite on Windows 2000 with Sun JDK 1.4.2--with no failures.

Thanks,
Army