From ee42536a141bc7ed0ca752f677da31ee7bd6ac29 Mon Sep 17 00:00:00 2001 From: Blou Date: Fri, 20 Mar 2015 15:17:48 -0400 Subject: [PATCH] Bugs 13624 - Test plan and fixes db scripts for overduerules migrations TEST PLAN 1) Create or improve on overduerules data .1) Intranet -> tools -> overdue notices .2) Make sure to have data in first, second and third tabs .3) Make sure to have checked mixes of Email/Phone/Print/SMS (depending on availability) .4) Make sure to have different letters and delay on first/second/third tab for at least one category .5) Remember it all. 2) Apply patch 3) run installer/data/mysql/updatedatabase.pl 4) run t/db_dependant/Overdues.t 5) Validate Overdue Notice page .1) validate data entered previously is still there .2) Add some more, save changes, validate This patch also - simplifies (somewhat) updatedatabase.pl - modifies kohastructure.sql to match updatedatabase.pl (because I couldn't 'insert after' with a KEY constraint) - fixes database call --- installer/data/mysql/kohastructure.sql | 3 +-- installer/data/mysql/updatedatabase.pl | 7 +++---- tools/overduerules.pl | 2 +- 3 files changed, 5 insertions(+), 7 deletions(-) diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 863e8ab..401019f 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -2599,9 +2599,8 @@ CREATE TABLE `message_transport_types` ( DROP TABLE IF EXISTS `overduerules_transport_types`; CREATE TABLE overduerules_transport_types( `id` INT(11) NOT NULL AUTO_INCREMENT, - `overduerules_id` mediumint NOT NULL, - `letternumber` INT(1) NOT NULL DEFAULT 1, `message_transport_type` VARCHAR(20) NOT NULL DEFAULT 'email', + `overduerules_id` mediumint 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 diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index a2a820c..2e28bac 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -9890,7 +9890,6 @@ if ( CheckVersion($DBversion) ) { $dbh->do("DROP TABLE old_overduerules"); $dbh->do("ALTER TABLE overduerules_transport_types ADD COLUMN overduerules_id mediumint NOT NULL"); - my $update = $dbh->prepare("update overduerules_transport_types set overduerules_id = (select overduerules_id from overduerules where branchcode=? and categorycode=? limit 1) where id=?"); 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 @@ -9898,10 +9897,10 @@ if ( CheckVersion($DBversion) ) { 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 (id, overduerules_id, letternumber, message_transport_type) values(?,?,?,?)"); + 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->{id}, 1, $mtt->{letternumber}, $mtt->{message_transport_type} ); - $update->execute($mtt->{branchcode}, $mtt->{categorycode}, $mtt->{id}); + $s->execute($mtt->{branchcode}, $mtt->{categorycode}, $mtt->{id}, $mtt->{letternumber}, $mtt->{message_transport_type} ); } $dbh->do("SET FOREIGN_KEY_CHECKS=1"); print "Upgrade to $DBversion done (Bug 13624 - Remove columns branchcode, categorytype from table overduerules_transport_types)\n"; diff --git a/tools/overduerules.pl b/tools/overduerules.pl index 807a2da..6d1babc 100755 --- a/tools/overduerules.pl +++ b/tools/overduerules.pl @@ -87,7 +87,7 @@ if ($op eq 'save') { my $sth_delete=$dbh->prepare("DELETE FROM overduerules WHERE branchcode=? AND categorycode=?"); my $sth_insert_mtt = $dbh->prepare(" INSERT INTO overduerules_transport_types( - branchcode, categorycode, letternumber, message_transport_type + overduerules_id, letternumber, message_transport_type ) VALUES ( (SELECT overduerules_id FROM overduerules WHERE branchcode = ? AND categorycode = ?), ?, ? ) -- 2.1.0