From 01b02553359a0c8b2cbd2d8fabc06c6d626b2f08 Mon Sep 17 00:00:00 2001 From: Bill Erickson Date: Mon, 12 Oct 2015 11:12:07 -0400 Subject: [PATCH] LP#838525 DoB as date SQL upgrade repairs 1. Modify auditor.actor_usr_history DoB column in the same manner as actor.usr to ensure consistent date truncation. 2. let update_auditors() recreate auditor.actor_usr_lifecycle for us. 3. Wrap the whole shebang in a PLPGSQL "DO" block so we can easily test for the presence of optional reporter views that depend on actor.usr, so they can be recreated as needed. Signed-off-by: Bill Erickson Signed-off-by: Jason Stephenson --- .../src/sql/Pg/upgrade/0945.schema.dob-as-date.sql | 221 ++++++++++++++++----- 1 file changed, 176 insertions(+), 45 deletions(-) diff --git a/Open-ILS/src/sql/Pg/upgrade/0945.schema.dob-as-date.sql b/Open-ILS/src/sql/Pg/upgrade/0945.schema.dob-as-date.sql index 12a5fbd7f4..1da112166f 100644 --- a/Open-ILS/src/sql/Pg/upgrade/0945.schema.dob-as-date.sql +++ b/Open-ILS/src/sql/Pg/upgrade/0945.schema.dob-as-date.sql @@ -2,56 +2,38 @@ BEGIN; SELECT evergreen.upgrade_deps_block_check('0945', :eg_version); +-- run the entire update inside a DO block for managing the logic +-- of whether to recreate the optional reporter views +DO $$ +DECLARE + has_current_circ BOOLEAN; + has_billing_summary BOOLEAN; +BEGIN + +SELECT INTO has_current_circ TRUE FROM pg_views + WHERE schemaname = 'reporter' AND viewname = 'classic_current_circ'; + +SELECT INTO has_billing_summary TRUE FROM pg_views + WHERE schemaname = 'reporter' AND + viewname = 'classic_current_billing_summary'; + DROP VIEW action.all_circulation; +DROP VIEW IF EXISTS reporter.classic_current_circ; +DROP VIEW IF EXISTS reporter.classic_current_billing_summary; DROP VIEW reporter.demographic; DROP VIEW auditor.actor_usr_lifecycle; DROP VIEW action.all_hold_request; -ALTER TABLE actor.usr ALTER dob TYPE date USING (dob + '3 hours')::date; - -CREATE VIEW auditor.actor_usr_lifecycle AS - SELECT (-1) AS audit_id, now() AS audit_time, - '-'::text AS audit_action, (-1) AS audit_user, (-1) AS audit_ws, - usr.id, usr.card, usr.profile, usr.usrname, usr.email, usr.passwd, - usr.standing, usr.ident_type, usr.ident_value, usr.ident_type2, - usr.ident_value2, usr.net_access_level, usr.photo_url, usr.prefix, - usr.first_given_name, usr.second_given_name, usr.family_name, - usr.suffix, usr.alias, usr.day_phone, usr.evening_phone, - usr.other_phone, usr.mailing_address, usr.billing_address, - usr.home_ou, usr.dob, usr.active, usr.master_account, - usr.super_user, usr.barred, usr.deleted, usr.juvenile, usr.usrgroup, - usr.claims_returned_count, usr.credit_forward_balance, - usr.last_xact_id, usr.alert_message, usr.create_date, - usr.expire_date, usr.claims_never_checked_out_count, - usr.last_update_time - FROM actor.usr -UNION ALL - SELECT actor_usr_history.audit_id, actor_usr_history.audit_time, - actor_usr_history.audit_action, actor_usr_history.audit_user, - actor_usr_history.audit_ws, actor_usr_history.id, - actor_usr_history.card, actor_usr_history.profile, - actor_usr_history.usrname, actor_usr_history.email, - actor_usr_history.passwd, actor_usr_history.standing, - actor_usr_history.ident_type, actor_usr_history.ident_value, - actor_usr_history.ident_type2, actor_usr_history.ident_value2, - actor_usr_history.net_access_level, actor_usr_history.photo_url, - actor_usr_history.prefix, actor_usr_history.first_given_name, - actor_usr_history.second_given_name, actor_usr_history.family_name, - actor_usr_history.suffix, actor_usr_history.alias, - actor_usr_history.day_phone, actor_usr_history.evening_phone, - actor_usr_history.other_phone, actor_usr_history.mailing_address, - actor_usr_history.billing_address, actor_usr_history.home_ou, - actor_usr_history.dob, actor_usr_history.active, - actor_usr_history.master_account, actor_usr_history.super_user, - actor_usr_history.barred, actor_usr_history.deleted, - actor_usr_history.juvenile, actor_usr_history.usrgroup, - actor_usr_history.claims_returned_count, - actor_usr_history.credit_forward_balance, - actor_usr_history.last_xact_id, actor_usr_history.alert_message, - actor_usr_history.create_date, actor_usr_history.expire_date, - actor_usr_history.claims_never_checked_out_count, - actor_usr_history.last_update_time - FROM auditor.actor_usr_history; +ALTER TABLE actor.usr + ALTER dob TYPE DATE USING (dob + '3 hours'::INTERVAL)::DATE; + +-- alter the auditor table manually to apply the same +-- dob mangling logic as above. +ALTER TABLE auditor.actor_usr_history + ALTER dob TYPE DATE USING (dob + '3 hours'::INTERVAL)::DATE; + +-- this recreates auditor.actor_usr_lifecycle +PERFORM auditor.update_auditors(); CREATE VIEW reporter.demographic AS SELECT u.id, u.dob, @@ -160,4 +142,153 @@ UNION ALL aged_hold_request.current_shelf_lib, aged_hold_request.behind_desk FROM action.aged_hold_request; +IF has_current_circ THEN +RAISE NOTICE 'Recreating optional view reporter.classic_current_circ'; + +CREATE OR REPLACE VIEW reporter.classic_current_circ AS +SELECT cl.shortname AS circ_lib, + cl.id AS circ_lib_id, + circ.xact_start AS xact_start, + circ_type.type AS circ_type, + cp.id AS copy_id, + cp.circ_modifier, + ol.shortname AS owning_lib_name, + lm.value AS language, + lfm.value AS lit_form, + ifm.value AS item_form, + itm.value AS item_type, + sl.name AS shelving_location, + p.id AS patron_id, + g.name AS profile_group, + dem.general_division AS demographic_general_division, + circ.id AS id, + cn.id AS call_number, + cn.label AS call_number_label, + call_number_dewey(cn.label) AS dewey, + CASE + WHEN call_number_dewey(cn.label) ~ E'^[0-9.]+$' + THEN + btrim( + to_char( + 10 * floor((call_number_dewey(cn.label)::float) / 10), '000' + ) + ) + ELSE NULL + END AS dewey_block_tens, + CASE + WHEN call_number_dewey(cn.label) ~ E'^[0-9.]+$' + THEN + btrim( + to_char( + 100 * floor((call_number_dewey(cn.label)::float) / 100), '000' + ) + ) + ELSE NULL + END AS dewey_block_hundreds, + CASE + WHEN call_number_dewey(cn.label) ~ E'^[0-9.]+$' + THEN + btrim( + to_char( + 10 * floor((call_number_dewey(cn.label)::float) / 10), '000' + ) + ) + || '-' || + btrim( + to_char( + 10 * floor((call_number_dewey(cn.label)::float) / 10) + 9, '000' + ) + ) + ELSE NULL + END AS dewey_range_tens, + CASE + WHEN call_number_dewey(cn.label) ~ E'^[0-9.]+$' + THEN + btrim( + to_char( + 100 * floor((call_number_dewey(cn.label)::float) / 100), '000' + ) + ) + || '-' || + btrim( + to_char( + 100 * floor((call_number_dewey(cn.label)::float) / 100) + 99, '000' + ) + ) + ELSE NULL + END AS dewey_range_hundreds, + hl.id AS patron_home_lib, + hl.shortname AS patron_home_lib_shortname, + paddr.county AS patron_county, + paddr.city AS patron_city, + paddr.post_code AS patron_zip, + sc1.stat_cat_entry AS stat_cat_1, + sc2.stat_cat_entry AS stat_cat_2, + sce1.value AS stat_cat_1_value, + sce2.value AS stat_cat_2_value + FROM action.circulation circ + JOIN reporter.circ_type circ_type ON (circ.id = circ_type.id) + JOIN asset.copy cp ON (cp.id = circ.target_copy) + JOIN asset.copy_location sl ON (cp.location = sl.id) + JOIN asset.call_number cn ON (cp.call_number = cn.id) + JOIN actor.org_unit ol ON (cn.owning_lib = ol.id) + JOIN metabib.rec_descriptor rd ON (rd.record = cn.record) + JOIN actor.org_unit cl ON (circ.circ_lib = cl.id) + JOIN actor.usr p ON (p.id = circ.usr) + JOIN actor.org_unit hl ON (p.home_ou = hl.id) + JOIN permission.grp_tree g ON (p.profile = g.id) + JOIN reporter.demographic dem ON (dem.id = p.id) + JOIN actor.usr_address paddr ON (paddr.id = p.billing_address) + LEFT JOIN config.language_map lm ON (rd.item_lang = lm.code) + LEFT JOIN config.lit_form_map lfm ON (rd.lit_form = lfm.code) + LEFT JOIN config.item_form_map ifm ON (rd.item_form = ifm.code) + LEFT JOIN config.item_type_map itm ON (rd.item_type = itm.code) + LEFT JOIN asset.stat_cat_entry_copy_map sc1 ON (sc1.owning_copy = cp.id AND sc1.stat_cat = 1) + LEFT JOIN asset.stat_cat_entry sce1 ON (sce1.id = sc1.stat_cat_entry) + LEFT JOIN asset.stat_cat_entry_copy_map sc2 ON (sc2.owning_copy = cp.id AND sc2.stat_cat = 2) + LEFT JOIN asset.stat_cat_entry sce2 ON (sce2.id = sc2.stat_cat_entry); +END IF; + +IF has_billing_summary THEN +RAISE NOTICE 'Recreating optional view reporter.classic_current_billing_summary'; + +CREATE OR REPLACE VIEW reporter.classic_current_billing_summary AS +SELECT x.id AS id, + x.usr AS usr, + bl.shortname AS billing_location_shortname, + bl.name AS billing_location_name, + x.billing_location AS billing_location, + c.barcode AS barcode, + u.home_ou AS usr_home_ou, + ul.shortname AS usr_home_ou_shortname, + ul.name AS usr_home_ou_name, + x.xact_start AS xact_start, + x.xact_finish AS xact_finish, + x.xact_type AS xact_type, + x.total_paid AS total_paid, + x.total_owed AS total_owed, + x.balance_owed AS balance_owed, + x.last_payment_ts AS last_payment_ts, + x.last_payment_note AS last_payment_note, + x.last_payment_type AS last_payment_type, + x.last_billing_ts AS last_billing_ts, + x.last_billing_note AS last_billing_note, + x.last_billing_type AS last_billing_type, + paddr.county AS patron_county, + paddr.city AS patron_city, + paddr.post_code AS patron_zip, + g.name AS profile_group, + dem.general_division AS demographic_general_division + FROM money.open_billable_xact_summary x + JOIN actor.org_unit bl ON (x.billing_location = bl.id) + JOIN actor.usr u ON (u.id = x.usr) + JOIN actor.org_unit ul ON (u.home_ou = ul.id) + JOIN actor.card c ON (u.card = c.id) + JOIN permission.grp_tree g ON (u.profile = g.id) + JOIN reporter.demographic dem ON (dem.id = u.id) + JOIN actor.usr_address paddr ON (paddr.id = u.billing_address); +END IF; + +END $$; + COMMIT; -- 2.11.0