1
|
|
|
<?php |
2
|
|
|
|
3
|
|
|
/** |
4
|
|
|
* SQL databases adapters implementation. |
5
|
|
|
* |
6
|
|
|
* @author Maksim Masiukevich <[email protected]> |
7
|
|
|
* @license MIT |
8
|
|
|
* @license https://opensource.org/licenses/MIT |
9
|
|
|
*/ |
10
|
|
|
|
11
|
|
|
declare(strict_types = 1); |
12
|
|
|
|
13
|
|
|
namespace ServiceBus\Storage\Sql; |
14
|
|
|
|
15
|
|
|
use function Amp\call; |
16
|
|
|
use function Latitude\QueryBuilder\field; |
17
|
|
|
use Amp\Promise; |
18
|
|
|
use Latitude\QueryBuilder\CriteriaInterface; |
19
|
|
|
use Latitude\QueryBuilder\Engine\PostgresEngine; |
20
|
|
|
use Latitude\QueryBuilder\EngineInterface; |
21
|
|
|
use Latitude\QueryBuilder\Query as LatitudeQuery; |
22
|
|
|
use Latitude\QueryBuilder\QueryFactory; |
23
|
|
|
use ServiceBus\Storage\Common\BinaryDataDecoder; |
24
|
|
|
use ServiceBus\Storage\Common\Exceptions\IncorrectParameterCast; |
25
|
|
|
use ServiceBus\Storage\Common\Exceptions\OneResultExpected; |
26
|
|
|
use ServiceBus\Storage\Common\QueryExecutor; |
27
|
|
|
use ServiceBus\Storage\Common\ResultSet; |
28
|
|
|
|
29
|
|
|
/** |
30
|
|
|
* Collect iterator data |
31
|
|
|
* Not recommended for use on large amounts of data. |
32
|
|
|
* |
33
|
|
|
* @psalm-suppress MixedReturnTypeCoercion |
34
|
|
|
* |
35
|
|
|
* @return Promise<array<int, mixed>> |
36
|
|
|
* |
37
|
|
|
* @throws \ServiceBus\Storage\Common\Exceptions\ResultSetIterationFailed |
38
|
|
|
*/ |
39
|
|
|
function fetchAll(ResultSet $iterator): Promise |
40
|
|
|
{ |
41
|
30 |
|
return call( |
42
|
|
|
static function () use ($iterator): \Generator |
43
|
|
|
{ |
44
|
30 |
|
$array = []; |
45
|
|
|
|
46
|
30 |
|
while (yield $iterator->advance()) |
47
|
|
|
{ |
48
|
22 |
|
$array[] = $iterator->getCurrent(); |
49
|
|
|
} |
50
|
|
|
|
51
|
30 |
|
return $array; |
52
|
30 |
|
} |
53
|
|
|
); |
54
|
|
|
} |
55
|
|
|
|
56
|
|
|
/** |
57
|
|
|
* Extract 1 result. |
58
|
|
|
* |
59
|
|
|
* @psalm-suppress MixedReturnTypeCoercion |
60
|
|
|
* |
61
|
|
|
* @return Promise<array<string, mixed>|null> |
62
|
|
|
* |
63
|
|
|
* @throws \ServiceBus\Storage\Common\Exceptions\ResultSetIterationFailed |
64
|
|
|
* @throws \ServiceBus\Storage\Common\Exceptions\OneResultExpected The result must contain only 1 row |
65
|
|
|
*/ |
66
|
|
|
function fetchOne(ResultSet $iterator): Promise |
67
|
|
|
{ |
68
|
12 |
|
return call( |
69
|
|
|
static function () use ($iterator): \Generator |
70
|
|
|
{ |
71
|
|
|
/** @var array $collection */ |
72
|
12 |
|
$collection = yield fetchAll($iterator); |
73
|
12 |
|
$resultsCount = \count($collection); |
74
|
|
|
|
75
|
12 |
|
if ($resultsCount === 0 || $resultsCount === 1) |
76
|
|
|
{ |
77
|
|
|
/** @var array|bool $endElement */ |
78
|
10 |
|
$endElement = \end($collection); |
79
|
|
|
|
80
|
10 |
|
if ($endElement !== false) |
81
|
|
|
{ |
82
|
10 |
|
return $endElement; |
83
|
|
|
} |
84
|
|
|
} |
85
|
|
|
else |
86
|
|
|
{ |
87
|
2 |
|
throw new OneResultExpected( |
88
|
2 |
|
\sprintf( |
89
|
2 |
|
'A single record was requested, but the result of the query execution contains several ("%d")', |
90
|
2 |
|
$resultsCount |
91
|
|
|
) |
92
|
|
|
); |
93
|
|
|
} |
94
|
12 |
|
} |
95
|
|
|
); |
96
|
|
|
} |
97
|
|
|
|
98
|
|
|
/** |
99
|
|
|
* Returns the value of the specified sequence (string). |
100
|
|
|
* |
101
|
|
|
* @psalm-suppress MixedReturnTypeCoercion |
102
|
|
|
* |
103
|
|
|
* @return Promise<string> |
104
|
|
|
*/ |
105
|
|
|
function sequence(string $sequenceName, QueryExecutor $executor): Promise |
106
|
|
|
{ |
107
|
|
|
return call( |
108
|
|
|
static function (string $sequenceName) use ($executor): \Generator |
109
|
|
|
{ |
110
|
|
|
/** @var \ServiceBus\Storage\Common\ResultSet $resultSet */ |
111
|
|
|
$resultSet = yield $executor->execute(\sprintf('SELECT nextval(\'%s\')', $sequenceName)); |
112
|
|
|
|
113
|
|
|
/** |
114
|
|
|
* @psalm-var array{nextval: string} $result |
115
|
|
|
* |
116
|
|
|
* @var array $result |
117
|
|
|
*/ |
118
|
|
|
$result = yield fetchOne($resultSet); |
119
|
|
|
|
120
|
|
|
unset($resultSet); |
121
|
|
|
|
122
|
|
|
return (string) $result['nextval']; |
123
|
|
|
}, |
124
|
|
|
$sequenceName |
125
|
|
|
); |
126
|
|
|
} |
127
|
|
|
|
128
|
|
|
/** |
129
|
|
|
* Create & execute SELECT query. |
130
|
|
|
* |
131
|
|
|
* @psalm-param array<mixed, \Latitude\QueryBuilder\CriteriaInterface> $criteria |
132
|
|
|
* @psalm-param array<string, string> $orderBy |
133
|
|
|
* @psalm-suppress MixedTypeCoercion |
134
|
|
|
* |
135
|
|
|
* @param \Latitude\QueryBuilder\CriteriaInterface[] $criteria |
136
|
|
|
* |
137
|
|
|
* @return Promise<\ServiceBus\Storage\Common\ResultSet> |
138
|
|
|
* |
139
|
|
|
* @throws \ServiceBus\Storage\Common\Exceptions\ConnectionFailed Could not connect to database |
140
|
|
|
* @throws \ServiceBus\Storage\Common\Exceptions\InvalidConfigurationOptions |
141
|
|
|
* @throws \ServiceBus\Storage\Common\Exceptions\StorageInteractingFailed Basic type of interaction errors |
142
|
|
|
* @throws \ServiceBus\Storage\Common\Exceptions\UniqueConstraintViolationCheckFailed |
143
|
|
|
*/ |
144
|
|
|
function find(QueryExecutor $queryExecutor, string $tableName, array $criteria = [], ?int $limit = null, array $orderBy = []): Promise |
145
|
|
|
{ |
146
|
8 |
|
return call( |
147
|
|
|
static function (string $tableName, array $criteria, ?int $limit, array $orderBy) use ($queryExecutor): \Generator |
148
|
|
|
{ |
149
|
|
|
/** |
150
|
|
|
* @var string $query |
151
|
|
|
* @var array $parameters |
152
|
|
|
* @psalm-var array<string, string|int|float|null> $parameters |
153
|
|
|
*/ |
154
|
8 |
|
[$query, $parameters] = buildQuery(selectQuery($tableName), $criteria, $orderBy, $limit); |
155
|
|
|
|
156
|
8 |
|
return yield $queryExecutor->execute($query, $parameters); |
157
|
8 |
|
}, |
158
|
8 |
|
$tableName, |
159
|
8 |
|
$criteria, |
160
|
8 |
|
$limit, |
161
|
8 |
|
$orderBy |
162
|
|
|
); |
163
|
|
|
} |
164
|
|
|
|
165
|
|
|
/** |
166
|
|
|
* Create & execute DELETE query. |
167
|
|
|
* |
168
|
|
|
* @psalm-param array<mixed, \Latitude\QueryBuilder\CriteriaInterface> $criteria |
169
|
|
|
* @psalm-suppress MixedTypeCoercion |
170
|
|
|
* |
171
|
|
|
* @param \Latitude\QueryBuilder\CriteriaInterface[] $criteria |
172
|
|
|
* |
173
|
|
|
* @return Promise<int> |
174
|
|
|
* |
175
|
|
|
* @throws \ServiceBus\Storage\Common\Exceptions\ConnectionFailed Could not connect to database |
176
|
|
|
* @throws \ServiceBus\Storage\Common\Exceptions\InvalidConfigurationOptions |
177
|
|
|
* @throws \ServiceBus\Storage\Common\Exceptions\StorageInteractingFailed Basic type of interaction errors |
178
|
|
|
* @throws \ServiceBus\Storage\Common\Exceptions\UniqueConstraintViolationCheckFailed |
179
|
|
|
* @throws \ServiceBus\Storage\Common\Exceptions\ResultSetIterationFailed |
180
|
|
|
*/ |
181
|
|
|
function remove(QueryExecutor $queryExecutor, string $tableName, array $criteria = []): Promise |
182
|
|
|
{ |
183
|
|
|
/** |
184
|
|
|
* @psalm-suppress InvalidArgument Incorrect psalm unpack parameters (...$args) |
185
|
|
|
* @psalm-suppress MixedArgument |
186
|
|
|
*/ |
187
|
2 |
|
return call( |
188
|
|
|
static function (string $tableName, array $criteria) use ($queryExecutor): \Generator |
189
|
|
|
{ |
190
|
|
|
/** |
191
|
|
|
* @var string $query |
192
|
|
|
* @var array $parameters |
193
|
|
|
* @psalm-var array<string, string|int|float|null> $parameters |
194
|
|
|
*/ |
195
|
2 |
|
[$query, $parameters] = buildQuery(deleteQuery($tableName), $criteria); |
196
|
|
|
|
197
|
|
|
/** |
198
|
|
|
* @var \ServiceBus\Storage\Common\ResultSet $resultSet |
199
|
|
|
*/ |
200
|
2 |
|
$resultSet = yield $queryExecutor->execute($query, $parameters); |
201
|
|
|
|
202
|
2 |
|
$affectedRows = $resultSet->affectedRows(); |
203
|
|
|
|
204
|
2 |
|
unset($resultSet); |
205
|
|
|
|
206
|
2 |
|
return $affectedRows; |
207
|
2 |
|
}, |
208
|
2 |
|
$tableName, |
209
|
2 |
|
$criteria |
210
|
|
|
); |
211
|
|
|
} |
212
|
|
|
|
213
|
|
|
/** |
214
|
|
|
* Create query from specified parameters. |
215
|
|
|
* |
216
|
|
|
* @psalm-param array<mixed, \Latitude\QueryBuilder\CriteriaInterface> $criteria |
217
|
|
|
* @psalm-param array<string, string> $orderBy |
218
|
|
|
* |
219
|
|
|
* @param \Latitude\QueryBuilder\CriteriaInterface[] $criteria |
220
|
|
|
* |
221
|
|
|
* @return array 0 - SQL query; 1 - query parameters |
222
|
|
|
*/ |
223
|
|
|
function buildQuery( |
224
|
|
|
LatitudeQuery\AbstractQuery $queryBuilder, |
225
|
|
|
array $criteria = [], |
226
|
|
|
array $orderBy = [], |
227
|
|
|
?int $limit = null |
228
|
|
|
): array { |
229
|
|
|
/** @var LatitudeQuery\DeleteQuery|LatitudeQuery\SelectQuery|LatitudeQuery\UpdateQuery $queryBuilder */ |
230
|
13 |
|
$isFirstCondition = true; |
231
|
|
|
|
232
|
13 |
|
foreach ($criteria as $criteriaItem) |
233
|
|
|
{ |
234
|
7 |
|
$methodName = $isFirstCondition === true ? 'where' : 'andWhere'; |
235
|
7 |
|
$queryBuilder->{$methodName}($criteriaItem); |
236
|
7 |
|
$isFirstCondition = false; |
237
|
|
|
} |
238
|
|
|
|
239
|
13 |
|
if ($queryBuilder instanceof LatitudeQuery\SelectQuery) |
240
|
|
|
{ |
241
|
11 |
|
foreach ($orderBy as $column => $direction) |
242
|
|
|
{ |
243
|
3 |
|
$queryBuilder->orderBy($column, $direction); |
244
|
|
|
} |
245
|
|
|
|
246
|
11 |
|
if (null !== $limit) |
247
|
|
|
{ |
248
|
3 |
|
$queryBuilder->limit($limit); |
249
|
|
|
} |
250
|
|
|
} |
251
|
|
|
|
252
|
13 |
|
$compiledQuery = $queryBuilder->compile(); |
253
|
|
|
|
254
|
|
|
return [ |
255
|
13 |
|
$compiledQuery->sql(), |
256
|
13 |
|
$compiledQuery->params(), |
257
|
|
|
]; |
258
|
|
|
} |
259
|
|
|
|
260
|
|
|
/** |
261
|
|
|
* Unescape binary data. |
262
|
|
|
* |
263
|
|
|
* @psalm-param array<string, string|int|null|float>|string $data |
264
|
|
|
* |
265
|
|
|
* @psalm-return array<string, string|int|null|float>|string |
266
|
|
|
* |
267
|
|
|
* @param array|string $data |
268
|
|
|
* |
269
|
|
|
* @return array|string |
270
|
|
|
*/ |
271
|
|
|
function unescapeBinary(QueryExecutor $queryExecutor, $data) |
272
|
|
|
{ |
273
|
4 |
|
if ($queryExecutor instanceof BinaryDataDecoder) |
274
|
|
|
{ |
275
|
4 |
|
if (\is_array($data) === false) |
276
|
|
|
{ |
277
|
4 |
|
return $queryExecutor->unescapeBinary((string) $data); |
278
|
|
|
} |
279
|
|
|
|
280
|
|
|
foreach ($data as $key => $value) |
|
|
|
|
281
|
|
|
{ |
282
|
|
|
if (empty($value) === false && \is_string($value) === true) |
283
|
|
|
{ |
284
|
|
|
$data[$key] = $queryExecutor->unescapeBinary($value); |
285
|
|
|
} |
286
|
|
|
} |
287
|
|
|
} |
288
|
|
|
|
289
|
|
|
return $data; |
290
|
|
|
} |
291
|
|
|
|
292
|
|
|
/** |
293
|
|
|
* Create equals criteria. |
294
|
|
|
* |
295
|
|
|
* @param float|int|object|string $value |
296
|
|
|
* |
297
|
|
|
* @throws \ServiceBus\Storage\Common\Exceptions\IncorrectParameterCast |
298
|
|
|
*/ |
299
|
|
|
function equalsCriteria(string $field, $value): CriteriaInterface |
300
|
|
|
{ |
301
|
11 |
|
if (\is_object($value) === true) |
302
|
|
|
{ |
303
|
|
|
$value = castObjectToString($value); |
304
|
|
|
} |
305
|
|
|
|
306
|
11 |
|
return field($field)->eq($value); |
307
|
|
|
} |
308
|
|
|
|
309
|
|
|
/** |
310
|
|
|
* Create not equals criteria. |
311
|
|
|
* |
312
|
|
|
* @param float|int|object|string $value |
313
|
|
|
* |
314
|
|
|
* @throws \ServiceBus\Storage\Common\Exceptions\IncorrectParameterCast |
315
|
|
|
*/ |
316
|
|
|
function notEqualsCriteria(string $field, $value): CriteriaInterface |
317
|
|
|
{ |
318
|
3 |
|
if (\is_object($value) === true) |
319
|
|
|
{ |
320
|
1 |
|
$value = castObjectToString($value); |
321
|
|
|
} |
322
|
|
|
|
323
|
3 |
|
return field($field)->notEq($value); |
324
|
|
|
} |
325
|
|
|
|
326
|
|
|
/** |
327
|
|
|
* Create query builder. |
328
|
|
|
*/ |
329
|
|
|
function queryBuilder(EngineInterface $engine = null): QueryFactory |
330
|
|
|
{ |
331
|
26 |
|
return new QueryFactory($engine ?? new PostgresEngine()); |
332
|
|
|
} |
333
|
|
|
|
334
|
|
|
/** |
335
|
|
|
* Create select query (for PostgreSQL). |
336
|
|
|
* |
337
|
|
|
* @noinspection PhpDocSignatureInspection |
338
|
|
|
*/ |
339
|
|
|
function selectQuery(string $fromTable, string ...$columns): LatitudeQuery\SelectQuery |
340
|
|
|
{ |
341
|
18 |
|
return queryBuilder()->select(...$columns)->from($fromTable); |
342
|
|
|
} |
343
|
|
|
|
344
|
|
|
/** |
345
|
|
|
* Create update query (for PostgreSQL). |
346
|
|
|
* |
347
|
|
|
* @psalm-param array<string, mixed>|object $toUpdate |
348
|
|
|
* |
349
|
|
|
* @param array|object $toUpdate |
350
|
|
|
* |
351
|
|
|
* @throws \ServiceBus\Storage\Common\Exceptions\IncorrectParameterCast |
352
|
|
|
*/ |
353
|
|
|
function updateQuery(string $tableName, $toUpdate): LatitudeQuery\UpdateQuery |
354
|
|
|
{ |
355
|
1 |
|
$values = \is_object($toUpdate) === true ? castObjectToArray($toUpdate) : $toUpdate; |
356
|
|
|
|
357
|
1 |
|
return queryBuilder()->update($tableName, $values); |
358
|
|
|
} |
359
|
|
|
|
360
|
|
|
/** |
361
|
|
|
* Create delete query (for PostgreSQL). |
362
|
|
|
*/ |
363
|
|
|
function deleteQuery(string $fromTable): LatitudeQuery\DeleteQuery |
364
|
|
|
{ |
365
|
3 |
|
return queryBuilder()->delete($fromTable); |
366
|
|
|
} |
367
|
|
|
|
368
|
|
|
/** |
369
|
|
|
* Create insert query (for PostgreSQL). |
370
|
|
|
* |
371
|
|
|
* @psalm-param array<string, mixed>|object $toInsert |
372
|
|
|
* |
373
|
|
|
* @param array|object $toInsert |
374
|
|
|
* |
375
|
|
|
* @throws \ServiceBus\Storage\Common\Exceptions\IncorrectParameterCast |
376
|
|
|
*/ |
377
|
|
|
function insertQuery(string $toTable, $toInsert): LatitudeQuery\InsertQuery |
378
|
|
|
{ |
379
|
8 |
|
$rows = \is_object($toInsert) === true ? castObjectToArray($toInsert) : $toInsert; |
380
|
|
|
|
381
|
8 |
|
return queryBuilder()->insert($toTable, $rows); |
382
|
|
|
} |
383
|
|
|
|
384
|
|
|
/** |
385
|
|
|
* Receive object as array (property/value). |
386
|
|
|
* |
387
|
|
|
* @internal |
388
|
|
|
* |
389
|
|
|
* @psalm-return array<string, float|int|string|null> |
390
|
|
|
* |
391
|
|
|
* @throws \ServiceBus\Storage\Common\Exceptions\IncorrectParameterCast |
392
|
|
|
*/ |
393
|
|
|
function castObjectToArray(object $object): array |
394
|
|
|
{ |
395
|
1 |
|
$result = []; |
396
|
|
|
|
397
|
|
|
/** @var float|int|object|string|null $value */ |
398
|
1 |
|
foreach (getObjectVars($object) as $key => $value) |
399
|
|
|
{ |
400
|
1 |
|
$result[toSnakeCase($key)] = cast($value); |
401
|
|
|
} |
402
|
|
|
|
403
|
1 |
|
return $result; |
404
|
|
|
} |
405
|
|
|
|
406
|
|
|
/** |
407
|
|
|
* Gets the properties of the given object. |
408
|
|
|
* |
409
|
|
|
* @internal |
410
|
|
|
* |
411
|
|
|
* @psalm-return array<string, float|int|object|string|null> |
412
|
|
|
*/ |
413
|
|
|
function getObjectVars(object $object): array |
414
|
|
|
{ |
415
|
|
|
/** @var \Closure $closure */ |
416
|
1 |
|
$closure = \Closure::bind( |
417
|
|
|
function (): array |
418
|
|
|
{ |
419
|
|
|
/** @psalm-var object $this */ |
420
|
1 |
|
return \get_object_vars($this); |
|
|
|
|
421
|
1 |
|
}, |
422
|
1 |
|
$object, |
423
|
1 |
|
$object |
424
|
|
|
); |
425
|
|
|
|
426
|
|
|
/** |
427
|
|
|
* @psalm-var array<string, float|int|object|string|null> $vars |
428
|
|
|
* |
429
|
|
|
* @var array $vars |
430
|
|
|
*/ |
431
|
1 |
|
$vars = $closure(); |
432
|
|
|
|
433
|
1 |
|
return $vars; |
434
|
|
|
} |
435
|
|
|
|
436
|
|
|
/** |
437
|
|
|
* @internal |
438
|
|
|
* |
439
|
|
|
* Convert string from lowerCamelCase to snake_case |
440
|
|
|
*/ |
441
|
|
|
function toSnakeCase(string $string): string |
442
|
|
|
{ |
443
|
2 |
|
$replaced = \preg_replace('/(?<!^)[A-Z]/', '_$0', $string); |
444
|
|
|
|
445
|
2 |
|
if (\is_string($replaced) === true) |
|
|
|
|
446
|
|
|
{ |
447
|
2 |
|
return \strtolower($replaced); |
448
|
|
|
} |
449
|
|
|
|
450
|
|
|
return $string; |
451
|
|
|
} |
452
|
|
|
|
453
|
|
|
/** |
454
|
|
|
* @internal |
455
|
|
|
* |
456
|
|
|
* @param float|int|object|string|null $value |
457
|
|
|
* |
458
|
|
|
* @throws \ServiceBus\Storage\Common\Exceptions\IncorrectParameterCast |
459
|
|
|
* |
460
|
|
|
* @return float|int|string|null |
461
|
|
|
*/ |
462
|
|
|
function cast($value) |
463
|
|
|
{ |
464
|
3 |
|
if ($value === null || \is_scalar($value) === true) |
465
|
|
|
{ |
466
|
1 |
|
return $value; |
467
|
|
|
} |
468
|
|
|
|
469
|
2 |
|
return castObjectToString($value); |
470
|
|
|
} |
471
|
|
|
|
472
|
|
|
/** |
473
|
|
|
* Cast object to string. |
474
|
|
|
* |
475
|
|
|
* @internal |
476
|
|
|
* |
477
|
|
|
* @throws \ServiceBus\Storage\Common\Exceptions\IncorrectParameterCast |
478
|
|
|
*/ |
479
|
|
|
function castObjectToString(object $object): string |
480
|
|
|
{ |
481
|
3 |
|
if (\method_exists($object, '__toString') === true) |
482
|
|
|
{ |
483
|
|
|
/** @psalm-suppress InvalidCast Object have __toString method */ |
484
|
2 |
|
return (string) $object; |
485
|
|
|
} |
486
|
|
|
|
487
|
1 |
|
throw new IncorrectParameterCast( |
488
|
1 |
|
\sprintf('"%s" must implements "__toString" method', \get_class($object)) |
489
|
|
|
); |
490
|
|
|
} |
491
|
|
|
|