From ea8b2ae34dee14d15f4958ced3dadbdc4ea8ebbe Mon Sep 17 00:00:00 2001 From: Bill Erickson Date: Fri, 15 Apr 2016 16:26:57 -0400 Subject: [PATCH] LP#1570909 User activity purge function Utility function for removing all activity entries by activity type, except for the most recent entry per user. This is primarily useful when cleaning up rows prior to setting the transient flag on an activity type to true. It allows for immediate cleanup of data (e.g. for patron privacy) and lets admins control when the data is deleted, which could be useful for huge activity tables. Signed-off-by: Bill Erickson Signed-off-by: Chris Sharp Signed-off-by: Mike Rylander --- Open-ILS/src/sql/Pg/002.schema.config.sql | 20 +++++++++++++++++ .../upgrade/XXXX.schema.usr_activity_transient.sql | 25 ++++++++++++++++++++++ 2 files changed, 45 insertions(+) diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index 6feebdd335..0bba11c55e 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -1011,6 +1011,26 @@ CREATE TABLE config.usr_activity_type ( CONSTRAINT one_of_wwh CHECK (COALESCE(ewho,ewhat,ehow) IS NOT NULL) ); +-- Remove all activity entries by activity type, +-- except the most recent entry per user. +CREATE OR REPLACE FUNCTION + actor.purge_usr_activity_by_type(act_type INTEGER) + RETURNS VOID AS $$ +DECLARE + cur_usr INTEGER; +BEGIN + FOR cur_usr IN SELECT DISTINCT(usr) + FROM actor.usr_activity WHERE etype = act_type LOOP + DELETE FROM actor.usr_activity WHERE id IN ( + SELECT id + FROM actor.usr_activity + WHERE usr = cur_usr AND etype = act_type + ORDER BY event_time DESC OFFSET 1 + ); + + END LOOP; +END $$ LANGUAGE PLPGSQL; + CREATE UNIQUE INDEX unique_wwh ON config.usr_activity_type (COALESCE(ewho,''), COALESCE (ewhat,''), COALESCE(ehow,'')); diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.usr_activity_transient.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.usr_activity_transient.sql index 02d4a8bc29..fb70a87141 100644 --- a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.usr_activity_transient.sql +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.usr_activity_transient.sql @@ -6,5 +6,30 @@ SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); ALTER TABLE config.usr_activity_type ALTER COLUMN transient SET DEFAULT TRUE; +-- Utility function for removing all activity entries by activity type, +-- except for the most recent entry per user. This is primarily useful +-- when cleaning up rows prior to setting the transient flag on an +-- activity type to true. It allows for immediate cleanup of data (e.g. +-- for patron privacy) and lets admins control when the data is deleted, +-- which could be useful for huge activity tables. + +CREATE OR REPLACE FUNCTION + actor.purge_usr_activity_by_type(act_type INTEGER) + RETURNS VOID AS $$ +DECLARE + cur_usr INTEGER; +BEGIN + FOR cur_usr IN SELECT DISTINCT(usr) + FROM actor.usr_activity WHERE etype = act_type LOOP + DELETE FROM actor.usr_activity WHERE id IN ( + SELECT id + FROM actor.usr_activity + WHERE usr = cur_usr AND etype = act_type + ORDER BY event_time DESC OFFSET 1 + ); + + END LOOP; +END $$ LANGUAGE PLPGSQL; + COMMIT; -- 2.11.0