From 768f1162a5553e1c16bb1c60726bed47981d9de7 Mon Sep 17 00:00:00 2001 From: Olli-Antti Kivilahti 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 + +=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