Repository: kayak/pypika
Pull request #159 (2018-09-07 11:09:16)

Added negation, fixed CAST, Added SUM DISTINCT
Added a negation term
fixed CAST function and SqlTypes enum
added SUM(DISTINCT ...)

coveralls: 
[![Coverage Status](https://coveralls.io/builds/18878761/badge)](https://coveralls.io/builds/18878761)

Coverage decreased (-0.1%) to 97.978% when pulling **d83d655bb7a543605130d953ef9fa7116c285fff on function_improvements** into **a5e08d172af0cf7e263c95456ee8699c3a2e3183 on master**.


Patch:
diff --git a/pypika/enums.py b/pypika/enums.py
index 493f6e1..fa90086 100644
--- a/pypika/enums.py
+++ b/pypika/enums.py
@@ -73,14 +73,46 @@ class DatePart(Enum):
     microsecond = 'MICROSECOND'
 
 
-class SqlTypes(Enum):
+class SqlType:
+    def __init__(self, name):
+        self.name = name
+
+    def __call__(self, length):
+        return SqlTypeLength(self.name, length)
+
+    def get_sql(self, **kwargs):
+        return '{name}'.format(name=self.name)
+
+
+class SqlTypeLength:
+    def __init__(self, name, length):
+        self.name = name
+        self.length = length
+
+    def get_sql(self, **kwargs):
+        return '{name}({length})'.format(name=self.name,
+                                         length=self.length)
+
+
+class SqlTypes:
+    BOOLEAN = 'BOOLEAN'
+    INTEGER = 'INTEGER'
+    FLOAT = 'FLOAT'
+    NUMERIC = 'NUMERIC'
     SIGNED = 'SIGNED'
     UNSIGNED = 'UNSIGNED'
-    utf8 = 'utf8'
+
     DATE = 'DATE'
+    TIME = 'TIME'
     TIMESTAMP = 'TIMESTAMP'
-    CHAR = 'CHAR'
-    VARCHAR = 'VARCHAR'
+
+    CHAR = SqlType('CHAR')
+    VARCHAR = SqlType('VARCHAR')
+    LONG_VARCHAR = SqlType('LONG VARCHAR')
+    BINARY = SqlType('BINARY')
+    VARBINARY = SqlType('VARBINARY')
+    LONG_VARBINARY = SqlType('LONG VARBINARY')
+
 
 
 class Dialects(Enum):
diff --git a/pypika/functions.py b/pypika/functions.py
index 80ef101..948f839 100644
--- a/pypika/functions.py
+++ b/pypika/functions.py
@@ -16,16 +16,18 @@ __author__ = "Timothy Heys"
 __email__ = "theys@kayak.com"
 
 
-class Count(AggregateFunction):
-    def __init__(self, param, alias=None):
-        is_star = isinstance(param, str) and '*' == param
-        super(Count, self).__init__('COUNT', Star() if is_star else param, alias=alias)
+class DistinctOptionFunction(AggregateFunction):
+    def __init__(self, name, *args, **kwargs):
+        alias = kwargs.get('alias')
+        super(DistinctOptionFunction, self).__init__(name, *args, alias=alias)
         self._distinct = False
 
     def get_function_sql(self, **kwargs):
-        s = super(Count, self).get_function_sql(**kwargs)
+        s = super(DistinctOptionFunction, self).get_function_sql(**kwargs)
+
+        n = len(self.name) + 1
         if self._distinct:
-            return s[:6] + 'DISTINCT ' + s[6:]
+            return s[:n] + 'DISTINCT ' + s[n:]
         return s
 
     @builder
@@ -33,8 +35,14 @@ class Count(AggregateFunction):
         self._distinct = True
 
 
+class Count(DistinctOptionFunction):
+    def __init__(self, param, alias=None):
+        is_star = isinstance(param, str) and '*' == param
+        super(Count, self).__init__('COUNT', Star() if is_star else param, alias=alias)
+
+
 # Arithmetic Functions
-class Sum(AggregateFunction):
+class Sum(DistinctOptionFunction):
     def __init__(self, term, alias=None):
         super(Sum, self).__init__('SUM', term, alias=alias)
 
@@ -76,7 +84,11 @@ class Cast(Function):
         self.as_type = as_type
 
     def get_special_params_sql(self, **kwargs):
-        return 'AS {type}'.format(type=self.as_type.value)
+        type_sql = self.as_type.get_sql(**kwargs) \
+            if hasattr(self.as_type, 'get_sql') \
+            else str(self.as_type).upper()
+
+        return 'AS {type}'.format(type=type_sql)
 
 
 class Convert(Function):
diff --git a/pypika/terms.py b/pypika/terms.py
index 921ccbf..dab009a 100644
--- a/pypika/terms.py
+++ b/pypika/terms.py
@@ -144,6 +144,12 @@ class Term(object):
     def __invert__(self):
         return Not(self)
 
+    def __pos__(self):
+        return self
+
+    def __neg__(self):
+        return Negative(self)
+
     def __add__(self, other):
         return ArithmeticExpression(Arithmetic.add, self, self._wrap(other))
 
@@ -215,6 +221,15 @@ class Term(object):
         raise NotImplementedError()
 
 
+class Negative(Term):
+    def __init__(self, term):
+        super(Negative, self).__init__()
+        self.term = term
+
+    def get_sql(self, **kwargs):
+        return '-{term}'.format(term=self.term.get_sql(**kwargs))
+
+
 class ValueWrapper(Term):
     is_aggregate = None
 
@@ -246,7 +261,6 @@ class ValueWrapper(Term):
             return 'null'
         return str(self.value)
 
-
 class Values(Term):
     def __init__(self, field,):
         super(Values, self).__init__(None)
@@ -396,10 +410,6 @@ class BasicCriterion(Criterion):
         self.left = left
         self.right = right
 
-    @property
-    def is_aggregate(self):
-        return resolve_is_aggregate([term.is_aggregate for term in [self.left, self.right]])
-
     @property
     def tables_(self):
         return self.left.tables_ | self.right.tables_


Test patch:
diff --git a/pypika/tests/test_aggregate.py b/pypika/tests/test_aggregate.py
index 6420a74..1f567cb 100644
--- a/pypika/tests/test_aggregate.py
+++ b/pypika/tests/test_aggregate.py
@@ -1,11 +1,7 @@
 # coding: utf-8
 import unittest
 
-from pypika import (
-    Case,
-    Field,
-    functions as fn,
-)
+from pypika import Case , Field, functions as fn
 from pypika.terms import ValueWrapper
 
 
@@ -77,26 +73,10 @@ class IsAggregateTests(unittest.TestCase):
 
         self.assertTrue(v.is_aggregate)
 
-    def test__case_with_field_is_not_aggregate(self):
+    def test__case_all_constants_is_aggregate_none(self):
         v = Case() \
             .when(Field('foo') == 1, 1) \
             .when(Field('foo') == 2, 2) \
             .else_(3)
 
-        self.assertFalse(v.is_aggregate)
-
-    def test__case_with_single_aggregate_field_is_not_aggregate(self):
-        v = Case() \
-            .when(Field('foo') == 1, 1) \
-            .when(fn.Sum(Field('foo')) == 2, 2) \
-            .else_(3)
-
-        self.assertFalse(v.is_aggregate)
-
-    def test__case_all_constants_is_aggregate_none(self):
-        v = Case() \
-            .when(True, 1) \
-            .when(False, 2) \
-            .else_(3)
-
         self.assertIsNone(v.is_aggregate)
diff --git a/pypika/tests/test_data_types.py b/pypika/tests/test_data_types.py
new file mode 100644
index 0000000..67e761a
--- /dev/null
+++ b/pypika/tests/test_data_types.py
@@ -0,0 +1,8 @@
+import unittest
+
+from pypika.terms import ValueWrapper
+
+
+class StringTests(unittest.TestCase):
+    def test_inline_string_concatentation(self):
+        self.assertEqual("'it''s'", ValueWrapper("it's").get_sql())
diff --git a/pypika/tests/test_functions.py b/pypika/tests/test_functions.py
index 7e4a84b..8d5fa00 100644
--- a/pypika/tests/test_functions.py
+++ b/pypika/tests/test_functions.py
@@ -7,11 +7,11 @@ from pypika import (
     DatePart,
     Field as F,
     Interval,
+    Query,
     Query as Q,
     Table as T,
     VerticaQuery,
     functions as fn,
-    Query,
 )
 from pypika.enums import (
     Dialects,
@@ -410,11 +410,6 @@ class CastTests(unittest.TestCase):
         self.assertEqual("SELECT CAST(\"foo\" AS UNSIGNED) FROM \"abc\"", str(q1))
         self.assertEqual("SELECT CAST(\"foo\" AS UNSIGNED) FROM \"abc\"", str(q2))
 
-    def test__convert__utf8(self):
-        q = Q.from_(self.t).select(fn.Convert(self.t.foo, SqlTypes.utf8))
-
-        self.assertEqual("SELECT CONVERT(\"foo\" USING utf8) FROM \"abc\"", str(q))
-
     def test__cast__date(self):
         q1 = Q.from_(self.t).select(fn.Date(self.t.foo))
         q2 = Q.from_(self.t).select(fn.Cast(self.t.foo, SqlTypes.DATE))
@@ -429,11 +424,86 @@ class CastTests(unittest.TestCase):
         self.assertEqual("SELECT TIMESTAMP(\"foo\") FROM \"abc\"", str(q1))
         self.assertEqual("SELECT CAST(\"foo\" AS TIMESTAMP) FROM \"abc\"", str(q2))
 
+    def test__cast__char(self):
+        q = Q.from_(self.t).select(fn.Cast(self.t.foo, SqlTypes.CHAR))
+
+        self.assertEqual("SELECT CAST(\"foo\" AS CHAR) FROM \"abc\"", str(q))
+
+    def test__cast__char_with_arg(self):
+        q = Q.from_(self.t).select(fn.Cast(self.t.foo, SqlTypes.VARCHAR(24)))
+
+        self.assertEqual("SELECT CAST(\"foo\" AS VARCHAR(24)) FROM \"abc\"", str(q))
+
     def test__cast__varchar(self):
         q = Q.from_(self.t).select(fn.Cast(self.t.foo, SqlTypes.VARCHAR))
 
         self.assertEqual("SELECT CAST(\"foo\" AS VARCHAR) FROM \"abc\"", str(q))
 
+    def test__cast__varchar_with_arg(self):
+        q = Q.from_(self.t).select(fn.Cast(self.t.foo, SqlTypes.VARCHAR(24)))
+
+        self.assertEqual("SELECT CAST(\"foo\" AS VARCHAR(24)) FROM \"abc\"", str(q))
+
+    def test__cast__long_varchar(self):
+        q = Q.from_(self.t).select(fn.Cast(self.t.foo, SqlTypes.LONG_VARCHAR))
+
+        self.assertEqual("SELECT CAST(\"foo\" AS LONG VARCHAR) FROM \"abc\"", str(q))
+
+    def test__cast__long_varchar_with_arg(self):
+        q = Q.from_(self.t).select(fn.Cast(self.t.foo, SqlTypes.LONG_VARCHAR(24)))
+
+        self.assertEqual("SELECT CAST(\"foo\" AS LONG VARCHAR(24)) FROM \"abc\"", str(q))
+
+    def test__cast__binary(self):
+        q = Q.from_(self.t).select(fn.Cast(self.t.foo, SqlTypes.BINARY))
+
+        self.assertEqual("SELECT CAST(\"foo\" AS BINARY) FROM \"abc\"", str(q))
+
+    def test__cast__binary_with_arg(self):
+        q = Q.from_(self.t).select(fn.Cast(self.t.foo, SqlTypes.BINARY(24)))
+
+        self.assertEqual("SELECT CAST(\"foo\" AS BINARY(24)) FROM \"abc\"", str(q))
+
+    def test__cast__varbinary(self):
+        q = Q.from_(self.t).select(fn.Cast(self.t.foo, SqlTypes.VARBINARY))
+
+        self.assertEqual("SELECT CAST(\"foo\" AS VARBINARY) FROM \"abc\"", str(q))
+
+    def test__cast__varbinary_with_arg(self):
+        q = Q.from_(self.t).select(fn.Cast(self.t.foo, SqlTypes.VARBINARY(24)))
+
+        self.assertEqual("SELECT CAST(\"foo\" AS VARBINARY(24)) FROM \"abc\"", str(q))
+
+    def test__cast__long_varbinary(self):
+        q = Q.from_(self.t).select(fn.Cast(self.t.foo, SqlTypes.LONG_VARBINARY))
+
+        self.assertEqual("SELECT CAST(\"foo\" AS LONG VARBINARY) FROM \"abc\"", str(q))
+
+    def test__cast__long_varbinary_with_arg(self):
+        q = Q.from_(self.t).select(fn.Cast(self.t.foo, SqlTypes.LONG_VARBINARY(24)))
+
+        self.assertEqual("SELECT CAST(\"foo\" AS LONG VARBINARY(24)) FROM \"abc\"", str(q))
+
+    def test__cast__boolean(self):
+        q = Q.from_(self.t).select(fn.Cast(self.t.foo, SqlTypes.BOOLEAN))
+
+        self.assertEqual("SELECT CAST(\"foo\" AS BOOLEAN) FROM \"abc\"", str(q))
+
+    def test__cast__integer(self):
+        q = Q.from_(self.t).select(fn.Cast(self.t.foo, SqlTypes.INTEGER))
+
+        self.assertEqual("SELECT CAST(\"foo\" AS INTEGER) FROM \"abc\"", str(q))
+
+    def test__cast__float(self):
+        q = Q.from_(self.t).select(fn.Cast(self.t.foo, SqlTypes.FLOAT))
+
+        self.assertEqual("SELECT CAST(\"foo\" AS FLOAT) FROM \"abc\"", str(q))
+
+    def test__cast__numeric(self):
+        q = Q.from_(self.t).select(fn.Cast(self.t.foo, SqlTypes.NUMERIC))
+
+        self.assertEqual("SELECT CAST(\"foo\" AS NUMERIC) FROM \"abc\"", str(q))
+
     def test__tochar__(self):
         q = Q.from_(self.t).select(fn.ToChar(self.t.foo, "SomeFormat"))
 
diff --git a/pypika/tests/test_negation.py b/pypika/tests/test_negation.py
new file mode 100644
index 0000000..742bb4d
--- /dev/null
+++ b/pypika/tests/test_negation.py
@@ -0,0 +1,31 @@
+import unittest
+
+from pypika import (
+    Tables,
+    functions as fn,
+)
+from pypika.terms import ValueWrapper
+
+
+class NegationTests(unittest.TestCase):
+    table_abc, table_efg = Tables('abc', 'efg')
+
+    def test_negate_wrapped_float(self):
+        q = -ValueWrapper(1.0)
+
+        self.assertEqual('-1.0', q.get_sql())
+
+    def test_negate_wrapped_int(self):
+        q = -ValueWrapper(1)
+
+        self.assertEqual('-1', q.get_sql())
+
+    def test_negate_field(self):
+        q = -self.table_abc.foo
+
+        self.assertEqual('-"abc"."foo"', q.get_sql(with_namespace=True, quote_char='"'))
+
+    def test_negate_function(self):
+        q = -fn.Sum(self.table_abc.foo)
+
+        self.assertEqual('-SUM("abc"."foo")', q.get_sql(with_namespace=True, quote_char='"'))
diff --git a/pypika/tests/test_selects.py b/pypika/tests/test_selects.py
index a51dcea..be305bb 100644
--- a/pypika/tests/test_selects.py
+++ b/pypika/tests/test_selects.py
@@ -326,6 +326,11 @@ class GroupByTests(unittest.TestCase):
 
         self.assertEqual('SELECT "foo",COUNT(DISTINCT *) FROM "abc" GROUP BY "foo"', str(q))
 
+    def test_groupby__sum_distinct(self):
+        q = Query.from_(self.t).groupby(self.t.foo).select(self.t.foo, fn.Sum(self.t.bar).distinct())
+
+        self.assertEqual('SELECT "foo",SUM(DISTINCT "bar") FROM "abc" GROUP BY "foo"', str(q))
+
     def test_groupby__str(self):
         q = Query.from_('abc').groupby('foo').select('foo', fn.Count('*').distinct())