- like() and ilike() take an optional keyword argument

"escape=<somestring>", which is set as the escape character
using the syntax "x LIKE y ESCAPE '<somestring>'"
[ticket:993]

5 files changed, 34 insertions, 30 deletions

diff --git a/lib/sqlalchemy/databases/postgres.py b/lib/sqlalchemy/databases/postgres.py
index 10f384475..a34388a89 100644
--- a/lib/sqlalchemy/databases/postgres.py
+++ b/lib/sqlalchemy/databases/postgres.py
@@ -641,8 +641,8 @@ class PGCompiler(compiler.DefaultCompiler):
     operators.update(
         {
             sql_operators.mod : '%%',
-            sql_operators.ilike_op: 'ILIKE',
-            sql_operators.notilike_op: 'NOT ILIKE'
+            sql_operators.ilike_op: lambda x, y, escape=None: '%s ILIKE %s' % (x, y) + (escape and ' ESCAPE \'%s\'' % escape or ''),
+            sql_operators.notilike_op: lambda x, y, escape=None: '%s NOT ILIKE %s' % (x, y) + (escape and ' ESCAPE \'%s\'' % escape or ''),
         }
     )
 
diff --git a/lib/sqlalchemy/orm/properties.py b/lib/sqlalchemy/orm/properties.py
index 834a7b339..e34c725ff 100644
--- a/lib/sqlalchemy/orm/properties.py
+++ b/lib/sqlalchemy/orm/properties.py
@@ -87,12 +87,12 @@ class ColumnProperty(StrategizedProperty):
         def clause_element(self):
             return self.prop.columns[0]
 
-        def operate(self, op, *other):
-            return op(self.prop.columns[0], *other)
+        def operate(self, op, *other, **kwargs):
+            return op(self.prop.columns[0], *other, **kwargs)
 
-        def reverse_operate(self, op, other):
+        def reverse_operate(self, op, other, **kwargs):
             col = self.prop.columns[0]
-            return op(col._bind_param(other), col)
+            return op(col._bind_param(other), col, **kwargs)
 
 ColumnProperty.logger = logging.class_logger(ColumnProperty)
 
diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index bb9cc7597..6a048a780 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -76,10 +76,10 @@ OPERATORS =  {
     operators.eq : '=',
     operators.distinct_op : 'DISTINCT',
     operators.concat_op : '||',
-    operators.like_op : 'LIKE',
-    operators.notlike_op : 'NOT LIKE',
-    operators.ilike_op : lambda x, y: "lower(%s) LIKE lower(%s)" % (x, y),
-    operators.notilike_op : lambda x, y: "lower(%s) NOT LIKE lower(%s)" % (x, y),
+    operators.like_op : lambda x, y, escape=None: '%s LIKE %s' % (x, y) + (escape and ' ESCAPE \'%s\'' % escape or ''),
+    operators.notlike_op : lambda x, y, escape=None: '%s NOT LIKE %s' % (x, y) + (escape and ' ESCAPE \'%s\'' % escape or ''),
+    operators.ilike_op : lambda x, y, escape=None: "lower(%s) LIKE lower(%s)" % (x, y) + (escape and ' ESCAPE \'%s\'' % escape or ''),
+    operators.notilike_op : lambda x, y, escape=None: "lower(%s) NOT LIKE lower(%s)" % (x, y) + (escape and ' ESCAPE \'%s\'' % escape or ''),
     operators.between_op : 'BETWEEN',
     operators.in_op : 'IN',
     operators.notin_op : 'NOT IN',
@@ -394,7 +394,7 @@ class DefaultCompiler(engine.Compiled):
     def visit_binary(self, binary, **kwargs):
         op = self.operator_string(binary.operator)
         if callable(op):
-            return op(self.process(binary.left), self.process(binary.right))
+            return op(self.process(binary.left), self.process(binary.right), **binary.modifiers)
         else:
             return self.process(binary.left) + " " + op + " " + self.process(binary.right)
 
diff --git a/lib/sqlalchemy/sql/expression.py b/lib/sqlalchemy/sql/expression.py
index 316cbd7c1..701987e20 100644
--- a/lib/sqlalchemy/sql/expression.py
+++ b/lib/sqlalchemy/sql/expression.py
@@ -1137,11 +1137,11 @@ class ColumnOperators(Operators):
     def concat(self, other):
         return self.operate(operators.concat_op, other)
 
-    def like(self, other):
-        return self.operate(operators.like_op, other)
+    def like(self, other, escape=None):
+        return self.operate(operators.like_op, other, escape=escape)
 
-    def ilike(self, other):
-        return self.operate(operators.ilike_op, other)
+    def ilike(self, other, escape=None):
+        return self.operate(operators.ilike_op, other, escape=escape)
 
     def in_(self, *other):
         return self.operate(operators.in_op, other)
@@ -1200,7 +1200,7 @@ class ColumnOperators(Operators):
 class _CompareMixin(ColumnOperators):
     """Defines comparison and math operations for ``ClauseElement`` instances."""
 
-    def __compare(self, op, obj, negate=None, reverse=False):
+    def __compare(self, op, obj, negate=None, reverse=False, **kwargs):
         if obj is None or isinstance(obj, _Null):
             if op == operators.eq:
                 return _BinaryExpression(self.expression_element(), null(), operators.is_, negate=operators.isnot)
@@ -1212,9 +1212,9 @@ class _CompareMixin(ColumnOperators):
             obj = self._check_literal(obj)
 
         if reverse:
-            return _BinaryExpression(obj, self.expression_element(), op, type_=sqltypes.Boolean, negate=negate)
+            return _BinaryExpression(obj, self.expression_element(), op, type_=sqltypes.Boolean, negate=negate, modifiers=kwargs)
         else:
-            return _BinaryExpression(self.expression_element(), obj, op, type_=sqltypes.Boolean, negate=negate)
+            return _BinaryExpression(self.expression_element(), obj, op, type_=sqltypes.Boolean, negate=negate, modifiers=kwargs)
 
     def __operate(self, op, obj, reverse=False):
         obj = self._check_literal(obj)
@@ -1245,13 +1245,13 @@ class _CompareMixin(ColumnOperators):
         operators.ilike_op : (__compare, operators.notilike_op),
     }
 
