File tests/schema.py artifact 94d841082d part of check-in 5ece4833fc

# schema.py
import psycopg2

def create_tables(conn):
    """
    Creates tables in PostgreSQL with constraints.
    PostgreSQL supports more robust constraints than SQLite.
    """
    with conn.cursor() as cur:
        # Spatial Table
        cur.execute("""
        CREATE TABLE IF NOT EXISTS Spatial (
            ID SERIAL PRIMARY KEY,
            LAT REAL CHECK (LAT >= -90 AND LAT <= 90),
            LON REAL CHECK (LON >= -180 AND LON <= 180),
            OFFNAME TEXT NOT NULL,
            LANG TEXT NOT NULL CHECK (LANG IN ('RUS','CHU','HIM','MEM','ERZ','TAT','UNK','OTH')),
            CLASS TEXT,
            TYPE TEXT,
            DISTRICT TEXT,
            DOUBT INTEGER,
            LANDMARK TEXT,
            COMMENTS TEXT,
            OTHER TEXT
        );
        """)

        # Linguistic Table
        cur.execute("""
        CREATE TABLE IF NOT EXISTS Linguistic (
            ID SERIAL PRIMARY KEY,
            SPATID INTEGER REFERENCES Spatial(ID) ON DELETE CASCADE ON UPDATE CASCADE,
            DOUSPAT INTEGER,
            MAINID INTEGER REFERENCES Linguistic(ID) ON DELETE CASCADE ON UPDATE CASCADE,
            TOPONYM TEXT NOT NULL,
            TOPFORMS TEXT,
            DOUTOPO INTEGER,
            LANG TEXT NOT NULL CHECK (LANG IN ('RUS','CHU','HIM','MEM','ERZ','TAT','UNK','OTH')),
            DOULANG INTEGER,
            PRONUNC TEXT,
            DOUPRON INTEGER,
            ETYM TEXT,
            ORIGIN TEXT,
            COMMENTS TEXT,
            OTHER TEXT
        );
        """)

        # Temporal Table
        cur.execute("""
        CREATE TABLE IF NOT EXISTS Temporal (
            ID SERIAL PRIMARY KEY,
            LINGID INTEGER REFERENCES Linguistic(ID) ON DELETE CASCADE ON UPDATE CASCADE,
            LINGNAME TEXT,
            STARTYEAR INTEGER NOT NULL,
            DOUSTART INTEGER,
            ENDYEAR INTEGER,
            DOUEND INTEGER,
            EVENT TEXT NOT NULL CHECK (EVENT IN ('MERGEIN', 'ACTIVE', 'RENAME', 'CEASE')),
            OBJID INTEGER,
            OBJNAME TEXT,
            COMMENTS TEXT,
            OTHER TEXT,
            FULLTEXT TEXT NOT NULL,
            CONSTRAINT valid_years CHECK (ENDYEAR IS NULL OR STARTYEAR < ENDYEAR)
        );
        """)

        # Sources Table
        cur.execute("""
        CREATE TABLE IF NOT EXISTS Sources (
            ID TEXT PRIMARY KEY,
            TYPE TEXT NOT NULL,
            AUTHOR TEXT,
            TITLE TEXT NOT NULL,
            YEAR INTEGER,
            PUBLISHER TEXT,
            CITATION TEXT,
            COMMENTS TEXT,
            PDF TEXT,
            OTHER TEXT
        );
        """)

    conn.commit()

def drop_tables(conn):
    """Helper to drop tables for clean testing."""
    with conn.cursor() as cur:
        cur.execute("DROP TABLE IF EXISTS Temporal, Linguistic, Spatial, Sources CASCADE;")
    conn.commit()