012-kaopeilian/知识库/Dify工作流-SQL执行器使用指南.md

# Dify工作流 - SQL执行器使用指南

**API端点**：`/api/v1/sql/execute`  
**支持DELETE操作**：✅ 是  
**自动提交事务**：✅ 是  

---

## 📋 API端点信息

### 主要端点
```
POST /api/v1/sql/execute
POST /api/v1/sql/execute-simple  (简化认证，推荐用于Dify)
```

### 认证方式

#### 方式1：标准JWT Token（/execute）
```bash
Authorization: Bearer {access_token}
```

#### 方式2：API Key或长期Token（/execute-simple）✅ 推荐
```bash
# 方式A：API Key
X-API-Key: dify-2025-kaopeilian

# 方式B：长期Token
Authorization: Bearer permanent-token-for-dify-2025
```

---

## ✅ DELETE权限验证

### 权限状态
- ✅ **DELETE语句已支持** - 第224行有警告但不阻止执行
- ✅ **自动提交事务** - 第145行：`await db.commit()`
- ✅ **返回影响行数** - 第146-152行返回 `affected_rows`
- ✅ **日志记录** - 第154-158行记录操作日志

### 代码验证
```python
# 位置：/root/aiedu/kaopeilian-backend/app/api/v1/sql_executor.py

# 第143-152行：写入操作处理
else:
    # 写入操作（包括DELETE）
    await db.commit()  # ✅ 自动提交
    affected_rows = result.rowcount
    
    response_data = {
        "type": "execute",
        "affected_rows": affected_rows,  # ✅ 返回删除行数
        "success": True
    }
```

---

## 🚀 在Dify中使用

### 方案1：HTTP Request节点（推荐）

#### 节点配置
```yaml
节点名称: 清空资料知识点
节点类型: HTTP Request
URL: http://kaopeilian-backend-dev:8000/api/v1/sql/execute-simple
方法: POST
Headers:
  Content-Type: application/json
  X-API-Key: dify-2025-kaopeilian

Body (JSON):
{
  "sql": "DELETE FROM knowledge_points WHERE material_id = {{material_id}}"
}
```

#### 响应格式
```json
{
  "code": 200,
  "message": "SQL 执行成功",
  "data": {
    "type": "execute",
    "affected_rows": 6,
    "success": true
  }
}
```

---

### 方案2：Code节点（Python）

```python
import requests
import json

# 配置
api_url = "http://kaopeilian-backend-dev:8000/api/v1/sql/execute-simple"
api_key = "dify-2025-kaopeilian"
material_id = {{material_id}}

# 准备SQL
sql = f"DELETE FROM knowledge_points WHERE material_id = {material_id}"

# 发送请求
response = requests.post(
    api_url,
    headers={
        "Content-Type": "application/json",
        "X-API-Key": api_key
    },
    json={"sql": sql}
)

# 解析结果
result = response.json()

if result.get("code") == 200:
    data = result.get("data", {})
    affected_rows = data.get("affected_rows", 0)
    
    return {
        "success": True,
        "deleted_count": affected_rows,
        "message": f"已删除 {affected_rows} 个知识点"
    }
else:
    return {
        "success": False,
        "error": result.get("message", "未知错误"),
        "message": "删除失败"
    }
```

---

### 方案3：使用参数化查询（更安全）

```yaml
Body (JSON):
{
  "sql": "DELETE FROM knowledge_points WHERE material_id = :material_id",
  "params": {
    "material_id": {{material_id}}
  }
}
```

---

## 📝 完整工作流示例

### Dify工作流配置

