From ef969f8623d076d3a4a24255d744988e5940770e 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'. --- installer/data/mysql/kohastructure.sql | 21 ++++++++++- installer/data/mysql/updatedatabase.pl | 61 +++++++++++++++++++++++++++++++- 2 files changed, 80 insertions(+), 2 deletions(-) diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index fdab879..83aee10 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -1323,7 +1323,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; -- @@ -2488,6 +2491,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 31f494a..d3b00f2 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -6396,7 +6396,7 @@ if ( CheckVersion($DBversion) ) { $dbh->do("INSERT IGNORE INTO systempreferences (variable,value,explanation,options,type) VALUES('IDreamBooksReadometer','0','Display Readometer from IDreamBooks.com','','YesNo');"); $dbh->do("INSERT IGNORE INTO systempreferences (variable,value,explanation,options,type) VALUES('IDreamBooksResults','0','Display IDreamBooks.com rating in search results','','YesNo');"); print "Upgrade to $DBversion done (Add IDreamBooks enhanced content)\n"; - SetVersion($DBversion); + SetVersion ($DBversion); } $DBversion = "3.11.00.018"; @@ -7139,6 +7139,65 @@ if ( CheckVersion($DBversion) ) { SetVersion ($DBversion); } + + + + + + + +$DBversion = "3.13.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.10.4