def session_auth(sessionID): results = None if sessionID in cached_sessions: results = [cached_sessions[sessionID]] else: sql = "SELECT * FROM Sessions WHERE SessionToken = %s" val = (sessionID, ) #TODO CHANGE TO LOGIN USER mydb, mycursor = db.connect() mycursor.execute(sql, val) results = mycursor.fetchall() mydb.close() if len(results) == 0: return None #cached_sessions[sessionID] = results[0] createTime = results[0][2] currentTime = time.time() if currentTime - createTime > SESSION_TIMEOUT: mydb, mycursor = db.connect() sql = "DELETE FROM Sessions WHERE SessionToken = %s" mycursor.execute(sql, val) mydb.commit() mydb.close() if sessionID in cached_sessions: del cached_sessions[sessionID] return None return results[0][0]
def real(environ, start_response, netId): message = check_request(environ, start_response) query = pq(environ[QUERY]) if message == "": crn = query["crn"][0] sql = "DELETE FROM Enrollments WHERE CRN = %s and NetId=%s" val = (crn, netId) #TODO CHANGE TO LOGGED IN USER mydb = None try: mydb, mycursor = db.connect() #db.mydb.start_transaction(isolation_level=SERIALIZABLE, readonly=False) mycursor.execute(sql, val) if mycursor.rowcount > 0: start_response('200 OK', [('Content-Type', 'json')]) mydb.commit() message = json.dumps({STATUS: SUCCESS}) else: start_response('400 Bad Request', [('Content-Type', 'json')]) mydb.commit() message = json.dumps({ STATUS: FAILED, MESSAGE: "User is not enrolled in class" }) except Exception as e: mydb.rollback() print_exc() start_response('500 INTERNAL SERVER ERROR', [('Content-Type', 'json')]) message = json.dumps({STATUS: FAILED, MESSAGE: str(e)}) finally: if mydb: mydb.close() print(message) return [message.encode()]
def real(environ, start_response, netId): message = check_request(environ,start_response) query = pq(environ[QUERY]) if message == "": q = db.escapeString(query["q"][0]) sql = """SELECT Title, Classes.CRN, Department, Instructor, (x.netId IS NOT NULL) FROM Classes LEFT JOIN (SELECT * FROM Enrollments WHERE NetID = %s) AS x ON Classes.CRN = x.CRN WHERE Title LIKE '""" + q + "%'" val1 = (netId, ) try: mydb, mycursor = db.connect() mycursor.execute(sql, val1) results = [{"class":row[0],"crn":row[1], "department":row[2], "instructor":row[3], "currentlyEnrolled": row[4] == 1} for row in mycursor.fetchall()] start_response('200 OK', [('Content-Type', 'json')]) message = json.dumps({ STATUS: SUCCESS, MESSAGE: {"results": results} }) mydb.close() except: print_exc() start_response('500 INTERNAL SERVER ERROR', [('Content-Type', 'json')]) message = json.dumps({ STATUS: FAILED, MESSAGE: "oopsie I made a poopsie" }) return [message.encode()]
def real(environ, start_response, netId): message = check_request(environ, start_response) query = pq(environ[QUERY]) if message == "": crn = query["crn"][0] sql = "INSERT INTO Enrollments (CRN, NetId) VALUES (%s, %s)" val = (crn, netId) #TODO CHANGE TO LOGIN USER try: mydb, mycursor = db.connect() #mydb.start_transaction(isolation_level=SERIALIZABLE, readonly=False) mycursor.execute(sql, val) start_response('200 OK', [('Content-Type', 'json')]) mydb.commit() message = json.dumps({STATUS: SUCCESS}) except Exception as e: mydb.rollback() print_exc() start_response('500 INTERNAL SERVER ERROR', [('Content-Type', 'json')]) message = json.dumps({STATUS: FAILED, MESSAGE: str(e)}) finally: if mydb: mydb.close() print(message) return [message.encode()]
def real(environ, start_response, netId): message = check_request(environ, start_response) if message == "": mydb = None sql = "SELECT Classes.CRN, Classes.Title FROM Classes INNER JOIN Enrollments ON Classes.CRN = Enrollments.CRN WHERE Enrollments.NetId = %s" val = (netId, ) try: mydb, mycursor = db.connect() mycursor.execute(sql, val) resultsClasses = mycursor.fetchall() classes = {} for row in resultsClasses: classes[row[0]] = row[1] start_response('200 OK', [('Content-Type', 'json')]) message = json.dumps({STATUS: SUCCESS, MESSAGE: classes}) except: print_exc() start_response('500 INTERNAL SERVER ERROR', [('Content-Type', 'json')]) message = json.dumps({ STATUS: FAILED, MESSAGE: "Internal server error" }) finally: if mydb: mydb.close() return [message.encode()]
def real(environ, start_response): message = check_request(environ, start_response) query = pq(environ[QUERY]) if message == "": crn = query["crn"][0] body = environ[BODY].read().decode("utf-8") body = json.loads(body) sql = "UPDATE Classes SET Note = %s WHERE CRN = %s" if 'note' not in body or str(type( body['note'])) != "<class 'str'>" or len(body['note']) > 280: start_response('400 Bad Request', [('Content-Type', 'json')]) message = json.dumps({STATUS: FAILED, MESSAGE: "Improper request"}) else: val = (body['note'], crn) try: mydb, mycursor = db.connect() mycursor.execute(sql, val) start_response('200 OK', [('Content-Type', 'json')]) mydb.commit() message = json.dumps({STATUS: SUCCESS}) except Exception as e: mydb.rollback() print_exc() start_response('500 INTERNAL SERVER ERROR', [('Content-Type', 'json')]) message = json.dumps({STATUS: FAILED, MESSAGE: str(e)}) finally: if mydb: mydb.close() return [message.encode()]
def add_user(netId, name="Abdu"): sql = "SELECT * FROM Users WHERE NetId = %s" val = (netId, ) mydb, mycursor = db.connect() mycursor.execute(sql, val) results = mycursor.fetchall() mydb.close() if len(results) == 0: mydb, mycursor = db.connect() sql = "INSERT INTO Users (NetId, Name) VALUES (%s, %s)" val = (netId, name) mydb, mycursor = db.connect() mycursor.execute(sql, val) mydb.commit() mydb.close() return True else: return False
def load_doc(environ, start_response): doc_id = environ[PATH].split("/doc/")[1] # Check cookies cookie = None netId = None if COOKIES in environ: raw_cookies = environ[COOKIES] cookie = SimpleCookie() cookie.load(raw_cookies) try: netId = session_auth(cookie['sessionID'].value) except: return please_login(environ, start_response) form = None content = b'' mydb = None try: sql = "SELECT format, content FROM Documents WHERE id = %s" vals = (doc_id,) mydb, mycursor = db.connect() mycursor.execute(sql, vals) row = mycursor.fetchall()[0] form = row[0] content = row[1] if mydb: mydb.close() except IndexError: if mydb: mydb.close() return not_found(environ, start_response) except: print_exc() if mydb: mydb.close() return internal_error(environ, start_response) try: contentType = [] if form == "png": contentType = [('Content-Type', 'image/png')] elif form == "jpg": contentType = [('Content-Type', 'image/jpg')] elif form == "pdf": contentType = [('Content-Type', 'application/pdf')] elif form == "txt": contentType = [('Content-Type', 'text/plain')] else: return internal_error(environ, start_response) start_response('200 OK', contentType + [('Cache-Control', 'no-store, must-revalidate'), ('Pragma', 'no-cache'), ('Expires', '0')]) return [content] except Exception as e: print_exc() return not_found(environ, start_response)
def stop_session(sessionID): sql = "DELETE FROM Sessions WHERE SessionToken = %s" val = (sessionID, ) mydb, mycursor = db.connect() mycursor.execute(sql, val) results = mycursor.rowcount print("RES:", results) mydb.commit() mydb.close() return results == 1
def real(environ, start_response): message = check_request(environ,start_response) query = pq(environ[QUERY]) if message == "": body = None # (CRN, Title, Department, Instructor , Note VARCHAR(280)) try: body = environ[BODY].read().decode("utf-8") body = json.loads(body) sql = "INSERT INTO Classes ( CRN , Title, Department, Instructor, Note) VALUES (%s, %s, %s, %s, %s)" num_attributes = 0 vals = [] vals.append(body["CRN"]) vals.append(body["title"]) # Extract updated values vals.append(body["department"]) vals.append(body["instructor"]) vals.append(body["note"] if "note" in body else "Nothing here.") except: print_exc() start_response('500 INTERNAL SERVER ERROR', [('Content-Type', 'json')]) message = json.dumps({ STATUS: FAILED, MESSAGE: "Please provide valid json" }) return [message.encode()] mydb = None try: print(vals) mydb, mycursor = db.connect() mycursor.execute(sql, vals) mydb.commit() print("done") start_response('200 OK', [('Content-Type', 'json')]) message = json.dumps({ STATUS: SUCCESS, "CRN" : body["CRN"] }) except Exception as e: if mydb is not None: mydb.rollback() print_exc() start_response('500 INTERNAL SERVER ERROR', [('Content-Type', 'json')]) message = json.dumps({ STATUS: FAILED, MESSAGE: str(e) }) finally: if mydb: mydb.close() print(message) return [message.encode()]
def insertEventData(day): mydb, mycursor = db.connect() val = [] sql = "INSERT INTO Events (CRN, Title, DueDate, Event_Des) VALUES (%s, %s, %s, %s)" crn = 1000 for c in classes: x = crn - 1000 for assignment in range(0,1 + random.randint(0,5)): val += [(str(crn), "MP" + str(assignment), time.strftime("%m-%d-%Y %H:%M", time.localtime(day + (100000 * (x + 1)))), "This assignment exists")] crn += 1 mycursor.executemany(sql, val) mydb.commit() mydb.close()
def start_session(netId): session = str(secrets.token_urlsafe(200))[:200] # Limit to first 200 chars sql = "SELECT * FROM Sessions WHERE SessionToken = %s" val = (session, ) #TODO CHANGE TO LOGIN USER mydb, mycursor = db.connect() mycursor.execute(sql, val) results = mycursor.fetchall() mydb.close() if len(results) == 0: mydb, mycursor = db.connect() sql = "INSERT INTO Sessions (NetId, SessionToken, CreateTime) VALUES (%s, %s, %s)" val = (netId, session, time.time()) mydb, mycursor = db.connect() mycursor.execute(sql, val) mydb.commit() mydb.close() #cached_sessions[session] = val return session else: # Session token generated collides with existing session token return start_session(netId)
def dropTables(): mydb, mycursor = db.connect() sql = "DROP TABLE IF EXISTS Documents" mycursor.execute(sql) sql = "DROP TABLE IF EXISTS Sessions" mycursor.execute(sql) sql = "DROP TABLE IF EXISTS EventDone" mycursor.execute(sql) sql = "DROP TABLE IF EXISTS Events" mycursor.execute(sql) sql = "DROP TABLE IF EXISTS Enrollments" mycursor.execute(sql) sql = "DROP TABLE IF EXISTS Users" mycursor.execute(sql) sql = "DROP TABLE IF EXISTS Classes" mycursor.execute(sql) mydb.close()
def real(environ, start_response): message = check_request(environ, start_response) query = pq(environ[QUERY]) if message == "": crn = query["crn"][0] sql = "SELECT Title, CRN, Department, Instructor, Note FROM Classes WHERE CRN = %s" mydb = None try: mydb, mycursor = db.connect() val = (crn, ) mycursor.execute(sql, val) results = mycursor.fetchall() if len(results) == 1: start_response('200 OK', [('Content-Type', 'json')]) row = results[0] message = json.dumps({ STATUS: SUCCESS, MESSAGE: { "class": row[0], "crn": row[1], "department": row[2], "instructor": row[3], "note": row[4] } }) else: start_response('400 Bad Request', [('Content-Type', 'json')]) message = json.dumps({ STATUS: FAILED, MESSAGE: "class does not exist" }) except: print_exc() start_response('500 INTERNAL SERVER ERROR', [('Content-Type', 'json')]) message = json.dumps({ STATUS: FAILED, MESSAGE: "oopsie I made a poopsie" }) finally: if mydb: mydb.close() print(message) return [message.encode()]
def real(environ, start_response): message = check_request(environ, start_response) query = pq(environ[QUERY]) if message == "": eventId = query["eventId"][0] sql = "SELECT CRN, Title, DueDate, Event_Des FROM Events WHERE EventId = %s" mydb = None try: mydb, mycursor = db.connect() val = (eventId, ) mycursor.execute(sql, val) results = mycursor.fetchall() if len(results) == 1: start_response('200 OK', [('Content-Type', 'json')]) row = results[0] message = json.dumps({ STATUS: SUCCESS, MESSAGE: { "eventId": str(query["eventId"][0]), "crn": row[0], "title": row[1], "duedate": row[2], "desc": row[3] } }) else: start_response('400 Bad Request', [('Content-Type', 'json')]) message = json.dumps({ STATUS: FAILED, MESSAGE: "class does not exist" }) except: print_exc() start_response('500 INTERNAL SERVER ERROR', [('Content-Type', 'json')]) message = json.dumps({ STATUS: FAILED, MESSAGE: "oopsie I made a poopsie" }) finally: if mydb: mydb.close() print(message) return [message.encode()]
def testData(): mydb, mycursor = db.connect() teachers = ["Abdu", "Zilles", "Angrave", "Fleck", "Chatman"] classes = [ "cs125", "cs126", "cs225", "cs233", "cs241", "cs411", "cs440", "tam212", "ese360", "cee330", "eng100", "aas100" ] val = [] sql = "INSERT INTO Users (NetId, Name) VALUES (%s, %s)" for user in range(0, 10): val += [(str(user), "Julian" + str(user))] mycursor.executemany(sql, val) val = [] sql = "INSERT INTO Classes (CRN, Title, Department, Instructor) VALUES (%s, %s, %s, %s)" crn = 1000 for c in classes: teacher = teachers[((crn - 1000) % len(teachers))] val += [(str(crn), c, c[:-3], teacher)] crn += 1 mycursor.executemany(sql, val) val = [] sql = "INSERT INTO Events (CRN, Title, DueDate, Event_Des) VALUES (%s, %s, %s, %s)" current = time.time() crn = 1000 for c in classes: x = crn - 1000 for assignment in range(0, 5): val += [(str(crn), "MP" + str(assignment), time.strftime('%m-%d-%Y %H:%M', time.localtime(current + (10000 * x + x))), "This assignment exists")] crn += 1 mycursor.executemany(sql, val) val = [] sql = "INSERT INTO Enrollments (CRN, NetId) VALUES (%s, %s)" for user in range(0, 10): # pick 4 random classes to enroll in for crn in random.sample(list(range(1000, 1000 + len(classes))), 4): val += [(str(crn), str(user))] mycursor.executemany(sql, val) mydb.commit() mydb.close()
def real(environ, start_response, netId): message = check_request(environ, start_response) query = pq(environ[QUERY]) if message == "": content = None try: content = environ[BODY].read() if 'binary' not in query or query['binary'][0].lower() == "false": print("Not binary") content = base64.decodebytes(content) if len(content) == 0: raise Exception("Please provide a non empty document") except: print_exc() start_response('400 Bad Request', [('Content-Type', 'json')]) message = json.dumps({ STATUS: FAILED, MESSAGE: "Error uploading document" }) return [message.encode()] eventId = query["eventId"][0] form = query["format"][0].lower() sql = "INSERT INTO Documents (format, content, EventId, author) VALUES (%s, %s, %s, %s)" val = (form, content, eventId, netId) #TODO CHANGE TO LOGIN USER mydb = None try: mydb, mycursor = db.connect() mycursor.execute(sql, val) _id = mycursor.lastrowid mydb.commit() start_response('200 OK', [('Content-Type', 'json')]) message = json.dumps({STATUS: SUCCESS, "id": _id}) except Exception as e: mydb.rollback() print_exc() start_response('500 INTERNAL SERVER ERROR', [('Content-Type', 'json')]) message = json.dumps({STATUS: FAILED, MESSAGE: str(e)}) finally: if mydb: mydb.close() return [message.encode()]
def real(environ, start_response, netId): message = check_request(environ, start_response) query = pq(environ[QUERY]) if message == "": EventId = query["EventId"][0] check = query["check"][0].lower() == "true" sql = "" val = (EventId, netId) if check: sql = "INSERT INTO EventDone (EventId, NetId) VALUES (%s, %s)" else: sql = "DELETE FROM EventDone WHERE EventId = %s and NetId=%s" try: mydb, mycursor = db.connect() #mydb.start_transaction(isolation_level=SERIALIZABLE, readonly=False) mycursor.execute(sql, val) count = mycursor.rowcount start_response('200 OK', [('Content-Type', 'json')]) mydb.commit() message = json.dumps({STATUS: SUCCESS if count == 1 else FAILED}) except Exception as e: mydb.rollback() print_exc() start_response('500 INTERNAL SERVER ERROR', [('Content-Type', 'json')]) message = json.dumps({ STATUS: FAILED, MESSAGE: "Already checked done on event" if check else "Event is already unchecked" }) finally: if mydb: mydb.close() print(message) return [message.encode()]
def real(environ, start_response, netId): message = check_request(environ, start_response) query = pq(environ[QUERY]) if message == "": mydb = None eventId = query["eventId"][0] sql = "SELECT id, format, author FROM Documents WHERE EventId = %s" try: eventId = int(eventId) val = (eventId, ) mydb, mycursor = db.connect() mycursor.execute(sql, val) results = mycursor.fetchall() docs = [] for row in results: docs.append({ "id": int(row[0]), "format": row[1], "author": row[2] }) start_response('200 OK', [('Content-Type', 'json')]) message = json.dumps({STATUS: SUCCESS, MESSAGE: docs}) except: print_exc() start_response('500 INTERNAL SERVER ERROR', [('Content-Type', 'json')]) message = json.dumps({ STATUS: FAILED, MESSAGE: "Internal server error" }) finally: if mydb: mydb.close() return [message.encode()]
def real(environ, start_response): message = check_request(environ, start_response) query = pq(environ[QUERY]) if message == "": body = None try: body = environ[BODY].read().decode("utf-8") print(body) body = json.loads(body) sql = "INSERT INTO Events ( CRN , Title, DueDate, Event_Des) VALUES (%s, %s, %s, %s)" num_attributes = 0 vals = [] vals.append(body["CRN"]) vals.append(body["title"]) # Extract updated values try: time.strptime(body["duedate"], "%m-%d-%Y %H:%M") except: print_exc() start_response('500 INTERNAL SERVER ERROR', [('Content-Type', 'json')]) message = json.dumps({ STATUS: FAILED, MESSAGE: "Incorrect Date in duedate put it in %Y-%m-%d %H:%M:%S" }) return [message.encode()] vals.append(body["duedate"]) vals.append(body["desc"]) except: print_exc() start_response('500 INTERNAL SERVER ERROR', [('Content-Type', 'json')]) message = json.dumps({ STATUS: FAILED, MESSAGE: "Please provide valid json" }) return [message.encode()] mydb = None try: print(vals) mydb, mycursor = db.connect() mycursor.execute(sql, vals) mydb.commit() _id = mycursor.lastrowid print("done") start_response('200 OK', [('Content-Type', 'json')]) message = json.dumps({STATUS: SUCCESS, "EventId": _id}) except Exception as e: if mydb is not None: mydb.rollback() print_exc() start_response('500 INTERNAL SERVER ERROR', [('Content-Type', 'json')]) message = json.dumps({STATUS: FAILED, MESSAGE: str(e)}) finally: if mydb: mydb.close() print(message) return [message.encode()]
def real(environ, start_response): message = check_request(environ, start_response) query = pq(environ[QUERY]) if message == "": body = None eventId = query["eventId"][0] try: body = environ[BODY].read().decode("utf-8") body = json.loads(body) except: start_response('500 INTERNAL SERVER ERROR', [('Content-Type', 'json')]) message = json.dumps({ STATUS: FAILED, MESSAGE: "Please provide valid json" }) return [message.encode()] sql = "UPDATE Events SET" num_attributes = 0 vals = [] # Extract updated values for key, val in body.items(): print(key, "=", val) if key not in key_map: num_attributes = 0 break if key == "duedate": # must validate it is a good time try: time.strptime(val, "%m-%d-%Y %H:%M") except: print_exc() num_attributes = 0 break if type(val) != str: print("Improper type! expecting string but got", type(val)) num_attributes = 0 break num_attributes += 1 sql += " " + key_map[key] + " = %s," vals.append(val) mydb = None try: if num_attributes == 0: raise Exception( "Invalid body provided please use one or more of the following keys with valid values: " + str(list(key_map.keys()))) # remove trailing comma sql = sql[:-1] sql += " WHERE EventId = %s" vals.append(eventId) mydb, mycursor = db.connect() mycursor.execute(sql, vals) mydb.commit() start_response('200 OK', [('Content-Type', 'json')]) message = json.dumps({STATUS: SUCCESS}) except Exception as e: if mydb is not None: mydb.rollback() print_exc() start_response('500 INTERNAL SERVER ERROR', [('Content-Type', 'json')]) message = json.dumps({STATUS: FAILED, MESSAGE: str(e)}) finally: if mydb: mydb.close() return [message.encode()]
def real(environ, start_response, netId): message = check_request(environ, start_response) query = pq(environ[QUERY]) if message == "": q = db.escapeString(query["date"][0]) numDays = int(db.escapeString(query["numDays"][0])) dates = [] start = time.time() current = start if q == "today": dates += [time.strftime('%m-%d-%Y', time.localtime(current))] elif q == "tomorrow": dates += [ time.strftime('%m-%d-%Y', time.localtime(current + 86400)) ] current = start + 86400 elif q == "yesterday": dates += [ time.strftime('%m-%d-%Y', time.localtime(current - 86400)) ] current = start - 86400 else: try: print(q) format = time.strptime(q, "%m-%d-%Y") start = time.mktime(format) current = start print("start is {}".format( time.strftime('%m-%d-%Y', time.localtime(current - (numDays) * 86400)))) dates += [q] except: print_exc() start_response('500 INTERNAL SERVER ERROR', [('Content-Type', 'json')]) message = json.dumps({ STATUS: FAILED, MESSAGE: "Date query has wrong format" }) return [message.encode()] previous = time.strftime('%m-%d-%Y', time.localtime(current - (numDays) * 86400)) for x in range(numDays - 1): dates += [ time.strftime('%m-%d-%Y', time.localtime(current + 86400)) ] current += 86400 nextDay = time.strftime('%m-%d-%Y', time.localtime(current + 86400)) mydb = None sql1 = "SELECT Enrollments.CRN, Classes.Title FROM Classes INNER JOIN Enrollments ON Classes.CRN = Enrollments.CRN WHERE Enrollments.NetId = %s" val1 = (netId, ) sql2 = """ SELECT * FROM ((SELECT Classes.CRN, `Events`.`Title`, `Events`.`DueDate`, `Events`.`Event_Des`, `Classes`.`Title` AS CTitle, `Events`.EventId as EventId FROM Classes LEFT JOIN `Events` ON Classes.crn = `Events`.crn) As Y LEFT JOIN ( SELECT * FROM EventDone WHERE EventDone.NetId = %s) as ED ON ED.EventId = Y.EventId), `Enrollments` WHERE `Enrollments`.crn = Y.CRN AND `Enrollments`.NetId = %s """ # Dates is not USER generated, I made it bitch thats right. I can bring down this whole system by changing motherfucking date sql2 += "AND (Y.DueDate LIKE '" + dates[0] + "%'" for x in range(1, len(dates)): sql2 += "OR Y.DueDate LIKE '" + dates[x] + "%'" sql2 += ")" val2 = (netId, netId) try: if len(dates) == "0": raise Exception('Date is not anything') mydb, mycursor = db.connect() mycursor.execute(sql1, val1) resultsClasses = mycursor.fetchall() mycursor.execute(sql2, val2) resultsCalendar = mycursor.fetchall() titles = {} events = {} for elem in dates: events[elem] = {} print("NETID:", netId) for row in resultsClasses: if row[0] not in titles: titles[row[0]] = row[1] for elem in dates: events[elem][row[0]] = [] for row in resultsCalendar: events[row[2].split(' ')[0]][row[0]].append({ "crn": row[0], "class": row[4], "title": row[1], "desc": row[3], "DueDate": row[2], "EventId": row[5], "checked": row[6] != None }) start_response('200 OK', [('Content-Type', 'json')]) message = json.dumps({ STATUS: SUCCESS, MESSAGE: { "dates": dates, "prev": previous, "next": nextDay, "titles": titles, "events": events } }) except: print_exc() start_response('500 INTERNAL SERVER ERROR', [('Content-Type', 'json')]) message = json.dumps({ STATUS: FAILED, MESSAGE: "oopsie I made a poopsie" }) finally: if mydb: mydb.close() return [message.encode()]