def user_file_list_with_name(user_id, name):
# name.upper
conn = None
try:
conn = dao.get()
sql = "SELECT \
user_file.id as id, \
personal_name as name, \
directory_path, directory_id ,type, size, add_timestamp, update_timestamp, hash, mime \
from user_file \
left join file on user_file.file_id = file.id\
WHERE user_id = %(user_id)s and UPPER(user_file.personal_name) like BINARY %(personal_name)s"
file, count, new_id, err = dao.do_execute_sql_with_connect(
conn=conn,
commit=False,
sql=sql,
kv=True,
args={
'personal_name': '%{}%'.format(name.upper()),
'user_id': user_id
})
conn.commit()
return {'files': [] if file is None else file}
except Exception as e:
print(e)
conn.rollback()
conn.commit()
return False
finally:
if conn:
conn.close()
def id_list(user_id, last_id=None, size=10):
conn = None
cursor = None
try:
conn = dao.get()
cursor = conn.cursor()
count = cursor.execute(
'SELECT * FROM art where user_id=%(user_id)s order by id desc',
{'user_id': user_id})
result = cursor.fetchmany(1)
if last_id is None and count > 0:
last_id = result[0][0] + 1
conn.commit()
except Exception as ex:
conn.rollback()
conn.commit()
return None, 0, 0
finally:
dao.close(conn, cursor)
sql = 'SELECT * FROM art where user_id=%(user_id)s AND id < %(last_id)s order by id desc limit %(size)s'
result, count, new_id = dao.execute_sql(sql,
kv=True,
args={
'user_id': user_id,
'last_id': last_id,
'size': size
})
last_id = 0
for row in result:
last_id = row['id']
return result, count, last_id if count > 0 else 0
def user_file_directory_list(user_id, directory_id=None):
conn = None
try:
conn = dao.get()
sql = "SELECT \
user_file.id as id, \
personal_name as name, \
directory_path, directory_id ,type, size, add_timestamp, update_timestamp, hash, mime \
from user_file \
left join file on user_file.file_id = file.id\
WHERE user_id = %(user_id)s and directory_id = %(directory_id)s and type=1"
file, count, new_id, err = dao.do_execute_sql_with_connect(
conn=conn,
commit=False,
sql=sql,
kv=True,
args={
'directory_id': directory_id,
'user_id': user_id
})
conn.commit()
return {'files': file}
except Exception as e:
print(e)
conn.rollback()
conn.commit()
return False
finally:
if conn:
conn.close()
def user_file_del(user_id):
user_file_id = request_value(request, 'id')
if user_file_id is None:
return jsonify(form_res(RGResCode.lack_param))
conn = None
try:
conn = dao.get()
delete_files = files.user_file_del_and_return_files(user_id=user_id,
id=user_file_id,
conn=conn)
if len(delete_files) > 0:
executor.submit(do_del_file, delete_files)
conn.commit()
return jsonify(form_res(RGResCode.ok, None))
except Exception as e:
print(e)
conn.rollback()
conn.commit()
return jsonify(form_res(RGResCode.del_fail, None))
finally:
if conn:
conn.close()
def user_file_list(user_id, directory_id=None):
conn = None
try:
conn = dao.get()
sql = "SELECT \
user_file.id as id, \
personal_name as name, \
file.exif_timestamp as exif_timestamp,\
directory_path, directory_id ,type, size, open_code, add_timestamp, update_timestamp, hash, mime \
from user_file \
left join file on user_file.file_id = file.id\
WHERE user_id = %(user_id)s and directory_id = %(directory_id)s"
file, count, new_id, err = dao.do_execute_sql_with_connect(
conn=conn,
commit=False,
sql=sql,
kv=True,
args={
'directory_id': directory_id,
'user_id': user_id
})
sql = "SELECT \
c.personal_name as name,\
c.id as id,\
c.type as type,\
c.add_timestamp as add_timestamp,\
c.update_timestamp as update_timestamp\
FROM user_file c\
LEFT JOIN ( \
SELECT CONCAT_WS(',', directory_path, id) as path from user_file \
where user_id=%(user_id)s and id=%(file_id)s) temp on true \
WHERE (\
SELECT FIND_IN_SET(c.id, CONCAT_WS(',', t.directory_path, t.id)) \
from user_file t where t.user_id=%(user_id)s and t.id=%(file_id)s \
)\
order by FIND_IN_SET(c.id, temp.path)"
directory, count, new_id, err = dao.do_execute_sql_with_connect(
conn=conn,
commit=False,
sql=sql,
kv=True,
args={
'file_id': directory_id,
'user_id': user_id
})
conn.commit()
return {'files': file, 'path': directory}
except Exception as e:
print(e)
conn.rollback()
conn.commit()
return False
finally:
if conn:
conn.close()
def check_and_new_user_file_with_hash(user_id,
directory_id=-1,
file_hash=None,
name=''):
conn = None
user_file = None
from RGUtil.RGCodeUtil import RGResCode
code = RGResCode.server_error
try:
conn = dao.get()
file_info = check_file(file_hash=file_hash, conn=conn)
if file_info is None:
code = RGResCode.not_existed
raise Exception(code)
if file_info is not None:
if not capacity_enough(user_id=user_id,
new_file_size=file_info['size'],
conn=conn):
code = RGResCode.full_size
raise Exception('capacity not enough')
time = RGTimeUtil.timestamp()
user_file = new_user_file(conn=conn,
user_id=user_id,
file_id=file_info['id'],
type=0,
directory_id=directory_id,
personal_name=name,
add_timestamp=time,
update_timestamp=time,
combined_file_info=file_info)
if user_file is None:
code = RGResCode.insert_fail
raise Exception('new_user_file failed')
code = RGResCode.ok
conn.commit()
except Exception as e:
print(e)
conn.rollback()
conn.commit()
finally:
if conn:
conn.close()
return user_file, code
def check_and_new_pic_with_hash(user_id, file_hash, filename, album_id=-1, full_url=False):
conn = None
pic = None
from RGUtil.RGCodeUtil import RGResCode
code = RGResCode.server_error
try:
conn = dao.get()
from Model import files
file_info = files.check_file(file_hash=file_hash, conn=conn)
if file_info is None:
code = RGResCode.not_existed
raise Exception('file not existed')
if file_info['forever'] == 0:
if not files.file_set(id=file_info['id'], conn=conn, args={'forever': 1}):
code = RGResCode.update_fail
raise Exception('update failed')
if support_image(filename=file_info['filename']):
if album_id is None or int(album_id) == -1:
de_album = album.default_album(user_id, conn=conn)
album_id = de_album.ID
pic = new_pic(user_id, file_info, album_id=album_id, conn=conn, title=filename, full_url=full_url)
if pic is None:
code = RGResCode.insert_fail
raise Exception('insert failed')
else:
code = RGResCode.param_error
raise Exception('format error')
code = RGResCode.ok
conn.commit()
except Exception as e:
print(e)
conn.rollback()
conn.commit()
finally:
if conn:
conn.close()
return pic, code
def new_directory(user_id, directory_id=-1, name=''):
conn = None
try:
conn = dao.get()
time = RGTimeUtil.timestamp()
file = new_user_file(conn=conn,
user_id=user_id,
file_id=None,
type=1,
directory_id=directory_id,
personal_name=name,
add_timestamp=time,
update_timestamp=time)
conn.commit()
return file
except Exception as e:
print(e)
conn.rollback()
conn.commit()
return None
finally:
if conn:
conn.close()
def new_user(username, pwd, title='Title', desc='Desc', nick='Nickname'):
timestamp = RGTimeUtil.timestamp()
pwd = sha256_key(username, pwd)
_user = None
conn = None
cursor = None
try:
conn = dao.get()
cursor = conn.cursor()
# 某些时候获取到重复的ID
# sql = "SELECT auto_increment FROM information_schema.`TABLES` \
# WHERE TABLE_SCHEMA='rg_database'AND TABLE_NAME='user'"
# cursor.execute(sql)
new_user_id = -1
from Model import album
_album = album.new_album(user_id=new_user_id,
title='日志相册',
desc='默认相册',
cover=None,
level=2,
timestamp=timestamp,
conn=conn,
commit=False)
if _album is None:
raise Exception
new_album_id = _album.ID
sql = "INSERT INTO user (username, pwd, addtime, title, description, nickname, default_album_id) VALUES \
(%(username)s, %(pwd)s, %(timestamp)s, %(title)s, %(desc)s, %(nick)s, %(new_album_id)s)"
count = cursor.execute(sql,
args={
'username': username,
'pwd': pwd,
'timestamp': timestamp,
'title': title,
'desc': desc,
'nick': nick,
'new_album_id': new_album_id,
'is_active': 1
})
if count == 0:
raise Exception
new_user_id = dao.get_last_insert(cursor=cursor)[0]
flag = album.update_owner(album_id=new_album_id,
user_id=new_user_id,
conn=conn,
commit=False)
if not flag:
raise Exception
conn.commit()
return get_user_with_username(username, need_email=True)
except Exception as e:
print(e)
conn.rollback()
conn.commit()
return None
finally:
dao.close(conn, cursor)
def verify_user(username,
email,
pwd,
verify_code,
verify_type,
title='Title',
desc='Desc',
nick='Nickname'):
check_time = RGTimeUtil.timestamp()
res_code = RGResCode.server_error
conn = None
try:
conn = dao.get()
sql = "SELECT * FROM user where username=%(username)s"
result, count, new_id, err = dao.do_execute_sql_with_connect(
sql=sql,
ret=True,
commit=False,
conn=conn,
args={'username': username})
if err:
res_code = RGResCode.server_error
raise Exception
if count <= 0:
res_code = RGResCode.not_existed
raise Exception
_user = user_with_db_result(result[0], need_pwd=True, need_email=True)
if verify_type != RGVerifyType.forget_pwd and _user.is_full_active():
res_code = RGResCode.has_existed
raise Exception
# 超时
if _user.is_time_out(check_time=check_time):
res_code = RGResCode.timeout
raise Exception
# 校验验证码
if verify_type == RGVerifyType.new or verify_type == RGVerifyType.forget_pwd:
email = _user.get_payload(key='email')
if not _user.verify_code(
code=verify_code, email=email, v_type=verify_type):
res_code = RGResCode.verify_code_incorrect
raise Exception
# 创建默认相册
user_id = _user.ID
if verify_type == RGVerifyType.new:
from Model import album
_album = album.new_album(user_id=user_id,
title='日志相册',
desc='默认相册',
cover=None,
level=2,
timestamp=check_time,
conn=conn,
commit=False)
if _album is None:
res_code = RGResCode.server_error
raise Exception
new_album_id = _album.ID
_user.addTime = check_time
else:
new_album_id = _user.defaultAlbumId
sha256_pwd = sha256_key(username, pwd)
args = {
'pwd': sha256_pwd,
'add_time': _user.addTime,
'title': title,
'desc': desc,
'nick': nick,
'new_album_id': new_album_id,
'id': user_id,
'is_active': 1,
'info_payload': ''
}
if verify_type == RGVerifyType.new:
sql = "UPDATE user SET " \
"pwd=%(pwd)s, " \
"addtime=%(add_time)s, " \
"title=%(title)s, " \
"description=%(desc)s, " \
"nickname=%(nick)s, " \
"default_album_id=%(new_album_id)s, " \
"is_active=%(is_active)s, " \
"info_payload=%(info_payload)s " \
"where id=%(id)s"
elif verify_type == RGVerifyType.bind:
if sha256_pwd != _user.pwd:
res_code = RGResCode.password_incorrect
raise Exception
sql = "UPDATE user SET " \
"info_payload=%(info_payload)s " \
"where id=%(id)s"
elif verify_type == RGVerifyType.forget_pwd:
sql = "UPDATE user SET " \
"pwd=%(pwd)s, " \
"info_payload=%(info_payload)s " \
"where id=%(id)s"
result, count, new_id, err = dao.do_execute_sql_with_connect(
sql=sql,
args=args,
conn=conn,
commit=False,
ret=True,
)
if err:
res_code = RGResCode.server_error
raise Exception
if verify_type == RGVerifyType.new:
code = RGOpenIdController.user_new(username=username,
email=email,
password=pwd)
elif verify_type == RGVerifyType.bind:
code = RGOpenIdController.user_new(username=username,
email=email,
password=pwd)
elif verify_type == RGVerifyType.forget_pwd:
code = RGOpenIdController.user_update(username=username,
password=pwd)
else:
code = RGResCode.lack_param
if code != RGResCode.ok:
res_code = code
raise Exception
res_code = RGResCode.ok
conn.commit()
return _user.ID, res_code
except Exception as e:
print(e)
conn.rollback()
conn.commit()
return None, res_code
finally:
dao.close(conn, None)
def new_user_and_save_verify_code(username,
email,
verify_code,
verify_type=RGVerifyType.new):
if username is None or email is None:
return RGResCode.lack_param
if len(username) <= 0 or len(email) <= 0:
return RGResCode.lack_param
timestamp = RGTimeUtil.timestamp()
conn = None
res_code = RGResCode.server_error
try:
conn = dao.get()
# OPEN ID
res_code, data = User.RGOpenIdController.user_list(email=email)
if res_code != RGResCode.ok:
raise Exception
if verify_type == RGVerifyType.forget_pwd:
if data is not None and len(data) > 0:
if data[0]['username'] != username:
res_code = RGResCode.auth_fail
raise Exception
else:
res_code = RGResCode.not_existed
raise Exception
else:
if data is not None and len(data) > 0:
res_code = RGResCode.has_existed
raise Exception
# OPEN ID
if verify_code == RGVerifyType.bind or verify_code == RGVerifyType.new:
res_code, data = User.RGOpenIdController.user_list(
username=username)
if res_code != RGResCode.ok:
raise Exception
if data is not None and len(data) > 0:
res_code = RGResCode.has_existed
raise Exception
sql = "SELECT * FROM user where username=%(username)s"
result, count, new_id, err = dao.do_execute_sql_with_connect(
sql=sql,
ret=True,
commit=False,
conn=conn,
args={'username': username})
if err:
res_code = RGResCode.server_error
raise Exception
if count > 0:
_user = user_with_db_result(result[0], need_email=True)
if verify_type == RGVerifyType.new:
if _user.is_full_active() or _user.has_email():
res_code = RGResCode.user_has_existed
raise Exception
elif verify_type == RGVerifyType.bind:
if _user.has_email():
res_code = RGResCode.user_has_existed
raise Exception
elif verify_type == RGVerifyType.forget_pwd:
pass
else:
res_code = RGResCode.lack_param
raise Exception
if _user.is_allow_resend_email(now_time=timestamp) is False:
res_code = RGResCode.frequent
raise Exception
sql = "INSERT INTO user (username, addtime, pwd, is_active, info_payload) VALUES \
(%(username)s, %(timestamp)s, %(pwd)s, %(is_active)s, %(info_payload)s) \
ON DUPLICATE KEY UPDATE info_payload=%(info_payload)s"
payload = json.dumps({
'code': verify_code,
'email': email,
'type': verify_type,
'timestamp': RGTimeUtil.timestamp()
})
args = {
'username': username,
'timestamp': timestamp,
'pwd': '',
'is_active': 0,
'info_payload': payload
}
result, count, new_id, err = dao.do_execute_sql_with_connect(
sql=sql, args=args, conn=conn, commit=False)
if err:
res_code = RGResCode.server_error
raise Exception
conn.commit()
return res_code
except Exception as e:
print(e)
conn.rollback()
conn.commit()
return res_code
finally:
dao.close(conn, None)
def user_file_move(user_id, move_id, to_id):
conn = None
try:
conn = dao.get()
sql = "SELECT * from user_file where id=%(id)s and user_id=%(user_id)s"
args = {'id': move_id, 'user_id': user_id}
file, count, new_id, err = dao.do_execute_sql_with_connect(
conn=conn, sql=sql, commit=False, kv=True, args=args)
if err or count <= 0:
raise Exception
file = file[0]
args['id'] = to_id
directory, count, new_id, err = dao.do_execute_sql_with_connect(
conn=conn, sql=sql, commit=False, kv=True, args=args)
if err:
raise Exception
dir_path = ''
dir_id = None
if count > 0:
directory = directory[0]
dir_path = directory['directory_path']
dir_id = directory['id']
to_directory_path = format_path(dir_path=dir_path, dir_id=dir_id)
if file['type'] == 1:
find_id = ',{},'.format(str(file['id']))
# 防止文件夹挪动到自己内部
if to_directory_path.find(find_id) >= 0:
raise Exception
# 改变文件夹内的所有文件路径
del_pre = file['directory_path']
new_pre = to_directory_path
del_pre = del_pre.rstrip(',')
new_pre = new_pre.rstrip(',')
sql = "UPDATE user_file SET " \
"directory_path=CONCAT(" \
"%(new_pre)s, " \
"',', " \
"trim(BOTH ',' from trim(LEADING %(del_pre)s from directory_path)), " \
"',') " \
"where user_id=%(user_id)s and (FIND_IN_SET(%(move_id)s, directory_path))"
args = {
'user_id': user_id,
'move_id': move_id,
'new_pre': new_pre,
'del_pre': del_pre
}
files, count, new_id, err = dao.do_execute_sql_with_connect(
conn=conn, sql=sql, commit=False, kv=True, args=args)
if err:
raise Exception
sql = "UPDATE user_file SET " \
"directory_id=%(directory_id)s, " \
"directory_path=%(directory_path)s " \
"where id=%(id)s and user_id=%(user_id)s"
args['directory_id'] = to_id
args['directory_path'] = to_directory_path
args['id'] = move_id
new_fil, count, new_id, err = dao.do_execute_sql_with_connect(
conn=conn, sql=sql, commit=False, kv=True, args=args)
if err:
raise Exception
conn.commit()
return True
except Exception as e:
print(e)
conn.rollback()
conn.commit()
return False
finally:
if conn:
conn.close()
def wrapper(*args, **kwargs):
code = request_value(request, 'open_code')
if code is None:
if wrapper_code_key is not None:
code = kwargs.get(wrapper_code_key)
if code is None:
return jsonify(form_res(RGResCode.lack_param))
else:
code = re.sub("[^A-Za-z0-9].*", "", code)
try:
f_id, u_id = RGRequestHelp.fid_decode(code)
except Exception as e:
return jsonify(form_res(RGResCode.param_error))
conn = None
try:
conn = dao.get()
open_code_info = files.user_file_info(user_id=u_id,
id=f_id,
open_code=code,
conn=conn)
if open_code_info is None:
return jsonify(form_res(RGResCode.not_existed))
file_id = request_value(request, 'id')
info = open_code_info
if file_id is not None:
file_id = int(file_id)
if f_id != file_id:
# 共享文件夹内的文件可访问
file_info = files.user_file_info(user_id=u_id,
id=file_id)
if file_info is None:
return jsonify(form_res(RGResCode.not_existed))
paths = file_info['directory_path'].split(sep=',')
open_path = str(open_code_info['id'])
if open_path in paths:
info = file_info
if wrapper_path_key is not None:
path = kwargs.get(wrapper_path_key, '')
info['filename'] = info['filename'] + '/' + path
info['name'] = path.split('/')[-1]
del info['mime']
kwargs.update({
'conn': conn,
'info': info,
'open_code': code,
'f_id': f_id,
'u_id': u_id,
})
response = func(*args, **kwargs)
conn.commit()
return response
except Exception as e:
print(e)
conn.rollback()
conn.commit()
return jsonify(form_res(RGResCode.database_error))
finally:
if conn:
conn.close()
def handler_upload_res(user_id,
up_res,
set_name=None,
in_album=False,
album_id=-1,
full_url=False,
in_file=False,
directory_id=-1):
"""
:param set_name:
:param user_id: userId
:param up_res: 上传结果
:param in_album: 1代表存入相册 0代表不存入相册
:param album_id: 插入到对应的相册
:param in_file: 存入用户文件
:param directory_id 文件夹id
:param full_url: 返回全地址
:return: {
'code': code,
'msg': err_msg,
'file_info': file_info,
'data': photo if in_album else user_file,
}
"""
file_info, user_file, photo = None, None, None
original_name, err_msg = '', ''
code = RGResCode.ok
conn = None
try:
conn = dao.get()
filename = ''
if in_file and not files.capacity_enough(
user_id=user_id,
new_file_size=request_file_size(request),
conn=conn):
code = RGResCode.full_size
raise Exception('capacity not enough')
if 'flag' in up_res:
if not up_res['flag']:
code = RGResCode.server_error
raise Exception(up_res['err_msg'])
exif_info = None
exif_time, size = 0, 0
exif_gps_lalo, mime = '', ''
if 'mime' in up_res:
mime = up_res['mime']
if 'name' in up_res:
original_name = up_res['name']
if 'size' in up_res:
size = up_res['size']
if 'path' in up_res:
filename = up_res['path']
if 'exif' in up_res:
exif = up_res.get('exif', None)
exif_time = exif.get('timestamp', None)
exif_gps_lalo = exif.get('gps_lalo', None)
exif_info = exif.get('original', None)
if exif_info:
exif_info = json.dumps(exif_info)
file_info = files.new_file(conn=conn,
mime=mime,
size=size,
filename=filename,
file_hash=up_res['hash'],
exif_time=exif_time,
exif_info=exif_info,
exif_lalo=exif_gps_lalo,
forever=0 if in_file else 1)
if file_info is None:
code = RGResCode.insert_fail
raise Exception('new_file failed')
if in_album and support_image(filename=filename):
photo = pic.new_pic(user_id,
file_info,
album_id=album_id,
conn=conn,
title=original_name,
full_url=full_url)
if photo is None:
raise Exception('new photo failed')
elif in_file:
user_file = files.new_user_file(
conn=conn,
user_id=user_id,
file_id=file_info['id'],
type=0,
directory_id=directory_id,
personal_name=set_name,
add_timestamp=file_info['timestamp'],
update_timestamp=file_info['timestamp'],
combined_file_info=file_info)
if user_file is None:
code = RGResCode.insert_fail
raise Exception('new_user_file failed')
conn.commit()
except Exception as e:
print(e)
err_msg = str(e)
if code == RGResCode.ok:
code = RGResCode.server_error
conn.rollback()
conn.commit()
finally:
if conn:
conn.close()
return {
'code': code,
'msg': err_msg,
'file_info': file_info,
'data': photo if in_album else user_file if in_file else file_info,
}
def __fast_up(user_id, file):
md5 = file.get('md5')
in_album = file.get('in_album', False)
album_id = file.get('album_id', None)
in_file = file.get('in_file', 0)
directory_id = file.get('directory_id', -1)
file_type = file.get('type', 0)
filename = file.get('name', '')
if in_album:
photo, code = pic.check_and_new_pic_with_hash(user_id=user_id,
file_hash=md5,
filename=filename,
album_id=album_id,
full_url=False)
file = {"file": photo, "code": code}
elif in_file:
# directory
if int(file_type) == 1:
file = files.new_directory(user_id=user_id,
name=filename,
directory_id=directory_id)
code = RGResCode.ok if file is not None else RGResCode.insert_fail
else:
file, code = files.check_and_new_user_file_with_hash(
user_id=user_id,
directory_id=directory_id,
file_hash=md5,
name=filename)
file = {"file": file, "code": code}
else:
conn = None
code = RGResCode.server_error
file_info = None
try:
conn = dao.get()
file_info = files.check_file(file_hash=md5, conn=conn)
if file_info is None:
code = RGResCode.not_existed
raise Exception('not existed')
else:
if file_info['forever'] == 0 and not files.file_set(
id=file_info['id'], conn=conn, args={'forever': 1}):
code = RGResCode.update_fail
raise Exception('update fail')
code = RGResCode.ok
conn.commit()
except Exception as e:
print(e)
file_info = None
conn.rollback()
conn.commit()
finally:
if conn:
conn.close()
if file_info is None:
file = {"file": None, "code": code}
else:
file = {
"file":
files.filter_return_file_info(file_info=file_info,
need_id=True),
"code":
RGResCode.ok
}
return file
