Difference between #3 and #4 of
UUID instead of an auto-increment integer for ID with Active Record

Revision #4 has been created by samdark on Nov 25, 2019, 10:33:30 PM with the memo:

Formatting fix
« previous (#3) next (#5) »

Changes

Title unchanged

UUID instead of an auto-increment integer for ID with Active Record

Category unchanged

How-tos

Yii version unchanged

2.0

Tags unchanged

mysql,active record,REST,UUID

Content changed

I have a dream ... I am happy to join with you today in what will go down in history as the greatest demonstration of ... bad design of Active Record. I have an API built with a RestESTful extension over Active Record, and some endpoints provide PUT methods to upload files. By a REST design we create an entity with `POST /video` first, and then upload a video file with `PUT /video/{id}/data`. How do we get the `{id}`? The essential solutuion is UUID generated by a client. It allows API application to be stateless and scale it, use master-master replication for databases and feel yourself a modern guy. If you have Poistgres - lucky you, feel free to use the built-in UUID data type and close this article.
With MySQL the essential solution is [insert into users values(unhex(replace(uuid(),'-',''))...](https://mysqlserverteam.com/storing-uuid-values-in-mysql-tables/)
MySQL team recommends updating our INSERT queries. With Active Record it is not really possible.
[...]
If you design the application from ground up, you can use defferent fields for a binary and text representation of UUID, and reference them in different parts of an application, but I am bound to the legacy code.

Adding
`getId()/`/`setId()` won't help - data comes from a client in JSON and fills the model object with a `setAttributes()` call avoiding generic magic methods. Here's the hack:
 
1. add a `private $idText;` property 2. add two filters ```php ['id','match', 'pattern'=>'/^[0-9a-f]{8}-[0-9a-f]{4}-[1-5][0-9a-f]{3}-[89ab][0-9a-f]{3}-[0-9a-f]{12}$/i'], // convert UUID from text value to binary and store the text value in a private variable // this is a workaround for lack of mapping in active record ['id','filter','skipOnError'=>true, 'filter'=>function($uuid){         $this->idText = $uuid;         return pack("H*", str_replace('-', '', $uuid));        }], ```
 
These filters will validate input, prepare UUID to be written in a binary format and keep the text form for output. 3. Add getters ```php
public function __get($name)
{
[...]
From the other hand, the first valiator calls `$model->id` triggering the getter before the UUID is saved to the private property so I need to serve the value from user input.

It is strange to mutate data in a validator, but I found this is the only solution. I belive I shouldn't use
`beforeSave()` callback to set the binary value for SQL, and return the text value back in `afterSave()`, supporting this code later will be a hell. So, now you can go the generic mysqlMySQL way
 
4. add a virtual column ```sql
ALTER TABLE t1 ADD id_text varchar(36) generated always as
(insert(
[...]
24,0,'-')
) virtual;
 
 
 
```
5 0
4 followers
Viewed: 35 365 times
Version: 2.0
Category: How-tos
Written by: grigori
Last updated by: samdark
Created on: Nov 25, 2019
Last updated: a year ago
Update Article

Revisions

View all history