Don't hardwire Database in Views filters, add regexp to DBTNG

+++ b/core/modules/views/lib/Drupal/views/Plugin/views/query/Sql.phpundefined
@@ -1739,4 +1739,12 @@ function aggregation_method_distinct($group_type, $field) {
 
+  function getRegexpOp() {
+    switch (Database::getConnection()->databaseType()) {
+      case 'mysql':
+        return 'RLIKE';
+      case 'pgsql':
+        return '~*';
+    }

I'm wondering whether this should be part of dbtng itself.

I chosed to use a static method() as you maybe shouldn't have a select object for example in the UI,
though this makes it hard for select extenders.

Make it really working :)

+++ b/core/lib/Drupal/Core/Database/Driver/mysql/Select.php
@@ -9,4 +9,13 @@
+  /**
+   * Implements \Drupal\Core\Database\Query\SelectInterface::getRegexOperator().
+   */
+  public static function getRegexOperator() {
+    return "RLIKE";

False. Making this method static has no benefit. Don't make it static. Nothing should be static unless it really really has to be, which this does not.

+++ b/core/lib/Drupal/Core/Database/Query/SelectExtender.php
@@ -327,4 +327,12 @@ public function __call($method, $args) {
+  /**
+   * Implements \Drupal\Core\Database\Query\SelectInterface::getRegexOperator().
+   */
+  public static function getRegexOperator() {
+    // @todo Figure out how to implement that as not-static version.

I have no idea what this @todo means, but as above, don't make this static. Then you can leave this out entirely in the abstract base class.

+++ b/core/modules/views/lib/Drupal/views/Plugin/views/filter/String.php
@@ -130,8 +130,9 @@ function operators() {
-    // Add regexp support for MySQL.
-    if (Database::getConnection()->databaseType() == 'mysql') {
+    // Add regexp support depending whether it's supported by the database.
+    $select_class = Database::getConnection()->getDriverClass('Select');
+    if ($select_class::getRegexOperator()) {
       $operators += array(

Huh? If we can't support regexes across all of our supported databases, then we can't add them. That means this if-check is meaningless.

Huh? If we can't support regexes across all of our supported databases, then we can't add them. That means this if-check is meaningless.

Well, yeah before it has been checked whether we use mysql, so the patch just had the same structure.

Rewrote the patch to not use a static. As you don't have the query object available when views builds the query,
I needed to fake it.

Added also a test for the filter by regex support.

Please. Make this a proper mapConditionOperator()... why would we special case this particular operator?

Oh i like that!

Oh, that's nice.

Just adapted a bunch of minor issues. This looks now basically ready to go.

Yay for removing hacks in favor of better generic database support :)

+++ b/core/lib/Drupal/Core/Database/Driver/pgsql/Connection.phpundefined
@@ -216,6 +216,7 @@ public function mapConditionOperator($operator) {
       'NOT LIKE' => array('operator' => 'NOT ILIKE'),
+      'REGEXP' => array('operator' => '~*'),

I assume we document the supported operators somewhere, so we should update that documentation?

+++ b/core/lib/Drupal/Core/Database/Driver/sqlite/Connection.phpundefined
@@ -224,6 +225,15 @@ public function sqlFunctionRand($seed = NULL) {
+  public function sqlFunctionRegexp($string, $pattern) {
+    return preg_match('#' . $pattern . '#', preg_quote($string, '#'));

So sqlite will use php-based perl-compatible regex syntax and mysql/postgreSQL will use POSIX. Are there any differences between the MySQL and PostgreSQL implementations/syntax? A quick google search pointed me to http://www.regular-expressions.info/refflavors.html, which says POSIX for MySQL and an special version for PostgreSQL that extends POSIX with perl-regex inspired features?

As we can't do anything about it, maybe just document which backend uses which implementation and that it is recommended to limit yourself to POSIX to have a common subset or something like that?

+++ b/core/modules/views/lib/Drupal/views/Plugin/views/filter/String.phpundefined
@@ -333,8 +328,14 @@ function op_longer($field) {
+  protected function op_regex($field) {
+    $this->query->add_where($this->options['group'], $field, $this->value, 'REGEXP');

Could there be a security issue when exposing this to the UI? Just asking, probably not :)

+  public function sqlFunctionRegexp($string, $pattern) {
+    return preg_match('#' . $pattern . '#', preg_quote($string, '#'));
+  }

This looks wrong. I assume you meant to quote the pattern, not the string.

In addition, this needs to be made case insensitive (both MySQL and PostgreSQL are).

I assume we document the supported operators somewhere, so we should update that documentation?

I tried to search for "BETWEEN" and "LIKE" but couldn't find a place in core, so I guess http://drupal.org/node/773090 is the place to update that?

Do have you have a good idea where to document that? We could use the page linked above.

Could there be a security issue when exposing this to the UI? Just asking, probably not :)

"Administer views" is a restricted permission, so you should trust these people. What is a proper way to take care of that? It seems to be that we could disable this operator to be available in exposed forms, but well, this seems to be not needed.

In addition, this needs to be made case insensitive (both MySQL and PostgreSQL are).

It seems to be impossible http://stackoverflow.com/questions/14616235/regex-sqlite-case-insensitiv...
What should we do about that, but yeah maybe you have another idea.

Added a test for the quoting.

So, this is still wrong:

+  public function sqlFunctionRegexp($string, $pattern) {
+    return preg_match('#' . preg_quote($pattern, '#') . '#', $string);
+  }

We don't want to use preg_quote() here, because it replaces all the special characters (ie. all the useful characters like *, ., etc.).

I think what we want is preg_match('#' . str_replace('#', '\#', $pattern) . '#i', $string).

#17: drupal-1935300-17.patch queued for re-testing.

.

I am perfectly fine with this patch.

Just a bot failure.

Looks like good cleanup. I'm a bit shocked we have this feature at all, but since we do no need to hard-code it.

Committed/pushed to 8.x, thanks!

+++ b/core/modules/views/lib/Drupal/views/Plugin/views/filter/Numeric.php
@@ -85,6 +85,12 @@ function operators() {
+        'method' => 'op_regex',

@@ -105,18 +111,6 @@ function operators() {
-          'method' => 'opRegex',

This change is wrong, is breaking the execution query() when you execute $this->{$info[$this->operator]['method']}($field); as the correct method is opRegex and not op_regex.

Created issue for the fixes I commented above. #2650964: Fix the execution of regular expression