Good day,
I am trying to set up tests to use a local in-memory HSQL database, but I’m having trouble generating the init scripts for my entities since the main datastore is Postgres, not HSQL (there are no additional datastores in this project).
My question
How can I automatically generate the init scripts for HSQL when the real database type is Postgres?
Background
Using Cuba Studio’s Generate Database Scripts
In order to generate the init scripts the official way, I need to change the project’s database type to HSQL, then click Generate Database Scripts in the menu, then change the project’s database settings back. This cannot be done automatically, and seems like a lot of work to do something seemingly simple.
I dug around for the code that this action uses, but it’s part of the Studio plugin and is inaccessible from the project. I had hoped to trigger the HSQL generation from inside the test container, but that doesn’t seem possible.
Using Eclipse Link
I succeeded in using Eclipse Link to generate init scripts when starting a test based on the metadata, but the resulting SQL is slightly different from what Cuba Studio generates:
Cuba:
alter table table1 add constraint FK_TABLE1_FIELD foreign key (FIELD) references table2(ID) on delete SET NULL
Eclipse Link:
ALTER TABLE table1 ADD CONSTRAINT FK_table1_field FOREIGN KEY (field) REFERENCES table2 (ID)
I find the mostly-opposite uppercase/lowercase amusing for some reason, but the problematic part is that Eclipse Link isn’t generating on delete set null
. I realize that this is because of @OnDeleteInverse(DeletePolicy.UNLINK)
, which is a Cuba thing and Eclipse Link has no support for it, nor does Eclipse Link have any alternative that I can find.
This results in failure to delete some entities after a test runs, since a foreign key is pointing to the item that needs to be deleted. I’ve tried using @PreRemove
and a BeforeDeleteEntityListener
, but it seems those aren’t able to modify the foreign key to set it as null
before table2
is deleted.
Any ideas on what can be done?