| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186 |
- 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'
|