Source

osworkflow / docs / 1.4 Persistence Options.html

Full commit
<html>
    <head>
        <title>OSWorkflow - 
         Persistence Options
        </title>
	    <link rel="stylesheet" href="styles/site.css" type="text/css" />
        <META http-equiv="Content-Type" content="text/html; charset=UTF-8">
    </head>

    <body>
	    <table class="pagecontent" border="0" cellpadding="0" cellspacing="0" width="100%" bgcolor="#ffffff">
		    <tr>
			    <td valign="top" class="pagebody">
				    <ul>
	<li>Back to <a href="1.3 Running the Example App.html" title="1.3 Running the Example App">1.3 Running the Example App</a></li>
	<li>Forward to <a href="1.5 Loading Workflow Definitions.html" title="1.5 Loading Workflow Definitions">1.5 Loading Workflow Definitions</a></li>
</ul>


<p>OSWorkflow provides a pluggable persistence mechanism that can be used to allow for many choices of ways for workflow content to be stored. Included with OSWorkflow are the following WorkflowStore implementations: <b>MemoryStore (default), SerializableStore, JDBCStore, OfbizStore, and EJBStore</b>. If one of these does not fit your requirements, you can implement your own workflow store by implementing the interface <a href="http://www.opensymphony.com/osworkflow/api/com/opensymphony/workflow/spi/WorkflowStore.html" title="Visit page outside Confluence">com.opensymphony.workflow.spi.WorkflowStore</a>. See the javadocs for more information. </p>

<p>Also, please note that each workflow store implementation may have required or optional properties that must or can be set. It is recommend that you read the javadocs for the workflow store that you plan to use so that you can configure it correctly. A sample JDBC configuration for the JDBCStore is given below:</p>

<p>(from osworkflow.xml)</p>
<div class="code"><div class="codeContent">
<pre class="code-java">&lt;persistence class=<span class="code-quote">"com.opensymphony.workflow.spi.jdbc.JDBCWorkflowStore"</span>&gt;
	&lt;!-- For jdbc persistence, all are required. --&gt;
	&lt;property key=<span class="code-quote">"datasource"</span> value=<span class="code-quote">"jdbc/DefaultDS"</span>/&gt;
	&lt;property key=<span class="code-quote">"entry.sequence"</span> 
                      value=<span class="code-quote">"SELECT nextVal('seq_os_wfentry')"</span>/&gt;
	&lt;property key=<span class="code-quote">"entry.table"</span> value=<span class="code-quote">"OS_WFENTRY"</span>/&gt;
	&lt;property key=<span class="code-quote">"entry.id"</span> value=<span class="code-quote">"ID"</span>/&gt;
	&lt;property key=<span class="code-quote">"entry.name"</span> value=<span class="code-quote">"NAME"</span>/&gt;
	&lt;property key=<span class="code-quote">"entry.state"</span> value=<span class="code-quote">"STATE"</span>/&gt;
	&lt;property key=<span class="code-quote">"step.sequence"</span> 
                       value=<span class="code-quote">"SELECT nextVal('seq_os_currentsteps')"</span>/&gt;
	&lt;property key=<span class="code-quote">"history.table"</span> value=<span class="code-quote">"OS_HISTORYSTEP"</span>/&gt;
	&lt;property key=<span class="code-quote">"current.table"</span> value=<span class="code-quote">"OS_CURRENTSTEP"</span>/&gt;
	&lt;property key=<span class="code-quote">"historyPrev.table"</span> value=<span class="code-quote">"OS_HISTORYSTEP_PREV"</span>/&gt;
	&lt;property key=<span class="code-quote">"currentPrev.table"</span> value=<span class="code-quote">"OS_CURRENTSTEP_PREV"</span>/&gt;
	&lt;property key=<span class="code-quote">"step.id"</span> value=<span class="code-quote">"ID"</span>/&gt;
	&lt;property key=<span class="code-quote">"step.entryId"</span> value=<span class="code-quote">"ENTRY_ID"</span>/&gt;
	&lt;property key=<span class="code-quote">"step.stepId"</span> value=<span class="code-quote">"STEP_ID"</span>/&gt;
	&lt;property key=<span class="code-quote">"step.actionId"</span> value=<span class="code-quote">"ACTION_ID"</span>/&gt;
	&lt;property key=<span class="code-quote">"step.owner"</span> value=<span class="code-quote">"OWNER"</span>/&gt;
	&lt;property key=<span class="code-quote">"step.caller"</span> value=<span class="code-quote">"CALLER"</span>/&gt;
	&lt;property key=<span class="code-quote">"step.startDate"</span> value=<span class="code-quote">"START_DATE"</span>/&gt;
	&lt;property key=<span class="code-quote">"step.finishDate"</span> value=<span class="code-quote">"FINISH_DATE"</span>/&gt;
	&lt;property key=<span class="code-quote">"step.dueDate"</span> value=<span class="code-quote">"DUE_DATE"</span>/&gt;
	&lt;property key=<span class="code-quote">"step.status"</span> value=<span class="code-quote">"STATUS"</span>/&gt;
	&lt;property key=<span class="code-quote">"step.previousId"</span> value=<span class="code-quote">"PREVIOUS_ID"</span>/&gt;
