You must add a file SQL_TESTING.md to your repository and provide the following for each table (at least 3 tables):

Table Name

rooms

Table Description

This table stores information about different rooms

For each field of the table, provide name and short description.

• roomID : unique identifier for each room
• hostName: the owner of the room
• gameStatus : Boolean indicating the game status for the specific room
• numPlayers : maximum number of players in the specific room

List of tests for verifying each table

1. Test for inserting a new room:

• Description: Check if a new room can be inserted into the rooms table successfully.
• Steps:
  • Insert a new room with a unique roomID and a gameStatus value.
  • Retrieve the inserted room and verify if the data matches the inserted values.
• Expected Result: The new room should be inserted without errors, and the retrieved data should match the inserted values.

1. Test for updating the game status of a room:

• Description: Check if the game status of an existing room can be updated.
• Steps:
  • Insert a new room into the rooms table.
  • Update the gameStatus of the inserted room.
  • Retrieve the updated room and verify if the gameStatus has been changed accordingly.
• Expected Result: The game status of the room should be updated without errors.

1. Test for deleting a room:

• Description: Check if a room can be deleted from the rooms table.
• Steps:
  • Insert a new room into the rooms table.
  • Delete the inserted room using its roomID.
  • Try to retrieve the deleted room from the table.
• Expected Result: The deleted room should not be found in the table.

Table Name

players

Table Description

This table stores information about players in each room

For each field of the table, provide name and short description.

• playerID : unique identifier for each player
• roomID : reference the roomID in the rooms table
• name: display each player's name
• sid: unique identifier generated by SocketIo
• hand: store each player's hand data
• discard: discarded card information
• isHost: Boolean indicating room host
• readyStatus: check if the player is ready or not
• token: the number of tokens to win the game
• isMyTurn: Check if this is current player's turn

List of tests for verifying each table

1. Test for inserting a new player:

• Description: Check if a new player can be inserted into the players table successfully.
• Steps:
  • Insert a new player with a unique playerID and a valid roomID.
  • Retrieve the inserted player and verify if the data matches the inserted values.
• Expected Result: The new player should be inserted without errors, and the retrieved data should match the inserted values.

1. Test for updating a player's hand:

• Description: Check if a player's hand can be updated.
• Steps:
  • Insert a new player into the players table.
  • Update the hand of the inserted player with new data.
  • Retrieve the updated player and verify if the hand has been changed accordingly.
• Expected Result: The player's hand should be updated without errors.

1. Test for deleting a player:

• Description: Check if a player can be deleted from the players table.
• Steps:
  • Insert a new player into the players table.
  • Delete the inserted player using its playerID.
  • Try to retrieve the deleted player from the table.
• Expected Result: The deleted player should not be found in the table.

Table Name

deck

Table Description

This table stores card information in a deck in each room

For each field of the table, provide name and short description.

• roomID : reference the roomID in the rooms table
• cards: JSONB representing the cards in the deck

List of tests for verifying each table

1. Test for inserting a new deck:

• Description: Check if a new deck can be inserted into the deck table successfully.
• Steps:
  • Insert a new deck with a valid roomID.
  • Retrieve the inserted deck and verify if the data matches the inserted values.
• Expected Result: The new deck should be inserted without errors, and the retrieved data should match the inserted values.

1. Test for updating the cards in a deck:

• Description: Check if the cards in a deck can be updated.
• Steps:
  • Insert a new deck into the deck table.
  • Update the cards of the inserted deck with new data.
  • Retrieve the updated deck and verify if the cards have been changed accordingly.
• Expected Result: The cards in the deck should be updated without errors.

1. Test for deleting a deck:

• Description: Check if a deck can be deleted from the deck table.
• Steps:
  • Insert a new deck into the deck table.
  • Delete the inserted deck using its deck_id.
  • Try to retrieve the deleted deck from the table.
• Expected Result: The deleted deck should not be found in the table.

You must also provide the following (in SQL_TESTING.md)for each data access method (at least one access method for each table or query required to get the data to display):

Table name

rooms

Access Method: insertRoom

Description: Inserts a new room into the rooms table.

Parameters:

• roomID: The unique identifier for the new room.
• hostName: the owner of the room
• numPlayers: maximum number of players in the specific room

Return Values:

• None

Tests for Insert Room:

1. Test for inserting a new room:

• Description:
  • Check if a new room can be inserted into the rooms table successfully
• Pre-conditions:
  • must have valid name, maximum number of players
• Test steps:
  1. Call the insertRoom method with valid form data
  2. Query the database to retrieve the inserted room
• Expected result:
  • The new room should be inserted into the rooms table
