test_large_query_design.py 10 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186
  1. import json
  2. import sqlite3
  3. from decimal import Decimal
  4. from pathlib import Path
  5. import pytest
  6. from toolsvc.catalog import CLASS_SUMMARY, MODEL_SUMMARY, SPECS, describe_table
  7. from toolsvc.config import settings
  8. from toolsvc.customs.engine.numbers import StrictSum
  9. from toolsvc.db import database
  10. from toolsvc.objects import store
  11. from toolsvc.readers import read_rows, ReadError, QueryTimeout
  12. @pytest.fixture
  13. def raw():
  14. ws=store.create_workspace('bounded query tests')
  15. 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))
  16. return store.register_upload(ws['id'],'large.csv',body.encode())
  17. def test_business_indexes_and_search(raw):
  18. path,table=store.table_sqlite(raw['id'],'raw')
  19. with sqlite3.connect(path) as conn:
  20. 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()
  21. assert any('SEARCH' in r[3] and 'idx_query_' in r[3] for r in plan)
  22. def test_count_modes_and_filter_null_total(raw):
  23. q=dict(object_id=raw['id'],filters=[dict(column='YEAR',value='2025')],limit=3)
  24. auto=read_rows(**q)
  25. assert auto['total'] is None and not auto['total_exact'] and auto['truncated']
  26. exact=read_rows(**q,count_mode='exact')
  27. assert exact['total']==501 and exact['total_exact']
  28. assert read_rows(raw['id'],limit=3)['total']==501
  29. assert read_rows(raw['id'],count_mode='none')['total'] is None
  30. def test_bound_aggregate_no_fetchall(raw):
  31. q=read_rows(raw['id'],aggregate={'group_by':['G_MODEL'],'metrics':[{'fn':'sum','column':'MONORSUM'}]},limit=7)
  32. assert len(q['rows'])==7 and q['truncated'] and q['truncation_reason']=='row_limit'
  33. assert q['total'] is None and not q['exhausted']
  34. assert q['columns']==['G_MODEL','sum_MONORSUM']
  35. count=read_rows(raw['id'],aggregate={'group_by':['G_MODEL'],'metrics':[{'fn':'count'}]},limit=7,count_mode='exact')
  36. assert count['total']==501
  37. def test_keyset_no_gaps_and_bound_to_query(raw):
  38. kwargs=dict(object_id=raw['id'],limit=37,filters=[dict(column='YEAR',value='2025')])
  39. seen=[];q=read_rows(**kwargs)
  40. while True:
  41. seen += [r['_trace']['row_index'] for r in q['rows']]
  42. if not q['next_cursor']:break
  43. q=read_rows(**kwargs,cursor=q['next_cursor'])
  44. assert seen==list(range(501))
  45. q=read_rows(raw['id'],limit=2)
  46. with pytest.raises(ReadError,match='游标'):
  47. read_rows(raw['id'],filters=[dict(column='YEAR',value='2026')],cursor=q['next_cursor'])
  48. with pytest.raises(ReadError):read_rows(raw['id'],filters=[dict(column='YEAR',value='2025')],offset=10001)
  49. assert read_rows(raw['id'],offset=10001)['rows']==[]
  50. @pytest.mark.parametrize('version_field',['rules_hash','params_hash','input_sha256'])
  51. def test_cache_version_and_independent_rows(raw,version_field):
  52. q=read_rows(raw['id'],limit=2); again=read_rows(raw['id'],limit=2)
  53. assert not q['cache_hit'] and again['cache_hit']
  54. again['rows'][0]['values']['MONORSUM']='poison'
  55. assert read_rows(raw['id'],limit=2)['rows'][0]['values']['MONORSUM']!='poison'
  56. with database.transaction() as conn:
  57. meta=raw['meta'];meta['lineage']={version_field:'changed-version'}
  58. conn.execute('UPDATE objects SET meta_json=? WHERE id=?',(json.dumps(meta),raw['id']))
  59. changed=read_rows(raw['id'],limit=2)
  60. assert not changed['cache_hit'] and changed['query_version']!=q['query_version']
  61. def test_query_deadline(raw,monkeypatch):
  62. monkeypatch.setattr(settings,'query_timeout_ms',0)
  63. with pytest.raises(QueryTimeout):read_rows(raw['id'],count_mode='exact',use_cache=False)
  64. def test_response_byte_budget(raw,monkeypatch):
  65. monkeypatch.setattr(settings,'query_max_bytes',180)
  66. q=read_rows(raw['id'],limit=200,use_cache=False)
  67. assert q['truncated'] and q['truncation_reason']=='byte_limit' and q['next_cursor']
  68. monkeypatch.setattr(settings,'query_max_bytes',1)
  69. with pytest.raises(ReadError,match='单行'):read_rows(raw['id'],use_cache=False)
  70. def test_invalid_aggregation_names_fail(raw):
  71. for aggregate in [dict(group_by=['MISSING'],metrics=[dict(fn='count')]),
  72. dict(metrics=[dict(fn='sum',column='MONORSUM',alias='x'),dict(fn='count',alias='x')])]:
  73. with pytest.raises(ReadError):read_rows(raw['id'],aggregate=aggregate)
  74. def test_catalog_covers_all_original_sheets(processed):
  75. original=[s for s in processed['result'].sheets if s.kind!='query_summary']
  76. assert len(original)==23 and all(s.sheet in SPECS for s in original)
  77. for sheet in processed['result'].sheets:
  78. cat=describe_table(sheet.sheet,sheet.columns)
  79. assert all(cat[k] for k in ('subject','row_grain','coverage','aggregation'))
  80. def test_summaries_preserve_exact_total_and_missingness(processed):
  81. with sqlite3.connect(processed['result'].sqlite_path) as stat, sqlite3.connect(processed['output']/'working.sqlite') as working:
  82. for conn in (stat,working):conn.create_aggregate('dsum',1,StrictSum)
  83. expected=working.execute('SELECT dsum(amount),dsum(quantity),COUNT(*) FROM fact').fetchone()
  84. for table in ('q_class_month','q_model_month'):
  85. total=stat.execute(f'SELECT dsum("实际金额(美元)"),dsum("成交数量"),SUM(CAST("记录数" AS INTEGER)) FROM {table}').fetchone()
  86. 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]
  87. for row in stat.execute(f'SELECT "实际金额(美元)","成交数量","平均单价(美元/成交数量单位)" FROM {table}'):
  88. if row[0] and row[1] and Decimal(row[1]): assert Decimal(row[2])==Decimal(row[0])/Decimal(row[1])
  89. def test_stat_semantic_guard_and_sum_summary(processed):
  90. ws=store.create_workspace('summary')
  91. source=store.register_upload(ws['id'],'synthetic.csv',b'x\n1\n')
  92. item=store.register_processed(ws['id'],source['id'],'result',processed['output'],processed['result'].to_dict())
  93. for fn,column in [('sum','价格指数'),('avg','平均单价(美元/成交数量单位)')]:
  94. with pytest.raises(ReadError,match='不能'):
  95. read_rows(item['id'],table='1.0-3.0_MLCC',aggregate={'metrics':[dict(fn=fn,column=column)]})
  96. with pytest.raises(ReadError,match='重叠视图'):
  97. read_rows(item['id'],table='1.0-3.0_存储器',aggregate={'metrics':[dict(fn='sum',column='实际金额(美元)')]})
  98. q=read_rows(item['id'],table=CLASS_SUMMARY,aggregate={'metrics':[dict(fn='sum',column='实际金额(美元)')]})
  99. assert len(q['rows'])==1
  100. assert store.row_lineage(item['id'],MODEL_SUMMARY,0)['source_object_id']==source['id']
  101. def test_numeric_null_is_not_zero_and_literal_wildcards(processed):
  102. from toolsvc.readers import _where
  103. where,params=_where([dict(column='amount',value=0)],{'amount'},{'amount'})
  104. with sqlite3.connect(':memory:') as conn:
  105. from toolsvc.query_precision import register_precision
  106. register_precision(conn)
  107. conn.execute('CREATE TABLE t(amount TEXT,_num_amount REAL)')
  108. conn.executemany('INSERT INTO t VALUES(?,?)',[('',None),('0',0)])
  109. assert conn.execute('SELECT COUNT(*) FROM t WHERE '+where,params).fetchone()[0]==1
  110. def test_exact_financial_filter_and_order_after_float_collision():
  111. from toolsvc.readers import _connect,_where
  112. from toolsvc.query_precision import register_precision
  113. conn=sqlite3.connect(':memory:');register_precision(conn)
  114. conn.execute('CREATE TABLE t(amount TEXT,_num_amount REAL)')
  115. conn.executemany('INSERT INTO t VALUES(?,?)',[(str(v),float(v)) for v in [9007199254740992,9007199254740993]])
  116. for op,value,expected in [('=',9007199254740993,['9007199254740993']),('>',9007199254740992,['9007199254740993']),('!=',9007199254740992,['9007199254740993']),('in',[9007199254740993],['9007199254740993']),('not_in',[9007199254740992],['9007199254740993'])]:
  117. where,params=_where([dict(column='amount',op=op,value=value)],{'amount'},{'amount'})
  118. assert [r[0] for r in conn.execute('SELECT amount FROM t WHERE '+where,params)]==expected
  119. assert conn.execute('SELECT amount FROM t ORDER BY amount COLLATE DECIMAL_VALUE DESC LIMIT 1').fetchone()[0]=='9007199254740993'
  120. conn.close()
  121. def test_literal_search_does_not_expand_sql_wildcards():
  122. ws=store.create_workspace('literal')
  123. obj=store.register_upload(ws['id'],'l.csv',b'value\nM%1\nM_1\nM21\n')
  124. result=read_rows(obj['id'],filters=[dict(column='value',op='contains',value='%')])
  125. assert [r['values']['value'] for r in result['rows']]==['M%1']
  126. def test_service_import_order_is_independent(tmp_path):
  127. import os,subprocess,sys
  128. root=Path(__file__).resolve().parents[2]
  129. 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')
  130. assert result.returncode==0,result.stderr
  131. def test_upgrade_copies_stat_and_preserves_original_bytes(processed):
  132. import os,subprocess,sys,hashlib
  133. ws=store.create_workspace('copy migration')
  134. parent=store.register_upload(ws['id'],'marker.txt',b'x',index=False)
  135. obj=store.register_processed(ws['id'],parent['id'],'old',processed['output'],processed['result'].to_dict())
  136. path=Path(obj['meta']['sqlite_path']);before=hashlib.sha256(path.read_bytes()).hexdigest()
  137. root=Path(__file__).resolve().parents[2]
  138. 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')
  139. assert run.returncode==0,run.stderr
  140. result=json.loads(run.stdout)[0];new=store.get_object(result['new_object_id'])
  141. assert new['id']!=obj['id'] and new['meta']['sqlite_path']!=str(path)
  142. assert hashlib.sha256(path.read_bytes()).hexdigest()==before
  143. assert len(new['tables'])==25
  144. def test_raw_calendar_range_and_sort_keep_original_text():
  145. ws=store.create_workspace('calendar')
  146. obj=store.register_upload(ws['id'],'w.csv',b'YEAR,WEEK\n2025.0,10\n2025,2\n2025,1\n')
  147. result=read_rows(obj['id'],filters=[dict(column='YEAR',value=2025),dict(column='WEEK',op='>=',value=2)],order_by=[dict(column='WEEK',dir='asc')])
  148. assert [r['values']['WEEK'] for r in result['rows']]==['2','10']
  149. assert result['rows'][1]['values']['YEAR']=='2025.0'