| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300 |
- """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()
|