topic Re: How can I update data in Activiti DB upgrades? in Alfresco Archive

How can I update data in Activiti DB upgrades?

frauke — Mon, 14 Jan 2013 07:54:52 GMT

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 generell

Re: How can I update data in Activiti DB upgrades?

trademak — Mon, 14 Jan 2013 09:20:00 GMT

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,

Re: How can I update data in Activiti DB upgrades?

frauke — Mon, 14 Jan 2013 09:34:58 GMT

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

Re: How can I update data in Activiti DB upgrades?

trademak — Mon, 14 Jan 2013 10:44:16 GMT

Hi Frauke,

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

Best regards,

Re: How can I update data in Activiti DB upgrades?

frauke — Mon, 14 Jan 2013 10:54:11 GMT

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

Re: How can I update data in Activiti DB upgrades?

trademak — Mon, 14 Jan 2013 11:02:44 GMT

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,

Re: How can I update data in Activiti DB upgrades?

frauke — Mon, 14 Jan 2013 11:12:12 GMT

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

Re: How can I update data in Activiti DB upgrades?

trademak — Mon, 14 Jan 2013 12:14:56 GMT

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,