def getFollowersList(email, since_id, order, limit): cursor = connection.cursor() try: query = ( "SELECT " "following_user.about AS about, " "following_user.email AS email, " "following_user.id AS id, " "following_user.isAnonymous AS isAnonymous, " "following_user.name AS name, " "following_user.username AS username " "FROM users JOIN followers ON users.id = followers.followee_id " "JOIN users AS following_user ON followers.follower_id = following_user.id " "WHERE users.email = %r AND followers.follows = TRUE " % (str(email)) ) if since_id: query += "AND following_user.id >= %s " % since_id if order: query += "ORDER BY following_user.name %s " % order if limit: query += "LIMIT %s " % limit cursor.execute(query) users = dictfetchall(cursor) for user in users: user["followers"] = __getFollowers(user["email"]) user["following"] = __getFollowees(user["email"]) user["subscriptions"] = __getSubscriptions(user["email"]) user["isAnonymous"] = bool(user["isAnonymous"]) except Exception: raise else: return users finally: cursor.close()
def getThreadsList(short_name, since, order, limit, related): cursor = connection.cursor() try: query = ( "SELECT " "threads.date AS date, " "threads.dislikes AS dislikes, " "forums.short_name AS forum, " "threads.id AS id, " "threads.isClosed AS isClosed, " "threads.isDeleted AS isDeleted, " "threads.likes AS likes, " "threads.message AS message, " "threads.likes - threads.dislikes AS points, " "COUNT(posts.id) AS posts, " "threads.slug AS slug, " "threads.title AS title, " "users.email AS user " "FROM forums LEFT JOIN threads ON forums.id = threads.forum_id " "JOIN users ON threads.user_id = users.id " "LEFT JOIN posts ON posts.thread_id = threads.id " "WHERE forums.short_name= %r " % str(short_name)) if since: query += "AND threads.date >= %r " % str(since) query += "GROUP BY forums.short_name, threads.slug " if order: query += "ORDER BY threads.date %s " % order if limit: query += "LIMIT %s " % limit cursor.execute(query) threads = dictfetchall(cursor) cursor.close() for thread in threads: thread['date'] = thread['date'].strftime("%Y-%m-%d %H:%M:%S") thread['isClosed'] = bool(thread['isClosed']) thread['isDeleted'] = bool(thread['isDeleted']) if 'user' in related: for thread in threads: from technopark_db_api_app.queries import user_queries thread['user'] = user_queries.getDetailedUser(thread['user']) if 'forum' in related: for thread in threads: thread['forum'] = getDetailedForum(thread['forum'], []) except Exception: raise else: return threads finally: cursor.close()
def __getSubscriptions(email): cursor = connection.cursor() try: cursor.execute( "SELECT subscribers.thread_id " "FROM users JOIN subscribers ON users.id = subscribers.user_id " "WHERE users.email = %s AND subscribed = TRUE", (email,), ) subscriptions = [item["thread_id"] for item in dictfetchall(cursor)] except Exception: raise else: return subscriptions finally: cursor.close()
def getPostsList(email, since, order, limit): cursor = connection.cursor() try: query = ( "SELECT " "posts.date AS date, " "posts.dislikes AS dislikes, " "forums.short_name AS forum, " "posts.id AS id, " "posts.isApproved AS isApproved, " "posts.isDeleted AS isDeleted, " "posts.isEdited AS isEdited, " "posts.isHighlighted AS isHighlighted, " "posts.isSpam AS isSpam, posts.likes AS likes, " "posts.message AS message, " "posts.post_id as parent, " "posts.likes - posts.dislikes AS points, " "threads.id AS thread, " "users.email AS user " "FROM posts RIGHT JOIN threads ON posts.thread_id = threads.id " "RIGHT JOIN forums ON threads.forum_id = forums.id " "JOIN users ON posts.user_id = users.id " "WHERE users.email=%r " "" % (str(email)) ) if since: query += "AND posts.date >= %r " % str(since) if order: query += "ORDER BY posts.date %s " % order if limit: query += "LIMIT %s " % limit cursor.execute(query) posts = dictfetchall(cursor) cursor.close() for post in posts: post["isApproved"] = bool(post["isApproved"]) post["isDeleted"] = bool(post["isDeleted"]) post["isEdited"] = bool(post["isEdited"]) post["isHighlighted"] = bool(post["isHighlighted"]) post["isSpam"] = bool(post["isSpam"]) post["date"] = post["date"].strftime("%Y-%m-%d %H:%M:%S") except Exception: raise else: return posts finally: cursor.close()
def __getFollowees(email): cursor = connection.cursor() try: cursor.execute( "SELECT followed_users.email " "FROM users JOIN followers ON users.id = followers.follower_id " "JOIN users AS followed_users ON followers.followee_id = followed_users.id " "WHERE users.email=%s AND followers.follows = TRUE", (email,), ) following = [item["email"] for item in dictfetchall(cursor)] except Exception: raise else: return following finally: cursor.close()
def getThreadsList(email, since, order, limit): cursor = connection.cursor() try: query = ( "SELECT " "threads.date AS date, " "threads.dislikes AS dislikes, " "forums.short_name AS forum, " "threads.id AS id, " "threads.isClosed AS isClosed, " "threads.isDeleted AS isDeleted, " "threads.likes AS likes, " "threads.message AS message, " "threads.likes - posts.dislikes AS points, " "COUNT(posts.id) AS posts, " "threads.slug AS slug, " "threads.title AS title, users.email AS user " "FROM forums LEFT JOIN threads ON forums.id = threads.forum_id " "JOIN users ON threads.user_id = users.id " "LEFT JOIN posts ON posts.thread_id = thread_id " "WHERE users.email=%r " % str(email) ) if since: query += "AND threads.date >= %r " % str(since) query += "GROUP BY forums.short_name, threads.slug " if order: query += "ORDER BY threads.date %s " % order if limit: query += "LIMIT %s " % limit cursor.execute(query) threads = dictfetchall(cursor) cursor.close() for thread in threads: thread["date"] = thread["date"].strftime("%Y-%m-%d %H:%M:%S") thread["isClosed"] = bool(thread["isClosed"]) thread["isDeleted"] = bool(thread["isDeleted"]) except Exception: raise else: return threads finally: cursor.close()
def getUsersList(short_name, since_id, order, limit): cursor = connection.cursor() try: query = ( "SELECT DISTINCT " "users.email AS email, " "users.id AS id, " "users.isAnonymous AS isAnonymous, " "users.name AS name, " "users.username AS username, " "users.about AS about " "FROM forums LEFT JOIN threads ON forums.id = threads.forum_id " "LEFT JOIN posts ON posts.thread_id = threads.id " "JOIN users ON posts.user_id = users.id " "WHERE forums.short_name=%r " % str(short_name) ) if since_id: query += "AND users.id >= %s " % since_id if order: query += "ORDER BY users.id %s " % order if limit: query += "LIMIT %s " % limit cursor.execute(query) users = dictfetchall(cursor) cursor.close() for user in users: user['isAnonymous'] = bool(user['isAnonymous']) except Exception: raise else: return users finally: cursor.close()
def getPostsList(short_name, since, order, limit, related): cursor = connection.cursor() try: query = ( "SELECT " "posts.date AS date, " "posts.dislikes AS dislikes, " "forums.short_name AS forum, " "posts.id AS id, " "posts.isApproved AS isApproved, " "posts.isDeleted AS isDeleted, " "posts.isEdited AS isEdited, " "posts.isHighlighted AS isHighlighted, " "posts.isSpam AS isSpam, " "posts.likes AS likes, " "posts.message AS message, " "posts.post_id as parent, " "posts.likes - posts.dislikes AS points, " "threads.id AS thread, users.email AS user " "FROM posts RIGHT JOIN threads ON posts.thread_id = threads.id " "RIGHT JOIN forums ON threads.forum_id = forums.id " "JOIN users ON posts.user_id = users.id " "WHERE forums.short_name=%r " % (str(short_name))) if since: query += "AND posts.date >= %r " % str(since) if order: query += "ORDER BY posts.date %s " % order if limit: query += "LIMIT %s " % limit cursor.execute(query) posts = dictfetchall(cursor) cursor.close() for post in posts: post['isApproved'] = bool(post['isApproved']) post['isDeleted'] = bool(post['isDeleted']) post['isEdited'] = bool(post['isEdited']) post['isHighlighted'] = bool(post['isHighlighted']) post['isSpam'] = bool(post['isSpam']) post['date'] = post['date'].strftime("%Y-%m-%d %H:%M:%S") if 'user' in related: for post in posts: from technopark_db_api_app.queries import user_queries post['user'] = user_queries.getDetailedUser(post['user']) if 'thread' in related: for post in posts: from technopark_db_api_app.queries import thread_queries post['thread'] = thread_queries.getDetailedThread(post['thread'], []) if 'forum' in related: for post in posts: post['forum'] = getDetailedForum(post['forum'], []) except Exception: raise else: return posts finally: cursor.close()