[Koha-patches] [PATCH 01/10] bug 3222: database changes for messaging preferences (DB rev 033)

[Daniel Sweeney]

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).

Signed-off-by: Daniel Sweeney <daniel.sweeney at liblime.com>
---
 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