File data/csv/db_experiment/v0.2/db_structure.sql artifact d36292f1ba part of check-in fbda0e251e127a50c3b455e0ead963326b30710824d9c9ba24e7f77c45d25ba3

CREATE TABLE spatial (
    ID INT PRIMARY KEY,
    LAT FLOAT NOT NULL,
    LON FLOAT NOT NULL,
    OFFNAME TEXT NOT NULL,
    LANG VARCHAR(3) NOT NULL,
    CLASS TEXT NOT NULL,
    TYPE TEXT NOT NULL,
    DISTRICT TEXT,
    DOUBT INT CHECK (DOUBT IN (1)) NULL,
    LANDMARK TEXT,
    COMMENTS TEXT,
    OTHER TEXT
);

CREATE TABLE linguistic (
    ID INT PRIMARY KEY,
    SPATID INT NOT NULL,
    DOUSPAT INT CHECK (DOUSPAT IN (1)) NULL,
    MAINID INT,
    TOPONYM TEXT NOT NULL,
    TOPFORMS TEXT,
    DOUTOPO INT CHECK (DOUTOPO IN (1)) NULL,
    LANG VARCHAR(3) NOT NULL,
    DOULANG INT CHECK (DOULANG IN (1)) NULL,
    PRONUNC TEXT,
    DOUPRON INT CHECK (DOUPRON IN (1)) NULL,
    ETYM TEXT, -- Always doubtful
    ORIGIN VARCHAR(3), -- Always doubtful
    COMMENTS TEXT,
    OTHER TEXT,
    FOREIGN KEY (SPATID) REFERENCES spatial(ID),
    FOREIGN KEY (MAINID) REFERENCES linguistic(ID)
);

CREATE TABLE temporal (
    ID INT PRIMARY KEY,
    LINGID INT NOT NULL,
    LINGNAME TEXT NOT NULL,
    START INT NOT NULL,
    DOUSTART INT CHECK (DOUSTART IN (1)) NULL,
    END INT,
    DOUEND INT CHECK (DOUEND IN (1)) NULL,
    EVENT TEXT CHECK (EVENT IN ('MERGEIN', 'ACTIVE', 'RENAME', 'CEASE')),
    OBJID INT,
    OBJNAME TEXT,
    FULLTEXT TEXT NOT NULL,
    COMMENTS TEXT,
    OTHER TEXT,
    FOREIGN KEY (LINGID, LINGNAME) REFERENCES linguistic(ID, TOPONYM),
    FOREIGN KEY (OBJID, OBJNAME) REFERENCES linguistic(ID, TOPONYM)
);