I have a small spring boot application that uses HSQB in server mode for its database and liquibase to maintain the schema. This works fine.
spring.datasource.url=jdbc:hsqldb:hsql://localhost:5678/mydb
One of the entities has a name that is a reserved SQL keyword:
@Entity
public class Group
But this causes no problems; Liquibase creates the table, the application can use the JPA entity.
Naturally I also want to write a few integration tests to prevent regression, for that I start HSQL in memory mode:
spring.datasource.url=jdbc:hsqldb:mem:mydb;DB_CLOSE_DELAY=-1
And the first thing of course is a test setup, where some entities are created. All can be created, except Group:
org.hsqldb.HsqlException: user lacks privilege or object not found: GROUP
The statement being executed is:
insert into group (code,lazylock,name,period_id,tenant_id,uid,id) values (?,?,?,?,?,?,default)
I know the table is present because prior in the log it reads:
07:07:27.942 [main] INFO liquibase.changelog - Table group created
07:07:27.943 [main] INFO liquibase.changelog - Unique constraint added to group(period_id,code)
07:07:27.943 [main] INFO liquibase.changelog - Unique constraint added to group(uid)
Using a reserved keyword is a risk, but it worked, and reserved keywords as table names usually are fixed by simply quoting the table name, like so:
@Entity
@Table(name = "`group`")
public class Group
And indeed the integration test now runs the setup correctly. However, now the application itself throws an exception:
org.hsqldb.HsqlException: user lacks privilege or object not found: group in statement [select g1_0.id,g1_0.code,g1_0.lazylock,g1_0.name,g1_0.period_id,g1_0.tenant_id,g1_0.uid from "group" g1_0 where g1_0.period_id=? order by g1_0.code]
The table name is now quoted, but apparently HSQL in server mode has issues with that. There appears to be a difference between server and in-memory mode concerning how reserved keywords are handled.
If the group table is renamed to group2 everything works fine. But group is the correct name, and it ís working, so I'd rather not change that name.
Am I missing some setting in the URL or for Hibernate to make this work in both server mode and in-memory mode? I've searched the interwebs, tried a few, but found no solution.
Trying to rename the table (in a populated database, the group2 test was in a newly created database) is not a success:
alter table group rename to groups;
[Code: -5581, SQL State: 42581] unexpected token: GROUP
alter table "group" rename to groups;
[Code: -5501, SQL State: 42501] user lacks privilege or object not found: PUBLIC.group
alter table 'group' rename to groups;
[Code: -5581, SQL State: 42581] unexpected token: group
The table group is present in the database.
It turned out I had changed the quoting strategy of Liquibase in an unrelated commit a while back already. The server mode tables were create without quoting, causing HSQLDB to convert them to uppercase. The tests used quoting, since they recreate the tables evert run, leaving them in lowercase. It is not possible to define a JPA mapping that matches both.
One can find the used case of tables using:
select * from INFORMATION_SCHEMA.TABLES;