Skip to content

Commit 58ccf6f

Browse files
darkdefdarkdef
authored
UUID PK support (#595)
* UUID PK support * fix * styleci * move to dedicated class * fixes * fixes for mariadb * test fix * test fix * add psalm fix * mysql 5.7 fix * add tests for UuidHelper * styleci fix
1 parent 6eb289c commit 58ccf6f

7 files changed

Lines changed: 315 additions & 5 deletions

File tree

‎src/Helper/UuidHelper.php‎

Lines changed: 57 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,57 @@
1+
<?php
2+
3+
declare(strict_types=1);
4+
5+
namespace Yiisoft\Db\Helper;
6+
7+
use Yiisoft\Db\Exception\InvalidArgumentException;
8+
9+
use function bin2hex;
10+
use function hex2bin;
11+
use function preg_match;
12+
use function str_replace;
13+
14+
final class UuidHelper
15+
{
16+
public static function toUuid(string $blobString): string
17+
{
18+
if (self::isValidUuid($blobString)) {
19+
return $blobString;
20+
}
21+
22+
if (strlen($blobString) === 16) {
23+
$hex = bin2hex($blobString);
24+
} elseif (strlen($blobString) === 32 && self::isValidHexUuid($blobString)) {
25+
$hex = $blobString;
26+
} else {
27+
throw new InvalidArgumentException('Length of source data is should be 16 or 32 bytes.');
28+
}
29+
30+
return
31+
substr($hex, 0, 8) . '-' .
32+
substr($hex, 8, 4) . '-' .
33+
substr($hex, 12, 4) . '-' .
34+
substr($hex, 16, 4) . '-' .
35+
substr($hex, 20)
36+
;
37+
}
38+
39+
public static function isValidUuid(string $uuidString): bool
40+
{
41+
return (bool) preg_match('/^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$/i', $uuidString);
42+
}
43+
44+
public static function isValidHexUuid(string $uuidString): bool
45+
{
46+
return (bool) preg_match('/^[0-9a-f]{32}$/i', $uuidString);
47+
}
48+
49+
public static function uuidToBlob(string $uuidString): string
50+
{
51+
if (!self::isValidUuid($uuidString)) {
52+
throw new InvalidArgumentException('Incorrect UUID.');
53+
}
54+
55+
return (string) hex2bin(str_replace('-', '', $uuidString));
56+
}
57+
}

‎src/Schema/Builder/AbstractColumn.php‎

Lines changed: 10 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -39,6 +39,8 @@ abstract class AbstractColumn implements ColumnInterface
3939
public const CATEGORY_NUMERIC = 'numeric';
4040
public const CATEGORY_TIME = 'time';
4141
public const CATEGORY_OTHER = 'other';
42+
public const CATEGORY_UUID = 'uuid';
43+
public const CATEGORY_UUID_PK = 'uuid_pk';
4244

4345
protected bool|null $isNotNull = null;
4446
protected bool $isUnique = false;
@@ -72,6 +74,8 @@ abstract class AbstractColumn implements ColumnInterface
7274
SchemaInterface::TYPE_BINARY => self::CATEGORY_OTHER,
7375
SchemaInterface::TYPE_BOOLEAN => self::CATEGORY_NUMERIC,
7476
SchemaInterface::TYPE_MONEY => self::CATEGORY_NUMERIC,
77+
SchemaInterface::TYPE_UUID => self::CATEGORY_UUID,
78+
SchemaInterface::TYPE_UUID_PK => self::CATEGORY_UUID_PK,
7579
];
7680

7781
/**
@@ -159,11 +163,12 @@ public function append(string $sql): static
159163

160164
public function asString(): string
161165
{
162-
if ($this->getTypeCategory() === self::CATEGORY_PK) {
163-
$format = '{type}{check}{comment}{append}';
164-
} else {
165-
$format = $this->format;
166-
}
166+
$format = match ($this->getTypeCategory()) {
167+
self::CATEGORY_PK => '{type}{check}{comment}{append}',
168+
self::CATEGORY_UUID => '{type}{notnull}{unique}{default}{check}{comment}{append}',
169+
self::CATEGORY_UUID_PK => '{type}{notnull}{default}{check}{comment}{append}',
170+
default => $this->format,
171+
};
167172

168173
return $this->buildCompleteString($format);
169174
}

‎src/Schema/SchemaInterface.php‎

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -47,10 +47,14 @@ interface SchemaInterface extends ConstraintSchemaInterface
4747
public const INDEX_NONCLUSTERED = 'NONCLUSTERED';
4848
/* Oracle */
4949
public const INDEX_BITMAP = 'BITMAP';
50+
/* DB Types */
5051
public const TYPE_PK = 'pk';
5152
public const TYPE_UPK = 'upk';
5253
public const TYPE_BIGPK = 'bigpk';
5354
public const TYPE_UBIGPK = 'ubigpk';
55+
public const TYPE_UUID_PK = 'uuid_pk';
56+
public const TYPE_UUID_PK_SEQ = 'uuid_pk_seq';
57+
public const TYPE_UUID = 'uuid';
5458
public const TYPE_CHAR = 'char';
5559
public const TYPE_STRING = 'string';
5660
public const TYPE_TEXT = 'text';
@@ -70,6 +74,7 @@ interface SchemaInterface extends ConstraintSchemaInterface
7074
public const TYPE_MONEY = 'money';
7175
public const TYPE_JSON = 'json';
7276
public const TYPE_JSONB = 'jsonb';
77+
/* PHP Types */
7378
public const PHP_TYPE_INTEGER = 'integer';
7479
public const PHP_TYPE_STRING = 'string';
7580
public const PHP_TYPE_BOOLEAN = 'boolean';

