schema.sql

CREATE TABLE product(
    id          BIGSERIAL,
    root_id     BIGINT,
    parent_id   BIGINT,

    PRIMARY KEY(id),
    FOREIGN KEY(parent_id) REFERENCES product(id) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY(root_id) REFERENCES product(id) ON DELETE RESTRICT ON UPDATE CASCADE
);
CREATE INDEX product_parent_id_index ON product USING BTREE(parent_id);
CREATE INDEX product_root_id_index ON product USING BTREE(root_id);

CREATE TABLE storage(
    id              BIGSERIAL,
    type            SMALLINT,    -- Type of the storage (whole, chunk)
    db_type         SMALLINT,    -- Type of database for storage management (sqlite)
    db              TEXT,   -- Used to locate where the db is

    PRIMARY KEY(id),
    FOREIGN KEY(type) REFERENCES storage_type(id) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY(db_type) REFERENCES storage_db_type(id) ON DELETE RESTRICT ON UPDATE CASCADE,
    NOT NULL(type),
    NOT NULL(db_type),
    NOT NULL(db)
);

CREATE TABLE storage_type(
    id      SMALLSERIAL,
    name    VARCHAR(64),

    PRIMARY KEY(id)
);

CREATE TABLE storage_db_type(
    id      SMALLSERIAL,
    name    VARCHAR(64),

    PRIMARY KEY(id)
);

