From 3fe925ee1900d607eca6eb8952430e02a85c4d24 Mon Sep 17 00:00:00 2001 From: Brendan Gallagher Date: Tue, 11 Oct 2016 11:18:50 +0000 Subject: [PATCH] UPdating the DBREv for Bug 17216 - Add a new table to store authorized value categories --- Koha.pm | 2 +- .../atomicupdate/bug_17216_1_add_table.sql | 49 ----------- .../mysql/atomicupdate/bug_17216_2_add_fk.sql | 7 -- installer/data/mysql/updatedatabase.pl | 88 +++++++++++++++++++ 4 files changed, 89 insertions(+), 57 deletions(-) delete mode 100644 installer/data/mysql/atomicupdate/bug_17216_1_add_table.sql delete mode 100644 installer/data/mysql/atomicupdate/bug_17216_2_add_fk.sql diff --git a/Koha.pm b/Koha.pm index 63ee3e11b3..8d638e00d7 100644 --- a/Koha.pm +++ b/Koha.pm @@ -29,7 +29,7 @@ use vars qw{ $VERSION }; # - #4 : the developer version. The 4th number is the database subversion. # used by developers when the database changes. updatedatabase take care of the changes itself # and is automatically called by Auth.pm when needed. -$VERSION = "16.06.00.032"; +$VERSION = "16.06.00.033"; sub version { return $VERSION; diff --git a/installer/data/mysql/atomicupdate/bug_17216_1_add_table.sql b/installer/data/mysql/atomicupdate/bug_17216_1_add_table.sql deleted file mode 100644 index 07dcb83734..0000000000 --- a/installer/data/mysql/atomicupdate/bug_17216_1_add_table.sql +++ /dev/null @@ -1,49 +0,0 @@ -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'); - -INSERT IGNORE INTO authorised_value_categories( category_name ) - VALUES - ('WITHDRAWN'), - ('RESTRICTED'), - ('NOT_LOAN'), - ('CCODE'), - ('LOC'), - ('STACK'); - --- 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; -ALTER TABLE authorised_values - ADD CONSTRAINT `authorised_values_authorised_values_category` FOREIGN KEY (`category`) REFERENCES `authorised_value_categories` (`category_name`) ON DELETE CASCADE ON UPDATE CASCADE; diff --git a/installer/data/mysql/atomicupdate/bug_17216_2_add_fk.sql b/installer/data/mysql/atomicupdate/bug_17216_2_add_fk.sql deleted file mode 100644 index 22b9ceb8eb..0000000000 --- a/installer/data/mysql/atomicupdate/bug_17216_2_add_fk.sql +++ /dev/null @@ -1,7 +0,0 @@ -INSERT IGNORE INTO authorised_value_categories( category_name ) SELECT DISTINCT(authorised_value) FROM marc_subfield_structure; - -UPDATE marc_subfield_structure SET authorised_value = NULL WHERE authorised_value = ';'; - -ALTER TABLE marc_subfield_structure - MODIFY COLUMN authorised_value VARCHAR(32) DEFAULT NULL, - ADD CONSTRAINT marc_subfield_structure_ibfk_1 FOREIGN KEY (authorised_value) REFERENCES authorised_value_categories (category_name) ON UPDATE CASCADE ON DELETE SET NULL; diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index b642105c23..7d67fe2aa1 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -13094,6 +13094,94 @@ if ( CheckVersion($DBversion) ) { } +$DBversion = "16.06.00.033"; +if ( CheckVersion($DBversion) ) { + $dbh->do(q{ + 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 + $dbh->do(q{ + INSERT INTO authorised_value_categories (category_name ) + SELECT DISTINCT category FROM authorised_values; + }); + +## Add special categories + $dbh->do(q{ + 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 + $dbh->do(q{ + INSERT IGNORE INTO authorised_value_categories( category_name ) + VALUES + ('branches'), + ('itemtypes'), + ('cn_source'); + }); + + $dbh->do(q{ + INSERT IGNORE INTO authorised_value_categories( category_name ) + VALUES + ('WITHDRAWN'), + ('RESTRICTED'), + ('NOT_LOAN'), + ('CCODE'), + ('LOC'), + ('STACK'); + }); + +## Update the FK + $dbh->do(q{ + ALTER TABLE items_search_fields + DROP FOREIGN KEY items_search_fields_authorised_values_category; + }); + + $dbh->do(q{ + 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; + }); + + $dbh->do(q{ + ALTER TABLE authorised_values + ADD CONSTRAINT `authorised_values_authorised_values_category` FOREIGN KEY (`category`) REFERENCES `authorised_value_categories` (`category_name`) ON DELETE CASCADE ON UPDATE CASCADE; + }); + + $dbh->do(q{ + INSERT IGNORE INTO authorised_value_categories( category_name ) SELECT DISTINCT(authorised_value) FROM marc_subfield_structure; + }); + + $dbh->do(q{ + UPDATE marc_subfield_structure SET authorised_value = NULL WHERE authorised_value = ';'; + }); + + $dbh->do(q{ + ALTER TABLE marc_subfield_structure + MODIFY COLUMN authorised_value VARCHAR(32) DEFAULT NULL, + ADD CONSTRAINT marc_subfield_structure_ibfk_1 FOREIGN KEY (authorised_value) REFERENCES authorised_value_categories (category_name) ON UPDATE CASCADE ON DELETE SET NULL; + }); + + print "Upgrade to $DBversion done (Bug 17216 - Add a new table to store authorized value categories)\n"; + SetVersion($DBversion); +} + + # DEVELOPER PROCESS, search for anything to execute in the db_update directory # SEE bug 13068 # if there is anything in the atomicupdate, read and execute it. -- 2.39.2