93 lines
3.7 KiB
PL/PgSQL
93 lines
3.7 KiB
PL/PgSQL
-- ============================================================================
|
||
-- 【手動數據庫遷移腳本】版本追踪系統
|
||
-- 如果自動遷移失敗，請由數據庫管理員手動執行此腳本
|
||
-- ============================================================================
|
||
|
||
-- 【步驟 1】添加 is_latest 字段
|
||
-- 用於標記該交易版本是否為最新版本（true = 最新，false = 已過時）
|
||
ALTER TABLE journal_entries
|
||
ADD COLUMN IF NOT EXISTS is_latest BOOLEAN DEFAULT true;
|
||
|
||
-- 【步驟 2】添加 revision_count 字段
|
||
-- 用於追踪修正版本（1 = 原始，2 = 第一次修正，3 = 第二次修正，...）
|
||
ALTER TABLE journal_entries
|
||
ADD COLUMN IF NOT EXISTS revision_count INTEGER DEFAULT 1;
|
||
|
||
-- 【步驟 3】添加 original_entry_id 字段
|
||
-- 用於指向原始交易 ID（NULL = 這是原始交易，>0 = 這是對ID的修正版本）
|
||
ALTER TABLE journal_entries
|
||
ADD COLUMN IF NOT EXISTS original_entry_id INTEGER;
|
||
|
||
-- 【步驟 4】創建性能索引
|
||
-- 用於快速查詢最新版本的交易（試算表查詢會經常使用）
|
||
CREATE INDEX IF NOT EXISTS idx_journal_entries_is_latest
|
||
ON journal_entries(is_latest, entry_date DESC);
|
||
|
||
-- 【步驟 5】創建追踪索引
|
||
-- 用於快速查詢指定交易的所有修正版本
|
||
CREATE INDEX IF NOT EXISTS idx_journal_entries_original_id
|
||
ON journal_entries(original_entry_id) WHERE original_entry_id IS NOT NULL;
|
||
|
||
-- ============================================================================
|
||
-- 【驗證】
|
||
-- ============================================================================
|
||
-- 執行以下查詢確認字段已添加：
|
||
SELECT column_name, data_type, column_default
|
||
FROM information_schema.columns
|
||
WHERE table_name = 'journal_entries'
|
||
AND column_name IN ('is_latest', 'revision_count', 'original_entry_id')
|
||
ORDER BY ordinal_position DESC;
|
||
|
||
-- 查詢索引是否已創建：
|
||
SELECT indexname, indexdef
|
||
FROM pg_indexes
|
||
WHERE tablename = 'journal_entries'
|
||
AND indexname LIKE 'idx_journal_entries%';
|
||
|
||
-- ============================================================================
|
||
-- 【可選】數據初始化
|
||
-- ============================================================================
|
||
-- 如果表中已有舊數據，執行以下操作以確保一致性：
|
||
|
||
-- 使用 begin;...commit; 作為事務邊界
|
||
BEGIN;
|
||
|
||
-- 確保所有現有交易都被標記為最新版本
|
||
UPDATE journal_entries
|
||
SET is_latest = true
|
||
WHERE is_latest IS NULL;
|
||
|
||
-- 確保所有現有交易的 revision_count 都至少為 1
|
||
UPDATE journal_entries
|
||
SET revision_count = 1
|
||
WHERE revision_count IS NULL;
|
||
|
||
-- 提交更改
|
||
COMMIT;
|
||
|
||
-- ============================================================================
|
||
-- 【完成】
|
||
-- ============================================================================
|
||
-- 遷移完成！系統現在支持版本追踪。
|
||
--
|
||
-- 關鍵概念：
|
||
-- - is_latest = true: 當前最新版本，應該在試算表和列表中顯示
|
||
-- - is_latest = false: 已過時的版本，通常只在修正歷史中顯示
|
||
-- - revision_count: 版本編號（1=原始，2=第一次修正，...）
|
||
-- - original_entry_id: 指向原始交易，NULL 表示這是原始交易
|
||
--
|
||
-- 常見查詢：
|
||
-- 1. 獲取所有最新交易（用於試算表）:
|
||
-- SELECT * FROM journal_entries WHERE is_latest = true AND is_deleted = false;
|
||
--
|
||
-- 2. 獲取指定交易的修正歷史:
|
||
-- SELECT * FROM journal_entries
|
||
-- WHERE journal_entry_id = 123 OR original_entry_id = 123
|
||
-- ORDER BY revision_count ASC;
|
||
--
|
||
-- 3. 獲取修正後的版本:
|
||
-- SELECT * FROM journal_entries
|
||
-- WHERE original_entry_id IS NOT NULL AND is_latest = true;
|
||
--
|
||
-- ============================================================================
|