upgrade_query_storage.py 4.0 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364
  1. """Dry-run by default. Raw derived indexes update in place; stat objects are copied.
  2. CDA_DATA_DIR=/absolute/data .venv/bin/python scripts/upgrade_query_storage.py --apply --summaries
  3. Preserves original stat files, row text/hashes, 23 workbook sheets and original business lineage.
  4. """
  5. import argparse
  6. import hashlib
  7. import json
  8. import shutil
  9. import sys
  10. from pathlib import Path
  11. ROOT=Path(__file__).resolve().parents[1]
  12. sys.path.insert(0,str(ROOT/'tools-service'))
  13. from toolsvc.objects import store,new_id
  14. from toolsvc.config import settings
  15. from toolsvc.query_indexes import INDEX_VERSION,create_query_indexes,ensure_raw_calendar,quote
  16. from toolsvc.customs.engine.storage import connect,sha256_file
  17. from toolsvc.customs.engine.query_summaries import build_query_summaries
  18. def main():
  19. p=argparse.ArgumentParser();p.add_argument('--apply',action='store_true');p.add_argument('--summaries',action='store_true');p.add_argument('--object-id');args=p.parse_args()
  20. results=[]
  21. for item in store.list_objects():
  22. if args.object_id and item['id']!=args.object_id:continue
  23. source=item['meta'].get('sqlite_path')
  24. if not source:continue
  25. if item['meta'].get('lineage',{}).get('query_index_version')==INDEX_VERSION:continue
  26. entry=dict(object_id=item['id'],apply=args.apply,strategy='derived_raw_index' if item['kind']=='raw' else 'copy_new_stat_object',indexes=[],new_summaries=[])
  27. if args.apply:
  28. original=Path(source)
  29. if item['kind']=='raw':target=original
  30. else:
  31. directory=settings.runs_dir/new_id('query_upgrade')
  32. shutil.copytree(original.parent,directory)
  33. target=directory/'stat.sqlite'
  34. if original.name!='stat.sqlite':shutil.copy2(directory/original.name,target)
  35. db=connect(target)
  36. try:
  37. for table in item['tables']:
  38. cols=[r[1] for r in db.execute(f'PRAGMA table_info({quote(table["sqlite_name"])})')]
  39. if item['kind']=='raw':
  40. ensure_raw_calendar(db,table['sqlite_name'],cols)
  41. cols=[r[1] for r in db.execute(f'PRAGMA table_info({quote(table["sqlite_name"])})')]
  42. entry['indexes']+=create_query_indexes(db,table['sqlite_name'],cols,raw=item['kind']=='raw')
  43. db.execute('ANALYZE');db.commit()
  44. exists=db.execute("SELECT 1 FROM sqlite_master WHERE name='q_class_month'").fetchone()
  45. finally:db.close()
  46. if item['kind']=='stat':
  47. sheets=[dict(sheet=t['name'],table=t['sqlite_name'],rows=t['rows'],columns=t['columns']) for t in item['tables']]
  48. if args.summaries and item['producer']=='customs_process' and {'1.0-3.0_MLCC','5.1_存储器'} <= {t['name'] for t in item['tables']} and (directory/'working.sqlite').exists() and not exists:
  49. working=connect(directory/'working.sqlite')
  50. try:extra=build_query_summaries(working,directory)
  51. finally:working.close()
  52. sheets += [s.__dict__ for s in extra];entry['new_summaries']=[s.sheet for s in extra]
  53. lineage={**item['meta'].get('lineage',{}),'query_parent_object':item['id'],'query_index_version':INDEX_VERSION}
  54. lineage['output_hash']='sha256:'+hashlib.sha256((item['sha256']+sha256_file(target)+INDEX_VERSION).encode()).hexdigest()
  55. result=dict(sheets=sheets,lineage=lineage,stats=item['meta'].get('stats',{}),warnings=item['meta'].get('warnings',[]),xlsx_path=str(directory/'统计结果.xlsx') if (directory/'统计结果.xlsx').exists() else None)
  56. created=store.register_processed(item['workspace_id'],item['parents'][0] if item['parents'] else item['id'],item['name']+'(查询优化)',directory,result,producer='query_index_upgrade')
  57. entry['new_object_id']=created['id']
  58. results.append(entry)
  59. print(json.dumps(results,ensure_ascii=False,indent=2))
  60. if __name__=='__main__':main()