changeset
#133 changes all varchar columns to text
Mon, 02 Aug 2021 15:13:04 +0200
- author
- Mike Becker <universe@uap-core.de>
- date
- Mon, 02 Aug 2021 15:13:04 +0200
- changeset 206
- fe4de34822a5
- parent 205
- 7725a79416f3
- child 207
- 479dd7993ef9
#133 changes all varchar columns to text
| setup/postgres/psql_create_tables.sql | file | annotate | diff | comparison | revisions |
1.1 --- a/setup/postgres/psql_create_tables.sql Sun Aug 01 18:56:28 2021 +0200 1.2 +++ b/setup/postgres/psql_create_tables.sql Mon Aug 02 15:13:04 2021 +0200 1.3 @@ -1,22 +1,24 @@ 1.4 -- This script creates the module management tables 1.5 -- 1.6 1.7 -create table lpit_user ( 1.8 - userid serial primary key, 1.9 - username varchar(50) not null unique, 1.10 - mail varchar(50), 1.11 - lastname varchar(50), 1.12 - givenname varchar(50) 1.13 +create table lpit_user 1.14 +( 1.15 + userid serial primary key, 1.16 + username text not null unique, 1.17 + mail text, 1.18 + lastname text, 1.19 + givenname text 1.20 ); 1.21 1.22 -create table lpit_project ( 1.23 - projectid serial primary key, 1.24 - name varchar(50) not null unique, 1.25 - node varchar(20) not null unique, 1.26 - ordinal integer not null default 0, 1.27 - description varchar(200), 1.28 - repoUrl varchar(50), 1.29 - owner integer references lpit_user(userid) 1.30 +create table lpit_project 1.31 +( 1.32 + projectid serial primary key, 1.33 + name text not null unique, 1.34 + node text not null unique, 1.35 + ordinal integer not null default 0, 1.36 + description text, 1.37 + repoUrl text, 1.38 + owner integer references lpit_user (userid) 1.39 ); 1.40 1.41 create type version_status as enum ( 1.42 @@ -25,31 +27,33 @@ 1.43 'Released', 1.44 'LTS', 1.45 'Deprecated' 1.46 + ); 1.47 + 1.48 +create table lpit_version 1.49 +( 1.50 + versionid serial primary key, 1.51 + project integer not null references lpit_project (projectid), 1.52 + name text not null, 1.53 + node text not null, 1.54 + ordinal integer not null default 0, 1.55 + status version_status not null default 'Future' 1.56 ); 1.57 1.58 -create table lpit_version ( 1.59 - versionid serial primary key, 1.60 - project integer not null references lpit_project(projectid), 1.61 - name varchar(20) not null, 1.62 - node varchar(20) not null, 1.63 - ordinal integer not null default 0, 1.64 - status version_status not null default 'Future' 1.65 +create unique index lpit_version_node_unique on lpit_version (project, node); 1.66 + 1.67 +create table lpit_component 1.68 +( 1.69 + id serial primary key, 1.70 + project integer not null references lpit_project (projectid), 1.71 + name text not null, 1.72 + node text not null, 1.73 + color char(6) not null default '000000', 1.74 + ordinal integer not null default 0, 1.75 + description text, 1.76 + lead integer references lpit_user (userid) 1.77 ); 1.78 1.79 -create unique index lpit_version_node_unique on lpit_version(project, node); 1.80 - 1.81 -create table lpit_component ( 1.82 - id serial primary key, 1.83 - project integer not null references lpit_project(projectid), 1.84 - name varchar(20) not null, 1.85 - node varchar(20) not null, 1.86 - color char(6) not null default '000000', 1.87 - ordinal integer not null default 0, 1.88 - description text, 1.89 - lead integer references lpit_user(userid) 1.90 -); 1.91 - 1.92 -create unique index lpit_component_node_unique on lpit_component(project, node); 1.93 +create unique index lpit_component_node_unique on lpit_component (project, node); 1.94 1.95 create type issue_status as enum ( 1.96 'InSpecification', 1.97 @@ -61,7 +65,7 @@ 1.98 'Rejected', 1.99 'Withdrawn', 1.100 'Duplicate' 1.101 -); 1.102 + ); 1.103 1.104 create type issue_category as enum ( 1.105 'Feature', 1.106 @@ -69,45 +73,50 @@ 1.107 'Bug', 1.108 'Task', 1.109 'Test' 1.110 + ); 1.111 + 1.112 +create table lpit_issue_phases 1.113 +( 1.114 + status issue_status primary key, 1.115 + phase integer not null 1.116 ); 1.117 1.118 -create table lpit_issue_phases ( 1.119 - status issue_status primary key, 1.120 - phase integer not null 1.121 +create table lpit_issue 1.122 +( 1.123 + issueid serial primary key, 1.124 + project integer not null references lpit_project (projectid), 1.125 + component integer references lpit_component (id), 1.126 + status issue_status not null default 'InSpecification', 1.127 + category issue_category not null default 'Feature', 1.128 + subject text not null, 1.129 + description text, 1.130 + assignee integer references lpit_user (userid), 1.131 + created timestamp with time zone not null default now(), 1.132 + updated timestamp with time zone not null default now(), 1.133 + eta date 1.134 ); 1.135 1.136 -create table lpit_issue ( 1.137 - issueid serial primary key, 1.138 - project integer not null references lpit_project(projectid), 1.139 - component integer references lpit_component(id), 1.140 - status issue_status not null default 'InSpecification', 1.141 - category issue_category not null default 'Feature', 1.142 - subject varchar(200) not null, 1.143 - description text, 1.144 - assignee integer references lpit_user(userid), 1.145 - created timestamp with time zone not null default now(), 1.146 - updated timestamp with time zone not null default now(), 1.147 - eta date 1.148 -); 1.149 - 1.150 -create table lpit_issue_affected_version ( 1.151 - issueid integer references lpit_issue(issueid), 1.152 - versionid integer references lpit_version(versionid), 1.153 +create table lpit_issue_affected_version 1.154 +( 1.155 + issueid integer references lpit_issue (issueid), 1.156 + versionid integer references lpit_version (versionid), 1.157 primary key (issueid, versionid) 1.158 ); 1.159 1.160 -create table lpit_issue_resolved_version ( 1.161 - issueid integer references lpit_issue(issueid), 1.162 - versionid integer references lpit_version(versionid), 1.163 +create table lpit_issue_resolved_version 1.164 +( 1.165 + issueid integer references lpit_issue (issueid), 1.166 + versionid integer references lpit_version (versionid), 1.167 primary key (issueid, versionid) 1.168 ); 1.169 1.170 -create table lpit_issue_comment ( 1.171 - commentid serial primary key, 1.172 - issueid integer not null references lpit_issue(issueid), 1.173 - userid integer references lpit_user(userid), 1.174 - created timestamp with time zone not null default now(), 1.175 - updated timestamp with time zone not null default now(), 1.176 - updatecount integer not null default 0, 1.177 - comment text not null 1.178 +create table lpit_issue_comment 1.179 +( 1.180 + commentid serial primary key, 1.181 + issueid integer not null references lpit_issue (issueid), 1.182 + userid integer references lpit_user (userid), 1.183 + created timestamp with time zone not null default now(), 1.184 + updated timestamp with time zone not null default now(), 1.185 + updatecount integer not null default 0, 1.186 + comment text not null 1.187 );
