def get_query_restore_post_for_id(post):
query = Query()
query.add_update("post", " isDeleted = 0 ")
query.add_where_condition(" id = {}".format(post))
return query
def get_query_list_threads(data, has_forum):
query = Query()
columns = "t.*"
if has_forum:
columns += ", f.id, f.name, f.short_name, f.user"
query.add_select("thread as t", columns)
if has_forum:
query.add_left_join("forum as f", "t.forum = f.short_name")
if data.get("forum"):
query.add_where_condition("t.forum = \"{}\"".format(data["forum"]))
else:
query.add_where_condition("t.user = \"{}\"".format(data["user"]))
if "since" in data:
query.add_more_where_condition("t.date > \"{}\"".format(data["since"]))
query.add_order_by("t.date", data["order"])
if "limit" in data:
query.add_limit(data["limit"])
return query
def get_query_open_thread(thread):
query = Query()
query.add_update("thread", " isClosed = 0 ")
query.add_where_condition(" id = {}".format(thread))
return query
def get_query_users_by_followers(data, follower=None):
query = Query()
columns = 'u.*, group_concat(distinct f.following),' \
'group_concat(distinct f1.follower),' \
'group_concat(distinct s.thread)'
query.add_select("user as u", columns)
query.add_left_join("follow as f", "u.email = f.follower")
query.add_left_join("follow as f1", "u.email = f1.following")
query.add_left_join("subscribe as s", "u.email= s.user")
query.add_left_join("post as p", "p.user = u.email")
if follower:
query.add_where_condition("f.following = \"{}\"".format(data["user"]))
else:
query.add_where_condition("f.follower = \"{}\"".format(data["user"]))
if "since_id" in data:
query.add_more_where_condition(" u.id >= {}".format(data["since_id"]))
if follower:
query.add_group_by("f.follower")
else:
query.add_group_by("f.following")
query.add_order_by("u.name", data["order"])
if "limit" in data:
query.add_limit(data["limit"])
return query
def get_query_update_user(user, about, name):
query = Query()
query.add_update("user",
" about = \"{}\", name = \"{}\" ".format(about, name))
query.add_where_condition(" email = \"{}\"".format(user))
return query
def get_query_list_posts_by_forum(data, has_forum, has_thread):
query = Query()
columns = "p.id, p.message, p.date, p.isApproved, p.isHighlighted, p.isEdited, p.isSpam, " \
"p.isDeleted, p.forum, p.thread, p.user, p.dislikes, p.likes, p.points, p.parent"
if has_forum:
columns += ", f.id, f.name, f.short_name, f.user"
if has_thread:
columns += ", t.id, t.title, t.slug, t.message, t.date, " \
"t.posts, t.likes, t.dislikes, t.points, " \
"t.isClosed, t.isDeleted, t.forum, t.user"
query.add_select("post as p", columns)
if has_forum:
query.add_left_join("forum as f", "p.forum = f.short_name")
if has_thread:
query.add_left_join("thread as t", "p.thread = t.id")
if data.get("forum"):
query.add_where_condition("p.forum = \"{}\"".format(data["forum"]))
else:
query.add_where_condition("p.thread = \"{}\"".format(data["thread"]))
if "since" in data:
query.add_more_where_condition("p.date > \"{}\"".format(data["since"]))
query.add_order_by("p.date", data["order"])
if "limit" in data:
query.add_limit(data["limit"])
return query
def get_query_restore_post_for_thread(thread):
query = Query()
query.add_update("post", " isDeleted = 0 ")
query.add_where_condition(" thread = {}".format(thread))
return query
def get_query_remove_thread(thread):
query = Query()
query.add_update("thread", " isDeleted = 1 , posts = 0")
query.add_where_condition(" id = {}".format(thread))
return query
def get_query_restore_thread(thread):
query = Query()
query.add_update("thread as t", " isDeleted = 0 , posts = ("
"select count(p.id) from post as p where p.thread = t.id)")
query.add_where_condition(" id = {}".format(thread))
return query
def status(request):
cursor = connection.cursor()
query = Query()
response = {}
for table in ['post', 'thread', 'forum', 'user']:
query.clear()
query.add_select(table, "count(*)")
cursor.execute(query.get())
response[table] = cursor.fetchone()[0]
cursor.close()
return HttpResponse(dumps({'code': Code.OK, "response": response}))
def get_query_detail_user_by_email(email):
query = Query()
columns = 'u.*, group_concat(distinct f.following),' \
'group_concat(distinct f1.follower),' \
'group_concat(distinct s.thread)'
query.add_select("user as u", columns)
query.add_left_join("follow as f", "u.email = f.follower")
query.add_left_join("follow as f1", "u.email = f1.following")
query.add_left_join("subscribe as s", "u.email= s.user")
query.add_where_condition("u.email = \"{}\"".format(email))
return query
def get_query_vote_thread(thread, vote):
if vote == 1:
column = "likes"
difference = " + 1 "
elif vote == -1:
column = "dislikes"
difference = " - 1 "
else:
return None
query = Query()
query.add_update("thread", " {} = {} + 1, points = points {}".format(column, column, difference))
query.add_where_condition(" id = {}".format(thread))
return query
def get_query_detail_thread_by_id(id_thread, has_forum):
query = Query()
columns = "t.*"
if has_forum:
columns += ", f.name f_name, f.short_name f_short_name, f.user f_user, f.id f_id"
query.add_select("thread as t", columns)
if has_forum:
query.add_left_join("forum as f", "t.forum = f.short_name")
query.add_where_condition("t.id = \"{}\"".format(id_thread))
return query
def get_query_list_child_posts(data, id_parent, do_limit=None):
query = Query()
columns = "id, message, date, isApproved, isHighlighted, isEdited, isSpam, " \
"isDeleted, forum, thread, user, dislikes, likes, points, parent"
query.add_select("post", columns)
query.add_where_condition("parent = {}".format(id_parent))
if "since" in data:
query.add_more_where_condition("date > \"{}\"".format(data["since"]))
if do_limit:
query.add_limit(data["limit"])
query.add_order_by("date", "asc")
return query
def create_user(data):
cursor = connection.cursor()
# insert user in db
try:
query = Query()
query.add_insert("user", data.items())
cursor.execute(query.get())
# if insert failed, that means the user with this name is existed
except Exception as e:
print str(e)
cursor.close()
return None
# get just insert user for answer
try:
query.clear()
query.select_last_insert_id()
cursor.execute(query.get())
user_id = cursor.fetchone()[0]
if data["isAnonymous"] == 1:
name = None
about = None
username = None
else:
name = data["name"]
username = data["username"]
about = data["about"]
response = data
response["name"] = name
response["username"] = username
response["about"] = about
response["id"] = user_id
# unknown error
except Exception as e:
print str(e)
cursor.close()
return None
cursor.close()
return response
def get_query_list_root_posts(data):
query = Query()
columns = "id, message, date, isApproved, isHighlighted, isEdited, isSpam, " \
"isDeleted, forum, thread, user, dislikes, likes, points, parent"
query.add_select("post", columns)
query.add_where_condition("parent is NULL ")
query.add_more_where_condition(" thread = {}".format(data["thread"]))
if "since" in data:
query.add_more_where_condition("date > \"{}\"".format(data["since"]))
query.add_order_by("date", data["order"])
if "limit" in data:
query.add_limit(data["limit"])
return query
def get_query_list_posts_by_thread(data):
query = Query()
columns = "p.id, p.message, p.date, p.isApproved, p.isHighlighted, p.isEdited, p.isSpam," \
"p.isDeleted, p.forum, p.thread, p.user, p.dislikes, p.likes, p.points, p.parent"
query.add_select("post as p", columns)
query.add_where_condition("p.thread = \"{}\"".format(data["thread"]))
if "since" in data:
query.add_more_where_condition("p.date > \"{}\"".format(data["since"]))
query.add_order_by("p.date", data["order"])
if "limit" in data:
query.add_limit(data["limit"])
return query
def subscribe_user(thread, user):
cursor = connection.cursor()
code = Code
try:
query = Query()
query.add_insert("subscribe", (("user", user), ("thread", thread)))
cursor.execute(query.get())
except Exception as e:
print str(e)
cursor.close()
return {
'code': code.NOT_FOUND,
"response": "insert failed, user or thread not found"
}
cursor.close()
response = {"thread": thread, "user": user}
return {'code': code.OK, "response": response}
def unsubscribe_user(thread, user):
cursor = connection.cursor()
code = Code
try:
query = Query()
query.add_delete("subscribe")
query.add_where_condition(" user = \"{}\" and thread = {} ".format(user, thread))
cursor.execute(query.get())
except Exception as e:
print str(e)
cursor.close()
return {'code': code.NOT_FOUND, "response": "insert failed, user or thread not found"}
cursor.close()
response = {
"thread": thread,
"user": user
}
return {'code': code.OK, "response": response}
def follow_user(follower, followee):
cursor = connection.cursor()
code = Code
try:
query = Query()
query.add_insert("follow",
(("follower", follower), ("following", followee)))
cursor.execute(query.get())
except Exception as e:
print str(e)
cursor.close()
return {
'code': code.NOT_FOUND,
"response": "insert failed, user or thread not found"
}
cursor.close()
response = get_detail_user(follower)
return {'code': code.OK, "response": response}
def unfollow_user(follower, followee):
cursor = connection.cursor()
code = Code
try:
query = Query()
query.add_delete("follow")
query.add_where_condition(
" follower = \"{}\" and following = \"{}\" ".format(
follower, followee))
cursor.execute(query.get())
except Exception as e:
print str(e)
cursor.close()
return {
'code': code.NOT_FOUND,
"response": "delete failed, user not found"
}
cursor.close()
response = get_detail_user(follower)
return {'code': code.OK, "response": response}
def get_query_detail_post_by_id(id_post, has_forum, has_thread):
query = Query()
columns = 'p.id, p.message, p.date, p.isApproved, p.isHighlighted, p.isEdited, p.isSpam, ' \
'p.isDeleted, p.forum, p.thread, p.user, p.dislikes, p.likes, p.points, p.parent'
if has_forum:
columns += ", f.name f_name, f.short_name f_short_name, f.user f_user, f.id f_id"
if has_thread:
columns += ', t.id, t.forum, t.title, t.isClosed, ' \
't.user, t.date, t.message, t.slug, t.isDeleted, ' \
't.posts, t.likes , t.dislikes , t.points '
query.add_select("post as p", columns)
if has_forum:
query.add_left_join("forum as f", "p.forum = f.short_name")
if has_thread:
query.add_left_join("thread as t", "p.thread = t.id")
query.add_where_condition("p.id = \"{}\"".format(id_post))
return query
def get_query_parent_thread_and_forum(post):
query = Query()
query.add_select("post", "forum, thread ")
query.add_where_condition("id = \"{}\"".format(post))
return query
def get_query_update_post(post, message):
query = Query()
query.add_update("post", " message = \"{}\" ".format(message))
query.add_where_condition(" id = {}".format(post))
return query
def get_query_id_post_by_id(post_id):
query = Query()
query.add_select("post", ["id"])
query.add_where_condition("id = \"{}\"".format(post_id))
return query
def get_query_id_forum_by_short_name(short_name):
query = Query()
query.add_select("forum", ["id"])
query.add_where_condition("short_name = \"{}\"".format(short_name))
return query
def get_query_for_remove_thread(forum_id, is_del):
query = Query()
data = [("isDeleted", is_del)]
query.add_update("thread", data)
query.add_where_condition("id = \"{}\"".format(forum_id))
return query
def get_query_increment_posts(thread_id):
query = Query()
query.add_update("thread", "posts = posts + 1")
query.add_where_condition("id = {}".format(thread_id))
return query
def get_query_update_thread(thread, message, slug):
query = Query()
query.add_update("thread", " message = \"{}\", slug = \"{}\" ".format(message, slug))
query.add_where_condition(" id = {}".format(thread))
return query
def get_query_id_thread_by_id(thread_id):
query = Query()
query.add_select("thread", ["id"])
query.add_where_condition("id = \"{}\"".format(thread_id))
return query