Repository: kayak/pypika
Pull request #62 (2017-07-04 12:59:17)

Added negate criterion
Added a `NegateCriterion` so each criterion `foo=bar` can be inverted to `NOT foo=bar` by calling `criterion.as_negated()`.

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

Coverage decreased (-0.03%) to 98.523% when pulling **eefbbe39a4289c2bfc7812d76b7c63c597ff4bfa on add-negate-criterion** into **c660393b2a34c25ff3a79e7b000413c2d8845816 on master**.

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

Coverage decreased (-0.1%) to 98.456% when pulling **a07e3ae79a24fa5f45e79803a393934578a09795 on add-negate-criterion** into **c660393b2a34c25ff3a79e7b000413c2d8845816 on master**.

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

Coverage decreased (-0.1%) to 98.458% when pulling **1aff04124db22a24f7d5a2cc861db8815556a2c6 on add-negate-criterion** into **c660393b2a34c25ff3a79e7b000413c2d8845816 on master**.


Patch:
diff --git a/pypika/terms.py b/pypika/terms.py
index 1cea8cd..477b947 100644
--- a/pypika/terms.py
+++ b/pypika/terms.py
@@ -1,23 +1,63 @@
 # coding: utf8
+import copy
 import re
 from datetime import date
+from functools import partial
 
 from aenum import Enum
 
-from pypika.enums import (Boolean,
-                          Dialects,
-                          Equality,
-                          Arithmetic,
-                          Matching)
-from pypika.utils import (CaseException,
-                          builder,
-                          resolve_is_aggregate,
-                          alias_sql)
+from pypika.enums import (
+    Boolean,
+    Dialects,
+    Equality,
+    Arithmetic,
+    Matching,
+)
+from pypika.utils import (
+    CaseException,
+    builder,
+    resolve_is_aggregate,
+    alias_sql,
+)
+import inspect
 
 __author__ = "Timothy Heys"
 __email__ = "theys@kayak.com"
 
 
+class Not(object):
+    def __init__(self, term):
+        self.term = term
+
+    def fields(self):
+        return self.term.fields() if self.term.fields else []
+
+    def get_sql(self, **kwargs):
+        return "NOT {term}".format(term=self.term.get_sql(**kwargs))
+
+    def __str__(self):
+        return self.get_sql(quote_char='"')
+
+    def __getattr__(self, item):
+        """
+        Delegate method calls to the class wrapped by Not().
+        Re-wrap methods on child classes of Term (e.g. isin, eg...) to retain 'NOT <term>' output.
+        """
+
+        item_func = getattr(self.term, item)
+
+        if not inspect.ismethod(item_func):
+            return item_func
+
+        def inner(inner_self, *args, **kwargs):
+            result = item_func(inner_self, *args, **kwargs)
+            if isinstance(result, (Term, )):
+                return Not(result)
+            return result
+
+        return inner
+
+
 class Term(object):
     is_aggregate = False
 
@@ -104,6 +144,9 @@ class Term(object):
     def bin_regex(self, pattern):
         return BasicCriterion(Matching.bin_regex, self, self._wrap(pattern))
 
+    def negate(self):
+        return Not(self)
+
     def __add__(self, other):
         return ArithmeticExpression(Arithmetic.add, self, self._wrap(other))
 
@@ -283,10 +326,6 @@ class Tuple(Term):
 
 
 class Criterion(Term):
-    def __init__(self, alias=None):
-        super(Criterion, self).__init__(alias)
-        self._is_negated = False
-
     def __and__(self, other):
         return ComplexCriterion(Boolean.and_, self, other)
 
@@ -296,10 +335,6 @@ class Criterion(Term):
     def __xor__(self, other):
         return ComplexCriterion(Boolean.xor_, self, other)
 
-    @builder
-    def negate(self):
-        self._is_negated = True
-
     def fields(self):
         raise NotImplementedError()
 
@@ -366,6 +401,7 @@ class ContainsCriterion(Criterion):
         super(ContainsCriterion, self).__init__(alias)
         self.term = term
         self.container = container
+        self._is_negated = False
 
     def fields(self):
         return self.term.fields() if self.term.fields else []
@@ -378,6 +414,10 @@ class ContainsCriterion(Criterion):
             not_='NOT ' if self._is_negated else ''
         )
 
