1015_IT_behavior_alignment_V2/資料庫遷移至MySQL-SDD.md

# 資料庫遷移至 MySQL - 系統設計文件 (SDD)

**專案名稱：** 夥伴對齊系統資料庫遷移  
**文件版本：** 1.0  
**建立日期：** 2025年1月28日  
**作者：** 資深全端工程師

---

## 1. 執行摘要

### 1.1 專案目標
將夥伴對齊系統（Partner Alignment System）的資料庫從 SQLite 遷移至 MySQL，以提升系統的擴展性、性能和生產環境適用性。

### 1.2 背景
目前系統使用 SQLite 作為資料庫，適合開發和測試環境，但在生產環境中存在以下限制：
- SQLite 不適合高並發讀寫
- 無法支援高可用性和負載均衡
- 缺乏企業級的備份和恢復機制
- 不支援網路連線，僅限本地檔案存取

### 1.3 技術範圍
- 資料庫引擎：SQLite → MySQL
- 使用 SQLAlchemy ORM 進行抽象層操作
- 實作 Flask-Migrate 進行資料庫版本管理
- 使用環境變數管理敏感資訊

---

## 2. 需求分析

### 2.1 功能性需求

#### FR-01: 資料庫連線
- **需求：** 系統必須能夠連線到指定的 MySQL 資料庫
- **資料庫連線資訊：**
  - 主機：mysql.theaken.com
  - 端口：33306
  - 資料庫名稱：db_A001
  - 使用者帳號：A001
  - 密碼：QFOts8FlibiI

#### FR-02: 環境變數配置
- **需求：** 資料庫連線參數必須從 `.env` 檔案讀取
- **必要變數：**
  - DB_HOST
  - DB_PORT
  - DB_NAME
  - DB_USER
  - DB_PASSWORD

#### FR-03: 資料庫初始化
- **需求：** 系統必須能夠使用 Flask-Migrate 初始化資料庫
- **操作：** `flask db init`, `flask db migrate`, `flask db upgrade`

#### FR-04: 資料模型相容性
- **需求：** 所有資料模型必須與 MySQL 相容
- **檢查點：** 確保無 SQLite 專屬語法或功能

### 2.2 非功能性需求

#### NFR-01: 安全性
- 所有敏感資訊（資料庫密碼）必須儲存於 `.env` 檔案
- `.env` 檔案必須加入 `.gitignore`
- 提供 `.env.example` 作為範本

#### NFR-02: 向後相容性
- 現有的資料模型定義不需修改結構
- API 端點行為保持一致
- 資料匯入/匯出功能不受影響

#### NFR-03: 文件完整性
- 提供完整的部署指南
- 記錄所有環境變數設定
- 提供資料庫遷移步驟

---

## 3. 技術方案

### 3.1 技術架構選擇

#### 3.1.1 ORM 層
- **選擇：** SQLAlchemy（已使用）
- **理由：** 
  - 提供統一的 ORM API
  - 跨資料庫相容性
  - 支援複雜查詢

#### 3.1.2 資料庫驅動
- **選擇：** PyMySQL
- **理由：**
  - 純 Python 實作
  - 相容 MySQL 5.7+
  - 效能穩定

#### 3.1.3 環境變數管理
- **選擇：** python-dotenv
- **理由：**
  - 業界標準
  - 簡單易用
  - 已安裝於 requirements.txt

#### 3.1.4 資料庫遷移工具
- **選擇：** Flask-Migrate（基於 Alembic）
- **理由：**
  - Flask 生態系整合
  - 版本控制管理
  - 自動生成遷移腳本

### 3.2 資料庫連線字串格式

#### SQLite 格式（現行）
```python
SQLALCHEMY_DATABASE_URI = 'sqlite:///partner_alignment.db'
```

#### MySQL 格式（目標）
```python
# 使用 PyMySQL
SQLALCHEMY_DATABASE_URI = 'mysql+pymysql://USER:PASSWORD@HOST:PORT/DATABASE'
```

#### 完整連線字串範例
```python
'mysql+pymysql://A001:QFOts8FlibiI@mysql.theaken.com:33306/db_A001'
```

### 3.3 連線參數說明

