From 0fadbc553b0005b0f309db05370030de9df1238c 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 Content-Type: text/plain; charset=utf-8 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 --- installer/data/mysql/kohastructure.sql | 21 +++++++++++- installer/data/mysql/updatedatabase.pl | 58 ++++++++++++++++++++++++++++++++ 2 files changed, 78 insertions(+), 1 deletions(-) diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 3cf9c65..26bcd7a 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -1352,7 +1352,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; -- @@ -2544,6 +2547,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 062ecc1..3e74cb1 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -8083,6 +8083,64 @@ if ( CheckVersion($DBversion) ) { SetVersion($DBversion); } + + + + + + +$DBversion = "3.15.00.XXX"; +if ( C4::Context->preference("Version") < TransformToNum($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) -- 1.7.7.6