Files

T

man.zhong 1a0aecbd7e chore(g3fo-db-ops): add Windows + double-confirm rules

Made-with: Cursor

2026-03-17 17:20:24 +08:00

21 KiB

Raw Permalink Blame History

name, description

name	description
g3fo-db-ops	Comprehensive database operations guide for G3FO project. Use when updating G3FO database data, creating tables, modifying table structures, inserting system menus/routes, or managing database changes that require SQL generation, execution, and Git commit workflows. Includes automated workflows for i18n updates, version management, file naming conventions, and duplicate sequence number detection. Uses Python db_ops tool (not MCP MySQL) for database operations.

name

description

g3fo-db-ops

Comprehensive database operations guide for G3FO project. Use when updating G3FO database data, creating tables, modifying table structures, inserting system menus/routes, or managing database changes that require SQL generation, execution, and Git commit workflows. Includes automated workflows for i18n updates, version management, file naming conventions, and duplicate sequence number detection. Uses Python db_ops tool (not MCP MySQL) for database operations.

G3FO Database Operations

Guide for performing database operations in the G3FO project, including automated workflows for data updates, table management, and SQL file versioning.

Detailed documentation: See references/DATABASE_UPDATE_AUTOMATION_GUIDE.md for complete automation guide.

Database Tool (db_ops.py)

本技能不再使用 MCP MySQL，改用 Python 程序 scripts/db_ops.py 执行所有数据库操作。

安装依赖与运行前提

重要：本技能依赖本机可用的 Python 解释器。若未安装 Python 或 python 不在 PATH 中，在 Cursor 的 Shell 里直接执行脚本会出现 Windows Exit code: 9009（命令未找到）错误。

在本机安装 Python（3.8+），并确认命令行中可以直接运行：
```
python --version
```
如命令无效，请将 Python 安装目录加入系统 PATH，或使用 py / python3 等本机实际命令名。

安装依赖：

pip install -r .claude/skills/g3fo-db-ops/scripts/requirements.txt

默认连接配置

与 MCP user-mysql 一致，未指定时自动使用：

参数	默认值
host	192.168.3.233
port	3306
user	root
password	afe123456
database	g3fo_base

切换数据库：系统表用 g3fo_base，交易相关用 g3fo_trade。通过 --database g3fo_trade 或环境变量 MYSQL_DATABASE 覆盖。

覆盖连接方式

命令行参数：--host, --port, --user, --password, --database
环境变量：MYSQL_HOST, MYSQL_PORT, MYSQL_USER, MYSQL_PASSWORD, MYSQL_DATABASE

命令列表

命令	说明
`query`	执行 SELECT 查询
`execute`	执行 INSERT/UPDATE/DELETE
`list_tables`	列举数据库所有表
`describe_table`	获取表结构（列信息、表注释）
`call_procedure`	执行存储过程
`batch_execute`	批量执行 SQL（支持文件、DELIMITER）
`get_table_comment`	获取表注释
`set_table_comment`	修改表注释
`get_column_comment`	获取列注释
`set_column_comment`	修改列注释
`test_connection`	测试连接

使用示例

# 从 skill 根目录或 server 根目录执行，base_dir 为 .claude/skills/g3fo-db-ops 所在路径

# 测试连接
python scripts/db_ops.py test_connection
python scripts/db_ops.py --database g3fo_trade test_connection

# 查询
python scripts/db_ops.py query "SELECT * FROM m_system_code LIMIT 5"

# 执行增删改
python scripts/db_ops.py execute "REPLACE INTO g3fo_base.m_system_code (...) VALUES (...)"

# 列举所有表
python scripts/db_ops.py list_tables
python scripts/db_ops.py --database g3fo_trade list_tables

# 获取表结构
python scripts/db_ops.py describe_table m_system_code

# 执行存储过程
python scripts/db_ops.py call_procedure InsertSystemMenu --params "菜单备注" "menu-name" "title_remark" "ADMIN"

# 批量执行 SQL 文件
python scripts/db_ops.py batch_execute --file path/to/script.sql

