Army wrote:
[snip]
DISCUSSION:
The question is now this: what's the best/preferred way to propagate this ODBC/JDBC duality from the "SystemProcedures.java" file to the corresponding methods in org.apache.derby.impl.jdbc.EmbedDatabaseMetadata.java (hereafter referred to as "EDM")?
Option I:
Add new SQL statements, such as "getColumnsForODBC", to the existing metadata.properties file, as described in the proposal for DERBY-107. Then, since EDM has to know which version of a given SQL statement to execute--for example, should it call the regular "getColumns" version, or should it call the new "getColumnsForODBC" version?--we could add new methods (such as "setForODBC()") to EDM that could be used by SystemProcedures to indicate (to EDM) that ODBC metadata should be returned, intead of JDBC metadata. Note that, since SystemProcedures is in a different package than EDM, the new methods on EDM would (I think) have to be _public_.
Regarding this approach, one must ask:
[ #1 **** COMMUNITY INPUT? **** ]
What's the general attitude toward adding public methods to a Derby class that is implementing a specific JDBC class? In the context of this discussion, is it or is it not acceptable/desireable to add Derby-specific public methods to a class like EmbedDatabaseMetadata.java, which is an implementation of java.sql.DatabaseMetaData? Technically speaking, I don't think the addition of public classes breaks the JDBC standard (so long as we aren't telling people that they can import EmbedDatabaseMetadata in their apps--which we aren't), but I'm curious as to whether there's a "good programming practice" here that the Derby community would like to (or already does?) hold to?
[ #1 **** End **** ]
I would prefer that the ODBC support not be put in the EmbedDatabaseMetadata class. Then applications that do not use ODBC do not have to load ODBC support into their JVM.
I think that the VTI option offers the most flexibility. VTIs are not limited to massaging the results of standard JDBC metadata queries, they can query the system tables in an entirely different way if necessary. They are not loaded until necessary so JDBC only servers are not burdened with ODBC support.
Option II:
Add new SQL statements, such as "getColumnsForODBC", to the existing metadata.properties file, as described in the proposal for DERBY-107. Then we could extend the EDM class with a new, simple class that sets ODBC-related state, and modify EDM to check the state and execute the appropriate statements. For example, we could add a protected variable "forODBC" to EDM, default it to "false", and then set it to true in the extended class for ODBC. EDM would then check the flag and execute the corresponding metadata statement. The presumption here is that SystemProcedures would check for the ODBC indicator and, if found, use an instance of the new subclass for the metadata calls, instead of using an instance of the existing EDM.
This approach allows us to avoid adding new (non-JDBC) public classes to EDM, at the cost of creating another (albeit fairly simple) metadata class.
With this approach, we could even go further and add another file, say "odbc_metadata.properties" that holds the ODBC metadata statements (instead of adding them to the existing metadata.properties file). The new subclass could then load _that_ file instead of the current metadata.properties file, which gives us a nice separation of functionality: all of the ODBC code cleanly separated from the JDBC code. Of course, that could be a bad thing, too, since 1) we'd then have TWO metadata files to worry about in the codeline, instead of just one, which introduces room for error if/when metadata-related processing changes occur in Derby, and 2) we'd have to duplicate any SQL statements that are the same for ODBC and JDBC (ex. several of the "getBestRowIdentifier" queries) in both files. So I'm guessing we wouldn't want to create another metadata file...but I thought I'd bring it up, just in case.
Option III:
Create some kind of internal VTI for ODBC metadata and use that. I have to admit that I don't know too much about how VTIs work, but Kathey gave me some places to look, so I'm going to read up. Apparently, we can execute the same metadata SQL statements that already exist for JDBC, then use a VTI to "massage" the result set into something that complies with ODBC specifications. This might be a good choice given that most of the differences between ODBC and JDBC are in the types of the columns returned. For example, JDBC might say that any String will do, whereas ODBC will say it has to be VARCHAR. In that case, a literal value ' ' will be fine for JDBC, but since it's treated as a CHAR value by Derby, it would be breaking ODBC standard. With a VTI, we could theoretically accomplish the same things that we'd be doing with new SQL statements--such as casting ' ' to VARCHAR in this particular case. Other modifications we'd have to implement include casting certain integer columns to smallints, and replacing null values in JDBC (such as for "sql_data_type" and "buffer_length" columns) to legal values (neither column is supposed to be null for ODBC).
Upside to this is that we still only have a single metadata.properties file, which (theoretically) makes maintenance of metadata procedures easier. As I don't know much about VTIs, I can't say what else this approach would require, but it seems safe to say that it would at least require another class to serve as the ODBC VTI. How that would tie into the SystemProcedures and EmbedDatabaseMetadata classes, I don't know yet...
So...
[ #2 **** COMMUNITY INPUT? **** ]
[snip]
Jack
