998211c483
- 从服务器拉取完整代码 - 按框架规范整理项目结构 - 配置 Drone CI 测试环境部署 - 包含后端(FastAPI)、前端(Vue3)、管理端 技术栈: Vue3 + TypeScript + FastAPI + MySQL
569 lines
16 KiB
YAML
569 lines
16 KiB
YAML
openapi: 3.1.0
|
||
info:
|
||
title: KaoPeiLian SQL Executor API
|
||
description: |
|
||
SQL 执行器 API，专门为 Dify 平台集成设计，支持对考陪练系统数据库执行查询和写入操作。
|
||
|
||
## 主要功能
|
||
- 执行 SQL 查询和写入操作
|
||
- 支持参数化查询防止 SQL 注入
|
||
- 获取数据库表列表和表结构
|
||
- SQL 语句验证
|
||
|
||
## 安全说明
|
||
所有接口都需要 JWT Bearer Token 认证。请先通过登录接口获取访问令牌。
|
||
version: 1.0.0
|
||
contact:
|
||
name: KaoPeiLian Tech Support
|
||
email: support@kaopeilian.com
|
||
|
||
servers:
|
||
- url: http://120.79.247.16:8000/api/v1
|
||
description: 考陪练系统服务器
|
||
- url: http://localhost:8000/api/v1
|
||
description: 本地开发服务器
|
||
- url: http://aiedu.ireborn.com.cn/api/v1
|
||
description: 域名访问
|
||
|
||
security:
|
||
- bearerAuth: []
|
||
|
||
paths:
|
||
/auth/login:
|
||
post:
|
||
tags:
|
||
- 认证
|
||
summary: 用户登录
|
||
description: 获取访问令牌，用于后续 API 调用
|
||
security: [] # 登录接口不需要认证
|
||
requestBody:
|
||
required: true
|
||
content:
|
||
application/json:
|
||
schema:
|
||
$ref: '#/components/schemas/LoginRequest'
|
||
examples:
|
||
admin:
|
||
summary: 管理员登录
|
||
value:
|
||
username: admin
|
||
password: admin123
|
||
responses:
|
||
'200':
|
||
description: 登录成功
|
||
content:
|
||
application/json:
|
||
schema:
|
||
$ref: '#/components/schemas/LoginResponse'
|
||
'401':
|
||
description: 用户名或密码错误
|
||
content:
|
||
application/json:
|
||
schema:
|
||
$ref: '#/components/schemas/ErrorResponse'
|
||
|
||
/sql/execute-simple:
|
||
post:
|
||
tags:
|
||
- SQL执行器-简化认证
|
||
summary: 执行 SQL 语句（简化认证版）
|
||
description: |
|
||
执行查询或写入 SQL 语句，使用简化的认证方式。
|
||
|
||
**认证方式（二选一）**：
|
||
1. API Key: X-API-Key: dify-2025-kaopeilian
|
||
2. 长期 Token: Authorization: Bearer permanent-token-for-dify-2025
|
||
|
||
**查询操作**: SELECT, SHOW, DESCRIBE
|
||
**写入操作**: INSERT, UPDATE, DELETE, CREATE, ALTER, DROP
|
||
|
||
支持参数化查询，使用 `:param_name` 格式定义参数。
|
||
security:
|
||
- apiKey: []
|
||
- bearerAuth: []
|
||
requestBody:
|
||
required: true
|
||
content:
|
||
application/json:
|
||
schema:
|
||
$ref: '#/components/schemas/SqlExecuteRequest'
|
||
examples:
|
||
simpleQuery:
|
||
summary: 简单查询
|
||
value:
|
||
sql: "SELECT id, username, role FROM users LIMIT 5"
|
||
parameterizedQuery:
|
||
summary: 参数化查询
|
||
value:
|
||
sql: "SELECT * FROM courses WHERE category = :category AND status = :status"
|
||
params:
|
||
category: "护肤"
|
||
status: "active"
|
||
insertData:
|
||
summary: 插入数据
|
||
value:
|
||
sql: "INSERT INTO knowledge_points (title, content, course_id) VALUES (:title, :content, :course_id)"
|
||
params:
|
||
title: "面部护理基础"
|
||
content: "面部护理的基本步骤..."
|
||
course_id: 1
|
||
responses:
|
||
'200':
|
||
description: SQL 执行成功
|
||
content:
|
||
application/json:
|
||
schema:
|
||
oneOf:
|
||
- $ref: '#/components/schemas/QueryResponse'
|
||
- $ref: '#/components/schemas/ExecuteResponse'
|
||
'401':
|
||
description: 未认证或认证失败
|
||
content:
|
||
application/json:
|
||
schema:
|
||
$ref: '#/components/schemas/ErrorResponse'
|
||
'500':
|
||
description: SQL 执行错误
|
||
content:
|
||
application/json:
|
||
schema:
|
||
$ref: '#/components/schemas/ErrorResponse'
|
||
|
||
/sql/execute:
|
||
post:
|
||
tags:
|
||
- SQL执行器
|
||
summary: 执行 SQL 语句（标准认证版）
|
||
description: |
|
||
执行查询或写入 SQL 语句。
|
||
|
||
**查询操作**: SELECT, SHOW, DESCRIBE
|
||
**写入操作**: INSERT, UPDATE, DELETE, CREATE, ALTER, DROP
|
||
|
||
支持参数化查询，使用 `:param_name` 格式定义参数。
|
||
requestBody:
|
||
required: true
|
||
content:
|
||
application/json:
|
||
schema:
|
||
$ref: '#/components/schemas/SqlExecuteRequest'
|
||
examples:
|
||
simpleQuery:
|
||
summary: 简单查询
|
||
value:
|
||
sql: "SELECT id, username, role FROM users LIMIT 5"
|
||
parameterizedQuery:
|
||
summary: 参数化查询
|
||
value:
|
||
sql: "SELECT * FROM courses WHERE category = :category AND status = :status"
|
||
params:
|
||
category: "护肤"
|
||
status: "active"
|
||
insertData:
|
||
summary: 插入数据
|
||
value:
|
||
sql: "INSERT INTO knowledge_points (title, content, course_id) VALUES (:title, :content, :course_id)"
|
||
params:
|
||
title: "面部护理基础"
|
||
content: "面部护理的基本步骤..."
|
||
course_id: 1
|
||
responses:
|
||
'200':
|
||
description: SQL 执行成功
|
||
content:
|
||
application/json:
|
||
schema:
|
||
oneOf:
|
||
- $ref: '#/components/schemas/QueryResponse'
|
||
- $ref: '#/components/schemas/ExecuteResponse'
|
||
examples:
|
||
queryResult:
|
||
summary: 查询结果
|
||
value:
|
||
code: 200
|
||
message: "SQL 执行成功"
|
||
data:
|
||
type: "query"
|
||
columns: ["id", "username", "role"]
|
||
rows:
|
||
- id: 1
|
||
username: "admin"
|
||
role: "admin"
|
||
- id: 2
|
||
username: "user1"
|
||
role: "trainee"
|
||
row_count: 2
|
||
executeResult:
|
||
summary: 写入结果
|
||
value:
|
||
code: 200
|
||
message: "SQL 执行成功"
|
||
data:
|
||
type: "execute"
|
||
affected_rows: 1
|
||
success: true
|
||
'400':
|
||
description: 请求参数错误
|
||
content:
|
||
application/json:
|
||
schema:
|
||
$ref: '#/components/schemas/ErrorResponse'
|
||
'401':
|
||
description: 未认证或认证失败
|
||
content:
|
||
application/json:
|
||
schema:
|
||
$ref: '#/components/schemas/ErrorResponse'
|
||
'500':
|
||
description: SQL 执行错误
|
||
content:
|
||
application/json:
|
||
schema:
|
||
$ref: '#/components/schemas/ErrorResponse'
|
||
|
||
/sql/validate:
|
||
post:
|
||
tags:
|
||
- SQL执行器
|
||
summary: 验证 SQL 语法
|
||
description: 验证 SQL 语句的语法正确性，不执行实际操作
|
||
requestBody:
|
||
required: true
|
||
content:
|
||
application/json:
|
||
schema:
|
||
$ref: '#/components/schemas/SqlValidateRequest'
|
||
responses:
|
||
'200':
|
||
description: 验证完成
|
||
content:
|
||
application/json:
|
||
schema:
|
||
$ref: '#/components/schemas/ValidateResponse'
|
||
|
||
/sql/tables:
|
||
get:
|
||
tags:
|
||
- SQL执行器
|
||
summary: 获取表列表
|
||
description: 获取数据库中所有表的列表
|
||
responses:
|
||
'200':
|
||
description: 成功获取表列表
|
||
content:
|
||
application/json:
|
||
schema:
|
||
$ref: '#/components/schemas/TablesResponse'
|
||
'401':
|
||
description: 未认证
|
||
content:
|
||
application/json:
|
||
schema:
|
||
$ref: '#/components/schemas/ErrorResponse'
|
||
|
||
/sql/table/{table_name}/schema:
|
||
get:
|
||
tags:
|
||
- SQL执行器
|
||
summary: 获取表结构
|
||
description: 获取指定表的结构信息，包括字段名、类型、约束等
|
||
parameters:
|
||
- name: table_name
|
||
in: path
|
||
required: true
|
||
description: 表名
|
||
schema:
|
||
type: string
|
||
pattern: '^[a-zA-Z_][a-zA-Z0-9_]*$'
|
||
example: users
|
||
responses:
|
||
'200':
|
||
description: 成功获取表结构
|
||
content:
|
||
application/json:
|
||
schema:
|
||
$ref: '#/components/schemas/TableSchemaResponse'
|
||
'400':
|
||
description: 无效的表名
|
||
content:
|
||
application/json:
|
||
schema:
|
||
$ref: '#/components/schemas/ErrorResponse'
|
||
'401':
|
||
description: 未认证
|
||
content:
|
||
application/json:
|
||
schema:
|
||
$ref: '#/components/schemas/ErrorResponse'
|
||
|
||
components:
|
||
securitySchemes:
|
||
bearerAuth:
|
||
type: http
|
||
scheme: bearer
|
||
bearerFormat: JWT
|
||
description: |
|
||
使用登录接口返回的 access_token。
|
||
格式: Bearer {access_token}
|
||
apiKey:
|
||
type: apiKey
|
||
in: header
|
||
name: X-API-Key
|
||
description: |
|
||
API Key 认证，适用于内部服务调用。
|
||
示例: X-API-Key: dify-2025-kaopeilian
|
||
|
||
schemas:
|
||
LoginRequest:
|
||
type: object
|
||
required:
|
||
- username
|
||
- password
|
||
properties:
|
||
username:
|
||
type: string
|
||
description: 用户名
|
||
example: admin
|
||
password:
|
||
type: string
|
||
format: password
|
||
description: 密码
|
||
example: admin123
|
||
|
||
LoginResponse:
|
||
type: object
|
||
properties:
|
||
code:
|
||
type: integer
|
||
example: 200
|
||
message:
|
||
type: string
|
||
example: 登录成功
|
||
data:
|
||
type: object
|
||
properties:
|
||
user:
|
||
type: object
|
||
properties:
|
||
id:
|
||
type: integer
|
||
example: 1
|
||
username:
|
||
type: string
|
||
example: admin
|
||
role:
|
||
type: string
|
||
example: admin
|
||
token:
|
||
type: object
|
||
properties:
|
||
access_token:
|
||
type: string
|
||
description: JWT 访问令牌
|
||
example: eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9...
|
||
token_type:
|
||
type: string
|
||
example: bearer
|
||
expires_in:
|
||
type: integer
|
||
description: 过期时间（秒）
|
||
example: 1800
|
||
|
||
SqlExecuteRequest:
|
||
type: object
|
||
required:
|
||
- sql
|
||
properties:
|
||
sql:
|
||
type: string
|
||
description: 要执行的 SQL 语句
|
||
example: "SELECT * FROM users WHERE role = :role"
|
||
params:
|
||
type: object
|
||
description: SQL 参数字典，键为参数名，值为参数值
|
||
additionalProperties: true
|
||
example:
|
||
role: admin
|
||
|
||
SqlValidateRequest:
|
||
type: object
|
||
required:
|
||
- sql
|
||
properties:
|
||
sql:
|
||
type: string
|
||
description: 要验证的 SQL 语句
|
||
example: "SELECT * FROM users"
|
||
|
||
QueryResponse:
|
||
type: object
|
||
properties:
|
||
code:
|
||
type: integer
|
||
example: 200
|
||
message:
|
||
type: string
|
||
example: SQL 执行成功
|
||
data:
|
||
type: object
|
||
properties:
|
||
type:
|
||
type: string
|
||
enum: [query]
|
||
example: query
|
||
columns:
|
||
type: array
|
||
items:
|
||
type: string
|
||
description: 列名数组
|
||
example: ["id", "username", "role"]
|
||
rows:
|
||
type: array
|
||
items:
|
||
type: object
|
||
additionalProperties: true
|
||
description: 查询结果行
|
||
row_count:
|
||
type: integer
|
||
description: 返回的行数
|
||
example: 5
|
||
|
||
ExecuteResponse:
|
||
type: object
|
||
properties:
|
||
code:
|
||
type: integer
|
||
example: 200
|
||
message:
|
||
type: string
|
||
example: SQL 执行成功
|
||
data:
|
||
type: object
|
||
properties:
|
||
type:
|
||
type: string
|
||
enum: [execute]
|
||
example: execute
|
||
affected_rows:
|
||
type: integer
|
||
description: 影响的行数
|
||
example: 1
|
||
success:
|
||
type: boolean
|
||
example: true
|
||
|
||
ValidateResponse:
|
||
type: object
|
||
properties:
|
||
code:
|
||
type: integer
|
||
example: 200
|
||
message:
|
||
type: string
|
||
example: SQL 验证完成
|
||
data:
|
||
type: object
|
||
properties:
|
||
valid:
|
||
type: boolean
|
||
description: SQL 是否有效
|
||
example: true
|
||
warnings:
|
||
type: array
|
||
items:
|
||
type: string
|
||
description: 警告信息列表
|
||
example: ["包含危险操作: DROP"]
|
||
sql_type:
|
||
type: string
|
||
description: SQL 类型
|
||
example: SELECT
|
||
|
||
TablesResponse:
|
||
type: object
|
||
properties:
|
||
code:
|
||
type: integer
|
||
example: 200
|
||
message:
|
||
type: string
|
||
example: 获取表列表成功
|
||
data:
|
||
type: object
|
||
properties:
|
||
tables:
|
||
type: array
|
||
items:
|
||
type: string
|
||
description: 表名列表
|
||
example: ["users", "courses", "exams"]
|
||
count:
|
||
type: integer
|
||
description: 表的数量
|
||
example: 20
|
||
|
||
TableSchemaResponse:
|
||
type: object
|
||
properties:
|
||
code:
|
||
type: integer
|
||
example: 200
|
||
message:
|
||
type: string
|
||
example: 获取表结构成功
|
||
data:
|
||
type: object
|
||
properties:
|
||
table_name:
|
||
type: string
|
||
example: users
|
||
columns:
|
||
type: array
|
||
items:
|
||
type: object
|
||
properties:
|
||
field:
|
||
type: string
|
||
description: 字段名
|
||
example: id
|
||
type:
|
||
type: string
|
||
description: 字段类型
|
||
example: int(11)
|
||
null:
|
||
type: string
|
||
enum: ["YES", "NO"]
|
||
description: 是否可为空
|
||
example: NO
|
||
key:
|
||
type: string
|
||
description: 键类型（PRI, UNI, MUL）
|
||
example: PRI
|
||
default:
|
||
type: string
|
||
nullable: true
|
||
description: 默认值
|
||
example: null
|
||
extra:
|
||
type: string
|
||
description: 额外信息
|
||
example: auto_increment
|
||
column_count:
|
||
type: integer
|
||
description: 列的数量
|
||
example: 10
|
||
|
||
ErrorResponse:
|
||
type: object
|
||
properties:
|
||
detail:
|
||
type: string
|
||
description: 错误详情
|
||
example: SQL 执行失败: You have an error in your SQL syntax
|
||
|
||
tags:
|
||
- name: 认证
|
||
description: 用户认证相关接口
|
||
- name: SQL执行器
|
||
description: SQL 执行和管理相关接口
|