Case-sensitive table names without quotes?

[Thomas Kellerer]

Hi,

I have a rather strange question:

I'm using H2 a lot to do unit testing on my application. Now SQL
Server (and MySQL) can be configured to be case-sensitive for table
names even without double quotes. As much as I dislike this behaviour,
I need to test my application if it can cope with that.

As I don't really want to maintain a special instance for MySQL and
SQL Server to test this, I was wondering if it would be possible to
add some kind of system option to H2 that would mimic that behaviour.
That way I could still run my tests using H2 :)

Of course the default must be the ANSI compatible behaviour as it is
currently implemented.

SQL Server's behaviour is controlled through the default collation of
the database. If that is case-sensitive, the table names are case-
sensitive as well (because it's compared against an entry in a system
table).

As H2 already has the IGNORECASE built-in I wonder if this could be
implemented in a similar way as with SQL Server: by defining the
system tables either as IGNORECASE or not?
Of course this will only work if H2 does not convert all non-quotes
names to uppercase.

Regards
Thomas

[Thomas Mueller]

Hi,

> I'm using H2 a lot to do unit testing on my application. Now SQL
> Server (and MySQL) can be configured to be case-sensitive for table
> names even without double quotes. As much as I dislike this behaviour,
> I need to test my application if it can cope with that.

This feature will be hard to implement in H2. You would need to
re-write the parser so that it doesn't convert everything to
uppercase, but the comparisons with keywords would need to be
case-insensitive. I think this is not something I am interested to add
to H2 at the moment unless more people ask for it.

> As I don't really want to maintain a special instance for MySQL and
> SQL Server to test this, I was wondering if it would be possible to
> add some kind of system option to H2 that would mimic that behaviour.
> That way I could still run my tests using H2 :)

What about generating the statements with double quotes, and remove
the quotes if you are running MySQL?

> As H2 already has the IGNORECASE built-in I wonder if this could be
> implemented in a similar way as with SQL Server: by defining the
> system tables either as IGNORECASE or not?

You could create views. That would be slow but should work. This is
actually how the PostgreSQL compatibility is implemented.

Regards,
Thomas

[Thomas Kellerer]

Hi,

thanks for the answer.

> This feature will be hard to implement in H2. You would need to
> re-write the parser so that it doesn't convert everything to
> uppercase, but the comparisons with keywords would need to be
> case-insensitive. I think this is not something I am interested to add
> to H2 at the moment unless more people ask for it.

Yes, I assumed that.
And I can understand that you don't want to do it :)
It's a completely dumb way of dealing with the standard.

> > As I don't really want to maintain a special instance for MySQL and
> > SQL Server to test this, I was wondering if it would be possible to
> > add some kind of system option to H2 that would mimic that behaviour.
> > That way I could still run my tests using H2 :)
>
> What about generating the statements with double quotes, and remove
> the quotes if you are running MySQL?

Well the statements are sometimes entered by the end user...

> > As H2 already has the IGNORECASE built-in I wonder if this could be
> > implemented in a similar way as with SQL Server: by defining the
> > system tables either as IGNORECASE or not?
>
> You could create views. That would be slow but should work. This is
> actually how the PostgreSQL compatibility is implemented.

But how would the JDBC "driver" know of those tables when e.g. I call
getTables() or getColumns()?

Regards
Thomas

[Thomas Mueller]

Hi,

In any case I will add it to the roadmap. If enough people ask for it,
it will eventually be implemented.

"MySQL and MS SQL Server compatibility: support case sensitive (mixed
case) identifiers without quotes."

By the way, do you have a link to the docs?

> Well the statements are sometimes entered by the end user...

Sure. I thought you want this feature to automate tests.

> But how would the JDBC "driver" know of those tables when e.g. I call
> getTables() or getColumns()?

At the moment there is no way. It would be required to change H2 to
support that (not sure how yet).

Regards,
Thomas

[Thomas Kellerer]

Hi,

> "MySQL and MS SQL Server compatibility: support case sensitive (mixed
> case) identifiers without quotes."

Thanks!

If I had the slightest hope that it would help I'd file an issue with
MySQL and Microsoft to make them respect the ANSI standard a lot
more :)

> By the way, do you have a link to the docs?

Which docs do you mean?

> > Well the statements are sometimes entered by the end user...
>
> Sure. I thought you want this feature to automate tests.

Yes, that's the point. As I don't have control over the statements
entered by the user, I need to test for as many combinations as
possible (case-insensitive server with/without quotes and case-
sensitive servers with/without quotes. case-preserving but case-
insesitive with/without quotes).

Cheers
Thomas