From 329678f39bb84b4ac2437e6b4d7baa1681b9d812 Mon Sep 17 00:00:00 2001 From: Galen Charlton Date: Wed, 20 May 2009 11:35:49 -0500 Subject: [PATCH] bug 3222: database changes for messaging preferences (DB rev 033) This patch modifies the database for the messaging preferences enhancement as follows: * adds a categorycode column to borrower_message_preferences This allows a set of messaging preferences to be linked to a patron category * allow the borrowernumber column to be null This allows a messaging preference set to be linked *only* to a patron category. If MySQL had check constraints, I would add one so that exactly one of borrowernumber and categorycode in a row in borrower_message_preferences could be NULL. * add a FK constraint from borrower_message_preferences.categorycode to categories; if you delete a patron category, any default preferences associated with it will be automatically deleted. The messaging preference enhancement was sponsored by the Northeast Kansas Library System (NEKLS). Signed-off-by: Daniel Sweeney Signed-off-by: Galen Charlton --- installer/data/mysql/kohastructure.sql | 7 +++++-- installer/data/mysql/updatedatabase.pl | 15 +++++++++++++++ kohaversion.pl | 2 +- 3 files changed, 21 insertions(+), 3 deletions(-) diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index cb5068f691..72ff5e3666 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -2306,15 +2306,18 @@ CREATE TABLE `message_transports` ( DROP TABLE IF EXISTS `borrower_message_preferences`; CREATE TABLE `borrower_message_preferences` ( `borrower_message_preference_id` int(11) NOT NULL auto_increment, - `borrowernumber` int(11) NOT NULL default '0', + `borrowernumber` int(11) default NULL, + `categorycode` varchar(10) default NULL, `message_attribute_id` int(11) default '0', `days_in_advance` int(11) default '0', `wants_digest` tinyint(1) NOT NULL default '0', PRIMARY KEY (`borrower_message_preference_id`), KEY `borrowernumber` (`borrowernumber`), + KEY `categorycode` (`categorycode`), KEY `message_attribute_id` (`message_attribute_id`), CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE + CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `borrower_message_preferences_ibfk_3` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index 6d7892157b..8225b8651e 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -2415,6 +2415,21 @@ ENDOFRENEWAL SetVersion ($DBversion); } +$DBversion = "3.01.00.033"; +if (C4::Context->preference("Version") < TransformToNum($DBversion)) { + $dbh->do(q/ + ALTER TABLE borrower_message_preferences + MODIFY borrowernumber int(11) default NULL, + ADD categorycode varchar(10) default NULL AFTER borrowernumber, + ADD KEY `categorycode` (`categorycode`), + ADD CONSTRAINT `borrower_message_preferences_ibfk_3` + FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) + ON DELETE CASCADE ON UPDATE CASCADE + /); + print "Upgrade to $DBversion done (DB changes to allow patron category defaults for messaging preferences)\n"; + SetVersion ($DBversion); +} + =item DropAllForeignKeys($table) Drop all foreign keys of the table $table diff --git a/kohaversion.pl b/kohaversion.pl index dbcc126684..a0248eaf8e 100644 --- a/kohaversion.pl +++ b/kohaversion.pl @@ -10,7 +10,7 @@ use strict; sub kohaversion { - our $VERSION = '3.01.00.032'; + our $VERSION = '3.01.00.033'; # version needs to be set this way # so that it can be picked up by Makefile.PL # during install -- 2.20.1