From b0ba8e4abd64476679b4912449f3fd8a220aefbb Mon Sep 17 00:00:00 2001
From: Mark Tompsett <mtompset@hotmail.com>
Date: Mon, 20 Apr 2015 20:11:23 -0400
Subject: [PATCH] Bug 14030 - Added tests for duplicates.

It seems that the id's are not really used, but rather
the subtags, types, and language related fields.

These tests check for duplicates.

TEST PLAN
---------
1) Apply both patches
2) prove -v t/db_dependent/Languages.t
   -- may fail if you have duplicates.
3) ./installer/data/mysql/updatedatabase.pl
   -- this will trigger the atomicupdate, which because of
      this tweak, will correct duplicates.
4) prove -v t/db_dependent/Languages.t
   -- should succeed.
5) Run the updatedatabase.pl script multiple times.
6) prove -v t/db_dependent/Languages.t
   -- should still succeed.
7) koha qa test tools.
---
 .../bug_14030-Add_Georgian_fix_Kannada_code.sql    | 22 ++++++++++++++
 t/db_dependent/Languages.t                         | 35 +++++++++++++++++++++-
 2 files changed, 56 insertions(+), 1 deletion(-)

diff --git a/installer/data/mysql/atomicupdate/bug_14030-Add_Georgian_fix_Kannada_code.sql b/installer/data/mysql/atomicupdate/bug_14030-Add_Georgian_fix_Kannada_code.sql
index d07503e..995490f 100644
--- a/installer/data/mysql/atomicupdate/bug_14030-Add_Georgian_fix_Kannada_code.sql
+++ b/installer/data/mysql/atomicupdate/bug_14030-Add_Georgian_fix_Kannada_code.sql
@@ -7,8 +7,23 @@ UPDATE language_descriptions SET subtag = 'kn', lang = 'kn' WHERE subtag = 'ka'
 UPDATE language_descriptions SET subtag = 'kn' WHERE subtag = 'ka' AND description = 'Kannada';
 
 INSERT IGNORE INTO language_subtag_registry( subtag, type, description, added) VALUES ( 'ka', 'language', 'Georgian','2015-04-20');
+DELETE FROM language_subtag_registry
+       WHERE NOT id IN
+         (SELECT id FROM
+           (SELECT MIN(id) as id,subtag,type,description,added
+            FROM language_subtag_registry
+            GROUP BY subtag,type,description,added)
+           AS subtable);
+
 
 INSERT IGNORE INTO language_rfc4646_to_iso639(rfc4646_subtag,iso639_2_code) VALUES ( 'ka', 'geo');
+DELETE FROM language_rfc4646_to_iso639
+       WHERE NOT id IN
+         (SELECT id FROM
+           (SELECT MIN(id) as id,rfc4646_subtag,iso639_2_code
+            FROM language_rfc4646_to_iso639
+            GROUP BY rfc4646_subtag,iso639_2_code)
+           AS subtable);
 
 INSERT IGNORE INTO language_descriptions(subtag, type, lang, description) VALUES ( 'ka', 'language', 'ka', 'ქართული');
 
@@ -19,3 +34,10 @@ INSERT IGNORE INTO language_descriptions(subtag, type, lang, description) VALUES
 INSERT IGNORE INTO language_descriptions(subtag, type, lang, description) VALUES ( 'ka', 'language', 'de', 'Georgische');
 
 INSERT IGNORE INTO language_descriptions(subtag, type, lang, description) VALUES ( 'ka', 'language', 'es', 'Georgiano');
+
+DELETE FROM language_descriptions
+       WHERE NOT id IN
+         (SELECT id FROM
+           (SELECT MIN(id) as id,subtag,type,lang,description
+            FROM language_descriptions GROUP BY subtag,type,lang,description)
+           AS subtable);
diff --git a/t/db_dependent/Languages.t b/t/db_dependent/Languages.t
index ca4413e..d27e823 100755
--- a/t/db_dependent/Languages.t
+++ b/t/db_dependent/Languages.t
@@ -6,7 +6,7 @@
 use strict;
 use warnings;
 
-use Test::More tests => 13;
+use Test::More tests => 16;
 use List::Util qw(first);
 use Data::Dumper;
 use Test::Warn;
@@ -57,4 +57,37 @@ my @currentcheck2 = map { $_->{current} } @$translatedlanguages2;
 $onlyzeros = first { $_ != 0 } @currentcheck2;
 ok($onlyzeros, "There is a $onlyzeros\n");
 
+# Language Descriptions
+my $sth = $dbh->prepare("SELECT DISTINCT subtag,type,lang,description from language_descriptions;");
+$sth->execute();
+my $DistinctLangDesc = $sth->fetchall_arrayref({});
+
+$sth = $dbh->prepare("SELECT subtag,type,lang,description from language_descriptions;");
+$sth->execute();
+my $LangDesc = $sth->fetchall_arrayref({});
+
+is(scalar(@$LangDesc),scalar(@$DistinctLangDesc),"No unexpected language_description duplicates.");
+
+# Language_subtag_registry
+$sth = $dbh->prepare("SELECT DISTINCT subtag,type,description,added FROM language_subtag_registry;");
+$sth->execute();
+my $DistinctLangReg = $sth->fetchall_arrayref({});
+
+$sth = $dbh->prepare("SELECT subtag,type,description,added FROM language_subtag_registry;");
+$sth->execute();
+my $LangReg = $sth->fetchall_arrayref({});
+
+is(scalar(@$LangReg),scalar(@$DistinctLangReg),"No unexpected language_subtag_registry duplicates.");
+
+# Language RFC4646 to ISO639
+$sth = $dbh->prepare("SELECT DISTINCT rfc4646_subtag,iso639_2_code FROM language_rfc4646_to_iso639;");
+$sth->execute();
+my $DistinctLangRfc4646 = $sth->fetchall_arrayref({});
+
+$sth = $dbh->prepare("SELECT rfc4646_subtag,iso639_2_code FROM language_rfc4646_to_iso639;");
+$sth->execute();
+my $LangRfc4646 = $sth->fetchall_arrayref({});
+
+is(scalar(@$LangRfc4646),scalar(@$DistinctLangRfc4646),"No unexpected language_rfc4646_to_iso639 duplicates.");
+
 $dbh->rollback;
-- 
1.9.1