Disable automatic SQL table initialization

[ChristianKernDev]

By default JBeret will create the needed SQL tables, if "quarkus.jberet.repository.type=jdbc" is set.

In some cases this is not intended, for example if the tables get automatically created with a tool like flyway. My project setup includes flyway where i created a migration file which creates the JBeret tables, as i need some extra stuff in them.

On Startup the first thing that is executed is JBeret creating the tables and only after that Flyway is called, which fails, because the tables already exist.

Possible solutions are:

• Option to disable the creation in JBeret
• Delay table creation to a later point so it happens after flyway

[radcortez]

I don't think we can do it from this side, because JBeret will always try to create the tables if the tables are not there.

I guess one option would be to add a dependency to the Flyway Extension and try to make it run first... but it is not a good option, since we don't need this dependency here, and depending on how things are being done, it may not even be possible.

Have you thought of adjusting your scripts to perform ALTER TABLES with the required changes, instead of creating the tables from scratch? In that way, JBeret would create the initial schema of the Job Repository, and Flyway would only apply the changes.

[luca-bassoricci]

@radcortez is right.
In JBeret user guide the chapter Configure JDBC Job Repository explains how to resolve this kind of problem with custom script

[ChristianKernDev]

Thanks for the feedback, another issue in my setup is, that the flyway user has special permissions to create schemas and tables while the JBeret user does not have them (and also should not have them).
But if there is nothing that can be done from quarkus-jberet we won't find a solution here.

In general i think it would be a good idea to introduce a config to disable table generation on JBeret side.

[ChristianKernDev]

Additional connected question:
If i want to add a custom .ddl file with "quarkus.jberet.repository.jdbc.ddl-file", it fails, because the file (which is in my src/main/resources) is not present in the class loader.
JBeret tries to load the file by using "getClassLoader(false).getResourceAsStream(ddlFile)" (see JdbcRepositry.java), but the class loader is the "Quarkus Base Runtime ClassLoader" and resources from my code are not (yet) loaded in this class loader.

Any thoughts on that or how this is normally solved, as i could not find a working example for this online

[radcortez]

In general i think it would be a good idea to introduce a config to disable table generation on JBeret side.

Yes, this would have to be done on the JBeret side. On the Quarkus side, we just call their API to create the Repository which does the table creation work: https://github.com/jberet/jsr352/blob/main/jberet-core/src/main/java/org/jberet/repository/JdbcRepository.java#L119-L310. There is nothing there that we can use to skip the tables creation.

Any thoughts on that or how this is normally solved, as i could not find a working example for this online

Strange. We do have an integration test with a custom ddl example here: https://github.com/quarkiverse/quarkus-jberet/tree/19b9ecfa043bae184ed3472cde3143e4b87ca93f/integration-tests/jdbc-repository

[ChristianKernDev]

One thing that could be done on the extension side is to introduce a dependency on the flyway build item, so in case flyway is included in the build steps, it is executed first. Similar to what @geoand proposed in quarkusio/quarkus#48450
For reference the flyway processor: https://github.com/quarkusio/quarkus/blob/main/extensions/flyway/deployment/src/main/java/io/quarkus/flyway/deployment/FlywayProcessor.java

I don't really have the nescessary knowledge about the build system to properly setup and test this, but this might be a good addition.

Regarding the integration test: I think this setup only works with an integration test, but not actually when starting an application in dev mode, at least i could not get it to work, as my resources are not even loaded by the time the extension is executed.

[radcortez]

One thing that could be done on the extension side is to introduce a dependency on the flyway build item, so in case flyway is included in the build steps, it is executed first. Similar to what @geoand proposed in quarkusio/quarkus#48450

Yes, that is inline with one of the possibilities I've suggested

Regarding the integration test: I think this setup only works with an integration test, but not actually when starting an application in dev mode, at least i could not get it to work, as my resources are not even loaded by the time the extension is executed.

I'll have a look.

[radcortez]

The problem with loading the resources is in Dev mode only due to: https://github.com/jberet/jsr352/blob/eeef999663d7da0e372aeeeac26ecf7201a3121d/jberet-core/src/main/java/org/jberet/repository/JdbcRepository.java#L211

Dev mode users 2 CLs... one for the dependencies and another one for the application resources. Because the code is calling the CL of JdbcRepository, that CL does not have the files. This does work in test or prod because there is a single CL.

I don't see a reason not to use TCCL to look for the files.

[radcortez]

A workaround is to set a separate module to place these files.