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 格式（現行）

SQLALCHEMY_DATABASE_URI = 'sqlite:///partner_alignment.db'

MySQL 格式（目標）

# 使用 PyMySQL
SQLALCHEMY_DATABASE_URI = 'mysql+pymysql://USER:PASSWORD@HOST:PORT/DATABASE'

完整連線字串範例

'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）

   DB_HOST=mysql.theaken.com
   DB_PORT=33306
   DB_NAME=db_A001
   DB_USER=A001
   DB_PASSWORD=QFOts8FlibiI
   

2. .env.example - 環境變數範本（提交至 Git）

   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 - 新增依賴

   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 - 修改配置類別

   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（推薦）

   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：直接修改（不推薦，但若維持現有結構）

   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

# 設定 Flask 應用程式環境變數
set FLASK_APP=simple_app.py  # Windows
# export FLASK_APP=simple_app.py  # Linux/Mac

# 初始化遷移目錄
flask db init

5.2.2 建立初始遷移

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

5.2.3 套用遷移至資料庫

# 在資料庫建立資料表
flask db upgrade

5.2.4 遷移後續變更

# 當模型變更時
flask db migrate -m "Description of changes"
flask db upgrade

5.3 資料遷移策略

5.3.1 SQLite 至 MySQL 資料轉移

方法一：使用 SQLAlchemy 直接轉移（適用少量資料）

# 匯出 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：安裝依賴

# 安裝新增的依賴
pip install Flask-Migrate
pip install -r requirements.txt

步驟 2：建立環境變數檔案

# 複製範本
copy .env.example .env  # Windows
# cp .env.example .env  # Linux/Mac

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

步驟 3：確認資料庫連線

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

步驟 4：初始化資料庫遷移

# 設定應用程式
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：啟動應用程式

python simple_app.py

6.2 生產環境部署

步驟 1：伺服器準備

# 安裝 MySQL 客戶端工具
# Windows: 下載 MySQL Installer
# Linux: sudo apt-get install mysql-client
# Mac: brew install mysql-client

步驟 2：環境變數設定

# 在伺服器上建立 .env 檔案
# 使用生產環境的資料庫憑證

步驟 3：資料庫遷移

# 執行遷移（僅首次部署）
flask db upgrade

步驟 4：設定反向代理（可選）

# 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: 資料庫連線測試

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: 環境變數載入測試

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: 資料模型建立測試

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 端點資料庫操作測試

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: 資料寫入測試

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 回滾步驟

# 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 連線池配置

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

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