GenerateChangeLog fails on escaped lowercase identifier
Description
Environment
hsql, postgresql on Ubuntu 10.10 x64
Attachments
Activity
Nathan Voxland January 25, 2011 at 3:00 AM
Added user and password as h2 reserved words. You will need to make sure you use consistant cases in all your tools, though, if you use a reserved word. You are right that it is not safe to escape all object names since that breaks times people expect it to be case insensitive
Sven Arnold January 9, 2011 at 12:49 PM
I tried this again with revision 1999 (I believe relevant commit is at rev. 1851 "Should not use upper case table name by default").
Now the error message becomes:
Still, I think the problem is that a descision about escaping the object name with double quotes is solely based on a list of keywords in the Database Implementation. In the given case the object "password" is escaped at database creation time and therefore has become case sensitive.
I can fix my case if I make HsqlDatabase.escapeDatabaseObject return an escaped objectName in every case:
But I suspect this will break other cases.
The rules right now for your ORM are:
1. Don't use escaped object identifiers if you are not mapping to a reserved keyword.
2. If you are using two different DMBS don't map to an identifier that is a reserved keyword at only one of them.
Attached is a mimimal hsql database that contains a table with name "user" and a column "password". Those names are escaped with double quotes. Running GenerateChangeLog on this database leads to:
Replacing the lowercase "password" with "PASSWORD" works.
It looks like liquibase removes/ignores the quoting and transforms to uppercase because it detects that "password" is not a reserved keyword on hsql. Instead, the exact name of the identifier in the original database should be used.
Slightly related to http://liquibase.jira.com/browse/CORE-710.
Background:
This issue striked me when I tried to run liquibase on a grails project. In this project I mapped the password field in my domain class explicitely using backticks:
User.groovy
This was necessary to avoid problems on postgresql where password is a reserved keyword.