"""Small workload-driven index portfolio; no automatic index-per-column strategy.""" import hashlib import json from .query_precision import register_precision INDEX_VERSION = '2026-11-18.1' def quote(value): return '"' + str(value).replace('"', '""') + '"' def create_query_indexes(db, table, columns, raw=False): register_precision(db) cols = set(columns) portfolios = [] if raw: from .customs.engine.storage import ALIASES, normalize_header canonical = {} lookup = {normalize_header(k).upper(): v for k, v in ALIASES.items()} for col in columns: key = normalize_header(col).upper() canonical.setdefault(lookup.get(key, key), col) # Exact HS+year+week trace, or year+week scan across HS. Raw values untouched. for keys in [('HS_CODE', 'YEAR', 'WEEK'), ('YEAR', 'WEEK')]: if all(k in canonical for k in keys): portfolios.append([('_num_'+canonical[k]) if k in {'YEAR','WEEK'} and '_num_'+canonical[k] in cols else canonical[k] for k in keys] + ['_row_index']) else: # Timeline: one direction/class, chronological order. TopN: one month/scope. if table == 'q_class_month': candidates = [('进/出口','二级类目','年度','月度'), ('进/出口','年度','月度','二级类目','_num_实际金额(美元)')] elif table == 'q_model_month': candidates = [('进/出口','品牌','型号','年度','月度'), ('进/出口','年度','月度','二级类目','_num_实际金额(美元)')] else: candidates = [('进/出口','基础类目','年度','月度'), ('进/出口','年度','月度','统计范围','统计层级','_num_实际金额(美元)')] for keys in candidates: if all(k in cols for k in keys): actual = ['_num_'+k if k in {'年度','月度','ISO年','ISO周','归属年','归属月','选样年度'} and '_num_'+k in cols else k for k in keys] actual = ['实际金额(美元)' if k=='_num_实际金额(美元)' else k for k in actual] portfolios.append(actual + ['_row_index']) created=[] for keys in portfolios: orders = ['DESC' if k == '实际金额(美元)' else 'ASC' for k in keys] suffix=hashlib.sha256(json.dumps([table,keys,orders],ensure_ascii=False).encode()).hexdigest()[:12] name='idx_query_'+suffix key_sql = ','.join(quote(k)+(' COLLATE DECIMAL_VALUE' if k=='实际金额(美元)' else '')+' '+order for k,order in zip(keys,orders)) db.execute(f'CREATE INDEX IF NOT EXISTS {quote(name)} ON {quote(table)}({key_sql})') created.append(dict(name=name,columns=keys)) return created def create_monthly_indexes(db): """Keep existing three paths plus one measured covering focus query (4m fixture).""" indexes=[('class','direction,c1,c2,c3,c4,year'), ('model','direction,brand,model,month'), ('focus','direction,year,c1,c3,app,brand_country,brand'), ('focus_query','direction,year,c1,c3,brand,amount,quantity,n')] for suffix, columns in indexes: db.execute(f'CREATE INDEX IF NOT EXISTS idx_{suffix} ON monthly({columns})') db.execute('ANALYZE monthly') def raw_numeric_columns(columns): from .customs.engine.storage import ALIASES, normalize_header lookup = {normalize_header(k).upper(): v for k,v in ALIASES.items()} return [c for c in columns if lookup.get(normalize_header(c).upper(),normalize_header(c).upper()) in {'YEAR','WEEK'}] def ensure_raw_calendar(db, table, columns): """Backfill only missing derived calendar columns; keep raw text and row hashes.""" from .query_precision import number existing={r[1] for r in db.execute(f'PRAGMA table_info({quote(table)})')} new=[c for c in raw_numeric_columns(columns) if '_num_'+c not in existing] if not new:return for c in new:db.execute(f'ALTER TABLE {quote(table)} ADD COLUMN {quote("_num_"+c)} REAL') cursor=db.execute(f'SELECT _row_index,{",".join(quote(c) for c in new)} FROM {quote(table)} ORDER BY _row_index') update=f'UPDATE {quote(table)} SET '+','.join(quote('_num_'+c)+'=?' for c in new)+' WHERE _row_index=?' while True: rows=cursor.fetchmany(1000) if not rows:break batch=[] for row in rows: parsed=[number(v) for v in row[1:]] batch.append(tuple(float(v) if v is not None else None for v in parsed)+(row[0],)) db.executemany(update,batch)