Source

osworkflow / docs / Migrating from version 1.0.html

Full commit
<html>
    <head>
        <title>Migrating from version 1.0</title>
	    <link rel="stylesheet" href="styles/site.css" type="text/css" />
    </head>

    <body>
	    <table class="pagecontent" border="0" cellpadding="0" cellspacing="0" width="100%" bgcolor="#ffffff">
		    <tr>
			    <td valign="top" class="pagebody">

				    <p class="paragraph">Migrating from OSWorkflow 1.x to 2.1+ is slightly complex due to the introduction of joins and splits, which means that there <b class="strong">can now be more than one current step</b>. Below is an attempt to provide helpful information for migrating your application to version 2.1+:
<ul class="star">
<li> <b class="strong">Check your workflow XML file with the latest DTD</b> - the DTD has changed a bit, especially with regards to functions, validators, and registers. The location attribute for these has been removed and must now be set with an <b class="strong">arg</b> name/value pair. Please read the documentation for the exact values you should place here.</li>
<li> <b class="strong">Data migration</b> - the tables in version 1.x were much simpler than they are now in 2.1+. <b class="strong">Status values have changed from numbers to Strings</b>. <b class="strong">The conversion is as follows: 0 -&gt; Queued, 1 -&gt; Underway, 2 - &gt; Finished</b>. Below is a small guide to help you migrate your data so that it works under 2.1+:</li>
</ul>
<h3 class="heading3"> The old OSWorkflow schema is as follows:</h3></p><div class="code"><pre>entry &#10;&#45;&#45;&#45;&#45; &#10;id &#10;workflowname &#10;currentStep &#10;&#10;step &#10;&#45;&#45; &#10;id &#10;prevId &#10;stepId&#10;entryId &#10;status &#10;startDate &#10;finishDate &#10;actionId &#10;owner</pre></div><p class="paragraph"><h3 class="heading3"> The new schema is now:</h3></p><div class="code"><pre>OS&#95;WFENTRY &#10;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45; &#10;ID &#10;NAME &#10;INITIALIZED &#10;&#10;&#10;OS&#95;CURRENTSTEP &#10;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45; &#10;ID &#10;ENTRY&#95;ID &#10;STEP&#95;ID &#10;ACTION&#95;ID &#10;OWNER &#10;START&#95;DATE &#10;FINISH&#95;DATE &#10;STATUS &#10;&#10;&#10;OS&#95;HISTORYSTEP &#10;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45; &#10;ID &#10;ENTRY&#95;ID &#10;STEP&#95;ID &#10;ACTION&#95;ID &#10;OWNER &#10;START&#95;DATE &#10;FINISH&#95;DATE &#10;STATUS &#10;&#10;&#10;OS&#95;CURRENTSTEP&#95;PREV &#10;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45; &#10;ID &#10;PREVIOUS&#95;ID &#10;&#10;&#10;OS&#95;HISTORYSTEP&#95;PREV &#10;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45;&#45; &#10;ID &#10;PREVIOUS&#95;ID</pre></div><p class="paragraph">As an example, a creation script for Oracle would look like:</p><div class="code"><pre>create table OS&#95;WFENTRY&#10;    (&#10;        ID number,&#10;        NAME varchar(20),&#10;        INITIALIZED integer,&#10;        primary key (ID)&#10;    );&#10;    create table OS&#95;CURRENTSTEP&#10;    (&#10;        ID number,&#10;        ENTRY&#95;ID number,&#10;        STEP&#95;ID integer,&#10;        ACTION&#95;ID integer,&#10;        OWNER varchar(20),&#10;        START&#95;DATE date,&#10;        FINISH&#95;DATE date,&#10;        STATUS varchar(20),&#10;        primary key (ID),&#10;        foreign key (ENTRY&#95;ID) references OS&#95;WFENTRY(ID),&#10;        foreign key (OWNER) references USERS(USERNAME)&#10;    );&#10;    create table OS&#95;HISTORYSTEP&#10;    (&#10;        ID number,&#10;        ENTRY&#95;ID number,&#10;        STEP&#95;ID integer,&#10;        ACTION&#95;ID integer,&#10;        OWNER varchar(20),&#10;        START&#95;DATE date,&#10;        FINISH&#95;DATE date,&#10;        STATUS varchar(20),&#10;        primary key (ID),&#10;        foreign key (ENTRY&#95;ID) references OS&#95;WFENTRY(ID),&#10;        foreign key (OWNER) references USERS(USERNAME)&#10;    );&#10;    create table OS&#95;CURRENTSTEP&#95;PREV&#10;    (&#10;        ID number,&#10;        PREVIOUS&#95;ID number,&#10;        primary key (ID, PREVIOUS&#95;ID),&#10;        foreign key (ID) references OS&#95;CURRENTSTEP(ID),&#10;        foreign key (PREVIOUS&#95;ID) references OS&#95;HISTORYSTEP(ID)&#10;    );&#10;    create table OS&#95;HISTORYSTEP&#95;PREV&#10;    (&#10;        ID number,&#10;        PREVIOUS&#95;ID number,&#10;        primary key (ID, PREVIOUS&#95;ID),&#10;        foreign key (ID) references OS&#95;HISTORYSTEP(ID),&#10;        foreign key (PREVIOUS&#95;ID) references OS&#95;HISTORYSTEP(ID)&#10;    );</pre></div><p class="paragraph"><h3 class="heading3"> How to migrate</h3></p>First, dump all non-initialized workflows in your old schema with 

