Bug 509497: Implement sql_group_concat for all databases

Patch by Max Kanat-Alexander <mkanat@bugzilla.org> r=LpSolit, a=mkanat
Patch by Xiaoou Wu <xiaoou.wu@oracle.com> r=mkanat, a=mkanat

4 files changed, 130 insertions, 47 deletions

diff --git a/Bugzilla/DB.pm b/Bugzilla/DB.pm
index 9ede5bd80..f39eb6511 100644
--- a/Bugzilla/DB.pm
+++ b/Bugzilla/DB.pm
@@ -273,7 +273,8 @@ EOT
 # List of abstract methods we are checking the derived class implements
 our @_abstract_methods = qw(REQUIRED_VERSION PROGRAM_NAME DBD_VERSION
                             new sql_regexp sql_not_regexp sql_limit sql_to_days
-                            sql_date_format sql_interval bz_explain);
+                            sql_date_format sql_interval bz_explain
+                            sql_group_concat);
 
 # This overridden import method will check implementation of inherited classes
 # for missing implementation of abstract methods
diff --git a/Bugzilla/DB/Oracle.pm b/Bugzilla/DB/Oracle.pm
index a2c78e094..4f19269a6 100644
--- a/Bugzilla/DB/Oracle.pm
+++ b/Bugzilla/DB/Oracle.pm
@@ -115,6 +115,12 @@ sub bz_explain {
      return join("\n", @$explain); 
 } 
 
