def post(self): # if no file if 'picture' not in request.files: return 'Missing file', 400 # get filename picture = request.files['picture'] # check if the file is one of the allowed filetypes / extensions or empty if picture is None or not allowed_file(picture.filename): return 'Bad file', 400 image = Image.open(picture.stream) croppedpicture = resizeimage.resize_cover(image, [256, 256]) imgByteArr = io.BytesIO() croppedpicture.save(imgByteArr, format='JPEG') imgByteArr = bytearray(imgByteArr.getvalue()) # write to db c = db.cursor(cursor_factory=RealDictCursor) c.execute("UPDATE users SET picture = %s WHERE id = %s", (imgByteArr, request.id)) db.commit() return 'OK', 200
def post(self): email = get_form('email') password = get_form('password') # make sure required fields are not empyty if email is None or password is None: return 'Bad request', 400 # lookup user c = db.cursor(cursor_factory=RealDictCursor) c.execute("SELECT * FROM users WHERE cppemail = %s", (email,)) # check if we got a result row = c.fetchone() if row is None: return 'User does not exist', 404 # shallow copy result row = dict(row) # check password passhash = hashlib.sha256(password + row['salt']).hexdigest() if passhash != row['passhash']: return 'Bad password', 401 return jsonify({'token': issue_token(row['id'], row['cppemail'])})
def get(session_id): cursor = db.cursor() cursor.execute( 'SELECT user_id, expires, expired FROM Sessions WHERE id=%s;', (session_id, )) session_information = {} result = cursor.fetchone() cursor.close() if result: session_information = { 'user_id': result[0], 'expires': result[1], 'expired': result[2], } #if expired = False, check if the 'expires' time has passed the current time, #and if that's the case, change to expired = True if not session_information['expired']: current_datetime = datetime.datetime.now() if current_datetime > session_information['expires']: session_information['expired'] = True #There's no information about this session in the database. else: session_information = None return session_information
def post(self): altemail = get_form('altemail') addressline1 = get_form('addressline1') addressline2 = get_form('addressline2') # not required city = get_form('city') zipcode = get_form('zip') schedule = get_form('schedule') drivingpref = get_form('drivingpref') maxdist = get_form('maxdist') # make sure required fields are not missing if addressline1 is None or city is None or zipcode is None or schedule is None or drivingpref is None or maxdist is None: return 'Missing fields', 400 # make sure the required fields are not empty if len(addressline1) == 0 or len(city) == 0 or len(zipcode) == 0 or len(schedule) == 0 or len(drivingpref) == 0 or len(maxdist) == 0: return 'Empty fields', 400 # write to db c = db.cursor(cursor_factory=RealDictCursor) c.execute("UPDATE users SET altemail = %s, addressline1 = %s, city = %s, zip = %s, drivingpref = %s, maxdist = %s, profilecomplete = true WHERE id = %s", (altemail, addressline1, city, zipcode, drivingpref, maxdist, request.id)) schedule = json.loads(schedule) # write schedule to db for i in range(len(schedule)): day = schedule[i] c.execute("UPDATE schedule SET arrive = %s, depart = %s WHERE userid = %s AND dayofweek = %s", (day['arrive'], day['depart'], request.id, i,)) db.commit() return "OK", 200
def get(self, user=None): # default value for user is None other_user_email = request.email if user is not None: other_user_email = user + '@cpp.edu' # lookup requested OtherUser c = db.cursor(cursor_factory=RealDictCursor) c.execute("SELECT * FROM users WHERE cppemail = %s", (other_user_email,)) # check if we got a result row = c.fetchone(); if row is None: return 'User does not exist', 404 # fetch user schedule c.execute("SELECT * FROM schedule WHERE userid = %s ORDER BY dayofweek ASC", (row['id'],)) schedule = c.fetchall() row['schedule'] = [{'arrive': day['arrive'], 'depart': day['depart']} for day in schedule] # delete salt and passhash from row del row['passhash'] del row['salt'] # check if requestinguser != otheruser. If so, remove exact location info # note: request.email is set by extensions.requires_auth function if request.email != other_user_email: del row['addressline1'] del row['addressline2'] # jsonify row and return return jsonify(row)
def get(self, other_email): if other_email is None: return 'Missing other_email', 400 # lookup userid for requested user c = db.cursor(cursor_factory=RealDictCursor) c.execute("SELECT id FROM users WHERE cppemail = %s", (other_email + '@cpp.edu',)) # check if we got a result row = c.fetchone() if row is None: return 'User does not exist', 404 # get id other_id = row['id'] # lookup message history c.execute("SELECT * FROM messages WHERE receive_userid = %s and send_userid = %s or receive_userid = %s and send_userid = %s ORDER BY timestamp ASC", (other_id, request.id, request.id, other_id)) messages = c.fetchall() for m in messages: m['outgoing'] = m['send_userid'] == request.id # mark as read in db c.execute("UPDATE messages SET read = true WHERE receive_userid = %s and send_userid = %s", (request.id, other_id)) db.commit() return jsonify(results=messages)
def get(self, param): email = param + '@cpp.edu' # lookup userid for requested user c = db.cursor(cursor_factory=RealDictCursor) c.execute("SELECT id FROM users WHERE cppemail = %s", (email, )) # check if we got a result row = c.fetchone() if row is None: return 'User does not exist', 404 # get id userid = dict(row)['id'] # get all reviews for user, join with users table c.execute( "SELECT reviews.id, reviews.reviewer_userid, reviews.reviewee_userid, reviews.stars, reviews.content, users.cppemail as reviewer_email, users.fullname as reviewer_name, users.picture as reviewer_picture FROM reviews, users WHERE reviews.reviewee_userid = %s AND reviews.reviewer_userid = users.id", (userid, )) # check if we got any rows rows = c.fetchall() if rows is None: return jsonify([]) # jsonify row and return return jsonify(results=rows)
def delete(self): c = db.cursor(cursor_factory=RealDictCursor) c.execute("DELETE FROM users WHERE id = %s", (request.id,)) c.execute("DELETE FROM reviews WHERE reviewer_userid = %s OR reviewee_userid = %s", (request.id, request.id,)) c.execute("DELETE FROM schedule WHERE userid = %s", (request.id,)) c.execute("DELETE FROM interaction WHERE user1 = %s OR user2 = %s", (request.id, request.id,)) return "OK", 202
def post(self): # make sure user is not reviewing themself reviewee_email = get_form('email') if reviewee_email == request.email: return 'You can not review yourself', 400 # lookup reviewer id c = db.cursor(cursor_factory=RealDictCursor) c.execute("SELECT id FROM users WHERE cppemail = %s", (request.email, )) # check if we got a result row = c.fetchone() if row is None: return 'You do not exist', 404 # get id reviewer_id = dict(row)['id'] # lookup reviewee id c.execute("SELECT id FROM users WHERE cppemail = %s", (reviewee_email, )) # check if we got a result row = c.fetchone() if row is None: return 'User does not exist', 404 # get id reviewee_id = dict(row)['id'] # make sure user has not already reviewed this person c.execute( "SELECT id FROM reviews WHERE reviewee_userid = %s and reviewer_userid = %s", (reviewee_id, reviewer_id)) # check if we got a result row = c.fetchone() if row is not None: return 'You can not review more than once', 400 # get # of stars stars_unparsed = get_form('stars') if stars_unparsed is None: return 'Missing field: stars', 400 stars = int(stars_unparsed) if stars < 1 or stars > 5: return 'Stars out of range', 400 content = get_form('content') if content is None or len(content) == 0: return 'Missing field: content', 400 # insert new record into reviews table c.execute( "INSERT INTO reviews (reviewer_userid, reviewee_userid, stars, content) VALUES (%s, %s, %s, %s)", (reviewer_id, reviewee_id, stars, content)) db.commit() return 'OK', 201
def create(username): user_id = users.get_user_id(username) expires = datetime.datetime.now() + datetime.timedelta(hours=2) cursor = db.cursor() cursor.execute('INSERT INTO Sessions (user_id, expires) VALUES (%s, %s);', (user_id, expires)) db.commit() cursor.execute('SELECT LAST_INSERT_ID();') session_id = cursor.fetchone()[0] cursor.close() return session_id
def update(post_id, title, content): response_message = "" with db.cursor() as cursor: cursor.execute("SELECT * FROM Posts WHERE id=%s;", (post_id,)) if cursor.fetchone(): cursor.execute("UPDATE Posts SET title=%s, content=%s WHERE id=%s;", (title, content,)) response_message = "Posts id %s is updated" % post_id else: response_message = "Posts id %s does not exist" % post_id cursor.close() return response_message
def validate(username, password): cursor = db.cursor() cursor.execute("SELECT password FROM Users WHERE username=%s;", (username, )) result = cursor.fetchone() cursor.close() if result: fetched_password = result[0] else: return False return bcrypt.checkpw(password.encode(), fetched_password)
def post(self): email = get_form('email') if email is None: return 'Bad request', 400 c = db.cursor(cursor_factory=RealDictCursor) c.execute("SELECT id, fullname, picture FROM users WHERE cppemail = %s", (email,)) row = c.fetchone() if row is None: return 'User does not exist', 404 return jsonify(row)
def get_one_post(post_id): cursor = db.cursor() cursor.execute("SELECT * FROM Posts WHERE id=%s;", (post_id,)) post_id, author_id, title, content, deleted, created, updated = cursor.fetchone() cursor.close() return { "id": post_id, "author_id": author_id, "title": title, "content": content, "deleted": deleted, "created": created, "updated": updated, }
def post(self): to_userid = get_form('to_userid') text = get_form('message') # make sure required fields are not empyty if to_userid is None or text is None: return 'Missing fields', 400 # write to db c = db.cursor(cursor_factory=RealDictCursor) c.execute("INSERT INTO messages (send_userid, receive_userid, message, timestamp) VALUES (%s, %s, %s, CURRENT_TIMESTAMP)", (request.id, to_userid, text)) db.commit() return 'OK', 200
def get(id_post): cursor = db.cursor() cursor.execute("SELECT * FROM Comment WHERE post_id=%s", (id_post, )) all_comments = [] for comment_id, post_id, commentor_id, content, created, updated in cursor.fetchall( ): all_comments.append({ 'id': comment_id, 'post_id': post_id, 'commentor_id': commentor_id, 'content': content, 'cretaed': created, 'updated': updated }) cursor.close() return all_comments
def get(): posts = [] cursor = db.cursor() cursor.execute("SELECT * FROM Posts;") for post_id, author_id, title, content, deleted, created, updated in cursor.fetchall(): posts.append({ "id": post_id, "author_id": author_id, "title": title, "content": content, "deleted": deleted, "created": created, "updated": updated, }) cursor.close() return posts
def get(self): # lookup user's entire message histroy c = db.cursor(cursor_factory=RealDictCursor) c.execute("SELECT * FROM messages WHERE receive_userid = %s or send_userid = %s ORDER BY timestamp DESC", (request.id, request.id)) rows = c.fetchall() if rows is None or len(rows) == 0: return jsonify(results=[]) conversations = {} order = [] for m in rows: is_sender = False other_id = m['send_userid'] if other_id == request.id: is_sender = True other_id = m['receive_userid'] if other_id not in conversations: order.append(other_id) conversations[other_id] = { 'id': other_id, 'lastmessage': m, 'unread': 0 } # fetch picture and name. Slow, but works for demo c.execute("SELECT picture, fullname, cppemail FROM users WHERE id = %s", (other_id,)) row = c.fetchone(); if row is not None: if row['picture'] is not None: conversations[other_id]['picture'] = row['picture'] if row['fullname'] is not None: conversations[other_id]['fullname'] = row['fullname'] if row['cppemail'] is not None: conversations[other_id]['cppemail'] = row['cppemail'] else: if m['timestamp'] > conversations[other_id]['lastmessage']['timestamp']: conversations[other_id]['lastmessage'] = m if not is_sender and not m['read']: conversations[other_id]['unread'] += 1 results_sorted = [] for conv_id in order: results_sorted.append(conversations[conv_id]) return jsonify(results=results_sorted)
def get_one_user(username): user_information = {} cursor = db.cursor() cursor.execute("SELECT * FROM Users WHERE username=%s;", (username, )) result = cursor.fetchone() user_information = { "id": result[0], "username": result[1], "password": result[2], "nickname": result[3], "picture": result[4], "user_level": result[5], "delete": result[6], "created": result[7], "updated": result[8] } cursor.close() return user_information
def delete(self, param): reviewid = int(param) # lookup review id and enforce reviewer_userid c = db.cursor(cursor_factory=RealDictCursor) c.execute( "SELECT id FROM reviews WHERE reviewer_userid = %s and id = %s", (request.id, reviewid)) # check if we got a result row = c.fetchone() if row is None: return 'Review does not exist', 404 # get all reviews for user, join with users table c.execute("DELETE FROM reviews WHERE id = %s", (reviewid, )) db.commit() return 'OK', 202
def post(self): oldpassword = get_form('oldpassword') newpassword = get_form('newpassword') # make sure required fields are not empty if oldpassword is None or newpassword is None: return 'Missing fields', 400 # salt password c = db.cursor(cursor_factory=RealDictCursor) c.execute("SELECT * FROM users WHERE cppemail = %s", (request.email,)) salt = os.urandom(32).encode('hex') passhash = hashlib.sha256(newpassword + salt).hexdigest() # write to db c.execute("UPDATE users SET salt = %s, passhash = %s WHERE id = %s", (salt, passhash, request.id)) db.commit() return 'OK', 200
def get(): users = [] cursor = db.cursor() cursor.execute("SELECT * FROM Users;") for (user_id, username, password, nickname, picture, user_level, delete, created, updated) in cursor.fetchall(): users.append({ "id": user_id, "username": username, "password": password, "nickname": nickname, "picture": picture, "user_level": user_level, "delete": delete, "created": created, "updated": updated, }) cursor.close() return users
def create(post_id, commentor_id, content): cursor = db.cursor() cursor.execute( "INSERT INTO Comment (post_id, commentor_id, content) VALUES (%s, %s, %s)", (post_id, commentor_id, content['content'])) last_id = cursor.lastrowid db.commit() cursor.execute("SELECT * FROM Comment WHERE id=%s", (last_id, )) comment_info = cursor.fetchone() cursor.close() comment = { "id": comment_info[0], "post_id": comment_info[1], "commentor_id": comment_info[2], "content": comment_info[3], "created": comment_info[4], "updated": comment_info[5] } return comment
def create(account_dictionary): cursor = db.cursor() cursor.execute( "INSERT INTO Users (username, password) VALUES (%s, %s);", (account_dictionary['username'], account_dictionary['password'])) last_id = cursor.lastrowid db.commit() cursor.execute("SELECT * FROM Users WHERE id=%s", (last_id, )) account_info = cursor.fetchone() cursor.close() account = { "id": account_info[0], "username": account_info[1], "password": bcrypt.hashpw(account_info[2], bcrypt.gensalt()), "nickname": account_info[3], "picture": account_info[4], "user_level": account_info[5], "deleted": account_info[6], "created": account_info[7], "updated": account_info[8] } return account
def post(self): email = get_form('email') alt = get_form('altemail') password = get_form('password') fullname = get_form('fullname') # make sure required fields are not empyty if email is None or password is None or fullname is None or len(fullname) == 0: return 'Missing fields', 400 # use regex to enforce .cpp email if re.match(r'^[a-zA-Z]+@cpp\.edu$', email) is None: return 'cpp email', 400 # make sure user doesn't already exist c = db.cursor() c.execute("SELECT id FROM users WHERE cppemail = %s", (email,)) if c.fetchone() is not None: return 'account exists', 400 # salt and hash password salt = os.urandom(32).encode('hex') passhash = hashlib.sha256(password + salt).hexdigest() verified = True # TODO: implement email verification # write to db c.execute("INSERT INTO users (cppemail, fullname, altemail, salt, passhash, verified, timestamp) VALUES (%s, %s, %s, %s, %s, %s, CURRENT_TIMESTAMP) RETURNING id", (email, fullname, alt, salt, passhash, verified)) userid = c.fetchone()[0] values = [] for day in range(7): values.append(userid) values.append(day) # write empty schedule to db c.execute("INSERT INTO schedule (userid, dayofweek) VALUES (%s, %s), (%s, %s), (%s, %s), (%s, %s), (%s, %s), (%s, %s), (%s, %s)", tuple(values)) db.commit() return 'OK', 200
def create(post_dictionary): post = {} cursor = db.cursor() if not post_dictionary['title'] or not post_dictionary['content']: return cursor.execute("INSERT INTO Posts (author_id, title, content) Values(%s, %s, %s)", (1, post_dictionary['title'], post_dictionary['content'])) db.commit() last_id = cursor.lastrowid cursor.execute("SELECT * FROM Posts WHERE id=%s", (last_id,)) post_info = cursor.fetchone() post = { "id": post_info[0], "author_id": post_info[1], "title": post_info[2], "content": post_info[3], "created": post_info[4], "updated": post_info[5] } cursor.close() return post
def delete(comment_id): cursor = db.cursor() cursor.execute("DELETE FROM Comment WHERE id=%s", (comment_id, )) db.commit() cursor.close() return
def get(self): c = db.cursor(cursor_factory=RealDictCursor) c.execute("SELECT * FROM users WHERE cppemail = %s", (request.email, )) # check if we got a result user = c.fetchone() if user is None: return 'User does not exist', 404 # load all other users c.execute( "SELECT * FROM users WHERE cppemail != %s AND verified = true AND profilecomplete = true AND addressline1 IS NOT NULL and city IS NOT NULL and zip IS NOT NULL", (request.email, )) rows = c.fetchall() if len(rows) == 0: return jsonify(results=[]) for row in rows: del row['passhash'] del row['salt'] sortedResults = maps.sortByDist(user, rows) sortedUsers = [] for res in sortedResults: print res # if distance is negative, pass if res[1] < 0: continue res[0]['dist'] = res[1] del res[0]['addressline1'] del res[0]['addressline2'] sortedUsers.append(res[0]) for user in sortedUsers: # fetch reviews c.execute("SELECT * FROM reviews WHERE reviewee_userid = %s", (user['id'], )) reviews = c.fetchall() reviewCount = len(reviews) stars = 0 if reviewCount > 0: # compute average num of stars starsList = list(map(lambda r: r['stars'], reviews)) stars = reduce(lambda sum, s: sum + s, starsList) / reviewCount # add on review count and avg stars user['reviewCount'] = reviewCount user['stars'] = stars # fetch schedule c.execute( "SELECT * FROM schedule WHERE userid = %s ORDER BY dayofweek ASC", (user['id'], )) scheduled_days = c.fetchall() user['schedule'] = [{ 'arrive': d['arrive'], 'depart': d['depart'] } for d in scheduled_days] return jsonify(results=sortedUsers)
def get_one_comment(comment_id): cursor = db.cursor() cursor.execute("SELECT * FROM Comment WHERE id=%s", (comment_id,)) comment_info = cursor.fetchone() return comment_info
def delete(post_id): response_message = "" cursor = db.cursor() cursor.execute("DELETE FROM Posts WHERE id=%s", (post_id,)) cursor.close() return response_message