Commits

Mike Bayer committed b05807d

- [feature] Reworked the startswith(), endswith(),
contains() operators to do a better job with
negation (NOT LIKE), and also to assemble them
at compilation time so that their rendered SQL
can be altered, such as in the case for Firebird
STARTING WITH [ticket:2470]
- [feature] firebird - The "startswith()" operator renders
as "STARTING WITH", "~startswith()" renders
as "NOT STARTING WITH", using FB's more efficient
operator. [ticket:2470]

Comments (0)

Files changed (7)

     name.  The deprecated fold_equivalents() feature is
     removed [ticket:1729].
 
+  - [feature] Reworked the startswith(), endswith(),
+    contains() operators to do a better job with
+    negation (NOT LIKE), and also to assemble them
+    at compilation time so that their rendered SQL
+    can be altered, such as in the case for Firebird
+    STARTING WITH [ticket:2470]
+
   - [bug] Fixes to the interpretation of the
     Column "default" parameter as a callable
     to not pass ExecutionContext into a keyword
     The phrase is established using with_hint().
     Courtesy Ryan Kelly [ticket:2506]
 
+- firebird
+  - [feature] The "startswith()" operator renders
+    as "STARTING WITH", "~startswith()" renders
+    as "NOT STARTING WITH", using FB's more efficient
+    operator.  [ticket:2470]
 
 - mysql
   - [bug] Dialect no longer emits expensive server

lib/sqlalchemy/dialects/firebird/base.py

 class FBCompiler(sql.compiler.SQLCompiler):
     """Firebird specific idiosyncrasies"""
 
