From ea647de07c19a724aabd6fa6fc667c2b9c5f1036 Mon Sep 17 00:00:00 2001 From: Galen Charlton Date: Thu, 21 Jul 2011 10:06:20 -0400 Subject: [PATCH] bring 2.0-2.1 upgrade SQL up to rev 0583 Signed-off-by: Galen Charlton --- Open-ILS/src/sql/Pg/2.0-2.1-upgrade-db.sql | 3681 ++++++++++++++++++++++++++++ 1 file changed, 3681 insertions(+) diff --git a/Open-ILS/src/sql/Pg/2.0-2.1-upgrade-db.sql b/Open-ILS/src/sql/Pg/2.0-2.1-upgrade-db.sql index d1d3a51569..7b9b781116 100644 --- a/Open-ILS/src/sql/Pg/2.0-2.1-upgrade-db.sql +++ b/Open-ILS/src/sql/Pg/2.0-2.1-upgrade-db.sql @@ -5992,6 +5992,3687 @@ BEGIN END; $func$ LANGUAGE plpgsql; +INSERT INTO config.upgrade_log (version) VALUES ('0535'); --dbs + +CREATE INDEX authority_record_deleted_idx ON authority.record_entry(deleted) WHERE deleted IS FALSE OR deleted = false; + +CREATE INDEX authority_full_rec_subfield_a_idx ON authority.full_rec (value) WHERE subfield = 'a'; + +INSERT INTO config.upgrade_log (version) VALUES ('0538'); -- senator + +UPDATE action_trigger.event_definition +SET template = '[% FILTER collapse %]' || template +WHERE id = 22 AND + SUBSTR(template, 0, 24) NOT LIKE '%FILTER collapse%'; + + +-- Evergreen DB patch 0540.schema.missing_serial_unit_triggers.sql +-- +-- Bring serial.unit into line with asset.copy +-- + + +-- check whether patch can be applied +INSERT INTO config.upgrade_log (version) VALUES ('0540'); -- dbwells + +CREATE TRIGGER sunit_status_changed_trig + BEFORE UPDATE ON serial.unit + FOR EACH ROW EXECUTE PROCEDURE asset.acp_status_changed(); + +SELECT auditor.create_auditor ( 'serial', 'unit' ); +CREATE INDEX aud_serial_unit_hist_creator_idx ON auditor.serial_unit_history ( creator ); +CREATE INDEX aud_serial_unit_hist_editor_idx ON auditor.serial_unit_history ( editor ); + +INSERT INTO config.upgrade_log (version) VALUES ('0541'); -- dbwells + +ALTER TABLE asset.call_number ALTER COLUMN label_class DROP DEFAULT; + +CREATE OR REPLACE FUNCTION asset.label_normalizer() RETURNS TRIGGER AS $func$ +DECLARE + sortkey TEXT := ''; +BEGIN + sortkey := NEW.label_sortkey; + + IF NEW.label_class IS NULL THEN + NEW.label_class := COALESCE( + ( + SELECT substring(value from E'\\d+')::integer + FROM actor.org_unit_setting + WHERE name = 'cat.default_classification_scheme' + AND org_unit = NEW.owning_lib + ), 1 + ); + END IF; + + EXECUTE 'SELECT ' || acnc.normalizer || '(' || + quote_literal( NEW.label ) || ')' + FROM asset.call_number_class acnc + WHERE acnc.id = NEW.label_class + INTO sortkey; + NEW.label_sortkey = sortkey; + RETURN NEW; +END; +$func$ LANGUAGE PLPGSQL; + +INSERT INTO config.upgrade_log (version) VALUES ('0542'); -- phasefx + +INSERT INTO permission.perm_list VALUES + (485, 'CREATE_VOLUME_SUFFIX', oils_i18n_gettext(485, 'Create suffix label definition.', 'ppl', 'description')) + ,(486, 'UPDATE_VOLUME_SUFFIX', oils_i18n_gettext(486, 'Update suffix label definition.', 'ppl', 'description')) + ,(487, 'DELETE_VOLUME_SUFFIX', oils_i18n_gettext(487, 'Delete suffix label definition.', 'ppl', 'description')) + ,(488, 'CREATE_VOLUME_PREFIX', oils_i18n_gettext(488, 'Create prefix label definition.', 'ppl', 'description')) + ,(489, 'UPDATE_VOLUME_PREFIX', oils_i18n_gettext(489, 'Update prefix label definition.', 'ppl', 'description')) + ,(490, 'DELETE_VOLUME_PREFIX', oils_i18n_gettext(490, 'Delete prefix label definition.', 'ppl', 'description')) + ,(491, 'CREATE_MONOGRAPH_PART', oils_i18n_gettext(491, 'Create monograph part definition.', 'ppl', 'description')) + ,(492, 'UPDATE_MONOGRAPH_PART', oils_i18n_gettext(492, 'Update monograph part definition.', 'ppl', 'description')) + ,(493, 'DELETE_MONOGRAPH_PART', oils_i18n_gettext(493, 'Delete monograph part definition.', 'ppl', 'description')) + ,(494, 'ADMIN_CODED_VALUE', oils_i18n_gettext(494, 'Create/Update/Delete SVF Record Attribute Coded Value Map', 'ppl', 'description')) + ,(495, 'ADMIN_SERIAL_ITEM', oils_i18n_gettext(495, 'Create/Retrieve/Update/Delete Serial Item', 'ppl', 'description')) + ,(496, 'ADMIN_SVF', oils_i18n_gettext(496, 'Create/Update/Delete SVF Record Attribute Defintion', 'ppl', 'description')) + ,(497, 'CREATE_BIB_PTYPE', oils_i18n_gettext(497, 'Create Bibliographic Record Peer Type', 'ppl', 'description')) + ,(498, 'CREATE_PURCHASE_REQUEST', oils_i18n_gettext(498, 'Create User Purchase Request', 'ppl', 'description')) + ,(499, 'DELETE_BIB_PTYPE', oils_i18n_gettext(499, 'Delete Bibliographic Record Peer Type', 'ppl', 'description')) + ,(500, 'MAP_MONOGRAPH_PART', oils_i18n_gettext(500, 'Create/Update/Delete Copy Monograph Part Map', 'ppl', 'description')) + ,(501, 'MARK_ITEM_MISSING_PIECES', oils_i18n_gettext(501, 'Allows the Mark Item Missing Pieces action.', 'ppl', 'description')) + ,(502, 'UPDATE_BIB_PTYPE', oils_i18n_gettext(502, 'Update Bibliographic Record Peer Type', 'ppl', 'description')) + ,(503, 'UPDATE_HOLD_REQUEST_TIME', oils_i18n_gettext(503, 'Allows editing of a hold''s request time, and/or its Cut-in-line/Top-of-queue flag.', 'ppl', 'description')) + ,(504, 'UPDATE_PICKLIST', oils_i18n_gettext(504, 'Allows update/re-use of an acquisitions pick/selection list.', 'ppl', 'description')) + ,(505, 'UPDATE_WORKSTATION', oils_i18n_gettext(505, 'Allows update of a workstation during workstation registration override.', 'ppl', 'description')) + ,(506, 'VIEW_USER_SETTING_TYPE', oils_i18n_gettext(506, 'Allows viewing of configurable user setting types.', 'ppl', 'description')) +; + +-- add new perms AND catch up on some missed upgrade data, if needed + +-- we could get away from these fixed-id inserts here, but then this +-- upgrade would be ahead of the mainline, I think + +INSERT INTO permission.grp_tree (id, name, parent, description, perm_interval, usergroup, application_perm) + SELECT 8, oils_i18n_gettext(8, 'Cataloging Administrator', 'pgt', 'name'), 3, NULL, '3 years', TRUE, 'group_application.user.staff.cat_admin' + WHERE NOT EXISTS ( + SELECT 1 + FROM permission.grp_tree + WHERE + id = 8 + ); + +INSERT INTO permission.grp_tree (id, name, parent, description, perm_interval, usergroup, application_perm) + SELECT 9, oils_i18n_gettext(9, 'Circulation Administrator', 'pgt', 'name'), 3, NULL, '3 years', TRUE, 'group_application.user.staff.circ_admin' + WHERE NOT EXISTS ( + SELECT 1 + FROM permission.grp_tree + WHERE + id = 9 + ); + +UPDATE permission.grp_tree SET description = oils_i18n_gettext(10, 'Can do anything at the Branch level', 'pgt', 'description') WHERE id = 10; + +INSERT INTO permission.grp_tree (id, name, parent, description, perm_interval, usergroup, application_perm) + SELECT 11, oils_i18n_gettext(11, 'Serials', 'pgt', 'name'), 3, oils_i18n_gettext(11, 'Serials (includes admin features)', 'pgt', 'description'), '3 years', TRUE, 'group_application.user.staff.serials' + WHERE NOT EXISTS ( + SELECT 1 + FROM permission.grp_tree + WHERE + id = 11 + ); + +INSERT INTO permission.grp_tree (id, name, parent, description, perm_interval, usergroup, application_perm) + SELECT 12, oils_i18n_gettext(12, 'System Administrator', 'pgt', 'name'), 3, oils_i18n_gettext(12, 'Can do anything at the System level', 'pgt', 'description'), '3 years', TRUE, 'group_application.user.staff.admin.system_admin' + WHERE NOT EXISTS ( + SELECT 1 + FROM permission.grp_tree + WHERE + id = 12 + ); + +INSERT INTO permission.grp_tree (id, name, parent, description, perm_interval, usergroup, application_perm) + SELECT 13, oils_i18n_gettext(13, 'Global Administrator', 'pgt', 'name'), 3, oils_i18n_gettext(13, 'Can do anything at the Consortium level', 'pgt', 'description'), '3 years', TRUE, 'group_application.user.staff.admin.global_admin' + WHERE NOT EXISTS ( + SELECT 1 + FROM permission.grp_tree + WHERE + id = 13 + ); + +INSERT INTO permission.grp_tree (id, name, parent, description, perm_interval, usergroup, application_perm) + SELECT 14, oils_i18n_gettext(14, 'Data Review', 'pgt', 'name'), 3, NULL, '3 years', TRUE, 'group_application.user.staff.data_review' + WHERE NOT EXISTS ( + SELECT 1 + FROM permission.grp_tree + WHERE + id = 14 + ); + +INSERT INTO permission.grp_tree (id, name, parent, description, perm_interval, usergroup, application_perm) + SELECT 15, oils_i18n_gettext(15, 'Volunteers', 'pgt', 'name'), 3, NULL, '3 years', TRUE, 'group_application.user.staff.volunteers' + WHERE NOT EXISTS ( + SELECT 1 + FROM permission.grp_tree + WHERE + id = 15 + ); + + + +INSERT INTO permission.grp_perm_map (grp, perm, depth, grantable) + SELECT + pgt.id, perm.id, aout.depth, TRUE + FROM + permission.grp_tree pgt, + permission.perm_list perm, + actor.org_unit_type aout + WHERE + pgt.name = 'Cataloging Administrator' AND + aout.name = 'Consortium' AND + perm.code IN ( + 'ADMIN_IMPORT_ITEM_ATTR_DEF', + 'ADMIN_MERGE_PROFILE', + 'CREATE_AUTHORITY_IMPORT_IMPORT_DEF', + 'CREATE_BIB_IMPORT_FIELD_DEF', + 'CREATE_BIB_PTYPE', + 'CREATE_BIB_SOURCE', + 'CREATE_IMPORT_ITEM_ATTR_DEF', + 'CREATE_IMPORT_TRASH_FIELD', + 'CREATE_MERGE_PROFILE', + 'CREATE_MONOGRAPH_PART', + 'CREATE_VOLUME_PREFIX', + 'CREATE_VOLUME_SUFFIX', + 'DELETE_AUTHORITY_IMPORT_IMPORT_FIELD_DEF', + 'DELETE_BIB_PTYPE', + 'DELETE_BIB_SOURCE', + 'DELETE_IMPORT_ITEM_ATTR_DEF', + 'DELETE_IMPORT_TRASH_FIELD', + 'DELETE_MERGE_PROFILE', + 'DELETE_MONOGRAPH_PART', + 'DELETE_VOLUME_PREFIX', + 'DELETE_VOLUME_SUFFIX', + 'MAP_MONOGRAPH_PART', + 'UPDATE_AUTHORITY_IMPORT_IMPORT_FIELD_DEF', + 'UPDATE_BIB_IMPORT_IMPORT_FIELD_DEF', + 'UPDATE_BIB_PTYPE', + 'UPDATE_IMPORT_ITEM_ATTR_DEF', + 'UPDATE_IMPORT_TRASH_FIELD', + 'UPDATE_MERGE_PROFILE', + 'UPDATE_MONOGRAPH_PART', + 'UPDATE_VOLUME_PREFIX', + 'UPDATE_VOLUME_SUFFIX' + ) AND NOT EXISTS ( + SELECT 1 + FROM permission.grp_perm_map AS map + WHERE + map.grp = pgt.id + AND map.perm = perm.id + ); + +INSERT INTO permission.grp_perm_map (grp, perm, depth, grantable) + SELECT + pgt.id, perm.id, aout.depth, TRUE + FROM + permission.grp_tree pgt, + permission.perm_list perm, + actor.org_unit_type aout + WHERE + pgt.name = 'Circulation Administrator' AND + aout.name = 'Branch' AND + perm.code IN ( + 'DELETE_USER' + ) AND NOT EXISTS ( + SELECT 1 + FROM permission.grp_perm_map AS map + WHERE + map.grp = pgt.id + AND map.perm = perm.id + ); + +INSERT INTO permission.grp_perm_map (grp, perm, depth, grantable) + SELECT + pgt.id, perm.id, aout.depth, TRUE + FROM + permission.grp_tree pgt, + permission.perm_list perm, + actor.org_unit_type aout + WHERE + pgt.name = 'Circulation Administrator' AND + aout.name = 'Consortium' AND + perm.code IN ( + 'ADMIN_MAX_FINE_RULE', + 'CREATE_CIRC_DURATION', + 'DELETE_CIRC_DURATION', + 'MARK_ITEM_MISSING_PIECES', + 'UPDATE_CIRC_DURATION', + 'UPDATE_HOLD_REQUEST_TIME', + 'UPDATE_NET_ACCESS_LEVEL', + 'VIEW_CIRC_MATRIX_MATCHPOINT', + 'VIEW_HOLD_MATRIX_MATCHPOINT' + ) AND NOT EXISTS ( + SELECT 1 + FROM permission.grp_perm_map AS map + WHERE + map.grp = pgt.id + AND map.perm = perm.id + ); + +INSERT INTO permission.grp_perm_map (grp, perm, depth, grantable) + SELECT + pgt.id, perm.id, aout.depth, TRUE + FROM + permission.grp_tree pgt, + permission.perm_list perm, + actor.org_unit_type aout + WHERE + pgt.name = 'Circulation Administrator' AND + aout.name = 'System' AND + perm.code IN ( + 'ADMIN_BOOKING_RESERVATION', + 'ADMIN_BOOKING_RESERVATION_ATTR_MAP', + 'ADMIN_BOOKING_RESERVATION_ATTR_VALUE_MAP', + 'ADMIN_BOOKING_RESOURCE', + 'ADMIN_BOOKING_RESOURCE_ATTR', + 'ADMIN_BOOKING_RESOURCE_ATTR_MAP', + 'ADMIN_BOOKING_RESOURCE_ATTR_VALUE', + 'ADMIN_BOOKING_RESOURCE_TYPE', + 'ADMIN_COPY_LOCATION_ORDER', + 'ADMIN_HOLD_CANCEL_CAUSE', + 'ASSIGN_GROUP_PERM', + 'BAR_PATRON', + 'COPY_HOLDS', + 'COPY_TRANSIT_RECEIVE', + 'CREATE_BILL', + 'CREATE_BILLING_TYPE', + 'CREATE_NON_CAT_TYPE', + 'CREATE_PATRON_STAT_CAT', + 'CREATE_PATRON_STAT_CAT_ENTRY', + 'CREATE_PATRON_STAT_CAT_ENTRY_MAP', + 'CREATE_USER_GROUP_LINK', + 'DELETE_BILLING_TYPE', + 'DELETE_NON_CAT_TYPE', + 'DELETE_PATRON_STAT_CAT', + 'DELETE_PATRON_STAT_CAT_ENTRY', + 'DELETE_PATRON_STAT_CAT_ENTRY_MAP', + 'DELETE_TRANSIT', + 'group_application.user.staff', + 'MANAGE_BAD_DEBT', + 'MARK_ITEM_AVAILABLE', + 'MARK_ITEM_BINDERY', + 'MARK_ITEM_CHECKED_OUT', + 'MARK_ITEM_ILL', + 'MARK_ITEM_IN_PROCESS', + 'MARK_ITEM_IN_TRANSIT', + 'MARK_ITEM_LOST', + 'MARK_ITEM_MISSING', + 'MARK_ITEM_ON_HOLDS_SHELF', + 'MARK_ITEM_ON_ORDER', + 'MARK_ITEM_RESHELVING', + 'MERGE_USERS', + 'money.collections_tracker.create', + 'money.collections_tracker.delete', + 'OFFLINE_EXECUTE', + 'OFFLINE_UPLOAD', + 'OFFLINE_VIEW', + 'REMOVE_USER_GROUP_LINK', + 'SET_CIRC_CLAIMS_RETURNED', + 'SET_CIRC_CLAIMS_RETURNED.override', + 'SET_CIRC_LOST', + 'SET_CIRC_MISSING', + 'UNBAR_PATRON', + 'UPDATE_BILL_NOTE', + 'UPDATE_NON_CAT_TYPE', + 'UPDATE_PATRON_CLAIM_NEVER_CHECKED_OUT_COUNT', + 'UPDATE_PATRON_CLAIM_RETURN_COUNT', + 'UPDATE_PICKUP_LIB_FROM_HOLDS_SHELF', + 'UPDATE_PICKUP_LIB_FROM_TRANSIT', + 'UPDATE_USER', + 'VIEW_REPORT_OUTPUT', + 'VIEW_STANDING_PENALTY', + 'VOID_BILLING', + 'VOLUME_HOLDS' + ) AND NOT EXISTS ( + SELECT 1 + FROM permission.grp_perm_map AS map + WHERE + map.grp = pgt.id + AND map.perm = perm.id + ); + +INSERT INTO permission.grp_perm_map (grp, perm, depth, grantable) + SELECT + pgt.id, perm.id, aout.depth, TRUE + FROM + permission.grp_tree pgt, + permission.perm_list perm, + actor.org_unit_type aout + WHERE + pgt.name = 'Local Administrator' AND + aout.name = 'Branch' AND + perm.code IN ( + 'EVERYTHING' + ) AND NOT EXISTS ( + SELECT 1 + FROM permission.grp_perm_map AS map + WHERE + map.grp = pgt.id + AND map.perm = perm.id + ); + +INSERT INTO permission.grp_perm_map (grp, perm, depth, grantable) + SELECT + pgt.id, perm.id, aout.depth, FALSE + FROM + permission.grp_tree pgt, + permission.perm_list perm, + actor.org_unit_type aout + WHERE + pgt.name = 'Serials' AND + aout.name = 'System' AND + perm.code IN ( + 'ADMIN_ASSET_COPY_TEMPLATE', + 'ADMIN_SERIAL_CAPTION_PATTERN', + 'ADMIN_SERIAL_DISTRIBUTION', + 'ADMIN_SERIAL_ITEM', + 'ADMIN_SERIAL_STREAM', + 'ADMIN_SERIAL_SUBSCRIPTION', + 'ISSUANCE_HOLDS', + 'RECEIVE_SERIAL' + ) AND NOT EXISTS ( + SELECT 1 + FROM permission.grp_perm_map AS map + WHERE + map.grp = pgt.id + AND map.perm = perm.id + ); + +INSERT INTO permission.grp_perm_map (grp, perm, depth, grantable) + SELECT + pgt.id, perm.id, aout.depth, TRUE + FROM + permission.grp_tree pgt, + permission.perm_list perm, + actor.org_unit_type aout + WHERE + pgt.name = 'System Administrator' AND + aout.name = 'System' AND + perm.code IN ( + 'EVERYTHING' + ) AND NOT EXISTS ( + SELECT 1 + FROM permission.grp_perm_map AS map + WHERE + map.grp = pgt.id + AND map.perm = perm.id + ); + +INSERT INTO permission.grp_perm_map (grp, perm, depth, grantable) + SELECT + pgt.id, perm.id, aout.depth, FALSE + FROM + permission.grp_tree pgt, + permission.perm_list perm, + actor.org_unit_type aout + WHERE + pgt.name = 'System Administrator' AND + aout.name = 'Consortium' AND + perm.code ~ '^VIEW_TRIGGER' + AND NOT EXISTS ( + SELECT 1 + FROM permission.grp_perm_map AS map + WHERE + map.grp = pgt.id + AND map.perm = perm.id + ); + +INSERT INTO permission.grp_perm_map (grp, perm, depth, grantable) + SELECT + pgt.id, perm.id, aout.depth, TRUE + FROM + permission.grp_tree pgt, + permission.perm_list perm, + actor.org_unit_type aout + WHERE + pgt.name = 'Global Administrator' AND + aout.name = 'Consortium' AND + perm.code IN ( + 'EVERYTHING' + ) AND NOT EXISTS ( + SELECT 1 + FROM permission.grp_perm_map AS map + WHERE + map.grp = pgt.id + AND map.perm = perm.id + ); + +INSERT INTO permission.grp_perm_map (grp, perm, depth, grantable) + SELECT + pgt.id, perm.id, aout.depth, FALSE + FROM + permission.grp_tree pgt, + permission.perm_list perm, + actor.org_unit_type aout + WHERE + pgt.name = 'Data Review' AND + aout.name = 'Consortium' AND + perm.code IN ( + 'CREATE_COPY_TRANSIT', + 'VIEW_BILLING_TYPE', + 'VIEW_CIRCULATIONS', + 'VIEW_COPY_NOTES', + 'VIEW_HOLD', + 'VIEW_ORG_SETTINGS', + 'VIEW_TITLE_NOTES', + 'VIEW_TRANSACTION', + 'VIEW_USER', + 'VIEW_USER_FINES_SUMMARY', + 'VIEW_USER_TRANSACTIONS', + 'VIEW_VOLUME_NOTES', + 'VIEW_ZIP_DATA' + ) AND NOT EXISTS ( + SELECT 1 + FROM permission.grp_perm_map AS map + WHERE + map.grp = pgt.id + AND map.perm = perm.id + ); + +INSERT INTO permission.grp_perm_map (grp, perm, depth, grantable) + SELECT + pgt.id, perm.id, aout.depth, FALSE + FROM + permission.grp_tree pgt, + permission.perm_list perm, + actor.org_unit_type aout + WHERE + pgt.name = 'Data Review' AND + aout.name = 'System' AND + perm.code IN ( + 'COPY_CHECKOUT', + 'COPY_HOLDS', + 'CREATE_IN_HOUSE_USE', + 'CREATE_TRANSACTION', + 'OFFLINE_EXECUTE', + 'OFFLINE_VIEW', + 'STAFF_LOGIN', + 'VOLUME_HOLDS' + ) AND NOT EXISTS ( + SELECT 1 + FROM permission.grp_perm_map AS map + WHERE + map.grp = pgt.id + AND map.perm = perm.id + ); + +INSERT INTO permission.grp_perm_map (grp, perm, depth, grantable) + SELECT + pgt.id, perm.id, aout.depth, FALSE + FROM + permission.grp_tree pgt, + permission.perm_list perm, + actor.org_unit_type aout + WHERE + pgt.name = 'Volunteers' AND + aout.name = 'Branch' AND + perm.code IN ( + 'COPY_CHECKOUT', + 'CREATE_BILL', + 'CREATE_IN_HOUSE_USE', + 'CREATE_PAYMENT', + 'VIEW_BILLING_TYPE', + 'VIEW_CIRCS', + 'VIEW_COPY_CHECKOUT', + 'VIEW_HOLD', + 'VIEW_TITLE_HOLDS', + 'VIEW_TRANSACTION', + 'VIEW_USER', + 'VIEW_USER_FINES_SUMMARY', + 'VIEW_USER_TRANSACTIONS' + ) AND NOT EXISTS ( + SELECT 1 + FROM permission.grp_perm_map AS map + WHERE + map.grp = pgt.id + AND map.perm = perm.id + ); + +INSERT INTO permission.grp_perm_map (grp, perm, depth, grantable) + SELECT + pgt.id, perm.id, aout.depth, FALSE + FROM + permission.grp_tree pgt, + permission.perm_list perm, + actor.org_unit_type aout + WHERE + pgt.name = 'Volunteers' AND + aout.name = 'Consortium' AND + perm.code IN ( + 'CREATE_COPY_TRANSIT', + 'CREATE_TRANSACTION', + 'CREATE_TRANSIT', + 'STAFF_LOGIN', + 'TRANSIT_COPY', + 'VIEW_ORG_SETTINGS' + ) AND NOT EXISTS ( + SELECT 1 + FROM permission.grp_perm_map AS map + WHERE + map.grp = pgt.id + AND map.perm = perm.id + ); + + +-- stock Users group +INSERT INTO permission.grp_perm_map (grp, perm, depth, grantable) + SELECT + pgt.id, perm.id, aout.depth, FALSE + FROM + permission.grp_tree pgt, + permission.perm_list perm, + actor.org_unit_type aout + WHERE + pgt.name = 'Users' AND + aout.name = 'Consortium' AND + perm.code IN ( + 'CREATE_PURCHASE_REQUEST' + ) AND NOT EXISTS ( + SELECT 1 + FROM permission.grp_perm_map AS map + WHERE + map.grp = pgt.id + AND map.perm = perm.id + ); + +-- stock Staff group +INSERT INTO permission.grp_perm_map (grp, perm, depth, grantable) + SELECT + pgt.id, perm.id, aout.depth, FALSE + FROM + permission.grp_tree pgt, + permission.perm_list perm, + actor.org_unit_type aout + WHERE + pgt.name = 'Staff' AND + aout.name = 'Consortium' AND + perm.code IN ( + 'VIEW_USER_SETTING_TYPE' + ) AND NOT EXISTS ( + SELECT 1 + FROM permission.grp_perm_map AS map + WHERE + map.grp = pgt.id + AND map.perm = perm.id + ); + +-- stock Circulators group +INSERT INTO permission.grp_perm_map (grp, perm, depth, grantable) + SELECT + pgt.id, perm.id, aout.depth, FALSE + FROM + permission.grp_tree pgt, + permission.perm_list perm, + actor.org_unit_type aout + WHERE + pgt.name = 'Circulators' AND + aout.name = 'Branch' AND + perm.code IN ( + 'MARK_ITEM_MISSING_PIECES' + ) AND NOT EXISTS ( + SELECT 1 + FROM permission.grp_perm_map AS map + WHERE + map.grp = pgt.id + AND map.perm = perm.id + ); + +-- stock Catalogers group +INSERT INTO permission.grp_perm_map (grp, perm, depth, grantable) + SELECT + pgt.id, perm.id, aout.depth, FALSE + FROM + permission.grp_tree pgt, + permission.perm_list perm, + actor.org_unit_type aout + WHERE + pgt.name = 'Catalogers' AND + aout.name = 'System' AND + perm.code IN ( + 'MAP_MONOGRAPH_PART' + ) AND NOT EXISTS ( + SELECT 1 + FROM permission.grp_perm_map AS map + WHERE + map.grp = pgt.id + AND map.perm = perm.id + ); + +-- stock Acquisitions group +INSERT INTO permission.grp_perm_map (grp, perm, depth, grantable) + SELECT + pgt.id, perm.id, aout.depth, FALSE + FROM + permission.grp_tree pgt, + permission.perm_list perm, + actor.org_unit_type aout + WHERE + pgt.name = 'Acquisitions' AND + aout.name = 'Consortium' AND + perm.code IN ( + 'UPDATE_PICKLIST' + ) AND NOT EXISTS ( + SELECT 1 + FROM permission.grp_perm_map AS map + WHERE + map.grp = pgt.id + AND map.perm = perm.id + ); + +-- stock Acq Admin group +INSERT INTO permission.grp_perm_map (grp, perm, depth, grantable) + SELECT + pgt.id, perm.id, aout.depth, TRUE + FROM + permission.grp_tree pgt, + permission.perm_list perm, + actor.org_unit_type aout + WHERE + pgt.name = 'Acquisitions Administrator' AND + aout.name = 'Consortium' AND + perm.code IN ( + 'UPDATE_PICKLIST' + ) AND NOT EXISTS ( + SELECT 1 + FROM permission.grp_perm_map AS map + WHERE + map.grp = pgt.id + AND map.perm = perm.id + ); + + + +-- Reformat generated_coverage to be JSON arrays rather than simple comma- +-- separated lists. + +-- This upgrade script is technically imperfect, but should do the right thing +-- in 99.9% of cases, and any mistakes will be self-healing as more serials +-- activity happens + +INSERT INTO config.upgrade_log (version) VALUES ('0543'); -- dbwells + +UPDATE serial.basic_summary SET generated_coverage = '["' || regexp_replace(regexp_replace(generated_coverage, '"', E'\\"', 'g'), ', ', '","', 'g') || '"]' WHERE generated_coverage <> ''; + +UPDATE serial.supplement_summary SET generated_coverage = '["' || regexp_replace(regexp_replace(generated_coverage, '"', E'\\"', 'g'), ', ', '","', 'g') || '"]' WHERE generated_coverage <> ''; + +UPDATE serial.index_summary SET generated_coverage = '["' || regexp_replace(regexp_replace(generated_coverage, '"', E'\\"', 'g'), ', ', '","', 'g') || '"]' WHERE generated_coverage <> ''; + + + +INSERT INTO config.upgrade_log (version) VALUES ('0547'); -- dbwells + +-- account for spelling errors (Admin != Administrator) +\qecho This might not insert much if you passed through 0542 on your way here, +\qecho but one group was missed there as well + +INSERT INTO permission.grp_perm_map (grp, perm, depth, grantable) + SELECT + pgt.id, perm.id, aout.depth, TRUE + FROM + permission.grp_tree pgt, + permission.perm_list perm, + actor.org_unit_type aout + WHERE + pgt.name = 'Cataloging Administrator' AND + aout.name = 'Consortium' AND + perm.code IN ( + 'ADMIN_IMPORT_ITEM_ATTR_DEF', + 'ADMIN_MERGE_PROFILE', + 'CREATE_AUTHORITY_IMPORT_IMPORT_DEF', + 'CREATE_BIB_IMPORT_FIELD_DEF', + 'CREATE_BIB_PTYPE', + 'CREATE_BIB_SOURCE', + 'CREATE_IMPORT_ITEM_ATTR_DEF', + 'CREATE_IMPORT_TRASH_FIELD', + 'CREATE_MERGE_PROFILE', + 'CREATE_MONOGRAPH_PART', + 'CREATE_VOLUME_PREFIX', + 'CREATE_VOLUME_SUFFIX', + 'DELETE_AUTHORITY_IMPORT_IMPORT_FIELD_DEF', + 'DELETE_BIB_PTYPE', + 'DELETE_BIB_SOURCE', + 'DELETE_IMPORT_ITEM_ATTR_DEF', + 'DELETE_IMPORT_TRASH_FIELD', + 'DELETE_MERGE_PROFILE', + 'DELETE_MONOGRAPH_PART', + 'DELETE_VOLUME_PREFIX', + 'DELETE_VOLUME_SUFFIX', + 'MAP_MONOGRAPH_PART', + 'UPDATE_AUTHORITY_IMPORT_IMPORT_FIELD_DEF', + 'UPDATE_BIB_IMPORT_IMPORT_FIELD_DEF', + 'UPDATE_BIB_PTYPE', + 'UPDATE_IMPORT_ITEM_ATTR_DEF', + 'UPDATE_IMPORT_TRASH_FIELD', + 'UPDATE_MERGE_PROFILE', + 'UPDATE_MONOGRAPH_PART', + 'UPDATE_VOLUME_PREFIX', + 'UPDATE_VOLUME_SUFFIX' + ) AND NOT EXISTS ( + SELECT 1 + FROM permission.grp_perm_map AS map + WHERE + map.grp = pgt.id + AND map.perm = perm.id + ); + +INSERT INTO permission.grp_perm_map (grp, perm, depth, grantable) + SELECT + pgt.id, perm.id, aout.depth, TRUE + FROM + permission.grp_tree pgt, + permission.perm_list perm, + actor.org_unit_type aout + WHERE + pgt.name = 'Cataloging Administrator' AND + aout.name = 'System' AND + perm.code IN ( + 'CREATE_COPY_STAT_CAT', + 'CREATE_COPY_STAT_CAT_ENTRY', + 'CREATE_COPY_STAT_CAT_ENTRY_MAP', + 'RUN_REPORTS', + 'SHARE_REPORT_FOLDER', + 'UPDATE_COPY_LOCATION', + 'UPDATE_COPY_STAT_CAT', + 'UPDATE_COPY_STAT_CAT_ENTRY', + 'VIEW_REPORT_OUTPUT' + ) AND NOT EXISTS ( + SELECT 1 + FROM permission.grp_perm_map AS map + WHERE + map.grp = pgt.id + AND map.perm = perm.id + ); + +INSERT INTO permission.grp_perm_map (grp, perm, depth, grantable) + SELECT + pgt.id, perm.id, aout.depth, TRUE + FROM + permission.grp_tree pgt, + permission.perm_list perm, + actor.org_unit_type aout + WHERE + pgt.name = 'Circulation Administrator' AND + aout.name = 'Branch' AND + perm.code IN ( + 'DELETE_USER' + ) AND NOT EXISTS ( + SELECT 1 + FROM permission.grp_perm_map AS map + WHERE + map.grp = pgt.id + AND map.perm = perm.id + ); + +INSERT INTO permission.grp_perm_map (grp, perm, depth, grantable) + SELECT + pgt.id, perm.id, aout.depth, TRUE + FROM + permission.grp_tree pgt, + permission.perm_list perm, + actor.org_unit_type aout + WHERE + pgt.name = 'Circulation Administrator' AND + aout.name = 'Consortium' AND + perm.code IN ( + 'ADMIN_MAX_FINE_RULE', + 'CREATE_CIRC_DURATION', + 'DELETE_CIRC_DURATION', + 'MARK_ITEM_MISSING_PIECES', + 'UPDATE_CIRC_DURATION', + 'UPDATE_HOLD_REQUEST_TIME', + 'UPDATE_NET_ACCESS_LEVEL', + 'VIEW_CIRC_MATRIX_MATCHPOINT', + 'VIEW_HOLD_MATRIX_MATCHPOINT' + ) AND NOT EXISTS ( + SELECT 1 + FROM permission.grp_perm_map AS map + WHERE + map.grp = pgt.id + AND map.perm = perm.id + ); + +INSERT INTO permission.grp_perm_map (grp, perm, depth, grantable) + SELECT + pgt.id, perm.id, aout.depth, TRUE + FROM + permission.grp_tree pgt, + permission.perm_list perm, + actor.org_unit_type aout + WHERE + pgt.name = 'Circulation Administrator' AND + aout.name = 'System' AND + perm.code IN ( + 'ADMIN_BOOKING_RESERVATION', + 'ADMIN_BOOKING_RESERVATION_ATTR_MAP', + 'ADMIN_BOOKING_RESERVATION_ATTR_VALUE_MAP', + 'ADMIN_BOOKING_RESOURCE', + 'ADMIN_BOOKING_RESOURCE_ATTR', + 'ADMIN_BOOKING_RESOURCE_ATTR_MAP', + 'ADMIN_BOOKING_RESOURCE_ATTR_VALUE', + 'ADMIN_BOOKING_RESOURCE_TYPE', + 'ADMIN_COPY_LOCATION_ORDER', + 'ADMIN_HOLD_CANCEL_CAUSE', + 'ASSIGN_GROUP_PERM', + 'BAR_PATRON', + 'COPY_HOLDS', + 'COPY_TRANSIT_RECEIVE', + 'CREATE_BILL', + 'CREATE_BILLING_TYPE', + 'CREATE_NON_CAT_TYPE', + 'CREATE_PATRON_STAT_CAT', + 'CREATE_PATRON_STAT_CAT_ENTRY', + 'CREATE_PATRON_STAT_CAT_ENTRY_MAP', + 'CREATE_USER_GROUP_LINK', + 'DELETE_BILLING_TYPE', + 'DELETE_NON_CAT_TYPE', + 'DELETE_PATRON_STAT_CAT', + 'DELETE_PATRON_STAT_CAT_ENTRY', + 'DELETE_PATRON_STAT_CAT_ENTRY_MAP', + 'DELETE_TRANSIT', + 'group_application.user.staff', + 'MANAGE_BAD_DEBT', + 'MARK_ITEM_AVAILABLE', + 'MARK_ITEM_BINDERY', + 'MARK_ITEM_CHECKED_OUT', + 'MARK_ITEM_ILL', + 'MARK_ITEM_IN_PROCESS', + 'MARK_ITEM_IN_TRANSIT', + 'MARK_ITEM_LOST', + 'MARK_ITEM_MISSING', + 'MARK_ITEM_ON_HOLDS_SHELF', + 'MARK_ITEM_ON_ORDER', + 'MARK_ITEM_RESHELVING', + 'MERGE_USERS', + 'money.collections_tracker.create', + 'money.collections_tracker.delete', + 'OFFLINE_EXECUTE', + 'OFFLINE_UPLOAD', + 'OFFLINE_VIEW', + 'REMOVE_USER_GROUP_LINK', + 'SET_CIRC_CLAIMS_RETURNED', + 'SET_CIRC_CLAIMS_RETURNED.override', + 'SET_CIRC_LOST', + 'SET_CIRC_MISSING', + 'UNBAR_PATRON', + 'UPDATE_BILL_NOTE', + 'UPDATE_NON_CAT_TYPE', + 'UPDATE_PATRON_CLAIM_NEVER_CHECKED_OUT_COUNT', + 'UPDATE_PATRON_CLAIM_RETURN_COUNT', + 'UPDATE_PICKUP_LIB_FROM_HOLDS_SHELF', + 'UPDATE_PICKUP_LIB_FROM_TRANSIT', + 'UPDATE_USER', + 'VIEW_REPORT_OUTPUT', + 'VIEW_STANDING_PENALTY', + 'VOID_BILLING', + 'VOLUME_HOLDS' + ) AND NOT EXISTS ( + SELECT 1 + FROM permission.grp_perm_map AS map + WHERE + map.grp = pgt.id + AND map.perm = perm.id + ); + + +-- Evergreen DB patch 0551.unnest_metabib_remap_metarecord_for_bib.sql +-- +-- Replace usage of custom explode_array() function with native unnest() +-- + +INSERT INTO config.upgrade_log (version) VALUES ('0551'); -- dbs + +CREATE OR REPLACE FUNCTION metabib.remap_metarecord_for_bib( bib_id BIGINT, fp TEXT ) RETURNS BIGINT AS $func$ +DECLARE + source_count INT; + old_mr BIGINT; + tmp_mr metabib.metarecord%ROWTYPE; + deleted_mrs BIGINT[]; +BEGIN + + DELETE FROM metabib.metarecord_source_map WHERE source = bib_id; -- Rid ourselves of the search-estimate-killing linkage + + FOR tmp_mr IN SELECT m.* FROM metabib.metarecord m JOIN metabib.metarecord_source_map s ON (s.metarecord = m.id) WHERE s.source = bib_id LOOP + + IF old_mr IS NULL AND fp = tmp_mr.fingerprint THEN -- Find the first fingerprint-matching + old_mr := tmp_mr.id; + ELSE + SELECT COUNT(*) INTO source_count FROM metabib.metarecord_source_map WHERE metarecord = tmp_mr.id; + IF source_count = 0 THEN -- No other records + deleted_mrs := ARRAY_APPEND(deleted_mrs, tmp_mr.id); + DELETE FROM metabib.metarecord WHERE id = tmp_mr.id; + END IF; + END IF; + + END LOOP; + + IF old_mr IS NULL THEN -- we found no suitable, preexisting MR based on old source maps + SELECT id INTO old_mr FROM metabib.metarecord WHERE fingerprint = fp; -- is there one for our current fingerprint? + IF old_mr IS NULL THEN -- nope, create one and grab its id + INSERT INTO metabib.metarecord ( fingerprint, master_record ) VALUES ( fp, bib_id ); + SELECT id INTO old_mr FROM metabib.metarecord WHERE fingerprint = fp; + ELSE -- indeed there is. update it with a null cache and recalcualated master record + UPDATE metabib.metarecord + SET mods = NULL, + master_record = ( SELECT id FROM biblio.record_entry WHERE fingerprint = fp ORDER BY quality DESC LIMIT 1) + WHERE id = old_mr; + END IF; + ELSE -- there was one we already attached to, update its mods cache and master_record + UPDATE metabib.metarecord + SET mods = NULL, + master_record = ( SELECT id FROM biblio.record_entry WHERE fingerprint = fp ORDER BY quality DESC LIMIT 1) + WHERE id = old_mr; + END IF; + + INSERT INTO metabib.metarecord_source_map (metarecord, source) VALUES (old_mr, bib_id); -- new source mapping + + IF ARRAY_UPPER(deleted_mrs,1) > 0 THEN + UPDATE action.hold_request SET target = old_mr WHERE target IN ( SELECT unnest(deleted_mrs) ) AND hold_type = 'M'; -- if we had to delete any MRs above, make sure their holds are moved + END IF; + + RETURN old_mr; + +END; +$func$ LANGUAGE PLPGSQL; + + +-- Evergreen DB patch 0552.unnest_biblio_map_authority_linking.sql +-- +-- Replace usage of custom explode_array() function with native unnest() +-- + +INSERT INTO config.upgrade_log (version) VALUES ('0552'); -- dbs + +CREATE OR REPLACE FUNCTION biblio.map_authority_linking (bibid BIGINT, marc TEXT) RETURNS BIGINT AS $func$ + DELETE FROM authority.bib_linking WHERE bib = $1; + INSERT INTO authority.bib_linking (bib, authority) + SELECT y.bib, + y.authority + FROM ( SELECT DISTINCT $1 AS bib, + BTRIM(remove_paren_substring(txt))::BIGINT AS authority + FROM unnest(oils_xpath('//*[@code="0"]/text()',$2)) x(txt) + WHERE BTRIM(remove_paren_substring(txt)) ~ $re$^\d+$$re$ + ) y JOIN authority.record_entry r ON r.id = y.authority; + SELECT $1; +$func$ LANGUAGE SQL; + + +-- Evergreen DB patch 0553.unnest_action_hold_request_permit_test.sql +-- +-- Replace usage of custom explode_array() function with native unnest() +-- + +INSERT INTO config.upgrade_log (version) VALUES ('0553'); -- dbs + +CREATE OR REPLACE FUNCTION action.hold_request_permit_test( pickup_ou INT, request_ou INT, match_item BIGINT, match_user INT, match_requestor INT, retargetting BOOL ) RETURNS SETOF action.matrix_test_result AS $func$ +DECLARE + matchpoint_id INT; + user_object actor.usr%ROWTYPE; + age_protect_object config.rule_age_hold_protect%ROWTYPE; + standing_penalty config.standing_penalty%ROWTYPE; + transit_range_ou_type actor.org_unit_type%ROWTYPE; + transit_source actor.org_unit%ROWTYPE; + item_object asset.copy%ROWTYPE; + item_cn_object asset.call_number%ROWTYPE; + ou_skip actor.org_unit_setting%ROWTYPE; + result action.matrix_test_result; + hold_test config.hold_matrix_matchpoint%ROWTYPE; + hold_count INT; + hold_transit_prox INT; + frozen_hold_count INT; + context_org_list INT[]; + done BOOL := FALSE; +BEGIN + SELECT INTO user_object * FROM actor.usr WHERE id = match_user; + SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( pickup_ou ); + + result.success := TRUE; + + -- Fail if we couldn't find a user + IF user_object.id IS NULL THEN + result.fail_part := 'no_user'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + RETURN; + END IF; + + SELECT INTO item_object * FROM asset.copy WHERE id = match_item; + + -- Fail if we couldn't find a copy + IF item_object.id IS NULL THEN + result.fail_part := 'no_item'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + RETURN; + END IF; + + SELECT INTO matchpoint_id action.find_hold_matrix_matchpoint(pickup_ou, request_ou, match_item, match_user, match_requestor); + result.matchpoint := matchpoint_id; + + SELECT INTO ou_skip * FROM actor.org_unit_setting WHERE name = 'circ.holds.target_skip_me' AND org_unit = item_object.circ_lib; + + -- Fail if the circ_lib for the item has circ.holds.target_skip_me set to true + IF ou_skip.id IS NOT NULL AND ou_skip.value = 'true' THEN + result.fail_part := 'circ.holds.target_skip_me'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + RETURN; + END IF; + + -- Fail if user is barred + IF user_object.barred IS TRUE THEN + result.fail_part := 'actor.usr.barred'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + RETURN; + END IF; + + -- Fail if we couldn't find any matchpoint (requires a default) + IF matchpoint_id IS NULL THEN + result.fail_part := 'no_matchpoint'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + RETURN; + END IF; + + SELECT INTO hold_test * FROM config.hold_matrix_matchpoint WHERE id = matchpoint_id; + + IF hold_test.holdable IS FALSE THEN + result.fail_part := 'config.hold_matrix_test.holdable'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + END IF; + + IF hold_test.transit_range IS NOT NULL THEN + SELECT INTO transit_range_ou_type * FROM actor.org_unit_type WHERE id = hold_test.transit_range; + IF hold_test.distance_is_from_owner THEN + SELECT INTO transit_source ou.* FROM actor.org_unit ou JOIN asset.call_number cn ON (cn.owning_lib = ou.id) WHERE cn.id = item_object.call_number; + ELSE + SELECT INTO transit_source * FROM actor.org_unit WHERE id = item_object.circ_lib; + END IF; + + PERFORM * FROM actor.org_unit_descendants( transit_source.id, transit_range_ou_type.depth ) WHERE id = pickup_ou; + + IF NOT FOUND THEN + result.fail_part := 'transit_range'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + END IF; + END IF; + + FOR standing_penalty IN + SELECT DISTINCT csp.* + FROM actor.usr_standing_penalty usp + JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty) + WHERE usr = match_user + AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) ) + AND (usp.stop_date IS NULL or usp.stop_date > NOW()) + AND csp.block_list LIKE '%HOLD%' LOOP + + result.fail_part := standing_penalty.name; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + END LOOP; + + IF hold_test.stop_blocked_user IS TRUE THEN + FOR standing_penalty IN + SELECT DISTINCT csp.* + FROM actor.usr_standing_penalty usp + JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty) + WHERE usr = match_user + AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) ) + AND (usp.stop_date IS NULL or usp.stop_date > NOW()) + AND csp.block_list LIKE '%CIRC%' LOOP + + result.fail_part := standing_penalty.name; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + END LOOP; + END IF; + + IF hold_test.max_holds IS NOT NULL AND NOT retargetting THEN + SELECT INTO hold_count COUNT(*) + FROM action.hold_request + WHERE usr = match_user + AND fulfillment_time IS NULL + AND cancel_time IS NULL + AND CASE WHEN hold_test.include_frozen_holds THEN TRUE ELSE frozen IS FALSE END; + + IF hold_count >= hold_test.max_holds THEN + result.fail_part := 'config.hold_matrix_test.max_holds'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + END IF; + END IF; + + IF item_object.age_protect IS NOT NULL THEN + SELECT INTO age_protect_object * FROM config.rule_age_hold_protect WHERE id = item_object.age_protect; + + IF item_object.create_date + age_protect_object.age > NOW() THEN + IF hold_test.distance_is_from_owner THEN + SELECT INTO item_cn_object * FROM asset.call_number WHERE id = item_object.call_number; + SELECT INTO hold_transit_prox prox FROM actor.org_unit_proximity WHERE from_org = item_cn_object.owning_lib AND to_org = pickup_ou; + ELSE + SELECT INTO hold_transit_prox prox FROM actor.org_unit_proximity WHERE from_org = item_object.circ_lib AND to_org = pickup_ou; + END IF; + + IF hold_transit_prox > age_protect_object.prox THEN + result.fail_part := 'config.rule_age_hold_protect.prox'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + END IF; + END IF; + END IF; + + IF NOT done THEN + RETURN NEXT result; + END IF; + + RETURN; +END; +$func$ LANGUAGE plpgsql; + + +-- Evergreen DB patch 0554.unnest_search_query_parser_fts.sql +-- +-- Replace usage of custom explode_array() function with native unnest() +-- + +INSERT INTO config.upgrade_log (version) VALUES ('0554'); -- dbs + +CREATE OR REPLACE FUNCTION search.query_parser_fts ( + + param_search_ou INT, + param_depth INT, + param_query TEXT, + param_statuses INT[], + param_locations INT[], + param_offset INT, + param_check INT, + param_limit INT, + metarecord BOOL, + staff BOOL + +) RETURNS SETOF search.search_result AS $func$ +DECLARE + + current_res search.search_result%ROWTYPE; + search_org_list INT[]; + + check_limit INT; + core_limit INT; + core_offset INT; + tmp_int INT; + + core_result RECORD; + core_cursor REFCURSOR; + core_rel_query TEXT; + + total_count INT := 0; + check_count INT := 0; + deleted_count INT := 0; + visible_count INT := 0; + excluded_count INT := 0; + +BEGIN + + check_limit := COALESCE( param_check, 1000 ); + core_limit := COALESCE( param_limit, 25000 ); + core_offset := COALESCE( param_offset, 0 ); + + -- core_skip_chk := COALESCE( param_skip_chk, 1 ); + + IF param_search_ou > 0 THEN + IF param_depth IS NOT NULL THEN + SELECT array_accum(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou, param_depth ); + ELSE + SELECT array_accum(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou ); + END IF; + ELSIF param_search_ou < 0 THEN + SELECT array_accum(distinct org_unit) INTO search_org_list FROM actor.org_lasso_map WHERE lasso = -param_search_ou; + ELSIF param_search_ou = 0 THEN + -- reserved for user lassos (ou_buckets/type='lasso') with ID passed in depth ... hack? sure. + END IF; + + OPEN core_cursor FOR EXECUTE param_query; + + LOOP + + FETCH core_cursor INTO core_result; + EXIT WHEN NOT FOUND; + EXIT WHEN total_count >= core_limit; + + total_count := total_count + 1; + + CONTINUE WHEN total_count NOT BETWEEN core_offset + 1 AND check_limit + core_offset; + + check_count := check_count + 1; + + PERFORM 1 FROM biblio.record_entry b WHERE NOT b.deleted AND b.id IN ( SELECT * FROM unnest( core_result.records ) ); + IF NOT FOUND THEN + -- RAISE NOTICE ' % were all deleted ... ', core_result.records; + deleted_count := deleted_count + 1; + CONTINUE; + END IF; + + PERFORM 1 + FROM biblio.record_entry b + JOIN config.bib_source s ON (b.source = s.id) + WHERE s.transcendant + AND b.id IN ( SELECT * FROM unnest( core_result.records ) ); + + IF FOUND THEN + -- RAISE NOTICE ' % were all transcendant ... ', core_result.records; + visible_count := visible_count + 1; + + current_res.id = core_result.id; + current_res.rel = core_result.rel; + + tmp_int := 1; + IF metarecord THEN + SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id; + END IF; + + IF tmp_int = 1 THEN + current_res.record = core_result.records[1]; + ELSE + current_res.record = NULL; + END IF; + + RETURN NEXT current_res; + + CONTINUE; + END IF; + + PERFORM 1 + FROM asset.call_number cn + JOIN asset.uri_call_number_map map ON (map.call_number = cn.id) + JOIN asset.uri uri ON (map.uri = uri.id) + WHERE NOT cn.deleted + AND cn.label = '##URI##' + AND uri.active + AND ( param_locations IS NULL OR array_upper(param_locations, 1) IS NULL ) + AND cn.record IN ( SELECT * FROM unnest( core_result.records ) ) + AND cn.owning_lib IN ( SELECT * FROM unnest( search_org_list ) ) + LIMIT 1; + + IF FOUND THEN + -- RAISE NOTICE ' % have at least one URI ... ', core_result.records; + visible_count := visible_count + 1; + + current_res.id = core_result.id; + current_res.rel = core_result.rel; + + tmp_int := 1; + IF metarecord THEN + SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id; + END IF; + + IF tmp_int = 1 THEN + current_res.record = core_result.records[1]; + ELSE + current_res.record = NULL; + END IF; + + RETURN NEXT current_res; + + CONTINUE; + END IF; + + IF param_statuses IS NOT NULL AND array_upper(param_statuses, 1) > 0 THEN + + PERFORM 1 + FROM asset.call_number cn + JOIN asset.copy cp ON (cp.call_number = cn.id) + WHERE NOT cn.deleted + AND NOT cp.deleted + AND cp.status IN ( SELECT * FROM unnest( param_statuses ) ) + AND cn.record IN ( SELECT * FROM unnest( core_result.records ) ) + AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) + LIMIT 1; + + IF NOT FOUND THEN + PERFORM 1 + FROM biblio.peer_bib_copy_map pr + JOIN asset.copy cp ON (cp.id = pr.target_copy) + WHERE NOT cp.deleted + AND cp.status IN ( SELECT * FROM unnest( param_statuses ) ) + AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) ) + AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) + LIMIT 1; + + IF NOT FOUND THEN + -- RAISE NOTICE ' % and multi-home linked records were all status-excluded ... ', core_result.records; + excluded_count := excluded_count + 1; + CONTINUE; + END IF; + END IF; + + END IF; + + IF param_locations IS NOT NULL AND array_upper(param_locations, 1) > 0 THEN + + PERFORM 1 + FROM asset.call_number cn + JOIN asset.copy cp ON (cp.call_number = cn.id) + WHERE NOT cn.deleted + AND NOT cp.deleted + AND cp.location IN ( SELECT * FROM unnest( param_locations ) ) + AND cn.record IN ( SELECT * FROM unnest( core_result.records ) ) + AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) + LIMIT 1; + + IF NOT FOUND THEN + PERFORM 1 + FROM biblio.peer_bib_copy_map pr + JOIN asset.copy cp ON (cp.id = pr.target_copy) + WHERE NOT cp.deleted + AND cp.location IN ( SELECT * FROM unnest( param_locations ) ) + AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) ) + AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) + LIMIT 1; + + IF NOT FOUND THEN + -- RAISE NOTICE ' % and multi-home linked records were all copy_location-excluded ... ', core_result.records; + excluded_count := excluded_count + 1; + CONTINUE; + END IF; + END IF; + + END IF; + + IF staff IS NULL OR NOT staff THEN + + PERFORM 1 + FROM asset.opac_visible_copies + WHERE circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) + AND record IN ( SELECT * FROM unnest( core_result.records ) ) + LIMIT 1; + + IF NOT FOUND THEN + PERFORM 1 + FROM biblio.peer_bib_copy_map pr + JOIN asset.opac_visible_copies cp ON (cp.copy_id = pr.target_copy) + WHERE cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) + AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) ) + LIMIT 1; + + IF NOT FOUND THEN + + -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records; + excluded_count := excluded_count + 1; + CONTINUE; + END IF; + END IF; + + ELSE + + PERFORM 1 + FROM asset.call_number cn + JOIN asset.copy cp ON (cp.call_number = cn.id) + WHERE NOT cn.deleted + AND NOT cp.deleted + AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) + AND cn.record IN ( SELECT * FROM unnest( core_result.records ) ) + LIMIT 1; + + IF NOT FOUND THEN + + PERFORM 1 + FROM biblio.peer_bib_copy_map pr + JOIN asset.copy cp ON (cp.id = pr.target_copy) + WHERE NOT cp.deleted + AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) + AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) ) + LIMIT 1; + + IF NOT FOUND THEN + + PERFORM 1 + FROM asset.call_number cn + WHERE cn.record IN ( SELECT * FROM unnest( core_result.records ) ) + LIMIT 1; + + IF FOUND THEN + -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records; + excluded_count := excluded_count + 1; + CONTINUE; + END IF; + END IF; + + END IF; + + END IF; + + visible_count := visible_count + 1; + + current_res.id = core_result.id; + current_res.rel = core_result.rel; + + tmp_int := 1; + IF metarecord THEN + SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id; + END IF; + + IF tmp_int = 1 THEN + current_res.record = core_result.records[1]; + ELSE + current_res.record = NULL; + END IF; + + RETURN NEXT current_res; + + IF visible_count % 1000 = 0 THEN + -- RAISE NOTICE ' % visible so far ... ', visible_count; + END IF; + + END LOOP; + + current_res.id = NULL; + current_res.rel = NULL; + current_res.record = NULL; + current_res.total = total_count; + current_res.checked = check_count; + current_res.deleted = deleted_count; + current_res.visible = visible_count; + current_res.excluded = excluded_count; + + CLOSE core_cursor; + + RETURN NEXT current_res; + +END; +$func$ LANGUAGE PLPGSQL; + + +-- Evergreen DB patch 0555.unnest_oils_xpath_table.sql +-- +-- Replace usage of custom explode_array() function with native unnest() +-- + +INSERT INTO config.upgrade_log (version) VALUES ('0555'); -- dbs + +CREATE OR REPLACE FUNCTION oils_xpath_table ( key TEXT, document_field TEXT, relation_name TEXT, xpaths TEXT, criteria TEXT ) RETURNS SETOF RECORD AS $func$ +DECLARE + xpath_list TEXT[]; + select_list TEXT[]; + where_list TEXT[]; + q TEXT; + out_record RECORD; + empty_test RECORD; +BEGIN + xpath_list := STRING_TO_ARRAY( xpaths, '|' ); + + select_list := ARRAY_APPEND( select_list, key || '::INT AS key' ); + + FOR i IN 1 .. ARRAY_UPPER(xpath_list,1) LOOP + IF xpath_list[i] = 'null()' THEN + select_list := ARRAY_APPEND( select_list, 'NULL::TEXT AS c_' || i ); + ELSE + select_list := ARRAY_APPEND( + select_list, + $sel$ + unnest( + COALESCE( + NULLIF( + oils_xpath( + $sel$ || + quote_literal( + CASE + WHEN xpath_list[i] ~ $re$/[^/[]*@[^/]+$$re$ OR xpath_list[i] ~ $re$text\(\)$$re$ THEN xpath_list[i] + ELSE xpath_list[i] || '//text()' + END + ) || + $sel$, + $sel$ || document_field || $sel$ + ), + '{}'::TEXT[] + ), + '{NULL}'::TEXT[] + ) + ) AS c_$sel$ || i + ); + where_list := ARRAY_APPEND( + where_list, + 'c_' || i || ' IS NOT NULL' + ); + END IF; + END LOOP; + + q := $q$ +SELECT * FROM ( + SELECT $q$ || ARRAY_TO_STRING( select_list, ', ' ) || $q$ FROM $q$ || relation_name || $q$ WHERE ($q$ || criteria || $q$) +)x WHERE $q$ || ARRAY_TO_STRING( where_list, ' OR ' ); + -- RAISE NOTICE 'query: %', q; + + FOR out_record IN EXECUTE q LOOP + RETURN NEXT out_record; + END LOOP; + + RETURN; +END; +$func$ LANGUAGE PLPGSQL IMMUTABLE; + + +-- Evergreen DB patch 0556.unnest_biblio_extract_metabib_field_entry.sql +-- +-- Replace usage of custom explode_array() function with native unnest() +-- + +INSERT INTO config.upgrade_log (version) VALUES ('0556'); -- dbs + +CREATE OR REPLACE FUNCTION biblio.extract_metabib_field_entry ( rid BIGINT, default_joiner TEXT ) RETURNS SETOF metabib.field_entry_template AS $func$ +DECLARE + bib biblio.record_entry%ROWTYPE; + idx config.metabib_field%ROWTYPE; + xfrm config.xml_transform%ROWTYPE; + prev_xfrm TEXT; + transformed_xml TEXT; + xml_node TEXT; + xml_node_list TEXT[]; + facet_text TEXT; + raw_text TEXT; + curr_text TEXT; + joiner TEXT := default_joiner; -- XXX will index defs supply a joiner? + output_row metabib.field_entry_template%ROWTYPE; +BEGIN + + -- Get the record + SELECT INTO bib * FROM biblio.record_entry WHERE id = rid; + + -- Loop over the indexing entries + FOR idx IN SELECT * FROM config.metabib_field ORDER BY format LOOP + + SELECT INTO xfrm * from config.xml_transform WHERE name = idx.format; + + -- See if we can skip the XSLT ... it's expensive + IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN + -- Can't skip the transform + IF xfrm.xslt <> '---' THEN + transformed_xml := oils_xslt_process(bib.marc,xfrm.xslt); + ELSE + transformed_xml := bib.marc; + END IF; + + prev_xfrm := xfrm.name; + END IF; + + xml_node_list := oils_xpath( idx.xpath, transformed_xml, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] ); + + raw_text := NULL; + FOR xml_node IN SELECT x FROM unnest(xml_node_list) AS x LOOP + CONTINUE WHEN xml_node !~ E'^\\s*<'; + + curr_text := ARRAY_TO_STRING( + oils_xpath( '//text()', + REGEXP_REPLACE( -- This escapes all &s not followed by "amp;". Data ise returned from oils_xpath (above) in UTF-8, not entity encoded + REGEXP_REPLACE( -- This escapes embeded [^<]+)(<)([^>]+<)$re$, + E'\\1<\\3', + 'g' + ), + '&(?!amp;)', + '&', + 'g' + ) + ), + ' ' + ); + + CONTINUE WHEN curr_text IS NULL OR curr_text = ''; + + IF raw_text IS NOT NULL THEN + raw_text := raw_text || joiner; + END IF; + + raw_text := COALESCE(raw_text,'') || curr_text; + + -- insert raw node text for faceting + IF idx.facet_field THEN + + IF idx.facet_xpath IS NOT NULL AND idx.facet_xpath <> '' THEN + facet_text := oils_xpath_string( idx.facet_xpath, xml_node, joiner, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] ); + ELSE + facet_text := curr_text; + END IF; + + output_row.field_class = idx.field_class; + output_row.field = -1 * idx.id; + output_row.source = rid; + output_row.value = BTRIM(REGEXP_REPLACE(facet_text, E'\\s+', ' ', 'g')); + + RETURN NEXT output_row; + END IF; + + END LOOP; + + CONTINUE WHEN raw_text IS NULL OR raw_text = ''; + + -- insert combined node text for searching + IF idx.search_field THEN + output_row.field_class = idx.field_class; + output_row.field = idx.id; + output_row.source = rid; + output_row.value = BTRIM(REGEXP_REPLACE(raw_text, E'\\s+', ' ', 'g')); + + RETURN NEXT output_row; + END IF; + + END LOOP; + +END; +$func$ LANGUAGE PLPGSQL; + + +-- Evergreen DB patch 0557.schmea.in-db_unapi_copy_related_visibility-1.sql +-- +-- Bring in-db unAPI opac visibility info up to date with (and a little beyond) ea3b8857d08b8a9050e763f8084c841e8df9a473 +-- + + +INSERT INTO config.upgrade_log (version) VALUES ('0557'); -- miker + +CREATE OR REPLACE FUNCTION unapi.acl ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$ + SELECT XMLELEMENT( + name location, + XMLATTRIBUTES( + CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns, + id AS ident, + holdable, + opac_visible, + label_prefix AS prefix, + label_suffix AS suffix + ), + name + ) + FROM asset.copy_location + WHERE id = $1; +$F$ LANGUAGE SQL; + + +-- Evergreen DB patch 0558.schema.in-db_unapi_copy_related_visibility-2.sql +-- +-- Bring in-db unAPI opac visibility info up to date with (and a little beyond) ea3b8857d08b8a9050e763f8084c841e8df9a473 +-- + + +INSERT INTO config.upgrade_log (version) VALUES ('0558'); -- miker + +CREATE OR REPLACE FUNCTION unapi.ccs ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$ + SELECT XMLELEMENT( + name status, + XMLATTRIBUTES( + CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns, + id AS ident, + holdable, + opac_visible + ), + name + ) + FROM config.copy_status + WHERE id = $1; +$F$ LANGUAGE SQL; + + +-- Evergreen DB patch 0559.schema.biblio.extract_located_uris.sql +-- +-- * Add a stored procedure to reingest problematic URIs +-- * Avoid duplicate row issues in biblio.extract_located_uris +-- * Fix LP 797304 and 797307 - asset.uri parsing bugs +-- + + +-- check whether patch can be applied +INSERT INTO config.upgrade_log (version) VALUES ('0559'); -- dbs + +-- FIXME: add/check SQL statements to perform the upgrade +CREATE OR REPLACE FUNCTION biblio.extract_located_uris( bib_id BIGINT, marcxml TEXT, editor_id INT ) RETURNS VOID AS $func$ +DECLARE + uris TEXT[]; + uri_xml TEXT; + uri_label TEXT; + uri_href TEXT; + uri_use TEXT; + uri_owner_list TEXT[]; + uri_owner TEXT; + uri_owner_id INT; + uri_id INT; + uri_cn_id INT; + uri_map_id INT; +BEGIN + + -- Clear any URI mappings and call numbers for this bib. + -- This leads to acn / auricnm inflation, but also enables + -- old acn/auricnm's to go away and for bibs to be deleted. + FOR uri_cn_id IN SELECT id FROM asset.call_number WHERE record = bib_id AND label = '##URI##' AND NOT deleted LOOP + DELETE FROM asset.uri_call_number_map WHERE call_number = uri_cn_id; + DELETE FROM asset.call_number WHERE id = uri_cn_id; + END LOOP; + + uris := oils_xpath('//*[@tag="856" and (@ind1="4" or @ind1="1") and (@ind2="0" or @ind2="1")]',marcxml); + IF ARRAY_UPPER(uris,1) > 0 THEN + FOR i IN 1 .. ARRAY_UPPER(uris, 1) LOOP + -- First we pull info out of the 856 + uri_xml := uris[i]; + + uri_href := (oils_xpath('//*[@code="u"]/text()',uri_xml))[1]; + uri_label := (oils_xpath('//*[@code="y"]/text()|//*[@code="3"]/text()',uri_xml))[1]; + uri_use := (oils_xpath('//*[@code="z"]/text()|//*[@code="2"]/text()|//*[@code="n"]/text()',uri_xml))[1]; + + IF uri_label IS NULL THEN + uri_label := uri_href; + END IF; + CONTINUE WHEN uri_href IS NULL; + + -- Get the distinct list of libraries wanting to use + SELECT ARRAY_ACCUM( + DISTINCT REGEXP_REPLACE( + x, + $re$^.*?\((\w+)\).*$$re$, + E'\\1' + ) + ) INTO uri_owner_list + FROM UNNEST( + oils_xpath( + '//*[@code="9"]/text()|//*[@code="w"]/text()|//*[@code="n"]/text()', + uri_xml + ) + )x; + + IF ARRAY_UPPER(uri_owner_list,1) > 0 THEN + + -- look for a matching uri + IF uri_use IS NULL THEN + SELECT id INTO uri_id + FROM asset.uri + WHERE label = uri_label AND href = uri_href AND use_restriction IS NULL AND active + ORDER BY id LIMIT 1; + IF NOT FOUND THEN -- create one + INSERT INTO asset.uri (label, href, use_restriction) VALUES (uri_label, uri_href, uri_use); + SELECT id INTO uri_id + FROM asset.uri + WHERE label = uri_label AND href = uri_href AND use_restriction IS NULL AND active; + END IF; + ELSE + SELECT id INTO uri_id + FROM asset.uri + WHERE label = uri_label AND href = uri_href AND use_restriction = uri_use AND active + ORDER BY id LIMIT 1; + IF NOT FOUND THEN -- create one + INSERT INTO asset.uri (label, href, use_restriction) VALUES (uri_label, uri_href, uri_use); + SELECT id INTO uri_id + FROM asset.uri + WHERE label = uri_label AND href = uri_href AND use_restriction = uri_use AND active; + END IF; + END IF; + + FOR j IN 1 .. ARRAY_UPPER(uri_owner_list, 1) LOOP + uri_owner := uri_owner_list[j]; + + SELECT id INTO uri_owner_id FROM actor.org_unit WHERE shortname = uri_owner; + CONTINUE WHEN NOT FOUND; + + -- we need a call number to link through + SELECT id INTO uri_cn_id FROM asset.call_number WHERE owning_lib = uri_owner_id AND record = bib_id AND label = '##URI##' AND NOT deleted; + IF NOT FOUND THEN + INSERT INTO asset.call_number (owning_lib, record, create_date, edit_date, creator, editor, label) + VALUES (uri_owner_id, bib_id, 'now', 'now', editor_id, editor_id, '##URI##'); + SELECT id INTO uri_cn_id FROM asset.call_number WHERE owning_lib = uri_owner_id AND record = bib_id AND label = '##URI##' AND NOT deleted; + END IF; + + -- now, link them if they're not already + SELECT id INTO uri_map_id FROM asset.uri_call_number_map WHERE call_number = uri_cn_id AND uri = uri_id; + IF NOT FOUND THEN + INSERT INTO asset.uri_call_number_map (call_number, uri) VALUES (uri_cn_id, uri_id); + END IF; + + END LOOP; + + END IF; + + END LOOP; + END IF; + + RETURN; +END; +$func$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION biblio.reingest_uris() RETURNS VOID AS $func$ +DECLARE + rec_id BIGINT; +BEGIN + -- Get the distinct set of record IDs that need to be reingested + -- (assuming that href = label is a reasonable red flag) + FOR rec_id IN SELECT rec_uris.id FROM ( + SELECT acn.record AS id + FROM asset.call_number acn + INNER JOIN asset.uri_call_number_map auricnm ON auricnm.call_number = acn.id + INNER JOIN asset.uri auri ON auri.id = auricnm.uri + INNER JOIN biblio.record_entry bre ON acn.record = bre.id + WHERE auri.href = auri.label + AND xml_is_well_formed(bre.marc) + GROUP BY acn.record + ORDER BY acn.record + ) AS rec_uris + LOOP + -- Reingest the offending records + PERFORM biblio.extract_located_uris(rec_id, bre.marc, 1) + FROM biblio.record_entry bre + WHERE bre.id = rec_id; + END LOOP; +END; +$func$ LANGUAGE PLPGSQL; + +-- Kick off the reingest; this may take a while +SELECT biblio.reingest_uris(); + +-- Hopefully this isn't something we'll need to run again +DROP FUNCTION biblio.reingest_uris(); + + +-- Evergreen DB patch XXXX.fix_opac_copy_vis_cache.sql +-- +-- Correct LP#788763: glitch in asset.cache_copy_visibility +-- prevented updating the visibility of copy locations, org +-- units, and copy statuses. +-- + + +-- check whether patch can be applied +SELECT evergreen.upgrade_deps_block_check('0560'); + +CREATE OR REPLACE FUNCTION asset.cache_copy_visibility () RETURNS TRIGGER as $func$ +DECLARE + add_query TEXT; + remove_query TEXT; + do_add BOOLEAN := false; + do_remove BOOLEAN := false; +BEGIN + add_query := $$ + INSERT INTO asset.opac_visible_copies (copy_id, circ_lib, record) + SELECT id, circ_lib, record FROM ( + SELECT cp.id, cp.circ_lib, cn.record, cn.id AS call_number, cp.location, cp.status + FROM asset.copy cp + JOIN asset.call_number cn ON (cn.id = cp.call_number) + JOIN actor.org_unit a ON (cp.circ_lib = a.id) + JOIN asset.copy_location cl ON (cp.location = cl.id) + JOIN config.copy_status cs ON (cp.status = cs.id) + JOIN biblio.record_entry b ON (cn.record = b.id) + WHERE NOT cp.deleted + AND NOT cn.deleted + AND NOT b.deleted + AND cs.opac_visible + AND cl.opac_visible + AND cp.opac_visible + AND a.opac_visible + UNION + SELECT cp.id, cp.circ_lib, pbcm.peer_record AS record, NULL AS call_number, cp.location, cp.status + FROM asset.copy cp + JOIN biblio.peer_bib_copy_map pbcm ON (pbcm.target_copy = cp.id) + JOIN actor.org_unit a ON (cp.circ_lib = a.id) + JOIN asset.copy_location cl ON (cp.location = cl.id) + JOIN config.copy_status cs ON (cp.status = cs.id) + WHERE NOT cp.deleted + AND cs.opac_visible + AND cl.opac_visible + AND cp.opac_visible + AND a.opac_visible + ) AS x + + $$; + + remove_query := $$ DELETE FROM asset.opac_visible_copies WHERE copy_id IN ( SELECT id FROM asset.copy WHERE $$; + + IF TG_TABLE_NAME = 'peer_bib_copy_map' THEN + IF TG_OP = 'INSERT' THEN + add_query := add_query || 'WHERE x.id = ' || NEW.target_copy || ' AND x.record = ' || NEW.peer_record || ';'; + EXECUTE add_query; + RETURN NEW; + ELSE + remove_query := 'DELETE FROM asset.opac_visible_copies WHERE copy_id = ' || OLD.target_copy || ' AND record = ' || OLD.peer_record || ';'; + EXECUTE remove_query; + RETURN OLD; + END IF; + END IF; + + IF TG_OP = 'INSERT' THEN + + IF TG_TABLE_NAME IN ('copy', 'unit') THEN + add_query := add_query || 'WHERE x.id = ' || NEW.id || ';'; + EXECUTE add_query; + END IF; + + RETURN NEW; + + END IF; + + -- handle items first, since with circulation activity + -- their statuses change frequently + IF TG_TABLE_NAME IN ('copy', 'unit') THEN + + IF OLD.location <> NEW.location OR + OLD.call_number <> NEW.call_number OR + OLD.status <> NEW.status OR + OLD.circ_lib <> NEW.circ_lib THEN + -- any of these could change visibility, but + -- we'll save some queries and not try to calculate + -- the change directly + do_remove := true; + do_add := true; + ELSE + + IF OLD.deleted <> NEW.deleted THEN + IF NEW.deleted THEN + do_remove := true; + ELSE + do_add := true; + END IF; + END IF; + + IF OLD.opac_visible <> NEW.opac_visible THEN + IF OLD.opac_visible THEN + do_remove := true; + ELSIF NOT do_remove THEN -- handle edge case where deleted item + -- is also marked opac_visible + do_add := true; + END IF; + END IF; + + END IF; + + IF do_remove THEN + DELETE FROM asset.opac_visible_copies WHERE copy_id = NEW.id; + END IF; + IF do_add THEN + add_query := add_query || 'WHERE x.id = ' || NEW.id || ';'; + EXECUTE add_query; + END IF; + + RETURN NEW; + + END IF; + + IF TG_TABLE_NAME IN ('call_number', 'record_entry') THEN -- these have a 'deleted' column + + IF OLD.deleted AND NEW.deleted THEN -- do nothing + + RETURN NEW; + + ELSIF NEW.deleted THEN -- remove rows + + IF TG_TABLE_NAME = 'call_number' THEN + DELETE FROM asset.opac_visible_copies WHERE copy_id IN (SELECT id FROM asset.copy WHERE call_number = NEW.id); + ELSIF TG_TABLE_NAME = 'record_entry' THEN + DELETE FROM asset.opac_visible_copies WHERE record = NEW.id; + END IF; + + RETURN NEW; + + ELSIF OLD.deleted THEN -- add rows + + IF TG_TABLE_NAME IN ('copy','unit') THEN + add_query := add_query || 'WHERE x.id = ' || NEW.id || ';'; + ELSIF TG_TABLE_NAME = 'call_number' THEN + add_query := add_query || 'WHERE x.call_number = ' || NEW.id || ';'; + ELSIF TG_TABLE_NAME = 'record_entry' THEN + add_query := add_query || 'WHERE x.record = ' || NEW.id || ';'; + END IF; + + EXECUTE add_query; + RETURN NEW; + + END IF; + + END IF; + + IF TG_TABLE_NAME = 'call_number' THEN + + IF OLD.record <> NEW.record THEN + -- call number is linked to different bib + remove_query := remove_query || 'call_number = ' || NEW.id || ');'; + EXECUTE remove_query; + add_query := add_query || 'WHERE x.call_number = ' || NEW.id || ';'; + EXECUTE add_query; + END IF; + + RETURN NEW; + + END IF; + + IF TG_TABLE_NAME IN ('record_entry') THEN + RETURN NEW; -- don't have 'opac_visible' + END IF; + + -- actor.org_unit, asset.copy_location, asset.copy_status + IF NEW.opac_visible = OLD.opac_visible THEN -- do nothing + + RETURN NEW; + + ELSIF NEW.opac_visible THEN -- add rows + + IF TG_TABLE_NAME = 'org_unit' THEN + add_query := add_query || 'WHERE x.circ_lib = ' || NEW.id || ';'; + ELSIF TG_TABLE_NAME = 'copy_location' THEN + add_query := add_query || 'WHERE x.location = ' || NEW.id || ';'; + ELSIF TG_TABLE_NAME = 'copy_status' THEN + add_query := add_query || 'WHERE x.status = ' || NEW.id || ';'; + END IF; + + EXECUTE add_query; + + ELSE -- delete rows + + IF TG_TABLE_NAME = 'org_unit' THEN + remove_query := 'DELETE FROM asset.opac_visible_copies WHERE circ_lib = ' || NEW.id || ';'; + ELSIF TG_TABLE_NAME = 'copy_location' THEN + remove_query := remove_query || 'location = ' || NEW.id || ');'; + ELSIF TG_TABLE_NAME = 'copy_status' THEN + remove_query := remove_query || 'status = ' || NEW.id || ');'; + END IF; + + EXECUTE remove_query; + + END IF; + + RETURN NEW; +END; +$func$ LANGUAGE PLPGSQL; + + +-- Just reserve this number to avoid confusion +-- Evergreen DB patch XXXX.data.collections_exempt_perm.sql +-- +-- Adds a new UPDATE_PATRON_COLLECTIONS_EXEMPT permission +-- + + +-- check whether patch can be applied +INSERT INTO config.upgrade_log (version) VALUES ('0563'); + +INSERT INTO permission.perm_list ( id, code, description ) + VALUES ( 510, 'UPDATE_PATRON_COLLECTIONS_EXEMPT', oils_i18n_gettext(510, + 'Allows a user to indicate that a patron is exempt from collections processing', 'ppl', 'description')); + +--- stock Circulation Administrator group + +INSERT INTO permission.grp_perm_map ( grp, perm, depth, grantable ) + SELECT + 4, + id, + 0, + 't' + FROM permission.perm_list + WHERE code in ('UPDATE_PATRON_COLLECTIONS_EXEMPT'); + + +-- Evergreen DB patch XXXX.schema.unAPI_XMLAGG_cleanup.sql +-- +-- FIXME: insert description of change, if needed +-- + + +-- check whether patch can be applied +INSERT INTO config.upgrade_log (version) VALUES ('0566'); + +CREATE OR REPLACE FUNCTION unapi.bre ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$ +DECLARE + me biblio.record_entry%ROWTYPE; + layout unapi.bre_output_layout%ROWTYPE; + xfrm config.xml_transform%ROWTYPE; + ouid INT; + tmp_xml TEXT; + top_el TEXT; + output XML; + hxml XML; + axml XML; +BEGIN + + SELECT id INTO ouid FROM actor.org_unit WHERE shortname = org; + + IF ouid IS NULL THEN + RETURN NULL::XML; + END IF; + + IF format = 'holdings_xml' THEN -- the special case + output := unapi.holdings_xml( obj_id, ouid, org, depth, includes, slimit, soffset, include_xmlns); + RETURN output; + END IF; + + SELECT * INTO layout FROM unapi.bre_output_layout WHERE name = format; + + IF layout.name IS NULL THEN + RETURN NULL::XML; + END IF; + + SELECT * INTO xfrm FROM config.xml_transform WHERE name = layout.transform; + + SELECT * INTO me FROM biblio.record_entry WHERE id = obj_id; + + -- grab SVF if we need them + IF ('mra' = ANY (includes)) THEN + axml := unapi.mra(obj_id,NULL,NULL,NULL,NULL); + ELSE + axml := NULL::XML; + END IF; + + -- grab hodlings if we need them + IF ('holdings_xml' = ANY (includes)) THEN + hxml := unapi.holdings_xml(obj_id, ouid, org, depth, evergreen.array_remove_item_by_value(includes,'holdings_xml'), slimit, soffset, include_xmlns); + ELSE + hxml := NULL::XML; + END IF; + + + -- generate our item node + + + IF format = 'marcxml' THEN + tmp_xml := me.marc; + IF tmp_xml !~ E'(.*?)$', axml || '\\1'); + END IF; + + IF hxml IS NOT NULL THEN -- XXX how do we configure the holdings position? + tmp_xml := REGEXP_REPLACE(tmp_xml, '(.*?)$', hxml || '\\1'); + END IF; + + IF ('bre.unapi' = ANY (includes)) THEN + output := REGEXP_REPLACE( + tmp_xml, + '(.*?)', + XMLELEMENT( + name abbr, + XMLATTRIBUTES( + 'http://www.w3.org/1999/xhtml' AS xmlns, + 'unapi-id' AS class, + 'tag:open-ils.org:U2@bre/' || obj_id || '/' || org AS title + ) + )::TEXT || '\\1' + ); + ELSE + output := tmp_xml; + END IF; + + output := REGEXP_REPLACE(output::TEXT,E'>\\s+<','><','gs')::XML; + RETURN output; +END; +$F$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION unapi.holdings_xml (bid BIGINT, ouid INT, org TEXT, depth INT DEFAULT NULL, includes TEXT[] DEFAULT NULL::TEXT[], slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE) RETURNS XML AS $F$ + SELECT XMLELEMENT( + name holdings, + XMLATTRIBUTES( + CASE WHEN $8 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns, + CASE WHEN ('bre' = ANY ($5)) THEN 'tag:open-ils.org:U2@bre/' || $1 || '/' || $3 ELSE NULL END AS id + ), + XMLELEMENT( + name counts, + (SELECT XMLAGG(XMLELEMENT::XML) FROM ( + SELECT XMLELEMENT( + name count, + XMLATTRIBUTES('public' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow) + )::text + FROM asset.opac_ou_record_copy_count($2, $1) + UNION + SELECT XMLELEMENT( + name count, + XMLATTRIBUTES('staff' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow) + )::text + FROM asset.staff_ou_record_copy_count($2, $1) + ORDER BY 1 + )x) + ), + CASE + WHEN ('bmp' = ANY ($5)) THEN + XMLELEMENT( + name monograph_parts, + (SELECT XMLAGG(bmp) FROM ( + SELECT unapi.bmp( id, 'xml', 'monograph_part', evergreen.array_remove_item_by_value( evergreen.array_remove_item_by_value($5,'bre'), 'holdings_xml'), $3, $4, $6, $7, FALSE) + FROM biblio.monograph_part + WHERE record = $1 + )x) + ) + ELSE NULL + END, + XMLELEMENT( + name volumes, + (SELECT XMLAGG(acn) FROM ( + SELECT unapi.acn(acn.id,'xml','volume',array_remove_item_by_value( evergreen.array_remove_item_by_value($5,'holdings_xml'),'bre'), $3, $4, $6, $7, FALSE) + FROM asset.call_number acn + WHERE acn.record = $1 + AND EXISTS ( + SELECT 1 + FROM asset.copy acp + JOIN actor.org_unit_descendants( + $2, + (COALESCE( + $4, + (SELECT aout.depth + FROM actor.org_unit_type aout + JOIN actor.org_unit aou ON (aou.ou_type = aout.id AND aou.id = $2) + ) + )) + ) aoud ON (acp.circ_lib = aoud.id) + LIMIT 1 + ) + ORDER BY label_sortkey + LIMIT $6 + OFFSET $7 + )x) + ), + CASE WHEN ('ssub' = ANY ($5)) THEN + XMLELEMENT( + name subscriptions, + (SELECT XMLAGG(ssub) FROM ( + SELECT unapi.ssub(id,'xml','subscription','{}'::TEXT[], $3, $4, $6, $7, FALSE) + FROM serial.subscription + WHERE record_entry = $1 + )x) + ) + ELSE NULL END, + CASE WHEN ('acp' = ANY ($5)) THEN + XMLELEMENT( + name foreign_copies, + (SELECT XMLAGG(acp) FROM ( + SELECT unapi.acp(p.target_copy,'xml','copy','{}'::TEXT[], $3, $4, $6, $7, FALSE) + FROM biblio.peer_bib_copy_map p + JOIN asset.copy c ON (p.target_copy = c.id) + WHERE NOT c.deleted AND peer_record = $1 + )x) + ) + ELSE NULL END + ); +$F$ LANGUAGE SQL; + +CREATE OR REPLACE FUNCTION unapi.ssub ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$ + SELECT XMLELEMENT( + name subscription, + XMLATTRIBUTES( + CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns, + 'tag:open-ils.org:U2@ssub/' || id AS id, + start_date AS start, end_date AS end, expected_date_offset + ), + unapi.aou( owning_lib, $2, 'owning_lib', evergreen.array_remove_item_by_value($4,'ssub'), $5, $6, $7, $8), + XMLELEMENT( name distributions, + CASE + WHEN ('sdist' = ANY ($4)) THEN + (SELECT XMLAGG(sdist) FROM ( + SELECT unapi.sdist( id, 'xml', 'distribution', evergreen.array_remove_item_by_value($4,'ssub'), $5, $6, $7, $8, FALSE) + FROM serial.distribution + WHERE subscription = ssub.id + )x) + ELSE NULL + END + ) + ) + FROM serial.subscription ssub + WHERE id = $1 + GROUP BY id, start_date, end_date, expected_date_offset, owning_lib; +$F$ LANGUAGE SQL; + +CREATE OR REPLACE FUNCTION unapi.sdist ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$ + SELECT XMLELEMENT( + name distribution, + XMLATTRIBUTES( + CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns, + 'tag:open-ils.org:U2@sdist/' || id AS id, + 'tag:open-ils.org:U2@acn/' || receive_call_number AS receive_call_number, + 'tag:open-ils.org:U2@acn/' || bind_call_number AS bind_call_number, + unit_label_prefix, label, unit_label_suffix, summary_method + ), + unapi.aou( holding_lib, $2, 'holding_lib', evergreen.array_remove_item_by_value($4,'sdist'), $5, $6, $7, $8), + CASE WHEN subscription IS NOT NULL AND ('ssub' = ANY ($4)) THEN unapi.ssub( subscription, 'xml', 'subscription', evergreen.array_remove_item_by_value($4,'sdist'), $5, $6, $7, $8, FALSE) ELSE NULL END, + XMLELEMENT( name streams, + CASE + WHEN ('sstr' = ANY ($4)) THEN + (SELECT XMLAGG(sstr) FROM ( + SELECT unapi.sstr( id, 'xml', 'stream', evergreen.array_remove_item_by_value($4,'sdist'), $5, $6, $7, $8, FALSE) + FROM serial.stream + WHERE distribution = sdist.id + )x) + ELSE NULL + END + ), + XMLELEMENT( name summaries, + CASE + WHEN ('ssum' = ANY ($4)) THEN + (SELECT XMLAGG(sbsum) FROM ( + SELECT unapi.sbsum( id, 'xml', 'serial_summary', evergreen.array_remove_item_by_value($4,'sdist'), $5, $6, $7, $8, FALSE) + FROM serial.basic_summary + WHERE distribution = sdist.id + )x) + ELSE NULL + END, + CASE + WHEN ('ssum' = ANY ($4)) THEN + (SELECT XMLAGG(sisum) FROM ( + SELECT unapi.sisum( id, 'xml', 'serial_summary', evergreen.array_remove_item_by_value($4,'sdist'), $5, $6, $7, $8, FALSE) + FROM serial.index_summary + WHERE distribution = sdist.id + )x) + ELSE NULL + END, + CASE + WHEN ('ssum' = ANY ($4)) THEN + (SELECT XMLAGG(sssum) FROM ( + SELECT unapi.sssum( id, 'xml', 'serial_summary', evergreen.array_remove_item_by_value($4,'sdist'), $5, $6, $7, $8, FALSE) + FROM serial.supplement_summary + WHERE distribution = sdist.id + )x) + ELSE NULL + END + ) + ) + FROM serial.distribution sdist + WHERE id = $1 + GROUP BY id, label, unit_label_prefix, unit_label_suffix, holding_lib, summary_method, subscription, receive_call_number, bind_call_number; +$F$ LANGUAGE SQL; + +CREATE OR REPLACE FUNCTION unapi.sstr ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$ + SELECT XMLELEMENT( + name stream, + XMLATTRIBUTES( + CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns, + 'tag:open-ils.org:U2@sstr/' || id AS id, + routing_label + ), + CASE WHEN distribution IS NOT NULL AND ('sdist' = ANY ($4)) THEN unapi.sssum( distribution, 'xml', 'distribtion', evergreen.array_remove_item_by_value($4,'sstr'), $5, $6, $7, $8, FALSE) ELSE NULL END, + XMLELEMENT( name items, + CASE + WHEN ('sitem' = ANY ($4)) THEN + (SELECT XMLAGG(sitem) FROM ( + SELECT unapi.sitem( id, 'xml', 'serial_item', evergreen.array_remove_item_by_value($4,'sstr'), $5, $6, $7, $8, FALSE) + FROM serial.item + WHERE stream = sstr.id + )x) + ELSE NULL + END + ) + ) + FROM serial.stream sstr + WHERE id = $1 + GROUP BY id, routing_label, distribution; +$F$ LANGUAGE SQL; + +CREATE OR REPLACE FUNCTION unapi.siss ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$ + SELECT XMLELEMENT( + name issuance, + XMLATTRIBUTES( + CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns, + 'tag:open-ils.org:U2@siss/' || id AS id, + create_date, edit_date, label, date_published, + holding_code, holding_type, holding_link_id + ), + CASE WHEN subscription IS NOT NULL AND ('ssub' = ANY ($4)) THEN unapi.ssub( subscription, 'xml', 'subscription', evergreen.array_remove_item_by_value($4,'siss'), $5, $6, $7, $8, FALSE) ELSE NULL END, + XMLELEMENT( name items, + CASE + WHEN ('sitem' = ANY ($4)) THEN + (SELECT XMLAGG(sitem) FROM ( + SELECT unapi.sitem( id, 'xml', 'serial_item', evergreen.array_remove_item_by_value($4,'siss'), $5, $6, $7, $8, FALSE) + FROM serial.item + WHERE issuance = sstr.id + )x) + ELSE NULL + END + ) + ) + FROM serial.issuance sstr + WHERE id = $1 + GROUP BY id, create_date, edit_date, label, date_published, holding_code, holding_type, holding_link_id, subscription; +$F$ LANGUAGE SQL; + +CREATE OR REPLACE FUNCTION unapi.sitem ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$ + SELECT XMLELEMENT( + name serial_item, + XMLATTRIBUTES( + CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns, + 'tag:open-ils.org:U2@sitem/' || id AS id, + 'tag:open-ils.org:U2@siss/' || issuance AS issuance, + date_expected, date_received + ), + CASE WHEN issuance IS NOT NULL AND ('siss' = ANY ($4)) THEN unapi.siss( issuance, $2, 'issuance', evergreen.array_remove_item_by_value($4,'sitem'), $5, $6, $7, $8, FALSE) ELSE NULL END, + CASE WHEN stream IS NOT NULL AND ('sstr' = ANY ($4)) THEN unapi.sstr( stream, $2, 'stream', evergreen.array_remove_item_by_value($4,'sitem'), $5, $6, $7, $8, FALSE) ELSE NULL END, + CASE WHEN unit IS NOT NULL AND ('sunit' = ANY ($4)) THEN unapi.sunit( stream, $2, 'serial_unit', evergreen.array_remove_item_by_value($4,'sitem'), $5, $6, $7, $8, FALSE) ELSE NULL END, + CASE WHEN uri IS NOT NULL AND ('auri' = ANY ($4)) THEN unapi.auri( uri, $2, 'uri', evergreen.array_remove_item_by_value($4,'sitem'), $5, $6, $7, $8, FALSE) ELSE NULL END +-- XMLELEMENT( name notes, +-- CASE +-- WHEN ('acpn' = ANY ($4)) THEN +-- (SELECT XMLAGG(acpn) FROM ( +-- SELECT unapi.acpn( id, 'xml', 'copy_note', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8) +-- FROM asset.copy_note +-- WHERE owning_copy = cp.id AND pub +-- )x) +-- ELSE NULL +-- END +-- ) + ) + FROM serial.item sitem + WHERE id = $1; +$F$ LANGUAGE SQL; + + +CREATE OR REPLACE FUNCTION unapi.bmp ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$ + SELECT XMLELEMENT( + name monograph_part, + XMLATTRIBUTES( + CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns, + 'tag:open-ils.org:U2@bmp/' || id AS id, + id AS ident, + label, + label_sortkey, + 'tag:open-ils.org:U2@bre/' || record AS record + ), + CASE + WHEN ('acp' = ANY ($4)) THEN + XMLELEMENT( name copies, + (SELECT XMLAGG(acp) FROM ( + SELECT unapi.acp( cp.id, 'xml', 'copy', evergreen.array_remove_item_by_value($4,'bmp'), $5, $6, $7, $8, FALSE) + FROM asset.copy cp + JOIN asset.copy_part_map cpm ON (cpm.target_copy = cp.id) + WHERE cpm.part = $1 + ORDER BY COALESCE(cp.copy_number,0), cp.barcode + LIMIT $7 + OFFSET $8 + )x) + ) + ELSE NULL + END, + CASE WHEN ('bre' = ANY ($4)) THEN unapi.bre( record, 'marcxml', 'record', evergreen.array_remove_item_by_value($4,'bmp'), $5, $6, $7, $8, FALSE) ELSE NULL END + ) + FROM biblio.monograph_part + WHERE id = $1 + GROUP BY id, label, label_sortkey, record; +$F$ LANGUAGE SQL; + +CREATE OR REPLACE FUNCTION unapi.acp ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$ + SELECT XMLELEMENT( + name copy, + XMLATTRIBUTES( + CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns, + 'tag:open-ils.org:U2@acp/' || id AS id, + create_date, edit_date, copy_number, circulate, deposit, + ref, holdable, deleted, deposit_amount, price, barcode, + circ_modifier, circ_as_type, opac_visible + ), + unapi.ccs( status, $2, 'status', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE), + unapi.acl( location, $2, 'location', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE), + unapi.aou( circ_lib, $2, 'circ_lib', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8), + unapi.aou( circ_lib, $2, 'circlib', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8), + CASE WHEN ('acn' = ANY ($4)) THEN unapi.acn( call_number, $2, 'call_number', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE) ELSE NULL END, + XMLELEMENT( name copy_notes, + CASE + WHEN ('acpn' = ANY ($4)) THEN + (SELECT XMLAGG(acpn) FROM ( + SELECT unapi.acpn( id, 'xml', 'copy_note', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE) + FROM asset.copy_note + WHERE owning_copy = cp.id AND pub + )x) + ELSE NULL + END + ), + XMLELEMENT( name statcats, + CASE + WHEN ('ascecm' = ANY ($4)) THEN + (SELECT XMLAGG(ascecm) FROM ( + SELECT unapi.ascecm( stat_cat_entry, 'xml', 'statcat', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE) + FROM asset.stat_cat_entry_copy_map + WHERE owning_copy = cp.id + )x) + ELSE NULL + END + ), + XMLELEMENT( name foreign_records, + CASE + WHEN ('bre' = ANY ($4)) THEN + (SELECT XMLAGG(bre) FROM ( + SELECT unapi.bre(peer_record,'marcxml','record','{}'::TEXT[], $5, $6, $7, $8, FALSE) + FROM biblio.peer_bib_copy_map + WHERE target_copy = cp.id + )x) + ELSE NULL + END + + ), + CASE + WHEN ('bmp' = ANY ($4)) THEN + XMLELEMENT( name monograph_parts, + (SELECT XMLAGG(bmp) FROM ( + SELECT unapi.bmp( part, 'xml', 'monograph_part', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE) + FROM asset.copy_part_map + WHERE target_copy = cp.id + )x) + ) + ELSE NULL + END + ) + FROM asset.copy cp + WHERE id = $1 + GROUP BY id, status, location, circ_lib, call_number, create_date, edit_date, copy_number, circulate, deposit, ref, holdable, deleted, deposit_amount, price, barcode, circ_modifier, circ_as_type, opac_visible; +$F$ LANGUAGE SQL; + +CREATE OR REPLACE FUNCTION unapi.sunit ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$ + SELECT XMLELEMENT( + name serial_unit, + XMLATTRIBUTES( + CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns, + 'tag:open-ils.org:U2@acp/' || id AS id, + create_date, edit_date, copy_number, circulate, deposit, + ref, holdable, deleted, deposit_amount, price, barcode, + circ_modifier, circ_as_type, opac_visible, status_changed_time, + floating, mint_condition, detailed_contents, sort_key, summary_contents, cost + ), + unapi.ccs( status, $2, 'status', evergreen.array_remove_item_by_value( evergreen.array_remove_item_by_value($4,'acp'),'sunit'), $5, $6, $7, $8, FALSE), + unapi.acl( location, $2, 'location', evergreen.array_remove_item_by_value( evergreen.array_remove_item_by_value($4,'acp'),'sunit'), $5, $6, $7, $8, FALSE), + unapi.aou( circ_lib, $2, 'circ_lib', evergreen.array_remove_item_by_value( evergreen.array_remove_item_by_value($4,'acp'),'sunit'), $5, $6, $7, $8), + unapi.aou( circ_lib, $2, 'circlib', evergreen.array_remove_item_by_value( evergreen.array_remove_item_by_value($4,'acp'),'sunit'), $5, $6, $7, $8), + CASE WHEN ('acn' = ANY ($4)) THEN unapi.acn( call_number, $2, 'call_number', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE) ELSE NULL END, + XMLELEMENT( name copy_notes, + CASE + WHEN ('acpn' = ANY ($4)) THEN + (SELECT XMLAGG(acpn) FROM ( + SELECT unapi.acpn( id, 'xml', 'copy_note', evergreen.array_remove_item_by_value( evergreen.array_remove_item_by_value($4,'acp'),'sunit'), $5, $6, $7, $8, FALSE) + FROM asset.copy_note + WHERE owning_copy = cp.id AND pub + )x) + ELSE NULL + END + ), + XMLELEMENT( name statcats, + CASE + WHEN ('ascecm' = ANY ($4)) THEN + (SELECT XMLAGG(ascecm) FROM ( + SELECT unapi.ascecm( stat_cat_entry, 'xml', 'statcat', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE) + FROM asset.stat_cat_entry_copy_map + WHERE owning_copy = cp.id + )x) + ELSE NULL + END + ), + XMLELEMENT( name foreign_records, + CASE + WHEN ('bre' = ANY ($4)) THEN + (SELECT XMLAGG(bre) FROM ( + SELECT unapi.bre(peer_record,'marcxml','record','{}'::TEXT[], $5, $6, $7, $8, FALSE) + FROM biblio.peer_bib_copy_map + WHERE target_copy = cp.id + )x) + ELSE NULL + END + + ), + CASE + WHEN ('bmp' = ANY ($4)) THEN + XMLELEMENT( name monograph_parts, + (SELECT XMLAGG(bmp) FROM ( + SELECT unapi.bmp( part, 'xml', 'monograph_part', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE) + FROM asset.copy_part_map + WHERE target_copy = cp.id + )x) + ) + ELSE NULL + END + ) + FROM serial.unit cp + WHERE id = $1 + GROUP BY id, status, location, circ_lib, call_number, create_date, edit_date, copy_number, circulate, floating, mint_condition, + deposit, ref, holdable, deleted, deposit_amount, price, barcode, circ_modifier, circ_as_type, opac_visible, status_changed_time, detailed_contents, sort_key, summary_contents, cost; +$F$ LANGUAGE SQL; + + + + +INSERT INTO config.upgrade_log (version) VALUES ('0568'); -- miker for tsbere + +CREATE OR REPLACE FUNCTION asset.cache_copy_visibility () RETURNS TRIGGER as $func$ +DECLARE + add_front TEXT; + add_back TEXT; + add_base_query TEXT; + add_peer_query TEXT; + remove_query TEXT; + do_add BOOLEAN := false; + do_remove BOOLEAN := false; +BEGIN + add_base_query := $$ + SELECT cp.id, cp.circ_lib, cn.record, cn.id AS call_number, cp.location, cp.status + FROM asset.copy cp + JOIN asset.call_number cn ON (cn.id = cp.call_number) + JOIN actor.org_unit a ON (cp.circ_lib = a.id) + JOIN asset.copy_location cl ON (cp.location = cl.id) + JOIN config.copy_status cs ON (cp.status = cs.id) + JOIN biblio.record_entry b ON (cn.record = b.id) + WHERE NOT cp.deleted + AND NOT cn.deleted + AND NOT b.deleted + AND cs.opac_visible + AND cl.opac_visible + AND cp.opac_visible + AND a.opac_visible + $$; + add_peer_query := $$ + SELECT cp.id, cp.circ_lib, pbcm.peer_record AS record, NULL AS call_number, cp.location, cp.status + FROM asset.copy cp + JOIN biblio.peer_bib_copy_map pbcm ON (pbcm.target_copy = cp.id) + JOIN actor.org_unit a ON (cp.circ_lib = a.id) + JOIN asset.copy_location cl ON (cp.location = cl.id) + JOIN config.copy_status cs ON (cp.status = cs.id) + WHERE NOT cp.deleted + AND cs.opac_visible + AND cl.opac_visible + AND cp.opac_visible + AND a.opac_visible + $$; + add_front := $$ + INSERT INTO asset.opac_visible_copies (copy_id, circ_lib, record) + SELECT id, circ_lib, record FROM ( + $$; + add_back := $$ + ) AS x + $$; + + remove_query := $$ DELETE FROM asset.opac_visible_copies WHERE copy_id IN ( SELECT id FROM asset.copy WHERE $$; + + IF TG_TABLE_NAME = 'peer_bib_copy_map' THEN + IF TG_OP = 'INSERT' THEN + add_peer_query := add_peer_query || ' AND cp.id = ' || NEW.target_copy || ' AND pbcm.record = ' || NEW.peer_record; + EXECUTE add_front || add_peer_query || add_back; + RETURN NEW; + ELSE + remove_query := 'DELETE FROM asset.opac_visible_copies WHERE copy_id = ' || OLD.target_copy || ' AND record = ' || OLD.peer_record || ';'; + EXECUTE remove_query; + RETURN OLD; + END IF; + END IF; + + IF TG_OP = 'INSERT' THEN + + IF TG_TABLE_NAME IN ('copy', 'unit') THEN + add_base_query := add_base_query || ' AND cp.id = ' || NEW.id; + EXECUTE add_front || add_base_query || add_back; + END IF; + + RETURN NEW; + + END IF; + + -- handle items first, since with circulation activity + -- their statuses change frequently + IF TG_TABLE_NAME IN ('copy', 'unit') THEN + + IF OLD.location <> NEW.location OR + OLD.call_number <> NEW.call_number OR + OLD.status <> NEW.status OR + OLD.circ_lib <> NEW.circ_lib THEN + -- any of these could change visibility, but + -- we'll save some queries and not try to calculate + -- the change directly + do_remove := true; + do_add := true; + ELSE + + IF OLD.deleted <> NEW.deleted THEN + IF NEW.deleted THEN + do_remove := true; + ELSE + do_add := true; + END IF; + END IF; + + IF OLD.opac_visible <> NEW.opac_visible THEN + IF OLD.opac_visible THEN + do_remove := true; + ELSIF NOT do_remove THEN -- handle edge case where deleted item + -- is also marked opac_visible + do_add := true; + END IF; + END IF; + + END IF; + + IF do_remove THEN + DELETE FROM asset.opac_visible_copies WHERE copy_id = NEW.id; + END IF; + IF do_add THEN + add_base_query := add_base_query || ' AND cp.id = ' || NEW.id; + add_peer_query := add_peer_query || ' AND cp.id = ' || NEW.id; + EXECUTE add_front || add_base_query || ' UNION ' || add_peer_query || add_back; + END IF; + + RETURN NEW; + + END IF; + + IF TG_TABLE_NAME IN ('call_number', 'record_entry') THEN -- these have a 'deleted' column + + IF OLD.deleted AND NEW.deleted THEN -- do nothing + + RETURN NEW; + + ELSIF NEW.deleted THEN -- remove rows + + IF TG_TABLE_NAME = 'call_number' THEN + DELETE FROM asset.opac_visible_copies WHERE copy_id IN (SELECT id FROM asset.copy WHERE call_number = NEW.id); + ELSIF TG_TABLE_NAME = 'record_entry' THEN + DELETE FROM asset.opac_visible_copies WHERE record = NEW.id; + END IF; + + RETURN NEW; + + ELSIF OLD.deleted THEN -- add rows + + IF TG_TABLE_NAME = 'call_number' THEN + add_base_query := add_base_query || ' AND cn.id = ' || NEW.id; + EXECUTE add_front || add_base_query || add_back; + ELSIF TG_TABLE_NAME = 'record_entry' THEN + add_base_query := add_base_query || ' AND cn.record = ' || NEW.id; + add_peer_query := add_peer_query || ' AND pbcm.record = ' || NEW.id; + EXECUTE add_front || add_base_query || ' UNION ' || add_peer_query || add_back; + END IF; + + RETURN NEW; + + END IF; + + END IF; + + IF TG_TABLE_NAME = 'call_number' THEN + + IF OLD.record <> NEW.record THEN + -- call number is linked to different bib + remove_query := remove_query || 'call_number = ' || NEW.id || ');'; + EXECUTE remove_query; + add_base_query := add_base_query || ' AND cn.id = ' || NEW.id; + EXECUTE add_front || add_base_query || add_back; + END IF; + + RETURN NEW; + + END IF; + + IF TG_TABLE_NAME IN ('record_entry') THEN + RETURN NEW; -- don't have 'opac_visible' + END IF; + + -- actor.org_unit, asset.copy_location, asset.copy_status + IF NEW.opac_visible = OLD.opac_visible THEN -- do nothing + + RETURN NEW; + + ELSIF NEW.opac_visible THEN -- add rows + + IF TG_TABLE_NAME = 'org_unit' THEN + add_base_query := add_base_query || ' AND cp.circ_lib = ' || NEW.id || ';'; + add_peer_query := add_peer_query || ' AND cp.circ_lib = ' || NEW.id || ';'; + ELSIF TG_TABLE_NAME = 'copy_location' THEN + add_base_query := add_base_query || ' AND cp.location = ' || NEW.id || ';'; + add_peer_query := add_peer_query || ' AND cp.location = ' || NEW.id || ';'; + ELSIF TG_TABLE_NAME = 'copy_status' THEN + add_base_query := add_base_query || ' AND cp.status = ' || NEW.id || ';'; + add_peer_query := add_peer_query || ' AND cp.status = ' || NEW.id || ';'; + END IF; + + EXECUTE add_front || add_base_query || ' UNION ' || add_peer_query || add_back; + + ELSE -- delete rows + + IF TG_TABLE_NAME = 'org_unit' THEN + remove_query := 'DELETE FROM asset.opac_visible_copies WHERE circ_lib = ' || NEW.id || ';'; + ELSIF TG_TABLE_NAME = 'copy_location' THEN + remove_query := remove_query || 'location = ' || NEW.id || ');'; + ELSIF TG_TABLE_NAME = 'copy_status' THEN + remove_query := remove_query || 'status = ' || NEW.id || ');'; + END IF; + + EXECUTE remove_query; + + END IF; + + RETURN NEW; +END; +$func$ LANGUAGE PLPGSQL; + + + + +INSERT INTO config.upgrade_log (version) VALUES ('0569'); --miker + +CREATE OR REPLACE FUNCTION unapi.auri ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$ + SELECT XMLELEMENT( + name uri, + XMLATTRIBUTES( + CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns, + 'tag:open-ils.org:U2@auri/' || uri.id AS id, + use_restriction, + href, + label + ), + XMLELEMENT( name copies, + CASE + WHEN ('acn' = ANY ($4)) THEN + (SELECT XMLAGG(acn) FROM (SELECT unapi.acn( call_number, 'xml', 'copy', evergreen.array_remove_item_by_value($4,'auri'), $5, $6, $7, $8, FALSE) FROM asset.uri_call_number_map WHERE uri = uri.id)x) + ELSE NULL + END + ) + ) AS x + FROM asset.uri uri + WHERE uri.id = $1 + GROUP BY uri.id, use_restriction, href, label; +$F$ LANGUAGE SQL; + + + +INSERT INTO config.upgrade_log (version) VALUES ('0570'); + +-- Not everything in 1XX tags should become part of the authorsort field +-- ($0 for example). The list of subfields chosen here is a superset of all +-- the fields found in the LoC authority mappin definitions for 1XX fields. +-- Anyway, if more fields should be here, add them. + +UPDATE config.record_attr_definition + SET sf_list = 'abcdefgklmnopqrstvxyz' + WHERE name='authorsort' AND sf_list IS NULL; + + +-- Evergreen DB patch 0571.schema.facet_normalizer.sql +-- +-- Alternate implementation of a regression fix for facet normalization +-- + + +-- check whether patch can be applied +INSERT INTO config.upgrade_log (version) VALUES ('0571'); + +-- FIXME: add/check SQL statements to perform the upgrade +CREATE OR REPLACE FUNCTION metabib.facet_normalize_trigger () RETURNS TRIGGER AS $$ +DECLARE + normalizer RECORD; + facet_text TEXT; +BEGIN + facet_text := NEW.value; + + FOR normalizer IN + SELECT n.func AS func, + n.param_count AS param_count, + m.params AS params + FROM config.index_normalizer n + JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id) + WHERE m.field = NEW.field AND m.pos < 0 + ORDER BY m.pos LOOP + + EXECUTE 'SELECT ' || normalizer.func || '(' || + quote_literal( facet_text ) || + CASE + WHEN normalizer.param_count > 0 + THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'') + ELSE '' + END || + ')' INTO facet_text; + + END LOOP; + + NEW.value = facet_text; + + RETURN NEW; +END; +$$ LANGUAGE PLPGSQL; + +CREATE TRIGGER facet_normalize_tgr + BEFORE UPDATE OR INSERT ON metabib.facet_entry + FOR EACH ROW EXECUTE PROCEDURE metabib.facet_normalize_trigger(); + + + + + +INSERT INTO config.upgrade_log (version) VALUES ('0573'); --miker + +CREATE OR REPLACE FUNCTION search.query_parser_fts ( + + param_search_ou INT, + param_depth INT, + param_query TEXT, + param_statuses INT[], + param_locations INT[], + param_offset INT, + param_check INT, + param_limit INT, + metarecord BOOL, + staff BOOL + +) RETURNS SETOF search.search_result AS $func$ +DECLARE + + current_res search.search_result%ROWTYPE; + search_org_list INT[]; + + check_limit INT; + core_limit INT; + core_offset INT; + tmp_int INT; + + core_result RECORD; + core_cursor REFCURSOR; + core_rel_query TEXT; + + total_count INT := 0; + check_count INT := 0; + deleted_count INT := 0; + visible_count INT := 0; + excluded_count INT := 0; + +BEGIN + + check_limit := COALESCE( param_check, 1000 ); + core_limit := COALESCE( param_limit, 25000 ); + core_offset := COALESCE( param_offset, 0 ); + + -- core_skip_chk := COALESCE( param_skip_chk, 1 ); + + IF param_search_ou > 0 THEN + IF param_depth IS NOT NULL THEN + SELECT array_accum(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou, param_depth ); + ELSE + SELECT array_accum(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou ); + END IF; + ELSIF param_search_ou < 0 THEN + SELECT array_accum(distinct org_unit) INTO search_org_list FROM actor.org_lasso_map WHERE lasso = -param_search_ou; + ELSIF param_search_ou = 0 THEN + -- reserved for user lassos (ou_buckets/type='lasso') with ID passed in depth ... hack? sure. + END IF; + + OPEN core_cursor FOR EXECUTE param_query; + + LOOP + + FETCH core_cursor INTO core_result; + EXIT WHEN NOT FOUND; + EXIT WHEN total_count >= core_limit; + + total_count := total_count + 1; + + CONTINUE WHEN total_count NOT BETWEEN core_offset + 1 AND check_limit + core_offset; + + check_count := check_count + 1; + + PERFORM 1 FROM biblio.record_entry b WHERE NOT b.deleted AND b.id IN ( SELECT * FROM unnest( core_result.records ) ); + IF NOT FOUND THEN + -- RAISE NOTICE ' % were all deleted ... ', core_result.records; + deleted_count := deleted_count + 1; + CONTINUE; + END IF; + + PERFORM 1 + FROM biblio.record_entry b + JOIN config.bib_source s ON (b.source = s.id) + WHERE s.transcendant + AND b.id IN ( SELECT * FROM unnest( core_result.records ) ); + + IF FOUND THEN + -- RAISE NOTICE ' % were all transcendant ... ', core_result.records; + visible_count := visible_count + 1; + + current_res.id = core_result.id; + current_res.rel = core_result.rel; + + tmp_int := 1; + IF metarecord THEN + SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id; + END IF; + + IF tmp_int = 1 THEN + current_res.record = core_result.records[1]; + ELSE + current_res.record = NULL; + END IF; + + RETURN NEXT current_res; + + CONTINUE; + END IF; + + PERFORM 1 + FROM asset.call_number cn + JOIN asset.uri_call_number_map map ON (map.call_number = cn.id) + JOIN asset.uri uri ON (map.uri = uri.id) + WHERE NOT cn.deleted + AND cn.label = '##URI##' + AND uri.active + AND ( param_locations IS NULL OR array_upper(param_locations, 1) IS NULL ) + AND cn.record IN ( SELECT * FROM unnest( core_result.records ) ) + AND cn.owning_lib IN ( SELECT * FROM unnest( search_org_list ) ) + LIMIT 1; + + IF FOUND THEN + -- RAISE NOTICE ' % have at least one URI ... ', core_result.records; + visible_count := visible_count + 1; + + current_res.id = core_result.id; + current_res.rel = core_result.rel; + + tmp_int := 1; + IF metarecord THEN + SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id; + END IF; + + IF tmp_int = 1 THEN + current_res.record = core_result.records[1]; + ELSE + current_res.record = NULL; + END IF; + + RETURN NEXT current_res; + + CONTINUE; + END IF; + + IF param_statuses IS NOT NULL AND array_upper(param_statuses, 1) > 0 THEN + + PERFORM 1 + FROM asset.call_number cn + JOIN asset.copy cp ON (cp.call_number = cn.id) + WHERE NOT cn.deleted + AND NOT cp.deleted + AND cp.status IN ( SELECT * FROM unnest( param_statuses ) ) + AND cn.record IN ( SELECT * FROM unnest( core_result.records ) ) + AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) + LIMIT 1; + + IF NOT FOUND THEN + PERFORM 1 + FROM biblio.peer_bib_copy_map pr + JOIN asset.copy cp ON (cp.id = pr.target_copy) + WHERE NOT cp.deleted + AND cp.status IN ( SELECT * FROM unnest( param_statuses ) ) + AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) ) + AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) + LIMIT 1; + + IF NOT FOUND THEN + -- RAISE NOTICE ' % and multi-home linked records were all status-excluded ... ', core_result.records; + excluded_count := excluded_count + 1; + CONTINUE; + END IF; + END IF; + + END IF; + + IF param_locations IS NOT NULL AND array_upper(param_locations, 1) > 0 THEN + + PERFORM 1 + FROM asset.call_number cn + JOIN asset.copy cp ON (cp.call_number = cn.id) + WHERE NOT cn.deleted + AND NOT cp.deleted + AND cp.location IN ( SELECT * FROM unnest( param_locations ) ) + AND cn.record IN ( SELECT * FROM unnest( core_result.records ) ) + AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) + LIMIT 1; + + IF NOT FOUND THEN + PERFORM 1 + FROM biblio.peer_bib_copy_map pr + JOIN asset.copy cp ON (cp.id = pr.target_copy) + WHERE NOT cp.deleted + AND cp.location IN ( SELECT * FROM unnest( param_locations ) ) + AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) ) + AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) + LIMIT 1; + + IF NOT FOUND THEN + -- RAISE NOTICE ' % and multi-home linked records were all copy_location-excluded ... ', core_result.records; + excluded_count := excluded_count + 1; + CONTINUE; + END IF; + END IF; + + END IF; + + IF staff IS NULL OR NOT staff THEN + + PERFORM 1 + FROM asset.opac_visible_copies + WHERE circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) + AND record IN ( SELECT * FROM unnest( core_result.records ) ) + LIMIT 1; + + IF NOT FOUND THEN + PERFORM 1 + FROM biblio.peer_bib_copy_map pr + JOIN asset.opac_visible_copies cp ON (cp.copy_id = pr.target_copy) + WHERE cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) + AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) ) + LIMIT 1; + + IF NOT FOUND THEN + + -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records; + excluded_count := excluded_count + 1; + CONTINUE; + END IF; + END IF; + + ELSE + + PERFORM 1 + FROM asset.call_number cn + JOIN asset.copy cp ON (cp.call_number = cn.id) + WHERE NOT cn.deleted + AND NOT cp.deleted + AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) + AND cn.record IN ( SELECT * FROM unnest( core_result.records ) ) + LIMIT 1; + + IF NOT FOUND THEN + + PERFORM 1 + FROM biblio.peer_bib_copy_map pr + JOIN asset.copy cp ON (cp.id = pr.target_copy) + WHERE NOT cp.deleted + AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) + AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) ) + LIMIT 1; + + IF NOT FOUND THEN + + PERFORM 1 + FROM asset.call_number cn + JOIN asset.copy cp ON (cp.call_number = cn.id) + WHERE cn.record IN ( SELECT * FROM unnest( core_result.records ) ) + AND NOT cp.deleted + LIMIT 1; + + IF FOUND THEN + -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records; + excluded_count := excluded_count + 1; + CONTINUE; + END IF; + END IF; + + END IF; + + END IF; + + visible_count := visible_count + 1; + + current_res.id = core_result.id; + current_res.rel = core_result.rel; + + tmp_int := 1; + IF metarecord THEN + SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id; + END IF; + + IF tmp_int = 1 THEN + current_res.record = core_result.records[1]; + ELSE + current_res.record = NULL; + END IF; + + RETURN NEXT current_res; + + IF visible_count % 1000 = 0 THEN + -- RAISE NOTICE ' % visible so far ... ', visible_count; + END IF; + + END LOOP; + + current_res.id = NULL; + current_res.rel = NULL; + current_res.record = NULL; + current_res.total = total_count; + current_res.checked = check_count; + current_res.deleted = deleted_count; + current_res.visible = visible_count; + current_res.excluded = excluded_count; + + CLOSE core_cursor; + + RETURN NEXT current_res; + +END; +$func$ LANGUAGE PLPGSQL; + + + +-- Evergreen DB patch 0576.fix_maintain_901_quoting.sql +-- +-- Fix for bug LP#809540 - fixes crash when inserting or updating +-- bib whose tcn_value contains regex metacharacters. +-- + +-- check whether patch can be applied +INSERT INTO config.upgrade_log (version) VALUES ('0576'); + +CREATE OR REPLACE FUNCTION evergreen.maintain_901 () RETURNS TRIGGER AS $func$ +DECLARE + use_id_for_tcn BOOLEAN; +BEGIN + -- Remove any existing 901 fields before we insert the authoritative one + NEW.marc := REGEXP_REPLACE(NEW.marc, E']*?tag="901".+?', '', 'g'); + + IF TG_TABLE_SCHEMA = 'biblio' THEN + -- Set TCN value to record ID? + SELECT enabled FROM config.global_flag INTO use_id_for_tcn + WHERE name = 'cat.bib.use_id_for_tcn'; + + IF use_id_for_tcn = 't' THEN + NEW.tcn_value := NEW.id; + END IF; + + NEW.marc := REGEXP_REPLACE( + NEW.marc, + E'()', + E'' || + '' || REPLACE(evergreen.xml_escape(NEW.tcn_value), E'\\', E'\\\\') || E'' || + '' || REPLACE(evergreen.xml_escape(NEW.tcn_source), E'\\', E'\\\\') || E'' || + '' || NEW.id || E'' || + '' || TG_TABLE_SCHEMA || E'' || + CASE WHEN NEW.owner IS NOT NULL THEN '' || NEW.owner || E'' ELSE '' END || + CASE WHEN NEW.share_depth IS NOT NULL THEN '' || NEW.share_depth || E'' ELSE '' END || + E'\\1' + ); + ELSIF TG_TABLE_SCHEMA = 'authority' THEN + NEW.marc := REGEXP_REPLACE( + NEW.marc, + E'()', + E'' || + '' || NEW.id || E'' || + '' || TG_TABLE_SCHEMA || E'' || + E'\\1' + ); + ELSIF TG_TABLE_SCHEMA = 'serial' THEN + NEW.marc := REGEXP_REPLACE( + NEW.marc, + E'()', + E'' || + '' || NEW.id || E'' || + '' || TG_TABLE_SCHEMA || E'' || + '' || NEW.owning_lib || E'' || + CASE WHEN NEW.record IS NOT NULL THEN '' || NEW.record || E'' ELSE '' END || + E'\\1' + ); + ELSE + NEW.marc := REGEXP_REPLACE( + NEW.marc, + E'()', + E'' || + '' || NEW.id || E'' || + '' || TG_TABLE_SCHEMA || E'' || + E'\\1' + ); + END IF; + + RETURN NEW; +END; +$func$ LANGUAGE PLPGSQL; + + + +INSERT INTO config.upgrade_log (version) VALUES ('0578'); -- tsbere via miker + +CREATE OR REPLACE VIEW reporter.hold_request_record AS +SELECT id, + target, + hold_type, + CASE + WHEN hold_type = 'T' + THEN target + WHEN hold_type = 'I' + THEN (SELECT ssub.record_entry FROM serial.subscription ssub JOIN serial.issuance si ON (si.subscription = ssub.id) WHERE si.id = ahr.target) + WHEN hold_type = 'V' + THEN (SELECT cn.record FROM asset.call_number cn WHERE cn.id = ahr.target) + WHEN hold_type IN ('C','R','F') + THEN (SELECT cn.record FROM asset.call_number cn JOIN asset.copy cp ON (cn.id = cp.call_number) WHERE cp.id = ahr.target) + WHEN hold_type = 'M' + THEN (SELECT mr.master_record FROM metabib.metarecord mr WHERE mr.id = ahr.target) + WHEN hold_type = 'P' + THEN (SELECT bmp.record FROM biblio.monograph_part bmp WHERE bmp.id = ahr.target) + END AS bib_record + FROM action.hold_request ahr; + + + + +INSERT INTO config.upgrade_log (version) VALUES ('0579'); -- tsbere via miker + +CREATE OR REPLACE FUNCTION action.item_user_circ_test( circ_ou INT, match_item BIGINT, match_user INT, renewal BOOL ) RETURNS SETOF action.circ_matrix_test_result AS $func$ +DECLARE + user_object actor.usr%ROWTYPE; + standing_penalty config.standing_penalty%ROWTYPE; + item_object asset.copy%ROWTYPE; + item_status_object config.copy_status%ROWTYPE; + item_location_object asset.copy_location%ROWTYPE; + result action.circ_matrix_test_result; + circ_test action.found_circ_matrix_matchpoint; + circ_matchpoint config.circ_matrix_matchpoint%ROWTYPE; + out_by_circ_mod config.circ_matrix_circ_mod_test%ROWTYPE; + circ_mod_map config.circ_matrix_circ_mod_test_map%ROWTYPE; + hold_ratio action.hold_stats%ROWTYPE; + penalty_type TEXT; + items_out INT; + context_org_list INT[]; + done BOOL := FALSE; +BEGIN + -- Assume success unless we hit a failure condition + result.success := TRUE; + + -- Need user info to look up matchpoints + SELECT INTO user_object * FROM actor.usr WHERE id = match_user AND NOT deleted; + + -- (Insta)Fail if we couldn't find the user + IF user_object.id IS NULL THEN + result.fail_part := 'no_user'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + RETURN; + END IF; + + -- Need item info to look up matchpoints + SELECT INTO item_object * FROM asset.copy WHERE id = match_item AND NOT deleted; + + -- (Insta)Fail if we couldn't find the item + IF item_object.id IS NULL THEN + result.fail_part := 'no_item'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + RETURN; + END IF; + + SELECT INTO circ_test * FROM action.find_circ_matrix_matchpoint(circ_ou, item_object, user_object, renewal); + + circ_matchpoint := circ_test.matchpoint; + result.matchpoint := circ_matchpoint.id; + result.circulate := circ_matchpoint.circulate; + result.duration_rule := circ_matchpoint.duration_rule; + result.recurring_fine_rule := circ_matchpoint.recurring_fine_rule; + result.max_fine_rule := circ_matchpoint.max_fine_rule; + result.hard_due_date := circ_matchpoint.hard_due_date; + result.renewals := circ_matchpoint.renewals; + result.grace_period := circ_matchpoint.grace_period; + result.buildrows := circ_test.buildrows; + + -- (Insta)Fail if we couldn't find a matchpoint + IF circ_test.success = false THEN + result.fail_part := 'no_matchpoint'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + RETURN; + END IF; + + -- All failures before this point are non-recoverable + -- Below this point are possibly overridable failures + + -- Fail if the user is barred + IF user_object.barred IS TRUE THEN + result.fail_part := 'actor.usr.barred'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + END IF; + + -- Fail if the item can't circulate + IF item_object.circulate IS FALSE THEN + result.fail_part := 'asset.copy.circulate'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + END IF; + + -- Fail if the item isn't in a circulateable status on a non-renewal + IF NOT renewal AND item_object.status NOT IN ( 0, 7, 8 ) THEN + result.fail_part := 'asset.copy.status'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + -- Alternately, fail if the item isn't checked out on a renewal + ELSIF renewal AND item_object.status <> 1 THEN + result.fail_part := 'asset.copy.status'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + END IF; + + -- Fail if the item can't circulate because of the shelving location + SELECT INTO item_location_object * FROM asset.copy_location WHERE id = item_object.location; + IF item_location_object.circulate IS FALSE THEN + result.fail_part := 'asset.copy_location.circulate'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + END IF; + + -- Apparently....use the circ matchpoint org unit to determine what org units are valid. + SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( circ_matchpoint.org_unit ); + + IF renewal THEN + penalty_type = '%RENEW%'; + ELSE + penalty_type = '%CIRC%'; + END IF; + + FOR standing_penalty IN + SELECT DISTINCT csp.* + FROM actor.usr_standing_penalty usp + JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty) + WHERE usr = match_user + AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) ) + AND (usp.stop_date IS NULL or usp.stop_date > NOW()) + AND csp.block_list LIKE penalty_type LOOP + + result.fail_part := standing_penalty.name; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + END LOOP; + + -- Fail if the test is set to hard non-circulating + IF circ_matchpoint.circulate IS FALSE THEN + result.fail_part := 'config.circ_matrix_test.circulate'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + END IF; + + -- Fail if the total copy-hold ratio is too low + IF circ_matchpoint.total_copy_hold_ratio IS NOT NULL THEN + SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item); + IF hold_ratio.total_copy_ratio IS NOT NULL AND hold_ratio.total_copy_ratio < circ_matchpoint.total_copy_hold_ratio THEN + result.fail_part := 'config.circ_matrix_test.total_copy_hold_ratio'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + END IF; + END IF; + + -- Fail if the available copy-hold ratio is too low + IF circ_matchpoint.available_copy_hold_ratio IS NOT NULL THEN + IF hold_ratio.hold_count IS NULL THEN + SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item); + END IF; + IF hold_ratio.available_copy_ratio IS NOT NULL AND hold_ratio.available_copy_ratio < circ_matchpoint.available_copy_hold_ratio THEN + result.fail_part := 'config.circ_matrix_test.available_copy_hold_ratio'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + END IF; + END IF; + + -- Fail if the user has too many items with specific circ_modifiers checked out + FOR out_by_circ_mod IN SELECT * FROM config.circ_matrix_circ_mod_test WHERE matchpoint = circ_matchpoint.id LOOP + SELECT INTO items_out COUNT(*) + FROM action.circulation circ + JOIN asset.copy cp ON (cp.id = circ.target_copy) + WHERE circ.usr = match_user + AND circ.circ_lib IN ( SELECT * FROM unnest(context_org_list) ) + AND circ.checkin_time IS NULL + AND (circ.stop_fines IN ('MAXFINES','LONGOVERDUE') OR circ.stop_fines IS NULL) + AND cp.circ_modifier IN (SELECT circ_mod FROM config.circ_matrix_circ_mod_test_map WHERE circ_mod_test = out_by_circ_mod.id); + IF items_out >= out_by_circ_mod.items_out THEN + result.fail_part := 'config.circ_matrix_circ_mod_test'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + END IF; + END LOOP; + + -- If we passed everything, return the successful matchpoint + IF NOT done THEN + RETURN NEXT result; + END IF; + + RETURN; +END; +$func$ LANGUAGE plpgsql; + + + + +INSERT INTO config.upgrade_log (version) VALUES ('0580'); -- tsbere via miker + +CREATE OR REPLACE FUNCTION actor.org_unit_parent_protect () RETURNS TRIGGER AS $$ + DECLARE + current_aou actor.org_unit%ROWTYPE; + seen_ous INT[]; + depth_count INT; + BEGIN + current_aou := NEW; + depth_count := 0; + seen_ous := ARRAY[NEW.id]; + IF TG_OP = 'INSERT' OR NEW.parent_ou IS DISTINCT FROM OLD.parent_ou THEN + LOOP + IF current_aou.parent_ou IS NULL THEN -- Top of the org tree? + RETURN NEW; -- No loop. Carry on. + END IF; + IF current_aou.parent_ou = ANY(seen_ous) THEN -- Parent is one we have seen? + RAISE 'OU LOOP: Saw % twice', current_aou.parent_ou; -- LOOP! ABORT! + END IF; + -- Get the next one! + SELECT INTO current_aou * FROM actor.org_unit WHERE id = current_aou.parent_ou; + seen_ous := seen_ous || current_aou.id; + depth_count := depth_count + 1; + IF depth_count = 100 THEN + RAISE 'OU CHECK TOO DEEP'; + END IF; + END LOOP; + END IF; + RETURN NEW; + END; +$$ LANGUAGE PLPGSQL; + +CREATE TRIGGER actor_org_unit_parent_protect_trigger + BEFORE INSERT OR UPDATE ON actor.org_unit FOR EACH ROW + EXECUTE PROCEDURE actor.org_unit_parent_protect (); + + + + +INSERT INTO config.upgrade_log (version) VALUES ('0581'); -- tsbere via miker + +INSERT INTO config.global_flag (name, label, enabled) + VALUES ( + 'circ.opac_renewal.use_original_circ_lib', + oils_i18n_gettext( + 'circ.opac_renewal.use_original_circ_lib', + 'Circ: Use original circulation library on opac renewal instead of user home library', + 'cgf', + 'label' + ), + FALSE + ); + + + + +INSERT INTO config.upgrade_log (version) VALUES ('0582'); -- miker + +CREATE OR REPLACE VIEW action.all_circulation AS + 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.aged_circulation + UNION ALL + SELECT DISTINCT circ.id,COALESCE(a.post_code,b.post_code) AS usr_post_code, p.home_ou AS usr_home_ou, p.profile AS usr_profile, EXTRACT(YEAR FROM p.dob)::INT AS usr_birth_year, + cp.call_number AS copy_call_number, cp.location AS copy_location, cn.owning_lib AS copy_owning_lib, cp.circ_lib AS copy_circ_lib, + cn.record AS copy_bib_record, circ.xact_start, circ.xact_finish, circ.target_copy, circ.circ_lib, circ.circ_staff, circ.checkin_staff, + circ.checkin_lib, circ.renewal_remaining, circ.grace_period, circ.due_date, circ.stop_fines_time, circ.checkin_time, circ.create_time, circ.duration, + circ.fine_interval, circ.recurring_fine, circ.max_fine, circ.phone_renewal, circ.desk_renewal, circ.opac_renewal, circ.duration_rule, + circ.recurring_fine_rule, circ.max_fine_rule, circ.stop_fines, circ.workstation, circ.checkin_workstation, circ.checkin_scan_time, + circ.parent_circ + FROM action.circulation circ + JOIN asset.copy cp ON (circ.target_copy = cp.id) + JOIN asset.call_number cn ON (cp.call_number = cn.id) + JOIN actor.usr p ON (circ.usr = p.id) + LEFT JOIN actor.usr_address a ON (p.mailing_address = a.id) + LEFT JOIN actor.usr_address b ON (p.billing_address = b.id); + + + +-- Evergreen DB patch 0583.schema.aging_circ_view.sql +-- +-- This implements the same update as 0582.schema.aging_circ_view.sql, +-- and exists purely the the sake of avoid a potential upgrade +-- glitch for somebody who's upgrading from 2.0.8+. +-- +-- + +-- check whether patch can be applied +INSERT INTO config.upgrade_log (version) VALUES ('0583'); + +CREATE OR REPLACE VIEW action.all_circulation AS + 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.aged_circulation + UNION ALL + SELECT DISTINCT circ.id,COALESCE(a.post_code,b.post_code) AS usr_post_code, p.home_ou AS usr_home_ou, p.profile AS usr_profile, EXTRACT(YEAR FROM p.dob)::INT AS usr_birth_year, + cp.call_number AS copy_call_number, cp.location AS copy_location, cn.owning_lib AS copy_owning_lib, cp.circ_lib AS copy_circ_lib, + cn.record AS copy_bib_record, circ.xact_start, circ.xact_finish, circ.target_copy, circ.circ_lib, circ.circ_staff, circ.checkin_staff, + circ.checkin_lib, circ.renewal_remaining, circ.grace_period, circ.due_date, circ.stop_fines_time, circ.checkin_time, circ.create_time, circ.duration, + circ.fine_interval, circ.recurring_fine, circ.max_fine, circ.phone_renewal, circ.desk_renewal, circ.opac_renewal, circ.duration_rule, + circ.recurring_fine_rule, circ.max_fine_rule, circ.stop_fines, circ.workstation, circ.checkin_workstation, circ.checkin_scan_time, + circ.parent_circ + FROM action.circulation circ + JOIN asset.copy cp ON (circ.target_copy = cp.id) + JOIN asset.call_number cn ON (cp.call_number = cn.id) + JOIN actor.usr p ON (circ.usr = p.id) + LEFT JOIN actor.usr_address a ON (p.mailing_address = a.id) + LEFT JOIN actor.usr_address b ON (p.billing_address = b.id); + + + + + -- do potentially large updates last to save time if upgrader needs -- to manually tweak the upgrade script to resolve errors -- 2.11.0