Attachment 35561 Details for Bug 13624 – Remove columns branchcode, categorytype from table overduerules_transport_types

[patch] Remove columns branchcode, categorytype from table overduerules_transport_types

Remove-columns-branchcode-categorytype-from-table-.patch (text/plain), 6.19 KB, created by Charles Farmer on 2015-01-27 17:07:15 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Charles Farmer

Created: 2015-01-27 17:07:15 UTC

Size: 6.19 KB

patch

obsolete

>From 320a288b2c788aff12ae9c704bb7cde76a909f21 Mon Sep 17 00:00:00 2001
>From: charles <charles@inlibro.com>
>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

Actions: View | Diff | Splinter Review

Attachments on bug 13624: 35561 | 35587 | 36122 | 36494 | 36497 | 37074 | 38288 | 38289 | 38290 | 39920 | 40329 | 40330 | 41684 | 41685 | 43524 | 43571 | 43583 | 43584 | 43585