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))