readers.py 16 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300
  1. """Bounded SQLite reads with semantic checks, exact metrics, deadlines and versioned cache."""
  2. from __future__ import annotations
  3. import base64
  4. import copy
  5. import hashlib
  6. import json
  7. import sqlite3
  8. import threading
  9. import time
  10. from collections import OrderedDict
  11. from pathlib import Path
  12. from .catalog import CATALOG_VERSION, describe_table
  13. from .customs.engine.numbers import D, number, to_text
  14. from .config import settings
  15. from .objects import store
  16. from .query_precision import CALENDAR, numeric_field, register_precision
  17. ALLOWED_OPS = {'=', '!=', '>', '>=', '<', '<=', 'contains', 'startswith', 'in', 'not_in'}
  18. ALLOWED_AGGREGATES = {'sum', 'avg', 'count', 'min', 'max'}
  19. CACHE_VERSION = 'query-v2-exact'
  20. _CACHE = OrderedDict()
  21. _CACHE_LOCK = threading.Lock()
  22. _CACHE_BYTES = 0
  23. class ReadError(ValueError):
  24. pass
  25. class QueryTimeout(ReadError):
  26. pass
  27. def _quote(identifier):
  28. return '"' + str(identifier).replace('"', '""') + '"'
  29. def _decimal_aggregate(operation):
  30. class Aggregate:
  31. def __init__(self):
  32. self.value, self.count, self.bad = None, 0, False
  33. def step(self, raw):
  34. value = number(raw)
  35. if value is None:
  36. self.bad = True
  37. return
  38. self.count += 1
  39. if self.value is None: self.value = value
  40. elif operation in ('sum', 'avg'): self.value += value
  41. else: self.value = (min if operation == 'min' else max)(self.value, value)
  42. def finalize(self):
  43. if self.bad or not self.count: return None
  44. return to_text(self.value / D(self.count) if operation == 'avg' else self.value)
  45. return Aggregate
  46. def _connect(path):
  47. connection = sqlite3.connect(f'{Path(path).resolve().as_uri()}?mode=ro', uri=True, timeout=1)
  48. connection.row_factory = sqlite3.Row
  49. register_precision(connection)
  50. connection.execute('PRAGMA temp_store=FILE')
  51. connection.execute('PRAGMA cache_size=-32768')
  52. for operation in ('sum', 'avg', 'min', 'max'):
  53. connection.create_aggregate('exact_' + operation, 1, _decimal_aggregate(operation))
  54. return connection
  55. def _numeric_columns(path, table):
  56. with _connect(path) as conn:
  57. return {row[1][5:] for row in conn.execute(f'PRAGMA table_info({_quote(table)})') if row[1].startswith('_num_')}
  58. def _is_number(value):
  59. return number(value) is not None
  60. def _where(filters, columns, numeric):
  61. if len(filters or []) > 32: raise ReadError('最多32个过滤条件')
  62. clauses=[]; params=[]
  63. for item in filters or []:
  64. column=str(item.get('column') or ''); op=str(item.get('op') or '=').lower()
  65. if column not in columns: raise ReadError(f'列不存在: {column}')
  66. if op not in ALLOWED_OPS: raise ReadError(f'不支持的操作符: {op}')
  67. value=item.get('value')
  68. if op in ('contains','startswith'):
  69. # Literal search: user % and _ are not SQL wildcards.
  70. text=str(value).replace('\\','\\\\').replace('%','\\%').replace('_','\\_')
  71. clauses.append(f'{_quote(column)} LIKE ? ESCAPE \'\\\'')
  72. params.append(('%' if op=='contains' else '')+text+'%')
  73. elif op in ('in','not_in'):
  74. values=value if isinstance(value,list) else [value]
  75. if not values or len(values)>500: raise ReadError('in/not_in 需要1至500个值')
  76. target='_num_'+column if column in numeric and all(_is_number(v) for v in values) else column
  77. clauses.append(f'{_quote(target)} {"IN" if op=="in" else "NOT IN"} ({",".join("?" for _ in values)})')
  78. if target!=column and column not in CALENDAR:
  79. if op=='not_in':
  80. clauses.pop()
  81. clauses.append(' AND '.join(f'decimal_cmp({_quote(column)},?) != 0' for _ in values))
  82. params.extend(str(v) for v in values)
  83. else:
  84. params.extend(float(v) for v in values)
  85. clauses.append('('+' OR '.join(f'decimal_cmp({_quote(column)},?) = 0' for _ in values)+')')
  86. params.extend(str(v) for v in values)
  87. else:
  88. params.extend(float(v) if target!=column else str(v) for v in values)
  89. else:
  90. target='_num_'+column if column in numeric and _is_number(value) else column
  91. # NULL remains unknown. Never coerce missing numeric cells to zero.
  92. if target!=column and column not in CALENDAR:
  93. if op!='!=':
  94. coarse={'>' : '>=', '<' : '<='}.get(op,op)
  95. clauses.append(f'{_quote(target)} {coarse} ?');params.append(float(value))
  96. clauses.append(f'decimal_cmp({_quote(column)},?) {op} 0');params.append(str(value))
  97. else:
  98. clauses.append(f'{_quote(target)} {op} ?')
  99. params.append(float(value) if target!=column else str(value))
  100. return ' AND '.join(clauses) or '1=1',params
  101. def _digest(value):
  102. return hashlib.sha256(json.dumps(value,sort_keys=True,ensure_ascii=False,separators=(',',':')).encode()).hexdigest()
  103. def _cache_get(key):
  104. with _CACHE_LOCK:
  105. if key not in _CACHE: return None
  106. payload,size=_CACHE.pop(key);_CACHE[key]=(payload,size)
  107. result=copy.deepcopy(payload);result['cache_hit']=True
  108. return result
  109. def _cache_put(key, result):
  110. global _CACHE_BYTES
  111. size=len(json.dumps(result,ensure_ascii=False).encode())
  112. if size>settings.query_max_bytes: return
  113. with _CACHE_LOCK:
  114. if key in _CACHE: _CACHE_BYTES-=_CACHE.pop(key)[1]
  115. _CACHE[key]=(copy.deepcopy(result),size);_CACHE_BYTES+=size
  116. while len(_CACHE)>64 or _CACHE_BYTES>16*1024*1024:
  117. _CACHE_BYTES-=_CACHE.popitem(last=False)[1][1]
  118. def _validate_aggregate(catalog, group_by, metrics, filters):
  119. policy=catalog['aggregation']
  120. partitions=set(group_by)|{f.get('column') for f in filters or [] if f.get('op','=')=='='}
  121. for metric in metrics:
  122. fn=metric.get('fn','sum'); column=metric.get('column')
  123. if fn not in ('sum','avg'): continue
  124. if policy['unknown_scope']:raise ReadError('该表来源与可汇总性未确认,不能自动求和或平均;请先确认业务口径')
  125. if column in policy['non_additive']:
  126. raise ReadError(f'{column} 不能{fn}:请取原值,或对金额数量分别求和后重算单价;占比、增长率、加权指数按原口径计算')
  127. if fn=='sum' and column not in policy['additive_fields']:
  128. raise ReadError(f'{column} 未声明可累加;请核对表目录或使用查询专用汇总表')
  129. missing=set(policy['required_partitions'])-partitions
  130. if missing:
  131. raise ReadError(f'原报表含重叠视图,聚合须固定或分组 {sorted(missing)};全量问答请使用查询_叶子类目月度或查询_品牌型号月度')
  132. def _compile_aggregate(aggregate, available, catalog, filters):
  133. groups=aggregate.get('group_by') or []
  134. if not isinstance(groups,list) or any(not isinstance(g,str) for g in groups):
  135. raise ReadError('group_by 必须是字段名数组')
  136. metric_input=aggregate.get('metrics')
  137. sum_input=aggregate.get('sum',[])
  138. if metric_input is not None and (not isinstance(metric_input,list) or any(not isinstance(m,dict) for m in metric_input)):
  139. raise ReadError('metrics 必须是指标对象数组')
  140. if not isinstance(sum_input,list) or any(not isinstance(c,str) for c in sum_input):
  141. raise ReadError('sum 必须是字段名数组')
  142. if len(groups)>12 or len(set(groups))!=len(groups) or any(g not in available for g in groups):
  143. raise ReadError('group_by 含未知/重复维度或超过12个维度')
  144. metrics=aggregate.get('metrics') or [dict(column=c,fn='sum',alias='sum_'+c) for c in aggregate.get('sum',[])]
  145. if not metrics or len(metrics)>16: raise ReadError('aggregate 需要1至16个 metrics')
  146. selects=[_quote(c) for c in groups];aliases=[]
  147. for metric in metrics:
  148. fn=str(metric.get('fn') or 'sum').lower();metric={**metric,'fn':fn}
  149. col=metric.get('column')
  150. if fn not in ALLOWED_AGGREGATES: raise ReadError(f'不支持的聚合函数: {fn}')
  151. if fn=='count' and not col: expression='COUNT(*)'
  152. else:
  153. if col not in available: raise ReadError(f'聚合列不存在: {col}')
  154. expression=f'COUNT({_quote(col)})' if fn=='count' else f'exact_{fn}({_quote(col)})'
  155. # Generic raw numeric columns are allowed; externally imported stat tables aren't.
  156. _validate_aggregate(catalog,groups,[metric],filters)
  157. alias=str(metric.get('alias') or f'{fn}_{col}')
  158. if alias in aliases or alias in groups or alias.startswith('_'): raise ReadError('聚合别名重复或保留')
  159. aliases.append(alias);selects.append(f'{expression} AS {_quote(alias)}')
  160. group_sql=' GROUP BY '+','.join(_quote(c) for c in groups) if groups else ''
  161. # Tie-breaking makes exact Decimal TopN deterministic.
  162. order=' ORDER BY '+_quote(aliases[0])+' COLLATE DECIMAL_VALUE DESC'+(' ,'+','.join(_quote(c) for c in groups) if groups else '')
  163. return selects,groups,aliases,group_sql,order
  164. def read_rows(object_id, table='raw', filters=None, columns=None, order_by=None, limit=None,
  165. offset=0, aggregate=None, count_mode='auto', cursor=None, use_cache=True):
  166. started=time.monotonic()
  167. if limit is not None and (not isinstance(limit,int) or isinstance(limit,bool) or limit<1):
  168. raise ReadError('limit 必须是正整数')
  169. if cursor is not None and (not isinstance(cursor,str) or len(cursor)>4096):
  170. raise ReadError('游标格式或长度无效')
  171. if count_mode not in ('auto','none','exact'): raise ReadError('count_mode 只支持 auto/none/exact')
  172. if offset<0 or offset>2**63-1 or (offset>settings.query_max_offset and (filters or order_by or aggregate)):
  173. raise ReadError('过滤/排序/聚合的OFFSET超过允许范围;请使用 next_cursor 或按行号追溯')
  174. cap=max(1,min(int(limit or settings.max_rows),settings.max_rows))
  175. item=store.get_object(object_id)
  176. sqlite_path,sqlite_name=store.table_sqlite(object_id,table)
  177. stat=sqlite_path.stat()
  178. table_meta=next(t for t in item['tables'] if t['name']==table)
  179. 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)
  180. shape=dict(binding=binding,table=table,filters=filters,columns=columns,order_by=order_by,aggregate=aggregate)
  181. query_hash=_digest(shape)
  182. cache_key=_digest([shape,cap,offset,count_mode,cursor,settings.query_timeout_ms,settings.query_max_bytes])
  183. lineage=item['meta'].get('lineage',{})
  184. cache_allowed=use_cache and (item['kind']=='raw' or all(lineage.get(k) for k in ('input_sha256','rules_hash','params_hash')))
  185. if cache_allowed:
  186. hit=_cache_get(cache_key)
  187. if hit is not None:return hit
  188. conn=_connect(sqlite_path)
  189. deadline=started+settings.query_timeout_ms/1000
  190. conn.set_progress_handler(lambda: int(time.monotonic()>deadline),1000)
  191. try:
  192. info=[row[1] for row in conn.execute(f'PRAGMA table_info({_quote(sqlite_name)})')]
  193. available=[c for c in info if not c.startswith('_')]
  194. numeric={c[5:] for c in info if c.startswith('_num_')}
  195. selected=list(columns or available)
  196. if not selected or any(c not in available for c in selected):raise ReadError('列不存在或不可读取')
  197. where,params=_where(filters,set(available),numeric)
  198. catalog=table_meta.get('catalog') or describe_table(table,available,kind=item['kind'])
  199. total=None;total_exact=False;after=-1
  200. if cursor:
  201. if aggregate or order_by or offset:raise ReadError('游标目前仅支持自然行号顺序,且不能与OFFSET/聚合同时使用')
  202. try:
  203. decoded=json.loads(base64.urlsafe_b64decode(cursor))
  204. if decoded['q']!=query_hash:raise ValueError()
  205. after=decoded['after']
  206. if not isinstance(after,int) or isinstance(after,bool) or not 0<=after<=2**63-1:raise ValueError()
  207. except Exception as error:raise ReadError('游标无效或数据/规则/参数/查询条件已变化') from error
  208. if aggregate:
  209. selects,groups,aliases,group_sql,order_sql=_compile_aggregate(aggregate,available,catalog,filters)
  210. sql=f'SELECT {",".join(selects)} FROM {_quote(sqlite_name)} WHERE {where}{group_sql}{order_sql} LIMIT ? OFFSET ?'
  211. query_params=[*params,cap+1,offset]
  212. selected=groups+aliases
  213. if count_mode=='exact':
  214. total=conn.execute(f'SELECT COUNT(*) FROM (SELECT 1 FROM {_quote(sqlite_name)} WHERE {where}{group_sql})',params).fetchone()[0];total_exact=True
  215. else:
  216. if count_mode=='exact':
  217. total=conn.execute(f'SELECT COUNT(*) FROM {_quote(sqlite_name)} WHERE {where}',params).fetchone()[0];total_exact=True
  218. elif count_mode=='auto' and not filters:
  219. total=table_meta['rows'];total_exact=True # immutable materialization metadata
  220. order=[]
  221. for item_order in order_by or []:
  222. c=item_order.get('column')
  223. if c not in available:raise ReadError(f'排序列不存在: {c}')
  224. target='_num_'+c if c in numeric and not numeric_field(c) else c
  225. expression=_quote(target)+(' COLLATE DECIMAL_VALUE' if numeric_field(c) else '')
  226. direction=str(item_order.get('dir',item_order.get('direction','asc'))).lower()
  227. if direction not in ('asc','desc'):raise ReadError('排序方向只支持asc/desc')
  228. order.append(expression+(' DESC' if direction=='desc' else ' ASC'))
  229. order.append('"_row_index" ASC')
  230. query_params=list(params)
  231. if cursor:
  232. where+=' AND "_row_index">?';query_params.append(after)
  233. elif not filters and not order_by and offset:
  234. # Immutable tables have contiguous 0-based indices; direct seek avoids deep skip.
  235. where+=' AND "_row_index">=?';query_params.append(offset);offset_sql=0
  236. offset_sql=0 if cursor or (not filters and not order_by) else offset
  237. 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 ?'
  238. query_params.extend([cap+1,offset_sql])
  239. cur=conn.execute(sql,query_params)
  240. result_rows=[];byte_count=0;truncated=False;reason=None
  241. for row in cur:
  242. if len(result_rows)>=cap:truncated=True;reason='row_limit';break
  243. values={c:row[c] for c in selected}
  244. size=len(json.dumps(values,ensure_ascii=False).encode())
  245. if byte_count+size>settings.query_max_bytes:
  246. if not result_rows:raise ReadError('单行超过响应字节预算,请减少 columns')
  247. truncated=True;reason='byte_limit';break
  248. byte_count+=size
  249. trace=dict(object_id=object_id,table=table,row_index=-1 if aggregate else int(row['_row_index']),
  250. row_hash='sha256:'+_digest(values) if aggregate else row['_row_hash'])
  251. if aggregate:trace['aggregate']=True
  252. result_rows.append(dict(values=values,_trace=trace))
  253. next_cursor=None
  254. if truncated and result_rows and not aggregate and not order_by:
  255. next_cursor=base64.urlsafe_b64encode(json.dumps(dict(q=query_hash,after=result_rows[-1]['_trace']['row_index'])).encode()).decode()
  256. result=dict(object_id=object_id,table=table,columns=selected,rows=result_rows,total=total,total_exact=total_exact,
  257. offset=offset,limit=cap,exhausted=not truncated,truncated=truncated,truncation_reason=reason,next_cursor=next_cursor,
  258. count_mode=count_mode,cache_hit=False,cache_enabled=cache_allowed,query_version=query_hash,
  259. warning='仅返回受限子集,不能把本页相加当作全量总数;聚合仍可能扫描全部匹配行' if truncated else None)
  260. if aggregate:result.update(aggregate=True,group_by=groups)
  261. if time.monotonic()>deadline:raise QueryTimeout('查询超时;请缩小范围或使用叶子查询汇总,后台大导出尚未实现')
  262. if cache_allowed:_cache_put(cache_key,result)
  263. return result
  264. except sqlite3.OperationalError as error:
  265. if 'interrupt' in str(error).lower() or time.monotonic()>deadline:
  266. raise QueryTimeout('查询超时,已中断;请增加筛选条件或使用查询汇总表') from error
  267. raise ReadError(f'查询执行失败: {error}') from error
  268. finally:
  269. conn.close()