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;

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.

Hi Alexandre Moulin,

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

Hi Alexandre Moulin,

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