structure.sql

PRAGMA foreign_keys = ON;

CREATE TABLE export_version AS SELECT 9 AS version;

CREATE TABLE image_server (
    id INTEGER NOT NULL,
    display_name VARCHAR(250) NOT NULL,
    url TEXT NOT NULL,
    max_width INTEGER,
    max_height INTEGER,
    PRIMARY KEY (id),
    UNIQUE (url),
    CHECK (max_width >= 0 AND max_height >= 0)
);

CREATE TABLE image (
    id VARCHAR(37) NOT NULL,
    url TEXT NOT NULL,
    width INTEGER NOT NULL,
    height INTEGER NOT NULL,
    server_id INTEGER NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (server_id) REFERENCES image_server (id) ON DELETE CASCADE,
    UNIQUE (url),
    CHECK (width >= 0 AND height >= 0)
);

CREATE TABLE worker_version (
    id VARCHAR(37) NOT NULL,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) NOT NULL,
    type VARCHAR(50) NOT NULL,
    version INTEGER,
    revision VARCHAR(50),
    repository_url TEXT,
    PRIMARY KEY (id),
    CHECK ((version IS NULL) <> (revision IS NULL)),
    CHECK ((revision IS NULL) == (repository_url IS NULL))
);

CREATE TABLE worker_run (
    id VARCHAR(37) NOT NULL,
    worker_version_id VARCHAR(37) NOT NULL,
    model_version_id VARCHAR(37),
    model_id VARCHAR(37),
    model_name VARCHAR(100),
    configuration_id VARCHAR(37),
    configuration TEXT,
    PRIMARY KEY (id),
    FOREIGN KEY (worker_version_id) REFERENCES worker_version (id) ON DELETE CASCADE,
    CHECK ((model_version_id IS NULL) = (model_id IS NULL) AND (model_id IS NULL) = (model_name IS NULL)),
    CHECK ((configuration_id IS NULL) = (configuration IS NULL))
);

CREATE TABLE element (
    id VARCHAR(37) NOT NULL,
    created REAL NOT NULL,
    updated REAL NOT NULL,
    name VARCHAR(250) NOT NULL,
    type VARCHAR(50) NOT NULL,
    polygon TEXT,
    image_id VARCHAR(37),
    rotation_angle INTEGER NOT NULL DEFAULT 0,
    mirrored INTEGER NOT NULL DEFAULT 0,
    worker_version_id VARCHAR(37),
    worker_run_id VARCHAR(37),
    confidence REAL,
    PRIMARY KEY (id),
    FOREIGN KEY (image_id) REFERENCES image (id) ON DELETE CASCADE,
    FOREIGN KEY (worker_version_id) REFERENCES worker_version (id) ON DELETE CASCADE,
    FOREIGN KEY (worker_run_id) REFERENCES worker_run (id) ON DELETE CASCADE,
    CHECK ((image_id IS NULL AND polygon IS NULL) OR (image_id IS NOT NULL AND polygon IS NOT NULL)),
    CHECK (rotation_angle >= 0 AND rotation_angle <= 359),
    CHECK (mirrored = 0 OR mirrored = 1),
    CHECK (confidence IS NULL OR (confidence >= 0 AND confidence <= 1)),
    CHECK (worker_run_id IS NULL OR worker_version_id IS NOT NULL)
);

CREATE TABLE element_path (
    id VARCHAR(37) NOT NULL,
    parent_id VARCHAR(37) NOT NULL,
    child_id VARCHAR(37) NOT NULL,
    ordering INTEGER NOT NULL DEFAULT 0,
    PRIMARY KEY (id),
    FOREIGN KEY (parent_id) REFERENCES element (id) ON DELETE CASCADE,
    FOREIGN KEY (child_id) REFERENCES element (id) ON DELETE CASCADE,
    UNIQUE (parent_id, child_id),
    CHECK (ordering >= 0)
);

CREATE TABLE transcription (
    id VARCHAR(37) NOT NULL,
    element_id VARCHAR(37) NOT NULL,
    text TEXT NOT NULL,
    confidence REAL,
    orientation TEXT NOT NULL DEFAULT 'horizontal-lr',
    worker_version_id VARCHAR(37),
    worker_run_id VARCHAR(37),
    PRIMARY KEY (id),
    FOREIGN KEY (element_id) REFERENCES element (id) ON DELETE CASCADE,
    FOREIGN KEY (worker_version_id) REFERENCES worker_version (id) ON DELETE CASCADE,
    FOREIGN KEY (worker_run_id) REFERENCES worker_run (id) ON DELETE CASCADE,
    CHECK (confidence IS NULL OR (confidence >= 0 AND confidence <= 1)),
    CHECK (orientation IN ('horizontal-lr', 'horizontal-rl', 'vertical-lr', 'vertical-rl'))
);