• Status:
  • Pass
• Post-conditions:
  • A new room can be found in the rooms table and player can join in this room

Access Method: updateGameStatus

Description: Updates the game status for a specific room in the rooms table.

Parameters:

• roomID: The unique identifier of the room to update.
• gameStatus: The new boolean value representing the updated game status.

Return Values:

• None

Tests for Update Game Status:

1. Test for updating the game status of a room:

• Description:
  • Check if the game status of an existing room can be updated.
• Pre-conditions:
  • The unique identifier of the room and a new boolean value representing the updated game status
• Test steps:
  1. Call the updateGameStatus method with the inserted roomID and a new gameStatus
  2. Query the database to retrieve the updated room
• Expected result:
  • The game status of the room should be updated without errors
• Acutal result:
  • The game status of the room (default value: false) should be updated to true
• Status:
  • Pass
• Post-conditions:
  • Once the game status is true, it means the game has started and no one can join the room anymore

Access Method: deleteRoom

Description: Deletes a room from the rooms table.

Parameters:

• roomID: The unique identifier of the room to delete.

Return Values:

• None

Tests for Delete Room:

1. Test for deleting a room:

• Description:
  • Check if a room can be deleted from the rooms table
• Pre-conditions:
  • The unique identifier of the room to delete
• Test steps:
  1. Call the deleteRoom method with the roomID
  2. Query the database to retrieve the deleted room
• Expected result:
  • The deleted room should not be found in the table
• Status:
  • Pass

Table name

players

Access Method: insertPlayer

Description: Inserts a new player into the players table.

Parameters:

• playerID: The unique identifier for the new player.
• roomID: The foreign key referencing the roomID in the rooms table.
• name: The player's name
• sid: The unique identifier generated by SocketIo

Return Values:

• display an error message if the room is full

Tests for Insert Player:

1. Test for inserting a new player:

• Description:
  • Check if a new player can be inserted into the players table successfully.
• Pre-conditions:
  • valid playerID, roomID, name, sid number and some default values (hand, discard, readyStatus, token, isMyTurn etc.)
• Test steps:
  1. Call the Insert Player method with valid data shown in the above
  2. Query the database to retrieve the inserted player
• Expected result:
  • The new player should be inserted without errors, and the retrieved data should match the inserted values
• Status:
  • Pass
• Post-conditions:
  • The new player information should be inserted into the player table and we can query the player information with valid roomID

Access Method: updatePlayerInfo

Description: Updates the hand data for a specific player in the players table.

Parameters:

• playerID : The unique identifier of the player to update
• roomID : The foreign key referencing the roomID in the rooms table
• name: display each player's name
• sid: unique identifier generated by SocketIo
• hand: store each player's hand data
• discard: discarded card information
• isHost: Boolean indicating room host
• readyStatus: check if the player is ready or not
• token: the number of tokens to win the game
• isMyTurn: Check if this is current player's turn

Return Values:

• None

Tests for Update Player's information:

1. Test for updating a player's information:

• Description:
  • Check if a specific player's info are updated in the players table successfully.
• Pre-conditions:
  • valid playerID, roomID and new data
• Test steps:
  • Insert a new player into the players table
  • Call the UpdatePlayerInfo method with the inserted playerID, roomID and new data:
  • Query the database to retrieve the updated player
• Expected result:
  • The player's new info should be updated without errors
• Acutal result:
  • The new player should be inserted without errors, and the retrieved data should match the inserted values
• Status:
  • Pass
• Post-conditions:
  • the game page can display new information about each player

Access Method: deletePlayer

Description: Deletes a player from the players table.

Parameters:

• playerID: The unique identifier of the player to delete.

Return Values:

• None

Tests for Delete Player:

1. Test for deleting a player:

• Description:
  • Check if a player can be deleted from the players table
• Pre-conditions:
  • valid playerID
• Test steps:
  • Insert a new player into the players table
  • Call the deletePlayer method with the inserted playerID
  • Query the database to retrieve the deleted player
• Expected result:
  • The deleted player should not be found in the table
• Status:
  • Pass

Table Name

deck

Access Method: InsertDeck

Description: Inserts a new deck into the deck table.

Parameters:

• roomID: The foreign key referencing the roomID in the rooms table.
• cards: The JSONB data representing the cards in the deck.

Return Values:

• display an error message if the room already had one deck

Tests for Insert Deck:

1. Test for deleting a player:

• Description:
  • Check if a player can be deleted from the players table
• Pre-conditions:
  • valid roomID and cards
• Test steps:
  • Call the Insert Deck method with valid roomID and cards values
  • Query the database to retrieve the inserted deck
