# MySQL Schema 自動建表設計

## 1. 文件目的

本文件用於整理第二階段 MySQL TaskStore PoC 的 schema 維護方式。

使用者希望 MySQL 的欄位型別、索引、描述可以直接在 C# Class 中維護，並且在 API 或 Host 啟動時自動產出 Table，不需要另外手動建置 MySQL Table。

本文件是設計計畫，不代表已經同意修改程式 repo、導入 WebApi、安裝套件、建立 schema 或變更專案啟動方式。

## 2. 設計結論

建議採用：

> C# Schema Definition + MySQL Schema Generator + Startup Schema Initializer

第一版不建議使用 EF Core Code First / Migration。

原因：

- 目前專案仍是 .NET 5.0。
- MySQL 版本為 5.6.2，SQL 語法與欄位型態需要保守。
- 第一版目標是 MySQL TaskStore PoC，不是完整 ORM 架構。
- 手寫 DDL Generator 可明確控制欄位型別、索引、COMMENT、字元集與 InnoDB 設定。
- 啟動自動建表可做到，但自動刪欄位或自動改型別風險太高，第一版不做。

## 3. 適用範圍

第一版只處理 TaskStore 需要的資料表：

| 資料表 | C# Class | 用途 |
|---|---|---|
| `task_executions` | `TaskExecutionRecord` | 保存任務層級執行狀態 |
| `node_executions` | `NodeExecutionRecord` | 保存節點層級執行歷程 |
| `command_executions` | `CommandExecutionRecord` | 保存設備命令執行結果 |
| `task_errors` | `TaskErrorRecord` | 保存任務、節點或命令錯誤快照 |

第一版不處理：

- 報表資料表。
- 多案場 / 租戶資料表。
- 權限資料表。
- 設備主檔管理。
- 自動資料搬移。
- 自動刪除欄位。
- 自動修改既有欄位型別。

## 4. 建議模組切分

| 模組 | 建議位置 | 責任 |
|---|---|---|
| Schema Attribute | `HS.DeviceControl.Infrastructure` 或未來 DB 子模組 | 描述 Table、Column、Index |
| Schema Model | Infrastructure 內部 model | 將 Attribute 解析成中立 schema |
| MySQL DDL Generator | Infrastructure DB 實作 | 將 schema 產成 MySQL 5.6.2 相容 SQL |
| Schema Initializer | Infrastructure DB 實作 | 啟動時檢查並建立缺少的 Table / Column / Index |
| TaskStore | Infrastructure DB 實作 | 寫入與查詢任務歷程 |

Core 不應依賴這些模組。

## 5. C# Attribute 規格

### 5.1 DbTableAttribute

用途：描述資料表名稱、描述、字元集與儲存引擎。

建議欄位：

| 欄位 | 型別 | 說明 |
|---|---|---|
| `Name` | `string` | MySQL table 名稱 |
| `Description` | `string` | Table COMMENT |
| `Engine` | `string` | 預設 `InnoDB` |
| `Charset` | `string` | 預設 `utf8` |

範例：

```csharp
[DbTable("task_executions", Description = "任務執行紀錄")]
public class TaskExecutionRecord
{
}
```

### 5.2 DbColumnAttribute

用途：描述欄位名稱、MySQL 型別、是否必填、主鍵與欄位描述。

建議欄位：

| 欄位 | 型別 | 說明 |
|---|---|---|
| `Name` | `string` | MySQL column 名稱 |
| `DbType` | `string` | MySQL 欄位型別，例如 `varchar(64)` |
| `IsRequired` | `bool` | 是否 `NOT NULL` |
| `IsPrimaryKey` | `bool` | 是否主鍵 |
| `DefaultValueSql` | `string` | 預設值 SQL，例如 `CURRENT_TIMESTAMP` |
| `Description` | `string` | Column COMMENT |

範例：

```csharp
[DbColumn("task_id", "varchar(64)", IsPrimaryKey = true, IsRequired = true, Description = "任務識別碼")]
public string TaskId { get; set; }
```

### 5.3 DbIndexAttribute

用途：描述索引名稱、索引欄位與是否唯一。

建議欄位：

| 欄位 | 型別 | 說明 |
|---|---|---|
| `Name` | `string` | Index 名稱 |
| `Columns` | `string[]` | 索引欄位清單 |
| `IsUnique` | `bool` | 是否唯一索引 |

範例：

```csharp
[DbIndex("idx_task_status", "status")]
[DbIndex("idx_task_started_at", "started_at")]
public class TaskExecutionRecord
{
}
```

## 6. Class 維護範例

```csharp
[DbTable("task_executions", Description = "任務執行紀錄")]
[DbIndex("idx_task_status", "status")]
[DbIndex("idx_task_started_at", "started_at")]
public class TaskExecutionRecord
{
    [DbColumn("task_id", "varchar(64)", IsPrimaryKey = true, IsRequired = true, Description = "任務識別碼")]
    public string TaskId { get; set; }

    [DbColumn("workflow_id", "varchar(64)", IsRequired = true, Description = "Workflow 識別碼")]
    public string WorkflowId { get; set; }

    [DbColumn("status", "varchar(32)", IsRequired = true, Description = "任務狀態")]
    public string Status { get; set; }

    [DbColumn("started_at", "datetime", Description = "開始時間")]
    public DateTime? StartedAt { get; set; }

    [DbColumn("ended_at", "datetime", Description = "結束時間")]
    public DateTime? EndedAt { get; set; }
}
```

