Advise on creating Oracle Persistence

Question

Hi Everyone,

I've started to attempt an oracle persistence service as i've no choice on database. I've taken the schema scripts from the sql server persistence and mapped them into a set of oracle tables. Now its the stored procs.

For now i'm not wrrying about locking or anything like that but am looking purely at getting the data in.

The signature for the sql server sp is:

Create Procedure [dbo].[InsertInstanceState]

@uidInstanceID uniqueidentifier,

@state image,

@status int,

@unlocked int,

@blocked int,

@info ntext,

@ownerID uniqueidentifier = NULL,

@ownedUntil datetime = NULL,

@nextTimer datetime,

@result int output,

@currentOwnerID uniqueidentifier output

What i'm trying to sort out is how to get the various values from the Activity passed into the

SaveWorkflowInstanceState method i need to override in my custom service.

i'm currently:

storedProcedureExecutor["p_uidInstanceID "] = rootActivity.GetValue(Activity.ActivityContextGuidProperty);

storedProcedureExecutor["p_State"] = GetDefaultSerializedForm(rootActivity);

storedProcedureExecutor["p_Status"] = GetWorkflowStatus(rootActivity);

storedProcedureExecutor["p_Blocked"] = GetIsBlocked(rootActivity);

storedProcedureExecutor["p_Info"] = GetSuspendOrTerminateInfo(rootActivity);.

 

What i was wondering is whether this is the right approach, i.e trying to replicate the sql server implementation. The only sample i can find at the moment is the File one which just dumps out a binary.. I'm trying to find values such as OwnedUntil, OwnerId etc...

Is there any good docs on the sql server one??

Regards...

 

Answer 1

it seems i needed a bit of config and some calculations. again if anyone can give me some pointers it'd be much appreciated. doesnt look like it going to be very trivial..

 

public sealed class OracleWorkflowPersistenceService : WorkflowPersistenceService