| 參數 | 說明 | 範例值 |
|------|------|--------|
| USER | 資料庫使用者帳號 | A001 |
| PASSWORD | 資料庫密碼 | QFOts8FlibiI |
| HOST | 資料庫主機位址 | mysql.theaken.com |
| PORT | 資料庫連接埠 | 33306 |
| DATABASE | 資料庫名稱 | db_A001 |

---

## 4. 系統設計

### 4.1 配置管理架構

```
專案根目錄/
├── .env                    # 實際環境變數（不提交）
├── .env.example            # 環境變數範本（提交）
├── .gitignore             # 忽略 .env
├── config.py              # 配置類別
└── simple_app.py          # 主應用程式
```

### 4.2 配置載入流程

```
1. 應用程式啟動
   ↓
2. load_dotenv() 載入 .env 檔案
   ↓
3. 從環境變數讀取 DB_* 參數
   ↓
4. 組裝 MySQL 連線字串
   ↓
5. 設定 app.config['SQLALCHEMY_DATABASE_URI']
   ↓
6. 初始化 SQLAlchemy 與資料庫連線
```

### 4.3 資料模型相容性檢查

#### 4.3.1 需檢查的資料類型

| SQLite 類型 | MySQL 類型 | 相容性 |
|------------|-----------|--------|
| INTEGER | INT | ✅ 相容 |
| TEXT | TEXT | ✅ 相容 |
| VARCHAR(n) | VARCHAR(n) | ✅ 相容 |
| BOOLEAN | TINYINT(1) | ✅ 相容 |
| DATETIME | DATETIME | ✅ 相容 |

#### 4.3.2 現有模型清單

需確認以下模型的欄位類型與 MySQL 相容：

1. **User**
   - id (Integer)
   - username (String 80)
   - email (String 120)
   - full_name (String 100)
   - department (String 50)
   - position (String 50)
   - employee_id (String 50)
   - password_hash (String 255)
   - is_active (Boolean)
   - created_at (DateTime)

2. **Capability**
   - id (Integer)
   - name (String 100)
   - l1_description (Text)
   - l2_description (Text)
   - l3_description (Text)
   - l4_description (Text)
   - l5_description (Text)
   - is_active (Boolean)

3. **DepartmentCapability**
   - id (Integer)
   - department (String 50)
   - capability_id (Integer, ForeignKey)
   - created_at (DateTime)

4. **Assessment**
   - id (Integer)
   - user_id (Integer, ForeignKey)
   - department (String 50)
   - position (String 50)
   - employee_name (String 100)
   - assessment_data (Text)
   - created_at (DateTime)

5. **StarFeedback**
   - id (Integer)
   - evaluator_name (String 100)
   - evaluatee_name (String 100)
   - evaluatee_department (String 50)
   - evaluatee_position (String 50)
   - situation (Text)
   - task (Text)
   - action (Text)
   - result (Text)
   - score (Integer)
   - points_earned (Integer)
   - feedback_date (Date)
   - created_at (DateTime)

6. **EmployeePoint**
   - id (Integer)
   - employee_name (String 100)
   - department (String 50)
   - position (String 50)
   - total_points (Integer)
   - monthly_points (Integer)

**檢查結果：** ✅ 所有模型欄位類型與 MySQL 完全相容，無需修改

---

## 5. 實作設計

### 5.1 檔案修改清單

#### 5.1.1 新建檔案

1. **`.env`** - 環境變數檔案（不提交至 Git）
   ```env
   DB_HOST=mysql.theaken.com
   DB_PORT=33306
   DB_NAME=db_A001
   DB_USER=A001
   DB_PASSWORD=QFOts8FlibiI
   ```

2. **`.env.example`** - 環境變數範本（提交至 Git）
   ```env
   DB_HOST=your_mysql_host
   DB_PORT=3306
   DB_NAME=your_database_name
   DB_USER=your_database_user
   DB_PASSWORD=your_database_password
   ```

3. **`.gitignore`** - Git 忽略規則
   ```
   # Environment variables
   .env

   # Python cache
   __pycache__/
   *.pyc
   *.pyo
   *.pyd

   # Database
   instance/
   *.db
   *.sqlite

   # Migrations (optional, 根據團隊決定是否提交)
   # migrations/

   # IDE
   .vscode/
   .idea/
   *.swp

   # Logs
   *.log
   ```

