From 320a288b2c788aff12ae9c704bb7cde76a909f21 Mon Sep 17 00:00:00 2001 From: charles Date: Tue, 27 Jan 2015 10:14:37 -0500 Subject: [PATCH] Remove columns branchcode, categorytype from table overduerules_transport_types Sponsored by Halland County Library This patch is a _requirement_ to Bug #12771, and will be necessary once we start managing more than 3 levels of overdue notice In the database : - 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' In C4/Overdues.pm : - The SQL query of the function 'GetOverdueMessageTransportTypes' is changed to take into account the new design of 'overduerules_transport_types' 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' TEST PLAN --------- 1) Apply the patch 2) Run the script installer/data/mysql/atomicupdate/bug_13624_overduerules_transport_types.pl - It will create new tables and migrate the data 3) Run Overdues.t. Tests should be successful --- C4/Overdues.pm | 7 +++- .../bug_13624_overduerules_transport_types.pl | 43 ++++++++++++++++++++ t/db_dependent/Overdues.t | 35 ++++++++-------- 3 files changed, 65 insertions(+), 20 deletions(-) create mode 100755 installer/data/mysql/atomicupdate/bug_13624_overduerules_transport_types.pl diff --git a/C4/Overdues.pm b/C4/Overdues.pm index 1797923..ca99b27 100644 --- a/C4/Overdues.pm +++ b/C4/Overdues.pm @@ -937,8 +937,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/bug_13624_overduerules_transport_types.pl b/installer/data/mysql/atomicupdate/bug_13624_overduerules_transport_types.pl new file mode 100755 index 0000000..56b5bca --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_13624_overduerules_transport_types.pl @@ -0,0 +1,43 @@ +#! /usr/bin/perl + +use strict; +use warnings; +use Data::Dumper; +use C4::Context; + +my $dbh = C4::Context->dbh(); +$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 $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 + DROP FOREIGN KEY overduerules_fk, + ADD FOREIGN KEY overduerules_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(?,?,?,?)"); +foreach my $mtt(@$mtts){ + $s->execute($mtt->{id}, 1, $mtt->{letternumber}, $mtt->{message_transport_type} ); + $update->execute($mtt->{branchcode}, $mtt->{categorycode}, $mtt->{id}); +} +$dbh->do("SET FOREIGN_KEY_CHECKS=1"); diff --git a/t/db_dependent/Overdues.t b/t/db_dependent/Overdues.t index 4fc8402..7bd4bf3 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; -- 1.7.9.5