[tryton-debian-vcs] python-sql branch upstream updated. upstream/0.7-1-gc4e89a6
Mathias Behrle
tryton-debian-vcs at alioth.debian.org
Mon Sep 21 11:48:05 UTC 2015
The following commit has been merged in the upstream branch:
https://alioth.debian.org/plugins/scmgit/cgi-bin/gitweb.cgi/?p=tryton/python-sql.git;a=commitdiff;h=upstream/0.7-1-gc4e89a6
commit c4e89a6942e2dc91c1bb0965a78c313623aa0b29
Author: Mathias Behrle <mathiasb at m9s.biz>
Date: Mon Sep 21 13:02:39 2015 +0200
Adding upstream version 0.8.
Signed-off-by: Mathias Behrle <mathiasb at m9s.biz>
diff --git a/CHANGELOG b/CHANGELOG
index f264ea9..e7337b6 100644
--- a/CHANGELOG
+++ b/CHANGELOG
@@ -1,3 +1,15 @@
+Version 0.8 - 2015-09-19
+* Add DISTINCT qualifier to aggregate expressions
+* Allow to order on select queries
+* Add NULL ordering
+* Use UPPER to simulate missing ILIKE
+* Add CURRENT_DATE function
+* Fix DateTrunc function name
+* Add no_boolean Flavor
+* Add converter format2numeric
+* Add rownum limit style
+* Add no_as Flavor
+
Version 0.7 - 2015-05-19
* Fix WINDOW and HAVING params order in Select
* Add window functions
diff --git a/PKG-INFO b/PKG-INFO
index 8201603..20cca95 100644
--- a/PKG-INFO
+++ b/PKG-INFO
@@ -1,6 +1,6 @@
Metadata-Version: 1.1
Name: python-sql
-Version: 0.7
+Version: 0.8
Summary: Library to write SQL queries
Home-page: http://python-sql.tryton.org/
Author: B2CK
@@ -177,12 +177,35 @@ Description: python-sql
>>> Flavor.set(Flavor(max_limit=-1))
>>> tuple(select)
('SELECT * FROM "user" AS "a" LIMIT -1 OFFSET 10', ())
+
+ Limit style::
+
+ >>> select = user.select(limit=10, offset=20)
+ >>> Flavor.set(Flavor(limitstyle='limit'))
+ >>> tuple(select)
+ ('SELECT * FROM "user" AS "a" LIMIT 10 OFFSET 20', ())
+ >>> Flavor.set(Flavor(limitstyle='fetch'))
+ >>> tuple(select)
+ ('SELECT * FROM "user" AS "a" OFFSET (20) ROWS FETCH FIRST (10) ROWS ONLY', ())
+ >>> Flavor.set(Flavor(limitstyle='rownum'))
+ >>> tuple(select)
+ ('SELECT "a".* FROM (SELECT "b".*, ROWNUM AS "rnum" FROM (SELECT * FROM "user" AS "c") AS "b" WHERE (ROWNUM <= %s)) AS "a" WHERE ("rnum" > %s)', (30, 20))
+
+ qmark style::
>>> Flavor.set(Flavor(paramstyle='qmark'))
>>> select = user.select()
>>> select.where = user.name == 'foo'
>>> tuple(select)
('SELECT * FROM "user" AS "a" WHERE ("a"."name" = ?)', ('foo',))
+ numeric style::
+
+ >>> Flavor.set(Flavor(paramstyle='format'))
+ >>> select = user.select()
+ >>> select.where = user.name == 'foo'
+ >>> format2numeric(*select)
+ ('SELECT * FROM "user" AS "a" WHERE ("a"."name" = :0)', ('foo',))
+
Platform: UNKNOWN
Classifier: Development Status :: 5 - Production/Stable
Classifier: Intended Audience :: Developers
diff --git a/README b/README
index ec5b752..431680b 100644
--- a/README
+++ b/README
@@ -169,8 +169,31 @@ Flavors::
>>> Flavor.set(Flavor(max_limit=-1))
>>> tuple(select)
('SELECT * FROM "user" AS "a" LIMIT -1 OFFSET 10', ())
+
+Limit style::
+
+ >>> select = user.select(limit=10, offset=20)
+ >>> Flavor.set(Flavor(limitstyle='limit'))
+ >>> tuple(select)
+ ('SELECT * FROM "user" AS "a" LIMIT 10 OFFSET 20', ())
+ >>> Flavor.set(Flavor(limitstyle='fetch'))
+ >>> tuple(select)
+ ('SELECT * FROM "user" AS "a" OFFSET (20) ROWS FETCH FIRST (10) ROWS ONLY', ())
+ >>> Flavor.set(Flavor(limitstyle='rownum'))
+ >>> tuple(select)
+ ('SELECT "a".* FROM (SELECT "b".*, ROWNUM AS "rnum" FROM (SELECT * FROM "user" AS "c") AS "b" WHERE (ROWNUM <= %s)) AS "a" WHERE ("rnum" > %s)', (30, 20))
+
+qmark style::
>>> Flavor.set(Flavor(paramstyle='qmark'))
>>> select = user.select()
>>> select.where = user.name == 'foo'
>>> tuple(select)
('SELECT * FROM "user" AS "a" WHERE ("a"."name" = ?)', ('foo',))
+
+numeric style::
+
+ >>> Flavor.set(Flavor(paramstyle='format'))
+ >>> select = user.select()
+ >>> select.where = user.name == 'foo'
+ >>> format2numeric(*select)
+ ('SELECT * FROM "user" AS "a" WHERE ("a"."name" = :0)', ('foo',))
diff --git a/python_sql.egg-info/PKG-INFO b/python_sql.egg-info/PKG-INFO
index 8201603..20cca95 100644
--- a/python_sql.egg-info/PKG-INFO
+++ b/python_sql.egg-info/PKG-INFO
@@ -1,6 +1,6 @@
Metadata-Version: 1.1
Name: python-sql
-Version: 0.7
+Version: 0.8
Summary: Library to write SQL queries
Home-page: http://python-sql.tryton.org/
Author: B2CK
@@ -177,12 +177,35 @@ Description: python-sql
>>> Flavor.set(Flavor(max_limit=-1))
>>> tuple(select)
('SELECT * FROM "user" AS "a" LIMIT -1 OFFSET 10', ())
+
+ Limit style::
+
+ >>> select = user.select(limit=10, offset=20)
+ >>> Flavor.set(Flavor(limitstyle='limit'))
+ >>> tuple(select)
+ ('SELECT * FROM "user" AS "a" LIMIT 10 OFFSET 20', ())
+ >>> Flavor.set(Flavor(limitstyle='fetch'))
+ >>> tuple(select)
+ ('SELECT * FROM "user" AS "a" OFFSET (20) ROWS FETCH FIRST (10) ROWS ONLY', ())
+ >>> Flavor.set(Flavor(limitstyle='rownum'))
+ >>> tuple(select)
+ ('SELECT "a".* FROM (SELECT "b".*, ROWNUM AS "rnum" FROM (SELECT * FROM "user" AS "c") AS "b" WHERE (ROWNUM <= %s)) AS "a" WHERE ("rnum" > %s)', (30, 20))
+
+ qmark style::
>>> Flavor.set(Flavor(paramstyle='qmark'))
>>> select = user.select()
>>> select.where = user.name == 'foo'
>>> tuple(select)
('SELECT * FROM "user" AS "a" WHERE ("a"."name" = ?)', ('foo',))
+ numeric style::
+
+ >>> Flavor.set(Flavor(paramstyle='format'))
+ >>> select = user.select()
+ >>> select.where = user.name == 'foo'
+ >>> format2numeric(*select)
+ ('SELECT * FROM "user" AS "a" WHERE ("a"."name" = :0)', ('foo',))
+
Platform: UNKNOWN
Classifier: Development Status :: 5 - Production/Stable
Classifier: Intended Audience :: Developers
diff --git a/sql/__init__.py b/sql/__init__.py
index 3384f7c..48d3fea 100644
--- a/sql/__init__.py
+++ b/sql/__init__.py
@@ -29,9 +29,9 @@
from __future__ import division
-__version__ = '0.7'
+__version__ = '0.8'
__all__ = ['Flavor', 'Table', 'Values', 'Literal', 'Column', 'Join',
- 'Asc', 'Desc']
+ 'Asc', 'Desc', 'NullsFirst', 'NullsLast', 'format2numeric']
import string
import warnings
@@ -66,19 +66,26 @@ class Flavor(object):
Contains the flavor of SQL
Contains:
+ limitstyle - state the type of pagination
max_limit - limit to use if there is no limit but an offset
paramstyle - state the type of parameter marker formatting
ilike - support ilike extension
+ no_as - doesn't support AS keyword for column and table
+ null_ordering - support NULL ordering
function_mapping - dictionary with Function to replace
'''
def __init__(self, limitstyle='limit', max_limit=None, paramstyle='format',
- ilike=False, function_mapping=None):
- assert limitstyle in ['fetch', 'limit']
+ ilike=False, no_as=False, no_boolean=False, null_ordering=True,
+ function_mapping=None):
+ assert limitstyle in ['fetch', 'limit', 'rownum']
self.limitstyle = limitstyle
self.max_limit = max_limit
self.paramstyle = paramstyle
self.ilike = ilike
+ self.no_as = no_as
+ self.no_boolean = no_boolean
+ self.null_ordering = null_ordering
self.function_mapping = function_mapping or {}
@property
@@ -161,6 +168,19 @@ class AliasManager(object):
return alias(i)
+def format2numeric(query, params):
+ '''
+ Convert format paramstyle query to numeric paramstyle
+
+ >>> format2numeric('SELECT * FROM table WHERE col = %s', ('foo',))
+ ('SELECT * FROM table WHERE col = :0', ('foo',))
+ >>> format2numeric('SELECT * FROM table WHERE col1 = %s AND col2 = %s',
+ ... ('foo', 'bar'))
+ ('SELECT * FROM table WHERE col1 = :0 AND col2 = :1', ('foo', 'bar'))
+ '''
+ return (query % tuple(':%i' % i for i, _ in enumerate(params)), params)
+
+
class Query(object):
__slots__ = ()
@@ -441,7 +461,10 @@ class Select(FromItem, SelectQuery):
@staticmethod
def _format_column(column):
if isinstance(column, As):
- return '%s AS %s' % (column.expression, column)
+ if Flavor.get().no_as:
+ return '%s %s' % (column.expression, column)
+ else:
+ return '%s AS %s' % (column.expression, column)
else:
return str(column)
@@ -462,7 +485,48 @@ class Select(FromItem, SelectQuery):
windows.add(window_function.window)
yield window_function
+ def _rownum(self, func):
+ aliases = [c.output_name if isinstance(c, As) else None
+ for c in self.columns]
+
+ def columns(table):
+ if aliases and all(aliases):
+ return [Column(table, alias) for alias in aliases]
+ else:
+ return [Column(table, '*')]
+
+ limitselect = self.select(*columns(self))
+ if self.limit is not None:
+ max_row = self.limit
+ if self.offset is not None:
+ max_row += self.offset
+ limitselect.where = _rownum <= max_row
+ if self.offset is not None:
+ rnum = _rownum.as_('rnum')
+ limitselect.columns += (rnum,)
+ offsetselect = limitselect.select(*columns(limitselect),
+ where=rnum > self.offset)
+ query = offsetselect
+ else:
+ query = limitselect
+
+ self.limit, limit = None, self.limit
+ self.offset, offset = None, self.offset
+ query.for_, self.for_ = self.for_, None
+
+ try:
+ value = func(query)
+ finally:
+ self.limit = limit
+ self.offset = offset
+ self.for_ = query.for_
+ return value
+
def __str__(self):
+ if (Flavor.get().limitstyle == 'rownum'
+ and (self.limit is not None or self.offset is not None)):
+ return self._rownum(str)
+
with AliasManager():
from_ = str(self.from_)
if self.columns:
@@ -493,6 +557,9 @@ class Select(FromItem, SelectQuery):
@property
def params(self):
+ if (Flavor.get().limitstyle == 'rownum'
+ and (self.limit is not None or self.offset is not None)):
+ return self._rownum(lambda q: q.params)
p = []
p.extend(self._with_params())
for column in self.columns:
@@ -944,13 +1011,17 @@ class From(list):
# TODO column_alias
columns_definitions = getattr(from_, 'columns_definitions',
None)
+ if Flavor.get().no_as:
+ alias_template = ' "%s"'
+ else:
+ alias_template = ' AS "%s"'
# XXX find a better test for __getattr__ which returns Column
if (alias and columns_definitions
and not isinstance(columns_definitions, Column)):
- return (template + ' AS "%s" (%s)') % (from_, alias,
+ return (template + alias_template + ' (%s)') % (from_, alias,
columns_definitions)
elif alias:
- return (template + ' AS "%s"') % (from_, alias)
+ return (template + alias_template) % (from_, alias)
else:
return template % from_
return ', '.join(map(format, self))
@@ -1122,6 +1193,14 @@ class Expression(object):
def desc(self):
return Desc(self)
+ @property
+ def nulls_first(self):
+ return NullsFirst(self)
+
+ @property
+ def nulls_last(self):
+ return NullsLast(self)
+
class Literal(Expression):
__slots__ = ('_value')
@@ -1135,15 +1214,35 @@ class Literal(Expression):
return self._value
def __str__(self):
- return Flavor.get().param
+ flavor = Flavor.get()
+ if flavor.no_boolean:
+ if self._value is True:
+ return '(1 = 1)'
+ elif self._value is False:
+ return '(1 != 1)'
+ return flavor.param
@property
def params(self):
+ if Flavor.get().no_boolean:
+ if self._value is True or self._value is False:
+ return ()
return (self._value,)
Null = None
+class _Rownum(Expression):
+
+ def __str__(self):
+ return 'ROWNUM'
+
+ @property
+ def params(self):
+ return ()
+_rownum = _Rownum()
+
+
class Column(Expression):
__slots__ = ('_from', '_name')
@@ -1326,15 +1425,27 @@ class Window(object):
class Order(Expression):
- __slots__ = ('expression')
+ __slots__ = ('_expression')
_sql = ''
def __init__(self, expression):
super(Order, self).__init__()
+ self._expression = None
self.expression = expression
# TODO USING
+ @property
+ def expression(self):
+ return self._expression
+
+ @expression.setter
+ def expression(self, value):
+ assert isinstance(value, (Expression, SelectQuery))
+ self._expression = value
+
def __str__(self):
+ if isinstance(self.expression, SelectQuery):
+ return '(%s) %s' % (self.expression, self._sql)
return '%s %s' % (self.expression, self._sql)
@property
@@ -1352,6 +1463,58 @@ class Desc(Order):
_sql = 'DESC'
+class NullOrder(Expression):
+ __slots__ = ('expression')
+ _sql = ''
+
+ def __init__(self, expression):
+ super(NullOrder, self).__init__()
+ self.expression = expression
+
+ def __str__(self):
+ if not Flavor.get().null_ordering:
+ return '%s, %s' % (self._case, self.expression)
+ return '%s NULLS %s' % (self.expression, self._sql)
+
+ @property
+ def params(self):
+ p = []
+ if not Flavor.get().null_ordering:
+ p.extend(self.expression.params)
+ p.extend(self._case_values())
+ p.extend(self.expression.params)
+ return tuple(p)
+
+ @property
+ def _case(self):
+ from .conditionals import Case
+ values = self._case_values()
+ if isinstance(self.expression, Order):
+ expression = self.expression.expression
+ else:
+ expression = self.expression
+ return Asc(Case((expression == Null, values[0]), else_=values[1]))
+
+ def _case_values(self):
+ raise NotImplementedError
+
+
+class NullsFirst(NullOrder):
+ __slots__ = ()
+ _sql = 'FIRST'
+
+ def _case_values(self):
+ return (0, 1)
+
+
+class NullsLast(NullOrder):
+ __slots__ = ()
+ _sql = 'LAST'
+
+ def _case_values(self):
+ return (1, 0)
+
+
class For(object):
__slots__ = ('_tables', '_type_', 'nowait')
diff --git a/sql/aggregate.py b/sql/aggregate.py
index 2acacb2..aa1c464 100644
--- a/sql/aggregate.py
+++ b/sql/aggregate.py
@@ -34,18 +34,29 @@ __all__ = ['Avg', 'BitAnd', 'BitOr', 'BoolAnd', 'BoolOr', 'Count', 'Every',
class Aggregate(Expression):
- __slots__ = ('expression', '_within', '_filter', '_window')
+ __slots__ = ('expression', '_distinct', '_within', '_filter', '_window')
_sql = ''
- def __init__(self, expression, within=None, filter_=None, window=None):
+ def __init__(self, expression, distinct=False, within=None, filter_=None,
+ window=None):
# TODO order_by
super(Aggregate, self).__init__()
self.expression = expression
+ self.distinct = distinct
self.within = within
self.filter_ = filter_
self.window = window
@property
+ def distinct(self):
+ return self._distinct
+
+ @distinct.setter
+ def distinct(self, value):
+ assert isinstance(value, bool)
+ self._distinct = value
+
+ @property
def within(self):
return self._within
@@ -79,7 +90,8 @@ class Aggregate(Expression):
self._window = value
def __str__(self):
- aggregate = '%s(%s)' % (self._sql, self.expression)
+ quantifier = 'DISTINCT ' if self.distinct else ''
+ aggregate = '%s(%s%s)' % (self._sql, quantifier, self.expression)
within = ''
if self.within:
within = (' WITHIN GROUP (ORDER BY %s)'
diff --git a/sql/functions.py b/sql/functions.py
index 1d0b196..4c24cd8 100644
--- a/sql/functions.py
+++ b/sql/functions.py
@@ -37,9 +37,9 @@ __all__ = ['Abs', 'Cbrt', 'Ceil', 'Degrees', 'Div', 'Exp', 'Floor', 'Ln',
'BitLength', 'CharLength', 'Overlay', 'Position', 'Substring', 'Trim',
'Upper',
'ToChar', 'ToDate', 'ToNumber', 'ToTimestamp',
- 'Age', 'ClockTimestamp', 'CurrentTime', 'CurrentTimestamp', 'DatePart',
- 'DateTrunc', 'Extract', 'Isfinite', 'JustifyDays', 'JustifyHours',
- 'JustifyInterval', 'Localtime', 'Localtimestamp', 'Now',
+ 'Age', 'ClockTimestamp', 'CurrentDate', 'CurrentTime', 'CurrentTimestamp',
+ 'DatePart', 'DateTrunc', 'Extract', 'Isfinite', 'JustifyDays',
+ 'JustifyHours', 'JustifyInterval', 'Localtime', 'Localtimestamp', 'Now',
'StatementTimestamp', 'Timeofday', 'TransactionTimestamp',
'AtTimeZone',
'RowNumber', 'Rank', 'DenseRank', 'PercentRank', 'CumeDist', 'Ntile',
@@ -379,6 +379,11 @@ class ClockTimestamp(Function):
_function = 'CLOCK_TIMESTAMP'
+class CurrentDate(FunctionNotCallable):
+ __slots__ = ()
+ _function = 'CURRENT_DATE'
+
+
class CurrentTime(FunctionNotCallable):
__slots__ = ()
_function = 'CURRENT_TIME'
@@ -396,7 +401,7 @@ class DatePart(Function):
class DateTrunc(Function):
__slots__ = ()
- _function = 'DateTrunc'
+ _function = 'DATE_TRUNC'
class Extract(FunctionKeyword):
diff --git a/sql/operators.py b/sql/operators.py
index 3072b90..d406105 100644
--- a/sql/operators.py
+++ b/sql/operators.py
@@ -128,8 +128,9 @@ class BinaryOperator(Operator):
return (self.left, self.right)
def __str__(self):
- return '(%s %s %s)' % (self._format(self.left), self._operator,
- self._format(self.right))
+ left, right = self._operands
+ return '(%s %s %s)' % (self._format(left), self._operator,
+ self._format(right))
def __invert__(self):
return _INVERT[self.__class__](self.left, self.right)
@@ -347,8 +348,16 @@ class ILike(BinaryOperator):
else:
return 'LIKE'
+ @property
+ def _operands(self):
+ operands = super(ILike, self)._operands
+ if not Flavor.get().ilike:
+ from .functions import Upper
+ operands = tuple(Upper(o) for o in operands)
+ return operands
+
-class NotILike(BinaryOperator):
+class NotILike(ILike):
__slots__ = ()
@property
diff --git a/sql/tests/test_aggregate.py b/sql/tests/test_aggregate.py
index 2a310fa..748d205 100644
--- a/sql/tests/test_aggregate.py
+++ b/sql/tests/test_aggregate.py
@@ -57,3 +57,8 @@ class TestAggregate(unittest.TestCase):
with AliasManager():
self.assertEqual(str(avg), 'AVG("a"."c") OVER "b"')
self.assertEqual(avg.params, ())
+
+ def test_distinct(self):
+ avg = Avg(self.table.c, distinct=True)
+ self.assertEqual(str(avg), 'AVG(DISTINCT "c")')
+ self.assertEqual(avg.params, ())
diff --git a/sql/tests/test_as.py b/sql/tests/test_as.py
index 8e18df9..01e3de1 100644
--- a/sql/tests/test_as.py
+++ b/sql/tests/test_as.py
@@ -28,10 +28,10 @@
import unittest
-from sql import As, Column, Table
+from sql import As, Column, Table, Flavor
-class TestOrder(unittest.TestCase):
+class TestAs(unittest.TestCase):
table = Table('t')
column = Column(table, 'c')
@@ -42,3 +42,12 @@ class TestOrder(unittest.TestCase):
query = self.table.select(self.column.as_('foo'))
self.assertEqual(str(query), 'SELECT "a"."c" AS "foo" FROM "t" AS "a"')
self.assertEqual(query.params, ())
+
+ def test_no_as(self):
+ query = self.table.select(self.column.as_('foo'))
+ try:
+ Flavor.set(Flavor(no_as=True))
+ self.assertEqual(str(query), 'SELECT "a"."c" "foo" FROM "t" "a"')
+ self.assertEqual(query.params, ())
+ finally:
+ Flavor.set(Flavor())
diff --git a/sql/tests/test_literal.py b/sql/tests/test_literal.py
index c8dd5bf..53b9865 100644
--- a/sql/tests/test_literal.py
+++ b/sql/tests/test_literal.py
@@ -28,7 +28,7 @@
import unittest
-from sql import Literal
+from sql import Literal, Flavor
class TestLiteral(unittest.TestCase):
@@ -37,3 +37,19 @@ class TestLiteral(unittest.TestCase):
self.assertEqual(str(literal), '%s')
self.assertEqual(literal.params, (1,))
self.assertEqual(literal.value, 1)
+
+ def test_no_boolean(self):
+ true = Literal(True)
+ false = Literal(False)
+ self.assertEqual(str(true), '%s')
+ self.assertEqual(true.params, (True,))
+ self.assertEqual(str(false), '%s')
+ self.assertEqual(false.params, (False,))
+ try:
+ Flavor.set(Flavor(no_boolean=True))
+ self.assertEqual(str(true), '(1 = 1)')
+ self.assertEqual(str(false), '(1 != 1)')
+ self.assertEqual(true.params, ())
+ self.assertEqual(false.params, ())
+ finally:
+ Flavor.set(Flavor())
diff --git a/sql/tests/test_operators.py b/sql/tests/test_operators.py
index 84fc218..3774a83 100644
--- a/sql/tests/test_operators.py
+++ b/sql/tests/test_operators.py
@@ -262,7 +262,7 @@ class TestOperators(unittest.TestCase):
Flavor.set(flavor)
try:
like = ILike(self.table.c1, 'foo')
- self.assertEqual(str(like), '("c1" LIKE %s)')
+ self.assertEqual(str(like), '(UPPER("c1") LIKE UPPER(%s))')
self.assertEqual(like.params, ('foo',))
finally:
Flavor.set(Flavor())
@@ -282,7 +282,7 @@ class TestOperators(unittest.TestCase):
Flavor.set(flavor)
try:
like = NotILike(self.table.c1, 'foo')
- self.assertEqual(str(like), '("c1" NOT LIKE %s)')
+ self.assertEqual(str(like), '(UPPER("c1") NOT LIKE UPPER(%s))')
self.assertEqual(like.params, ('foo',))
finally:
Flavor.set(Flavor())
@@ -325,5 +325,7 @@ class TestOperators(unittest.TestCase):
FloorDiv(4, 2)
self.assertEqual(len(w), 1)
self.assertTrue(issubclass(w[-1].category, DeprecationWarning))
- self.assertIn('FloorDiv operator is deprecated, use Div function',
- str(w[-1].message))
+ if hasattr(self, 'assertIn'):
+ self.assertIn(
+ 'FloorDiv operator is deprecated, use Div function',
+ str(w[-1].message))
diff --git a/sql/tests/test_order.py b/sql/tests/test_order.py
index 6d859cd..47c55f7 100644
--- a/sql/tests/test_order.py
+++ b/sql/tests/test_order.py
@@ -28,7 +28,8 @@
import unittest
-from sql import Asc, Desc, Column, Table
+from sql import Asc, Desc, NullsFirst, NullsLast, Column, Table, Literal
+from sql import Flavor
class TestOrder(unittest.TestCase):
@@ -39,3 +40,43 @@ class TestOrder(unittest.TestCase):
def test_desc(self):
self.assertEqual(str(Desc(self.column)), '"c" DESC')
+
+ def test_nulls_first(self):
+ self.assertEqual(str(NullsFirst(self.column)), '"c" NULLS FIRST')
+ self.assertEqual(str(NullsFirst(Asc(self.column))),
+ '"c" ASC NULLS FIRST')
+
+ def test_nulls_last(self):
+ self.assertEqual(str(NullsLast(self.column)), '"c" NULLS LAST')
+ self.assertEqual(str(NullsLast(Asc(self.column))),
+ '"c" ASC NULLS LAST')
+
+ def test_no_null_ordering(self):
+ try:
+ Flavor.set(Flavor(null_ordering=False))
+
+ exp = NullsFirst(self.column)
+ self.assertEqual(str(exp),
+ 'CASE WHEN ("c" IS NULL) THEN %s ELSE %s END ASC, "c"')
+ self.assertEqual(exp.params, (0, 1))
+
+ exp = NullsFirst(Desc(self.column))
+ self.assertEqual(str(exp),
+ 'CASE WHEN ("c" IS NULL) THEN %s ELSE %s END ASC, "c" DESC')
+ self.assertEqual(exp.params, (0, 1))
+
+ exp = NullsLast(Literal(2))
+ self.assertEqual(str(exp),
+ 'CASE WHEN (%s IS NULL) THEN %s ELSE %s END ASC, %s')
+ self.assertEqual(exp.params, (2, 1, 0, 2))
+ finally:
+ Flavor.set(Flavor())
+
+ def test_order_query(self):
+ table = Table('t')
+ column = Column(table, 'c')
+ query = table.select(column)
+ self.assertEqual(str(Asc(query)),
+ '(SELECT "a"."c" FROM "t" AS "a") ASC')
+ self.assertEqual(str(Desc(query)),
+ '(SELECT "a"."c" FROM "t" AS "a") DESC')
diff --git a/sql/tests/test_select.py b/sql/tests/test_select.py
index 16230f5..ad82b93 100644
--- a/sql/tests/test_select.py
+++ b/sql/tests/test_select.py
@@ -115,8 +115,9 @@ class TestSelect(unittest.TestCase):
interesect = Interesect(query1, query2)
self.assertEqual(len(w), 1)
self.assertTrue(issubclass(w[-1].category, DeprecationWarning))
- self.assertIn('Interesect query is deprecated, use Intersect',
- str(w[-1].message))
+ if hasattr(self, 'assertIn'):
+ self.assertIn('Interesect query is deprecated, use Intersect',
+ str(w[-1].message))
self.assertTrue(isinstance(interesect, Intersect))
def test_select_except(self):
@@ -252,6 +253,83 @@ class TestSelect(unittest.TestCase):
finally:
Flavor.set(Flavor())
+ def test_select_rownum(self):
+ try:
+ Flavor.set(Flavor(limitstyle='rownum'))
+ query = self.table.select(limit=50, offset=10)
+ self.assertEqual(str(query),
+ 'SELECT "a".* FROM ('
+ 'SELECT "b".*, ROWNUM AS "rnum" FROM ('
+ 'SELECT * FROM "t" AS "c") AS "b" '
+ 'WHERE (ROWNUM <= %s)) AS "a" '
+ 'WHERE ("rnum" > %s)')
+ self.assertEqual(query.params, (60, 10))
+
+ query = self.table.select(
+ self.table.c1.as_('col1'), self.table.c2.as_('col2'),
+ limit=50, offset=10)
+ self.assertEqual(str(query),
+ 'SELECT "a"."col1", "a"."col2" FROM ('
+ 'SELECT "b"."col1", "b"."col2", ROWNUM AS "rnum" FROM ('
+ 'SELECT "c"."c1" AS "col1", "c"."c2" AS "col2" '
+ 'FROM "t" AS "c") AS "b" '
+ 'WHERE (ROWNUM <= %s)) AS "a" '
+ 'WHERE ("rnum" > %s)')
+ self.assertEqual(query.params, (60, 10))
+
+ subquery = query.select(query.col1, query.col2)
+ self.assertEqual(str(subquery),
+ 'SELECT "a"."col1", "a"."col2" FROM ('
+ 'SELECT "b"."col1", "b"."col2" FROM ('
+ 'SELECT "a"."col1", "a"."col2", ROWNUM AS "rnum" '
+ 'FROM ('
+ 'SELECT "c"."c1" AS "col1", "c"."c2" AS "col2" '
+ 'FROM "t" AS "c") AS "a" '
+ 'WHERE (ROWNUM <= %s)) AS "b" '
+ 'WHERE ("rnum" > %s)) AS "a"')
+ # XXX alias of query is reused but not a problem
+ # as it is hidden in subquery
+ self.assertEqual(query.params, (60, 10))
+
+ query = self.table.select(limit=50, offset=10,
+ order_by=[self.table.c])
+ self.assertEqual(str(query),
+ 'SELECT "a".* FROM ('
+ 'SELECT "b".*, ROWNUM AS "rnum" FROM ('
+ 'SELECT * FROM "t" AS "c" ORDER BY "c"."c") AS "b" '
+ 'WHERE (ROWNUM <= %s)) AS "a" '
+ 'WHERE ("rnum" > %s)')
+ self.assertEqual(query.params, (60, 10))
+
+ query = self.table.select(limit=50)
+ self.assertEqual(str(query),
+ 'SELECT "a".* FROM ('
+ 'SELECT * FROM "t" AS "b") AS "a" '
+ 'WHERE (ROWNUM <= %s)')
+ self.assertEqual(query.params, (50,))
+
+ query = self.table.select(offset=10)
+ self.assertEqual(str(query),
+ 'SELECT "a".* FROM ('
+ 'SELECT "b".*, ROWNUM AS "rnum" FROM ('
+ 'SELECT * FROM "t" AS "c") AS "b") AS "a" '
+ 'WHERE ("rnum" > %s)')
+ self.assertEqual(query.params, (10,))
+
+ query = self.table.select(self.table.c.as_('col'),
+ where=self.table.c >= 20,
+ limit=50, offset=10)
+ self.assertEqual(str(query),
+ 'SELECT "a"."col" FROM ('
+ 'SELECT "b"."col", ROWNUM AS "rnum" FROM ('
+ 'SELECT "c"."c" AS "col" FROM "t" AS "c" '
+ 'WHERE ("c"."c" >= %s)) AS "b" '
+ 'WHERE (ROWNUM <= %s)) AS "a" '
+ 'WHERE ("rnum" > %s)')
+ self.assertEqual(query.params, (20, 60, 10))
+ finally:
+ Flavor.set(Flavor())
+
def test_select_for(self):
c = self.table.c
query = self.table.select(c, for_=For('UPDATE'))
@@ -320,3 +398,12 @@ class TestSelect(unittest.TestCase):
order_by=[Literal(6)],
having=Literal(7))
self.assertEqual(query.params, (1, 2, 3, 4, 5, 6, 7, 8))
+
+ def test_no_as(self):
+ query = self.table.select(self.table.c)
+ try:
+ Flavor.set(Flavor(no_as=True))
+ self.assertEqual(str(query), 'SELECT "a"."c" FROM "t" "a"')
+ self.assertEqual(query.params, ())
+ finally:
+ Flavor.set(Flavor())
--
python-sql
More information about the tryton-debian-vcs
mailing list