1
|
|
|
<?php |
2
|
|
|
/** |
3
|
|
|
* Class Postgres |
4
|
|
|
* |
5
|
|
|
* @filesource Postgres.php |
6
|
|
|
* @created 11.01.2018 |
7
|
|
|
* @package chillerlan\Database\Dialects |
8
|
|
|
* @author Smiley <[email protected]> |
9
|
|
|
* @copyright 2018 Smiley |
10
|
|
|
* @license MIT |
11
|
|
|
*/ |
12
|
|
|
|
13
|
|
|
namespace chillerlan\Database\Dialects; |
14
|
|
|
|
15
|
|
|
use chillerlan\Database\Query\QueryException; |
16
|
|
|
|
17
|
|
|
class Postgres extends DialectAbstract{ |
18
|
|
|
|
19
|
|
|
protected $quotes = ['"', '"']; |
20
|
|
|
|
21
|
|
|
/** @inheritdoc */ |
22
|
|
|
public function select(array $cols, array $from, string $where = null, $limit = null, $offset = null, bool $distinct = null, array $groupby, array $orderby):array{ |
23
|
|
|
$sql = ['SELECT']; |
24
|
|
|
|
25
|
|
|
if($distinct){ |
26
|
|
|
$sql[] = 'DISTINCT'; |
27
|
|
|
} |
28
|
|
|
|
29
|
|
|
!empty($cols) |
30
|
|
|
? $sql[] = implode(', ', $cols) |
31
|
|
|
: $sql[] = '*'; |
32
|
|
|
|
33
|
|
|
$sql[] = 'FROM'; |
34
|
|
|
$sql[] = implode(', ', $from); |
35
|
|
|
$sql[] = $where; |
36
|
|
|
|
37
|
|
|
if(!empty($groupby)){ |
38
|
|
|
$sql[] = 'GROUP BY'; |
39
|
|
|
$sql[] = implode(', ', $groupby); |
40
|
|
|
} |
41
|
|
|
|
42
|
|
|
if(!empty($orderby)){ |
43
|
|
|
$sql[] = 'ORDER BY'; |
44
|
|
|
$sql[] = implode(', ', $orderby); |
45
|
|
|
} |
46
|
|
|
|
47
|
|
|
if($offset !== null){ |
48
|
|
|
$sql[] = 'OFFSET ?'; |
49
|
|
|
} |
50
|
|
|
|
51
|
|
|
if($limit !== null){ |
52
|
|
|
$sql[] = 'LIMIT ?'; |
53
|
|
|
} |
54
|
|
|
|
55
|
|
|
return $sql; |
56
|
|
|
} |
57
|
|
|
|
58
|
|
|
/** |
59
|
|
|
* @link https://www.postgresql.org/docs/9.5/static/sql-insert.html#SQL-ON-CONFLICT |
60
|
|
|
* |
61
|
|
|
* @inheritdoc |
62
|
|
|
*/ |
63
|
|
|
public function insert(string $table, array $fields, string $onConflict = null, string $conflictTarget = null):array{ |
64
|
|
|
$sql = parent::insert($table, $fields); |
65
|
|
|
|
66
|
|
|
if(in_array($onConflict, ['IGNORE', 'REPLACE'], true)){ |
67
|
|
|
|
68
|
|
|
if(empty($conflictTarget)){ |
69
|
|
|
throw new QueryException('postgres insert on conflict: no conflict target given'); |
70
|
|
|
} |
71
|
|
|
|
72
|
|
|
$sql[] = 'ON CONFLICT ('.$this->quote($conflictTarget).') DO'; |
73
|
|
|
|
74
|
|
|
switch($onConflict){ |
75
|
|
|
case 'IGNORE': |
76
|
|
|
$sql[] = 'NOTHING'; |
77
|
|
|
break; |
78
|
|
|
case 'REPLACE': |
79
|
|
|
$sql[] = $this->onConflictUpdate($fields); |
80
|
|
|
break; |
81
|
|
|
} |
82
|
|
|
|
83
|
|
|
} |
84
|
|
|
|
85
|
|
|
return $sql; |
86
|
|
|
} |
87
|
|
|
|
88
|
|
|
/** |
89
|
|
|
* @param array $fields |
90
|
|
|
* |
91
|
|
|
* @return string |
92
|
|
|
*/ |
93
|
|
|
protected function onConflictUpdate(array $fields):string { |
94
|
|
|
$onConflictUpdate = []; |
95
|
|
|
|
96
|
|
|
foreach($fields as $f){ |
97
|
|
|
$onConflictUpdate[] = $this->quote($f).' = EXCLUDED.'.$this->quote($f); |
98
|
|
|
} |
99
|
|
|
|
100
|
|
|
return 'UPDATE SET '.implode(', ', $onConflictUpdate); |
101
|
|
|
} |
102
|
|
|
|
103
|
|
|
/** @inheritdoc */ |
104
|
|
|
public function createDatabase(string $dbname, bool $ifNotExists = null, string $collate = null):array{ |
105
|
|
|
$sql = ['CREATE DATABASE']; |
106
|
|
|
$sql[] = $this->quote($dbname); |
107
|
|
|
|
108
|
|
|
if($collate){ |
|
|
|
|
109
|
|
|
$charset = explode(',', $collate, 3); |
110
|
|
|
|
111
|
|
|
$count = count($charset); |
112
|
|
|
|
113
|
|
|
if($count > 0){ |
114
|
|
|
$sql[] = 'ENCODING \''.strtoupper($charset[0]).'\''; |
115
|
|
|
} |
116
|
|
|
|
117
|
|
|
if($count > 1){ |
118
|
|
|
$sql[] = 'LC_COLLATE=\''.$charset[1].'\''; |
119
|
|
|
} |
120
|
|
|
|
121
|
|
|
if($count > 2){ |
122
|
|
|
$sql[] = 'LC_CTYPE=\''.$charset[2].'\''; |
123
|
|
|
} |
124
|
|
|
|
125
|
|
|
} |
126
|
|
|
|
127
|
|
|
return $sql; |
128
|
|
|
} |
129
|
|
|
|
130
|
|
|
/** @inheritdoc */ |
131
|
|
|
public function fieldspec(string $name, string $type, $length = null, string $attribute = null, string $collation = null, bool $isNull = null, string $defaultType = null, $defaultValue = null, string $extra = null):string{ |
132
|
|
|
$name = trim($name); |
133
|
|
|
$type = strtoupper(trim($type)); |
134
|
|
|
|
135
|
|
|
$field = [$this->quote($name)]; |
136
|
|
|
|
137
|
|
|
$type_translation = [ |
138
|
|
|
'TINYINT' => 'SMALLINT', |
139
|
|
|
'MEDIUMINT' => 'INT', |
140
|
|
|
'DOUBLE' => 'DOUBLE PRECISION', |
141
|
|
|
'TINYTEXT' => 'VARCHAR(255)', |
142
|
|
|
'DATETIME' => 'TIMESTAMP', |
143
|
|
|
'IMAGE' => 'BLOB', |
144
|
|
|
'MEDIUMTEXT' => 'TEXT', |
145
|
|
|
'LONGTEXT' => 'TEXT', |
146
|
|
|
][$type] ?? $type; |
147
|
|
|
|
148
|
|
View Code Duplication |
if((is_int($length) || is_string($length) && count(explode(',', $length)) === 2) |
|
|
|
|
149
|
|
|
&& in_array($type, ['BIT', 'VARBIT', 'CHAR', 'VARCHAR', 'DECIMAL', 'NUMERIC', 'TIME', 'TIMESTAMP', 'INTERVAL'], true)){ |
150
|
|
|
$field[] = $type_translation.'('.$length.')'; |
151
|
|
|
} |
152
|
|
|
else{ |
153
|
|
|
$field[] = $type_translation; |
154
|
|
|
} |
155
|
|
|
|
156
|
|
|
if($collation && in_array($type, ['TINYTEXT', 'TEXT', 'MEDIUMTEXT', 'LONGTEXT', 'VARCHAR', 'CHAR'], true) |
|
|
|
|
157
|
|
|
&& !in_array(strtolower($collation), ['utf8'], true)){ |
158
|
|
|
$field[] = 'COLLATE '.$collation; |
159
|
|
|
} |
160
|
|
|
|
161
|
|
|
if($isNull !== true){ |
162
|
|
|
$field[] = 'NOT NULL'; |
163
|
|
|
} |
164
|
|
|
|
165
|
|
|
if($attribute){ |
|
|
|
|
166
|
|
|
$field[] = strtoupper($attribute); |
167
|
|
|
} |
168
|
|
|
|
169
|
|
|
$defaultType = strtoupper($defaultType); |
170
|
|
|
|
171
|
|
|
if($defaultType === 'USER_DEFINED'){ |
172
|
|
|
|
173
|
|
|
switch(true){ |
174
|
|
|
case $type === 'TIMESTAMP' && intval($defaultValue) === 0: |
|
|
|
|
175
|
|
|
$field[] = 'DEFAULT 0'; |
176
|
|
|
break; |
177
|
|
|
case $type === 'BIT' || $type === 'VARBIT': |
|
|
|
|
178
|
|
|
$field[] = 'DEFAULT b\''.preg_replace('/[^01]/', '0', $defaultValue).'\''; |
179
|
|
|
break; |
180
|
|
|
case $type === 'BOOLEAN': |
181
|
|
|
$field[] = 'DEFAULT '.preg_match('/^1|T|TRUE|YES$/i', $defaultValue) ? 'TRUE' : 'FALSE'; |
182
|
|
|
break; |
183
|
|
View Code Duplication |
case strtoupper($defaultValue) === 'NULL' && $isNull === true: |
|
|
|
|
184
|
|
|
$field[] = 'DEFAULT NULL'; |
185
|
|
|
break; |
186
|
|
|
default: |
187
|
|
|
$field[] = 'DEFAULT \''.$defaultValue.'\''; |
188
|
|
|
} |
189
|
|
|
|
190
|
|
|
} |
191
|
|
|
elseif($defaultType === 'CURRENT_TIMESTAMP'){ |
192
|
|
|
$field[] = 'DEFAULT CURRENT_TIMESTAMP'; |
193
|
|
|
} |
194
|
|
|
elseif($defaultType === 'NULL' && $isNull === true){ |
195
|
|
|
$field[] = 'DEFAULT NULL'; |
196
|
|
|
} |
197
|
|
|
|
198
|
|
|
if($extra){ |
|
|
|
|
199
|
|
|
$field[] = $extra; |
200
|
|
|
} |
201
|
|
|
|
202
|
|
|
return implode(' ', $field); |
203
|
|
|
} |
204
|
|
|
|
205
|
|
|
/** @inheritdoc */ |
206
|
|
|
public function createTable(string $table, array $cols, string $primaryKey = null, bool $ifNotExists, bool $temp, string $dir = null):array{ |
207
|
|
|
$sql[] = 'CREATE'; |
|
|
|
|
208
|
|
|
|
209
|
|
|
if($temp){ |
210
|
|
|
$sql[] = 'TEMPORARY'; |
211
|
|
|
} |
212
|
|
|
|
213
|
|
|
$sql[] = 'TABLE'; |
214
|
|
|
|
215
|
|
|
if($ifNotExists){ |
216
|
|
|
$sql[] = 'IF NOT EXISTS'; |
217
|
|
|
} |
218
|
|
|
|
219
|
|
|
$n = explode('.', $table); |
220
|
|
|
|
221
|
|
|
$sql[] = $this->quote($n[count($n) - 1]); |
222
|
|
|
|
223
|
|
View Code Duplication |
if(!empty($cols)){ |
|
|
|
|
224
|
|
|
$sql[] = ' ('; |
225
|
|
|
$sql[] = implode(', ', $cols); |
226
|
|
|
|
227
|
|
|
if($primaryKey){ |
|
|
|
|
228
|
|
|
$sql[] = ','.'PRIMARY KEY ('.$this->quote($primaryKey).')'; |
229
|
|
|
} |
230
|
|
|
|
231
|
|
|
$sql[] = ')'; |
232
|
|
|
} |
233
|
|
|
|
234
|
|
|
return $sql; |
235
|
|
|
} |
236
|
|
|
|
237
|
|
|
/** @inheritdoc */ |
238
|
|
|
public function showDatabases():array{ |
239
|
|
|
/** @noinspection SqlResolve */ |
240
|
|
|
return ['SELECT datname AS "Database" FROM pg_database']; |
241
|
|
|
} |
242
|
|
|
|
243
|
|
|
/** @inheritdoc */ |
244
|
|
|
public function showTables(string $database = null, string $pattern = null, string $where = null):array{ |
245
|
|
|
/** @noinspection SqlResolve */ |
246
|
|
|
return ['SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname != \'pg_catalog\'AND schemaname != \'information_schema\' ']; |
247
|
|
|
} |
248
|
|
|
|
249
|
|
|
/** |
250
|
|
|
* @link https://stackoverflow.com/a/16154183 |
251
|
|
|
* |
252
|
|
|
* @param string $table |
253
|
|
|
* |
254
|
|
|
* @return array |
255
|
|
|
*/ |
256
|
|
|
public function showCreateTable(string $table):array{ |
257
|
|
|
|
258
|
|
|
/** @noinspection SqlResolve */ |
259
|
|
|
$def = $this->db->prepared('SELECT |
260
|
|
|
a.attnum AS "id", |
261
|
|
|
a.attname AS "name", |
262
|
|
|
pg_catalog.format_type(a.atttypid, a.atttypmod) AS "type", |
263
|
|
|
CASE WHEN a.attnotnull = TRUE |
264
|
|
|
THEN \'NOT NULL\' |
265
|
|
|
ELSE \'\' END AS "isnull", |
266
|
|
|
CASE WHEN ( |
267
|
|
|
SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) FOR 128) |
268
|
|
|
FROM pg_catalog.pg_attrdef d |
269
|
|
|
WHERE |
270
|
|
|
d.adrelid = a.attrelid |
271
|
|
|
AND d.adnum = a.attnum |
272
|
|
|
AND a.atthasdef |
273
|
|
|
) IS NOT NULL |
274
|
|
|
THEN \'DEFAULT \' || ( |
275
|
|
|
SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) FOR 128) |
276
|
|
|
FROM pg_catalog.pg_attrdef d |
277
|
|
|
WHERE |
278
|
|
|
d.adrelid = a.attrelid |
279
|
|
|
AND d.adnum = a.attnum |
280
|
|
|
AND a.atthasdef |
281
|
|
|
) |
282
|
|
|
ELSE \'\' END AS "default", |
283
|
|
|
( |
284
|
|
|
SELECT collation_name |
285
|
|
|
FROM information_schema.columns |
286
|
|
|
WHERE |
287
|
|
|
columns.table_name = b.relname |
288
|
|
|
AND columns.column_name = a.attname |
289
|
|
|
) AS "collation", |
290
|
|
|
( |
291
|
|
|
SELECT c.relname |
292
|
|
|
FROM pg_catalog.pg_class AS c, pg_attribute AS at, pg_catalog.pg_index AS i, pg_catalog.pg_class c2 |
293
|
|
|
WHERE |
294
|
|
|
c.relkind = \'i\' |
295
|
|
|
AND at.attrelid = c.oid |
296
|
|
|
AND i.indexrelid = c.oid |
297
|
|
|
AND i.indrelid = c2.oid |
298
|
|
|
AND c2.relname = b.relname |
299
|
|
|
AND at.attnum = a.attnum |
300
|
|
|
) AS "index" |
301
|
|
|
FROM |
302
|
|
|
pg_catalog.pg_attribute AS a |
303
|
|
|
INNER JOIN |
304
|
|
|
( |
305
|
|
|
SELECT |
306
|
|
|
c.oid, |
307
|
|
|
n.nspname, |
308
|
|
|
c.relname |
309
|
|
|
FROM pg_catalog.pg_class AS c, pg_catalog.pg_namespace AS n |
310
|
|
|
WHERE |
311
|
|
|
pg_catalog.pg_table_is_visible(c.oid) |
312
|
|
|
AND n.oid = c.relnamespace |
313
|
|
|
AND c.relname = ? |
314
|
|
|
ORDER BY 2, 3) b |
315
|
|
|
ON a.attrelid = b.oid |
316
|
|
|
INNER JOIN |
317
|
|
|
( |
318
|
|
|
SELECT a.attrelid |
319
|
|
|
FROM pg_catalog.pg_attribute a |
320
|
|
|
WHERE |
321
|
|
|
a.attnum > 0 |
322
|
|
|
AND NOT a.attisdropped |
323
|
|
|
GROUP BY a.attrelid |
324
|
|
|
) AS e |
325
|
|
|
ON a.attrelid = e.attrelid |
326
|
|
|
WHERE a.attnum > 0 |
327
|
|
|
AND NOT a.attisdropped |
328
|
|
|
ORDER BY a.attnum', [$table]); |
329
|
|
|
|
330
|
|
|
foreach($def as $field){ |
|
|
|
|
331
|
|
|
// @todo primary key/indices |
332
|
|
|
$fields[] = $this->fieldspec(trim($field->name), trim($field->type), null, null, null, $field->isnull !== 'NOT NULL', null, null, trim($field->default)); |
|
|
|
|
333
|
|
|
} |
334
|
|
|
|
335
|
|
|
$create = sprintf(/** @lang text */'CREATE TABLE %1$s (%2$s)', $this->quote($table), PHP_EOL.implode(','.PHP_EOL, $fields).PHP_EOL); |
|
|
|
|
336
|
|
|
|
337
|
|
|
$this->db->prepared('CREATE TEMPORARY TABLE IF NOT EXISTS TEMP$SQL_CREATE ("name" TEXT, "create" TEXT) ON COMMIT PRESERVE ROWS'); |
338
|
|
|
$this->db->prepared('TRUNCATE TEMP$SQL_CREATE'); |
339
|
|
|
$this->db->prepared('INSERT INTO TEMP$SQL_CREATE ("name", "create") VALUES (?, ?)', [$table, $create]); |
340
|
|
|
|
341
|
|
|
return ['SELECT "name" AS "Table", "create" AS "Create Table" FROM TEMP$SQL_CREATE']; |
342
|
|
|
} |
343
|
|
|
|
344
|
|
|
} |
345
|
|
|
|
In PHP, under loose comparison (like
==
, or!=
, orswitch
conditions), values of different types might be equal.For
string
values, the empty string''
is a special case, in particular the following results might be unexpected: