from __future__ import annotations import sqlite3 import shutil from pathlib import Path from database.db_helper import Database, DatabaseSettings, SqliteSettings from database.seed import seed_default_material_categories REQUIRED_RUNTIME_TABLES = { "material_categories", "materials", "workflow_generation_rounds", "workflow_generation_candidates", "workflow_round_candidate_pool", "character_profile_cards", "character_profile_card_assets", } def _database(path: Path) -> Database: return Database( DatabaseSettings( backend="sqlite", sqlite=SqliteSettings(path=str(path)), ) ) def _table_names(path: Path) -> set[str]: with sqlite3.connect(path) as connection: rows = connection.execute( "SELECT name FROM sqlite_master WHERE type = 'table'" ).fetchall() return {str(row[0]) for row in rows} def test_fresh_install_and_repeated_initialize_create_all_runtime_tables(tmp_path: Path) -> None: path = tmp_path / "fresh-install.sqlite3" database = _database(path) database.initialize() database.initialize() assert REQUIRED_RUNTIME_TABLES <= _table_names(path) seeded = seed_default_material_categories(database) assert seeded["total"] >= 1 def test_applied_migration_is_repaired_when_material_table_is_missing(tmp_path: Path) -> None: path = tmp_path / "missing-material-table.sqlite3" database = _database(path) database.initialize() with sqlite3.connect(path) as connection: connection.execute("PRAGMA foreign_keys = OFF") connection.execute("DROP TABLE material_categories") assert connection.execute( "SELECT version FROM schema_migrations WHERE version = ?", ("0008_materials",), ).fetchone() connection.commit() database.initialize() seeded = seed_default_material_categories(database) assert "material_categories" in _table_names(path) assert seeded["total"] >= 1 def test_partially_missing_new_migration_tables_are_recreated_idempotently(tmp_path: Path) -> None: path = tmp_path / "partial-new-tables.sqlite3" database = _database(path) database.initialize() with sqlite3.connect(path) as connection: connection.execute("PRAGMA foreign_keys = OFF") connection.execute("DROP TABLE workflow_round_candidate_pool") connection.execute("DROP TABLE character_profile_card_assets") connection.commit() database.initialize() database.initialize() assert REQUIRED_RUNTIME_TABLES <= _table_names(path) def test_legacy_schema_upgrades_in_place_without_replacing_existing_data(tmp_path: Path) -> None: path = tmp_path / "legacy-upgrade.sqlite3" legacy_migrations = tmp_path / "legacy-migrations" legacy_migrations.mkdir() migration_root = Path(__file__).resolve().parents[1] / "database" / "migrations" for source in sorted(migration_root.glob("*.sql")): if source.stem <= "0013_model_providers": shutil.copy2(source, legacy_migrations / source.name) database = _database(path) database.initialize(legacy_migrations) with sqlite3.connect(path) as connection: connection.execute( "INSERT INTO character_profiles " "(profile_id, owner_platform, owner_user_id, name, metadata_json) " "VALUES (?, ?, ?, ?, ?)", ("profile-legacy", "qq", "owner-legacy", "旧角色", "{}"), ) connection.commit() database.initialize() assert REQUIRED_RUNTIME_TABLES <= _table_names(path) with sqlite3.connect(path) as connection: row = connection.execute( "SELECT name FROM character_profiles WHERE profile_id = ?", ("profile-legacy",), ).fetchone() assert row == ("旧角色",)