+1 Great idea to make things easier for our users. On 2024/09/30 20:32:57 "Pere Fernandez (apache)" wrote: > Hi, > > I recently rised some PR's to introduce a new KIE Flyway Add-On that tries > to solve some issues we found when enabling Flyway to initialize the > application database in a compact setup (having runtime with other modules > that require persistence in the same app like data-index, jobs-service and > data-audit). In this setup, a single Flyway instance is executed for the > whole application during the startup and we are configuring it to apply all > the SQL scripts (sorted by the version specified in the file name) from all > those modules and keeping a single index for all the scripts applied during > the Flyway execution. > > The main problems we identified are related to the versioning of those SQL > files inside the modules. Flyway gets script information (version, > description...) from the file name (Ej: > V1.35.0__create_runtime_PostgreSQL.sql -> version is 1.35.0) and expects > version to be unique, it doesn't discriminate script source, description. > Each of our add-on uses its own versions in the SQL scripts (ej: runtime > persistence goes from V1.35.0 to V10.0.1, data-index from V1.32.0 to > V1.45.0.4...), in this scenario it is very likely to have collisions if > different modules use the same version in the script name. > Additionally, it's also possible that for the same reason, DB upgrades > won't work. For example if a module is updated to bring new SQL Scripts to > upgrade the DB (let's say data-index bringing V1.5.0) the same application > running a Flyway again won't be able to apply those changes if in a > previous run another add-on brought a script with a higher version (in this > case runtime-persistence has v10). > > Another issue is that, in this setup, Flyway must be configured to know the > locations of all the SQL scripts in the applications.properties. In > general, this SQL files are in different locations, and there are modules > that provide SQL's for different DB types (h2, pgsql). This can be a bit > cumbersome to have it properly configured, specially for customers that are > not familiar with the internals of our code. > > > So, in order to help here, we are adding this Kie Flyway Add-On. > > The idea is that instead of using the default Flyway integration provided > by the Platform (Quarkus/Spring-Boot), this Add-on will be in charge of > running a separate instance of Flyway for each module that requires it and > using an independent index for each one them, this way we can ensure that > there will be no friction between SQL scripts. To make an module available > for KIE Flyway, first we must add the following dependency in our module > pom.xml: > > <dependency> > <groupId>org.kie</groupId> > <artifactId>kie-addons-<quarkus|springboot>-flyway</artifactId> > </dependency> > > And make sure we include a kie-flyway.properties descriptor located at ` > src/main/resources/META-INF` with the following properties: > > - module.name: logic name to identify the module. > - module.locations.<db_type>: this property map is used to setup the script > locations per each database supported by our module, (ej: > module.locations.postgresql=classpath:<path-to-my-sql-files>). It's > possible to use the module.locations.default key if we want to use some > fallback SQL's in case the application Database type isn't supported. This > locations should refer to a path (or comma-separate paths) unique to the > module to avoid possible collisions and we should as much as possible using > default Flyway locations like db/migrations. You'll notice that I moved the > current SQL scripts from its location to > src/main/resources/kie-flyway/db/<module-name>/<db-type> > > You can see an example of this file here (kie-flyway.properties > <https://github.com/apache/incubator-kie-kogito-runtimes/blob/5037c9de5608fde136fef274effd3045c91c162f/addons/common/persistence/jdbc/src/main/resources/META-INF/kie-flyway.properties>) > and the new module locations here (locations > <https://github.com/apache/incubator-kie-kogito-runtimes/tree/5037c9de5608fde136fef274effd3045c91c162f/addons/common/persistence/jdbc/src/main/resources/kie-flyway/db/persistence-jdbc> > ). > > > This addon supports the following configurations in the > application.properties file: > > - kie.flyway.enabled=true|false # Enables the execution of the KIE > Flyway Add-On on startup, it's disabled by default in prod mode (Quarkus) > and fully disabled in Spring-boot. > - kie.flyway.<module-name>.enabled=true|false # This is used to disable > specific module if we need to > > > Some considerations: > > - This Add-On is intended to be used for development / test / examples > and not recommended to use in production > - As all our Add-Ons will use the default application datasource, so > it's not possible to make it use a different > - It doesn't cover all the Flyway features, it's only used to run > migrations to initialize our components. > - It doesn't replace the Platform Flyway in the kogito applications, the > user can still use Flyway to load its own scripts without having to care of > configuring anything from our modules. > > > At this point, for simplicity I only tried to address the DB Initialization > of our Add-Ons (runtime persistence, data-index, jobs-service, data-audit & > user-tasks), but this feature can be also brought to the Data-Index & Jobs > Service if we want to, the only reason I didn't do it is to avoid doing > changes that could break the Sonataflow Operator and the examples, but I'll > happily apply the changes there if there's an agreement on this. > > I think that's all... sorry for this long email but I think this > improvement required some explanations. > > Thank you! > > Pere >
--------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
