pocket_archive/data/schema.sql

/* SQLite schema for pocket_archive. */

CREATE TABLE atbl_art_type (
    id int PRIMARY KEY AUTOINCREMENT,
    label text NOT NULL UNIQUE,
    broader_id int,

    FOREIGN KEY (broader_id) REFERENCES atbl_art_type(id)
);

/* Metadata schema. Each row represents a field definition. */
CREATE TABLE atbl_md_schema (
    id int PRIMARY KEY AUTOINCREMENT,
    uri TEXT NOT NULL UNIQUE,
    label TEXT NOT NULL UNIQUE,
    description TEXT,
    data_type int NOT NULL,
    min_card int DEFAULT 0,
    max_card int,
    range TEXT
);

CREATE TABLE tbl_metadatum (
    id int PRIMARY KEY AUTOINCREMENT,
    field_id int NOT NULL,
    value_int int,  /* This includes timestamps. */
    value_float real,
    value_str TEXT
    /* TODO add more types as needs arise. */
);

CREATE TABLE tbl_artifact (
    id int PRIMARY KEY AUTOINCREMENT,
    provided_id TEXT UNIQUE,
    type_id int NOT NULL,
    metadata_id int NOT NULL,

    FOREIGN KEY (type_id) REFERENCES atbl_art_type(id),
    FOREIGN KEY (metadata_id) REFERENCES tbl_metadatum(id) ON DELETE CASCADE
);