· 5 years ago · May 02, 2020, 04:10 AM
1package edu.wpi.cs3733.d20.teamL.services.db;
2
3import java.util.ArrayList;
4import java.util.Arrays;
5
6public class DBConstants {
7 static final String DB_DRIVER = "com.mysql.cj.jdbc.Driver";
8 static final String DB_PREFIX = "jdbc:mysql://";
9 static final String DB_URL = "cs3733-bwh-db.cqqsqwjmcbj4.us-east-2.rds.amazonaws.com";
10 static final String DB_PORT = ":5008";
11 static final String DB_NAME_DEV = "/bwh_dev";
12 static final String DB_NAME_PROD = "/bwh_prod";
13 static final String DB_NAME_CANARY = "/bwh_canary";
14 static final String DB_USER = "teaml";
15 static final String DB_PASSWORD = "linenleviathans";
16 public static final String SERVICE_NAME = "mysql-db-01";
17
18 public static ArrayList<String> GET_TABLE_NAMES() {
19 return new ArrayList<>(Arrays.asList("Nodes", "Edges", "Users", "Doctors", "Patients", "Gifts", "Gift_Delivery_Requests", "Medication_Requests", "Service_Requests"));
20 }
21
22 public static final String CREATE_NODE_TABLE =
23 "CREATE TABLE Nodes(" +
24 "id VARCHAR(16) NOT NULL PRIMARY KEY, " +
25 "x_pos DOUBLE NOT NULL, " +
26 "y_pos DOUBLE NOT NULL, " +
27 "floor CHAR(1) NOT NULL, " +
28 "building VARCHAR(64) NOT NULL, " +
29 "node_type CHAR(4) NOT NULL, " +
30 "l_name VARCHAR(64) NOT NULL, " +
31 "s_name VARCHAR(32) NOT NULL)";
32
33 public static final String CREATE_EDGE_TABLE =
34 "CREATE TABLE Edges(" +
35 "id VARCHAR(21) NOT NULL PRIMARY KEY, " +
36 "node_start VARCHAR(16) NOT NULL REFERENCES Nodes(id), " +
37 "node_end VARCHAR(16) NOT NULL REFERENCES Nodes(id))";
38
39 public static final String CREATE_USER_TABLE =
40 "CREATE TABLE Users(" +
41 "id INT NOT NULL AUTO_INCREMENT, " +
42 "f_name VARCHAR(32) NOT NULL, " +
43 "l_name VARCHAR(32) NOT NULL, " +
44 "username VARCHAR(32) NOT NULL PRIMARY KEY, " +
45 "password VARCHAR(60) NOT NULL, " +
46 // 0: Staff member, 1: Nurse, 2: Doctor, 3: Admin
47 "acct_type CHAR(1) NOT NULL, " +
48 "services VARCHAR(512), " +
49 "manager VARCHAR(32), " +
50 "INDEX(id))";
51
52 public static final String CREATE_DOCTOR_TABLE =
53 "CREATE TABLE Doctors(" +
54 "id INT NOT NULL PRIMARY KEY, " +
55 "f_name VARCHAR(32) NOT NULL, " +
56 "l_name VARCHAR(32) NOT NULL, " +
57 "username VARCHAR(32) REFERENCES Users(username), " +
58 "office_id VARCHAR(16) REFERENCES Nodes(id), " +
59 "addl_info VARCHAR(256))";
60
61 public static final String CREATE_PATIENT_TABLE =
62 "CREATE TABLE Patients(" +
63 "id INT NOT NULL PRIMARY KEY, " +
64 "f_name VARCHAR(32) NOT NULL, " +
65 "l_name VARCHAR(32) NOT NULL, " +
66 "doctor_id INT REFERENCES Doctors(id), " +
67 "room_id VARCHAR(16) REFERENCES Nodes(id), " +
68 "addl_info VARCHAR(256))";
69
70 public static final String CREATE_GIFT_TABLE =
71 "CREATE TABLE Gifts(" +
72 "id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, " +
73 "type VARCHAR(16) NOT NULL, " +
74 "subtype VARCHAR(16) NOT NULL, " +
75 "description VARCHAR(128) NOT NULL, " +
76 "inventory INT NOT NULL)";
77
78 public static final String CREATE_GIFT_DELIVERY_REQUEST_TABLE =
79 "CREATE TABLE Gift_Delivery_Requests(" +
80 "id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, " +
81 "patient_id INT NOT NULL REFERENCES Patients(id), " +
82 "sender_name VARCHAR(32) NOT NULL, " +
83 "request_username VARCHAR(32) NOT NULL REFERENCES Users(username), " +
84 "assignee_username VARCHAR(32) REFERENCES Users(username), " +
85 "gift1_id INT NOT NULL REFERENCES Gifts(id), " +
86 "gift2_id INT REFERENCES Gifts(id), " +
87 "gift3_id INT REFERENCES Gifts(id), " +
88 "message VARCHAR(128), " +
89 "notes VARCHAR(256), " +
90 // 0: Pending, 1: Approved, 2: Assigned, 3: Denied, 4: Completed
91 "status CHAR(1) NOT NULL, " +
92 "date_and_time CHAR(19) NOT NULL)";
93
94 public static final String CREATE_MEDICATION_REQUEST_TABLE =
95 "CREATE TABLE Medication_Requests(" +
96 "id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, " +
97 "patient_id INT NOT NULL REFERENCES Patients(id), " +
98 "doctor_id INT NOT NULL REFERENCES Doctors(id), " +
99 "nurse_username VARCHAR(32) NOT NULL REFERENCES Users(username), " +
100 "deliverer_username VARCHAR(32) REFERENCES Users(username), " +
101 "dose VARCHAR(64) NOT NULL, " +
102 "type VARCHAR(64) NOT NULL, " +
103 "notes VARCHAR(256), " +
104 // 0: Pending, 1: Approved, 2: Assigned, 3: Denied, 4: Completed
105 "status CHAR(1) NOT NULL, " +
106 "date_and_time CHAR(19) NOT NULL)";
107
108 public static final String CREATE_SERVICE_REQUEST_TABLE =
109 "CREATE TABLE Service_Requests(" +
110 "id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, " +
111 "patient_id INT REFERENCES Patients(id), " +
112 "request_username VARCHAR(32) REFERENCES Users(username), " +
113 "assignee_username VARCHAR(32) REFERENCES Users(username), " +
114 "location VARCHAR(16) REFERENCES Nodes(id), " +
115 "service VARCHAR(64) NOT NULL, " +
116 "type VARCHAR(64), " +
117 "notes VARCHAR(256), " +
118 // 0: Pending, 1: Approved, 2: Assigned, 3: Denied, 4: Completed
119 "status CHAR(1) NOT NULL, " +
120 "date_and_time CHAR(19) NOT NULL)";
121
122 public static final String DROP_NODE_TABLE =
123 "DROP TABLE IF EXISTS Nodes";
124
125 public static final String DROP_EDGE_TABLE =
126 "DROP TABLE IF EXISTS Edges";
127
128 public static final String DROP_USER_TABLE =
129 "DROP TABLE IF EXISTS Users";
130
131 public static final String DROP_DOCTOR_TABLE =
132 "DROP TABLE IF EXISTS Doctors";
133
134 public static final String DROP_PATIENT_TABLE =
135 "DROP TABLE IF EXISTS Patients";
136
137 public static final String DROP_GIFT_TABLE =
138 "DROP TABLE IF EXISTS Gifts";
139
140 public static final String DROP_GIFT_DELIVER_REQUEST_TABLE =
141 "DROP TABLE IF EXISTS Gift_Delivery_Requests";
142
143 public static final String DROP_MEDICATION_REQUEST_TABLE =
144 "DROP TABLE IF EXISTS Medication_Requests";
145
146 public static final String DROP_SERVICE_REQUEST_TABLE =
147 "DROP TABLE IF EXISTS Service_Requests";
148
149 public static final String ADD_NODE =
150 "INSERT INTO Nodes(id, x_pos, y_pos, floor, building, node_type, l_name, s_name)" +
151 "VALUES(?, ?, ?, ?, ?, ?, ?, ?)";
152
153 public static final String ADD_EDGE =
154 "INSERT INTO Edges(id, node_start, node_end)" +
155 "VALUES(?, ?, ?)";
156
157 public static final String ADD_USER =
158 "INSERT INTO Users(f_name, l_name, username, password, acct_type, services, manager)" +
159 "VALUES(?, ?, ?, ?, ?, ?, ?)";
160
161 public static final String ADD_DOCTOR =
162 "INSERT INTO Doctors(id, f_name, l_name, username, office_id, addl_info)" +
163 "VALUES(?, ?, ?, ?, ?, ?)";
164
165 public static final String ADD_PATIENT =
166 "INSERT INTO Patients(id, f_name, l_name, doctor_id, room_id, addl_info)" +
167 "VALUES(?, ?, ?, ?, ?, ?)";
168
169 public static final String ADD_GIFT =
170 "INSERT INTO Gifts(type, subtype, description, inventory)" +
171 "VALUES(?, ?, ?, ?)";
172
173 public static final String ADD_GIFT_DELIVERY_REQUEST =
174 "INSERT INTO Gift_Delivery_Requests(patient_id, sender_name, request_username, assignee_username, gift1_id, gift2_id, gift3_id, message, notes, status, date_and_time)" +
175 "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
176
177 public static final String ADD_MEDICATION_REQUEST =
178 "INSERT INTO Medication_Requests(doctor_id, patient_id, nurse_username, deliverer_username, dose, type, notes, status, date_and_time)" +
179 "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)";
180
181 public static final String ADD_SERVICE_REQUEST =
182 "INSERT INTO Service_Requests(patient_id, request_username, assignee_username, location, service, type, notes, status, date_and_time)" +
183 "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)";
184
185 public static final String SELECT_ALL_NODES =
186 "SELECT * " +
187 "FROM Nodes";
188
189 public static final String SELECT_ALL_EDGES =
190 "SELECT * " +
191 "FROM Edges";
192
193 public static final String SELECT_ALL_USERS =
194 "SELECT id, f_name, l_name, username, acct_type, services, manager " +
195 "FROM Users";
196
197 public static final String GET_USER =
198 "SELECT id, f_name, l_name, username, password, acct_type, services, manager " +
199 "FROM Users " +
200 "WHERE username = ?";
201
202 public static final String GET_USERNAME_BY_NAME =
203 "SELECT username " +
204 "FROM Users " +
205 "WHERE f_name = ? AND l_name = ?";
206
207 public static final String GET_USER_BY_ID =
208 "SELECT id, username, f_name, l_name, acct_type " +
209 "FROM Users " +
210 "WHERE id = ?";
211
212 public static final String GET_NAME_BY_USERNAME =
213 "SELECT f_name, l_name " +
214 "FROM Users " +
215 "WHERE username = ?";
216
217 public static final String SELECT_ALL_DOCTORS =
218 "SELECT * " +
219 "FROM Doctors";
220
221 public static final String GET_DOCTOR_NAME =
222 "SELECT f_name, l_name " +
223 "FROM Doctors " +
224 "WHERE id = ?";
225
226 public static final String GET_DOCTOR_ID_BY_NAME =
227 "SELECT id " +
228 "FROM Doctors " +
229 "WHERE f_name = ? AND l_name = ?";
230
231 public static final String GET_DOCTOR_ID_BY_USERNAME =
232 "SELECT id " +
233 "FROM Doctors " +
234 "WHERE username = ?";
235
236 public static final String SELECT_ALL_PATIENTS =
237 "SELECT * " +
238 "FROM Patients";
239
240 public static final String GET_PATIENT_ID =
241 "SELECT id " +
242 "FROM Patients " +
243 "WHERE f_name = ? AND l_name = ?";
244
245 public static final String GET_PATIENT_NAME =
246 "SELECT f_name, l_name " +
247 "FROM Patients " +
248 "WHERE id = ?";
249
250 public static final String GET_PATIENT_ROOM =
251 "SELECT room_id " +
252 "FROM Patients " +
253 "WHERE id = ?";
254
255 public static final String SELECT_ALL_GIFTS =
256 "SELECT * " +
257 "FROM Gifts";
258
259 public static final String GET_GIFT =
260 "SELECT * " +
261 "FROM Gifts " +
262 "WHERE id = ?";
263
264 public static final String SELECT_ALL_GIFT_DELIVERY_REQUESTS =
265 "SELECT * " +
266 "FROM Gift_Delivery_Requests";
267
268 public static final String SELECT_ALL_GIFT_DELIVERY_REQUESTS_FOR_ASSIGNEE =
269 "SELECT * " +
270 "FROM Gift_Delivery_Requests " +
271 "WHERE assignee_username = ?";
272
273 public static final String SELECT_ALL_MEDICATION_REQUESTS =
274 "SELECT * " +
275 "FROM Medication_Requests";
276
277 public static final String SELECT_ALL_MEDICATION_REQUESTS_FOR_DOCTOR =
278 "SELECT * " +
279 "FROM Medication_Requests " +
280 "WHERE doctor_id = ?";
281
282 public static final String SELECT_ALL_MEDICATION_REQUESTS_FOR_DELIVERER =
283 "SELECT * " +
284 "FROM Medication_Requests " +
285 "WHERE deliverer_username = ?";
286
287 public static final String SELECT_ALL_SERVICE_REQUESTS =
288 "SELECT * " +
289 "FROM Service_Requests";
290
291 public static final String SELECT_ALL_SERVICE_REQUESTS_FOR_ASSIGNEE =
292 "SELECT * " +
293 "FROM Service_Requests " +
294 "WHERE assignee_username = ?";
295
296 public static final String SELECT_ALL_SERVICE_REQUESTS_FOR_MANAGER =
297 "SELECT * " +
298 "FROM Service_Requests " +
299 "WHERE service = ?";
300
301 public static final String UPDATE_NODE =
302 "UPDATE Nodes " +
303 "SET x_pos = ?, y_pos = ?, floor = ?, building = ?, node_type = ?, l_name = ?, s_name = ? " +
304 "WHERE id = ?";
305
306 public static final String UPDATE_EDGE =
307 "UPDATE Edges " +
308 "SET node_start = ?, node_end = ? " +
309 "WHERE id = ?";
310
311 public static final String UPDATE_USER_NAME =
312 "UPDATE Users " +
313 "SET f_name = ?, l_name = ? " +
314 "WHERE id = ?";
315
316 public static final String UPDATE_DOCTOR_USERNAME =
317 "UPDATE Doctors " +
318 "SET username = ?" +
319 "WHERE id = ?";
320
321 public static final String UPDATE_USER_PASSWORD =
322 "UPDATE Users " +
323 "SET password = ? " +
324 "WHERE id = ?";
325
326 public static final String UPDATE_USER_ACCT_TYPE =
327 "UPDATE Users " +
328 "SET acct_type = ? " +
329 "WHERE id = ?";
330
331 public static final String UPDATE_GIFT_DELIVERY_REQUEST =
332 "UPDATE Gift_Delivery_Requests " +
333 "SET patient_id = ?, request_username = ?, assignee_username = ?, gift_id = ?, message = ?, notes = ?, status = ?, date_and_time = ? " +
334 "WHERE id = ?";
335
336 public static final String UPDATE_GIFT_DELIVERY_REQUEST_ASSIGNEE =
337 "UPDATE Gift_Delivery_Requests " +
338 "SET assignee_username = ? " +
339 "WHERE id = ?";
340
341 public static final String UPDATE_GIFT_DELIVERY_REQUEST_STATUS =
342 "UPDATE Gift_Delivery_Requests " +
343 "SET status = ? " +
344 "WHERE id = ?";
345
346 public static final String UPDATE_GIFT_DELIVERY_REQUEST_NOTES =
347 "UPDATE Gift_Delivery_Requests " +
348 "SET notes = ? " +
349 "WHERE id = ?";
350
351 public static final String UPDATE_MEDICATION_REQUEST =
352 "UPDATE Medication_Requests " +
353 "SET doctor_id = ?, patient_id = ?, nurse_username = ?, deliverer_username = ?, dose = ?, type = ?, notes = ?, status = ?, date_and_time = ? " +
354 "WHERE id = ?";
355
356 public static final String UPDATE_MEDICATION_REQUEST_DELIVERER =
357 "UPDATE Medication_Requests " +
358 "SET deliverer_username = ? " +
359 "WHERE id = ?";
360
361 public static final String UPDATE_MEDICATION_REQUEST_STATUS =
362 "UPDATE Medication_Requests " +
363 "SET status = ? " +
364 "WHERE id = ?";
365
366 public static final String UPDATE_MEDICATION_REQUEST_NOTES =
367 "UPDATE Medication_Requests " +
368 "SET notes = ? " +
369 "WHERE id = ?";
370
371 public static final String UPDATE_SERVICE_REQUEST =
372 "UPDATE Service_Requests " +
373 "SET patient_id = ?, request_username = ?, assignee_username = ?, location = ?, service = ?, type = ?, notes = ?, status = ?, date_and_time = ? " +
374 "WHERE id = ?";
375
376 public static final String UPDATE_SERVICE_REQUEST_ASSIGNEE =
377 "UPDATE Service_Requests " +
378 "SET assignee_username = ? " +
379 "WHERE id = ?";
380
381 public static final String UPDATE_SERVICE_REQUEST_STATUS =
382 "UPDATE Service_Requests " +
383 "SET status = ? " +
384 "WHERE id = ?";
385
386 public static final String UPDATE_SERVICE_REQUEST_NOTES =
387 "UPDATE Service_Requests " +
388 "SET notes = ? " +
389 "WHERE id = ?";
390
391 public static final String REMOVE_NODE =
392 "DELETE FROM Nodes " +
393 "WHERE id = ?";
394
395 public static final String REMOVE_EDGE =
396 "DELETE FROM Edges " +
397 "WHERE id = ?";
398
399 public static final String REMOVE_GIFT_DELIVERY_REQUEST =
400 "DELETE FROM Gift_Delivery_Requests " +
401 "WHERE id = ?";
402
403 public static final String REMOVE_MEDICATION_REQUEST =
404 "DELETE FROM Medication_Requests " +
405 "WHERE id = ?";
406
407 public static final String REMOVE_SERVICE_REQUEST =
408 "DELETE FROM Service_Requests " +
409 "WHERE id = ?";
410}