From 420ef4ddcacf173f58d44acea76d4c58c053c136 Mon Sep 17 00:00:00 2001 From: Bill Erickson Date: Fri, 23 Oct 2015 16:07:04 -0400 Subject: [PATCH] JBAS-944 2.5 -> 2.7 SQL recreating dropped views Signed-off-by: Bill Erickson --- KCLS/sql/schema/deploy/2.5-to-2.7-upgrade.sql | 168 ++++++++++++++++++-------- 1 file changed, 120 insertions(+), 48 deletions(-) diff --git a/KCLS/sql/schema/deploy/2.5-to-2.7-upgrade.sql b/KCLS/sql/schema/deploy/2.5-to-2.7-upgrade.sql index 000d45fde8..d1b4822c79 100644 --- a/KCLS/sql/schema/deploy/2.5-to-2.7-upgrade.sql +++ b/KCLS/sql/schema/deploy/2.5-to-2.7-upgrade.sql @@ -2969,6 +2969,9 @@ INSERT INTO metabib.record_attr_vector_list (source,vlist) GROUP BY 1; DROP VIEW IF EXISTS reporter.classic_current_circ; +DROP VIEW IF EXISTS stats.fleshed_call_number; +DROP VIEW IF EXISTS stats.fleshed_circulation; +DROP VIEW IF EXISTS stats.fleshed_copy; DROP VIEW metabib.rec_descriptor; DROP TABLE metabib.record_attr; @@ -3017,6 +3020,107 @@ CREATE VIEW metabib.rec_descriptor AS (populate_record(NULL::metabib.rec_desc_type, attrs)).* FROM metabib.record_attr; +-- recreate as-is +CREATE VIEW stats.fleshed_call_number AS + SELECT cn.id, cn.creator, cn.create_date, cn.editor, cn.edit_date, cn.record, + cn.owning_lib, cn.label, cn.deleted, cn.label_class, cn.label_sortkey, + cn.prefix, cn.suffix, cn.create_date::date AS create_date_day, + cn.edit_date::date AS edit_date_day, + date_trunc('hour'::text, cn.create_date) AS create_date_hour, + date_trunc('hour'::text, cn.edit_date) AS edit_date_hour, rd.item_lang, + rd.item_type, rd.item_form + FROM asset.call_number cn + JOIN metabib.rec_descriptor rd ON rd.record = cn.record; + +-- recreate as-is +CREATE VIEW stats.fleshed_copy AS + SELECT cp.id, cp.circ_lib, cp.creator, cp.call_number, cp.editor, + cp.create_date, cp.edit_date, cp.copy_number, cp.status, cp.location, + cp.loan_duration, cp.fine_level, cp.age_protect, cp.circulate, cp.deposit, + cp.ref, cp.holdable, cp.deposit_amount, cp.price, cp.barcode, + cp.circ_modifier, cp.circ_as_type, cp.dummy_title, cp.dummy_author, + cp.alert_message, cp.opac_visible, cp.deleted, cp.floating, cp.dummy_isbn, + cp.status_changed_time, cp.mint_condition, cp.cost, + cp.create_date::date AS create_date_day, + cp.edit_date::date AS edit_date_day, + date_trunc('hour'::text, cp.create_date) AS create_date_hour, + date_trunc('hour'::text, cp.edit_date) AS edit_date_hour, + cn.label AS call_number_label, cn.owning_lib, rd.item_lang, rd.item_type, + rd.item_form + FROM asset.copy cp + JOIN asset.call_number cn ON cp.call_number = cn.id + JOIN metabib.rec_descriptor rd ON rd.record = cn.record; + +-- recreate as-is +CREATE VIEW stats.fleshed_circulation AS + SELECT c.id, c.usr, c.xact_start, c.xact_finish, c.unrecovered, c.target_copy, + c.circ_lib, c.circ_staff, c.checkin_staff, c.checkin_lib, + c.renewal_remaining, c.due_date, c.stop_fines_time, c.checkin_time, + c.create_time, c.duration, c.fine_interval, c.recurring_fine, c.max_fine, + c.phone_renewal, c.desk_renewal, c.opac_renewal, c.duration_rule, + c.recurring_fine_rule, c.max_fine_rule, c.stop_fines, c.workstation, + c.checkin_workstation, c.checkin_scan_time, c.parent_circ, c.grace_period, + c.xact_start::date AS start_date_day, + c.xact_finish::date AS finish_date_day, + date_trunc('hour'::text, c.xact_start) AS start_date_hour, + date_trunc('hour'::text, c.xact_finish) AS finish_date_hour, + cp.call_number_label, cp.owning_lib, cp.item_lang, cp.item_type, + cp.item_form + FROM action.circulation c + JOIN stats.fleshed_copy cp ON cp.id = c.target_copy; + +CREATE VIEW reporter.classic_current_circ AS + SELECT cl.shortname AS circ_lib, cl.id AS circ_lib_id, circ.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, + 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) ~ '^[0-9.]+$'::text THEN btrim(to_char(10::double precision * floor(call_number_dewey(cn.label)::double precision / 10::double precision), '000'::text)) + ELSE NULL::text + END AS dewey_block_tens, + CASE + WHEN call_number_dewey(cn.label) ~ '^[0-9.]+$'::text THEN btrim(to_char(100::double precision * floor(call_number_dewey(cn.label)::double precision / 100::double precision), '000'::text)) + ELSE NULL::text + END AS dewey_block_hundreds, + CASE + WHEN call_number_dewey(cn.label) ~ '^[0-9.]+$'::text THEN (btrim(to_char(10::double precision * floor(call_number_dewey(cn.label)::double precision / 10::double precision), '000'::text)) || '-'::text) || btrim(to_char(10::double precision * floor(call_number_dewey(cn.label)::double precision / 10::double precision) + 9::double precision, '000'::text)) + ELSE NULL::text + END AS dewey_range_tens, + CASE + WHEN call_number_dewey(cn.label) ~ '^[0-9.]+$'::text THEN (btrim(to_char(100::double precision * floor(call_number_dewey(cn.label)::double precision / 100::double precision), '000'::text)) || '-'::text) || btrim(to_char(100::double precision * floor(call_number_dewey(cn.label)::double precision / 100::double precision) + 99::double precision, '000'::text)) + ELSE NULL::text + 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; + CREATE OR REPLACE FUNCTION metabib.compile_composite_attr_cache_init () RETURNS BOOL AS $f$ $_SHARED{metabib_compile_composite_attr_cache} = {} if ! exists $_SHARED{metabib_compile_composite_attr_cache}; @@ -4033,6 +4137,7 @@ INSERT INTO config.composite_attr_entry_definition +/* KCLS alread has this SELECT evergreen.upgrade_deps_block_check('0869', :eg_version); CREATE OR REPLACE FUNCTION action.hold_copy_calculated_proximity_update () RETURNS TRIGGER AS $f$ @@ -4042,10 +4147,12 @@ BEGIN END; $f$ LANGUAGE PLPGSQL; -CREATE TRIGGER hold_copy_proximity_update_tgr BEFORE INSERT OR UPDATE ON action.hold_copy_map FOR EACH ROW EXECUTE PROCEDURE action.hold_copy_calculated_proximity_update (); +CREATE TRIGGER hold_copy_proximity_update_tgr BEFORE INSERT OR UPDATE ON + action.hold_copy_map FOR EACH ROW EXECUTE PROCEDURE action.hold_copy_calculated_proximity_update (); -- Now, cause the update we need in a HOT-friendly manner (http://pgsql.tapoueh.org/site/html/misc/hot.html) UPDATE action.hold_copy_map SET proximity = proximity WHERE proximity IS NULL; +*/ /* @@ -7474,15 +7581,12 @@ CREATE OR REPLACE FUNCTION unapi.mmr_mra ( ) $F$ LANGUAGE SQL STABLE; - - SELECT evergreen.upgrade_deps_block_check('0899', :eg_version); ALTER FUNCTION asset.label_normalizer_generic(TEXT) IMMUTABLE; ALTER FUNCTION asset.label_normalizer_dewey(TEXT) IMMUTABLE; ALTER FUNCTION asset.label_normalizer_lc(TEXT) IMMUTABLE; - SELECT evergreen.upgrade_deps_block_check('0900', :eg_version); CREATE OR REPLACE VIEW metabib.record_attr AS @@ -7491,14 +7595,6 @@ CREATE OR REPLACE VIEW metabib.record_attr AS WHERE attr IS NOT NULL GROUP BY 1; - - --- Include helpful note to run 2.6.2-2.6.3 if missed during 2.6-2.7 upgrade -\qecho **** NOTICE **** -\qecho 'There was a missed upgrade script on the path from 2.6 series' -\qecho 'to 2.7 series. If you are upgrading from 2.7.1 and have not' -\qecho 'already run 2.6.2-2.6.3-upgrade-db.sql script, please go back' -\qecho 'and run now to receive those missed fixes.' --Upgrade Script for 2.7.2 to 2.7.3 \set eg_version '''2.7.3''' INSERT INTO config.upgrade_log (version, applied_to) VALUES ('2.7.3', :eg_version); @@ -8195,41 +8291,6 @@ SELECT evergreen.upgrade_deps_block_check('0906', :eg_version); ALTER FUNCTION evergreen.z3950_attr_name_is_valid (TEXT) STABLE; - --- These need to happen outside of the transaction to avoid this: --- ERROR: cannot ALTER TABLE "record_entry" because it has pending trigger --- events -ALTER TABLE authority.record_entry ENABLE TRIGGER a_marcxml_is_well_formed; -ALTER TABLE authority.record_entry ENABLE TRIGGER aaa_auth_ingest_or_delete; -ALTER TABLE authority.record_entry ENABLE TRIGGER b_maintain_901; -ALTER TABLE authority.record_entry ENABLE TRIGGER c_maintain_control_numbers; -ALTER TABLE authority.record_entry ENABLE TRIGGER map_thesaurus_to_control_set; - -\qecho -\qecho -\qecho **** Certain improvements in this upgrade series require a partial reingest of -\qecho **** your bib records. In order to allow this to continue without locking -\qecho **** your entire bibliographic data set, consider generating an SQL script -\qecho **** with the following queries: -\qecho -\qecho -\qecho '\\t' -\qecho '\\o /tmp/partial_reingest_bib_recs.sql' -\qecho 'SELECT ''select metabib.reingest_record_attributes('' || id || '');'' FROM biblio.record_entry WHERE NOT DELETED AND id > 0;' -\qecho '\\o' -\qecho '\\t' -\qecho -\qecho -\qecho **** then running it via psql: -\qecho -\qecho -\qecho '\\i /tmp/partial_reingest_bib_recs.sql' -\qecho -\qecho -\qecho **** If you require a more responsive catalog/database while reingesting, -\qecho **** consider adding 'pg_sleep()' calls between each reingest select or -\qecho **** update. ---Upgrade Script for 2.7.3 to 2.7.4 \set eg_version '''2.7.4''' INSERT INTO config.upgrade_log (version, applied_to) VALUES ('2.7.4', :eg_version); @@ -9230,5 +9291,16 @@ UPDATE authority.control_set_authority_field SET sf_list = REGEXP_REPLACE( sf_li UPDATE authority.control_set_authority_field SET sf_list = REGEXP_REPLACE( sf_list, 'e', '', 'i') WHERE tag = '110' AND control_set = 1 AND sf_list ILIKE '%e%'; -ROLLBACK +ROLLBACK; --COMMIT; + +-- These need to happen outside of the transaction to avoid this: +-- ERROR: cannot ALTER TABLE "record_entry" because it has pending trigger +-- events +ALTER TABLE authority.record_entry ENABLE TRIGGER a_marcxml_is_well_formed; +ALTER TABLE authority.record_entry ENABLE TRIGGER aaa_auth_ingest_or_delete; +ALTER TABLE authority.record_entry ENABLE TRIGGER b_maintain_901; +ALTER TABLE authority.record_entry ENABLE TRIGGER c_maintain_control_numbers; +ALTER TABLE authority.record_entry ENABLE TRIGGER map_thesaurus_to_control_set; + + -- 2.11.0