Artifact 6f5bba95897e2daec1a532dac5bb9238a57603d23114ad8cefedc8678201009b:

• File data/sql/schema.sql — part of check-in [0d5487156c] at 2025-12-03 20:36:49 on branch trunk — add 2025 Nov IRI work -- explicit schema.sql (user: sisrahtak, size: 2783) [annotate] [blame] [check-ins using]

BEGIN;
PRAGMA foreign_keys = ON;

CREATE TABLE spatial (
    id INTEGER PRIMARY KEY,
    latitude REAL,
    longitude REAL,
    off_name TEXT NOT NULL,
    lang TEXT NOT NULL CHECK (lang IN ('RUS', 'CHU', 'HIM', 'MEM', 'ERZ', 'TAT', 'UNK')),
    toponym_class TEXT NOT NULL,
    toponym_type TEXT NOT NULL,
    district TEXT,
    doubt INTEGER NULL CHECK (doubt IN ('', 1)),
    landmark TEXT,
    comments TEXT,
    other TEXT,
    CHECK ((latitude = '' AND longitude = '')
        OR (latitude != '' AND longitude != '' AND
            latitude BETWEEN 54.62 AND 56.4 AND
            longitude BETWEEN 45.87 AND 48.48))
);

CREATE TABLE water (
    id INTEGER PRIMARY KEY,
    spatial_id INTEGER,
    spatial_off_name TEXT,
    water_type TEXT NOT NULL,
    water_wkt TEXT UNIQUE NOT NULL,
    FOREIGN KEY (spatial_id, spatial_off_name)
        REFERENCES spatial (id, off_name)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);

CREATE TABLE linguistic (
    id INTEGER PRIMARY KEY,
    spatial_id INTEGER,
    doubt_spatial INTEGER CHECK (doubt_spatial IN ('', 1)),
    main_id INTEGER,
    toponym TEXT NOT NULL,
    toponym_forms TEXT NOT NULL,
    doubt_toponym INTEGER CHECK (doubt_toponym IN ('', 1)),
    lang TEXT NOT NULL,
    doubt_lang INTEGER CHECK (doubt_lang IN ('', 1)),
    pronunciation TEXT,
    doubt_pronunciation INTEGER CHECK (doubt_pronunciation IN ('', 1)),
    etym TEXT,
    origin TEXT,
    comments TEXT,
    other TEXT,
    FOREIGN KEY (spatial_id)
        REFERENCES spatial (spatial_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    FOREIGN KEY (main_id)
        REFERENCES linguistic (id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);

CREATE TABLE sources (
    id INTEGER PRIMARY KEY,
    source_type TEXT NOT NULL,
    title TEXT NOT NULL,
    source_year INTEGER NOT NULL CHECK (source_year > 100 AND source_year < 2026),
    citation TEXT UNIQUE NOT NULL,
    url TEXT UNIQUE,
    digital_copy TEXT CHECK (digital_copy IN ('yes', 'no')),
    comments TEXT,
    other TEXT
);

CREATE TABLE temporal (
    id INTEGER PRIMARY KEY,
    linguistic_id INTEGER,
    linguistic_name TEXT,
    nest_id INTEGER NOT NULL,
    start_year INTEGER NOT NULL CHECK (start_year > 100 AND start_year < 2025),
    doubt_start INTEGER CHECK (doubt_start IN ('', 1)),
    end_year TEXT CHECK ((end_year = '') OR (CAST(end_year AS INTEGER) BETWEEN 100 AND 2025)),
    doubt_end INTEGER CHECK (doubt_end IN ('', 1)),
    comments TEXT,
    other TEXT,
    fulltext TEXT,
    FOREIGN KEY (linguistic_id, linguistic_name)
        REFERENCES linguistic (id, toponym)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    FOREIGN KEY (nest_id)
        REFERENCES temporal (id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);

COMMIT;