#### 5.1.2 修改檔案

1. **`requirements.txt`** - 新增依賴
   ```txt
   Flask==2.3.3
   Flask-SQLAlchemy==3.0.5
   Flask-CORS==4.0.0
   Flask-Migrate==4.0.5        # 新增：資料庫遷移工具
   PyMySQL==1.1.0              # 已存在，確認保留
   python-dotenv==1.0.0        # 已存在，確認保留
   # ... 其他依賴
   ```

2. **`config.py`** - 修改配置類別
   ```python
   import os
   from dotenv import load_dotenv

   load_dotenv()

   class Config:
       SECRET_KEY = os.environ.get('SECRET_KEY') or 'dev-secret-key-for-testing-only'
       
       # MySQL 資料庫配置
       DB_HOST = os.environ.get('DB_HOST', 'localhost')
       DB_PORT = os.environ.get('DB_PORT', '3306')
       DB_NAME = os.environ.get('DB_NAME', 'partner_alignment')
       DB_USER = os.environ.get('DB_USER', 'root')
       DB_PASSWORD = os.environ.get('DB_PASSWORD', '')
       
       SQLALCHEMY_DATABASE_URI = f"mysql+pymysql://{DB_USER}:{DB_PASSWORD}@{DB_HOST}:{DB_PORT}/{DB_NAME}"
       SQLALCHEMY_TRACK_MODIFICATIONS = False
       SQLALCHEMY_ECHO = False  # 是否顯示 SQL 語句
       
       # ... 其他配置
   ```

3. **`simple_app.py`** - 修改資料庫初始化

   **方案 A：使用 config.py（推薦）**
   ```python
   from flask import Flask, render_template, request, jsonify
   from flask_cors import CORS
   from flask_sqlalchemy import SQLAlchemy
   from flask_migrate import Migrate  # 新增
   from config import Config  # 新增
   
   # 創建 Flask 應用程式
   app = Flask(__name__)
   
   # 從配置類載入設定
   app.config.from_object(Config)
   
   # 初始化擴展
   db = SQLAlchemy(app)
   migrate = Migrate(app, db)  # 新增
   CORS(app, origins=Config.CORS_ORIGINS)
   
   # ... 資料模型定義（不變）
   
   if __name__ == '__main__':
       with app.app_context():
           # 使用 Flask-Migrate 初始化資料庫（取代 db.create_all()）
           # db.create_all()  # 移除
           print("資料庫連線成功！")
           print(f"連線至: {app.config['SQLALCHEMY_DATABASE_URI']}")
           
           # 不再需要手動創建表，改用 Flask-Migrate
           # db.create_all()
           
           # 創建樣本數據
           create_sample_data()
           
       app.run(debug=True, host='0.0.0.0', port=5000)
   ```

   **方案 B：直接修改（不推薦，但若維持現有結構）**
   ```python
   from flask import Flask, render_template, request, jsonify
   from flask_cors import CORS
   from flask_sqlalchemy import SQLAlchemy
   from flask_migrate import Migrate  # 新增
   from dotenv import load_dotenv
   import os
   
   load_dotenv()  # 新增
   
   # 創建 Flask 應用程式
   app = Flask(__name__)
   
   # 從環境變數讀取資料庫設定
   DB_HOST = os.environ.get('DB_HOST', 'mysql.theaken.com')
   DB_PORT = os.environ.get('DB_PORT', '33306')
   DB_NAME = os.environ.get('DB_NAME', 'db_A001')
   DB_USER = os.environ.get('DB_USER', 'A001')
   DB_PASSWORD = os.environ.get('DB_PASSWORD', 'QFOts8FlibiI')
   
   # 配置
   app.config['SECRET_KEY'] = os.environ.get('SECRET_KEY', 'dev-secret-key-for-testing')
   app.config['SQLALCHEMY_DATABASE_URI'] = f'mysql+pymysql://{DB_USER}:{DB_PASSWORD}@{DB_HOST}:{DB_PORT}/{DB_NAME}'
   app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
   
   # 初始化擴展
   db = SQLAlchemy(app)
   migrate = Migrate(app, db)  # 新增
   CORS(app, origins=['http://localhost:5000', 'http://127.0.0.1:5000'])
   
   # ... 其餘程式碼（不變）
   ```

