def update_settings(uid, value):
db.get_db().execute(
'''
UPDATE profile
SET distance_setting = %s
WHERE user_id = %s;
''', value, uid)
def update_profile(subject, uid):
db.get_db().execute(
'''
UPDATE profile
SET nickname= %s, bio= %s, email= %s, phone= %s, photo= %s
WHERE user_id= %s;
''', subject['nickname'], subject['bio'], subject['email'],
subject['phone'], subject['photo'], uid)
def test_get_close_db(api):
with api.app_context():
db = dbc.get_db()
assert db is dbc.get_db()
with pytest.raises(sqlalchemy.exc.StatementError) as e:
db.execute('SELECT 1')
assert 'closed' in str(e.value)
def update_user_hobbies(uid, request):
hobbies = request["hobbies"]
for category in hobbies:
db.get_db().execute(
'''
DELETE FROM user_hobbies
WHERE user_id = %s
AND swap = %s;
''', uid, category['container'])
for hobby in category['hobbies']:
db.get_db().execute(
'''
INSERT INTO user_hobbies (user_id, hobby_id, swap)
VALUES (%s, %s, %s);
''', uid, hobby['id'], category['container'])
def get_matches(subject):
rows = db.get_db().execute(
'''
SELECT
profile.*,
MAX(time) AS time,
MAX(COALESCE(rating.rate_value, - 1)) AS rating,
MAX(COALESCE(distance.distance,-1)) AS distance
FROM (
SELECT user_b AS user_id, time
FROM matches
WHERE user_a = %s
AND unmatch = false
UNION
SELECT user_a AS user_id, time
FROM matches
WHERE user_b = %s
AND unmatch = false
) matches
LEFT JOIN profile ON profile.user_id = matches.user_id
LEFT JOIN rating ON matches.user_id = rating.user_to AND rating.user_from = %s
LEFT JOIN distance_table(%s) AS distance ON matches.user_id = distance.user_id
GROUP BY profile.user_id
ORDER BY time DESC
''', subject, subject, subject, subject)
matches = []
for row in rows:
matches.append(build_profile_data(row, 2))
return {"matches": matches}
def retrieve_settings(uid):
rows = db.get_db().execute(
'''
SELECT distance_setting
FROM profile
WHERE user_id = %s;
''', uid)
return {"distance_setting": rows.first()['distance_setting']}
def unmatch(uid, id):
rows = db.get_db().execute(
'''
UPDATE matches
SET unmatch = TRUE
WHERE user_a = %s
AND user_b = %s
OR user_a = %s
AND user_b = %s;
''', uid, id, id, uid)
def retrieve_profile(subject):
# Retrieves user info.
rows = db.get_db().execute(
'''
SELECT *, -1 as rating
FROM profile
WHERE user_id = %s
''', subject)
return build_profile_data(rows.first(), 3)
def make_user(name, email, uid):
rows = db.get_db().execute(
'''
INSERT INTO profile (nickname, email, user_id) VALUES (%s, %s, %s) RETURNING user_id;
''', name, email, uid)
for row in rows:
print(str(row['user_id']))
return str(row['user_id'])
print('IOError: No Rows')
raise IOError
def rate_user(uid, id, value):
if value is None:
row = db.get_db().execute(
'''
SELECT COUNT(*) as c
FROM rating
WHERE user_from = %s
AND user_to = %s;
''', uid, id).first()
if row['c'] > 0:
db.get_db().execute(
'''
DELETE FROM rating WHERE user_from = %s AND user_to = %s;
''', uid, id)
print('deleted')
else:
db.get_db().execute(
'''
INSERT INTO rating (user_to, user_from, rate_value) VALUES (%s, %s, %s);
''', id, uid, int(value))
def verify_user(subject):
rows = db.get_db().execute(
'''
SELECT COUNT(user_id)
FROM profile
WHERE user_id = %s;
''', subject)
for row in rows:
if row['count'] == 1:
return True
return False
def get_hobbies(subject):
# Retrieves hobbies
rows = db.get_db().execute(
'''
SELECT h.hobby_id, h.name, uh.swap
FROM profile
JOIN user_hobbies uh
ON profile.user_id = uh.user_id
JOIN hobbies h
ON uh.hobby_id = h.hobby_id
WHERE profile.user_id = %s;
''', subject)
return build_hobby_data(rows)
def get_recommendation_profile(user_id, my_id):
row = db.get_db().execute(
'''
SELECT *, -1 as rating, (
SELECT distance FROM distance_table(%s)
WHERE user_id = %s
LIMIT 1
)
FROM profile
WHERE user_id = %s;
''', my_id, user_id, user_id
).first()
return build_profile_data(row, 1)
def like_user(uid, id, vote):
######
# TESTING
# REMOVE FOR PRODUCTION
#####
# Initialises random matches
import random
if id.startswith('testaccount') and random.choice([0, 0, 1]) == 1:
db.get_db().execute(
'''
INSERT INTO votes (time, vote, user_from, user_to)
VALUES (now(), %s, %s, %s)
RETURNING *;
''', vote, id, uid
)
# End of testing block
rows = db.get_db().execute(
'''
INSERT INTO votes (time, vote, user_from, user_to)
VALUES (now(), %s, %s, %s)
RETURNING *;
''', vote, uid, id
)
if rows.first() is None:
print('MATCH')
notify_match(id)
matches = [retrieve_profile(id)]
return {
"match": True,
"matched": matches,
}
return {
"match": False,
"matched": [],
}
def get_hobby_list():
try:
rows = db.get_db().execute('''
SELECT * FROM hobbies ORDER BY name;
''')
hobbies = []
for row in rows:
hobby = {
"id": row['hobby_id'],
"name": row["name"],
}
hobbies.append(hobby)
hobbies_list = {'hobby_list': hobbies}
return hobbies_list
except Exception as e:
raise IOError(str(e))
def get_matched_hobbies(uid, id):
try:
rows = db.get_db().execute(
'''
SELECT DISTINCT(me.hobby_id), 'matched' AS swap, (
SELECT name
FROM hobbies
WHERE me.hobby_id = hobbies.hobby_id
)
FROM user_hobbies me
INNER JOIN user_hobbies you
ON me.hobby_id = you.hobby_id
AND me.swap != you.swap
WHERE me.user_id = %s
AND you.user_id = %s;
''', uid, id)
return build_hobby_data(rows)
except Exception as e:
print(str(e))
raise IOError(str(e))
def get_match_by_id(uid, id):
print(uid, id)
# EXTRACT(year FROM age(current_date, dob)) :: INTEGER AS age # If we need age calculation
rows = db.get_db().execute(
'''
SELECT *,
CASE WHEN rating.rate_value is NULL THEN -1 ELSE rating.rate_value END AS rating
FROM profile
FULL OUTER JOIN rating
ON user_id = user_to
AND user_from = %s
WHERE user_id = %s
AND user_id IN (
SELECT user_id FROM matches
WHERE user_a = %s
AND user_b = %s
OR user_b = %s
AND user_a = %s
);
''', uid, id, uid, id, uid, id)
return build_profile_data(rows.first(), 3)
def get_recommendations(uid):
top_users = []
rows = db.get_db().execute(
'''
SELECT * FROM matching_algorithm(%s);
''', uid
)
for row in rows:
entry = [row['user_id'], calculate_compatibility(row)]
top_users.append(tuple(entry))
top_users.sort(key=lambda top: top[1], reverse=True)
top_users = top_users[:20]
# print(top_users)
recommendations = []
for user in top_users:
recommendations.append(get_recommendation_profile(user[0], uid))
return {
"recommendations": recommendations
}
def notify_match(id):
row = db.get_db().execute(
'''
SELECT device_id
FROM location_data
WHERE user_id = %s
ORDER BY ping_time DESC
LIMIT 1;
''', id
).first()
# This registration token comes from the client FCM SDKs.
registration_token = row['device_id']
if registration_token is None:
print('Message not sent: No device token')
return
if registration_token == "":
print('Message not sent: No device token')
return
notification = messaging.Notification(title='You have a new match!')
# See documentation on defining a message payload.
message = messaging.Message(notification= notification, token= registration_token)
try:
# Send a message to the device corresponding to the provided
# registration token.
response = messaging.send(message)
# Response is a message ID string.
print(response)
print('Successfully sent message:', response)
except ValueError as e:
print(str(e))
return 'Failed: ' + str(e), 500
def delete_account(uid):
db.get_db().execute(
'''
DELETE FROM profile WHERE user_id = %s;
''', uid)
def set_location(uid, lat, long, dev):
db.get_db().execute(
'''
INSERT INTO location_data (user_id, lat, long, device_id) VALUES (%s, %s, %s, %s)
''', uid, float(lat), float(long), dev)