[tryton-debian-vcs] python-sql branch debian updated. debian/0.6-2-5-g84ba7a3
Mathias Behrle
tryton-debian-vcs at alioth.debian.org
Tue May 19 13:20:32 UTC 2015
The following commit has been merged in the debian branch:
https://alioth.debian.org/plugins/scmgit/cgi-bin/gitweb.cgi/?p=tryton/python-sql.git;a=commitdiff;h=debian/0.6-2-5-g84ba7a3
commit 84ba7a350bb9c9b04655463444e35d7c5eaee393
Author: Mathias Behrle <mathiasb at m9s.biz>
Date: Tue May 19 14:48:45 2015 +0200
Releasing debian version 0.7-1.
Signed-off-by: Mathias Behrle <mathiasb at m9s.biz>
diff --git a/debian/changelog b/debian/changelog
index 35414b8..31c4c6d 100644
--- a/debian/changelog
+++ b/debian/changelog
@@ -1,3 +1,12 @@
+python-sql (0.7-1) unstable; urgency=medium
+
+ * Updating year of debian copyright.
+ * New home of python-sql.
+ * Merging upstream version 0.7.
+ * Updating copyright.
+
+ -- Mathias Behrle <mathiasb at m9s.biz> Tue, 19 May 2015 14:48:13 +0200
+
python-sql (0.6-2) unstable; urgency=medium
* Wrapping and sorting control files (wrap-and-sort -bts).
commit 575946e9884f3dc6f218454fd88a59e6a222e22f
Author: Mathias Behrle <mathiasb at m9s.biz>
Date: Tue May 19 12:13:58 2015 +0200
Updating copyright.
diff --git a/debian/copyright b/debian/copyright
index 146efa8..58428c2 100644
--- a/debian/copyright
+++ b/debian/copyright
@@ -1,8 +1,8 @@
Format: http://www.debian.org/doc/packaging-manuals/copyright-format/1.0/
Files: *
-Copyright: 2011-2013 Cédric Krier
- 2011-2013 B2CK
+Copyright: 2011-2015 Cédric Krier
+ 2011-2015 B2CK
License: BSD-3-clause
Files: sql/__init__.py
@@ -11,11 +11,6 @@ Copyright: 2013-2014 Nicolas Évrard
2011-2015 B2CK
License: BSD-3-clause
-Files: sql/tests/test_table.py
-Copyright: 2015 Cédric Krier
- 2015 B2CK
-License: BSD-3-clause
-
Files: sql/tests/test_combining_query.py
Copyright: 2014 Cédric Krier
2014 B2CK
@@ -33,8 +28,8 @@ License: BSD-3-clause
Files: sql/tests/test_select.py
Copyright: 2013-2014 Nicolas Évrard
- 2011-2013 Cédric Krier
- 2011-2013 B2CK
+ 2011-2015 Cédric Krier
+ 2011-2015 B2CK
License: BSD-3-clause
Files: debian/*
commit 4a0757d732409c27420c5058142f6c3c1616d0f2
Author: Mathias Behrle <mathiasb at m9s.biz>
Date: Tue May 19 12:05:55 2015 +0200
Merging upstream version 0.7.
diff --git a/CHANGELOG b/CHANGELOG
index a52b0bd..f264ea9 100644
--- a/CHANGELOG
+++ b/CHANGELOG
@@ -1,3 +1,10 @@
+Version 0.7 - 2015-05-19
+* Fix WINDOW and HAVING params order in Select
+* Add window functions
+* Add filter and within group to aggregate
+* Add limitstyle with 'offset' and 'limit'
+* Add Lateral
+
Version 0.6 - 2015-02-05
* Fix Delete query under multithread
* Add missing quote for with query name
diff --git a/PKG-INFO b/PKG-INFO
index 432eb1e..8201603 100644
--- a/PKG-INFO
+++ b/PKG-INFO
@@ -1,8 +1,8 @@
Metadata-Version: 1.1
Name: python-sql
-Version: 0.6
+Version: 0.7
Summary: Library to write SQL queries
-Home-page: http://code.google.com/p/python-sql/
+Home-page: http://python-sql.tryton.org/
Author: B2CK
Author-email: info at b2ck.com
License: BSD
diff --git a/python_sql.egg-info/PKG-INFO b/python_sql.egg-info/PKG-INFO
index 432eb1e..8201603 100644
--- a/python_sql.egg-info/PKG-INFO
+++ b/python_sql.egg-info/PKG-INFO
@@ -1,8 +1,8 @@
Metadata-Version: 1.1
Name: python-sql
-Version: 0.6
+Version: 0.7
Summary: Library to write SQL queries
-Home-page: http://code.google.com/p/python-sql/
+Home-page: http://python-sql.tryton.org/
Author: B2CK
Author-email: info at b2ck.com
License: BSD
diff --git a/python_sql.egg-info/SOURCES.txt b/python_sql.egg-info/SOURCES.txt
index 66256d5..e0bb3cf 100644
--- a/python_sql.egg-info/SOURCES.txt
+++ b/python_sql.egg-info/SOURCES.txt
@@ -24,6 +24,7 @@ sql/tests/test_for.py
sql/tests/test_functions.py
sql/tests/test_insert.py
sql/tests/test_join.py
+sql/tests/test_lateral.py
sql/tests/test_literal.py
sql/tests/test_operators.py
sql/tests/test_order.py
@@ -31,4 +32,5 @@ sql/tests/test_select.py
sql/tests/test_table.py
sql/tests/test_update.py
sql/tests/test_values.py
+sql/tests/test_window.py
sql/tests/test_with.py
\ No newline at end of file
diff --git a/setup.py b/setup.py
index 09e38fe..f9bed98 100644
--- a/setup.py
+++ b/setup.py
@@ -48,7 +48,7 @@ setup(name='python-sql',
long_description=read('README'),
author='B2CK',
author_email='info at b2ck.com',
- url='http://code.google.com/p/python-sql/',
+ url='http://python-sql.tryton.org/',
packages=find_packages(),
classifiers=[
'Development Status :: 5 - Production/Stable',
diff --git a/sql/__init__.py b/sql/__init__.py
index 0a39deb..3384f7c 100644
--- a/sql/__init__.py
+++ b/sql/__init__.py
@@ -29,7 +29,7 @@
from __future__ import division
-__version__ = '0.6'
+__version__ = '0.7'
__all__ = ['Flavor', 'Table', 'Values', 'Literal', 'Column', 'Join',
'Asc', 'Desc']
@@ -72,8 +72,10 @@ class Flavor(object):
function_mapping - dictionary with Function to replace
'''
- def __init__(self, max_limit=None, paramstyle='format', ilike=False,
- function_mapping=None):
+ def __init__(self, limitstyle='limit', max_limit=None, paramstyle='format',
+ ilike=False, function_mapping=None):
+ assert limitstyle in ['fetch', 'limit']
+ self.limitstyle = limitstyle
self.max_limit = max_limit
self.paramstyle = paramstyle
self.ilike = ilike
@@ -241,6 +243,25 @@ class FromItem(object):
def join(self, right, type_='INNER', condition=None):
return Join(self, right, type_=type_, condition=condition)
+ def lateral(self):
+ return Lateral(self)
+
+
+class Lateral(FromItem):
+ __slots__ = ('_from_item',)
+
+ def __init__(self, from_item):
+ self._from_item = from_item
+
+ def __str__(self):
+ template = '%s'
+ if isinstance(self._from_item, Query):
+ template = '(%s)'
+ return 'LATERAL ' + template % self._from_item
+
+ def __getattr__(self, name):
+ return getattr(self._from_item, name)
+
class With(FromItem):
__slots__ = ('columns', 'query', 'recursive')
@@ -309,17 +330,6 @@ class SelectQuery(WithQuery):
self._limit = value
@property
- def _limit_str(self):
- limit = ''
- if self.limit is not None:
- limit = ' LIMIT %s' % self.limit
- elif self.offset:
- max_limit = Flavor.get().max_limit
- if max_limit:
- limit = ' LIMIT %s' % max_limit
- return limit
-
- @property
def offset(self):
return self._offset
@@ -330,11 +340,27 @@ class SelectQuery(WithQuery):
self._offset = value
@property
- def _offset_str(self):
- offset = ''
- if self.offset:
- offset = ' OFFSET %s' % self.offset
- return offset
+ def _limit_offset_str(self):
+ if Flavor.get().limitstyle == 'limit':
+ offset = ''
+ if self.offset:
+ offset = ' OFFSET %s' % self.offset
+ limit = ''
+ if self.limit is not None:
+ limit = ' LIMIT %s' % self.limit
+ elif self.offset:
+ max_limit = Flavor.get().max_limit
+ if max_limit:
+ limit = ' LIMIT %s' % max_limit
+ return limit + offset
+ else:
+ offset = ''
+ if self.offset:
+ offset = ' OFFSET (%s) ROWS' % self.offset
+ fetch = ''
+ if self.limit is not None:
+ fetch = ' FETCH FIRST (%s) ROWS ONLY' % self.limit
+ return offset + fetch
class Select(FromItem, SelectQuery):
@@ -419,6 +445,23 @@ class Select(FromItem, SelectQuery):
else:
return str(column)
+ def _window_functions(self):
+ from sql.functions import WindowFunction
+ from sql.aggregate import Aggregate
+ windows = set()
+ for column in self.columns:
+ window_function = None
+ if isinstance(column, (WindowFunction, Aggregate)):
+ window_function = column
+ elif (isinstance(column, As)
+ and isinstance(column.expression,
+ (WindowFunction, Aggregate))):
+ window_function = column.expression
+ if (window_function and window_function.window
+ and window_function.window not in windows):
+ windows.add(window_function.window)
+ yield window_function
+
def __str__(self):
with AliasManager():
from_ = str(self.from_)
@@ -435,13 +478,18 @@ class Select(FromItem, SelectQuery):
having = ''
if self.having:
having = ' HAVING ' + str(self.having)
+ window = ''
+ windows = [f.window for f in self._window_functions()]
+ if windows:
+ window = ' WINDOW ' + ', '.join(
+ '"%s" AS (%s)' % (w.alias, w) for w in windows)
for_ = ''
if self.for_ is not None:
for_ = ' ' + ' '.join(map(str, self.for_))
return (self._with_str()
+ 'SELECT %s FROM %s' % (columns, from_)
- + where + group_by + having + self._order_by_str
- + self._limit_str + self._offset_str + for_)
+ + where + group_by + having + window + self._order_by_str
+ + self._limit_offset_str + for_)
@property
def params(self):
@@ -457,11 +505,13 @@ class Select(FromItem, SelectQuery):
if self.group_by:
for expression in self.group_by:
p.extend(expression.params)
- if self.having:
- p.extend(self.having.params)
if self.order_by:
for expression in self.order_by:
p.extend(expression.params)
+ if self.having:
+ p.extend(self.having.params)
+ for window_function in self._window_functions():
+ p.extend(window_function.window.params)
return tuple(p)
@@ -724,7 +774,7 @@ class CombiningQuery(FromItem, SelectQuery):
with AliasManager():
operator = ' %s %s' % (self._operator, 'ALL ' if self.all_ else '')
return (operator.join(map(str, self.queries)) + self._order_by_str
- + self._limit_str + self._offset_str)
+ + self._limit_offset_str)
@property
def params(self):
@@ -1166,6 +1216,115 @@ class Cast(Expression):
return (self.expression,)
+class Window(object):
+ __slots__ = ('_partition', '_order_by', '_frame', '_start', '_end')
+
+ def __init__(self, partition, order_by=None,
+ frame=None, start=None, end=0):
+ super(Window, self).__init__()
+ self._partition = None
+ self._order_by = None
+ self._frame = None
+ self._start = None
+ self._end = None
+ self.partition = partition
+ self.order_by = order_by
+ self.frame = frame
+ self.start = start
+ self.end = end
+
+ @property
+ def partition(self):
+ return self._partition
+
+ @partition.setter
+ def partition(self, value):
+ assert all(isinstance(e, Expression) for e in value)
+ self._partition = value
+
+ @property
+ def order_by(self):
+ return self._order_by
+
+ @order_by.setter
+ def order_by(self, value):
+ if value is not None:
+ if isinstance(value, Expression):
+ value = [value]
+ assert all(isinstance(col, Expression) for col in value)
+ self._order_by = value
+
+ @property
+ def frame(self):
+ return self._frame
+
+ @frame.setter
+ def frame(self, value):
+ if value:
+ assert value in ['RANGE', 'ROWS']
+ self._frame = value
+
+ @property
+ def start(self):
+ return self._start
+
+ @start.setter
+ def start(self, value):
+ if value:
+ assert isinstance(value, (int, long))
+ self._start = value
+
+ @property
+ def end(self):
+ return self._end
+
+ @end.setter
+ def end(self, value):
+ if value:
+ assert isinstance(value, (int, long))
+ self._end = value
+
+ @property
+ def alias(self):
+ return AliasManager.get(self)
+
+ def __str__(self):
+ partition = ''
+ if self.partition:
+ partition = 'PARTITION BY ' + ', '.join(map(str, self.partition))
+ order_by = ''
+ if self.order_by:
+ order_by = ' ORDER BY ' + ', '.join(map(str, self.order_by))
+
+ def format(frame, direction):
+ if frame is None:
+ return 'UNBOUNDED %s' % direction
+ elif not frame:
+ return 'CURRENT ROW'
+ elif frame < 0:
+ return '%s PRECEDING' % -frame
+ elif frame > 0:
+ return '%s FOLLOWING' % frame
+
+ frame = ''
+ if self.frame:
+ start = format(self.start, 'PRECEDING')
+ end = format(self.end, 'FOLLOWING')
+ frame = ' %s BETWEEN %s AND %s' % (self.frame, start, end)
+ return partition + order_by + frame
+
+ @property
+ def params(self):
+ p = []
+ if self.partition:
+ for expression in self.partition:
+ p.extend(expression.params)
+ if self.order_by:
+ for expression in self.order_by:
+ p.extend(expression.params)
+ return tuple(p)
+
+
class Order(Expression):
__slots__ = ('expression')
_sql = ''
diff --git a/sql/aggregate.py b/sql/aggregate.py
index ea75d20..2acacb2 100644
--- a/sql/aggregate.py
+++ b/sql/aggregate.py
@@ -1,7 +1,7 @@
# -*- coding: utf-8 -*-
#
-# Copyright (c) 2011-2013, Cédric Krier
-# Copyright (c) 2011-2013, B2CK
+# Copyright (c) 2011-2015, Cédric Krier
+# Copyright (c) 2011-2015, B2CK
# All rights reserved.
#
# Redistribution and use in source and binary forms, with or without
@@ -27,26 +27,80 @@
# SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
-from sql import Expression
+from sql import Expression, Window
__all__ = ['Avg', 'BitAnd', 'BitOr', 'BoolAnd', 'BoolOr', 'Count', 'Every',
'Max', 'Min', 'Stddev', 'Sum', 'Variance']
class Aggregate(Expression):
- __slots__ = ('expression')
+ __slots__ = ('expression', '_within', '_filter', '_window')
_sql = ''
- def __init__(self, expression):
+ def __init__(self, expression, within=None, filter_=None, window=None):
+ # TODO order_by
super(Aggregate, self).__init__()
self.expression = expression
+ self.within = within
+ self.filter_ = filter_
+ self.window = window
+
+ @property
+ def within(self):
+ return self._within
+
+ @within.setter
+ def within(self, value):
+ if value is not None:
+ if isinstance(value, Expression):
+ value = [value]
+ assert all(isinstance(col, Expression) for col in value)
+ self._within = value
+
+ @property
+ def filter_(self):
+ return self._filter
+
+ @filter_.setter
+ def filter_(self, value):
+ from sql.operators import And, Or
+ if value is not None:
+ assert isinstance(value, (Expression, And, Or))
+ self._filter = value
+
+ @property
+ def window(self):
+ return self._window
+
+ @window.setter
+ def window(self, value):
+ if value:
+ assert isinstance(value, Window)
+ self._window = value
def __str__(self):
- return '%s(%s)' % (self._sql, self.expression)
+ aggregate = '%s(%s)' % (self._sql, self.expression)
+ within = ''
+ if self.within:
+ within = (' WITHIN GROUP (ORDER BY %s)'
+ % ', '.join(map(str, self.within)))
+ filter_ = ''
+ if self.filter_:
+ filter_ = ' FILTER (WHERE %s)' % self.filter_
+ window = ''
+ if self.window:
+ window = ' OVER "%s"' % self.window.alias
+ return aggregate + within + filter_ + window
@property
def params(self):
- return self.expression.params
+ p = list(self.expression.params)
+ if self.within:
+ for expression in self.within:
+ p.extend(expression.params)
+ if self.filter_:
+ p.extend(self.filter_.params)
+ return tuple(p)
class Avg(Aggregate):
diff --git a/sql/functions.py b/sql/functions.py
index 1eeb7c8..1d0b196 100644
--- a/sql/functions.py
+++ b/sql/functions.py
@@ -1,7 +1,7 @@
# -*- coding: utf-8 -*-
#
-# Copyright (c) 2011-2013, Cédric Krier
-# Copyright (c) 2011-2013, B2CK
+# Copyright (c) 2011-2015, Cédric Krier
+# Copyright (c) 2011-2015, B2CK
# All rights reserved.
#
# Redistribution and use in source and binary forms, with or without
@@ -28,7 +28,7 @@
from itertools import chain
-from sql import Expression, Flavor, FromItem
+from sql import Expression, Flavor, FromItem, Window
__all__ = ['Abs', 'Cbrt', 'Ceil', 'Degrees', 'Div', 'Exp', 'Floor', 'Ln',
'Log', 'Mod', 'Pi', 'Power', 'Radians', 'Random', 'Round', 'SetSeed',
@@ -41,7 +41,9 @@ __all__ = ['Abs', 'Cbrt', 'Ceil', 'Degrees', 'Div', 'Exp', 'Floor', 'Ln',
'DateTrunc', 'Extract', 'Isfinite', 'JustifyDays', 'JustifyHours',
'JustifyInterval', 'Localtime', 'Localtimestamp', 'Now',
'StatementTimestamp', 'Timeofday', 'TransactionTimestamp',
- 'AtTimeZone']
+ 'AtTimeZone',
+ 'RowNumber', 'Rank', 'DenseRank', 'PercentRank', 'CumeDist', 'Ntile',
+ 'Lag', 'Lead', 'FirstValue', 'LastValue', 'NthValue']
# Mathematical
@@ -474,3 +476,103 @@ class AtTimeZone(Function):
if Mapping:
return Mapping(self.field, self.zone).params
return self.field.params + (self.zone,)
+
+
+class WindowFunction(Function):
+ __slots__ = ('_filter', '_window')
+
+ def __init__(self, *args, **kwargs):
+ self.filter_ = kwargs.pop('filter_', None)
+ self.window = kwargs['window']
+ super(WindowFunction, self).__init__(*args, **kwargs)
+
+ @property
+ def filter_(self):
+ return self._filter
+
+ @filter_.setter
+ def filter_(self, value):
+ from sql.operators import And, Or
+ if value is not None:
+ assert isinstance(value, (Expression, And, Or))
+ self._filter = value
+
+ @property
+ def window(self):
+ return self._window
+
+ @window.setter
+ def window(self, value):
+ if value:
+ assert isinstance(value, Window)
+ self._window = value
+
+ def __str__(self):
+ function = super(WindowFunction, self).__str__()
+ filter_ = ''
+ if self.filter_:
+ filter_ = ' FILTER (WHERE %s)' % self.filter_
+ over = ' OVER "%s"' % self.window.alias
+ return function + filter_ + over
+
+ @property
+ def params(self):
+ p = list(super(WindowFunction, self).params)
+ if self.filter_:
+ p.extend(self.filter_.params)
+ return tuple(p)
+
+
+class RowNumber(WindowFunction):
+ __slots__ = ()
+ _function = 'ROW_NUMBER'
+
+
+class Rank(WindowFunction):
+ __slots__ = ()
+ _function = 'RANK'
+
+
+class DenseRank(WindowFunction):
+ __slots__ = ()
+ _function = 'DENSE_RANK'
+
+
+class PercentRank(WindowFunction):
+ __slots__ = ()
+ _function = 'PERCENT_RANK'
+
+
+class CumeDist(WindowFunction):
+ __slots__ = ()
+ _function = 'CUME_DIST'
+
+
+class Ntile(WindowFunction):
+ __slots__ = ()
+ _function = 'NTILE'
+
+
+class Lag(WindowFunction):
+ __slots__ = ()
+ _function = 'LAG'
+
+
+class Lead(WindowFunction):
+ __slots__ = ()
+ _function = 'LEAD'
+
+
+class FirstValue(WindowFunction):
+ __slots__ = ()
+ _function = 'FIRST_VALUE'
+
+
+class LastValue(WindowFunction):
+ __slots__ = ()
+ _function = 'LAST_VALUE'
+
+
+class NthValue(WindowFunction):
+ __slots__ = ()
+ _function = 'NTH_VALUE'
diff --git a/sql/tests/__init__.py b/sql/tests/__init__.py
index c262fba..cdc9569 100644
--- a/sql/tests/__init__.py
+++ b/sql/tests/__init__.py
@@ -62,9 +62,9 @@ def additional_tests():
def main():
suite = test_suite()
runner = unittest.TextTestRunner()
- runner.run(suite)
+ return runner.run(suite)
if __name__ == '__main__':
sys.path.insert(0, os.path.dirname(os.path.dirname(
os.path.dirname(os.path.abspath(__file__)))))
- main()
+ sys.exit(not main().wasSuccessful())
diff --git a/sql/tests/test_aggregate.py b/sql/tests/test_aggregate.py
index db59c67..2a310fa 100644
--- a/sql/tests/test_aggregate.py
+++ b/sql/tests/test_aggregate.py
@@ -1,7 +1,7 @@
# -*- coding: utf-8 -*-
#
-# Copyright (c) 2011-2013, Cédric Krier
-# Copyright (c) 2011-2013, B2CK
+# Copyright (c) 2011-2015, Cédric Krier
+# Copyright (c) 2011-2015, B2CK
# All rights reserved.
#
# Redistribution and use in source and binary forms, with or without
@@ -28,7 +28,7 @@
import unittest
-from sql import Table
+from sql import Table, Window, AliasManager
from sql.aggregate import Avg
@@ -41,3 +41,19 @@ class TestAggregate(unittest.TestCase):
avg = Avg(self.table.a + self.table.b)
self.assertEqual(str(avg), 'AVG(("a" + "b"))')
+
+ def test_within(self):
+ avg = Avg(self.table.a, within=self.table.b)
+ self.assertEqual(str(avg), 'AVG("a") WITHIN GROUP (ORDER BY "b")')
+ self.assertEqual(avg.params, ())
+
+ def test_filter(self):
+ avg = Avg(self.table.a, filter_=self.table.a > 0)
+ self.assertEqual(str(avg), 'AVG("a") FILTER (WHERE ("a" > %s))')
+ self.assertEqual(avg.params, (0,))
+
+ def test_window(self):
+ avg = Avg(self.table.c, window=Window([]))
+ with AliasManager():
+ self.assertEqual(str(avg), 'AVG("a"."c") OVER "b"')
+ self.assertEqual(avg.params, ())
diff --git a/sql/tests/test_functions.py b/sql/tests/test_functions.py
index 248142b..4c7b148 100644
--- a/sql/tests/test_functions.py
+++ b/sql/tests/test_functions.py
@@ -1,7 +1,7 @@
# -*- coding: utf-8 -*-
#
-# Copyright (c) 2011-2013, Cédric Krier
-# Copyright (c) 2011-2013, B2CK
+# Copyright (c) 2011-2015, Cédric Krier
+# Copyright (c) 2011-2015, B2CK
# All rights reserved.
#
# Redistribution and use in source and binary forms, with or without
@@ -28,9 +28,9 @@
import unittest
-from sql import Table, Flavor
+from sql import Table, Flavor, Window, AliasManager
from sql.functions import (Function, FunctionKeyword, FunctionNotCallable, Abs,
- Overlay, Trim, AtTimeZone, Div, CurrentTime)
+ Overlay, Trim, AtTimeZone, Div, CurrentTime, Rank)
class TestFunctions(unittest.TestCase):
@@ -135,3 +135,23 @@ class TestFunctions(unittest.TestCase):
current_time = CurrentTime()
self.assertEqual(str(current_time), 'CURRENT_TIME')
self.assertEqual(current_time.params, ())
+
+
+class TestWindowFunction(unittest.TestCase):
+
+ def test_window(self):
+ t = Table('t')
+ function = Rank(t.c, window=Window([]))
+
+ with AliasManager():
+ self.assertEqual(str(function), 'RANK("a"."c") OVER "b"')
+ self.assertEqual(function.params, ())
+
+ def test_filter(self):
+ t = Table('t')
+ function = Rank(t.c, filter_=t.c > 0, window=Window([]))
+
+ with AliasManager():
+ self.assertEqual(str(function),
+ 'RANK("a"."c") FILTER (WHERE ("a"."c" > %s)) OVER "b"')
+ self.assertEqual(function.params, (0,))
diff --git a/sql/tests/test_aggregate.py b/sql/tests/test_lateral.py
similarity index 62%
copy from sql/tests/test_aggregate.py
copy to sql/tests/test_lateral.py
index db59c67..1117daa 100644
--- a/sql/tests/test_aggregate.py
+++ b/sql/tests/test_lateral.py
@@ -1,7 +1,7 @@
# -*- coding: utf-8 -*-
#
-# Copyright (c) 2011-2013, Cédric Krier
-# Copyright (c) 2011-2013, B2CK
+# Copyright (c) 2015, Cédric Krier
+# Copyright (c) 2015, B2CK
# All rights reserved.
#
# Redistribution and use in source and binary forms, with or without
@@ -28,16 +28,32 @@
import unittest
-from sql import Table
-from sql.aggregate import Avg
+from sql import Table, Lateral, From
+from sql.functions import Function
-class TestAggregate(unittest.TestCase):
- table = Table('t')
+class TestLateral(unittest.TestCase):
- def test_avg(self):
- avg = Avg(self.table.c)
- self.assertEqual(str(avg), 'AVG("c")')
+ def test_lateral_select(self):
+ t1 = Table('t1')
+ t2 = Table('t2')
+ lateral = Lateral(t2.select(where=t2.id == t1.t2))
+ query = From([t1, lateral]).select()
- avg = Avg(self.table.a + self.table.b)
- self.assertEqual(str(avg), 'AVG(("a" + "b"))')
+ self.assertEqual(str(query),
+ 'SELECT * FROM "t1" AS "a", LATERAL '
+ '(SELECT * FROM "t2" AS "c" WHERE ("c"."id" = "a"."t2")) AS "b"')
+ self.assertEqual(query.params, ())
+
+ def test_lateral_function(self):
+
+ class Func(Function):
+ _function = 'FUNC'
+
+ t = Table('t')
+ lateral = Lateral(Func(t.a))
+ query = From([t, lateral]).select()
+
+ self.assertEqual(str(query),
+ 'SELECT * FROM "t" AS "a", LATERAL FUNC("a"."a") AS "b"')
+ self.assertEqual(query.params, ())
diff --git a/sql/tests/test_select.py b/sql/tests/test_select.py
index 1e17ed1..16230f5 100644
--- a/sql/tests/test_select.py
+++ b/sql/tests/test_select.py
@@ -1,8 +1,8 @@
# -*- coding: utf-8 -*-
#
-# Copyright (c) 2011-2013, Cédric Krier
+# Copyright (c) 2011-2015, Cédric Krier
# Copyright (c) 2013-2014, Nicolas Évrard
-# Copyright (c) 2011-2013, B2CK
+# Copyright (c) 2011-2015, B2CK
# All rights reserved.
#
# Redistribution and use in source and binary forms, with or without
@@ -31,8 +31,8 @@ import unittest
import warnings
from copy import deepcopy
-from sql import Table, Join, Union, Literal, Flavor, For, With
-from sql.functions import Now, Function
+from sql import Table, Join, Union, Literal, Flavor, For, With, Window, Select
+from sql.functions import Now, Function, Rank, DatePart
from sql.aggregate import Min
@@ -195,24 +195,25 @@ class TestSelect(unittest.TestCase):
self.assertEqual(query.params, (1,))
def test_select_limit_offset(self):
- query = self.table.select(limit=50, offset=10)
- self.assertEqual(str(query),
- 'SELECT * FROM "t" AS "a" LIMIT 50 OFFSET 10')
- self.assertEqual(query.params, ())
+ try:
+ Flavor.set(Flavor(limitstyle='limit'))
+ query = self.table.select(limit=50, offset=10)
+ self.assertEqual(str(query),
+ 'SELECT * FROM "t" AS "a" LIMIT 50 OFFSET 10')
+ self.assertEqual(query.params, ())
- query.limit = None
- self.assertEqual(str(query),
- 'SELECT * FROM "t" AS "a" OFFSET 10')
- self.assertEqual(query.params, ())
+ query.limit = None
+ self.assertEqual(str(query),
+ 'SELECT * FROM "t" AS "a" OFFSET 10')
+ self.assertEqual(query.params, ())
- query.offset = 0
- self.assertEqual(str(query),
- 'SELECT * FROM "t" AS "a"')
- self.assertEqual(query.params, ())
+ query.offset = 0
+ self.assertEqual(str(query),
+ 'SELECT * FROM "t" AS "a"')
+ self.assertEqual(query.params, ())
+
+ Flavor.set(Flavor(limitstyle='limit', max_limit=-1))
- flavor = Flavor(max_limit=-1)
- Flavor.set(flavor)
- try:
query.offset = None
self.assertEqual(str(query),
'SELECT * FROM "t" AS "a"')
@@ -230,6 +231,27 @@ class TestSelect(unittest.TestCase):
finally:
Flavor.set(Flavor())
+ def test_select_offset_fetch(self):
+ try:
+ Flavor.set(Flavor(limitstyle='fetch'))
+ query = self.table.select(limit=50, offset=10)
+ self.assertEqual(str(query),
+ 'SELECT * FROM "t" AS "a" '
+ 'OFFSET (10) ROWS FETCH FIRST (50) ROWS ONLY')
+ self.assertEqual(query.params, ())
+
+ query.limit = None
+ self.assertEqual(str(query),
+ 'SELECT * FROM "t" AS "a" OFFSET (10) ROWS')
+ self.assertEqual(query.params, ())
+
+ query.offset = 0
+ self.assertEqual(str(query),
+ 'SELECT * FROM "t" AS "a"')
+ self.assertEqual(query.params, ())
+ finally:
+ Flavor.set(Flavor())
+
def test_select_for(self):
c = self.table.c
query = self.table.select(c, for_=For('UPDATE'))
@@ -252,3 +274,49 @@ class TestSelect(unittest.TestCase):
'WITH "a" AS (SELECT "b"."c1" FROM "t" AS "b") '
'SELECT * FROM "a" AS "a"')
self.assertEqual(query.params, ())
+
+ def test_window(self):
+ query = self.table.select(Min(self.table.c1,
+ window=Window([self.table.c2])))
+
+ self.assertEqual(str(query),
+ 'SELECT MIN("a"."c1") OVER "b" FROM "t" AS "a" '
+ 'WINDOW "b" AS (PARTITION BY "a"."c2")')
+ self.assertEqual(query.params, ())
+
+ query = self.table.select(Rank(window=Window([])))
+ self.assertEqual(str(query),
+ 'SELECT RANK() OVER "b" FROM "t" AS "a" '
+ 'WINDOW "b" AS ()')
+ self.assertEqual(query.params, ())
+
+ window = Window([self.table.c1])
+ query = self.table.select(
+ Rank(filter_=self.table.c1 > 0, window=window),
+ Min(self.table.c1, window=window))
+ self.assertEqual(str(query),
+ 'SELECT RANK() FILTER (WHERE ("a"."c1" > %s)) OVER "b", '
+ 'MIN("a"."c1") OVER "b" FROM "t" AS "a" '
+ 'WINDOW "b" AS (PARTITION BY "a"."c1")')
+ self.assertEqual(query.params, (0,))
+
+ window = Window([DatePart('year', self.table.date_col)])
+ query = self.table.select(
+ Min(self.table.c1, window=window))
+ self.assertEqual(str(query),
+ 'SELECT MIN("a"."c1") OVER "b" FROM "t" AS "a" '
+ 'WINDOW "b" AS (PARTITION BY DATE_PART(%s, "a"."date_col"))')
+ self.assertEqual(query.params, ('year',))
+
+ def test_order_params(self):
+ with_ = With(query=self.table.select(self.table.c,
+ where=(self.table.c > 1)))
+ w = Window([Literal(8)])
+ query = Select([Literal(2), Min(self.table.c, window=w)],
+ from_=self.table.select(where=self.table.c > 3),
+ with_=with_,
+ where=self.table.c > 4,
+ group_by=[Literal(5)],
+ order_by=[Literal(6)],
+ having=Literal(7))
+ self.assertEqual(query.params, (1, 2, 3, 4, 5, 6, 7, 8))
diff --git a/sql/tests/test_window.py b/sql/tests/test_window.py
new file mode 100644
index 0000000..7e3460f
--- /dev/null
+++ b/sql/tests/test_window.py
@@ -0,0 +1,86 @@
+# -*- coding: utf-8 -*-
+#
+# Copyright (c) 2015, Cédric Krier
+# Copyright (c) 2015, B2CK
+# All rights reserved.
+#
+# Redistribution and use in source and binary forms, with or without
+# modification, are permitted provided that the following conditions are met:
+# * Redistributions of source code must retain the above copyright
+# notice, this list of conditions and the following disclaimer.
+# * Redistributions in binary form must reproduce the above copyright
+# notice, this list of conditions and the following disclaimer in the
+# documentation and/or other materials provided with the distribution.
+# * Neither the name of the <organization> nor the
+# names of its contributors may be used to endorse or promote products
+# derived from this software without specific prior written permission.
+#
+# THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
+# AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
+# IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
+# ARE DISCLAIMED. IN NO EVENT SHALL <COPYRIGHT HOLDER> BE LIABLE FOR ANY
+# DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
+# (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
+# LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
+# ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
+# (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
+# SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
+
+import unittest
+
+from sql import Window, Table
+
+
+class TestWindow(unittest.TestCase):
+
+ def test_window(self):
+ t = Table('t')
+ window = Window([t.c1, t.c2])
+
+ self.assertEqual(str(window), 'PARTITION BY "c1", "c2"')
+ self.assertEqual(window.params, ())
+
+ def test_window_order(self):
+ t = Table('t')
+ window = Window([t.c], order_by=t.c)
+
+ self.assertEqual(str(window), 'PARTITION BY "c" ORDER BY "c"')
+ self.assertEqual(window.params, ())
+
+ def test_window_range(self):
+ t = Table('t')
+ window = Window([t.c], frame='RANGE')
+
+ self.assertEqual(str(window),
+ 'PARTITION BY "c" RANGE '
+ 'BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW')
+ self.assertEqual(window.params, ())
+
+ window.start = -1
+ self.assertEqual(str(window),
+ 'PARTITION BY "c" RANGE '
+ 'BETWEEN 1 PRECEDING AND CURRENT ROW')
+ self.assertEqual(window.params, ())
+
+ window.start = 0
+ window.end = 1
+ self.assertEqual(str(window),
+ 'PARTITION BY "c" RANGE '
+ 'BETWEEN CURRENT ROW AND 1 FOLLOWING')
+ self.assertEqual(window.params, ())
+
+ window.start = 1
+ window.end = None
+ self.assertEqual(str(window),
+ 'PARTITION BY "c" RANGE '
+ 'BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING')
+ self.assertEqual(window.params, ())
+
+ def test_window_rows(self):
+ t = Table('t')
+ window = Window([t.c], frame='ROWS')
+
+ self.assertEqual(str(window),
+ 'PARTITION BY "c" ROWS '
+ 'BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW')
+ self.assertEqual(window.params, ())
--
python-sql
More information about the tryton-debian-vcs
mailing list