- [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]

4 files changed, 75 insertions, 37 deletions

diff --git a/lib/sqlalchemy/dialects/firebird/base.py b/lib/sqlalchemy/dialects/firebird/base.py
index f7877a901..b4b856804 100644
--- a/lib/sqlalchemy/dialects/firebird/base.py
+++ b/lib/sqlalchemy/dialects/firebird/base.py
@@ -200,6 +200,22 @@ class FBTypeCompiler(compiler.GenericTypeCompiler):
 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),
@@ -265,9 +281,9 @@ class FBCompiler(sql.compiler.SQLCompiler):
 
         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
diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index 8e4f0288f..297cd9adb 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -24,7 +24,7 @@ To generate user-defined SQL strings, see
 
 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
 )
@@ -670,6 +670,50 @@ class SQLCompiler(engine.Compiled):
     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' % (
diff --git a/lib/sqlalchemy/sql/expression.py b/lib/sqlalchemy/sql/expression.py
index 0e8a46b60..2583e6510 100644
--- a/lib/sqlalchemy/sql/expression.py
+++ b/lib/sqlalchemy/sql/expression.py
@@ -2049,37 +2049,6 @@ class _DefaultColumnComparator(operators.ColumnOperators):
         """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,
@@ -2124,6 +2093,9 @@ class _DefaultColumnComparator(operators.ColumnOperators):
         "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),
@@ -2133,9 +2105,6 @@ class _DefaultColumnComparator(operators.ColumnOperators):
         "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,),
     }
diff --git a/lib/sqlalchemy/sql/operators.py b/lib/sqlalchemy/sql/operators.py
index f1607c884..ba33d016a 100644
--- a/lib/sqlalchemy/sql/operators.py
+++ b/lib/sqlalchemy/sql/operators.py
@@ -558,12 +558,21 @@ def distinct_op(a):
 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)