From a3d0c321a846c416fc7b0b99655252a5c867b90a Mon Sep 17 00:00:00 2001 From: Blou Date: Tue, 21 Apr 2015 13:54:45 -0400 Subject: [PATCH] Bug 13624 - Remove columns branchcode, categorytype from table overduerules_transport_types This patch is a _requirement_ to Bug #12771, and will be necessary once we start managing more than 3 levels of overdue notice 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/atomicupdate/update_13624_overduerules_transport_type.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 In installer/data/mysql/atomicupdate/update_13624_overduerules_transport_type.pl - A new column and primary key, 'overduerules_id', is added to the table 'overduerules' - A new column referencing a foreign key, 'overduerules_id', is added to the table 'overduerules_transport_types' - The columns 'branchcode' and 'categorytype' are removed from the table 'overduerules_transport_types' - Data is migrated from the old tables to the new ones In installer/data/mysql/kohastructure.sql : - Table 'overduerules' - Added a primary key named 'overduerules_id' - Table 'overduerules_transport_types' - Added a foreign key named 'overduerules_id' - Dropped columns 'branchcode', 'categorycode' since we now have them referenced through 'overduerules_id' In tools/overduerules.pl - INSERT and DELETE queries on 'overduerules_transport_types' were changed to reflect the new schema In C4/Overdues.pm : - The SQL query of the function 'GetOverdueMessageTransportTypes' is changed to take into account the new design of 'overduerules_transport In t/db_dependent/Overdues.t : - The INSERT calls before the tests were changed to take into account the new design of 'overduerules' and 'overduerules_transport_types' Sponsored by : Halland County Library Signed-off-by: Jesse Maseto Signed-off-by: Martin Persson --- C4/Overdues.pm | 7 +++- .../update_13624_overduerules_transport_type.pl | 49 ++++++++++++++++++++++ installer/data/mysql/kohastructure.sql | 9 ++-- t/db_dependent/Overdues.t | 35 ++++++++-------- tools/overduerules.pl | 6 +-- 5 files changed, 78 insertions(+), 28 deletions(-) create mode 100755 installer/data/mysql/atomicupdate/update_13624_overduerules_transport_type.pl diff --git a/C4/Overdues.pm b/C4/Overdues.pm index 6eeb6cf..07021f1 100644 --- a/C4/Overdues.pm +++ b/C4/Overdues.pm @@ -932,8 +932,11 @@ sub GetOverdueMessageTransportTypes { return unless $categorycode and $letternumber; my $dbh = C4::Context->dbh; my $sth = $dbh->prepare(" - SELECT message_transport_type FROM overduerules_transport_types - WHERE branchcode = ? AND categorycode = ? AND letternumber = ? + SELECT message_transport_type + FROM overduerules odr LEFT JOIN overduerules_transport_types ott USING (overduerules_id) + WHERE branchcode = ? + AND categorycode = ? + AND letternumber = ? "); $sth->execute( $branchcode, $categorycode, $letternumber ); my @mtts; diff --git a/installer/data/mysql/atomicupdate/update_13624_overduerules_transport_type.pl b/installer/data/mysql/atomicupdate/update_13624_overduerules_transport_type.pl new file mode 100755 index 0000000..a1ac778 --- /dev/null +++ b/installer/data/mysql/atomicupdate/update_13624_overduerules_transport_type.pl @@ -0,0 +1,49 @@ +#! /usr/bin/perl + +use strict; +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"; + +#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, + `branchcode` varchar(10) NOT NULL DEFAULT '', + `categorycode` varchar(10) NOT NULL DEFAULT '', + `delay1` int(4) DEFAULT NULL, + `letter1` varchar(20) DEFAULT NULL, + `debarred1` varchar(1) DEFAULT '0', + `delay2` int(4) DEFAULT NULL, + `debarred2` varchar(1) DEFAULT '0', + `letter2` varchar(20) DEFAULT NULL, + `delay3` int(4) DEFAULT NULL, + `letter3` varchar(20) DEFAULT NULL, + `debarred3` int(1) DEFAULT '0', + PRIMARY KEY (`overduerules_id`) + ) 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 => {} }); + $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 = ?),?,?,?)"); + foreach my $mtt(@$mtts){ + $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"; +# SetVersion ($DBversion); +#} + +print "\nDone\n"; diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 13b6a6a..c252dda 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -1705,6 +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 `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 @@ -1716,7 +1717,7 @@ 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 (`branchcode`,`categorycode`) + PRIMARY KEY (`overduerules_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- @@ -2591,12 +2592,10 @@ CREATE TABLE `message_transport_types` ( DROP TABLE IF EXISTS `overduerules_transport_types`; CREATE TABLE overduerules_transport_types( `id` INT(11) NOT NULL AUTO_INCREMENT, - `branchcode` varchar(10) NOT NULL DEFAULT '', - `categorycode` VARCHAR(10) NOT NULL DEFAULT '', - `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 (branchcode, categorycode) REFERENCES overduerules (branchcode, categorycode) ON DELETE CASCADE ON UPDATE CASCADE, + 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 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; diff --git a/t/db_dependent/Overdues.t b/t/db_dependent/Overdues.t index 4fc8402..7c53e97 100644 --- a/t/db_dependent/Overdues.t +++ b/t/db_dependent/Overdues.t @@ -24,26 +24,25 @@ $dbh->do(q| |); $dbh->do(q| - INSERT INTO overduerules ( branchcode, categorycode ) VALUES - ('CPL', 'PT'), - ('CPL', 'YA'), - ('', 'PT'), - ('', 'YA') + INSERT INTO overduerules ( overduerules_id, branchcode, categorycode ) VALUES + (1, 'CPL', 'PT'), + (2, 'CPL', 'YA'), + (3, '', 'PT'), + (4, '', 'YA') |); -$dbh->do(q| - INSERT INTO overduerules_transport_types( branchcode, categorycode, letternumber, message_transport_type ) VALUES - ('CPL', 'PT', 1, 'email'), - ('CPL', 'PT', 2, 'sms'), - ('CPL', 'PT', 3, 'email'), - ('CPL', 'YA', 3, 'print'), - ('', 'PT', 1, 'email'), - ('', 'PT', 2, 'email'), - ('', 'PT', 2, 'sms'), - ('', 'PT', 3, 'sms'), - ('', 'PT', 3, 'email'), - ('', 'PT', 3, 'print'), - ('', 'YA', 2, 'sms') +$dbh->do(q|INSERT INTO overduerules_transport_types (overduerules_id, letternumber, message_transport_type) VALUES + (1, 1, 'email'), + (1, 2, 'sms'), + (1, 3, 'email'), + (2, 3, 'print'), + (3, 1, 'email'), + (3, 2, 'email'), + (3, 2, 'sms'), + (3, 3, 'sms'), + (3, 3, 'email'), + (3, 3, 'print'), + (4, 2, 'sms') |); my $mtts; diff --git a/tools/overduerules.pl b/tools/overduerules.pl index 463c73e..f875dae 100755 --- a/tools/overduerules.pl +++ b/tools/overduerules.pl @@ -87,14 +87,14 @@ 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 = ?), ?, ? ) "); my $sth_delete_mtt = $dbh->prepare(" DELETE FROM overduerules_transport_types - WHERE branchcode = ? AND categorycode = ? + WHERE overduerules_id = (SELECT overduerules_id FROM overduerules WHERE branchcode = ? AND categorycode = ?) "); foreach my $key (@names){ -- 2.1.4