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

[patch] Bug 13624 - Remove columns branchcode, categorytype from table overduerules_transport_types

Bug-13624---Remove-columns-branchcode-categorytype.patch (text/plain), 11.16 KB, created by Mark Tompsett on 2015-06-18 18:06:23 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Mark Tompsett

Created: 2015-06-18 18:06:23 UTC

Size: 11.16 KB

patch

obsolete

>From d7189248e48a09abcbea483592d44557616f53e9 Mon Sep 17 00:00:00 2001
>From: Blou <philippe.blouin@inlibro.com>
>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 <jesse@bywatersolutions.com>
>---
> 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 9d6e2ce..1022e30 100644
>--- a/installer/data/mysql/kohastructure.sql
>+++ b/installer/data/mysql/kohastructure.sql
>@@ -1706,6 +1706,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
>@@ -1717,7 +1718,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;
> 
> --
>@@ -2592,12 +2593,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

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