The tournament host (and anyone with the necessary permissions) must be able to assign and revoke specific permissions from staff team members via roles.
How?
The following procedures must be created:
Create staff role
Insert a record into the StaffRole table, the input of the procedure containing the values for name and tournamentId. The value for order must be one greater than the amount of staff roles in that tournament (can be achieved using an insert statement with a with clause). The rest are defaults.
The unique constraint uni_staff_role_name_tournament_id violation must be caught for the frontend to display as an error.
Update staff role
Update the StaffRole record by StaffRole.id, the input of the procedure containing the values for name, color and permissions.
The unique constraint uni_staff_role_name_tournament_id violation must be caught for the frontend to display as an error.
Swap staff role order
The order field dictates the order in which the staff roles are displayed within the tournament, being set by the host or anyone else with the permissions to manage staff roles (for example: 1- host -> 2- admin -> 3- referees -> n- ...).
The procedure must take as input the StaffRole.id of the two roles to swap the orders of and execute an update statement that does updates the order field values accordingly, using only one database query. Example:
1- Referee
2- Mappooler
3- Admin
Swap referee with admin (since admins are greater in importance, usually):
1- Admin
2- Mappooler
3- Referee
Delete staff role
Delete the StaffRole record by StaffRole.id.
You must also update the order value for whatever role had its order value greater than the role that will be deleted. Example:
1- Host
2- Co-host
3- Admin
4- Referee
If I want to delete the Co-host role, then the order for Admin and Referee must be updated too:
1- Host
2- Admin
3- Referee
Otherwise you end up with this:
1- Host
3- Admin
4- Referee
Why?
The tournament host (and anyone with the necessary permissions) must be able to assign and revoke specific permissions from staff team members via roles.
How?
The following procedures must be created:
Create staff role Insert a record into the
StaffRole
table, the input of the procedure containing the values forname
andtournamentId
. The value fororder
must be one greater than the amount of staff roles in that tournament (can be achieved using an insert statement with a with clause). The rest are defaults.The unique constraint
uni_staff_role_name_tournament_id
violation must be caught for the frontend to display as an error.Update staff role Update the
StaffRole
record byStaffRole.id
, the input of the procedure containing the values forname
,color
andpermissions
.The unique constraint
uni_staff_role_name_tournament_id
violation must be caught for the frontend to display as an error.Swap staff role order The
order
field dictates the order in which the staff roles are displayed within the tournament, being set by the host or anyone else with the permissions to manage staff roles (for example: 1- host -> 2- admin -> 3- referees -> n- ...).The procedure must take as input the
StaffRole.id
of the two roles to swap the orders of and execute an update statement that does updates theorder
field values accordingly, using only one database query. Example:Delete staff role Delete the
StaffRole
record byStaffRole.id
.You must also update the
order
value for whatever role had itsorder
value greater than the role that will be deleted. Example: