How to reset WorkflowGen runtime content?


#1

How to reinitialize WorkflowGen while keeping my webforms, web services and users? I would like the request count and the history of the past request reinitialize.

Here are 3 SQL queries that will remove all the existing requests and set back the counters to 0 while keeping all the process definitions and users.

You will also need to manually delete all the requests files data in \wfgen\App_Data\Files\DataSet\runtime as indicated below.

/* Delete all requests and actions */
DELETE FROM WFPROCESS_INST;

/*

  • Delete all requests and actions data
  • For all the data files stored in the file system, you will need to manually delete the WorkflowGen folder \wfgen\App_Data\Files\DataSet\runtime
    */
    DELETE FROM WFDATASET WHERE RUNTIME = ‘Y’;

/* Reset all processes relative id and request id */
UPDATE WFCOUNTER SET COUNTER_VALUE = 0 WHERE ID_COUNTER <> ‘WFPROCESS.ID_PROCESS’ AND ID_COUNTER <> ‘WFDATASET.ID_DATASET’;

If you want to remove all users and groups (except wfgen_admin account) you have to execute the following SQL Queries too:

/*

  • Delete all regular users information (not admin).
  • USERS_PREFS and USERS_VIEWS are preserved for admins.
  • Delegations (WFDELEGATE) are preserved for admins if the delegation are made between admin accounts
    */
    DELETE FROM USERS WHERE ID_USER NOT IN (SELECT ID_USER FROM USERS_PROFILE WHERE ID_PROFILE = 1);

/* Delete all groups that have no members */
DELETE FROM GROUPS WHERE ID_GROUP NOT IN (SELECT ID_GROUP FROM USERS_GROUPS, USERS WHERE USERS_GROUPS.ID_USER = USERS.ID_USER);

/* Delete all directories that have no users and groups */
DELETE FROM DIRECTORY WHERE ID_DIRECTORY NOT IN (SELECT ID_DIRECTORY FROM USERS) AND ID_DIRECTORY NOT IN (SELECT ID_DIRECTORY FROM GROUPS);

/*

Important notes:

  • After deleting most of the users, groups and directories, it’s possible that some participants have no associations at all.

  • Use this query to get a list of participants without associations
    SELECT DISTINCT ID_PARTICIPANT, NAME FROM WFPARTICIPANT WHERE ID_PARTICIPANT NOT IN (SELECT ID_PARTICIPANT FROM WFPARTICIPANT_MAPTO);

  • The following Transact-SQL block will create a new association for those participants with the first admin account (*** ONLY COMPATIBLE WITH MS SQL SERVER ***)
    */

DECLARE @ID_PARTICIPANT_TEMP int;
DECLARE PARTICIPANT_CURSOR CURSOR FOR SELECT DISTINCT ID_PARTICIPANT FROM WFPARTICIPANT WHERE ID_PARTICIPANT NOT IN (SELECT ID_PARTICIPANT FROM WFPARTICIPANT_MAPTO);
OPEN PARTICIPANT_CURSOR;
FETCH NEXT FROM PARTICIPANT_CURSOR INTO @ID_PARTICIPANT_TEMP;
WHILE @@FETCH_STATUS = 0
   BEGIN
      INSERT INTO WFPARTICIPANT_MAPTO (ID_PARTICIPANT, NUM_MAPTO, MAPTO, ID_USER, COORDINATOR) VALUES (@ID_PARTICIPANT_TEMP, 1, ‘USERS’, (SELECT MIN(ID_USER) FROM USERS), ‘N’);
      FETCH NEXT FROM PARTICIPANT_CURSOR;
   END;
CLOSE PARTICIPANT_CURSOR;
DEALLOCATE PARTICIPANT_CURSOR;