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