+    #def visit_contains_op_binary(self, binary, operator, **kw):
+        # cant use CONTAINING b.c. it's case insensitive.
+
+    #def visit_notcontains_op_binary(self, binary, operator, **kw):
+        # cant use NOT CONTAINING b.c. it's case insensitive.
+
+    def visit_startswith_op_binary(self, binary, operator, **kw):
+        return '%s STARTING WITH %s' % (
+                            binary.left._compiler_dispatch(self, **kw),
+                            binary.right._compiler_dispatch(self, **kw))
+
+    def visit_notstartswith_op_binary(self, binary, operator, **kw):
+        return '%s NOT STARTING WITH %s' % (
+                            binary.left._compiler_dispatch(self, **kw),
+                            binary.right._compiler_dispatch(self, **kw))
+
     def visit_mod_binary(self, binary, operator, **kw):
         return "mod(%s, %s)" % (
                                 self.process(binary.left, **kw),
 
         result = ""
         if select._limit:
-            result += "FIRST %s "  % self.process(sql.literal(select._limit))
+            result += "FIRST %s " % self.process(sql.literal(select._limit))
         if select._offset:
-            result +="SKIP %s "  %  self.process(sql.literal(select._offset))
+            result += "SKIP %s " % self.process(sql.literal(select._offset))
         if select._distinct:
             result += "DISTINCT "
         return result

lib/sqlalchemy/sql/compiler.py

 
 import re
 import sys
-from .. import schema, engine, util, exc
+from .. import schema, engine, util, exc, types
 from . import (
     operators, functions, util as sql_util, visitors, expression as sql
 )
     def _generate_generic_unary_modifier(self, unary, opstring, **kw):
         return unary.element._compiler_dispatch(self, **kw) + opstring
 
+    @util.memoized_property
+    def _like_percent_literal(self):
+        return sql.literal_column("'%'", type_=types.String())
+
+    def visit_contains_op_binary(self, binary, operator, **kw):
+        binary = binary._clone()
+        percent = self._like_percent_literal
+        binary.right = percent.__add__(binary.right).__add__(percent)
+        return self.visit_like_op_binary(binary, operator, **kw)
+
+    def visit_notcontains_op_binary(self, binary, operator, **kw):
+        binary = binary._clone()
+        percent = self._like_percent_literal
+        binary.right = percent.__add__(binary.right).__add__(percent)
+        return self.visit_notlike_op_binary(binary, operator, **kw)
+
+    def visit_startswith_op_binary(self, binary, operator, **kw):
+        binary = binary._clone()
+        percent = self._like_percent_literal
+        binary.right = percent.__radd__(
+                    binary.right
+                )
+        return self.visit_like_op_binary(binary, operator, **kw)
+
+    def visit_notstartswith_op_binary(self, binary, operator, **kw):
+        binary = binary._clone()
+        percent = self._like_percent_literal
+        binary.right = percent.__radd__(
+                    binary.right
+                )
+        return self.visit_notlike_op_binary(binary, operator, **kw)
+
+    def visit_endswith_op_binary(self, binary, operator, **kw):
+        binary = binary._clone()
+        percent = self._like_percent_literal
+        binary.right = percent.__add__(binary.right)
+        return self.visit_like_op_binary(binary, operator, **kw)
+
+    def visit_notendswith_op_binary(self, binary, operator, **kw):
+        binary = binary._clone()
+        percent = self._like_percent_literal
+        binary.right = percent.__add__(binary.right)
+        return self.visit_notlike_op_binary(binary, operator, **kw)
+
     def visit_like_op_binary(self, binary, operator, **kw):
         escape = binary.modifiers.get("escape", None)
         return '%s LIKE %s' % (

lib/sqlalchemy/sql/expression.py

         """See :meth:`.ColumnOperators.__neg__`."""
         return UnaryExpression(expr, operator=operators.neg)
 
-    def _startswith_impl(self, expr, op, other, escape=None, **kw):
-        """See :meth:`.ColumnOperators.startswith`."""
-        # use __radd__ to force string concat behavior
-        return self._boolean_compare(
-            expr,
-            operators.like_op,
-            literal_column("'%'", type_=sqltypes.String).__radd__(
-                                self._check_literal(expr,
-                                        operators.like_op, other)
-                            ),
-            escape=escape)
-
-    def _endswith_impl(self, expr, op, other, escape=None, **kw):
-        """See :meth:`.ColumnOperators.endswith`."""
-        return self._boolean_compare(
-            expr,
-            operators.like_op,
-            literal_column("'%'", type_=sqltypes.String) +
-                self._check_literal(expr, operators.like_op, other),
-            escape=escape)
-
-    def _contains_impl(self, expr, op, other, escape=None, **kw):
-        """See :meth:`.ColumnOperators.contains`."""
-        return self._boolean_compare(
-            expr,
-            operators.like_op,
-            literal_column("'%'", type_=sqltypes.String) +
-                self._check_literal(expr, operators.like_op, other) +
-                literal_column("'%'", type_=sqltypes.String),
-            escape=escape)
-
     def _match_impl(self, expr, op, other, **kw):
         """See :meth:`.ColumnOperators.match`."""
         return self._boolean_compare(expr, operators.match_op,
         "eq": (_boolean_compare, operators.ne),
         "like_op": (_boolean_compare, operators.notlike_op),
         "ilike_op": (_boolean_compare, operators.notilike_op),
+        "contains_op": (_boolean_compare, operators.notcontains_op),
+        "startswith_op": (_boolean_compare, operators.notstartswith_op),
+        "endswith_op": (_boolean_compare, operators.notendswith_op),
         "desc_op": (_scalar, desc),
         "asc_op": (_scalar, asc),
         "nullsfirst_op": (_scalar, nullsfirst),
         "match_op": (_match_impl,),
         "distinct_op": (_distinct_impl,),
         "between_op": (_between_impl, ),
-        "contains_op": (_contains_impl, ),
-        "startswith_op": (_startswith_impl,),
-        "endswith_op": (_endswith_impl,),
         "neg": (_neg_impl,),
         "getitem": (_unsupported_impl,),
     }

lib/sqlalchemy/sql/operators.py

 def startswith_op(a, b, escape=None):
     return a.startswith(b, escape=escape)
 
+def notstartswith_op(a, b, escape=None):
+    return ~a.startswith(b, escape=escape)
+
 def endswith_op(a, b, escape=None):
     return a.endswith(b, escape=escape)
 
+def notendswith_op(a, b, escape=None):
+    return ~a.endswith(b, escape=escape)
+
 def contains_op(a, b, escape=None):
     return a.contains(b, escape=escape)
 
+def notcontains_op(a, b, escape=None):
+    return ~a.contains(b, escape=escape)
+
 def match_op(a, b):
     return a.match(b)
 

test/sql/test_compiler.py

         ]:
             self.assert_compile(expr, check, dialect=dialect)
 
-    def test_composed_string_comparators(self):
-        self.assert_compile(
-            table1.c.name.contains('jo'),
-            "mytable.name LIKE '%%' || :name_1 || '%%'" ,
-            checkparams = {'name_1': u'jo'},
-        )
-        self.assert_compile(
-            table1.c.name.contains('jo'),
-            "mytable.name LIKE concat(concat('%%', %s), '%%')" ,
-            checkparams = {'name_1': u'jo'},
-            dialect=mysql.dialect()
-        )
-        self.assert_compile(
-            table1.c.name.contains('jo', escape='\\'),
-            "mytable.name LIKE '%%' || :name_1 || '%%' ESCAPE '\\'" ,
-            checkparams = {'name_1': u'jo'},
-        )
-        self.assert_compile(
-            table1.c.name.startswith('jo', escape='\\'),
-            "mytable.name LIKE :name_1 || '%%' ESCAPE '\\'" )
-        self.assert_compile(
-            table1.c.name.endswith('jo', escape='\\'),
-            "mytable.name LIKE '%%' || :name_1 ESCAPE '\\'" )
-        self.assert_compile(
-            table1.c.name.endswith('hn'),
-            "mytable.name LIKE '%%' || :name_1",
-            checkparams = {'name_1': u'hn'}, )
-        self.assert_compile(
-            table1.c.name.endswith('hn'),
-            "mytable.name LIKE concat('%%', %s)",
-            checkparams = {'name_1': u'hn'}, dialect=mysql.dialect()
-        )
-        self.assert_compile(
-            table1.c.name.startswith(u"hi \xf6 \xf5"),
-            "mytable.name LIKE :name_1 || '%%'",
-            checkparams = {'name_1': u'hi \xf6 \xf5'},
-        )
-        self.assert_compile(
-                column('name').endswith(text("'foo'")),
-                "name LIKE '%%' || 'foo'"  )
-        self.assert_compile(
-                column('name').endswith(literal_column("'foo'")),
-                "name LIKE '%%' || 'foo'"  )
-        self.assert_compile(
-                column('name').startswith(text("'foo'")),
-                "name LIKE 'foo' || '%%'"  )
-        self.assert_compile(
-                column('name').startswith(text("'foo'")),
-                 "name LIKE concat('foo', '%%')", dialect=mysql.dialect())
-        self.assert_compile(
-                column('name').startswith(literal_column("'foo'")),
-                "name LIKE 'foo' || '%%'"  )
-        self.assert_compile(
-                column('name').startswith(literal_column("'foo'")),
-                "name LIKE concat('foo', '%%')", dialect=mysql.dialect())
 
     def test_multiple_col_binds(self):
         self.assert_compile(

test/sql/test_operators.py

 from test.lib.testing import assert_raises_message
 from sqlalchemy.sql import column, desc, asc, literal, collate
 from sqlalchemy.sql.expression import BinaryExpression, \
-                ClauseList, Grouping, _DefaultColumnComparator,\
+                ClauseList, Grouping, \
                 UnaryExpression
 from sqlalchemy.sql import operators
 from sqlalchemy import exc
 from sqlalchemy.schema import Column, Table, MetaData
 from sqlalchemy.types import Integer, TypeEngine, TypeDecorator
+from sqlalchemy.dialects import mysql, firebird
+
+from sqlalchemy import text, literal_column
 
 class DefaultColumnComparatorTest(fixtures.TestBase):
 
         self.assert_compile(f / (f / (f - f)), "f / (f / (f - f))")
 
 
+class ComposedLikeOperatorsTest(fixtures.TestBase, testing.AssertsCompiledSQL):
+    __dialect__ = 'default'
+
+    def test_contains(self):
+        self.assert_compile(
+            column('x').contains('y'),
+            "x LIKE '%%' || :x_1 || '%%'",
+            checkparams={'x_1': 'y'}
+        )
+
+    def test_contains_escape(self):
+        self.assert_compile(
+            column('x').contains('y', escape='\\'),
+            "x LIKE '%%' || :x_1 || '%%' ESCAPE '\\'",
+            checkparams={'x_1': 'y'}
+        )
+
+    def test_contains_literal(self):
+        self.assert_compile(
+            column('x').contains(literal_column('y')),
+            "x LIKE '%%' || y || '%%'",
+            checkparams={}
+        )
+
+    def test_contains_text(self):
+        self.assert_compile(
+            column('x').contains(text('y')),
+            "x LIKE '%%' || y || '%%'",
+            checkparams={}
+        )
+
+    def test_not_contains(self):
+        self.assert_compile(
+            ~column('x').contains('y'),
+            "x NOT LIKE '%%' || :x_1 || '%%'",
+            checkparams={'x_1': 'y'}
+        )
+
+    def test_not_contains_escape(self):
+        self.assert_compile(
+            ~column('x').contains('y', escape='\\'),
+            "x NOT LIKE '%%' || :x_1 || '%%' ESCAPE '\\'",
+            checkparams={'x_1': 'y'}
+        )
+
+    def test_contains_concat(self):
+        self.assert_compile(
+            column('x').contains('y'),
+            "x LIKE concat(concat('%%', %s), '%%')",
+            checkparams={'x_1': 'y'},
+            dialect=mysql.dialect()
+        )
+
+    def test_not_contains_concat(self):
+        self.assert_compile(
+            ~column('x').contains('y'),
+            "x NOT LIKE concat(concat('%%', %s), '%%')",
+            checkparams={'x_1': 'y'},
+            dialect=mysql.dialect()
+        )
+
+    def test_contains_literal_concat(self):
+        self.assert_compile(
+            column('x').contains(literal_column('y')),
+            "x LIKE concat(concat('%%', y), '%%')",
+            checkparams={},
+            dialect=mysql.dialect()
+        )
+
+    def test_contains_text_concat(self):
+        self.assert_compile(
+            column('x').contains(text('y')),
+            "x LIKE concat(concat('%%', y), '%%')",
+            checkparams={},
+            dialect=mysql.dialect()
+        )
+
+    def test_startswith(self):
+        self.assert_compile(
+            column('x').startswith('y'),
+            "x LIKE :x_1 || '%%'",
+            checkparams={'x_1': 'y'}
+        )
+
+    def test_startswith_escape(self):
+        self.assert_compile(
+            column('x').startswith('y', escape='\\'),
+            "x LIKE :x_1 || '%%' ESCAPE '\\'",
+            checkparams={'x_1': 'y'}
+        )
+
+    def test_not_startswith(self):
+        self.assert_compile(
+            ~column('x').startswith('y'),
+            "x NOT LIKE :x_1 || '%%'",
+            checkparams={'x_1': 'y'}
+        )
+
+    def test_not_startswith_escape(self):
+        self.assert_compile(
+            ~column('x').startswith('y', escape='\\'),
+            "x NOT LIKE :x_1 || '%%' ESCAPE '\\'",
+            checkparams={'x_1': 'y'}
+        )
+
+    def test_startswith_literal(self):
+        self.assert_compile(
+            column('x').startswith(literal_column('y')),
+            "x LIKE y || '%%'",
+            checkparams={}
+        )
+
+    def test_startswith_text(self):
+        self.assert_compile(
+            column('x').startswith(text('y')),
+            "x LIKE y || '%%'",
+            checkparams={}
+        )
+
+    def test_startswith_concat(self):
+        self.assert_compile(
+            column('x').startswith('y'),
+            "x LIKE concat(%s, '%%')",
+            checkparams={'x_1': 'y'},
+            dialect=mysql.dialect()
+        )
+
+    def test_not_startswith_concat(self):
+        self.assert_compile(
+            ~column('x').startswith('y'),
+            "x NOT LIKE concat(%s, '%%')",
+            checkparams={'x_1': 'y'},
+            dialect=mysql.dialect()
+        )
+
+    def test_startswith_firebird(self):
+        self.assert_compile(
+            column('x').startswith('y'),
+            "x STARTING WITH :x_1",
+            checkparams={'x_1': 'y'},
+            dialect=firebird.dialect()
+        )
+
+    def test_not_startswith_firebird(self):
+        self.assert_compile(
+            ~column('x').startswith('y'),
+            "x NOT STARTING WITH :x_1",
+            checkparams={'x_1': 'y'},
+            dialect=firebird.dialect()
+        )
+
+    def test_startswith_literal_mysql(self):
+        self.assert_compile(
+            column('x').startswith(literal_column('y')),
+            "x LIKE concat(y, '%%')",
+            checkparams={},
+            dialect=mysql.dialect()
+        )
+
+    def test_startswith_text_mysql(self):
+        self.assert_compile(
+            column('x').startswith(text('y')),
+            "x LIKE concat(y, '%%')",
+            checkparams={},
+            dialect=mysql.dialect()
+        )
+
+    def test_endswith(self):
+        self.assert_compile(
+            column('x').endswith('y'),
+            "x LIKE '%%' || :x_1",
+            checkparams={'x_1': 'y'}
+        )
+
+    def test_endswith_escape(self):
+        self.assert_compile(
+            column('x').endswith('y', escape='\\'),
+            "x LIKE '%%' || :x_1 ESCAPE '\\'",
+            checkparams={'x_1': 'y'}
+        )
+
+    def test_not_endswith(self):
+        self.assert_compile(
+            ~column('x').endswith('y'),
+            "x NOT LIKE '%%' || :x_1",
+            checkparams={'x_1': 'y'}
+        )
+
+    def test_not_endswith_escape(self):
+        self.assert_compile(
+            ~column('x').endswith('y', escape='\\'),
+            "x NOT LIKE '%%' || :x_1 ESCAPE '\\'",
+            checkparams={'x_1': 'y'}
+        )
+
+    def test_endswith_literal(self):
+        self.assert_compile(
+            column('x').endswith(literal_column('y')),
+            "x LIKE '%%' || y",
+            checkparams={}
+        )
+
+    def test_endswith_text(self):
+        self.assert_compile(
+            column('x').endswith(text('y')),
+            "x LIKE '%%' || y",
+            checkparams={}
+        )
+
+    def test_endswith_mysql(self):
+        self.assert_compile(
+            column('x').endswith('y'),
+            "x LIKE concat('%%', %s)",
+            checkparams={'x_1': 'y'},
+            dialect=mysql.dialect()
+        )
+
+    def test_not_endswith_mysql(self):
+        self.assert_compile(
+            ~column('x').endswith('y'),
+            "x NOT LIKE concat('%%', %s)",
+            checkparams={'x_1': 'y'},
+            dialect=mysql.dialect()
+        )
+
+    def test_endswith_literal_mysql(self):
+        self.assert_compile(
+            column('x').endswith(literal_column('y')),
+            "x LIKE concat('%%', y)",
+            checkparams={},
+            dialect=mysql.dialect()
+        )
+
+    def test_endswith_text_mysql(self):
+        self.assert_compile(
+            column('x').endswith(text('y')),
+            "x LIKE concat('%%', y)",
+            checkparams={},
+            dialect=mysql.dialect()
+        )
+