hr-portal/DATABASE-TEST.md

🧪 資料庫測試指南

📋 測試流程

前置需求

• ✅ Ubuntu Server (10.1.0.254) 可訪問
• ✅ PostgreSQL 運行中
• ✅ SSH 配置完成

---

🚀 快速開始 (推薦)

Windows PowerShell

# 1. 切換到腳本目錄
cd W:\DevOps-Workspace\hr-portal\scripts

# 2. 檢查 PostgreSQL 連接
.\check-postgres.ps1

# 3. 設定資料庫 (會提示輸入密碼)
.\setup-db-simple.ps1

# 4. 驗證設定
# (會在步驟 3 自動執行)

執行測試資料

# 透過 SSH 執行測試資料插入
cd W:\DevOps-Workspace\hr-portal\scripts

# 上傳 SQL 檔案
scp insert-test-data.sql ubuntu@10.1.0.254:/tmp/

# 執行 SQL
ssh ubuntu@10.1.0.254 "docker exec -i postgres psql -U hr_user -d hr_portal < /tmp/insert-test-data.sql"

---

📝 手動測試步驟

步驟 1: 連接到 PostgreSQL

# SSH 到 Ubuntu Server
ssh ubuntu@10.1.0.254

# 進入 PostgreSQL 容器
docker exec -it postgres psql -U hr_user -d hr_portal

步驟 2: 驗證資料表

-- 列出所有資料表
\dt

-- 應該看到:
-- audit_logs
-- business_units
-- divisions
-- email_accounts
-- employees
-- network_drives
-- project_members
-- projects
-- system_permissions

步驟 3: 查詢基礎資料

-- 查看事業部
SELECT * FROM business_units;

-- 查看部門
SELECT * FROM divisions;

-- 查看視圖
\dv

-- 測試視圖
SELECT * FROM v_employees_full;
SELECT * FROM v_division_headcount;

步驟 4: 插入測試資料

-- 在 psql 中執行
\i /tmp/insert-test-data.sql

或從外部執行:

# 在 Ubuntu Server 上
docker exec -i postgres psql -U hr_user -d hr_portal < /tmp/insert-test-data.sql

步驟 5: 驗證測試資料

-- 查看員工
SELECT employee_id, username, chinese_name, email, position
FROM employees
ORDER BY employee_id;

-- 查看完整員工資訊 (含事業部/部門)
SELECT
    e.employee_id,
    e.chinese_name,
    bu.name as business_unit,
    d.name as division,
    e.position
FROM employees e
LEFT JOIN business_units bu ON e.business_unit_id = bu.id
LEFT JOIN divisions d ON e.division_id = d.id
ORDER BY e.employee_id;

-- 查看員工的資源配置
SELECT
    e.username,
    e.chinese_name,
    (SELECT COUNT(*) FROM email_accounts WHERE employee_id = e.id) as emails,
    (SELECT COUNT(*) FROM network_drives WHERE employee_id = e.id) as drives,
    (SELECT COUNT(*) FROM system_permissions WHERE employee_id = e.id AND is_active = true) as permissions
FROM employees e;

-- 查看專案與成員
SELECT
    p.project_code,
    p.project_name,
    e.chinese_name as manager,
    (SELECT COUNT(*) FROM project_members WHERE project_id = p.id) as members
FROM projects p
LEFT JOIN employees e ON p.project_manager_id = e.id;

---

🔍 常用查詢

查詢特定員工的完整資訊

-- 以 alice.wang 為例
SELECT
    e.*,
    bu.name as business_unit_name,
    d.name as division_name
FROM employees e
LEFT JOIN business_units bu ON e.business_unit_id = bu.id
LEFT JOIN divisions d ON e.division_id = d.id
WHERE e.username = 'alice.wang';

查詢員工的郵件帳號

SELECT
    e.username,
    e.email,
    ea.email_address,
    ea.mailbox_quota_mb,
    ea.is_active
FROM employees e
LEFT JOIN email_accounts ea ON e.id = ea.employee_id
WHERE e.username = 'alice.wang';

查詢員工的網路硬碟

SELECT
    e.username,
    nd.drive_name,
    nd.quota_gb,
    nd.webdav_url,
    nd.smb_path
FROM employees e
LEFT JOIN network_drives nd ON e.id = nd.employee_id
WHERE e.username = 'alice.wang';

查詢員工的系統權限

SELECT
    e.username,
    sp.system_name,
    sp.access_level,
    sp.granted_at
