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

Remove SugarBPM Processes

John Boyes
12 days ago

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 12 days ago

    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
Reply
  • 0 12 days ago

    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
Children