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
