From 0da6edee161f256f3d167d489bc7e9922e030548 Mon Sep 17 00:00:00 2001 From: Galen Charlton Date: Fri, 17 Mar 2017 17:46:37 -0400 Subject: [PATCH] LP#1673857: schema, IDL, library settings & perms for copy tags Three new tables are added to store copy tags: * config.copy_tag_type Defines types that can be used for distinguishing between classes of copy tags when searching the catalog. The seed data includes a 'bookplate' type by default. The new permission ADMIN_COPY_TAG_TYPES controls C/U/D access to this table. * asset.copy_tag The actual copy tag values. Copy tags have both labels and values, and since at least one interface allows creating copy tags on the fly, a trigger will set the value of a new tag to its label if the value is null. asset.copy_tag also has a flag for setting whether given tag should be searchable (and visible) in the public catalog or not. The new permission ADMIN_COPY_TAG controls C/U/D access to this table. * asset.copy_tag_copy_map This stores the link between copies and their tags. Only the UPDATE_COPY permission is required in order to set tag mappings. The new library setting is opac.search.enable_bookplate_search, which controls whether or not to display a "Digital Bookplate" entry in the catalog search fields dropdowns. Signed-off-by: Galen Charlton Signed-off-by: Josh Stompro Signed-off-by: Galen Charlton Conflicts: Open-ILS/src/sql/Pg/950.data.seed-values.sql Signed-off-by: Galen Charlton --- Open-ILS/examples/fm_IDL.xml | 68 +++++++++++++ Open-ILS/src/sql/Pg/002.schema.config.sql | 9 ++ Open-ILS/src/sql/Pg/040.schema.asset.sql | 55 +++++++++++ Open-ILS/src/sql/Pg/800.fkeys.sql | 2 + Open-ILS/src/sql/Pg/950.data.seed-values.sql | 28 +++++- .../sql/Pg/upgrade/XXXX.schema.copy_tags.sql | 96 +++++++++++++++++++ 6 files changed, 257 insertions(+), 1 deletion(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.copy_tags.sql diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index f84734a7df..bc99527096 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -6854,6 +6854,7 @@ SELECT usr, + @@ -6879,6 +6880,7 @@ SELECT usr, + @@ -11608,6 +11610,72 @@ SELECT usr, + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + SELECT ahcm.target_copy AS id,count(*) AS count diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index 07d491e9f7..9edf3b1ef2 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -1219,4 +1219,13 @@ ALTER TABLE config.marc_subfield ) ); +CREATE TABLE config.copy_tag_type ( + code TEXT NOT NULL PRIMARY KEY, + label TEXT NOT NULL, + owner INTEGER NOT NULL -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED +); + +CREATE INDEX config_copy_tag_type_owner_idx + ON config.copy_tag_type (owner); + COMMIT; diff --git a/Open-ILS/src/sql/Pg/040.schema.asset.sql b/Open-ILS/src/sql/Pg/040.schema.asset.sql index 283b476bbd..267b3024db 100644 --- a/Open-ILS/src/sql/Pg/040.schema.asset.sql +++ b/Open-ILS/src/sql/Pg/040.schema.asset.sql @@ -905,5 +905,60 @@ BEGIN END; $F$ LANGUAGE PLPGSQL; +CREATE TABLE asset.copy_tag ( + id SERIAL PRIMARY KEY, + tag_type TEXT REFERENCES config.copy_tag_type (code) + ON UPDATE CASCADE ON DELETE CASCADE, + label TEXT NOT NULL, + value TEXT NOT NULL, + index_vector tsvector NOT NULL, + staff_note TEXT, + pub BOOLEAN DEFAULT TRUE, + owner INTEGER NOT NULL REFERENCES actor.org_unit (id) +); + +CREATE INDEX asset_copy_tag_label_idx + ON asset.copy_tag (label); +CREATE INDEX asset_copy_tag_label_lower_idx + ON asset.copy_tag (evergreen.lowercase(label)); +CREATE INDEX asset_copy_tag_index_vector_idx + ON asset.copy_tag + USING GIN(index_vector); +CREATE INDEX asset_copy_tag_tag_type_idx + ON asset.copy_tag (tag_type); +CREATE INDEX asset_copy_tag_owner_idx + ON asset.copy_tag (owner); + +CREATE OR REPLACE FUNCTION asset.set_copy_tag_value () RETURNS TRIGGER AS $$ +BEGIN + IF NEW.value IS NULL THEN + NEW.value = NEW.label; + END IF; + + RETURN NEW; +END; +$$ LANGUAGE 'plpgsql'; + +-- name of following trigger chosen to ensure it runs first +CREATE TRIGGER asset_copy_tag_do_value + BEFORE INSERT OR UPDATE ON asset.copy_tag + FOR EACH ROW EXECUTE PROCEDURE asset.set_copy_tag_value(); +CREATE TRIGGER asset_copy_tag_fti_trigger + BEFORE UPDATE OR INSERT ON asset.copy_tag + FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('default'); + +CREATE TABLE asset.copy_tag_copy_map ( + id BIGSERIAL PRIMARY KEY, + copy BIGINT REFERENCES asset.copy (id) + ON UPDATE CASCADE ON DELETE CASCADE, + tag INTEGER REFERENCES asset.copy_tag (id) + ON UPDATE CASCADE ON DELETE CASCADE +); + +CREATE INDEX asset_copy_tag_copy_map_copy_idx + ON asset.copy_tag_copy_map (copy); +CREATE INDEX asset_copy_tag_copy_map_tag_idx + ON asset.copy_tag_copy_map (tag); + COMMIT; diff --git a/Open-ILS/src/sql/Pg/800.fkeys.sql b/Open-ILS/src/sql/Pg/800.fkeys.sql index ed2e79f285..7d10125289 100644 --- a/Open-ILS/src/sql/Pg/800.fkeys.sql +++ b/Open-ILS/src/sql/Pg/800.fkeys.sql @@ -175,4 +175,6 @@ ALTER TABLE asset.copy_template ADD CONSTRAINT asset_copy_template_floating_fkey ALTER TABLE config.marc_field ADD CONSTRAINT config_marc_field_owner_fkey FOREIGN KEY (owner) REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED; ALTER TABLE config.marc_subfield ADD CONSTRAINT config_marc_subfield_owner_fkey FOREIGN KEY (owner) REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE config.copy_tag_type ADD CONSTRAINT copy_tag_type_owner_fkey FOREIGN KEY (owner) REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED; + COMMIT; diff --git a/Open-ILS/src/sql/Pg/950.data.seed-values.sql b/Open-ILS/src/sql/Pg/950.data.seed-values.sql index ffa673286d..eef768d400 100644 --- a/Open-ILS/src/sql/Pg/950.data.seed-values.sql +++ b/Open-ILS/src/sql/Pg/950.data.seed-values.sql @@ -1675,7 +1675,11 @@ INSERT INTO permission.perm_list ( id, code, description ) VALUES ( 588, 'ITEM_NOT_HOLDABLE.override', oils_i18n_gettext( 588, 'Override the ITEM_NOT_HOLDABLE event', 'ppl', 'description' )), ( 589, 'ITEM_RENTAL_FEE_REQUIRED.override', oils_i18n_gettext( 589, - 'Override the ITEM_RENTAL_FEE_REQUIRED event', 'ppl', 'description' )) + 'Override the ITEM_RENTAL_FEE_REQUIRED event', 'ppl', 'description' )), + ( 590, 'ADMIN_COPY_TAG_TYPES', oils_i18n_gettext( 590, + 'Administer copy tag types', 'ppl', 'description' )), + ( 591, 'ADMIN_COPY_TAG', oils_i18n_gettext( 591, + 'Administer copy tag', 'ppl', 'description' )) ; SELECT SETVAL('permission.perm_list_id_seq'::TEXT, 1000); @@ -16876,3 +16880,25 @@ INSERT into config.org_unit_setting_type ( ) ,'string' ); + +INSERT INTO config.org_unit_setting_type + (name, label, description, grp, datatype) +VALUES ( + 'opac.search.enable_bookplate_search', + oils_i18n_gettext( + 'opac.search.enable_bookplate_search', + 'Enable Digital Bookplate Search', + 'coust', + 'label' + ), + oils_i18n_gettext( + 'opac.search.enable_bookplate_search', + 'If enabled, adds a "Digital Bookplate" option to the query type selectors in the public catalog for search on copy tags.', + 'coust', + 'description' + ), + 'opac', + 'bool' +); + +INSERT INTO config.copy_tag_type (code, label, owner) VALUES ('bookplate', 'Digital Bookplate', 1); diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.copy_tags.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.copy_tags.sql new file mode 100644 index 0000000000..07cabb94a8 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.copy_tags.sql @@ -0,0 +1,96 @@ +BEGIN; + +CREATE TABLE config.copy_tag_type ( + code TEXT NOT NULL PRIMARY KEY, + label TEXT NOT NULL, + owner INTEGER NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED +); + +CREATE INDEX config_copy_tag_type_owner_idx + ON config.copy_tag_type (owner); + +CREATE TABLE asset.copy_tag ( + id SERIAL PRIMARY KEY, + tag_type TEXT REFERENCES config.copy_tag_type (code) + ON UPDATE CASCADE ON DELETE CASCADE, + label TEXT NOT NULL, + value TEXT NOT NULL, + index_vector tsvector NOT NULL, + staff_note TEXT, + pub BOOLEAN DEFAULT TRUE, + owner INTEGER NOT NULL REFERENCES actor.org_unit (id) +); + +CREATE INDEX asset_copy_tag_label_idx + ON asset.copy_tag (label); +CREATE INDEX asset_copy_tag_label_lower_idx + ON asset.copy_tag (evergreen.lowercase(label)); +CREATE INDEX asset_copy_tag_index_vector_idx + ON asset.copy_tag + USING GIN(index_vector); +CREATE INDEX asset_copy_tag_tag_type_idx + ON asset.copy_tag (tag_type); +CREATE INDEX asset_copy_tag_owner_idx + ON asset.copy_tag (owner); + +CREATE OR REPLACE FUNCTION asset.set_copy_tag_value () RETURNS TRIGGER AS $$ +BEGIN + IF NEW.value IS NULL THEN + NEW.value = NEW.label; + END IF; + + RETURN NEW; +END; +$$ LANGUAGE 'plpgsql'; + +-- name of following trigger chosen to ensure it runs first +CREATE TRIGGER asset_copy_tag_do_value + BEFORE INSERT OR UPDATE ON asset.copy_tag + FOR EACH ROW EXECUTE PROCEDURE asset.set_copy_tag_value(); +CREATE TRIGGER asset_copy_tag_fti_trigger + BEFORE UPDATE OR INSERT ON asset.copy_tag + FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('default'); + +CREATE TABLE asset.copy_tag_copy_map ( + id BIGSERIAL PRIMARY KEY, + copy BIGINT REFERENCES asset.copy (id) + ON UPDATE CASCADE ON DELETE CASCADE, + tag INTEGER REFERENCES asset.copy_tag (id) + ON UPDATE CASCADE ON DELETE CASCADE +); + +CREATE INDEX asset_copy_tag_copy_map_copy_idx + ON asset.copy_tag_copy_map (copy); +CREATE INDEX asset_copy_tag_copy_map_tag_idx + ON asset.copy_tag_copy_map (tag); + +INSERT INTO config.copy_tag_type (code, label, owner) VALUES ('bookplate', 'Digital Bookplate', 1); + +INSERT INTO permission.perm_list ( id, code, description ) VALUES + ( 590, 'ADMIN_COPY_TAG_TYPES', oils_i18n_gettext( 590, + 'Administer copy tag types', 'ppl', 'description' )), + ( 591, 'ADMIN_COPY_TAG', oils_i18n_gettext( 591, + 'Administer copy tag', 'ppl', 'description' )) +; + +INSERT INTO config.org_unit_setting_type + (name, label, description, grp, datatype) +VALUES ( + 'opac.search.enable_bookplate_search', + oils_i18n_gettext( + 'opac.search.enable_bookplate_search', + 'Enable Digital Bookplate Search', + 'coust', + 'label' + ), + oils_i18n_gettext( + 'opac.search.enable_bookplate_search', + 'If enabled, adds a "Digital Bookplate" option to the query type selectors in the public catalog for search on copy tags.', + 'coust', + 'description' + ), + 'opac', + 'bool' +); + +COMMIT; -- 2.43.2