240 lines
8.1 KiB
Python
240 lines
8.1 KiB
Python
import bcrypt
|
|
import datetime
|
|
import psycopg2
|
|
from textwrap import dedent
|
|
|
|
def create_database_connection(db_name, user, password, host, port=5432):
|
|
|
|
"""Create a database connection, then ensure that tables are created
|
|
|
|
Args:
|
|
db_name (str): name of database
|
|
user (str): username of database
|
|
password (str): password to database
|
|
host (str): host address of database
|
|
port (int): port of database
|
|
|
|
Returns:
|
|
psycopg2.connection: connection to database
|
|
"""
|
|
|
|
connection = psycopg2.connect(
|
|
dbname=db_name,
|
|
user=user,
|
|
password=password,
|
|
host=host,
|
|
port=port
|
|
)
|
|
with connection:
|
|
with connection.cursor() as curs:
|
|
curs.execute(dedent("""
|
|
CREATE TABLE IF NOT EXISTS Users (
|
|
id SERIAL PRIMARY KEY,
|
|
username varchar(255),
|
|
password_hash varchar(255),
|
|
name varchar(255),
|
|
dob date
|
|
);
|
|
CREATE TABLE IF NOT EXISTS Events (
|
|
id SERIAL PRIMARY KEY,
|
|
name varchar(255),
|
|
datetime timestamp,
|
|
max_age int,
|
|
completed boolean,
|
|
registration varchar(255),
|
|
results varchar(255)
|
|
);
|
|
CREATE TABLE IF NOT EXISTS Tournaments (
|
|
id SERIAL PRIMARY KEY,
|
|
name varchar(255),
|
|
start_date date,
|
|
end_date date,
|
|
events varchar(255)
|
|
);
|
|
"""))
|
|
return connection
|
|
|
|
class Users:
|
|
|
|
@staticmethod
|
|
def create_user(db, login_info, user_info):
|
|
|
|
"""Creates a new user in the database
|
|
|
|
Args:
|
|
db (psycopg2.connection): connection to postgres database
|
|
login_info (dict of str:str): username and password info
|
|
user_info (dict of str:value): name (str) and dob (datetime.date) info
|
|
|
|
Returns:
|
|
int: 0 if the username is non-unique, user id otherwise
|
|
"""
|
|
|
|
username, password = login_info["username"], login_info["password"]
|
|
password_hash = bcrypt.hashpw(password.encode(), bcrypt.gensalt()).decode()
|
|
name, dob = user_info["name"], user_info["dob"]
|
|
with db:
|
|
with db.cursor() as curs:
|
|
curs.execute("SELECT * FROM Users WHERE username = %s", (username,))
|
|
conflicts = curs.fetchall()
|
|
if conflicts:
|
|
return 0
|
|
curs.execute("INSERT INTO Users (username, password_hash, name, dob) VALUES (%s, %s, %s, %s) RETURNING id", (username, password_hash, name, dob))
|
|
user_id = curs.fetchone()[0]
|
|
db.commit()
|
|
return user_id
|
|
|
|
@staticmethod
|
|
def get_user_from_username(db, username):
|
|
|
|
"""Get a user id based on their username
|
|
|
|
Args:
|
|
db (psycopg2.connection): connection to postgres database
|
|
username (str): the username used to query
|
|
|
|
Returns:
|
|
int: 0 if the username is not in the system, user id otherwise
|
|
"""
|
|
|
|
with db:
|
|
with db.cursor() as curs:
|
|
curs.execute("SELECT * FROM Users WHERE username = %s", (username,))
|
|
users = curs.fetchall()
|
|
if not users:
|
|
return 0
|
|
return users[0][0]
|
|
|
|
@staticmethod
|
|
def get_user_details(db, user_id):
|
|
|
|
"""Gets the details of a user based on their id
|
|
|
|
Args:
|
|
db (psycopg2.connection): connection to postgres database
|
|
user_id (int): user id
|
|
|
|
Returns:
|
|
bool, dict of str to str, dict of str to value: user existance, login info (username, password), user info (name (str), dob (datetime.date))
|
|
"""
|
|
|
|
with db:
|
|
with db.cursor() as curs:
|
|
curs.execute("SELECT * FROM Users WHERE id = %s", (user_id,))
|
|
user = curs.fetchall()
|
|
if not user:
|
|
return False, None, None
|
|
user = user[0]
|
|
return True, {"username": user[1], "password_hash": user[2]}, {"name": user[3], "dob": user[4]}
|
|
|
|
class Tournaments:
|
|
|
|
@staticmethod
|
|
def create_event(db, name, time, max_age):
|
|
|
|
"""Creates a new event in the database
|
|
|
|
Args:
|
|
db (psycopg2.connection): connection to postgres database
|
|
name (str): event name
|
|
time (date.datetime): event time
|
|
max_age (int): how old a fencer is allowed to be at the time of the event starting
|
|
|
|
Returns:
|
|
int: id of the event created
|
|
"""
|
|
|
|
with db:
|
|
with db.cursor() as curs:
|
|
curs.execute("INSERT INTO Events (name, datetime, max_age, completed, registration, results) VALUES (%s, %s, %s, False, '', '') RETURNING id", (name, time, max_age))
|
|
event_id = curs.fetchone()[0]
|
|
db.commit()
|
|
return event_id
|
|
|
|
@staticmethod
|
|
def get_event_details(db, event_id):
|
|
|
|
"""Gets the details of an event based on its id
|
|
|
|
Args:
|
|
db (psycopg2.connection): connection to postgres database
|
|
event_id (int): event id
|
|
|
|
Returns:
|
|
bool, str, datetime.datetime, int, bool, list of ints, list of ints: existance of event, name, datetime of event, max age, event completion, registration, results
|
|
"""
|
|
|
|
with db:
|
|
with db.cursor() as curs:
|
|
curs.execute("SELECT * FROM Events WHERE id = %s", (event_id,))
|
|
event = curs.fetchall()
|
|
if not event:
|
|
return False, None, None, None, None, None, None
|
|
event = event[0]
|
|
return True, event[1], event[2], event[3], event[4], list(map(int, event[5].split())), list(map(int, event[6].split()))
|
|
|
|
@staticmethod
|
|
def create_tournament(db, name, event_ids):
|
|
|
|
"""Create a new tournament in the database
|
|
|
|
Args:
|
|
db (psycopg2.connection): connection to postgres database
|
|
name (str): name of tournament
|
|
event_ids (list of ints): list of events in the tournament
|
|
|
|
Returns:
|
|
int: id of event
|
|
"""
|
|
|
|
event_str = " ".join(map(str, event_ids))
|
|
events = [Tournaments.get_event_details(db, event_id)[2] for event_id in event_ids]
|
|
print(events)
|
|
start_date, end_date = min(events).date(), max(events).date()
|
|
with db:
|
|
with db.cursor() as curs:
|
|
curs.execute("INSERT INTO Tournaments (name, start_date, end_date, events) VALUES (%s, %s, %s, %s) RETURNING id", (name, start_date, end_date, event_str))
|
|
tournament_id = curs.fetchone()[0]
|
|
db.commit()
|
|
return tournament_id
|
|
|
|
@staticmethod
|
|
def add_event_registration(db, event_id, user_id):
|
|
|
|
"""Registers a user for an event
|
|
|
|
Args:
|
|
db (psycopg2.connection): connection to postgres database
|
|
event_id (int): id of event
|
|
user_id (int): id of user
|
|
|
|
Returns:
|
|
bool: registration success"""
|
|
|
|
event_details = Tournaments.get_event_details(db, event_id)
|
|
if datetime.datetime.now() >= event_details[2]:
|
|
return False
|
|
users = set(event_details[5])
|
|
users.add(user_id)
|
|
registration_str = " ".join(map(str, users))
|
|
with db:
|
|
with db.cursor() as curs:
|
|
curs.execute("UPDATE Events SET registration = %s WHERE id = %s", (registration_str, event_id))
|
|
db.commit()
|
|
return True
|
|
|
|
@staticmethod
|
|
def get_tournament_details(db, tournament_id):
|
|
|
|
with db:
|
|
with db.cursor() as curs:
|
|
curs.execute("SELECT * FROM Tournaments WHERE id = %s", (tournament_id,))
|
|
tournament = curs.fetchall()
|
|
if not tournament:
|
|
return False, None, None, None
|
|
tournament = tournament[0]
|
|
return True, tournament[1], tournament[2], tournament[3], list(map(int, tournament[4].split()))
|
|
|
|
@staticmethod
|
|
def add_event_results(db, event_id, results):
|
|
pass |