import json import sqlite3 from decimal import Decimal from pathlib import Path import pytest from toolsvc.catalog import CLASS_SUMMARY, MODEL_SUMMARY, SPECS, describe_table from toolsvc.config import settings from toolsvc.customs.engine.numbers import StrictSum from toolsvc.db import database from toolsvc.objects import store from toolsvc.readers import read_rows, ReadError, QueryTimeout @pytest.fixture def raw(): ws=store.create_workspace('bounded query tests') body='HS_CODE,YEAR,WEEK,MONORSUM,G_MODEL\n'+''.join(f'{8532241000+i%20},2025,{i%52+1},{i}.1,M{i}\n' for i in range(501)) return store.register_upload(ws['id'],'large.csv',body.encode()) def test_business_indexes_and_search(raw): path,table=store.table_sqlite(raw['id'],'raw') with sqlite3.connect(path) as conn: plan=conn.execute('EXPLAIN QUERY PLAN SELECT * FROM raw WHERE HS_CODE=? AND _num_YEAR=? AND _num_WEEK=? ORDER BY _row_index LIMIT 20',('8532241000','2025','1')).fetchall() assert any('SEARCH' in r[3] and 'idx_query_' in r[3] for r in plan) def test_count_modes_and_filter_null_total(raw): q=dict(object_id=raw['id'],filters=[dict(column='YEAR',value='2025')],limit=3) auto=read_rows(**q) assert auto['total'] is None and not auto['total_exact'] and auto['truncated'] exact=read_rows(**q,count_mode='exact') assert exact['total']==501 and exact['total_exact'] assert read_rows(raw['id'],limit=3)['total']==501 assert read_rows(raw['id'],count_mode='none')['total'] is None def test_bound_aggregate_no_fetchall(raw): q=read_rows(raw['id'],aggregate={'group_by':['G_MODEL'],'metrics':[{'fn':'sum','column':'MONORSUM'}]},limit=7) assert len(q['rows'])==7 and q['truncated'] and q['truncation_reason']=='row_limit' assert q['total'] is None and not q['exhausted'] assert q['columns']==['G_MODEL','sum_MONORSUM'] count=read_rows(raw['id'],aggregate={'group_by':['G_MODEL'],'metrics':[{'fn':'count'}]},limit=7,count_mode='exact') assert count['total']==501 def test_keyset_no_gaps_and_bound_to_query(raw): kwargs=dict(object_id=raw['id'],limit=37,filters=[dict(column='YEAR',value='2025')]) seen=[];q=read_rows(**kwargs) while True: seen += [r['_trace']['row_index'] for r in q['rows']] if not q['next_cursor']:break q=read_rows(**kwargs,cursor=q['next_cursor']) assert seen==list(range(501)) q=read_rows(raw['id'],limit=2) with pytest.raises(ReadError,match='游标'): read_rows(raw['id'],filters=[dict(column='YEAR',value='2026')],cursor=q['next_cursor']) with pytest.raises(ReadError):read_rows(raw['id'],filters=[dict(column='YEAR',value='2025')],offset=10001) assert read_rows(raw['id'],offset=10001)['rows']==[] @pytest.mark.parametrize('version_field',['rules_hash','params_hash','input_sha256']) def test_cache_version_and_independent_rows(raw,version_field): q=read_rows(raw['id'],limit=2); again=read_rows(raw['id'],limit=2) assert not q['cache_hit'] and again['cache_hit'] again['rows'][0]['values']['MONORSUM']='poison' assert read_rows(raw['id'],limit=2)['rows'][0]['values']['MONORSUM']!='poison' with database.transaction() as conn: meta=raw['meta'];meta['lineage']={version_field:'changed-version'} conn.execute('UPDATE objects SET meta_json=? WHERE id=?',(json.dumps(meta),raw['id'])) changed=read_rows(raw['id'],limit=2) assert not changed['cache_hit'] and changed['query_version']!=q['query_version'] def test_query_deadline(raw,monkeypatch): monkeypatch.setattr(settings,'query_timeout_ms',0) with pytest.raises(QueryTimeout):read_rows(raw['id'],count_mode='exact',use_cache=False) def test_response_byte_budget(raw,monkeypatch): monkeypatch.setattr(settings,'query_max_bytes',180) q=read_rows(raw['id'],limit=200,use_cache=False) assert q['truncated'] and q['truncation_reason']=='byte_limit' and q['next_cursor'] monkeypatch.setattr(settings,'query_max_bytes',1) with pytest.raises(ReadError,match='单行'):read_rows(raw['id'],use_cache=False) def test_invalid_aggregation_names_fail(raw): for aggregate in [dict(group_by=['MISSING'],metrics=[dict(fn='count')]), dict(metrics=[dict(fn='sum',column='MONORSUM',alias='x'),dict(fn='count',alias='x')])]: with pytest.raises(ReadError):read_rows(raw['id'],aggregate=aggregate) def test_catalog_covers_all_original_sheets(processed): original=[s for s in processed['result'].sheets if s.kind!='query_summary'] assert len(original)==23 and all(s.sheet in SPECS for s in original) for sheet in processed['result'].sheets: cat=describe_table(sheet.sheet,sheet.columns) assert all(cat[k] for k in ('subject','row_grain','coverage','aggregation')) def test_summaries_preserve_exact_total_and_missingness(processed): with sqlite3.connect(processed['result'].sqlite_path) as stat, sqlite3.connect(processed['output']/'working.sqlite') as working: for conn in (stat,working):conn.create_aggregate('dsum',1,StrictSum) expected=working.execute('SELECT dsum(amount),dsum(quantity),COUNT(*) FROM fact').fetchone() for table in ('q_class_month','q_model_month'): total=stat.execute(f'SELECT dsum("实际金额(美元)"),dsum("成交数量"),SUM(CAST("记录数" AS INTEGER)) FROM {table}').fetchone() assert [Decimal(x) if x is not None else None for x in total]==[Decimal(x) if x is not None else None for x in expected] for row in stat.execute(f'SELECT "实际金额(美元)","成交数量","平均单价(美元/成交数量单位)" FROM {table}'): if row[0] and row[1] and Decimal(row[1]): assert Decimal(row[2])==Decimal(row[0])/Decimal(row[1]) def test_stat_semantic_guard_and_sum_summary(processed): ws=store.create_workspace('summary') source=store.register_upload(ws['id'],'synthetic.csv',b'x\n1\n') item=store.register_processed(ws['id'],source['id'],'result',processed['output'],processed['result'].to_dict()) for fn,column in [('sum','价格指数'),('avg','平均单价(美元/成交数量单位)')]: with pytest.raises(ReadError,match='不能'): read_rows(item['id'],table='1.0-3.0_MLCC',aggregate={'metrics':[dict(fn=fn,column=column)]}) with pytest.raises(ReadError,match='重叠视图'): read_rows(item['id'],table='1.0-3.0_存储器',aggregate={'metrics':[dict(fn='sum',column='实际金额(美元)')]}) q=read_rows(item['id'],table=CLASS_SUMMARY,aggregate={'metrics':[dict(fn='sum',column='实际金额(美元)')]}) assert len(q['rows'])==1 assert store.row_lineage(item['id'],MODEL_SUMMARY,0)['source_object_id']==source['id'] def test_numeric_null_is_not_zero_and_literal_wildcards(processed): from toolsvc.readers import _where where,params=_where([dict(column='amount',value=0)],{'amount'},{'amount'}) with sqlite3.connect(':memory:') as conn: from toolsvc.query_precision import register_precision register_precision(conn) conn.execute('CREATE TABLE t(amount TEXT,_num_amount REAL)') conn.executemany('INSERT INTO t VALUES(?,?)',[('',None),('0',0)]) assert conn.execute('SELECT COUNT(*) FROM t WHERE '+where,params).fetchone()[0]==1 def test_exact_financial_filter_and_order_after_float_collision(): from toolsvc.readers import _connect,_where from toolsvc.query_precision import register_precision conn=sqlite3.connect(':memory:');register_precision(conn) conn.execute('CREATE TABLE t(amount TEXT,_num_amount REAL)') conn.executemany('INSERT INTO t VALUES(?,?)',[(str(v),float(v)) for v in [9007199254740992,9007199254740993]]) for op,value,expected in [('=',9007199254740993,['9007199254740993']),('>',9007199254740992,['9007199254740993']),('!=',9007199254740992,['9007199254740993']),('in',[9007199254740993],['9007199254740993']),('not_in',[9007199254740992],['9007199254740993'])]: where,params=_where([dict(column='amount',op=op,value=value)],{'amount'},{'amount'}) assert [r[0] for r in conn.execute('SELECT amount FROM t WHERE '+where,params)]==expected assert conn.execute('SELECT amount FROM t ORDER BY amount COLLATE DECIMAL_VALUE DESC LIMIT 1').fetchone()[0]=='9007199254740993' conn.close() def test_literal_search_does_not_expand_sql_wildcards(): ws=store.create_workspace('literal') obj=store.register_upload(ws['id'],'l.csv',b'value\nM%1\nM_1\nM21\n') result=read_rows(obj['id'],filters=[dict(column='value',op='contains',value='%')]) assert [r['values']['value'] for r in result['rows']]==['M%1'] def test_service_import_order_is_independent(tmp_path): import os,subprocess,sys root=Path(__file__).resolve().parents[2] result=subprocess.run([sys.executable,'-c','import toolsvc.query_indexes; import toolsvc.api'],cwd=root/'tools-service',env={**os.environ,'CDA_DATA_DIR':str(tmp_path/'import-data'),'PYTHONIOENCODING':'utf-8'},capture_output=True,text=True,encoding='utf-8') assert result.returncode==0,result.stderr def test_upgrade_copies_stat_and_preserves_original_bytes(processed): import os,subprocess,sys,hashlib ws=store.create_workspace('copy migration') parent=store.register_upload(ws['id'],'marker.txt',b'x',index=False) obj=store.register_processed(ws['id'],parent['id'],'old',processed['output'],processed['result'].to_dict()) path=Path(obj['meta']['sqlite_path']);before=hashlib.sha256(path.read_bytes()).hexdigest() root=Path(__file__).resolve().parents[2] run=subprocess.run([sys.executable,str(root/'scripts/upgrade_query_storage.py'),'--apply','--summaries','--object-id',obj['id']],env={**os.environ,'PYTHONIOENCODING':'utf-8'},capture_output=True,text=True,encoding='utf-8') assert run.returncode==0,run.stderr result=json.loads(run.stdout)[0];new=store.get_object(result['new_object_id']) assert new['id']!=obj['id'] and new['meta']['sqlite_path']!=str(path) assert hashlib.sha256(path.read_bytes()).hexdigest()==before assert len(new['tables'])==25 def test_raw_calendar_range_and_sort_keep_original_text(): ws=store.create_workspace('calendar') obj=store.register_upload(ws['id'],'w.csv',b'YEAR,WEEK\n2025.0,10\n2025,2\n2025,1\n') result=read_rows(obj['id'],filters=[dict(column='YEAR',value=2025),dict(column='WEEK',op='>=',value=2)],order_by=[dict(column='WEEK',dir='asc')]) assert [r['values']['WEEK'] for r in result['rows']]==['2','10'] assert result['rows'][1]['values']['YEAR']=='2025.0'