Search code examples
javapostgresqlhibernatejpahsqldb

CITEXT: which solution I should use for tests with JPA?

I need to test psql citext with JUnit using hsqldb

Step 1

Use liquibase to set up psql-dialect to hsqldb

<changeSet id="5" author="ustimenko" context="QA">
    <comment>Modify test database to be case insensitive</comment>

    <sql>
        SET DATABASE SQL SYNTAX PGS TRUE;
        SET DATABASE COLLATION SQL_TEXT_UCC;
    </sql>
</changeSet>

Step 2

Change column definition via JPA

@Column(columnDefinition = "citext")
private String email;

Step 3

Caused by: java.sql.SQLSyntaxErrorException: type not found or user lacks privilege: CITEXT
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
    at org.hsqldb.jdbc.JDBCStatement.executeUpdate(Unknown Source)
    at org.hibernate.tool.schema.internal.TargetDatabaseImpl.accept(TargetDatabaseImpl.java:56)
    ... 79 common frames omitted
Caused by: org.hsqldb.HsqlException: type not found or user lacks privilege: CITEXT
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.ParserDQL.readTypeDefinition(Unknown Source)
    at org.hsqldb.ParserTable.readColumnDefinitionOrNull(Unknown Source)
    at org.hsqldb.ParserTable.readTableContentsSource(Unknown Source)
    at org.hsqldb.ParserTable.compileCreateTableBody(Unknown Source)
    at org.hsqldb.ParserTable.compileCreateTable(Unknown Source)
    at org.hsqldb.ParserDDL.compileCreate(Unknown Source)
    at org.hsqldb.ParserCommand.compilePart(Unknown Source)
    at org.hsqldb.ParserCommand.compileStatements(Unknown Source)
    at org.hsqldb.Session.executeDirectStatement(Unknown Source)
    at org.hsqldb.Session.execute(Unknown Source)
    ... 82 common frames omitted

I tried to apply some number of solutions

Solution 1

jdbc:hsqldb:mem:test;sql.live_object=true as url of database
Have the same result

Solution 2

SET DATABASE LIVE OBJECT as part of liquibase changeset Have syntax error at LIVE keyword. I made sure it via inmemory hsqldb console. But when alter table with citext field type is changed to VARCHAR(3***)

Solution 3

Try to add jdbc:postgresql://localhost/test?stringtype=unspecified for my production datasource, but have the same result

Solution 4

Play with ddl-auto, but have no needed results.

Can you, please, explain what I should do? I would not like to use separate psql database for tests purpose. Is there any solution to use in-memory to solve my problem?

Solution

  • You want to use PostgreSQL case-insensitive comparison using the CITEX data type, which is case-insensitive string type. HSQLDB uses collation, which can be applied to the whole database or to a specific user-defined type. Define the CITEX type as follows, choosing the maximum character size of the columns using this type.

    CREATE TYPE CITEX AS VARCHAR(200) COLLATE SQL_TEXT_UCC