How can I update data in Activiti DB upgrades?

[frauke]

Hello everybody,

something I miss in the Activitiy database upgrades is a data transfer of the old data into the new format, i.e. into the new table ACT_HI_VARINST. Since it is a history table I do not want to lose the data. Have you any suggestions how to migrate the data, for this case and generelly for future cases?

We translate the upgrade scripts into Liquibase (Apache licenced), which is highly recommendable since it is database independant. Wouldn't it be a nice idea for Activiti too? I even detected some discrepancies in your scripts for the different databases, i.e. sometimes the ID_ field is nullable and sometimes not. You could avoid that using Liquibase. We even could consider to provide our Liquibase scripts to you: we have them for each version from the very beginning 

Best regards
Frauke

[trademak]

Hi,

In general there's no need for data transfers. But in the upgrade to Activiti 5.11 we introduced a new table ACT_HI_VARINST.
We discussed to do a data transfer, but we eventually chose the solution where the table is getting filled when new history is written (so also for existing processes).
One of the reasons we did choose this direction, is that we need to make sure the transfer solution runs on every supported database and this requires quite a bit of effort.
Another reason is that if the history tables are filled with a lot of data, you may not want to copy that amount of data to the new table.

We also looked at Liquibase and it's a very nice framework indeed. But I don't see the benefit it would bring to remove discrepancies in the scripts.
Because you would still need to write Liquibase XML definitions for every database supported. Do you have any thoughts on this?

Best regards,

[frauke]

Hi Tijs,

ok, your answer concerning ACT_HI_VARINST is comprehensible and ok for us this time. But still we have to be careful in future cases.

With Liquibase the common-source idea is the greatest advantage it has. We also support several databases and need only one Liquibase XML.
Adding one column to a table for example looks like that - for all databases:

   <changeSet id="2 Activiti 5.11 Update ACT_RE_DEPLOYMENT" author="fheyl">
        <addColumn tableName="ACT_RE_DEPLOYMENT">
            <column name="CATEGORY_" type="NVARCHAR(255)" />
        </addColumn>
   </changeSet>
Best regards
Frauke

[trademak]

Hi Frauke,

That sounds great, but in your example how would that map on DB2?
That definition is CATEGORY_ varchar(255),

Best regards,

[frauke]

Hi Tijs,

we configure Liquibase so that 'NVARCHAR' always maps to 'varchar' in DB2.
Liquibase provides some database specific TypeConverter classes for cases like that.

So thats very easy to solve. There might be more difficult cases where you need native SQL for some more complicated conversions. Then - yes - you have to provide different statements for each database. But hopefully that is not needed very often.

Best regards
Frauke

[trademak]

Hi Frauke,

Okay cool, would it be possible to share your Liquibase files and TypeConverter classes to Activiti?
If we could really have just one Liquibase file to create and update the databases then that would be a very nice step forward.

Best regards,

[frauke]

Hmmmm….. ok 

But some of the Activiti upgrades we (unfortunately) merged into one. And the Liquibase mappings are designed for our special needs, there might be other possibilities…

How can I send them to you? It seems I may not be able to upload them here. I would prefer to send them per mail anyway….

Best regards,
Frauke

[trademak]

It's also mainly to learn from your Liquibase usage, not to do an one-to-one copy.
You can send the files to tijs.rademakers AT alfresco.com

Thanks,