Bug 9016: DB changes: new table overduerules_transport_types

This patch adds:
- a new table overduerules_transport_types.
- a new column letter.message_transport_type.
- a new primary key for letter.
- fill the new table with existing values.

Test plan:
After applying this patch and executing the updatedatabase entry, verify
that the overduerules_transport_types table contains a row for each
entry in the overduerules table.
The message_transport_type column should contain 'email'.

Signed-off-by: Olli-Antti Kivilahti <olli-antti.kivilahti@jns.fi>
Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
Signed-off-by: Galen Charlton <gmc@esilibrary.com>

diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index c7d35f2..46db97f 100644 (file)
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -1354,7 +1354,10 @@ CREATE TABLE `letter` ( -- table for all notice templates in Koha
   `is_html` tinyint(1) default 0, -- does this notice or slip use HTML (1 for yes, 0 for no)
   `title` varchar(200) NOT NULL default '', -- subject line of the notice
   `content` text, -- body text for the notice or slip
-  PRIMARY KEY  (`module`,`code`, `branchcode`)
+  `message_transport_type` varchar(20) NOT NULL DEFAULT 'email', -- transport type for this notice
+  PRIMARY KEY  (`module`,`code`, `branchcode`, `message_transport_type`),
+  CONSTRAINT `message_transport_type_fk` FOREIGN KEY (`message_transport_type`)
+  REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 --
@@ -2549,6 +2552,22 @@ CREATE TABLE `message_transport_types` (
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 --
+-- Table structure for table `overduerules_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',
+    PRIMARY KEY (id),
+    CONSTRAINT overduerules_fk FOREIGN KEY (branchcode, categorycode) REFERENCES overduerules (branchcode, categorycode) 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;
+
+--
 -- Table structure for table `message_attributes`
 --
 

diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl
index 8aed2d6..6a4cdf9 100755 (executable)
--- a/installer/data/mysql/updatedatabase.pl
+++ b/installer/data/mysql/updatedatabase.pl
@@ -8243,6 +8243,63 @@ if ( CheckVersion($DBversion) ) {
     SetVersion ($DBversion);
 }
 
+
+
+
+
+
+$DBversion = "3.15.00.XXX";
+if ( CheckVersion($DBversion) ) {
+
+    $dbh->do( q{
+        ALTER TABLE letter ADD COLUMN message_transport_type VARCHAR(20) NOT NULL DEFAULT 'email' AFTER content
+    } );
+
+    $dbh->do( q{
+        ALTER TABLE letter ADD CONSTRAINT message_transport_type_fk FOREIGN KEY (message_transport_type) REFERENCES message_transport_types(message_transport_type);
+    } );
+
+    $dbh->do( q{
+        ALTER TABLE letter DROP PRIMARY KEY, ADD PRIMARY KEY (`module`,`code`,`branchcode`, message_transport_type);
+    } );
+
+    $dbh->do( q{
+        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',
+            PRIMARY KEY (id),
+            CONSTRAINT overduerules_fk FOREIGN KEY (branchcode, categorycode) REFERENCES overduerules (branchcode, categorycode) 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;
+    } );
+
+    my $sth = $dbh->prepare( q{
+        SELECT * FROM overduerules;
+    } );
+
+    $sth->execute;
+    my $sth_insert_mtt = $dbh->prepare( q{
+        INSERT INTO overduerules_transport_types (branchcode, categorycode, letternumber, message_transport_type) VALUES ( ?, ?, ?, ? )
+    } );
+    while ( my $row = $sth->fetchrow_hashref ) {
+        my $branchcode = $row->{branchcode};
+        my $categorycode = $row->{categorycode};
+        for my $letternumber ( 1 .. 3 ) {
+            next unless $row->{"letter$letternumber"};
+            $sth_insert_mtt->execute(
+                $branchcode, $categorycode, $letternumber, 'email'
+            );
+        }
+    }
+
+    print "Upgrade done (Bug 9016: Adds the association table overduerules_transport_types)\n";
+    SetVersion($DBversion);
+}
+
+
 =head1 FUNCTIONS
 
 =head2 TableExists($table)