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)