+    def negate(self):
+        self._is_negated = True
+        return self
+
 
 class BetweenCriterion(Criterion):
     def __init__(self, term, start, end, alias=None):
@@ -420,9 +460,8 @@ class NullCriterion(Criterion):
         self.term = self.term.for_(table)
 
     def get_sql(self, **kwargs):
-        return "{term} IS{not_} NULL".format(
+        return "{term} IS NULL".format(
             term=self.term.get_sql(**kwargs),
-            not_=' NOT' if self._is_negated else ''
         )
 
     def fields(self):
@@ -600,7 +639,7 @@ class Function(Term):
             A copy of the field with it's table value replaced.
         """
         self.args = [param.for_(table) if hasattr(param, 'for_')
-                       else param
+                     else param
                      for param in self.args]
 
     def get_special_params_sql(self, **kwargs):
@@ -612,8 +651,8 @@ class Function(Term):
         return '{name}({args}{special})'.format(
             name=self.name,
             args=','.join(p.get_sql(with_alias=False, **kwargs)
-                            if hasattr(p, 'get_sql')
-                            else str(p)
+                          if hasattr(p, 'get_sql')
+                          else str(p)
                           for p in self.args),
             special=(' ' + special_params_sql) if special_params_sql else '',
         )


Test patch:
diff --git a/pypika/tests/test_criterions.py b/pypika/tests/test_criterions.py
index bfc3cf8..870f11d 100644
--- a/pypika/tests/test_criterions.py
+++ b/pypika/tests/test_criterions.py
@@ -80,13 +80,6 @@ class CriterionTests(unittest.TestCase):
         self.assertEqual('"foo" IS NULL', str(c1))
         self.assertEqual('"crit"."foo" IS NULL', str(c2))
 
-    def test__criterion_is_not_null(self):
-        c1 = Field('foo').notnull()
-        c2 = Field('foo', table=self.t).notnull()
-
-        self.assertEqual('"foo" IS NOT NULL', str(c1))
-        self.assertEqual('"crit"."foo" IS NOT NULL', str(c2))
-
     def test__criterion_ne_number(self):
         c1 = Field('foo') != 1
         c2 = Field('foo', table=self.t).ne(0)
@@ -245,6 +238,42 @@ class CriterionTests(unittest.TestCase):
         self.assertEqual('"crit"."foo">=-1', str(c2))
 
 
+class NotTests(unittest.TestCase):
+    table_abc = Table('abc', alias='cx0')
+
+    def test_negate(self):
+        c1 = Field('foo') >= 1
+        c2 = c1.negate()
+
+        self.assertEqual('"foo">=1', str(c1))
+        self.assertEqual('NOT "foo">=1', str(c2))
+
+    def test_variable_access(self):
+        c1 = Field('foo').negate()
+
+        self.assertEqual(c1.is_aggregate, False)
+
+    def test_chained_function(self):
+        field1 = Field('foo').negate()
+        field2 = field1.eq('bar')
+
+        self.assertEqual('NOT "foo"', str(field1))
+        self.assertEqual('NOT "foo"=\'bar\'', str(field2))
+        self.assertIsNot(field1, field2)
+
+    def test_not_null(self):
+        c1 = Field('foo').notnull()
+        c2 = Field('foo', table=self.table_abc).notnull()
+
+        self.assertEqual('NOT "foo" IS NULL', str(c1))
+        self.assertEqual('NOT "cx0"."foo" IS NULL', str(c2))
+
+    def test_notnullcriterion_for_table(self):
+        f = self.table_abc.foo.notnull().for_(self.table_abc)
+
+        self.assertEqual('NOT "cx0"."foo" IS NULL', str(f))
+
+
 class BetweenTests(unittest.TestCase):
     t = Table('abc', alias='btw')
 
@@ -459,8 +488,3 @@ class CriterionOperationsTests(unittest.TestCase):
         f = self.table_abc.foo.isnull().for_(self.table_efg)
 
         self.assertEqual('"cx1"."foo" IS NULL', str(f))
-
-    def test_notnullcriterion_for_table(self):
-        f = self.table_abc.foo.notnull().for_(self.table_efg)
-
-        self.assertEqual('"cx1"."foo" IS NOT NULL', str(f))