From 85d063a283c7fea656f6cd9165fc1d1be6c19292 Mon Sep 17 00:00:00 2001 From: Bill Erickson Date: Tue, 19 Apr 2016 13:56:33 -0400 Subject: [PATCH] JBAS-1378 Purge user activity ; apply transience Remove all but the most recent actor.usr_activity row per etype and per user, except keep all of the lynda and hoopla SIP login events. Signed-off-by: Bill Erickson --- KCLS/sql/schema/deploy/purge-user-activity.sql | 36 ++++++++++++++++++++++++++ KCLS/sql/schema/revert/purge-user-activity.sql | 7 +++++ KCLS/sql/schema/sqitch.plan | 1 + KCLS/sql/schema/verify/purge-user-activity.sql | 7 +++++ 4 files changed, 51 insertions(+) create mode 100644 KCLS/sql/schema/deploy/purge-user-activity.sql create mode 100644 KCLS/sql/schema/revert/purge-user-activity.sql create mode 100644 KCLS/sql/schema/verify/purge-user-activity.sql diff --git a/KCLS/sql/schema/deploy/purge-user-activity.sql b/KCLS/sql/schema/deploy/purge-user-activity.sql new file mode 100644 index 0000000000..5bb71d23cb --- /dev/null +++ b/KCLS/sql/schema/deploy/purge-user-activity.sql @@ -0,0 +1,36 @@ +-- purge-user-activity + +BEGIN; + +SET STATEMENT_TIMEOUT = 0; + +CREATE TEMPORARY TABLE tmp_usr_activity ON COMMIT DROP AS + -- Most recent usr_activity row per usr per etype + WITH ranked_entry_per_type_and_user AS ( + SELECT + entry.*, + RANK() OVER ( + PARTITION BY entry.etype, entry.usr + ORDER BY event_time DESC + ) AS position + FROM actor.usr_activity entry + ) + SELECT ranked.id, ranked.usr, ranked.etype, ranked.event_time + FROM ranked_entry_per_type_and_user ranked + WHERE ranked.position = 1 + -- keep all events for lyndasip and hooplasip2 authz + OR ranked.etype IN (1005, 1006); + +TRUNCATE actor.usr_activity; + +INSERT INTO actor.usr_activity SELECT * FROM tmp_usr_activity; + +-- keep only the most recent even going forward with 2 +-- exceptions for lyndasip and hooplasip2. +UPDATE config.usr_activity_type + SET transient = TRUE WHERE id NOT IN (1005, 1006); + +COMMIT; + +ANALYZE actor.usr_activity; + diff --git a/KCLS/sql/schema/revert/purge-user-activity.sql b/KCLS/sql/schema/revert/purge-user-activity.sql new file mode 100644 index 0000000000..80d17a3eb6 --- /dev/null +++ b/KCLS/sql/schema/revert/purge-user-activity.sql @@ -0,0 +1,7 @@ +-- Revert kcls-evergreen:purge-user-activity from pg + +BEGIN; + +-- XXX Add DDLs here. + +COMMIT; diff --git a/KCLS/sql/schema/sqitch.plan b/KCLS/sql/schema/sqitch.plan index 3b45bcd200..cf3e77ddea 100644 --- a/KCLS/sql/schema/sqitch.plan +++ b/KCLS/sql/schema/sqitch.plan @@ -25,3 +25,4 @@ ingram-edi-mods [sip-activity-types] 2016-05-24T16:31:46Z Bill Erickson # Give each org unit its own mailing address drop-cc-cols [sip-activity-types] 2016-05-03T15:26:50Z Bill Erickson # Drop unneeded CC payment columns connexion-auth-imports [sip-activity-types] 2016-05-11T15:10:49Z Bill Erickson,,, # OCLC Connexion Authority Record Imports Data +purge-user-activity [sip-activity-types] 2016-04-29T17:07:46Z Bill Erickson # Clean up actor.usr_activity diff --git a/KCLS/sql/schema/verify/purge-user-activity.sql b/KCLS/sql/schema/verify/purge-user-activity.sql new file mode 100644 index 0000000000..4587b5bc0c --- /dev/null +++ b/KCLS/sql/schema/verify/purge-user-activity.sql @@ -0,0 +1,7 @@ +-- Verify kcls-evergreen:purge-user-activity on pg + +BEGIN; + +-- XXX Add verifications here. + +ROLLBACK; -- 2.11.0