# 修改表注释
python scripts/db_ops.py set_table_comment m_system_code "系统编码表"

在 Cursor 中调用时：使用 run_terminal_cmd 或 Shell 工具执行上述命令，工作目录为 d:\AFE Git\G3SF\G3FO\server，脚本路径为 .claude/skills/g3fo-db-ops/scripts/db_ops.py。

Windows 执行规范（必读）

Python 命令优先级（Windows）

优先使用：py
备选使用：python
要求：在 Windows 上执行本 skill 的所有命令时，先尝试 py，失败再尝试 python（不要反过来）。

控制台编码（避免 UnicodeEncodeError）

在 Windows/PowerShell 中执行 db_ops.py 时，如果输出包含中文，可能因控制台默认 GBK 编码导致 UnicodeEncodeError。执行前请统一设置：

# PowerShell
$env:PYTHONIOENCODING='utf-8'; py .claude/skills/g3fo-db-ops/scripts/db_ops.py test_connection

变更执行流程（双确认，强制）

生成 SQL 文件（按版本目录与序号规则落盘）
展示 SQL 内容并等待用户确认（用户确认 yes 后才允许执行）
执行 SQL（batch_execute --file / execute），确认返回 "success": true
再次询问用户是否提交 Git（用户确认后才允许 git pull/add/commit/push）

Core Principles

Use existing templates: Prioritize templates and tools in g3fo-db/common_sql/
Update i18n simultaneously: When updating tables requiring multilingual support, must also update m_system_i18n table
Automated workflow: Follow standardized automation including SQL generation, execution, and Git commit

Tables Requiring i18n Updates

When inserting or updating data in these tables, must simultaneously update m_system_i18n:

Table	i18n_id Format	Example
`m_system_code`	`CODE-{4位数字ID}`	`CODE-0404`
`m_system_error_message`	`ERR-{error_code}`	`ERR-17100`
`m_market`	`MKT-{4位数字ID}`	`MKT-0001`
`m_currency`	`CURR-{currency_code}`	`CURR-USD`
`m_channel`	`CH-{channel_code}`	`CH-001`
`m_exchange`	`EXCH-{exchange_code}`	`EXCH-HKEX`
`m_country`	`CNTY-{country_code}`	`CNTY-HK`
`m_exchange_charge`	`EX_CHARGE-{id}`	`EX_CHARGE-1`
`m_company_charge`	`COM_CHARGE-{id}`	`COM_CHARGE-1`

Automated Workflow

1. Data Input Phase

Request user input based on table type. For m_system_code:

Code_type: [编码类型]
Code_value: [编码值]
Remark: [备注（用于生成多语言描述）]
Sort: [排序号]

2. SQL Generation Phase

Get max ID (if applicable):

SELECT COALESCE(MAX(id), 0) + 1 FROM {schema}.{table_name};

Generate i18n_id: Use format rules based on table type
Generate multilingual descriptions:
- Extract or generate English, Simplified Chinese, Traditional Chinese from remark
- Vietnamese and French optional
Generate complete SQL:
- Main table INSERT/UPDATE
- m_system_i18n INSERT/UPDATE (if applicable)

3. User Confirmation Phase

Display generated SQL and ask:

已生成以下 SQL 语句，请确认数据是否正确：

[显示SQL语句]

确认数据无误，是否继续下一步？(yes/no)

4. SQL Execution Phase

If user confirms:

Use db_ops.py to execute SQL (run in terminal):
- Default connection (see "Database Tool" section above): host 192.168.3.233, user root, password afe123456
- System tables: --database g3fo_base
- Trade tables: --database g3fo_trade
- Override via --host, --port, --user, --password, --database if user provides different connection info
For single SELECT: python .claude/skills/g3fo-db-ops/scripts/db_ops.py query "SQL"
For INSERT/UPDATE/DELETE: python .claude/skills/g3fo-db-ops/scripts/db_ops.py execute "SQL"
For batch SQL or file: python .claude/skills/g3fo-db-ops/scripts/db_ops.py batch_execute --file path/to/file.sql
Verify results (check JSON output for "success": true)
If failed, report error and stop

