def delete_relationship(user_id1, user_id2, relation_type): handled_execute(db_conn, """ DELETE FROM %s WHERE firstUserId = %s AND secondUserId = %s; """, (AsIs(relation_type), user_id1, user_id2)) return True
def create_relationship(user_id1, user_id2, relation_type): handled_execute(db_conn, """ INSERT INTO %s (firstUserId, secondUserId) VALUES (%s, %s) """, (AsIs(relation_type), user_id1, user_id2)) return True
def search_user_by_name(current_user_id, query, limit):
cursor = handled_execute(db_conn, """
SELECT userId,firstName,lastName,nickName,portrait,
(userId IN (SELECT secondUserId FROM userFriends WHERE firstUserId=%(current_user)s)) AS isRequestSent,
(userId IN (SELECT firstUserId FROM userFriends WHERE secondUserId=%(current_user)s)) AS isRequestPending,
(userId IN (SELECT secondUserId FROM userFollows WHERE firstUserId=%(current_user)s)) AS isFollowing
FROM users
WHERE concat(firstName, ' ', lastName) LIKE %(query)s
LIMIT %(limit)s;
""", {"query" : "%" + query + "%", "limit" : limit, "current_user" : current_user_id})
user_rows = cursor.fetchall()
returned_users = []
for user_row in user_rows:
(user_id, first_name, last_name, nick_name,portrait,
is_request_sent, is_request_pending, is_following) = user_row
user_friend_status = "none"
if is_request_sent and is_request_pending:
user_friend_status = "friends"
elif is_request_sent:
user_friend_status = "sent"
elif is_request_pending:
user_friend_status = "pending"
returned_users.append(UserSearchEntry(user_id, first_name + ' ' + last_name, portrait, is_following, user_friend_status))
return returned_users
def get_replies_with_post_ids(post_ids, current_user_id):
cursor = handled_execute(db_conn, """
SELECT
replyId, postId, authorId, replyBody, replyDate, cityName, longitude, latitude, privacy, edited,
firstName, lastName, nickName, portrait, showLastName,
(authorId IN (SELECT firstUserId FROM userFriends WHERE secondUserId = %(current_user_id)s)
AND %(current_user_id)s IN (SELECT firstUserId FROM userFriends WHERE secondUserId = authorId)) AS areFriends
FROM replies LEFT JOIN users ON replies.authorId = users.userId
WHERE postId = ANY(%(post_ids)s)
ORDER BY replyId ASC;
""", {"current_user_id" : current_user_id, "post_ids" : post_ids})
rows = cursor.fetchall()
replies = []
for row in rows:
(reply_id, post_id, author_id, reply_body, reply_date, city_name, longitude, latitude, privacy, edited,
first_name, last_name, nick_name, portrait, show_last_name, are_friends) = row
author_name = build_name(first_name, nick_name, last_name, are_friends, show_last_name)
new_location = build_location(longitude, latitude, city_name, are_friends, privacy != POST_PRIVACY.HIDE_LOCATION)
new_reply = Reply(post_id, author_id, author_name, portrait, reply_body, reply_date, new_location, privacy, edited)
new_reply.reply_id = reply_id
replies.append(new_reply)
return replies
def create_external_link(userId, platform, externalId): cursor = handled_execute(db_conn, """ INSERT INTO platformLink (userId, loginPlatform, externalId) VALUES (%s, %s, %s); """, (userId, platform, externalId)) return userId
def update_user(user_obj): cursor = handled_execute(db_conn, """UPDATE users SET email=%s, phone=%s, firstName=%s, lastName=%s, nickName=%s, portrait=%s, showLastName=%s, searchableByName=%s, useBrowserGeolocation=%s WHERE userId=%s;""", (user_obj.email, user_obj.phone, user_obj.first_name, user_obj.last_name, user_obj.nick_name, user_obj.portrait, user_obj.preferences.show_last_name, user_obj.preferences.name_search, user_obj.preferences.browser_geo, user_obj.user_id)) return user_obj
def get_post_by_id(current_user_id, post_id):
cursor = handled_execute(db_conn, """
SELECT
postId, authorId, postBody, postDate, privacy, cityName, longitude, latitude,
eventId, eventName, eventLocation, eventStart, eventEnd,
imageId,
firstName, lastName, nickName, portrait, showLastName,
(SELECT COUNT(likes.likerId) FROM likes WHERE likes.postId = posts.postId GROUP BY likes.postId) AS likeCount,
(authorId IN (SELECT userId FROM likes WHERE likes.postId = posts.postId)) AS liked,
(authorId IN (SELECT firstUserId FROM userFriends WHERE secondUserId = %(current_user_id)s)) AS requestPending,
(%(current_user_id)s IN (SELECT firstUserId FROM userFriends WHERE secondUserId = authorId)) AS requestSent,
(authorId IN (SELECT firstUserId FROM userFollows WHERE secondUserId = %(current_user_id)s)) AS follower,
(%(current_user_id)s IN (SELECT firstUserId FROM userFollows WHERE secondUserId = authorId)) AS following
FROM posts LEFT JOIN users ON posts.authorId = users.userId
WHERE postId = %(searched_post_id)s
AND (privacy != 'friends' OR authorId=%(current_user_id)s
OR (authorId IN (SELECT firstUserId FROM userFriends WHERE secondUserId = %(current_user_id)s)
AND %(current_user_id)s IN (SELECT firstUserId FROM userFriends WHERE secondUserId = authorId)))
ORDER BY postId DESC
LIMIT 1;""", {
"searched_post_id" : post_id, "current_user_id" : current_user_id
})
row = cursor.fetchone()
(post_id, author_id, post_body, post_date, privacy, city_name,
longitude, latitude, event_id, event_name, event_location, event_start,
event_end, image_id, first_name, last_name, nick_name, portrait, show_last_name,
likes, liked, request_pending, request_sent, follower, following) = row
if likes == None:
likes = 0
are_friends = request_sent and request_pending
author_name = build_name(first_name, nick_name, last_name, are_friends, show_last_name)
post_location = build_location(longitude, latitude, city_name, are_friends, privacy != POST_PRIVACY.HIDE_LOCATION)
if(event_id != None):
new_post = EventPost(author_id, author_name, portrait, post_body, post_date,
privacy, post_location, event_id,
event_name, event_location, event_start, event_end)
elif(image_id != None):
new_post = ImagePost(author_id, author_name, portrait, post_body, post_date,
privacy, post_location, image_id)
else:
new_post = Post(author_id, author_name, portrait, post_body, post_date,
privacy, post_location, likes, liked)
new_post.post_id = post_id
replies = reply_dao.get_replies_by_post_id(post_id, current_user_id)
new_post.replies = replies
return new_post
def get_credentials_by_field(field_name, field_value):
cursor = handled_execute(db_conn, """SELECT userId, hash, salt from users WHERE %s=%s;
""",(AsIs(field_name), field_value))
row = cursor.fetchone()
if row != None:
userId, password_hash, salt = row
return UserCredentials(userId, password_hash, salt)
else:
raise DAOException("No user found in database with field " + str(field_name) + " with value " + str(field_value) + " when searching credentials")
def create_reply(reply): cursor = handled_execute(db_conn, """ INSERT INTO replies (postId, authorId, replyBody, replyDate, cityName, longitude, latitude, privacy, edited) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s) RETURNING replyId; """, (reply.post_id, reply.author_id, reply.reply_body, reply.reply_date, reply.city, reply.longitude, reply.latitude, reply.privacy, reply.edited)) new_reply_id = cursor.fetchone()[0] reply.reply_id = new_reply_id return reply
def create_notification(notification): cursor = handled_execute(db_conn, """ INSERT INTO notifications (notifiedId, notifiedDate, seen, notifyType, targetId) VALUES (%s, %s, %s, %s, %s) RETURNING notificationId; """, (notification.notified_id, notification.notified_date, notification.seen, notification.notify_type, notification.target_id)) notification_id = cursor.fetchone()[0] notification.notification_id = notification_id return notification
def create_post(post): cursor = handled_execute(db_conn, """ INSERT INTO posts (authorId, postBody, postDate, privacy, cityName, longitude, latitude) VALUES (%s, %s, %s, %s, %s, %s, %s) RETURNING postId; """, (post.author_id, post.body, post.post_date, post.privacy, post.city, post.longitude, post.latitude)) last_id = cursor.fetchone()[0] post.post_id = last_id return post
def create_profile_picture(profile_pic_obj): cursor = handled_execute(db_conn, """ INSERT INTO profilePictures (uploadedPictureId, setDate) VALUES (%s, %s) RETURNING profilePictureId; """, (profile_pic_obj.uploaded_picture_id, profile_pic_obj.set_date)) profile_pic_id = cursor.fetchone()[0] profile_pic_obj.profile_picture_id = profile_pic_id return profile_pic_obj
def get_posts_by_user(current_user_id, searched_user, friends, limit, offset, max_id):
cursor = handled_execute(db_conn, """
SELECT
postId, authorId, postBody, postDate, privacy, cityName, longitude, latitude,
eventId, eventName, eventLocation, eventStart, eventEnd,
imageId,
(SELECT COUNT(likes.likerId) FROM likes WHERE likes.postId = posts.postId GROUP BY likes.postId) AS likeCount,
(authorId IN (SELECT likerId FROM likes WHERE likes.postId = posts.postId)) AS liked
FROM posts
WHERE authorId=%(searched_user_id)s
AND (privacy != 'friends' OR %(friends)s = True)
AND (%(max_id)s IS NULL OR postId <= %(max_id)s)
ORDER BY postId DESC
LIMIT %(limit)s OFFSET %(offset)s;
""", { "searched_user_id" : searched_user.user_id, "friends" : friends, "max_id" : max_id, "limit" : limit, "offset" : offset})
post_rows = cursor.fetchall()
post_objects = []
post_ids = []
for row in post_rows:
(post_id, author_id, post_body, post_date, privacy, city_name,
longitude, latitude, event_id, event_name, event_location, event_start,
event_end, image_id, likes, liked) = row
if likes == None:
likes = 0
author_name = build_name(searched_user.first_name, searched_user.nick_name, searched_user.last_name, friends, searched_user.preferences.show_last_name)
post_location = build_location(longitude, latitude, city_name, friends, privacy != POST_PRIVACY.HIDE_LOCATION)
if(event_id != None):
new_post = EventPost(author_id, author_name, searched_user.portrait, post_body, post_date,
privacy, post_location, event_id,
event_name, event_location, event_start, event_end)
elif(image_id != None):
new_post = ImagePost(author_id, author_name, searched_user.portrait, post_body, post_date,
privacy, post_location, image_id)
else:
new_post = Post(author_id, author_name, searched_user.portrait, post_body, post_date,
privacy, post_location, likes, liked)
new_post.post_id = post_id
post_ids.append(post_id)
post_objects.append(new_post)
build_replies(post_ids, post_objects, current_user_id)
return post_objects
def update_reply(reply):
if reply.reply_id == -1:
raise DAOException("Need to create reply before updating")
cursor = handled_execute(db_conn, """
UPDATE replies SET
postId = %s, authorId = %s, replyBody = %s, replyDate = %s, cityName = %s,
longitude = %s, latitude = %s, privacy = %s, edited = %s
WHERE replyId = %s;
""", (reply.post_id, reply.author_id, reply.author_name, reply.reply_body,
reply.reply_date, reply.city, reply.longitude, reply.latitude, reply.privacy,
reply.edited, reply.reply_id))
return reply
def create_user_by_field(user_obj, field_name, field_value, password_hash, salt): cursor = handled_execute(db_conn, """INSERT INTO users (%s, hash, salt, firstName, lastName, nickName, portrait, showLastName, searchableByName, useBrowserGeolocation) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s) RETURNING userId;""", (AsIs(field_name), field_value, password_hash, salt, user_obj.first_name, user_obj.last_name, user_obj.nick_name, user_obj.portrait, user_obj.preferences.show_last_name, user_obj.preferences.name_search, user_obj.preferences.browser_geo)) last_id = cursor.fetchone()[0] user_obj.user_id = last_id return user_obj
def get_profile_picture_by_id(picture_id): cursor = handled_execute(db_conn, """ SELECT profilePictureId, uploadedPictureId, setDate FROM profilePictures WHERE profilePictureId=%s; """, (picture_id,)) profile_picture = None if cursor.rowcount > 0: (profile_pic_id, uploaded_pic_id, set_date) = cursor.fetchone() profile_picture = ProfilePicture(uploaded_pic_id, set_date) profile_picture.profile_picture_id = profile_pic_id return profile_picture
def get_notification_existing_id(notified_id, notify_type, target_id, date, notifier_id): cursor = handled_execute(db_conn, """ SELECT notificationId, EXISTS( SELECT notificationId FROM notificationLink WHERE notificationLink.notificationId = notifications.notificationId AND notificationLink.userId=%s ) AS isLinked FROM notifications WHERE notifiedId=%s AND targetId=%s AND seen=FALSE AND notifyType=%s AND notifiedDate > %s """, (notifier_id, notified_id, target_id, notify_type, date)) if cursor.rowcount > 0: return cursor.fetchone() else: return None, False
def get_friendship_status(user_id1, user_id2):
cursor = handled_execute(db_conn, """
SELECT EXISTS(SELECT firstUserId FROM userFriends WHERE firstUserId = %(first_user_id)s AND secondUserId = %(second_user_id)s),
EXISTS(SELECT firstUserId FROM userFriends WHERE firstUserId = %(second_user_id)s AND secondUserId = %(first_user_id)s);
""", { "first_user_id" : user_id1, "second_user_id" : user_id2 })
sent, pending = cursor.fetchone()
if sent and pending == 2:
return Friendship.FRIENDS
elif sent:
return Friendship.SENT
elif pending:
return Friendship.PENDING
else:
return Friendship.NOTHING
def get_external_link(platform, externalId): cursor = handled_execute(db_conn, """ SELECT userId FROM platformLink WHERE loginPlatform=%s AND externalId=%s; """, (platform, externalId)) db_conn.commit() row = db_conn.fetchone() if row != None: return row[0] else: # Do we want to raise an exception here or do we want to return None? # In isolation, returning None makes more sense, but for the sake of # consistency, it may make more sense to raise an exception return None
def get_users_by_ids(user_ids): cursor = handled_execute(db_conn, """ SELECT userId,email,phone,firstName,lastName,nickName,portrait,biography, showLastName,searchableByName,useBrowserGeolocation FROM users WHERE userId = ANY (%s)""", (user_ids,)) user_rows = cursor.fetchall() user_objs = [] for row in user_rows: (user_id, email, phone, first_name, last_name, nick_name, portrait, biography, show_last_name, name_search, browser_geo) = row user_prefs = UserPreferences(show_last_name, name_search, browser_geo) user_obj = User(email, phone, first_name, last_name, nick_name, portrait, biography, user_prefs) user_obj.user_id = user_id user_objs.append(user_obj) return user_objs
def get_user_by_field(field, value):
cursor = handled_execute(db_conn, """
SELECT userId,email,phone,firstName,lastName,nickName,portrait,biography,
showLastName,searchableByName,useBrowserGeolocation
FROM users WHERE %s=%s;""", (AsIs(field), value))
user_row = cursor.fetchone()
returned_user = None
if(user_row != None):
(user_id, email, phone, first_name, last_name, nick_name, portrait, biography,
show_last_name, name_search, browser_geo) = user_row
returned_prefs = UserPreferences(show_last_name, name_search, browser_geo)
returned_user = User(email, phone, first_name, last_name, nick_name, portrait, biography, returned_prefs)
returned_user.user_id = user_id
else:
raise DAOException("No user found in database with field " + str(field) + " with value " + str(value))
return returned_user
def build_notification_links(notification_ids, notification_objs):
cursor = handled_execute(db_conn, """
SELECT notificationId, notificationLink.userId, firstName, lastName, nickName, portrait, showLastName
FROM notificationLink LEFT JOIN users ON notificationLink.userId = users.userId
WHERE notificationId = ANY(%s);
""", (notification_ids,))
rows = cursor.fetchall()
notification_links = {}
for row in rows:
(notification_id, user_id, first_name, last_name, nick_name, portrait, show_last_name) = row
name = build_name(first_name, nick_name, last_name, False, show_last_name)
links = notification_links.get(notification_id, [])
links.append(UserSummary(user_id, name, portrait))
notification_links[notification_id] = links
for notification in notification_objs:
notification.notification_links = notification_links.get(notification.notification_id, [])
def get_notifications_by_user_id(user_id): cursor = handled_execute(db_conn, """ SELECT notificationId, notifiedId, notifiedDate, seen, notifyType, targetId FROM notifications WHERE notifiedId = %s ORDER BY notificationId DESC; """, (user_id,)) rows = cursor.fetchall() notification_objs = [] notification_ids = [] for row in rows: (notification_id, notified_id, notified_date, seen, notify_type, target_id) = row notification = Notification(notified_id, notified_date, notify_type, target_id) notification.notification_id = notification_id notification.seen = seen notification_objs.append(notification) notification_ids.append(notification_id) build_notification_links(notification_ids, notification_objs) return notification_objs
def delete_reply_by_id(reply_id, author_id): cursor = handled_execute(db_conn, """ DELETE FROM replies WHERE replyId=%s AND authorId=%s """, (reply_id, author_id)) return cursor.rowcount
def delete_replies_by_post_id(post_id): cursor = handled_execute(db_conn, """ DELETE FROM replies WHERE postId=%s """, (post_id,)) return cursor.rowcount
def trim_notifications(user_id, last_date): cursor = handled_execute(db_conn, """ DELETE FROM notifications WHERE notifiedId = %s AND seen = TRUE AND notifiedDate < %s """, (user_id, last_date)) return True
def delete_like(post_id, author_id): cursor = handled_execute(db_conn, """ DELETE FROM likes WHERE postId=%s AND likerId=%s """, (post_id, author_id)) return cursor.rowcount
def create_like(post_id, author_id): cursor = handled_execute(db_conn, """ INSERT INTO likes (postId, likerId) VALUES (%s, %s); """, (post_id, author_id)) return cursor.rowcount
def update_user_biography(user_obj, new_biography): cursor = handled_execute(db_conn, """UPDATE users SET biography=%s WHERE userId=%s""", (new_biography, user_obj.user_id)) return True
def update_user_credentials(user_obj, new_hash, new_salt): cursor = handled_execute(db_conn, """UPDATE users SET hash=%s, salt=%s WHERE userId=%s;""", (new_hash, new_salt, user_obj.user_id)) return True
