From 1a33f01390b8bd9948ce471cf92a2fd2f6cbfbf4 Mon Sep 17 00:00:00 2001
From: Dmitriy Samborskiy <samborsky_d@yahoo.com>
Date: Wed, 5 Aug 2015 12:05:10 +0300
Subject: [PATCH] _find_by_unique_key - bugfix

There was a wrong order of the argument binding.
Both the orders of the fields of the unique key: (1)
in the SQL statement and (2) in $dbd->bind_param() were
determined by the simple hash key iteration.

This accidentally worked with some old Perl versions but
with Perl v.5.18 (Ubuntu 14.04 LTS) it is not working anymore.

As a result, during the saving of RichSeq into the BioSQL database
`term` entries were sometimes not found by its unique key.
It led to the automatical creation of duplicated entries in the
`term` table. This, in its turn, lead to a crash later, when
the search by name or ontology_id brought several rows whereas just
one is expected. The unique key of the table:

UNIQUE KEY `name` (`name`,`ontology_id`, `is_obsolete`)

did not prevent the duplications because `is_obsolete` is always
NULL (it seems that this field is itself obsolete) and, since
SQL standard assumes NULL is not equal NULL, the duplications
are "unique" do not violate the constraint.

In order to resolve this issuet the sorting was added so that
the two argument orders were always the same.
---
 lib/Bio/DB/BioSQL/BaseDriver.pm                  | 2 +-
 lib/Bio/DB/BioSQL/BasePersistenceAdaptor.pm      | 2 +-
 lib/Bio/DB/BioSQL/Oracle/SpeciesAdaptorDriver.pm | 2 +-
 lib/Bio/DB/BioSQL/Pg/SpeciesAdaptorDriver.pm     | 2 +-
 lib/Bio/DB/BioSQL/mysql/SpeciesAdaptorDriver.pm  | 2 +-
 5 files changed, 5 insertions(+), 5 deletions(-)

diff --git a/lib/Bio/DB/BioSQL/BaseDriver.pm b/lib/Bio/DB/BioSQL/BaseDriver.pm
index 4b2e9f5..e22c4e1 100644
--- a/lib/Bio/DB/BioSQL/BaseDriver.pm
+++ b/lib/Bio/DB/BioSQL/BaseDriver.pm
@@ -481,7 +481,7 @@ sub prepare_findbyuk_sth {
 
     # WHERE clause constraints
     my @cattrs = ();
-    foreach ( keys %$ukval_h ) {
+    foreach ( sort keys %$ukval_h ) {
         my $col;
         if ( exists( $slotmap->{$_} ) ) {
             $col = $slotmap->{$_};
diff --git a/lib/Bio/DB/BioSQL/BasePersistenceAdaptor.pm b/lib/Bio/DB/BioSQL/BasePersistenceAdaptor.pm
index e45d140..e5ac567 100644
--- a/lib/Bio/DB/BioSQL/BasePersistenceAdaptor.pm
+++ b/lib/Bio/DB/BioSQL/BasePersistenceAdaptor.pm
@@ -957,7 +957,7 @@ sub _find_by_unique_key{
     }
     my $dbd = $self->dbd();
     $i = 0;
-    foreach (keys %$query_h) {
+    foreach (sort keys %$query_h) {
 	$dbd->bind_param($sth, ++$i, $query_h->{$_});
     }
     # execute and check for error
diff --git a/lib/Bio/DB/BioSQL/Oracle/SpeciesAdaptorDriver.pm b/lib/Bio/DB/BioSQL/Oracle/SpeciesAdaptorDriver.pm
index 92c93eb..dfe5c17 100644
--- a/lib/Bio/DB/BioSQL/Oracle/SpeciesAdaptorDriver.pm
+++ b/lib/Bio/DB/BioSQL/Oracle/SpeciesAdaptorDriver.pm
@@ -207,7 +207,7 @@ sub prepare_findbyuk_sth{
     my @attrs = $self->_build_select_list($adp,$fkslots);
     # WHERE clause constraints
     my @cattrs = ();
-    foreach (keys %$ukval_h) {
+    foreach (sort keys %$ukval_h) {
 	my $col;
 	if(exists($slotmap->{$_})) {
 	    $col = $slotmap->{$_};
diff --git a/lib/Bio/DB/BioSQL/Pg/SpeciesAdaptorDriver.pm b/lib/Bio/DB/BioSQL/Pg/SpeciesAdaptorDriver.pm
index 38dbc15..2234d1b 100644
--- a/lib/Bio/DB/BioSQL/Pg/SpeciesAdaptorDriver.pm
+++ b/lib/Bio/DB/BioSQL/Pg/SpeciesAdaptorDriver.pm
@@ -201,7 +201,7 @@ sub prepare_findbyuk_sth{
     my @attrs = $self->_build_select_list($adp,$fkslots);
     # WHERE clause constraints
     my @cattrs = ();
-    foreach (keys %$ukval_h) {
+    foreach (sort keys %$ukval_h) {
 	my $col;
 	if(exists($slotmap->{$_})) {
 	    $col = $slotmap->{$_};
diff --git a/lib/Bio/DB/BioSQL/mysql/SpeciesAdaptorDriver.pm b/lib/Bio/DB/BioSQL/mysql/SpeciesAdaptorDriver.pm
index c0da5d0..b0559ac 100644
--- a/lib/Bio/DB/BioSQL/mysql/SpeciesAdaptorDriver.pm
+++ b/lib/Bio/DB/BioSQL/mysql/SpeciesAdaptorDriver.pm
@@ -200,7 +200,7 @@ sub prepare_findbyuk_sth{
     my @attrs = $self->_build_select_list($adp,$fkslots);
     # WHERE clause constraints
     my @cattrs = ();
-    foreach (keys %$ukval_h) {
+    foreach (sort keys %$ukval_h) {
 	my $col;
 	if(exists($slotmap->{$_})) {
 	    $col = $slotmap->{$_};
-- 
2.11.4.GIT