|
1
|
|
|
<?php |
|
2
|
|
|
|
|
3
|
|
|
namespace Lagdo\DbAdmin\Driver\PgSql\Db; |
|
4
|
|
|
|
|
5
|
|
|
use Lagdo\DbAdmin\Driver\Db\Grammar as AbstractGrammar; |
|
6
|
|
|
|
|
7
|
|
|
use Lagdo\DbAdmin\Driver\Entity\TableEntity; |
|
8
|
|
|
|
|
9
|
|
|
class Grammar extends AbstractGrammar |
|
10
|
|
|
{ |
|
11
|
|
|
/** |
|
12
|
|
|
* @inheritDoc |
|
13
|
|
|
*/ |
|
14
|
|
|
public function escapeId($idf) |
|
15
|
|
|
{ |
|
16
|
|
|
return '"' . str_replace('"', '""', $idf) . '"'; |
|
17
|
|
|
} |
|
18
|
|
|
|
|
19
|
|
|
private function constraints(string $table) |
|
20
|
|
|
{ |
|
21
|
|
|
$constraints = []; |
|
22
|
|
|
$query = "SELECT conname, consrc FROM pg_catalog.pg_constraint " . |
|
23
|
|
|
"INNER JOIN pg_catalog.pg_namespace ON pg_constraint.connamespace = pg_namespace.oid " . |
|
24
|
|
|
"INNER JOIN pg_catalog.pg_class ON pg_constraint.conrelid = pg_class.oid " . |
|
25
|
|
|
"AND pg_constraint.connamespace = pg_class.relnamespace WHERE pg_constraint.contype = 'c' " . |
|
26
|
|
|
// "-- handle only CONSTRAINTs here, not TYPES " . |
|
27
|
|
|
"AND conrelid != 0 AND nspname = current_schema() AND relname = " . |
|
28
|
|
|
$this->driver->quote($table) . "ORDER BY connamespace, conname"; |
|
|
|
|
|
|
29
|
|
|
foreach ($this->driver->rows($query) as $row) |
|
30
|
|
|
{ |
|
31
|
|
|
$constraints[$row['conname']] = $row['consrc']; |
|
32
|
|
|
} |
|
33
|
|
|
return $constraints; |
|
34
|
|
|
} |
|
35
|
|
|
|
|
36
|
|
|
/** |
|
37
|
|
|
* @inheritDoc |
|
38
|
|
|
*/ |
|
39
|
|
|
public function sqlForForeignKeys(string $table) |
|
40
|
|
|
{ |
|
41
|
|
|
$query = ""; |
|
42
|
|
|
|
|
43
|
|
|
$status = $this->driver->tableStatus($table); |
|
44
|
|
|
$fkeys = $this->driver->foreignKeys($table); |
|
45
|
|
|
ksort($fkeys); |
|
46
|
|
|
|
|
47
|
|
|
foreach ($fkeys as $fkey_name => $fkey) { |
|
48
|
|
|
$query .= "ALTER TABLE ONLY " . $this->escapeId($status->schema) . "." . |
|
49
|
|
|
$this->escapeId($status->name) . " ADD CONSTRAINT " . $this->escapeId($fkey_name) . |
|
50
|
|
|
" {$fkey->definition} " . ($fkey->deferrable ? 'DEFERRABLE' : 'NOT DEFERRABLE') . ";\n"; |
|
51
|
|
|
} |
|
52
|
|
|
|
|
53
|
|
|
return ($query ? "$query\n" : $query); |
|
54
|
|
|
} |
|
55
|
|
|
|
|
56
|
|
|
/** |
|
57
|
|
|
* @param array $fields |
|
58
|
|
|
* @param boolean $autoIncrement |
|
59
|
|
|
* @param string $style |
|
60
|
|
|
* |
|
61
|
|
|
* @return array |
|
62
|
|
|
*/ |
|
63
|
|
|
private function _sequences(array $fields, bool $autoIncrement, string $style) |
|
64
|
|
|
{ |
|
65
|
|
|
$sequences = []; |
|
66
|
|
|
// Fields definitions |
|
67
|
|
|
foreach ($fields as $field_name => $field) { |
|
68
|
|
|
// sequences for fields |
|
69
|
|
|
if (preg_match('~nextval\(\'([^\']+)\'\)~', $field->default, $matches)) { |
|
70
|
|
|
$sequence_name = $matches[1]; |
|
71
|
|
|
$sq = reset($this->driver->rows($this->driver->minVersion(10) ? |
|
72
|
|
|
"SELECT *, cache_size AS cache_value FROM pg_sequences " . |
|
73
|
|
|
"WHERE schemaname = current_schema() AND sequencename = " . |
|
74
|
|
|
$this->driver->quote($sequence_name) : "SELECT * FROM $sequence_name")); |
|
75
|
|
|
$sequences[] = ($style == "DROP+CREATE" ? "DROP SEQUENCE IF EXISTS $sequence_name;\n" : "") . |
|
76
|
|
|
"CREATE SEQUENCE $sequence_name INCREMENT $sq[increment_by] MINVALUE $sq[min_value] MAXVALUE $sq[max_value]" . |
|
77
|
|
|
($autoIncrement && $sq['last_value'] ? " START $sq[last_value]" : "") . " CACHE $sq[cache_value];"; |
|
78
|
|
|
} |
|
79
|
|
|
} |
|
80
|
|
|
return $sequences; |
|
81
|
|
|
} |
|
82
|
|
|
|
|
83
|
|
|
/** |
|
84
|
|
|
* @param array $fields |
|
85
|
|
|
* @param array $indexes |
|
86
|
|
|
* |
|
87
|
|
|
* @return array |
|
88
|
|
|
*/ |
|
89
|
|
|
private function _clauses(array $fields, array $indexes) |
|
90
|
|
|
{ |
|
91
|
|
|
$clauses = []; |
|
92
|
|
|
// Fields definitions |
|
93
|
|
|
foreach ($fields as $field_name => $field) { |
|
94
|
|
|
$clauses[] = $this->escapeId($field->name) . ' ' . $field->fullType . |
|
95
|
|
|
$this->driver->defaultValue($field) . ($field->attnotnull ? " NOT NULL" : ""); |
|
96
|
|
|
} |
|
97
|
|
|
// Primary + unique keys |
|
98
|
|
|
foreach ($indexes as $index_name => $index) { |
|
99
|
|
|
switch ($index->type) { |
|
100
|
|
|
case 'UNIQUE': |
|
101
|
|
|
$clauses[] = "CONSTRAINT " . $this->escapeId($index_name) . |
|
102
|
|
|
" UNIQUE (" . implode(', ', array_map(function ($column) { |
|
103
|
|
|
return $this->escapeId($column); |
|
104
|
|
|
}, $index->columns)) . ")"; |
|
105
|
|
|
break; |
|
106
|
|
|
case 'PRIMARY': |
|
107
|
|
|
$clauses[] = "CONSTRAINT " . $this->escapeId($index_name) . |
|
108
|
|
|
" PRIMARY KEY (" . implode(', ', array_map(function ($column) { |
|
109
|
|
|
return $this->escapeId($column); |
|
110
|
|
|
}, $index->columns)) . ")"; |
|
111
|
|
|
break; |
|
112
|
|
|
} |
|
113
|
|
|
} |
|
114
|
|
|
// Constraints |
|
115
|
|
|
$constraints = $this->constraints($table); |
|
|
|
|
|
|
116
|
|
|
foreach ($constraints as $conname => $consrc) { |
|
117
|
|
|
$clauses[] = "CONSTRAINT " . $this->escapeId($conname) . " CHECK $consrc"; |
|
118
|
|
|
} |
|
119
|
|
|
|
|
120
|
|
|
return $clauses; |
|
121
|
|
|
} |
|
122
|
|
|
|
|
123
|
|
|
/** |
|
124
|
|
|
* @param array $indexes |
|
125
|
|
|
* |
|
126
|
|
|
* @return string |
|
127
|
|
|
*/ |
|
128
|
|
|
private function _indexQueries(array $indexes) |
|
129
|
|
|
{ |
|
130
|
|
|
$query = ''; |
|
131
|
|
|
// Indexes after table definition |
|
132
|
|
|
foreach ($indexes as $index_name => $index) { |
|
133
|
|
|
if ($index->type == 'INDEX') { |
|
134
|
|
|
$columns = []; |
|
135
|
|
|
foreach ($index->columns as $key => $val) { |
|
136
|
|
|
$columns[] = $this->escapeId($val) . ($index->descs[$key] ? " DESC" : ""); |
|
137
|
|
|
} |
|
138
|
|
|
$query .= "\n\nCREATE INDEX " . $this->escapeId($index_name) . " ON " . |
|
139
|
|
|
$this->escapeId($status->schema) . "." . $this->escapeId($status->name) . |
|
|
|
|
|
|
140
|
|
|
" USING btree (" . implode(', ', $columns) . ");"; |
|
141
|
|
|
} |
|
142
|
|
|
} |
|
143
|
|
|
return $query; |
|
144
|
|
|
} |
|
145
|
|
|
|
|
146
|
|
|
/** |
|
147
|
|
|
* @param array $fields |
|
148
|
|
|
* @param TableEntity $status |
|
149
|
|
|
* |
|
150
|
|
|
* @return string |
|
151
|
|
|
*/ |
|
152
|
|
|
private function _commentQueries(array $fields, TableEntity $status) |
|
153
|
|
|
{ |
|
154
|
|
|
$query = ''; |
|
155
|
|
|
$table = $this->escapeId($status->schema) . '.' . $this->escapeId($status->name); |
|
156
|
|
|
// Comments for table & fields |
|
157
|
|
|
if ($status->comment) { |
|
158
|
|
|
$query .= "\n\nCOMMENT ON TABLE $table IS " . $this->driver->quote($status->comment) . ";"; |
|
159
|
|
|
} |
|
160
|
|
|
foreach ($fields as $name => $field) { |
|
161
|
|
|
if ($field->comment) { |
|
162
|
|
|
$query .= "\n\nCOMMENT ON COLUMN $table." . $this->escapeId($name) . |
|
163
|
|
|
" IS " . $this->driver->quote($field->comment) . ";"; |
|
164
|
|
|
} |
|
165
|
|
|
} |
|
166
|
|
|
return $query; |
|
167
|
|
|
} |
|
168
|
|
|
|
|
169
|
|
|
/** |
|
170
|
|
|
* @inheritDoc |
|
171
|
|
|
*/ |
|
172
|
|
|
public function sqlForCreateTable(string $table, bool $autoIncrement, string $style) |
|
173
|
|
|
{ |
|
174
|
|
|
$status = $this->driver->tableStatus($table); |
|
175
|
|
|
if ($status !== null && $this->driver->isView($status)) { |
|
176
|
|
|
$view = $this->driver->view($table); |
|
177
|
|
|
return rtrim("CREATE VIEW " . $this->escapeId($table) . " AS $view[select]", ";"); |
|
178
|
|
|
} |
|
179
|
|
|
|
|
180
|
|
|
$fields = $this->driver->fields($table); |
|
181
|
|
|
if (empty($status) || empty($fields)) { |
|
182
|
|
|
return ''; |
|
183
|
|
|
} |
|
184
|
|
|
|
|
185
|
|
|
$sequences = $this->_sequences($fields, $autoIncrement, $style); |
|
186
|
|
|
$indexes = $this->driver->indexes($table); |
|
187
|
|
|
ksort($indexes); |
|
188
|
|
|
$clauses = $this->_clauses($fields, $indexes); |
|
189
|
|
|
// Adding sequences before table definition |
|
190
|
|
|
$query = ''; |
|
191
|
|
|
if (!empty($sequences)) { |
|
192
|
|
|
$query = implode("\n\n", $sequences) . "\n\n"; |
|
193
|
|
|
} |
|
194
|
|
|
$query .= 'CREATE TABLE ' . $this->escapeId($status->schema) . '.' . $this->escapeId($status->name) . " (\n "; |
|
195
|
|
|
$query .= implode(",\n ", $clauses) . "\n) WITH (oids = " . ($status->oid ? 'true' : 'false') . ");"; |
|
196
|
|
|
$query .= $this->_indexQueries($indexes); |
|
197
|
|
|
$query .= $this->_commentQueries($fields, $status); |
|
198
|
|
|
|
|
199
|
|
|
return rtrim($query, ';'); |
|
200
|
|
|
} |
|
201
|
|
|
|
|
202
|
|
|
/** |
|
203
|
|
|
* @inheritDoc |
|
204
|
|
|
*/ |
|
205
|
|
|
public function sqlForTruncateTable(string $table) |
|
206
|
|
|
{ |
|
207
|
|
|
return "TRUNCATE " . $this->table($table); |
|
208
|
|
|
} |
|
209
|
|
|
|
|
210
|
|
|
/** |
|
211
|
|
|
* @inheritDoc |
|
212
|
|
|
*/ |
|
213
|
|
|
public function sqlForCreateTrigger(string $table) |
|
214
|
|
|
{ |
|
215
|
|
|
$status = $this->driver->tableStatus($table); |
|
216
|
|
|
$query = ""; |
|
217
|
|
|
foreach ($this->driver->triggers($table) as $trg_id => $trg) { |
|
218
|
|
|
$trigger = $this->driver->trigger($trg_id, $status->name); |
|
219
|
|
|
$query .= "\nCREATE TRIGGER " . $this->escapeId($trigger['Trigger']) . |
|
220
|
|
|
" $trigger[Timing] $trigger[Events] ON " . $this->escapeId($status->schema) . "." . |
|
221
|
|
|
$this->escapeId($status->name) . " $trigger[Type] $trigger[Statement];;\n"; |
|
222
|
|
|
} |
|
223
|
|
|
return $query; |
|
224
|
|
|
} |
|
225
|
|
|
|
|
226
|
|
|
|
|
227
|
|
|
/** |
|
228
|
|
|
* @inheritDoc |
|
229
|
|
|
*/ |
|
230
|
|
|
public function sqlForUseDatabase(string $database) |
|
231
|
|
|
{ |
|
232
|
|
|
return "\connect " . $this->escapeId($database); |
|
233
|
|
|
} |
|
234
|
|
|
} |
|
235
|
|
|
|
lagdo /
dbadmin-driver-pgsql
Loading history...
This check looks for calls to methods that do not seem to exist on a given type. It looks for the method on the type itself as well as in inherited classes or implemented interfaces.
This is most likely a typographical error or the method has been renamed.