-- Releases attach notes and assets to tags (hub spec §9). Tags themselves

-- live in the repository; assets are content addressed under <data>/assets.

CREATE TABLE releases (

    id          INTEGER PRIMARY KEY,

    repo_id     INTEGER NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,

    tag         TEXT NOT NULL,

    title       TEXT NOT NULL,

    notes       TEXT NOT NULL DEFAULT '',

    prerelease  INTEGER NOT NULL DEFAULT 0,

    author_id   INTEGER REFERENCES accounts(id) ON DELETE SET NULL,

    author      TEXT NOT NULL DEFAULT '',

    created_ms  INTEGER NOT NULL,

    UNIQUE (repo_id, tag)

);

CREATE TABLE release_assets (

    id           INTEGER PRIMARY KEY,

    release_id   INTEGER NOT NULL REFERENCES releases(id) ON DELETE CASCADE,

    name         TEXT NOT NULL,

    size         INTEGER NOT NULL,

    hash         TEXT NOT NULL,

    content_type TEXT NOT NULL DEFAULT 'application/octet-stream',

    downloads    INTEGER NOT NULL DEFAULT 0,

    created_ms   INTEGER NOT NULL,

    UNIQUE (release_id, name)

);

-- Git mirror settings per repository (hub spec §14).

ALTER TABLE repositories ADD COLUMN git_mirror INTEGER NOT NULL DEFAULT 0;

ALTER TABLE repositories ADD COLUMN git_mirror_state TEXT NOT NULL DEFAULT '';