From 931a2a41d6671ee695c866b7437c1cd201583e94 Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Fri, 24 Feb 2017 15:07:16 +0100 Subject: [PATCH] Bug 18097: Add FK for authorised values --- installer/data/mysql/kohastructure.sql | 32 ++++++++++++++++++-------------- 1 file changed, 18 insertions(+), 14 deletions(-) diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 117c14f..773d6fb 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -61,7 +61,7 @@ CREATE TABLE `auth_subfield_structure` ( `repeatable` tinyint(4) NOT NULL default 0, `mandatory` tinyint(4) NOT NULL default 0, `tab` tinyint(1) default NULL, - `authorised_value` varchar(10) default NULL, + `authorised_value` varchar(32) default NULL, `value_builder` varchar(80) default NULL, `seealso` varchar(255) default NULL, `isurl` tinyint(1) default NULL, @@ -72,7 +72,8 @@ CREATE TABLE `auth_subfield_structure` ( `defaultvalue` TEXT, PRIMARY KEY (`authtypecode`,`tagfield`,`tagsubfield`), KEY `tab` (`authtypecode`,`tab`), - CONSTRAINT `auth_subfield_structure_ibfk_1` FOREIGN KEY (`authtypecode`) REFERENCES `auth_types` (`authtypecode`) ON DELETE CASCADE ON UPDATE CASCADE + CONSTRAINT `auth_subfield_structure_ibfk_1` FOREIGN KEY (`authtypecode`) REFERENCES `auth_types` (`authtypecode`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `auth_subfield_structure_ibfk_2` FOREIGN KEY (`authorised_value`) REFERENCES `authorised_value_categories` (`category_name`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- @@ -87,9 +88,10 @@ CREATE TABLE `auth_tag_structure` ( `libopac` varchar(255) NOT NULL default '', `repeatable` tinyint(4) NOT NULL default 0, `mandatory` tinyint(4) NOT NULL default 0, - `authorised_value` varchar(10) default NULL, + `authorised_value` varchar(32) default NULL, PRIMARY KEY (`authtypecode`,`tagfield`), - CONSTRAINT `auth_tag_structure_ibfk_1` FOREIGN KEY (`authtypecode`) REFERENCES `auth_types` (`authtypecode`) ON DELETE CASCADE ON UPDATE CASCADE + CONSTRAINT `auth_tag_structure_ibfk_1` FOREIGN KEY (`authtypecode`) REFERENCES `auth_types` (`authtypecode`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `auth_tag_structure_ibfk_2` FOREIGN KEY (`authorised_value`) REFERENCES `authorised_value_categories` (`category_name`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; @@ -220,7 +222,7 @@ CREATE TABLE `borrower_attribute_types` ( -- definitions for custom patron field `category_code` VARCHAR(10) NULL DEFAULT NULL,-- defines a category for an attribute_type `class` VARCHAR(255) NOT NULL DEFAULT '',-- defines a class for an attribute_type PRIMARY KEY (`code`), - KEY `auth_val_cat_idx` (`authorised_value_category`) + CONSTRAINT `bat_ibfk_1` FOREIGN KEY (`authorised_value_category`) REFERENCES `authorised_value_categories` (`category_name`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- @@ -676,7 +678,7 @@ CREATE TABLE `deleteditems` ( `onloan` date default NULL, -- defines if item is checked out (NULL for not checked out, and checkout date for checked out) `cn_source` varchar(10) default NULL, -- classification source used on this item (MARC21 952$2) `cn_sort` varchar(255) default NULL, -- normalized form of the call number (MARC21 952$o) used for sorting - `ccode` varchar(10) default NULL, -- authorized value for the collection code associated with this item (MARC21 952$8) + `ccode` varchar(80) default NULL, -- authorized value for the collection code associated with this item (MARC21 952$8) `materials` text default NULL, -- materials specified (MARC21 952$3) `uri` varchar(255) default NULL, -- URL for the item (MARC21 952$u) `itype` varchar(10) default NULL, -- foreign key from the itemtypes table defining the type for this item (MARC21 952$y) @@ -933,7 +935,7 @@ CREATE TABLE `items` ( -- holdings/item information `onloan` date default NULL, -- defines if item is checked out (NULL for not checked out, and checkout date for checked out) `cn_source` varchar(10) default NULL, -- classification source used on this item (MARC21 952$2) `cn_sort` varchar(255) default NULL, -- normalized form of the call number (MARC21 952$o) used for sorting - `ccode` varchar(10) default NULL, -- authorized value for the collection code associated with this item (MARC21 952$8) + `ccode` varchar(80) default NULL, -- authorized value for the collection code associated with this item (MARC21 952$8) `materials` text default NULL, -- materials specified (MARC21 952$3) `uri` varchar(255) default NULL, -- URL for the item (MARC21 952$u) `itype` varchar(10) default NULL, -- foreign key from the itemtypes table defining the type for this item (MARC21 952$y) @@ -1149,9 +1151,10 @@ CREATE TABLE `marc_tag_structure` ( `libopac` varchar(255) NOT NULL default '', `repeatable` tinyint(4) NOT NULL default 0, `mandatory` tinyint(4) NOT NULL default 0, - `authorised_value` varchar(10) default NULL, + `authorised_value` varchar(32) default NULL, `frameworkcode` varchar(4) NOT NULL default '', - PRIMARY KEY (`frameworkcode`,`tagfield`) + PRIMARY KEY (`frameworkcode`,`tagfield`), + CONSTRAINT `marc_tag_structure_ibfk_1` FOREIGN KEY (`authorised_value`) REFERENCES `authorised_value_categories` (`category_name`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- @@ -1991,7 +1994,7 @@ CREATE TABLE `statistics` ( -- information related to transactions (circulation `itemtype` varchar(10) default NULL, -- foreign key from the itemtypes table, links transaction to a specific item type `borrowernumber` int(11) default NULL, -- foreign key from the borrowers table, links transaction to a specific borrower `associatedborrower` int(11) default NULL, -- unused in Koha - `ccode` varchar(10) default NULL, -- foreign key from the items table, links transaction to a specific collection code + `ccode` varchar(80) default NULL, -- foreign key from the items table, links transaction to a specific collection code KEY `timeidx` (`datetime`), KEY `branch_idx` (`branch`), KEY `proccode_idx` (`proccode`), @@ -2644,7 +2647,7 @@ CREATE TABLE branch_transfer_limits ( toBranch varchar(10) NOT NULL, fromBranch varchar(10) NOT NULL, itemtype varchar(10) NULL, - ccode varchar(10) NULL, + ccode varchar(80) NULL, PRIMARY KEY (limitId) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; @@ -3635,11 +3638,12 @@ CREATE TABLE `additional_fields` ( `id` int(11) NOT NULL AUTO_INCREMENT, -- primary key identifier `tablename` varchar(255) NOT NULL DEFAULT '', -- tablename of the new field `name` varchar(255) NOT NULL DEFAULT '', -- name of the field - `authorised_value_category` varchar(16) NOT NULL DEFAULT '', -- is an authorised value category + `authorised_value_category` varchar(32) NOT NULL DEFAULT '', -- is an authorised value category `marcfield` varchar(16) NOT NULL DEFAULT '', -- contains the marc field to copied into the record `searchable` tinyint(1) NOT NULL DEFAULT '0', -- is the field searchable? PRIMARY KEY (`id`), - UNIQUE KEY `fields_uniq` (`tablename`,`name`) + UNIQUE KEY `fields_uniq` (`tablename`,`name`), + CONSTRAINT `additional_fields_ibfk_1` FOREIGN KEY (`authorised_value`) REFERENCES `authorised_value_categories` (`category_name`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- @@ -3760,7 +3764,7 @@ CREATE TABLE `course_items` ( `ci_id` int(11) NOT NULL AUTO_INCREMENT, -- course item id `itemnumber` int(11) NOT NULL, -- items.itemnumber for the item on reserve `itype` varchar(10) DEFAULT NULL, -- new itemtype for the item to have while on reserve (optional) - `ccode` varchar(10) DEFAULT NULL, -- new category code for the item to have while on reserve (optional) + `ccode` varchar(80) DEFAULT NULL, -- new category code for the item to have while on reserve (optional) `holdingbranch` varchar(10) DEFAULT NULL, -- new holding branch for the item to have while on reserve (optional) `location` varchar(80) DEFAULT NULL, -- new shelving location for the item to have while on reseve (optional) `enabled` enum('yes','no') NOT NULL DEFAULT 'no', -- if at least one enabled course has this item on reseve, this field will be 'yes', otherwise it will be 'no' -- 2.9.3