Skip to content

Commit 0d9db18

Browse files
TigrovTigrov
authored
ColumnSchema classes for performance of typecasting (#273)
1 parent 85a1939 commit 0d9db18

4 files changed

Lines changed: 73 additions & 144 deletions

File tree

‎CHANGELOG.md‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3,6 +3,8 @@
33
## 2.0.0 under development
44

55
- Enh #289: Implement `SqlParser` and `ExpressionBuilder` driver classes (@Tigrov)
6+
- Enh #273: Implement `ColumnSchemaInterface` classes according to the data type of database table columns
7+
for type casting performance. Related with yiisoft/db#752 (@Tigrov)
68
- Chg #307: Replace call of `SchemaInterface::getRawTableName()` to `QuoterInterface::getRawTableName()` (@Tigrov)
79

810
## 1.2.0 March 21, 2024

‎src/ColumnSchema.php‎

Lines changed: 0 additions & 85 deletions
This file was deleted.

‎src/Schema.php‎

Lines changed: 53 additions & 48 deletions
Original file line numberDiff line numberDiff line change
@@ -17,7 +17,7 @@
1717
use Yiisoft\Db\Expression\Expression;
1818
use Yiisoft\Db\Helper\DbArrayHelper;
1919
use Yiisoft\Db\Schema\Builder\ColumnInterface;
20-
use Yiisoft\Db\Schema\ColumnSchemaInterface;
20+
use Yiisoft\Db\Schema\Column\ColumnSchemaInterface;
2121
use Yiisoft\Db\Schema\TableSchemaInterface;
2222

2323
use function array_column;
@@ -67,7 +67,10 @@
6767
* type:string,
6868
* notnull:string,
6969
* dflt_value:string|null,
70-
* pk:string
70+
* pk:string,
71+
* size?: int,
72+
* precision?: int,
73+
* scale?: int,
7174
* }
7275
*/
7376
final class Schema extends AbstractPdoSchema
@@ -370,10 +373,10 @@ protected function findColumns(TableSchemaInterface $table): bool
370373
}
371374

372375
$column = $this->loadColumnSchema($info);
373-
$table->column($column->getName(), $column);
376+
$table->column($info['name'], $column);
374377

375378
if ($column->isPrimaryKey()) {
376-
$table->primaryKey($column->getName());
379+
$table->primaryKey($info['name']);
377380
}
378381
}
379382