5. Git Commit Phase

After successful execution, ask:

SQL 执行成功！

是否需要将 SQL 提交到待执行文件夹中？(yes/no)

If confirmed, execute in this order to ensure remote repository and local data consistency:

Switch to g3fo-db directory and pull latest code (must execute first):
```
cd g3fo-db
git pull origin main
```
- This step ensures getting latest file list and changes from remote repository
- If pull fails (e.g., conflicts), report error and stop workflow
Determine target folder (based on latest pulled code):
- Find latest "pending release {version}" folder in update_service_release
- If not exists, create new folder:
  - View all version number folders in update_service_release (e.g., 1.5.1.0, 1.5.1.1, 1.5.1.17, etc.)
  - Find maximum version number
  - New version = max version + 0.0.0.1 (increment last digit)
  - Create folder: pending release {新版本号}
  - Auto-create set_version_01.sql:
```
UPDATE `g3fo_base`.`m_system_setting`
SET `param_value` = '1.5.1.0 M{版本号最后两位数字}',
    `update_on` = NOW()
WHERE
    `param_name` = 'base.systemVersion';
UPDATE `g3fo_base`.`m_system_setting`
SET `param_value` = '0',
    `update_on` = NOW()
WHERE
    `param_name` = 'base.patchNo';
```
  - Version mapping rules:
    - Extract last digit N from version number (format X.Y.Z.N)
    - Format as '1.5.1.0 M{N}'
    - Examples:
      - 1.5.1.17 → extract 17 → '1.5.1.0 M17'
      - 1.5.1.18 → extract 18 → '1.5.1.0 M18'
      - 1.5.1.10 → extract 10 → '1.5.1.0 M10'
      - 1.5.1.9 → extract 9 → '1.5.1.0 M9' (no zero padding)
- Note: Folder list is now latest, avoiding filename conflicts
Generate filename (based on latest file list):
- Format: {功能描述英文}_{序号}.sql
- Sequence number rules:
  - View all .sql files in target folder (after git pull)
  - Calculate sequence number across all files uniformly, regardless of filename prefix
  - Extract sequence number method:
    1. Traverse all .sql files in folder
    2. Extract two-digit number after underscore from each filename (format: _XX.sql)
    3. Example: set_version_01.sql → extract 01, add_system_code_02.sql → extract 02
    4. Find maximum sequence number among all
    5. New sequence = max + 1
  - Use two-digit format (01-99)
  - Example:
    - If folder has: set_version_01.sql and add_system_code_02.sql
    - Extracted sequences: 01, 02
    - Max sequence: 02
    - New file sequence: 03
    - New filename: add_new_feature_03.sql
- Important: Must generate after git pull to avoid conflicts
Detect and fix duplicate sequence numbers (before saving):
- Scan all .sql files in target folder
- Detect duplicates
- If found, follow "Duplicate Sequence Detection and Auto-Fix" rules
- Ensure all file sequences are unique and continuous
Save SQL file to target folder
Git commit and push:
```
git add .
git commit -m "feat: {简短描述}"
git push origin main
```
If push fails (remote has new commits):
- Execute git pull origin main again
- Resolve conflicts and push again

6. Completion Message

操作完成！

已执行的操作：
✓ SQL 语句已执行
✓ SQL 文件已保存到: update_service_release/pending release {version}/{filename}
✓ 已推送到 Git 仓库

Using Existing Templates

Important: Adding menus and route permissions must use stored procedure files from common_sql folder.

Files to use:

references/insert_system_menu_procedure.sql - Insert system menu
references/insert_system_route_procedure.sql - Insert system route

Execution and commit rules:

SQL execution phase:
- Use db_ops.py batch_execute --file for SQL files with DELIMITER (script handles it)
- Or use call_procedure for stored procedure calls
- Or use execute with direct INSERT INTO / REPLACE INTO if data matches procedure output
Git commit phase:
- Must use complete SQL from stored procedure file
- Copy full stored procedure definition from common_sql folder
- Add CALL statement at end to invoke procedure
- Reference format in update_service_release/pending release 1.5.1.18

