Repository: kayak/pypika
Pull request #50 (2017-06-08 14:45:48)

Added dialects for intervals and specific DB Query classes


Patch:
diff --git a/pypika/__init__.py b/pypika/__init__.py
index 3cffaf5..506f8f6 100644
--- a/pypika/__init__.py
+++ b/pypika/__init__.py
@@ -31,10 +31,27 @@ pypika.utils
 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, Tuple
-from .utils import JoinException, GroupingException, CaseException, UnionException, RollupException
+from .enums import (Order,
+                    JoinType,
+                    DatePart)
+from .queries import (Query,
+                      MySQLQuery,
+                      MSSQLQuery,
+                      OracleQuery,
+                      PostgreSQLQuery,
+                      VerticaQuery,
+                      Table,
+                      make_tables as Tables,
+                      Tuple)
+from .terms import (Field,
+                    Case,
+                    Interval,
+                    Rollup)
+from .utils import (JoinException,
+                    GroupingException,
+                    CaseException,
+                    UnionException,
+                    RollupException)
 
 __author__ = "Timothy Heys"
 __email__ = "theys@kayak.com"
diff --git a/pypika/enums.py b/pypika/enums.py
index a32b4bd..3945289 100644
--- a/pypika/enums.py
+++ b/pypika/enums.py
@@ -73,3 +73,11 @@ class SqlTypes(Enum):
     DATE = 'DATE'
     TIMESTAMP = 'TIMESTAMP'
     VARCHAR = 'VARCHAR'
+
+
+class Dialects(Enum):
+    VERTICA = 'vertica'
+    ORACLE = 'oracle'
+    MSSQL = 'mssql'
+    MYSQL = 'mysql'
+    POSTGRESQL = 'postgressql'
diff --git a/pypika/queries.py b/pypika/queries.py
index 3ba2edc..13b8b81 100644
--- a/pypika/queries.py
+++ b/pypika/queries.py
@@ -1,8 +1,20 @@
 # coding: utf8
 
-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, Tuple
+from pypika.enums import (Dialects,
+                          JoinType,
+                          UnionType)
+from pypika.utils import (JoinException,
+                          UnionException,
+                          RollupException,
+                          builder,
+                          alias_sql)
+from .terms import (Field,
+                    Star,
+                    Term,
+                    Function,
+                    ArithmeticExpression,
+                    Rollup,
+                    Tuple)
 
 __author__ = "Timothy Heys"
 __email__ = "theys@kayak.com"
@@ -26,9 +38,6 @@ class Selectable(object):
 
         return self.field(name)
 
-        # def __hash__(self):
-        #     return hash(str(self))
-
 
 class Table(Selectable):
     def __init__(self, name, schema=None, alias=None):
@@ -43,13 +52,13 @@ class Table(Selectable):
             table_sql = "{quote}{schema}{quote}.{quote}{name}{quote}".format(
                 schema=self.schema,
                 name=self.table_name,
-                quote=quote_char or '',
+                quote=quote_char or ''
             )
 
         else:
             table_sql = "{quote}{name}{quote}".format(
                 name=self.table_name,
-                quote=quote_char or '',
+                quote=quote_char or ''
             )
 
         if self.alias is None:
@@ -92,9 +101,11 @@ class Query(object):
 
     This class is immutable.
     """
+    QUOTE_CHARACTER = '"'
+    DIALECT = None
 
-    @staticmethod
-    def from_(table):
+    @classmethod
+    def from_(cls, table):
         """
         Query builder entry point.  Initializes query building and sets the table to select from.  When using this
         function, the query becomes a SELECT query.
@@ -106,10 +117,10 @@ class Query(object):
 
         :returns QueryBuilder
         """
-        return QueryBuilder().from_(table)
+        return QueryBuilder(quote_char=cls.QUOTE_CHARACTER, dialect=cls.DIALECT).from_(table)
 
-    @staticmethod
-    def into(table):
+    @classmethod
+    def into(cls, table):
         """
         Query builder entry point.  Initializes query building and sets the table to insert into.  When using this
         function, the query becomes an INSERT query.
@@ -121,10 +132,10 @@ class Query(object):
 
         :returns QueryBuilder
         """
-        return QueryBuilder().into(table)
+        return QueryBuilder(quote_char=cls.QUOTE_CHARACTER, dialect=cls.DIALECT).into(table)
 
-    @staticmethod
-    def select(*terms):
+    @classmethod
+    def select(cls, *terms):
         """
         Query builder entry point.  Initializes query building without a table and selects fields.  Useful when testing
         SQL functions.
