Open-ILS/src/sql/Pg/upgrade/0281.schema.purge-circs-stored-procs.sql

   1 BEGIN;
   2
   3 INSERT INTO config.upgrade_log (version) VALUES ('0281');
   4
   5 -- We need this data to do anything interesting, so just add it in the schema upgrade script
   6 INSERT INTO config.global_flag (name,label,enabled)
   7     VALUES ('history.circ.retention_age',oils_i18n_gettext('history.circ.retention_age', 'Historical Circulation Retention Age', 'cgf', 'label'), TRUE);
   8 INSERT INTO config.global_flag (name,label,enabled)
   9     VALUES ('history.circ.retention_count',oils_i18n_gettext('history.circ.retention_count', 'Historical Circulations per Copy', 'cgf', 'label'), TRUE);
  10
  11 INSERT INTO config.usr_setting_type (name,opac_visible,label,description,datatype)
  12     VALUES ('history.circ.retention_age', TRUE, 'Historical Circulation Retention Age', 'Historical Circulation Retention Age', 'interval');
  13 INSERT INTO config.usr_setting_type (name,opac_visible,label,description,datatype)
  14     VALUES ('history.circ.retention_start', FALSE, 'Historical Circulation Retention Start Date', 'Historical Circulation Retention Start Date', 'date');
  15
  16
  17 -- upgrade is_json to allow non-ref JSON (strings, numbers, etc)
  18 CREATE OR REPLACE FUNCTION is_json( TEXT ) RETURNS BOOL AS $f$
  19     use JSON::XS;
  20     my $json = shift();
  21     eval { JSON::XS->new->allow_nonref->decode( $json ) };
  22     return $@ ? 0 : 1;
  23 $f$ LANGUAGE PLPERLU;
  24
  25 -- turn a JSON scalar into an SQL TEXT value
  26 CREATE OR REPLACE FUNCTION oils_json_to_text( TEXT ) RETURNS TEXT AS $f$
  27     use JSON::XS;
  28     my $json = shift();
  29     my $txt;
  30     eval { $txt = JSON::XS->new->allow_nonref->decode( $json ) };
  31     return undef if ($@);
  32     return $txt
  33 $f$ LANGUAGE PLPERLU;
  34
  35 -- Return the list of circ chain heads in xact_start order that the user has chosen to "retain"
  36 CREATE OR REPLACE FUNCTION action.usr_visible_circs (usr_id INT) RETURNS SETOF action.circulation AS $func$
  37 DECLARE
  38     c               action.circulation%ROWTYPE;
  39     view_age        INTERVAL;
  40     usr_view_age    actor.usr_setting%ROWTYPE;
  41     usr_view_start  actor.usr_setting%ROWTYPE;
  42 BEGIN
  43     SELECT * INTO usr_view_age FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.circ.retention_age';
  44     SELECT * INTO usr_view_start FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.circ.retention_start_date';
  45
  46     IF usr_view_age.value IS NOT NULL AND usr_view_start.value IS NOT NULL THEN
  47         -- User opted in and supplied a retention age
  48         IF oils_json_to_string(usr_view_age.value)::INTERVAL > AGE(NOW(), oils_json_to_string(usr_view_start.value)::TIMESTAMPTZ) THEN
  49             view_age := AGE(NOW(), oils_json_to_string(usr_view_start.value)::TIMESTAMPTZ);
  50         ELSE
  51             view_age := oils_json_to_string(usr_view_age.value)::INTERVAL;
  52         END IF;
  53     ELSIF usr_view_start.value IS NOT NULL THEN
  54         -- User opted in
  55         view_age := AGE(NOW(), oils_json_to_string(usr_view_start.value)::TIMESTAMPTZ);
  56     ELSE
  57         -- User did not opt in
  58         RETURN;
  59     END IF;
  60
  61     FOR c IN
  62         SELECT  *
  63           FROM  action.circulation
  64           WHERE usr = usr_id
  65                 AND parent_circ IS NULL
  66                 AND xact_start < NOW() - view_age
  67           ORDER BY xact_start
  68     LOOP
  69         RETURN NEXT c;
  70     END LOOP;
  71
  72     RETURN;
  73 END;
  74 $func$ LANGUAGE PLPGSQL;
  75
  76 CREATE OR REPLACE FUNCTION action.purge_circulations () RETURNS INT AS $func$
  77 DECLARE
  78     usr_keep_age    actor.usr_setting%ROWTYPE;
  79     usr_keep_start  actor.usr_setting%ROWTYPE;
  80     org_keep_age    INTERVAL;
  81     org_keep_count  INT;
  82
  83     keep_age        INTERVAL;
  84
  85     target_acp      RECORD;
  86     circ_chain_head action.circulation%ROWTYPE;
  87     circ_chain_tail action.circulation%ROWTYPE;
  88
  89     purge_position  INT;
  90     count_purged    INT;
  91 BEGIN
  92
  93     count_purged := 0;
  94
  95     SELECT value::INTERVAL INTO org_keep_age FROM config.global_flag WHERE name = 'history.circ.retention_age' AND enabled;
  96
  97     SELECT value::INT INTO org_keep_count FROM config.global_flag WHERE name = 'history.circ.retention_count' AND enabled;
  98     IF org_keep_count IS NULL THEN
  99         RETURN count_purged; -- Gimme a count to keep, or I keep them all, forever
 100     END IF;
 101
 102     -- First, find copies with more than keep_count non-renewal circs
 103     FOR target_acp IN
 104         SELECT  target_copy,
 105                 COUNT(*) AS total_real_circs
 106           FROM  action.circulation
 107           WHERE parent_circ IS NULL
 108                 AND xact_finish IS NOT NULL
 109           GROUP BY target_copy
 110           HAVING COUNT(*) > org_keep_count
 111     LOOP
 112         purge_position := 0;
 113         -- And, for those, select circs that are finished and older than keep_age
 114         FOR circ_chain_head IN
 115             SELECT  *
 116               FROM  action.circulation
 117               WHERE target_copy = target_acp.target_copy
 118                     AND parent_circ IS NULL
 119               ORDER BY xact_start
 120         LOOP
 121
 122             -- Stop once we've purged enough circs to hit org_keep_count
 123             EXIT WHEN target_acp.total_real_circs - purge_position <= org_keep_count;
 124
 125             SELECT * INTO circ_chain_tail FROM action.circ_chain(circ_chain_head.id) ORDER BY xact_start DESC LIMIT 1;
 126             EXIT WHEN circ_chain_tail.xact_finish IS NULL;
 127
 128             -- Now get the user setings, if any, to block purging if the user wants to keep more circs
 129             usr_keep_age.value := NULL;
 130             SELECT * INTO usr_keep_age FROM actor.usr_setting WHERE usr = circ_chain_head.usr AND name = 'history.circ.retention_age';
 131
 132             usr_keep_start.value := NULL;
 133             SELECT * INTO usr_keep_start FROM actor.usr_setting WHERE usr = circ_chain_head.usr AND name = 'history.circ.retention_start_date';
 134
 135             IF usr_keep_age.value IS NOT NULL AND usr_keep_start.value IS NOT NULL THEN
 136                 IF oils_json_to_string(usr_keep_age.value)::INTERVAL > AGE(NOW(), oils_json_to_string(usr_keep_start.value)::TIMESTAMPTZ) THEN
 137                     keep_age := AGE(NOW(), oils_json_to_string(usr_keep_start.value)::TIMESTAMPTZ);
 138                 ELSE
 139                     keep_age := oils_json_to_string(usr_keep_age.value)::INTERVAL;
 140                 END IF;
 141             ELSIF usr_keep_start.value IS NOT NULL THEN
 142                 keep_age := AGE(NOW(), oils_json_to_string(usr_keep_start.value)::TIMESTAMPTZ);
 143             ELSE
 144                 keep_age := COALESCE( org_keep_age::INTERVAL, '2000 years'::INTEVAL );
 145             END IF;
 146
 147             EXIT WHEN AGE(NOW(), circ_chain_tail.xact_finish) < keep_age;
 148
 149             -- We've passed the purging tests, purge the circ chain starting at the end
 150             DELETE FROM action.circulation WHERE id = circ_chain_tail.id;
 151             WHILE circ_chain_tail.parent_circ IS NOT NULL LOOP
 152                 SELECT * INTO circ_chain_tail FROM action.circulation WHERE id = circ_chain_tail.parent_circ;
 153                 DELETE FROM action.circulation WHERE id = circ_chain_tail.id;
 154             END LOOP;
 155
 156             count_purged := count_purged + 1;
 157             purge_position := purge_position + 1;
 158
 159         END LOOP;
 160     END LOOP;
 161 END;
 162 $func$ LANGUAGE PLPGSQL;
 163
 164 COMMIT;
 165