<div class="code"><pre>DELTE <span class="sql&#45;keyword">FROM</span> entry <span class="sql&#45;keyword">WHERE</span> currentStep is NULL or currentStep = 0;</pre></div><p class="paragraph">Now we can assume the rest of the workflow instances are all initialized. Now just copy the (id, workflowName) tuple in to OS_WFENTRY as the tuple (id, name, 1) (the 1 is to mark the workflow as initialized).</p>Now you want to populate the new OS_HISTORYSTEP and OS_HISTORYSTEP_PREV tables with data for non-current steps. In the old tables, non-current steps are those which don&#039;t have a join from the old &#039;step&#039; table to the old &#039;entry&#039; table. A sample query which would return you only non-current steps would look like:

<div class="code"><pre>select ID as id, STEPID as STEP&#95;ID, STATUS as STATUS, STARTDATE as START&#95;DATE,&#10;        FINISHDATE as FINISH&#95;DATE, ACTIONID as ACTION&#95;ID, OWNER as OWNER, PREVID as PREVIOUS&#95;ID,&#10;        ENTRYID as ENTRY&#95;ID from STEP&#10;    where ID not in&#10;        (select step.ID from STEP step, WORKFLOWENTRY we&#10;            where step.ID = we.CURRENTSTEP)</pre></div><p class="paragraph">Now you want to populate the new OS_CURRENTSTEP and OS_CURRENTSTEP_PREV tables with data for current steps. In the old tables, current steps are those which do have a join from the old &#039;step&#039; table to the old &#039;in the old &#039;step&#039; table to the old entry table. A sample query which would return you only non-current steps would look like:</p><div class="code"><pre>select step.ID as id, STEPID as STEP&#95;ID, STATUS as STATUS, STARTDATE as START&#95;DATE,&#10;        FINISHDATE as FINISH&#95;DATE, ACTIONID as ACTION&#95;ID, OWNER as OWNER, PREVID as PREVIOUS&#95;ID,&#10;        ENTRYID as ENTRY&#95;ID from STEP, WORKFLOWENTRY we&#10;        where step.ID = we.CURRENTSTEP</pre></div><p class="paragraph">Note that if your db can not handle the complex query above for the non-current step (MySQL maybe?), an alternate mechanism would be to get and process the current steps, delete them, and then get all the remaining steps and process them, which would be historical. But the &#039;xxx_PREV&#039; tables should be written after the actual step tables, so the constraints from the declared foreign key relationships are not violated.</p>Now delete the old tables.<p class="paragraph">A Java/iBatis sample script to handle the above conversion is available else where in the docs:</p>Note also that OsWorkflow 1.0 used an old version of OsCore, while OsWorkflow 2.1 uses the 2.01 version of OsCore. When using the EJB version, you need to update the OS_SEQUENCE table used by the OsCore sequence generator, to change the &#039;count&#039; column to &#039;value&#039;. Here&#039;s a groovy script which shows the logic for handling this:

<div class="code"><pre>Connection conn = java.sql.DriverManager.getConnection(dbUrl, dbUsername, dbPassword)&#10;sql = <span class="java&#45;keyword">new</span> Sql(conn)&#10;&#10;sql.execute(&quot;alter table OS&#95;SEQUENCE add (value number(10))&quot;);&#10;&#10;count = 0;&#10;sql.eachRow(&quot;select &#42; from OS&#95;SEQUENCE&quot;) &#123; &#10;  sql.execute(&quot;update OS&#95;SEQUENCE set value = $&#123;it.count&#125; where name = $&#123;it.name&#125;&quot;);&#10;  count&#43;&#43;;&#10;&#125;&#10;&#10;println &quot;Updated $&#123;count&#125; rows&quot;&#10;&#10;sql.execute(&quot;alter table OS&#95;SEQUENCE drop column count&quot;);</pre></div>


				    
			    </td>
		    </tr>
	    </table>
    </body>
</html>