‎tests/Common/CommonColumnSchemaBuilderTest.php‎

Lines changed: 99 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -5,6 +5,10 @@
55
namespace Yiisoft\Db\Tests\Common;
66

77
use PHPUnit\Framework\TestCase;
8+
use Yiisoft\Db\Expression\Expression;
9+
use Yiisoft\Db\Helper\UuidHelper;
10+
use Yiisoft\Db\Query\Query;
11+
use Yiisoft\Db\Schema\SchemaInterface;
812
use Yiisoft\Db\Tests\Support\TestTrait;
913

1014
use function array_shift;
@@ -22,6 +26,58 @@ public function testCustomTypes(string $expected, string $type, int|null $length
2226
$this->checkBuildString($expected, $type, $length, $calls);
2327
}
2428

29+
/**
30+
* @dataProvider \Yiisoft\Db\Tests\Provider\ColumnSchemaBuilderProvider::createColumnTypes
31+
*/
32+
public function testCreateColumnTypes(string $expected, string $type, int|null $length, array $calls): void
33+
{
34+
$this->checkCreateColumn($expected, $type, $length, $calls);
35+
}
36+
37+
public function testUuid(): void
38+
{
39+
$db = $this->getConnection();
40+
$schema = $db->getSchema();
41+
42+
$tableName = '{{%column_schema_builder_types}}';
43+
if ($db->getTableSchema($tableName, true)) {
44+
$db->createCommand()->dropTable($tableName)->execute();
45+
}
46+
47+
$db->createCommand()->createTable($tableName, [
48+
'uuid_pk' => $schema->createColumn(SchemaInterface::TYPE_UUID_PK),
49+
'int_col' => $schema->createColumn(SchemaInterface::TYPE_INTEGER),
50+
])->execute();
51+
$tableSchema = $db->getTableSchema($tableName, true);
52+
$this->assertNotNull($tableSchema);
53+
54+
$uuidValue = $uuidSource = '738146be-87b1-49f2-9913-36142fb6fcbe';
55+
56+
if ($db->getName() === 'oci') {
57+
$uuidValue = new Expression('HEXTORAW(REGEXP_REPLACE(:uuid, \'-\', \'\'))', [':uuid' => $uuidValue]);
58+
} elseif ($db->getName() === 'mysql') {
59+
$uuidValue = UuidHelper::uuidToBlob($uuidValue);
60+
}
61+
62+
$db->createCommand()->insert($tableName, [
63+
'int_col' => 1,
64+
'uuid_pk' => $uuidValue,
65+
])->execute();
66+
67+
$uuid = (new Query($db))
68+
->select(['[[uuid_pk]]'])
69+
->from($tableName)
70+
->where(['int_col' => 1])
71+
->scalar()
72+
;
73+
74+
$uuidString = strtolower(UuidHelper::toUuid($uuid));
75+
76+
$this->assertEquals($uuidSource, $uuidString);
77+
78+
$db->close();
79+
}
80+
2581
protected function checkBuildString(string $expected, string $type, int|null $length, array $calls): void
2682
{
2783
$db = $this->getConnection();
@@ -38,4 +94,47 @@ protected function checkBuildString(string $expected, string $type, int|null $le
3894

3995
$db->close();
4096
}
97+
98+
protected function checkCreateColumn(string $expected, string $type, int|null $length, array $calls): void
99+
{
100+
$db = $this->getConnection();
101+
102+
if (str_contains($expected, 'UUID_TO_BIN')) {
103+
$serverVersion = $db->getServerVersion();
104+
if (str_contains($serverVersion, 'MariaDB')) {
105+
$db->close();
106+
$this->markTestSkipped('UUID_TO_BIN not supported MariaDB as defaultValue');
107+
}
108+
if (version_compare($serverVersion, '8', '<')) {
109+
$db->close();
110+
$this->markTestSkipped('UUID_TO_BIN not exists in MySQL 5.7');
111+
}
112+
}
113+
114+
$schema = $db->getSchema();
115+
$builder = $schema->createColumn($type, $length);
116+
117+
foreach ($calls as $call) {
118+
$method = array_shift($call);
119+
call_user_func_array([$builder, $method], $call);
120+
}
121+
122+
$tableName = '{{%column_schema_builder_types}}';
123+
if ($db->getTableSchema($tableName, true)) {
124+
$db->createCommand()->dropTable($tableName)->execute();
125+
}
126+
127+
$command = $db->createCommand()->createTable($tableName, [
128+
'column' => $builder,
129+
]);
130+
131+
$this->assertStringContainsString("\t" . $expected . "\n", $command->getRawSql());
132+
133+
$command->execute();
134+
135+
$tableSchema = $db->getTableSchema($tableName, true);
136+
$this->assertNotNull($tableSchema);
137+
138+
$db->close();
139+
}
41140
}

