ZZoutian/figure2data-database-v2

1"""Schema compatibility test（方案 §49 Phase 0 / §52.2）。2 3验证 databasev1 的 schema.sql 与现有 database 完全兼容：4表、字段、FK、documents kind CHECK、核心 CHECK、VIEW。5"""6 7from __future__ import annotations8 9import sqlite310from pathlib import Path11 12import pytest13 14from databasev1 import constants as C15 16REQUIRED_TABLES = [17    "metadata", "samples", "documents", "features", "panels", "axes", "series",18    "human_reviews", "source_datasets", "artifact_registry", "review_queue", "design_references",19]20REQUIRED_VIEWS = ["split_summary", "chart_family_summary"]21 22SAMPLES_COLUMNS = [23    "sample_id", "dataset_version", "layer", "difficulty", "split", "split_ordinal",24    "image_path", "image_sha256", "image_width", "image_height", "array_path", "array_sha256",25    "parameter_group", "template_group", "style_group", "source_kind", "recipe_id", "seed",26    "license", "automatic_status", "human_status", "human_grade", "release_eligible",27    "panel_count", "axis_count", "series_count",28]29 30DOCUMENT_KINDS_SQL = "('manifest','truth','geometry','calibration','quality','scene','eval')"31 32 33@pytest.fixture(scope="module")34def schema_sql() -> str:35    return C.SCHEMA_SQL_PATH.read_text()36 37 38@pytest.fixture(scope="module")39def fresh_db(schema_sql) -> sqlite3.Connection:40    """用 databasev1 的 schema.sql 建一个内存库。"""41    con = sqlite3.connect(":memory:")42    con.executescript(schema_sql)43    yield con44    con.close()45 46 47def test_schema_sql_identical_to_existing():48    assert C.SCHEMA_SQL_PATH.read_bytes() == C.EXISTING_SCHEMA_SQL.read_bytes()49 50 51def test_required_tables(fresh_db):52    tables = {r[0] for r in fresh_db.execute("SELECT name FROM sqlite_master WHERE type='table'")}53    for t in REQUIRED_TABLES:54        assert t in tables, f"missing table {t}"55 56 57def test_required_views(fresh_db):58    views = {r[0] for r in fresh_db.execute("SELECT name FROM sqlite_master WHERE type='view'")}59    for v in REQUIRED_VIEWS:60        assert v in views, f"missing view {v}"61 62 63def test_samples_columns(fresh_db):64    cols = [r[1] for r in fresh_db.execute("PRAGMA table_info(samples)")]65    for c in SAMPLES_COLUMNS:66        assert c in cols, f"samples.{c} missing"67 68 69def test_documents_kind_check(fresh_db):70    sql = fresh_db.execute("SELECT sql FROM sqlite_master WHERE name='documents'").fetchone()[0]71    assert "kind IN ('manifest','truth','geometry','calibration','quality','scene','eval')" in sql72    assert "encoding = 'zlib-json'" in sql73 74 75def test_samples_check_constraints(fresh_db):76    sql = fresh_db.execute("SELECT sql FROM sqlite_master WHERE name='samples'").fetchone()[0]77    for frag in [78        "dataset_version IN ('1.0','1.1')",79        "layer IN ('reference','expansion')",80        "difficulty IN ('simple','medium','hard')",81        "split IN ('train','validation','test')",82        "automatic_status = 'passed'",83        "human_status IN ('pending','completed')",84        "release_eligible IN (0,1)",85        "(array_path IS NULL) = (array_sha256 IS NULL)",86    ]:87        assert frag in sql, f"missing CHECK fragment: {frag}"88 89 90def test_foreign_keys(fresh_db):91    fks = {r[2] for r in fresh_db.execute("PRAGMA foreign_key_list(documents)")}92    assert "samples" in fks93    fks_axes = {r[2] for r in fresh_db.execute("PRAGMA foreign_key_list(axes)")}94    assert "panels" in fks_axes95    fks_series = {r[2] for r in fresh_db.execute("PRAGMA foreign_key_list(series)")}96    assert "panels" in fks_series97 98 99def test_roundtrip_minimal_row(fresh_db):100    """能按现有契约写入一行 sample + 7 documents + 投影行（dataset_version='1.1'）。"""101    sid = "dbv1_00000001"102    fresh_db.execute("PRAGMA foreign_keys = ON")103    fresh_db.execute(104        "INSERT INTO samples VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",105        (sid, "1.1", "expansion", "medium", None, None, "images/x.png", "a" * 64, 800, 600,106         None, None, "pg", "tg", "sg", "synthetic", "synthetic_x_v1", 1, "CC0-1.0",107         "passed", "pending", None, 1, 1, 2, 2),108    )109    for kind in ("manifest", "truth", "geometry", "calibration", "quality", "scene", "eval"):110        fresh_db.execute(111            "INSERT INTO documents VALUES (?,?,?,?,?,?)",112            (sid, kind, "zlib-json", b"payload", 7, "b" * 64),113        )114    fresh_db.execute("INSERT INTO panels VALUES (?,?,?,?,?,?,?)", (sid, "panel_1", "main", None, "curve", "multi_curve", "T"))115    fresh_db.execute("INSERT INTO axes VALUES (?,?,?,?,?,?,?)", (sid, "panel_1_x", "panel_1", "x", "linear", "T", None))116    fresh_db.execute("INSERT INTO series VALUES (?,?,?,?,?,?)", (sid, "panel_1_series_1", "panel_1", "S", "data", "xy"))117    fresh_db.execute("INSERT INTO features VALUES (?,?)", (sid, "single_panel"))118    fresh_db.commit()119    (n,) = fresh_db.execute("SELECT COUNT(*) FROM documents WHERE sample_id=?", (sid,)).fetchone()120    assert n == 7121    fk_errors = list(fresh_db.execute("PRAGMA foreign_key_check"))122    assert fk_errors == []123 124 125def test_views_work_after_insert(fresh_db):126    fresh_db.execute(127        "UPDATE samples SET split='train', split_ordinal=1 WHERE sample_id='dbv1_00000001'"128    )129    fresh_db.commit()130    rows = list(fresh_db.execute("SELECT split, samples FROM split_summary"))131    assert ("train", 1) in rows132    fam = list(fresh_db.execute("SELECT chart_family, panels FROM chart_family_summary"))133    assert ("curve", 1) in fam134 135 136def test_existing_database_contract_matches_schema():137    """现有数据库的实际对象集合与 schema.sql 声明一致（确保我们复制的 schema 就是事实契约）。"""138    con = sqlite3.connect(f"file:{C.EXISTING_DATABASE_DIR / 'figure2data.sqlite3'}?mode=ro", uri=True)139    try:140        existing_tables = {r[0] for r in con.execute("SELECT name FROM sqlite_master WHERE type='table'")}141        existing_views = {r[0] for r in con.execute("SELECT name FROM sqlite_master WHERE type='view'")}142    finally:143        con.close()144    for t in REQUIRED_TABLES:145        assert t in existing_tables146    for v in REQUIRED_VIEWS:147        assert v in existing_views148