From 333b8fcbaad05e5e3ab01a897529560dea37dd53 Mon Sep 17 00:00:00 2001
From: Max Kanat-Alexander <mkanat@bugzilla.org>
Date: Tue, 6 Jul 2010 17:13:46 -0700
Subject: Bug 552919: Sort group_concat results so that they sort correctly for
 buglists r=mkanat, a=mkanat (module owner)

---
 Bugzilla/DB/Mysql.pm  | 13 ++++++++-----
 Bugzilla/DB/Oracle.pm |  2 +-
 Bugzilla/DB/Pg.pm     | 25 ++++++++++++++++++++++---
 3 files changed, 31 insertions(+), 9 deletions(-)

(limited to 'Bugzilla/DB')

diff --git a/Bugzilla/DB/Mysql.pm b/Bugzilla/DB/Mysql.pm
index 4b90a2a34..7f3eb2ef8 100644
--- a/Bugzilla/DB/Mysql.pm
+++ b/Bugzilla/DB/Mysql.pm
@@ -126,12 +126,15 @@ sub bz_last_key {
 }
 
 sub sql_group_concat {
-    my ($self, $column, $separator) = @_;
-    my $sep_sql;
-    if ($separator) {
-        $sep_sql = " SEPARATOR $separator";
+    my ($self, $column, $separator, $sort) = @_;
+    $separator = $self->quote(', ') if !defined $separator;
+    $sort = 1 if !defined $sort;
+    if ($sort) {
+        my $sort_order = $column;
+        $sort_order =~ s/^DISTINCT\s+//i;
+        $column = "$column ORDER BY $sort_order";
     }
-    return "GROUP_CONCAT($column$sep_sql)";
+    return "GROUP_CONCAT($column SEPARATOR $separator)";
 }
 
 sub sql_regexp {
diff --git a/Bugzilla/DB/Oracle.pm b/Bugzilla/DB/Oracle.pm
index a671a0e68..0819bd19a 100644
--- a/Bugzilla/DB/Oracle.pm
+++ b/Bugzilla/DB/Oracle.pm
@@ -119,7 +119,7 @@ sub bz_explain {
 
 sub sql_group_concat {
     my ($self, $text, $separator) = @_;
-    $separator ||= "','";
+    $separator = $self->quote(', ') if !defined $separator;
     return "group_concat(T_CLOB_DELIM($text, $separator))";
 }
 
diff --git a/Bugzilla/DB/Pg.pm b/Bugzilla/DB/Pg.pm
index c85c1d710..018900129 100644
--- a/Bugzilla/DB/Pg.pm
+++ b/Bugzilla/DB/Pg.pm
@@ -98,9 +98,14 @@ sub bz_last_key {
 }
 
 sub sql_group_concat {
-    my ($self, $text, $separator) = @_;
-    $separator ||= "','";
-    return "array_to_string(array_accum($text), $separator)";
+    my ($self, $text, $separator, $sort) = @_;
+    $sort = 1 if !defined $sort;
+    $separator = $self->quote(', ') if !defined $separator;
+    my $sql = "array_accum($text)";
+    if ($sort) {
+        $sql = "array_sort($sql)";
+    }
+    return "array_to_string($sql, $separator)";
 }
 
 sub sql_istring {
@@ -224,6 +229,20 @@ sub bz_setup_database {
                    )");
     }
 
+   $self->do(<<'END');
+CREATE OR REPLACE FUNCTION array_sort(ANYARRAY)
+RETURNS ANYARRAY LANGUAGE SQL
+IMMUTABLE STRICT
+AS $$
+SELECT ARRAY(
+    SELECT $1[s.i] AS each_item
+    FROM
+        generate_series(array_lower($1,1), array_upper($1,1)) AS s(i)
+    ORDER BY each_item
+);
+$$;
+END
+
     # PostgreSQL doesn't like having *any* index on the thetext
     # field, because it can't have index data longer than 2770
     # characters on that field.
-- 
cgit v1.2.3-24-g4f1b