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
