-
-
Save juherr/0ae72e2568f00833780ad1ea277b47a0 to your computer and use it in GitHub Desktop.
Directus (MySQL): Remove old activities, revisions, flow logs, notifications
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
DISCLAIMER: Use at your own risk! | |
Tested with Directus 10.8.0 and MySQL 5.6 | |
This will delete all flow execution logs, revisions and acitivites which are older | |
than the specified MinTimestamp and are not in the top <specified MinItems> latest | |
items. Activities related to saved revisions / flow logs are also saved even when | |
they normally would have been deleted. | |
Archived notifiactions are deleted when the creation of the notifaction is older | |
than the specified MinTimestamp. | |
Feel free to adjust the 4 variables at the start to your liking. | |
*/ | |
START TRANSACTION; | |
SET @MinFlowLogs = 10; | |
SET @MinRevisions = 10; | |
SET @MinActivities = 300; | |
SET @MinTimestamp = DATE_ADD(CURRENT_DATE(), INTERVAL -2 WEEK); | |
-- Change revisions 'parent' constraint | |
ALTER TABLE directus_revisions | |
DROP FOREIGN KEY directus_revisions_parent_foreign; | |
ALTER TABLE directus_revisions | |
ADD CONSTRAINT directus_revisions_parent_foreign | |
FOREIGN KEY (parent) REFERENCES directus_revisions (id) | |
ON DELETE SET NULL; | |
-- Preselection for revisions | |
SET @row_number = 0; | |
CREATE TEMPORARY TABLE flow_logs | |
SELECT | |
revisions.id AS revisions_id, | |
activity.id AS activity_id, | |
activity.timestamp, | |
(@row_number:=@row_number + 1) AS `row_number` | |
FROM directus_revisions AS revisions | |
INNER JOIN directus_activity AS activity | |
ON activity.id = revisions.activity | |
, (SELECT @row_number:=0) AS temp | |
WHERE action = 'run' | |
ORDER BY activity.timestamp DESC; | |
SET @row_number = 0; | |
CREATE TEMPORARY TABLE normal_revisions | |
SELECT | |
revisions.id AS revisions_id, | |
activity.id AS activity_id, | |
activity.timestamp, | |
(@row_number:=@row_number + 1) AS `row_number` | |
FROM directus_revisions AS revisions | |
INNER JOIN directus_activity AS activity | |
ON activity.id = revisions.activity | |
, (SELECT @row_number:=0) AS temp | |
WHERE action != 'run' | |
ORDER BY activity.timestamp DESC; | |
-- Delete old flow logs | |
DELETE revisions, flow_logs | |
FROM flow_logs | |
INNER JOIN directus_revisions AS revisions | |
ON revisions.id = flow_logs.revisions_id | |
WHERE flow_logs.timestamp < @MinTimestamp | |
AND flow_logs.row_number > @MinFlowLogs; | |
-- Delete old normal (no flow logs) revisions | |
DELETE revisions, normal_revisions | |
FROM normal_revisions | |
INNER JOIN directus_revisions AS revisions | |
ON revisions.id = normal_revisions.revisions_id | |
WHERE normal_revisions.timestamp < @MinTimestamp | |
AND normal_revisions.row_number > @MinRevisions; | |
-- Count activities | |
SET @row_number = 0; | |
CREATE TEMPORARY TABLE activities | |
SELECT | |
id, | |
(@row_number:=@row_number + 1) AS `row_number` | |
FROM directus_activity | |
ORDER BY `timestamp` DESC; | |
-- Delete old activities | |
DELETE directus_activity | |
FROM directus_activity | |
INNER JOIN activities | |
ON directus_activity.id = activities.id | |
WHERE `timestamp` < @MinTimestamp | |
AND `row_number` > @MinActivities | |
AND directus_activity.id NOT IN ( | |
SELECT activity_id FROM ( | |
SELECT activity_id FROM normal_revisions | |
UNION | |
SELECT activity_id FROM flow_logs | |
) AS saved_activities | |
); | |
-- Delete old archived notifications | |
DELETE FROM directus_notifications | |
WHERE `timestamp` < @MinTimestamp | |
AND `status` = 'archived'; | |
-- Re-add old parent constraint | |
ALTER TABLE directus_revisions | |
DROP FOREIGN KEY directus_revisions_parent_foreign; | |
ALTER TABLE directus_revisions | |
ADD CONSTRAINT directus_revisions_parent_foreign | |
FOREIGN KEY (parent) REFERENCES directus_revisions (id) | |
ON UPDATE RESTRICT | |
ON DELETE RESTRICT; | |
COMMIT; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment