Merge "Add support for regular expression on supported backend."

5 files changed, 220 insertions, 2 deletions

diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py
index 46529636d..0c9859e79 100644
--- a/lib/sqlalchemy/dialects/mysql/base.py
+++ b/lib/sqlalchemy/dialects/mysql/base.py
@@ -1759,6 +1759,62 @@ class MySQLCompiler(compiler.SQLCompiler):
             self.process(binary.right),
         )
 
+    def _mariadb_regexp_flags(self, flags, pattern, **kw):
+        return "CONCAT('(?', %s, ')', %s)" % (
+            self.process(flags, **kw),
+            self.process(pattern, **kw),
+        )
+
+    def _regexp_match(self, op_string, binary, operator, **kw):
+        flags = binary.modifiers["flags"]
+        if flags is None:
+            return self._generate_generic_binary(binary, op_string, **kw)
+        elif self.dialect.is_mariadb:
+            return "%s%s%s" % (
+                self.process(binary.left, **kw),
+                op_string,
+                self._mariadb_regexp_flags(flags, binary.right),
+            )
+        else:
+            text = "REGEXP_LIKE(%s, %s, %s)" % (
+                self.process(binary.left, **kw),
+                self.process(binary.right, **kw),
+                self.process(flags, **kw),
+            )
+            if op_string == " NOT REGEXP ":
+                return "NOT %s" % text
+            else:
+                return text
+
+    def visit_regexp_match_op_binary(self, binary, operator, **kw):
+        return self._regexp_match(" REGEXP ", binary, operator, **kw)
+
+    def visit_not_regexp_match_op_binary(self, binary, operator, **kw):
+        return self._regexp_match(" NOT REGEXP ", binary, operator, **kw)
+
+    def visit_regexp_replace_op_binary(self, binary, operator, **kw):
+        flags = binary.modifiers["flags"]
+        replacement = binary.modifiers["replacement"]
+        if flags is None:
+            return "REGEXP_REPLACE(%s, %s, %s)" % (
+                self.process(binary.left, **kw),
+                self.process(binary.right, **kw),
+                self.process(replacement, **kw),
+            )
+        elif self.dialect.is_mariadb:
+            return "REGEXP_REPLACE(%s, %s, %s)" % (
+                self.process(binary.left, **kw),
+                self._mariadb_regexp_flags(flags, binary.right),
+                self.process(replacement, **kw),
+            )
+        else:
+            return "REGEXP_REPLACE(%s, %s, %s, %s)" % (
+                self.process(binary.left, **kw),
+                self.process(binary.right, **kw),
+                self.process(replacement, **kw),
+                self.process(flags, **kw),
+            )
+
 
 class MySQLDDLCompiler(compiler.DDLCompiler):
     def get_column_specification(self, column, **kw):
diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py
index 2e5ce2581..65580b4e1 100644
--- a/lib/sqlalchemy/dialects/oracle/base.py
+++ b/lib/sqlalchemy/dialects/oracle/base.py
@@ -23,7 +23,7 @@ available, which are the use of IDENTITY columns (Oracle 12 and above only)
 or the association of a SEQUENCE with the column.
 
 Specifying GENERATED AS IDENTITY (Oracle 12 and above)
-^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
+~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
 Starting from version 12 Oracle can make use of identity columns using
 the :class:`_sql.Identity` to specify the autoincrementing behavior::
@@ -53,7 +53,7 @@ setting :paramref:`_schema.Identity.on_null` to ``True`` to specify ON NULL
 in conjunction with a 'BY DEFAULT' identity column.
 
 Using a SEQUENCE (all Oracle versions)
-^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
+~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
 Older version of Oracle had no "autoincrement"
 feature, SQLAlchemy relies upon sequences to produce these values.   With the
@@ -1211,6 +1211,43 @@ class OracleCompiler(compiler.SQLCompiler):
             self.process(binary.right),
         )
 
+    def _get_regexp_args(self, binary, kw):
+        string = self.process(binary.left, **kw)
+        pattern = self.process(binary.right, **kw)
+        flags = binary.modifiers["flags"]
+        if flags is not None:
+            flags = self.process(flags, **kw)
+        return string, pattern, flags
+
+    def visit_regexp_match_op_binary(self, binary, operator, **kw):
+        string, pattern, flags = self._get_regexp_args(binary, kw)
+        if flags is None:
+            return "REGEXP_LIKE(%s, %s)" % (string, pattern)
+        else:
+            return "REGEXP_LIKE(%s, %s, %s)" % (string, pattern, flags)
+
+    def visit_not_regexp_match_op_binary(self, binary, operator, **kw):
+        return "NOT %s" % self.visit_regexp_match_op_binary(
+            binary, operator, **kw
+        )
+
+    def visit_regexp_replace_op_binary(self, binary, operator, **kw):
+        string, pattern, flags = self._get_regexp_args(binary, kw)
+        replacement = self.process(binary.modifiers["replacement"], **kw)
+        if flags is None:
+            return "REGEXP_REPLACE(%s, %s, %s)" % (
+                string,
+                pattern,
+                replacement,
+            )
+        else:
+            return "REGEXP_REPLACE(%s, %s, %s, %s)" % (
+                string,
+                pattern,
+                replacement,
+                flags,
+            )
+
 
 class OracleDDLCompiler(compiler.DDLCompiler):
     def define_constraint_cascades(self, constraint):
diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index 07405e6d1..8eb116111 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -1796,6 +1796,53 @@ class PGCompiler(compiler.SQLCompiler):
             else ""
         )
 
+    def _regexp_match(self, base_op, binary, operator, kw):
+        flags = binary.modifiers["flags"]
+        if flags is None:
+            return self._generate_generic_binary(
+                binary, " %s " % base_op, **kw
+            )
+        if isinstance(flags, elements.BindParameter) and flags.value == "i":
+            return self._generate_generic_binary(
+                binary, " %s* " % base_op, **kw
+            )
+        flags = self.process(flags, **kw)
+        string = self.process(binary.left, **kw)
+        pattern = self.process(binary.right, **kw)
+        return "%s %s CONCAT('(?', %s, ')', %s)" % (
+            string,
+            base_op,
+            flags,
+            pattern,
+        )
+
+    def visit_regexp_match_op_binary(self, binary, operator, **kw):
+        return self._regexp_match("~", binary, operator, kw)
+
+    def visit_not_regexp_match_op_binary(self, binary, operator, **kw):
+        return self._regexp_match("!~", binary, operator, kw)
+
+    def visit_regexp_replace_op_binary(self, binary, operator, **kw):
+        string = self.process(binary.left, **kw)
+        pattern = self.process(binary.right, **kw)
+        flags = binary.modifiers["flags"]
+        if flags is not None:
+            flags = self.process(flags, **kw)
+        replacement = self.process(binary.modifiers["replacement"], **kw)
+        if flags is None:
+            return "REGEXP_REPLACE(%s, %s, %s)" % (
+                string,
+                pattern,
+                replacement,
+            )
+        else:
+            return "REGEXP_REPLACE(%s, %s, %s, %s)" % (
+                string,
+                pattern,
+                replacement,
+                flags,
+            )
+
     def visit_empty_set_expr(self, element_types):
         # cast the empty set to the type we are comparing against.  if
         # we are comparing against the null type, pick an arbitrary
diff --git a/lib/sqlalchemy/dialects/sqlite/base.py b/lib/sqlalchemy/dialects/sqlite/base.py
index 3a8ffa23d..8ef35514a 100644
--- a/lib/sqlalchemy/dialects/sqlite/base.py
+++ b/lib/sqlalchemy/dialects/sqlite/base.py
@@ -1077,6 +1077,12 @@ class SQLiteCompiler(compiler.SQLCompiler):
             ", ".join("1" for type_ in element_types or [INTEGER()]),
         )
 
+    def visit_regexp_match_op_binary(self, binary, operator, **kw):
+        return self._generate_generic_binary(binary, " REGEXP ", **kw)
+
+    def visit_not_regexp_match_op_binary(self, binary, operator, **kw):
+        return self._generate_generic_binary(binary, " NOT REGEXP ", **kw)
+
 
 class SQLiteDDLCompiler(compiler.DDLCompiler):
     def get_column_specification(self, column, **kwargs):
diff --git a/lib/sqlalchemy/dialects/sqlite/pysqlite.py b/lib/sqlalchemy/dialects/sqlite/pysqlite.py
index 3c88dab8e..a8b234921 100644
--- a/lib/sqlalchemy/dialects/sqlite/pysqlite.py
+++ b/lib/sqlalchemy/dialects/sqlite/pysqlite.py
@@ -120,6 +120,46 @@ that creates a Python sqlite3 driver level connection directly.
     `Uniform Resource Identifiers <https://www.sqlite.org/uri.html>`_ - in
     the SQLite documentation
 
+.. _pysqlite_regexp:
+
+Regular Expression Support
+---------------------------
+
+.. versionadded:: 1.4
+
+Support for the :meth:`_sql.ColumnOperators.regexp_match` operator is provided
+using Python's re.match_ function.  SQLite itself does not include a working
+regular expression operator; instead, it includes a non-implemented placeholder
+operator ``REGEXP`` that calls a user-defined function that must be provided.
+
+SQLAlchemy's implementation makes use of the pysqlite create_function_ hook
+as follows::
+
+
+    def regexp(a, b):
+        return bool(re.match(a, b))
+
+    sqlite_connection.create_function(
+        "regexp", 2, regexp,
+    )
+
+There is currently no support for regular expression flags as a separate
+argument, as these are not supported by SQLite's REGEXP operator, however these
+may be included inline within the regular expression string.  See `Python regular expressions`_ for
+details.
+
+.. seealso::
+
+    `Python regular expressions`_: Documentation for Python's regular expression syntax.
+
+.. _create_function: https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.create_function
+
+.. _re.match: https://docs.python.org/3/library/re.html#re.match
+
+.. _Python regular expressions: https://docs.python.org/3/library/re.html#re.match
+
+
+
 Compatibility with sqlite3 "native" date and datetime types
 -----------------------------------------------------------
 
@@ -362,6 +402,7 @@ by adding the desired locking mode to our ``"BEGIN"``::
 """  # noqa
 
 import os
+import re
 
 from .base import DATE
 from .base import DATETIME
@@ -461,6 +502,37 @@ class SQLiteDialect_pysqlite(SQLiteDialect):
                 connection, level
             )
 
+    def on_connect(self):
+        connect = super(SQLiteDialect_pysqlite, self).on_connect()
+
+        def regexp(a, b):
+            return bool(re.match(a, b))
+
+        def set_regexp(connection):
+            if hasattr(connection, "connection"):
+                dbapi_connection = connection.connection
+            else:
+                dbapi_connection = connection
+
+            dbapi_connection.create_function(
+                "regexp", 2, regexp,
+            )
+
+        fns = [set_regexp]
+
+        if self.isolation_level is not None:
+
+            def iso_level(conn):
+                self.set_isolation_level(conn, self.isolation_level)
+
+            fns.append(iso_level)
+
+        def connect(conn):
+            for fn in fns:
+                fn(conn)
+
+        return connect
+
     def create_connect_args(self, url):
         if url.username or url.password or url.host or url.port:
             raise exc.ArgumentError(