DatabaseMetaData JDBC specification question

[Daniel John Debrunner <dj...@apache.org>]

For the DatabaseMetaData methods that fetch attributes of SQL objects 
(such as getTables, getColumns) the parameters are described in the 
javadoc in three ways:

1) Parameter must match the object's name as stored in the database.

<example javadoc quote>
a table name; must match the table name as it is stored in the database
</example javadoc quote>

2) Parameter must match the object's name as stored in the database and 
two special values are allowed (empty string and null).

<example javadoc quote>
a schema name; must match the schema name as it is stored in the 
database; "" retrieves those without a schema; null means that the 
schema name should not be used to narrow the search
</example javadoc quote>

3) A pattern, explicitly called out in the overview of DatabaseMetaData 
and states the parameter name will end in 'pattern'.

<example javadoc quote>
a table name pattern; must match the table name as it is stored in the 
database
</example javadoc quote>

These imply very different behaviours for the various styles, e.g.

for 1) null should not be allowed and the value should not be treated as 
a pattern or use empty string to indicate those without a schema.

for 2) the value should not be treated as a pattern.

Derby does not follow this split, it treats every such argument as a 
pattern (in some cases the client may treat arguments differently).

Is the split in DatabaseMetaData intentional, it seems so given the 
explicit wording for patterns? And the fact that in some cases the 
method's description indicates information for 'a table' (singular) is 
returned and the resulting rows have no information that allows one to 
determine which table a row is for (e.g getBestRowIdentifier & 
getVersionColumns). Though in both those cases the api also implicitly 
allows for multiple tables to be returned when the same table name 
exists in multiple schemas.

If so should Derby follow the strict definitions of the javadoc or could 
Derby just be providing extensions to the standard behaviour?
The only area where I could see issues coming up with this extension is 
when the parameter is in category 1) or 2) and the object name contains 
'%' or '_'. Then a meta data call could potentially return rows for 
other tables and in some cases the meta data provides no way to 
determine which table a row is for (e.g getBestRowIdentifier & 
getVersionColumns).

One other point is that removing the pattern support from a number of 
meta data queries would most likely improve their performance.

Thanks,
Dan.

Re: DatabaseMetaData JDBC specification question

["Lance J. Andersen" <La...@Sun.COM>]

If the JDBC spec does not indicate that the parameter accepts a pattern 
for the value, then i would suggest you only support patterns where it 
is required.  It is possible tests could be added to do additional 
validation of parameters passed into API methods in future TCKs for 
conformance.

Daniel John Debrunner wrote:
> For the DatabaseMetaData methods that fetch attributes of SQL objects 
> (such as getTables, getColumns) the parameters are described in the 
> javadoc in three ways:
>
> 1) Parameter must match the object's name as stored in the database.
>
> <example javadoc quote>
> a table name; must match the table name as it is stored in the database
> </example javadoc quote>
>
> 2) Parameter must match the object's name as stored in the database 
> and two special values are allowed (empty string and null).
>
> <example javadoc quote>
> a schema name; must match the schema name as it is stored in the 
> database; "" retrieves those without a schema; null means that the 
> schema name should not be used to narrow the search
> </example javadoc quote>
>
> 3) A pattern, explicitly called out in the overview of 
> DatabaseMetaData and states the parameter name will end in 'pattern'.
>
> <example javadoc quote>
> a table name pattern; must match the table name as it is stored in the 
> database
> </example javadoc quote>
>
> These imply very different behaviours for the various styles, e.g.
>
> for 1) null should not be allowed and the value should not be treated 
> as a pattern or use empty string to indicate those without a schema.
>
> for 2) the value should not be treated as a pattern.
>
> Derby does not follow this split, it treats every such argument as a 
> pattern (in some cases the client may treat arguments differently).
>
> Is the split in DatabaseMetaData intentional, it seems so given the 
> explicit wording for patterns? And the fact that in some cases the 
> method's description indicates information for 'a table' (singular) is 
> returned and the resulting rows have no information that allows one to 
> determine which table a row is for (e.g getBestRowIdentifier & 
> getVersionColumns). Though in both those cases the api also implicitly 
> allows for multiple tables to be returned when the same table name 
> exists in multiple schemas.
>
> If so should Derby follow the strict definitions of the javadoc or 
> could Derby just be providing extensions to the standard behaviour?
> The only area where I could see issues coming up with this extension 
> is when the parameter is in category 1) or 2) and the object name 
> contains '%' or '_'. Then a meta data call could potentially return 
> rows for other tables and in some cases the meta data provides no way 
> to determine which table a row is for (e.g getBestRowIdentifier & 
> getVersionColumns).
>
> One other point is that removing the pattern support from a number of 
> meta data queries would most likely improve their performance.
>
> Thanks,
> Dan.
>
>