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

2 files changed, 245 insertions, 56 deletions

diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py
index 40d29f222..356f2e8b1 100644
--- a/test/sql/test_compiler.py
+++ b/test/sql/test_compiler.py
@@ -1029,61 +1029,6 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL):
         ]:
             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(
diff --git a/test/sql/test_operators.py b/test/sql/test_operators.py
index 26a36fd34..69a22172f 100644
--- a/test/sql/test_operators.py
+++ b/test/sql/test_operators.py
@@ -2,12 +2,15 @@ from test.lib import fixtures, testing
 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):
 
@@ -320,3 +323,244 @@ class OperatorAssociativityTest(fixtures.TestBase, testing.AssertsCompiledSQL):
         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()
+        )
+