Handling Uuid Pk Column In Yii

Yii 1.1 General Discussion
1

I’m using UUID’s as PK in my tables. They’re stored in a BINARY(16) MySQL column. I find that they’re being mapped to string type in YII. The CRUD code I generate breaks down though, because these binary column types are being HTML encoded in the views. Example:




    <?php echo 

        CHtml::link(CHtml::encode($data->usr_uuid), /* This is my binary uuid field */

        array('view', 'id'=>$data->usr_uuid)); ?>

To work around this problem, I overrode


afterFind()

and


beforeSave()

in my model where I convert the values to/from hex using


bin2hex

and


hex2bin

respectively. See this for more details.

This takes care of the view problems.

However, now the search on PK when accessing a url of the form:

myhost.com/mysite/user/ec12ef8ebf90460487abd77b3f534404

results in


User::loadModel($id)

being called which in turn calls:


User::model()->findByPk($id);

This doesn’t work since the SQL is being generated (on account of it being mapped to php string type) is




select ... where usr_uuid='EC12EF8EBF90460487ABD77B3F534404'

What would have worked is if I could, for these uuid fields change the condition to:




select ... where usr_uuid=unhex('EC12EF8EBF90460487ABD77B3F534404')

I was wondering how I take care of this problem cleanly. I see one possiblity - extend


CMysqlColumnSchema

and override the necessary methods to special case and handle binary(16) columns as uuid type.

This doesn’t seem neat as there’s no support for uuid natively either in php (where it is treated as string) or in mysql (where I have it as binary(16) column).

Does anyone have any recommendation?

2

Just extent base class and override findByPk.

3

Perhaps beforeFind() and afterFind() as well? After all, there has to be a place where the UUIDs are being packed and unpacked.

4

I’d rather use getUuid() and setUuid for creating virtual attribute, so you can use it as usual (this goes to model or extended ActiveRecord):




public function getUuid()

{

    return your_unpack_function($this->usr_uuid);

}

public function setUuid($val)

{

    $this->usr_uuid = your_pack_function($val);

}

echo $model->uuid;

$model->uuid = 'plain_text_uuid';

$model->save();

Don’t forget to modify your validation rules and all.

5

Hm, good point.

6

I like this solution and have implemented it. Two questions:

  • I notice that you have the accessor/mutator as getUuid/setUuid. While you’re accessing the attribute as ‘model->uuid’. Is this a typo? Sorry I’m new to php as well.

  • If I use this method everything works fine, except if I have


enableParamLogging' => true

in the ‘db’ config (main.php). It gives an error - “htmlspecialchars(): Invalid multibyte sequence in argument” while trying to log the sql statements with the binary ‘usr_uuid’ columns data. How can I avoid this?

7

Answering for ORey:

That’s fine. Yii relies heavily on a technique called “virtual attributes.”

There is no way to fix this unless you control a bit how your UUIDs are being generated: Some binary numbers match ASCII control characters that cannot be represented in SGML-based markup (that is HTML, XML, XHTML and so forth).