Support for optimized UUID storage in the API

[nik2208]

this article seems to give a valid and performant option to use uuids as primary keys.
So I would let mysql generate uuids on post, then convert bin2uuid/uuid2bin respectively in customization.afterHandler for get requests and customization.beforeHandler for put/patch/delete.

Does it seem to be reasonable?

How would I implement it in beforeHandler/afterHandler functions?

[mevdschee]

this article seems to give a valid and performant option to use uuids as primary keys.

Okay, I've read the article and I'm not convinced at all.

Does it seem to be reasonable?

Yeah you could try to implement UUID support in the PHP-CRUD-API, but I would skip the binary UUID. There is no need for UTF-8: UUIDs consist of 7 bit ASCII characters and the characters are always 1 byte, therefor the ascii_general_ci or ascii_bin collations are a good choice (depending on whether or not you want case insensitivity). A char(36) with that collation will not take 146 bytes in the index (but 36).

How would I implement it in beforeHandler/afterHandler functions?

Well, I'm not sure, what did you try so far? Did you consider generating the UUID(v4) client side? See also this comment

[pottertech]

There are Unique UIDs (UUID), Global UIDs (GUID), and Sequential UIDs (SGUID). Each is better than the next and should be considered when designing your database and where the UIDs are being generated and how many machines are contributing data to the database.

[mevdschee]

Each is better than the next and should be considered when designing your database

@pottertech I have doubts about that. I thought UUID vs GUID is more of a Microsoft vs RFC thing, see here. Also I believe that Sequential UIDs have the same problem as auto-increment fields they need a (centralized) oracle to produce them.

Let's talk in this issue about (random) RFC4122 version 4 UUIDs.

[nik2208]

@mevdschee, the matter to me is how to store uuids more than where to generate them. If what the article says is right and storing the uuid as bin(16) is that much better option than storing it as varchar, there would be the problem of converting it from binary to char in get requests and viceversa in others, and I would do that in beforeHandler and afterHandler functions.
But my php knowledge doesn't allow me to write those functions properly (I don't know how objects are nested)

[nik2208]

@mevdschee so doesn't matter (at the moment) whether I auto generate a UUIDv1 in mysql or a UUIDv4 client side, I would like to eventually get in response a masked json showing the string version of them.
Does it sound?

[mevdschee]

If what the article says is right and storing the uuid as bin(16) is that much better option than storing it as varchar

I doubt it. The difference is a 16 byte vs 36 byte in index size (and not 16 vs 144 byte) and you are introducing complexity because of a hearsay performance improvement. I'm not a fan of such "optimizations".

I would like to eventually get in response a masked json showing the string version of them.

You introduce a lot of computation and complexity while you could just generate uuidv4 client side and store it as-is in the right collation (as an optimization) in a char(36) field. I'm skeptical about the advantage of the binary storage. If you want an ultra efficient API, then there are many points of improvement: PHP is not the fastest language, the number of roundtrips and http(s) overhead cause unnecessary load in applications, etc.. etc..

Does it sound?

My advice is that if you need an efficient random primary key, choose BIGINT and generate it client side.

[nik2208]

ok I'll work on it and do some test.
For testing and for potential other purposes, what would be the approach to transform data using the customization handler?
I found this in the issue section (used in afterHandler):

$requestData = json_decode($response->getBody()->getContents());
            if(isset($requestData->id)) {
                ...
            }
return ResponseFactory::fromObject($response->getStatusCode(), $responseData);

what would be the equivalent for beforeHandler function?
would simply be $request->getBody()->getContents and then RequestFactory::?

I don't know how to get output so I'm struggling trying to debug it.

[mevdschee]

would simply be $request->getBody()->getContents and then RequestFactory::?

Yes, but not necessarily with RequestFactory, you can also return $request->withBody($newBody), see:

#599 (comment)

I don't know how to get output so I'm struggling trying to debug it.

try: ob_end_flush() and die(var_export($var,true));

[pottertech]

Each is better than the next and should be considered when designing your database

@pottertech I have doubts about that. I thought UUID vs GUID is more of a Microsoft vs RFC thing, see here. Also I believe that Sequential UIDs have the same problem as auto-increment fields they need a (centralized) oracle to produce them.

Let's talk in this issue about (random) RFC4122 version 4 UUIDs.

All of my comments are more generic database related terms and not specific to the operating system. For me I would make sure for any UIDs to consider storage size, speed, sorting. Not all the different UIDs give you everything.

[mevdschee]

Does it sound?

Well.. I don't think your approach is feasible in Middleware, as you also need to modify filter and comparison behavior (I did a short hacking session to modify the JsonMiddleware, but I failed). Either this should be handled the way binary fields are handled (binary fields are represented as base64 strings) or it should be left to the client (as I already suggested). Supporting UUID as char(36) seems not that bad to me, nor using random unsigned bigint as primary key. I'm not convinced we should pursue this feature.

[mevdschee]

this should be handled the way binary fields are handled

This would require a way to indentify columns that have a UUID 'type'. This could be any unsigned bigint or any binary 16 char field, but that seems a bit 'magic' to me (as it is with boolean types as well). We would then have to make this configurable to overcome that obstacle. Anyway.. I think it is a lot of work for (at best) a performance improvement, as the functionality is already there.