Skip to content

Commit 1b375d2

Browse files
TigrovTigrov
authored
Fix #263: Support json type
1 parent fd9a542 commit 1b375d2

14 files changed

Lines changed: 347 additions & 2 deletions

‎CHANGELOG.md‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2,6 +2,7 @@
22

33
## 1.0.2 under development
44

5+
- Enh #263: Support json type (@Tigrov)
56
- Bug #268: Fix foreign keys: support multiple foreign keys referencing to one table and possible null columns for reference (@Tigrov)
67

78
## 1.0.1 July 24, 2023

‎composer.json‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -24,7 +24,8 @@
2424
"php": "^8.0",
2525
"ext-mbstring": "*",
2626
"ext-pdo": "*",
27-
"yiisoft/db": "^1.0"
27+
"yiisoft/db": "^1.0",
28+
"yiisoft/json": "^1.0"
2829
},
2930
"require-dev": {
3031
"ext-json": "*",

‎src/Builder/JsonExpressionBuilder.php‎

Lines changed: 55 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,55 @@
1+
<?php
2+
3+
declare(strict_types=1);
4+
5+
namespace Yiisoft\Db\Sqlite\Builder;
6+
7+
use JsonException;
8+
use Yiisoft\Db\Exception\Exception;
9+
use Yiisoft\Db\Exception\InvalidArgumentException;
10+
use Yiisoft\Db\Exception\InvalidConfigException;
11+
use Yiisoft\Db\Exception\NotSupportedException;
12+
use Yiisoft\Db\Expression\ExpressionBuilderInterface;
13+
use Yiisoft\Db\Expression\ExpressionInterface;
14+
use Yiisoft\Db\Expression\JsonExpression;
15+
use Yiisoft\Db\QueryBuilder\QueryBuilderInterface;
16+
use Yiisoft\Db\Query\QueryInterface;
17+
use Yiisoft\Json\Json;
18+
19+
/**
20+
* Builds expressions for {@see `Yiisoft\Db\Expression\JsonExpression`} for SQLite.
21+
*/
22+
final class JsonExpressionBuilder implements ExpressionBuilderInterface
23+
{
24+
public function __construct(private QueryBuilderInterface $queryBuilder)
25+
{
26+
}
27+
28+
/**
29+
* The method builds the raw SQL from the `$expression` that won't be additionally escaped or quoted.
30+
*
31+
* @param JsonExpression $expression The expression to build.
32+
* @param array $params The binding parameters.
33+
*
34+
* @throws Exception
35+
* @throws InvalidArgumentException
36+
* @throws InvalidConfigException
37+
* @throws JsonException
38+
* @throws NotSupportedException
39+
*
40+
* @return string The raw SQL that won't be additionally escaped or quoted.
41+
*/
42+
public function build(ExpressionInterface $expression, array &$params = []): string
43+
{
44+
/** @psalm-var mixed $value */
45+
$value = $expression->getValue();
46+
47+
if ($value instanceof QueryInterface) {
48+
[$sql, $params] = $this->queryBuilder->build($value, $params);
49+
50+
return "($sql)";
51+
}
52+
53+
return $this->queryBuilder->bindParam(Json::encode($value), $params);
54+
}
55+
}

‎src/ColumnSchema.php‎

Lines changed: 50 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4,7 +4,13 @@
44

55
namespace Yiisoft\Db\Sqlite;
66

7+
use JsonException;
8+
use Yiisoft\Db\Expression\ExpressionInterface;
9+
use Yiisoft\Db\Expression\JsonExpression;
710
use Yiisoft\Db\Schema\AbstractColumnSchema;
11+
use Yiisoft\Db\Schema\SchemaInterface;
12+
13+
use function json_decode;
814

915
/**
1016
* Represents the metadata of a column in a database table for SQLite Server.
@@ -32,4 +38,48 @@
3238
*/
3339
final class ColumnSchema extends AbstractColumnSchema
3440
{
41+
/**
42+
* Converts a value from its PHP representation to a database-specific representation.
43+
*
44+
* If the value is null or an {@see Expression}, it won't be converted.
45+
*
46+
* @param mixed $value The value to be converted.
47+
*
48+
* @return mixed The converted value.
49+
*/
50+
public function dbTypecast(mixed $value): mixed
51+
{
52+
if ($value === null || $value instanceof ExpressionInterface) {
53+
return $value;
54+
}
55+
56+
if ($this->getType() === SchemaInterface::TYPE_JSON) {
57+
return new JsonExpression($value, $this->getDbType());
58+
}
59+
60+
return parent::dbTypecast($value);
61+
}
62+
63+
/**
64+
* Converts the input value according to {@see phpType} after retrieval from the database.
65+
*
66+
* If the value is null or an {@see Expression}, it won't be converted.
67+
*
68+
* @param mixed $value The value to be converted.
69+
*
70+
* @throws JsonException
71+
* @return mixed The converted value.
72+
*/
73+
public function phpTypecast(mixed $value): mixed
74+
{
75+
if ($value === null) {
76+
return null;
77+
}
78+
79+
if ($this->getType() === SchemaInterface::TYPE_JSON) {
80+
return json_decode((string) $value, true, 512, JSON_THROW_ON_ERROR);
81+
}
82+
83+
return parent::phpTypecast($value);
84+
}
3585
}

‎src/DQLQueryBuilder.php‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -6,12 +6,14 @@
66

77
use Yiisoft\Db\Expression\ExpressionBuilderInterface;
88
use Yiisoft\Db\Expression\ExpressionInterface;
9+
use Yiisoft\Db\Expression\JsonExpression;
910
use Yiisoft\Db\Query\Query;
1011
use Yiisoft\Db\Query\QueryInterface;
1112
use Yiisoft\Db\QueryBuilder\AbstractDQLQueryBuilder;
1213
use Yiisoft\Db\QueryBuilder\Condition\InCondition;
1314
use Yiisoft\Db\QueryBuilder\Condition\LikeCondition;
1415
use Yiisoft\Db\Sqlite\Builder\InConditionBuilder;
16+
use Yiisoft\Db\Sqlite\Builder\JsonExpressionBuilder;
1517
use Yiisoft\Db\Sqlite\Builder\LikeConditionBuilder;
1618

1719
use function array_filter;
@@ -135,6 +137,7 @@ protected function defaultExpressionBuilders(): array
135137
return array_merge(parent::defaultExpressionBuilders(), [
136138
LikeCondition::class => LikeConditionBuilder::class,
137139
InCondition::class => InConditionBuilder::class,
140+
JsonExpression::class => JsonExpressionBuilder::class,
138141
]);
139142
}
140143
}

