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 currency_rate_sql(cls):
"Return a SQL query with currency, rate, start_date and end_date"
pool = Pool()
Rate = pool.get('currency.currency.rate')
transaction = Transaction()
database = transaction.database
rate = Rate.__table__()
if database.has_window_functions():
window = Window([rate.currency],
order_by=[rate.date.asc],
frame='ROWS',
start=0,
end=1)
# Use NthValue instead of LastValue to get NULL for the last row
end_date = NthValue(rate.date, 2, window=window)
else:
next_rate = Rate.__table__()
end_date = next_rate.select(
next_rate.date,
where=(next_rate.currency == rate.currency)
& (next_rate.date > rate.date),
order_by=[next_rate.date.asc],
limit=1)
query = (rate.select(
rate.currency.as_('currency'),
rate.rate.as_('rate'),
rate.date.as_('start_date'),
end_date.as_('end_date'),
))
return query
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, ())
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_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, ))
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))
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 getter_latest_book(cls, authors, name):
result = {x.id: None for x in authors}
Book = Pool().get('library.book')
book = Book.__table__()
sub_book = Book.__table__()
cursor = Transaction().connection.cursor()
sub_query = sub_book.select(
sub_book.author,
Max(Coalesce(sub_book.publishing_date, datetime.date.min),
window=Window([sub_book.author])).as_('max_date'),
where=sub_book.author.in_([x.id for x in authors]))
cursor.execute(
*book.join(sub_query,
condition=(book.author == sub_query.author)
& (Coalesce(book.publishing_date, datetime.date.min) ==
sub_query.max_date)).select(book.author, book.id))
for author_id, book in cursor.fetchall():
result[author_id] = book
return result
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, ())
def table_query(cls):
pool = Pool()
Attendance = pool.get('attendance.line')
transaction = Transaction()
database = transaction.database
attendance = Attendance.__table__()
if database.has_window_functions():
window = Window([attendance.employee],
order_by=[attendance.at.asc],
frame='ROWS',
start=0,
end=1)
type = NthValue(attendance.type, 1, window=window)
from_ = NthValue(attendance.at, 1, window=window)
to = NthValue(attendance.at, 2, window=window)
date = NthValue(attendance.date, 1, window=window)
query = attendance.select(attendance.id.as_('id'),
attendance.company.as_('company'),
attendance.employee.as_('employee'),
type.as_('type'), from_.as_('from_'),
to.as_('to'), date.as_('date'))
sheet = (Min(query.id * 2,
window=Window([query.employee, query.date])))
else:
next_attendance = Attendance.__table__()
to = next_attendance.select(
next_attendance.at,
where=(next_attendance.employee == attendance.employee)
& (next_attendance.at > attendance.at),
order_by=[next_attendance.at.asc],
limit=1)
query = attendance.select(attendance.id.as_('id'),
attendance.company.as_('company'),
attendance.employee.as_('employee'),
attendance.type.as_('type'),
attendance.at.as_('from_'), to.as_('to'),
attendance.date.as_('date'))
query2 = copy.copy(query)
sheet = query2.select(Min(query2.id * 2),
where=(query2.employee == query.employee)
& (query2.date == query.date))
from_ = Column(query, 'from_')
if backend.name == 'sqlite':
# As SQLite does not support operation on datetime
# we convert datetime into seconds
duration = (SQLiteStrftime('%s', query.to) -
SQLiteStrftime('%s', from_))
else:
duration = query.to - from_
return query.select(
query.id.as_('id'),
Literal(0).as_('create_uid'),
CurrentTimestamp().as_('create_date'),
cls.write_uid.sql_cast(Literal(Null)).as_('write_uid'),
cls.write_date.sql_cast(Literal(Null)).as_('write_date'),
query.company.as_('company'),
query.employee.as_('employee'),
from_.as_('from_'),
query.to.as_('to'),
query.date.as_('date'),
duration.as_('duration'),
sheet.as_('sheet'),
where=query.type == 'in')
def table_query(cls):
pool = Pool()
Date = pool.get('ir.date')
Location = pool.get('stock.location')
Move = pool.get('stock.move')
Product = pool.get('product.product')
move = from_ = Move.__table__()
transaction = Transaction()
context = transaction.context
database = transaction.database
today = Date.today()
if context.get('product_template') is not None:
product_template = context['product_template']
if isinstance(product_template, int):
product_template = [product_template]
product = Product.__table__()
from_ = move.join(product, condition=move.product == product.id)
product_clause = product.template.in_(product_template or [-1])
product_column = Concat('product.template,', product.template)
else:
product = context.get('product', -1)
if product is None:
product = -1
if isinstance(product, int):
product = [product]
product_clause = move.product.in_(product or [-1])
product_column = Concat('product.product,', move.product)
if 'warehouse' in context:
warehouse = Location(context.get('warehouse'))
if context.get('stock_skip_warehouse'):
location_id = warehouse.storage_location.id
else:
location_id = warehouse.id
else:
location_id = -1
warehouse = With('id',
query=Location.search([
('parent', 'child_of', [location_id]),
],
query=True,
order=[]))
date_column = Coalesce(move.effective_date, move.planned_date)
quantity = Case((move.to_location.in_(warehouse.select(
warehouse.id)), move.internal_quantity),
else_=-move.internal_quantity)
if database.has_window_functions():
cumulative_quantity_delta = Sum(quantity,
window=Window(
[product_column, date_column],
order_by=[move.id.asc]))
else:
cumulative_quantity_delta = Literal(0)
return (from_.select(
move.id.as_('id'),
Literal(0).as_('create_uid'),
CurrentTimestamp().as_('create_date'),
Literal(None).as_('write_uid'),
Literal(None).as_('write_date'),
product_column.as_('product'),
date_column.as_('date'),
move.id.as_('move'),
move.origin.as_('origin'),
quantity.as_('quantity'),
cumulative_quantity_delta.as_('cumulative_quantity_delta'),
move.company.as_('company'),
where=product_clause
& ((move.from_location.in_(warehouse.select(warehouse.id))
& ~move.to_location.in_(warehouse.select(warehouse.id)))
| (~move.from_location.in_(warehouse.select(warehouse.id))
& move.to_location.in_(warehouse.select(warehouse.id))))
& ((date_column < today) & (move.state == 'done')
| (date_column >= today)),
with_=warehouse))
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(self):
t = Table('t')
window = Window([t.c1, t.c2])
self.assertEqual(str(window), 'PARTITION BY "c1", "c2"')
self.assertEqual(window.params, ())