CREATE TABLE detail(
    product_id          BIGINT,
    product_root_id     BIGINT,
    hidden              BOOLEAN NOT NULL DEFAULT FALSE,
    added_timestamp     TIMESTAMPTZ NOT NULL DEFAULT TIMEZONE('UTC', NOW()),
    modified_timestamp  TIMESTAMPTZ NOT NULL DEFAULT TIMEZONE('UTC', NOW()),
    name                TEXT,
    name_vector         TS_VECTOR,
    type                SMALLINT,
    description         TEXT,           -- Should have an option for user to enable description vector?
    publishers          TEXT[],
    release_year        SMALLINT,
    tags                VARCHAR(255)[],
    size_in_bytes       BIGINT,

    PRIMARY KEY(product_id),
    FOREIGN KEY(product_id) REFERENCES product(id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(product_root_id) REFERENCES product(id) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY(type) REFERENCES product_type(id) ON DELETE RESTRICT ON UPDATE CASCADE,
    NOT NULL(product_root_id),
    NOT NULL(name),
    NOT NULL(type)
);
CREATE INDEX detail_release_year_index ON detail USING BTREE(release_year, product_root_id);
CREATE INDEX detail_type_index ON detail USING BTREE(type, name);

CREATE TABLE product_type(
    id      SMALLSERIAL,
    name    VARCHAR(64),

    PRIMARY KEY(id)
);

CREATE TABLE genre(
    id      SERIAL,
    name    VARCHAR(255),

    PRIMARY KEY(id),
    NOT NULL (name)
);

CREATE TABLE platform(
    id      SERIAL,
    name    VARCHAR(255),

    PRIMARY KEY(id),
    NOT NULL (name)
);

CREATE TABLE global_id_type(
    id      SERIAL,
    name    VARCHAR(255),

    PRIMARY KEY(id),
    UNIQUE(name)
);

CREATE TABLE global_id(
    type            INT,
    id              BIGSERIAL,
    name            TEXT,
    name_vector     TS_VECTOR,
    publishers      TEXT[],
    release_year    SMALLINT,

    PRIMARY KEY(type, id),
    FOREIGN KEY(type) REFERENCES global_id_type(id) ON UPDATE CASCADE ON DELETE RESTRICT
);

CREATE TABLE global_id_product_join(
    global_id_type  INT,
    global_id       BIGINT,
    product_id      BIGINT,

    PRIMARY KEY(global_id_type, global_id),
    FOREIGN KEY(global_id_type, global_id) REFERENCES global_id(type, id) ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY(product_id) REFERENCES product(id) ON UPDATE CASCADE ON DELETE CASCADE
)

-- M-M table
CREATE TABLE product_storage_join(
    product_id BIGINT,
    storage_id BIGINT,
    product_hash BYTEA,

    FOREIGN KEY(product_id) REFERENCES product(id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(storage_id) REFERENCES storage(id) ON DELETE RESTRICT ON UPDATE CASCADE
);
CREATE INDEX product_storage_join_product_id_storage_id_index ON product_storage_join USING BTREE(product_id, storage_id);
CREATE INDEX product_storage_join_storage_id_index ON product_storage_join USING BTREE(storage_id);
CREATE INDEX product_storage_join_product_hash_storage_id_index ON product_storage_join USING BTREE(product_hash, storage_id);

CREATE TABLE product_genre_join(
    product_id BIGINT,
    genre_id   INT,

    FOREIGN KEY(product_id) REFERENCES product(id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(genre_id) REFERENCES genre(id) ON DELETE RESTRICT ON UPDATE CASCADE
);
CREATE INDEX product_genre_join_product_id_index ON product_genre_join USING BTREE(product_id);
CREATE INDEX product_genre_join_genre_id_index ON product_genre_join USING BTREE(genre_id);

CREATE TABLE product_platform_join(
    product_id  BIGINT,
    platform_id INT,

    FOREIGN KEY(product_id) REFERENCES product(id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(platform_id) REFERENCES platform(id) ON DELETE RESTRICT ON UPDATE CASCADE
);
CREATE INDEX product_platform_join_product_id_index ON product_platform_join USING BTREE(product_id);
CREATE INDEX product_platform_join_platform_id_index ON product_platform_join USING BTREE(platform_id);

-- --- Procedures
-- CREATE FUNCTION update_detail_timestamp_modified
--     (IN)

-- --- Triggers
-- CREATE TRIGGER after_update_detail AFTER UPDATE
--         OF name, type, description, publishers, year, tags
--         ON detail
--         DEFERRABLE
--         FOR EACH ROW
--         WHEN (OLD.* IS DISTINCT FROM NEW.*)
--         EXECUTE FUNCTION update --TODO

-- CREATE TRIGGER after_insert_genre_product_imm AFTER INSERT
--         ON product_genre_join
--         DEFFERABLE
--         WHEN (OLD.* IS DISTINCT FROM NEW.*)
--         FOR EACH ROW
        

-- Storage db
CREATE TABLE storage_engine(
    key    TEXT,
    value   TEXT,

    PRIMARY KEY(key)
);

CREATE TABLE build(
    id                      INTEGER,
    product_hash            BLOB,
    name                    TEXT NOT NULL,
    product_name            TEXT,
    category                TEXT DEFAULT 'default',
    metadata_file           TEXT,
    sort_order              INTEGER,
    size_in_bytes           INTEGER,
    db                      TEXT,

    PRIMARY KEY(id AUTOINCREMENT)
);
CREATE INDEX build_product_hash ON build USING BTREE(product_hash);

-- chunk db
CREATE TABLE file(
    id INTEGER,
    build_id INTEGER,
    path TEXT,
    size_in_bytes INTEGER,
    content_hash BLOB,       -- Should be SHA256

    PRIMARY KEY(id AUTOINCREMENT)
);
CREATE INDEX file_bid_path_index ON file USING BTREE(build_id, path);
CREATE INDEX file_content_hash_index ON file USING BTREE(content_hash);

CREATE TABLE directory( -- Only use for empty directory
    id INTEGER,
    build_id INTEGER,
    path TEXT,

    PRIMARY KEY(id AUTOINCREMENT)
);
CREATE INDEX directory_bid_path_index ON directory USING BTREE(build_id, path);

CREATE TABLE chunk(
    hash BLOB,
    size_in_bytes INTEGER,

    PRIMARY KEY(hash),
);
CREATE INDEX chunk_hash_index ON chunk USING BTREE(hash);

CREATE TABLE chunk_file_join(
    file_id INTEGER,
    file_offset INTEGER,
    chunk_hash BLOB,

    PRIMARY KEY(file_id, offset),
    FOREIGN KEY(file_id) REFERENCES file(id) ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY(chunk_hash) REFERENCES chunk(hash) ON UPDATE RESTRICT ON DELETE RESTRICT
)
-- CREATE INDEX chunk_fid_offset_index ON chunk USING BTREE(file_id, offset);

-- end chunk db

CREATE TABLE aux_whole(
    build_id  INTEGER,
    path        TEXT,

    PRIMARY KEY(build_id),
    FOREIGN KEY(build_id) REFERENCES build(id) ON DELETE CASCADE ON UPDATE CASCADE
);