1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
|
<?php
namespace Illuminate\Tests\Integration\Database\MariaDb;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use PHPUnit\Framework\Attributes\DataProvider;
use PHPUnit\Framework\Attributes\RequiresOperatingSystem;
use PHPUnit\Framework\Attributes\RequiresPhpExtension;
#[RequiresOperatingSystem('Linux|Darwin')]
#[RequiresPhpExtension('pdo_mysql')]
class DatabaseMariaDbConnectionTest extends MariaDbTestCase
{
const TABLE = 'player';
const FLOAT_COL = 'float_col';
const JSON_COL = 'json_col';
const FLOAT_VAL = 0.2;
protected function afterRefreshingDatabase()
{
if (! Schema::hasTable(self::TABLE)) {
Schema::create(self::TABLE, function (Blueprint $table) {
$table->json(self::JSON_COL)->nullable();
$table->float(self::FLOAT_COL)->nullable();
});
}
}
protected function destroyDatabaseMigrations()
{
Schema::drop(self::TABLE);
}
#[DataProvider('floatComparisonsDataProvider')]
public function testJsonFloatComparison($value, $operator, $shouldMatch)
{
DB::table(self::TABLE)->insert([self::JSON_COL => '{"rank":'.self::FLOAT_VAL.'}']);
$this->assertSame(
$shouldMatch,
DB::table(self::TABLE)->where(self::JSON_COL.'->rank', $operator, $value)->exists(),
self::JSON_COL.'->rank should '.($shouldMatch ? '' : 'not ')."be $operator $value"
);
}
public static function floatComparisonsDataProvider()
{
return [
[0.2, '=', true],
[0.2, '>', false],
[0.2, '<', false],
[0.1, '=', false],
[0.1, '<', false],
[0.1, '>', true],
[0.3, '=', false],
[0.3, '<', true],
[0.3, '>', false],
];
}
public function testFloatValueStoredCorrectly()
{
DB::table(self::TABLE)->insert([self::FLOAT_COL => self::FLOAT_VAL]);
$this->assertEquals(self::FLOAT_VAL, DB::table(self::TABLE)->value(self::FLOAT_COL));
}
#[DataProvider('jsonWhereNullDataProvider')]
public function testJsonWhereNull($expected, $key, array $value = ['value' => 123])
{
DB::table(self::TABLE)->insert([self::JSON_COL => json_encode($value)]);
$this->assertSame($expected, DB::table(self::TABLE)->whereNull(self::JSON_COL.'->'.$key)->exists());
}
#[DataProvider('jsonWhereNullDataProvider')]
public function testJsonWhereNotNull($expected, $key, array $value = ['value' => 123])
{
DB::table(self::TABLE)->insert([self::JSON_COL => json_encode($value)]);
$this->assertSame(! $expected, DB::table(self::TABLE)->whereNotNull(self::JSON_COL.'->'.$key)->exists());
}
public static function jsonWhereNullDataProvider()
{
return [
'key not exists' => [true, 'invalid'],
'key exists and null' => [true, 'value', ['value' => null]],
'key exists and "null"' => [false, 'value', ['value' => 'null']],
'key exists and not null' => [false, 'value', ['value' => false]],
'nested key not exists' => [true, 'nested->invalid'],
'nested key exists and null' => [true, 'nested->value', ['nested' => ['value' => null]]],
'nested key exists and "null"' => [false, 'nested->value', ['nested' => ['value' => 'null']]],
'nested key exists and not null' => [false, 'nested->value', ['nested' => ['value' => false]]],
'array index not exists' => [false, '[0]', [1 => 'invalid']],
'array index exists and null' => [true, '[0]', [null]],
'array index exists and "null"' => [false, '[0]', ['null']],
'array index exists and not null' => [false, '[0]', [false]],
'nested array index not exists' => [false, 'nested[0]', ['nested' => [1 => 'nested->invalid']]],
'nested array index exists and null' => [true, 'nested->value[1]', ['nested' => ['value' => [0, null]]]],
'nested array index exists and "null"' => [false, 'nested->value[1]', ['nested' => ['value' => [0, 'null']]]],
'nested array index exists and not null' => [false, 'nested->value[1]', ['nested' => ['value' => [0, false]]]],
];
}
public function testJsonPathUpdate()
{
DB::table(self::TABLE)->insert([
[self::JSON_COL => '{"foo":["bar"]}'],
[self::JSON_COL => '{"foo":["baz"]}'],
]);
$updatedCount = DB::table(self::TABLE)->where(self::JSON_COL.'->foo[0]', 'baz')->update([
self::JSON_COL.'->foo[0]' => 'updated',
]);
$this->assertSame(1, $updatedCount);
}
#[DataProvider('jsonContainsKeyDataProvider')]
public function testWhereJsonContainsKey($count, $column)
{
DB::table(self::TABLE)->insert([
['json_col' => '{"foo":{"bar":["baz"]}}'],
['json_col' => '{"foo":{"bar":false}}'],
['json_col' => '{"foo":{}}'],
['json_col' => '{"foo":[{"bar":"bar"},{"baz":"baz"}]}'],
['json_col' => '{"bar":null}'],
]);
$this->assertSame($count, DB::table(self::TABLE)->whereJsonContainsKey($column)->count());
}
public static function jsonContainsKeyDataProvider()
{
return [
'string key' => [4, 'json_col->foo'],
'nested key exists' => [2, 'json_col->foo->bar'],
'string key missing' => [0, 'json_col->none'],
'integer key with arrow ' => [0, 'json_col->foo->bar->0'],
'integer key with braces' => [2, 'json_col->foo->bar[0]'],
'integer key missing' => [0, 'json_col->foo->bar[1]'],
'mixed keys' => [1, 'json_col->foo[1]->baz'],
'null value' => [1, 'json_col->bar'],
];
}
}
|
