rssKeeper/backend/database.py

"""数据库连接与初始化"""
from sqlalchemy import create_engine, event
from sqlalchemy.orm import sessionmaker, declarative_base
from config import DATABASE_URL

# SQLite 连接
engine = create_engine(
    f"sqlite:///{DATABASE_URL}",
    connect_args={"check_same_thread": False},
    echo=False,
)

# 启用 SQLite 外键约束
@event.listens_for(engine, "connect")
def set_sqlite_pragma(dbapi_conn, connection_record):
    cursor = dbapi_conn.cursor()
    cursor.execute("PRAGMA foreign_keys=ON")
    cursor.close()

SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base = declarative_base()


def get_db():
    """FastAPI 依赖注入用"""
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()


def init_db():
    """初始化数据库表"""
    from models import Feed, Article, FetchLog  # noqa

    Base.metadata.create_all(bind=engine)
    init_fts5()


def init_fts5():
    """初始化 FTS5 全文搜索虚拟表"""
    conn = engine.raw_connection()
    cursor = conn.cursor()

    import logging
    logger = logging.getLogger(__name__)

    # 检查 FTS5 扩展是否可用
    try:
        cursor.execute("SELECT sqlite_compileoption_used('ENABLE_FTS5')")
        has_fts5 = cursor.fetchone()[0]
        if not has_fts5:
            logger.warning("SQLite 未启用 FTS5 扩展，全文搜索将不可用")
            return
    except Exception as e:
        logger.error(f"FTS5 检测失败: {e}")
        return

    # 创建 FTS5 虚拟表
    cursor.execute("""
        CREATE VIRTUAL TABLE IF NOT EXISTS articles_fts USING fts5(
            title, content,
            content='articles',
            content_rowid='id'
        )
    """)

    # 创建触发器，自动同步 articles 表到 FTS5
    cursor.execute("""
        CREATE TRIGGER IF NOT EXISTS articles_fts_insert AFTER INSERT ON articles BEGIN
            INSERT INTO articles_fts(rowid, title, content)
            VALUES (new.id, new.title, new.content);
        END
    """)
    cursor.execute("""
        CREATE TRIGGER IF NOT EXISTS articles_fts_delete AFTER DELETE ON articles BEGIN
            INSERT INTO articles_fts(articles_fts, rowid, title, content)
            VALUES ('delete', old.id, old.title, old.content);
        END
    """)
    cursor.execute("""
        CREATE TRIGGER IF NOT EXISTS articles_fts_update AFTER UPDATE ON articles BEGIN
            INSERT INTO articles_fts(articles_fts, rowid, title, content)
            VALUES ('delete', old.id, old.title, old.content);
            INSERT INTO articles_fts(rowid, title, content)
            VALUES (new.id, new.title, new.content);
        END
    """)

    conn.commit()
    cursor.close()
    conn.close()