RarBFBMN

1CREATE TABLE IF NOT EXISTS users ( 
2  user_id serial8 not null,
3  name varchar not null,
4  type varchar not null,
5  competence varchar not null,
6  login varchar not null,
7  password varchar not null,
8  personal_data json,
9  PRIMARY KEY (user_id)
10) WITHOUT OIDS;
11
12CREATE TABLE IF NOT EXISTS documents ( 
13  document_id varchar not null,
14  title varchar not null,
15  case_id int8 not null,
16  PRIMARY KEY (document_id)
17) WITHOUT OIDS;
18
19ALTER TABLE documents ADD CONSTRAINT documents_case_id_foreign FOREIGN KEY (case_id) REFERENCES cases (case_id) ;
20
21CREATE TABLE IF NOT EXISTS cases ( 
22  case_id serial8 not null,
23  category varchar not null,
24  title varchar not null,
25  description varchar not null,
26  s_id int8,
27  t_id int8,
28  status varchar not null default ожидает назначения ответственных,
29  supplicant_id int8 not null,
30  dispatcher_id int8 not null,
31  last_update timestamp not null,
32  PRIMARY KEY (case_id)
33) WITHOUT OIDS;
34
35ALTER TABLE cases ADD CONSTRAINT cases_s_id_foreign FOREIGN KEY (s_id) REFERENCES users (user_id) ;
36ALTER TABLE cases ADD CONSTRAINT cases_t_id_foreign FOREIGN KEY (t_id) REFERENCES users (user_id) ;
37ALTER TABLE cases ADD CONSTRAINT cases_supplicant_id_foreign FOREIGN KEY (supplicant_id) REFERENCES supplicants (supplicant_id) ;
38ALTER TABLE cases ADD CONSTRAINT cases_dispatcher_id_foreign FOREIGN KEY (dispatcher_id) REFERENCES users (user_id) ;
39
40CREATE TABLE IF NOT EXISTS points_feed ( 
41  event_id serial8 not null,
42  points decimal not null default 0.0,
43  s_id int8 not null,
44  t_id int8 not null,
45  reason varchar not null,
46  date_time timestamp not null,
47  PRIMARY KEY (event_id)
48) WITHOUT OIDS;
49
50ALTER TABLE points_feed ADD CONSTRAINT points_feed_s_id_foreign FOREIGN KEY (s_id) REFERENCES users (user_id) ;
51ALTER TABLE points_feed ADD CONSTRAINT points_feed_t_id_foreign FOREIGN KEY (t_id) REFERENCES users (user_id) ;
52
53CREATE TABLE IF NOT EXISTS duty_feed ( 
54  duty_id serial8 not null,
55  s_id int8 not null,
56  date date not null,
57  status varchar not null default false,
58  PRIMARY KEY (duty_id)
59) WITHOUT OIDS;
60
61ALTER TABLE duty_feed ADD CONSTRAINT duty_feed_s_id_foreign FOREIGN KEY (s_id) REFERENCES users (user_id) ;
62
63CREATE TABLE IF NOT EXISTS supplicants ( 
64  supplicant_id serial8 not null,
65  name varchar not null,
66  telephone_number varchar not null,
67  PRIMARY KEY (supplicant_id)
68) WITHOUT OIDS;