I am creating a spring profile for dynamic environments in gitlab and don't want to create a separate mysql db for each instance, so what I try is to use my existing liquibase migrations with hsqldb in that speciffic profile, which seems to work besides the engine=InnoDb part in the sql.
I already added sql.syntax_mys=true
to the datasource url, which supported the datatypes, not the engine part tho.
Since I want to avoid writing different sql migrations for the dynamic environments and already have a prod instance changing the migration or adding separate migrations is not really an option for me.
Is there a way to tell hsql to just ignore that part, or define it as some function which does nothing?
An example sql would be:
create table if not exists xy(
field1 varchar(255) not null,
field2 ....
) engine=InnoDB;
MySQL supports comments, including a special format for conditional execution: https://dev.mysql.com/doc/refman/8.0/en/comments.html
If you add a version number after the
!
character, the syntax within the comment is executed only if the MySQL version is greater than or equal to the specified version number. The KEY_BLOCK_SIZE keyword in the following comment is executed only by servers from MySQL 5.1.10 or higher:CREATE TABLE t1(a INT, KEY (a)) /*!50110 KEY_BLOCK_SIZE=1024 */;
HSQLDB also supports comment syntax in SQL statements: http://www.hsqldb.org/doc/1.8/guide/ch09.html#N124ED
All these types of comments are ignored by the database.
Based on this, you could put the ENGINE=InnoDB
into a comment so that HSQLDB will ignore it, but MySQL will run it:
create table if not exists xy(
field1 varchar(255) not null,
field2 ....
) /*!10000 engine=InnoDB; */