Bug 13624 - Remove columns branchcode, categorytype from table overduerules_transport...

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>

Signed-off-by: Martin Persson <xarragon@gmail.com>

Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org>

Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>

diff --git a/C4/Overdues.pm b/C4/Overdues.pm
index 72c0704..047c000 100644 (file)
--- a/C4/Overdues.pm
+++ b/C4/Overdues.pm
@@ -939,8 +939,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 (executable)
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 8607abe..3a21269 100644 (file)
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -1703,6 +1703,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
@@ -1714,7 +1715,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;
 
 --
@@ -2567,12 +2568,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 (file)
--- 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 c745861..5657b19 100755 (executable)
--- a/tools/overduerules.pl
+++ b/tools/overduerules.pl
@@ -98,14 +98,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){