diff options
Diffstat (limited to 'db')
-rw-r--r-- | db/create.sql | 58 |
1 files changed, 52 insertions, 6 deletions
diff --git a/db/create.sql b/db/create.sql index 5b3fc8c..39eab20 100644 --- a/db/create.sql +++ b/db/create.sql @@ -6,22 +6,30 @@ \c codeq \i create.sql grant all privileges on all tables in schema public to codeq; - grant all privileges on codeq_user_id_seq to codeq; - grant all privileges on solution_id_seq to codeq; + grant all privileges on all sequences in schema public to codeq; */ +create sequence user_group_id_seq; create sequence codeq_user_id_seq; +create sequence language_id_seq; +create sequence problem_group_id_seq; +create sequence problem_id_seq; create sequence solution_id_seq; +create table user_group ( + id integer not null default nextval('user_group_id_seq'), + name varchar(80) not null, + constraint user_group_pk primary key (id), + constraint user_group_uq1 unique (name) +); + create table codeq_user ( id integer not null default nextval('codeq_user_id_seq'), username varchar(30) not null, password varchar(128) not null, - first_name varchar(30) not null, - last_name varchar(30) not null, + name varchar(200), email varchar(254) not null, - is_superuser bool not null, - is_staff bool not null, + is_admin bool not null, is_active bool not null, date_joined timestamp not null, last_login timestamp not null, @@ -29,6 +37,44 @@ create table codeq_user ( constraint codeq_user_uq1 unique (username) ); +create table user_in_group ( + user_group_id integer not null, + codeq_user_id integer not null, + constraint user_in_group_pk primary key (user_group_id, codeq_user_id) +); + +create table language ( + id integer not null default nextval('language_id_seq'), + name varchar(30) not null, + identifier varchar(30) not null, + constraint language_pk primary key (id), + constraint language_uq1 unique (name), + constraint language_uq2 unique (identifier) +); + +create table problem_group ( + id integer not null default nextval('problem_group_id_seq'), + name varchar(100) not null, + identifier varchar(100) not null, + constraint group_pk primary key (id), + constraint group_uq1 unique (name), + constraint group_uq2 unique (identifier) +); + +create table problem ( + id integer not null default nextval('problem_id_seq'), + language_id integer not null, + problem_group_id integer not null, + name varchar(100) not null, + identifier varchar(100) not null, + is_visible bool not null default true, + constraint problem_pk primary key (id), + constraint problem_fk1 foreign key (language_id) references language (id) on delete no action on update cascade, + constraint problem_fk2 foreign key (problem_group_id) references problem_group (id) on delete no action on update cascade, + constraint problem_uq1 unique (language_id, problem_group_id, name), + constraint problem_uq2 unique (identifier) +); + create table solution ( id integer not null default nextval('solution_id_seq'), done bool not null, |