Open-ILS/src/sql/Pg/upgrade/0021.schema.triggers-for-deleting-circs.sql

   1 U    Open-ILS/src/sql/Pg/110.hold_matrix.sql
   2
   3 BEGIN;
   4
   5 INSERT INTO config.upgrade_log (version) VALUES ('0021');
   6
   7 -- Must drop a dependent view temporarily:
   8 DROP VIEW extend_reporter.full_circ_count;
   9
  10 -- Now drop and create the view we want to change:
  11 DROP VIEW action.all_circulation;
  12
  13 CREATE OR REPLACE VIEW action.all_circulation AS
  14     SELECT  id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
  15         copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
  16         circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, due_date,
  17         stop_fines_time, checkin_time, create_time, duration, fine_interval, recuring_fine,
  18         max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recuring_fine_rule,
  19         max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ
  20       FROM  action.aged_circulation
  21             UNION ALL
  22     SELECT  DISTINCT circ.id,COALESCE(a.post_code,b.post_code) AS usr_post_code, p.home_ou AS usr_home_ou, p.profile AS usr_profile, EXTRACT(YEAR FROM p.dob)::INT AS usr_birth_year,
  23         cp.call_number AS copy_call_number, cp.location AS copy_location, cn.owning_lib AS copy_owning_lib, cp.circ_lib AS copy_circ_lib,
  24         cn.record AS copy_bib_record, circ.xact_start, circ.xact_finish, circ.target_copy, circ.circ_lib, circ.circ_staff, circ.checkin_staff,
  25         circ.checkin_lib, circ.renewal_remaining, circ.due_date, circ.stop_fines_time, circ.checkin_time, circ.create_time, circ.duration,
  26         circ.fine_interval, circ.recuring_fine, circ.max_fine, circ.phone_renewal, circ.desk_renewal, circ.opac_renewal, circ.duration_rule,
  27         circ.recuring_fine_rule, circ.max_fine_rule, circ.stop_fines, circ.workstation, circ.checkin_workstation, circ.checkin_scan_time,
  28         circ.parent_circ
  29       FROM  action.circulation circ
  30         JOIN asset.copy cp ON (circ.target_copy = cp.id)
  31         JOIN asset.call_number cn ON (cp.call_number = cn.id)
  32         JOIN actor.usr p ON (circ.usr = p.id)
  33         LEFT JOIN actor.usr_address a ON (p.mailing_address = a.id)
  34         LEFT JOIN actor.usr_address b ON (p.billing_address = a.id);
  35
  36 -- Recreate the temporarily dropped view, with a revised view action.all_circulation:
  37
  38 CREATE OR REPLACE VIEW extend_reporter.full_circ_count AS
  39  SELECT cp.id, COALESCE(sum(c.circ_count), 0::bigint) + COALESCE(count(circ.id), 0::bigint) + COALESCE(count(acirc.id), 0::bigint) AS circ_count
  40    FROM asset."copy" cp
  41    LEFT JOIN extend_reporter.legacy_circ_count c USING (id)
  42    LEFT JOIN "action".circulation circ ON circ.target_copy = cp.id
  43    LEFT JOIN "action".aged_circulation acirc ON acirc.target_copy = cp.id
  44   GROUP BY cp.id;
  45
  46 -- Change the pre-delete trigger to copy the new columns
  47
  48 CREATE OR REPLACE FUNCTION action.age_circ_on_delete () RETURNS TRIGGER AS $$
  49 DECLARE
  50 found char := 'N';
  51 BEGIN
  52
  53     -- If there are any renewals for this circulation, don't archive or delete
  54     -- it yet.   We'll do so later, when we archive and delete the renewals.
  55
  56     SELECT 'Y' INTO found
  57     FROM action.circulation
  58     WHERE parent_circ = OLD.id
  59     LIMIT 1;
  60
  61     IF found = 'Y' THEN
  62         RETURN NULL;  -- don't delete
  63         END IF;
  64
  65     -- Archive a copy of the old row to action.aged_circulation
  66
  67     INSERT INTO action.aged_circulation
  68         (id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
  69         copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
  70         circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, due_date,
  71         stop_fines_time, checkin_time, create_time, duration, fine_interval, recuring_fine,
  72         max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recuring_fine_rule,
  73         max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ)
  74       SELECT
  75         id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
  76         copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
  77         circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, due_date,
  78         stop_fines_time, checkin_time, create_time, duration, fine_interval, recuring_fine,
  79         max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recuring_fine_rule,
  80         max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ
  81         FROM action.all_circulation WHERE id = OLD.id;
  82
  83     RETURN OLD;
  84 END;
  85 $$ LANGUAGE 'plpgsql';
  86
  87 -- New post-delete trigger to propagate deletions to parent(s)
  88
  89 CREATE OR REPLACE FUNCTION action.age_parent_circ_on_delete () RETURNS TRIGGER AS $$
  90 BEGIN
  91
  92     -- Having deleted a renewal, we can delete the original circulation (or a previous
  93     -- renewal, if that's what parent_circ is pointing to).  That deletion will trigger
  94     -- deletion of any prior parents, etc. recursively.
  95
  96     IF OLD.parent_circ IS NOT NULL THEN
  97         DELETE FROM action.circulation
  98         WHERE id = OLD.parent_circ;
  99     END IF;
 100
 101     RETURN OLD;
 102 END;
 103 $$ LANGUAGE 'plpgsql';
 104
 105 CREATE TRIGGER age_parent_circ AFTER DELETE ON action.circulation
 106 FOR EACH ROW EXECUTE PROCEDURE action.age_parent_circ_on_delete ();
 107
 108 COMMIT;