Forums - Thread Details
  • State Suggested Answer
  • Replies 8 replies
  • Answers 1 answer
  • Subscribers 321 subscribers
  • Views 8882 views
  • Users 0 members are here
Forum Actions
Related

Remove SugarBPM Processes

John Boyes

Does anyone have the SQL queries for removing SugarBPM process instances. I thought i had them but am unable to find them.

It would be nice if this was able to be done in the Data Archiver like  discussed: Data archiver: remove records older than x days

Parents
  • 0

    Here they are

    -- pmse_email_message
    DELETE em
    FROM pmse_email_message em
    INNER JOIN pmse_bpm_flow bf ON em.flow_id = bf.id
    INNER JOIN pmse_inbox i ON bf.cas_id = i.cas_id
    WHERE i.date_modified < '2023-01-01' AND i.cas_status <> 'IN PROGRESS';

    -- pmse_email_message
    DELETE em
    FROM pmse_email_message em
    INNER JOIN pmse_bpm_flow bf ON em.flow_id = bf.id
    INNER JOIN pmse_inbox i ON bf.cas_id = i.cas_id
    INNER JOIN pmse_bpm_process_definition pd ON i.pro_id = pd.id AND pd.pro_status = 'INACTIVE'
    WHERE i.date_modified < '2023-01-01';

    OPTIMIZE TABLE pmse_email_message;

    -- pmse_bpm_flow
    DELETE bf
    FROM pmse_bpm_flow bf
    INNER JOIN pmse_inbox i ON bf.cas_id = i.cas_id
    WHERE i.date_modified < '2023-01-01' AND i.cas_status <> 'IN PROGRESS';

    -- pmse_bpm_flow
    DELETE bf
    FROM pmse_bpm_flow bf
    INNER JOIN pmse_inbox i ON bf.cas_id = i.cas_id
    INNER JOIN pmse_bpm_process_definition pd ON i.pro_id = pd.id AND pd.pro_status = 'INACTIVE'
    WHERE i.date_modified < '2023-01-01';

    OPTIMIZE TABLE pmse_bpm_flow;

    -- pmse_bpm_thread
    DELETE bt
    FROM pmse_bpm_thread bt
    INNER JOIN pmse_inbox i ON bt.cas_id = i.cas_id
    WHERE i.date_modified < '2023-01-01' AND i.cas_status <> 'IN PROGRESS';

    -- pmse_bpm_thread
    DELETE bt
    FROM pmse_bpm_thread bt
    INNER JOIN pmse_inbox i ON bt.cas_id = i.cas_id
    INNER JOIN pmse_bpm_process_definition pd ON i.pro_id = pd.id AND pd.pro_status = 'INACTIVE'
    WHERE i.date_modified < '2023-01-01';

    OPTIMIZE TABLE pmse_bpm_thread;

    -- pmse_bpm_form_action
    DELETE bfa
    FROM pmse_bpm_form_action bfa
    INNER JOIN pmse_inbox i ON bfa.cas_id = i.cas_id
    WHERE i.date_modified < '2023-01-01' AND i.cas_status <> 'IN PROGRESS';

    -- pmse_bpm_form_action
    DELETE bfa
    FROM pmse_bpm_form_action bfa
    INNER JOIN pmse_inbox i ON bfa.cas_id = i.cas_id
    INNER JOIN pmse_bpm_process_definition pd ON i.pro_id = pd.id AND pd.pro_status = 'INACTIVE'
    WHERE i.date_modified < '2023-01-01';

    OPTIMIZE TABLE pmse_bpm_form_action;

    -- pmse_inbox
    DELETE i
    FROM pmse_inbox i
    WHERE i.date_modified < '2023-01-01' AND i.cas_status <> 'IN PROGRESS';

    -- pmse_inbox
    DELETE i
    FROM pmse_inbox i
    INNER JOIN pmse_bpm_process_definition pd ON i.pro_id = pd.id AND pd.pro_status = 'INACTIVE'
    WHERE i.date_modified < '2023-01-01';

    OPTIMIZE TABLE pmse_inbox;

    André Lopes
    Lampada Global
    Skype: andre.lampada
  • 0 in reply to André Lopes

    Hi,

    be carefull because with these queries all BPM that have an entry condition with "on first update" because it relies on "pmse_bpm_flow" content to know if the BPM has been trigger once or not.

Reply Children
  • 0 in reply to Alexandre Moulin

    Hi ,

    This is good advice.

    While I usually recommend not using the "on first update" precisely for this reason, here are alternative queries that account for that concern:

    #1 - Creates Temp table used to store the pmse_bpm_flow rows we want to keep.
CREATE TABLE pmse_bpm_flow_cleaned LIKE pmse_bpm_flow; INSERT INTO pmse_bpm_flow_cleaned SELECT * FROM pmse_bpm_flow WHERE pmse_bpm_flow.cas_id NOT IN (SELECT cas_id from pmse_inbox WHERE cas_status in ('COMPLETED', 'TERMINATED') AND date_modified < DATE_SUB(NOW(), INTERVAL 30 DAY) ) OR pmse_bpm_flow.cas_index = 1; 
​
#2 Swap pmse_bpm_flows_cleaned and pmse_bpm_flow tables
RENAME TABLE pmse_bpm_flow TO pmse_bpm_flow_backup, pmse_bpm_flow_cleaned TO pmse_bpm_flow;
​
#3 Creates temp table used to store the pmse_inbox rows we want to keep.
CREATE TABLE pmse_inbox_cleaned LIKE pmse_inbox; INSERT INTO pmse_inbox_cleaned SELECT * FROM pmse_inbox WHERE pmse_inbox.cas_status NOT IN ('COMPLETED', 'TERMINATED') OR pmse_inbox.date_modified > DATE_SUB(NOW(), INTERVAL 30 DAY) OR pmse_inbox.cas_id NOT IN (SELECT cas_id FROM pmse_bpm_flow WHERE cas_index = '1'); 
​
#4 Swap pmse_inbox_cleaned and pmse_inbox tables.
RENAME TABLE pmse_inbox TO pmse_inbox_backup, pmse_inbox_cleaned TO pmse_inbox;
​
#5 Verify everything looks good after the pruning of Completed/Terminated flows older than 30 days, then remove the backup tables.
DROP TABLE IF EXISTS pmse_bpm_flow_backup, pmse_inbox_backup;

    These queries leave the first entry for each run in the pmse_bpm_flow table. For many, this can still prove to be a massive collection remaining in the table, but it accounts for the first update only start criteria where needed.

    Regards,
    Patrick McQueen
    Director, SugarCRM Support

  • 0 in reply to Alexandre Moulin

    -- pmse_bpm_flow
    DELETE bf
    FROM pmse_bpm_flow bf
    INNER JOIN pmse_inbox i ON bf.cas_id = i.cas_id
    WHERE i.date_modified < '2023-01-01' AND i.cas_status <> 'IN PROGRESS'

    AND bf.id not in* <put in exclusive sql by condition here>