H2 error or renaming table from PascalCase to snake_case

0
votes

I use spring boot in a project. I have liquibase scripts to create tables and enter seed data. The repo tests are run using the @DataJpaTest annotation which by default uses an in-memory h2 database.

The db for this project was initially SQLServer, so the liquibase scripts had table names in PascalCase e.g. FooBar. Later the db had to be changed to PostgreSQL, so new liquibase scripts were added to rename to tables from PascalCase to snake_case e.g. from FooBar to foo_bar.

The liquibase scripts run successfully against the PostgreSQL database, but when they run as part of the repo tests (against the H2 db), the rename scripts fail. They fail when trying to rename a table name with a single word e.g. from Insitution to institution. I get the below error:

Caused by: org.h2.jdbc.JdbcSQLException: Table "INSTITUTION" already exists; SQL statement:
ALTER TABLE PUBLIC.Institution RENAME TO institution [42101-196]

Solution that I have already tried and failed:

  1. A separate application.properties file in src/test/resources pointing to a custom in-memory h2 database which looks as follows:

    spring.datasource.url = jdbc:h2:mem:test;DB_CLOSE_ON_EXIT=FALSE;MODE=PostgreSQL spring.datasource.username = sa spring.datasource.password = sa spring.datasource.driverClassName = org.h2.Driver

  2. I have also tried to mention the table name in double quotes in the liquibase script thinking it would preserve the case. Did not work

Please help! :(

1
spring-booth2integration-testing

1 Answers

1
votes

H2 converts your unquoted table name references to uppercase. If you quote the target name with double quotes:

ALTER TABLE Institution RENAME TO "institution"

... will rename the INSTITUTION table to institution.

Your JPA implementation may have to be configured to quote the table names from here on. With hibernate you can use hibernate.globally_quoted_identifiers=true for that.