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

[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));

[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.

[vip9]

When using (in MySQL) binary uuids as id, the /records/posts returns base64 encoded id, e.g.
{"records":[{"id":"1H4OCAYxTRqSYdJSxhFELw==","name":"test"}, {...}] and that's fine.
How to get/delete/patch that one record? What form it should have to pass as an id to request?
/records/posts/1H4OCAYxTRqSYdJSxhFELw== was my first try, but it does not work.

[mevdschee]

@vip9 You cannot achieve what you want without modification to the PHP code.

If you need binary UUID support from your database I suggest you use PostgreSQL.

See: https://stackoverflow.com/questions/43056220/store-uuid-v4-in-mysql

NB: Did you know about the ULID standard? See: https://github.com/ulid/spec