Repository: kayak/pypika
Pull request #290 (2019-08-07 13:23:38)

Support collate on
Support COLLATE when joining tables.

Example:
`q1 = Query.from_(table_a).select(table_b.ouch).join(table_b).on(table_a.foo == table_b.boo, collate="utf8_general_ci")`

Creates `SELECT "b"."ouch" FROM "a" JOIN "b" ON "a"."foo"="b"."boo" COLLATE utf8_general_ci`


Patch:
diff --git a/README.rst b/README.rst
index af8e05a..d5b9d98 100644
--- a/README.rst
+++ b/README.rst
@@ -97,7 +97,7 @@ Using ``pypika.Table`` based select alias
 .. code-block:: python
 
     customers = Table('customers')
-    q = customers.select(customers.id, customers.fname)
+    q = customers.select(custimers.id, customers.fname)
 
 query will return in the following SQL:
 
diff --git a/pypika/functions.py b/pypika/functions.py
index d4e527d..52cf3c4 100644
--- a/pypika/functions.py
+++ b/pypika/functions.py
@@ -135,11 +135,6 @@ class DateDiff(Function):
         super(DateDiff, self).__init__('DATEDIFF', interval, start_date, end_date, alias=alias)
 
 
-class TimeDiff(Function):
-    def __init__(self, start_time, end_time, alias=None):
-        super(TimeDiff, self).__init__('TIMEDIFF', start_time, end_time, alias=alias)
-
-
 class DateAdd(Function):
     def __init__(self, date_part, interval, term, alias=None):
         super(DateAdd, self).__init__('DATE_ADD', date_part, interval, term, alias=alias)
diff --git a/pypika/queries.py b/pypika/queries.py
index f25d9ba..726f1d7 100644
--- a/pypika/queries.py
+++ b/pypika/queries.py
@@ -1102,12 +1102,12 @@ class Joiner:
         self.how = how
         self.type_label = type_label
 
-    def on(self, criterion):
+    def on(self, criterion, collate=None):
         if criterion is None:
             raise JoinException("Parameter 'criterion' is required for a "
                                 "{type} JOIN but was not supplied.".format(type=self.type_label))
 
-        self.query.do_join(JoinOn(self.item, self.how, criterion))
+        self.query.do_join(JoinOn(self.item, self.how, criterion, collate))
         return self.query
 
     def on_field(self, *fields):
@@ -1172,15 +1172,17 @@ class Join:
 
 
 class JoinOn(Join):
-    def __init__(self, item, how, criteria):
+    def __init__(self, item, how, criteria, collate=None):
         super(JoinOn, self).__init__(item, how)
         self.criterion = criteria
+        self.collate = collate
 
     def get_sql(self, **kwargs):
         join_sql = super(JoinOn, self).get_sql(**kwargs)
-        return '{join} ON {criterion}'.format(
+        return '{join} ON {criterion}{collate}'.format(
               join=join_sql,
               criterion=self.criterion.get_sql(**kwargs),
+              collate=" COLLATE {}".format(self.collate) if self.collate else ""
         )
 
     def validate(self, _from, _joins):


Test patch:
diff --git a/pypika/tests/test_functions.py b/pypika/tests/test_functions.py
index 75f3a09..fcb3add 100644
--- a/pypika/tests/test_functions.py
+++ b/pypika/tests/test_functions.py
@@ -576,10 +576,6 @@ class DateFunctionsTests(unittest.TestCase):
         a = fn.TimestampAdd('year', 1, '2017-10-01')
         self.assertEqual(str(a), "TIMESTAMPADD('year',1,'2017-10-01')")
 
-    def test_time_diff(self):
-        a = fn.TimeDiff('18:00:00', '10:00:00')
-        self.assertEqual(str(a), "TIMEDIFF('18:00:00','10:00:00')")
-
     def test_date_add(self):
         a = fn.DateAdd('year', 1, '2017-10-01')
         self.assertEqual(str(a), "DATE_ADD('year',1,'2017-10-01')")
diff --git a/pypika/tests/test_joins.py b/pypika/tests/test_joins.py
index 466c016..6ec35a3 100644
--- a/pypika/tests/test_joins.py
+++ b/pypika/tests/test_joins.py
@@ -156,6 +156,17 @@ class SelectQueryJoinTests(unittest.TestCase):
             str(q_b)
         )
 
+    def test_join_on_collate(self):
+        table_a, table_b = Tables("a", "b")
+
+        q1 = Query.from_(table_a).select(table_b.ouch).join(table_b).on(table_a.foo == table_b.boo,
+                                                                        collate="utf8_general_ci")
+        q2 = Query.from_(table_a).select(table_b.ouch).join(table_b).on(table_a.foo == table_b.boo)
+
+        self.assertEqual('SELECT "b"."ouch" FROM "a" JOIN "b" ON "a"."foo"="b"."boo" COLLATE utf8_general_ci', str(q1))
+        self.assertEqual('SELECT "b"."ouch" FROM "a" JOIN "b" ON "a"."foo"="b"."boo"', str(q2))
+
+
 class JoinBehaviorTests(unittest.TestCase):
     table_abc, table_efg, table_hij, table_klm = Tables('abc', 'efg', 'hij', 'klm')