From 50aad6f535ff51de03451fbcbe735524a735c393 Mon Sep 17 00:00:00 2001 From: Blou Date: Fri, 5 Jun 2015 15:06:36 -0400 Subject: [PATCH] Bug 13624 - Tidying the atomicupdate file and kohastructure.sql --- .../update_13624_overduerules_transport_type.pl | 15 ++++++++------- installer/data/mysql/kohastructure.sql | 7 ++++--- 2 files changed, 12 insertions(+), 10 deletions(-) diff --git a/installer/data/mysql/atomicupdate/update_13624_overduerules_transport_type.pl b/installer/data/mysql/atomicupdate/update_13624_overduerules_transport_type.pl index a1ac778..be3af3c 100755 --- a/installer/data/mysql/atomicupdate/update_13624_overduerules_transport_type.pl +++ b/installer/data/mysql/atomicupdate/update_13624_overduerules_transport_type.pl @@ -5,13 +5,13 @@ use warnings; use C4::Context; my $dbh=C4::Context->dbh; -print "Will do : Upgrade to $DBversion done (Bug 13624 - Remove columns branchcode, categorytype from table overduerules_transport_types)\n"; +print "Will do : Bug 13624 - Remove columns branchcode, categorytype from table overduerules_transport_types\n"; #if ( CheckVersion($DBversion) ) { $dbh->do("SET FOREIGN_KEY_CHECKS=0"); $dbh->do("ALTER TABLE overduerules RENAME old_overduerules"); $dbh->do("CREATE TABLE overduerules ( - `overduerules_id` mediumint NOT NULL AUTO_INCREMENT, + `overduerules_id` int(11) NOT NULL AUTO_INCREMENT, `branchcode` varchar(10) NOT NULL DEFAULT '', `categorycode` varchar(10) NOT NULL DEFAULT '', `delay1` int(4) DEFAULT NULL, @@ -23,21 +23,22 @@ print "Will do : Upgrade to $DBversion done (Bug 13624 - Remove columns branchco `delay3` int(4) DEFAULT NULL, `letter3` varchar(20) DEFAULT NULL, `debarred3` int(1) DEFAULT '0', - PRIMARY KEY (`overduerules_id`) + PRIMARY KEY (`overduerules_id`), + UNIQUE KEY `overduerules_branch_cat` (`branchcode`,`categorycode`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;"); $dbh->do("INSERT INTO overduerules(branchcode, categorycode, delay1, letter1, debarred1, delay2, debarred2, letter2, delay3, letter3, debarred3) SELECT * FROM old_overduerules"); $dbh->do("DROP TABLE old_overduerules"); $dbh->do("ALTER TABLE overduerules_transport_types - ADD COLUMN overduerules_id mediumint NOT NULL"); - my $mtts = $dbh->selectall_arrayref("select * from overduerules_transport_types", { Slice => {} }); + ADD COLUMN overduerules_id int(11) NOT NULL"); + my $mtts = $dbh->selectall_arrayref("SELECT * FROM overduerules_transport_types", { Slice => {} }); $dbh->do("DELETE FROM overduerules_transport_types"); $dbh->do("ALTER TABLE overduerules_transport_types DROP FOREIGN KEY overduerules_fk, ADD FOREIGN KEY overduerules_transport_types_fk (overduerules_id) REFERENCES overduerules (overduerules_id) ON DELETE CASCADE ON UPDATE CASCADE, DROP COLUMN branchcode, DROP COLUMN categorycode"); - my $s = $dbh->prepare("insert into overduerules_transport_types (overduerules_id, id, letternumber, message_transport_type) " - ." values((SELECT overduerules_id FROM overduerules WHERE branchcode = ? AND categorycode = ?),?,?,?)"); + my $s = $dbh->prepare("INSERT INTO overduerules_transport_types (overduerules_id, id, letternumber, message_transport_type) " + ." VALUES((SELECT overduerules_id FROM overduerules WHERE branchcode = ? AND categorycode = ?),?,?,?)"); foreach my $mtt(@$mtts){ $s->execute($mtt->{branchcode}, $mtt->{categorycode}, $mtt->{id}, $mtt->{letternumber}, $mtt->{message_transport_type} ); } diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index e46ed75..a541bf3 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -1705,7 +1705,7 @@ CREATE TABLE `opac_news` ( -- data from the news tool DROP TABLE IF EXISTS `overduerules`; CREATE TABLE `overduerules` ( -- overdue notice status and triggers - `overduerules_id` mediumint NOT NULL AUTO_INCREMENT, -- unique identifier for the overduerules + `overduerules_id` int(11) NOT NULL AUTO_INCREMENT, -- unique identifier for the overduerules `branchcode` varchar(10) NOT NULL default '', -- foreign key from the branches table to define which branch this rule is for (if blank it's all libraries) `categorycode` varchar(10) NOT NULL default '', -- foreign key from the categories table to define which patron category this rule is for `delay1` int(4) default NULL, -- number of days after the item is overdue that the first notice is sent @@ -1717,7 +1717,8 @@ CREATE TABLE `overduerules` ( -- overdue notice status and triggers `delay3` int(4) default NULL, -- number of days after the item is overdue that the third notice is sent `letter3` varchar(20) default NULL, -- foreign key from the letter table to define which notice should be sent as the third notice `debarred3` int(1) default 0, -- is the patron restricted when the third notice is sent (1 for yes, 0 for no) - PRIMARY KEY (`overduerules_id`) + PRIMARY KEY (`overduerules_id`), + UNIQUE KEY `overduerules_branch_cat` (`branchcode`,`categorycode`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- @@ -2593,7 +2594,7 @@ DROP TABLE IF EXISTS `overduerules_transport_types`; CREATE TABLE overduerules_transport_types( `id` INT(11) NOT NULL AUTO_INCREMENT, `message_transport_type` VARCHAR(20) NOT NULL DEFAULT 'email', - `overduerules_id` mediumint NOT NULL, + `overduerules_id` INT(11) NOT NULL, PRIMARY KEY (id), CONSTRAINT overduerules_fk FOREIGN KEY (overduerules_id) REFERENCES overduerules (overduerules_id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT mtt_fk FOREIGN KEY (message_transport_type) REFERENCES message_transport_types (message_transport_type) ON DELETE CASCADE ON UPDATE CASCADE -- 2.1.0