CardScoreDataServer/app/core/database_loader.py

import mysql.connector
from mysql.connector import errorcode
from .config import settings
from app.core.logger import get_logger

logger = get_logger(__name__)

# 全局的连接池
db_connection_pool = None


def init_database():
    """
    初始化数据库：如果数据库或表不存在，则创建它们。
    主键统一为 'id'。
    """
    logger.info("--- 开始初始化数据库 ---")

    try:
        cnx = mysql.connector.connect(**settings.DATABASE_CONFIG)
        cursor = cnx.cursor()

        cursor.execute(f"CREATE DATABASE IF NOT EXISTS {settings.DB_NAME} DEFAULT CHARACTER SET 'utf8mb4'")
        logger.info(f"数据库 '{settings.DB_NAME}' 已准备就绪。")
        cnx.database = settings.DB_NAME

        # 1. 创建 cards 表 (主键为 id)
        cards_table = (
            f"CREATE TABLE IF NOT EXISTS `{settings.DB_CARD_TABLE_NAME}` ("
            "  `id` INT AUTO_INCREMENT PRIMARY KEY,"
            "  `card_name` VARCHAR(255) NULL COMMENT '卡牌的通用名称',"
            "  `card_type` VARCHAR(50) NOT NULL DEFAULT 'pokemon' COMMENT '卡牌类型 (pokemon, basketball, etc)',"
            "  `detection_score` DECIMAL(4, 2) NULL COMMENT '原始检测总分',"
            "  `modified_score` DECIMAL(4, 2) NULL COMMENT '修改后总分',"
            "  `is_edited` BOOLEAN NOT NULL DEFAULT FALSE COMMENT '是否被编辑过',"
            "  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,"
            "  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"
            ") ENGINE=InnoDB COMMENT='存储实体卡牌的核心信息'"
        )
        cursor.execute(cards_table)
        logger.info(f"数据表 '{settings.DB_CARD_TABLE_NAME}' 已准备就绪。")

        # 2. 创建 card_images 表 (主键为 id)
        card_images_table = (
            f"CREATE TABLE IF NOT EXISTS `{settings.DB_IMAGE_TABLE_NAME}` ("
            "  `id` INT AUTO_INCREMENT PRIMARY KEY,"
            "  `card_id` INT NOT NULL COMMENT '关联的卡牌ID',"
            "  `image_type` VARCHAR(50) NOT NULL COMMENT '图片类型 (front_face, back_face, etc)',"
            "  `image_name` VARCHAR(255) NULL,"
            "  `image_path` VARCHAR(512) NOT NULL,"
            "  `detection_image_path` VARCHAR(512) NULL COMMENT '检测结果图的路径',"
            "  `modified_image_path` VARCHAR(512) NULL COMMENT '修改后结果图的路径',"
            "  `detection_json` JSON NOT NULL COMMENT '原始检测JSON数据',"
            "  `modified_json` JSON NULL COMMENT '修改后的JSON数据',"
            "  `is_edited` BOOLEAN NOT NULL DEFAULT FALSE COMMENT '该图片记录是否被修改过',"
            "  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,"
            "  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,"
            f"  FOREIGN KEY (`card_id`) REFERENCES `{settings.DB_CARD_TABLE_NAME}`(`id`) ON DELETE CASCADE,"
            "  UNIQUE KEY `uk_card_image_type` (`card_id`, `image_type`)"
            ") ENGINE=InnoDB COMMENT='存储所有卡牌的图片及检测数据'"
        )
        cursor.execute(card_images_table)
        logger.info(f"数据表 '{settings.DB_IMAGE_TABLE_NAME}' 已准备就绪。")

    except mysql.connector.Error as err:
        logger.error(f"数据库初始化失败: {err}")
        exit(1)
    finally:
        if 'cursor' in locals() and cursor:
            cursor.close()
        if 'cnx' in locals() and cnx.is_connected():
            cnx.close()

    logger.info("--- 数据库初始化完成 ---")


def load_database_pool():
    """
    在应用启动时创建数据库连接池。
    """
    global db_connection_pool
    if db_connection_pool is None:
        logger.info("--- 创建数据库连接池 ---")
        try:
            db_connection_pool = mysql.connector.pooling.MySQLConnectionPool(
                pool_name="mypool",
                pool_size=5,
                **settings.DATABASE_CONFIG_WITH_DB
            )
            logger.info("--- 数据库连接池创建成功 ---")
        except mysql.connector.Error as err:
            logger.error(f"创建数据库连接池失败: {err}")
            exit(1)


def close_database_pool():
    """
    在应用关闭时，不需要手动关闭连接池，连接器会自动处理。
    这个函数留作备用。
    """
    logger.info("--- 数据库连接池将自动关闭 ---")


# --- FastAPI 依赖注入 ---
def get_db_connection():
    """
    一个FastAPI依赖项，用于从池中获取数据库连接。
    它确保连接在使用后返回到池中。
    """
    if db_connection_pool is None:
        raise RuntimeError("数据库连接池未初始化")

    db_conn = None
    try:
        db_conn = db_connection_pool.get_connection()
        yield db_conn
    except mysql.connector.Error as err:
        logger.error(f"获取数据库连接失败: {err}")
    finally:
        if db_conn and db_conn.is_connected():
            db_conn.close()