From 768f1162a5553e1c16bb1c60726bed47981d9de7 Mon Sep 17 00:00:00 2001
From: Olli-Antti Kivilahti <olli-antti.kivilahti@jns.fi>
Date: Tue, 24 Feb 2015 19:00:23 +0200
Subject: [PATCH] Bug 7793 - redefine the field message_id as PRIMARY KEY of
 message_queue

Making message_queue work nicely with DBIx.

DROP the existing KEY and replace with an PRIMARY KEY.
If somebody has managed to get duplicate message_ids in his/her
message_queue-table, this patch takes care of them as well, but givin
all duplicate message_id's a new id.

TEST INSTRUCTIONS: in the patch.
---
 Koha/Schema/Result/MessageQueue.pm     |   19 ++++++++---------
 installer/data/mysql/kohastructure.sql |    2 +-
 installer/data/mysql/updatedatabase.pl |   35 ++++++++++++++++++++++++++++++++
 3 files changed, 44 insertions(+), 12 deletions(-)

diff --git a/Koha/Schema/Result/MessageQueue.pm b/Koha/Schema/Result/MessageQueue.pm
index 6612db5..342dadd 100644
--- a/Koha/Schema/Result/MessageQueue.pm
+++ b/Koha/Schema/Result/MessageQueue.pm
@@ -1,21 +1,17 @@
-use utf8;
 package Koha::Schema::Result::MessageQueue;
 
 # Created by DBIx::Class::Schema::Loader
 # DO NOT MODIFY THE FIRST PART OF THIS FILE
 
-=head1 NAME
-
-Koha::Schema::Result::MessageQueue
-
-=cut
-
 use strict;
 use warnings;
 
 use base 'DBIx::Class::Core';
 
-=head1 TABLE: C<message_queue>
+
+=head1 NAME
+
+Koha::Schema::Result::MessageQueue
 
 =cut
 
@@ -130,6 +126,7 @@ __PACKAGE__->add_columns(
   "content_type",
   { data_type => "text", is_nullable => 1 },
 );
+__PACKAGE__->set_primary_key("message_id");
 
 =head1 RELATIONS
 
@@ -165,12 +162,12 @@ __PACKAGE__->belongs_to(
   "message_transport_type",
   "Koha::Schema::Result::MessageTransportType",
   { message_transport_type => "message_transport_type" },
-  { is_deferrable => 1, on_delete => "RESTRICT", on_update => "CASCADE" },
+  { is_deferrable => 1, on_delete => "CASCADE", on_update => "CASCADE" },
 );
 
 
-# Created by DBIx::Class::Schema::Loader v0.07039 @ 2014-07-11 09:26:55
-# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:YWxM2O2W/h34qqIOIpzOtw
+# Created by DBIx::Class::Schema::Loader v0.07010 @ 2015-02-24 18:59:44
+# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:v+Th3wvct4LBCEp+fRlMBw
 
 
 # You can replace this text with custom content, and it will be preserved on regeneration
diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index a789dec..aaccb00 100644
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -2596,7 +2596,7 @@ CREATE TABLE `message_queue` (
   `to_address` mediumtext,
   `from_address` mediumtext,
   `content_type` text,
-  KEY `message_id` (`message_id`),
+  PRIMARY KEY `message_id` (`message_id`),
   KEY `borrowernumber` (`borrowernumber`),
   KEY `message_transport_type` (`message_transport_type`),
   CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl
index c83990b..5040d85 100755
--- a/installer/data/mysql/updatedatabase.pl
+++ b/installer/data/mysql/updatedatabase.pl
@@ -6744,6 +6744,41 @@ if ( CheckVersion($DBversion) ) {
     SetVersion ($DBversion);
 }
 
+$DBversion = "3.19.00.XXX";
+if ( CheckVersion($DBversion) ) {
+
+    #First make sure that there are no duplicate message_id's which might be quite impossible thanks to the AUTO_INCREMENT.
+    #If duplicates exists, find the next free message_id and UPDATE the duplicate message_queues with the new biggest id's.
+    my $sthGetDuplicatePKs = $dbh->prepare("SELECT count(message_id) as duplicate_pks, message_id ".
+                            "FROM message_queue GROUP BY message_id HAVING duplicate_pks > 1;");
+#USE THIS TO TEST           "FROM message_queue GROUP BY message_id HAVING duplicate_pks > 0;");
+    my $sthGetMessage_queues = $dbh->prepare("SELECT * FROM message_queue WHERE message_id = ?");
+    my $sthGetBiggestMessageId = $dbh->prepare("SELECT max(message_id) as message_id FROM message_queue;");
+    my $sthUpdateMessageQueue = $dbh->prepare("UPDATE message_queue SET message_id = ? WHERE message_id = ? AND borrowernumber = ? AND time_queued = ? AND content = ?;");
+
+    #Get duplicate message_ids
+    $sthGetDuplicatePKs->execute;
+    my $message_ids = $sthGetDuplicatePKs->fetchall_arrayref({});
+    foreach my $mi (@$message_ids) {
+        if ($mi->{duplicate_pks} > 1) { #Making sure there are multiple usages of this id
+#USE TO TEST  if ($mi->{duplicate_pks} > 0) { #Making sure we trigger this repair routine for messages with no duplicates, just to test.
+            $sthGetMessage_queues->execute($mi->{message_id}); #Get all the message_queues sharing the same id.
+            my $duplicates = $sthGetMessage_queues->fetchall_arrayref({});
+            foreach my $duplicate (@$duplicates) {
+                $sthGetBiggestMessageId->execute();
+                my $max_message_id = $sthGetBiggestMessageId->fetchrow();
+                $max_message_id++;
+                $sthUpdateMessageQueue->execute( $max_message_id, $duplicate->{message_id}, $duplicate->{borrowernumber}, $duplicate->{time_queued}, $duplicate->{content} );
+            }
+        }
+    }
+
+    #Finally add the PRIMARY KEY
+    $dbh->do( "ALTER TABLE message_queue DROP KEY message_id, ADD CONSTRAINT PRIMARY KEY (message_id);" );
+    print "Upgrade to $DBversion done (Bug 7793 - redefine the field message_id as PRIMARY KEY of message_queue)\n";
+    SetVersion ($DBversion);
+}
+
 $DBversion = "3.11.00.113";
 if ( CheckVersion($DBversion) ) {
     $dbh->do(q{
-- 
1.7.9.5