From 8844f63c028d249b294ebdedfa00216fd26d56eb Mon Sep 17 00:00:00 2001 From: Josef Moravec Date: Sun, 12 Nov 2017 10:49:40 +0000 Subject: [PATCH] Bug 19520: Update database --- ...dd_more_information_to_rotating_collections.perl | 21 +++++++++++++++++++++ installer/data/mysql/kohastructure.sql | 6 ++++++ 2 files changed, 27 insertions(+) create mode 100644 installer/data/mysql/atomicupdate/Bug19520_Add_more_information_to_rotating_collections.perl diff --git a/installer/data/mysql/atomicupdate/Bug19520_Add_more_information_to_rotating_collections.perl b/installer/data/mysql/atomicupdate/Bug19520_Add_more_information_to_rotating_collections.perl new file mode 100644 index 0000000000..a344e98392 --- /dev/null +++ b/installer/data/mysql/atomicupdate/Bug19520_Add_more_information_to_rotating_collections.perl @@ -0,0 +1,21 @@ +$DBversion = 'XXX'; # will be replaced by the RM +if( CheckVersion( $DBversion ) ) { + + if( !column_exists( 'collections', 'createdBy' ) ) { + $dbh->do( "ALTER TABLE collections ADD COLUMN createdBy int(11) default NULL AFTER colBranchcode" ); + } + + $dbh->do( "ALTER TABLE collections ADD CONSTRAINT `collections_ibfk_2` FOREIGN KEY (`createdBy`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE"); + + if( !column_exists( 'collections', 'createdOn' ) ) { + $dbh->do( "ALTER TABLE collections ADD COLUMN createdOn datetime default NULL AFTER createdBy" ); + } + + if( !column_exists( 'collections', 'lastTransferredOn' ) ) { + $dbh->do( "ALTER TABLE collections ADD COLUMN lastTransferredOn datetime default NULL AFTER createdOn" ); + } + + # Always end with this (adjust the bug info) + SetVersion( $DBversion ); + print "Upgrade to $DBversion done (Bug 19520 - Add additional information to rotating collections)\n"; +} diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 496f791255..6093ecad75 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -318,6 +318,9 @@ CREATE TABLE collections ( colTitle varchar(100) NOT NULL DEFAULT '', colDesc MEDIUMTEXT NOT NULL, colBranchcode varchar(10) DEFAULT NULL, -- 'branchcode for branch where item should be held.' + createdBy int(11) default NULL, + createdOn datetime default NULL, + lastTransferredOn datetime default NULL, PRIMARY KEY (colId) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; @@ -327,6 +330,9 @@ CREATE TABLE collections ( ALTER TABLE `collections` ADD CONSTRAINT `collections_ibfk_1` FOREIGN KEY (`colBranchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE; +ALTER TABLE `collections` + ADD CONSTRAINT `collections_ibfk_2` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE; + -- -- Table structure for table `branch_borrower_circ_rules` -- -- 2.14.1