|
1
|
|
|
<?php |
|
2
|
|
|
|
|
3
|
|
|
declare(strict_types=1); |
|
4
|
|
|
|
|
5
|
|
|
namespace Yiisoft\Db\Pgsql; |
|
6
|
|
|
|
|
7
|
|
|
use Throwable; |
|
8
|
|
|
use Yiisoft\Db\Exception\NotSupportedException; |
|
9
|
|
|
use Yiisoft\Db\QueryBuilder\AbstractDDLQueryBuilder; |
|
10
|
|
|
use Yiisoft\Db\Schema\Builder\ColumnInterface; |
|
11
|
|
|
|
|
12
|
|
|
use function array_diff; |
|
13
|
|
|
use function array_unshift; |
|
14
|
|
|
use function explode; |
|
15
|
|
|
use function implode; |
|
16
|
|
|
use function preg_match; |
|
17
|
|
|
use function preg_replace; |
|
18
|
|
|
use function str_contains; |
|
19
|
|
|
|
|
20
|
|
|
/** |
|
21
|
|
|
* Implements a (Data Definition Language) SQL statements for PostgreSQL Server. |
|
22
|
|
|
*/ |
|
23
|
|
|
final class DDLQueryBuilder extends AbstractDDLQueryBuilder |
|
24
|
|
|
{ |
|
25
|
3 |
|
public function addDefaultValue(string $table, string $name, string $column, mixed $value): string |
|
26
|
|
|
{ |
|
27
|
3 |
|
throw new NotSupportedException(__METHOD__ . ' is not supported by PostgreSQL.'); |
|
28
|
|
|
} |
|
29
|
|
|
|
|
30
|
1 |
|
public function alterColumn(string $table, string $column, ColumnInterface|string $type): string |
|
31
|
|
|
{ |
|
32
|
1 |
|
$columnName = $this->quoter->quoteColumnName($column); |
|
33
|
1 |
|
$tableName = $this->quoter->quoteTableName($table); |
|
34
|
|
|
|
|
35
|
1 |
|
if ($type instanceof ColumnInterface) { |
|
36
|
1 |
|
$type = $type->asString(); |
|
37
|
|
|
} |
|
38
|
|
|
|
|
39
|
|
|
/** |
|
40
|
|
|
* @link https://github.com/yiisoft/yii2/issues/4492 |
|
41
|
|
|
* @link https://www.postgresql.org/docs/9.1/static/sql-altertable.html |
|
42
|
|
|
*/ |
|
43
|
1 |
|
if (preg_match('/^(DROP|SET|RESET|USING)\s+/i', $type)) { |
|
44
|
1 |
|
return "ALTER TABLE $tableName ALTER COLUMN $columnName $type"; |
|
45
|
|
|
} |
|
46
|
|
|
|
|
47
|
1 |
|
$type = 'TYPE ' . $this->queryBuilder->getColumnType($type); |
|
48
|
1 |
|
$multiAlterStatement = []; |
|
49
|
1 |
|
$constraintPrefix = preg_replace('/[^a-z0-9_]/i', '', $table . '_' . $column); |
|
50
|
|
|
|
|
51
|
1 |
|
if (preg_match('/\s+DEFAULT\s+(["\']?\w*["\']?)/i', $type, $matches)) { |
|
52
|
1 |
|
$type = preg_replace('/\s+DEFAULT\s+(["\']?\w*["\']?)/i', '', $type); |
|
53
|
1 |
|
$multiAlterStatement[] = "ALTER COLUMN $columnName SET DEFAULT $matches[1]"; |
|
54
|
|
|
} |
|
55
|
|
|
|
|
56
|
1 |
|
$type = preg_replace('/\s+NOT\s+NULL/i', '', $type, -1, $count); |
|
57
|
|
|
|
|
58
|
1 |
|
if ($count > 0) { |
|
59
|
1 |
|
$multiAlterStatement[] = "ALTER COLUMN $columnName SET NOT NULL"; |
|
60
|
|
|
} else { |
|
61
|
|
|
/** remove extra null if any */ |
|
62
|
1 |
|
$type = preg_replace('/\s+NULL/i', '', $type, -1, $count); |
|
63
|
1 |
|
if ($count > 0) { |
|
64
|
1 |
|
$multiAlterStatement[] = "ALTER COLUMN $columnName DROP NOT NULL"; |
|
65
|
|
|
} |
|
66
|
|
|
} |
|
67
|
|
|
|
|
68
|
1 |
|
if (preg_match('/\s+CHECK\s+\((.+)\)/i', $type, $matches)) { |
|
69
|
1 |
|
$type = preg_replace('/\s+CHECK\s+\((.+)\)/i', '', $type); |
|
70
|
1 |
|
$multiAlterStatement[] = "ADD CONSTRAINT {$constraintPrefix}_check CHECK ($matches[1])"; |
|
71
|
|
|
} |
|
72
|
|
|
|
|
73
|
1 |
|
$type = preg_replace('/\s+UNIQUE/i', '', $type, -1, $count); |
|
74
|
|
|
|
|
75
|
1 |
|
if ($count > 0) { |
|
76
|
1 |
|
$multiAlterStatement[] = "ADD UNIQUE ($columnName)"; |
|
77
|
|
|
} |
|
78
|
|
|
|
|
79
|
|
|
/** add what's left at the beginning */ |
|
80
|
1 |
|
array_unshift($multiAlterStatement, "ALTER COLUMN $columnName $type"); |
|
81
|
|
|
|
|
82
|
1 |
|
return 'ALTER TABLE ' . $tableName . ' ' . implode(', ', $multiAlterStatement); |
|
83
|
|
|
} |
|
84
|
|
|
|
|
85
|
|
|
/** |
|
86
|
|
|
* @throws Throwable |
|
87
|
|
|
*/ |
|
88
|
2 |
|
public function checkIntegrity(string $schema = '', string $table = '', bool $check = true): string |
|
89
|
|
|
{ |
|
90
|
|
|
/** @psalm-var Schema $schemaInstance */ |
|
91
|
2 |
|
$schemaInstance = $this->schema; |
|
92
|
2 |
|
$enable = $check ? 'ENABLE' : 'DISABLE'; |
|
93
|
2 |
|
$schema = $schema ?: $schemaInstance->getDefaultSchema(); |
|
94
|
2 |
|
$tableNames = []; |
|
95
|
2 |
|
$viewNames = []; |
|
96
|
|
|
|
|
97
|
2 |
|
if ($schema !== null) { |
|
98
|
2 |
|
$tableNames = $table ? [$table] : $schemaInstance->getTableNames($schema); |
|
99
|
2 |
|
$viewNames = $schemaInstance->getViewNames($schema); |
|
100
|
|
|
} |
|
101
|
|
|
|
|
102
|
2 |
|
$tableNames = array_diff($tableNames, $viewNames); |
|
103
|
2 |
|
$command = ''; |
|
104
|
|
|
|
|
105
|
|
|
/** @psalm-var string[] $tableNames */ |
|
106
|
2 |
|
foreach ($tableNames as $tableName) { |
|
107
|
2 |
|
$tableName = $this->quoter->quoteTableName("$schema.$tableName"); |
|
108
|
2 |
|
$command .= "ALTER TABLE $tableName $enable TRIGGER ALL; "; |
|
109
|
|
|
} |
|
110
|
|
|
|
|
111
|
2 |
|
return $command; |
|
112
|
|
|
} |
|
113
|
|
|
|
|
114
|
10 |
|
public function createIndex(string $table, string $name, array|string $columns, ?string $indexType = null, ?string $indexMethod = null): string |
|
115
|
|
|
{ |
|
116
|
10 |
|
return 'CREATE ' . (!empty($indexType) ? $indexType . ' ' : '') . 'INDEX ' |
|
117
|
10 |
|
. $this->quoter->quoteTableName($name) . ' ON ' |
|
118
|
10 |
|
. $this->quoter->quoteTableName($table) |
|
119
|
10 |
|
. (!empty($indexMethod) ? " USING $indexMethod" : '') |
|
120
|
10 |
|
. ' (' . $this->queryBuilder->buildColumns($columns) . ')'; |
|
121
|
|
|
} |
|
122
|
|
|
|
|
123
|
2 |
|
public function dropDefaultValue(string $table, string $name): string |
|
124
|
|
|
{ |
|
125
|
2 |
|
throw new NotSupportedException(__METHOD__ . ' is not supported by PostgreSQL.'); |
|
126
|
|
|
} |
|
127
|
|
|
|
|
128
|
2 |
|
public function dropIndex(string $table, string $name): string |
|
129
|
|
|
{ |
|
130
|
2 |
|
if (str_contains($table, '.') && !str_contains($name, '.')) { |
|
131
|
1 |
|
if (str_contains($table, '{{')) { |
|
132
|
1 |
|
$table = preg_replace('/{{(.*?)}}/', '\1', $table); |
|
133
|
1 |
|
[$schema] = explode('.', $table); |
|
134
|
1 |
|
if (!str_contains($schema, '%')) { |
|
135
|
1 |
|
$name = $schema . '.' . $name; |
|
136
|
|
|
} else { |
|
137
|
1 |
|
$name = '{{' . $schema . '.' . $name . '}}'; |
|
138
|
|
|
} |
|
139
|
|
|
} else { |
|
140
|
1 |
|
[$schema] = explode('.', $table); |
|
141
|
1 |
|
$name = $schema . '.' . $name; |
|
142
|
|
|
} |
|
143
|
|
|
} |
|
144
|
|
|
|
|
145
|
2 |
|
return 'DROP INDEX ' . $this->quoter->quoteTableName($name); |
|
146
|
|
|
} |
|
147
|
|
|
|
|
148
|
2 |
|
public function truncateTable(string $table): string |
|
149
|
|
|
{ |
|
150
|
2 |
|
return 'TRUNCATE TABLE ' . $this->quoter->quoteTableName($table) . ' RESTART IDENTITY'; |
|
151
|
|
|
} |
|
152
|
|
|
|
|
153
|
3 |
|
public function renameTable(string $oldName, string $newName): string |
|
154
|
|
|
{ |
|
155
|
3 |
|
return 'ALTER TABLE ' |
|
156
|
3 |
|
. $this->quoter->quoteTableName($oldName) |
|
157
|
3 |
|
. ' RENAME TO ' |
|
158
|
3 |
|
. $this->quoter->quoteTableName($newName); |
|
159
|
|
|
} |
|
160
|
|
|
} |
|
161
|
|
|
|
yiisoft /
db-pgsql
