runloopable / platform /harness /canonical_source.py
fsanyoto's picture
Deploy AIOS web (React glide grid + FastAPI slice)
812ec21 verified
Raw History Blame Contribute Delete
56.7 kB
"""Bounded, read-only operands for the existing compiled customer and product projections.""" import contextvars import itertools import json import logging import re import uuid from contextlib import contextmanager from dataclasses import dataclass from core import odoo as _odoo BATCH = 1024 _ACTIVE = contextvars.ContextVar('canonical_source', default=None) _LOG = logging.getLogger('canonical_source') def code_sql(alias): raw = f"json_extract_string({alias}.body, '/default_code')" return (f"CASE WHEN {raw} IS NULL OR {raw} = '' OR json_type({alias}.body, '/default_code') = 'BOOLEAN' " f"THEN 'pid:' || json_extract_string({alias}.body, '/id') ELSE trim({raw}) END") class TierProjection(list): """Retain displayed tiers and the complete overflow receipt from SQL.""" def __init__(self): super().__init__() self.overflow = {} class Rows: """Reiterate a SQL snapshot without retaining its Python records.""" def __init__(self, owner, table): self.owner, self.table = owner, table def __iter__(self): last = -1 while True: batch = self.owner.cur.execute( f'SELECT ordinal, body FROM "{self.table}" WHERE ordinal > ? ORDER BY ordinal LIMIT ?', [last, BATCH]).fetchall() if not batch: return for _, body in batch: yield json.loads(body) last = batch[-1][0] batch.clear() def __len__(self): return self.owner.cur.execute(f'SELECT count(*) FROM "{self.table}"').fetchone()[0] def __bool__(self): return bool(len(self)) @dataclass(frozen=True) class RowIds: """A SQL-backed selection of related identities from one source snapshot.""" rows: Rows path: str def sql(self): return (f'SELECT DISTINCT try_cast(json_extract_string(body, \'{self.path}\') AS BIGINT) id ' f'FROM "{self.rows.table}" WHERE id IS NOT NULL') class Source: """Own bounded source operands and release every temporary snapshot on exit.""" def __init__(self, cur, kind, identities, store=None): self.cur, self.kind, self.identities, self.store = cur, kind, list(identities), store self.tables = [] self.cache = {} self.failure = None def snapshot(self, records): name = 'canonical_' + uuid.uuid4().hex self.cur.execute(f'CREATE TEMP TABLE "{name}" (ordinal BIGINT, body JSON)') self.tables.append(name) records, offset = iter(records), 0 try: while True: batch = list(itertools.islice(records, BATCH)) if not batch: break self.cur.execute(f'INSERT INTO "{name}" SELECT unnest(?::BIGINT[]), unnest(?::JSON[])', [list(range(offset, offset + len(batch))), [json.dumps(r, default=str) for r in batch]]) offset += len(batch) batch.clear() except BaseException as exc: self.failure = exc raise finally: close = getattr(records, 'close', None) if close: close() return Rows(self, name) def domain(self, model, domain): domain = list(domain or []) if self.kind == 'customer': field = {'res.partner': 'id', 'sale.order': 'partner_id', 'sale.order.line': 'order_partner_id', 'account.move': 'partner_id', 'account.move.line': 'partner_id'}.get(model) # Explicit partner lookup domains also resolve agents and must retain their ids. if model == 'res.partner' and any(isinstance(d, (tuple, list)) and d[0] in {'id', 'name'} for d in domain): field = None if field: domain.append((field, 'in', self.identities)) elif self.kind == 'product' and model == 'product.product': # Match the canonical stripped code, including every duplicate and archived variant. codes = list(self.identities) ids = [int(c[4:]) for c in self.identities if c.startswith('pid:') and c[4:].isdigit()] terms = [('default_code', '=like', '%' + code + '%') for code in codes] terms.append(('id', 'in', ids)) domain += ['|'] * (len(terms) - 1) + terms elif self.kind == 'product' and model in {'sale.order.line', 'stock.quant'}: codes = list(self.identities) ids = [int(c[4:]) for c in self.identities if c.startswith('pid:') and c[4:].isdigit()] terms = [('product_id.default_code', '=like', '%' + code + '%') for code in codes] terms.append(('product_id', 'in', ids)) domain += ['|'] * (len(terms) - 1) + terms return domain def products(self): key = ('products',) if key not in self.cache: self.cache[key] = self.search_read('product.product', [('active', 'in', [True, False])], ['id', 'default_code', 'categ_id', 'type', 'standard_price', 'name', 'active']) return self.cache[key] def related_read(self, model, selections, fields, domain=()): """Page related source reads by selected identities without retaining the population in Python.""" def records(): for field, ids, extra in selections: last = -1 while True: page = [row[0] for row in self.cur.execute( f'SELECT id FROM ({ids.sql()}) selected WHERE id > ? ORDER BY id LIMIT ?', [last, BATCH]).fetchall()] if not page: break selected = self.search_read(model, [*domain, *extra, (field, 'in', page)], fields) try: yield from selected finally: self.cur.execute(f'DROP TABLE "{selected.table}"') self.tables.remove(selected.table) last = page[-1] return self.snapshot(records()) def category_rows(self, products): return self.related_read('product.category', [('id', RowIds(products, '/categ_id/0'), [])], ['id', 'complete_name']) def sku_revenue(self, groups): products = self.products() code = code_sql('p') rows = self.cur.execute(f"""SELECT {code} code, first(json_extract_string(g.body, '/product_id/1') ORDER BY g.ordinal), sum(coalesce(try_cast(json_extract_string(g.body, '/price_subtotal') AS DOUBLE), 0)), sum(coalesce(try_cast(json_extract_string(g.body, '/product_uom_qty') AS DOUBLE), 0)), count(DISTINCT nullif(json_extract_string(g.body, '/order_id/0'), '0')) FROM "{groups.table}" g JOIN "{products.table}" p ON json_extract_string(g.body, '/product_id/0') = json_extract_string(p.body, '/id') WHERE json_extract_string(g.body, '/product_id/0') IS NOT NULL GROUP BY code""").fetchall() return {code: {'name': name, 'rev': rev, 'qty': qty, 'orders': orders} for code, name, rev, qty, orders in rows} def customer_mix(self, groups): def products(): last = None while True: ids = [r[0] for r in self.cur.execute(f"""SELECT DISTINCT try_cast(json_extract_string(body, '/product_id/0') AS BIGINT) id FROM "{groups.table}" WHERE id IS NOT NULL AND (? IS NULL OR id > ?) ORDER BY id LIMIT ?""", [last, last, BATCH]).fetchall()] if not ids: return yield from odoo.search_read('product.product', [('id', 'in', ids)], ['id', 'name', 'categ_id'], limit=BATCH, order='id asc') last = ids[-1] prods = self.snapshot(products()) query = f"""WITH lines AS ( SELECT try_cast(json_extract_string(g.body, '/order_partner_id/0') AS BIGINT) pid, json_extract_string(g.body, '/product_id/0') product, coalesce(nullif(trim(list_last(string_split(json_extract_string(p.body, '/categ_id/1'), ' / '))), ''), '(none)') cat, coalesce(try_cast(json_extract_string(g.body, '/price_subtotal') AS DOUBLE), 0) val, g.ordinal, json_extract_string(p.body, '/name') AS product_name FROM "{groups.table}" g LEFT JOIN "{prods.table}" p ON json_extract_string(g.body, '/product_id/0') = json_extract_string(p.body, '/id') WHERE json_extract_string(g.body, '/product_id/0') IS NOT NULL), cats AS (SELECT pid, cat, sum(val) val, min(ordinal) ordinal FROM lines GROUP BY pid, cat), topcat AS (SELECT *, row_number() OVER (PARTITION BY pid ORDER BY val DESC, ordinal) rn FROM cats), skus AS (SELECT pid, product, first(product_name ORDER BY ordinal) AS product_name, sum(val) val, min(ordinal) ordinal FROM lines GROUP BY pid, product), topsku AS (SELECT *, row_number() OVER (PARTITION BY pid ORDER BY val DESC, ordinal) rn FROM skus), totals AS (SELECT pid, count(DISTINCT product) n, sum(val) total FROM lines GROUP BY pid) SELECT t.pid, c.cat, CASE WHEN t.total != 0 THEN c.val / t.total END, t.n, coalesce(s.product_name, '(none)') FROM totals t JOIN topcat c ON t.pid=c.pid AND c.rn=1 JOIN topsku s ON t.pid=s.pid AND s.rn=1""" return {pid: {'top_category': cat, 'top_category_pct': pct, 'sku_count': n, 'top_sku': name} for pid, cat, pct, n, name in self.cur.execute(query).fetchall()} def categories(self, keep_blank_code=False): rows = self.products() cats = self.category_rows(rows) blank = '' if keep_blank_code else "AND json_extract_string(p.body, '/default_code') != ''" return {code: category for code, category in self.cur.execute(f"""SELECT {code_sql('p')} code, first(coalesce(nullif(trim(list_extract(string_split(json_extract_string(c.body, '/complete_name'), '/'), 2)), ''), '(uncategorized)') ORDER BY p.ordinal) FROM "{rows.table}" p LEFT JOIN "{cats.table}" c ON json_extract_string(p.body, '/categ_id/0')=json_extract_string(c.body, '/id') WHERE json_extract_string(p.body, '/active') = 'true' AND json_type(p.body, '/default_code') = 'VARCHAR' {blank} GROUP BY code""").fetchall()} def backlog(self, groups): products = self.products() return {code: qty for code, qty in self.cur.execute(f"""WITH per AS ( SELECT json_extract_string(body, '/product_id/0') id, greatest(0, coalesce(try_cast(json_extract_string(body, '/product_uom_qty') AS DOUBLE), 0) - coalesce(try_cast(json_extract_string(body, '/qty_delivered') AS DOUBLE), 0)) qty FROM "{groups.table}") SELECT {code_sql('p')} code, sum(per.qty) FROM per JOIN "{products.table}" p ON per.id=json_extract_string(p.body, '/id') WHERE per.id IS NOT NULL AND per.qty > 1e-9 GROUP BY code""").fetchall()} def inventory_frame(self, t): from core import periods from modules import inventory import pandas as pd lf, lt = periods.ltm(t) quant = self.read_group('stock.quant', [('location_id.usage', '=', 'internal')], ['product_id', 'quantity:sum'], ['product_id'], lazy=False) sales = self.read_group('sale.order.line', _odoo.sale_line_domain(lf, lt, all_channels=True), ['product_id', 'price_subtotal:sum', 'product_uom_qty:sum'], ['product_id'], lazy=False) products = self.search_read('product.product', [('default_code', '!=', False)], ['id', 'default_code', 'name', 'categ_id', 'standard_price', 'sale_ok', 'type']) cats = self.category_rows(products) query = f"""WITH raw AS (SELECT p.ordinal, trim(json_extract_string(p.body, '/default_code')) sku, json_extract_string(p.body, '/name') AS product_name, coalesce(nullif(trim(list_extract(string_split(json_extract_string(c.body, '/complete_name'), '/'), 2)), ''), '(uncategorized)') category, json_extract_string(p.body, '/type') = 'product' storable, coalesce(try_cast(json_extract_string(p.body, '/standard_price') AS DOUBLE), 0) AS cost, coalesce(try_cast(json_extract_string(q.body, '/quantity') AS DOUBLE), 0) on_hand, coalesce(try_cast(json_extract_string(s.body, '/product_uom_qty') AS DOUBLE), 0) qty_ltm, coalesce(try_cast(json_extract_string(s.body, '/price_subtotal') AS DOUBLE), 0) rev_ltm FROM "{products.table}" p LEFT JOIN "{quant.table}" q ON json_extract_string(p.body, '/id')=json_extract_string(q.body, '/product_id/0') LEFT JOIN "{sales.table}" s ON json_extract_string(p.body, '/id')=json_extract_string(s.body, '/product_id/0') LEFT JOIN "{cats.table}" c ON json_extract_string(p.body, '/categ_id/0')=json_extract_string(c.body, '/id')) SELECT sku, first(product_name ORDER BY ordinal) AS name, first(category ORDER BY ordinal) category, bool_or(storable) storable, sum(on_hand) on_hand, sum(on_hand*cost) inv_value, sum(qty_ltm*cost) cogs_ltm, sum(qty_ltm) qty_ltm, sum(rev_ltm) rev_ltm FROM raw GROUP BY sku""" result = self.cur.execute(query) names = [d[0] for d in result.description] return pd.DataFrame(result.fetchall(), columns=names), (lf, lt) def tiers(self, prods): from core import periods from modules import products prods = products.active_products() if prods is None else prods today = periods.today().isoformat() selections = [('product_id', RowIds(prods, '/id'), [('applied_on', '=', '0_product_variant')]), ('product_tmpl_id', RowIds(prods, '/product_tmpl_id/0'), [('applied_on', '=', '1_product')])] rules = self.related_read('product.pricelist.item', selections, ['pricelist_id', 'product_id', 'product_tmpl_id', 'applied_on', 'fixed_price', 'packaging_id', 'packaging_price'], [('compute_price', '=', 'fixed'), '|', ('date_start', '=', False), ('date_start', '<=', today), '|', ('date_end', '=', False), ('date_end', '>=', today), ('fixed_price', '>', 0)]) lists = self.related_read('product.pricelist', [('id', RowIds(rules, '/pricelist_id/0'), [])], ['id', 'name']) normalized = self.snapshot(dict(r, canonical_price=round(r.get('packaging_price') or r.get('fixed_price') or 0.0, 2), canonical_unit=str(_odoo.m2o_name(r.get('packaging_id')) or '').strip()) for r in rules) declared = sorted({name for _p, _u, name, _n in products.PRICELIST_TIER_COLUMNS}) # Two equality joins and an anti-join: an OR join with a correlated NOT EXISTS is a nested loop # (234 s for the whole catalogue on the staging mirror). query = f"""WITH pp AS ( SELECT {code_sql('p')} code, p.ordinal product_order, json_extract_string(p.body, '/id') pid, json_extract_string(p.body, '/product_tmpl_id/0') tmpl FROM "{prods.table}" p), rr AS ( SELECT try_cast(json_extract_string(r.body, '/id') AS BIGINT) rule_order, json_extract_string(r.body, '/applied_on') applied_on, json_extract_string(r.body, '/product_id/0') rpid, json_extract_string(r.body, '/product_tmpl_id/0') rtmpl, json_extract_string(r.body, '/pricelist_id/0') plid, try_cast(json_extract_string(r.body, '/canonical_price') AS DOUBLE) price, json_extract_string(r.body, '/canonical_unit') unit FROM "{normalized.table}" r), ll AS (SELECT json_extract_string(body, '/id') plid, trim(json_extract_string(body, '/name')) pricelist FROM "{lists.table}"), shadow AS (SELECT DISTINCT plid, rpid FROM rr WHERE applied_on='0_product_variant'), eligible AS ( SELECT pp.code, pp.product_order, rr.rule_order, ll.pricelist, rr.price, rr.unit FROM pp JOIN rr ON rr.applied_on='0_product_variant' AND rr.rpid=pp.pid JOIN ll ON ll.plid=rr.plid UNION ALL SELECT pp.code, pp.product_order, rr.rule_order, ll.pricelist, rr.price, rr.unit FROM pp JOIN rr ON rr.applied_on='1_product' AND rr.rtmpl=pp.tmpl JOIN ll ON ll.plid=rr.plid ANTI JOIN shadow ON shadow.plid=rr.plid AND shadow.rpid=pp.pid), identified AS (SELECT *, min(product_order) OVER (PARTITION BY code) first_product, row_number() OVER (PARTITION BY code, pricelist, price, unit ORDER BY product_order, rule_order) ident FROM eligible), merged AS (SELECT * FROM identified WHERE product_order=first_product OR ident=1), ranked AS (SELECT *, row_number() OVER (PARTITION BY code ORDER BY price, lower(unit), lower(pricelist), product_order, rule_order) pooled, row_number() OVER (PARTITION BY code, pricelist ORDER BY price, lower(unit), product_order, rule_order) tier, count(*) OVER (PARTITION BY code, pricelist) n FROM merged) SELECT code, pricelist, price, unit, n FROM ranked WHERE pooled <= 3 OR (pricelist IN (SELECT unnest(?::VARCHAR[])) AND tier <= ?) ORDER BY code, price, lower(unit), lower(pricelist), product_order, rule_order""" result = self.cur.execute(query, [declared, products.PRICELIST_TIER_DEPTH]) out = {} while True: batch = result.fetchmany(BATCH) if not batch: break for code, name, price, unit, n in batch: entry = out.setdefault(code, TierProjection()) entry.append({'pricelist': name, 'unit_price': price, 'unit': unit}) if name in declared and n > products.PRICELIST_TIER_DEPTH: entry.overflow[name] = n - products.PRICELIST_TIER_DEPTH return out, self.tier_report() def tier_report(self): return {'bounded': True, 'rules_total': self.search_count('product.pricelist.item', []), 'rules_not_fixed': self.search_count('product.pricelist.item', [('compute_price', '!=', 'fixed')])} def search_read(self, model, domain=None, fields=None, **kw): domain = self.domain(model, domain) # Keyset traversal avoids a connector's default limit and never silently caps connected rows. kw = {k: v for k, v in kw.items() if k not in {'limit', 'offset', 'order'}} def records(): last = 0 while True: page = _odoo.search_read(model, domain + [('id', '>', last)], list(dict.fromkeys(['id', *(['default_code'] if self.kind == 'product' and model == 'product.product' else []), *(fields or [])])), limit=BATCH, order='id asc', **kw) if not page: return nxt = max(int(r['id']) for r in page) if nxt <= last: raise RuntimeError('canonical_source_invalid: source keyset did not advance') if self.kind == 'product' and model == 'product.product': selected = set(self.identities) for row in page: code = str(row['default_code']).strip() if row.get('default_code') else f"pid:{row['id']}" if code in selected: yield row else: yield from page last = nxt page.clear() return self.snapshot(records()) def read_group(self, model, domain, fields, groupby, **kw): domain = self.domain(model, domain) kw = {k: v for k, v in kw.items() if k not in {'limit', 'offset'}} def records(): offset = 0 while True: options = dict(kw, offset=offset, limit=BATCH) if groupby: options.setdefault('orderby', ','.join(groupby)) page = _odoo.get_odoo().execute_kw(model, 'read_group', [domain, fields, groupby], options) if not page: return for row in page: yield {key: value for key, value in row.items() if not key.startswith('__') or key == '__count'} offset += len(page) page.clear() return self.snapshot(records()) def search_count(self, model, domain=None, **kw): if self.kind == 'product' and model == 'product.product': return len(self.search_read(model, domain, ['id', 'default_code'])) return _odoo.get_odoo().search_count(model, self.domain(model, domain), **kw) def agent_members(self, name): if name == '(none)': return self.search_read('res.partner', [('customer_rank', '>', 0), ('agent_ids', '=', False), ('active', 'in', [True, False])], ['id']) agents = self.search_read('res.partner', [('name', '=', name), ('active', 'in', [True, False])], ['id']) def records(): source = iter(agents) while True: batch = list(itertools.islice(source, BATCH)) if not batch: return rows = self.search_read('res.partner', [('agent_ids', 'in', [r['id'] for r in batch]), ('active', 'in', [True, False])], ['id']) try: yield from rows finally: self.cur.execute(f'DROP TABLE IF EXISTS "{rows.table}"') self.tables.remove(rows.table) return self.snapshot(records()) def close(self): for table in reversed(self.tables): self.cur.execute(f'DROP TABLE IF EXISTS "{table}"') self.tables.clear() self.cache.clear() #: Tables a product pool needs before it may read the mirror instead of Odoo. MIRROR_TABLES = ('product_product', 'stock_quant', 'stock_location', 'sale_order', 'sale_order_line', 'res_partner') CUSTOMER_MIRROR_TABLES = ('product_product', 'sale_order', 'sale_order_line', 'res_partner', 'account_move', 'account_move_line', 'account_account') CUSTOMER_PARTNER_FIELDS = ('property_payment_term_id', 'property_product_pricelist', 'create_date', 'partner_latitude', 'partner_longitude', 'category_source_ids') _MIRROR_MODELS = { 'product.product': 'product_product', 'sale.order.line': 'sale_order_line', 'sale.order': 'sale_order', 'stock.quant': 'stock_quant', 'stock.location': 'stock_location', 'res.partner': 'res_partner', 'account.move': 'account_move', 'account.move.line': 'account_move_line', 'account.account': 'account_account', 'res.partner.category': ('(SELECT DISTINCT try_cast(ids.value AS BIGINT) AS id, ' 'json_extract_string(names.value, \'$\') AS name FROM res_partner p, ' 'json_each(p.category_source_ids) ids, json_each(p.category_id) names ' 'WHERE ids.key = names.key)'), 'product.pricelist': 'product_pricelist', 'product.pricelist.item': 'product_pricelist_item', 'product.tag': 'product_tag', # product.category is not synced: its id and complete_name ride on product_product. 'product.category': ('(SELECT DISTINCT categ_id AS id, categ_name AS complete_name ' 'FROM product_product WHERE categ_id IS NOT NULL)'), } _CUSTOMER_MIRROR_MODELS = {'account.move', 'account.move.line', 'account.account', 'res.partner.category'} # Every many2one hop must be declared before a mirrored domain can cross it. _MIRROR_RELATIONS = { ('sale.order.line', 'order_id'): 'sale.order', ('sale.order.line', 'product_id'): 'product.product', ('sale.order.line', 'order_partner_id'): 'res.partner', ('stock.quant', 'product_id'): 'product.product', ('stock.quant', 'location_id'): 'stock.location', ('sale.order', 'partner_id'): 'res.partner', ('account.move.line', 'account_id'): 'account.account', ('account.move.line', 'move_id'): 'account.move', ('account.move.line', 'partner_id'): 'res.partner', ('account.move', 'partner_id'): 'res.partner', } # Odoo's line state is the order's state, and the mirror stores it only on sale_order. _MIRROR_ALIASES = {('sale.order.line', 'state'): 'order_id.state', ('res.partner', 'category_id'): 'category_source_ids', ('res.partner', 'property_product_pricelist'): 'property_product_pricelist_id', ('account.move.line', 'invoice_date'): 'move_id.invoice_date'} _NUMERIC = ('DOUBLE', 'FLOAT', 'REAL', 'INTEGER', 'BIGINT', 'SMALLINT', 'TINYINT', 'HUGEINT', 'UBIGINT', 'UINTEGER', 'USMALLINT', 'UTINYINT', 'DECIMAL') _CODE_FIELD = '__code__' #: The product pool's mirror contract, read as declared even before `datastore.ENTITIES` names it. _DECLARED = { 'product_product': { 'create_date': 'VARCHAR', 'product_tmpl_id': 'BIGINT', 'product_tmpl_name': 'VARCHAR', 'volume': 'DOUBLE', 'display_name': 'VARCHAR', 'product_tag_ids': 'VARCHAR', 'incoming_qty': 'DOUBLE', 'free_qty': 'DOUBLE', 'virtual_available': 'DOUBLE'}, 'product_pricelist': {'id': 'BIGINT', 'name': 'VARCHAR'}, 'product_pricelist_item': { 'id': 'BIGINT', 'pricelist_id': 'BIGINT', 'pricelist_name': 'VARCHAR', 'product_id': 'BIGINT', 'product_name': 'VARCHAR', 'product_tmpl_id': 'BIGINT', 'product_tmpl_name': 'VARCHAR', 'applied_on': 'VARCHAR', 'compute_price': 'VARCHAR', 'fixed_price': 'DOUBLE', 'min_quantity': 'DOUBLE', 'date_start': 'VARCHAR', 'date_end': 'VARCHAR', 'packaging_id': 'BIGINT', 'packaging_name': 'VARCHAR', 'packaging_price': 'DOUBLE'}, 'product_tag': {'id': 'BIGINT', 'name': 'VARCHAR'}, } _DATE_ONLY = re.compile(r'\d{4}-\d{2}-\d{2}') def declared_columns(table): """`{column: SQL type}` the mirror sync declares for `table`, as `datastore._cols` names them.""" from harness import datastore out = {} spec = datastore.ENTITIES.get(table) for col in datastore._cols(spec) if spec else (): name, typ = col.split()[:2] out[name] = typ out.update(_DECLARED.get(table) or {}) return out def _kind(name, typ): typ = str(typ).upper() if typ == 'BOOLEAN': return 'bool' if typ.startswith(_NUMERIC): return 'num' # A many2many lands as a VARCHAR JSON list of ids. return 'm2m' if name.endswith('_ids') else 'str' class _Unmirrored(Exception): """A model, field, path or operator the mirror cannot answer.""" def mirror_missing(cur, kind='product'): """The `MIRROR_TABLES` this cursor cannot see, in declaration order.""" required = CUSTOMER_MIRROR_TABLES if kind == 'customer' else MIRROR_TABLES found = {r[0] for r in cur.execute( 'SELECT DISTINCT table_name FROM information_schema.tables WHERE table_name IN ' '(SELECT unnest($1::VARCHAR[]))', [list(required)]).fetchall()} missing = [t for t in required if t not in found] if kind != 'customer' or missing: return missing from harness import datastore try: phases = dict(cur.execute('SELECT entity, phase FROM _sync_state').fetchall()) for table in required: if phases.get(table) != 'live': missing.append(table + '.sync') states = datastore._column_backfill_states(cur, 'res_partner') missing.extend('res_partner.' + f for f in CUSTOMER_PARTNER_FIELDS if states.get(f, (None,))[0] != 'done') for table in ('account_move', 'account_move_line', 'account_account'): if not datastore._columns_backfilled(cur, table, datastore.ENTITIES[table]): missing.append(table + '.columns') cols = {r[0] for r in cur.execute('DESCRIBE res_partner').fetchall()} for field in CUSTOMER_PARTNER_FIELDS: missing.extend('res_partner.' + col for col in datastore._field_cols(field) if col not in cols) if 'category_source_ids' not in cols or cur.execute( 'SELECT 1 FROM res_partner WHERE category_source_ids IS NULL LIMIT 1').fetchone(): missing.append('res_partner.category_source_ids') except Exception as exc: from core.request_timing import raise_if_resource_error raise_if_resource_error(exc) missing.append('customer.sync_state') return list(dict.fromkeys(missing)) def _unready(missing): return ('canonical_source_mirror_unavailable: the Odoo mirror has not synced ' + ', '.join(missing) + ' yet, and the canonical pool does not read Odoo live') def _sql_list_type(values): if all(isinstance(v, bool) for v in values): return 'BOOLEAN' if all(isinstance(v, int) and not isinstance(v, bool) for v in values): return 'BIGINT' if all(isinstance(v, (int, float)) and not isinstance(v, bool) for v in values): return 'DOUBLE' if all(isinstance(v, str) for v in values): return 'VARCHAR' raise _Unmirrored('mixed value types ' + repr(values[:3])) def _plain(value): # Dates compare as Odoo's fixed-width text, the shape the mirror stores them in. return str(value) if hasattr(value, 'isoformat') else value class _Query: """Translate one Odoo model's domain and fields into SQL over the mirror tables.""" def __init__(self, source, model, params=None): if model not in _MIRROR_MODELS or (model in _CUSTOMER_MIRROR_MODELS and source.required_kind != 'customer'): raise _Unmirrored('model ' + model) self.source, self.model = source, model self.params = params if params is not None else [] self.joins = {} def param(self, value): self.params.append(value) return f'${len(self.params)}' def _column(self, model, name, alias): if name == _CODE_FIELD and model == 'product.product': return (f"CASE WHEN {alias}.default_code IS NULL OR {alias}.default_code = '' " f"THEN 'pid:' || CAST({alias}.id AS VARCHAR) ELSE trim({alias}.default_code) END"), 'str' cols = self.source.columns(model) kind = cols.get(name) if kind is None: # A declared column the sync has not filled yet reads blank; an undeclared one refuses. typ = self.source.pending_column(model, name) if typ is None: raise _Unmirrored(f'{model}.{name}') kind, expr = _kind(name, typ), f'CAST(NULL AS {typ})' if name.endswith('_id') and kind == 'num': return (expr, 'CAST(NULL AS VARCHAR)'), 'm2o' return expr, kind if name == 'id': return f'{alias}.id', 'int' if name.endswith('_id') and kind == 'num': label = name[:-3] + '_name' if label not in cols: self.source.pending_column(model, label) return (f'{alias}.{name}', f'{alias}.{label}' if label in cols else 'NULL'), 'm2o' return f'{alias}.{name}', kind def field(self, path): """`(expr, kind, guard)` for a field path; a dotted path joins its many2one once.""" if not isinstance(path, str) or not path.replace('.', '').replace('_', '').isalnum(): raise _Unmirrored('path ' + repr(path)) path = _MIRROR_ALIASES.get((self.model, path), path) model, alias, traversed, guards = self.model, 'b', [], [] parts = path.split('.') for index, head in enumerate(parts): expr, kind = self._column(model, head, alias) if index == len(parts) - 1: return expr, kind, ' AND '.join(guards) or None if parts[index + 1:] == ['name'] and kind == 'm2o': return expr[1], 'str', ' AND '.join(guards) or None comodel = _MIRROR_RELATIONS.get((model, head)) if comodel is None: raise _Unmirrored(f'{self.model}.{path}') traversed.append(head) target = 'j_' + '_'.join(traversed) self.joins[target] = (comodel, target, alias, head) model, alias = comodel, target guards.append(f'{alias}.id IS NOT NULL') def leaf(self, term): if not isinstance(term, (list, tuple)) or len(term) != 3: raise _Unmirrored('term ' + repr(term)) path, op, value = term op = {'==': '=', '<>': '!='}.get(str(op).lower(), str(op).lower()) expr, kind, guard = self.field(path) if kind == 'm2m': if self.source.required_kind != 'customer' or self.model != 'res.partner' or path not in { 'agent_ids', 'category_id', 'category_source_ids'}: raise _Unmirrored(f'many2many filter on {self.model}.{path}') empty = value is False or value is None if op in {'=', '!='} and empty: cond = f'coalesce(json_array_length({expr}), 0) = 0' return cond if op == '=' else f'NOT ({cond})' if op in {'in', 'not in'}: values = list(value) if isinstance(value, (list, tuple, set)) else [value] cond = (f'EXISTS (SELECT 1 FROM json_each({expr}) member WHERE ' f'try_cast(member.value AS BIGINT) IN ' f'(SELECT unnest({self.param(values)}::BIGINT[])))') return cond if op == 'in' else f'NOT ({cond})' raise _Unmirrored(f'many2many filter on {self.model}.{path}') if kind == 'str' and op in {'<=', '>'} and isinstance(value, str) and _DATE_ONLY.fullmatch(value): # Odoo reads a bare date as its midnight, so a value of exactly midnight is <= that day. value += ' 00:00:00' if kind == 'm2o': expr = expr[0] if isinstance(value, str) or op in {'like', 'ilike', '=like', '=ilike'}: raise _Unmirrored(f'name search on {path}') if op == 'in' and isinstance(value, RowIds): cond = f'{expr} IN ({value.sql()})' return f'coalesce(({guard} AND {cond}), false)' if guard else f'coalesce(({cond}), false)' if kind == 'bool': expr = f'coalesce({expr}, false)' value = [_plain(v) for v in value] if isinstance(value, (list, tuple, set)) else _plain(value) empty = value is False or value is None if op in {'=', '!='} and empty and kind != 'bool': cond = f'{expr} IS NULL' if op == '=' else f'{expr} IS NOT NULL' elif op in {'=', '!='}: value = bool(value) if kind == 'bool' else value cond = f'{expr} = {self.param(value)}' if op == '=' else \ f'({expr} <> {self.param(value)} OR {expr} IS NULL)' elif op in {'in', 'not in'}: values = list(value) if isinstance(value, list) else [value] if kind == 'bool': values, nulls = [bool(v) for v in values], False else: nulls = any(v is False or v is None for v in values) values = [v for v in values if v is not False and v is not None] member = (f'{expr} IN (SELECT unnest({self.param(values)}::{_sql_list_type(values)}[]))' if values else 'false') if nulls: member = f'({member} OR {expr} IS NULL)' cond = member if op == 'in' else f'NOT coalesce({member}, false)' elif op in {'<', '<=', '>', '>='}: cond = f'{expr} {op} {self.param(value)}' elif op in {'=like', 'like', '=ilike', 'ilike'}: pattern = value if op.startswith('=') else f'%{value}%' cond = f"{expr} {'ILIKE' if 'ilike' in op else 'LIKE'} {self.param(pattern)}" else: raise _Unmirrored('operator ' + op) return f'coalesce(({guard} AND {cond}), false)' if guard else f'coalesce(({cond}), false)' def where(self, domain): """Odoo prefix notation to SQL, with the model's default active filter.""" domain = list(domain or []) stack = [] for term in reversed(domain): if isinstance(term, str) and term in {'&', '|'}: if len(stack) < 2: raise _Unmirrored('malformed domain') a, b = stack.pop(), stack.pop() stack.append(f"({a} {'AND' if term == '&' else 'OR'} {b})") elif isinstance(term, str) and term == '!': if not stack: raise _Unmirrored('malformed domain') stack.append(f'(NOT {stack.pop()})') else: stack.append(self.leaf(term)) named = any(isinstance(t, (list, tuple)) and len(t) == 3 and t[0] == 'active' for t in domain) if not named and 'active' in self.source.columns(self.model): stack.append('coalesce(b.active, false)') return ' AND '.join(reversed(stack)) or 'true' def from_sql(self): joins = ''.join(f' LEFT JOIN {self.source.relation(comodel)} {alias} ON {alias}.id = {parent}.{head}' for comodel, alias, parent, head in self.joins.values()) return f'{self.source.relation(self.model)} b{joins}' def _json_value(expr, kind, grouped=False): if isinstance(expr, str) and expr.startswith('CAST(NULL AS'): return "'[]'::JSON" if kind == 'm2m' else "'false'::JSON" if kind == 'm2m': return f"coalesce(TRY_CAST({expr} AS JSON), '[]'::JSON)" if kind == 'm2o': ident, label = expr label = f'min({label})' if grouped else label return f"CASE WHEN {ident} IS NULL THEN 'false'::JSON ELSE json_array({ident}, {label}) END" if kind == 'bool': return f'coalesce({expr}, false)' if kind == 'num': return f'coalesce({expr}, 0)' if kind == 'int': return expr return f"CASE WHEN {expr} IS NULL THEN 'false'::JSON ELSE to_json({expr}) END" def _refuse(method, model, cause): return RuntimeError(f'canonical_source_unmirrored: {model}.{method} needs {cause}, ' 'which the Odoo mirror does not carry') class MirrorSource(Source): """Answer canonical customer and product reads from the tenant's Odoo mirror. `live_reads` stays `[]`. `pending` names each declared `table.column` or `table` the sync has not filled yet: those read blank or empty, logged once per root build. """ def __init__(self, cur, kind, identities, store=None): super().__init__(cur, kind, identities, store) self.live_reads = [] self.pending = [] self.dropped = [] self.parent = None self._columns = {} self._relations = {} self.required_kind = 'product' def _note(self, bucket, name): if name not in bucket: bucket.append(name) def relation(self, model): """The FROM operand for `model`: its mirror table, or an empty typed one while unsynced.""" if model not in self._relations: target = _MIRROR_MODELS[model] if model == 'product.category' and self.kind == 'product': products = self.products() target = ("(SELECT DISTINCT try_cast(json_extract_string(body, '/categ_id/0') AS BIGINT) AS id, " "json_extract_string(body, '/categ_id/1') AS complete_name " f'FROM "{products.table}" WHERE id IS NOT NULL)') if not target.startswith('(') and not self.cur.execute( 'SELECT 1 FROM information_schema.tables WHERE table_name = $1 LIMIT 1', [target]).fetchone(): declared = declared_columns(target) required = CUSTOMER_MIRROR_TABLES if self.required_kind == 'customer' else MIRROR_TABLES if target in required: raise RuntimeError(_unready([target])) if not declared: raise _Unmirrored('table ' + target) self._note(self.pending, target) target = '(SELECT ' + ', '.join(f'CAST(NULL AS {t}) AS {c}' for c, t in declared.items()) \ + ' WHERE false)' self._relations[model] = target return self._relations[model] def pending_column(self, model, name): """The SQL type of a declared column the mirror table lacks, recorded as pending, or None.""" target = _MIRROR_MODELS[model] if target.startswith('('): return None typ = declared_columns(target).get(name) if typ is not None: self._note(self.pending, f'{target}.{name}') return typ def categories(self, keep_blank_code=True): # Live `_code_category` keys every `default_code != False` product, a blank '' code included. return Source.categories(self, keep_blank_code) def related_read(self, model, selections, fields, domain=()): branches = [['&'] * len(extra) + [*extra, (field, 'in', ids)] for field, ids, extra in selections] scoped = ['|'] * (len(branches) - 1) + [term for branch in branches for term in branch] return self.search_read(model, [*domain, *scoped], fields) def tier_report(self): owner = self.parent or self if 'tier_report' not in owner.cache: query = _Query(self, 'product.pricelist.item') price = query.field('compute_price')[0] where = query.where([]) total, other = self.cur.execute( f"SELECT count(*), count(*) FILTER (WHERE {price} IS DISTINCT FROM 'fixed') " f'FROM {query.from_sql()} WHERE {where}', query.params).fetchone() owner.cache['tier_report'] = {'bounded': True, 'rules_total': total, 'rules_not_fixed': other} return dict(owner.cache['tier_report']) def servable(self, model, names): """The `names` the mirror declares for `model`; the rest are logged once and dropped.""" kept, dropped = [], [] for name in names: try: _Query(self, model).field(name) kept.append(name) except _Unmirrored: dropped.append(name) fresh = [n for n in dropped if f'{model}.{n}' not in self.dropped] for name in fresh: self._note(self.dropped, f'{model}.{name}') if fresh: _LOG.warning('canonical_source_dropped: the Odoo mirror does not declare %s, so the product ' 'pool leaves those fields blank. Declare them in harness.datastore.ENTITIES to ' 'sync them.', ', '.join(f'{model}.{n}' for n in fresh)) return kept @property def cur(self): # A factory opens the mirror on the first read, so a build that reads nothing never opens it. if self._cur is None: opened = self._factory() try: missing = mirror_missing(opened, self.required_kind) except BaseException: opened.close() raise if missing: opened.close() raise RuntimeError(_unready(missing)) self._cur = opened return self._cur @cur.setter def cur(self, value): lazy = callable(value) and not hasattr(value, 'execute') self._factory, self._cur = (value, None) if lazy else (None, value) def columns(self, model): if model not in self._columns: rows = self.cur.execute(f'DESCRIBE SELECT * FROM {self.relation(model)} AS t').fetchall() self._columns[model] = {name: _kind(name, typ) for name, typ, *_ in rows} return self._columns[model] def _scoped(self, model, domain): domain = list(domain or []) if self.kind == 'product' and model == 'product.product': domain.append((_CODE_FIELD, 'in', list(self.identities))) elif self.kind == 'product' and model in {'sale.order.line', 'stock.quant'}: domain.append(('product_id.' + _CODE_FIELD, 'in', list(self.identities))) elif self.kind == 'customer': domain = Source.domain(self, model, domain) return domain def _materialize(self, query, select, where, order, group=None): name = 'canonical_' + uuid.uuid4().hex self.cur.execute(f'CREATE TEMP TABLE "{name}" (ordinal BIGINT, body JSON)') self.tables.append(name) body = ', '.join(f"'{key}', {value}" for key, value in select) grouping = f' GROUP BY {group}' if group else '' try: self.cur.execute(f'INSERT INTO "{name}" SELECT row_number() OVER (ORDER BY {order}) - 1, ' f'json_object({body}) FROM {query.from_sql()} WHERE {where}{grouping}', query.params) except BaseException as exc: self.failure = exc raise return Rows(self, name) def search_read(self, model, domain=None, fields=None, **kw): wanted = list(dict.fromkeys(['id', *(['default_code'] if self.kind == 'product' and model == 'product.product' else []), *(fields or [])])) try: query = _Query(self, model) if not fields: wanted = list(self.columns(model)) select = [(f, _json_value(*query.field(f)[:2])) for f in wanted] where = query.where(self._scoped(model, domain)) except _Unmirrored as exc: raise _refuse('search_read', model, exc) from None return self._materialize(query, select, where, 'b.id') def read_group(self, model, domain, fields, groupby, **kw): groupby = [groupby] if isinstance(groupby, str) else list(groupby or []) try: query = _Query(self, model) select, keys = [], [] for g in groupby: if ':' in g: raise _Unmirrored('date grouping ' + g) expr, kind, _ = query.field(g) if kind == 'm2m': raise _Unmirrored('many2many grouping ' + g) keys.append(expr[0] if kind == 'm2o' else expr) select.append((g, _json_value(expr, kind, grouped=True))) for spec in fields or []: name, _, agg = str(spec).partition(':') if name in groupby or name in {'id', '__count'}: continue named = re.fullmatch(r'(sum|min|max|avg|count|count_distinct)\(([\w.]+)\)', agg) field = name if named: agg, field = named.groups() expr, kind, _ = query.field(field) if kind == 'm2m': raise _Unmirrored('many2many aggregate ' + spec) ident = expr[0] if kind == 'm2o' else expr agg = agg or ('sum' if kind == 'num' else '') if agg in {'sum', 'min', 'max', 'avg'}: select.append((name, _json_value(f'{agg}({ident})', kind))) elif agg == 'count': select.append((name, f'count({ident})')) elif agg == 'count_distinct': select.append((name, f'count(DISTINCT {ident})')) elif agg: raise _Unmirrored('aggregate ' + spec) select.append(('__count', 'count(*)')) where = query.where(self._scoped(model, domain)) except _Unmirrored as exc: raise _refuse('read_group', model, exc) from None order = ', '.join(f'{k} NULLS LAST' for k in keys) or '1' return self._materialize(query, select, where, order, group=', '.join(keys) or None) def search_count(self, model, domain=None, **kw): try: query = _Query(self, model) where = query.where(self._scoped(model, domain)) except _Unmirrored as exc: raise _refuse('search_count', model, exc) from None return self.cur.execute(f'SELECT count(*) FROM {query.from_sql()} WHERE {where}', query.params).fetchone()[0] def sum_field(self, model, domain, field): return sum(row.get(field) or 0 for row in self.read_group(model, domain, [field + ':sum'], [])) def excluded_partner_ids(self): """`core.odoo.excluded_partner_ids` resolved from res_partner, shared through its cache.""" ctx = _odoo._ctx() other = ctx is not None and ctx['slug'] != _odoo._DEFAULT_SLUG slug = ctx['slug'] if other else _odoo._DEFAULT_SLUG names = (ctx['exclude_partner_names'] or set()) if other else _odoo.EXCLUDE_PARTNER_NAMES with _odoo._EXCL_LOCK: if slug in _odoo._EXCL_IDS: return _odoo._EXCL_IDS[slug] found = tuple(r[0] for r in self.cur.execute( 'SELECT id FROM res_partner WHERE name IN (SELECT unnest($1::VARCHAR[])) ' 'AND coalesce(active, false) ORDER BY id', [sorted(names)]).fetchall()) if names else () with _odoo._EXCL_LOCK: return _odoo._EXCL_IDS.setdefault(slug, found) def sale_line_domain(self, *args, **kw): # core.sale_line_domain calls its own excluded_partner_ids, so the cache is filled first. self.excluded_partner_ids() return _odoo.sale_line_domain(*args, **kw) def inventory_frame(self, t): from core import periods import pandas as pd lf, lt = periods.ltm(t) params = [] prods = _Query(self, 'product.product', params) quants = _Query(self, 'stock.quant', params) sales = _Query(self, 'sale.order.line', params) pw = prods.where(self._scoped('product.product', [('default_code', '!=', False)])) qw = quants.where([('location_id.usage', '=', 'internal')]) sw = sales.where(_odoo.sale_line_domain(lf, lt, all_channels=True)) result = self.cur.execute(f"""WITH prods AS (SELECT b.id, trim(b.default_code) AS sku, b.name, b.type = 'product' AS storable, coalesce(b.standard_price, 0) AS cost, coalesce(nullif(trim(list_extract(string_split(b.categ_name, '/'), 2)), ''), '(uncategorized)') AS category FROM {prods.from_sql()} WHERE {pw}), quant AS (SELECT b.product_id id, sum(coalesce(b.quantity, 0)) qty FROM {quants.from_sql()} WHERE {qw} AND b.product_id IN (SELECT id FROM prods) GROUP BY 1), sold AS (SELECT b.product_id id, sum(coalesce(b.product_uom_qty, 0)) qty, sum(coalesce(b.price_subtotal, 0)) rev FROM {sales.from_sql()} WHERE {sw} AND b.product_id IN (SELECT id FROM prods) GROUP BY 1) SELECT p.sku, first(p.name ORDER BY p.id) AS name, first(p.category ORDER BY p.id) category, bool_or(p.storable) storable, sum(coalesce(q.qty, 0)) on_hand, sum(coalesce(q.qty, 0) * p.cost) inv_value, sum(coalesce(s.qty, 0) * p.cost) cogs_ltm, sum(coalesce(s.qty, 0)) qty_ltm, sum(coalesce(s.rev, 0)) rev_ltm FROM prods p LEFT JOIN quant q ON q.id = p.id LEFT JOIN sold s ON s.id = p.id GROUP BY p.sku ORDER BY p.sku""", params) names = [d[0] for d in result.description] rows = [] while True: batch = result.fetchmany(BATCH) if not batch: break rows.extend(batch) return pd.DataFrame(rows, columns=names), (lf, lt) def fba_qty(self, product_ids=None): """`warehouse.fba_qty_by_product_id`'s `(by_pid | None, report)` from the mirror's internal quants under the FBA view location's parent_path.""" from modules import warehouse view = self.cur.execute( "SELECT id, parent_path, warehouse_id, warehouse_name FROM stock_location " "WHERE usage = 'view' AND warehouse_id IS NOT NULL AND complete_name = $1 " "AND coalesce(active, false) ORDER BY id LIMIT 1", [warehouse.FBA_WAREHOUSE_CODE]).fetchone() if view is None: return None, warehouse.no_fba_warehouse_report() if product_ids is None: ids = [r[0] for r in self.cur.execute( 'SELECT id FROM product_product WHERE coalesce(active, false) ORDER BY id').fetchall()] else: ids = [int(i) for i in product_ids if i is not None] out = {} # Explicit selected ids include archived products; automatic enumeration stays active-only. result = self.cur.execute( "SELECT p.id, coalesce(f.qty, 0) FROM product_product p LEFT JOIN (" "SELECT q.product_id, sum(coalesce(q.quantity, 0)) qty FROM stock_quant q " "JOIN stock_location l ON l.id = q.location_id " "WHERE l.usage = 'internal' AND starts_with(l.parent_path, $1) " "AND q.product_id IN (SELECT unnest($2::BIGINT[])) " "GROUP BY 1) f ON f.product_id = p.id " "WHERE p.id IN (SELECT unnest($2::BIGINT[]))", [view[1], ids]) while True: batch = result.fetchmany(BATCH) if not batch: break out.update({pid: float(qty) for pid, qty in batch}) wh = {'id': view[2], 'name': view[3], 'code': warehouse.FBA_WAREHOUSE_CODE} return out, warehouse.fba_report(wh, len(ids), len(out), 1 if ids else 0, 'the FBA view location in the Odoo mirror, never a pinned id') def close(self): super().close() self._relations.clear() if self.parent is None and self.pending: _LOG.warning('canonical_source_pending: the Odoo mirror has not synced %s yet, so the ' 'product pool reads those cells blank until the next sync pass', ', '.join(self.pending)) if self._factory is not None and self._cur is not None: self._cur.close() self._cur = None def active(): return _ACTIVE.get() @contextmanager def _activate(source): token = _ACTIVE.set(source) try: yield source if source.failure is not None: raise RuntimeError('canonical_source_unavailable: ' + str(source.failure)) from source.failure finally: _ACTIVE.reset(token) source.close() @contextmanager def bounded(cur, kind, identities, store=None): parent = active() if isinstance(parent, MirrorSource): source = MirrorSource(cur, kind, identities, store) source.live_reads, source.pending, source.dropped = parent.live_reads, parent.pending, parent.dropped source.parent = parent source.required_kind = parent.required_kind else: source = Source(cur, kind, identities, store) with _activate(source) as entered: yield entered @contextmanager def mirrored(cur, store=None, *, kind='product'): """Serve every proxied read in this block from the Odoo mirror; never reads Odoo live. `cur` is a mirror cursor, or a zero-argument factory opened on the first read and closed on exit. """ if not callable(cur) or hasattr(cur, 'execute'): missing = mirror_missing(cur, kind) if missing: raise RuntimeError(_unready(missing)) source = MirrorSource(cur, 'all', [], store) source.required_kind = kind with _activate(source) as entered: yield entered class _Odoo: def __getattr__(self, name): source = active() if source is not None and name in {'search_read', 'read_group', 'search_count'}: return getattr(source, name) if source is not None and name == 'get_odoo': return lambda: self if isinstance(source, MirrorSource) and name in {'excluded_partner_ids', 'sale_line_domain', 'sum_field'}: return getattr(source, name) return getattr(_odoo, name) odoo = _Odoo()
"""Bounded, read-only operands for the existing compiled customer and product projections."""
import contextvars
import itertools
import json
import logging
import re
import uuid
from contextlib import contextmanager
from dataclasses import dataclass
from core import odoo as _odoo
BATCH = 1024
_ACTIVE = contextvars.ContextVar('canonical_source', default=None)
_LOG = logging.getLogger('canonical_source')
def code_sql(alias):
raw = f"json_extract_string({alias}.body, '/default_code')"
return (f"CASE WHEN {raw} IS NULL OR {raw} = '' OR json_type({alias}.body, '/default_code') = 'BOOLEAN' "
f"THEN 'pid:' || json_extract_string({alias}.body, '/id') ELSE trim({raw}) END")
class TierProjection(list):
"""Retain displayed tiers and the complete overflow receipt from SQL."""
def __init__(self):
super().__init__()
self.overflow = {}
class Rows:
"""Reiterate a SQL snapshot without retaining its Python records."""
def __init__(self, owner, table):
self.owner, self.table = owner, table
def __iter__(self):
last = -1
while True:
batch = self.owner.cur.execute(
f'SELECT ordinal, body FROM "{self.table}" WHERE ordinal > ? ORDER BY ordinal LIMIT ?',
[last, BATCH]).fetchall()
if not batch:
return
for _, body in batch:
yield json.loads(body)
last = batch[-1][0]
batch.clear()
def __len__(self):
return self.owner.cur.execute(f'SELECT count(*) FROM "{self.table}"').fetchone()[0]
def __bool__(self):
return bool(len(self))
@dataclass(frozen=True)
class RowIds:
"""A SQL-backed selection of related identities from one source snapshot."""
rows: Rows
path: str
def sql(self):
return (f'SELECT DISTINCT try_cast(json_extract_string(body, \'{self.path}\') AS BIGINT) id '
f'FROM "{self.rows.table}" WHERE id IS NOT NULL')
class Source:
"""Own bounded source operands and release every temporary snapshot on exit."""
def __init__(self, cur, kind, identities, store=None):
self.cur, self.kind, self.identities, self.store = cur, kind, list(identities), store
self.tables = []
self.cache = {}
self.failure = None
def snapshot(self, records):
name = 'canonical_' + uuid.uuid4().hex
self.cur.execute(f'CREATE TEMP TABLE "{name}" (ordinal BIGINT, body JSON)')
self.tables.append(name)
records, offset = iter(records), 0
try:
while True:
batch = list(itertools.islice(records, BATCH))
if not batch:
break
self.cur.execute(f'INSERT INTO "{name}" SELECT unnest(?::BIGINT[]), unnest(?::JSON[])',
[list(range(offset, offset + len(batch))), [json.dumps(r, default=str) for r in batch]])
offset += len(batch)
batch.clear()
except BaseException as exc:
self.failure = exc
raise
finally:
close = getattr(records, 'close', None)
if close:
close()
return Rows(self, name)
def domain(self, model, domain):