Case of the database short name

[GoogleCodeExporter]

What steps will reproduce the problem?
1. connect to jdbc:h2:mem:Test
2. CREATE SCHEMA "schema";
CREATE TABLE "schema"."T" (
"ID"  IDENTITY,
"VARCHAR" varchar(100) );
INSERT INTO "schema"."T" VALUES(1, 'some text');
3. select * from "Test"."schema"."T";

What is the expected output?
ID      VARCHAR  
1   some text

What do you see instead?

Schema "Test" not found;

What version of the product are you using? On what operating system, file
system, and virtual machine?
h2-1.2.135.jar on Windows XP and NTFS.

Do you know a workaround?
The bug is that the database name is parsed to upper case (this is why select * 
from "TEST"."schema"."T" 
works, which is also wrong). In Database.parseDatabaseShortName() I have 
replaced return 
StringUtils.toUpperEnglish(n); by return n;. This solves the bug, however it 
does break the tests, for 
example in testSimple.in.txt :
 insert into scriptSimple.public.test(id) values(1), (2)
Schema "SCRIPTSIMPLE" not found;

Since there are no quotes the catalog name is converted to upper case. To avoid 
adding quotes to all 
existing SQL, maybe you could add a property governing the parsing of the 
database name.

How important/urgent is the problem for you?
To access our databases, we use a framework that generates queries. They are 
always quoted as per the SQL 
standard to avoid case issues. So this problem prevents us from using h2.

In your view, is this a defect or a feature request?
A defect

Original issue reported on code.google.com by ilm.info...@gmail.com on 3 Jun 2010 at 8:14

[GoogleCodeExporter]

What framework do you use? How does the framework detect the database name, 
does it
parse the JDBC URL? How does it parse the URL exactly? 

Or do you create the database URL using some algorithm (appending the database 
name
to "jdbc:h2:mem:")? If the algorithm to create the URL is in your application, I
suggest to use the uppercase name. 

While not directly related to your problem, there is also the problem that the 
file
system is case insensitive in Windows.

For me, this is not a bug but a request for a new feature. A bug would be a 
deviation
from the specification or documentation, but using the database name in the 
query is
not a fully supported feature (it's also not documented). And so far I don't 
think
it's a feature I would like to add.

Original comment by thomas.t...@gmail.com on 4 Jun 2010 at 9:37

[GoogleCodeExporter]

> What framework do you use? 

One we built :-)

> Or do you create the database URL using some algorithm (appending the 
database 
> name to "jdbc:h2:mem:")? 

Yes that's what we do.

> If the algorithm to create the URL is in your application, I
> suggest to use the uppercase name. 

We can't : we have a whole lot of queries that are saved in .sql files 
(including at our customers), and they are shared for Postgresql and H2. The 
thing is, it used to work on H2 : when r2251 was committed, all 
token.equals(currentToken) were replaced by equalsToken(token, currentToken) 
which depends on identifiersToUpper. But in readIdentifierWithSchema(String) 
schemaName.equalsIgnoreCase(database.getShortName()) was replaced by 
equalsToken(schemaName, database.getShortName()). So queries like select * from 
"Test"."schema"."T"; worked before and now don't.

Every database system has a way to give arbitrary names to databases : in 
postgresql one always can, in H2 until recently I thought it worked like pg, 
and in MySQL they have lower_case_table_names.

> While not directly related to your problem, there is also the problem that the
> file system is case insensitive in Windows.

I know, see lower_case_table_names in 
http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html . 
There's a warning saying index corruption can occur under specific 
circumstances but I as a developer can take responsability. Further I 
understand the problem but it's an implementation detail, a database should 
completely abstract everything underneath it.

> For me, this is not a bug but a request for a new feature. A bug would be a 
deviation
> from the specification or documentation, but using the database name in the 
query is
> not a fully supported feature (it's also not documented). And so far I don't 
think
> it's a feature I would like to add.

I didn't realize that ; coming from Postgresql, I connected to a url like 
jdbc:h2:Test , tested my queries using "Test"."schema"."T" and they worked. 
Since I quoted the database name it seemed that internally H2 did retain the 
case as Postgresql did. And now I have customers with existing queries, so it 
would be greatly appreciated if you could add a property (even undocumented) 
like in the attached small patch so I can upgrade their H2.

Original comment by ilm.info...@gmail.com on 8 Jun 2010 at 10:44

Attachments:

• dbNameCase.patch

[GoogleCodeExporter]

> > What framework do you use? 
> One we built :-)

In this case it's in your control to set the jdbc URL. I suggest to use the 
uppercase database name in the database URL in this case.

> We can't

Why not? The database URL is in not related to the scripts.

By the way, the user name is also case insensitive. You can login using 'SA', 
'sa', 'Sa' or 'sA'.

Original comment by thomas.t...@gmail.com on 12 Jun 2010 at 11:05

[GoogleCodeExporter]

Implementing your request will break backward compatibility.

Original comment by thomas.t...@gmail.com on 12 Jun 2010 at 11:06

[GoogleCodeExporter]

> The database URL is in not related to the scripts.

the URL is not but the database name is : select * from "Test"."schema"."T";

> Implementing your request will break backward compatibility.

How so ? h2.databaseToUpper is true by default, so parseDatabaseShortName() 
will return the same value it always have.

Thanks for your time.

Original comment by ilm.info...@gmail.com on 14 Jun 2010 at 1:50

[GoogleCodeExporter]

> h2.databaseToUpper is true by default, so parseDatabaseShortName() will 
return the same value it always have.

You are right, I didn't see that. Sorry.

The patch looks easy, I will try to add it. If there are no problems with other 
tools, it could be made the default behavior with H2 version 1.3

Original comment by thomas.t...@gmail.com on 14 Jun 2010 at 6:33

• Changed state: Accepted

[GoogleCodeExporter]

I committed you patch.

Original comment by thomas.t...@gmail.com on 14 Jun 2010 at 7:04

[GoogleCodeExporter]

Great ! Thanks a lot.

Original comment by ilm.info...@gmail.com on 15 Jun 2010 at 4:58

[GoogleCodeExporter]

This is implemented in version 1.2.138, but not enabled by default (for 
compatibility with old version of H2). To test it, enable the system property 
h2.databaseToUpper. This property will be enabled by default in version 1.3.x

Original comment by thomas.t...@gmail.com on 28 Jun 2010 at 4:50

• Changed state: Started

[GoogleCodeExporter]

I'm setting the issue to 'fixed', even if the setting not yet enabled by 
default. Please add a comment if it still doesn't work for you (or doesn't work 
as expected) even when the system property is enabled.

Original comment by thomas.t...@gmail.com on 28 Jun 2010 at 6:53

• Changed state: Fixed