CREATE TABLE meta
(
    id    INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    key   VARCHAR NOT NULL,
    value VARCHAR NOT NULL,
    UNIQUE(key)
);

INSERT INTO meta (key, value)
VALUES ('VERSION', '0');

CREATE TABLE files
(
    id    INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    hash  BLOB                              NOT NULL,
    path  VARCHAR                           NOT NULL,
    valid BOOLEAN                           NOT NULL DEFAULT TRUE,
    added DATETIME                          NOT NULL,
    size  BIGINT                            NOT NULL,
    mtime DATETIME                          NULL
);

CREATE INDEX files_hash ON files (hash);
CREATE INDEX files_valid ON files (valid);

CREATE TABLE data
(
    identity          BLOB PRIMARY KEY NOT NULL,
    entity            BLOB             NOT NULL,
    entity_searchable VARCHAR,
    attribute         VARCHAR          NOT NULL,
    value_str         VARCHAR,
    value_num         NUMERIC,
    immutable         BOOLEAN          NOT NULL
);

CREATE INDEX data_entity ON data (entity);
CREATE INDEX data_entity_searchable ON data (entity_searchable);
CREATE INDEX data_attribute ON data (attribute);
CREATE INDEX data_value_str ON data (value_str);
CREATE INDEX data_value_num ON data (value_num);
