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