‎src/QueryBuilder.php‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -42,6 +42,7 @@ final class QueryBuilder extends AbstractQueryBuilder
4242
SchemaInterface::TYPE_MONEY => 'decimal(19,4)',
4343
SchemaInterface::TYPE_UUID => 'blob(16)',
4444
SchemaInterface::TYPE_UUID_PK => 'blob(16) PRIMARY KEY',
45+
SchemaInterface::TYPE_JSON => 'json',
4546
];
4647

4748
public function __construct(QuoterInterface $quoter, SchemaInterface $schema)

‎src/Schema.php‎

Lines changed: 27 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -105,6 +105,7 @@ final class Schema extends AbstractPdoSchema
105105
'time' => self::TYPE_TIME,
106106
'timestamp' => self::TYPE_TIMESTAMP,
107107
'enum' => self::TYPE_STRING,
108+
'json' => self::TYPE_JSON,
108109
];
109110

110111
public function createColumn(string $type, array|int|string $length = null): ColumnInterface
@@ -364,8 +365,13 @@ protected function findColumns(TableSchemaInterface $table): bool
364365
{
365366
/** @psalm-var ColumnInfo[] $columns */
366367
$columns = $this->getPragmaTableInfo($table->getName());
368+
$jsonColumns = $this->getJsonColumns($table);
367369

368370
foreach ($columns as $info) {
371+
if (in_array($info['name'], $jsonColumns, true)) {
372+
$info['type'] = self::TYPE_JSON;
373+
}
374+
369375
$column = $this->loadColumnSchema($info);
370376
$table->column($column->getName(), $column);
371377

@@ -734,4 +740,25 @@ protected function getCacheTag(): string
734740
{
735741
return md5(serialize(array_merge([self::class], $this->generateCacheKey())));
736742
}
743+
744+
/**
745+
* @throws Throwable
746+
*/
747+
private function getJsonColumns(TableSchemaInterface $table): array
748+
{
749+
$result = [];
750+
/** @psalm-var CheckConstraint[] $checks */
751+
$checks = $this->getTableChecks((string) $table->getFullName());
752+
$regexp = '/\bjson_valid\(\s*["`\[]?(.+?)["`\]]?\s*\)/i';
753+
754+
foreach ($checks as $check) {
755+
if (preg_match_all($regexp, $check->getExpression(), $matches, PREG_SET_ORDER)) {
756+
foreach ($matches as $match) {
757+
$result[] = $match[1];
758+
}
759+
}
760+
}
761+
762+
return $result;
763+
}
737764
}

‎tests/ColumnSchemaTest.php‎

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4,7 +4,12 @@
44

55
namespace Yiisoft\Db\Sqlite\Tests;
66

7+
use PDO;
78
use PHPUnit\Framework\TestCase;
9+
use Yiisoft\Db\Command\Param;
10+
use Yiisoft\Db\Expression\JsonExpression;
11+
use Yiisoft\Db\Sqlite\ColumnSchema;
12+
use Yiisoft\Db\Schema\SchemaInterface;
813
use Yiisoft\Db\Sqlite\Tests\Support\TestTrait;
914
use Yiisoft\Db\Query\Query;
1015

@@ -36,6 +41,8 @@ public function testPhpTypeCast(): void
3641
'timestamp_col' => '2023-07-11 14:50:23',
3742
'bool_col' => false,
3843
'bit_col' => 0b0110_0110, // 102
44+
'json_col' => [['a' => 1, 'b' => null, 'c' => [1, 3, 5]]],
45+
'json_text_col' => (new Query($db))->select(new Param('[1,2,3,"string",null]', PDO::PARAM_STR)),
3946
]
4047
);
4148
$command->execute();
@@ -51,6 +58,8 @@ public function testPhpTypeCast(): void
5158
$timestampColPhpType = $tableSchema->getColumn('timestamp_col')?->phpTypecast($query['timestamp_col']);
5259
$boolColPhpType = $tableSchema->getColumn('bool_col')?->phpTypecast($query['bool_col']);
5360
$bitColPhpType = $tableSchema->getColumn('bit_col')?->phpTypecast($query['bit_col']);
61+
$jsonColPhpType = $tableSchema->getColumn('json_col')?->phpTypecast($query['json_col']);
62+
$jsonTextColPhpType = $tableSchema->getColumn('json_text_col')?->phpTypecast($query['json_text_col']);
5463