CREATE TABLE classification (
    id VARCHAR(37) NOT NULL,
    element_id VARCHAR(37) NOT NULL,
    class_name VARCHAR(1024) NOT NULL,
    state VARCHAR(16) NOT NULL DEFAULT 'pending',
    moderator VARCHAR(255),
    confidence REAL,
    high_confidence INTEGER NOT NULL DEFAULT 0,
    worker_version_id VARCHAR(37),
    worker_run_id VARCHAR(37),
    PRIMARY KEY (id),
    FOREIGN KEY (element_id) REFERENCES element (id) ON DELETE CASCADE,
    FOREIGN KEY (worker_version_id) REFERENCES worker_version (id) ON DELETE CASCADE,
    FOREIGN KEY (worker_run_id) REFERENCES worker_run (id) ON DELETE CASCADE,
    CHECK (confidence IS NULL OR (confidence >= 0 AND confidence <= 1)),
    CHECK (high_confidence = 0 OR high_confidence = 1),
    CHECK (worker_run_id IS NULL OR worker_version_id IS NOT NULL)
);

CREATE TABLE entity (
    id VARCHAR(37) NOT NULL,
    name TEXT NOT NULL,
    type_id VARCHAR(37) NOT NULL,
    validated INTEGER NOT NULL DEFAULT 0,
    moderator VARCHAR(255),
    metas TEXT,
    worker_version_id VARCHAR(37),
    worker_run_id VARCHAR(37),
    PRIMARY KEY (id),
    FOREIGN KEY (worker_version_id) REFERENCES worker_version (id) ON DELETE CASCADE,
    FOREIGN KEY (worker_run_id) REFERENCES worker_run (id) ON DELETE CASCADE,
    FOREIGN KEY (type_id) REFERENCES entity_type (id) ON DELETE CASCADE,
    CHECK (validated = 0 OR validated = 1),
    CHECK (worker_run_id IS NULL OR worker_version_id IS NOT NULL)
);

CREATE TABLE entity_type (
    id VARCHAR(37) NOT NULL,
    name TEXT NOT NULL,
    color VARCHAR(6) NOT NULL DEFAULT 'ff0000',
    PRIMARY KEY (id)
    UNIQUE (name)
);

CREATE TABLE transcription_entity (
    id INTEGER NOT NULL,
    transcription_id VARCHAR(37) NOT NULL,
    entity_id VARCHAR(37) NOT NULL,
    offset INTEGER NOT NULL,
    length INTEGER NOT NULL,
    worker_version_id VARCHAR(37),
    worker_run_id VARCHAR(37),
    confidence REAL,
    PRIMARY KEY (id),
    FOREIGN KEY (transcription_id) REFERENCES transcription (id) ON DELETE CASCADE,
    FOREIGN KEY (entity_id) REFERENCES entity (id) ON DELETE CASCADE,
    FOREIGN KEY (worker_version_id) REFERENCES worker_version (id) ON DELETE CASCADE,
    FOREIGN KEY (worker_run_id) REFERENCES worker_run (id) ON DELETE CASCADE,
    UNIQUE (transcription_id, entity_id, offset, length, worker_version_id, worker_run_id),
    CHECK (offset >= 0 AND length >= 0),
    CHECK (confidence IS NULL OR (confidence >= 0 AND confidence <= 1)),
    CHECK (worker_run_id IS NULL OR worker_version_id IS NOT NULL)
);

CREATE TABLE metadata (
    id VARCHAR(37) NOT NULL,
    element_id VARCHAR(37) NOT NULL,
    name VARCHAR(250) NOT NULL,
    type VARCHAR(50) NOT NULL,
    value TEXT NOT NULL,
    entity_id VARCHAR(37),
    worker_version_id VARCHAR(37),
    worker_run_id VARCHAR(37),
    PRIMARY KEY (id),
    FOREIGN KEY (element_id) REFERENCES element (id) ON DELETE CASCADE,
    FOREIGN KEY (entity_id) REFERENCES entity (id) ON DELETE SET NULL,
    FOREIGN KEY (worker_version_id) REFERENCES worker_version (id) ON DELETE CASCADE,
    FOREIGN KEY (worker_run_id) REFERENCES worker_run (id) ON DELETE CASCADE,
    CHECK (worker_run_id IS NULL OR worker_version_id IS NOT NULL)
);

CREATE TABLE dataset (
    id VARCHAR(37) NOT NULL,
    name VARCHAR(100) NOT NULL,
    state VARCHAR(50) NOT NULL DEFAULT 'open',
    sets TEXT NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE dataset_element (
    id VARCHAR(37) NOT NULL,
    element_id VARCHAR(37) NOT NULL,
    dataset_id VARCHAR(37) NOT NULL,
    set_name VARCHAR(50) NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (element_id) REFERENCES element (id) ON DELETE NO ACTION,
    FOREIGN KEY (dataset_id) REFERENCES dataset (id) ON DELETE NO ACTION
);