def get_all_in_group(self, group_id): client = Database.get_session().query(GroupClientKey) \ .filter(GroupClientKey.group_id == group_id) \ .order_by(GroupClientKey.client_id.asc()) \ .all() Database.get().session.remove() return client
def get_client_key_by_owner_group(self, group_id, client_id): client = Database.get_session().query(GroupClientKey) \ .join(GroupChat, GroupClientKey.group_id == GroupChat.id, isouter=True) \ .filter(GroupChat.owner_group_id == group_id, GroupClientKey.client_id == client_id) \ .one_or_none() Database.get().session.remove() return client
def get_users(self, client_id): user = Database.get_session().query(User) \ .filter(User.id != client_id) \ .filter(User.last_login_at != None) \ .all() Database.get().session.remove() return user
def get_google_user(self, email, auth_source): user = Database.get_session().query(User) \ .filter(User.email == email) \ .filter(User.auth_source == auth_source) \ .one_or_none() Database.get().session.remove() return user
def get_client_device_ids(self, client_id): client_tokens = Database.get_session().query(NotifyToken) \ .filter(NotifyToken.client_id == client_id) \ .order_by(desc(NotifyToken.created_at)) \ .all() Database.get().session.remove() return client_tokens
def get_joined_group_type(self, client_id, group_type): result = Database.get_session().query(GroupChat, GroupClientKey.id, GroupChat.group_clients) \ .join(GroupClientKey, GroupChat.id == GroupClientKey.group_id) \ .filter(GroupClientKey.client_id == client_id) \ .filter(GroupChat.group_type == group_type) \ .all() Database.get().session.remove() return result
def get(self, group_id): group = Database.get_session().query(GroupChat, Message) \ .join(Message, GroupChat.last_message_id == Message.id, isouter=True) \ .filter(GroupChat.id == group_id) \ .one_or_none() Database.get().session.remove() return group
def search(self, keyword): search = "%{}%".format(keyword) group = Database.get_session().query(GroupChat, Message) \ .join(Message, GroupChat.last_message_id == Message.id, isouter=True) \ .filter(GroupChat.group_name.like(search)) \ .all() Database.get().session.remove() return group
def search(self, keyword, client_id): search = "%{}%".format(keyword) user = Database.get_session().query(User) \ .filter(User.id != client_id) \ .filter(User.display_name.ilike(search)) \ .filter(User.last_login_at != None) \ .all() Database.get().session.remove() return user
def get_joined(self, client_id): result = Database.get_session().query(GroupChat, Message, GroupClientKey) \ .join(GroupClientKey, GroupChat.id == GroupClientKey.group_id) \ .join(Message, GroupChat.last_message_id == Message.id, isouter=True) \ .options(joinedload(Message.users_read).joinedload(MessageUserRead.user)) \ .filter(GroupClientKey.client_id == client_id) \ .all() Database.get().session.remove() return result
def get_clients_in_group(self, group_id): result = Database.get_session().query(GroupClientKey, User) \ .options(joinedload(User.tokens)) \ .join(User, GroupClientKey.client_id == User.id, isouter=True) \ .filter(GroupClientKey.group_id == group_id) \ .order_by(GroupClientKey.client_id.asc()) \ .all() Database.get().session.remove() return result
def get_message_in_group(self, group_id, offset, from_time): message = Database.get_session().query(Message) \ .options(joinedload(Message.users_read).joinedload(MessageUserRead.user)) \ .filter(Message.group_id == group_id) message = message.order_by(Message.created_at.desc()) if from_time != 0: dt = datetime.fromtimestamp(from_time / 1000) # from time in milisecond => second message = message.filter(Message.created_at < dt) if offset != 0: message = message.limit(offset) result = message.all() Database.get().session.remove() return result
class MessageUserRead(Database.get().Model): __tablename__ = 'message_user_read' id = Database.get().Column(Database.get().Integer, primary_key=True) message_id = Database.get().Column(Database.get().String(36), ForeignKey('message.id')) client_id = Database.get().Column(Database.get().String(36), ForeignKey('user.id')) created_at = Database.get().Column(Database.get().DateTime, default=datetime.now) user = relationship('User', back_populates='messages_read') message = relationship('Message', back_populates='users_read') def add(self): Database.get_session().add(self) Database.get_session().commit() return self def get_by_message_id(self, message_id): message_user_read = Database.get_session().query(MessageUserRead) \ .filter(MessageUserRead.message_id == message_id) \ .one_or_none() if message_user_read: message_user_read.get().session.remove() return message_user_read
def get_group_rtc_token(self, group_id): result = Database.get_session().query(GroupChat.group_rtc_token) \ .filter(GroupChat.id == group_id) \ .first() Database.get().session.remove() return result
class PeerClientKey(Database.get().Model): __tablename__ = 'peer_client_key' id = Database.get().Column(Database.get().Integer, primary_key=True) client_id = Database.get().Column(Database.get().String(36), nullable=True) device_id = Database.get().Column(Database.get().Integer, unique=False, nullable=False) registration_id = Database.get().Column(Database.get().Integer, unique=False, nullable=False) identity_key_public = Database.get().Column(Database.get().Binary) prekey_id = Database.get().Column(Database.get().Integer, unique=False, nullable=False) prekey = Database.get().Column(Database.get().Binary) signed_prekey_id = Database.get().Column(Database.get().Integer, unique=False, nullable=False) signed_prekey = Database.get().Column(Database.get().Binary) signed_prekey_signature = Database.get().Column(Database.get().Binary) identity_key_encrypted = Database.get().Column(Database.get().String(255), unique=False) created_at = Database.get().Column(Database.get().DateTime, default=datetime.now) updated_at = Database.get().Column(Database.get().DateTime, default=datetime.now, onupdate=datetime.now) def set_key(self, client_id, registration_id, device_id, identity_key_public, prekey_id, prekey, signed_prekey_id, signed_prekey, signed_prekey_signature, identity_key_encrypted): self.client_id = client_id self.registration_id = registration_id self.device_id = device_id self.identity_key_public = identity_key_public self.prekey_id = prekey_id self.prekey = prekey self.signed_prekey_id = signed_prekey_id self.signed_prekey = signed_prekey self.signed_prekey_signature = signed_prekey_signature self.identity_key_encrypted = identity_key_encrypted return self def get(self, id): client = Database.get_session().query(PeerClientKey) \ .filter(PeerClientKey.id == id) \ .one_or_none() Database.get().session.remove() return client def add(self): client = self.get_by_client_id(client_id=self.client_id) if client is not None: return False else: try: Database.get_session().add(self) Database.get_session().commit() return True except Exception as e: Database.get_session().rollback() logger.error(e) def get_by_client_id(self, client_id): client = Database.get_session().query(PeerClientKey) \ .filter(PeerClientKey.client_id == client_id) \ .one_or_none() Database.get().session.remove() return client def update(self): try: Database.get_session().merge(self) Database.get_session().commit() except Exception as e: Database.get_session().rollback() logger.error(e) def delete(self): try: Database.get_session().delete(self) Database.get_session().commit() except Exception as e: Database.get_session().rollback() logger.error(e)
def get_by_group_owner(self, owner_group_id): result = Database.get_session().query(GroupChat) \ .filter(GroupChat.owner_group_id == owner_group_id) \ .all() Database.get().session.remove() return result
class GroupChat(Database.get().Model): __tablename__ = 'group_chat' id = Database.get().Column(Database.get().Integer, primary_key=True) owner_group_id = Database.get().Column(Database.get().Integer, nullable=True) owner_workspace_domain = Database.get().Column(Database.get().String(255), nullable=True) group_name = Database.get().Column(Database.get().String(255), unique=False, nullable=True) group_avatar = Database.get().Column(Database.get().String(255), unique=False, nullable=True) group_type = Database.get().Column(Database.get().String(36), unique=False, nullable=True) group_clients = Database.get().Column(Database.get().Text, unique=False, nullable=True) group_rtc_token = Database.get().Column(Database.get().Text, unique=False, nullable=True) total_member = Database.get().Column(Database.get().Integer, nullable=True) created_by = Database.get().Column(Database.get().String(36), unique=False, nullable=True) created_at = Database.get().Column(Database.get().DateTime, default=datetime.now) updated_by = Database.get().Column(Database.get().String(36), unique=False, nullable=True) updated_at = Database.get().Column(Database.get().DateTime, onupdate=datetime.now, nullable=True) last_message_at = Database.get().Column(Database.get().DateTime, nullable=True) last_message_id = Database.get().Column(Database.get().String(36), unique=False, nullable=True) deleted_at = Database.get().Column(Database.get().DateTime, nullable=True) def add(self): try: Database.get_session().add(self) Database.get_session().commit() return self except Exception as e: Database.get_session().rollback() logger.error(e) def get(self, group_id): group = Database.get_session().query(GroupChat, Message) \ .join(Message, GroupChat.last_message_id == Message.id, isouter=True) \ .filter(GroupChat.id == group_id) \ .one_or_none() Database.get().session.remove() return group def get_group(self, group_id): group = Database.get_session().query(GroupChat) \ .filter(GroupChat.id == group_id) \ .one_or_none() Database.get().session.remove() return group def get_group_type(self, group_id): group = Database.get_session().query(GroupChat.group_type) \ .filter(GroupChat.id == group_id) \ .one_or_none() Database.get().session.remove() return group def search(self, keyword): search = "%{}%".format(keyword) group = Database.get_session().query(GroupChat, Message) \ .join(Message, GroupChat.last_message_id == Message.id, isouter=True) \ .filter(GroupChat.group_name.like(search)) \ .all() Database.get().session.remove() return group def get_joined(self, client_id): result = Database.get_session().query(GroupChat, Message, GroupClientKey) \ .join(GroupClientKey, GroupChat.id == GroupClientKey.group_id) \ .join(Message, GroupChat.last_message_id == Message.id, isouter=True) \ .options(joinedload(Message.users_read).joinedload(MessageUserRead.user)) \ .filter(GroupClientKey.client_id == client_id) \ .all() Database.get().session.remove() return result def get_joined_group_type(self, client_id, group_type): result = Database.get_session().query(GroupChat, GroupClientKey.id, GroupChat.group_clients) \ .join(GroupClientKey, GroupChat.id == GroupClientKey.group_id) \ .filter(GroupClientKey.client_id == client_id) \ .filter(GroupChat.group_type == group_type) \ .all() Database.get().session.remove() return result def get_by_group_owner(self, owner_group_id): result = Database.get_session().query(GroupChat) \ .filter(GroupChat.owner_group_id == owner_group_id) \ .all() Database.get().session.remove() return result def get_client_key_by_owner_group(self, group_id, client_id): client = Database.get_session().query(GroupClientKey) \ .join(GroupChat, GroupClientKey.group_id == GroupChat.id, isouter=True) \ .filter(GroupChat.owner_group_id == group_id, GroupClientKey.client_id == client_id) \ .one_or_none() Database.get().session.remove() return client def update(self): try: Database.get_session().merge(self) Database.get_session().commit() except Exception as e: Database.get_session().rollback() logger.error(e) def delete(self): try: Database.get_session().delete(self) Database.get_session().commit() except Exception as e: Database.get_session().rollback() logger.error(e) def get_group_rtc_token(self, group_id): result = Database.get_session().query(GroupChat.group_rtc_token) \ .filter(GroupChat.id == group_id) \ .first() Database.get().session.remove() return result def __repr__(self): return '<Item(id=%s, group_name=%s, owner_group_id=%s)>' % ( self.id, self.group_name, self.owner_group_id)
def get_by_client_id(self, client_id): client = Database.get_session().query(PeerClientKey) \ .filter(PeerClientKey.client_id == client_id) \ .one_or_none() Database.get().session.remove() return client
def get_clients(self, client_ids): client_tokens = Database.get_session().query(NotifyToken) \ .filter(NotifyToken.client_id.in_(client_ids)) \ .all() Database.get().session.remove() return client_tokens
def get_user_notes(self, user_id): user_notes = Database.get_session().query(Note) \ .filter(Note.user_id == user_id) \ .all() Database.get().session.remove() return user_notes
class ServerInfo(Database.get().Model): __tablename__ = 'server_info' id = Database.get().Column(Database.get().Integer, primary_key=True) stun_server = Database.get().Column(Database.get().String(500), nullable=True) turn_server = Database.get().Column(Database.get().String(500), nullable=True) created_at = Database.get().Column(Database.get().DateTime, default=datetime.now) updated_at = Database.get().Column(Database.get().DateTime, onupdate=datetime.now) def add(self): try: Database.get_session().add(self) Database.get_session().commit() return self except Exception as e: Database.get_session().rollback() logger.error(e) def get(self): server_info = Database.get_session().query(ServerInfo) \ .filter(ServerInfo.id == 1) \ .one_or_none() Database.get().session.remove() return server_info def update(self): server_info = self.get() if server_info is not None: self.id = server_info.id try: Database.get_session().merge(self) Database.get_session().commit() except Exception as e: Database.get_session().rollback() logger.error(e) else: self.add() return True
def get(self): server_info = Database.get_session().query(ServerInfo) \ .filter(ServerInfo.id == 1) \ .one_or_none() Database.get().session.remove() return server_info
class NotifyToken(Database.get().Model): __tablename__ = 'notify_token' id = Database.get().Column(Database.get().String(36), primary_key=True) client_id = Database.get().Column(Database.get().String(36), ForeignKey('user.id')) device_id = Database.get().Column(Database.get().String(255), nullable=False) device_type = Database.get().Column(Database.get().String(16), nullable=False) push_token = Database.get().Column(Database.get().String(255), nullable=True) created_at = Database.get().Column(Database.get().DateTime, default=datetime.now) updated_at = Database.get().Column(Database.get().DateTime, onupdate=datetime.now) user = relationship('User', back_populates='tokens') def add(self): client_device = self.get(self.client_id, self.device_id) if client_device is not None: self.id = client_device.id self.update() else: self.id = str(uuid.uuid4()) try: Database.get_session().add(self) Database.get_session().commit() except Exception as e: Database.get_session().rollback() logger.error(e) return self def get(self, client_id, device_id): client_device = Database.get_session().query(NotifyToken) \ .filter(NotifyToken.client_id == client_id, NotifyToken.device_id == device_id) \ .one_or_none() Database.get().session.remove() return client_device def get_client(self, client_id): client_tokens = Database.get_session().query(NotifyToken) \ .filter(NotifyToken.client_id == client_id) \ .order_by(desc(NotifyToken.created_at)) \ .limit(1) \ .all() Database.get().session.remove() return client_tokens def get_client_device_ids(self, client_id): client_tokens = Database.get_session().query(NotifyToken) \ .filter(NotifyToken.client_id == client_id) \ .order_by(desc(NotifyToken.created_at)) \ .all() Database.get().session.remove() return client_tokens def get_clients(self, client_ids): client_tokens = Database.get_session().query(NotifyToken) \ .filter(NotifyToken.client_id.in_(client_ids)) \ .all() Database.get().session.remove() return client_tokens def update(self): try: Database.get_session().merge(self) Database.get_session().commit() except Exception as e: Database.get_session().rollback() logger.error(e) def delete(self): try: Database.get_session().delete(self) Database.get_session().commit() except Exception as e: Database.get_session().rollback() logger.error(e)
def get(self, message_id): message = Database.get_session().query(Message) \ .filter(Message.id == message_id) \ .one_or_none() Database.get().session.remove() return message
class Note(Database.get().Model): __tablename__ = 'note' id = Database.get().Column(Database.get().String(36), primary_key=True) user_id = Database.get().Column(Database.get().String(36), unique=False, nullable=True) title = Database.get().Column(Database.get().String(36), unique=False, nullable=True) content = Database.get().Column(Database.get().Binary) note_type = Database.get().Column(Database.get().String(128), default='text', nullable=False) created_by = Database.get().Column(Database.get().String(36), unique=False, nullable=True) created_at = Database.get().Column(Database.get().DateTime, default=datetime.now) updated_by = Database.get().Column(Database.get().String(36), unique=False, nullable=True) updated_at = Database.get().Column(Database.get().DateTime, onupdate=datetime.now, nullable=True) deleted_at = Database.get().Column(Database.get().DateTime, nullable=True) def add(self): Database.get_session().add(self) Database.get_session().commit() return self def get(self, note_id): note = Database.get_session().query(Note) \ .filter(Note.id == note_id) \ .one_or_none() Database.get().session.remove() return note def get_user_notes(self, user_id): user_notes = Database.get_session().query(Note) \ .filter(Note.user_id == user_id) \ .all() Database.get().session.remove() return user_notes def update(self): try: Database.get_session().merge(self) Database.get_session().commit() except Exception as e: Database.get_session().rollback() logger.error(e) def delete(self): try: Database.get_session().delete(self) Database.get_session().commit() except Exception as e: Database.get_session().rollback() logger.error(e)
def get(self, notify_id): notify = Database.get_session().query(Notify) \ .filter(Notify.id == notify_id) \ .one_or_none() Database.get().session.remove() return notify
def get_group_type(self, group_id): group = Database.get_session().query(GroupChat.group_type) \ .filter(GroupChat.id == group_id) \ .one_or_none() Database.get().session.remove() return group
class Notify(Database.get().Model): __tablename__ = 'notify' id = Database.get().Column(Database.get().Integer, primary_key=True) client_id = Database.get().Column(Database.get().String(36), nullable=True) client_workspace_domain = Database.get().Column(Database.get().String(255), unique=False, nullable=True) ref_client_id = Database.get().Column(Database.get().String(36), unique=False, nullable=True) ref_group_id = Database.get().Column(Database.get().Integer, unique=False, nullable=True) ref_subject_name = Database.get().Column(Database.get().String(255), unique=False, nullable=True) ref_workspace_domain = Database.get().Column(Database.get().String(255), unique=False, nullable=True) notify_type = Database.get().Column(Database.get().String(36), unique=False, nullable=True) # new-peer, in-peer, new-group, in-group notify_image = Database.get().Column(Database.get().String(255), unique=False, nullable=True) notify_title = Database.get().Column(Database.get().String(255), unique=False, nullable=True) notify_content = Database.get().Column(Database.get().String(255), unique=False, nullable=True) notify_platform = Database.get().Column(Database.get().String(36), unique=False, nullable=True) read_flg = Database.get().Column(Database.get().Boolean, nullable=True, default=False) created_at = Database.get().Column(Database.get().DateTime, default=datetime.now) def add(self): try: Database.get_session().add(self) Database.get_session().commit() return self.get(self.id) except Exception as e: Database.get_session().rollback() logger.error(e) def get(self, notify_id): notify = Database.get_session().query(Notify) \ .filter(Notify.id == notify_id) \ .one_or_none() Database.get().session.remove() return notify def get_unread_notifies(self, client_id): notifies = self.query.filter_by(client_id=client_id, read_flg=False) return notifies def update(self): try: Database.get_session().merge(self) Database.get_session().commit() except Exception as e: Database.get_session().rollback() logger.error(e)
class Message(Database.get().Model): __tablename__ = 'message' id = Database.get().Column(Database.get().String(36), primary_key=True) group_id = Database.get().Column(Database.get().Integer, nullable=True) from_client_id = Database.get().Column(Database.get().String(36), unique=False, nullable=True) from_client_workspace_domain = Database.get().Column(Database.get().String(100), unique=False, nullable=True) client_id = Database.get().Column(Database.get().String(36), nullable=True) client_workspace_domain = Database.get().Column(Database.get().String(100), unique=False, nullable=True) message = Database.get().Column(Database.get().Binary) sender_message = Database.get().Column(Database.get().Binary) message_type = Database.get().Column( Database.get().String(128), default='text', nullable=False ) created_at = Database.get().Column(Database.get().DateTime, default=datetime.now) updated_at = Database.get().Column(Database.get().DateTime, onupdate=datetime.now) deleted_at = Database.get().Column(Database.get().DateTime, nullable=True) users_read = relationship('MessageUserRead', back_populates='message') def add(self): Database.get_session().add(self) Database.get_session().commit() return self def get(self, message_id): message = Database.get_session().query(Message) \ .filter(Message.id == message_id) \ .one_or_none() Database.get().session.remove() return message def get_message_in_group(self, group_id, offset, from_time): message = Database.get_session().query(Message) \ .options(joinedload(Message.users_read).joinedload(MessageUserRead.user)) \ .filter(Message.group_id == group_id) message = message.order_by(Message.created_at.desc()) if from_time != 0: dt = datetime.fromtimestamp(from_time / 1000) # from time in milisecond => second message = message.filter(Message.created_at < dt) if offset != 0: message = message.limit(offset) result = message.all() Database.get().session.remove() return result def update(self): try: Database.get_session().merge(self) Database.get_session().commit() except Exception as e: Database.get_session().rollback() logger.error(e)
def get(self, client_id, device_id): client_device = Database.get_session().query(NotifyToken) \ .filter(NotifyToken.client_id == client_id, NotifyToken.device_id == device_id) \ .one_or_none() Database.get().session.remove() return client_device