From 1cbee9e917e63b7cdc985a1c9e821a14a7c652d5 Mon Sep 17 00:00:00 2001 From: Chris Sharp Date: Wed, 25 Sep 2019 07:38:50 -0400 Subject: [PATCH] update DB upgrade scripts to accommodate what we have already applied --- .../Pg/version-upgrade/3.2.5-3.3.0-upgrade-db.sql | 512 ++++++++++----------- .../Pg/version-upgrade/3.3.0-3.3.1-upgrade-db.sql | 6 +- .../version-upgrade/3.3.3-3.4-beta1-upgrade-db.sql | 308 ++++++------- 3 files changed, 413 insertions(+), 413 deletions(-) diff --git a/Open-ILS/src/sql/Pg/version-upgrade/3.2.5-3.3.0-upgrade-db.sql b/Open-ILS/src/sql/Pg/version-upgrade/3.2.5-3.3.0-upgrade-db.sql index 47e69d75d1..b7aae84a09 100644 --- a/Open-ILS/src/sql/Pg/version-upgrade/3.2.5-3.3.0-upgrade-db.sql +++ b/Open-ILS/src/sql/Pg/version-upgrade/3.2.5-3.3.0-upgrade-db.sql @@ -87,170 +87,170 @@ $func$ LANGUAGE PLPGSQL; SELECT evergreen.upgrade_deps_block_check('1139', :eg_version); -ALTER TABLE actor.usr ADD COLUMN guardian TEXT; - -CREATE INDEX actor_usr_guardian_idx - ON actor.usr (evergreen.lowercase(guardian)); -CREATE INDEX actor_usr_guardian_unaccent_idx - ON actor.usr (evergreen.unaccent_and_squash(guardian)); - --- Modify auditor tables accordingly. -SELECT auditor.update_auditors(); - --- clear the guardian field on delete -CREATE OR REPLACE FUNCTION actor.usr_delete( - src_usr IN INTEGER, - dest_usr IN INTEGER -) RETURNS VOID AS $$ -DECLARE - old_profile actor.usr.profile%type; - old_home_ou actor.usr.home_ou%type; - new_profile actor.usr.profile%type; - new_home_ou actor.usr.home_ou%type; - new_name text; - new_dob actor.usr.dob%type; -BEGIN - SELECT - id || '-PURGED-' || now(), - profile, - home_ou, - dob - INTO - new_name, - old_profile, - old_home_ou, - new_dob - FROM - actor.usr - WHERE - id = src_usr; - -- - -- Quit if no such user - -- - IF old_profile IS NULL THEN - RETURN; - END IF; - -- - perform actor.usr_purge_data( src_usr, dest_usr ); - -- - -- Find the root grp_tree and the root org_unit. This would be simpler if we - -- could assume that there is only one root. Theoretically, someday, maybe, - -- there could be multiple roots, so we take extra trouble to get the right ones. - -- - SELECT - id - INTO - new_profile - FROM - permission.grp_ancestors( old_profile ) - WHERE - parent is null; - -- - SELECT - id - INTO - new_home_ou - FROM - actor.org_unit_ancestors( old_home_ou ) - WHERE - parent_ou is null; - -- - -- Truncate date of birth - -- - IF new_dob IS NOT NULL THEN - new_dob := date_trunc( 'year', new_dob ); - END IF; - -- - UPDATE - actor.usr - SET - card = NULL, - profile = new_profile, - usrname = new_name, - email = NULL, - passwd = random()::text, - standing = DEFAULT, - ident_type = - ( - SELECT MIN( id ) - FROM config.identification_type - ), - ident_value = NULL, - ident_type2 = NULL, - ident_value2 = NULL, - net_access_level = DEFAULT, - photo_url = NULL, - prefix = NULL, - first_given_name = new_name, - guardian = NULL, - family_name = new_name, - suffix = NULL, - alias = NULL, - guardian = NULL, - day_phone = NULL, - evening_phone = NULL, - other_phone = NULL, - mailing_address = NULL, - billing_address = NULL, - home_ou = new_home_ou, - dob = new_dob, - active = FALSE, - master_account = DEFAULT, - super_user = DEFAULT, - barred = FALSE, - deleted = TRUE, - juvenile = DEFAULT, - usrgroup = 0, - claims_returned_count = DEFAULT, - credit_forward_balance = DEFAULT, - last_xact_id = DEFAULT, - alert_message = NULL, - create_date = now(), - expire_date = now() - WHERE - id = src_usr; -END; -$$ LANGUAGE plpgsql; - -INSERT into config.org_unit_setting_type (name, label, description, datatype) -VALUES ( - 'ui.patron.edit.au.guardian.show', - oils_i18n_gettext( - 'ui.patron.edit.au.guardian.show', - 'GUI: Show guardian field on patron registration', - 'coust', 'label' - ), - oils_i18n_gettext( - 'ui.patron.edit.au.guardian.show', - 'The guardian field will be shown on the patron registration screen. Showing a field makes it appear with required fields even when not required. If the field is required this setting is ignored.', - 'coust', 'description' - ), - 'bool' -), ( - 'ui.patron.edit.au.guardian.suggest', - oils_i18n_gettext( - 'ui.patron.edit.au.guardian.suggest', - 'GUI: Suggest guardian field on patron registration', - 'coust', 'label' - ), - oils_i18n_gettext( - 'ui.patron.edit.au.guardian.suggest', - 'The guardian field will be suggested on the patron registration screen. Suggesting a field makes it appear when suggested fields are shown. If the field is shown or required this setting is ignored.', - 'coust', 'description'), - 'bool' -), ( - 'ui.patron.edit.guardian_required_for_juv', - oils_i18n_gettext( - 'ui.patron.edit.guardian_required_for_juv', - 'GUI: Juvenile account requires parent/guardian', - 'coust', 'label' - ), - oils_i18n_gettext( - 'ui.patron.edit.guardian_required_for_juv', - 'Require a value for the parent/guardian field in the patron editor for patrons marked as juvenile', - 'coust', 'description'), - 'bool' -); +--ALTER TABLE actor.usr ADD COLUMN guardian TEXT; +-- +--CREATE INDEX actor_usr_guardian_idx +-- ON actor.usr (evergreen.lowercase(guardian)); +--CREATE INDEX actor_usr_guardian_unaccent_idx +-- ON actor.usr (evergreen.unaccent_and_squash(guardian)); +-- +---- Modify auditor tables accordingly. +--SELECT auditor.update_auditors(); +-- +---- clear the guardian field on delete +--CREATE OR REPLACE FUNCTION actor.usr_delete( +-- src_usr IN INTEGER, +-- dest_usr IN INTEGER +--) RETURNS VOID AS $$ +--DECLARE +-- old_profile actor.usr.profile%type; +-- old_home_ou actor.usr.home_ou%type; +-- new_profile actor.usr.profile%type; +-- new_home_ou actor.usr.home_ou%type; +-- new_name text; +-- new_dob actor.usr.dob%type; +--BEGIN +-- SELECT +-- id || '-PURGED-' || now(), +-- profile, +-- home_ou, +-- dob +-- INTO +-- new_name, +-- old_profile, +-- old_home_ou, +-- new_dob +-- FROM +-- actor.usr +-- WHERE +-- id = src_usr; +-- -- +-- -- Quit if no such user +-- -- +-- IF old_profile IS NULL THEN +-- RETURN; +-- END IF; +-- -- +-- perform actor.usr_purge_data( src_usr, dest_usr ); +-- -- +-- -- Find the root grp_tree and the root org_unit. This would be simpler if we +-- -- could assume that there is only one root. Theoretically, someday, maybe, +-- -- there could be multiple roots, so we take extra trouble to get the right ones. +-- -- +-- SELECT +-- id +-- INTO +-- new_profile +-- FROM +-- permission.grp_ancestors( old_profile ) +-- WHERE +-- parent is null; +-- -- +-- SELECT +-- id +-- INTO +-- new_home_ou +-- FROM +-- actor.org_unit_ancestors( old_home_ou ) +-- WHERE +-- parent_ou is null; +-- -- +-- -- Truncate date of birth +-- -- +-- IF new_dob IS NOT NULL THEN +-- new_dob := date_trunc( 'year', new_dob ); +-- END IF; +-- -- +-- UPDATE +-- actor.usr +-- SET +-- card = NULL, +-- profile = new_profile, +-- usrname = new_name, +-- email = NULL, +-- passwd = random()::text, +-- standing = DEFAULT, +-- ident_type = +-- ( +-- SELECT MIN( id ) +-- FROM config.identification_type +-- ), +-- ident_value = NULL, +-- ident_type2 = NULL, +-- ident_value2 = NULL, +-- net_access_level = DEFAULT, +-- photo_url = NULL, +-- prefix = NULL, +-- first_given_name = new_name, +-- guardian = NULL, +-- family_name = new_name, +-- suffix = NULL, +-- alias = NULL, +-- guardian = NULL, +-- day_phone = NULL, +-- evening_phone = NULL, +-- other_phone = NULL, +-- mailing_address = NULL, +-- billing_address = NULL, +-- home_ou = new_home_ou, +-- dob = new_dob, +-- active = FALSE, +-- master_account = DEFAULT, +-- super_user = DEFAULT, +-- barred = FALSE, +-- deleted = TRUE, +-- juvenile = DEFAULT, +-- usrgroup = 0, +-- claims_returned_count = DEFAULT, +-- credit_forward_balance = DEFAULT, +-- last_xact_id = DEFAULT, +-- alert_message = NULL, +-- create_date = now(), +-- expire_date = now() +-- WHERE +-- id = src_usr; +--END; +--$$ LANGUAGE plpgsql; +-- +--INSERT into config.org_unit_setting_type (name, label, description, datatype) +--VALUES ( +-- 'ui.patron.edit.au.guardian.show', +-- oils_i18n_gettext( +-- 'ui.patron.edit.au.guardian.show', +-- 'GUI: Show guardian field on patron registration', +-- 'coust', 'label' +-- ), +-- oils_i18n_gettext( +-- 'ui.patron.edit.au.guardian.show', +-- 'The guardian field will be shown on the patron registration screen. Showing a field makes it appear with required fields even when not required. If the field is required this setting is ignored.', +-- 'coust', 'description' +-- ), +-- 'bool' +--), ( +-- 'ui.patron.edit.au.guardian.suggest', +-- oils_i18n_gettext( +-- 'ui.patron.edit.au.guardian.suggest', +-- 'GUI: Suggest guardian field on patron registration', +-- 'coust', 'label' +-- ), +-- oils_i18n_gettext( +-- 'ui.patron.edit.au.guardian.suggest', +-- 'The guardian field will be suggested on the patron registration screen. Suggesting a field makes it appear when suggested fields are shown. If the field is shown or required this setting is ignored.', +-- 'coust', 'description'), +-- 'bool' +--), ( +-- 'ui.patron.edit.guardian_required_for_juv', +-- oils_i18n_gettext( +-- 'ui.patron.edit.guardian_required_for_juv', +-- 'GUI: Juvenile account requires parent/guardian', +-- 'coust', 'label' +-- ), +-- oils_i18n_gettext( +-- 'ui.patron.edit.guardian_required_for_juv', +-- 'Require a value for the parent/guardian field in the patron editor for patrons marked as juvenile', +-- 'coust', 'description'), +-- 'bool' +--); @@ -1170,98 +1170,98 @@ VALUES ( -SELECT evergreen.upgrade_deps_block_check('1153', :eg_version); - -UPDATE config.org_unit_setting_type -SET label = oils_i18n_gettext( - 'webstaff.cat.label.left_label.left_margin' - ,'Item Print Label - Left Margin for Spine Label' - ,'coust' - ,'label' - ), - description = oils_i18n_gettext( - 'webstaff.cat.label.left_label.left_margin' - ,'Set the default left margin for the item print Spine Label. Please include a unit of measurement that is valid CSS. For example, "1in" or "2.5cm"' - ,'coust' - ,'description' - ) -WHERE NAME = 'webstaff.cat.label.left_label.left_margin'; - -UPDATE config.org_unit_setting_type -SET label = oils_i18n_gettext( - 'webstaff.cat.label.right_label.left_margin' - ,'Item Print Label - Left Margin for Pocket Label' - ,'coust' - ,'label' - ), - description = oils_i18n_gettext( - 'webstaff.cat.label.right_label.left_margin' - ,'Set the default left margin for the item print Pocket Label (or in other words, the desired space between the two labels). Please include a unit of measurement that is valid CSS. For example, "1in" or "2.5cm"' - ,'coust' - ,'description' - ) -WHERE NAME = 'webstaff.cat.label.right_label.left_margin'; - - -UPDATE config.org_unit_setting_type -SET label = oils_i18n_gettext( - 'webstaff.cat.label.left_label.height' - ,'Item Print Label - Height for Spine Label' - ,'coust' - ,'label' - ), - description = oils_i18n_gettext( - 'webstaff.cat.label.left_label.height' - ,'Set the default height for the item print Spine Label. Please include a unit of measurement that is valid CSS. For example, "1in" or "2.5cm"' - ,'coust' - ,'description' - ) -WHERE NAME = 'webstaff.cat.label.left_label.height'; - -UPDATE config.org_unit_setting_type -SET label = oils_i18n_gettext( - 'webstaff.cat.label.left_label.width' - ,'Item Print Label - Width for Spine Label' - ,'coust' - ,'label' - ), - description = oils_i18n_gettext( - 'webstaff.cat.label.left_label.width' - ,'Set the default width for the item print Spine Label. Please include a unit of measurement that is valid CSS. For example, "1in" or "2.5cm"' - ,'coust' - ,'description' - ) -WHERE NAME = 'webstaff.cat.label.left_label.width'; - -UPDATE config.org_unit_setting_type -SET label = oils_i18n_gettext( - 'webstaff.cat.label.right_label.height' - ,'Item Print Label - Height for Pocket Label' - ,'coust' - ,'label' - ), - description = oils_i18n_gettext( - 'webstaff.cat.label.right_label.height' - ,'Set the default height for the item print Pocket Label. Please include a unit of measurement that is valid CSS. For example, "1in" or "2.5cm"' - ,'coust' - ,'description' - ) -WHERE NAME = 'webstaff.cat.label.right_label.height'; - -UPDATE config.org_unit_setting_type -SET label = oils_i18n_gettext( - 'webstaff.cat.label.right_label.width' - ,'Item Print Label - Width for Pocket Label' - ,'coust' - ,'label' - ), - description = oils_i18n_gettext( - 'webstaff.cat.label.right_label.width' - ,'Set the default width for the item print Pocket Label. Please include a unit of measurement that is valid CSS. For example, "1in" or "2.5cm"' - ,'coust' - ,'description' - ) -WHERE NAME = 'webstaff.cat.label.right_label.width'; +--SELECT evergreen.upgrade_deps_block_check('1153', :eg_version); +-- +--UPDATE config.org_unit_setting_type +--SET label = oils_i18n_gettext( +-- 'webstaff.cat.label.left_label.left_margin' +-- ,'Item Print Label - Left Margin for Spine Label' +-- ,'coust' +-- ,'label' +-- ), +-- description = oils_i18n_gettext( +-- 'webstaff.cat.label.left_label.left_margin' +-- ,'Set the default left margin for the item print Spine Label. Please include a unit of measurement that is valid CSS. For example, "1in" or "2.5cm"' +-- ,'coust' +-- ,'description' +-- ) +--WHERE NAME = 'webstaff.cat.label.left_label.left_margin'; +-- +--UPDATE config.org_unit_setting_type +--SET label = oils_i18n_gettext( +-- 'webstaff.cat.label.right_label.left_margin' +-- ,'Item Print Label - Left Margin for Pocket Label' +-- ,'coust' +-- ,'label' +-- ), +-- description = oils_i18n_gettext( +-- 'webstaff.cat.label.right_label.left_margin' +-- ,'Set the default left margin for the item print Pocket Label (or in other words, the desired space between the two labels). Please include a unit of measurement that is valid CSS. For example, "1in" or "2.5cm"' +-- ,'coust' +-- ,'description' +-- ) +--WHERE NAME = 'webstaff.cat.label.right_label.left_margin'; +-- +-- +--UPDATE config.org_unit_setting_type +--SET label = oils_i18n_gettext( +-- 'webstaff.cat.label.left_label.height' +-- ,'Item Print Label - Height for Spine Label' +-- ,'coust' +-- ,'label' +-- ), +-- description = oils_i18n_gettext( +-- 'webstaff.cat.label.left_label.height' +-- ,'Set the default height for the item print Spine Label. Please include a unit of measurement that is valid CSS. For example, "1in" or "2.5cm"' +-- ,'coust' +-- ,'description' +-- ) +--WHERE NAME = 'webstaff.cat.label.left_label.height'; +-- +--UPDATE config.org_unit_setting_type +--SET label = oils_i18n_gettext( +-- 'webstaff.cat.label.left_label.width' +-- ,'Item Print Label - Width for Spine Label' +-- ,'coust' +-- ,'label' +-- ), +-- description = oils_i18n_gettext( +-- 'webstaff.cat.label.left_label.width' +-- ,'Set the default width for the item print Spine Label. Please include a unit of measurement that is valid CSS. For example, "1in" or "2.5cm"' +-- ,'coust' +-- ,'description' +-- ) +--WHERE NAME = 'webstaff.cat.label.left_label.width'; +-- +--UPDATE config.org_unit_setting_type +--SET label = oils_i18n_gettext( +-- 'webstaff.cat.label.right_label.height' +-- ,'Item Print Label - Height for Pocket Label' +-- ,'coust' +-- ,'label' +-- ), +-- description = oils_i18n_gettext( +-- 'webstaff.cat.label.right_label.height' +-- ,'Set the default height for the item print Pocket Label. Please include a unit of measurement that is valid CSS. For example, "1in" or "2.5cm"' +-- ,'coust' +-- ,'description' +-- ) +--WHERE NAME = 'webstaff.cat.label.right_label.height'; +-- +--UPDATE config.org_unit_setting_type +--SET label = oils_i18n_gettext( +-- 'webstaff.cat.label.right_label.width' +-- ,'Item Print Label - Width for Pocket Label' +-- ,'coust' +-- ,'label' +-- ), +-- description = oils_i18n_gettext( +-- 'webstaff.cat.label.right_label.width' +-- ,'Set the default width for the item print Pocket Label. Please include a unit of measurement that is valid CSS. For example, "1in" or "2.5cm"' +-- ,'coust' +-- ,'description' +-- ) +--WHERE NAME = 'webstaff.cat.label.right_label.width'; SELECT evergreen.upgrade_deps_block_check('1155', :eg_version); diff --git a/Open-ILS/src/sql/Pg/version-upgrade/3.3.0-3.3.1-upgrade-db.sql b/Open-ILS/src/sql/Pg/version-upgrade/3.3.0-3.3.1-upgrade-db.sql index 68140cecfa..caf0e45ba2 100644 --- a/Open-ILS/src/sql/Pg/version-upgrade/3.3.0-3.3.1-upgrade-db.sql +++ b/Open-ILS/src/sql/Pg/version-upgrade/3.3.0-3.3.1-upgrade-db.sql @@ -92,6 +92,6 @@ COMMIT; -- No transaction needed. This can be run on a live, production server. SELECT evergreen.upgrade_deps_block_check('1161', :eg_version); -- jboyer/stompro/gmcharlt -CREATE INDEX CONCURRENTLY atev_template_output ON action_trigger.event (template_output); -CREATE INDEX CONCURRENTLY atev_async_output ON action_trigger.event (async_output); -CREATE INDEX CONCURRENTLY atev_error_output ON action_trigger.event (error_output); +--CREATE INDEX CONCURRENTLY atev_template_output ON action_trigger.event (template_output); +--CREATE INDEX CONCURRENTLY atev_async_output ON action_trigger.event (async_output); +--CREATE INDEX CONCURRENTLY atev_error_output ON action_trigger.event (error_output); diff --git a/Open-ILS/src/sql/Pg/version-upgrade/3.3.3-3.4-beta1-upgrade-db.sql b/Open-ILS/src/sql/Pg/version-upgrade/3.3.3-3.4-beta1-upgrade-db.sql index 0b0eced631..3e78637c0a 100644 --- a/Open-ILS/src/sql/Pg/version-upgrade/3.3.3-3.4-beta1-upgrade-db.sql +++ b/Open-ILS/src/sql/Pg/version-upgrade/3.3.3-3.4-beta1-upgrade-db.sql @@ -372,31 +372,31 @@ VALUES ( SELECT evergreen.upgrade_deps_block_check('1178', :eg_version); -INSERT INTO action_trigger.event_definition (active, owner, name, hook, validator, reactor, delay, group_field, max_delay, template) - VALUES (false, 1, 'Fine Limit Exceeded', 'penalty.PATRON_EXCEEDS_FINES', 'NOOP_True', 'SendEmail', '00:05:00', 'usr', '1 day', -$$ -[%- USE date -%] -[%- user = target.usr -%] - -To: [%- params.recipient_email || user.email %] -From: [%- params.sender_email || default_sender %] -Date: [%- date.format(date.now, '%a, %d %b %Y %T -0000', gmt => 1) %] -Subject: Fine Limit Exceeded -Auto-Submitted: auto-generated - -Dear [% user.first_given_name %] [% user.family_name %], - - -Our records indicate your account has exceeded the fine limit allowed for the use of your library account. - -Please visit the library to pay your fines and restore full access to your account. -[% END %] - -$$); - -INSERT INTO action_trigger.environment (event_def, path) VALUES - (currval('action_trigger.event_definition_id_seq'), 'usr'), - (currval('action_trigger.event_definition_id_seq'), 'usr.card'); +--INSERT INTO action_trigger.event_definition (active, owner, name, hook, validator, reactor, delay, group_field, max_delay, template) +-- VALUES (false, 1, 'Fine Limit Exceeded', 'penalty.PATRON_EXCEEDS_FINES', 'NOOP_True', 'SendEmail', '00:05:00', 'usr', '1 day', +--$$ +--[%- USE date -%] +--[%- user = target.usr -%] +-- +--To: [%- params.recipient_email || user.email %] +--From: [%- params.sender_email || default_sender %] +--Date: [%- date.format(date.now, '%a, %d %b %Y %T -0000', gmt => 1) %] +--Subject: Fine Limit Exceeded +--Auto-Submitted: auto-generated +-- +--Dear [% user.first_given_name %] [% user.family_name %], +-- +-- +--Our records indicate your account has exceeded the fine limit allowed for the use of your library account. +-- +--Please visit the library to pay your fines and restore full access to your account. +--[% END %] +-- +--$$); +-- +--INSERT INTO action_trigger.environment (event_def, path) VALUES +-- (currval('action_trigger.event_definition_id_seq'), 'usr'), +-- (currval('action_trigger.event_definition_id_seq'), 'usr.card'); SELECT evergreen.upgrade_deps_block_check('1179', :eg_version); @@ -514,135 +514,135 @@ $func$ LANGUAGE plpgsql; SELECT evergreen.upgrade_deps_block_check('1181', :eg_version); - -\qecho Migrating aged billing and payment data. This might take a while. - -CREATE TABLE money.aged_payment (LIKE money.payment INCLUDING INDEXES); -ALTER TABLE money.aged_payment ADD COLUMN payment_type TEXT NOT NULL; - -CREATE TABLE money.aged_billing (LIKE money.billing INCLUDING INDEXES); - -INSERT INTO money.aged_payment - SELECT mp.* FROM money.payment_view mp - JOIN action.aged_circulation circ ON (circ.id = mp.xact); - -INSERT INTO money.aged_billing - SELECT mb.* FROM money.billing mb - JOIN action.aged_circulation circ ON (circ.id = mb.xact); - -CREATE OR REPLACE VIEW money.all_payments AS - SELECT * FROM money.payment_view - UNION ALL - SELECT * FROM money.aged_payment; - -CREATE OR REPLACE VIEW money.all_billings AS - SELECT * FROM money.billing - UNION ALL - SELECT * FROM money.aged_billing; - -CREATE OR REPLACE FUNCTION action.age_circ_on_delete () RETURNS TRIGGER AS $$ -DECLARE -found char := 'N'; -BEGIN - - -- If there are any renewals for this circulation, don't archive or delete - -- it yet. We'll do so later, when we archive and delete the renewals. - - SELECT 'Y' INTO found - FROM action.circulation - WHERE parent_circ = OLD.id - LIMIT 1; - - IF found = 'Y' THEN - RETURN NULL; -- don't delete - END IF; - - -- Archive a copy of the old row to action.aged_circulation - - INSERT INTO action.aged_circulation - (id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location, - copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy, - circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date, - stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine, - max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule, - max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ) - SELECT - id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location, - copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy, - circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date, - stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine, - max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule, - max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ - FROM action.all_circulation WHERE id = OLD.id; - - -- Migrate billings and payments to aged tables - - INSERT INTO money.aged_billing - SELECT * FROM money.billing WHERE xact = OLD.id; - - INSERT INTO money.aged_payment - SELECT * FROM money.payment_view WHERE xact = OLD.id; - - DELETE FROM money.payment WHERE xact = OLD.id; - DELETE FROM money.billing WHERE xact = OLD.id; - - RETURN OLD; -END; -$$ LANGUAGE 'plpgsql'; - --- NOTE you could COMMIT here then start a new TRANSACTION if desired. - -\qecho Deleting aged payments and billings from active payment/billing -\qecho tables. This may take a while... - -ALTER TABLE money.payment DISABLE TRIGGER mat_summary_del_tgr; -ALTER TABLE money.cash_payment DISABLE TRIGGER mat_summary_del_tgr; -ALTER TABLE money.check_payment DISABLE TRIGGER mat_summary_del_tgr; -ALTER TABLE money.credit_card_payment DISABLE TRIGGER mat_summary_del_tgr; -ALTER TABLE money.forgive_payment DISABLE TRIGGER mat_summary_del_tgr; -ALTER TABLE money.credit_payment DISABLE TRIGGER mat_summary_del_tgr; -ALTER TABLE money.goods_payment DISABLE TRIGGER mat_summary_del_tgr; - -DELETE FROM money.payment WHERE id IN (SELECT id FROM money.aged_payment); - -ALTER TABLE money.payment ENABLE TRIGGER mat_summary_del_tgr; -ALTER TABLE money.cash_payment ENABLE TRIGGER mat_summary_del_tgr; -ALTER TABLE money.check_payment ENABLE TRIGGER mat_summary_del_tgr; -ALTER TABLE money.credit_card_payment ENABLE TRIGGER mat_summary_del_tgr; -ALTER TABLE money.forgive_payment ENABLE TRIGGER mat_summary_del_tgr; -ALTER TABLE money.credit_payment ENABLE TRIGGER mat_summary_del_tgr; -ALTER TABLE money.goods_payment ENABLE TRIGGER mat_summary_del_tgr; - --- TODO: This approach assumes most of the money.billing rows have been --- copied to money.aged_billing. If that is not the case, which would --- happen if circ anonymization is not enabled, it will be faster to --- perform a simple delete instead of a truncate/rebuild. - --- Copy all money.billing rows that are not represented in money.aged_billing -CREATE TEMPORARY TABLE tmp_money_billing ON COMMIT DROP AS - SELECT mb.* FROM money.billing mb - LEFT JOIN money.aged_billing mab USING (id) - WHERE mab.id IS NULL; - -ALTER TABLE money.billing DISABLE TRIGGER ALL; - --- temporarily remove the foreign key constraint to money.billing on --- account adjusment. Needed for money.billing truncate. -ALTER TABLE money.account_adjustment - DROP CONSTRAINT account_adjustment_billing_fkey; - -TRUNCATE money.billing; - -INSERT INTO money.billing SELECT * FROM tmp_money_billing; - -ALTER TABLE money.billing ENABLE TRIGGER ALL; -ALTER TABLE money.account_adjustment - ADD CONSTRAINT account_adjustment_billing_fkey - FOREIGN KEY (billing) REFERENCES money.billing (id); - - --- Good to run after truncating -- OK to run after COMMIT. -ANALYZE money.billing; +-- +--\qecho Migrating aged billing and payment data. This might take a while. +-- +--CREATE TABLE money.aged_payment (LIKE money.payment INCLUDING INDEXES); +--ALTER TABLE money.aged_payment ADD COLUMN payment_type TEXT NOT NULL; +-- +--CREATE TABLE money.aged_billing (LIKE money.billing INCLUDING INDEXES); +-- +--INSERT INTO money.aged_payment +-- SELECT mp.* FROM money.payment_view mp +-- JOIN action.aged_circulation circ ON (circ.id = mp.xact); +-- +--INSERT INTO money.aged_billing +-- SELECT mb.* FROM money.billing mb +-- JOIN action.aged_circulation circ ON (circ.id = mb.xact); +-- +--CREATE OR REPLACE VIEW money.all_payments AS +-- SELECT * FROM money.payment_view +-- UNION ALL +-- SELECT * FROM money.aged_payment; +-- +--CREATE OR REPLACE VIEW money.all_billings AS +-- SELECT * FROM money.billing +-- UNION ALL +-- SELECT * FROM money.aged_billing; +-- +--CREATE OR REPLACE FUNCTION action.age_circ_on_delete () RETURNS TRIGGER AS $$ +--DECLARE +--found char := 'N'; +--BEGIN +-- +-- -- If there are any renewals for this circulation, don't archive or delete +-- -- it yet. We'll do so later, when we archive and delete the renewals. +-- +-- SELECT 'Y' INTO found +-- FROM action.circulation +-- WHERE parent_circ = OLD.id +-- LIMIT 1; +-- +-- IF found = 'Y' THEN +-- RETURN NULL; -- don't delete +-- END IF; +-- +-- -- Archive a copy of the old row to action.aged_circulation +-- +-- INSERT INTO action.aged_circulation +-- (id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location, +-- copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy, +-- circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date, +-- stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine, +-- max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule, +-- max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ) +-- SELECT +-- id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location, +-- copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy, +-- circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date, +-- stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine, +-- max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule, +-- max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ +-- FROM action.all_circulation WHERE id = OLD.id; +-- +-- -- Migrate billings and payments to aged tables +-- +-- INSERT INTO money.aged_billing +-- SELECT * FROM money.billing WHERE xact = OLD.id; +-- +-- INSERT INTO money.aged_payment +-- SELECT * FROM money.payment_view WHERE xact = OLD.id; +-- +-- DELETE FROM money.payment WHERE xact = OLD.id; +-- DELETE FROM money.billing WHERE xact = OLD.id; +-- +-- RETURN OLD; +--END; +--$$ LANGUAGE 'plpgsql'; +-- +---- NOTE you could COMMIT here then start a new TRANSACTION if desired. +-- +--\qecho Deleting aged payments and billings from active payment/billing +--\qecho tables. This may take a while... +-- +--ALTER TABLE money.payment DISABLE TRIGGER mat_summary_del_tgr; +--ALTER TABLE money.cash_payment DISABLE TRIGGER mat_summary_del_tgr; +--ALTER TABLE money.check_payment DISABLE TRIGGER mat_summary_del_tgr; +--ALTER TABLE money.credit_card_payment DISABLE TRIGGER mat_summary_del_tgr; +--ALTER TABLE money.forgive_payment DISABLE TRIGGER mat_summary_del_tgr; +--ALTER TABLE money.credit_payment DISABLE TRIGGER mat_summary_del_tgr; +--ALTER TABLE money.goods_payment DISABLE TRIGGER mat_summary_del_tgr; +-- +--DELETE FROM money.payment WHERE id IN (SELECT id FROM money.aged_payment); +-- +--ALTER TABLE money.payment ENABLE TRIGGER mat_summary_del_tgr; +--ALTER TABLE money.cash_payment ENABLE TRIGGER mat_summary_del_tgr; +--ALTER TABLE money.check_payment ENABLE TRIGGER mat_summary_del_tgr; +--ALTER TABLE money.credit_card_payment ENABLE TRIGGER mat_summary_del_tgr; +--ALTER TABLE money.forgive_payment ENABLE TRIGGER mat_summary_del_tgr; +--ALTER TABLE money.credit_payment ENABLE TRIGGER mat_summary_del_tgr; +--ALTER TABLE money.goods_payment ENABLE TRIGGER mat_summary_del_tgr; +-- +---- TODO: This approach assumes most of the money.billing rows have been +---- copied to money.aged_billing. If that is not the case, which would +---- happen if circ anonymization is not enabled, it will be faster to +---- perform a simple delete instead of a truncate/rebuild. +-- +---- Copy all money.billing rows that are not represented in money.aged_billing +--CREATE TEMPORARY TABLE tmp_money_billing ON COMMIT DROP AS +-- SELECT mb.* FROM money.billing mb +-- LEFT JOIN money.aged_billing mab USING (id) +-- WHERE mab.id IS NULL; +-- +--ALTER TABLE money.billing DISABLE TRIGGER ALL; +-- +---- temporarily remove the foreign key constraint to money.billing on +---- account adjusment. Needed for money.billing truncate. +--ALTER TABLE money.account_adjustment +-- DROP CONSTRAINT account_adjustment_billing_fkey; +-- +--TRUNCATE money.billing; +-- +--INSERT INTO money.billing SELECT * FROM tmp_money_billing; +-- +--ALTER TABLE money.billing ENABLE TRIGGER ALL; +--ALTER TABLE money.account_adjustment +-- ADD CONSTRAINT account_adjustment_billing_fkey +-- FOREIGN KEY (billing) REFERENCES money.billing (id); +-- +-- +---- Good to run after truncating -- OK to run after COMMIT. +--ANALYZE money.billing; SELECT evergreen.upgrade_deps_block_check('1182', :eg_version); -- 2.11.0