Repository: kayak/pypika (python)

Issue:
Support analytic queries for Vertica and Oracle
https://my.vertica.com/docs/7.1.x/HTML/Content/Authoring/SQLReferenceManual/Functions/Analytic/AnalyticFunctions.htm

Add support for analytical functions and queries using PARTITION OVER

Pull request:
Fixes #47 Added support for tuples
Repurposed the ListField class as Tuple and added support for Criterion operations.

Patch:
diff --git a/README.rst b/README.rst
index 32d9ee5..55eea89 100644
--- a/README.rst
+++ b/README.rst
@@ -398,18 +398,60 @@ used except for weeks and quarters, which must be used separately and will ignor
     from pypika import functions as fn
 
     fruits = Tables('fruits')
-    q = Query.from_(fruits).select(
-        fruits.id,
-        fruits.name,
-    ).where(
-        fruits.harvest_date + Interval(months=1) < fn.Now()
-    )
+    q = Query.from_(fruits) \
+        .select(fruits.id, fruits.name) \
+        .where(fruits.harvest_date + Interval(months=1) < fn.Now())
 
 .. code-block:: sql
 
     SELECT id,name FROM fruits WHERE harvest_date+INTERVAL 1 MONTH<NOW()
 
 
+Tuples
+""""""
+
+Tuples are supported through the class ``pypika.Tuple`` but also through the native python tuple wherever possible.
+Tuples can be used with ``pypika.Criterion`` in **WHERE** clauses for pairwise comparisons.
+
+.. code-block:: python
+
+    from pypika import Query, Tuple
+
+    q = Query.from_(self.table_abc) \
+        .select(self.table_abc.foo, self.table_abc.bar) \
+        .where(Tuple(self.table_abc.foo, self.table_abc.bar) == Tuple(1, 2))
+
+.. code-block:: sql
+
+    SELECT "foo","bar" FROM "abc" WHERE ("foo","bar")=(1,2)
+
+Using ``pypika.Tuple`` on both sides of the comparison is redundant and |Brand| supports native python tuples.
+
+.. code-block:: python
+
+    from pypika import Query, Tuple
+
+    q = Query.from_(self.table_abc) \
+        .select(self.table_abc.foo, self.table_abc.bar) \
+        .where(Tuple(self.table_abc.foo, self.table_abc.bar) == (1, 2))
+
+.. code-block:: sql
+
+    SELECT "foo","bar" FROM "abc" WHERE ("foo","bar")=(1,2)
+
+Tuples can be used in **IN** clauses.
+
+.. code-block:: python
+
+    Query.from_(self.table_abc) \
+            .select(self.table_abc.foo, self.table_abc.bar) \
+            .where(Tuple(self.table_abc.foo, self.table_abc.bar).isin([(1, 1), (2, 2), (3, 3)]))
+
+.. code-block:: sql
+
+    SELECT "foo","bar" FROM "abc" WHERE ("foo","bar") IN ((1,1),(2,2),(3,3))
+
+
 Strings Functions
 """""""""""""""""
 
@@ -465,6 +507,7 @@ There are several string operations and function wrappers included in |Brand|.
 
     SELECT id,CONCAT(fname, ' ', lname) full_name FROM customers
 
+
 Case Statements
 """""""""""""""
 
diff --git a/pypika/__init__.py b/pypika/__init__.py
index 98904ce..3cffaf5 100644
--- a/pypika/__init__.py
+++ b/pypika/__init__.py
@@ -33,7 +33,7 @@ This contains all of the utility classes such as exceptions and decorators.
 """
 from .enums import Order, JoinType, DatePart
 from .queries import Query, Table, make_tables as Tables
-from .terms import Field, Case, Interval, Rollup
+from .terms import Field, Case, Interval, Rollup, Tuple
 from .utils import JoinException, GroupingException, CaseException, UnionException, RollupException
 
 __author__ = "Timothy Heys"
diff --git a/pypika/queries.py b/pypika/queries.py
index f2d7918..3ba2edc 100644
--- a/pypika/queries.py
+++ b/pypika/queries.py
@@ -2,7 +2,7 @@
 
 from pypika.enums import JoinType, UnionType
 from pypika.utils import JoinException, UnionException, RollupException, builder, alias_sql
-from .terms import Field, Star, Term, Function, ArithmeticExpression, Rollup, ListField
+from .terms import Field, Star, Term, Function, ArithmeticExpression, Rollup, Tuple
 
 __author__ = "Timothy Heys"
 __email__ = "theys@kayak.com"
@@ -287,7 +287,7 @@ class QueryBuilder(Selectable, Term):
         if self._mysql_rollup:
             raise AttributeError("'Query' object has no attribute '%s'" % 'rollup')
 
-        terms = [ListField(term) if isinstance(term, (list, tuple, set))
+        terms = [Tuple(*term) if isinstance(term, (list, tuple, set))
                  else term
                  for term in terms]
 
@@ -515,9 +515,9 @@ class QueryBuilder(Selectable, Term):
 
     def _columns_sql(self, with_namespace=False, **kwargs):
         """
-        SQL for Columns clause for INSERT queries 
+        SQL for Columns clause for INSERT queries
         :param with_namespace:
-            Remove from kwargs, never format the column terms with namespaces since only one table can be inserted into 
+            Remove from kwargs, never format the column terms with namespaces since only one table can be inserted into
         """
         return ' ({columns})'.format(
             columns=','.join(term.get_sql(with_namespace=False, **kwargs)
diff --git a/pypika/terms.py b/pypika/terms.py
index ceaf670..ff4d10a 100644
--- a/pypika/terms.py
+++ b/pypika/terms.py
@@ -41,10 +41,13 @@ class Term(object):
 
         """
         from .queries import QueryBuilder