### 5.2 資料庫遷移指令

#### 5.2.1 初始化 Flask-Migrate
```bash
# 設定 Flask 應用程式環境變數
set FLASK_APP=simple_app.py  # Windows
# export FLASK_APP=simple_app.py  # Linux/Mac

# 初始化遷移目錄
flask db init
```

#### 5.2.2 建立初始遷移
```bash
# 掃描模型並建立遷移腳本
flask db migrate -m "Initial migration from SQLite to MySQL"
```

#### 5.2.3 套用遷移至資料庫
```bash
# 在資料庫建立資料表
flask db upgrade
```

#### 5.2.4 遷移後續變更
```bash
# 當模型變更時
flask db migrate -m "Description of changes"
flask db upgrade
```

### 5.3 資料遷移策略

#### 5.3.1 SQLite 至 MySQL 資料轉移

**方法一：使用 SQLAlchemy 直接轉移（適用少量資料）**
```python
# 匯出 SQLite 資料
from sqlalchemy import create_engine
import pandas as pd

# SQLite 連線
sqlite_engine = create_engine('sqlite:///instance/partner_alignment.db')

# MySQL 連線
mysql_engine = create_engine('mysql+pymysql://USER:PASSWORD@HOST:PORT/DATABASE')

# 匯出各表資料
tables = ['user', 'capability', 'assessment', 'star_feedback', 'employee_point']

for table in tables:
    df = pd.read_sql_table(table, sqlite_engine)
    df.to_sql(table, mysql_engine, if_exists='append', index=False)
    print(f"匯入 {table} 完成")
```

**方法二：使用 mysqldump 和匯入工具（適用大量資料）**

**方法三：重新初始化測試資料（適用開發/測試）**
- 直接使用 `create_sample_data()` 函數建立測試資料

---

## 6. 部署步驟

### 6.1 開發環境準備

#### 步驟 1：安裝依賴
```bash
# 安裝新增的依賴
pip install Flask-Migrate
pip install -r requirements.txt
```

#### 步驟 2：建立環境變數檔案
```bash
# 複製範本
copy .env.example .env  # Windows
# cp .env.example .env  # Linux/Mac

# 編輯 .env 檔案，填入實際資料庫連線資訊
```

#### 步驟 3：確認資料庫連線
```bash
# 測試資料庫連線
python -c "from sqlalchemy import create_engine; engine = create_engine('mysql+pymysql://A001:QFOts8FlibiI@mysql.theaken.com:33306/db_A001'); engine.connect(); print('連線成功')"
```

#### 步驟 4：初始化資料庫遷移
```bash
# 設定應用程式
set FLASK_APP=simple_app.py  # Windows
# export FLASK_APP=simple_app.py  # Linux/Mac

# 初始化遷移目錄
flask db init

# 建立初始遷移
flask db migrate -m "Initial migration to MySQL"

# 執行遷移
flask db upgrade
```

#### 步驟 4：啟動應用程式
```bash
python simple_app.py
```

### 6.2 生產環境部署

#### 步驟 1：伺服器準備
```bash
# 安裝 MySQL 客戶端工具
# Windows: 下載 MySQL Installer
# Linux: sudo apt-get install mysql-client
# Mac: brew install mysql-client
```

#### 步驟 2：環境變數設定
```bash
# 在伺服器上建立 .env 檔案
# 使用生產環境的資料庫憑證
```

#### 步驟 3：資料庫遷移
```bash
# 執行遷移（僅首次部署）
flask db upgrade
```

#### 步驟 4：設定反向代理（可選）
```nginx
# nginx.conf
server {
    listen 80;
    server_name your-domain.com;
    
    location / {
        proxy_pass http://127.0.0.1:5000;
        proxy_set_header Host $host;
        proxy_set_header X-Real-IP $remote_addr;
    }
}
```

---

## 7. 測試計畫

### 7.1 單元測試

#### Test-01: 資料庫連線測試
```python
def test_database_connection():
    """測試資料庫連線是否成功"""
    from sqlalchemy import create_engine
    from config import Config
    
    engine = create_engine(Config.SQLALCHEMY_DATABASE_URI)
    connection = engine.connect()
    assert connection is not None
    connection.close()
```

