def get_picture_by_id(picture_id, current_user_id):
cursor = handled_execute(db_conn, """
SELECT
pictureId, authorId, uploadedDate, filename,
photoTitle, photoDescription, cityName, longitude, latitude, privacy,
(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 pictures
WHERE pictureId=%(picture_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)))
""", { "picture_id" : picture_id, "current_user_id" : current_user_id })
result_row = cursor.fetchone()
picture = None
if result_row != None:
(picture_id, author_id, uploaded_date, filename,
title, descr, city, longitude, latitude, privacy, friends) = result_row
taken_location = build_location(longitude, latitude, city, friends, privacy != "hide_location")
picture = UploadedPicture(author_id, uploaded_date, filename, taken_location, title, descr, privacy)
picture.picture_id = picture_id
return picture
def get_pictures_by_user(searched_user_id, current_user_id, limit, offset, max_id):
cursor = handled_execute(db_conn, """
SELECT
pictureId, authorId, uploadedDate, filename,
photoTitle, photoDescription, cityName, longitude, latitude, privacy,
(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 pictures
WHERE authorId=%(author_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)))
AND (%(max_id)s IS NULL OR pictureId < %(max_id)s)
LIMIT %(limit)s OFFSET %(offset)s
""", { "author_id" : searched_user_id, "current_user_id" : current_user_id, "max_id" : max_id, "limit" : limit, "offset" : offset })
result_rows = cursor.fetchall()
picture_objs = []
for result_row in result_rows:
(picture_id, author_id, uploaded_date, filename,
title, descr, city, longitude, latitude, privacy, friends) = result_row
taken_location = build_location(longitude, latitude, city, friends, privacy != "hide_location")
picture = UploadedPicture(author_id, uploaded_date, filename, taken_location, title, descr, privacy)
picture.picture_id = picture_id
picture_objs.append(picture)
return picture_objs