From 7ae384a0b1ca177d76e841bf67a5bb27bd4a6488 Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Fri, 6 Sep 2013 09:36:42 +0200 Subject: [PATCH] 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 Signed-off-by: Marcel de Rooy Signed-off-by: Galen Charlton --- installer/data/mysql/kohastructure.sql | 21 +++++++++- installer/data/mysql/updatedatabase.pl | 57 ++++++++++++++++++++++++++ 2 files changed, 77 insertions(+), 1 deletion(-) diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index c7d35f250b..46db97fcbe 100644 --- 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; -- @@ -2548,6 +2551,22 @@ CREATE TABLE `message_transport_types` ( PRIMARY KEY (`message_transport_type`) ) 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 8aed2d6e85..6a4cdf972e 100755 --- 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) -- 2.39.5