store UUIDs as optimized binary(16) instead of varchar(32)

[jeromecc]

space

switch to binary(16) for space, it is particularly relevant for databases using utf8mb4 (130 bytes instead of 16 bytes when dashes removed and UNHEX(value)

performance

If UUIDs of type 1 were issued by the server, we could rearrange the 4 hex digits groups to improve index performance and use UUIDs as primary keys for the user and patient tables 1. Current version of UUID used is 4 (totally random). Should we use type 1, created by the database, to improve indexing? For this performance improvement, UUIDs should be generated by the same machine (the MySQL server). We could do this in the future:

• when MySQL 8 becomes available in Debian/Ubuntu: it supports UUID_TO_BIN/BIN_TO_UUID
• when SQLite is discontinued

[jeromecc]

We can move to type 1 UUID immediately without rewrite anything else than 1 function and without breaking anything.

[jeromecc]

In fact Qt cannot easily create UUIDs of type 1. Let's keep type 4 until we get rid of SQLite, then we will use the RDBMS (My/Maria) to generate type 1 UUIDs.

[jeromecc]

fhio v0.11 will use uuids stored binary(16) in patients database (PATIENT_IDENTITY table). To simplify database update, other changes will be implemented once SQLite is discontinued.