Proposal to ease maintenance of database schema

Community Contributions & Discussions
1

Currently you’re maintaining SQL-scripts for 7 different database systems, 7 create and 7 drop scripts for each. 98 files all together. As this is done manually, errors happen like last Friday, fixed on Saturday.
Every time I pull you latest changes into my fork I have to see what has changed in the meantime and take over those changes into my scripts for the Informix database system.

My idea to change the maintenance of the schema:

  • Maintain only 1 xml file for each “data domain” (case.engine, case.history, decision.engine, decision.history, …)
  • Use xslt files to create the sql files from that.
    Most of the work can be done by a base xslt file, additionally you have to have one for every database system to define the specific data types and other exceptions from the common implementation.
    I locally created a small prototype for proof of concept:
  • xsd file describing the schema
  • xml file for decision.engine
  • xslt for base used by xslt for DB2 and Informix
  • changed engine/pom.xml to call the xml transformation.
    If you agree that this would be helpful I could some more time on this to create a pull request for it.
2

I hear you say you have to maintaining 98 files, but the majority of these files are migration scripts from older Camunda versions. They do not change anymore.

Anyhow, as long as the bundled Camunda versions are shipped with the flat .sql files, I’m good with any solution for the repo.

3

Hi Frank,

I understand that this is frustrating to you. You follow the development very closely and this way get hit by errors in the scripts immediately.
Our CI in which we run all these databases in different versions also always finds these errors and then we fix them as soon as possible.

Currently I would not be in favor for “putting an XML/XSLT layer on top of SQL DDLs”. Then the errors happen at that layer and they are harder to trace back to the XSLT script.
I would first look at existing solutions like Flyweight or Liquibase.

Thanks for your continuous effort and feedback to the project!

Best,
Daniel

4