Example #1
0
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)
Example #2
0
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)
Example #3
0
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)
Example #4
0
def load_user_profile(cur, uid) -> Optional:
    query = 'SELECT * FROM Users WHERE id LIKE ?;'
    parameters = (uid, )
    user_profile = sqldb.do_sql(cur, query, parameters)

    if user_profile is None or len(user_profile) == 0:
        return None

    uid, name, dob, gender, bio, picture_id = user_profile[0]

    query = 'SELECT data FROM UserPictures WHERE id LIKE ?;'
    parameters = (picture_id, )
    picture = sqldb.do_sql(cur, query, parameters)[0][0]
    picture_base64_bytes = base64.b64encode(picture)

    query = '''SELECT interestId FROM UsersInterestsJoin 
    INNER JOIN Users ON UsersInterestsJoin.userId = Users.id 
    INNER JOIN Interests ON UsersInterestsJoin.interestId = Interests.id 
    WHERE userId LIKE ?;'''
    parameters = (uid, )
    interests = sqldb.do_sql(cur, query, parameters)

    query = 'SELECT name FROM Interests WHERE id LIKE ?;'
    interest_names = [None] * len(interests)
    for idx, interest in enumerate(interests):
        interest_names[idx] = sqldb.do_sql(cur, query, interest)[0][0]

    return uid, name, dob, gender, bio, picture_base64_bytes, interest_names
Example #5
0
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])
Example #6
0
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)
Example #7
0
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)
Example #8
0
def n_rand_matches(cur, uid: int, n: int = 1) -> List[Tuple[int]]:
    """
    Fetches n other random users. The returned list may be smaller than 
    n (if not enough random matches exist to fill it).
    """
    
    query = '''SELECT userId FROM UsersInterestsJoin WHERE userId <> ?;'''

    others = sqldb.do_sql(cur, query, (uid,))

    if others is None:
        return []

    others = set(others)

    # using a set deduplicates the user list for us, but we have to convert 
    # back to a list to pass to random.sample without a deprecation warning
    return random.sample(list(others), min(n, len(others)))
Example #9
0
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])
Example #10
0
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)
Example #11
0
def n_best_matches(cur, uid: int, user_interests: List[int], n: int = 1) -> List[Tuple[int, int]]:
    """
    Fetches the n other users who have the most matching interests from the 
    given list. The list is of the format [(user-id, matching_interests)*], 
    and may be smaller than n (if not enough matches exist to fill it).
    """
    user_matching_interests = {}

    interest_category = '''SELECT categoryId FROM Interests
    INNER JOIN InterestCategories ON Interests.categoryId = InterestCategories.id
    WHERE Interests.id LIKE ?;'''

    user_interest_categories = set()
    for interest in user_interests:
        category = sqldb.do_sql(cur, interest_category, (interest,))[0][0]
        if category not in user_interest_categories:
            user_interest_categories.add(category)

    matching_categories = '''SELECT userId FROM UsersInterestsJoin
    INNER JOIN Users ON UsersInterestsJoin.userId = Users.id
    INNER JOIN Interests ON UsersInterestsJoin.interestId = Interests.id
    INNER JOIN InterestCategories ON Interests.categoryId = InterestCategories.id
    WHERE userId <> ? AND categoryId LIKE ?;'''

    matching_interests = '''SELECT userId FROM UsersInterestsJoin
    INNER JOIN Users ON UsersInterestsJoin.userId = Users.id 
    INNER JOIN Interests ON UsersInterestsJoin.interestId = Interests.id
    INNER JOIN InterestCategories ON Interests.categoryId = InterestCategories.id
    WHERE userId <> ? AND interestId LIKE ?;'''

    matched_interest_score = 10
    matched_category_score = 1

    # search based on interest categories (to establish a baseline)
    for category in user_interest_categories:
        matching_users = sqldb.do_sql(cur, matching_categories, (uid, category))

        if matching_users is not None:
            matching_users = set(matching_users)

            for other in matching_users:
                other_id = other[0]

                old_score = user_matching_interests.get(other_id, 0)
                new_score = old_score + matched_category_score
                user_matching_interests[other_id] = new_score

    # search based on more granular interest
    for interest in user_interests:
        matching_users = sqldb.do_sql(cur, matching_interests, (uid, interest))

        if matching_users is not None:
            matching_users = set(matching_users)

            for other in matching_users:
                other_id = other[0]

                old_score = user_matching_interests.get(other_id, 0)
                new_score = old_score + matched_interest_score
                user_matching_interests[other_id] = new_score

    random_matches = n_rand_matches(cur, uid, n)

    score_key = lambda user: user_matching_interests[user]
    best_matches = sorted(user_matching_interests, key=score_key, reverse=True)[:n]

    matches = [(user, user_matching_interests[user]) for user in best_matches]

    # 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
Example #12
0
            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))

    print(f'Finding 3 best matches for user {user} with interests {user_interests}')
    print(n_best_matches(cur, user[0], [interest[0] for interest in user_interests], 3))

    print(f'Finding 10 best matches for user {user} with interests {user_interests}')
    print(n_best_matches(cur, user[0], [interest[0] for interest in user_interests], 10))

Example #13
0
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,
    })