· 6 years ago · Dec 18, 2019, 06:06 PM
1CREATE TABLE IF NOT EXISTS supplicants (
2 supplicant_id int8 not null,
3 name varchar,
4 address varchar,
5 telephone_number varchar,
6 email varchar,
7 PRIMARY KEY (supplicant_id)
8) WITHOUT OIDS;
9
10CREATE TABLE IF NOT EXISTS cases (
11 case_id int8 not null,
12 category varchar,
13 description text,
14 supplicant_id int8,
15 case_status varchar not null,
16 operator_id int8,
17 industry_affiliation varchar,
18 source_of_knowledge_jc varchar,
19 application_date_time timestamp,
20 meeting_date_time timestamp,
21 done bool not null,
22 PRIMARY KEY (case_id)
23) WITHOUT OIDS;
24
25ALTER TABLE cases ADD CONSTRAINT cases_supplicant_id_foreign FOREIGN KEY (supplicant_id) REFERENCES supplicants (supplicant_id) ;
26
27CREATE TABLE IF NOT EXISTS documents (
28 document_id int8 not null,
29 title text,
30 case_id int8 not null,
31 type varchar,
32 data_id int8,
33 PRIMARY KEY (document_id)
34) WITHOUT OIDS;
35
36ALTER TABLE documents ADD CONSTRAINT documents_case_id_foreign FOREIGN KEY (case_id) REFERENCES cases (case_id) ;
37
38CREATE TABLE IF NOT EXISTS site_users (
39 user_id int8 not null,
40 name varchar,
41 role int8,
42 st_login varchar not null,
43 personal_data text,
44 password_hash varchar,
45 cookie text,
46 PRIMARY KEY (user_id)
47) WITHOUT OIDS;
48
49CREATE TABLE IF NOT EXISTS applications (
50 application_id int8 not null,
51 title varchar,
52 case_id int8 not null,
53 description text,
54 addressee_user_id int8 not null,
55 status varchar not null,
56 date_time timestamp,
57 PRIMARY KEY (application_id)
58) WITHOUT OIDS;
59
60ALTER TABLE applications ADD CONSTRAINT applications_addressee_user_id_foreign FOREIGN KEY (addressee_user_id) REFERENCES site_users (user_id) ;
61ALTER TABLE applications ADD CONSTRAINT applications_case_id_foreign FOREIGN KEY (case_id) REFERENCES cases (case_id) ;
62
63CREATE TABLE IF NOT EXISTS administration (
64 admin_id int8 not null,
65 name varchar not null,
66 type varchar,
67 email varchar,
68 user_id int8 not null,
69 PRIMARY KEY (admin_id)
70) WITHOUT OIDS;
71
72ALTER TABLE administration ADD CONSTRAINT administration_user_id_foreign FOREIGN KEY (user_id) REFERENCES site_users (user_id) ;
73
74CREATE TABLE IF NOT EXISTS notifications (
75 notification_id int8 not null,
76 text text not null,
77 status varchar not null,
78 user_id int8 not null,
79 date_time timestamp,
80 event_data json,
81 PRIMARY KEY (notification_id)
82) WITHOUT OIDS;
83
84ALTER TABLE notifications ADD CONSTRAINT notifications_user_id_foreign FOREIGN KEY (user_id) REFERENCES site_users (user_id) ;
85
86CREATE TABLE IF NOT EXISTS teachers (
87 teacher_id int8 not null,
88 competence varchar,
89 user_id int8 not null,
90 PRIMARY KEY (teacher_id)
91) WITHOUT OIDS;
92
93ALTER TABLE teachers ADD CONSTRAINT teachers_user_id_foreign FOREIGN KEY (user_id) REFERENCES site_users (user_id) ;
94
95CREATE TABLE IF NOT EXISTS case_teachers_relations (
96 relation_id int8 not null,
97 case_id int8 not null,
98 teacher_id int8 not null,
99 role varchar,
100 PRIMARY KEY (relation_id)
101) WITHOUT OIDS;
102
103ALTER TABLE case_teachers_relations ADD CONSTRAINT case_teachers_relations_case_id_foreign FOREIGN KEY (case_id) REFERENCES cases (case_id) ;
104ALTER TABLE case_teachers_relations ADD CONSTRAINT case_teachers_relations_teacher_id _foreign FOREIGN KEY (teacher_id) REFERENCES teachers (teacher_id) ;
105
106CREATE TABLE IF NOT EXISTS students (
107 student_id int8 not null,
108 teacher_id int8,
109 functional_group varchar,
110 points decimal,
111 user_id int8 not null,
112 subgroup_name varchar,
113 PRIMARY KEY (student_id)
114) WITHOUT OIDS;
115
116ALTER TABLE students ADD CONSTRAINT students_user_id _foreign FOREIGN KEY (user_id) REFERENCES site_users (user_id) ;
117ALTER TABLE students ADD CONSTRAINT students_teacher_id_foreign FOREIGN KEY (teacher_id) REFERENCES teachers (teacher_id) ;
118ALTER TABLE students ADD CONSTRAINT students_subgroup_name _foreign FOREIGN KEY (subgroup_name) REFERENCES students_subgroup (name) ;
119
120CREATE TABLE IF NOT EXISTS students_subgroup (
121 name varchar not null,
122 admin_id int8 not null,
123 PRIMARY KEY (name)
124) WITHOUT OIDS;
125
126ALTER TABLE students_subgroup ADD CONSTRAINT students_subgroup_admin_id _foreign FOREIGN KEY (admin_id) REFERENCES students (student_id) ;
127
128CREATE TABLE IF NOT EXISTS case_student_relations (
129 relation_id int8 not null,
130 case_id int8 not null,
131 student_id int8 not null,
132 role varchar,
133 PRIMARY KEY (relation_id)
134) WITHOUT OIDS;
135
136ALTER TABLE case_student_relations ADD CONSTRAINT case_student_relations_case_id _foreign FOREIGN KEY (case_id) REFERENCES cases (case_id) ;
137ALTER TABLE case_student_relations ADD CONSTRAINT case_student_relations_student_id _foreign FOREIGN KEY (student_id) REFERENCES students (student_id) ;
138
139CREATE TABLE IF NOT EXISTS rejected_requests (
140 request_id int8 not null,
141 reason text,
142 operator_id int8,
143 supplicant_name varchar,
144 request_text text,
145 date_time timestamp,
146 PRIMARY KEY (request_id)
147) WITHOUT OIDS;
148
149ALTER TABLE rejected_requests ADD CONSTRAINT rejected_requests_operator_id _foreign FOREIGN KEY (operator_id) REFERENCES students (student_id) ;
150
151CREATE TABLE IF NOT EXISTS points_feed (
152 event_id int8 not null,
153 student_id int8 not null,
154 teacher_id int8 not null,
155 points decimal not null,
156 reason text,
157 date_time timestamp,
158 PRIMARY KEY (event_id)
159) WITHOUT OIDS;
160
161ALTER TABLE points_feed ADD CONSTRAINT points_feed_student_id _foreign FOREIGN KEY (student_id) REFERENCES students (student_id) ;
162ALTER TABLE points_feed ADD CONSTRAINT points_feed_teacher_id _foreign FOREIGN KEY (teacher_id) REFERENCES teachers (teacher_id) ;
163
164CREATE TABLE IF NOT EXISTS cases_feed (
165 event_id int8 not null,
166 case_id int8 not null,
167 case_status varchar not null,
168 date_time timestamp,
169 event_data json,
170 done bool not null,
171 PRIMARY KEY (event_id)
172) WITHOUT OIDS;
173
174ALTER TABLE cases_feed ADD CONSTRAINT cases_feed_case_id _foreign FOREIGN KEY (case_id) REFERENCES cases (case_id) ;
175
176CREATE TABLE IF NOT EXISTS duty_roster (
177 duty_id int8 not null,
178 student_id int8 not null,
179 class_room varchar,
180 start_date_time timestamp,
181 finish_date_time timestamp,
182 PRIMARY KEY (duty_id)
183) WITHOUT OIDS;
184
185ALTER TABLE duty_roster ADD CONSTRAINT duty_roster_student_id _foreign FOREIGN KEY (student_id) REFERENCES students (student_id) ;
186
187CREATE TABLE IF NOT EXISTS users (
188 user_id serial8 not null,
189 name varchar not null,
190 type varchar not null,
191 competence varchar not null,
192 login varchar not null,
193 password varchar not null,
194 personal_data json,
195 PRIMARY KEY (user_id)
196) WITHOUT OIDS;
197
198CREATE TABLE IF NOT EXISTS documents (
199 document_id varchar not null,
200 title varchar not null,
201 case_id int8 not null,
202 PRIMARY KEY (document_id)
203) WITHOUT OIDS;
204
205ALTER TABLE documents ADD CONSTRAINT documents_case_id_foreign FOREIGN KEY (case_id) REFERENCES cases (case_id) ;
206
207CREATE TABLE IF NOT EXISTS cases (
208 case_id serial8 not null,
209 category varchar not null,
210 title varchar not null,
211 description varchar not null,
212 s_id int8,
213 t_id int8,
214 status varchar not null default ожидает назначения ответственных,
215 supplicant_id int8 not null,
216 dispatcher_id int8 not null,
217 last_update timestamp not null,
218 PRIMARY KEY (case_id)
219) WITHOUT OIDS;
220
221ALTER TABLE cases ADD CONSTRAINT cases_s_id_foreign FOREIGN KEY (s_id) REFERENCES users (user_id) ;
222ALTER TABLE cases ADD CONSTRAINT cases_t_id_foreign FOREIGN KEY (t_id) REFERENCES users (user_id) ;
223ALTER TABLE cases ADD CONSTRAINT cases_supplicant_id_foreign FOREIGN KEY (supplicant_id) REFERENCES supplicants (supplicant_id) ;
224ALTER TABLE cases ADD CONSTRAINT cases_dispatcher_id_foreign FOREIGN KEY (dispatcher_id) REFERENCES users (user_id) ;
225
226CREATE TABLE IF NOT EXISTS points_feed (
227 event_id serial8 not null,
228 points decimal not null default 0.0,
229 s_id int8 not null,
230 t_id int8 not null,
231 reason varchar not null,
232 date_time timestamp not null,
233 PRIMARY KEY (event_id)
234) WITHOUT OIDS;
235
236ALTER TABLE points_feed ADD CONSTRAINT points_feed_s_id_foreign FOREIGN KEY (s_id) REFERENCES users (user_id) ;
237ALTER TABLE points_feed ADD CONSTRAINT points_feed_t_id_foreign FOREIGN KEY (t_id) REFERENCES users (user_id) ;
238
239CREATE TABLE IF NOT EXISTS duty_feed (
240 duty_id serial8 not null,
241 s_id int8 not null,
242 date date not null,
243 status varchar not null default false,
244 PRIMARY KEY (duty_id)
245) WITHOUT OIDS;
246
247ALTER TABLE duty_feed ADD CONSTRAINT duty_feed_s_id_foreign FOREIGN KEY (s_id) REFERENCES users (user_id) ;
248
249CREATE TABLE IF NOT EXISTS supplicants (
250 supplicant_id serial8 not null,
251 name varchar not null,
252 telephone_number varchar not null,
253 PRIMARY KEY (supplicant_id)
254) WITHOUT OIDS;