Open-ILS/src/sql/Pg/upgrade/0418.function.action.fix-purge-circ.sql

   1 -- Apply in an update script some fixes that were previously applied only
   2 -- to the base installation script 090.schema.action.sql.
   3
   4 -- Also fix a typo: INTEVAL -> INTERVAL
   5
   6 BEGIN;
   7
   8 INSERT INTO config.upgrade_log (version) VALUES ('0418'); -- Scott McKellar
   9
  10 CREATE OR REPLACE FUNCTION action.purge_circulations () RETURNS INT AS $func$
  11 DECLARE
  12     usr_keep_age    actor.usr_setting%ROWTYPE;
  13     usr_keep_start  actor.usr_setting%ROWTYPE;
  14     org_keep_age    INTERVAL;
  15     org_keep_count  INT;
  16
  17     keep_age        INTERVAL;
  18
  19     target_acp      RECORD;
  20     circ_chain_head action.circulation%ROWTYPE;
  21     circ_chain_tail action.circulation%ROWTYPE;
  22
  23     purge_position  INT;
  24     count_purged    INT;
  25 BEGIN
  26
  27     count_purged := 0;
  28
  29     SELECT value::INTERVAL INTO org_keep_age FROM config.global_flag WHERE name = 'history.circ.retention_age' AND enabled;
  30
  31     SELECT value::INT INTO org_keep_count FROM config.global_flag WHERE name = 'history.circ.retention_count' AND enabled;
  32     IF org_keep_count IS NULL THEN
  33         RETURN count_purged; -- Gimme a count to keep, or I keep them all, forever
  34     END IF;
  35
  36     -- First, find copies with more than keep_count non-renewal circs
  37     FOR target_acp IN
  38         SELECT  target_copy,
  39                 COUNT(*) AS total_real_circs
  40           FROM  action.circulation
  41           WHERE parent_circ IS NULL
  42                 AND xact_finish IS NOT NULL
  43           GROUP BY target_copy
  44           HAVING COUNT(*) > org_keep_count
  45     LOOP
  46         purge_position := 0;
  47         -- And, for those, select circs that are finished and older than keep_age
  48         FOR circ_chain_head IN
  49             SELECT  *
  50               FROM  action.circulation
  51               WHERE target_copy = target_acp.target_copy
  52                     AND parent_circ IS NULL
  53               ORDER BY xact_start
  54         LOOP
  55
  56             -- Stop once we've purged enough circs to hit org_keep_count
  57             EXIT WHEN target_acp.total_real_circs - purge_position <= org_keep_count;
  58
  59             SELECT * INTO circ_chain_tail FROM action.circ_chain(circ_chain_head.id) ORDER BY xact_start DESC LIMIT 1;
  60             EXIT WHEN circ_chain_tail.xact_finish IS NULL;
  61
  62             -- Now get the user settings, if any, to block purging if the user wants to keep more circs
  63             usr_keep_age.value := NULL;
  64             SELECT * INTO usr_keep_age FROM actor.usr_setting WHERE usr = circ_chain_head.usr AND name = 'history.circ.retention_age';
  65
  66             usr_keep_start.value := NULL;
  67             SELECT * INTO usr_keep_start FROM actor.usr_setting WHERE usr = circ_chain_head.usr AND name = 'history.circ.retention_start';
  68
  69             IF usr_keep_age.value IS NOT NULL AND usr_keep_start.value IS NOT NULL THEN
  70                 IF oils_json_to_text(usr_keep_age.value)::INTERVAL > AGE(NOW(), oils_json_to_text(usr_keep_start.value)::TIMESTAMPTZ) THEN
  71                     keep_age := AGE(NOW(), oils_json_to_text(usr_keep_start.value)::TIMESTAMPTZ);
  72                 ELSE
  73                     keep_age := oils_json_to_text(usr_keep_age.value)::INTERVAL;
  74                 END IF;
  75             ELSIF usr_keep_start.value IS NOT NULL THEN
  76                 keep_age := AGE(NOW(), oils_json_to_text(usr_keep_start.value)::TIMESTAMPTZ);
  77             ELSE
  78                 keep_age := COALESCE( org_keep_age::INTERVAL, '2000 years'::INTERVAL );
  79             END IF;
  80
  81             EXIT WHEN AGE(NOW(), circ_chain_tail.xact_finish) < keep_age;
  82
  83             -- We've passed the purging tests, purge the circ chain starting at the end
  84             DELETE FROM action.circulation WHERE id = circ_chain_tail.id;
  85             WHILE circ_chain_tail.parent_circ IS NOT NULL LOOP
  86                 SELECT * INTO circ_chain_tail FROM action.circulation WHERE id = circ_chain_tail.parent_circ;
  87                 DELETE FROM action.circulation WHERE id = circ_chain_tail.id;
  88             END LOOP;
  89
  90             count_purged := count_purged + 1;
  91             purge_position := purge_position + 1;
  92
  93         END LOOP;
  94     END LOOP;
  95 END;
  96 $func$ LANGUAGE PLPGSQL;
  97
  98
  99 COMMIT;