def db_profile_available(profile_id: str) -> bool:
"""Update profile availability info in DB"""
from main import logger
if not db_duplicate_check([Profiles], Profiles.profile_id == profile_id):
# profile not exist in DB
return False
db_session = Session()
if profile_deleted(profile_id=profile_id):
# profile deleted on site
update_q = update(Profiles).where(
Profiles.profile_id == profile_id). \
values(available=False)
db_session.execute(update_q)
db_session.commit()
db_session.close()
logger.info(f'User {current_user} updated profile_id: {profile_id} '
f'and changed availability to 0,'
f'because profile deleted on site')
return False
else:
# profile not deleted on site
update_q = update(Profiles).where(
Profiles.profile_id == profile_id). \
values(available=True)
db_session.execute(update_q)
db_session.commit()
db_session.close()
logger.info(f'User {current_user} updated profile_id: {profile_id} '
f'and changed availability to 1,'
f'because profile not deleted on site')
return True
def create_user(login: str, user_password: str, role: str = 'default'):
from main import logger
session = Session()
new_user = Users(login=login,
user_password=generate_password_hash(user_password,
"sha256",
salt_length=8))
try:
session.add(new_user)
session.commit()
except exc.IntegrityError:
session.rollback()
logger.error(f"{current_user.login} get IntegrityError because "
f"user with such parameters already exists:\n"
f"login = {login}"
f"password = {user_password}"
f"role = {role}")
return False
new_user_role = RolesOfUsers(login=new_user.login, user_role=role)
try:
session.add(new_user_role)
session.commit()
except exc.IntegrityError:
session.rollback()
logger.error(f"{current_user.login} get IntegrityError because "
f"user {login} already has {role} role")
return False
session.close()
logger.info(f'User {login} successfully created')
return True
def register_user(login: str, user_password: str):
from main import logger
db_session = Session()
users = db_session.query(Users).filter(Users.login == login).all()
if len(users) == 0:
logger.info(f'User signup with wrong e-mail: {login}')
return None
db_invites = db_session.query(Invites).all()
for db_invite in db_invites:
if check_password_hash(users[0].user_password, db_invite.invite_id):
invite_id = db_invite.invite_id
update_q = update(Users).where(
Users.login == login). \
values(user_password=generate_password_hash(
user_password,
"sha256",
salt_length=8))
db_session.execute(update_q)
db_session.commit()
db_session.close()
logger.info(f'User {login} successful registered '
f'with invite_id: {invite_id}')
return None
role = db_session.query(
RolesOfUsers.user_role).filter(RolesOfUsers.login == login).all()
if role[0][0] == 'deleted':
logger.info(f'User signup with e-mail: {login}, '
f'but this account deleted')
return None
db_session.close()
logger.info(f'User signup with e-mail: {login}, '
f'but this account already exists')
return 'Such account already exists'
def db_add_profile(profile_id: str, profile_password: str):
"""Add profile to DB"""
from main import logger
if profile_deleted(profile_id=profile_id):
logger.info(f'User {current_user} tried to add profile info, '
f'for profile_id: {profile_id}, '
f'but this profile deleted on site')
return False
db_session = Session()
if db_duplicate_check([Profiles], Profiles.profile_id == profile_id):
# profile already in DB, update password
update_q = update(Profiles). \
where(Profiles.profile_id == profile_id). \
values(profile_password=profile_password)
db_session.execute(update_q)
logger.info(f'User {current_user} updated profile info, '
f'for profile_id: {profile_id}')
else:
# create new profile
new_profile = Profiles(profile_id=profile_id,
profile_password=profile_password,
available=True,
can_receive=True)
db_session.add(new_profile)
logger.info(f'User {current_user} added profile, '
f'with profile_id: {profile_id}')
db_session.commit()
db_session.close()
db_load_profile_description(profile_id=profile_id)
return True
def db_chat_length_check(chat_id: bytes, total_msg: int,
sender_id: str) -> int:
# return count of new messages, which not presented in database
db_session = Session()
messages = db_session.query(Messages).filter(
Messages.chat_id == chat_id).filter(Messages.profile_id == sender_id)
db_session.close()
return total_msg - len(messages.all())
def db_get_rows(tables: list, *statements) -> list:
"""Select all rows from tables list,
which have been filtered with 'statements'"""
db_session = Session()
query = db_session.query(*tables)
for statement in statements:
query = query.filter(statement != '')
result = query.all()
db_session.close()
return result
def db_change_user_role(user_login: str, role: str):
db_session = Session()
update_q = update(RolesOfUsers).where(
RolesOfUsers.login == user_login). \
values(user_role=role)
db_session.execute(update_q)
db_session.commit()
db_session.close()
return None
def db_template_update(text_id: bytes, text: str) -> bool:
if not db_duplicate_check([Texts.text_id], Texts.text_id == text_id):
return False
db_session = Session()
update_q = update(Texts).where(
Texts.text_id == text_id). \
values(text=text)
db_session.execute(update_q)
db_session.commit()
db_session.close()
return True
def db_delete_rows(tables: list, *statements) -> int:
"""Delete all rows from tables list,
which have been filtered with 'statements'.
Returns number of deleted rows"""
db_session = Session()
query = db_session.query(*tables)
for statement in statements:
query = query.filter(statement != '')
rows = query.delete() # return number of deleted msg
db_session.commit() # return number of deleted msg
db_session.close()
return rows
def db_show_receivers(sender: str) -> list:
# you can swap sender and receiver
"""Returns list of dicts, with messages in dialogue for admin panel"""
db_session = Session()
# subquery for chats with sender
sub_query_1 = db_session.query(ChatSessions.chat_id). \
filter(ChatSessions.profile_id == sender).subquery()
# subquery for chats with this users
query = db_session.query(ChatSessions.profile_id). \
filter(ChatSessions.chat_id.in_(sub_query_1)). \
filter(ChatSessions.profile_id != sender)
# query to show messages and texts
receivers = query.all()
db_session.close()
return [{"profile_id": row[0]} for row in receivers]
def send_email_instruction(email_to):
"""Функция выполняет отправку сообщения на указанный email адресс с
инструкцией для регистрации в системе NatalyBot
Keyword arguments:
email_to -- адрессат, которому будет доставлено сообщение с инструкцией
"""
# Настройки по умолчанию
db_session = Session()
query = db_session.query(
EmailInfo.email_host, EmailInfo.email_port, EmailInfo.email_address,
EmailInfo.email_password, EmailInfo.email_subject,
EmailInfo.email_text).filter(
EmailInfo.email_description == 'default_register')
default_email_info = query.all()
if len(default_email_info) > 0:
HOST, PORT, EMAIL, PASSWORD, SUBJECT, TEXT = default_email_info[0]
db_session.close()
# Соединяемся с почтовым сервисом
smtpObj = smtplib.SMTP(HOST, PORT)
# Шифруем сообщение
smtpObj.starttls()
# Логинимся на почтовый ящик
smtpObj.login(EMAIL, PASSWORD)
# Экзепляр Email сообщения
message = EmailMessage()
# Текст сообщения
message.set_content(TEXT)
# Хедер сообщения. Нужен в соотвествии со стандартами Google
# Тема сообщения
message['Subject'] = SUBJECT
# Почта с которой идет отправка
message['From'] = EMAIL
# Кому отправляем
message['To'] = email_to
# Отправляем сообщение через наш почтовый сервер
smtpObj.send_message(message)
# Закрываем соединение с почтовым сервером
smtpObj.quit()
def db_fill_visibility(login: str) -> bool:
"""Adds visibility status of all profiles for user by login value,
in database"""
db_session = Session()
query = db_session.query(Profiles.profile_id)
for profile in query.all():
query_check = db_session.query(Visibility.profile_id, Visibility.login)
query_check = query_check.filter(Visibility.login == login)
query_check = query_check.filter(Visibility.profile_id == profile[0])
check_result = query_check.all()
if len(check_result) > 0:
continue
access = Visibility(login=login, profile_id=profile[0])
db_session.add(access)
db_session.commit()
db_session.close()
return True
def db_message_create(chat_id: bytes, send_time, viewed: bool, sender: str,
text: str) -> bool:
db_session = Session()
text_id = uuid4().bytes
message_id = uuid4().bytes
msg_text = Texts(text_id=text_id, text=text)
db_session.add(msg_text)
db_session.commit()
new_message = Messages(message_token=message_id,
chat_id=chat_id,
text_id=text_id,
send_time=send_time,
viewed=viewed,
profile_id=sender)
db_session.add(new_message)
db_session.commit()
db_session.close()
return True
def db_get_profiles(*args) -> list:
db_session = Session()
query = db_session.query(Profiles.profile_id, Profiles.profile_password,
Profiles.available, Profiles.can_receive,
Profiles.msg_limit, Profiles.profile_type)
for statement in args:
query = query.filter(statement != '')
profiles = query.all()
profiles = [{
"profile_id": profile[0],
"profile_password": profile[1],
"available": profile[2],
"can_receive": profile[3],
"msg_limit": profile[4],
"profile_type": profile[5]
} for profile in profiles]
db_session.close()
return profiles
def db_add_visibility(login: str, profile_id: str) -> str:
"""Adds visibility status of profiles for user by login value,
in database"""
db_session = Session()
from main import logger
# check user in database
user_in_db = db_duplicate_check([Users.login], Users.login == login,
Users.login != 'server',
Users.login != 'anonymous')
if not user_in_db:
logger.info(f'User {current_user.login} tried to add profiles for '
f'{login} but such user not exists')
# profile not in database
return 'UserNotFound'
# check profile in database
profile_in_db = db_duplicate_check([Profiles.profile_id],
Profiles.profile_id == profile_id)
if not profile_in_db:
logger.info(f'User {current_user.login} tried to add profile '
f'{profile_id} for '
f'{login} but such profile not exists')
# profile not in database
return 'ProfileNotFound'
# check if user already has access to profile
access_in_db = db_duplicate_check(
[Visibility.profile_id, Visibility.login], Visibility.login == login,
Visibility.profile_id == profile_id)
if access_in_db:
logger.info(f'User {current_user.login} tried to add '
f'profile {profile_id} for '
f'{login} but this account already has this profile')
# user already has access to profile
return 'ProfileAlreadyAvailable'
# user already hasn't access to profile
access = Visibility(login=login, profile_id=profile_id)
db_session.add(access)
db_session.commit()
db_session.close()
logger.info(f'User {current_user.login} added '
f'profile {profile_id} for user '
f'{login}')
return 'Success'
def db_get_users(*statements) -> list:
db_session = Session()
query = db_session.query(Users.login, Users.user_password,
SentInvites.invite_id, RolesOfUsers.user_role)
query = query.outerjoin(SentInvites, Users.login == SentInvites.login)
query = query.outerjoin(RolesOfUsers, Users.login == RolesOfUsers.login)
query = query.group_by(Users.login)
query = query.filter(Users.login != 'anonymous')
query = query.filter(Users.login != 'server')
query = query.filter(RolesOfUsers.user_role != 'deleted')
for statement in statements:
query = query.filter(statement != '')
users = query.all()
users = [{
"login": user[0],
"register_status": not check_password_hash(user[1], user[2]),
"role": user[3]
} for user in users]
db_session.close()
return users
def db_add_category_level(category_name: str, level_list: str):
from main import logger
db_session = Session()
if not db_duplicate_check([Categories], Categories.category_name
== category_name):
new_category = Categories(category_name=category_name)
db_session.add(new_category)
db_session.commit()
logger.info('Added category: ', category_name)
db_session.close()
logger.info('START ADD LEVELS LEVELS')
for row in level_list:
db_session = Session()
if not db_duplicate_check([Levels], Levels.level_name == row):
new_row = Levels(level_name=row)
db_session.add(new_row)
db_session.commit()
logger.info(f'Added level_name: ', row)
db_session.close()
logger.info('START ADD CATEGORY LEVELS')
for row in level_list:
db_session = Session()
if not db_duplicate_check([CategoryLevel], CategoryLevel.category_name
== category_name, CategoryLevel.level_name
== row):
new_row = CategoryLevel(category_name=category_name,
level_name=row)
db_session.add(new_row)
db_session.commit()
logger.info(
f'Added CategoryLevel: {category_name} with level: {row}')
db_session.close()
def db_chat_create(observer_login: str, observer_pass: str,
target_profile_id: str) -> bytes:
# chat session require profiles in database
# check profiles in database
sender_profile = Profiles(profile_id=observer_login,
profile_password=observer_pass,
available=True,
can_receive=True)
db_session = Session()
# check if sender profile exists in database
profiles = db_session.query(Profiles).filter(
Profiles.profile_id == sender_profile.profile_id).all()
if len(profiles) == 0:
db_session.add(sender_profile)
db_session.commit()
# check receiver profile in database
profiles = db_session.query(Profiles).filter(
Profiles.profile_id == target_profile_id).all()
if len(profiles) == 0:
target_profile = Profiles(profile_id=target_profile_id,
can_receive=True,
available=True)
db_session.add(target_profile)
db_session.commit()
# finding chat id for this users
# find all chats with sender
sub_query_1 = db_session.query(ChatSessions.chat_id). \
filter(ChatSessions.profile_id == observer_login).subquery()
# find all chats with receiver
sub_query_2 = db_session.query(ChatSessions.chat_id). \
filter(ChatSessions.profile_id == target_profile_id).subquery()
# find chat from sender to receiver
query = db_session.query(ChatSessions). \
filter(ChatSessions.chat_id.in_(sub_query_1)). \
filter(ChatSessions.chat_id.in_(sub_query_2))
chat_sessions = query.all()
# if chat found, we return chat_id
if len(chat_sessions) != 0:
db_session.close()
return chat_sessions[0].chat_id
# if chat not found, we create chat and chat session
chat_id = uuid4().bytes
new_chat = Chats(chat_id=chat_id)
db_session.add(new_chat)
db_session.commit()
chat_session_1 = ChatSessions(chat_id=chat_id, profile_id=observer_login)
db_session.close()
db_session = Session()
db_session.add(chat_session_1)
db_session.commit()
chat_session_2 = ChatSessions(chat_id=chat_id,
profile_id=target_profile_id)
db_session.add(chat_session_2)
db_session.commit()
db_session.close()
return chat_id
def db_delete_user(user_login: str) -> bool:
"""Delete user by login in database,
changes user role to 'deleted',
also delete all invites and visibility statuses for this user"""
db_session = Session()
update_q = update(RolesOfUsers).where(
RolesOfUsers.login == user_login). \
values(user_role='deleted')
db_session.execute(update_q)
db_session.commit()
update_q = update(Users).where(
Users.login == user_login). \
values(user_password='******')
db_session.execute(update_q)
db_session.commit()
db_session.close()
db_delete_rows([Visibility], Visibility.login == user_login)
invite_id = bytes((bytearray(
db_get_rows([SentInvites.invite_id],
SentInvites.login == user_login)[0][0])))
db_delete_rows([SentInvites], SentInvites.invite_id == invite_id)
db_delete_rows([Invites], Invites.invite_id == invite_id)
return True
def db_download_new_msg(observer_login: str, observer_password: str,
sender_id: str, receiver_profile_id: str) -> bool:
# find chat in db to delete new messages
chat_id = db_chat_create(observer_login=observer_login,
observer_pass=observer_password,
target_profile_id=receiver_profile_id)
db_session = Session()
msg_delete = db_session.query(Messages). \
filter(Messages.chat_id == chat_id). \
filter(Messages.viewed == False)
msg_delete.delete() # return number of deleted msg
db_session.commit()
db_session.close()
dialog_download(observer_login=observer_login,
observer_password=observer_password,
sender_id=sender_id,
receiver_profile_id=receiver_profile_id,
download_new=True)
dialog_download(observer_login=observer_login,
observer_password=observer_password,
sender_id=receiver_profile_id,
receiver_profile_id=receiver_profile_id,
download_new=True)
return True
def db_load_profile_description(profile_id: str) -> bool:
from main import logger
# check profile delete status
if not db_profile_available(profile_id=profile_id):
logger.info(f'User {current_user} opened load of profile '
f'description for profile_id: {profile_id},'
f'but this profile deleted on site')
return False
else:
logger.info(f'User {current_user} opened load of profile '
f'description for profile_id: {profile_id}')
db_session = Session()
# collect info from site
profile_details = collect_info_from_profile(profile_id=profile_id)
# delete old profile info
if db_duplicate_check([ProfileDescription],
ProfileDescription.profile_id == profile_id):
logger.info(f'User {current_user} deleted old profile '
f'description for profile_id: {profile_id}')
# delete profile description
db_delete_rows([ProfileDescription],
ProfileDescription.profile_id == profile_id)
# delete old category level
db_delete_rows([ProfileCategories],
ProfileCategories.profile_id == profile_id)
# delete old languages
db_delete_rows([ProfileLanguages],
ProfileLanguages.profile_id == profile_id)
# create new profile info
new_pr_desc = ProfileDescription(profile_id=profile_id)
for i in range(len(profile_details)):
key = list(profile_details.keys())[i]
val = profile_details[key]
if type(val) == str:
if val.lower() == 'not specified':
val = None
if key == 'Languages':
# add new languages
for lang in profile_details[key].keys():
language = lang
level_name = profile_details[key][lang]
# check if user not specified languages
if language.lower() == 'not specified':
continue
elif level_name.lower() == 'not specified':
level_name = None
new_lang_lvl = ProfileLanguages(profile_id=profile_id,
language=language,
level_name=level_name)
db_session.add(new_lang_lvl)
db_session.commit()
continue
# check if key in categories table
elif db_duplicate_check([Categories],
Categories.category_name == key.lower()):
# add new category levels
new_category_lvl = ProfileCategories(category_name=key.lower(),
profile_id=profile_id,
level_name=val)
db_session.add(new_category_lvl)
db_session.commit()
continue
setattr(new_pr_desc, key.lower(), val)
db_session.close()
db_session = Session()
db_session.add(new_pr_desc)
db_session.commit()
logger.info(f'User {current_user} added profile '
f'description for profile_id: {profile_id}')
db_session.close()
return True
def create_invite(creator: User, invited_email: str, role: str) -> bool:
from main import logger
db_session = Session()
invite = Invites(invite_id=uuid4().bytes)
# new user creating
new_user = Users(login=invited_email,
user_password=generate_password_hash(invite.invite_id,
"sha256",
salt_length=8))
# assign role to user
new_user_role = RolesOfUsers(login=invited_email, user_role=role)
# create invite from user
sent_invite_from = SentInvites(invite_id=invite.invite_id,
login=creator.login)
# create invite to user
sent_invite_to = SentInvites(invite_id=invite.invite_id,
login=new_user.login)
# database duplicate check
users = db_get_users(Users.login == invited_email)
if len(users) > 0:
if users[0]['role'] == 'deleted':
db_session = Session()
# create new invite id
db_session.add(invite)
db_session.commit()
# create new users-invite link
db_session.add(sent_invite_from)
db_session.add(sent_invite_to)
db_session.commit()
# change password from deleted to new, based on invite_id
update_q = update(Users).where(
Users.login == invited_email). \
values(user_password=new_user.user_password)
db_session.execute(update_q)
db_session.commit()
db_session.close()
# reload users, for next checks
users = db_get_users(Users.login == invited_email)
# user already created
if users[0]['register_status']:
# user already registered
logger.info(f'User {current_user.login} '
f'tried to create invite for '
f'already registered user: {invited_email}')
return False
if users[0]['role'] != role:
# user role another from db role
# check user role is valid
query = db_session.query(UserRoles).filter(
UserRoles.user_role == role)
if len(query.all()) == 0:
return False
update_q = update(RolesOfUsers).where(
RolesOfUsers.login == invited_email). \
values(user_role=role)
db_session.execute(update_q)
db_session.commit()
logger.info(f'User {current_user.login} '
f'update role for unregistered user: {invited_email}')
logger.info(f'User {current_user.login} '
f'resend invite to: {invited_email}')
return True
else:
# no user in DB
db_session.add(invite)
db_session.commit()
db_session.add(new_user)
db_session.commit()
db_session.add(new_user_role)
db_session.commit()
db_session.add(sent_invite_from)
db_session.add(sent_invite_to)
db_session.commit()
db_session.close()
logger.info(f'created invite for e-mail: {invited_email}')
return True
def db_show_dialog(sender: str = None,
receiver: str = None,
email_filter: bool = False,
inbox_filter: bool = False,
outbox_filter: bool = False,
descending: bool = False) -> list:
# you can swap sender and receiver
"""Returns list of dicts, with messages in dialogue for admin panel"""
db_session = Session()
# Check visibility of profiles for current user
sub_query_0 = db_session.query(ChatSessions.chat_id). \
filter(ChatSessions.profile_id == Visibility.profile_id). \
filter(Visibility.login == current_user.login)
# subquery for chats with this profiles
query_chat = db_session.query(ChatSessions.chat_id). \
filter(ChatSessions.chat_id.in_(sub_query_0))
if sender:
# subquery for chats with sender
sub_query_1 = db_session.query(ChatSessions.chat_id). \
filter(ChatSessions.profile_id == sender).subquery()
# subquery for chats with this profiles
query_chat = db_session.query(ChatSessions.chat_id). \
filter(ChatSessions.chat_id.in_(sub_query_1))
if receiver:
# subquery for chats with receiver, if we have receiver id
sub_query_2 = db_session.query(ChatSessions.chat_id). \
filter(ChatSessions.profile_id == receiver).subquery()
# subquery for chats with this profiles
query_chat = query_chat. \
filter(ChatSessions.chat_id.in_(sub_query_2))
# filter only email dialogues
if email_filter:
query_chat = query_chat.filter(ChatSessions.email_address)
query_chat = query_chat.subquery()
# query to find accounts which have dialog
find_account_query = db_session.query(ChatSessions.profile_id). \
filter(ChatSessions.chat_id.in_(sub_query_0)). \
filter(Profiles.profile_id == ChatSessions.profile_id). \
filter(Profiles.profile_password != '').subquery()
# QUERY FOR MESSAGES
# query to show messages and texts
query = db_session.query(Messages.profile_id, Messages.send_time,
Messages.viewed, Texts.text,
ProfileDescription.nickname,
Messages.message_token)
query = query.filter(Messages.chat_id.in_(query_chat))
query = query.outerjoin(Texts, Messages.text_id == Texts.text_id)
query = query.filter(ProfileDescription.profile_id == Messages.profile_id)
if inbox_filter:
# show only inbox messages
query = query.filter(Messages.profile_id.notin_(find_account_query))
elif outbox_filter:
# show only inbox messages
query = query.filter(Messages.profile_id.in_(find_account_query))
# sorting
if descending:
query = query.order_by(desc(Messages.send_time))
else:
query = query.order_by(Messages.send_time)
# QUERY FOR ACCOUNTS
# query to show messages and texts
query_a = db_session.query(Messages.message_token)
query_a = query_a.filter(Messages.chat_id.in_(query_chat))
query_a = query_a.outerjoin(Texts, Messages.text_id == Texts.text_id)
query_a = query_a.filter(
ProfileDescription.profile_id == Messages.profile_id)
if inbox_filter:
# show only inbox messages
query_a = query_a.filter(
Messages.profile_id.notin_(find_account_query))
elif outbox_filter:
# show only inbox messages
query_a = query_a.filter(Messages.profile_id.in_(find_account_query))
query_a = query_a.order_by(Messages.send_time).subquery()
# final query to link messages with accounts
accounts = db_session.query(ProfileDescription.nickname,
ProfileDescription.profile_id,
Messages.message_token)
accounts = accounts. \
filter(ChatSessions.chat_id == Messages.chat_id). \
filter(ChatSessions.profile_id == Profiles.profile_id). \
filter(ProfileDescription.profile_id == Profiles.profile_id). \
filter(Profiles.profile_password != '')
accounts = accounts.join(query_a,
Messages.message_token == query_a.c.message_token)
result_accounts = accounts.all()
result = query.all()
db_session.close()
print(result)
print(result_accounts)
return [{
"profile_id": result[i][0],
"send_time": result[i][1],
"viewed": result[i][2],
"text": result[i][3],
"nickname": result[i][4],
"message_token": result[i][5],
"account_nickname": result_accounts[i][0],
"account_id": result_accounts[i][1]
} for i in range(len(result))]