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