+sub sql_group_concat {
+    my ($self, $text, $separator) = @_;
+    $separator ||= "','";
+    return "group_concat(T_CLOB_DELIM($text, $separator))";
+}
+
 sub sql_regexp {
     my ($self, $expr, $pattern, $nocheck, $real_pattern) = @_;
     $real_pattern ||= $pattern;
@@ -271,6 +277,10 @@ sub _fix_hashref {
 
 sub adjust_statement {
     my ($sql) = @_;
+    
+    if ($sql =~ /^CREATE OR REPLACE.*/i){
+        return $sql;
+    } 
 
     # We can't just assume any occurrence of "''" in $sql is an empty
     # string, since "''" can occur inside a string literal as a way of
@@ -529,6 +539,88 @@ sub bz_setup_database {
               . " RETURN DATE IS BEGIN RETURN SYSDATE; END;");
     $self->do("CREATE OR REPLACE FUNCTION CHAR_LENGTH(COLUMN_NAME VARCHAR2)" 
               . " RETURN NUMBER IS BEGIN RETURN LENGTH(COLUMN_NAME); END;");
+    
+    # Create types for group_concat
+    my $t_clob_delim = $self->selectcol_arrayref("
+        SELECT TYPE_NAME FROM USER_TYPES WHERE TYPE_NAME=?",
+        undef, 'T_CLOB_DELIM'); 
+
+    if ( !@$t_clob_delim ) {
+        $self->do("CREATE OR REPLACE TYPE T_CLOB_DELIM AS OBJECT "
+              . "( p_CONTENT CLOB, p_DELIMITER VARCHAR2(256));");
+    }
+
+    $self->do("CREATE OR REPLACE TYPE T_GROUP_CONCAT AS OBJECT 
+               (  CLOB_CONTENT CLOB,
+                  DELIMITER    VARCHAR2(256),
+                  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(
+                      SCTX IN OUT NOCOPY T_GROUP_CONCAT)
+                  RETURN NUMBER,
+                  MEMBER FUNCTION ODCIAGGREGATEITERATE(
+                      SELF IN OUT NOCOPY T_GROUP_CONCAT,
+                      VALUE IN T_CLOB_DELIM) 
+                  RETURN NUMBER,
+                  MEMBER FUNCTION ODCIAGGREGATETERMINATE(
+                      SELF IN T_GROUP_CONCAT,
+                      RETURNVALUE OUT NOCOPY CLOB,
+                      FLAGS       IN NUMBER)
+                  RETURN NUMBER,
+                  MEMBER FUNCTION ODCIAGGREGATEMERGE(
+                      SELF IN OUT NOCOPY T_GROUP_CONCAT,
+                      CTX2 IN T_GROUP_CONCAT) 
+                  RETURN NUMBER);");
+
+    $self->do("CREATE OR REPLACE TYPE BODY T_GROUP_CONCAT IS
+                  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(
+                  SCTX IN OUT NOCOPY T_GROUP_CONCAT)
+                  RETURN NUMBER IS
+                  BEGIN
+                      SCTX := T_GROUP_CONCAT(EMPTY_CLOB(), NULL);
+                      DBMS_LOB.CREATETEMPORARY(SCTX.CLOB_CONTENT, TRUE);
+                      RETURN ODCICONST.SUCCESS;
+                  END;
+                  MEMBER FUNCTION ODCIAGGREGATEITERATE(
+                      SELF IN OUT NOCOPY T_GROUP_CONCAT,
+                      VALUE IN T_CLOB_DELIM) 
+                  RETURN NUMBER IS
+                  BEGIN
+                      SELF.DELIMITER := VALUE.P_DELIMITER;
+                      DBMS_LOB.WRITEAPPEND(SELF.CLOB_CONTENT, 
+                                           LENGTH(SELF.DELIMITER),
+                                           SELF.DELIMITER);
+                      DBMS_LOB.APPEND(SELF.CLOB_CONTENT, VALUE.P_CONTENT);
+  
+                      RETURN ODCICONST.SUCCESS;
+                  END;
+                  MEMBER FUNCTION ODCIAGGREGATETERMINATE(
+                      SELF IN T_GROUP_CONCAT,
+                      RETURNVALUE OUT NOCOPY CLOB,
+                      FLAGS IN NUMBER) 
+                  RETURN NUMBER IS
+                  BEGIN
+                      RETURNVALUE := RTRIM(LTRIM(SELF.CLOB_CONTENT, 
+                                     SELF.DELIMITER), 
+                                     SELF.DELIMITER);
+                      RETURN ODCICONST.SUCCESS;
+                  END;
+                  MEMBER FUNCTION ODCIAGGREGATEMERGE(
+                      SELF IN OUT NOCOPY T_GROUP_CONCAT,
+                      CTX2 IN T_GROUP_CONCAT) 
+                  RETURN NUMBER IS
+                  BEGIN
+                      DBMS_LOB.WRITEAPPEND(SELF.CLOB_CONTENT, 
+                                           LENGTH(SELF.DELIMITER), 
+                                           SELF.DELIMITER);
+                      DBMS_LOB.APPEND(SELF.CLOB_CONTENT, CTX2.CLOB_CONTENT);
+                      RETURN ODCICONST.SUCCESS;
+                  END;
+               END;");
+
+    # Create user-defined aggregate function group_concat
+    $self->do("CREATE OR REPLACE FUNCTION GROUP_CONCAT(P_INPUT T_CLOB_DELIM) 
+               RETURN CLOB 
+               DETERMINISTIC PARALLEL_ENABLE AGGREGATE USING T_GROUP_CONCAT;");
+
     # Create a WORLD_LEXER named BZ_LEX for multilingual fulltext search
     my $lexer = $self->selectcol_arrayref(
        "SELECT pre_name FROM CTXSYS.CTX_PREFERENCES WHERE pre_name = ? AND
diff --git a/Bugzilla/DB/Pg.pm b/Bugzilla/DB/Pg.pm
index 18f9abf88..585c0884b 100644
--- a/Bugzilla/DB/Pg.pm
+++ b/Bugzilla/DB/Pg.pm
@@ -94,6 +94,12 @@ sub bz_last_key {
     return $last_insert_id;
 }
 
+sub sql_group_concat {
+    my ($self, $text, $separator) = @_;
+    $separator ||= "','";
+    return "array_to_string(array_accum($text), $separator)";
+}
+
 sub sql_regexp {
     my ($self, $expr, $pattern, $nocheck, $real_pattern) = @_;
     $real_pattern ||= $pattern;
@@ -189,6 +195,20 @@ sub bz_setup_database {
     my $self = shift;
     $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 = '{}' 
+                   )");
+    }
+
     # 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.
diff --git a/Bugzilla/Install/DB.pm b/Bugzilla/Install/DB.pm
index 48fc06630..697e2fdcc 100644
--- a/Bugzilla/Install/DB.pm
+++ b/Bugzilla/Install/DB.pm
@@ -3133,52 +3133,22 @@ sub _populate_bugs_fulltext {
         my $bug_ids = $dbh->selectcol_arrayref('SELECT bug_id FROM bugs');
         return if !@$bug_ids;
 
-        # Populating bugs_fulltext can be very slow for large installs,
-        # so we special-case any DB that supports GROUP_CONCAT, which is
-        # a much faster way to do things.
-        if (UNIVERSAL::can($dbh, 'sql_group_concat')) {
-            print "Populating bugs_fulltext...";
-            print " (this can take a long time.)\n";
-            $dbh->do(
-                q{INSERT INTO bugs_fulltext (bug_id, short_desc, comments, 
-                                             comments_noprivate)
-                       SELECT bugs.bug_id, bugs.short_desc, }
-                     . $dbh->sql_group_concat('longdescs.thetext', '\'\n\'')
-              . ', ' . $dbh->sql_group_concat('nopriv.thetext',    '\'\n\'') .
-                      q{ FROM bugs 
-                              LEFT JOIN longdescs
-                                     ON bugs.bug_id = longdescs.bug_id
-                              LEFT JOIN longdescs AS nopriv
-                                     ON longdescs.comment_id = nopriv.comment_id
-                                        AND nopriv.isprivate = 0 }
-                     . $dbh->sql_group_by('bugs.bug_id', 'bugs.short_desc'));
-        }
-        # The slow way, without group_concat.
-        else {
-            print "Populating bugs_fulltext.short_desc...\n";
-            $dbh->do('INSERT INTO bugs_fulltext (bug_id, short_desc)
-                           SELECT bug_id, short_desc FROM bugs');
-
-            my $count = 1;
-            my $sth_all = $dbh->prepare('SELECT thetext FROM longdescs 
-                                          WHERE bug_id = ?');
-            my $sth_nopriv = $dbh->prepare(
-                'SELECT thetext FROM longdescs
-                  WHERE bug_id = ? AND isprivate = 0');
-            my $sth_update = $dbh->prepare(
-                'UPDATE bugs_fulltext SET comments = ?, comments_noprivate = ?
-                  WHERE bug_id = ?');
-
-            print "Populating bugs_fulltext comment fields...\n";
-            foreach my $id (@$bug_ids) { 
-                my $all = $dbh->selectcol_arrayref($sth_all, undef, $id);
-                my $nopriv = $dbh->selectcol_arrayref($sth_nopriv, undef, $id);
-                $sth_update->execute(join("\n", @$all), join("\n", @$nopriv), $id);
-                indicate_progress({ total   => scalar @$bug_ids, every => 100,
-                                    current => $count++ });
-            }
-            print "\n";
-        }
+        print "Populating bugs_fulltext...";
+        print " (this can take a long time.)\n";
+        my $newline = $dbh->quote("\n");
+        $dbh->do(
+            q{INSERT INTO bugs_fulltext (bug_id, short_desc, comments, 
+                                         comments_noprivate)
+                   SELECT bugs.bug_id, bugs.short_desc, }
+                 . $dbh->sql_group_concat('longdescs.thetext', $newline)
+          . ', ' . $dbh->sql_group_concat('nopriv.thetext',    $newline) .
+                  q{ FROM bugs 
+                          LEFT JOIN longdescs
+                                 ON bugs.bug_id = longdescs.bug_id
+                          LEFT JOIN longdescs AS nopriv
+                                 ON longdescs.comment_id = nopriv.comment_id
+                                    AND nopriv.isprivate = 0 }
+                 . $dbh->sql_group_by('bugs.bug_id', 'bugs.short_desc'));
     }
 }