2019-04-01 16:13:44 +02:00
|
|
|
from database_connection import DatabaseConnection, SQLInjectionError, get_sql_time
|
2019-03-14 00:01:02 +01:00
|
|
|
from user import User
|
|
|
|
import datetime
|
|
|
|
import hashlib
|
|
|
|
import uuid
|
2019-03-29 12:52:03 +01:00
|
|
|
from settings import elo_start_value
|
2019-03-14 00:01:02 +01:00
|
|
|
|
2019-04-01 16:13:44 +02:00
|
|
|
from tools import debug
|
|
|
|
|
2019-03-14 00:01:02 +01:00
|
|
|
|
|
|
|
class UserManager(object):
|
2019-04-01 16:13:44 +02:00
|
|
|
def __init__(self, account_lifespan_timedelta):
|
|
|
|
self.account_lifespan_timedelta = account_lifespan_timedelta
|
2019-03-14 00:01:02 +01:00
|
|
|
pass
|
|
|
|
|
|
|
|
def get_user(self, user_name):
|
2019-03-27 17:23:04 +01:00
|
|
|
query = "SELECT name, last_seen FROM users where name=%s"
|
|
|
|
return DatabaseConnection.global_single_query(query, (user_name))
|
2019-03-14 00:01:02 +01:00
|
|
|
|
|
|
|
def delete_user(self, user_name):
|
|
|
|
|
2019-03-24 16:44:20 +01:00
|
|
|
# remove from friends:
|
2019-03-27 17:23:04 +01:00
|
|
|
query = "DELETE FROM friends WHERE user=%s"
|
|
|
|
DatabaseConnection.global_single_execution(query, (user_name))
|
2019-03-24 16:44:20 +01:00
|
|
|
|
2019-03-27 17:23:04 +01:00
|
|
|
query = "DELETE FROM friends WHERE friend=%s"
|
|
|
|
DatabaseConnection.global_single_execution(query, (user_name))
|
2019-03-24 16:44:20 +01:00
|
|
|
|
2019-03-29 15:21:55 +01:00
|
|
|
# remove from sessions:
|
|
|
|
query = "DELETE FROM sessions WHERE registered_user=%s"
|
|
|
|
DatabaseConnection.global_single_execution(query, (user_name))
|
|
|
|
|
|
|
|
# remove from matches:
|
|
|
|
query = "DELETE FROM matches WHERE user_a=%s OR user_b=%s"
|
|
|
|
DatabaseConnection.global_single_execution(query, (user_name, user_name))
|
|
|
|
|
|
|
|
# finally remove user
|
|
|
|
|
|
|
|
query = "DELETE FROM users where name=%s"
|
|
|
|
DatabaseConnection.global_single_execution(query, (user_name))
|
|
|
|
|
2019-03-14 00:01:02 +01:00
|
|
|
def verify_user(self, user_name, pw):
|
2019-03-27 17:23:04 +01:00
|
|
|
query = "SELECT * FROM users where name=%s"
|
|
|
|
users = DatabaseConnection.global_single_query(query, (user_name))
|
2019-03-14 00:01:02 +01:00
|
|
|
if len(self.get_user(user_name)) == 0:
|
|
|
|
return False
|
|
|
|
|
|
|
|
user = users[0]
|
|
|
|
pw_salt = user['pw_salt']
|
|
|
|
stored_pw_hash = user['pw_hash']
|
|
|
|
|
|
|
|
pw_hash = hashlib.sha512(pw.encode() + pw_salt.encode()).hexdigest()
|
|
|
|
return stored_pw_hash == pw_hash
|
|
|
|
|
|
|
|
def create_user(self, user_name, pw):
|
|
|
|
assert len(self.get_user(user_name)) == 0
|
|
|
|
pw_salt = uuid.uuid4().hex
|
|
|
|
pw_hash = hashlib.sha512(pw.encode() + pw_salt.encode()).hexdigest()
|
2019-03-29 12:52:03 +01:00
|
|
|
query = "INSERT INTO users (name, pw_hash, pw_salt, last_seen, elo) VALUES ( %s, %s, %s, %s, %s)"
|
|
|
|
DatabaseConnection.global_single_execution(
|
|
|
|
query, (user_name, pw_hash, pw_salt, datetime.datetime.now(), elo_start_value))
|
|
|
|
|
2019-03-14 00:01:02 +01:00
|
|
|
def touch_user(self, user_name):
|
2019-03-27 17:23:04 +01:00
|
|
|
query = "UPDATE users SET last_seen=%s WHERE name=%s"
|
2019-03-29 12:52:03 +01:00
|
|
|
DatabaseConnection.global_single_execution(
|
|
|
|
query, (datetime.datetime.now(), user_name))
|
|
|
|
|
|
|
|
def update_elo(self, user_name, new_elo):
|
|
|
|
query = "UPDATE users SET elo=%s WHERE name=%s"
|
|
|
|
DatabaseConnection.global_single_execution(query, (new_elo, user_name))
|
|
|
|
|
|
|
|
def get_elo(self, user_name):
|
|
|
|
query = "SELECT elo FROM users WHERE name=%s"
|
|
|
|
q = DatabaseConnection.global_single_query(query, (user_name))
|
|
|
|
if len(q) > 0:
|
|
|
|
return q[0]['elo']
|
|
|
|
|
|
|
|
return None
|
|
|
|
|
|
|
|
def get_average_elo(self):
|
|
|
|
query = "SELECT AVG(elo) AS average FROM users"
|
|
|
|
q = DatabaseConnection.global_single_query(query)
|
|
|
|
if len(q) > 0:
|
|
|
|
return float(q[0]['average'])
|
|
|
|
|
|
|
|
return None
|
2019-03-29 14:50:38 +01:00
|
|
|
|
|
|
|
def get_highscores(self, n, list_offset):
|
|
|
|
query = "SELECT name, elo FROM users ORDER BY elo DESC LIMIT %s OFFSET %s"
|
|
|
|
q = DatabaseConnection.global_single_query(query, (n, list_offset))
|
|
|
|
names = []
|
|
|
|
elos = []
|
|
|
|
for entry in q:
|
|
|
|
names.append(entry['name'])
|
|
|
|
elos.append(int(entry['elo']))
|
|
|
|
|
|
|
|
return names, elos
|
|
|
|
|
|
|
|
def get_rank_for_user(self, user_name):
|
|
|
|
query = "SELECT Count(*)+1 as rank from users where elo >(SELECT elo from users WHERE name=%s)"
|
|
|
|
q = DatabaseConnection.global_single_query(query, (user_name))
|
|
|
|
return int(q[0]['rank'])
|
2019-03-29 12:52:03 +01:00
|
|
|
|
2019-03-24 16:44:20 +01:00
|
|
|
def add_friend_to_user(self, user_name, friend_name):
|
|
|
|
if len(self.get_user(friend_name)) > 0:
|
2019-03-27 17:23:04 +01:00
|
|
|
query = "INSERT INTO friends (user, friend) VALUES ( %s, %s)"
|
2019-03-29 12:52:03 +01:00
|
|
|
DatabaseConnection.global_single_execution(
|
|
|
|
query, (user_name, friend_name))
|
2019-03-24 16:44:20 +01:00
|
|
|
return True
|
|
|
|
return False
|
2019-03-29 12:52:03 +01:00
|
|
|
|
2019-03-24 16:44:20 +01:00
|
|
|
def get_friends_for_user(self, user_name):
|
2019-03-27 17:23:04 +01:00
|
|
|
query = "SELECT friend FROM friends WHERE user=%s"
|
|
|
|
tmp = DatabaseConnection.global_single_query(query, (user_name))
|
2019-03-24 16:44:20 +01:00
|
|
|
friends = set()
|
|
|
|
for entry in tmp:
|
|
|
|
friends.add(entry['friend'])
|
2019-03-29 12:52:03 +01:00
|
|
|
return list(friends)
|
|
|
|
|
|
|
|
def get_friends_and_elos_for_user(self, user_name):
|
|
|
|
query = "SELECT friends.friend AS friend, users.elo AS elo FROM friends JOIN users ON friends.friend=users.name WHERE friends.user=%s"
|
|
|
|
tmp = DatabaseConnection.global_single_query(query, (user_name))
|
|
|
|
friends = []
|
|
|
|
elos = []
|
|
|
|
for entry in tmp:
|
|
|
|
friends.append(entry['friend'])
|
|
|
|
elos.append(entry['elo'])
|
|
|
|
return friends, elos
|
|
|
|
|
2019-03-24 16:44:20 +01:00
|
|
|
def remove_friend_from_user(self, user_name, friend_name):
|
2019-03-27 17:23:04 +01:00
|
|
|
query = "DELETE FROM friends WHERE user=%s AND friend=%s"
|
2019-03-29 12:52:03 +01:00
|
|
|
DatabaseConnection.global_single_execution(
|
|
|
|
query, (user_name, friend_name))
|
2019-03-14 00:01:02 +01:00
|
|
|
|
|
|
|
def get_all_users(self):
|
|
|
|
query = "SELECT name, last_seen FROM users"
|
|
|
|
return DatabaseConnection.global_single_query(query)
|
2019-04-01 16:13:44 +02:00
|
|
|
|
|
|
|
def revoke_inactive_accounts(self):
|
|
|
|
revoke_time = datetime.datetime.now() - self.account_lifespan_timedelta
|
|
|
|
query = "SELECT * from users WHERE last_seen < %s"
|
|
|
|
revoked_sessions = DatabaseConnection.global_single_query(query, (get_sql_time(revoke_time)))
|
|
|
|
|
|
|
|
for entry in revoked_sessions:
|
|
|
|
name = entry['name']
|
|
|
|
self.delete_user(name)
|
|
|
|
|
|
|
|
debug("deleted users: " + str(revoked_sessions))
|