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_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 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 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 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_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_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_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_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_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