@@ -137,7 +148,47 @@ class Query(object):
 
         :returns QueryBuilder
         """
-        return QueryBuilder().select(*terms)
+        return QueryBuilder(quote_char=cls.QUOTE_CHARACTER, dialect=cls.DIALECT).select(*terms)
+
+
+class MySQLQuery(Query):
+    """
+    Defines a query class for use with MySQL.
+    """
+    QUOTE_CHARACTER = "`"
+    DIALECT = Dialects.MYSQL
+
+
+class VerticaQuery(Query):
+    """
+    Defines a query class for use with Vertica.
+    """
+    QUOTE_CHARACTER = '"'
+    DIALECT = Dialects.VERTICA
+
+
+class OracleQuery(Query):
+    """
+    Defines a query class for use with Oracle.
+    """
+    QUOTE_CHARACTER = '"'
+    DIALECT = Dialects.ORACLE
+
+
+class PostgreSQLQuery(Query):
+    """
+    Defines a query class for use with PostgreSQL.
+    """
+    QUOTE_CHARACTER = '"'
+    DIALECT = Dialects.POSTGRESQL
+
+
+class MSSQLQuery(Query):
+    """
+    Defines a query class for use with Microsoft SQL Server.
+    """
+    QUOTE_CHARACTER = '"'
+    DIALECT = Dialects.MSSQL
 
 
 class QueryBuilder(Selectable, Term):
@@ -146,7 +197,7 @@ class QueryBuilder(Selectable, Term):
     state to be branched immutably.
     """
 
-    def __init__(self):
+    def __init__(self, quote_char='"', dialect=None):
         super(QueryBuilder, self).__init__(None)
 
         self._from = []
@@ -174,6 +225,8 @@ class QueryBuilder(Selectable, Term):
         self._select_into = False
 
         self._subquery_count = 0
+        self.quote_char = quote_char
+        self.dialect = dialect
 
     @builder
     def from_(self, selectable):
@@ -420,7 +473,7 @@ class QueryBuilder(Selectable, Term):
         self._subquery_count += 1
 
     def __str__(self):
-        return self.get_sql(with_unions=True, quote_char='"')
+        return self.get_sql(with_unions=True, quote_char=self.quote_char, dialect=self.dialect)
 
     def __eq__(self, other):
         if not isinstance(other, QueryBuilder):
@@ -502,7 +555,7 @@ class QueryBuilder(Selectable, Term):
 
     def _select_sql(self, **kwargs):
         return 'SELECT {distinct}{select}'.format(
-            distinct='distinct ' if self._distinct else '',
+            distinct='DISTINCT ' if self._distinct else '',
             select=','.join(term.get_sql(with_alias=True, **kwargs)
                             for term in self._selects),
         )
diff --git a/pypika/terms.py b/pypika/terms.py
index ff4d10a..30f5fed 100644
--- a/pypika/terms.py
+++ b/pypika/terms.py
@@ -4,8 +4,15 @@ from datetime import date
 
 from aenum import Enum
 
-from pypika.enums import Boolean, 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)
 
 __author__ = "Timothy Heys"
 __email__ = "theys@kayak.com"
