SQL: Ticket state: Use native sort of enumerations instead of separate sort column

[pegro]

tbl_ticket_state has a column sort to store the order of the ticket states.

PostgreSQL supports native ordering of enumeration types and relies on the order of the values when the type was created. So this could replace the sort column for ordering ticket states.

Note: To replace a enum type which is in use, the following migration is proposed:

1. Introduce the new enum type with a temporary name
2. add temporary columns to all related tables
3. copy the values from the old column
4. remove the old columns
5. remove the now unused enum type
6. rename the enum type back to the old name
7. rename the columns back to their old name

[a-tze]

Note: This can AFAIK not replace the sort column completely, because there is no native support for (ordered) enums in PHP. To have the states ordered there, the sort is still needed. Nevertheless, it would reduce lots of things in the SQL part.

[jjeising]

because there is no native support for (ordered) enums in PHP.

Where would we need this? I can't finde any reference to 'sort'. There is

https://github.com/crs-tools/tracker/blob/master/src/Application/Model/Project.php#L78-L79

but this is only a query. As we only use strings in PHP, we don't do additional sorting there.

[a-tze]

Where would we need this? I can't finde any reference to 'sort'.

There is an order in every dropdown where a state can be selected (Ticket edit / mass edit). Additionally, in the "States" view of project settings the states are in the correct order as well as in the dropdown for the dependee ticket trigger state.

So e.g. it is used additionally here: https://github.com/crs-tools/tracker/blob/master/src/Application/Controller/Projects.php#L156

The other mentioned functionalities seem to derive correct order from the $States property of a project model instance.

[jjeising]

There is an order in every dropdown where a state can be selected (Ticket edit / mass edit).

We need ordering, sure, but ordering in PostgreSQL would still work, doesn't it?

What I was trying to say: We don't need support for ordered enums in PHP, as we don't use enums anyway. Arrays in PHP are ordered maps, so when we get ordered results from PostgreSQL this would be enough.

[a-tze]

Correct, my concern was just to get the order from the database into PHP. Since most code seems to rely on a central logic for this, it might be a lot easier than I initially thought.