def _joins(cls):
pool = Pool()
Company = pool.get('company.company')
Currency = pool.get('currency.currency')
context = Transaction().context
tables = {}
company = context.get('company')
lines = cls._lines()
tables['line'] = line = Union(*(l(len(lines), i, company)
for i, l in enumerate(lines)))
tables['line.company'] = company = Company.__table__()
withs = {}
currency_sale = With(query=Currency.currency_rate_sql())
withs['currency_sale'] = currency_sale
currency_company = With(query=Currency.currency_rate_sql())
withs['currency_company'] = currency_company
from_item = (line.join(
currency_sale,
condition=(line.currency == currency_sale.currency)
& (currency_sale.start_date <= line.date)
&
((currency_sale.end_date == Null)
| (currency_sale.end_date >= line.date))).join(
company, condition=line.company == company.id).join(
currency_company,
condition=(company.currency == currency_company.currency)
& (currency_company.start_date <= line.date)
& ((currency_company.end_date == Null)
| (currency_company.end_date >= line.date))))
return from_item, tables, withs
def test_with(self):
w = With(query=self.table.select(self.table.c1))
query = w.select(with_=[w])
self.assertEqual(str(query),
'WITH "a" AS (SELECT "b"."c1" FROM "t" AS "b") '
'SELECT * FROM "a" AS "a"')
self.assertEqual(query.params, ())
def test_with(self):
w = With(query=self.table.select(self.table.c1))
query = w.select(with_=[w])
self.assertEqual(
str(query), 'WITH "a" AS (SELECT "b"."c1" FROM "t" AS "b") '
'SELECT * FROM "a" AS "a"')
self.assertEqual(query.params, ())
def test_with_columns(self):
with AliasManager():
second = With('a', query=self.table.select(self.table.a))
self.assertEqual(second.statement(),
'"a"("a") AS ('
'SELECT "b"."a" FROM "t" AS "b"'
')')
self.assertEqual(second.statement_params(), ())
def test_with_columns(self):
with AliasManager():
second = With('a', query=self.table.select(self.table.a))
self.assertEqual(
second.statement(), '"a" ("a") AS ('
'SELECT "b"."a" FROM "t" AS "b"'
')')
self.assertEqual(second.statement_params(), ())
def group_parent_all_cte(cls):
group = cls.__table__()
parents = With('id', 'parent', recursive=True)
parents.query = group.select(group.id, group.parent)
parents.query |= group.select(group.id, group.id)
parents.query |= (group
.join(parents, condition=group.parent == parents.id)
.select(group.id, parents.parent))
return parents
def test_with(self):
t1 = Table('t1')
w = With(query=t1.select(t1.c1))
query = self.table.delete(with_=[w],
where=self.table.c2.in_(w.select(w.c3)))
self.assertEqual(str(query),
'WITH a AS (SELECT "b"."c1" FROM "t1" AS "b") '
'DELETE FROM "t" WHERE ("c2" IN (SELECT "a"."c3" FROM a AS "a"))')
self.assertEqual(query.params, ())
def test_with(self):
with AliasManager():
simple = With(query=self.table.select(self.table.id,
where=self.table.id == 1))
self.assertEqual(simple.statement(),
'"a" AS ('
'SELECT "b"."id" FROM "t" AS "b" WHERE ("b"."id" = %s)'
')')
self.assertEqual(simple.statement_params(), (1,))
def test_with(self):
with AliasManager():
simple = With(query=self.table.select(self.table.id,
where=self.table.id == 1))
self.assertEqual(
simple.statement(), '"a" AS ('
'SELECT "b"."id" FROM "t" AS "b" WHERE ("b"."id" = %s)'
')')
self.assertEqual(simple.statement_params(), (1, ))
def test_with(self):
t1 = Table('t1')
w = With(query=t1.select())
query = self.table.insert([self.table.c1],
with_=[w],
values=w.select())
self.assertEqual(
str(query), 'WITH "a" AS (SELECT * FROM "t1" AS "b") '
'INSERT INTO "t" ("c1") SELECT * FROM "a" AS "a"')
self.assertEqual(query.params, ())
def test_with(self):
t1 = Table('t1')
w = With(query=t1.select(t1.c1))
query = self.table.delete(with_=[w],
where=self.table.c2.in_(w.select(w.c3)))
self.assertEqual(str(query),
'WITH "a" AS (SELECT "b"."c1" FROM "t1" AS "b") '
'DELETE FROM "t" WHERE '
'("c2" IN (SELECT "a"."c3" FROM "a" AS "a"))')
self.assertEqual(query.params, ())
def test_with(self):
t1 = Table('t1')
w = With(query=t1.select())
query = self.table.insert(
[self.table.c1],
with_=[w],
values=w.select())
self.assertEqual(str(query),
'WITH "a" AS (SELECT * FROM "t1" AS "b") '
'INSERT INTO "t" ("c1") SELECT * FROM "a" AS "a"')
self.assertEqual(query.params, ())
def test_with(self):
t1 = Table('t1')
w = With(query=t1.select(t1.c1))
query = self.table.update([self.table.c2],
with_=[w],
values=[w.select(w.c3, where=w.c4 == 2)])
self.assertEqual(
str(query), 'WITH "b" AS (SELECT "c"."c1" FROM "t1" AS "c") '
'UPDATE "t" SET "c2" = (SELECT "b"."c3" FROM "b" AS "b" '
'WHERE ("b"."c4" = %s))')
self.assertEqual(query.params, (2, ))
def test_with(self):
t1 = Table('t1')
w = With(query=t1.select(t1.c1))
query = self.table.update(
[self.table.c2],
with_=[w],
values=[w.select(w.c3, where=w.c4 == 2)])
self.assertEqual(str(query),
'WITH b AS (SELECT "c"."c1" FROM "t1" AS "c") '
'UPDATE "t" SET "c2" = (SELECT "b"."c3" FROM b AS "b" '
'WHERE ("b"."c4" = %s))')
self.assertEqual(query.params, (2,))
def test_recursive(self):
upto10 = With('n', recursive=True)
upto10.query = Values([(1, )])
upto10.query |= upto10.select(upto10.n + Literal(1),
where=upto10.n < Literal(100))
upto10.query.all_ = True
q = upto10.select(with_=[upto10])
self.assertEqual(
str(q), 'WITH RECURSIVE "a" ("n") AS ('
'VALUES (%s) '
'UNION ALL '
'SELECT ("a"."n" + %s) FROM "a" AS "a" WHERE ("a"."n" < %s)'
') SELECT * FROM "a" AS "a"')
self.assertEqual(q.params, (1, 1, 100))
def test_recursive(self):
upto10 = With('n', recursive=True)
upto10.query = Values([(1,)])
upto10.query |= upto10.select(
upto10.n + Literal(1),
where=upto10.n < Literal(100))
upto10.query.all_ = True
q = upto10.select(with_=[upto10])
self.assertEqual(str(q),
'WITH RECURSIVE "a"("n") AS ('
'VALUES (%s) '
'UNION ALL '
'SELECT ("a"."n" + %s) FROM "a" AS "a" WHERE ("a"."n" < %s)'
') SELECT * FROM "a" AS "a"')
self.assertEqual(q.params, (1, 1, 100))
def test_with_query(self):
with AliasManager():
simple = With()
simple.query = self.table.select(self.table.id,
where=self.table.id == 1)
second = With()
second.query = simple.select()
wq = WithQuery(with_=[simple, second])
self.assertEqual(wq._with_str(),
'WITH "a" AS ('
'SELECT "b"."id" FROM "t" AS "b" WHERE ("b"."id" = %s)'
'), "c" AS ('
'SELECT * FROM "a" AS "a"'
') ')
self.assertEqual(wq._with_params(), (1,))
def table_query(cls):
pool = Pool()
Move = pool.get('stock.move')
Location = pool.get('stock.location')
Product = pool.get('product.product')
Date = pool.get('ir.date')
move = from_ = Move.__table__()
context = Transaction().context
today = Date.today()
if context.get('product_template') is not None:
product = Product.__table__()
from_ = move.join(product, condition=move.product == product.id)
product_clause = (product.template == context['product_template'])
else:
product_clause = move.product == context.get('product', -1)
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)
return (from_.select(
Max(move.id).as_('id'),
Literal(0).as_('create_uid'),
CurrentTimestamp().as_('create_date'),
Literal(None).as_('write_uid'),
Literal(None).as_('write_date'),
date_column.as_('date'),
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)),
group_by=(date_column, move.product, move.company),
with_=warehouse)
| Select([
Literal(0).as_('id'),
Literal(0).as_('create_uid'),
CurrentTimestamp().as_('create_date'),
Literal(None).as_('write_uid'),
Literal(None).as_('write_date'),
Literal(today).as_('date'),
Literal(context.get('company', -1)).as_('company'),
]))
def convert_domain_tree(self, domain, tables):
Target = self.get_target()
target = Target.__table__()
table, _ = tables[None]
name, operator, ids = domain
red_sql = reduce_ids(target.id, (i for i in ids if i is not None))
if operator.endswith('child_of'):
tree = With('id', recursive=True)
tree.query = target.select(target.id, where=red_sql)
tree.query |= (target
.join(tree, condition=Column(target, name) == tree.id)
.select(target.id))
else:
tree = With('id', name, recursive=True)
tree.query = target.select(
target.id, Column(target, name), where=red_sql)
tree.query |= (target
.join(tree, condition=target.id == Column(tree, name))
.select(target.id, Column(target, name)))
expression = table.id.in_(tree.select(tree.id, with_=[tree]))
if operator.startswith('not'):
return ~expression
return expression
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 _joins(cls):
pool = Pool()
Company = pool.get('company.company')
Currency = pool.get('currency.currency')
Move = pool.get('stock.move')
Location = pool.get('stock.location')
tables = {}
tables['move'] = move = Move.__table__()
tables['move.company'] = company = Company.__table__()
tables['move.company.currency'] = currency = Currency.__table__()
tables['move.from_location'] = from_location = Location.__table__()
tables['move.to_location'] = to_location = Location.__table__()
withs = {}
withs['currency_rate'] = currency_rate = With(
query=Currency.currency_rate_sql())
withs['currency_rate_company'] = currency_rate_company = With(
query=Currency.currency_rate_sql())
from_item = (move.join(
currency_rate,
condition=(move.currency == currency_rate.currency)
& (currency_rate.start_date <= move.effective_date)
& ((currency_rate.end_date == Null)
| (currency_rate.end_date >= move.effective_date))
).join(company, condition=move.company == company.id).join(
currency, condition=company.currency == currency.id).join(
currency_rate_company,
condition=(company.currency == currency_rate_company.currency)
& (currency_rate_company.start_date <= move.effective_date)
& ((currency_rate_company.end_date == Null)
| (currency_rate_company.end_date >= move.effective_date))
).join(from_location,
condition=(move.from_location == from_location.id)).join(
to_location,
condition=(move.to_location == to_location.id)))
return from_item, tables, withs
def test_with_query(self):
with AliasManager():
simple = With()
simple.query = self.table.select(self.table.id,
where=self.table.id == 1)
second = With()
second.query = simple.select()
wq = WithQuery(with_=[simple, second])
self.assertEqual(
wq._with_str(), 'WITH "a" AS ('
'SELECT "b"."id" FROM "t" AS "b" WHERE ("b"."id" = %s)'
'), "c" AS ('
'SELECT * FROM "a" AS "a"'
') ')
self.assertEqual(wq._with_params(), (1, ))
def pull(cls, database, connection, name=None):
cursor = connection.cursor()
queue = cls.__table__()
candidates = With('id', 'scheduled_at', 'expected_at',
query=queue.select(
queue.id,
queue.scheduled_at,
queue.expected_at,
where=((queue.name == name) if name else Literal(True))
& (queue.dequeued_at == Null),
order_by=[
queue.scheduled_at.nulls_first,
queue.expected_at.nulls_first]))
selected = With('id', query=candidates.select(
candidates.id,
where=((candidates.scheduled_at <= CurrentTimestamp())
| (candidates.scheduled_at == Null))
& database.lock_id(candidates.id),
order_by=[
candidates.scheduled_at.nulls_first,
candidates.expected_at.nulls_first],
limit=1))
next_timeout = With('seconds', query=candidates.select(
Min(Extract('second',
candidates.scheduled_at - CurrentTimestamp())
),
where=candidates.scheduled_at >= CurrentTimestamp()))
task_id, seconds = None, None
if database.has_returning():
query = queue.update([queue.dequeued_at], [CurrentTimestamp()],
where=queue.id == selected.select(selected.id),
with_=[candidates, selected, next_timeout],
returning=[
queue.id, next_timeout.select(next_timeout.seconds)])
cursor.execute(*query)
row = cursor.fetchone()
if row:
task_id, seconds = row
else:
query = queue.select(queue.id,
where=queue.id == selected.select(selected.id),
with_=[candidates, selected])
cursor.execute(*query)
row = cursor.fetchone()
if row:
task_id, = row
query = queue.update([queue.dequeued_at], [CurrentTimestamp()],
where=queue.id == task_id)
cursor.execute(*query)
query = next_timeout.select(next_timeout.seconds)
cursor.execute(*query)
row = cursor.fetchone()
if row:
seconds, = row
if not task_id and database.has_channel():
cursor.execute('LISTEN "%s"', (cls.__name__,))
return task_id, seconds
def table_query(cls):
pool = Pool()
Move = pool.get('stock.move')
Location = pool.get('stock.location')
Product = pool.get('product.product')
Date = pool.get('ir.date')
move = from_ = Move.__table__()
context = Transaction().context
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)
products = [('product.template', i) for i in product_template]
else:
product = context.get('product')
if product is None:
product = []
if isinstance(product, int):
product = [product]
product_clause = move.product.in_(product or [-1])
product_column = Concat('product.product,', move.product)
products = [('product.product', i) for i in 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)
query = (from_.select(
Max(move.id * 3).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.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)),
group_by=(date_column, product_column, move.company),
with_=warehouse))
for model, id_ in products:
gap = ['product.template', 'product.product'].index(model) + 1
query |= Select([
Literal(id_ * 3 + gap).as_('id'),
Literal(0).as_('create_uid'),
CurrentTimestamp().as_('create_date'),
Literal(None).as_('write_uid'),
Literal(None).as_('write_date'),
Literal('%s,%s' % (model, id_)).as_('product'),
Literal(today).as_('date'),
Literal(context.get('company', -1)).as_('company'),
])
return query
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))
