Agent/knowhub/scripts/version_investigate.py

#!/usr/bin/env python3
"""
Step 1: 查看当前 DB version 分布，为 dev_dedup / dev_abstract 版本化做准备。

不改数据，只 SELECT。
"""
import sys
from pathlib import Path

sys.path.insert(0, str(Path(__file__).parent.parent.parent))
from knowhub.knowhub_db.pg_capability_store import PostgreSQLCapabilityStore


def show(cur, title, sql, params=None):
    print(f'\n=== {title} ===', flush=True)
    cur.execute(sql, params or ())
    rows = cur.fetchall()
    if not rows:
        print('  (无数据)', flush=True); return
    cols = list(rows[0].keys())
    print('  ' + ' | '.join(cols), flush=True)
    for r in rows:
        print('  ' + ' | '.join(str(r[c]) for c in cols), flush=True)


def main():
    s = PostgreSQLCapabilityStore()
    cur = s._get_cursor()
    try:
        cur.execute("SET statement_timeout = '30s'")

        # 主表版本分布
        for tbl in ['capability', 'strategy', 'knowledge', 'resource']:
            show(cur, f'{tbl} version 分布',
                 f'SELECT version, COUNT(*) n FROM {tbl} GROUP BY version ORDER BY n DESC')

        # requirement 是否有 version 列
        show(cur, 'requirement 表列结构',
             """SELECT column_name, data_type FROM information_schema.columns
                WHERE table_name='requirement' ORDER BY ordinal_position""")

        # junction 表行数
        for tbl in ['requirement_strategy', 'requirement_knowledge', 'requirement_capability',
                    'requirement_resource', 'strategy_capability', 'strategy_resource',
                    'strategy_knowledge', 'knowledge_capability', 'knowledge_resource']:
            cur.execute(f'SELECT COUNT(*) c FROM {tbl}')
            print(f'  {tbl}: {cur.fetchone()["c"]}', flush=True)

        # 备份表
        print('\n=== bk_20260422_* 备份表 ===', flush=True)
        cur.execute("""SELECT table_name FROM information_schema.tables
                       WHERE table_name LIKE 'bk_20260422_%' ORDER BY table_name""")
        for r in cur.fetchall():
            cur.execute(f'SELECT COUNT(*) c FROM {r["table_name"]}')
            print(f'  {r["table_name"]}: {cur.fetchone()["c"]}', flush=True)

        # 备份 strategy 里的字段（确认有 version 列以便恢复）
        show(cur, 'bk_20260422_strategy 列结构',
             """SELECT column_name, data_type FROM information_schema.columns
                WHERE table_name='bk_20260422_strategy' ORDER BY ordinal_position""")

        # 现在 strategy 表里 version 为 howard_strategy_instance 的有多少
        show(cur, 'strategy version=howard_strategy_instance 的条目示例',
             """SELECT id, name, version FROM strategy
                WHERE version='howard_strategy_instance' LIMIT 3""")

        # knowledge v0 示例
        show(cur, 'knowledge version=v0 示例',
             """SELECT id, version FROM knowledge WHERE version='v0' LIMIT 3""")

        # capability 现在的 version（可能是 NULL 或某默认值）
        show(cur, 'capability sample',
             """SELECT id, name, version FROM capability LIMIT 3""")

        # resource 现在的 version
        show(cur, 'resource sample',
             """SELECT id, version FROM resource LIMIT 3""")

    finally:
        cur.close(); s.close()


if __name__ == '__main__':
    main()