Bugzilla – Attachment 36145 Details for
Bug 7793
redefine the field message_id as PRIMARY KEY of message_queue
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
[patch]
Bug 7793 - redefine the field message_id as PRIMARY KEY of message_queue
Bug-7793---redefine-the-field-messageid-as-PRIMARY.patch (text/plain), 5.55 KB, created by
Olli-Antti Kivilahti
on 2015-02-24 17:02:45 UTC
(
hide
)
Description:
Bug 7793 - redefine the field message_id as PRIMARY KEY of message_queue
Filename:
MIME Type:
Creator:
Olli-Antti Kivilahti
Created:
2015-02-24 17:02:45 UTC
Size:
5.55 KB
patch
obsolete
>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
You cannot view the attachment while viewing its details because your browser does not support IFRAMEs.
View the attachment on a separate page
.
View Attachment As Diff
View Attachment As Raw
Actions:
View
|
Diff
|
Splinter Review
Attachments on
bug 7793
:
8489
|
11743
|
36145
|
36945
|
36987
|
37027
|
37028
|
39143
|
39525
|
39526
|
40172
|
40173