def new_pic(user_id, pic_file, conn=None, album_id=-1, title='', desc='', level=0, full_url=False, original=False): if album_id is None or int(album_id) == -1: de_album = album.default_album(user_id, conn=conn) album_id = de_album.ID sql = "INSERT INTO pic (user_id, album_id, file_id, title, description, level) VALUES \ (%(user_id)s, %(album_id)s, %(file_id)s, %(title)s, %(desc)s, %(level)s)" args = { 'user_id': user_id, 'album_id': album_id, 'file_id': pic_file['id'], 'title': title, 'desc': desc, 'level': level } result, count, new_id, err = dao.do_execute_sql(sql=sql, conn=conn, new_id=True, args=args, commit=False) if err is not None: return None url = url_with_name(pic_file['filename'], needhost=full_url, original=original) return { 'id': new_id, 'album_id': album_id, 'url': url, 'exif_timestamp': pic_file['exif_timestamp'], 'timestamp': pic_file['timestamp'] }
def user_file_info(user_id, id=None, type=None, open_code=None, conn=None): temp = ['user_file.id=%(id)s and user_file.user_id = %(user_id)s'] if type is not None: temp.append('user_file.type = %(type)s') if open_code is not None: temp.append('user_file.open_code = %(open_code)s') sql = "SELECT \ file.filename as filename, \ user_file.personal_name as name, \ file.mime as mime, \ file.size as size, \ file.hash as hash, \ user_file.id as id, \ user_file.type as type, \ user_file.open_code as open_code, \ user_file.directory_id as directory_id, \ user_file.directory_path as directory_path \ from user_file \ left join file on user_file.file_id = file.id \ WHERE {} limit 1".format(' and '.join(temp)) result, count, new_id, error = dao.do_execute_sql(conn=conn, sql=sql, kv=True, args={ 'id': id, 'user_id': user_id, 'type': type, 'open_code': open_code }) if count > 0: return result[0] return None
def user_file_del(user_id, id, conn=None): sql = "DELETE from user_file where ((find_in_set(%(id)s, directory_path) or (id=%(id)s)) and user_id=%(user_id)s)" args = {'id': id, 'user_id': user_id} result, count, new_id, error = dao.do_execute_sql(sql=sql, conn=conn, ret=True, commit=False, args=args) return error
def use_capacity(user_id, conn=None): sql = 'SELECT COALESCE(sum(size), 0) as sum from user_file left join file on user_file.file_id = file.id \ where user_file.type = 0 and user_id = %(user_id)s' result, count, new_id, error = dao.do_execute_sql( sql=sql, args={'user_id': user_id}, conn=conn, kv=True) capacity = RGFileMaxCapacity if result is not None and len(result) > 0: capacity = result[0]['sum'] return capacity
def default_album(user_id, conn=None): sql = "Select * from album where user_id=%(user_id)s order by id limit 1" result, count, new_id, err = dao.do_execute_sql(sql=sql, conn=conn, args={'user_id': user_id}, commit=False) if count is 0: return new_album(user_id=user_id, title='默认相册', desc='默认相册', level=2) else: return album_obj_with_result(result[0])
def file_del(ids, conn=None): sql = "DELETE from file where find_in_set(id, %(ids)s) and forever = 0" args = { 'ids': ','.join(ids), } result, count, new_id, error = dao.do_execute_sql(sql=sql, conn=conn, ret=True, commit=False, args=args) return error
def update_user_info(user_id=None, username=None, nickname=None, icon=None, background=None, tag=None, style=None, info_payload=None): if user_id is None and username is None: return False data = [] if nickname is not None: data.append("nickname=%(nickname)s") if icon is not None: data.append("icon=%(icon)s") if background is not None: data.append("bg_image=%(background)s") if tag is not None: data.append("tag=%(tag)s") if style is not None: data.append("style=%(style)s") if info_payload is not None: data.append("info_payload=%(info_payload)s") if len(data) == 0: return False params = '' for item in data: if len(params) > 0: params += ',' params += item if user_id is not None: sql = "UPDATE user SET {} where id=%(user_id)s".format(params) else: sql = "UPDATE user SET {} where username=%(username)s".format(params) args = { 'nickname': nickname, 'icon': icon, 'background': background, 'tag': tag, 'style': style, 'info_payload': info_payload, 'user_id': user_id, 'username': username } result, count, new_id, error = dao.do_execute_sql(sql, ret=False, args=args) return True if error is None else False
def check_file(file_hash=None, forever=None, conn=None): if forever is None: sql = "SELECT * from file WHERE `hash` = %(hash)s limit 1" else: sql = "SELECT * from file WHERE `hash` = %(hash)s and `forever` = %(forever)s limit 1" result, count, new_id, error = dao.do_execute_sql(sql=sql, conn=conn, kv=True, args={ 'hash': file_hash, 'forever': forever }) if count > 0: return result[0] return None
def update_owner(album_id=None, user_id=None, conn=None, commit=True): sql = "UPDATE album SET user_id=%(user_id)s where id=%(album_id)s" args = { 'user_id': user_id, 'album_id': album_id, } result, count, new_id, error = dao.do_execute_sql(sql=sql, args=args, conn=conn, commit=commit) if count: return True else: return False
def user_file_size(user_id, id, conn=None): sql = "SELECT \ COALESCE(sum(size), 0) as size\ from user_file \ left join file on user_file.file_id = file.id\ where ((find_in_set(%(id)s, user_file.directory_path) or (user_file.id=%(id)s)) \ and user_file.type = 0 \ and user_file.user_id=%(user_id)s)" args = {'id': '' if id == -1 else id, 'user_id': user_id} result, count, new_id, error = dao.do_execute_sql(sql=sql, args=args, conn=conn, kv=True) capacity = 0 if result is not None and len(result) > 0: capacity = result[0]['size'] return error, capacity
def user_file_files_relative_with_id(user_id, id, conn=None): sql = "SELECT \ user_file.id as id, \ user_file.file_id as file_id, \ user_file.type as type,\ user_file.personal_name as name, \ file.mime as mime,\ file.filename as filename,\ file.forever as forever\ from user_file \ left join file on user_file.file_id = file.id\ where ((find_in_set(%(id)s, user_file.directory_path) or (user_file.id=%(id)s)) \ and user_file.type = 0 \ and user_file.user_id=%(user_id)s)" args = {'id': '' if id == -1 else id, 'user_id': user_id} result, count, new_id, error = dao.do_execute_sql(sql=sql, conn=conn, kv=True, commit=False, args=args) return result, error
def file_set(id, conn=None, args=None): if len(args.keys()) == 0: return False if args is None: args = {} params = '' for k, v in args.items(): if len(params) > 0: params += ',' params += '{}=%({})s'.format(k, k) sql = "UPDATE file SET {} where id=%(id)s".format(params) args['id'] = id result, count, new_id, error = dao.do_execute_sql(sql=sql, conn=conn, ret=False, commit=False, args=args) return True if error is None else False
def user_file_need_remove_from_disk(delete_user_files, conn=None): file_ids = [ str(file['file_id']) for file in delete_user_files if file['forever'] == 0 ] file_ids = list(set(file_ids)) sql = "SELECT file_id from user_file where find_in_set(file_id, %(file_ids)s)" args = { 'file_ids': ",".join(file_ids), } exist, count, new_id, error = dao.do_execute_sql(sql=sql, conn=conn, kv=True, ret=True, commit=False, args=args) if error is not None: return [], error exist_ids = [str(file['file_id']) for file in exist] file_ids = [file_id for file_id in file_ids if file_id not in exist_ids] return file_ids, None
def new_file(conn=None, filename='', mime='', exif_time=0, exif_info=None, exif_lalo=None, file_hash=None, size=0, forever=0): timestamp = RGTimeUtil.timestamp() sql = "INSERT INTO `file` \ (filename, mime, exif_timestamp, `timestamp`, `exif_info`, `exif_lalo`, `hash`, `size`, `forever`) \ VALUES \ (%(filename)s, %(mime)s, %(exif_timestamp)s, %(timestamp)s, \ %(exif_info)s, %(exif_lalo)s, %(hash)s, %(size)s, %(forever)s)" args = { 'filename': filename, 'mime': mime, 'exif_timestamp': exif_time, 'timestamp': timestamp, 'exif_info': exif_info, 'exif_lalo': exif_lalo, 'hash': file_hash, 'size': str(size), 'forever': forever } result, count, new_id, err = dao.do_execute_sql(sql=sql, conn=conn, new_id=True, args=args, commit=False) if count > 0: args['id'] = new_id return args else: return None
def new_album(user_id, title='', desc='', cover=None, level=0, timestamp=None, conn=None, commit=True): if timestamp is None: timestamp = RGTimeUtil.timestamp() sql = new_album_sql(user_id_key='user_id', title_key='title', desc_key='desc', cover_key='cover', level_key='level', timestamp_key='timestamp') args = { 'desc': desc, 'title': title, 'user_id': user_id, 'cover': cover, 'level': level, 'timestamp': timestamp } result, count, new_id, error = dao.do_execute_sql(sql, new_id=True, conn=conn, commit=commit, args=args) if count > 0: return album(new_id, user_id, title, desc, cover, level, timestamp) else: return None
def add_or_update_art(user_id, title=None, content='', cate=0, group_id=None, art_id=None, summary='', cover='', conn=None, commit=True, timestamp=None): if timestamp is None: timestamp = RGTimeUtil.timestamp() g = Goose({ 'browser_user_agent': 'Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebKit/537.36 (KHTML, like Gecko)\ Chrome/57.0.2987.110 Safari/537.36', 'stopwords_class': StopWordsChinese }) h5_format = \ '<!DOCTYPE html> \ <html lang = "zh"> \ <head> \ <meta charset = "UTF-8"> \ <title> blog </title > \ </head> \ <body> \ <article><div> %s </div></article> \ </body> \ </html>' art_parse = g.extract(raw_html=h5_format % content) open_graph = art_parse.opengraph p_summary = '' p_cover = '' if 'description' in open_graph: p_summary = open_graph['description'] if p_summary is not None and len(p_summary) is 0: p_summary = art_parse.cleaned_text if p_summary is not None and len(p_summary) is not 0: summary = p_summary if len(summary) >= 100: summary = summary[:97] + '...' if 'image' in open_graph: p_cover = open_graph['image'] if p_cover is '' and art_parse.top_image is not None: p_cover = art_parse.top_image.src if p_cover is not None and len(p_cover) is not 0: cover = p_cover if cover is not None and len(cover) > 0: try: parsed_tuple = parse.urlparse(cover) if parsed_tuple.netloc.endswith( RGDomainName) or parsed_tuple.hostname is None: p_cover = parsed_tuple.path index = p_cover.find(FilePreFix) if index == 0 or index == 1: cover = p_cover index = cover.rfind(RGQualityName + '.') if index != -1: cover = cover[0:index] + RGThumbnailName + cover[ index + len(RGQualityName):] except Exception as e: print(e) if group_id is not None and int(group_id) < 0: group_id = None if art_id is None: sql = "INSERT INTO art \ (title, summary, cate, user_id, group_id, cover, content, addtime, updatetime, create_time) \ VALUES (%(title)s, %(summary)s, %(cate)s, %(user_id)s, %(group_id)s, %(cover)s, %(content)s, \ {}, {}, from_unixtime({}))".format(timestamp, timestamp, timestamp / 1000) else: art_id = int(art_id) sql = "UPDATE art SET \ title = %(title)s, summary=%(summary)s, cate=%(cate)s, \ user_id=%(user_id)s, group_id=%(group_id)s, cover=%(cover)s, \ content=%(content)s, updatetime=%(timestamp)s, group_id=%(group_id)s \ WHERE id=%(art_id)s and user_id =%(user_id)s" args = { 'title': title, 'summary': summary, 'cate': cate, 'user_id': user_id, 'group_id': group_id, 'cover': cover, 'content': content, 'timestamp': timestamp, 'art_id': art_id, } result, count, new_id, err = dao.do_execute_sql(sql=sql, kv=True, new_id=True, conn=conn, commit=commit, args=args) if count > 0: return True, art_id if art_id is not None else new_id else: return False, 0