Bug 1184431: Bug searching is slow on PostgreSQL

r=LpSolit

1 files changed, 69 insertions, 0 deletions

diff --git a/Bugzilla/DB/Pg.pm b/Bugzilla/DB/Pg.pm
index 72e4b5b7d..ac9ec6309 100644
--- a/Bugzilla/DB/Pg.pm
+++ b/Bugzilla/DB/Pg.pm
@@ -117,6 +117,36 @@ sub sql_position {
     return "POSITION(${fragment}::text IN ${text}::text)";
 }
 
+sub sql_like {
+    my ($self, $fragment, $column, $not) = @_;
+    $not //= '';
+
+    return "${column}::text $not LIKE " . $self->sql_like_escape($fragment) . " ESCAPE '|'";
+}
+
+sub sql_ilike {
+    my ($self, $fragment, $column, $not) = @_;
+    $not //= '';
+
+    return "${column}::text $not ILIKE " . $self->sql_like_escape($fragment) . " ESCAPE '|'";
+}
+
+sub sql_not_ilike {
+    return shift->sql_ilike(@_, 'NOT');
+}
+
+# Escapes any % or _ characters which are special in a LIKE match.
+# Also performs a $dbh->quote to escape any quote characters.
+sub sql_like_escape {
+    my ($self, $fragment) = @_;
+
+    $fragment =~ s/\|/\|\|/g;  # escape the escape character if it appears
+    $fragment =~ s/%/\|%/g;    # percent and underscore are the special match
+    $fragment =~ s/_/\|_/g;    # characters in SQL.
+
+    return $self->quote("%$fragment%");
+}
+
 sub sql_regexp {
     my ($self, $expr, $pattern, $nocheck, $real_pattern) = @_;
     $real_pattern ||= $pattern;
@@ -393,6 +423,39 @@ sub bz_table_list_real {
 
 1;
 
+=head2 Functions
+
+=over
+
+=item C<sql_like_escape>
+
+=over
+
+=item B<Description>
+
+The postgres versions of the sql_like methods use the ANSI SQL LIKE
+statements to perform substring searching.  To prevent issues with
+users attempting to search for strings containing special characters
+associated with LIKE, we escape them out so they don't affect the search
+terms.
+
+=item B<Params>
+
+=over
+
+=item C<$fragment> - The string fragment in need of escaping and quoting
+
+=back
+
+=item B<Returns>
+
+The fragment with any pre existing %,_,| characters escaped out, wrapped in
+percent characters and quoted.
+
+=back
+
+=back
+
 =head1 B<Methods in need of POD>
 
 =over
@@ -407,6 +470,12 @@ sub bz_table_list_real {
 
 =item sql_position
 
+=item sql_like
+
+=item sql_ilike
+
+=item sql_not_ilike
+
 =item sql_limit
 
 =item sql_not_regexp