diff options
| -rw-r--r-- | Bugzilla/DB.pm | 3 | ||||
| -rw-r--r-- | Bugzilla/DB/Oracle.pm | 92 | ||||
| -rw-r--r-- | Bugzilla/DB/Pg.pm | 20 | ||||
| -rw-r--r-- | Bugzilla/Install/DB.pm | 62 |
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')); } } |