roles: manage role priority in each generation

[BrunoRosendo]

Similarly to Discord, we want to keep an order of roles (priority) to know which are higher in the hierarchy. This is useful, for example, in the members' page where we need to know which roles are displayed as sections (direção, member, recruit, etc.).

In #88 we tried to implement this simply using @OrderColumn as we can see below. This would automatically keep the insertion order of roles in the generations and accounts.

// In Generation.kt

@OneToMany(cascade = [CascadeType.ALL], fetch = FetchType.EAGER, mappedBy = "generation")
@OrderColumn
@JsonManagedReference
@field:NoDuplicateRoles
val roles: MutableList<@Valid Role> = mutableListOf()

// In Account.kt

@ManyToMany
@JoinTable
@OrderColumn
@JsonIgnore // TODO: Decide if we want to return roles (or IDs) by default
val roles: MutableList<@Valid Role> = mutableListOf(),

// In Activity.kt

@OneToMany(cascade = [CascadeType.ALL], mappedBy = "activity")
@OrderColumn
@JsonIgnore // TODO: Decide if we want to return perRoles (or IDs) by default
open val associatedRoles: MutableList<@Valid PerActivityRole> = mutableListOf(),

However, we noticed some problems with this approach in #145 and the annotation was thus removed, namely:

• The annotation works poorly with role deletions. As documented here, The persistence provider maintains a contiguous (non-sparse) ordering of the values of the order column when updating the association or element collection. After a role deletion, the values of the order column are not updated and become sparse (e.g. `0-1-2 becomes 0-2 after the role with order 1 is deleted). This would result in a list of roles containing null values that would result in unexpected errors somewhere else.
  More StackOverflow threads with this problem here and here.
• This approach works well when creating a new generation with a list of roles but this is not the case when we want to create a new single role. This would bring questions such as:
  • How does the frontend send the role's priority?
  • How can we update the order column? Probably by accessing the DB directly...

@LuisDuarte1 and I discussed possible solutions for this and we'd like you to give your own thoughts and ideas:

• Maintain the @OrderColumn logic and create an entity listener to update the order columns upon role deletion. This is the solution proposed in one of the stack overflow threads but we found it unviable since it does not solve problem 2 and would likely require direct access to the DB.
• Create a new field order/priority in Role and PerActivityRole and control it in the application (e.g. when creating generations or new roles). We could then use the @OrderBy annotation to automatically sort the lists when fetching from the generation/activity entities. We would also need to change the way we handle roles in the account entity since the order of the roles is relative to its generation so we can't use @OrderBy. I'll leave some suggestions:
  • Make the account's list of roles private (or rename it to unorderedRoles if it's useful as public) and provide public methods to retrieve the ordered roles associated with a given generation.
  • Find a way to automatically order the roles by generation and by order/priority. I don't know how to do this but there's most likely a way.
• Don't use any Spring annotations and handle everything in the application. I believe this is only worth it if the previous solution doesn't work for some reason.

In sum, this issue is comprised of the following tasks:

• Understand the problem well and design a solution
• Implement a solution to handle role priority for each generation in a persistent way
• Implement a solution to handle role priority for the accounts
• Update all existing code (services, controllers, tests, etc.) to reflect the changes made above, including adding new roles in some order
• Test and document the functionalities accordingly