```yaml
workflow:
  name: "重新生成资料知识点"
  
  nodes:
    # 节点1：清空旧知识点
    - id: "clear_old_kp"
      name: "清空旧知识点"
      type: "http_request"
      config:
        url: "http://kaopeilian-backend-dev:8000/api/v1/sql/execute-simple"
        method: "POST"
        headers:
          Content-Type: "application/json"
          X-API-Key: "dify-2025-kaopeilian"
        body:
          sql: "DELETE FROM knowledge_points WHERE material_id = {{material_id}}"
      outputs:
        - deleted_count: "{{response.data.affected_rows}}"
    
    # 节点2：验证删除结果
    - id: "verify_deletion"
      name: "验证删除"
      type: "http_request"
      config:
        url: "http://kaopeilian-backend-dev:8000/api/v1/sql/execute-simple"
        method: "POST"
        headers:
          Content-Type: "application/json"
          X-API-Key: "dify-2025-kaopeilian"
        body:
          sql: "SELECT COUNT(*) as count FROM knowledge_points WHERE material_id = {{material_id}}"
      outputs:
        - remaining_count: "{{response.data.rows[0].count}}"
    
    # 节点3：获取资料文件内容
    - id: "get_material"
      name: "获取资料"
      type: "http_request"
      config:
        url: "http://kaopeilian-backend-dev:8000/api/v1/courses/materials/{{material_id}}"
        method: "GET"
    
    # 节点4：AI分析生成知识点
    - id: "ai_analysis"
      name: "AI知识点分析"
      type: "llm"
      config:
        model: "gpt-4"
        prompt: |
          分析以下课程资料，提取知识点：
          
          文件名: {{get_material.data.name}}
          内容: {{file_content}}
          
          请提取：
          1. 知识点名称
          2. 知识点描述
          3. 知识点类型（理论知识/诊断设计/操作步骤/沟通话术/案例分析/注意事项/技巧方法/客诉处理）
          4. 与主题的关系
          
          返回JSON格式，数组形式。
    
    # 节点5：批量保存知识点
    - id: "save_kp"
      name: "保存知识点"
      type: "code"
      config:
        language: "python"
        code: |
          import requests
          import json
          
          api_url = "http://kaopeilian-backend-dev:8000/api/v1/sql/execute-simple"
          api_key = "dify-2025-kaopeilian"
          
          knowledge_points = {{ai_analysis.output}}
          material_id = {{material_id}}
          course_id = {{course_id}}
          
          success_count = 0
          
          for kp in knowledge_points:
              # 构造INSERT SQL
              sql = """
              INSERT INTO knowledge_points 
              (course_id, material_id, name, description, type, source, topic_relation, created_at)
              VALUES 
              (:course_id, :material_id, :name, :description, :type, 1, :topic_relation, NOW())
              """
              
              params = {
                  "course_id": course_id,
                  "material_id": material_id,
                  "name": kp.get("name"),
                  "description": kp.get("description"),
                  "type": kp.get("type", "理论知识"),
                  "topic_relation": kp.get("relation", "")
              }
              
              response = requests.post(
                  api_url,
                  headers={
                      "Content-Type": "application/json",
                      "X-API-Key": api_key
                  },
                  json={"sql": sql, "params": params}
              )
              
              if response.json().get("code") == 200:
                  success_count += 1
          
          return {
              "success": True,
              "total": len(knowledge_points),
              "saved": success_count,
              "message": f"成功保存 {success_count}/{len(knowledge_points)} 个知识点"
          }
```

---

## 🔍 SQL示例

### 1. 删除单个资料的知识点
```json
{
  "sql": "DELETE FROM knowledge_points WHERE material_id = 34"
}
```

### 2. 条件删除（仅AI生成的）
```json
{
  "sql": "DELETE FROM knowledge_points WHERE material_id = 34 AND source = 1"
}
```

### 3. 批量删除
```json
{
  "sql": "DELETE FROM knowledge_points WHERE material_id IN (34, 35, 36)"
}
```

### 4. 参数化删除（推荐）
```json
{
  "sql": "DELETE FROM knowledge_points WHERE material_id = :material_id",
  "params": {
    "material_id": 34
  }
}
```

### 5. 删除并返回数量
```json
{
  "sql": "DELETE FROM knowledge_points WHERE material_id = 34"
}
```
**响应**：
```json
{
  "code": 200,
  "data": {
    "type": "execute",
    "affected_rows": 6,
    "success": true
  }
}
```

---

## 🔒 安全特性

### 1. 认证要求
- ✅ 必须提供有效的API Key或Token
- ✅ 所有操作都会记录用户ID和操作日志

### 2. SQL验证
- ⚠️ 第224行检查危险操作（DELETE、DROP、TRUNCATE、UPDATE）
- ⚠️ 仅作为警告，不阻止执行
- ℹ️ 这是设计行为，允许Dify工作流执行必要的DELETE操作

### 3. 事务管理
- ✅ 写入操作自动提交事务（第145行）
- ✅ 异常时自动回滚（第168行）

### 4. 日志记录
```python
logger.info(
    "sql_execute_success",
    user_id=current_user.id,
    affected_rows=affected_rows
)
```

---

## ❌ 当前问题分析

### 前端显示81个知识点，但数据库只有0个

#### 数据库状态
```sql
SELECT COUNT(*) FROM knowledge_points WHERE material_id = 34;
-- 结果：0
```

#### 可能原因

**1. 浏览器缓存** ⭐ 最可能
- 前端缓存了旧的API响应
- 解决：强制刷新（Ctrl+Shift+R）

**2. 前端代码缓存**
- localStorage/sessionStorage缓存
- 解决：清除缓存
```javascript
localStorage.clear()
sessionStorage.clear()
location.reload(true)
```

**3. API响应缓存**
- Nginx或后端缓存
- 解决：检查响应头，禁用缓存