FROM employees e
LEFT JOIN system_permissions sp ON e.id = sp.employee_id
WHERE e.username = 'alice.wang'
AND sp.is_active = true;

部門統計

SELECT
    bu.name as business_unit,
    d.name as division,
    COUNT(e.id) as employee_count
FROM divisions d
LEFT JOIN business_units bu ON d.business_unit_id = bu.id
LEFT JOIN employees e ON d.id = e.division_id AND e.status = 'active'
GROUP BY bu.name, d.name
ORDER BY bu.name, d.name;

---

🧪 功能測試

測試 1: 新增員工

INSERT INTO employees (
    employee_id,
    username,
    first_name,
    last_name,
    chinese_name,
    email,
    business_unit_id,
    division_id,
    position,
    job_level,
    hire_date,
    status
) VALUES (
    'E9999',
    'test.user',
    'Test',
    'User',
    '測試用戶',
    'test.user@lab.taipei',
    3,  -- 智能研發
    7,  -- 軟體研發部
    'Tester',
    'Junior',
    CURRENT_DATE,
    'active'
);

-- 驗證
SELECT * FROM employees WHERE username = 'test.user';

測試 2: 更新員工資料

UPDATE employees
SET
    job_level = 'Staff',
    position = 'Senior Tester',
    updated_at = CURRENT_TIMESTAMP
WHERE username = 'test.user';

-- 驗證
SELECT employee_id, username, position, job_level, updated_at
FROM employees
WHERE username = 'test.user';

測試 3: 關聯查詢

-- 查詢智能研發服務事業部的所有員工
SELECT
    e.employee_id,
    e.chinese_name,
    d.name as division,
    e.position
FROM employees e
JOIN divisions d ON e.division_id = d.id
JOIN business_units bu ON d.business_unit_id = bu.id
WHERE bu.code = 'smart-rd'
AND e.status = 'active'
ORDER BY d.name, e.employee_id;

測試 4: 聚合統計

-- 各事業部員工統計
SELECT
    bu.name as business_unit,
    COUNT(e.id) as total_employees,
    COUNT(CASE WHEN e.job_level IN ('C-Level', 'VP', 'Director', 'Manager') THEN 1 END) as managers,
    COUNT(CASE WHEN e.job_level NOT IN ('C-Level', 'VP', 'Director', 'Manager') THEN 1 END) as staff
FROM business_units bu
LEFT JOIN divisions d ON bu.id = d.business_unit_id
LEFT JOIN employees e ON d.id = e.division_id AND e.status = 'active'
GROUP BY bu.name
ORDER BY bu.name;

---

🔧 故障排除

問題 1: 無法連接資料庫

# 檢查 PostgreSQL 容器
docker ps | grep postgres

# 檢查端口
sudo netstat -tlnp | grep 5432

# 檢查防火牆
sudo ufw status

問題 2: 權限不足

-- 以 postgres 超級用戶執行
GRANT ALL PRIVILEGES ON DATABASE hr_portal TO hr_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO hr_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO hr_user;

問題 3: 重置資料庫

# 刪除並重建資料庫
docker exec postgres psql -U postgres -c "DROP DATABASE hr_portal;"
docker exec postgres psql -U postgres -c "CREATE DATABASE hr_portal OWNER hr_user;"

# 重新執行 Schema
docker exec -i postgres psql -U hr_user -d hr_portal < /path/to/init-db.sql

---

✅ 測試檢查清單

測試完成後,確認以下項目:

• 所有 9 個資料表已建立
• 3 個視圖可正常查詢
• 事業部資料 (4 筆)
• 部門資料 (13 筆)
• 測試員工資料已插入
• 關聯查詢正常運作
• 觸發器自動更新 updated_at
• 外鍵約束正常運作

---

📊 效能測試

查詢效能

-- 開啟查詢分析
EXPLAIN ANALYZE
SELECT * FROM v_employees_full;

-- 檢查索引
SELECT
    tablename,
    indexname,
    indexdef
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY tablename, indexname;

資料庫大小

-- 查看資料庫大小
SELECT
    pg_size_pretty(pg_database_size('hr_portal')) as database_size;

-- 查看各表大小
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

---

🔄 下一步

資料庫測試通過後:

1. ✅ 更新 backend/.env 設定 DATABASE_URL
2. ✅ 啟動後端服務測試連接
3. ✅ 使用 Python 測試 ORM 模型
4. ✅ 開發 API 端點

# 測試後端連接
cd backend
python -c "from app.db.database import engine; print(engine.connect().execute('SELECT version()').fetchone())"

---

資料庫設定與測試完成! 🎉