def add_art_read_count(ids=None, counts=None): try: sql = "UPDATE art SET `read_count` = `read_count` + case id \ {} where id in ({})".format('{}', '{}') case = '' update_ids = '' for index in range(len(ids)): a_id = int(ids[index]) count = int(counts[index]) if count is not 0: case += ('when %ld then %ld ' % (a_id, count)) if index is 0: update_ids += '{}'.format(a_id) else: update_ids += ',{}'.format(a_id) if len(update_ids) <= 0: return True case += 'end' sql = sql.format(case, update_ids) dao.execute_sql(sql, ret=False) return True
def page_list(other_id=None, art_user_id=-1, page=1, size=10): size = int(size) relation = -1 page = int(page) if page < 1: page = 1 if other_id is None: sql = 'select art.* from art left join art_group as g on g.id = art.group_id \ where \ art.user_id=%(art_user)s \ and cate <= 0 \ and (g.id is NULL or g.level <= 0) \ order by addtime desc' relation = 0 elif user.isHome(art_user_id, other_id): relation = 2 sql = 'select art.* from art where user_id=%(art_user)s order by addtime desc' else: sql = 'select art.*, relation from art \ left join art_group as g on g.id = art.group_id \ left join (select relation from user_relation \ where m_user_id = %(art_user)s and o_user_id = %(other_id)s) as r on 1=1 \ where \ art.user_id=%(art_user)s \ and (g.id is NULL or g.level <= 0 or g.level <= r.relation) \ and (cate <= 0 or cate <= r.relation) \ order by addtime desc' args = { 'art_user': art_user_id, 'other_id': other_id, } result, count, new_id = dao.execute_sql(sql, ret=False, args=args) page_count = int(operator.truediv(count - 1, size)) + 1 page = min(page, page_count) sql += ' limit %d offset %d' % (size, (page - 1) * size) result, this_page_count, new_id = dao.execute_sql(sql, kv=True, args=args) page = page if this_page_count > 0 else page_count if result is not None: for row in result: if relation is -1 and 'relation' in row: relation = row['relation'] if relation is None: relation = 0 del row['content'] return result, page_count, page, size, count, relation
def destroy_token(user_id, token_type): sql = 'delete from tokens where user_id=%(user_id)s and type=%(token_type)s' try: dao.execute_sql(sql, args={ 'user_id': user_id, 'token_type': token_type }) return True except: return False
def page_list(other_id, album_id, page=1, size=10, relation=0): # type: (int, int, int, int, int) -> (list, int, int, int, int) size = int(size) other_id = int(other_id) album_id = int(album_id) page = int(page) if page < 1: page = 1 sql = "SELECT pic.id, pic.title, pic.description, pic.level, file.timestamp, file.exif_timestamp, file.filename\ FROM pic\ left join file on pic.file_id = file.id\ where user_id=%(other_id)s and album_id=%(album_id)s {} order by pic.id desc".format('{}') if relation == -1: sql = sql.format('') elif relation == 0: sql = sql.format('and pic.level=0') elif relation == 1: sql = sql.format('and pic.level<=1') else: return None, 0, 0, 0, 0 result, count, new_id = dao.execute_sql(sql, ret=False, args={ 'other_id': other_id, 'album_id': album_id }) page_count = int(operator.truediv(count - 1, size)) + 1 page = min(page, page_count) sql += ' limit %d offset %d' % (size, (page - 1) * size) result, this_page_count, new_id = dao.execute_sql(sql, kv=True, args={ 'other_id': other_id, 'album_id': album_id }) page = page if this_page_count > 0 else page_count for row in result: row['url'] = url_with_name(row['filename'], thumb=True) row['qUrl'] = url_with_name(row['filename']) return result, page_count, page, size, count
def update_group_info(user_id=None, g_id=None, name=None, level=None): sql = 'UPDATE art_group SET {} where id=%(g_id)s and user_id=%(user_id)s'.format( '{}') data = [] if name: data.append("name=%(name)s") if level: data.append("level=%(level)s") if len(data) == 0: return False params = '' for item in data: if len(params) > 0: params += ',' params += item sql = sql.format(params) result, count, new_id = dao.execute_sql(sql, ret=False, args={ 'level': level, 'name': name, 'user_id': user_id, 'g_id': g_id }) if count > 0: return True else: return False
def album_detail(album_id, relation): album_id = int(album_id) sql = "SELECT * FROM album where id=%(album_id)s {}".format('{}') if relation == -1: sql = sql.format('') elif relation == 0: sql = sql.format('and level=0') elif relation == 1: sql = sql.format('and level<=1') elif relation == 2: return False, None else: return False, None result, count, new_id = dao.execute_sql(sql, kv=True, ret=True, args={'album_id': album_id}) if result is not None and count > 0: return True, result[0] else: return False, None
def art_detail(user_id, art_id): sql = \ 'SELECT art.id, art.title, art.summary, \ art.cate, art.user_id, art.cover, \ art.content, art.addtime, art.updatetime, art.read_count, \ art_group.name as "group_name", art_group.id as "group_id", art_group.level as "group_level"\ FROM art \ left join art_group on art_group.id = art.group_id\ left join user_relation as r on r.m_user_id = art.user_id and r.o_user_id=%(other_id)s \ where art.id = %(art_id)s and ((art.user_id = %(other_id)s) or\ (art_group.id is NULL \ or \ art_group.level <= 0 \ or \ art_group.level <= r.relation \ ) \ and (\ art.cate <= 0\ or \ art.cate <= r.relation \ ))' result, count, new_id = dao.execute_sql(sql, kv=True, args={ 'art_id': art_id, 'other_id': user_id }) if count: art = result[0] art['logicCate'] = max( art['cate'], art['group_level'] if art['group_level'] is not None else 0) return result[0] return None
def update_info(p_id=None, user_id=None, title=None, desc=None, level=None): if p_id is None or user_id is None: return False sql = "UPDATE pic SET {} where id=%(p_id)s and user_id=%(user_id)s".format('{}') data = [] if title is not None: data.append("title=%(title)s") if desc is not None: data.append("description=%(desc)s") if level is not None: data.append("level=%(level)s") if len(data) == 0: return False params = '' for item in data: if len(params) > 0: params += ',' params += item sql = sql.format(params) result, count, new_id = dao.execute_sql(sql, ret=False, args={ 'p_id': p_id, 'user_id': user_id, 'title': title, 'desc': desc, 'level': level }) if count > 0: return True else: return False
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_login(username, pwd): sql = "SELECT * FROM user where username=%(username)s" result, count, new_id = dao.execute_sql(sql=sql, args={'username': username}) _user = None if count <= 0: code, data = User.RGOpenIdController.auth(username=username, password=pwd) if code == RGResCode.ok and data is not None and len(data) > 0: _user = new_user(username=username, pwd=pwd) else: _user = user_with_db_result(result[0], need_pwd=True, need_username=True, need_email=True) if _user.is_active_and_need_bind_email(): if _user.pwd != sha256_key(username, pwd): _user = None else: code, data = User.RGOpenIdController.auth(username=username, password=pwd) if code == RGResCode.ok and data is not None and len(data) > 0: pass else: _user = None return _user
def friend_page_list(user_id, page=1, size=10): user_id = int(user_id) size = int(size) page = int(page) if page < 1: page = 1 sql_temp = "SELECT %s FROM user_relation as re, user as u %s \ where re.relation=1 and re.m_user_id=%s and re.o_user_id = u.id \ order by %s DESC" % ('%s', '%s', '%s', '%s') sql = sql_temp % ('count(*)', '', '%(user_id)s', 're.addtime') result, count, new_id = dao.execute_sql(sql, ret=True, args={ 'user_id': user_id, }) if count > 0: count = result[0][0] page_count = int(operator.truediv(count - 1, size)) + 1 page = min(page, page_count) sql = sql_temp % ("re.addtime as 'follow_time', file.filename as 'icon', \ u.nickname, u.tag, u.id as 'ID', u.title as 'title', u.description as 'desc', u.addtime as 'addTime'", ' left join file on file.id = u.icon', '%(user_id)s', 'follow_time') offset = (size, (page - 1) * size) sql += (' limit %d offset %d' % offset) result, this_page_count, new_id = dao.execute_sql(sql, kv=True, args={ 'user_id': user_id, }) page = page if this_page_count > 0 else page_count for row in result: icon = row['icon'] if icon is not None and len(icon): row['icon'] = url_with_name(icon, thumb=True) return result, page_count, page, size, count
def login_sign_check(username): sql = "SELECT * FROM user where username=%(username)s and is_active=1" result, count, new_id = dao.execute_sql(sql, ret=True, args={'username': username}) _user = None if count > 0: _user = user_with_db_result(result[0]) return _user
def update_group_order(user_id=None, ids=None, orders=None): try: sql = "UPDATE art_group SET `order` = case id \ {} where id in ({}) and user_id=%(user_id)s".format('{}', '{}') case = '' for index in range(len(ids)): gid = int(ids[index]) order = int(orders[index]) case += ('when %ld then %ld ' % (gid, order)) case += 'end' update_ids = ",".join(str(i) for i in ids) sql = sql.format(case, update_ids) dao.execute_sql(sql, ret=False, args={'user_id': user_id}) return True except: return False
def get_user_icon_name(user_id): sql = """SELECT file.filename FROM user left join file on file.id = user.icon where user.id=%(user_id)s""" result, count, new_id = dao.execute_sql(sql, args={'user_id': user_id}) if count: result = result[0] return result else: return None
def del_albums(user_id, ids=[]): ids_str = ",".join(str(i) for i in ids) sql = 'delete album from album, user \ where user.id = album.user_id and user.default_album_id != album.id and album.user_id=%s and album.id in (%s)' % ( '%(user_id)s', ids_str) result, count, new_id = dao.execute_sql(sql, args={'user_id': user_id}) if count > 0: return True else: return False
def update_desc(user_id, desc): sql = "UPDATE user SET description = %(desc)s where id=%(user_id)s" result, count, new_id = dao.execute_sql(sql, ret=False, args={ 'desc': desc, 'user_id': user_id }) if count > 0: return True else: return False
def update_title(user_id, title): sql = "UPDATE user SET title = %(title)s where id=%(user_id)s" result, count, new_id = dao.execute_sql(sql, ret=False, args={ 'title': title, 'user_id': user_id }) if count > 0: return True else: return False
def update_name(user_id, name): sql = "UPDATE user SET nickname = %(name)s where id=%(user_id)s" result, count, new_id = dao.execute_sql(sql, ret=False, args={ 'name': name, 'id': user_id }) if count > 0: return True else: return False
def get_user_with_username(username, need_email=True): sql = "SELECT * FROM user where username=%(username)s" result, count, new_id = dao.execute_sql(sql, args={'username': username}) if count > 0: return user_with_db_result(result[0], need_pwd=True, need_username=True, need_email=need_email) else: return None
def LogToSteady(label, level, message, timestamp, dp=0): """ Write log to the runtime steady logging data model. :param label: event label :param level: event level :param message: event description :param timestamp: event timestamp :param dp: depth of exception stack """ t = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(timestamp)) sql = "INSERT INTO log(label, level, message, timestamp) VALUES \ (%(label)s, %(level)s, %(message)s, %(t)s)" from DAO import rg_dao as dao dao.execute_sql(sql=sql, dp=dp, args={ 'label': label, 'level': level, 'message': message, 't': t })
def info(p_id=None, user_id=None): if p_id is None or user_id is None: return False, None sql = "select * from pic where id=%(p_id)s and user_id=%(user_id)s" result, count, new_id = dao.execute_sql(sql, kv=True, args={ 'p_id': p_id, 'user_id': user_id }) if count > 0: return True, result[0] else: return False, None
def id_list(user_id, album_id, current_id=1, size=1, relation=0): # type: (int, int, int, int, int) -> (list, int, int, int, int) user_id = int(user_id) album_id = int(album_id) current_id = int(current_id) size = int(size) relation = int(relation) if relation == -1: level_sql = '' elif relation == 0: level_sql = 'and pic.level=0' elif relation == 1: level_sql = 'and pic.level<=1' else: return False, [], [] sql_format = \ '(' + 'SELECT pic.id, pic.title, pic.description, pic.level, \ file.id as "fileId", file.filename as "url", \ file.exif_timestamp as `exif_timestamp`, file.exif_lalo as `exif_lalo` \ FROM pic left join file on pic.file_id = file.id \ where pic.user_id=%(user_id)s and pic.album_id=%(album_id)s {} and {} \ {} limit {}'.format('{}', '{}', '{}', '{}') + ')' sql = sql_format.format(level_sql, 'pic.id < %ld' % current_id, 'order by pic.id desc', size) sql += 'UNION' sql += sql_format.format(level_sql, 'pic.id >= %ld' % current_id, 'order by pic.id', size + 1) result, count, new_id = dao.execute_sql(sql, kv=True, args={ 'user_id': user_id, 'album_id': album_id }) pre_id = [] next_id = [] for row in result: if row['id'] < current_id: next_id.append(row) elif row['id'] > current_id: pre_id.append(row) else: current = row row['oUrl'] = url_with_name(row['url'], original=True) row['qUrl'] = url_with_name(row['url']) row['url'] = url_with_name(row['url'], thumb=True) # pre_id.reverse() return True, pre_id, next_id, current
def new_group(user_id=None, name='', order=0, level=0): sql = new_group_sql() result, count, new_id = dao.execute_sql(sql, new_id=True, args={ 'name': name, 'user_id': user_id, 'order': order, 'level': level }) if count: return True, new_id else: return False, -1
def certify_token(token, token_type=0): sql = "SELECT * FROM tokens where token=%(token)s AND type=%(token_type)s" result, count, new_id = dao.execute_sql(sql, args={ 'token': token, 'token_type': token_type }) if count: user_id = result[0][0] return user_id, RGTokenUtil.certify_token(user_id, token) else: return None, False
def months_list_view(art_user=None, other_id=None, group_id=None, timezone=8): timezone = '%+d' % timezone time_format = '%%Y-%%m' if other_id is None: sql = 'SELECT date_format(CONVERT_TZ(create_time, @@session.time_zone, "{}:00"), "{}") months, count(art.id) as "count" \ FROM art \ left join art_group as g on g.id = art.group_id \ where \ art.user_id=%(art_user)s \ and cate <= 0 \ and (g.id is NULL or g.level <= 0) \ {} \ group by months order by months desc'.format( timezone, time_format, '{}') elif user.isHome(art_user, other_id): sql = 'SELECT date_format(CONVERT_TZ(create_time, @@session.time_zone, "{}:00"), "{}") months, count(art.id) as "count" \ FROM art where user_id=%(art_user)s {} \ group by months order by months desc'.format( timezone, time_format, '{}') else: sql = 'SELECT date_format(CONVERT_TZ(create_time, @@session.time_zone, "{}:00"), "{}") months, count(art.id) as "count" \ FROM art \ left join art_group as g on g.id = art.group_id \ left join (select relation from user_relation \ where m_user_id = %(art_user)s and o_user_id = %(other_id)s) as r on 1=1 \ where art.user_id=%(art_user)s \ and (g.id is NULL or g.level <= 0 or g.level <= r.relation) \ and (cate <= 0 or cate <= r.relation) \ {} \ group by months order by months desc'.format( timezone, time_format, '{}') if group_id is None: sql = sql.format('') elif group_id < 0: sql = sql.format( 'and (group_id is null or group_id not in (SELECT id from art_group))' ) else: sql = sql.format('and group_id=%(group_id)s') result, count, new_id = dao.execute_sql(sql, kv=True, args={ 'art_user': art_user, 'other_id': other_id, 'group_id': group_id, }) return result
def del_art(user_id, art_id=None): if art_id is None or art_id is '': return False, 0 sql = "DELETE from art where id=%(art_id)s and user_id=%(user_id)s" result, count, new_id = dao.execute_sql(sql, args={ 'art_id': art_id, 'user_id': user_id }) if count > 0: return True, art_id else: return False, 0
def filename(file_id, needUrl=False): if file_id is None: return None sql = 'SELECT * FROM file where id=%(file_id)s' result, count, new_id = dao.execute_sql(sql, kv=True, args={'file_id': file_id}) if count > 0: name = result[0]['filename'] if needUrl: return url_with_name(name) else: return name else: return None
def album_list(user_id, other_id): relation = 0 lastPicUrl = 'lastPicUrl' coverUrl = 'coverUrl' sql = "SELECT ab.*, covers.filename as '{}', jp.filename as '{}' \ FROM \ album as ab \ left join (select file.filename, pic.album_id \ from pic, file \ where pic.file_id = file.id and pic.user_id = %(other_id)s \ order by file.timestamp desc limit 1) as jp \ on jp.album_id = ab.id \ left join (select file.filename, pic.id \ from pic, file \ where pic.file_id = file.id and pic.user_id = %(other_id)s) as covers \ on covers.id = ab.cover \ where ab.user_id=%(other_id)s {} order by ab.id desc".format( coverUrl, lastPicUrl, '{}') if user.isHome(user_id, other_id): sql = sql.format('') else: relation = user.get_relation(other_id, user_id) if relation == 0: sql = sql.format('and level=0') elif relation == 1: sql = sql.format('and level<=1') else: return None, relation result, count, new_id = dao.execute_sql(sql, ret=True, kv=True, args={'other_id': other_id}) if count > 0: for row in result: if row[lastPicUrl] is not None: row[lastPicUrl] = url_with_name(row[lastPicUrl], thumb=True) if row[coverUrl] is not None: row[coverUrl] = url_with_name(row[coverUrl], thumb=True) return result, relation else: return None, relation
def group_list(other_id=None, relation=0): if relation != -1: if relation == 0: sql = 'SELECT * FROM art_group where user_id=%(other_id)s and `level`=0' elif relation == 1: sql = 'SELECT * FROM art_group where user_id=%(other_id)s and `level`<=1' else: return True, None else: sql = 'SELECT * FROM art_group where user_id=%(other_id)s' sql += " order by `order` desc, id asc" result, count, new_id = dao.execute_sql(sql, kv=True, args={'other_id': other_id}) if count: return True, result else: return True, []
def get_relation(my_id, other_id): # type: (int, int) -> int if my_id is None or other_id is None: return 0 my_id = int(my_id) other_id = int(other_id) if my_id == other_id: return -1 sql = "SELECT * FROM user_relation where m_user_id =%(my_id)s AND o_user_id=%(other_id)s" result, count, new_id = dao.execute_sql(sql, kv=True, args={ 'my_id': my_id, 'other_id': other_id }) if count > 0: return result[0]['relation'] else: return 0