-
-
Notifications
You must be signed in to change notification settings - Fork 16
Expand file tree
/
Copy pathDMLQueryBuilder.php
More file actions
139 lines (106 loc) · 4.7 KB
/
Copy pathDMLQueryBuilder.php
File metadata and controls
139 lines (106 loc) · 4.7 KB
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
<?php
declare(strict_types=1);
namespace Yiisoft\Db\Pgsql;
use InvalidArgumentException;
use Yiisoft\Db\Query\QueryInterface;
use Yiisoft\Db\QueryBuilder\AbstractDMLQueryBuilder;
use function array_map;
use function implode;
use function str_ends_with;
use function substr;
/**
* Implements a DML (Data Manipulation Language) SQL statements for PostgreSQL Server.
*/
final class DMLQueryBuilder extends AbstractDMLQueryBuilder
{
public function insertReturningPks(string $table, array|QueryInterface $columns, array &$params = []): string
{
$insertSql = $this->insert($table, $columns, $params);
$tableSchema = $this->schema->getTableSchema($table);
$primaryKeys = $tableSchema?->getPrimaryKey() ?? [];
if (empty($primaryKeys)) {
return $insertSql;
}
$primaryKeys = array_map($this->quoter->quoteColumnName(...), $primaryKeys);
return $insertSql . ' RETURNING ' . implode(', ', $primaryKeys);
}
public function resetSequence(string $table, int|string|null $value = null): string
{
$tableSchema = $this->schema->getTableSchema($table);
if ($tableSchema === null) {
throw new InvalidArgumentException("Table not found: '$table'.");
}
$sequence = $tableSchema->getSequenceName();
if ($sequence === null) {
throw new InvalidArgumentException("There is not sequence associated with table '$table'.");
}
/** @link https://www.postgresql.org/docs/8.1/static/functions-sequence.html */
$sequence = $this->quoter->quoteTableName($sequence);
if ($value === null) {
$table = $this->quoter->quoteTableName($table);
$key = $tableSchema->getPrimaryKey()[0];
$key = $this->quoter->quoteColumnName($key);
$value = "(SELECT COALESCE(MAX($key),0) FROM $table)+1";
}
return "SELECT SETVAL('$sequence',$value,false)";
}
public function upsert(
string $table,
array|QueryInterface $insertColumns,
array|bool $updateColumns = true,
array &$params = [],
): string {
$insertSql = $this->insert($table, $insertColumns, $params);
[$uniqueNames, , $updateNames] = $this->prepareUpsertColumns($table, $insertColumns, $updateColumns);
if (empty($uniqueNames)) {
return $insertSql;
}
if (empty($updateColumns) || $updateNames === []) {
/** there are no columns to update */
return "$insertSql ON CONFLICT DO NOTHING";
}
$quotedUniqueNames = array_map($this->quoter->quoteColumnName(...), $uniqueNames);
$updates = $this->prepareUpsertSets($table, $updateColumns, $updateNames, $params);
return $insertSql
. ' ON CONFLICT (' . implode(', ', $quotedUniqueNames) . ')'
. ' DO UPDATE SET ' . implode(', ', $updates);
}
public function upsertReturning(
string $table,
array|QueryInterface $insertColumns,
array|bool $updateColumns = true,
?array $returnColumns = null,
array &$params = [],
): string {
$upsertSql = $this->upsert($table, $insertColumns, $updateColumns, $params);
$returnColumns ??= $this->schema->getTableSchema($table)?->getColumnNames();
if (empty($returnColumns)) {
return $upsertSql;
}
$returnColumns = array_map($this->quoter->quoteColumnName(...), $returnColumns);
if (str_ends_with($upsertSql, ' ON CONFLICT DO NOTHING')) {
$tableName = $this->quoter->quoteTableName($table);
$dummyColumn = $this->getDummyColumn($table);
$uniqueNames = $this->prepareUpsertColumns($table, $insertColumns, $updateColumns)[0];
$quotedUniqueNames = array_map($this->quoter->quoteColumnName(...), $uniqueNames);
$upsertSql = substr($upsertSql, 0, -10)
. '(' . implode(', ', $quotedUniqueNames) . ')'
. " DO UPDATE SET $dummyColumn = $tableName.$dummyColumn";
}
return $upsertSql . ' RETURNING ' . implode(', ', $returnColumns);
}
private function getDummyColumn(string $table): string
{
/** @psalm-suppress PossiblyNullReference */
$columns = $this->schema->getTableSchema($table)->getColumns();
foreach ($columns as $column) {
if ($column->isPrimaryKey() || $column->isUnique()) {
continue;
}
/** @psalm-suppress PossiblyNullArgument */
return $this->quoter->quoteColumnName($column->getName());
}
/** @psalm-suppress PossiblyNullArgument, PossiblyFalseReference */
return $this->quoter->quoteColumnName(end($columns)->getName());
}
}