1 -- Evergreen DB patch 0663.schema.archive_circ_stat_cats.sql
3 -- Enables users to set copy and patron stat cats to be archivable
4 -- for the purposes of statistics even after the circs are aged.
8 -- check whether patch can be applied
9 SELECT evergreen.upgrade_deps_block_check('0663', :eg_version);
13 CREATE TABLE action.archive_actor_stat_cat (
14 id BIGSERIAL PRIMARY KEY,
16 stat_cat INT NOT NULL,
20 CREATE TABLE action.archive_asset_stat_cat (
21 id BIGSERIAL PRIMARY KEY,
23 stat_cat INT NOT NULL,
27 -- Add columns to existing tables
29 -- Archive Flag Columns
30 ALTER TABLE actor.stat_cat
31 ADD COLUMN checkout_archive BOOL NOT NULL DEFAULT FALSE;
32 ALTER TABLE asset.stat_cat
33 ADD COLUMN checkout_archive BOOL NOT NULL DEFAULT FALSE;
35 -- Circulation copy column
36 ALTER TABLE action.circulation
37 ADD COLUMN copy_location INT NULL REFERENCES asset.copy_location(id) DEFERRABLE INITIALLY DEFERRED;
39 -- Create trigger function to auto-fill the copy_location field
40 CREATE OR REPLACE FUNCTION action.fill_circ_copy_location () RETURNS TRIGGER AS $$
42 SELECT INTO NEW.copy_location location FROM asset.copy WHERE id = NEW.target_copy;
47 -- Create trigger function to auto-archive stat cat entries
48 CREATE OR REPLACE FUNCTION action.archive_stat_cats () RETURNS TRIGGER AS $$
50 INSERT INTO action.archive_actor_stat_cat(xact, stat_cat, value)
51 SELECT NEW.id, asceum.stat_cat, asceum.stat_cat_entry
52 FROM actor.stat_cat_entry_usr_map asceum
53 JOIN actor.stat_cat sc ON asceum.stat_cat = sc.id
54 WHERE NEW.usr = asceum.target_usr AND sc.checkout_archive;
55 INSERT INTO action.archive_asset_stat_cat(xact, stat_cat, value)
56 SELECT NEW.id, ascecm.stat_cat, asce.value
57 FROM asset.stat_cat_entry_copy_map ascecm
58 JOIN asset.stat_cat sc ON ascecm.stat_cat = sc.id
59 JOIN asset.stat_cat_entry asce ON ascecm.stat_cat_entry = asce.id
60 WHERE NEW.target_copy = ascecm.owning_copy AND sc.checkout_archive;
66 CREATE TRIGGER fill_circ_copy_location_tgr BEFORE INSERT ON action.circulation FOR EACH ROW EXECUTE PROCEDURE action.fill_circ_copy_location();
67 CREATE TRIGGER archive_stat_cats_tgr AFTER INSERT ON action.circulation FOR EACH ROW EXECUTE PROCEDURE action.archive_stat_cats();
69 -- Ensure all triggers are disabled for speedy updates!
70 ALTER TABLE action.circulation DISABLE TRIGGER ALL;
72 -- Update view to use circ's copy_location field instead of the copy's current copy_location field
73 CREATE OR REPLACE VIEW action.all_circulation AS
74 SELECT id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
75 copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
76 circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
77 stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
78 max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
79 max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ
80 FROM action.aged_circulation
82 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,
83 cp.call_number AS copy_call_number, circ.copy_location, cn.owning_lib AS copy_owning_lib, cp.circ_lib AS copy_circ_lib,
84 cn.record AS copy_bib_record, circ.xact_start, circ.xact_finish, circ.target_copy, circ.circ_lib, circ.circ_staff, circ.checkin_staff,
85 circ.checkin_lib, circ.renewal_remaining, circ.grace_period, circ.due_date, circ.stop_fines_time, circ.checkin_time, circ.create_time, circ.duration,
86 circ.fine_interval, circ.recurring_fine, circ.max_fine, circ.phone_renewal, circ.desk_renewal, circ.opac_renewal, circ.duration_rule,
87 circ.recurring_fine_rule, circ.max_fine_rule, circ.stop_fines, circ.workstation, circ.checkin_workstation, circ.checkin_scan_time,
89 FROM action.circulation circ
90 JOIN asset.copy cp ON (circ.target_copy = cp.id)
91 JOIN asset.call_number cn ON (cp.call_number = cn.id)
92 JOIN actor.usr p ON (circ.usr = p.id)
93 LEFT JOIN actor.usr_address a ON (p.mailing_address = a.id)
94 LEFT JOIN actor.usr_address b ON (p.billing_address = b.id);
96 -- Update action.circulation with real copy_location numbers instead of all NULL
97 DO $$BEGIN RAISE WARNING 'We are about to do an update on every row in action.circulation. This may take a while. %', timeofday(); END;$$;
98 UPDATE action.circulation circ SET copy_location = ac.location FROM asset.copy ac WHERE ac.id = circ.target_copy;
100 -- Set not null/default on new column, re-enable triggers
101 ALTER TABLE action.circulation
102 ALTER COLUMN copy_location SET NOT NULL,
103 ALTER COLUMN copy_location SET DEFAULT 1,