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