git-in-postgres.sql

CREATE TABLE users (
    name TEXT UNIQUE NOT NULL PRIMARY KEY
);

CREATE TABLE branches (
    name TEXT UNIQUE NOT NULL,
    owner TEXT NOT NULL REFERENCES users(name),

    PRIMARY KEY(name, owner)
);

CREATE TABLE commits (
    id BIGINT PRIMARY KEY,
    branch_name TEXT NOT NULL REFERENCES branches(name),
    change_type TEXT NOT NULL,
    document_type TEXT NOT NULL
);

CREATE TABLE tags (
    name TEXT NOT NULL,
    owner TEXT NOT NULL,
    commit_id BIGINT NOT NULL REFERENCES commits(id),
    public BOOLEAN NOT NULL DEFAULT(false),

    PRIMARY KEY(name, owner)
);

CREATE SEQUENCE commits_id INCREMENT BY 1 START 1 OWNED BY commits.id;

INSERT INTO users (name) VALUES
    ('fred'),
    ('bob'),
    ('jim');

INSERT INTO branches (name, owner) VALUES
    ('my_foo_branch', 'fred');

INSERT INTO commits (id, branch_name, change_type, document_type) VALUES
    (1, 'my_foo_branch', 'ADDITION', 'TM-credentials-store'),
    (2, 'my_foo_branch', 'ADDITION', 'TM-continuous-integration'),
    (3, 'my_foo_branch', 'ADDITION', 'TM-deployment guide');

INSERT INTO tags (name, owner, commit_id) VALUES
    ('my branch release', 'fred', 3);