From a79eb3f30a68b1a6b41387e1d47ef65857853112 Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Mon, 29 Aug 2016 14:04:53 +0100 Subject: [PATCH] Bug 17216: Add new table authorised_value_categories and populate it This patch set adds a new table authorised_value_categories to store authori(s|z)ed value categories into a separate table. The problematic is explained on bug 15799 comment 4: We need FK to the AV categories but some may not have authorized values yet. What does this patch set: - Add a new authorised_value_categories table - Populate it with known categories - Update FK items_search_fields.authorised_values_category - Create a new FK marc_subfield_structure.authorised_value (FIXME should be authorised_value_categories instead) They are some problems this patch set do not take into account: - The .sql installer files won't insert correctly (will have to be updated when this patch set will be ready to be pushed) - All the categories (even the ones without authorized values defined) are listed when you edit frameworks (marc_subfield_structure.pl) - There is no way to delete a category (TODO). But to do so it would be good to have a authorised_value_categories.is_internal field to mark some categories as "cannot be deleted". Test plan: 0/ Execute the DB entry to create and populate the new table and set the FK 1/ Create a new AV category from the admin module (admin/authorised_values.pl) 2/ Add/edit subfield linked to a AV category (admin/marc_subfield_structure.pl) 3/ You won't be allowed to add AV for branches, itemtypes or cn_source. They are used internally. Signed-off-by: Owen Leonard Signed-off-by: Katrin Fischer Signed-off-by: Brendan Gallagher --- .../atomicupdate/bug_17216_1_add_table.sql | 38 +++++++++++++++++++ .../data/mysql/en/mandatory/auth_values.sql | 20 ++++++++++ installer/data/mysql/kohastructure.sql | 13 ++++++- 3 files changed, 70 insertions(+), 1 deletion(-) create mode 100644 installer/data/mysql/atomicupdate/bug_17216_1_add_table.sql diff --git a/installer/data/mysql/atomicupdate/bug_17216_1_add_table.sql b/installer/data/mysql/atomicupdate/bug_17216_1_add_table.sql new file mode 100644 index 0000000000..221a7e760c --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_17216_1_add_table.sql @@ -0,0 +1,38 @@ +CREATE TABLE authorised_value_categories ( + category_name VARCHAR(32) NOT NULL, + primary key (category_name) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ; + +-- Add authorised value categories +INSERT INTO authorised_value_categories (category_name ) + SELECT DISTINCT category FROM authorised_values; + +-- Add special categories +INSERT IGNORE INTO authorised_value_categories( category_name ) + VALUES + ('Asort1'), + ('Asort2'), + ('Bsort1'), + ('Bsort2'), + ('SUGGEST'), + ('DAMAGED'), + ('LOST'), + ('REPORT_GROUP'), + ('REPORT_SUBGROUP'), + ('DEPARTMENT'), + ('TERM'), + ('SUGGEST_STATUS'), + ('ITEMTYPECAT'); + +-- Add very special categories +INSERT IGNORE INTO authorised_value_categories( category_name ) + VALUES + ('branches'), + ('itemtypes'), + ('cn_source'); + +-- Update the FK +ALTER TABLE items_search_fields + DROP FOREIGN KEY items_search_fields_authorised_values_category; +ALTER TABLE items_search_fields + ADD CONSTRAINT `items_search_fields_authorised_values_category` FOREIGN KEY (`authorised_values_category`) REFERENCES `authorised_value_categories` (`category_name`) ON DELETE SET NULL ON UPDATE CASCADE; diff --git a/installer/data/mysql/en/mandatory/auth_values.sql b/installer/data/mysql/en/mandatory/auth_values.sql index d7fb280625..e0785f0bce 100644 --- a/installer/data/mysql/en/mandatory/auth_values.sql +++ b/installer/data/mysql/en/mandatory/auth_values.sql @@ -1,2 +1,22 @@ INSERT INTO authorised_values (category,authorised_value,lib,lib_opac) VALUES ('YES_NO','0','No','No'); INSERT INTO authorised_values (category,authorised_value,lib,lib_opac) VALUES ('YES_NO','1','Yes','Yes'); +INSERT IGNORE INTO authorised_value_categories( category_name ) + VALUES + ('Asort1'), + ('Asort2'), + ('Bsort1'), + ('Bsort2'), + ('SUGGEST'), + ('DAMAGED'), + ('LOST'), + ('REPORT_GROUP'), + ('REPORT_SUBGROUP'), + ('DEPARTMENT'), + ('TERM'), + ('SUGGEST_STATUS'), + ('ITEMTYPECAT'); +INSERT IGNORE INTO authorised_value_categories( category_name ) + VALUES + ('branches'), + ('itemtypes'), + ('cn_source'); diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index e38efc58ad..a87753edf7 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -92,6 +92,17 @@ CREATE TABLE `auth_tag_structure` ( CONSTRAINT `auth_tag_structure_ibfk_1` FOREIGN KEY (`authtypecode`) REFERENCES `auth_types` (`authtypecode`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; + +-- +-- Table structure for table `authorised_value_categories` +-- + +DROP TABLE IF EXISTS `authorised_value_categories`; +CREATE TABLE `authorised_value_categories` ( + `category_name` varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', + PRIMARY KEY (`category_name`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; + -- -- Table structure for table `authorised_values` -- @@ -3576,7 +3587,7 @@ CREATE TABLE items_search_fields ( authorised_values_category VARCHAR(32) NULL DEFAULT NULL, PRIMARY KEY(name), CONSTRAINT items_search_fields_authorised_values_category - FOREIGN KEY (authorised_values_category) REFERENCES authorised_values (category) + FOREIGN KEY (authorised_values_category) REFERENCES authorised_value_categories (category_name) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- 2.39.5