## 7. 產出 SQL 範例

```sql
CREATE TABLE IF NOT EXISTS task_executions (
  task_id varchar(64) NOT NULL COMMENT '任務識別碼',
  workflow_id varchar(64) NOT NULL COMMENT 'Workflow 識別碼',
  status varchar(32) NOT NULL COMMENT '任務狀態',
  started_at datetime NULL COMMENT '開始時間',
  ended_at datetime NULL COMMENT '結束時間',
  PRIMARY KEY (task_id),
  INDEX idx_task_status (status),
  INDEX idx_task_started_at (started_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='任務執行紀錄';
```

## 8. 啟動流程設計

啟動時建議流程：

1. 讀取 appsettings 或環境變數中的 MySQL 連線設定。
2. 檢查是否啟用 `AutoCreateSchema`。
3. 掃描已註冊的 schema record class。
4. 將 Attribute 轉成中立 SchemaDefinition。
5. 連線到既有 database。
6. 讀取 `information_schema` 中的既有 table / column / index。
7. 若 table 不存在，執行 `CREATE TABLE IF NOT EXISTS`。
8. 若 table 存在但缺少欄位，依規則執行 `ALTER TABLE ADD COLUMN`。
9. 若缺少索引，執行 `ALTER TABLE ADD INDEX`。
10. 若欄位型別不同，只記錄錯誤或警告，不自動修改。
11. 完成後才啟動 TaskStore 寫入流程。

## 9. 設定檔建議

```json
{
  "Database": {
    "Provider": "MySql",
    "ConnectionString": "Server=localhost;Database=hs_device_control;Uid=app;Pwd=***;",
    "AutoCreateSchema": true,
    "DryRunSchemaSql": false,
    "StopOnSchemaMismatch": true
  }
}
```

欄位說明：

| 欄位 | 說明 |
|---|---|
| `Provider` | 第一版固定 `MySql` |
| `ConnectionString` | MySQL 連線字串 |
| `AutoCreateSchema` | 是否啟動時自動建表與補欄位 |
| `DryRunSchemaSql` | 只輸出 SQL，不實際執行 |
| `StopOnSchemaMismatch` | 欄位型別不一致時是否停止啟動 |

## 10. 自動化允許與禁止規則

| 類型 | 第一版是否允許自動處理 | 原因 |
|---|---|---|
| Table 不存在 | 允許 | 可安全建立 |
| Column 不存在 | 允許 | 可安全補欄位 |
| Index 不存在 | 允許 | 可安全補索引 |
| Column COMMENT 不同 | 可先只警告 | 不影響資料正確性 |
| Column 型別不同 | 不允許自動修改 | 可能造成資料截斷 |
| Column 被移除 | 不允許自動刪除 | 避免破壞既有資料 |
| Index 多出 | 不自動刪除 | 避免影響現場查詢 |
| Database 不存在 | 第一版不建議自動建立 | 權限與部署責任需分清楚 |

## 11. 權限需求

若要啟動自動建表，MySQL 帳號至少需要：

- `SELECT`
- `INSERT`
- `UPDATE`
- `CREATE`
- `ALTER`
- `INDEX`

第一版建議 database 先由 DBA 或部署流程建立，例如：

```sql
CREATE DATABASE hs_device_control DEFAULT CHARACTER SET utf8;
```

程式只負責 database 內的 table、column、index 初始化。

## 12. 驗證節點

後續進入程式 repo 實作時，需建立驗證節點：

| 驗證項目 | 預期結果 |
|---|---|
| 空 database 啟動 | 自動建立四張 TaskStore table |
| 重複啟動 | 不重複建立、不報錯 |
| 缺少欄位 | 自動補欄位 |
| 缺少索引 | 自動補索引 |
| 欄位型別不一致 | 回報 schema mismatch，不自動修改 |
| DryRun 模式 | 只輸出 SQL，不修改 DB |
| DB 權限不足 | 回傳明確錯誤與 Log |

## 13. 風險與控制方式

| 風險 | 控制方式 |
|---|---|
| 啟動時誤改正式資料表 | 第一版禁止自動刪欄位與改型別 |
| MySQL 5.6.2 不支援部分語法 | DDL Generator 固定使用 MySQL 5.6.2 相容語法 |
| Class 與 DB 實際結構不同步 | 啟動時檢查 `information_schema` 並輸出 mismatch |
| 權限不足導致啟動失敗 | 設定清楚權限需求與錯誤碼 |
| API 啟動時間變長 | Schema 初始化只在啟動時執行，並記錄耗時 |

## 14. 建議實作順序

1. 先確認本文件的自動建表策略。
2. 整理四張資料表的 C# Class 欄位初稿。
3. 建立 Attribute 規格。
4. 建立 SchemaDefinition 解析器。
5. 建立 MySQL 5.6.2 DDL Generator。
6. 建立 Dry Run SQL 輸出。
7. 建立 Schema Initializer。
8. 串接 TaskStore PoC。
9. 建立自動建表驗證節點。

## 15. 建議下一步

MySQL schema Class 初稿請參考：[MySQL schema Class 初稿](./mysql-schema-class-draft.md)。

建議下一步做「MySQL schema Class 初稿確認」，先確認 `TaskExecutionRecord`、`NodeExecutionRecord`、`CommandExecutionRecord`、`TaskErrorRecord` 四個 Class 的欄位、型別、索引與描述是否符合需求。

在這份 Class 初稿確認前，不建議直接進入程式 repo 實作自動建表。