Text-to-SQL Skill Guide (懂业务的SQL翻译官)

Usage

Generate SQL from natural language

scripts\run.ps1 "<natural_language_question>" --dbtype <mysql|postgresql> [--schema <path_to_schema.yaml>] [--metrics <path_to_metrics.yaml>] [--filter <key=value>]...

Examples: | Intent | Command | | --- | --- | | 查询昨天的销售额 | scripts\run.ps1 "昨天的销售额是多少" --dbtype mysql | | 统计用户复购率 | scripts\run.ps1 "最近30天用户复购率" --dbtype postgresql --filter time_range=30d | | 获取高价值订单列表 | scripts\run.ps1 "金额超过1000的已支付订单" --dbtype mysql --filter min_amount=1000 | Important:

• scripts\run.ps1 is the only supported interface — do not call other scripts directly.
• --dbtype is required: must be mysql, postgresql, or sqlite.
• Schema and metric definitions are loaded from config/schema.yaml and config/metrics.yaml by default.
• First call ensures the local environment; subsequent calls are fast.
• Never fall back to a cloud service — all processing runs locally.

OpenVINO™ LLM 增强模式（可选）

当自然语言查询比较复杂时，可以启用 OpenVINO 本地 LLM 来增强意图理解能力：

| 参数 | 说明 | | --- | --- | | --llm / --openvino | 启用 OpenVINO LLM 增强（不可用则自动降级到规则引擎） | | --generic | 强制使用通用查询模式（不依赖预定义指标） |

Examples: | Intent | Command | | --- | --- | | 通用模式查股票 | scripts\run.ps1 "价格低于5元的股票有多少只" --dbtype sqlite --generic | | LLM 增强模式 | scripts\run.ps1 "查询最近涨幅超过10%的低价股" --dbtype sqlite --llm | | 强制 LLM + 通用 | scripts\run.ps1 "按行业统计股价低于3元的股票数量" --dbtype sqlite --generic --openvino |

环境变量（可选）：

• TEXT2SQL_MODEL：自定义 LLM 模型名（默认 Qwen/Qwen2-0.5B-Instruct）
• TEXT2SQL_MODEL_DIR：本地模型目录（优先从本地加载）

Interpreting the reply

The output is a JSON object with three fields:

• sql (可执行SQL): The generated SQL statement, ready for execution
• explanation (逻辑解释): Natural language breakdown of the SQL logic for review
• warnings (风险提示): Potential issues like full-table scans, permission boundaries, or missing LIMIT

What this skill does NOT do

• Does NOT execute SQL against a real database (generates SQL only)
• Does NOT support write operations (INSERT/UPDATE/DELETE/DROP) — read-only SELECT
• Does NOT handle DDL or schema migrations
• Does NOT replace a human DBA for complex query optimization

MVP Configuration

This skill ships with three critical configurations that make generated SQL "actually usable" rather than just "looking right":

1) Schema Injection (config/schema.yaml)

Predefined table and field metadata with business descriptions:

tables:
  - name: orders
    description: 订单主表
    fields:
      - name: pay_amount
        desc: 实付金额（已扣除退款）
      - name: created_at
        desc: 下单时间
      - name: status
        desc: 订单状态
  - name: users
    description: 用户表
    fields:
      - name: user_id
        desc: 用户唯一标识
      - name: created_at
        desc: 注册时间

2) Business Metric Constraints (config/metrics.yaml)

Standardized metric definitions to ensure consistent calculation:

metrics:
  sales:
    definition: SUM(orders.pay_amount)
    filters:
      - orders.status = 'paid'
  repurchase_rate:
    definition: count(DISTINCT if(order_count>=2, user_id, null)) / count(DISTINCT user_id)

3) Safety Policy (config/safety.yaml)

Built-in safety guards:

• Only SELECT statements allowed
• SELECT * is prohibited (explicit columns required)
• LIMIT is enforced on all queries
• High-risk tables require manual confirmation

Database Integration (Schema Auto-Extract)

This skill can connect to your real database to auto-extract table structure. SQL execution is intentionally NOT supported — you run the generated SQL yourself.

Step 1: Set environment variables

# MySQL (默认端口 3306)
$env:DB_TYPE="mysql"
$env:DB_HOST="localhost"
$env:DB_PORT="3306"
$env:DB_USER="root"
$env:DB_PASSWORD="your_password"
$env:DB_NAME="your_database"

# PostgreSQL (默认端口 5432)
$env:DB_TYPE="postgresql"
$env:DB_HOST="localhost"
$env:DB_PORT="5432"
$env:DB_USER="postgres"
$env:DB_PASSWORD="your_password"
$env:DB_NAME="your_database"

# Oracle (默认端口 1521)
$env:DB_TYPE="oracle"
$env:DB_HOST="localhost"
$env:DB_PORT="1521"
$env:DB_USER="system"
$env:DB_PASSWORD="your_password"
$env:DB_NAME="ORCL"

Step 2: Install the database driver

# MySQL
pip install pymysql

# PostgreSQL
pip install psycopg2-binary

# Oracle
pip install oracledb

Step 3: Test the connection

python scripts\pipeline.py --test-db

Expected output:

{
  "ok": true,
  "db_type": "mysql",
  "host": "localhost",
  "database": "your_database",
  "version": "8.0.35"
}

Step 4: Extract Schema into config/schema.yaml

# 抽取所有表
python scripts\pipeline.py --extract-schema

# 只抽取指定的表
python scripts\pipeline.py --extract-schema --tables orders,users,order_items

# 输出到自定义路径
python scripts\pipeline.py --extract-schema --output D:\my_schema.yaml

The extractor reads:

• Table names and comments (表名和注释)
• Column names, types, and comments (字段名、类型、注释)
• Primary keys (主键)
• Foreign keys (外键关系)

Step 5: Enrich the generated schema.yaml

The auto-extracted schema uses DB comments as field descriptions. For better SQL generation quality, edit config/schema.yaml and enrich the desc fields with business meaning.

Example — before (auto-extracted):

- name: pay_amount
  desc: 支付金额
  type: decimal(12,2)

Example — after (enriched):

- name: pay_amount
  desc: 实付金额（已扣除退款，用于销售额统计）
  type: decimal(12,2)