#### Test-02: 環境變數載入測試
```python
def test_env_loading():
    """測試環境變數是否正確載入"""
    from config import Config
    
    assert Config.DB_HOST == 'mysql.theaken.com'
    assert Config.DB_PORT == '33306'
    assert Config.DB_NAME == 'db_A001'
```

#### Test-03: 資料模型建立測試
```python
def test_model_creation():
    """測試所有資料模型是否正確建立"""
    with app.app_context():
        # 檢查所有表是否存在
        inspector = inspect(db.engine)
        tables = inspector.get_table_names()
        
        expected_tables = ['user', 'capability', 'assessment', 
                          'star_feedback', 'employee_point']
        
        for table in expected_tables:
            assert table in tables
```

### 7.2 整合測試

#### Test-04: API 端點資料庫操作測試
```python
def test_login_with_mysql():
    """測試登入功能是否正常運作"""
    response = client.post('/api/auth/login', json={
        'username': 'admin',
        'password': 'admin123'
    })
    assert response.status_code == 200
    assert 'access_token' in response.json
```

#### Test-05: 資料寫入測試
```python
def test_create_assessment():
    """測試建立評估記錄"""
    response = client.post('/api/assessments', json={
        'department': 'IT',
        'position': 'Engineer',
        'assessment_data': {}
    })
    assert response.status_code == 201
```

### 7.3 效能測試

#### Test-06: 連線池測試
- 測試多個同時連線是否正常
- 驗證連線池配置

#### Test-07: 查詢效能測試
- 比較 SQLite 與 MySQL 的查詢效能
- 驗證複雜查詢的執行時間

---

## 8. 風險評估

### 8.1 技術風險

| 風險 | 影響 | 機率 | 緩解措施 |
|------|------|------|----------|
| 資料庫連線失敗 | 高 | 中 | 實作重試機制與連線池 |
| 資料遷移資料遺失 | 高 | 低 | 完整備份與測試環境驗證 |
| 效能不如預期 | 中 | 低 | 建立索引與查詢優化 |
| 相容性問題 | 中 | 低 | 完整相容性測試 |

### 8.2 營運風險

| 風險 | 影響 | 機率 | 緩解措施 |
|------|------|------|----------|
| 服務中斷 | 高 | 低 | 維護窗口與回滾計畫 |
| 資料不一致 | 高 | 低 | 交易控制與驗證 |
| 權限問題 | 中 | 中 | 預先測試使用者權限 |

---

## 9. 回滾計畫

### 9.1 回滾條件
- 資料庫連線持續失敗
- 資料遷移造成資料損壞
- 效能嚴重下降

### 9.2 回滾步驟
```bash
# 1. 停止應用程式
# Ctrl+C 或 systemctl stop your-app

# 2. 恢復原 SQLite 配置
# 修改 simple_app.py 中的資料庫連線字串

# 3. 確認 SQLite 資料庫完整
# 檢查 instance/partner_alignment.db

# 4. 重新啟動應用程式
python simple_app.py
```

---

## 10. 文件交付清單

### 10.1 技術文件
- [ ] 本 SDD 文件
- [ ] 修改後的 `config.py`
- [ ] 修改後的 `simple_app.py`
- [ ] 新建的 `.env.example`
- [ ] 新建的 `.gitignore`
- [ ] 更新後的 `requirements.txt`

### 10.2 作業文件
- [ ] 部署指南（Deployment Guide）
- [ ] 資料庫連線設定說明
- [ ] 環境變數配置說明
- [ ] 故障排除手冊

### 10.3 測試文件
- [ ] 測試計劃
- [ ] 測試結果報告
- [ ] 效能測試報告

---

## 11. 時程規劃

### 階段 1：準備階段（預計 1 天）
- 建立環境變數檔案
- 更新 requirements.txt
- 安裝依賴套件
- 測試資料庫連線

### 階段 2：程式修改（預計 0.5 天）
- 修改 config.py
- 修改 simple_app.py
- 建立 .env.example 和 .gitignore

