Bug 305976: Allow Bugzilla::DB sql_regexp/sql_not_regexp methods to accept string and pattern as arguments

Patch By Lance Larsh <lance.larsh@oracle.com> r=joel, a=justdave

4 files changed, 39 insertions, 13 deletions

diff --git a/Bugzilla/DB.pm b/Bugzilla/DB.pm
index 6ec377cd7..fd846d2a5 100644
--- a/Bugzilla/DB.pm
+++ b/Bugzilla/DB.pm
@@ -24,6 +24,7 @@
 #                 Christopher Aillon <christopher@aillon.com>
 #                 Tomas Kopal <Tomas.Kopal@altap.cz>
 #                 Max Kanat-Alexander <mkanat@bugzilla.org>
+#                 Lance Larsh <lance.larsh@oracle.com>
 
 package Bugzilla::DB;
 
@@ -1081,7 +1082,8 @@ formatted SQL command have prefix C<sql_>. All other methods have prefix C<bz_>.
               searches (case insensitive) in format suitable for a given
               database.
               Abstract method, should be overriden by database specific code.
- Params:      none
+ Params:      $expr = SQL expression for the text to be searched (scalar)
+              $pattern = the regular expression to search for (scalar)
  Returns:     formatted SQL for regular expression search (e.g. REGEXP)
               (scalar)
 
@@ -1091,7 +1093,8 @@ formatted SQL command have prefix C<sql_>. All other methods have prefix C<bz_>.
               regex searches (case insensitive) in format suitable for a given
               database.
               Abstract method, should be overriden by database specific code.
- Params:      none
+ Params:      $expr = SQL expression for the text to be searched (scalar)
+              $pattern = the regular expression to search for (scalar)
  Returns:     formatted SQL for negative regular expression search
               (e.g. NOT REGEXP) (scalar)
 
diff --git a/Bugzilla/DB/Mysql.pm b/Bugzilla/DB/Mysql.pm
index 0951cdc5f..c3824ab9a 100644
--- a/Bugzilla/DB/Mysql.pm
+++ b/Bugzilla/DB/Mysql.pm
@@ -23,6 +23,7 @@
 #                 Dave Lawrence <dkl@redhat.com>
 #                 Tomas Kopal <Tomas.Kopal@altap.cz>
 #                 Max Kanat-Alexander <mkanat@bugzilla.org>
+#                 Lance Larsh <lance.larsh@oracle.com>
 
 =head1 NAME
 
@@ -83,11 +84,15 @@ sub bz_last_key {
 }
 
 sub sql_regexp {
-    return "REGEXP";
+    my ($self, $expr, $pattern) = @_;
+
+    return "$expr REGEXP $pattern";
 }
 
 sub sql_not_regexp {
-    return "NOT REGEXP";
+    my ($self, $expr, $pattern) = @_;
+
+    return "$expr NOT REGEXP $pattern";
 }
 
 sub sql_limit {
diff --git a/Bugzilla/DB/Pg.pm b/Bugzilla/DB/Pg.pm
index c8ff4221b..f0c18b728 100644
--- a/Bugzilla/DB/Pg.pm
+++ b/Bugzilla/DB/Pg.pm
@@ -23,6 +23,7 @@
 #                 Dave Lawrence <dkl@redhat.com>
 #                 Tomas Kopal <Tomas.Kopal@altap.cz>
 #                 Max Kanat-Alexander <mkanat@bugzilla.org>
+#                 Lance Larsh <lance.larsh@oracle.com>
 
 =head1 NAME
 
@@ -89,11 +90,15 @@ sub bz_last_key {
 }
 
 sub sql_regexp {
-    return "~*";
+    my ($self, $expr, $pattern) = @_;
+
+    return "$expr ~* $pattern";
 }
 
 sub sql_not_regexp {
-    return "!~*" 
+    my ($self, $expr, $pattern) = @_;
+
+    return "$expr !~* $pattern" 
 }
 
 sub sql_limit {
diff --git a/Bugzilla/Search.pm b/Bugzilla/Search.pm
index ae11dfa67..42f6da749 100644
--- a/Bugzilla/Search.pm
+++ b/Bugzilla/Search.pm
@@ -26,6 +26,7 @@
 #                 Michael Schindler <michael@compressconsult.com>
 #                 Max Kanat-Alexander <mkanat@bugzilla.org>
 #                 Joel Peshkin <bugreport@peshkin.net>
+#                 Lance Larsh <lance.larsh@oracle.com>
 
 use strict;
 
@@ -724,9 +725,15 @@ sub init {
              } elsif ($t eq "notequal") {
                  $oper = "<>";
              } elsif ($t eq "regexp") {
-                 $oper = $dbh->sql_regexp();
+                 # This is just a dummy to help catch bugs- $oper won't be used
+                 # since "regexp" is treated as a special case below.  But
+                 # leaving $oper uninitialized seems risky...
+                 $oper = "sql_regexp";
              } elsif ($t eq "notregexp") {
-                 $oper = $dbh->sql_not_regexp();
+                 # This is just a dummy to help catch bugs- $oper won't be used
+                 # since "notregexp" is treated as a special case below.  But
+                 # leaving $oper uninitialized seems risky...
+                 $oper = "sql_not_regexp";
              } else {
                  $oper = "noop";
              }
@@ -744,7 +751,13 @@ sub init {
                                               COUNT(DISTINCT $table.bug_when) /
                                               COUNT(bugs.bug_id)) +
                                              bugs.remaining_time)))";
-                 push(@having, "$expression $oper " . &::SqlQuote($v));
+                 if ($t eq "regexp") {
+                     push(@having, $dbh->sql_regexp($expression, &::SqlQuote($v)));
+                 } elsif ($t eq "notregexp") {
+                     push(@having, $dbh->sql_not_regexp($expression, &::SqlQuote($v)));
+                 } else {
+                     push(@having, "$expression $oper " . &::SqlQuote($v));
+                 }
                  push(@groupby, "bugs.remaining_time");
              }
              $term = "0=0";
@@ -1024,10 +1037,10 @@ sub init {
              $term = $dbh->sql_position(lc($q), "LOWER($ff)") . " = 0";
          },
          ",regexp" => sub {
-             $term = "$ff " . $dbh->sql_regexp() . " $q";
+             $term = $dbh->sql_regexp($ff, $q);
          },
          ",notregexp" => sub {
-             $term = "$ff " . $dbh->sql_not_regexp() . " $q";
+             $term = $dbh->sql_not_regexp($ff, $q);
          },
          ",lessthan" => sub {
              $term = "$ff < $q";
@@ -1517,7 +1530,7 @@ sub ListIDsForEmail {
         }
     } elsif ($type eq 'regexp') {
         &::SendSQL("SELECT userid FROM profiles WHERE " .
-            "login_name " . $dbh->sql_regexp() . ::SqlQuote($email) .
+            $dbh->sql_regexp("login_name", ::SqlQuote($email)) .
             " " . $dbh->sql_limit(51));
         while (&::MoreSQLData()) {
             my ($id) = &::FetchSQLData();
@@ -1558,7 +1571,7 @@ sub GetByWordList {
             $word =~ s/^'//;
             $word =~ s/'$//;
             $word = '(^|[^a-z0-9])' . $word . '($|[^a-z0-9])';
-            push(@list, "$field " . $dbh->sql_regexp() . " '$word'");
+            push(@list, $dbh->sql_regexp($field, "'$word'"));
         }
     }