From c7c8efa55c2e98058b9adc358ec5da1d31ee9f15 Mon Sep 17 00:00:00 2001
From: Kyle M Hall <kyle@bywatersolutions.com>
Date: Fri, 20 Mar 2015 08:15:41 -0400
Subject: [PATCH] [SIGNED-OFF] Bug 7793: Simplify db update

Since message_id is not linked to anything else in the database, we
can ensure the message_id's are unique by simply dropping the column
and recreating it.

Signed-off-by: Bernardo Gonzalez Kriegel <bgkriegel@gmail.com>

Moved updatedatabase to atomicupdate
First patch signed.
No errors
---
 installer/data/mysql/atomicupdate/bug-7793.sql |   14 +++++++++
 installer/data/mysql/updatedatabase.pl         |   37 ------------------------
 2 files changed, 14 insertions(+), 37 deletions(-)
 create mode 100644 installer/data/mysql/atomicupdate/bug-7793.sql

diff --git a/installer/data/mysql/atomicupdate/bug-7793.sql b/installer/data/mysql/atomicupdate/bug-7793.sql
new file mode 100644
index 0000000..2a1891c
--- /dev/null
+++ b/installer/data/mysql/atomicupdate/bug-7793.sql
@@ -0,0 +1,14 @@
+ALTER TABLE message_queue DROP message_id;
+
+ALTER TABLE message_queue ADD message_id INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;
+
+-- $DBversion = "3.19.00.XXX";
+-- if ( CheckVersion($DBversion) ) {
+-- 
+--     $dbh->do("ALTER TABLE message_queue DROP message_id" );
+--     $dbh->do("ALTER TABLE message_queue ADD message_id INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST");
+-- 
+--     print "Upgrade to $DBversion done (Bug 7793 - redefine the field message_id as PRIMARY KEY of message_queue)\n";
+--     SetVersion ($DBversion);
+-- }
+
diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl
index 14fb8a0..2274251 100755
--- a/installer/data/mysql/updatedatabase.pl
+++ b/installer/data/mysql/updatedatabase.pl
@@ -6762,43 +6762,6 @@ 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({});
-            my $firstRun = 1; #On the first replace run for this set of duplicates, preserve the original id.
-            foreach my $duplicate (@$duplicates) {
-                next if $firstRun-- == 1; #Preserve the original id for the first conflicting message_queue-row
-                $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