Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Development" <derby-dev@db.apache.org>
Received-SPF: neutral (hermes.apache.org: local policy)
Message-ID: <41F55095.8060801@Mutagen.Net>
Date: Mon, 24 Jan 2005 11:46:29 -0800
From: Jack Klebanoff <klebanof@Mutagen.Net>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US;
 rv:1.7.1) Gecko/20040707
MIME-Version: 1.0
To: Derby Development <derby-dev@db.apache.org>
Subject: Re: On DERBY-107 : ODBC Metadata functions
References: <41F18707.4060700@golux.com>
In-Reply-To: <41F18707.4060700@golux.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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.

>
> 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? **** ]
>
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.
[snip]

Jack