SQL Tutor

# 步骤1：提取 Schema（紧凑模式，适合作为 LLM 上下文）
python3 scripts/sql_query_helper.py --db-path data.db schema --compact

# 步骤2：分析 SQL 优化建议
python3 scripts/sql_query_helper.py optimize "SELECT * FROM orders WHERE user_id = 100"

# 步骤3：查看 EXPLAIN 执行计划
python3 scripts/sql_query_helper.py --db-path data.db explain "SELECT * FROM orders WHERE user_id = 100"

# 提取完整 Schema（JSON 格式，含样本数据）
python3 scripts/sql_query_helper.py --db-path data.db schema

# 紧凑模式（纯文本，适合嵌入 prompt）
python3 scripts/sql_query_helper.py --db-path data.db schema --compact

# 不采样数据
python3 scripts/sql_query_helper.py --db-path data.db schema --sample-rows 0

# PostgreSQL
python3 scripts/sql_query_helper.py --db-type postgres --dsn "host=localhost dbname=mydb user=reader" schema --compact

# 分析 SQL 查询（无需数据库连接，纯规则检测）
python3 scripts/sql_query_helper.py optimize "SELECT * FROM orders o, users u WHERE o.user_id = u.id"

python3 scripts/sql_query_helper.py optimize "SELECT name FROM users WHERE UPPER(email) LIKE '%@GMAIL.COM'"

python3 scripts/sql_query_helper.py optimize "SELECT id, (SELECT COUNT(*) FROM orders WHERE user_id = u.id) AS order_count FROM users u"

# SQLite EXPLAIN
python3 scripts/sql_query_helper.py --db-path data.db explain "SELECT * FROM orders WHERE user_id = 100"

# PostgreSQL EXPLAIN
python3 scripts/sql_query_helper.py --db-type postgres --dsn "host=localhost dbname=mydb" explain "SELECT * FROM orders WHERE user_id = 100"

# PostgreSQL EXPLAIN ANALYZE（实际执行查询，获取真实数据）
python3 scripts/sql_query_helper.py --db-type postgres --dsn "host=localhost dbname=mydb" explain --analyze "SELECT * FROM orders WHERE user_id = 100"

-- Database: sqlite
-- users (1500 rows): id INTEGER  PK, name TEXT, email TEXT, age INTEGER, created_at TEXT
--   IDX(unique): idx_users_email on (email)
-- orders (8200 rows): id INTEGER  PK, user_id INTEGER, amount REAL, status TEXT, created_at TEXT
--   FK: user_id -> users.id
--   IDX: idx_orders_user_id on (user_id)

{
  "sql": "SELECT * FROM orders o, users u WHERE o.user_id = u.id",
  "issues": [
    {
      "severity": "warning",
      "rule": "avoid-select-star",
      "message": "避免 SELECT *：只选择需要的列，减少 I/O 和网络传输",
      "suggestion": "将 SELECT * 改为显式列出需要的列名"
    },
    {
      "severity": "info",
      "rule": "implicit-join",
      "message": "使用了隐式连接（逗号分隔表），可读性差且易出错",
      "suggestion": "改用显式 JOIN ... ON 语法，提高可读性和可维护性"
    }
  ]
}

{
  "db_type": "sqlite",
  "query": "SELECT * FROM orders WHERE user_id = 100",
  "plan": [
    {"id": 2, "parent": 0, "detail": "SEARCH orders USING INDEX idx_orders_user_id (user_id=?)"}
  ],
  "interpretation": [
    {
      "severity": "ok",
      "type": "index-search",
      "detail": "使用索引查找: idx_orders_user_id",
      "suggestion": "索引查找效率良好"
    }
  ]
}