def add(self,
date: str,
header: str,
sender: str,
to: str = None,
body: str = None,
html: str = None) -> DBResult:
session = NBdb().get_session()
try:
new_email = Email(mail_hash=self.mail_hash,
date=date,
header=header,
sender=sender,
to=to,
body=body,
html=html,
created_at=datetime.now())
session.add(new_email)
session.commit()
result = DBResult(error=False, info='Success added', result=0)
except Exception as e:
session.rollback()
result = DBResult(error=True, info=repr(e), result=-1)
finally:
session.close()
return result
def rand_illust(cls, num: int, nsfw_tag: int):
session = NBdb().get_session()
_res = session.query(Pixiv.pid).filter(Pixiv.nsfw_tag == nsfw_tag).order_by(func.random()).limit(num).all()
pid_list = []
for pid in _res:
pid_list.append(pid[0])
return pid_list
def get_info(self) -> DBResult:
session = NBdb().get_session()
try:
# 已存在则更新
exist_box = session.query(EmailBox).filter(
EmailBox.address == self.address).one()
server_host = exist_box.server_host
port = exist_box.port
password = exist_box.password
res_dict = {
'server_host': server_host,
'port': port,
'password': password
}
result = DBResult(error=False, info='Success', result=res_dict)
except NoResultFound:
result = DBResult(error=True, info='NoResultFound', result={})
except MultipleResultsFound:
result = DBResult(error=True,
info='MultipleResultsFound',
result={})
except Exception as e:
result = DBResult(error=True, info=repr(e), result={})
finally:
session.close()
return result
def list(cls) -> DBResult:
session = NBdb().get_session()
try:
res = [x[0] for x in session.query(EmailBox.address).all()]
result = DBResult(error=False, info='Success', result=res)
except Exception as e:
result = DBResult(error=True, info=repr(e), result=[])
finally:
session.close()
return result
def permission_set(self,
notice: int = 0,
command: int = 0,
level: int = 0) -> DBResult:
session = NBdb().get_session()
# 检查群组是否在表中, 存在则直接更新状态
try:
exist_group = session.query(Group).filter(
Group.group_id == self.group_id).one()
exist_group.notice_permissions = notice
exist_group.command_permissions = command
exist_group.permission_level = level
exist_group.updated_at = datetime.now()
session.commit()
result = DBResult(error=False, info='Success', result=0)
except NoResultFound:
result = DBResult(error=True, info='NoResultFound', result=-1)
except MultipleResultsFound:
result = DBResult(error=True,
info='MultipleResultsFound',
result=-1)
except Exception as e:
session.rollback()
result = DBResult(error=True, info=repr(e), result=-1)
finally:
session.close()
return result
def status(self) -> DBResult:
session = NBdb().get_session()
try:
res = session.query(Vocation.status).filter(
Vocation.user_id == self.id().result).one()
result = DBResult(error=False, info='Success', result=res[0])
except Exception as e:
result = DBResult(error=True, info=repr(e), result=-1)
finally:
session.close()
return result
def permission_level(self) -> DBResult:
session = NBdb().get_session()
try:
res = session.query(Group.permission_level).filter(
Group.group_id == self.group_id).one()
result = DBResult(error=False, info='Success', result=res[0])
except Exception as e:
result = DBResult(error=True, info=repr(e), result=-1)
finally:
session.close()
return result
def mailbox_list(self) -> DBResult:
session = NBdb().get_session()
if self.exist():
mailbox_list = session.query(EmailBox.address).join(GroupEmailBox).\
filter(EmailBox.id == GroupEmailBox.email_box_id). \
filter(GroupEmailBox.group_id == self.id().result).all()
res = [x[0] for x in mailbox_list]
result = DBResult(error=False, info='Success', result=res)
else:
result = DBResult(error=True, info='Group not exist', result=[])
session.close()
return result
def skill_list(self) -> DBResult:
session = NBdb().get_session()
res = []
if self.exist():
for item in session.query(Skill.name, UserSkill.skill_level).join(UserSkill). \
filter(Skill.id == UserSkill.skill_id). \
filter(UserSkill.user_id == self.id().result).all():
res.append(item)
result = DBResult(error=False, info='Success', result=res)
else:
result = DBResult(error=True, info='User not exist', result=res)
session.close()
return result
def member_list(self) -> DBResult:
session = NBdb().get_session()
res = []
if self.exist():
for item in session.query(User.qq, UserGroup.user_group_nickname).join(UserGroup). \
filter(User.id == UserGroup.user_id). \
filter(UserGroup.group_id == self.id().result).all():
res.append(item)
result = DBResult(error=False, info='Success', result=res)
else:
result = DBResult(error=True, info='Group not exist', result=res)
session.close()
return result
def vocation_status(self) -> DBResult:
session = NBdb().get_session()
try:
status, stop_time = session.query(Vocation.status, Vocation.stop_at). \
filter(Vocation.user_id == self.id().result).one()
result = DBResult(error=False,
info='Success',
result=[status, stop_time])
except Exception as e:
result = DBResult(error=True, info=repr(e), result=[-1, None])
finally:
session.close()
return result
def id(self) -> DBResult:
session = NBdb().get_session()
try:
pixiv_table_id = session.query(Pixiv.id).filter(Pixiv.pid == self.pid).one()[0]
result = DBResult(error=False, info='Success', result=pixiv_table_id)
except NoResultFound:
result = DBResult(error=True, info='NoResultFound', result=-1)
except MultipleResultsFound:
result = DBResult(error=True, info='MultipleResultsFound', result=-1)
except Exception as e:
result = DBResult(error=True, info=repr(e), result=-1)
finally:
session.close()
return result
def permission_command(self) -> DBResult:
session = NBdb().get_session()
try:
res = session.query(Group.command_permissions).filter(
Group.group_id == self.group_id).one()
if res and res[0] == 1:
result = DBResult(error=False, info='Success', result=1)
else:
result = DBResult(error=False, info='Success', result=0)
except Exception as e:
result = DBResult(error=True, info=repr(e), result=-1)
finally:
session.close()
return result
def subscription_list(self) -> DBResult:
session = NBdb().get_session()
res = []
if self.exist():
for item in session.query(Subscription.sub_type, Subscription.sub_id, Subscription.up_name).\
join(GroupSub).\
filter(Subscription.id == GroupSub.sub_id). \
filter(GroupSub.group_id == self.id().result).all():
res.append(item)
result = DBResult(error=False, info='Success', result=res)
else:
result = DBResult(error=True, info='Group not exist', result=res)
session.close()
return result
def clear_time_out_event(cls):
session = NBdb().get_session()
events = session.query(CoolDownEvent).order_by(CoolDownEvent.id).all()
for event in events:
try:
if datetime.now() >= event.stop_at:
session.delete(event)
session.commit()
except Exception:
session.rollback()
continue
def id(self) -> DBResult:
session = NBdb().get_session()
try:
bilidynamic_table_id = session.query(Bilidynamic.id).\
filter(Bilidynamic.uid == self.uid).\
filter(Bilidynamic.dynamic_id == self.dynamic_id).one()[0]
result = DBResult(error=False, info='Success', result=bilidynamic_table_id)
except NoResultFound:
result = DBResult(error=True, info='NoResultFound', result=-1)
except MultipleResultsFound:
result = DBResult(error=True, info='MultipleResultsFound', result=-1)
except Exception as e:
result = DBResult(error=True, info=repr(e), result=-1)
finally:
session.close()
return result
def add(self, sub_type: str = None, group_id: int = None, user_id: int = None, user_name: str = None,
raw_data: str = None, msg_data: str = None) -> DBResult:
session = NBdb().get_session()
try:
new_event = History(time=self.time, self_id=self.self_id,
post_type=self.post_type, detail_type=self.detail_type, sub_type=sub_type,
group_id=group_id, user_id=user_id, user_name=user_name,
raw_data=raw_data, msg_data=msg_data, created_at=datetime.now())
session.add(new_event)
session.commit()
result = DBResult(error=False, info='Success added', result=0)
except Exception as e:
session.rollback()
result = DBResult(error=True, info=repr(e), result=-1)
finally:
session.close()
return result
def name(self) -> DBResult:
session = NBdb().get_session()
try:
group_name = session.query(
Group.name).filter(Group.group_id == self.group_id).one()[0]
result = DBResult(error=False, info='Success', result=group_name)
except NoResultFound:
result = DBResult(error=True, info='NoResultFound', result='')
except MultipleResultsFound:
result = DBResult(error=True,
info='MultipleResultsFound',
result='')
except Exception as e:
result = DBResult(error=True, info=repr(e), result='')
finally:
session.close()
return result
def list_illust(self, nsfw_tag: int) -> DBResult:
session = NBdb().get_session()
try:
pid_list = session.query(Pixiv.pid).join(PixivT2I).join(PixivTag). \
filter(Pixiv.id == PixivT2I.illust_id). \
filter(PixivT2I.tag_id == PixivTag.id). \
filter(Pixiv.nsfw_tag == nsfw_tag). \
filter(PixivTag.tagname.ilike(f'%{self.tagname}%')).all()
tag_pid_list = []
for pid in pid_list:
tag_pid_list.append(pid[0])
result = DBResult(error=False, info='Success', result=tag_pid_list)
except Exception as e:
result = DBResult(error=True, info=repr(e), result=[])
finally:
session.close()
return result
def permission_info(self) -> DBResult:
session = NBdb().get_session()
res = {}
# 检查群组是否在表中, 存在则直接更新状态
try:
notice, command, level = session.query(Group.notice_permissions,
Group.command_permissions,
Group.permission_level).\
filter(Group.group_id == self.group_id).one()
res['notice'] = notice
res['command'] = command
res['level'] = level
result = DBResult(error=False, info='Success', result=res)
except NoResultFound:
result = DBResult(error=True, info='NoResultFound', result=res)
except MultipleResultsFound:
result = DBResult(error=True,
info='MultipleResultsFound',
result=res)
except Exception as e:
session.rollback()
result = DBResult(error=True, info=repr(e), result=res)
finally:
session.close()
return result
def id(self) -> DBResult:
session = NBdb().get_session()
try:
email_box_table_id = session.query(
EmailBox.id).filter(EmailBox.address == self.address).one()[0]
result = DBResult(error=False,
info='Success',
result=email_box_table_id)
except NoResultFound:
result = DBResult(error=True, info='NoResultFound', result=-1)
except MultipleResultsFound:
result = DBResult(error=True,
info='MultipleResultsFound',
result=-1)
except Exception as e:
result = DBResult(error=True, info=repr(e), result=-1)
finally:
session.close()
return result
def id(self) -> DBResult:
session = NBdb().get_session()
try:
subscription_table_id = session.query(Subscription.id).filter(Subscription.sub_type == self.sub_type).\
filter(Subscription.sub_id == self.sub_id).one()[0]
result = DBResult(error=False,
info='Success',
result=subscription_table_id)
except NoResultFound:
result = DBResult(error=True, info='NoResultFound', result=-1)
except MultipleResultsFound:
result = DBResult(error=True,
info='MultipleResultsFound',
result=-1)
except Exception as e:
result = DBResult(error=True, info=repr(e), result=-1)
finally:
session.close()
return result
def nickname(self) -> DBResult:
session = NBdb().get_session()
try:
user_nickname = session.query(
User.nickname).filter(User.qq == self.qq).one()[0]
result = DBResult(error=False,
info='Success',
result=user_nickname)
except NoResultFound:
result = DBResult(error=True, info='NoResultFound', result='')
except MultipleResultsFound:
result = DBResult(error=True,
info='MultipleResultsFound',
result='')
except Exception as e:
result = DBResult(error=True, info=repr(e), result='')
finally:
session.close()
return result
def vocation_member_list(self) -> DBResult:
session = NBdb().get_session()
res = []
# 查询所有没有假的人
try:
for nickname, stop_at in session.query(UserGroup.user_group_nickname, Vocation.stop_at).\
select_from(UserGroup).join(User).join(Group). \
filter(UserGroup.user_id == User.id). \
filter(User.id == Vocation.user_id). \
filter(UserGroup.group_id == Group.id). \
filter(Vocation.status == 1). \
filter(Group.group_id == self.group_id). \
all():
res.append([nickname, stop_at])
result = DBResult(error=False, info='Success', result=res)
except Exception as e:
result = DBResult(error=True, info=repr(e), result=res)
finally:
session.close()
return result
def check_global_cool_down_event(cls) -> DBResult:
session = NBdb().get_session()
try:
event = session.query(CoolDownEvent).filter(
CoolDownEvent.event_type == 'global').one()
stop_at = event.stop_at
result = DBResult(error=False,
info=f'CoolDown until: {stop_at}',
result=1)
except NoResultFound:
result = DBResult(error=False, info='NoResultFound', result=0)
except MultipleResultsFound:
result = DBResult(error=True,
info='MultipleResultsFound',
result=-1)
except Exception as e:
result = DBResult(error=True, info=repr(e), result=-1)
finally:
session.close()
return result
def id(self) -> DBResult:
session = NBdb().get_session()
try:
if self.auth_type == 'user':
auth_table_id = session.query(AuthUser.id).join(User). \
filter(AuthUser.user_id == User.id). \
filter(User.qq == self.auth_id). \
filter(AuthUser.auth_node == self.auth_node).one()[0]
result = DBResult(error=False,
info='Success',
result=auth_table_id)
elif self.auth_type == 'group':
auth_table_id = session.query(AuthGroup.id).join(Group). \
filter(AuthGroup.group_id == Group.id). \
filter(Group.group_id == self.auth_id). \
filter(AuthGroup.auth_node == self.auth_node).one()[0]
result = DBResult(error=False,
info='Success',
result=auth_table_id)
else:
result = DBResult(error=True,
info='Auth type error',
result=-1)
except NoResultFound:
result = DBResult(error=True, info='NoResultFound', result=-1)
except MultipleResultsFound:
result = DBResult(error=True,
info='MultipleResultsFound',
result=-1)
except Exception as e:
result = DBResult(error=True, info=repr(e), result=-1)
finally:
session.close()
return result
def idle_member_list(self) -> DBResult:
session = NBdb().get_session()
res = []
# 查询该群组中所有没有假的人
try:
for user_id, nickname in session.query(User.id, UserGroup.user_group_nickname). \
join(Vocation).join(UserGroup).join(Group). \
filter(User.id == Vocation.user_id). \
filter(User.id == UserGroup.user_id). \
filter(UserGroup.group_id == Group.id). \
filter(Vocation.status == 0). \
filter(Group.group_id == self.group_id). \
all():
res_skills = session.query(Skill.name). \
join(UserSkill).join(User). \
filter(Skill.id == UserSkill.skill_id). \
filter(UserSkill.user_id == User.id). \
filter(User.id == user_id). \
all()
user_skill = ''
if res_skills:
for skill in res_skills:
user_skill += f'/{skill[0]}'
else:
user_skill = '/暂无技能'
res.append([nickname, user_skill])
result = DBResult(error=False, info='Success', result=res)
except Exception as e:
result = DBResult(error=True, info=repr(e), result=res)
finally:
session.close()
return result
def idle_skill_list(self, skill: DBSkill) -> DBResult:
session = NBdb().get_session()
res = []
# 查询这这个技能有那些人会
try:
for user_id, nickname in session.query(User.id, UserGroup.user_group_nickname). \
join(UserSkill).join(Skill).join(UserGroup).join(Group). \
filter(User.id == UserSkill.user_id). \
filter(UserSkill.skill_id == Skill.id). \
filter(User.id == UserGroup.user_id). \
filter(UserGroup.group_id == Group.id). \
filter(Skill.name == skill.name). \
filter(Group.group_id == self.group_id). \
all():
# 查这个人是不是空闲
user_status = session.query(Vocation.status).filter(
Vocation.user_id == user_id).one()[0]
# 如果空闲则把这个人昵称放进结果列表里面
if user_status == 0:
res.append(nickname)
result = DBResult(error=False, info='Success', result=res)
except Exception as e:
result = DBResult(error=True, info=repr(e), result=res)
finally:
session.close()
return result
def list(cls, auth_type: str, auth_id: int) -> DBResult:
session = NBdb().get_session()
try:
if auth_type == 'user':
auth_node_list = session.query(AuthUser.auth_node, AuthUser.allow_tag, AuthUser.deny_tag). \
join(User). \
filter(AuthUser.user_id == User.id). \
filter(User.qq == auth_id).all()
result = DBResult(error=False,
info='Success',
result=auth_node_list)
elif auth_type == 'group':
auth_node_list = session.query(AuthGroup.auth_node, AuthGroup.allow_tag, AuthGroup.deny_tag). \
join(Group). \
filter(AuthGroup.group_id == Group.id). \
filter(Group.group_id == auth_id).all()
result = DBResult(error=False,
info='Success',
result=auth_node_list)
else:
result = DBResult(error=True,
info='Auth type error',
result=-1)
except NoResultFound:
result = DBResult(error=True, info='NoResultFound', result=-1)
except MultipleResultsFound:
result = DBResult(error=True,
info='MultipleResultsFound',
result=-1)
except Exception as e:
result = DBResult(error=True, info=repr(e), result=-1)
finally:
session.close()
return result
def check_user_cool_down_event(cls, plugin: str, user_id: int) -> DBResult:
session = NBdb().get_session()
try:
event = session.query(CoolDownEvent). \
filter(CoolDownEvent.plugin == plugin). \
filter(CoolDownEvent.event_type == 'user').\
filter(CoolDownEvent.user_id == user_id).one()
stop_at = event.stop_at
result = DBResult(error=False,
info=f'CoolDown until: {stop_at}',
result=1)
except NoResultFound:
result = DBResult(error=False, info='NoResultFound', result=0)
except MultipleResultsFound:
result = DBResult(error=True,
info='MultipleResultsFound',
result=-1)
except Exception as e:
result = DBResult(error=True, info=repr(e), result=-1)
finally:
session.close()
return result