&lt;/persistence&gt;</pre>
</div></div>

<p>If you are using tomcat as the servlet container, you should config <b>$TOMCAT_HOME/conf/server.xml</b> for data source, by default, the data source name is <b>jdbc/DefaultDS</b>.</p>

<p>You should also config the propertyset to use jdbc by adding  <em>WEB-INF/classes/propertyset.xml</em>, please note that the datasource setting should match the name defined in tomcat.</p>
<div class="code"><div class="codeContent">
<pre class="code-java">&lt;propertysets&gt;
    &lt;propertyset name=<span class="code-quote">"jdbc"</span> 
      class=<span class="code-quote">"com.opensymphony.module.propertyset.database.JDBCPropertySet"</span>&gt;
        &lt;arg name=<span class="code-quote">"datasource"</span> value=<span class="code-quote">"jdbc/DefaultDS"</span>/&gt;
        &lt;arg name=<span class="code-quote">"table.name"</span> value=<span class="code-quote">"OS_PROPERTYENTRY"</span>/&gt;
        &lt;arg name=<span class="code-quote">"col.globalKey"</span> value=<span class="code-quote">"GLOBAL_KEY"</span>/&gt;
        &lt;arg name=<span class="code-quote">"col.itemKey"</span> value=<span class="code-quote">"ITEM_KEY"</span>/&gt;
        &lt;arg name=<span class="code-quote">"col.itemType"</span> value=<span class="code-quote">"ITEM_TYPE"</span>/&gt;
        &lt;arg name=<span class="code-quote">"col.string"</span> value=<span class="code-quote">"STRING_VALUE"</span>/&gt;
        &lt;arg name=<span class="code-quote">"col.date"</span> value=<span class="code-quote">"DATE_VALUE"</span>/&gt;
        &lt;arg name=<span class="code-quote">"col.data"</span> value=<span class="code-quote">"DATA_VALUE"</span>/&gt;
        &lt;arg name=<span class="code-quote">"col.<span class="code-object">float</span>"</span> value=<span class="code-quote">"FLOAT_VALUE"</span>/&gt;
        &lt;arg name=<span class="code-quote">"col.number"</span> value=<span class="code-quote">"NUMBER_VALUE"</span>/&gt;
    &lt;/propertyset&gt;
&lt;/propertysets&gt;</pre>
</div></div>

<p>A number of sample sql scripts to create the required tables are included in the OSWorkflow distribution in in the src/etc/deployment/jdbc directory.</p>

<p>If HypersonicSQL is used as the datasource, you can follow these steps:</p>