-    def operate(self, op, *other):
+    def operate(self, op, *other, **kwargs):
         o = _CompareMixin.operators[op]
-        return o[0](self, op, other[0], *o[1:])
+        return o[0](self, op, other[0], *o[1:], **kwargs)
 
-    def reverse_operate(self, op, other):
+    def reverse_operate(self, op, other, **kwargs):
         o = _CompareMixin.operators[op]
-        return o[0](self, op, other, reverse=True, *o[1:])
+        return o[0](self, op, other, reverse=True, *o[1:], **kwargs)
 
     def in_(self, *other):
         return self._in_impl(operators.in_op, operators.notin_op, *other)
@@ -2114,13 +2114,17 @@ class _UnaryExpression(ColumnElement):
 class _BinaryExpression(ColumnElement):
     """Represent an expression that is ``LEFT <operator> RIGHT``."""
 
-    def __init__(self, left, right, operator, type_=None, negate=None):
+    def __init__(self, left, right, operator, type_=None, negate=None, modifiers=None):
         ColumnElement.__init__(self)
         self.left = _literal_as_text(left).self_group(against=operator)
         self.right = _literal_as_text(right).self_group(against=operator)
         self.operator = operator
         self.type = sqltypes.to_instance(type_)
         self.negate = negate
+        if modifiers is None:
+            self.modifiers = {}
+        else:
+            self.modifiers = modifiers
 
     def _get_from_objects(self, **modifiers):
         return self.left._get_from_objects(**modifiers) + self.right._get_from_objects(**modifiers)
@@ -2159,7 +2163,7 @@ class _BinaryExpression(ColumnElement):
 
     def _negate(self):
         if self.negate is not None:
-            return _BinaryExpression(self.left, self.right, self.negate, negate=self.operator, type_=self.type)
+            return _BinaryExpression(self.left, self.right, self.negate, negate=self.operator, type_=self.type, modifiers=self.modifiers)
         else:
             return super(_BinaryExpression, self)._negate()
 
diff --git a/lib/sqlalchemy/sql/operators.py b/lib/sqlalchemy/sql/operators.py
index e31d27075..13f5e6131 100644
--- a/lib/sqlalchemy/sql/operators.py
+++ b/lib/sqlalchemy/sql/operators.py
@@ -25,16 +25,16 @@ def isnot():
 def op(a, opstring, b):
     return a.op(opstring)(b)
 
-def like_op(a, b):
-    return a.like(b)
+def like_op(a, b, escape=None):
+    return a.like(b, escape=escape)
 
-def notlike_op(a, b):
+def notlike_op(a, b, escape=None):
     raise NotImplementedError()
 
-def ilike_op(a, b):
-    return a.ilike(b)
+def ilike_op(a, b, escape=None):
+    return a.ilike(b, escape=escape)
 
-def notilike_op(a, b):
+def notilike_op(a, b, escape=None):
     raise NotImplementedError()
 
 def between_op(a, b, c):