Last active
January 18, 2024 13:16
-
-
Save lukas-schaetzle/f93eff3d961ac595d0e1ab4be5f34536 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 9.23.4 and MySQL | |
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 CONSTRAINT 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 | |
CREATE TEMPORARY TABLE flow_logs | |
SELECT | |
revisions.id AS revisions_id, | |
activity.id AS activity_id, | |
activity.timestamp, | |
ROW_NUMBER() OVER ( | |
PARTITION BY revisions.item ORDER BY activity.timestamp DESC | |
) AS `row_number` | |
FROM directus_revisions AS revisions | |
INNER JOIN directus_activity AS activity | |
ON activity.id = revisions.activity | |
WHERE action = 'run'; | |
CREATE TEMPORARY TABLE normal_revisions | |
SELECT | |
revisions.id AS revisions_id, | |
activity.id AS activity_id, | |
activity.timestamp, | |
ROW_NUMBER() OVER ( | |
PARTITION BY revisions.item, revisions.collection ORDER BY activity.timestamp DESC | |
) AS `row_number` | |
FROM directus_revisions AS revisions | |
INNER JOIN directus_activity AS activity | |
ON activity.id = revisions.activity | |
WHERE action != 'run'; | |
-- 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 | |
CREATE TEMPORARY TABLE activities | |
SELECT | |
id, | |
ROW_NUMBER() OVER (ORDER BY `timestamp` DESC) AS `row_number` | |
FROM directus_activity; | |
-- 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 CONSTRAINT 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