age-circs.sql: a script to age circs/anonymize patron circ history
authorJeff Davis <jdavis@sitka.bclibraries.ca>
Wed, 27 Aug 2014 22:22:09 +0000 (15:22 -0700)
committerJeff Davis <jdavis@sitka.bclibraries.ca>
Wed, 27 Aug 2014 22:22:09 +0000 (15:22 -0700)
Signed-off-by: Jeff Davis <jdavis@sitka.bclibraries.ca>
maintenance/age-circs.sql [new file with mode: 0644]

diff --git a/maintenance/age-circs.sql b/maintenance/age-circs.sql
new file mode 100644 (file)
index 0000000..5fb7f53
--- /dev/null
@@ -0,0 +1,169 @@
+/*
+ * age-circs.sql - a script to age/anonymize patrons' circ history.
+ *
+ * Copyright (C) 2008 Equinox Software, Inc.
+ * Copyright (C) 2014 BC Libraries Cooperative
+ * Author: Jeff Davis <jdavis@sitka.bclibraries.ca>
+ *
+ * This program is free software; you can redistribute it and/or
+ * modify it under the terms of the GNU General Public License
+ * as published by the Free Software Foundation; either version 2
+ * of the License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+ * GNU General Public License for more details.
+ */
+
+BEGIN;
+
+\echo 'exception matrix table'
+CREATE TABLE sitka.age_circ_exception_matrix (
+  id SERIAL NOT NULL,
+  active BOOLEAN NOT NULL DEFAULT TRUE,
+  org_unit INT REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
+  grp INT REFERENCES permission.grp_tree (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
+);
+
+\echo 'function for finding exceptions'
+CREATE OR REPLACE FUNCTION sitka.has_age_circ_exception(circ_id BIGINT) RETURNS BOOL AS $$
+DECLARE
+  potential_aged_circ  action.circulation%ROWTYPE;
+  matching_exceptions  INT;
+BEGIN
+  matching_exceptions := 0;
+  -- TODO: should we match exceptions against the entire circ chain?
+  SELECT * INTO potential_aged_circ FROM action.circulation WHERE id = circ_id;
+  SELECT count(*) INTO matching_exceptions
+    FROM sitka.age_circ_exception_matrix m
+      LEFT JOIN permission.grp_ancestors_distance( potential_aged_circ.usr_profile ) upgad ON m.grp = upgad.id
+      LEFT JOIN actor.org_unit_ancestors_distance( potential_aged_circ.circ_lib ) ctoua ON m.org_unit = ctoua.id
+    WHERE m.active
+      AND (m.grp IS NULL      OR upgad.id IS NOT NULL)
+      AND (m.org_unit IS NULL OR ctoua.id IS NOT NULL);
+  IF matching_exceptions > 0 THEN
+    RETURN TRUE;
+  END IF;
+  RETURN FALSE;
+END;
+$$ LANGUAGE plpgsql;
+
+\echo 'table for aged circs'
+CREATE TABLE sitka.aged_circs (
+  id BIGINT NOT NULL,
+  circ_chain_tail_xact_finish TIMESTAMPTZ,
+  delete_date DATE NOT NULL
+);
+
+\echo 'table for aged circ count'
+CREATE TABLE sitka.aged_circs_by_user (
+  usr BIGINT NOT NULL,
+  month INT NOT NULL,
+  year INT NOT NULL,
+  count INT NOT NULL DEFAULT 0,
+  UNIQUE (usr, month, year)
+);
+
+CREATE OR REPLACE FUNCTION sitka.age_circs(cutoff DATE, max_circ_count BIGINT) RETURNS INT AS $$
+DECLARE
+  today                DATE;
+  potential_aged_circ  action.circulation%ROWTYPE;
+  circ_chain_tail      action.circulation%ROWTYPE;
+  usr_keep_start       actor.usr_setting%ROWTYPE;
+  usr_keep_age         actor.usr_setting%ROWTYPE;
+  circ_cutoff          DATE;
+  has_exception        BOOL;
+  circs_aged           INT;
+BEGIN
+  today := now()::DATE;
+
+  -- TODO: more strictness here?
+  CREATE TEMPORARY TABLE circs_to_age AS
+    SELECT c.id FROM action.circulation c
+    JOIN money.materialized_billable_xact_summary m ON c.id = m.id
+    WHERE c.xact_finish IS NOT NULL AND c.xact_finish < cutoff
+    AND m.balance_owed = 0
+    ORDER BY c.xact_start
+    LIMIT max_circ_count;
+
+  -- for each circ that we might want to age...
+  FOR potential_aged_circ IN
+    SELECT b.* FROM circs_to_age a
+    JOIN action.circulation b ON a.id = b.id
+  LOOP
+
+    -- use the cutoff param as the default cutoff date for the current circ
+    circ_cutoff := cutoff;
+
+    -- renewals create a "chain" of circs; find the last circ in the current chain
+    SELECT * INTO circ_chain_tail FROM action.circ_chain(potential_aged_circ.id) ORDER BY xact_start DESC LIMIT 1;
+
+    -- user's history.circ.retention_start setting, if any, overrides default cutoff date
+    -- (we are ignoring history.circ.retention_age which does not appear to be in use)
+    usr_keep_start.value := NULL;
+    SELECT * INTO usr_keep_start FROM actor.usr_setting WHERE usr = potential_aged_circ.usr AND name = 'history.circ.retention_start';
+    IF usr_keep_start.value IS NOT NULL THEN
+      circ_cutoff := oils_json_to_text(usr_keep_start.value)::DATE;
+    END IF;
+
+    -- don't age the chain if the last circ is later than the cutoff
+    IF circ_chain_tail.xact_finish IS NULL OR circ_chain_tail.xact_finish > circ_cutoff THEN
+      DELETE FROM circs_to_age WHERE id IN (SELECT id FROM action.circ_chain(potential_aged_circ.id));
+      CONTINUE; -- proceed to next potential_aged_circ
+    END IF;
+
+    -- don't age the circ if it matches a defined exception
+    -- TODO: If there's a matching exception, we skip only the current circ; should we skip the entire chain?
+    has_exception := FALSE;
+    SELECT * INTO has_exception FROM sitka.has_age_circ_exception(potential_aged_circ.id);
+    IF has_exception IS TRUE THEN
+      DELETE FROM circs_to_age WHERE id = potential_aged_circ.id;
+      CONTINUE; -- proceed to next potential_aged_circ
+    END IF;
+
+  END LOOP;
+
+  -- log the aging of this circ
+  INSERT INTO sitka.aged_circs (id, delete_date)
+    SELECT id, today FROM circs_to_age;
+
+  -- update user circ counts
+  CREATE TEMPORARY TABLE usr_circ_totals AS
+    SELECT a.usr, (EXTRACT(month FROM a.xact_start)) AS month,
+      (EXTRACT(year FROM a.xact_start)) AS year, count(*) AS fresh_circs
+    FROM action.circulation a 
+    JOIN circs_to_age b ON a.id = b.id
+    GROUP BY 1,2,3;
+  UPDATE sitka.aged_circs_by_user a SET count = count + b.fresh_circs
+    FROM usr_circ_totals b WHERE a.usr = b.usr
+    AND a.month = b.month AND a.year = b.year;
+  INSERT INTO sitka.aged_circs_by_user (usr, month, year, count)
+    SELECT usr, month, year, fresh_circs FROM usr_circ_totals
+    WHERE (usr, month, year) NOT IN (SELECT usr, month, year FROM sitka.aged_circs_by_user);
+  DROP TABLE usr_circ_totals;
+
+  -- this will set off the age_circs trigger on action.circulation
+  DELETE FROM action.circulation WHERE id IN (SELECT id FROM circs_to_age);
+
+  UPDATE action.aged_circulation
+  SET usr_post_code = NULL, usr_birth_year = NULL
+  WHERE usr_post_code IS NOT NULL OR usr_birth_year IS NOT NULL;
+
+  SELECT count(*) INTO circs_aged FROM circs_to_age;
+  RETURN circs_aged;
+END;
+$$ LANGUAGE plpgsql;
+
+SAVEPOINT ready;
+
+---- To actually age circs, do something like the following:
+--SELECT count(*) FROM action.aged_circulation;
+--SELECT * FROM sitka.age_circs( (now() - '2 years'::INTERVAL)::DATE, NULL );
+--SELECT count(*) FROM action.aged_circulation;
+--
+--\echo 'SAVEPOINT done:'
+--SAVEPOINT done;
+--
+--COMMIT;
+