‎tests/Db/Helper/UuidHelperTest.php‎

Lines changed: 91 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,91 @@
1+
<?php
2+
3+
declare(strict_types=1);
4+
5+
namespace Yiisoft\Db\Tests\Db\Helper;
6+
7+
use PHPUnit\Framework\TestCase;
8+
use Yiisoft\Db\Exception\InvalidArgumentException;
9+
use Yiisoft\Db\Helper\UuidHelper;
10+
11+
/**
12+
* @group db
13+
*/
14+
final class UuidHelperTest extends TestCase
15+
{
16+
/**
17+
* @dataProvider successUuids
18+
*/
19+
public function testConvert(string $uuid): void
20+
{
21+
$blobUuid = UuidHelper::uuidToBlob($uuid);
22+
$this->assertEquals($uuid, UuidHelper::toUuid($blobUuid));
23+
}
24+
25+
/**
26+
* @dataProvider incorrectUuids
27+
*/
28+
public function testToBlobIncorrectUuid(string $uuid): void
29+
{
30+
$this->expectException(InvalidArgumentException::class);
31+
$this->expectExceptionMessage('Incorrect UUID.');
32+
33+
$blobUuid = UuidHelper::uuidToBlob($uuid);
34+
$this->assertEquals($uuid, UuidHelper::toUuid($blobUuid));
35+
}
36+
37+
/**
38+
* @dataProvider blobUuids
39+
*/
40+
public function testToUuid($blobUuid, $expected): void
41+
{
42+
$uuid = UuidHelper::toUuid($blobUuid);
43+
$this->assertEquals($expected, $uuid);
44+
}
45+
46+
/**
47+
* @dataProvider incorrectBlobUuids
48+
*/
49+
public function testToUuidFailed($blobUuid, $expected): void
50+
{
51+
$this->expectException(InvalidArgumentException::class);
52+
$this->expectExceptionMessage('Length of source data is should be 16 or 32 bytes.');
53+
54+
$uuid = UuidHelper::toUuid($blobUuid);
55+
$this->assertEquals($expected, $uuid);
56+
}
57+
58+
public function successUuids(): array
59+
{
60+
return [
61+
['738146be-87b1-49f2-9913-36142fb6fcbe'],
62+
];
63+
}
64+
65+
public function incorrectUuids(): array
66+
{
67+
return [
68+
['738146be-87b149f2-9913-36142fb6fcbe'],
69+
['738146be-87b1-K9f2-9913-36142fb6fcbe'],
70+
['738146be+87b1-K9f2-9913-36142fb6fcbe'],
71+
];
72+
}
73+
74+
public function blobUuids(): array
75+
{
76+
return [
77+
['738146be-87b1-49f2-9913-36142fb6fcbe', '738146be-87b1-49f2-9913-36142fb6fcbe'],
78+
['738146be87b149f2991336142fb6fcbe', '738146be-87b1-49f2-9913-36142fb6fcbe'],
79+
[hex2bin('738146be87b149f2991336142fb6fcbe'), '738146be-87b1-49f2-9913-36142fb6fcbe'],
80+
];
81+
}
82+
83+
public function incorrectBlobUuids(): array
84+
{
85+
return [
86+
['738146be-87b1-49f2-9913-36142fbfcbe', '738146be-87b1-49f2-9913-36142fb6fcbe'],
87+
['738146be87b149f2991336142fb6fcb', '738146be-87b1-49f2-9913-36142fb6fcbe'],
88+
[hex2bin('738146be87b149f291336142fb6fcb'), '738146be-87b1-49f2-9913-36142fb6fcbe'],
89+
];
90+
}
91+
}

‎tests/Db/Schema/ColumnSchemaBuilderTest.php‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -157,6 +157,8 @@ public function testGetCategoryMap(): void
157157
'binary' => 'other',
158158
'boolean' => 'numeric',
159159
'money' => 'numeric',
160+
'uuid' => 'uuid',
161+
'uuid_pk' => 'uuid_pk',
160162
],
161163
$column->getCategoryMap(),
162164
);

0 commit comments

Comments
 (0)