### 階段 3：資料庫遷移（預計 0.5 天）
- 初始化 Flask-Migrate
- 建立遷移腳本
- 執行資料庫升級

### 階段 4：測試驗證（預計 1 天）
- 單元測試
- 整合測試
- 效能測試
- 使用者驗收測試

### 階段 5：部署上線（預計 0.5 天）
- 生產環境部署
- 監控與驗證
- 文件更新

**總計：約 3.5 個工作天**

---

## 12. 確認事項

### 12.1 需要確認的資訊

**資料庫連線資訊已提供：**
- ✅ DB_HOST = mysql.theaken.com
- ✅ DB_PORT = 33306
- ✅ DB_NAME = db_A001
- ✅ DB_USER = A001
- ✅ DB_PASSWORD = QFOts8FlibiI

**資料庫權限需求：**
- [ ] 確認 A001 使用者具有 CREATE TABLE 權限
- [ ] 確認 A001 使用者具有 CREATE INDEX 權限
- [ ] 確認 A001 使用者具有 INSERT, UPDATE, DELETE, SELECT 權限
- [ ] 確認 db_A001 資料庫已建立（或具有建立權限）

### 12.2 需要決策的事項

1. **是否遷移現有資料？**
   - 選項 A：直接使用測試資料（快速）
   - 選項 B：從 SQLite 遷移現有資料（完整）

2. **Flask-Migrate 遷移目錄是否提交？**
   - 選項 A：提交至 Git（團隊協作）
   - 選項 B：不提交（僅本地開發）

3. **Config 方案選擇？**
   - 選項 A：使用 config.py（模組化，推薦）
   - 選項 B：直接修改 simple_app.py（簡單，不推薦）

---

## 附錄 A：資料庫連線字串範例

### A.1 完整連線字串
```
mysql+pymysql://A001:QFOts8FlibiI@mysql.theaken.com:33306/db_A001
```

### A.2 帶參數的連線字串
```
mysql+pymysql://USER:PASSWORD@HOST:PORT/DATABASE?charset=utf8mb4&use_unicode=1
```

---

## 附錄 B：常見錯誤與解決方案

### B.1 連線錯誤

**錯誤：Access denied for user**
```
解決：檢查 DB_USER 和 DB_PASSWORD 是否正確
```

**錯誤：Can't connect to MySQL server**
```
解決：
1. 檢查 DB_HOST 和 DB_PORT 是否正確
2. 確認防火牆設定
3. 確認 MySQL 服務是否運行
```

### B.2 遷移錯誤

**錯誤：Target database is not up to date**
```
解決：執行 flask db upgrade
```

**錯誤：Multiple heads detected**
```
解決：flask db merge heads
```

---

## 附錄 C：效能優化建議

### C.1 索引建立
建議在以下欄位建立索引：
- `User.username` - 登入查詢
- `User.email` - 登入查詢
- `Assessment.user_id` - 關聯查詢
- `StarFeedback.evaluatee_name` - 排名查詢
- `EmployeePoint.total_points` - 排序查詢

### C.2 連線池配置
```python
app.config['SQLALCHEMY_ENGINE_OPTIONS'] = {
    'pool_size': 10,
    'pool_recycle': 3600,
    'pool_pre_ping': True
}
```

---

## 文件簽核

| 角色 | 姓名 | 簽名 | 日期 |
|------|------|------|------|
| 專案經理 | | | |
| 技術主管 | | | |
| 開發人員 | | | |

---

**文件版本歷史**

| 版本 | 日期 | 修改內容 | 修改人 |
|------|------|----------|--------|
| 1.0 | 2025-01-28 | 初始版本 | 資深全端工程師 |

---

## 總結

本 SDD 文件詳細說明了將夥伴對齊系統從 SQLite 遷移至 MySQL 的完整方案，包含：

1. ✅ 技術架構設計
2. ✅ 詳細實作步驟
3. ✅ 配置管理方案
4. ✅ 資料遷移策略
5. ✅ 測試計畫
6. ✅ 風險評估與回滾計畫
7. ✅ 部署指南

**待確認項目：**
- 資料庫使用者權限
- 是否遷移現有資料
- 使用 Config 方案 A 或 B

**下一步行動：**
請審閱本文件並確認需求後，即可開始實作。