setup/postgres/psql_create_tables.sql

-- This script creates the module management tables
--

create table lpit_user (
    userid          serial          primary key,
    username        varchar(50)     not null unique,
    mail            varchar(50),
    lastname        varchar(50),
    givenname       varchar(50)
);

create table lpit_project (
    id              serial          primary key,
    name            varchar(20)     not null unique,
    description     varchar(200),
    repoUrl         varchar(50),
    owner           integer         references lpit_user(userid)
);

create type version_status as enum (
    'Future',
    'Unreleased',
    'Released',
    'LTS',
    'Deprecated'
);

create table lpit_version (
    id              serial          primary key,
    project         integer         not null references lpit_project(id),
    name            varchar(20)     not null,
    ordinal         integer         not null default 0,
    status          version_status  not null default 'Future'
);