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_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_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_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(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 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_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 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_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 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 delete(self):
try:
Database.get_session().delete(self)
Database.get_session().commit()
except Exception as e:
Database.get_session().rollback()
logger.error(e)
def add(self):
try:
Database.get_session().add(self)
Database.get_session().commit()
except:
Database.get_session().rollback()
raise
def update_bulk_client_key(self, client_id, list_group_client_key):
try:
for group_client_key in list_group_client_key:
sql_update = 'UPDATE group_client_key SET ' \
'device_id=:device_id, ' \
'client_key=:client_key, ' \
'client_sender_key_id=:client_sender_key_id, ' \
'client_sender_key=:client_sender_key, ' \
'client_public_key=:client_public_key, ' \
'client_private_key=:client_private_key, ' \
'updated_at=NOW() ' \
'WHERE group_id=:group_id ' \
'AND client_id=:client_id'
Database.get_session().execute(
sql_update, {
'device_id': group_client_key.deviceId,
'client_key': group_client_key.clientKeyDistribution,
'client_sender_key_id': group_client_key.senderKeyId,
'client_sender_key': group_client_key.senderKey,
'client_public_key': group_client_key.publicKey,
'client_private_key': group_client_key.privateKey,
'group_id': group_client_key.groupId,
'client_id': client_id
})
Database.get_session().commit()
return True
except Exception as e:
logger.error(e)
Database.get_session().rollback()
return False
def update(self):
try:
Database.get_session().merge(self)
Database.get_session().commit()
# Database.get().session.remove()
except Exception as e:
Database.get_session().rollback()
logger.error(e)
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 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)
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 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 add(self):
client = self.get(self.group_id, self.client_id)
if client is not None:
self.id = client.id
self.update()
return self
else:
try:
Database.get_session().add(self)
Database.get_session().commit()
return self
except Exception as e:
Database.get_session().rollback()
logger.error(e)
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_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 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 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)
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
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)
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