Обсуждение: Re: BUG #3894: JDBC DatabaseMetaData.getTables is inconsistently case-sensitive with schema name

I think my message below didn't get through, according to error notifications from pgsql-bugs-owner@postgresql.org

Hopefully this will be successful. See copy below.



Kris Jurka on 23/01/08 08:51, wrote:
> On Tue, 22 Jan 2008, Adam Hardy wrote:
>> The following bug has been logged online:
>>
>> Bug reference:      3894
>> Description:        JDBC DatabaseMetaData.getTables is inconsistently
>> case-sensitive with schema name
>> Details:
>>
>> create schema DEV;
>>
>> but then DatabaseMetaData.getTables(null, "DEV", "%", new String[]
>> {"TABLE"}) returned an empty resultset.
>>
>> However giving it the schema name "dev" returns the full resultset.
>>
>
> This is by design.  While SQL provides case folding and quoting rules
> those are not the same as the JDBC driver's rules.  In SQL an unquoted
> identifier is case folded while quoted identifiers are not.  To
> provide the most straightforward mapping for the JDBC driver, we
> require an exact match because quoting in Java String objects isn't
> the same as SQL.  It would be odd to say getTable(..., "\"Dev\"",...)
> to imply that you wanted a case sensitive match.  Even odder would it
> be to do a search for a schema with a quote in it's name using SQL
> identifier rules in Java code.
>
> Kris Jurka
>
Hi Kris,

it seems to me from what you just said that PostgreSQL server and JDBC
driver require the schema name to be lower case deliberately, and that
any given name that is not all lower case is converted to lower case by
the server or the driver. Am I correct?

Thanks
Adam

On Fri, 1 Feb 2008, Adam Hardy wrote:

> it seems to me from what you just said that PostgreSQL server and JDBC driver
> require the schema name to be lower case deliberately, and that any given
> name that is not all lower case is converted to lower case by the server or
> the driver. Am I correct?
>

Anything that is not quoted is converted to lowercase.  This is contrary
to the SQL spec (it says to convert it to uppercase), but that's unlikely
to change anytime soon.  A JDBC app can portably detect this case by
checking DatabaseMetaData.storesLowerCaseIdentifiers() and adjusting the
other metadata calls appropriately.

Kris Jurka

Kris,
thanks for the advice.

Because this rules out certain important features of the JPA framework
such as native SQL queries, you may want to prioritize this issue. I
will have to use Oracle or mySQL until PostgreSQL can rectify things.

Thanks and regards
Adam

Kris Jurka on 01/02/08 20:45, wrote:
>
>
> On Fri, 1 Feb 2008, Adam Hardy wrote:
>
>> it seems to me from what you just said that PostgreSQL server and
>> JDBC driver require the schema name to be lower case deliberately,
>> and that any given name that is not all lower case is converted to
>> lower case by the server or the driver. Am I correct?
>>
>
> Anything that is not quoted is converted to lowercase.  This is
> contrary to the SQL spec (it says to convert it to uppercase), but
> that's unlikely to change anytime soon.  A JDBC app can portably
> detect this case by checking
> DatabaseMetaData.storesLowerCaseIdentifiers() and adjusting the other
> metadata calls appropriately.
>
> Kris Jurka
>

On Tue, 12 Feb 2008, Adam Hardy wrote:

> Because this rules out certain important features of the JPA framework such
> as native SQL queries, you may want to prioritize this issue. I will have to
> use Oracle or mySQL until PostgreSQL can rectify things.

I wouldn't expect postgresql to change anytime soon.  Could you explain in
more detail what you can't use and why the JPA side couldn't be fixed to
support postgresql?

You could also consider always quoting all identifiers in both database
creation scripts and queries.  This guarantees that the database won't
mess with the case of the object although it can be a pain to type if you
writing a lot of queries by hand.

Kris Jurka

Kris Jurka on 12/02/08 16:40, wrote:
> On Tue, 12 Feb 2008, Adam Hardy wrote:
>> Because this rules out certain important features of the JPA
>> framework such as native SQL queries, you may want to prioritize this
>> issue. I will have to use Oracle or mySQL until PostgreSQL can
>> rectify things.
>
> I wouldn't expect postgresql to change anytime soon.  Could you
> explain in more detail what you can't use and why the JPA side
> couldn't be fixed to support postgresql?
>
> You could also consider always quoting all identifiers in both
> database creation scripts and queries.  This guarantees that the
> database won't mess with the case of the object although it can be a
> pain to type if you writing a lot of queries by hand.

Well, JPA is just a standard, so a 'JPA fix' would require each JPA
provider  (Toplink, JDO, OpenJPA, Hibernate etc) to rectify their
postgresql-specific dialect / SQL syntax builder.

This issue arises using PostgreSQL with a schema name when running
native SQL queries.

That means I write pure SQL in my JPA mapping files. It's a common
practice.

Using a schema name is also a common practice.

However I will, at some stage in the future, code a test batch, which
tests basic CRUD functionality and OR mapping across different
combinations of JPA providers and DB vendors. So I will keep the bug
reference handy and let you know how many JPA providers suffer this
issue once I run the test.


Best regards
Adam