**4. 查看错误的资料**
- 可能不是资料34
- 解决：确认URL中的资料ID

---

## 🔧 排查步骤

### 1. 确认数据库状态
```bash
docker exec kaopeilian-mysql-dev mysql -u root -p'nj861021' kaopeilian \
  -e "SELECT COUNT(*) FROM knowledge_points WHERE material_id = 34;"
```
**当前结果**：0

### 2. 直接调用API查看响应
```bash
# 获取access_token
TOKEN=$(curl -s -X POST http://localhost:8000/api/v1/auth/login \
  -H "Content-Type: application/json" \
  -d '{"username":"admin","password":"admin123"}' \
  | jq -r '.data.access_token')

# 查询知识点
curl -s "http://localhost:8000/api/v1/courses/materials/34/knowledge-points" \
  -H "Authorization: Bearer $TOKEN" \
  | jq '.data | length'
```
**预期结果**：0

### 3. 检查前端Network请求
1. 打开浏览器开发者工具（F12）
2. 切换到Network标签
3. 勾选"Disable cache"
4. 刷新页面
5. 查找知识点API请求
6. 查看Response内容

### 4. 强制清除缓存
```javascript
// 在浏览器控制台执行
localStorage.clear()
sessionStorage.clear()
// 强制刷新所有缓存
caches.keys().then(keys => keys.forEach(key => caches.delete(key)))
location.reload(true)
```

---

## 📊 测试验证

### 测试脚本
```bash
#!/bin/bash
# 测试SQL执行器DELETE功能

API_URL="http://localhost:8000/api/v1/sql/execute-simple"
API_KEY="dify-2025-kaopeilian"
MATERIAL_ID=34

echo "=== 测试1：查看删除前的数量 ==="
curl -s -X POST "$API_URL" \
  -H "Content-Type: application/json" \
  -H "X-API-Key: $API_KEY" \
  -d "{\"sql\": \"SELECT COUNT(*) as count FROM knowledge_points WHERE material_id = $MATERIAL_ID\"}" \
  | jq '.data.rows[0].count'

echo ""
echo "=== 测试2：执行DELETE ==="
curl -s -X POST "$API_URL" \
  -H "Content-Type: application/json" \
  -H "X-API-Key: $API_KEY" \
  -d "{\"sql\": \"DELETE FROM knowledge_points WHERE material_id = $MATERIAL_ID\"}" \
  | jq '.data.affected_rows'

echo ""
echo "=== 测试3：验证删除结果 ==="
curl -s -X POST "$API_URL" \
  -H "Content-Type: application/json" \
  -H "X-API-Key: $API_KEY" \
  -d "{\"sql\": \"SELECT COUNT(*) as count FROM knowledge_points WHERE material_id = $MATERIAL_ID\"}" \
  | jq '.data.rows[0].count'
```

---

## ✅ 权限确认总结

### DELETE权限状态
- ✅ **API支持DELETE** - `/api/v1/sql/execute` 和 `/api/v1/sql/execute-simple`
- ✅ **自动提交事务** - 第145行自动commit
- ✅ **返回影响行数** - response.data.affected_rows
- ✅ **记录操作日志** - 包含user_id和affected_rows
- ✅ **支持参数化查询** - 防止SQL注入
- ✅ **简化认证方式** - API Key方式，适合Dify长期使用

### 使用建议
1. **推荐使用** `/execute-simple` 端点（API Key认证）
2. **工作流第一步**：DELETE清空旧知识点
3. **工作流最后一步**：验证新知识点已保存
4. **日志监控**：检查系统日志确认操作成功

---

## 📚 相关文档

- **API实现**：`/root/aiedu/kaopeilian-backend/app/api/v1/sql_executor.py`
- **路由配置**：`/root/aiedu/kaopeilian-backend/app/api/v1/__init__.py` (第63行)
- **数据库架构**：`/root/aiedu/kaopeilian-backend/数据库架构-统一版.md`

---

## 🔗 数据库连接信息

```
主机: 120.79.247.16
端口: 3307
数据库: kaopeilian
用户: root
密码: nj861021
```

---

## 💡 快速开始

### Dify中的最简配置

**HTTP Request节点**：
```yaml
URL: http://kaopeilian-backend-dev:8000/api/v1/sql/execute-simple
Method: POST
Headers:
  Content-Type: application/json
  X-API-Key: dify-2025-kaopeilian
Body:
  {
    "sql": "DELETE FROM knowledge_points WHERE material_id = {{material_id}}"
  }
```

**验证响应**：
```javascript
// 检查删除是否成功
if (response.code === 200 && response.data.success) {
  console.log(`已删除 ${response.data.affected_rows} 个知识点`)
}
```

---

**结论**：✅ DELETE权限已完全支持，可以直接在Dify工作流中使用！