Insert system menu example:

-- Copy complete stored procedure definition from insert_system_menu_procedure.sql
-- ... (complete stored procedure code) ...

-- Add CALL statement at end
CALL InsertSystemMenu('菜单备注', 'menu-name', 'title_remark', 'ADMIN', NULL, 1);

Insert system route example:

-- Copy complete stored procedure definition from insert_system_route_procedure.sql
-- ... (complete stored procedure code) ...

-- Add CALL statement at end
CALL InsertSystemRoute('备注', '/route/path', '标题备注', 'ADMIN'|'USER');

File naming:

Menu: insert_system_menu_procedure_{序号}.sql
Route: insert_system_route_procedure_{序号}.sql

Create New Table

Reference references/create_table_template.sql format:

Use CREATE TABLE IF NOT EXISTS
Specify correct schema (e.g., g3fo_trade, g3fo_base)
Include necessary fields, indexes, comments
Use ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Table Structure Changes

Reference references/table_change_template.sql method:

Use stored procedure to check if column exists
Use IF EXISTS for idempotency checks
Delete procedure after execution

列新增/DDL（强制使用存储过程模板）

所有涉及 ALTER TABLE 的结构变更（新增列/修改列/删除列等），一律按 references/table_change_template.sql 的模式执行：

USE <schema>;
DROP PROCEDURE IF EXISTS <ProcName>;
CREATE PROCEDURE <ProcName>() BEGIN ... END;
在过程内通过 information_schema.columns 做存在性判断后再执行 ALTER TABLE
CALL <ProcName>();
DROP PROCEDURE IF EXISTS <ProcName>;

Order Table Field Synchronization Rule

Important: When m_order table adds a field, must simultaneously add same field to:

m_order_actions - Order actions table
h_order - Order history table
h_order_actions - Order actions history table

Rules:

Field name must be identical
Field type must be identical
Field constraints (NULL/NOT NULL, DEFAULT, etc.) must be identical
Field comment must be identical
Field position (AFTER clause) should be consistent

SQL generation requirements:

Use stored procedure for idempotency checks (reference table_change_template.sql)
Check field existence for each table separately, add if not exists
Ensure all four tables' field changes in same SQL file

Example: If need to add field new_field VARCHAR(100) COMMENT '新字段' to m_order table, must simultaneously add same field to m_order_actions, h_order, h_order_actions tables.

SQL Statement Standards

Use REPLACE INTO instead of INSERT INTO for idempotency
Timestamp fields:
- create_on: NOW()
- update_on: NOW()
- create_by: 'sys'
- update_by: 'sys'
Add comments: Add function description comment at SQL file beginning

File Naming Standards

Format: {功能描述英文}_{序号}.sql
Sequence: Two-digit number (01-99), auto-increment
Examples: add_system_code_02.sql, update_system_code_03.sql

Duplicate Sequence Detection and Auto-Fix

Important: Must detect and fix duplicate sequence numbers before generating new files or committing to Git

Detection Rules

Scan target folder: Check all .sql files in update_service_release/pending release {version}/
Extract sequence: Extract two-digit number after underscore from each filename
Detect duplicates: If multiple files use same sequence, trigger fix flow

Fix Rules

When duplicate sequences found (e.g., xxxxxx_03.sql and aaaaaaa_03.sql):

Analyze file dependencies:
- Compare SQL content of both files
- Check for dependencies:
  - File A references table/field/data created in File B
  - File A uses stored procedure/function defined in File B
  - File A updates data inserted in File B
- Dependency detection methods:
  - Check if table names, field names, or data values in one file appear in another file
  - Check if CREATE/DROP operations in one file have corresponding references in another file
  - Check if INSERT/UPDATE operations in one file have corresponding queries in another file
Determine file order:
- If dependencies exist:
  - Dependent file (referenced) keeps original sequence
  - Dependent file gets next available sequence
- If no dependencies:
  - Order by file creation time (filesystem time)
  - Earlier file keeps original sequence
  - Later file gets next available sequence
