setup/postgres/psql_create_tables.sql

Sun, 10 May 2020 10:58:31 +0200

author
Mike Becker <universe@uap-core.de>
date
Sun, 10 May 2020 10:58:31 +0200
changeset 37
fecda0f466e6
parent 36
0f4f8f255c32
child 62
833e0385572a
permissions
-rw-r--r--

adds data model for projects and versions

20
bd1a76c91d5b module synchronization with database
Mike Becker <universe@uap-core.de>
parents: 16
diff changeset
1 -- This script creates the module management tables
bd1a76c91d5b module synchronization with database
Mike Becker <universe@uap-core.de>
parents: 16
diff changeset
2 --
2
fcb452578142 adds create database setup script
Mike Becker <universe@uap-core.de>
parents:
diff changeset
3
37
fecda0f466e6 adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents: 36
diff changeset
4 create table lpit_user (
20
bd1a76c91d5b module synchronization with database
Mike Becker <universe@uap-core.de>
parents: 16
diff changeset
5 userid serial primary key,
bd1a76c91d5b module synchronization with database
Mike Becker <universe@uap-core.de>
parents: 16
diff changeset
6 username varchar(50) not null unique,
37
fecda0f466e6 adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents: 36
diff changeset
7 mail varchar(50),
20
bd1a76c91d5b module synchronization with database
Mike Becker <universe@uap-core.de>
parents: 16
diff changeset
8 lastname varchar(50),
bd1a76c91d5b module synchronization with database
Mike Becker <universe@uap-core.de>
parents: 16
diff changeset
9 givenname varchar(50)
bd1a76c91d5b module synchronization with database
Mike Becker <universe@uap-core.de>
parents: 16
diff changeset
10 );
37
fecda0f466e6 adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents: 36
diff changeset
11
fecda0f466e6 adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents: 36
diff changeset
12 create table lpit_project (
fecda0f466e6 adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents: 36
diff changeset
13 id serial primary key,
fecda0f466e6 adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents: 36
diff changeset
14 name varchar(20) not null unique,
fecda0f466e6 adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents: 36
diff changeset
15 description varchar(200),
fecda0f466e6 adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents: 36
diff changeset
16 repoUrl varchar(50),
fecda0f466e6 adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents: 36
diff changeset
17 owner integer references lpit_user(userid)
fecda0f466e6 adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents: 36
diff changeset
18 );
fecda0f466e6 adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents: 36
diff changeset
19
fecda0f466e6 adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents: 36
diff changeset
20 create type version_status as enum (
fecda0f466e6 adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents: 36
diff changeset
21 'Future',
fecda0f466e6 adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents: 36
diff changeset
22 'Unreleased',
fecda0f466e6 adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents: 36
diff changeset
23 'Released',
fecda0f466e6 adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents: 36
diff changeset
24 'LTS',
fecda0f466e6 adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents: 36
diff changeset
25 'Deprecated'
fecda0f466e6 adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents: 36
diff changeset
26 );
fecda0f466e6 adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents: 36
diff changeset
27
fecda0f466e6 adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents: 36
diff changeset
28 create table lpit_version (
fecda0f466e6 adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents: 36
diff changeset
29 id serial primary key,
fecda0f466e6 adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents: 36
diff changeset
30 project integer not null references lpit_project(id),
fecda0f466e6 adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents: 36
diff changeset
31 name varchar(20) not null,
fecda0f466e6 adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents: 36
diff changeset
32 ordinal integer not null default 0,
fecda0f466e6 adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents: 36
diff changeset
33 status version_status not null default 'Future'
fecda0f466e6 adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents: 36
diff changeset
34 );

mercurial