From 0cc04e6e1b70abc4817f275a898aa063da3de007 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Wed, 19 Mar 2008 19:35:42 +0000 Subject: [PATCH] - like() and ilike() take an optional keyword argument "escape=", which is set as the escape character using the syntax "x LIKE y ESCAPE ''" [ticket:993] --- CHANGES | 5 +++++ lib/sqlalchemy/databases/postgres.py | 4 ++-- lib/sqlalchemy/orm/properties.py | 8 ++++---- lib/sqlalchemy/sql/compiler.py | 10 +++++----- lib/sqlalchemy/sql/expression.py | 30 ++++++++++++++++------------ lib/sqlalchemy/sql/operators.py | 12 +++++------ test/sql/select.py | 25 +++++++++++++++-------- 7 files changed, 56 insertions(+), 38 deletions(-) diff --git a/CHANGES b/CHANGES index 3fb68b1ef1..ec08aef41d 100644 --- a/CHANGES +++ b/CHANGES @@ -44,6 +44,11 @@ CHANGES Column(). It (and .key) may now be deferred until the column is added to a Table. + - like() and ilike() take an optional keyword argument + "escape=", which is set as the escape character + using the syntax "x LIKE y ESCAPE ''" + [ticket:993] + - extensions - The "synonym" function is now directly usable with "declarative". Pass in the decorated property using the diff --git a/lib/sqlalchemy/databases/postgres.py b/lib/sqlalchemy/databases/postgres.py index 10f384475f..a34388a89d 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 834a7b3397..e34c725ffe 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 bb9cc7597a..6a048a7809 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 316cbd7c19..701987e203 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 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 e31d270757..13f5e6131c 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): diff --git a/test/sql/select.py b/test/sql/select.py index f64373d3ab..21b246d26e 100644 --- a/test/sql/select.py +++ b/test/sql/select.py @@ -429,15 +429,24 @@ sq.myothertable_othername AS sq_myothertable_othername FROM (" + sqstring + ") A clause = (table1.c.myid == 12) & table1.c.myid.between(15, 20) & table1.c.myid.like('hoho') assert str(clause) == str(util.pickle.loads(util.pickle.dumps(clause))) - # ILIKE - stmt = table1.select(table1.c.name.ilike('%something%')) - self.assert_compile(stmt, "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE lower(mytable.name) LIKE lower(:mytable_name_1)") - self.assert_compile(stmt, "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.name ILIKE %(mytable_name_1)s", dialect=postgres.PGDialect()) - - stmt = table1.select(~table1.c.name.ilike('%something%')) - self.assert_compile(stmt, "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE lower(mytable.name) NOT LIKE lower(:mytable_name_1)") - self.assert_compile(stmt, "SELECT mytable.myid, mytable.name, mytable.description FROM mytable WHERE mytable.name NOT ILIKE %(mytable_name_1)s", dialect=postgres.PGDialect()) + def test_like(self): + for expr, check, dialect in [ + (table1.c.myid.like('somstr'), "mytable.myid LIKE :mytable_myid_1", None), + (~table1.c.myid.like('somstr'), "mytable.myid NOT LIKE :mytable_myid_1", None), + (table1.c.myid.like('somstr', escape='\\'), "mytable.myid LIKE :mytable_myid_1 ESCAPE '\\'", None), + (~table1.c.myid.like('somstr', escape='\\'), "mytable.myid NOT LIKE :mytable_myid_1 ESCAPE '\\'", None), + (table1.c.myid.ilike('somstr', escape='\\'), "lower(mytable.myid) LIKE lower(:mytable_myid_1) ESCAPE '\\'", None), + (~table1.c.myid.ilike('somstr', escape='\\'), "lower(mytable.myid) NOT LIKE lower(:mytable_myid_1) ESCAPE '\\'", None), + (table1.c.myid.ilike('somstr', escape='\\'), "mytable.myid ILIKE %(mytable_myid_1)s ESCAPE '\\'", postgres.PGDialect()), + (~table1.c.myid.ilike('somstr', escape='\\'), "mytable.myid NOT ILIKE %(mytable_myid_1)s ESCAPE '\\'", postgres.PGDialect()), + (table1.c.name.ilike('%something%'), "lower(mytable.name) LIKE lower(:mytable_name_1)", None), + (table1.c.name.ilike('%something%'), "mytable.name ILIKE %(mytable_name_1)s", postgres.PGDialect()), + (~table1.c.name.ilike('%something%'), "lower(mytable.name) NOT LIKE lower(:mytable_name_1)", None), + (~table1.c.name.ilike('%something%'), "mytable.name NOT ILIKE %(mytable_name_1)s", postgres.PGDialect()), + ]: + self.assert_compile(expr, check, dialect=dialect) + def test_composed_string_comparators(self): self.assert_compile( table1.c.name.contains('jo'), "mytable.name LIKE '%%' || :mytable_name_1 || '%%'" , checkparams = {'mytable_name_1': u'jo'}, -- 2.47.3