def create_user(self, **json):
# with (yield self.db.db()) as db:
Rules.rule_usercreate(**json)
account = json['account']
#账户查重
cnk_SQL = SQL_refector.user_get(account)
cnk_res = yield self.db.query_one(cnk_SQL)
if cnk_res:
raise RuntimeError(errors.ACCOUNT_ALREADY_EXIST)
account_hash = static.get_hash(account)
table_num = str(static.get_table_num(account_hash))
json['uid'] = account_hash
json['tbn'] = static.USER_TABLE_BASE_NAME+'_'+str(table_num)
#头像处理
portrait_base64=json.get('portrait', None)
if portrait_base64:
path = os.path.join(static.config.PORTRAIT_PATH, table_num)
filename = str(account_hash)+'.png'
photopathname = utils.load_picture(path, filename, portrait_base64)
photourl = utils.path_to_url(static.config.DATA_PATH, photopathname)
json['photo_url'] = photourl
#为birth添加默认值
if len(json.get('birth', '')) < 3:
json['birth']='1900-01-01'
json['age'] = '0'
SQL = SQL_refector.user_insert(**json)
effect_row = yield self.db.execute(SQL)
nickname = json.get('nickname', None)
uid = json.get('uid', None)
account = json.get('account', None)
raise gen.Return((uid, nickname, account))
def defocus_user(self, masterid ,fanid):
#with (yield self.db.db()) as db:
masterid = int(masterid)
fanid = int(fanid)
deleterelation_SQL = SQL_refector.relation_delete(masterid, fanid)
effect_row = yield self.db.execute(deleterelation_SQL)
if not effect_row:
raise gen.Return()
# yield self.update_user(masterid, fans='fans-1')
# yield self.update_user(fanid, follow='follow-1')
# TODO 这个间隙可能会导致数据出问题，多个请求到来会导致丢失一些记录
updateuser_SQL = SQL_refector.user_incr_follow_fan(masterid, fanamount=-1)
yield self.db.execute(updateuser_SQL)
updateuser_SQL = SQL_refector.user_incr_follow_fan(fanid, followamount=-1)
yield self.db.execute(updateuser_SQL)
def insert_favoritetag(self, user, **json):
if not user:
raise RuntimeError(errors.LOGIN_COOKIES_INVALID)
if not json.get('tags', None):
raise RuntimeError(errors.JSON_SHOULD_BE_NOT_NONE)
#with (yield self.db.db()) as db:
favoritetag_SQL = SQL_refector.favoritetag_get(uid=user.uid)
favoritetag_res = yield self.db.query_all(favoritetag_SQL)
if favoritetag_res:
raise RuntimeError(errors.API_PERMISSION_DENY)
favoritetaginsert_SQLS=[]
for tag in json.get('tags'):
favoritetaginsert_SQL = SQL_refector.favoritetag_insert(user.uid, tag)
favoritetaginsert_SQLS.append(favoritetaginsert_SQL)
yield self.db.execute_many_no_result(favoritetaginsert_SQLS)
def get_favoritetagname(self, user):
if not user:
raise RuntimeError(errors.LOGIN_COOKIES_INVALID)
#with (yield self.db.db()) as db:
videotag_SQL = SQL_refector.favoritetagname_get(uid=user.uid)
videotag_res = yield self.db.query_all(videotag_SQL)
raise gen.Return([videotag_record['tag_name'] for videotag_record in videotag_res])
def insert_videocomment(self, vid, uid, **json):
#with (yield self.db.db()) as db:
comment = Rules.getfromrequest(json, 'comment')
ref_comment_id = json.get('ref_comment_id', None)
user = yield self.get_user(uid)
insertcomment_SQL = SQL_refector.videocomment_insert(vid, uid, comment, user.get('nickname', ''), user.get('photo_url', ''), ref_comment_id)
effect_row = yield self.db.execute(insertcomment_SQL)
def focus_user(self, masterid, fanid):
#with (yield self.db.db()) as db:
masterid=int(masterid)
fanid=int(fanid)
insertrelation_SQL = SQL_refector.relation_insert(masterid, fanid)
effect_row = yield self.db.execute(insertrelation_SQL)
if not effect_row:
raise gen.Return()
# TODO 这个间隙可能会导致数据出问题，多个请求到来会导致丢失一些记录
# yield self.update_user(masterid, fans='fans+1')
# yield self.update_user(fanid, follow='follow+1')
# TODO 可以用一条sql语句完成操作
updateuser_SQL = SQL_refector.user_incr_follow_fan(masterid, fanamount=1)
yield self.db.execute(updateuser_SQL)
updateuser_SQL = SQL_refector.user_incr_follow_fan(fanid, followamount=1)
yield self.db.execute(updateuser_SQL)
def isfollow(self, masterid, fanid):
#with (yield self.db.db()) as db:
getrelaiton_SQL = SQL_refector.relation_get(masterid, fanid)
getrelation_res = yield self.db.query_one(getrelaiton_SQL)
if getrelation_res:
raise gen.Return(True)
else:
raise gen.Return(False)
def updatevideo(self, key, vid):
video = self.data.hgetall(key)
if video:
if video.get('like', None):
video['`like`'] = video['like']
update_video_sql = SQL_refector.video_update(vid, **video)
# print update_video_sql
self.execute(update_video_sql)
self.data.delete(key)
def updatecomment(self, key, cid):
comment = self.data.hgetall(key)
if comment:
if comment.get('like', None):
comment['`like`'] = comment['like']
update_comment_sql = SQL_refector.videocomment_update(
cid, **comment)
self.execute(update_comment_sql)
self.data.delete(key)
def get_masteruser(self, selfid, page):
getrelation_SQL = SQL_refector.relations_master_get(selfid, page)
getrelation_res = yield self.db.query_all(getrelation_SQL)
data = []
for relation in getrelation_res:
masterid=relation['master_uid']
userdata = yield self.get_user(masterid)
if userdata:
data.append(userdata)
raise gen.Return(data)
def get_video(self, video_id):
getvideo_res = self.cache.getvideo(video_id)
if not getvideo_res:
getvideo_SQL = SQL_refector.video_get(video_id)
getvideo_res = yield self.db.query_one(getvideo_SQL)
if not getvideo_res:
raise gen.Return([])
else:
self.cache.setvideo(video_id, **getvideo_res)
raise gen.Return([Jsonify.videojson(getvideo_res)])
def get_videos_uid(self, uid, page):
getvideo_SQL = SQL_refector.videos_getwithuid(uid, page)
getvideo_res = yield self.db.query_all(getvideo_SQL)
if not getvideo_res:
raise gen.Return([])
else:
datas = []
for data in getvideo_res:
datas.append(Jsonify.videojson(data))
raise gen.Return(datas)
def get_user(self, uid):
data = self.cache.getuser(uid)
if not data:
getuser_SQL = SQL_refector.user_getwithid(uid)
data = yield self.db.query_one(getuser_SQL)
if not data:
raise gen.Return({})
else:
self.cache.setuser(uid, **data)
raise gen.Return([Jsonify.userjson(data)])
def get_videocomment(self, vid):
#with (yield self.db.db()) as db:
getcomment_SQL = SQL_refector.videocomments_get(vid)
getcomment_res = yield self.db.query_all(getcomment_SQL)
if not getcomment_res:
raise gen.Return([])
else:
datas = []
for data in getcomment_res:
datas.append(Jsonify.commentjson(data))
raise gen.Return(datas)
def get_fanuser(self, selfid, page):
#with (yield self.db.db()) as db:
getrelation_SQL = SQL_refector.relations_fan_get(selfid, page)
getrelation_res = yield self.db.query_all(getrelation_SQL)
data = []
for relation in getrelation_res:
fanid=relation['fans_uid']
userdata = yield self.get_user(fanid)
if userdata:
data.append(userdata)
raise gen.Return(data)
def like_video(self, vid):
# with (yield self.db.db()) as db:
# getvideo_SQL = SQL_refector.video_get(vid)
# getvideo_res = yield db.execute(getvideo_SQL)
# if not getvideo_res:
# raise ValueError(errors.VIDEO_NOT_FOUND)
# like = int(getvideo_res[0][8])
# TODO 这个间隙可能会导致数据出问题，多个点赞到来会导致丢失一些点赞的记录
# TODO 可以用一条sql语句完成操作 `like`=`like`+'1'
if not self.cache.likevideo(vid):
updatevideo_SQL = SQL_refector.video_incr_share_view_like(vid, likeamount=1)
effect_row = yield self.db.execute(updatevideo_SQL)
def get_videos(self, page):
#with (yield self.db.db()) as db:
getvideos_SQL = SQL_refector.videos_get(page)
getvideos_res = yield self.db.query_all(getvideos_SQL)
if not getvideos_res:
raise gen.Return([])
else:
datas = []
for data in getvideos_res:
datas.append(Jsonify.videojson(data))
#为所有的视频观看次数+1
# TODO 这个间隙可能会导致数据出问题，多个请求到来会导致丢失一些记录
sqls=[]
for video in datas:
vid = video.get('vid', None)
view = video.get('view', None)
if vid is not None:
updatevideo_SQL = SQL_refector.video_incr_share_view_like(vid, viewamount=1)
sqls.append(updatevideo_SQL)
yield self.db.execute_many_no_result(sqls)
raise gen.Return(datas)
def get_send_message(self, uid, page=0):
#with (yield self.db.db()) as db:
getmessages_SQL = SQL_refector.messages_get_selfsend(uid, page)
getmessage_res = yield self.db.query_all(getmessages_SQL)
if not getmessage_res:
raise gen.Return(None)
else:
datas = []
for data in getmessage_res:
#特殊处理字段
if data.get('send_time', None):
data['send_time'] = str(data['send_time'])
datas.append(data)
raise gen.Return(datas)
def get_videoswithvids(self, vids):
getvideos_res = []
for vid in vids:
video = yield self.get_video(vid)
getvideos_res.extend(video)
if not getvideos_res:
getvideos_SQL = SQL_refector.videos_getwithvids(vids)
getvideos_res = yield self.db.query_all(getvideos_SQL)
if not getvideos_res:
raise gen.Return([])
else:
datas = []
for data in getvideos_res:
datas.append(Jsonify.videojson(data))
raise gen.Return(datas)
def get_videoswithneighbour(self, longitude, latitude, level, page):
if not Rules.rule_geo(latitude=latitude, longitude=longitude, level=level):
raise RuntimeError(errors.GEOINFORMATION_ERROR)
longitude=round(float(longitude), 10)
latitude=round(float(latitude), 10)
level = int(level)
geohash = mzgeohash.encode((longitude, latitude), level)
neighbours = mzgeohash.neighbors(geohash).values()
getvideos_SQL = SQL_refector.videos_getwithgeo(neighbours, level, page)
getvideos_res = yield self.db.query_all(getvideos_SQL)
if not getvideos_res:
raise gen.Return([])
else:
datas = []
for data in getvideos_res:
datas.append(Jsonify.videojson(data))
raise gen.Return(datas)
def check_user(self, **json):
#with (yield self.db.db()) as db:
account, password = Rules.rule_userlogin(**json)
getuser_SQL = SQL_refector.user_get(account)
getuser_res = yield self.db.query_one(getuser_SQL)
if getuser_res.get('status', 0) < 0:
raise RuntimeError(errors.ACCOUNT_BAN)
if not getuser_res:
raise RuntimeError(errors.ACCOUNT_PASSWORD_ERROR)
if not password == getuser_res['password']:
raise RuntimeError(errors.ACCOUNT_PASSWORD_ERROR)
'''
getuser_res参考数据
((-840729673, 0, u'\u5f20\u4e09123', u'/data/minitrill/user/photo/default/default.jpg', u'lemon123', u'aa1321231', u'\u4fdd\u5bc6', 18, None, u'\u4fdd\u5bc6', u'\u4e2d\u56fd', u'\u4fdd\u5bc6', u'\u4fdd\u5bc6', 0, 0, datetime.datetime(2018, 7, 19, 15, 37, 40)),)
'''
nickname = getuser_res['nickname']
uid = getuser_res['uid']
account = getuser_res['account']
raise gen.Return((uid, nickname, account))
def unread_messagenum(self, uid):
#with (yield self.db.db()) as db:
getmessagenum_SQL = SQL_refector.message_getnum_unread(uid)
unreadnum = yield self.db.query_one(getmessagenum_SQL)
raise gen.Return(unreadnum)
def delete_message(self, messageid):
#with (yield self.db.db()) as db:
deletemessage_SQL = SQL_refector.message_delete(messageid)
effect_row = yield self.db.execute_no_result(deletemessage_SQL)
def read_message(self, messageid):
#with (yield self.db.db()) as db:
readmessage_SQL = SQL_refector.message_update(messageid, isread='\'1\'')
effect_row = yield self.db.execute(readmessage_SQL)
def update_user(self, uid, **json):
updateuser_SQL = SQL_refector.user_update(uid, **json)
effect_row = yield self.db.execute(updateuser_SQL)
if self.cache.getuser(uid):
self.cache.deleteuser(uid)
def insert_message(self, senduid, **json):
#with (yield self.db.db()) as db:
reciveuid, text = Rules.getfromrequest(json, 'recive_uid', 'text')
insertmessage_SQL = SQL_refector.message_insert(senduid, reciveuid, text)
effect_row = yield self.db.execute(insertmessage_SQL)
def delete_videocomment(self, commentid):
#with (yield self.db.db()) as db:
deletecomment_SQL = SQL_refector.videocomment_delete(commentid)
effect_row = yield self.db.execute(deletecomment_SQL)
def update_like_videocomment(self, commentid):
#with (yield self.db.db()) as db:
updatecomment_SQL = SQL_refector.videocomment_incr_like(commentid, likeamount=1)
effect_row = yield self.db.execute(updatecomment_SQL)
def get_videotag(self):
#with (yield self.db.db()) as db:
videotag_SQL = SQL_refector.videotag_get()
videotag_res = yield self.db.query_all(videotag_SQL)
raise gen.Return([videotag_record['tag_name'] for videotag_record in videotag_res])
def view_video(self, vid):
if not self.cache.viewvideo(vid):
updatevideo_SQL = SQL_refector.video_incr_share_view_like(vid, viewamount=1)
effect_row = yield self.db.execute(updatevideo_SQL)