3 CREATE TABLE action.archive_actor_stat_cat (
4 id BIGSERIAL PRIMARY KEY,
10 CREATE TABLE action.archive_asset_stat_cat (
11 id BIGSERIAL PRIMARY KEY,
13 stat_cat INT NOT NULL,
17 -- Add columns to existing tables
19 -- Archive Flag Columns
20 ALTER TABLE actor.stat_cat
21 ADD COLUMN checkout_archive BOOL NOT NULL DEFAULT FALSE;
22 ALTER TABLE asset.stat_cat
23 ADD COLUMN checkout_archive BOOL NOT NULL DEFAULT FALSE;
25 -- Circulation copy column
26 ALTER TABLE action.circulation
27 ADD COLUMN copy_location INT NOT NULL DEFAULT 1 REFERENCES asset.copy_location(id) DEFERRABLE INITIALLY DEFERRED;
29 -- Update action.circulation with real copy_location numbers instead of all "Stacks"
30 UPDATE action.circulation circ SET copy_location = ac.location FROM asset.copy ac WHERE ac.id = circ.target_copy;
32 -- Create trigger function to auto-fill the copy_location field
33 CREATE OR REPLACE FUNCTION action.fill_circ_copy_location () RETURNS TRIGGER AS $$
35 SELECT INTO NEW.copy_location location FROM asset.copy WHERE id = NEW.target_copy;
40 -- Create trigger function to auto-archive stat cat entries
41 CREATE OR REPLACE FUNCTION action.archive_stat_cats () RETURNS TRIGGER AS $$
43 INSERT INTO action.archive_actor_stat_cat(xact, stat_cat, value)
44 SELECT NEW.id, asceum.stat_cat, asceum.stat_cat_entry
45 FROM actor.stat_cat_entry_usr_map asceum
46 JOIN actor.stat_cat sc ON asceum.stat_cat = sc.id
47 WHERE NEW.usr = asceum.target_usr AND sc.checkout_archive;
48 INSERT INTO action.archive_asset_stat_cat(xact, stat_cat, value)
49 SELECT NEW.id, ascecm.stat_cat, asce.value
50 FROM asset.stat_cat_entry_copy_map ascecm
51 JOIN asset.stat_cat sc ON ascecm.stat_cat = sc.id
52 JOIN asset.stat_cat_entry asce ON ascecm.stat_cat_entry = asce.id
53 WHERE NEW.target_copy = ascecm.owning_copy AND sc.checkout_archive;
59 CREATE TRIGGER fill_circ_copy_location_tgr BEFORE INSERT ON action.circulation FOR EACH ROW EXECUTE PROCEDURE action.fill_circ_copy_location();
60 CREATE TRIGGER archive_stat_cats_tgr AFTER INSERT ON action.circulation FOR EACH ROW EXECUTE PROCEDURE action.archive_stat_cats();