def get_paged_questions(page_count=10, last_qid=None, pre=False): # 获取问题列表
conn = yield async_connect()
cur = conn.cursor()
if not pre: # 前页
if not last_qid:
sql = "SELECT q.qid, q.abstract, q.content, q.view_count, q.answer_count, u.username, t.tag_name FROM t_question q LEFT JOIN t_user u ON q.uid=u.uid LEFT JOIN t_tag t ON q.tid=t.tid ORDER BY qid DESC LIMIT %d;" % page_count
else:
sql = "SELECT q.qid, q.abstract, q.content, q.view_count, q.answer_count, u.username, t.tag_name FROM t_question q LEFT JOIN t_user u ON q.uid=u.uid LEFT JOIN t_tag t ON q.tid=t.tid WHERE qid<%d ORDER BY qid DESC LIMIT %d;" % (
last_qid, page_count)
else: # 后页
if not last_qid:
return []
else:
sql = "SELECT q.qid, q.abstract, q.content, q.view_count, q.answer_count, u.username, t.tag_name FROM t_question q LEFT JOIN t_user u ON q.uid=u.uid LEFT JOIN t_tag t ON q.tid=t.tid WHERE qid>=%d ORDER BY qid DESC LIMIT %d;" % (
last_qid, page_count)
try:
yield cur.execute(sql)
data = cur.fetchall()
except Exception as e:
data = []
finally:
cur.close()
conn.close()
raise gen.Return(data)
def get_filtered_questions(name, user=None, tag=None): # 获取过滤问题列表
conn = yield async_connect()
cur = conn.cursor()
if name == 'newest':
sql = "SELECT q.qid, q.abstract, q.content, q.view_count, q.answer_count, u.username, t.tag_name FROM t_question q LEFT JOIN t_user u ON q.uid=u.uid LEFT JOIN t_tag t ON q.tid=t.tid ORDER BY q.created_at DESC LIMIT 15;"
elif name == 'hotest':
sql = "SELECT q.qid, q.abstract, q.content, q.view_count, q.answer_count, u.username, t.tag_name FROM t_question q LEFT JOIN t_user u ON q.uid=u.uid LEFT JOIN t_tag t ON q.tid=t.tid ORDER BY answer_count DESC LIMIT 15;"
elif name == 'under':
sql = "SELECT q.qid, q.abstract, q.content, q.view_count, q.answer_count, u.username, t.tag_name FROM t_question q LEFT JOIN t_user u ON q.uid=u.uid LEFT JOIN t_tag t ON q.tid=t.tid WHERE q.status=0 ORDER BY q.created_at DESC LIMIT 15;"
elif name == 'hasdone':
sql = "SELECT q.qid, q.abstract, q.content, q.view_count, q.answer_count, u.username, t.tag_name FROM t_question q LEFT JOIN t_user u ON q.uid=u.uid LEFT JOIN t_tag t ON q.tid=t.tid WHERE q.status=1 ORDER BY q.created_at DESC LIMIT 15;"
elif name == 'prefer' and user:
sql = "SELECT q.qid, q.abstract, q.content, q.view_count, q.answer_count, u.username, t.tag_name FROM t_question q"
sql += " LEFT JOIN t_user u ON q.uid=u.uid"
sql += " LEFT JOIN t_tag t ON q.tid=t.tid"
sql += " WHERE q.tid = (SELECT tid FROM t_question WHERE uid = (SELECT uid FROM t_user WHERE username = '******') GROUP BY tid ORDER BY COUNT(tid) DESC LIMIT 1)" % user
sql += " ORDER BY q.created_at DESC LIMIT 15;"
elif tag:
sql = "SELECT q.qid, q.abstract, q.content, q.view_count, q.answer_count, u.username, t.tag_name FROM t_question q LEFT JOIN t_user u ON q.uid=u.uid LEFT JOIN t_tag t ON t.tid = q.tid WHERE q.tid=%d ORDER BY answer_count DESC LIMIT 15;" % tag
else:
raise gen.Return([])
try:
yield cur.execute(sql)
data = cur.fetchall()
except Exception as e:
data = []
finally:
cur.close()
conn.close()
raise gen.Return(data)
def delete_answer_by_id(aid, qid, user): # 删除答案
conn = yield async_connect()
cur = conn.cursor()
sql = "DELETE FROM t_answer WHERE aid = %d AND qid = %d AND uid=(SELECT uid FROM t_user WHERE username='******');" % (aid, qid, user)
try:
result = yield cur.execute(sql)
except Exception as e:
result = 0
raise gen.Return(result)
def check_answers(qid):
conn = yield async_connect()
cur = conn.cursor()
sql = "UPDATE t_answer SET has_read=1 WHERE qid=%d" % qid
try:
data = yield cur.execute(sql)
except Exception as e:
data = 0
finally:
cur.close()
conn.close()
raise gen.Return(data)
def update_question_answer(qid): # 更新答案
conn = yield async_connect()
cur = conn.cursor()
sql = "UPDATE t_question SET answer_count = answer_count - 1 WHERE qid = %d;" % qid
try:
data = yield cur.execute(sql)
except Exception as e:
data = 0
finally:
cur.close()
conn.close()
raise gen.Return(data)
def create_user(username, password): # 创建用户
conn = yield async_connect()
cur = conn.cursor()
sql = "INSERT INTO t_user(username, password) VALUES ('%s', '%s');" % (username, password)
try:
data = yield cur.execute(sql)
except Exception as e:
data = 0
finally:
cur.close()
conn.close()
raise gen.Return(data)
def adopt_answer(aid, qid): # 采纳答案
conn = yield async_connect()
cur = conn.cursor()
sql = "UPDATE t_answer SET status=TRUE WHERE aid=%d AND qid=(SELECT qid FROM t_question WHERE qid=%d);" % (aid, qid)
try:
data = yield cur.execute(sql)
except Exception as e:
data = 0
finally:
cur.close()
conn.close()
raise gen.Return(data)
def get_user_list(): # 获取用户列表
conn = yield async_connect()
cur = conn.cursor()
sql = "SELECT (@r:=@r+1) AS ranks, username, point FROM t_user u, (SELECT (@r:=0)) c WHERE u.group_type = 0 ORDER BY u.point DESC LIMIT 50;"
try:
yield cur.execute(sql)
data = cur.fetchall()
except Exception as e:
data = []
finally:
cur.close()
conn.close()
raise gen.Return(data)
def get_answers(qid):
conn = yield async_connect()
cur = conn.cursor()
sql = "SELECT a.aid, a.status, a.created_at, a.updated_at, a.content, u.username FROM t_answer a LEFT JOIN t_user u ON u.uid=a.uid WHERE qid=%d ORDER BY a.created_at ASC;" % qid
try:
yield cur.execute(sql)
data = cur.fetchall()
except Exception as e:
data = []
finally:
cur.close()
conn.close()
raise gen.Return(data)
def get_answer_status(user):
conn = yield async_connect()
cur = conn.cursor()
sql = "SELECT SUM(c) AS answer_count FROM (SELECT qid, COUNT(qid) AS c FROM t_answer WHERE has_read=0 GROUP BY qid) AS a WHERE a.qid IN (SELECT qid FROM t_question WHERE uid=(SELECT uid FROM t_user WHERE username='******'));" % user
try:
yield cur.execute(sql)
data = cur.fetchone()
except Exception as e:
data = {}
finally:
cur.close()
conn.close()
raise gen.Return(data)
def get_adopted_count(qid, user): # 获取得分数据
conn = yield async_connect()
cur = conn.cursor()
sql = "SELECT adopted_count FROM t_question WHERE qid=%d AND uid=(SELECT uid FROM t_user WHERE username='******')" % (qid, user)
try:
yield cur.execute(sql)
data = cur.fetchone()
except Exception as e:
data = {}
finally:
cur.close()
conn.close()
raise gen.Return(data)
def get_group_by_user(username): # 获取用户组
conn = yield async_connect()
cur = conn.cursor()
sql = "SELECT group_type FROM t_user WHERE username='******'" % username
try:
yield cur.execute(sql)
data = cur.fetchone()
except Exception as e:
data = None
finally:
cur.close()
conn.close()
raise gen.Return(data)
def get_user_by_username(username): # 获取用户名
conn = yield async_connect()
cur = conn.cursor()
sql = "SELECT username, email, password FROM t_user WHERE username='******';" % username
try:
yield cur.execute(sql)
data = cur.fetchone()
except Exception as e:
data = {}
finally:
cur.close()
conn.close()
raise gen.Return(data)
def get_all_tags(): # 获取标签名列表
conn = yield async_connect()
cur = conn.sursor()
sql = "SELECT tid, tag_name FROM t_tag ORDER BY tid;"
try:
yield cur.execute(sql)
data = cur.fetchall()
except Exception as e:
data = []
finally:
cur.close()
conn.close()
raise gen.Return(data)
def delete_question_by_id(qid, user): # 删除问题
conn = yield async_connect()
cur = conn.cursor()
sql = "DELETE FROM t_question WHERE qid = %d AND uid = (SELECT uid FROM t_user WHERE username='******')" % (
qid, user)
try:
data = yield cur.execute(sql)
except Exception as e:
data = 0
finally:
cur.close()
conn.close()
raise gen.Return(data)
def get_question_by_qid(qid): # 获取问题详情
conn = yield async_connect()
cur = conn.cursor()
sql = "SELECT q.qid, q.abstract, q.content, q.view_count, q.answer_count, q.created_at, q.updated_at, u.username, t.tag_name FROM t_question AS q LEFT JOIN t_user as u ON u.uid=q.uid LEFT JOIN t_tag as t ON q.tid=t.tid WHERE qid=%d;" % qid
try:
yield cur.execute(sql)
data = cur.fetchone()
except Exception as e:
data = {}
finally:
cur.close()
conn.close()
raise gen.Return(data)
def add_point(aid, qid, user): # 增加得分
conn = yield async_connect()
cur = conn.cursor()
sql1 = "UPDATE t_user SET point = point + 1 WHERE uid = (SELECT uid FROM t_answer WHERE aid=%d AND qid=%d);" % (aid, qid)
sql2 = "UPDATE t_question SET status=TRUE, adopted_count = adopted_count + 1 WHERE qid = %d AND uid = (SELECT uid FROM t_user WHERE username='******');" % (qid, user)
try:
data1 = yield cur.execute(sql1)
data2 = yield cur.execute(sql2)
data = data1 and data2
except Exception as e:
data = 0
finally:
cur.close()
conn.close()
raise gen.Return(data)
def get_question_by_str(s): # 查询问题
conn = yield async_connect()
cur = conn.cursor()
sql = "SELECT q.qid, q.abstract, q.view_count, q.answer_count, q.created_at, q.updated_at, u.username, t.tag_name FROM t_question AS q "
sql += "LEFT JOIN t_user as u ON u.uid=q.uid LEFT JOIN t_tag as t ON q.tid=t.tid WHERE abstract LIKE BINARY '%{}%' OR content LIKE BINARY '%{}%';".format(
s, s)
try:
yield cur.execute(sql)
data = cur.fetchall()
except Exception as e:
data = []
finally:
cur.close()
conn.close()
raise gen.Return(data)
def get_user_by_str(s): # 查询用户
conn = yield async_connect()
cur = conn.cursor()
sql = "SELECT rank, username, point FROM"
sql += " (SELECT (@r:=@r+1) AS rank, username, point FROM t_user u, (SELECT (@r:=0)) c WHERE u.group_type = 0 ORDER BY u.point DESC) d"
sql += " WHERE username LIKE BINARY '%{}%';".format(s)
try:
yield cur.execute(sql)
data = cur.fetchall()
except Exception as e:
data = []
finally:
cur.close()
conn.close()
raise gen.Return(data)
def create_answer(qid, user, content): # 创建答案
conn = yield async_connect()
cur = conn.cursor()
if isinstance(content, str):
content = escape_string(content)
sql1 = "INSERT INTO t_answer (qid, uid, content) VALUES (%d, (SELECT uid FROM t_user WHERE username='******'), '%s');" % (qid, user, content)
sql2 = "UPDATE t_question SET answer_count = answer_count + 1 WHERE qid=%d;" % qid
try:
data = yield cur.execute(sql1)
yield cur.execute(sql2)
except Exception as e:
data = 0
finally:
cur.close()
conn.close()
raise gen.Return(data)
def get_unread_answer(user):
conn = yield async_connect()
cur = conn.cursor()
# sql = "SELECT q.qid, q.abstract, u.username FROM t_answer a LEFT JOIN t_question q ON a.qid = q.qid LEFT JOIN t_user u ON u.uid = a.uid WHERE q.uid = (SELECT uid FROM t_user WHERE username='******');" % user
sql = "SELECT a.qid, a.answer_count, c.abstract FROM "
sql += "(SELECT qid, COUNT(qid) AS answer_count FROM t_answer WHERE has_read=0 GROUP BY qid) AS a"
sql += " LEFT JOIN t_question AS c ON c.qid = a.qid WHERE a.qid IN (SELECT b.qid FROM t_question AS b"
sql += " WHERE uid=(SELECT uid FROM t_user WHERE username='******'));" % user
try:
yield cur.execute(sql)
data = cur.fetchall()
except Exception as e:
data = []
finally:
cur.close()
conn.close()
raise gen.Return(data)
def check_answers(qid,user): # 更新未读答案
conn = yield async_connect()
cur = conn.cursor()
sql = "SELECT u.username from t_user as u LEFT JOIN t_question as q on u.uid = q.uid where q.qid = %d" %qid
try:
yield cur.execute(sql)
data = cur.fetchone()
username = data["username"]
if username == user:
update_answers(qid)
else:
data = 0
except Exception as e:
data = 0
finally:
cur.close()
conn.close()
raise gen.Return(data)
def get_tag_list(): # 获取标签列表
conn = yield async_connect()
cur = conn.cursor()
sql = "SELECT d.tid, d.tag_name, SUM(d.qcount) question_count, SUM(d.ucount) user_count FROM ("
sql += "SELECT tid, tag_name, COUNT(tid) qcount, uid, username, COUNT(uid) ucount FROM ("
sql += "SELECT q.tid, t.tag_name, t.tid, u.username, u.uid FROM t_question q"
sql += " LEFT JOIN t_tag t ON t.tid = q.tid"
sql += " LEFT JOIN t_user u ON u.uid = q.uid) c"
sql += " GROUP BY tid, uid) d GROUP BY d.tid ORDER BY question_count DESC;"
try:
yield cur.execute(sql)
data = cur.fetchall()
except Exception as e:
data = []
finally:
cur.close()
conn.close()
raise gen.Return(data)
def check_user_has_read(user, qid): # 获取未读信息
redis = redis_connect()
redis.connect()
conn = yield async_connect()
cur = conn.cursor()
has_read = yield gen.Task(redis.sismember, 'user:has:read:%d' % qid, user)
if has_read:
data = 0
raise gen.Return(data)
redis.sadd('user:has:read:%d' % qid, user)
sql = "UPDATE t_question SET view_count = view_count + 1 WHERE qid = %d" % qid
try:
data = yield cur.execute(sql)
except Exception as e:
data = 0
finally:
cur.close()
conn.close()
raise gen.Return(data)
def create_question(tid, username, abstract, content): # 创建问题
conn = yield async_connect()
cur = conn.cursor()
if isinstance(abstract, str):
abstract = escape_string(abstract)
if isinstance(content, str):
content = escape_string(content)
sql1 = "INSERT INTO t_question (abstract, content, uid, tid) VALUES ('%s', '%s', (SELECT uid FROM t_user WHERE username='******'), %d);" % (
abstract, content, username, tid)
sql2 = "SELECT LAST_INSERT_ID() as qid FROM t_question;"
try:
data = yield cur.execute(sql1)
yield cur.execute(sql2)
last_insert = cur.fetchone()
except Exception as e:
data = 0
last_insert = {}
finally:
cur.close()
conn.close()
raise gen.Return((data, last_insert.get('qid', None)))