diff options
| -rw-r--r-- | Bugzilla/DB/Mysql.pm | 7 | ||||
| -rw-r--r-- | Bugzilla/DB/Pg.pm | 101 | ||||
| -rw-r--r-- | Bugzilla/Search.pm | 3 |
3 files changed, 78 insertions, 33 deletions
diff --git a/Bugzilla/DB/Mysql.pm b/Bugzilla/DB/Mysql.pm index fcb3ef889..28df1f0e8 100644 --- a/Bugzilla/DB/Mysql.pm +++ b/Bugzilla/DB/Mysql.pm @@ -125,10 +125,13 @@ sub bz_last_key { } sub sql_group_concat { - my ($self, $column, $separator, $sort) = @_; + my ($self, $column, $separator, $sort, $order_by) = @_; $separator = $self->quote(', ') if !defined $separator; $sort = 1 if !defined $sort; - if ($sort) { + if ($order_by) { + $column .= " ORDER BY $order_by"; + } + elsif ($sort) { my $sort_order = $column; $sort_order =~ s/^DISTINCT\s+//i; $column = "$column ORDER BY $sort_order"; diff --git a/Bugzilla/DB/Pg.pm b/Bugzilla/DB/Pg.pm index 890b19089..165eea559 100644 --- a/Bugzilla/DB/Pg.pm +++ b/Bugzilla/DB/Pg.pm @@ -25,6 +25,7 @@ use 5.10.1; use strict; use Bugzilla::Error; +use Bugzilla::Version; use DBD::Pg; # This module extends the DB interface via inheritance @@ -79,14 +80,33 @@ sub bz_last_key { } sub sql_group_concat { - my ($self, $text, $separator, $sort) = @_; + my ($self, $text, $separator, $sort, $order_by) = @_; $sort = 1 if !defined $sort; $separator = $self->quote(', ') if !defined $separator; - my $sql = "array_accum($text)"; - if ($sort) { - $sql = "array_sort($sql)"; + + if ($order_by && $text =~ /^DISTINCT\s*(.+)$/i) { + # Since Postgres (quite rightly) doesn't support "SELECT DISTINCT x + # ORDER BY y", we need to sort the list, and then get the unique + # values + return "ARRAY_TO_STRING(ANYARRAY_UNIQ(ARRAY_AGG($1 ORDER BY $order_by)), $separator)"; + } + + # Determine the ORDER BY clause (if any) + if ($order_by) { + $order_by = " ORDER BY $order_by"; + } + elsif ($sort) { + # We don't include the DISTINCT keyword in an order by + $text =~ /^(?:DISTINCT\s*)?(.+)$/i; + $order_by = " ORDER BY $1"; } - return "array_to_string($sql, $separator)"; + + if (vers_cmp($self->bz_server_version, 9) < 0) { + # PostgreSQL 8.x doesn't support STRING_AGG + return "ARRAY_TO_STRING(ARRAY_AGG($text$order_by), $separator)"; + } + + return "STRING_AGG($text, $separator$order_by)"; } sub sql_istring { @@ -211,32 +231,53 @@ sub bz_setup_database { $self->SUPER::bz_setup_database(@_); # Custom Functions - my $function = 'array_accum'; - my $array_accum = $self->selectrow_array( - 'SELECT 1 FROM pg_proc WHERE proname = ?', undef, $function); - if (!$array_accum) { - print "Creating function $function...\n"; - $self->do("CREATE AGGREGATE array_accum ( - SFUNC = array_append, - BASETYPE = anyelement, - STYPE = anyarray, - INITCOND = '{}' - )"); - } - $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 + # -Copyright © 2013 Joshua D. Burns (JDBurnZ) and Message In Action LLC + # JDBurnZ: https://github.com/JDBurnZ + # Message In Action: https://www.messageinaction.com + # + #Permission is hereby granted, free of charge, to any person obtaining a copy of + #this software and associated documentation files (the "Software"), to deal in + #the Software without restriction, including without limitation the rights to + #use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of + #the Software, and to permit persons to whom the Software is furnished to do so, + #subject to the following conditions: + # + #The above copyright notice and this permission notice shall be included in all + #copies or substantial portions of the Software. + # + #THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR + #IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS + #FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR + #COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER + #IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN + #CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE. + $self->do(q| + DROP FUNCTION IF EXISTS anyarray_uniq(anyarray); + CREATE OR REPLACE FUNCTION anyarray_uniq(with_array anyarray) + RETURNS anyarray AS $BODY$ + DECLARE + -- The variable used to track iteration over "with_array". + loop_offset integer; + + -- The array to be returned by this function. + return_array with_array%TYPE := '{}'; + BEGIN + IF with_array IS NULL THEN + return NULL; + END IF; + + -- Iterate over each element in "concat_array". + FOR loop_offset IN ARRAY_LOWER(with_array, 1)..ARRAY_UPPER(with_array, 1) LOOP + IF NOT with_array[loop_offset] = ANY(return_array) THEN + return_array = ARRAY_APPEND(return_array, with_array[loop_offset]); + END IF; + END LOOP; + + RETURN return_array; + END; + $BODY$ LANGUAGE plpgsql; + |); # PostgreSQL doesn't like having *any* index on the thetext # field, because it can't have index data longer than 2770 diff --git a/Bugzilla/Search.pm b/Bugzilla/Search.pm index 7368040cc..036e0a605 100644 --- a/Bugzilla/Search.pm +++ b/Bugzilla/Search.pm @@ -599,7 +599,8 @@ sub COLUMNS { . " END)", 'flagtypes.name' => $dbh->sql_group_concat('DISTINCT ' - . $dbh->sql_string_concat('map_flagtypes.name', 'map_flags.status')), + . $dbh->sql_string_concat('map_flagtypes.name', 'map_flags.status'), + undef, undef, 'map_flagtypes.sortkey, map_flagtypes.name'), 'keywords' => $dbh->sql_group_concat('DISTINCT map_keyworddefs.name'), |