def get_online(username, args=None):
"""
Checks if there're online users.
With no args parameter searches for all online users.
With specified args parameter searches only for specified ones.
User's data tuple contains:
- username
- is active (1 - Yes, 0 - No)
- date of last activity in seconds
:param username: who executed the command
:param args: list of usernames, optional
:return: list of tuples with users' data about their activity
"""
connection = create_connection("data.sqlite3")
if args:
select_users = f"SELECT username, is_active, last_active " \
f"FROM users " \
f"WHERE username IN ({','.join(['?'] * len(args))})"
query_data = execute_read_query(connection, select_users, 1, args)
else:
query_data = execute_read_query(connection,
select_queries['select_active_users'])
connection.close()
return query_data
def status(*args):
"""
Calculates server time, amount of users, online users & messages.
:return: dict of all calculated data
:rtype: {
"status": bool,
"time": str,
"users_count": int,
"messages_count": int,
"users_online": int
}
"""
connection = create_connection("data.sqlite3")
select_users_count = "SELECT Count(*) FROM users"
users_count = execute_read_query(connection, select_users_count, 0)
select_messages_count = "SELECT Count(*) FROM messages"
messages_count = execute_read_query(connection, select_messages_count, 0)
users_online = len(get_online(None, None))
connection.close()
return {
"status": True,
"time": datetime.now().strftime("%d/%m/%Y, %H:%M:%S"),
"users_count": users_count[0],
"messages_count": messages_count[0],
"users_online": users_online,
}
def send_message():
"""
Stores the message, time of sending and username of author in database.
:request: {
"username": str,
"text": str
}
:return: dict of execution status
:rtype: {"ok": bool}
"""
data = request.json
username = data["username"]
text = data["text"]
connection = create_connection("data.sqlite3")
select_user_id = f"SELECT id FROM users WHERE username LIKE :username"
query_data = execute_read_query(connection, select_user_id, 0,
{'username': username})
data_dict = {'text': text, 'id': query_data[0]}
new_message = f"INSERT INTO messages (text, time, user_id) " \
f"VALUES (:text, strftime('%s','now'), :id)"
execute_query(connection, new_message, data_dict)
connection.close()
return {'ok': True}
def get_messages():
"""
Collects new messages after specified point of time.
:request: ?after=1234567890.4 - point of time
:return: new messages after specified point
:rtype: {
"messages": [
{"username": str, "text": str, "time": float},
...
]
}
"""
after_time = float(request.args['after'])
new_messages = []
connection = create_connection("data.sqlite3")
select_messages = f"SELECT m.text, m.time, u.username FROM messages m " \
f"INNER JOIN users u " \
f"ON m.user_id = u.id " \
f"WHERE m.time > :after"
query_data = execute_read_query(connection, select_messages, 1,
{'after': after_time})
# Generate messages.
for data in query_data:
message = {'username': data[2], 'text': data[0], 'time': data[1]}
new_messages.append(message)
connection.close()
return {"messages": new_messages}
def sign_up_user():
"""
Register user.
Confirms whether login and password are in range and whether user exist.
Hashes and encrypts password using :func:`codec`.
Stores user's data in database.
request: {
"username": str,
"password": str
}
:return: dict of execution status
:rtype: {
"login_out_of_range": bool,
"password_out_of_range": bool,
"ok": bool
}
"""
username = request.authorization.username
password = request.authorization.password
# Make sure credentials are in range.
if len(username) not in range(4, 20, 1):
return {"login_out_of_range": True}
elif len(password) not in range(4, 20, 1):
return {"login_out_of_range": False, "password_out_of_range": True}
connection = create_connection("data.sqlite3")
select_user = f"SELECT id FROM users WHERE username LIKE :username"
query_data = execute_read_query(connection, select_user, 0,
{'username': username})
# If user isn't registered, encrypt password and store in database.
if query_data is None:
password_hash = codec(password, 1)
password_hash = Binary(password_hash)
data_dict = {'username': username, 'password_hash': password_hash}
create_user = f"INSERT INTO users (username, password_hash, registered)" \
f"VALUES (:username, :password_hash, strftime('%s','now'))"
execute_query(connection, create_user, data_dict)
else:
connection.close()
return {
"login_out_of_range": False,
"password_out_of_range": False,
'ok': False
}
connection.close()
return {
"login_out_of_range": False,
"password_out_of_range": False,
'ok': True
}
def auth_user():
"""
Verifies user exists, password matches and whether user is banned.
request: {
"username": str,
"password": str
}
:return: dict of execution status
:rtype: {
"exist": bool,
"match": bool,
"banned": bool
}
"""
username = request.authorization.username
password = request.authorization.password
connection = create_connection("data.sqlite3")
select_user = f"SELECT password_hash, is_banned FROM users WHERE username LIKE :username"
query_data = execute_read_query(connection, select_user, 0,
{'username': username})
if query_data is None:
connection.close()
return {'exist': False}
# Decrypt password hash.
password_hash = codec(query_data[0], 0)
# Compare entered password and hash from database.
if not check_password(password.encode(), password_hash):
connection.close()
return {'exist': True, 'match': False}
# Check if user is banned.
elif query_data[1] == 1:
connection.close()
return {'exist': True, 'match': True, 'banned': True}
set_online = f"UPDATE users " \
f"SET is_active = 1 " \
f"WHERE username LIKE :username"
execute_query(connection, set_online, {'username': username})
connection.close()
return {'exist': True, 'match': True, 'banned': False}
def get_registered(*args):
"""
Queries all registered users.
:param args: not used, optional
:return: list of one-element tuples with registered usernames
"""
connection = create_connection("data.sqlite3")
all_usernames = select_queries['select_all_usernames']
query_data = execute_read_query(connection, all_usernames)
connection.close()
return query_data
def get_permissions(username):
"""
Checks on user's permissions.
There're 3 numbers for the permissions: 1, 2 and 3.
Where: 1 - User, 2 - Moderator, 3 - Administrator.
:param username: who executed the command
:return: one-element tuple with number for the permissions
"""
connection = create_connection("data.sqlite3")
select_permission = f"SELECT role " \
f"FROM users " \
f"WHERE username LIKE :username"
query_data = execute_read_query(connection, select_permission, 0,
{'username': username})
connection.close()
return query_data
def myself(username, *args):
"""
Queries user's data.
Executes read query to get user's data:
- id
- permissions
- date of registration in seconds
- date of last activity in seconds
:param username: who executed the command
:return: tuple of user's data
"""
connection = create_connection("data.sqlite3")
select_user = f"SELECT id, role, registered, last_active " \
f"FROM users " \
f"WHERE username LIKE :username"
query_data = execute_read_query(connection, select_user, 0,
{'username': username})
connection.close()
return query_data