<ol>
	<li>Assume your hsql db is named <b>oswf</b> and created in directory <b>/db</b></li>
	<li>Use the hsql.sql script to create the tables, you can use <b>java -cp hsqldb.jar org.hsqldb.util.DatabaseManager</b> to startup the tool to execute the sript.</li>
	<li>Add the context config to <b>$TOMCAT_HOME/conf/server.xml</b></li>
</ol>


<div class="code"><div class="codeContent">
<pre class="code-java">&lt;Context path=<span class="code-quote">"/osworkflow"</span> 
         docBase=<span class="code-quote">"/jakarta-tomcat-4.1.27/webapps/osworkflow-2.8.0-example"</span>&gt;
          &lt;Resource name=<span class="code-quote">"jdbc/oswf"</span> type=<span class="code-quote">"javax.sql.DataSource"</span>/&gt;
          &lt;ResourceParams name=<span class="code-quote">"jdbc/DefaultDS"</span>&gt;
            &lt;parameter&gt;&lt;name&gt;username&lt;/name&gt;&lt;value&gt;sa&lt;/value&gt;&lt;/parameter&gt;
            &lt;parameter&gt;&lt;name&gt;password&lt;/name&gt;&lt;value&gt;&lt;/value&gt;&lt;/parameter&gt;
            &lt;parameter&gt;&lt;name&gt;driverClassName&lt;/name&gt;
              &lt;value&gt;org.hsqldb.jdbcDriver&lt;/value&gt;&lt;/parameter&gt;
            &lt;parameter&gt;&lt;name&gt;url&lt;/name&gt;
              &lt;value&gt;jdbc:hsqldb:/db/oswf&lt;/value&gt;&lt;/parameter&gt;
          &lt;/ResourceParams&gt;
&lt;/Context&gt;</pre>
</div></div>

<p>4. Add <b>WEB-INF/classes/propertyset.xml</b> as described above</p>

<p>5. Change the persistent setting of <em>WEB-INF/classes/osworkflow.xml</em>. This example below should be used for any database that does not support sequences (eg, HSQL)</p>

<div class="code"><div class="codeContent">
<pre class="code-java">&lt;persistence class=<span class="code-quote">"com.opensymphony.workflow.spi.jdbc.JDBCWorkflowStore"</span>&gt;
	&lt;!- For jdbc persistence, all are required. --&gt;
	&lt;property key=<span class="code-quote">"datasource"</span> value=<span class="code-quote">"jdbc/DefaultDS"</span>/&gt;
	&lt;property key=<span class="code-quote">"entry.sequence"</span> 
                       value=<span class="code-quote">"select count(*) + 1 from os_wfentry"</span>/&gt;
	&lt;property key=<span class="code-quote">"entry.table"</span> value=<span class="code-quote">"OS_WFENTRY"</span>/&gt;
	&lt;property key=<span class="code-quote">"entry.id"</span> value=<span class="code-quote">"ID"</span>/&gt;
	&lt;property key=<span class="code-quote">"entry.name"</span> value=<span class="code-quote">"NAME"</span>/&gt;
	&lt;property key=<span class="code-quote">"entry.state"</span> value=<span class="code-quote">"STATE"</span>/&gt;
	&lt;property key=<span class="code-quote">"step.sequence"</span> value="select sum(c1) from 
