def register():
conn = sqldb.try_open_conn()
assert conn is not None
cur = conn.cursor()
email = request.values.get('email')
dob = request.values.get('dob')
username = request.values.get('username')
password = request.values.get('password')
if email is None or dob is None or username is None or password is None:
return app.response_class(status=400)
query = 'SELECT * FROM UserAuth WHERE username LIKE ? OR email LIKE ?;'
parameters = (username, email)
existing_user = sqldb.do_sql(cur, query, parameters)
if existing_user is not None and len(existing_user) > 0:
return app.response_class(status=400)
query = 'INSERT INTO Users (name, dob, pictureId) VALUES (?,?,?);'
parameters = (username, dob, sqldb.DEFAULT_PICTURE_ID)
sqldb.do_sql(cur, query, parameters)
uid = cur.lastrowid
query = 'INSERT INTO UserAuth (username, email, hash, salt, userId) VALUES (?,?,?,?,?);'
parameters = (username, email, *crypto.hash_secret(password), uid)
sqldb.do_sql(cur, query, parameters)
conn.commit()
return app.response_class(status=200)
def fetch_all_friends():
conn = sqldb.try_open_conn()
assert conn is not None
cur = conn.cursor()
uid = session.get('uid')
query = '''SELECT conversationId FROM UsersConversationsJoin
WHERE userId LIKE ?;'''
parameters = (uid, )
conversations = sqldb.do_sql(cur, query, parameters)
if conversations is None:
return []
friend_ids = []
query = '''SELECT userId FROM UsersConversationsJoin
WHERE userId <> ? AND conversationId LIKE ?;'''
for idx, tup in enumerate(conversations):
parameters = (uid, tup[0])
friends = sqldb.do_sql(cur, query, parameters)
if friends is None or len(friends) == 0:
continue
for friend_id in friends:
friend_ids.append(friend_id)
return jsonify(friend_ids)
def send_message():
conn = sqldb.try_open_conn()
assert conn is not None
cur = conn.cursor()
uid = session.get('uid')
cid = session.get('cid')
message = request.values.get('content')
date_time = datetime.utcnow()
query = '''SELECT conversationId FROM UsersConversationsJoin
INNER JOIN Users ON UsersConversationsJoin.userId = Users.id
WHERE userId LIKE ? AND conversationId LIKE ?;'''
parameters = (uid, cid)
users_conversations = sqldb.do_sql(cur, query, parameters)
if users_conversations is None or len(users_conversations) == 0:
return app.response_class(status=400)
query = '''SELECT fpath FROM Conversations
INNER JOIN UsersConversationsJoin ON UsersConversationsJoin.conversationId = Conversations.id
WHERE Conversations.id LIKE ?;'''
parameters = (cid, )
fpath = sqldb.do_sql(cur, query, parameters)
if fpath is None or len(fpath) == 0:
return app.response_class(status=200)
conversation.write_message(fpath[0][0], date_time, uid, message)
return app.response_class(status=200)
def read_profile():
conn = sqldb.try_open_conn()
assert conn is not None
cur = conn.cursor()
# take the uid provided in the request, falling back to the session uid
uid = request.values.get('uid', session.get('uid'))
if (user_profile := load_user_profile(cur, uid)) is None:
return app.response_class(status=400)
def logout():
conn = sqldb.try_open_conn()
assert conn is not None
cur = conn.cursor()
session.pop('uid', None)
session.pop('cid', None)
return app.response_class(status=200,
headers={'Clear-Site-Data': '"*", "cookies"'})
def fetch_best_matches():
conn = sqldb.try_open_conn()
assert conn is not None
cur = conn.cursor()
uid = session.get('uid')
match_count = int(request.values.get('matches', DEFAULT_MATCH_COUNT))
query = '''SELECT interestId FROM UsersInterestsJoin WHERE userId LIKE ?;'''
parameters = (uid, )
interest_ids = [tup[0] for tup in sqldb.do_sql(cur, query, parameters)]
matches = matching.n_best_matches(cur, uid, interest_ids, match_count)
return jsonify([{'uid': match[0], 'score': match[1]} for match in matches])
def fetch_all_interests():
conn = sqldb.try_open_conn()
assert conn is not None
cur = conn.cursor()
query = '''SELECT InterestCategories.name, Interests.name FROM InterestCategories
INNER JOIN Interests ON Interests.categoryId = InterestCategories.id
ORDER BY InterestCategories.name;'''
results = sqldb.do_sql(cur, query)
interests = dict()
for category, interest in results:
prev = interests.get(category, [])
prev.append(interest)
interests[category] = prev
return jsonify(interests)
def fetch_messages():
conn = sqldb.try_open_conn()
assert conn is not None
cur = conn.cursor()
uid = session.get('uid')
cid = session.get('cid')
from_date = request.values.get('fromDate')
from_time = request.values.get('fromTime')
query = '''SELECT fpath FROM UsersConversationsJoin
INNER JOIN Conversations ON UsersConversationsJoin.conversationId = Conversations.id
WHERE userId LIKE ? AND conversationId LIKE ?;'''
parameters = (uid, cid)
fpath = sqldb.do_sql(cur, query, parameters)
if fpath is None or len(fpath) == 0:
return app.response_class(status=400)
try:
from_date_index = int(from_date)
except Exception:
from_date_index = -1
try:
from_time_index = int(from_time)
except Exception:
from_time_index = -1
msgs_to_read = conversation.read_messages(fpath[0][0], from_date_index,
from_time_index)
return jsonify([{
'timestamp': t[0],
'datestamp': t[1],
'content': t[3],
'isLocal': int(t[2]) == uid
} for t in msgs_to_read])
def start_conversation():
conn = sqldb.try_open_conn()
assert conn is not None
cur = conn.cursor()
uid = session.get('uid')
other_uid = request.values.get('other')
query = '''SELECT a.conversationId FROM UsersConversationsJoin a
INNER JOIN UsersConversationsJoin b ON a.conversationId = b.conversationId
WHERE a.userId LIKE ? AND b.userId LIKE ?;'''
parameters = (uid, other_uid)
result = sqldb.do_sql(cur, query, parameters)
if result is not None and len(result) > 0:
session['cid'] = result[0][0]
return app.response_class(status=200)
query = 'INSERT INTO Conversations (fpath) VALUES (?);'
parameters = ('', )
sqldb.do_sql(cur, query, parameters)
cid = cur.lastrowid
new_fpath = f'{CONVERSATION_ROOT}/{cid}'
query = 'UPDATE Conversations SET fpath = ? WHERE id = ?;'
parameters = (new_fpath, cid)
sqldb.do_sql(cur, query, parameters)
query = '''INSERT INTO UsersConversationsJoin (userId, conversationId)
VALUES (?,?);'''
sqldb.do_sql(cur, query, (uid, cid))
sqldb.do_sql(cur, query, (other_uid, cid))
conn.commit()
session['cid'] = cid
return app.response_class(status=200)
def login():
conn = sqldb.try_open_conn()
assert conn is not None
cur = conn.cursor()
username = request.values.get('username', None)
password = request.values.get('password', None)
if username is None or password is None:
return app.response_class(status=400)
query = 'SELECT userId, hash, salt FROM UserAuth WHERE username LIKE ?;'
parameters = (username, )
matching_users = sqldb.do_sql(cur, query, parameters)
print(matching_users)
for uid, user_hash, user_salt in matching_users:
if crypto.verify_secret(password, user_hash, user_salt):
session['uid'] = uid
return app.response_class(status=200)
return app.response_class(status=401)
# fill the remainder of the match list with random matches
previously_matched = set(best_matches)
while len(matches) < n and 0 < len(random_matches):
random = random_matches.pop()[0]
if random not in previously_matched:
previously_matched.add(random)
matches.append((random, 0))
break
return matches
if __name__ == '__main__':
conn = sqldb.try_open_conn()
cur = conn.cursor()
select_interests = '''SELECT interestId FROM UsersInterestsJoin
INNER JOIN Users ON UsersInterestsJoin.userId = Users.id
INNER JOIN Interests ON UsersInterestsJoin.interestId = Interests.id
WHERE userId LIKE ?;'''
user = sqldb.do_sql(cur, 'SELECT id FROM Users;')[2]
user_interests = sqldb.do_sql(cur, select_interests, (user[0],))
print(f'Finding 1 random match for user {user}')
print(n_rand_matches(cur, user[0], 1))
print(f'Finding 10 random match for user {user}')
print(n_rand_matches(cur, user[0], 10))
def update_profile():
conn = sqldb.try_open_conn()
assert conn is not None
cur = conn.cursor()
uid = session.get('uid')
print(request.values)
print(request.files)
name = request.values.get('name')
dob = request.values.get('dob')
interests = request.values.get('interests')
biography = request.values.get('biography')
gender = request.values.get('gender')
picture = request.files.get('profilePictureUpload').read()
if load_user_profile(cur, uid) is None:
return app.response_class(status=400)
query = 'UPDATE Users SET bio = ?, gender = ? WHERE id LIKE ?;'
parameters = (biography, gender, uid)
sqldb.do_sql(cur, query, parameters)
if picture != b'':
query = 'SELECT pictureId FROM Users WHERE id LIKE ?;'
parameters = (uid, )
picture_id = sqldb.do_sql(cur, query, parameters)[0][0]
if picture_id == sqldb.DEFAULT_PICTURE_ID:
query = 'INSERT INTO UserPictures (data) VALUES (?);'
parameters = (picture, )
sqldb.do_sql(cur, query, parameters)
new_picture_id = cur.lastrowid
query = 'UPDATE Users SET pictureId = ? WHERE id LIKE ?;'
parameters = (new_picture_id, uid)
sqldb.do_sql(cur, query, parameters)
else:
query = 'UPDATE UserPictures SET data = ? WHERE id LIKE ?;'
parameters = (picture, picture_id)
result = sqldb.do_sql(cur, query, parameters)
if interests is not None and interests != '':
interest_names = set(interests.split(','))
interest_ids = set()
query = 'SELECT id FROM Interests WHERE name LIKE ?;'
for name in interest_names:
i = sqldb.do_sql(cur, query, (name, ))
if i is not None and len(i) == 1:
interest_ids.add(i[0][0])
query = 'SELECT interestId FROM UsersInterestsJoin WHERE userId LIKE ?;'
existing_interests = sqldb.do_sql(cur, query, (uid, ))
existing_interests = set([x[0] for x in existing_interests])
added_interests = interest_ids.difference(existing_interests)
removed_interests = existing_interests.difference(interest_ids)
for interest_id in added_interests:
query = 'INSERT INTO UsersInterestsJoin (userId, interestId) VALUES (?,?);'
sqldb.do_sql(cur, query, (uid, interest_id))
for interest_id in removed_interests:
query = 'DELETE FROM UsersInterestsJoin WHERE userId LIKE ? AND interestId LIKE ?;'
sqldb.do_sql(cur, query, (uid, interest_id))
elif interests is not None and interests == '':
query = 'DELETE FROM UsersInterestsJoin WHERE userId LIKE ?;'
sqldb.do_sql(cur, query, (uid, ))
conn.commit()
# return the newly updated user profile
uid, name, dob, gender, bio, picture_base64_bytes, interest_names = load_user_profile(
cur, uid)
return jsonify({
'uid': uid,
'username': name,
'dob': dob,
'gender': gender,
'biography': bio,
'pictureBase64Src': picture_base64_bytes.decode('utf-8'),
'interests': interest_names,
})