5564
$this->assertSame(1, $intColPhpType);
5665
$this->assertSame(str_repeat('x', 100), $charColPhpType);
@@ -60,7 +69,19 @@ public function testPhpTypeCast(): void
6069
$this->assertSame('2023-07-11 14:50:23', $timestampColPhpType);
6170
$this->assertFalse($boolColPhpType);
6271
$this->assertSame(0b0110_0110, $bitColPhpType);
72+
$this->assertSame([['a' => 1, 'b' => null, 'c' => [1, 3, 5]]], $jsonColPhpType);
73+
$this->assertSame([1, 2, 3, 'string', null], $jsonTextColPhpType);
6374

6475
$db->close();
6576
}
77+
78+
public function testTypeCastJson(): void
79+
{
80+
$columnSchema = new ColumnSchema('json_col');
81+
$columnSchema->dbType(SchemaInterface::TYPE_JSON);
82+
$columnSchema->type(SchemaInterface::TYPE_JSON);
83+
84+
$this->assertSame(['a' => 1], $columnSchema->phpTypeCast('{"a":1}'));
85+
$this->assertEquals(new JsonExpression(['a' => 1], SchemaInterface::TYPE_JSON), $columnSchema->dbTypeCast(['a' => 1]));
86+
}
6687
}

‎tests/CommandTest.php‎