• Expected result:
  • The new deck should be inserted without errors, and the retrieved data should match the inserted values
• Status:
  • Pass
• Post-conditions:
  • Once the deck is created and the game has started, each player can draw a card from the deck in their own current turn

Access Method: UpdateCards

Description: Updates the cards data for a specific deck in the deck table.

Parameters:

• roomID: The foreign key referencing the roomID in the rooms table
• cards: The new JSONB data representing the updated cards in the deck.

Return Values:

• None

Tests for Update Deck Cards:

1. Test for deleting a player:

• Description:
  • Check if the cards in a deck can be updated
• Pre-conditions:
  • valid roomID and new cards data
• Test steps:
  • Call the updateCards method with valid roomID and new cards values
  • Query the database to retrieve the new updated deck
• Expected result:
  • The cards in the deck should be updated
• Status:
  • Pass
• Post-conditions:
  • Once the deck is created and the game has started, each player can draw a card from the deck in their own current turn

Access Method: Delete Deck

Description: Deletes a deck from the deck table.

Parameters:

• roomID: The foreign key referencing the roomID in the rooms table

Return Values:

• None

Tests for Delete Deck:

1. Test for deleting a deck:

• Description:
  • Check if the cards in a deck can be updated
• Pre-conditions:
  • valid roomID and new cards data
• Test steps:
  • Insert a new deck into the deck table
  • Call the deleteDeck method with the inserted roomID
  • Query the database to retrieve the deleted deck
• Expected result:
  • The deleted deck should not be found in the table
• Status:
  • Pass

Database Design Document

Table Name: rooms

Fields:

• roomID : unique identifier for each room
• hostName: the owner of the room
• gameStatus : Boolean indicating the game status for the specific room
• numPlayers : maximum number of players in the specific room

Constraints:

• roomID is a unique identifier for each room, not null
• hostName should be not null
• numPlayers should be 2 - 4.

Table Name: players

Fields:

• playerID : unique identifier for each player
• roomID : reference the roomID in the rooms table
• name: display each player's name
• sid: unique identifier generated by SocketIo
• hand: store each player's hand data
• discard: discarded card information
• isHost: Boolean indicating room host
• readyStatus: check if the player is ready or not
• token: the number of tokens to win the game
• isMyTurn: Check if this is current player's turn

Constraints:

• playerID is an auto-incrementing primary key, uniquely identifying each player.
• roomID is a foreign key referencing roomID in the rooms table, not null
• Each player's roomID should have a corresponding room in the rooms table.

Table Name: deck

Fields:

• roomID : a foreign key referencing room_id in the rooms table
• cards: JSONB representing the cards in the deck

Constraints:

• roomID is a foreign key referencing roomID in the rooms table.
• Each deck's roomID should have a corresponding room in the rooms table.

Relationships:

One room has one game deck. Table: rooms (roomID) -> Table: deck (roomID)

One room has multiple players. Table: rooms (roomID) -> Table: players (roomID)

Access Functions:

• insertRoom: Inserts a new room into the rooms table.
• updateGameStatus: Updates the game status for a specific room in the rooms table.
• deleteRoom: Deletes a room from the rooms table.
• insertPlayer: Inserts a new player into the players table.
• updatePlayerInfo: Updates new information for a specific player in the players table
• deletePlayer: Deletes a player from the players table.
• insertDeck: Inserts a new deck into the deck table
• updateCards: Updates the cards data for a specific deck in the deck table
• deleteDeck: Deletes a deck from the deck table

Tests:

Test Insert Room:

• Verify if a new room can be inserted into the rooms table successfully.

Test Update Game Status:

• Verify if the game status of an existing room can be updated.

Test Delete Room:

• Verify if a room can be deleted from the rooms table.

Test Insert Player:

• Verify if a new player can be inserted into the players table successfully.

Test Update Player's information:

• Verify if a player's new information can be updated.

Test Delete Player:

• Verify if a player can be deleted from the players table.

Test Insert Deck:

• Verify if a new deck can be inserted into the deck table successfully.

Test Update Deck Cards:

• Verify if the cards in a deck can be updated.

Test Delete Deck:

• Verify if a deck can be deleted from the deck table.

Pages Accessing Database Information:

Session Page: Displays information about each room, including the number of players, host name, room ID. Game Page: Displays information about each player, including name, their hand, their discard and ready status etc.

Tests for Pages:

Test Session Page:

• Verify if the Room Dashboard page displays the correct information about each room.

Test Game Page:

• Verify if the Game page displays the correct information about each player, their hand, their discard and ready status etc. the corresponding room etc.