Open-ILS/src/sql/Pg/upgrade/XXXX.schema.decouple_co_history.sql

   1
   2 BEGIN;
   3
   4 -- TODO process to delete history items once the age threshold
   5 -- history.circ.retention_age is reached?
   6
   7 -- SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version);
   8
   9 CREATE TABLE action.usr_circ_history (
  10     id           BIGSERIAL PRIMARY KEY,
  11     usr          INTEGER NOT NULL REFERENCES actor.usr(id)
  12                  DEFERRABLE INITIALLY DEFERRED,
  13     xact_start   TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
  14     target_copy  BIGINT NOT NULL REFERENCES asset.copy(id)
  15                  DEFERRABLE INITIALLY DEFERRED,
  16     due_date     TIMESTAMP WITH TIME ZONE NOT NULL,
  17     checkin_time TIMESTAMP WITH TIME ZONE,
  18     source_circ  BIGINT REFERENCES action.circulation(id)
  19                  ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED
  20 );
  21
  22 CREATE OR REPLACE FUNCTION action.maintain_usr_circ_history()
  23     RETURNS TRIGGER AS $FUNK$
  24 DECLARE
  25     cur_circ  BIGINT;
  26     first_circ BIGINT;
  27 BEGIN
  28
  29     -- Any retention value signifies history is enabled.
  30     -- This assumes that clearing these values via external
  31     -- process deletes the action.usr_circ_history rows.
  32     -- TODO: replace these settings w/ a single bool setting?
  33     PERFORM 1 FROM actor.usr_setting
  34         WHERE usr = NEW.usr AND value IS NOT NULL AND name IN (
  35             'history.circ.retention_age',
  36             'history.circ.retention_start'
  37         );
  38
  39     IF NOT FOUND THEN
  40         RETURN NEW;
  41     END IF;
  42
  43     IF TG_OP = 'INSERT' AND NEW.parent_circ IS NULL THEN
  44         -- Starting a new circulation.  Insert the history row.
  45         INSERT INTO action.usr_circ_history
  46             (usr, xact_start, target_copy, due_date, source_circ)
  47         VALUES (
  48             NEW.usr,
  49             NEW.xact_start,
  50             NEW.target_copy,
  51             NEW.due_date,
  52             NEW.id
  53         );
  54
  55         RETURN NEW;
  56     END IF;
  57
  58     -- find the first and last circs in the circ chain
  59     -- for the currently modified circ.
  60     FOR cur_circ IN SELECT id FROM action.circ_chain(NEW.id) LOOP
  61         IF first_circ IS NULL THEN
  62             first_circ := cur_circ;
  63             CONTINUE;
  64         END IF;
  65         -- Allow the loop to continue so that at as the loop
  66         -- completes cur_circ points to the final circulation.
  67     END LOOP;
  68
  69     IF NEW.id <> cur_circ THEN
  70         -- Modifying an intermediate circ.  Ignore it.
  71         RETURN NEW;
  72     END IF;
  73
  74     -- Update the due_date/checkin_time on the history row if the current
  75     -- circ is the last circ in the chain and an update is warranted.
  76
  77     UPDATE action.usr_circ_history
  78         SET
  79             due_date = NEW.due_date,
  80             checkin_time = NEW.checkin_time
  81         WHERE
  82             source_circ = first_circ
  83             AND (
  84                 due_date <> NEW.due_date OR (
  85                     (checkin_time IS NULL AND NEW.checkin_time IS NOT NULL) OR
  86                     (checkin_time IS NOT NULL AND NEW.checkin_time IS NULL) OR
  87                     (checkin_time <> NEW.checkin_time)
  88                 )
  89             );
  90     RETURN NEW;
  91 END;
  92 $FUNK$ LANGUAGE PLPGSQL;
  93
  94 CREATE TRIGGER maintain_usr_circ_history_tgr
  95     AFTER INSERT OR UPDATE ON action.circulation
  96     FOR EACH ROW EXECUTE PROCEDURE action.maintain_usr_circ_history();
  97
  98 UPDATE action_trigger.hook
  99     SET core_type = 'auch'
 100     WHERE key ~ '^circ.format.history.';
 101
 102 UPDATE action_trigger.event_definition SET template =
 103 $$
 104 [%- USE date -%]
 105 [%- SET user = target.0.usr -%]
 106 To: [%- params.recipient_email || user.email %]
 107 From: [%- params.sender_email || default_sender %]
 108 Subject: Circulation History
 109
 110     [% FOR circ IN target %]
 111             [% helpers.get_copy_bib_basics(circ.target_copy.id).title %]
 112             Barcode: [% circ.target_copy.barcode %]
 113             Checked Out: [% date.format(helpers.format_date(circ.xact_start), '%Y-%m-%d') %]
 114             Due Date: [% date.format(helpers.format_date(circ.due_date), '%Y-%m-%d') %]
 115             Returned: [%
 116                 date.format(
 117                     helpers.format_date(circ.checkin_time), '%Y-%m-%d')
 118                     IF circ.checkin_time;
 119             %]
 120     [% END %]
 121 $$
 122 WHERE id = 25 AND template =
 123 $$
 124 [%- USE date -%]
 125 [%- SET user = target.0.usr -%]
 126 To: [%- params.recipient_email || user.email %]
 127 From: [%- params.sender_email || default_sender %]
 128 Subject: Circulation History
 129
 130     [% FOR circ IN target %]
 131             [% helpers.get_copy_bib_basics(circ.target_copy.id).title %]
 132             Barcode: [% circ.target_copy.barcode %]
 133             Checked Out: [% date.format(helpers.format_date(circ.xact_start), '%Y-%m-%d') %]
 134             Due Date: [% date.format(helpers.format_date(circ.due_date), '%Y-%m-%d') %]
 135             Returned: [% date.format(helpers.format_date(circ.checkin_time), '%Y-%m-%d') %]
 136     [% END %]
 137 $$;
 138
 139 -- avoid TT undef date errors
 140 UPDATE action_trigger.event_definition SET template =
 141 $$
 142 [%- USE date -%]
 143 <div>
 144     <style> li { padding: 8px; margin 5px; }</style>
 145     <div>[% date.format %]</div>
 146     <br/>
 147
 148     [% user.family_name %], [% user.first_given_name %]
 149     <ol>
 150     [% FOR circ IN target %]
 151         <li>
 152             <div>[% helpers.get_copy_bib_basics(circ.target_copy.id).title %]</div>
 153             <div>Barcode: [% circ.target_copy.barcode %]</div>
 154             <div>Checked Out: [% date.format(helpers.format_date(circ.xact_start), '%Y-%m-%d') %]</div>
 155             <div>Due Date: [% date.format(helpers.format_date(circ.due_date), '%Y-%m-%d') %]</div>
 156             <div>Returned: [%
 157                 date.format(
 158                     helpers.format_date(circ.checkin_time), '%Y-%m-%d')
 159                     IF circ.checkin_time; -%]
 160             </div>
 161         </li>
 162     [% END %]
 163     </ol>
 164 </div>
 165 $$
 166 WHERE id = 26 AND template = -- only replace template if it matches stock
 167 $$
 168 [%- USE date -%]
 169 <div>
 170     <style> li { padding: 8px; margin 5px; }</style>
 171     <div>[% date.format %]</div>
 172     <br/>
 173
 174     [% user.family_name %], [% user.first_given_name %]
 175     <ol>
 176     [% FOR circ IN target %]
 177         <li>
 178             <div>[% helpers.get_copy_bib_basics(circ.target_copy.id).title %]</div>
 179             <div>Barcode: [% circ.target_copy.barcode %]</div>
 180             <div>Checked Out: [% date.format(helpers.format_date(circ.xact_start), '%Y-%m-%d') %]</div>
 181             <div>Due Date: [% date.format(helpers.format_date(circ.due_date), '%Y-%m-%d') %]</div>
 182             <div>Returned: [% date.format(helpers.format_date(circ.checkin_time), '%Y-%m-%d') %]</div>
 183         </li>
 184     [% END %]
 185     </ol>
 186 </div>
 187 $$;
 188
 189 -- NOTE: ^-- stock CSV template does not include checkin_time, so
 190 -- no modifications are required.
 191
 192 -- Create circ history rows for existing circ history data.
 193 DO $FUNK$
 194 DECLARE
 195     cur_usr   INTEGER;
 196     cur_circ  action.circulation%ROWTYPE;
 197     last_circ action.circulation%ROWTYPE;
 198     counter   INTEGER DEFAULT 1;
 199 BEGIN
 200
 201     RAISE NOTICE
 202         'Migrating circ history for % users.  This might take a while...',
 203         (SELECT COUNT(DISTINCT(au.id)) FROM actor.usr au
 204             JOIN actor.usr_setting aus ON (aus.usr = au.id)
 205             WHERE NOT au.deleted AND
 206                 aus.name ~ '^history.circ.retention_');
 207
 208     FOR cur_usr IN
 209         SELECT DISTINCT(au.id)
 210             FROM actor.usr au
 211             JOIN actor.usr_setting aus ON (aus.usr = au.id)
 212             WHERE NOT au.deleted AND
 213                 aus.name ~ '^history.circ.retention_' LOOP
 214
 215         FOR cur_circ IN SELECT * FROM action.usr_visible_circs(cur_usr) LOOP
 216
 217             -- Find the last circ in the circ chain.
 218             SELECT INTO last_circ *
 219                 FROM action.circ_chain(cur_circ.id)
 220                 ORDER BY xact_start DESC LIMIT 1;
 221
 222             -- Create the history row.
 223             -- It's OK if last_circ = cur_circ
 224             INSERT INTO action.usr_circ_history
 225                 (usr, xact_start, target_copy,
 226                     due_date, checkin_time, source_circ)
 227             VALUES (
 228                 cur_circ.usr,
 229                 cur_circ.xact_start,
 230                 cur_circ.target_copy,
 231                 last_circ.due_date,
 232                 last_circ.checkin_time,
 233                 cur_circ.id
 234             );
 235
 236             -- useful for alleviating administrator anxiety.
 237             IF counter % 10000 = 0 THEN
 238                 RAISE NOTICE 'Migrated history for % total users', counter;
 239             END IF;
 240
 241             counter := counter + 1;
 242
 243         END LOOP;
 244     END LOOP;
 245
 246 END $FUNK$;
 247
 248 DROP FUNCTION IF EXISTS action.usr_visible_circs (INTEGER);
 249 DROP FUNCTION IF EXISTS action.usr_visible_circ_copies (INTEGER);
 250
 251 -- remove user retention age checks
 252 CREATE OR REPLACE FUNCTION action.purge_circulations () RETURNS INT AS $func$
 253 DECLARE
 254     org_keep_age    INTERVAL;
 255     org_use_last    BOOL = false;
 256     org_age_is_min  BOOL = false;
 257     org_keep_count  INT;
 258
 259     keep_age        INTERVAL;
 260
 261     target_acp      RECORD;
 262     circ_chain_head action.circulation%ROWTYPE;
 263     circ_chain_tail action.circulation%ROWTYPE;
 264
 265     count_purged    INT;
 266     num_incomplete  INT;
 267
 268     last_finished   TIMESTAMP WITH TIME ZONE;
 269 BEGIN
 270
 271     count_purged := 0;
 272
 273     SELECT value::INTERVAL INTO org_keep_age FROM config.global_flag WHERE name = 'history.circ.retention_age' AND enabled;
 274
 275     SELECT value::INT INTO org_keep_count FROM config.global_flag WHERE name = 'history.circ.retention_count' AND enabled;
 276     IF org_keep_count IS NULL THEN
 277         RETURN count_purged; -- Gimme a count to keep, or I keep them all, forever
 278     END IF;
 279
 280     SELECT enabled INTO org_use_last FROM config.global_flag WHERE name = 'history.circ.retention_uses_last_finished';
 281     SELECT enabled INTO org_age_is_min FROM config.global_flag WHERE name = 'history.circ.retention_age_is_min';
 282
 283     -- First, find copies with more than keep_count non-renewal circs
 284     FOR target_acp IN
 285         SELECT  target_copy,
 286                 COUNT(*) AS total_real_circs
 287           FROM  action.circulation
 288           WHERE parent_circ IS NULL
 289                 AND xact_finish IS NOT NULL
 290           GROUP BY target_copy
 291           HAVING COUNT(*) > org_keep_count
 292     LOOP
 293         -- And, for those, select circs that are finished and older than keep_age
 294         FOR circ_chain_head IN
 295             -- For reference, the subquery uses a window function to order the circs newest to oldest and number them
 296             -- The outer query then uses that information to skip the most recent set the library wants to keep
 297             -- End result is we don't care what order they come out in, as they are all potentials for deletion.
 298             SELECT ac.* FROM action.circulation ac JOIN (
 299               SELECT  rank() OVER (ORDER BY xact_start DESC), ac.id
 300                 FROM  action.circulation ac
 301                 WHERE ac.target_copy = target_acp.target_copy
 302                   AND ac.parent_circ IS NULL
 303                 ORDER BY ac.xact_start ) ranked USING (id)
 304                 WHERE ranked.rank > org_keep_count
 305         LOOP
 306
 307             SELECT * INTO circ_chain_tail FROM action.circ_chain(circ_chain_head.id) ORDER BY xact_start DESC LIMIT 1;
 308             SELECT COUNT(CASE WHEN xact_finish IS NULL THEN 1 ELSE NULL END), MAX(xact_finish) INTO num_incomplete, last_finished FROM action.circ_chain(circ_chain_head.id);
 309             CONTINUE WHEN circ_chain_tail.xact_finish IS NULL OR num_incomplete > 0;
 310
 311             IF NOT org_use_last THEN
 312                 last_finished := circ_chain_tail.xact_finish;
 313             END IF;
 314
 315             keep_age := COALESCE( org_keep_age, '2000 years'::INTERVAL );
 316
 317             IF org_age_is_min THEN
 318                 keep_age := GREATEST( keep_age, org_keep_age );
 319             END IF;
 320
 321             CONTINUE WHEN AGE(NOW(), last_finished) < keep_age;
 322
 323             -- We've passed the purging tests, purge the circ chain starting at the end
 324             -- A trigger should auto-purge the rest of the chain.
 325             DELETE FROM action.circulation WHERE id = circ_chain_tail.id;
 326
 327             count_purged := count_purged + 1;
 328
 329         END LOOP;
 330     END LOOP;
 331
 332     return count_purged;
 333 END;
 334 $func$ LANGUAGE PLPGSQL;
 335
 336
 337 COMMIT;
 338