Lines changed: 35 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -8,6 +8,7 @@
88
use Yiisoft\Db\Exception\Exception;
99
use Yiisoft\Db\Exception\InvalidConfigException;
1010
use Yiisoft\Db\Exception\NotSupportedException;
11+
use Yiisoft\Db\Expression\JsonExpression;
1112
use Yiisoft\Db\Schema\SchemaInterface;
1213
use Yiisoft\Db\Sqlite\Tests\Support\TestTrait;
1314
use Yiisoft\Db\Tests\Common\CommonCommandTest;
@@ -495,4 +496,38 @@ public function testShowDatabases(): void
495496
$this->assertSame('sqlite::memory:', $db->getDriver()->getDsn());
496497
$this->assertSame(['main'], $command->showDatabases());
497498
}
499+
500+
public function testJsonTable(): void
501+
{
502+
$db = $this->getConnection();
503+
$command = $db->createCommand();
504+
505+
if ($db->getTableSchema('json_table', true) !== null) {
506+
$command->dropTable('json_table')->execute();
507+
}
508+
509+
$command->createTable('json_table', [
510+
'id' => SchemaInterface::TYPE_PK,
511+
'json_col' => SchemaInterface::TYPE_JSON,
512+
])->execute();
513+
514+
$command->insert('json_table', ['id' => 1, 'json_col' => ['a' => 1, 'b' => 2]])->execute();
515+
$command->insert('json_table', ['id' => 2, 'json_col' => new JsonExpression(['c' => 3, 'd' => 4])])->execute();
516+
517+
$tableSchema = $db->getTableSchema('json_table', true);
518+
$this->assertNotNull($tableSchema);
519+
$this->assertSame('json_col', $tableSchema->getColumn('json_col')->getName());
520+
$this->assertSame('json', $tableSchema->getColumn('json_col')->getType());
521+
$this->assertSame('json', $tableSchema->getColumn('json_col')->getDbType());
522+
523+
$this->assertSame(
524+
'{"a":1,"b":2}',
525+
$command->setSql('SELECT `json_col` FROM `json_table` WHERE `id`=1')->queryScalar(),
526+
);
527+
528+
$this->assertSame(
529+
'{"c":3,"d":4}',
530+
$command->setSql('SELECT `json_col` FROM `json_table` WHERE `id`=2')->queryScalar(),
531+
);
532+
}
498533
}

‎tests/Provider/CommandProvider.php‎

Lines changed: 33 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4,11 +4,44 @@
44

55
namespace Yiisoft\Db\Sqlite\Tests\Provider;
66

7+
use Yiisoft\Db\Expression\JsonExpression;
78
use Yiisoft\Db\Sqlite\Tests\Support\TestTrait;
89

910
final class CommandProvider extends \Yiisoft\Db\Tests\Provider\CommandProvider
1011
{
1112
use TestTrait;
1213

1314
protected static string $driverName = 'sqlite';
15+
16+
public static function batchInsert(): array
17+
{
18+
$batchInsert = parent::batchInsert();
19+
20+
$batchInsert['batchInsert binds json params'] = [
21+
'{{%type}}',
22+
['int_col', 'char_col', 'float_col', 'bool_col', 'json_col'],
23+
[
24+
[1, 'a', 0.0, true, ['a' => 1, 'b' => true, 'c' => [1, 2, 3]]],
25+
[2, 'b', -1.0, false, new JsonExpression(['d' => 'e', 'f' => false, 'g' => [4, 5, null]])],
26+
],
27+
'expected' => 'INSERT INTO `type` (`int_col`, `char_col`, `float_col`, `bool_col`, `json_col`) '
28+
. 'VALUES (:qp0, :qp1, :qp2, :qp3, :qp4), (:qp5, :qp6, :qp7, :qp8, :qp9)',
29+
'expectedParams' => [
30+
':qp0' => 1,
31+
':qp1' => 'a',
32+
':qp2' => 0.0,
33+
':qp3' => true,
34+
':qp4' => '{"a":1,"b":true,"c":[1,2,3]}',
35+
36+
':qp5' => 2,
37+
':qp6' => 'b',
38+
':qp7' => -1.0,
39+
':qp8' => false,
40+
':qp9' => '{"d":"e","f":false,"g":[4,5,null]}',
41+
],
42+
2,
43+
];
44+
45+
return $batchInsert;
46+
}
1447
}

0 commit comments

Comments
 (0)