@@ -42,7 +49,7 @@ class Term(object):
         """
         from .queries import QueryBuilder
 
-        if isinstance(val, (Term, QueryBuilder)):
+        if isinstance(val, (Term, QueryBuilder, Interval)):
             return val
         if val is None:
             return NullValue()
@@ -570,7 +577,6 @@ class Case(Term):
 
         return alias_sql(case_sql, self.alias, kwargs.get('quote_char'))
 
-
     def fields(self):
         fields = []
 
@@ -674,7 +680,12 @@ class Interval(object):
     def __str__(self):
         return self.get_sql()
 
+    def fields(self):
+        return []
+
     def get_sql(self, **kwargs):
+        dialect = kwargs.get('dialect')
+
         if hasattr(self, 'quarters'):
             expr = getattr(self, 'quarters')
             unit = 'QUARTER'
@@ -684,7 +695,7 @@ class Interval(object):
             unit = 'WEEK'
 
         else:
-            # Create the whole expression but trim out the unnecessery fields
+            # Create the whole expression but trim out the unnecessary fields
             expr = self.trim_pattern.sub(
                 '',
                 "{years}-{months}-{days} {hours}:{minutes}:{seconds}.{microseconds}".format(
@@ -701,10 +712,16 @@ class Interval(object):
                 largest=self.largest,
                 smallest=self.smallest,
             ) if self.largest != self.smallest else self.largest
-        return 'INTERVAL \'{expr} {unit}\''.format(
-            expr=expr,
-            unit=unit,
-        )
+
+        interval_templates = {
+            # MySQL requires no single quotes around the expr and unit
+            Dialects.MYSQL: 'INTERVAL {expr} {unit}',
+
+            # Oracle requires just single quotes around the expr
+            Dialects.ORACLE: 'INTERVAL \'{expr}\' {unit}'
+        }
+
+        return interval_templates.get(dialect, 'INTERVAL \'{expr} {unit}\'').format(expr=expr, unit=unit)
 
 
 class Pow(Function):


Test patch:
diff --git a/pypika/tests/test_functions.py b/pypika/tests/test_functions.py
index df964b3..e46cc7b 100644
--- a/pypika/tests/test_functions.py
+++ b/pypika/tests/test_functions.py
@@ -1,8 +1,16 @@
 # coding: utf8
 import unittest
 
-from pypika import Query as Q, Table as T, Field as F, functions as fn, CaseException, Case, Interval, DatePart
-from pypika.enums import SqlTypes
+from pypika import (Query as Q,
+                    Table as T,
+                    Field as F,
+                    functions as fn,
+                    CaseException,
+                    Case,
+                    Interval,
+                    DatePart)
+from pypika.enums import (SqlTypes,
+                          Dialects)
 
 __author__ = "Timothy Heys"
 __email__ = "theys@kayak.com"
@@ -511,6 +519,18 @@ class DateFunctionsTests(unittest.TestCase):
 
         self.assertEqual("\"dt\"+INTERVAL \'1 QUARTER\'+INTERVAL \'1 WEEK\'", str(c))
 
+    def test_mysql_dialect_does_not_use_quotes_around_interval(self):
+        c = Interval(days=1).get_sql(dialect=Dialects.MYSQL)
+        self.assertEqual("INTERVAL 1 DAY", str(c))
+
+    def test_oracle_dialect_uses_single_quotes_around_expression_in_an_interval(self):
+        c = Interval(days=1).get_sql(dialect=Dialects.ORACLE)
+        self.assertEqual("INTERVAL '1' DAY", str(c))
+
+    def test_vertica_dialect_uses_single_quotes_around_interval(self):
+        c = Interval(days=1).get_sql(dialect=Dialects.VERTICA)
+        self.assertEqual("INTERVAL '1 DAY'", str(c))
+
     def _test_extract_datepart(self, date_part):
         q = Q.from_(self.t).select(fn.Extract(date_part, self.t.foo))
 
diff --git a/pypika/tests/test_selects.py b/pypika/tests/test_selects.py
index 62cdc6b..d25a3de 100644
--- a/pypika/tests/test_selects.py
+++ b/pypika/tests/test_selects.py
@@ -1,7 +1,18 @@
 # coding: utf8
 import unittest
 
-from pypika import Query, Table, Tables, Field as F, Case, functions as fn, Order
+from pypika import (Query,
+                    Table,
+                    Tables,
+                    Field as F,
+                    Case,
+                    functions as fn,
+                    Order,
+                    MySQLQuery,
+                    VerticaQuery,
+                    MSSQLQuery,
+                    PostgreSQLQuery,
+                    OracleQuery)
 
 __author__ = "Timothy Heys"
 __email__ = "theys@kayak.com"
@@ -33,12 +44,12 @@ class SelectTests(unittest.TestCase):
     def test_select__distinct__single(self):
         q = Query.from_('abc').select('foo').distinct()
 
-        self.assertEqual('SELECT distinct "foo" FROM "abc"', str(q))
+        self.assertEqual('SELECT DISTINCT "foo" FROM "abc"', str(q))
 
     def test_select__distinct__multi(self):
         q = Query.from_('abc').select('foo', 'bar').distinct()
 
-        self.assertEqual('SELECT distinct "foo","bar" FROM "abc"', str(q))
+        self.assertEqual('SELECT DISTINCT "foo","bar" FROM "abc"', str(q))
 
     def test_select__column__single__str(self):
         q = Query.from_('abc').select('foo')
@@ -120,6 +131,31 @@ class SelectTests(unittest.TestCase):
 
         self.assertEqual('SELECT "foo" FROM "abc" OFFSET 10 LIMIT 10', str(q1))
 
+    def test_mysql_query_uses_backtick_quote_chars(self):
+        q = MySQLQuery.from_('abc').select('foo', 'bar')
+
+        self.assertEqual('SELECT `foo`,`bar` FROM `abc`', str(q))
+
+    def test_vertica_query_uses_double_quote_chars(self):
+        q = VerticaQuery.from_('abc').select('foo', 'bar')
+
+        self.assertEqual('SELECT "foo","bar" FROM "abc"', str(q))
+
+    def test_mssql_query_uses_double_quote_chars(self):
+        q = MSSQLQuery.from_('abc').select('foo', 'bar')
+
+        self.assertEqual('SELECT "foo","bar" FROM "abc"', str(q))
+
+    def test_oracle_query_uses_double_quote_chars(self):
+        q = OracleQuery.from_('abc').select('foo', 'bar')
+
+        self.assertEqual('SELECT "foo","bar" FROM "abc"', str(q))
+
+    def test_postgres_query_uses_double_quote_chars(self):
+        q = PostgreSQLQuery.from_('abc').select('foo', 'bar')
+
+        self.assertEqual('SELECT "foo","bar" FROM "abc"', str(q))
+
 
 class WhereTests(unittest.TestCase):
     t = Table('abc')
@@ -219,6 +255,31 @@ class GroupByTests(unittest.TestCase):
 
         self.assertEqual('SELECT SUM("foo") "bar" FROM "abc" GROUP BY SUM("foo")', str(q))
 
+    def test_mysql_query_uses_backtick_quote_chars(self):
+        q = MySQLQuery.from_(self.t).groupby(self.t.foo).select(self.t.foo)
+
+        self.assertEqual('SELECT `foo` FROM `abc` GROUP BY `foo`', str(q))
+
+    def test_vertica_query_uses_double_quote_chars(self):
+        q = VerticaQuery.from_(self.t).groupby(self.t.foo).select(self.t.foo)
+
+        self.assertEqual('SELECT "foo" FROM "abc" GROUP BY "foo"', str(q))
+
+    def test_mssql_query_uses_double_quote_chars(self):
+        q = MSSQLQuery.from_(self.t).groupby(self.t.foo).select(self.t.foo)
+
+        self.assertEqual('SELECT "foo" FROM "abc" GROUP BY "foo"', str(q))
+
+    def test_oracle_query_uses_double_quote_chars(self):
+        q = OracleQuery.from_(self.t).groupby(self.t.foo).select(self.t.foo)
+
+        self.assertEqual('SELECT "foo" FROM "abc" GROUP BY "foo"', str(q))
+
+    def test_postgres_query_uses_double_quote_chars(self):
+        q = PostgreSQLQuery.from_(self.t).groupby(self.t.foo).select(self.t.foo)
+
+        self.assertEqual('SELECT "foo" FROM "abc" GROUP BY "foo"', str(q))
+
 
 class HavingTests(unittest.TestCase):
     table_abc, table_efg = Tables('abc', 'efg')
@@ -266,6 +327,56 @@ class HavingTests(unittest.TestCase):
                          'GROUP BY "abc"."foo" '
                          "HAVING \"abc\".\"buz\"='fiz' AND SUM(\"efg\".\"bar\")>100", str(q))
 
+    def test_mysql_query_uses_backtick_quote_chars(self):
+        q = MySQLQuery.from_(self.table_abc).select(
+            self.table_abc.foo
+        ).groupby(
+            self.table_abc.foo
+        ).having(
+            self.table_abc.buz == 'fiz'
+        )
+        self.assertEqual("SELECT `foo` FROM `abc` GROUP BY `foo` HAVING `buz`='fiz'", str(q))
+
+    def test_vertica_query_uses_double_quote_chars(self):
+        q = VerticaQuery.from_(self.table_abc).select(
+            self.table_abc.foo
+        ).groupby(
+            self.table_abc.foo
+        ).having(
+            self.table_abc.buz == 'fiz'
+        )
+        self.assertEqual("SELECT \"foo\" FROM \"abc\" GROUP BY \"foo\" HAVING \"buz\"='fiz'", str(q))
+
+    def test_mssql_query_uses_double_quote_chars(self):
+        q = MSSQLQuery.from_(self.table_abc).select(
+            self.table_abc.foo
+        ).groupby(
+            self.table_abc.foo
+        ).having(
+            self.table_abc.buz == 'fiz'
+        )
+        self.assertEqual("SELECT \"foo\" FROM \"abc\" GROUP BY \"foo\" HAVING \"buz\"='fiz'", str(q))
+
+    def test_oracle_query_uses_double_quote_chars(self):
+        q = OracleQuery.from_(self.table_abc).select(
+            self.table_abc.foo
+        ).groupby(
+            self.table_abc.foo
+        ).having(
+            self.table_abc.buz == 'fiz'
+        )
+        self.assertEqual("SELECT \"foo\" FROM \"abc\" GROUP BY \"foo\" HAVING \"buz\"='fiz'", str(q))
+
+    def test_postgres_query_uses_double_quote_chars(self):
+        q = PostgreSQLQuery.from_(self.table_abc).select(
+            self.table_abc.foo
+        ).groupby(
+            self.table_abc.foo
+        ).having(
+            self.table_abc.buz == 'fiz'
+        )
+        self.assertEqual("SELECT \"foo\" FROM \"abc\" GROUP BY \"foo\" HAVING \"buz\"='fiz'", str(q))
+
 
 class OrderByTests(unittest.TestCase):
     t = Table('abc')