# HG changeset patch # User Mike Becker # Date 1627909984 -7200 # Node ID fe4de34822a50b914a80771d02908b1392ad715c # Parent 7725a79416f32d927c22d9e702789ecd56581e48 #133 changes all varchar columns to text diff -r 7725a79416f3 -r fe4de34822a5 setup/postgres/psql_create_tables.sql --- a/setup/postgres/psql_create_tables.sql Sun Aug 01 18:56:28 2021 +0200 +++ b/setup/postgres/psql_create_tables.sql Mon Aug 02 15:13:04 2021 +0200 @@ -1,22 +1,24 @@ -- 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_user +( + userid serial primary key, + username text not null unique, + mail text, + lastname text, + givenname text ); -create table lpit_project ( - projectid serial primary key, - name varchar(50) not null unique, - node varchar(20) not null unique, - ordinal integer not null default 0, - description varchar(200), - repoUrl varchar(50), - owner integer references lpit_user(userid) +create table lpit_project +( + projectid serial primary key, + name text not null unique, + node text not null unique, + ordinal integer not null default 0, + description text, + repoUrl text, + owner integer references lpit_user (userid) ); create type version_status as enum ( @@ -25,31 +27,33 @@ 'Released', 'LTS', 'Deprecated' -); + ); -create table lpit_version ( - versionid serial primary key, - project integer not null references lpit_project(projectid), - name varchar(20) not null, - node varchar(20) not null, - ordinal integer not null default 0, - status version_status not null default 'Future' +create table lpit_version +( + versionid serial primary key, + project integer not null references lpit_project (projectid), + name text not null, + node text not null, + ordinal integer not null default 0, + status version_status not null default 'Future' ); -create unique index lpit_version_node_unique on lpit_version(project, node); +create unique index lpit_version_node_unique on lpit_version (project, node); -create table lpit_component ( - id serial primary key, - project integer not null references lpit_project(projectid), - name varchar(20) not null, - node varchar(20) not null, - color char(6) not null default '000000', - ordinal integer not null default 0, - description text, - lead integer references lpit_user(userid) +create table lpit_component +( + id serial primary key, + project integer not null references lpit_project (projectid), + name text not null, + node text not null, + color char(6) not null default '000000', + ordinal integer not null default 0, + description text, + lead integer references lpit_user (userid) ); -create unique index lpit_component_node_unique on lpit_component(project, node); +create unique index lpit_component_node_unique on lpit_component (project, node); create type issue_status as enum ( 'InSpecification', @@ -61,7 +65,7 @@ 'Rejected', 'Withdrawn', 'Duplicate' -); + ); create type issue_category as enum ( 'Feature', @@ -69,45 +73,50 @@ 'Bug', 'Task', 'Test' -); + ); -create table lpit_issue_phases ( - status issue_status primary key, - phase integer not null +create table lpit_issue_phases +( + status issue_status primary key, + phase integer not null ); -create table lpit_issue ( - issueid serial primary key, - project integer not null references lpit_project(projectid), - component integer references lpit_component(id), - status issue_status not null default 'InSpecification', - category issue_category not null default 'Feature', - subject varchar(200) not null, - description text, - assignee integer references lpit_user(userid), - created timestamp with time zone not null default now(), - updated timestamp with time zone not null default now(), - eta date +create table lpit_issue +( + issueid serial primary key, + project integer not null references lpit_project (projectid), + component integer references lpit_component (id), + status issue_status not null default 'InSpecification', + category issue_category not null default 'Feature', + subject text not null, + description text, + assignee integer references lpit_user (userid), + created timestamp with time zone not null default now(), + updated timestamp with time zone not null default now(), + eta date ); -create table lpit_issue_affected_version ( - issueid integer references lpit_issue(issueid), - versionid integer references lpit_version(versionid), +create table lpit_issue_affected_version +( + issueid integer references lpit_issue (issueid), + versionid integer references lpit_version (versionid), primary key (issueid, versionid) ); -create table lpit_issue_resolved_version ( - issueid integer references lpit_issue(issueid), - versionid integer references lpit_version(versionid), +create table lpit_issue_resolved_version +( + issueid integer references lpit_issue (issueid), + versionid integer references lpit_version (versionid), primary key (issueid, versionid) ); -create table lpit_issue_comment ( - commentid serial primary key, - issueid integer not null references lpit_issue(issueid), - userid integer references lpit_user(userid), - created timestamp with time zone not null default now(), - updated timestamp with time zone not null default now(), - updatecount integer not null default 0, - comment text not null +create table lpit_issue_comment +( + commentid serial primary key, + issueid integer not null references lpit_issue (issueid), + userid integer references lpit_user (userid), + created timestamp with time zone not null default now(), + updated timestamp with time zone not null default now(), + updatecount integer not null default 0, + comment text not null );