1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
|
/* initial (server preparatory) statements:
create user codeq with password 'c0d3q';
create user vladar with password 'vl4d4r' superuser createdb createrole;
* to create the database (logged in as vladar):
create database codeq with encoding 'UTF8' template template0;
\c codeq
\i create.sql
grant all privileges on all tables in schema public 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,
name varchar(200),
email varchar(254) not null,
is_admin bool not null,
is_active bool not null,
date_joined timestamp not null,
last_login timestamp not null,
gui_lang varchar(2),
robot_address varchar(30),
gui_layout varchar(30),
constraint codeq_user_pk primary key (id),
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'),
identifier varchar(30) not null,
constraint language_pk primary key (id),
constraint language_uq2 unique (identifier)
);
create table problem_group (
id integer not null default nextval('problem_group_id_seq'),
identifier varchar(100) not null,
constraint group_pk primary key (id),
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,
identifier varchar(100) not null,
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_uq2 unique (language_id, problem_group_id, identifier)
);
create table solution (
id integer not null default nextval('solution_id_seq'),
done bool not null,
content text not null,
problem_id integer not null,
codeq_user_id integer not null,
trace jsonb not null default '[]'::jsonb,
constraint solution_pk primary key (id),
constraint solution_fk1 foreign key (codeq_user_id) references codeq_user (id) on delete cascade on update cascade
);
|
