def get_players_in_room_from_db(cls, room_id):
with contextlib.closing(
DBConnection.get_connection()) as db_connection:
query = "SELECT * FROM players WHERE room_id=?"
result = db_connection.cursor().execute(query, (room_id, ))
players_as_dict = result.fetchall()
return players_as_dict
def add_new_player_to_db(player):
with contextlib.closing(
DBConnection.get_connection()) as db_connection:
with db_connection as current_transaction:
query = "INSERT INTO players(id, username, password, room_id) VALUES(?, ?, ?, ?);"
current_transaction.execute(
query, (player.id, player.username, player.password, None))
def update_player_linked_room_in_db(player_username, room_id):
with contextlib.closing(
DBConnection.get_connection()) as db_connection:
with db_connection as current_transaction:
query = "UPDATE players SET room_id=? WHERE username=?"
current_transaction.cursor().execute(
query, (room_id, player_username))
def check_if_room_with_same_name_already_exists(room_name):
with contextlib.closing(
DBConnection.get_connection()) as db_connection:
query = "SELECT name FROM rooms WHERE name=?"
result = db_connection.cursor().execute(query, (room_name, ))
if result.fetchone() is None:
return False
return True
def add_room_to_db(room):
with contextlib.closing(
DBConnection.get_connection()) as db_connection:
with db_connection as current_transaction:
query = "INSERT INTO rooms(id, name, state, host_username) VALUES(?, ?, ?, ?);"
current_transaction.execute(
query,
(room.id, room.name, room.state, room.host.username))
def check_if_player_exists(player_username):
with contextlib.closing(
DBConnection.get_connection()) as db_connection:
query = "SELECT * FROM players WHERE username=?"
result = db_connection.cursor().execute(query, (player_username, ))
if result.fetchone() is None:
return False
return True
def create_players_table():
with contextlib.closing(
DBConnection.get_connection()) as db_connection:
query = "CREATE TABLE IF NOT EXISTS players (" \
"id TEXT PRIMARY KEY NOT NULL," \
"username TEXT NOT NULL," \
"password TEXT NOT NULL," \
"room_id TEXT" \
");"
db_connection.cursor().execute(query)
def get_room_details_from_db(cls, room_name):
with contextlib.closing(
DBConnection.get_connection()) as db_connection:
query = "SELECT * FROM rooms WHERE name=?"
result = db_connection.cursor().execute(query, (room_name, ))
room_as_dict = result.fetchone()
if room_as_dict is None:
return room_as_dict
room_as_object = cls.create_room_object_from_dict(room_as_dict)
return room_as_object
def create_rooms_table():
with contextlib.closing(
DBConnection.get_connection()) as db_connection:
query = "CREATE TABLE IF NOT EXISTS rooms (" \
"id TEXT PRIMARY KEY," \
"name TEXT NOT NULL," \
"state TEXT NOT NULL," \
"host_username TEXT NOT NULL" \
");"
db_connection.cursor().execute(query)
def check_if_player_already_in_a_room(player_username):
with contextlib.closing(
DBConnection.get_connection()) as db_connection:
query = "SELECT * FROM players WHERE username=?"
result = db_connection.cursor().execute(query, (player_username, ))
player_details = result.fetchone()
if player_details is None:
return False
if player_details['room_id'] is None:
return False
return True
def get_available_rooms_from_db(cls):
with contextlib.closing(
DBConnection.get_connection()) as db_connection:
query = "SELECT * FROM rooms"
result = db_connection.cursor().execute(query)
rooms_as_dicts = result.fetchall()
rooms_as_objects = []
for room_as_dict in rooms_as_dicts:
room_as_object = cls.create_room_object_from_dict(room_as_dict)
rooms_as_objects.append(room_as_object)
return rooms_as_objects
def get_player_details_from_db(player_username):
with contextlib.closing(
DBConnection.get_connection()) as db_connection:
query = "SELECT * FROM players WHERE username=?"
result = db_connection.cursor().execute(query, (player_username, ))
player_as_dict = result.fetchone()
if player_as_dict is None:
return None
player_id = player_as_dict['id']
player_password = player_as_dict['password']
player_as_object = Player(player_id, player_username,
player_password)
return player_as_object