From 506d748a7d1a57f0093e96009bf055d72133c256 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 --- ..._Add_more_information_to_rotating_collections.perl | 19 +++++++++++++++++++ installer/data/mysql/kohastructure.sql | 6 ++++++ 2 files changed, 25 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 0000000..46d009e --- /dev/null +++ b/installer/data/mysql/atomicupdate/Bug19520_Add_more_information_to_rotating_collections.perl @@ -0,0 +1,19 @@ +$DBversion = 'XXX'; # will be replaced by the RM +if( CheckVersion( $DBversion ) ) { + $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"); + + $dbh->do( "ALTER TABLE collections ADD COLUMN createdOn datetime default NULL AFTER createdBy" ); + + $dbh->do( "ALTER TABLE collections ADD COLUMN lastTransferredOn datetime default NULL AFTER createdOn" ); + + # or perform some test and warn + # if( !column_exists( 'biblio', 'biblionumber' ) ) { + # warn "There is something wrong"; + # } + + # 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 2704642..453e77c 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.1.4