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

You are viewing revision #1 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.

next (#2) »

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 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(),'-',''))... 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.
    
    
  3. 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
4. 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: 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

Related Articles