(select 1 tb, count(*) c1 from os_currentstep 
union select 2 tb, count(*) c1 from os_historystep)"/&gt;
	&lt;property key=<span class="code-quote">"history.table"</span> value=<span class="code-quote">"OS_HISTORYSTEP"</span>/&gt;
	&lt;property key=<span class="code-quote">"current.table"</span> value=<span class="code-quote">"OS_CURRENTSTEP"</span>/&gt;
	&lt;property key=<span class="code-quote">"historyPrev.table"</span> value=<span class="code-quote">"OS_HISTORYSTEP_PREV"</span>/&gt;
	&lt;property key=<span class="code-quote">"currentPrev.table"</span> value=<span class="code-quote">"OS_CURRENTSTEP_PREV"</span>/&gt;
	&lt;property key=<span class="code-quote">"step.id"</span> value=<span class="code-quote">"ID"</span>/&gt;
	&lt;property key=<span class="code-quote">"step.entryId"</span> value=<span class="code-quote">"ENTRY_ID"</span>/&gt;
	&lt;property key=<span class="code-quote">"step.stepId"</span> value=<span class="code-quote">"STEP_ID"</span>/&gt;
	&lt;property key=<span class="code-quote">"step.actionId"</span> value=<span class="code-quote">"ACTION_ID"</span>/&gt;
	&lt;property key=<span class="code-quote">"step.owner"</span> value=<span class="code-quote">"OWNER"</span>/&gt;
	&lt;property key=<span class="code-quote">"step.caller"</span> value=<span class="code-quote">"CALLER"</span>/&gt;
	&lt;property key=<span class="code-quote">"step.startDate"</span> value=<span class="code-quote">"START_DATE"</span>/&gt;
	&lt;property key=<span class="code-quote">"step.finishDate"</span> value=<span class="code-quote">"FINISH_DATE"</span>/&gt;
	&lt;property key=<span class="code-quote">"step.dueDate"</span> value=<span class="code-quote">"DUE_DATE"</span>/&gt;
	&lt;property key=<span class="code-quote">"step.status"</span> value=<span class="code-quote">"STATUS"</span>/&gt;
	&lt;property key=<span class="code-quote">"step.previousId"</span> value=<span class="code-quote">"PREVIOUS_ID"</span>/&gt;
&lt;/persistence&gt;</pre>
</div></div>

<p>Note that the exact query for step.sequence and entry.sequence might vary in order to use an appropriate sequencing native DB mechanism. </p>

<p>For example, in MSSQL the correct step.sequence value would be (assuming you're not using a database sequence):</p>

<div class="code"><div class="codeContent">
<pre class="code-xml">&lt;property key=<span class="code-quote">"step.sequence"</span> value="select sum(c1) + 1 from (select 1 as
tb, count(*) as c1 from os_currentstep union select 2 as tb, count(*) as c1
from os_historystep) as TabelaFinal" /&gt;</pre>
</div></div>

<p>For MYSQL, OSWorkflow provides a custom store that can be used. This schema uses a separate table to store ID values (the schema is listed in the mysql.sql file).</p>

<p>In addition to this, two other changes from a standard deployment must be specified. The first are the calls to access the ID sequences. There are specified in the store properties in osworkflow.xml. The elements to be added are:</p>
<div class="code"><div class="codeContent">
<pre class="code-xml">&lt;property key=<span class="code-quote">"step.sequence.increment"</span> 
    value=<span class="code-quote">"INSERT INTO OS_STEPIDS (ID) values (null)"</span>/&gt;
  &lt;property key=<span class="code-quote">"step.sequence.retrieve"</span> 
    value=<span class="code-quote">"SELECT max(ID) FROM OS_STEPIDS"</span>/&gt;
  &lt;property key=<span class="code-quote">"entry.sequence.increment"</span> 
    value=<span class="code-quote">"INSERT INTO OS_ENTRYIDS (ID) values (null)"</span>/&gt;
  &lt;property key=<span class="code-quote">"entry.sequence.retrieve"</span> 
    value=<span class="code-quote">"SELECT max(ID) FROM OS_ENTRYIDS"</span>/&gt;</pre>
</div></div>

<p>In the same file, the store factory specified should be <b>com.opensymphony.workflow.spi.jdbc.MySQLWorkflowStore</b></p>

<ul>
	<li>Back to <a href="1.3 Running the Example App.html" title="1.3 Running the Example App">1.3 Running the Example App</a></li>
	<li>Forward to <a href="1.5 Loading Workflow Definitions.html" title="1.5 Loading Workflow Definitions">1.5 Loading Workflow Definitions</a></li>
</ul>


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