f114c209ce
All checks were successful
CD Pipeline / deploy (push) Successful in 1m7s
- 80 行 schema 修復邏輯抽出，搭配 _ensure_column helper 去除 7 個 promo_products 欄位重複碼塊 - app.py 改為 from database.schema_repair import repair_database_schema 維持原呼叫 - 行為 100% 對齊（含 SQLite WAL 啟用、products.created_at 補資料） 行數變化: app.py 7,151 → 7,070 (-81)
76 lines
3.2 KiB
Python
76 lines
3.2 KiB
Python
"""啟動時資料庫 schema 自動修復。
|
||
|
||
從 app.py 抽出的 repair_database_schema()。
|
||
歷史背景：早期 V9.53 起為了讓部署不必執行 migration，在啟動時動態檢查欄位並
|
||
ALTER TABLE 補上。長期應改走 Alembic / Migration script，但這個 fallback 還活著。
|
||
|
||
呼叫端：app.py 啟動末段呼叫一次。
|
||
"""
|
||
from utils.logger_manager import SystemLogger
|
||
|
||
_log = SystemLogger("SchemaRepair").get_logger()
|
||
|
||
|
||
def _ensure_column(engine, text_fn, table, column, ddl, post_sql=None):
|
||
"""補上單一欄位 helper：若不存在則 ALTER TABLE。"""
|
||
from sqlalchemy import inspect
|
||
inspector = inspect(engine)
|
||
if table not in inspector.get_table_names():
|
||
return False
|
||
columns = [c['name'] for c in inspector.get_columns(table)]
|
||
if column in columns:
|
||
return False
|
||
_log.warning(f"[Database] [Schema] ⚠️ 偵測到 {table} 表缺少 {column} 欄位 | 正在自動修復...")
|
||
with engine.connect() as conn:
|
||
conn.execute(text_fn(ddl))
|
||
if post_sql:
|
||
conn.execute(text_fn(post_sql))
|
||
conn.commit()
|
||
_log.info(f"[Database] [Schema] ✅ {table}.{column} 欄位修復完成")
|
||
return True
|
||
|
||
|
||
def repair_database_schema():
|
||
"""啟動時資料庫結構自動修復（V9.53）。"""
|
||
from database.manager import DatabaseManager
|
||
from sqlalchemy import inspect, text
|
||
from config import DATABASE_TYPE
|
||
|
||
db = DatabaseManager()
|
||
engine = db.engine
|
||
|
||
try:
|
||
# V9.96: SQLite WAL 模式（解決 database is locked）
|
||
if DATABASE_TYPE == 'sqlite':
|
||
with engine.connect() as conn:
|
||
conn.execute(text("PRAGMA journal_mode=WAL"))
|
||
conn.commit()
|
||
_log.info("[Database] [WAL] ✅ SQLite WAL 模式已啟用 | 提升並發寫入效能")
|
||
else:
|
||
_log.info(f"[Database] ✅ 使用 {DATABASE_TYPE.upper()} 資料庫")
|
||
|
||
inspector = inspect(engine)
|
||
|
||
# V9.70: products 表
|
||
_ensure_column(engine, text, 'products', 'image_url',
|
||
"ALTER TABLE products ADD COLUMN image_url TEXT")
|
||
_ensure_column(engine, text, 'products', 'created_at',
|
||
"ALTER TABLE products ADD COLUMN created_at DATETIME",
|
||
post_sql="UPDATE products SET created_at = updated_at WHERE created_at IS NULL")
|
||
|
||
# promo_products 表
|
||
promo_columns = [
|
||
('url', "ALTER TABLE promo_products ADD COLUMN url TEXT"),
|
||
('image_url', "ALTER TABLE promo_products ADD COLUMN image_url TEXT"),
|
||
('previous_price', "ALTER TABLE promo_products ADD COLUMN previous_price INTEGER"),
|
||
('session_time_text', "ALTER TABLE promo_products ADD COLUMN session_time_text TEXT"),
|
||
('remain_qty', "ALTER TABLE promo_products ADD COLUMN remain_qty INTEGER"),
|
||
('discount_text', "ALTER TABLE promo_products ADD COLUMN discount_text TEXT"),
|
||
('page_type', "ALTER TABLE promo_products ADD COLUMN page_type TEXT DEFAULT 'edm'"),
|
||
]
|
||
for col_name, ddl in promo_columns:
|
||
_ensure_column(engine, text, 'promo_products', col_name, ddl)
|
||
|
||
except Exception as e:
|
||
_log.error(f"[Database] [Schema] ❌ 資料庫修復失敗 | Error: {e}")
|