-
Notifications
You must be signed in to change notification settings - Fork 1
/
createDB.py
294 lines (233 loc) · 9.32 KB
/
createDB.py
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
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
from psycopg2.extras import Json
import psycopg2
import os
def createSchoolDB(conn):
conn.execute("DROP TABLE IF EXISTS school CASCADE;")
conn.execute("""
CREATE TABLE school(
id serial UNIQUE,
name varchar(255) NOT NULL,
city varchar(255) NOT NULL,
state varchar(2) NOT NULL,
PRIMARY KEY (name, city, state)
);""")
def createHallDB(conn):
conn.execute("DROP TABLE IF EXISTS res_hall CASCADE;")
conn.execute("""
CREATE TABLE res_hall(
id serial UNIQUE,
name varchar(50),
school_id int NOT NULL,
PRIMARY KEY (name, school_id),
FOREIGN KEY (school_id) REFERENCES school(id)
);""")
def createScheduleDB(conn):
conn.execute("DROP TABLE IF EXISTS schedule CASCADE;")
conn.execute("""
CREATE TABLE schedule(
id serial UNIQUE,
hall_id int,
month_id int,
created date,
PRIMARY KEY (id),
FOREIGN KEY (hall_id) REFERENCES res_hall(id),
FOREIGN KEY (month_id) REFERENCES month(id)
);""")
def createRaDB(conn):
conn.execute("DROP TABLE IF EXISTS ra CASCADE;")
conn.execute("""
CREATE TABLE ra(
id serial UNIQUE,
first_name varchar(20),
last_name varchar(50),
color varchar(7),
email varchar(256) UNIQUE,
PRIMARY KEY (id)
);""")
def createConflictDB(conn):
conn.execute("DROP TABLE IF EXISTS conflicts CASCADE;")
conn.execute("""
CREATE TABLE conflicts(
id serial UNIQUE,
ra_id int,
day_id int,
PRIMARY KEY (ra_id, day_id),
FOREIGN KEY (ra_id) REFERENCES ra(id),
FOREIGN KEY (day_id) REFERENCES day(id)
);""")
def createDutyDB(conn):
conn.execute("DROP TABLE IF EXISTS duties CASCADE;")
conn.execute("""
CREATE TABLE duties(
id serial UNIQUE,
hall_id int,
ra_id int,
day_id int,
sched_id int,
point_val int DEFAULT 0 CONSTRAINT pos_duty_point_value CHECK (point_val >= 0),
flagged boolean NOT NULL DEFAULT FALSE,
PRIMARY KEY (id),
FOREIGN KEY (hall_id) REFERENCES res_hall(id),
FOREIGN KEY (day_id) REFERENCES day(id),
FOREIGN KEY (ra_id) REFERENCES ra(id),
FOREIGN KEY (sched_id) REFERENCES schedule(id)
);""")
def createDayDB(conn):
conn.execute("DROP TABLE IF EXISTS day CASCADE;")
conn.execute("""
CREATE TABLE day(
id serial UNIQUE,
month_id int,
date date,
PRIMARY KEY (month_id,date),
FOREIGN KEY (month_id) REFERENCES month(id)
);""")
def createMonthDB(conn):
conn.execute("DROP TABLE IF EXISTS month CASCADE;")
conn.execute("""
CREATE TABLE month(
id serial UNIQUE,
num int,
name varchar(10),
year date,
PRIMARY KEY (name,year)
);""")
def createUserDB(conn):
conn.execute('DROP TABLE IF EXISTS "user" CASCADE;')
conn.execute("""
CREATE TABLE "user"(
id serial UNIQUE,
ra_id int UNIQUE,
username varchar(50) UNIQUE,
PRIMARY KEY (id),
FOREIGN KEY (ra_id) REFERENCES ra(id)
);""")
def createOAuthDB(conn):
conn.execute('DROP TABLE IF EXISTS flask_dance_oauth CASCADE;')
conn.execute("""
CREATE TABLE flask_dance_oauth(
id serial UNIQUE,
provider varchar(50),
created_at timestamp without time zone,
token json,
provider_user_id varchar(256),
user_id int,
PRIMARY KEY (id)
);""")
def createBreakDutiesTable(conn):
conn.execute('DROP TABLE IF EXISTS break_duties CASCADE;')
conn.execute("""
CREATE TABLE break_duties(
id serial UNIQUE,
ra_id int,
hall_id int,
month_id int,
day_id int,
point_val int DEFAULT 0 CONSTRAINT pos_break_duty_point_value CHECK (point_val >= 0),
PRIMARY KEY (hall_id, month_id, day_id, ra_id),
FOREIGN KEY (ra_id) REFERENCES ra(id),
FOREIGN KEY (hall_id) REFERENCES res_hall(id),
FOREIGN KEY (month_id) REFERENCES month(id),
FOREIGN KEY (day_id) REFERENCES day(id)
);""")
def createGoogleCalendarDB(conn):
conn.execute("DROP TABLE IF EXISTS google_calendar_info CASCADE;")
conn.execute("""
CREATE TABLE google_calendar_info(
id serial UNIQUE,
res_hall_id int,
auth_state varchar(30),
token bytea,
calendar_id varchar(60),
PRIMARY KEY (res_hall_id),
FOREIGN KEY (res_hall_id) REFERENCES res_hall(id)
);""")
def createPointModifierDB(conn):
conn.execute("DROP TABLE IF EXISTS point_modifier CASCADE;")
conn.execute("""
CREATE TABLE point_modifier(
id serial UNIQUE,
ra_id int,
res_hall_id int,
modifier int DEFAULT 0,
PRIMARY KEY (ra_id, res_hall_id),
FOREIGN KEY (ra_id) REFERENCES ra(id),
FOREIGN KEY (res_hall_id) REFERENCES res_hall(id)
);""")
def createHallSettingsDB(conn):
conn.execute("DROP TABLE IF EXISTS hall_settings CASCADE;")
defaultJSON = {
"reg_duty_num_assigned": 1, # Number of RAs to be assigned on regular duty days.
"multi_duty_num_assigned": 2, # Number of RAs to be assigned on multi-duty days.
"brk_duty_num_assigned": 1, # Number of RAs to be assigned on break duty days.
"reg_duty_pts": 1, # Number of points to be awarded for regular duties.
"multi_duty_pts": 2, # Number of points to be awarded for multi-day duties.
"brk_duty_pts": 3, # Number of points to be awarded for break duties.
"multi_duty_days": [4, 5] # Days of the week which are considered multi-duty days.
# Mon, Tues, Wed, Thurs, Fri, Sat, Sun
# 0 1 2 3 4 5 6
}
conn.execute("""
CREATE TABLE hall_settings(
id serial UNIQUE,
res_hall_id int NOT NULL,
year_start_mon int NOT NULL DEFAULT 8 CHECK (year_start_mon >= 1 AND
year_start_mon <= 12),
year_end_mon int NOT NULL DEFAULT 7 CHECK (year_end_mon >= 1 AND
year_end_mon <= 12),
duty_config json NOT NULL DEFAULT %s::JSON,
auto_adj_excl_ra_pts boolean NOT NULL DEFAULT false,
flag_multi_duty boolean NOT NULL DEFAULT false,
duty_flag_label VARCHAR(20) NOT NULL DEFAULT 'Secondary Personnel',
PRIMARY KEY (res_hall_id),
FOREIGN KEY (res_hall_id) REFERENCES res_hall(id)
);""", (Json(defaultJSON),))
def createStaffMembershipDB(conn):
conn.execute("DROP TABLE IF EXISTS staff_membership CASCADE;")
conn.execute("""
CREATE TABLE staff_membership(
id serial UNIQUE,
ra_id int NOT NULL,
res_hall_id int NOT NULL,
start_date date NOT NULL DEFAULT NOW(),
auth_level int NOT NULL DEFAULT 1,
selected boolean NOT NULL DEFAULT false,
PRIMARY KEY (ra_id, res_hall_id),
FOREIGN KEY (ra_id) REFERENCES ra(id),
FOREIGN KEY (res_hall_id) REFERENCES res_hall(id)
);""")
def createSchedulerQueueDB(conn):
conn.execute("DROP TABLE IF EXISTS scheduler_queue CASCADE;")
conn.execute("""
CREATE TABLE scheduler_queue(
id serial UNIQUE,
status int NOT NULL DEFAULT 0,
reason varchar(255) NOT NULL DEFAULT '',
res_hall_id int NOT NULL,
created_ra_id int NOT NULL,
created_date timestamp NOT NULL WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
FOREIGN KEY (res_hall_id) REFERENCES res_hall(id),
FOREIGN KEY (created_ra_id) REFERENCES ra(id)
);""")
def main():
conn = psycopg2.connect(os.environ["DATABASE_URL"])
createSchoolDB(conn.cursor())
createHallDB(conn.cursor())
createRaDB(conn.cursor())
createMonthDB(conn.cursor())
createDayDB(conn.cursor())
createConflictDB(conn.cursor())
createScheduleDB(conn.cursor())
createDutyDB(conn.cursor())
createBreakDutiesTable(conn.cursor())
createUserDB(conn.cursor())
createOAuthDB(conn.cursor())
createGoogleCalendarDB(conn.cursor())
createPointModifierDB(conn.cursor())
createHallSettingsDB(conn.cursor())
createStaffMembershipDB(conn.cursor())
createSchedulerQueueDB(conn.cursor())
conn.commit()
if __name__ == "__main__":
main()