Ordering

The goal is to enable to completely avoid putting manual numbers in exos files and to let Delibay understand and keep track of the order on a "top to bottom" style.

The list of skills at left in the course details, should be in the same order as defined in skills.dy. The list of exos inside each skill should be the same as the alphabetical ordering of exos files in this skill and top to bottom order inside those files. As dy parse parse file in the correct order and extract exos from top to bottom, this is already working for the first course deploy. The challenge starts when we start to update exos...

We want a kind of hint that would allow an ordering before display.

I made a little research on the problem that should be common to a lot of apps:

• https://softwareengineering.stackexchange.com/questions/304593/how-to-store-ordered-information-in-a-relational-database
• https://stackoverflow.com/questions/2826829/whats-the-best-way-to-store-sort-order-in-sql
• Textual ranking: https://confluence.atlassian.com/adminjiraserver/managing-lexorank-938847803.html#:~:text=LexoRank%20is%20ranking%20system%20that,key%20areas%20of%20LexoRank%20administration

The conclusion is that there are various values possible for an order attribute in exos and skills:

1. An integer value 1, 2, 3, ... but the issue that we will need to shift all values when something is inserted in the middle
2. A float value 1.0, 1.5, 2.5, 3.0, ... but floating numbers don't have an infinite number of decimals and dividing per 2 several times quickly reach a lot of decimals, so same issue as before. In addition, it's harder to reason about. I just don't know how much decimal to the DECIMAL type in MySQL I should allocate.
3. An integer value with a lot of space in between: 100, 200, 300. Same issue as before but easier to reason about and probably less "conflicts" that would imply shifting another or more value.
4. A textual identifier: it seems harder to me to generate and I don't really have time to dig into this

We could always shift order hint of next values when inserting in the middle but it would imply that all these elements are updated and need to be synced down by all clients again. If there is 40 exos in a skill, you would add one at the start and 40 exos would be synced down again, it seems just a loss of bandwidth to me even if it could work in terms of performance. I try to design everything to be light and with little traffic.

#Chosen solution

1. Space between values is 100
2. We use an integer field order on exos and skills tables
   1. It would consist of an unsigned 2 bytes integer (MySQL unsigned SMALLINT), to limit size
   2. It would allow: 65535 values. The edge case would be to add more than 655 exos (65536/100) one after the other in a single skill, the insert would probably fail so nothing bad. Adding so much exos to a single skill is a nonsense. And if exos are inserted in the middle, the limit become ~65500 which is again non realistic.
3. Minimize the number of order field update to minimize unnecessary bandwidth
4. Order values start at 1, not 0

It already exists some Composer packages like eloquent-sequence that could do the job but they don't minimize the number of updates so I need to develop my own solution...

#Example of algo behavior

//Array of Lid (shorter version for demo) -> order
// including all elements of a given skill
$dbOrder = [
	"a" => 100,
	"b" => 200,
	"c" => 300,
	"d" => 400,
];

// Exos in an exo file for the given skill
// it may not include all exos of the skills
$parserOrder = [
	'a' => 0,
	'w' => 0,	//2 new elements inserted after "a"
	'x' => 0,
	'b' => 0,
	'c' => 0,
	'y' => 0,	//one single element inserted after "c"
	'd' => 0,
	'z' => 0,	//one new element at the end,
];

//Resulting array should be
$result = [
	"a" => 100,
	"w" => 133,	//(200-100)/3 + 100
	"x" => 166, //2*(200-100)/3 + 100
	"b" => 200,
	"c" => 300,
	"y" => 350,	// (400-300)/2 + 300
	"d" => 400,
	"z" => 500,	// previous + space: 400 + 100
];

This was just the start of a brainstorm, this is now implemented in Util::getNewOrderAttribution() and the associated tests are in the UtilTest.