@@ -469,49 +472,63 @@ public function getSchemaDefaultValues(string $schema = '', bool $refresh = fals
469472
*
470473
* @return ColumnSchemaInterface The column schema object.
471474
*
472-
* @psalm-param array{cid:string, name:string, type:string, notnull:string, dflt_value:string|null, pk:string} $info
475+
* @psalm-param ColumnInfo $info
473476
*/
474-
protected function loadColumnSchema(array $info): ColumnSchemaInterface
477+
private function loadColumnSchema(array $info): ColumnSchemaInterface
475478
{
476-
$column = $this->createColumnSchema($info['name']);
479+
$dbType = strtolower($info['type']);
480+
$type = $this->getColumnType($dbType, $info);
481+
$isUnsigned = str_contains($dbType, 'unsigned');
482+
/** @psalm-var ColumnInfo $info */
483+
$column = $this->createColumnSchema($type, unsigned: $isUnsigned);
484+
$column->name($info['name']);
485+
$column->size($info['size'] ?? null);
486+
$column->precision($info['precision'] ?? null);
487+
$column->scale($info['scale'] ?? null);
477488
$column->allowNull(!$info['notnull']);
478-
$column->primaryKey($info['pk'] != '0');
479-
$column->dbType(strtolower($info['type']));
480-
$column->unsigned(str_contains($column->getDbType() ?? '', 'unsigned'));
481-
$column->type(self::TYPE_STRING);
489+
$column->primaryKey((bool) $info['pk']);
490+
$column->dbType($dbType);
491+
$column->defaultValue($this->normalizeDefaultValue($info['dflt_value'], $column));
482492

483-
if (preg_match('/^(\w+)(?:\(([^)]+)\))?/', $column->getDbType() ?? '', $matches)) {
484-
$type = strtolower($matches[1]);
493+
return $column;
494+
}
485495

486-
if (isset(self::TYPE_MAP[$type])) {
487-
$column->type(self::TYPE_MAP[$type]);
488-
}
496+
/**
497+
* Get the abstract data type for the database data type.
498+
*
499+
* @param string $dbType The database data type
500+
* @param array $info Column information.
501+
*
502+
* @return string The abstract data type.
503+
*/
504+
private function getColumnType(string $dbType, array &$info): string
505+
{
506+
preg_match('/^(\w*)(?:\(([^)]+)\))?/', $dbType, $matches);
507+
$dbType = strtolower($matches[1]);
489508

490-
if (!empty($matches[2])) {
491-
$values = explode(',', $matches[2]);
492-
$column->precision((int) $values[0]);
493-
$column->size((int) $values[0]);
509+
if (!empty($matches[2])) {
510+
$values = explode(',', $matches[2], 2);
511+
$info['size'] = (int) $values[0];
512+
$info['precision'] = (int) $values[0];
494513

495-
if (isset($values[1])) {
496-
$column->scale((int) $values[1]);
497-
}
514+
if (isset($values[1])) {
515+
$info['scale'] = (int) $values[1];
516+
}
498517

499-
if (($type === 'tinyint' || $type === 'bit') && $column->getSize() === 1) {
500-
$column->type(self::TYPE_BOOLEAN);
501-
} elseif ($type === 'bit') {
502-
if ($column->getSize() > 32) {
503-
$column->type(self::TYPE_BIGINT);
504-
} elseif ($column->getSize() === 32) {
505-
$column->type(self::TYPE_INTEGER);
506-
}
507-
}
518+
if (($dbType === 'tinyint' || $dbType === 'bit') && $info['size'] === 1) {
519+
return self::TYPE_BOOLEAN;
508520
}
509-
}
510521

511-
$column->phpType($this->getColumnPhpType($column));
512-
$column->defaultValue($this->normalizeDefaultValue($info['dflt_value'], $column));
522+
if ($dbType === 'bit') {
523+
return match (true) {
524+
$info['size'] === 32 => self::TYPE_INTEGER,
525+
$info['size'] > 32 => self::TYPE_BIGINT,
526+
default => self::TYPE_SMALLINT,
527+
};
528+
}
529+
}
513530

514-
return $column;
531+
return self::TYPE_MAP[$dbType] ?? self::TYPE_STRING;
515532
}
516533

517534
/**
@@ -627,18 +644,6 @@ private function loadTableConstraints(string $tableName, string $returnType): Co
627644
return $result[$returnType];
628645
}
629646

630-
/**
631-
* Creates a column schema for the database.
632-
*
633-
* This method may be overridden by child classes to create a DBMS-specific column schema.
634-
*
635-
* @param string $name Name of the column.
636-
*/
637-
private function createColumnSchema(string $name): ColumnSchemaInterface
638-
{
639-
return new ColumnSchema($name);
640-
}
641-
642647
/**
643648
* @throws Exception
644649
* @throws InvalidConfigException

‎tests/ColumnSchemaTest.php‎

Lines changed: 18 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -5,20 +5,23 @@
55
namespace Yiisoft\Db\Sqlite\Tests;
66

77
use PDO;
8-
use PHPUnit\Framework\TestCase;
98
use Yiisoft\Db\Command\Param;
10-
use Yiisoft\Db\Expression\JsonExpression;
11-
use Yiisoft\Db\Sqlite\ColumnSchema;
12-
use Yiisoft\Db\Schema\SchemaInterface;
9+
use Yiisoft\Db\Schema\Column\BinaryColumnSchema;
10+
use Yiisoft\Db\Schema\Column\BooleanColumnSchema;
11+
use Yiisoft\Db\Schema\Column\DoubleColumnSchema;
12+
use Yiisoft\Db\Schema\Column\IntegerColumnSchema;
13+
use Yiisoft\Db\Schema\Column\JsonColumnSchema;
14+
use Yiisoft\Db\Schema\Column\StringColumnSchema;
1315
use Yiisoft\Db\Sqlite\Tests\Support\TestTrait;
1416
use Yiisoft\Db\Query\Query;
17+
use Yiisoft\Db\Tests\Common\CommonColumnSchemaTest;
1518

1619
use function str_repeat;
1720

1821
/**
1922
* @group sqlite
2023
*/
21-
final class ColumnSchemaTest extends TestCase
24+
final class ColumnSchemaTest extends CommonColumnSchemaTest
2225
{
2326
use TestTrait;
2427

@@ -75,13 +78,17 @@ public function testPhpTypeCast(): void
7578
$db->close();
7679
}
7780

78-
public function testTypeCastJson(): void
81+
public function testColumnSchemaInstance()
7982
{
80-
$columnSchema = new ColumnSchema('json_col');
81-
$columnSchema->dbType(SchemaInterface::TYPE_JSON);
82-
$columnSchema->type(SchemaInterface::TYPE_JSON);
83+
$db = $this->getConnection(true);
84+
$schema = $db->getSchema();
85+
$tableSchema = $schema->getTableSchema('type');
8386

84-
$this->assertSame(['a' => 1], $columnSchema->phpTypeCast('{"a":1}'));
85-
$this->assertEquals(new JsonExpression(['a' => 1], SchemaInterface::TYPE_JSON), $columnSchema->dbTypeCast(['a' => 1]));
87+
$this->assertInstanceOf(IntegerColumnSchema::class, $tableSchema->getColumn('int_col'));
88+
$this->assertInstanceOf(StringColumnSchema::class, $tableSchema->getColumn('char_col'));
89+
$this->assertInstanceOf(DoubleColumnSchema::class, $tableSchema->getColumn('float_col'));
90+
$this->assertInstanceOf(BinaryColumnSchema::class, $tableSchema->getColumn('blob_col'));
91+
$this->assertInstanceOf(BooleanColumnSchema::class, $tableSchema->getColumn('bool_col'));
92+
$this->assertInstanceOf(JsonColumnSchema::class, $tableSchema->getColumn('json_col'));
8693
}
8794
}

0 commit comments

Comments
 (0)