Attachment 60656 Details for Bug 18097 – Bug 18097: Add FK for authorised values

[patch] Bug 18097: Add FK for authorised values

Bug-18097-Add-FK-for-authorised-values.patch (text/plain), 8.46 KB, created by Jonathan Druart on 2017-02-24 14:07:30 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Jonathan Druart

Created: 2017-02-24 14:07:30 UTC

Size: 8.46 KB

patch

obsolete

>From 931a2a41d6671ee695c866b7437c1cd201583e94 Mon Sep 17 00:00:00 2001
>From: Jonathan Druart <jonathan.druart@bugs.koha-community.org>
>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

Actions: View | Diff | Splinter Review

Attachments on bug 18097: 60121 | 60656