{

private TimeSpan ownershipDelta;

private Guid serviceInstanceId;

public Guid ServiceInstanceId

{

get { return serviceInstanceId; }

set { serviceInstanceId = value; }

}

public DateTime OwnershipTimeout

{

get

{

if (ownershipDelta == TimeSpan.MaxValue)

{

return DateTime.MaxValue;

}

return (DateTime.UtcNow + ownershipDelta);

}

}

private bool enableRetries;

public bool EnableRetries

{

get { return enableRetries; }

set { enableRetries = value; }

}

private TimeSpan loadingInterval;

public TimeSpan LoadingInterval

{

get { return loadingInterval; }

set { loadingInterval = value; }

}

public OracleWorkflowPersistenceService() : base()

{

serviceInstanceId = Guid.Empty;

//can Load From config

ownershipDelta = TimeSpan.MaxValue;

}

public OracleWorkflowPersistenceService(NameValueCollection parameters)

: base()

{

serviceInstanceId = Guid.Empty;

//can Load From config

ownershipDelta = TimeSpan.MaxValue;

}

 

///<summary>

///Saves the workflow instance state to a data store.

///</summary>

///

///<param name="rootActivity">The root activity of the workflow instance.</param>

///<param name="unlock">true if the workflow instance should not be locked; false if the workflow instance should be locked.</param>

protected override void SaveWorkflowInstanceState(Activity rootActivity, bool unlock)

{

GenericSPExecutor executor = new GenericSPExecutor("WORKFLOWFOUNDATION_PKG.InsertInstanceState");

executor["p_InstanceId"] = WorkflowEnvironment.WorkflowInstanceId;

//Not sure what this is used for yet..

Guid stateId = (Guid)rootActivity.GetValue(Activity.ActivityContextGuidProperty);

WorkflowStatus status = GetWorkflowStatus(rootActivity);

if ((status != WorkflowStatus.Completed) && (status != WorkflowStatus.Terminated))

{

executor["p_State"] = GetDefaultSerializedForm(rootActivity);

}

else

{

executor["p_State"] = new byte[0];

}

executor["p_Status"] = status;

executor["p_Unlocked"] = unlock;

executor["p_Blocked"] = GetIsBlocked(rootActivity);

executor["p_Info"] = GetSuspendOrTerminateInfo(rootActivity);

if (executor["p_Info"] == null)

{

executor["p_Info"] = "";

}

//ServiceInstanceId

executor["p_OwnerId"] = ServiceInstanceId;

//Config OwnerShip Timeout.

if (OwnershipTimeout == DateTime.MaxValue)

{

executor["p_OwnedUntil"] = OracleDate.MaxValue;

}

else

{

executor["p_OwnedUntil"] = OwnershipTimeout;

}

TimerEventSubscription subscription1 = ((TimerEventSubscriptionCollection)rootActivity.GetValue(TimerEventSubscriptionCollection.TimerCollectionProperty)).Peek();

executor["p_NextTimer"] = (subscription1 == null) ? DateTime.MaxValue : subscription1.ExpiresAt;

executor.ExecuteNonQuery();

CheckOwnershipResult(executor);

}

private void CheckOwnershipResult(GenericSPExecutor executor)

{

int result = Int32.Parse(executor["p_Result"].ToString());

if (result == -2)

{

Guid currentOwnerId = (Guid)executor["p_CurrentOwnerId"];

if (currentOwnerId == null)

throw new WorkflowOwnershipException(WorkflowEnvironment.WorkflowInstanceId);

}

}

Answer 2

Hi Mick,

Great project. Are you planning to share the resulting persistence with the community?

I'd recommend you start with the file persistence service as a working implementation. And reimplement the functions step by step until you have the result you want.

And let us know when you have some specific problems that we might be able to help with.

Regards,
Paul

Answer 3

Thanks Paul.

Definitely going to  share it. We've choosen WF and sharepoint going forward but have a large investment in oracle so this is quite a crucial area to get right.

Regards.

Answer 4

*** To the above comment: OH Please release this to the community!

Larry

Answer 5

Hi mickdelaney

I have the some problem of you. Have you solve this problem? If you do. Please give me a hand, My mail address:[e-mail]. Waiting the message from you. Thank you!

Answer 6

hi guys,

i had initially intended to release it start away but i've had some issues with the ip policy at work so i'm having to deal with that now. its working for now but has some issues that i'm hoping to iron out. hopefully i can rework it over the weekend to avoid these ip issues.

cheers.

Answer 7

If there is any part of this that could use some assistance/coding, please let me know. I have *some* time and a need for a Oracle WF provider as well.

Answer 8

Hi Larry,

I''m going to post it by tommorow night (hopefully). At the moment it's written using ent lib 2 wrappers i developed for my company so i need to remove that from it becuase of the ip issues. once thats sorted i'll post the sql ddl &dml & the .net  classes. as i said it's not 100% yet though there are a few bugs. i used reflector to examine ms's sql server impl to get the basis of it, but i think if a few of us get together on it we'll sort it out in a couple of days. i've been having debugger and designer issues too (which i posted about!!!) which is really slowing everything down at the moment so bare with me. 

Answer 9

hi,

i've created a rough oracle workflow persistence service...

its on my blog. let me stress though that its not been thoroughly tested and just a quick stab.. i got most of the code from reflecting on the sql server impl...

there's also a windows forms & state machine test harness...

http://micksdevblog.blogspot.com/

i'm hoping to get a few people to collaborate on this so we can finish it off...

cheers..

Answer 10

Hi Mick. I would like to see your Oracle persistence work, but the download file is in RAR. Do you have it in a common format like zip or cab?

Thanks,

Matt

Answer 11

This is too good mick...and this is what I've been looking for...even I have Oracle DB as a constraint...

Answer 12

i've actually found some bugs etc, so i need to update the code, when i do, i'll zip it instead. prob tommorow night. i'll update this when i do so.

(sorry for the delay btw, was away for a while).

cheers.

Answer 13

This is great. I am unable to download the files though. Can you please post it in the zip format ?

 

Thank you

RS

Answer 14

Hi

I am unable to get the source files for Oracle Persisstence service for Workflow. Can you please post the zip format ?

 

thank you!

RS

Answer 15

Hello world,

 

Has anybody made an oracle persistence service that they can share with the community?

 

 

Thanks,

 

Peter

Answer 16

 

hi Mick, first off thank u for this great initiative, I've wouldn't have known where to start otherwise!

 

I think I found some kind of "bug" in the oracle package.

My WFs didn't get loaded after the expiration of the timers so I did some debugging and

found out that p_ownerID  wasn't getting used in GetExpiredTimerIds.

So I believe this was what u intended to do:

 

Code Snippet

 

Procedure GetExpiredTimerIds
(
  p_ownerID in wf_InstanceState.ownerID%type := null,
  p_ownedUntil in wf_InstanceState.ownedUntil%type := null,
  p_now in date,
  p_out out RefCursor
)
as
begin
     
      open    p_out for    
      select  InstanceID
      from    wf_InstanceState
      where   nextTimer < p_now
      and     status != 1
      and     status != 3
      and     status != 2 -- not blocked and not completed and not terminated and not suspended
      and    
      (
        (unlocked = 1 and ownerID is null)
        or ownedUntil < sysdate
        or ownerID = p_ownerID -- if it's expired and I'm the owner then I should get it!?
      );
 
end GetExpiredTimerIds;

 

 

 

correct me if I'm wrong!

 

Thanks again,

 

kepar

Answer 17

hi guys,

we've created a codeplex project called WFTools (workflow tools). at present there is a working oracle persistence provider. It uses the ms oracle provider,  not odp.net (which has a bug around transactionscope which we're currently trying to escelate with oracle.

the long term desire is to add oracle providers for all other aspects of workflow, i.e. tracking etc... new contributers, testers etc would really help us to make this possible.

 

so please come have a play download, test and contribute...

 

http://www.codeplex.com/WFTools   

 

regards,

mick...

 

Answer 18

Hi kepar,

 

Looks like you're using a very old version of the original Oracle persistence service. I'd advise you to visit http://www.codeplex.com/WFTools which has the latest version.

 

v0.2 Alpha is the current release and works out of the box under XP/2003 with the MS Oracle Provider. There are some issues with the ODP.NET provider at present, we're escalating to Oracle

 

Cheers,

 

Dean Ward

Project Co-ordinator

WFTools