My team is using the Flowable BPMN engine with admin and IDM. We’re using the provided mssql scripts to manage the DB schema, and have databaseSchemaUpdate explicitly set to false, but the service still makes schema changes on startup. In particular, it adds a table ACT_ADM_SERVER_CONFIG, which is not anywhere in the provided scripts. Is this table necessary for admin/IDM? If so, why isn’t it in the provided DB scripts? Do we need to allow Liquibase to modify schema in order to use admin and/or IDM?
For the engines we provide the database scripts to create the database schema manually. For the Modeler and Admin application we use Liquibase to manage the database schema for these applications (only a couple of tables), and there we don’t provide the database scripts yet. Would it be ok for you to let Liquibase manage the database schema automatically for the Modeler and Admin application tables? The Engines can still be created manually with the provided db scripts.
Hi Tijs, thank you for the explanation! If possible, it would be helpful to surface that information in the documentation (or make it more obvious, if it’s already there and I missed it)–we lost some time trying to figure out why the schema was still being modified after changing the config.
Ideally, we’d have scripts to manage all necessary schema changes, since the on-the-fly schema changes that Liquibase makes cause trouble for our db management in general. If there are plans to make these available in the future, that would help. Otherwise we may end up hacking together our own, which would be somewhat unreliable and cause extra work each time we want to upgrade.
That’s a fair point. We’ve already have the sql scripts for the other liquibase based engines, so this shouldn’t be too hard to do.
Does this look right (for mssql)? Anything else I’ll need?
create table ACT_ADM_SERVER_CONFIG ( ID_ nvarchar(255) not null, NAME_ nvarchar(50), DESCRIPTION_ nvarchar(255), SERVER_ADDRESS_ nvarchar(100), PORT_ int, CONTEXT_ROOT_ nvarchar(100), REST_ROOT_ nvarchar(100), USER_NAME_ nvarchar(100), PASSWORD_ nvarchar(100), ENDPOINT_TYPE_ int, TENANT_ID_ nvarchar(50), primary key (ID_) );
…should there maybe be a property table to track admin schema version?
I’ve reviewed the oracle create scripts in the latest flowable-sql project, for Flowable 22.214.171.124. It looks like they still do not contain the DDL to create 8 tables: the Admin (ACT_ADM_) tables and the Design/Modeling (ACT_DE_) tables. I think we need these, because we are not permitted to do schema updates by Liquibase in our production environment, we need to provide SQL scripts that go through DBA review. A couple years ago, @joram indicated that it wouldn’t be hard to do these.
That’s correct: both Modeler/Admin don’t have the DDL in the create scripts. Turns out that generating those is harder than I said in the past (need real databases and manual checking as there have been errors in the generated sql which makes it time intensive). We simply haven’t come to semi-automating it, but we still want to.