From d7e56da5c96f53949022b0f3635183f66e5bb843 Mon Sep 17 00:00:00 2001 From: Bill Erickson Date: Thu, 20 Sep 2018 11:43:38 -0400 Subject: [PATCH] JBAS-1832 3.2 SQL rearrangements Move more of the updates into the data file so they can run after the main schema upgrade. Signed-off-by: Bill Erickson --- .../sql/schema/deploy/2.12-3.2-db-upgrade-data.sql | 82 ++++++++++++++++++++-- .../schema/deploy/2.12-3.2-db-upgrade-schema.sql | 16 ++++- 2 files changed, 92 insertions(+), 6 deletions(-) diff --git a/KCLS/sql/schema/deploy/2.12-3.2-db-upgrade-data.sql b/KCLS/sql/schema/deploy/2.12-3.2-db-upgrade-data.sql index a96cba71d3..0bac8e1df7 100644 --- a/KCLS/sql/schema/deploy/2.12-3.2-db-upgrade-data.sql +++ b/KCLS/sql/schema/deploy/2.12-3.2-db-upgrade-data.sql @@ -13,7 +13,13 @@ SET STATEMENT_TIMEOUT = 0; -- TODO: run all in transaction for testing -- remove transactions later. -BEGIN; +-- Remove trigger on biblio.record_entry +SELECT CLOCK_TIMESTAMP(), 'Rebuilding materialized simple record'; +SELECT reporter.disable_materialized_simple_record_trigger(); + +-- Rebuild reporter.materialized_simple_record +SELECT reporter.enable_materialized_simple_record_trigger(); + DROP TRIGGER IF EXISTS a_opac_vis_mat_view_tgr ON biblio.peer_bib_copy_map; DROP TRIGGER IF EXISTS a_opac_vis_mat_view_tgr ON biblio.record_entry; @@ -50,6 +56,56 @@ CREATE TRIGGER z_opac_vis_mat_view_tgr AFTER INSERT OR UPDATE CREATE TRIGGER z_opac_vis_mat_view_tgr AFTER INSERT OR UPDATE ON serial.unit FOR EACH ROW EXECUTE PROCEDURE asset.cache_copy_visibility(); +SELECT CLOCK_TIMESTAMP(), 'Updating billing timestamps'; + +ALTER TABLE money.billing + ADD COLUMN create_date TIMESTAMP WITH TIME ZONE, + ADD COLUMN period_start TIMESTAMP WITH TIME ZONE, + ADD COLUMN period_end TIMESTAMP WITH TIME ZONE; + +--Disable materialized update trigger +--It takes forever, and doesn't matter yet for what we are doing, as the +--view definition is unchanged (still using billing_ts) +ALTER TABLE money.billing DISABLE TRIGGER mat_summary_upd_tgr; + +--Limit to btype=1 / 'Overdue Materials' +--Update day-granular fines first (i.e. 24 hour, 1 day, 2 day, etc., all of which are multiples of 86400 seconds), and simply remove the time portion of timestamp +UPDATE money.billing mb + SET period_start = date_trunc('day', billing_ts), period_end = date_trunc('day', billing_ts) + (ac.fine_interval - '1 second') + FROM action.circulation ac +WHERE mb.xact = ac.id + AND mb.btype = 1 + AND (EXTRACT(EPOCH FROM ac.fine_interval))::integer % 86400 = 0; + +--Update fines for non-day intervals +UPDATE money.billing mb + SET period_start = billing_ts - ac.fine_interval + interval '1 sec', period_end = billing_ts + FROM action.circulation ac +WHERE mb.xact = ac.id + AND mb.btype = 1 + AND (EXTRACT(EPOCH FROM ac.fine_interval))::integer % 86400 > 0; + +SET CONSTRAINTS ALL IMMEDIATE; +UPDATE money.billing SET create_date = COALESCE(period_start, billing_ts); + +--Re-enable update trigger +ALTER TABLE money.billing ENABLE TRIGGER mat_summary_upd_tgr; + +ALTER TABLE money.billing ALTER COLUMN create_date SET DEFAULT NOW(); +ALTER TABLE money.billing ALTER COLUMN create_date SET NOT NULL; + +CREATE INDEX m_b_create_date_idx ON money.billing (create_date); +CREATE INDEX m_b_period_start_idx ON money.billing (period_start); +CREATE INDEX m_b_period_end_idx ON money.billing (period_end); + +CREATE OR REPLACE FUNCTION money.maintain_billing_ts () RETURNS TRIGGER AS $$ +BEGIN + NEW.billing_ts := COALESCE(NEW.period_end, NEW.create_date); + RETURN NEW; +END; +$$ LANGUAGE PLPGSQL; +CREATE TRIGGER maintain_billing_ts_tgr BEFORE INSERT OR UPDATE ON money.billing FOR EACH ROW EXECUTE PROCEDURE money.maintain_billing_ts(); + SELECT CLOCK_TIMESTAMP(), 'Making copy alert types active'; UPDATE config.copy_alert_type @@ -74,6 +130,26 @@ UPDATE actor.usr SET name_kw_tsvector = -- Authority record re-ingest +SELECT CLOCK_TIMESTAMP(), 'Updating copy alert messages'; + +SELECT CLOCK_TIMESTAMP(), 'Copying copy alert messages to normal checkout copy alerts...'; +INSERT INTO asset.copy_alert (alert_type, copy, note, create_staff) +SELECT 1, id, alert_message, 1 +FROM asset.copy +WHERE alert_message IS NOT NULL +AND alert_message <> ''; + +SELECT CLOCK_TIMESTAMP(), 'Copying copy alert messages to normal checkin copy alerts...'; +INSERT INTO asset.copy_alert (alert_type, copy, note, create_staff) +SELECT 2, id, alert_message, 1 +FROM asset.copy +WHERE alert_message IS NOT NULL +AND alert_message <> ''; + +SELECT CLOCK_TIMESTAMP(), 'Clearing legacy copy alert field; this may take a while'; +UPDATE asset.copy SET alert_message = NULL +WHERE alert_message IS NOT NULL; + SELECT CLOCK_TIMESTAMP(), 'Reingesting authority records'; -- add the flag ingest.disable_authority_full_rec if it does not exist @@ -117,7 +193,5 @@ SET enabled = b.enabled FROM internal_flag_state b WHERE a.name = b.name; - -COMMIT; ---ROLLBACK; +SELECT CLOCK_TIMESTAMP(), 'Done reingesting authority records'; diff --git a/KCLS/sql/schema/deploy/2.12-3.2-db-upgrade-schema.sql b/KCLS/sql/schema/deploy/2.12-3.2-db-upgrade-schema.sql index e23c432bbb..884cc01fbd 100644 --- a/KCLS/sql/schema/deploy/2.12-3.2-db-upgrade-schema.sql +++ b/KCLS/sql/schema/deploy/2.12-3.2-db-upgrade-schema.sql @@ -2448,13 +2448,14 @@ SELECT r.id, LEFT JOIN metabib.full_rec issn ON (r.id = issn.record AND issn.tag = '022' AND issn.subfield = 'a') GROUP BY 1,2,3,4,5; - + +/* MOVED TO UPDATE SECTION -- Remove trigger on biblio.record_entry SELECT reporter.disable_materialized_simple_record_trigger(); -- Rebuild reporter.materialized_simple_record SELECT reporter.enable_materialized_simple_record_trigger(); - +*/ -- SELECT evergreen.upgrade_deps_block_check('1060', :eg_version); @@ -8583,6 +8584,7 @@ END $INSERT$; --SELECT evergreen.upgrade_deps_block_check('1098', :eg_version); +/* MOVED TO DATA SECTION \qecho Copying copy alert messages to normal checkout copy alerts... INSERT INTO asset.copy_alert (alert_type, copy, note, create_staff) SELECT 1, id, alert_message, 1 @@ -8600,6 +8602,7 @@ AND alert_message <> ''; \qecho Clearing legacy copy alert field; this may take a while UPDATE asset.copy SET alert_message = NULL WHERE alert_message IS NOT NULL; +*/ --SELECT evergreen.upgrade_deps_block_check('1099', :eg_version); @@ -16415,6 +16418,7 @@ END $INSERT$; --SELECT evergreen.upgrade_deps_block_check('1106', :eg_version); +/* MOVED TO DATA FILE ALTER TABLE money.billing ADD COLUMN create_date TIMESTAMP WITH TIME ZONE, ADD COLUMN period_start TIMESTAMP WITH TIME ZONE, @@ -16425,6 +16429,9 @@ ALTER TABLE money.billing --view definition is unchanged (still using billing_ts) ALTER TABLE money.billing DISABLE TRIGGER mat_summary_upd_tgr; + +SELECT CLOCK_TIMESTAMP(), 'Updating billing timestamps'; + --Limit to btype=1 / 'Overdue Materials' --Update day-granular fines first (i.e. 24 hour, 1 day, 2 day, etc., all of which are multiples of 86400 seconds), and simply remove the time portion of timestamp UPDATE money.billing mb @@ -16463,6 +16470,8 @@ END; $$ LANGUAGE PLPGSQL; CREATE TRIGGER maintain_billing_ts_tgr BEFORE INSERT OR UPDATE ON money.billing FOR EACH ROW EXECUTE PROCEDURE money.maintain_billing_ts(); +*/ + --SELECT evergreen.upgrade_deps_block_check('1108', :eg_version); @@ -17286,6 +17295,9 @@ CREATE TABLE action.emergency_closing ( last_update_time TIMESTAMPTZ ); +-- needed to resolve earlier table and data changes. +SET CONSTRAINTS actor.org_unit_closed_org_unit_fkey IMMEDIATE; + ALTER TABLE actor.org_unit_closed ADD COLUMN emergency_closing INT REFERENCES action.emergency_closing (id) ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED; -- 2.11.0