-        if isinstance(val, Term) or isinstance(val, QueryBuilder):
+
+        if isinstance(val, (Term, QueryBuilder)):
             return val
         if val is None:
             return NullValue()
+        if isinstance(val, (list, tuple)):
+            return Tuple(*val)
 
         return ValueWrapper(val)
 
@@ -86,7 +89,7 @@ class Term(object):
 
     def isin(self, arg):
         if isinstance(arg, (list, tuple, set)):
-            return ContainsCriterion(self, ListField([self._wrap(value) for value in arg]))
+            return ContainsCriterion(self, Tuple(*[self._wrap(value) for value in arg]))
         return ContainsCriterion(self, arg)
 
     def notin(self, arg):
@@ -256,14 +259,17 @@ class Star(Field):
         return '*'
 
 
-class ListField(Term):
-    def __init__(self, values):
-        super(ListField, self).__init__()
-        self.values = values
+class Tuple(Term):
+    def __init__(self, *values):
+        super(Tuple, self).__init__()
+        self.values = [self._wrap(value) for value in values]
 
     def __str__(self):
         return self.get_sql()
 
+    def fields(self):
+        return sum([value.fields() for value in self.values], [])
+
     def get_sql(self, **kwargs):
         return '({})'.format(
             ','.join(term.get_sql(**kwargs)
diff --git a/pypika/utils.py b/pypika/utils.py
index 1e9048c..c118e13 100644
--- a/pypika/utils.py
+++ b/pypika/utils.py
@@ -31,11 +31,11 @@ class RollupException(Exception):
 def builder(func):
     import copy
 
-    def _decorator(self, *args, **kwargs):
+    def _copy(self, *args, **kwargs):
         self_copy = copy.deepcopy(self)
         return func(self_copy, *args, **kwargs) or self_copy
 
-    return _decorator
+    return _copy
 
 
 def resolve_is_aggregate(values):


Test patch:
diff --git a/pypika/tests/test_tuples.py b/pypika/tests/test_tuples.py
new file mode 100644
index 0000000..b08fc5b
--- /dev/null
+++ b/pypika/tests/test_tuples.py
@@ -0,0 +1,58 @@
+# coding: utf-8
+
+import unittest
+
+from pypika import Query, Tables, Tuple
+
+
+class TupleTests(unittest.TestCase):
+    table_abc, table_efg = Tables('abc', 'efg')
+
+    def test_tuple_equality_tuple_on_both(self):
+        q = Query.from_(self.table_abc) \
+            .select(self.table_abc.foo, self.table_abc.bar) \
+            .where(Tuple(self.table_abc.foo, self.table_abc.bar) == Tuple(1, 2))
+
+        self.assertEqual('SELECT "foo","bar" FROM "abc" '
+                         'WHERE ("foo","bar")=(1,2)', str(q))
+
+    def test_tuple_equality_tuple_on_left(self):
+        q = Query.from_(self.table_abc) \
+            .select(self.table_abc.foo, self.table_abc.bar) \
+            .where(Tuple(self.table_abc.foo, self.table_abc.bar) == (1, 2))
+
+        self.assertEqual('SELECT "foo","bar" FROM "abc" '
+                         'WHERE ("foo","bar")=(1,2)', str(q))
+
+    def test_tuple_equality_tuple_on_right(self):
+        q = Query.from_(self.table_abc) \
+            .select(self.table_abc.foo, self.table_abc.bar) \
+            .where((self.table_abc.foo, self.table_abc.bar) == Tuple(1, 2))
+
+        # Order is reversed due to lack of right equals method
+        self.assertEqual('SELECT "foo","bar" FROM "abc" '
+                         'WHERE (1,2)=("foo","bar")', str(q))
+
+    def test_tuple_in_using_python_tuples(self):
+        q = Query.from_(self.table_abc) \
+            .select(self.table_abc.foo, self.table_abc.bar) \
+            .where(Tuple(self.table_abc.foo, self.table_abc.bar).isin([(1, 1), (2, 2), (3, 3)]))
+
+        self.assertEqual('SELECT "foo","bar" FROM "abc" '
+                         'WHERE ("foo","bar") IN ((1,1),(2,2),(3,3))', str(q))
+
+    def test_tuple_in_using_pypika_tuples(self):
+        q = Query.from_(self.table_abc) \
+            .select(self.table_abc.foo, self.table_abc.bar) \
+            .where(Tuple(self.table_abc.foo, self.table_abc.bar).isin([Tuple(1, 1), Tuple(2, 2), Tuple(3, 3)]))
+
+        self.assertEqual('SELECT "foo","bar" FROM "abc" '
+                         'WHERE ("foo","bar") IN ((1,1),(2,2),(3,3))', str(q))
+
+    def test_tuple_in_using_mixed_tuples(self):
+        q = Query.from_(self.table_abc) \
+            .select(self.table_abc.foo, self.table_abc.bar) \
+            .where(Tuple(self.table_abc.foo, self.table_abc.bar).isin([(1, 1), Tuple(2, 2), (3, 3)]))
+
+        self.assertEqual('SELECT "foo","bar" FROM "abc" '
+                         'WHERE ("foo","bar") IN ((1,1),(2,2),(3,3))', str(q))