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

You are viewing revision #4 of this wiki article.
This version may not be up to date with the latest version.
You may want to view the differences to the latest version or see the changes made in this revision.

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

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 RESTful 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 Postgres - 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(),'-',''))... MySQL team recommends updating our INSERT queries. With Active Record it is not really possible. For fetching UUIDs it recommends adding a virtual column - this can be used.

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

  1. Add getters
public function __get($name)
{
    return ($name === 'id') ? $this->getId() : parent::__get($name);
}

/**
 * Return UUID in a textual representation
 */
public function getId(): string
{
    if ($this->idText === NULL && $this->getIsNewRecord()){
        //the filter did not convert ID to binary yet, return the data from input
        return $this->getAttribute('id');
    }
    //ID is converted
    return $this->idText ?? $this->getAttribute('id_text');
}

Active Record does not call the getter method if attributes contain the property. It should not be this way, so I return the default component behavior and make ID returned the right way. 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 MySQL way

  1. add a virtual column
ALTER TABLE t1 ADD id_text varchar(36) generated always as
 (insert(
    insert(
      insert(
        insert(hex(id_bin),9,0,'-'),
        14,0,'-'),
      19,0,'-'),
    24,0,'-')
 ) virtual;
5 0
4 followers
Viewed: 55 626 times
Version: 2.0
Category: How-tos
Written by: grigori
Last updated by: samdark
Created on: Nov 25, 2019
Last updated: 3 years ago
Update Article

Revisions

View all history

Related Articles