From 190a97332783944ced933cb7132b36e498941170 Mon Sep 17 00:00:00 2001 From: Bill Erickson Date: Mon, 30 Nov 2015 15:47:48 -0500 Subject: [PATCH] Sqitch bits for 0946-0949 Signed-off-by: Bill Erickson --- .../sql/schema/deploy/function.lpad_number_fix.sql | 16 +++ .../schema.batch_settings_retrieve_function.sql | 34 +++++ .../sql/schema/deploy/schema.fake-delete-parts.sql | 140 +++++++++++++++++++++ .../deploy/schema.no_delete_acq_cancel_reasons.sql | 22 ++++ .../sql/schema/revert/function.lpad_number_fix.sql | 7 ++ .../schema.batch_settings_retrieve_function.sql | 7 ++ .../sql/schema/revert/schema.fake-delete-parts.sql | 7 ++ .../revert/schema.no_delete_acq_cancel_reasons.sql | 7 ++ Open-ILS/src/sql/schema/sqitch.plan | 4 + .../sql/schema/verify/function.lpad_number_fix.sql | 7 ++ .../schema.batch_settings_retrieve_function.sql | 7 ++ .../sql/schema/verify/schema.fake-delete-parts.sql | 7 ++ .../verify/schema.no_delete_acq_cancel_reasons.sql | 7 ++ 13 files changed, 272 insertions(+) create mode 100644 Open-ILS/src/sql/schema/deploy/function.lpad_number_fix.sql create mode 100644 Open-ILS/src/sql/schema/deploy/schema.batch_settings_retrieve_function.sql create mode 100644 Open-ILS/src/sql/schema/deploy/schema.fake-delete-parts.sql create mode 100644 Open-ILS/src/sql/schema/deploy/schema.no_delete_acq_cancel_reasons.sql create mode 100644 Open-ILS/src/sql/schema/revert/function.lpad_number_fix.sql create mode 100644 Open-ILS/src/sql/schema/revert/schema.batch_settings_retrieve_function.sql create mode 100644 Open-ILS/src/sql/schema/revert/schema.fake-delete-parts.sql create mode 100644 Open-ILS/src/sql/schema/revert/schema.no_delete_acq_cancel_reasons.sql create mode 100644 Open-ILS/src/sql/schema/verify/function.lpad_number_fix.sql create mode 100644 Open-ILS/src/sql/schema/verify/schema.batch_settings_retrieve_function.sql create mode 100644 Open-ILS/src/sql/schema/verify/schema.fake-delete-parts.sql create mode 100644 Open-ILS/src/sql/schema/verify/schema.no_delete_acq_cancel_reasons.sql diff --git a/Open-ILS/src/sql/schema/deploy/function.lpad_number_fix.sql b/Open-ILS/src/sql/schema/deploy/function.lpad_number_fix.sql new file mode 100644 index 0000000000..6d6fefee76 --- /dev/null +++ b/Open-ILS/src/sql/schema/deploy/function.lpad_number_fix.sql @@ -0,0 +1,16 @@ +-- Deploy evergreen:function.lpad_number_fix to pg +-- requires: schema.batch_settings_retrieve_function + +BEGIN; + +CREATE OR REPLACE FUNCTION evergreen.lpad_number_substrings( TEXT, TEXT, INT ) RETURNS TEXT AS $$ + my $string = shift; # Source string + my $pad = shift; # string to fill. Typically '0'. This should be a single character. + my $len = shift; # length of resultant padded field + + $string =~ s/([0-9]+)/$pad x ($len - length($1)) . $1/eg; + + return $string; +$$ LANGUAGE PLPERLU; + +COMMIT; diff --git a/Open-ILS/src/sql/schema/deploy/schema.batch_settings_retrieve_function.sql b/Open-ILS/src/sql/schema/deploy/schema.batch_settings_retrieve_function.sql new file mode 100644 index 0000000000..a7476325e6 --- /dev/null +++ b/Open-ILS/src/sql/schema/deploy/schema.batch_settings_retrieve_function.sql @@ -0,0 +1,34 @@ +-- Deploy evergreen:schema.batch_settings_retrieve_function to pg +-- requires: schema.dob-as-date + +BEGIN; + +CREATE OR REPLACE FUNCTION actor.org_unit_ancestor_setting_batch( org_id INT, VARIADIC setting_names TEXT[] ) RETURNS SETOF actor.org_unit_setting AS $$ +DECLARE + setting RECORD; + setting_name TEXT; + cur_org INT; +BEGIN + FOREACH setting_name IN ARRAY setting_names + LOOP + cur_org := org_id; + LOOP + SELECT INTO setting * FROM actor.org_unit_setting WHERE org_unit = cur_org AND name = setting_name; + IF FOUND THEN + RETURN NEXT setting; + EXIT; + END IF; + SELECT INTO cur_org parent_ou FROM actor.org_unit WHERE id = cur_org; + EXIT WHEN cur_org IS NULL; + END LOOP; + END LOOP; + RETURN; +END; +$$ LANGUAGE plpgsql STABLE; + +COMMENT ON FUNCTION actor.org_unit_ancestor_setting_batch( INT, VARIADIC TEXT[] ) IS $$ +For each setting name passed, search "up" the org_unit tree until +we find the first occurrence of an org_unit_setting with the given name. +$$; + +COMMIT; diff --git a/Open-ILS/src/sql/schema/deploy/schema.fake-delete-parts.sql b/Open-ILS/src/sql/schema/deploy/schema.fake-delete-parts.sql new file mode 100644 index 0000000000..d3d38301da --- /dev/null +++ b/Open-ILS/src/sql/schema/deploy/schema.fake-delete-parts.sql @@ -0,0 +1,140 @@ +-- Deploy evergreen:schema.fake-delete-parts to pg +-- requires: function.lpad_number_fix + +BEGIN; + +ALTER TABLE biblio.monograph_part ADD COLUMN deleted BOOL NOT NULL DEFAULT FALSE; +CREATE RULE protect_mono_part_delete AS ON DELETE TO biblio.monograph_part DO INSTEAD ( + UPDATE biblio.monograph_part SET deleted = TRUE WHERE OLD.id = biblio.monograph_part.id; + DELETE FROM asset.copy_part_map WHERE part = OLD.id +); + +CREATE OR REPLACE FUNCTION unapi.holdings_xml ( + bid BIGINT, + ouid INT, + org TEXT, + depth INT DEFAULT NULL, + includes TEXT[] DEFAULT NULL::TEXT[], + slimit HSTORE DEFAULT NULL, + soffset HSTORE DEFAULT NULL, + include_xmlns BOOL DEFAULT TRUE, + pref_lib INT DEFAULT NULL +) +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, + (SELECT record_has_holdable_copy FROM asset.record_has_holdable_copy($1)) AS has_holdable + ), + 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) + UNION + SELECT XMLELEMENT( + name count, + XMLATTRIBUTES('pref_lib' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow) + )::text + FROM asset.opac_ou_record_copy_count($9, $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 NOT deleted AND record = $1 + )x) + ) + ELSE NULL + END, + XMLELEMENT( + name volumes, + (SELECT XMLAGG(acn ORDER BY rank, name, label_sortkey) FROM ( + -- Physical copies + SELECT unapi.acn(y.id,'xml','volume',evergreen.array_remove_item_by_value( evergreen.array_remove_item_by_value($5,'holdings_xml'),'bre'), $3, $4, $6, $7, FALSE), y.rank, name, label_sortkey + FROM evergreen.ranked_volumes($1, $2, $4, $6, $7, $9, $5) AS y + UNION ALL + -- Located URIs + SELECT unapi.acn(uris.id,'xml','volume',evergreen.array_remove_item_by_value( evergreen.array_remove_item_by_value($5,'holdings_xml'),'bre'), $3, $4, $6, $7, FALSE), uris.rank, name, label_sortkey + FROM evergreen.located_uris($1, $2, $9) AS uris + )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',evergreen.array_remove_item_by_value($5,'acp'), $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 p.peer_record = $1 + LIMIT ($6 -> 'acp')::INT + OFFSET ($7 -> 'acp')::INT + )x) + ) + ELSE NULL END + ); +$F$ LANGUAGE SQL STABLE; + +CREATE OR REPLACE FUNCTION unapi.bmp ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit HSTORE DEFAULT NULL, soffset HSTORE 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 + AND cp.deleted IS FALSE + ORDER BY COALESCE(cp.copy_number,0), cp.barcode + LIMIT ($7 -> 'acp')::INT + OFFSET ($8 -> 'acp')::INT + + )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 NOT deleted AND id = $1 + GROUP BY id, label, label_sortkey, record; +$F$ LANGUAGE SQL STABLE; + + +COMMIT; diff --git a/Open-ILS/src/sql/schema/deploy/schema.no_delete_acq_cancel_reasons.sql b/Open-ILS/src/sql/schema/deploy/schema.no_delete_acq_cancel_reasons.sql new file mode 100644 index 0000000000..2d5fbeb3ac --- /dev/null +++ b/Open-ILS/src/sql/schema/deploy/schema.no_delete_acq_cancel_reasons.sql @@ -0,0 +1,22 @@ +-- Deploy evergreen:schema.no_delete_acq_cancel_reasons to pg +-- requires: schema.fake-delete-parts + +BEGIN; + +CREATE OR REPLACE FUNCTION evergreen.protect_reserved_rows_from_delete() RETURNS trigger AS $protect_reserved$ +BEGIN +IF OLD.id < TG_ARGV[0]::INT THEN + RAISE EXCEPTION 'Cannot delete row with reserved ID %', OLD.id; +END IF; +END +$protect_reserved$ +LANGUAGE plpgsql; + +DROP TRIGGER IF EXISTS acq_no_deleted_reserved_cancel_reasons ON acq.cancel_reason; + +CREATE TRIGGER acq_no_deleted_reserved_cancel_reasons BEFORE DELETE ON acq.cancel_reason + FOR EACH ROW EXECUTE PROCEDURE evergreen.protect_reserved_rows_from_delete(2000); + +ALTER TABLE acq.cancel_reason ENABLE TRIGGER acq_no_deleted_reserved_cancel_reasons; + +COMMIT; diff --git a/Open-ILS/src/sql/schema/revert/function.lpad_number_fix.sql b/Open-ILS/src/sql/schema/revert/function.lpad_number_fix.sql new file mode 100644 index 0000000000..d9b951ccc7 --- /dev/null +++ b/Open-ILS/src/sql/schema/revert/function.lpad_number_fix.sql @@ -0,0 +1,7 @@ +-- Revert evergreen:function.lpad_number_fix from pg + +BEGIN; + +-- XXX Add DDLs here. + +COMMIT; diff --git a/Open-ILS/src/sql/schema/revert/schema.batch_settings_retrieve_function.sql b/Open-ILS/src/sql/schema/revert/schema.batch_settings_retrieve_function.sql new file mode 100644 index 0000000000..c7a6d290aa --- /dev/null +++ b/Open-ILS/src/sql/schema/revert/schema.batch_settings_retrieve_function.sql @@ -0,0 +1,7 @@ +-- Revert evergreen:schema.batch_settings_retrieve_function from pg + +BEGIN; + +-- XXX Add DDLs here. + +COMMIT; diff --git a/Open-ILS/src/sql/schema/revert/schema.fake-delete-parts.sql b/Open-ILS/src/sql/schema/revert/schema.fake-delete-parts.sql new file mode 100644 index 0000000000..120158b380 --- /dev/null +++ b/Open-ILS/src/sql/schema/revert/schema.fake-delete-parts.sql @@ -0,0 +1,7 @@ +-- Revert evergreen:schema.fake-delete-parts from pg + +BEGIN; + +-- XXX Add DDLs here. + +COMMIT; diff --git a/Open-ILS/src/sql/schema/revert/schema.no_delete_acq_cancel_reasons.sql b/Open-ILS/src/sql/schema/revert/schema.no_delete_acq_cancel_reasons.sql new file mode 100644 index 0000000000..e4e6b1fecb --- /dev/null +++ b/Open-ILS/src/sql/schema/revert/schema.no_delete_acq_cancel_reasons.sql @@ -0,0 +1,7 @@ +-- Revert evergreen:schema.no_delete_acq_cancel_reasons from pg + +BEGIN; + +-- XXX Add DDLs here. + +COMMIT; diff --git a/Open-ILS/src/sql/schema/sqitch.plan b/Open-ILS/src/sql/schema/sqitch.plan index b1a74df2ce..3a4a1ac314 100644 --- a/Open-ILS/src/sql/schema/sqitch.plan +++ b/Open-ILS/src/sql/schema/sqitch.plan @@ -73,3 +73,7 @@ schema.filter_authority_browse_search_by_thesaurus [data.authority.control_set_a data.additional_authority_fixed_fields [schema.filter_authority_browse_search_by_thesaurus] 2015-10-15T14:06:55Z Bill Erickson # data.additional_authority_fixed_fields data.adjust_bills_perm [data.additional_authority_fixed_fields] 2015-10-15T14:08:04Z Bill Erickson # data.additional_authority_fixed_fields schema.dob-as-date [data.adjust_bills_perm] 2015-10-15T14:19:48Z Bill Erickson # schema.dob-as-date +schema.batch_settings_retrieve_function [schema.dob-as-date] 2015-11-30T20:37:22Z Bill Erickson # schema.batch_settings_retrieve_function +function.lpad_number_fix [schema.batch_settings_retrieve_function] 2015-11-30T20:38:26Z Bill Erickson # function.lpad_number_fix +schema.fake-delete-parts [function.lpad_number_fix] 2015-11-30T20:38:51Z Bill Erickson # schema.fake-delete-parts +schema.no_delete_acq_cancel_reasons [schema.fake-delete-parts] 2015-11-30T20:39:23Z Bill Erickson # schema.no_delete_acq_cancel_reasons diff --git a/Open-ILS/src/sql/schema/verify/function.lpad_number_fix.sql b/Open-ILS/src/sql/schema/verify/function.lpad_number_fix.sql new file mode 100644 index 0000000000..ca4e563216 --- /dev/null +++ b/Open-ILS/src/sql/schema/verify/function.lpad_number_fix.sql @@ -0,0 +1,7 @@ +-- Verify evergreen:function.lpad_number_fix on pg + +BEGIN; + +-- XXX Add verifications here. + +ROLLBACK; diff --git a/Open-ILS/src/sql/schema/verify/schema.batch_settings_retrieve_function.sql b/Open-ILS/src/sql/schema/verify/schema.batch_settings_retrieve_function.sql new file mode 100644 index 0000000000..42c1815725 --- /dev/null +++ b/Open-ILS/src/sql/schema/verify/schema.batch_settings_retrieve_function.sql @@ -0,0 +1,7 @@ +-- Verify evergreen:schema.batch_settings_retrieve_function on pg + +BEGIN; + +-- XXX Add verifications here. + +ROLLBACK; diff --git a/Open-ILS/src/sql/schema/verify/schema.fake-delete-parts.sql b/Open-ILS/src/sql/schema/verify/schema.fake-delete-parts.sql new file mode 100644 index 0000000000..34b37eaf9e --- /dev/null +++ b/Open-ILS/src/sql/schema/verify/schema.fake-delete-parts.sql @@ -0,0 +1,7 @@ +-- Verify evergreen:schema.fake-delete-parts on pg + +BEGIN; + +-- XXX Add verifications here. + +ROLLBACK; diff --git a/Open-ILS/src/sql/schema/verify/schema.no_delete_acq_cancel_reasons.sql b/Open-ILS/src/sql/schema/verify/schema.no_delete_acq_cancel_reasons.sql new file mode 100644 index 0000000000..d50aa53c71 --- /dev/null +++ b/Open-ILS/src/sql/schema/verify/schema.no_delete_acq_cancel_reasons.sql @@ -0,0 +1,7 @@ +-- Verify evergreen:schema.no_delete_acq_cancel_reasons on pg + +BEGIN; + +-- XXX Add verifications here. + +ROLLBACK; -- 2.11.0