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

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)
);