"""Bounded SQLite reads with semantic checks, exact metrics, deadlines and versioned cache.""" from __future__ import annotations import base64 import copy import hashlib import json import sqlite3 import threading import time from collections import OrderedDict from pathlib import Path from .catalog import CATALOG_VERSION, describe_table from .customs.engine.numbers import D, number, to_text from .config import settings from .objects import store from .query_precision import CALENDAR, numeric_field, register_precision ALLOWED_OPS = {'=', '!=', '>', '>=', '<', '<=', 'contains', 'startswith', 'in', 'not_in'} ALLOWED_AGGREGATES = {'sum', 'avg', 'count', 'min', 'max'} CACHE_VERSION = 'query-v2-exact' _CACHE = OrderedDict() _CACHE_LOCK = threading.Lock() _CACHE_BYTES = 0 class ReadError(ValueError): pass class QueryTimeout(ReadError): pass def _quote(identifier): return '"' + str(identifier).replace('"', '""') + '"' def _decimal_aggregate(operation): class Aggregate: def __init__(self): self.value, self.count, self.bad = None, 0, False def step(self, raw): value = number(raw) if value is None: self.bad = True return self.count += 1 if self.value is None: self.value = value elif operation in ('sum', 'avg'): self.value += value else: self.value = (min if operation == 'min' else max)(self.value, value) def finalize(self): if self.bad or not self.count: return None return to_text(self.value / D(self.count) if operation == 'avg' else self.value) return Aggregate def _connect(path): connection = sqlite3.connect(f'{Path(path).resolve().as_uri()}?mode=ro', uri=True, timeout=1) connection.row_factory = sqlite3.Row register_precision(connection) connection.execute('PRAGMA temp_store=FILE') connection.execute('PRAGMA cache_size=-32768') for operation in ('sum', 'avg', 'min', 'max'): connection.create_aggregate('exact_' + operation, 1, _decimal_aggregate(operation)) return connection def _numeric_columns(path, table): with _connect(path) as conn: return {row[1][5:] for row in conn.execute(f'PRAGMA table_info({_quote(table)})') if row[1].startswith('_num_')} def _is_number(value): return number(value) is not None def _where(filters, columns, numeric): if len(filters or []) > 32: raise ReadError('最多32个过滤条件') clauses=[]; params=[] for item in filters or []: column=str(item.get('column') or ''); op=str(item.get('op') or '=').lower() if column not in columns: raise ReadError(f'列不存在: {column}') if op not in ALLOWED_OPS: raise ReadError(f'不支持的操作符: {op}') value=item.get('value') if op in ('contains','startswith'): # Literal search: user % and _ are not SQL wildcards. text=str(value).replace('\\','\\\\').replace('%','\\%').replace('_','\\_') clauses.append(f'{_quote(column)} LIKE ? ESCAPE \'\\\'') params.append(('%' if op=='contains' else '')+text+'%') elif op in ('in','not_in'): values=value if isinstance(value,list) else [value] if not values or len(values)>500: raise ReadError('in/not_in 需要1至500个值') target='_num_'+column if column in numeric and all(_is_number(v) for v in values) else column clauses.append(f'{_quote(target)} {"IN" if op=="in" else "NOT IN"} ({",".join("?" for _ in values)})') if target!=column and column not in CALENDAR: if op=='not_in': clauses.pop() clauses.append(' AND '.join(f'decimal_cmp({_quote(column)},?) != 0' for _ in values)) params.extend(str(v) for v in values) else: params.extend(float(v) for v in values) clauses.append('('+' OR '.join(f'decimal_cmp({_quote(column)},?) = 0' for _ in values)+')') params.extend(str(v) for v in values) else: params.extend(float(v) if target!=column else str(v) for v in values) else: target='_num_'+column if column in numeric and _is_number(value) else column # NULL remains unknown. Never coerce missing numeric cells to zero. if target!=column and column not in CALENDAR: if op!='!=': coarse={'>' : '>=', '<' : '<='}.get(op,op) clauses.append(f'{_quote(target)} {coarse} ?');params.append(float(value)) clauses.append(f'decimal_cmp({_quote(column)},?) {op} 0');params.append(str(value)) else: clauses.append(f'{_quote(target)} {op} ?') params.append(float(value) if target!=column else str(value)) return ' AND '.join(clauses) or '1=1',params def _digest(value): return hashlib.sha256(json.dumps(value,sort_keys=True,ensure_ascii=False,separators=(',',':')).encode()).hexdigest() def _cache_get(key): with _CACHE_LOCK: if key not in _CACHE: return None payload,size=_CACHE.pop(key);_CACHE[key]=(payload,size) result=copy.deepcopy(payload);result['cache_hit']=True return result def _cache_put(key, result): global _CACHE_BYTES size=len(json.dumps(result,ensure_ascii=False).encode()) if size>settings.query_max_bytes: return with _CACHE_LOCK: if key in _CACHE: _CACHE_BYTES-=_CACHE.pop(key)[1] _CACHE[key]=(copy.deepcopy(result),size);_CACHE_BYTES+=size while len(_CACHE)>64 or _CACHE_BYTES>16*1024*1024: _CACHE_BYTES-=_CACHE.popitem(last=False)[1][1] def _validate_aggregate(catalog, group_by, metrics, filters): policy=catalog['aggregation'] partitions=set(group_by)|{f.get('column') for f in filters or [] if f.get('op','=')=='='} for metric in metrics: fn=metric.get('fn','sum'); column=metric.get('column') if fn not in ('sum','avg'): continue if policy['unknown_scope']:raise ReadError('该表来源与可汇总性未确认,不能自动求和或平均;请先确认业务口径') if column in policy['non_additive']: raise ReadError(f'{column} 不能{fn}:请取原值,或对金额数量分别求和后重算单价;占比、增长率、加权指数按原口径计算') if fn=='sum' and column not in policy['additive_fields']: raise ReadError(f'{column} 未声明可累加;请核对表目录或使用查询专用汇总表') missing=set(policy['required_partitions'])-partitions if missing: raise ReadError(f'原报表含重叠视图,聚合须固定或分组 {sorted(missing)};全量问答请使用查询_叶子类目月度或查询_品牌型号月度') def _compile_aggregate(aggregate, available, catalog, filters): groups=aggregate.get('group_by') or [] if not isinstance(groups,list) or any(not isinstance(g,str) for g in groups): raise ReadError('group_by 必须是字段名数组') metric_input=aggregate.get('metrics') sum_input=aggregate.get('sum',[]) if metric_input is not None and (not isinstance(metric_input,list) or any(not isinstance(m,dict) for m in metric_input)): raise ReadError('metrics 必须是指标对象数组') if not isinstance(sum_input,list) or any(not isinstance(c,str) for c in sum_input): raise ReadError('sum 必须是字段名数组') if len(groups)>12 or len(set(groups))!=len(groups) or any(g not in available for g in groups): raise ReadError('group_by 含未知/重复维度或超过12个维度') metrics=aggregate.get('metrics') or [dict(column=c,fn='sum',alias='sum_'+c) for c in aggregate.get('sum',[])] if not metrics or len(metrics)>16: raise ReadError('aggregate 需要1至16个 metrics') selects=[_quote(c) for c in groups];aliases=[] for metric in metrics: fn=str(metric.get('fn') or 'sum').lower();metric={**metric,'fn':fn} col=metric.get('column') if fn not in ALLOWED_AGGREGATES: raise ReadError(f'不支持的聚合函数: {fn}') if fn=='count' and not col: expression='COUNT(*)' else: if col not in available: raise ReadError(f'聚合列不存在: {col}') expression=f'COUNT({_quote(col)})' if fn=='count' else f'exact_{fn}({_quote(col)})' # Generic raw numeric columns are allowed; externally imported stat tables aren't. _validate_aggregate(catalog,groups,[metric],filters) alias=str(metric.get('alias') or f'{fn}_{col}') if alias in aliases or alias in groups or alias.startswith('_'): raise ReadError('聚合别名重复或保留') aliases.append(alias);selects.append(f'{expression} AS {_quote(alias)}') group_sql=' GROUP BY '+','.join(_quote(c) for c in groups) if groups else '' # Tie-breaking makes exact Decimal TopN deterministic. order=' ORDER BY '+_quote(aliases[0])+' COLLATE DECIMAL_VALUE DESC'+(' ,'+','.join(_quote(c) for c in groups) if groups else '') return selects,groups,aliases,group_sql,order def read_rows(object_id, table='raw', filters=None, columns=None, order_by=None, limit=None, offset=0, aggregate=None, count_mode='auto', cursor=None, use_cache=True): started=time.monotonic() if limit is not None and (not isinstance(limit,int) or isinstance(limit,bool) or limit<1): raise ReadError('limit 必须是正整数') if cursor is not None and (not isinstance(cursor,str) or len(cursor)>4096): raise ReadError('游标格式或长度无效') if count_mode not in ('auto','none','exact'): raise ReadError('count_mode 只支持 auto/none/exact') if offset<0 or offset>2**63-1 or (offset>settings.query_max_offset and (filters or order_by or aggregate)): raise ReadError('过滤/排序/聚合的OFFSET超过允许范围;请使用 next_cursor 或按行号追溯') cap=max(1,min(int(limit or settings.max_rows),settings.max_rows)) item=store.get_object(object_id) sqlite_path,sqlite_name=store.table_sqlite(object_id,table) stat=sqlite_path.stat() table_meta=next(t for t in item['tables'] if t['name']==table) binding=dict(object=object_id,sha256=item['sha256'],lineage=item['meta'].get('lineage',{}),file=[stat.st_size,stat.st_mtime_ns],catalog=CATALOG_VERSION,reader=CACHE_VERSION) shape=dict(binding=binding,table=table,filters=filters,columns=columns,order_by=order_by,aggregate=aggregate) query_hash=_digest(shape) cache_key=_digest([shape,cap,offset,count_mode,cursor,settings.query_timeout_ms,settings.query_max_bytes]) lineage=item['meta'].get('lineage',{}) cache_allowed=use_cache and (item['kind']=='raw' or all(lineage.get(k) for k in ('input_sha256','rules_hash','params_hash'))) if cache_allowed: hit=_cache_get(cache_key) if hit is not None:return hit conn=_connect(sqlite_path) deadline=started+settings.query_timeout_ms/1000 conn.set_progress_handler(lambda: int(time.monotonic()>deadline),1000) try: info=[row[1] for row in conn.execute(f'PRAGMA table_info({_quote(sqlite_name)})')] available=[c for c in info if not c.startswith('_')] numeric={c[5:] for c in info if c.startswith('_num_')} selected=list(columns or available) if not selected or any(c not in available for c in selected):raise ReadError('列不存在或不可读取') where,params=_where(filters,set(available),numeric) catalog=table_meta.get('catalog') or describe_table(table,available,kind=item['kind']) total=None;total_exact=False;after=-1 if cursor: if aggregate or order_by or offset:raise ReadError('游标目前仅支持自然行号顺序,且不能与OFFSET/聚合同时使用') try: decoded=json.loads(base64.urlsafe_b64decode(cursor)) if decoded['q']!=query_hash:raise ValueError() after=decoded['after'] if not isinstance(after,int) or isinstance(after,bool) or not 0<=after<=2**63-1:raise ValueError() except Exception as error:raise ReadError('游标无效或数据/规则/参数/查询条件已变化') from error if aggregate: selects,groups,aliases,group_sql,order_sql=_compile_aggregate(aggregate,available,catalog,filters) sql=f'SELECT {",".join(selects)} FROM {_quote(sqlite_name)} WHERE {where}{group_sql}{order_sql} LIMIT ? OFFSET ?' query_params=[*params,cap+1,offset] selected=groups+aliases if count_mode=='exact': total=conn.execute(f'SELECT COUNT(*) FROM (SELECT 1 FROM {_quote(sqlite_name)} WHERE {where}{group_sql})',params).fetchone()[0];total_exact=True else: if count_mode=='exact': total=conn.execute(f'SELECT COUNT(*) FROM {_quote(sqlite_name)} WHERE {where}',params).fetchone()[0];total_exact=True elif count_mode=='auto' and not filters: total=table_meta['rows'];total_exact=True # immutable materialization metadata order=[] for item_order in order_by or []: c=item_order.get('column') if c not in available:raise ReadError(f'排序列不存在: {c}') target='_num_'+c if c in numeric and not numeric_field(c) else c expression=_quote(target)+(' COLLATE DECIMAL_VALUE' if numeric_field(c) else '') direction=str(item_order.get('dir',item_order.get('direction','asc'))).lower() if direction not in ('asc','desc'):raise ReadError('排序方向只支持asc/desc') order.append(expression+(' DESC' if direction=='desc' else ' ASC')) order.append('"_row_index" ASC') query_params=list(params) if cursor: where+=' AND "_row_index">?';query_params.append(after) elif not filters and not order_by and offset: # Immutable tables have contiguous 0-based indices; direct seek avoids deep skip. where+=' AND "_row_index">=?';query_params.append(offset);offset_sql=0 offset_sql=0 if cursor or (not filters and not order_by) else offset sql=f'SELECT {",".join(_quote(c) for c in selected)},"_row_index","_row_hash" FROM {_quote(sqlite_name)} WHERE {where} ORDER BY {",".join(order)} LIMIT ? OFFSET ?' query_params.extend([cap+1,offset_sql]) cur=conn.execute(sql,query_params) result_rows=[];byte_count=0;truncated=False;reason=None for row in cur: if len(result_rows)>=cap:truncated=True;reason='row_limit';break values={c:row[c] for c in selected} size=len(json.dumps(values,ensure_ascii=False).encode()) if byte_count+size>settings.query_max_bytes: if not result_rows:raise ReadError('单行超过响应字节预算,请减少 columns') truncated=True;reason='byte_limit';break byte_count+=size trace=dict(object_id=object_id,table=table,row_index=-1 if aggregate else int(row['_row_index']), row_hash='sha256:'+_digest(values) if aggregate else row['_row_hash']) if aggregate:trace['aggregate']=True result_rows.append(dict(values=values,_trace=trace)) next_cursor=None if truncated and result_rows and not aggregate and not order_by: next_cursor=base64.urlsafe_b64encode(json.dumps(dict(q=query_hash,after=result_rows[-1]['_trace']['row_index'])).encode()).decode() result=dict(object_id=object_id,table=table,columns=selected,rows=result_rows,total=total,total_exact=total_exact, offset=offset,limit=cap,exhausted=not truncated,truncated=truncated,truncation_reason=reason,next_cursor=next_cursor, count_mode=count_mode,cache_hit=False,cache_enabled=cache_allowed,query_version=query_hash, warning='仅返回受限子集,不能把本页相加当作全量总数;聚合仍可能扫描全部匹配行' if truncated else None) if aggregate:result.update(aggregate=True,group_by=groups) if time.monotonic()>deadline:raise QueryTimeout('查询超时;请缩小范围或使用叶子查询汇总,后台大导出尚未实现') if cache_allowed:_cache_put(cache_key,result) return result except sqlite3.OperationalError as error: if 'interrupt' in str(error).lower() or time.monotonic()>deadline: raise QueryTimeout('查询超时,已中断;请增加筛选条件或使用查询汇总表') from error raise ReadError(f'查询执行失败: {error}') from error finally: conn.close()