SQL errors in batch module

[rhobolic]

Problem
If the batch module is generated out of the archetype and the batch is executed then sql errors arrise on the batch tables.

Expectation
No sql errors are present and everything is working out of the box

Solution hint
We have chosen postgre in the archetype instead of h2. It could be that the "Add_batch_tables.sql" is working just in h2 and is h2 specific. In below screenshot you can see what needs to be done that it works for postgre.

[hohwille]

@rhobolic thanks for your issue.
As you can see by the path the SQL is written for h2:
https://github.com/devonfw/devon4j/blob/master/templates/server/src/main/resources/archetype-resources/batch/src/main/resources/db/migration/h2/V0005__Add_batch_tables.sql

As we recommend(ed) to use h2 database for JUnit testing, we had written the SQL for h2.
There are tons of databases out there and it does not seem feasible to support all of them.
So to be direct I would assume your expectation is wrong here. If you change the suggestions and assumptions e.g. switching from h2 to postgresql you are also in charge of chaning the SQL accordingly.

@maihacke do you have some cents on this?`Why did we actually have to use very h2 specific SQL here if we could have done it in a DB agnostic way?
@maybeec as we are switching strategies (esp. for quarkus) to use docker for DB in JUnits, how should we proceed here?
Is it wrong in general that we started all such stuff in a framework like manner and taking responsibility of DDL for products like spring-batch that we do not own? Most probably we had to add the DDL in order to be able to insert testdata via flyway while hibernates hbm2auto-ddl auto feature can create tables for you but acts after flyway has taken action...

[maybeec]

@hohwille honestly saying I started to remove H2 support in most of my projects I am in and run DB on demand with docker as all my projects are at least kubenetes deployments.
That brings several advantages as I faced several workarounds in tests to run on H2.
In addition H2 isn't even faster in some cases. So if possible run tests against the target dB in a container brings a lot of advantages with sometimes depending on the DB with costs on test duration. But especially in a real continuous development project you anyhow want to test your feature branches as realistic as possible to not introduce issues you haven't seen with H2.

My voting would be to get rid of H2 by default and propose DB in a container whenever possible.

[maihacke]

@maihacke do you have some cents on this?`Why did we actually have to use very h2 specific SQL here if we could have done it in a DB agnostic way?

I'm not very sure, but propably it is because of the identity columns. Propably there is no hibernate orm magic here which decouples this from the database technology and spring batch just assumes that the column is handled by the DB.

@hohwille honestly saying I started to remove H2 support in most of my projects

I second this. But this has also a strong impact on CI/CD. But all in all the hassle with the containers (even in CI/CD) is better to solve than having to adopt the implementation (or tests) to different databases.