[Koha-patches] [PATCH] bug 3222: database changes for messaging preferences (DB rev 033)
Daniel Sweeney
daniel.sweeney at liblime.com
Wed May 20 18:12:41 CEST 2009
From: Galen Charlton <galen.charlton at liblime.com>
This patch modifies the database for the messaging
preferences enhancement <http://wiki.koha.org/doku.php?id=en:development:rfcs3.2:rfc32_defaultmessagingsettings>
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).
---
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 cb5068f..72ff5e3 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 6d78921..8225b86 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 dbcc126..a0248ea 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
--
1.5.6.5
More information about the Koha-patches
mailing list