134 lines
4.8 KiB
Python
134 lines
4.8 KiB
Python
#!/usr/bin/env python3
|
||
"""
|
||
修复孤立的 is_latest=false 记录
|
||
将所有 is_latest=false 且 original_entry_id=NULL 的记录
|
||
链接到它们对应的最新版本
|
||
"""
|
||
import psycopg
|
||
|
||
try:
|
||
conn = psycopg.connect(
|
||
host='192.168.0.61',
|
||
port=55432,
|
||
dbname='njts_acct',
|
||
user='njts_app',
|
||
password='njts_app2025'
|
||
)
|
||
|
||
with conn.cursor() as cur:
|
||
print("=" * 80)
|
||
print("【修復孤立記錄的版本鏈】")
|
||
print("=" * 80)
|
||
|
||
# 找出所有孤立的 is_latest=false, original_entry_id=NULL 的記錄
|
||
cur.execute("""
|
||
SELECT journal_entry_id, description, entry_date
|
||
FROM journal_entries
|
||
WHERE is_latest = false
|
||
AND original_entry_id IS NULL
|
||
AND is_deleted = false
|
||
ORDER BY journal_entry_id
|
||
""")
|
||
|
||
orphans = cur.fetchall()
|
||
|
||
if not orphans:
|
||
print("\n✓ 沒有孤立的記錄")
|
||
else:
|
||
print(f"\n發現 {len(orphans)} 條孤立記錄\n")
|
||
|
||
fixed_count = 0
|
||
|
||
for orphan in orphans:
|
||
orphan_id, desc, entry_date = orphan
|
||
|
||
print(f"【修復 ID={orphan_id}】{desc}")
|
||
|
||
# 找出這條記錄對應的最新版本
|
||
# 假設修正版本的 ID 會比原始版本更大（遞增）
|
||
# 且 is_latest=true，並且創建時間更晚
|
||
cur.execute("""
|
||
SELECT journal_entry_id
|
||
FROM journal_entries
|
||
WHERE journal_entry_id > %s
|
||
AND entry_date = %s
|
||
AND is_latest = true
|
||
AND (description LIKE %s OR description LIKE %s)
|
||
ORDER BY journal_entry_id DESC
|
||
LIMIT 1
|
||
""", (orphan_id, entry_date, f"%{desc}%", f"%【修正後】%{desc}%"))
|
||
|
||
latest_row = cur.fetchone()
|
||
|
||
if latest_row:
|
||
latest_id = latest_row[0]
|
||
# 更新孤立記錄，將其 original_entry_id 設置為它自己
|
||
# 這樣它就成為一條有效的"原始版本"
|
||
cur.execute("""
|
||
UPDATE journal_entries
|
||
SET original_entry_id = %s
|
||
WHERE journal_entry_id = %s
|
||
""", (orphan_id, orphan_id))
|
||
|
||
# 更新最新版本，確保指向正確的原始 ID
|
||
cur.execute("""
|
||
UPDATE journal_entries
|
||
SET original_entry_id = %s
|
||
WHERE journal_entry_id = %s
|
||
AND (original_entry_id IS NULL OR original_entry_id != %s)
|
||
""", (orphan_id, latest_id, orphan_id))
|
||
|
||
conn.commit()
|
||
print(f" ✓ 已將 ID={latest_id} 的 original_entry_id 設置為 {orphan_id}")
|
||
fixed_count += 1
|
||
else:
|
||
# 如果沒有找到更新的版本，就把這條記錄標記為原始版本
|
||
cur.execute("""
|
||
UPDATE journal_entries
|
||
SET original_entry_id = %s
|
||
WHERE journal_entry_id = %s
|
||
""", (orphan_id, orphan_id))
|
||
conn.commit()
|
||
print(f" ✓ 標記為原始版本")
|
||
fixed_count += 1
|
||
|
||
print(f"\n✓ 修復完成，共處理 {fixed_count} 條記錄")
|
||
|
||
# 驗證結果
|
||
print("\n【驗證結果】")
|
||
print("-" * 80)
|
||
|
||
cur.execute("""
|
||
SELECT COUNT(*)
|
||
FROM journal_entries
|
||
WHERE is_latest = false
|
||
AND original_entry_id IS NULL
|
||
AND is_deleted = false
|
||
""")
|
||
|
||
remaining = cur.fetchone()[0]
|
||
print(f"剩餘孤立記錄: {remaining} 條")
|
||
|
||
# 檢查 test 元 是否已修復
|
||
cur.execute("""
|
||
SELECT journal_entry_id, description, original_entry_id, is_latest
|
||
FROM journal_entries
|
||
WHERE description LIKE '%test%'
|
||
AND is_deleted = false
|
||
ORDER BY journal_entry_id
|
||
""")
|
||
|
||
test_records = cur.fetchall()
|
||
print(f"\ntest 元 記錄（修復後）:")
|
||
for rec in test_records:
|
||
je_id, desc, orig_id, is_latest = rec
|
||
status = "✓" if is_latest else " "
|
||
print(f"{status} ID={je_id}: original_entry_id={orig_id} - {desc}")
|
||
|
||
conn.close()
|
||
|
||
except Exception as e:
|
||
print(f'錯誤: {e}')
|
||
import traceback
|
||
traceback.print_exc()
|