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 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 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 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 user_with_db_result(result=None, need_pwd=False, need_username=False, need_icon=False, icon_name=None, need_bg=False, bg_name=None, need_email=False): if need_icon: icon = files.filename( result[6], needUrl=True) if icon_name is None else url_with_name( icon_name, thumb=True) else: icon = None if need_bg: bg = files.filename( result[7], needUrl=True) if bg_name is None else url_with_name( bg_name, original=True) else: bg = None username = result[1] if need_email: data = User.RGOpenIdController.user_data(username=username) email = data['email'] if (data is not None and 'email' in data) else None else: email = None my_user = user(result[0], username if need_username else None, result[2] if need_pwd else None, result[3], result[4], result[5], icon, bg, result[8], result[9], result[10], result[11], result[12], result[13], result[14], email) my_user.iconId = result[6] my_user.bgId = result[7] return my_user
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 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