A unversal way to work with UUIDs

[samdark]

Proposed new feature or change

Currently you can't work universally with UUIDs:

<?php

declare(strict_types=1);

namespace App\Migration;

use Yiisoft\Db\Migration\MigrationBuilder;
use Yiisoft\Db\Migration\RevertibleMigrationInterface;

final class M251102141707Page implements RevertibleMigrationInterface
{
    public function up(MigrationBuilder $b): void
    {
        $column = $b->columnBuilder();

        $b->createTable('page', [
            'id' => $column::uuidPrimaryKey(),
            'title' => $column::string()->notNull(),
            'slug' => $column::string()->notNull()->unique(),
            'text' => $column::text()->notNull(),
            'created_at' => $column::dateTime(),
            'updated_at' => $column::dateTime(),
        ]);
    }

    public function down(MigrationBuilder $b): void
    {
        $b->dropTable('page');
    }
}

Creates a proper column for PostgreSQL and MySQL but then if you want to insert UUID, there's no universal way to do so:

$this->connection->createCommand()->insert('{{%page}}', [
            'id' => Uuid::uuid7()->getBytes(), // MySQL
            // 'id' => Uuid::uuid7()->toString(), // PostgreSQL
            'title' => 'title',
            'slug' => 'slug',
            'text' => 'text',
            'created_at' => new DateTimeImmutable(),
            'updated_at' => new DateTimeImmutable(),
        ])->execute();

MySQL accepts bytes and errors with strings. PostgreSQL accepts strings and errors with bytes.

[samdark]

yiisoft/docs#324

[samdark]

https://forum.yiiframework.com/t/possible-error-in-working-with-databases-yii3-guide/138932

[Tigrov]

One more reason why UuidColumn class is required. At least in PostgreSQL it can replace bytes to string.

[samdark]

@Tigrov I think it should be an interface and a factory. Could it be achieved without breaking backwards compatibility? If we stick with UUID v7, we can even provide a simple implementation like the following:

interface UuidColumnInterface
{
    public function asString(): string;
    public function asBinary(): string;
}

final readonly class UuidColumn extends UuidColumnInterface
{
    private string $value;

    public function __construct(?string $value = null)
    {
         if ($value === null) {
            $value = random_bytes(16);
            $timestamp = intval(microtime(true) * 1000);

            $value[0] = chr(($timestamp >> 40) & 0xFF);
            $value[1] = chr(($timestamp >> 32) & 0xFF);
            $value[2] = chr(($timestamp >> 24) & 0xFF);
            $value[3] = chr(($timestamp >> 16) & 0xFF);
            $value[4] = chr(($timestamp >> 8) & 0xFF);
            $value[5] = chr($timestamp & 0xFF);

            $value[6] = chr((ord($value[6]) & 0x0F) | 0x70);
            $value[8] = chr((ord($value[8]) & 0x3F) | 0x80);

            $this->value = $value;
        }    
    }

    public function asString(): string
    {
        return bin2hex($this->value);
    }

    public function asBinary(): string
    {
        return $this->value;
    }
}

[Tigrov]

It should follow to ColumnInterface. Using custom type casting, user can define what he needs (bytes or strings), but the default implementation should return one type, I'd prefer string when retrieving from DB.
The difficulty is that we cannot determine the uuid type in all DBMSs. E.g. MySQL has binary(16) for this.
As an idea we can parse default value, currently it is (unhex(replace(uuid(),'-',''))) for uuid.