@@ -, +, @@ authorised_value_categories and populate it - 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) - 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". --- .../mysql/atomicupdate/bug_17216_1_add_table.sql | 38 ++++++++++++++++++++++ installer/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 --- a/installer/data/mysql/atomicupdate/bug_17216_1_add_table.sql +++ a/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; --- a/installer/data/mysql/en/mandatory/auth_values.sql +++ a/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'); --- a/installer/data/mysql/kohastructure.sql +++ a/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; --