Auto-extend subsequent files:
- Find next available sequence (max + 1)
- Rename file needing adjustment to new sequence
- Check if subsequent files need extension:
  - If new sequence conflicts with subsequent files, auto-extend them
  - Recursively process until no conflicts
Execute fix:
- Rename files (use git mv to preserve Git history)
- Update Git index
- Generate fix report

Fix Examples

Scenario 1: With dependencies

Files: create_table_03.sql (creates table m_new_table), insert_data_03.sql (inserts data into m_new_table)
Detection: insert_data_03.sql depends on create_table_03.sql
Fix:
- create_table_03.sql keeps 03
- insert_data_03.sql → insert_data_04.sql

Scenario 2: No dependencies, order by creation time

Files: add_system_code_03.sql (created: 2024-01-01), update_setting_03.sql (created: 2024-01-02)
Detection: No dependencies
Fix:
- add_system_code_03.sql keeps 03 (earlier)
- update_setting_03.sql → update_setting_04.sql (later)

Scenario 3: Need to extend subsequent files

Files: file_a_03.sql, file_b_03.sql, file_c_04.sql, file_d_05.sql
Detection: file_a_03.sql and file_b_03.sql duplicate, no dependencies, file_a_03.sql earlier
Fix:
- file_a_03.sql keeps 03
- file_b_03.sql → file_b_04.sql
- file_c_04.sql → file_c_05.sql (extended)
- file_d_05.sql → file_d_06.sql (extended)

Scenario 4: Multiple duplicate sequences

Files: file_a_03.sql, file_b_03.sql, file_c_04.sql, file_d_04.sql, file_e_05.sql
Detection: 03 duplicate, 04 also duplicate
Fix:
- First handle 03 duplicate: file_a_03.sql keeps 03, file_b_03.sql → file_b_04.sql
- Then handle 04 duplicate: file_c_04.sql keeps 04, file_d_04.sql → file_d_05.sql
- Extend: file_e_05.sql → file_e_06.sql

Fix Report Format

After fix completion, generate report:

检测到重复序号，已自动修复：

修复的文件：
- file_b_03.sql → file_b_04.sql（原因：与 file_a_03.sql 重复，无依赖关系，按创建时间调整）
- file_c_04.sql → file_c_05.sql（原因：序号冲突，自动顺延）
- file_d_05.sql → file_d_06.sql（原因：序号冲突，自动顺延）

所有文件序号已修复，无重复。

Execution Timing

Before generating new file: Detect and fix existing duplicate sequences
Before committing to Git: Detect and fix again, ensure no duplicates
Manual trigger: When user requests detection and fix

Error Handling

SQL execution failure: Display error, don't execute Git operations
Git operation failure: Display error, SQL file saved locally
Data validation failure: Validate data format before generating SQL

Important Reminders

All table operations requiring i18n must simultaneously update m_system_i18n table
When m_order table adds field, must simultaneously add same field to m_order_actions, h_order, h_order_actions
When adding menus and route permissions, execution can use other methods, but Git commit must use complete SQL from stored procedure file
All SQL files must be saved to correct version folder
File sequences cannot be duplicated, must be incremental. Must detect and fix duplicates before generating new files and committing to Git
Use REPLACE INTO to ensure SQL can be executed repeatedly
Must get user confirmation before executing SQL

Continuous Improvement

This workflow is continuously updated and improved. If new tables requiring i18n are discovered, new naming conventions are needed, or process issues are found, update the DATABASE_UPDATE_AUTOMATION_GUIDE.md document.

Reference Files

For detailed templates and examples, see:

references/DATABASE_UPDATE_AUTOMATION_GUIDE.md - Complete automation guide
references/create_table_template.sql - Table creation template
references/table_change_template.sql - Table structure change template
references/insert_system_menu_procedure.sql - System menu insertion procedure
references/insert_system_route_procedure.sql - System route insertion procedure
references/system_code_update_template.sql - System code update template
references/SQL_SITE_NAMING.md - SQL site naming conventions

21 KiB Raw Permalink Blame History Unescape Escape