5 Replies Latest reply on Mar 30, 2009 6:59 PM by Ronald van Kuijk

    how do we generate database migration script

    apdo Newbie

      I have a question that must be very easy to answer.

      We are currently using jbpm 3.0.1

      We are working on upgrading to jbpm 3.3.1 GA

      I have search about database migration.

      I have found the following:

      https://jira.jboss.org/jira/browse/JBPM-1823


      However, I have no Idea where to find that target or anyother target uses to make my database schema upgrade to the version 3.3.1 GA. (most probably by generating a schema migration script)
      Could someone help me to find that information?

      obviously the database to migrate is not empty. Should we therefore, have to perform other manipulation on the data or the schema migration script generated by the target will be enough?

      thank you very much for your help,

      An Phong Do

        • 1. Re: how do we generate database migration script
          Ronald van Kuijk Master

          afaik, there is no migrationscript from 3.0 to 3.2/3.3. 3.2.x is the same as 3.3 just without the GWT console...

          From 3.1 to 3.2 there is (afaik, never needed it)

          From 3.2 on (so to 4) db migration will have a high priority

          • 2. Re: how do we generate database migration script
            apdo Newbie

            Thank you very much for your answer.

            If I understand correctly, there is no script from 3.0 to 3.1 but there is one from 3.1 to 3.2.

            This means that I will have to write a migration script from 3.0 and 3.1 and use the migration script that already exist for the migration from 3.1 to 3.2/3.3.

            Does somebody already write a script for the migration between 3.0 and 3.1? If nobody did it I will do it. I have production data that need to be migrated.

            Could you please tell me where to find the migration script from 3.1 to 3.2? I have look for migrationscript and I didn't find anything. Obviously I am not looking at the right place.

            Thanks

            An Phong Do

            • 3. Re: how do we generate database migration script
              Ronald van Kuijk Master

              In the jbpm 3.2 source, in the db module, there is an ant script that generates update scripts. It uses the class JbpmSchemaTask from /modules/core/src/main/java/org/jbpm/ant.

              Never used it, so not sure how it exactly works. But it is a schema update. Not sure if all data in it is still valid then (e.g. new colums with 'not null' etc)

              • 4. Re: how do we generate database migration script
                apdo Newbie

                My migration script from 3.0.1 to 3.3.1.

                Use the following carefully since there is assumption on the content of my database.


                update JBPM_ACTION set ISASYNC_ = 0
                update JBPM_COMMENT set VERSION_ = 0

                --newString + oldStringvalue back to varchar 255?

                update JBPM_MODULEINSTANCE set VERSION_ = 0

                update JBPM_NODE set ISASYNC_ = 0, ISASYNCEXCL_ = 0
                update JBPM_NODE set ENDTASKS_ = 0 where CLASS_ = 'K' --verifier avec Diem bizzarement il y a que 93 row
                -- NOTE: SUBPROCNAME_, DECISIONEXPRESSION_, SCRIPT_ were all null in my database

                update JBPM_POOLEDACTOR set VERSION_ = 0

                update JBPM_PROCESSDEFINITION set CLASS_ = 'P'
                -- Note: DESCRIPTION_ is left null

                update JBPM_PROCESSINSTANCE set VERSION_ = 0, ISSUSPENDED_ = 0
                --Note: KEY_ is left null
                --Question: is ISSUSPENDED_ represent a cancelled process?

                update JBPM_RUNTIMEACTION set VERSION_ = 0

                /* I dont have any entries of this type in my database
                JBPM_SWIMLANE
                Colimn ACTORIDEXPRESSION_ not in bpmtemp
                Colimn POOLEDACTORSEXPRESSION_ not in bpmtemp
                */

                update JBPM_TASK set ISSIGNALLING_ = 1, PRIORITY_ = 3
                --Colimn CONDITION_, ACTORIDEXPRESSION_, POOLEDACTORSEXPRESSION_ are left null


                /* update JBPM_TASKINSTANCE */
                DECLARE JBPM_TASKINSTANCE_CURSOR CURSOR
                FOR
                select ti.ID_, ti.CREATE_, ti.END_, PROCESSINSTANCE_
                from JBPM_TASKINSTANCE as ti
                inner join JBPM_TOKEN as t on (t.id_ = ti.token_)

                DECLARE @taskId int
                DECLARE @TaskCreate datetime
                DECLARE @TaskEnd datetime
                DECLARE @pi numeric

                OPEN JBPM_TASKINSTANCE_CURSOR
                FETCH NEXT FROM JBPM_TASKINSTANCE_CURSOR INTO @taskId, @TaskCreate, @TaskEnd, @pi
                WHILE (@@FETCH_STATUS = 0)
                BEGIN
                DECLARE @isOpen tinyint
                set @isOpen = 0;

                if (@TaskEnd is null)
                set @isOpen = 1;

                update JBPM_TASKINSTANCE set CLASS_ = 'T', VERSION_ = 1, ISSUSPENDED_ = 0, ISOPEN_ = @isOpen, PROCINST_ = @pi where id_ = @taskId
                --I do not manually suspend task force 0

                FETCH NEXT FROM JBPM_TASKINSTANCE_CURSOR INTO @taskId, @TaskCreate, @TaskEnd, @pi
                END

                CLOSE JBPM_TASKINSTANCE_CURSOR
                ;

                update JBPM_TOKEN set VERSION_ = 0, ISSUSPENDED_ = 0
                --Note: LOCK_, SUBPROCESSINSTANCE_ left null

                update JBPM_TOKENVARIABLEMAP set VERSION_ = 0

                /* both new column are left null
                update JBPM_TRANSITION
                Colimn DESCRIPTION_ not in bpmtemp
                Colimn CONDITION_ not in bpmtemp
                */

                update JBPM_VARIABLEINSTANCE set VERSION_ = 0

                Let me know if you use it and found error in the script.

                An Phong Do

                • 5. Re: how do we generate database migration script
                  Ronald van Kuijk Master

                  ohhh btw, you should focus on the 3.2.x branche.... 3.3.x is 'halted' search the forum on why... 3.2.x is not that different