We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. Database

Phalcon PHP - Converting from HEX strings to Binary(16) and back during model save/read/hydration

I'm using Phalcon PHP 3.0.4 and I'm not sure how to achieve this.

Given this MySQL MariaDB table schema:

CREATE TABLE `players` (
    `Id` BINARY(16) NOT NULL,
    `Email` VARCHAR(255) NOT NULL,
    PRIMARY KEY (`Id`),
    INDEX `Email` (`Email`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

How would I achieve the following (using a Phalcon PHP preferred approach):

  1. Take a regular HEX string either with or without dashes (example '15E077BCFD8511E683A600FF8A8C8699') in PHP and convert it to Binary(16) just before Phalcon saves the 'Id' property to the 'Id' column?

  2. Take the value of the 'Id' column and convert it back to a HEX string from Binary(16) so the result would be a Hex string either with or without dashes?

I followed this article for storing GUIDs as Primary keys in MariaDB and then I realized that the Hex/Binary conversion really needs to happen in PHP so Phalcon won't store binary data in it's model objects in memory.

https://mariadb.com/kb/en/mariadb/guiduuid-performance/

At the end of the day this 'Id' column value is passed around in the HTTP request and responses to be used in other systems. Therefore passing around binary GUIDs is not going to work for me, hence why I need to convert it between strings and binary just before it enters and leaves the DB.



145.0k
edited Mar '17

I don't think it's possible. Phalcon can't edit primiar key values. Can't you add some trigger to mariadb? Well i mean afterFetch should work, but for beforeSave you need surely to use trigger.



79.0k
edited Mar '17

Binary or Hex conversion is for sure possible in PHP. I did it years ago w/o any framework.

To tell you the truth - I'm not familiar with GUID's as the primary key in MySQL/MariaDB, but if you're sure that's the think you need, you can try with HEX and UNHEX built it functions: https://mariadb.com/kb/en/mariadb/unhex/



2.9k
in reply to Wojciech Ślawski
edited Mar '17

Thanks buddy! I was stupid and figured the DB trigger will not work even though I didn't try it.

On the MariaDB side: I've got a BEFORE INSERT trigger setup to generate the GUID and I updated my table schema so the Id column defaults to 0 (to prevent INSERTS from failing).

On the Phalcon side: Phalcon is successfully inserting the records, MariaDB is successfully autogenerating a GUID and converting it to Binary for the Id column. However, after Phalcon saves the database row Phalcon doesn't retrieve the Id binary column value that the trigger created.

Is this because the trigger is executing asynchronously therefore after the trigger is done executing Phalcon has already finished its INSERT and SELECT PRIMARY KEY process?

I created a second copy of the table, this time with a BIGINT auto_increment PRIMARY KEY column instead of Binary and Phalcon does return the auto increment integer value which tells me it could be the trigger that's the problem. I guess this means I need to select the model record after insert to get the binary value?

I don't think it's possible. Phalcon can't edit primiar key values. Can't you add some trigger to mariadb? Well i mean afterFetch should work, but for beforeSave you need surely to use trigger.

Here's the new Players table schema.

CREATE TABLE IF NOT EXISTS players ( Id binary(16) NOT NULL DEFAULT '0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0', Email varchar(255) NOT NULL, PRIMARY KEY (Id), KEY Email (Email) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

and the Players table trigger for creating a new primary key GUID.

DELIMITER // CREATE TRIGGER players_before_insert BEFORE INSERT ON players FOR EACH ROW BEGIN SET NEW.Id = UNHEX(REPLACE(UUID(),'-','')); END// DELIMITER ;

EDIT :: I came across this article about using $model->refresh(), but this doesn't work because the primary key Id value isn't in Phalcon's model so Phalcon doesn't know which model record to re-fetch.

https://forum.phalcon.io/discussion/8397/return-primary-key-after-createsave



2.9k
Accepted
answer

Ok I figured this out.

All in all the trigger worked perfectly, it just requires the model to be refetched by another identifyable indexed column, like an Email or Username column.

I didn't take that approach because I didn't want an additional SELECT query to retrieve the new PRIMARY KEY value. Instead I took the following approach.

I deleted the database trigger I created and using PHP I generate a GUID and convert it to binary from hex using PHP's hex2bin() function. This way Phalcon has the binary primary key value before it saves the record to the database. This reduces the extra model DB call to retrieve the primary key value from the DB (limitating of relying on a DB trigger to create the primary key value).

Last night this appeared to not be working because I was getting an error in my action method Unexpected 'o' and I thought this error was produced by the binary data. It appears that error was produced because I was calling $this->response->setJsonContent() without a parameter.

The final working PHP code (without the precense of a DB trigger to create the primary key) is below:

function getGUID(){
    mt_srand((double)microtime()*10000);//optional for php 4.2.0 and up.
    $charid = strtoupper(md5(uniqid(rand(), true)));
    $hyphen = chr(45);// "-"
    $uuid = substr($charid, 0, 8).$hyphen
        .substr($charid, 8, 4).$hyphen
        .substr($charid,12, 4).$hyphen
        .substr($charid,16, 4).$hyphen
        .substr($charid,20,12);
    return $uuid;
}

$guid = getGUID();
var_dump($guid);
$player2 = new Players([
    "Id" => hex2bin(str_replace('-', '', $guid)),
    "Email" => "[email protected]"
]);
var_dump($player2->save());
var_dump(bin2hex($player2->getId()));

The result of executing the code above is as expected:

string(36) "C03F5B38-FF32-8D32-EC42-797A764E8684"
bool(true)
string(32) "c03f5b38ff328d32ec42797a764e8684"

The GUID value is stored in MariaDB correctly, too.