1
|
|
|
<?php declare(strict_types=1); |
2
|
|
|
|
3
|
|
|
/** |
4
|
|
|
* ___ _ _ |
5
|
|
|
* | _ \__ _| |_ __ _| |__ __ _ ___ ___ |
6
|
|
|
* | _/ _` | _/ _` | '_ \/ _` (_-</ -_) |
7
|
|
|
* |_| \__,_|\__\__,_|_.__/\__,_/__/\___| |
8
|
|
|
* |
9
|
|
|
* This file is part of Kristuff\Patabase. |
10
|
|
|
* (c) Kristuff <[email protected]> |
11
|
|
|
* |
12
|
|
|
* For the full copyright and license information, please view the LICENSE |
13
|
|
|
* file that was distributed with this source code. |
14
|
|
|
* |
15
|
|
|
* @version 1.0.1 |
16
|
|
|
* @copyright 2017-2022 Christophe Buliard |
17
|
|
|
*/ |
18
|
|
|
|
19
|
|
|
namespace Kristuff\Patabase\Driver\Postgres; |
20
|
|
|
|
21
|
|
|
use Kristuff\Patabase\Driver\ServerDriver; |
22
|
|
|
|
23
|
|
|
/** |
24
|
|
|
* Class Driver |
25
|
|
|
* |
26
|
|
|
* Postgres Sql Driver |
27
|
|
|
* |
28
|
|
|
* Data types (main): |
29
|
|
|
* [Numeric Types] |
30
|
|
|
* smallint, integer, bigint, (serial/bigserial) |
31
|
|
|
* decimal, numeric |
32
|
|
|
* real, double |
33
|
|
|
* [Character Types] |
34
|
|
|
* character varying(n), varchar(n) |
35
|
|
|
* character(n), char(n) |
36
|
|
|
* text |
37
|
|
|
* [Binary Data Types] |
38
|
|
|
* bytea |
39
|
|
|
* [Date/Time Types] |
40
|
|
|
* timestamp, time, date, interval |
41
|
|
|
* [Boolean Type] |
42
|
|
|
* boolean (literal: TRUE 't' 'true' 'y' 'yes' 'on' '1' | FALSE 'f' 'false' 'n' 'no' 'off' '0') |
43
|
|
|
* [Geometric Types] |
44
|
|
|
* [Network Address Types] |
45
|
|
|
* [Bit String Types] |
46
|
|
|
* bit(n), bit varying(n) cast... |
47
|
|
|
* [UUID Type] |
48
|
|
|
* uuid |
49
|
|
|
*/ |
50
|
|
|
class PostgresDriver extends ServerDriver |
51
|
|
|
{ |
52
|
|
|
/** |
53
|
|
|
* List of DSN attributes |
54
|
|
|
* |
55
|
|
|
* @access protected |
56
|
|
|
* @var array |
57
|
|
|
*/ |
58
|
|
|
protected $dsnAttributes = array( |
59
|
|
|
'hostname', |
60
|
|
|
'username', |
61
|
|
|
'password', |
62
|
|
|
'database' |
63
|
|
|
); |
64
|
|
|
|
65
|
|
|
/** |
66
|
|
|
* Escape an identifier |
67
|
|
|
* |
68
|
|
|
* @access public |
69
|
|
|
* @param string $identifier |
70
|
|
|
* |
71
|
|
|
* @return string |
72
|
|
|
*/ |
73
|
|
|
public function escapeIdentifier(string $identifier) : string |
74
|
|
|
{ |
75
|
|
|
return '"'.$identifier.'"'; |
76
|
|
|
} |
77
|
|
|
|
78
|
|
|
/** |
79
|
|
|
* Escape a value |
80
|
|
|
* |
81
|
|
|
* @access public |
82
|
|
|
* @param string $value |
83
|
|
|
* |
84
|
|
|
* @return string |
85
|
|
|
*/ |
86
|
|
|
public function escapeValue(string $value): string |
87
|
|
|
{ |
88
|
|
|
return "'".$value."'"; |
89
|
|
|
} |
90
|
|
|
|
91
|
|
|
/** |
92
|
|
|
* Create a new PDO connection |
93
|
|
|
* |
94
|
|
|
* @access public |
95
|
|
|
* @param array $settings |
96
|
|
|
* |
97
|
|
|
* @return void |
98
|
|
|
*/ |
99
|
|
|
public function createConnection(array $settings): void |
100
|
|
|
{ |
101
|
|
|
$port = !empty($settings['port']) ? ';port='.$settings['port'] : ''; |
102
|
|
|
$dbname = !empty($settings['database']) ? ';dbname='.$settings['database'] : ''; |
103
|
|
|
$dsn = 'pgsql:host='.$settings['hostname'] .$port .$dbname ; |
104
|
|
|
|
105
|
|
|
$this->pdo = new \PDO( |
106
|
|
|
$dsn, |
107
|
|
|
$settings['username'], |
108
|
|
|
$settings['password'], |
109
|
|
|
array() |
110
|
|
|
); |
111
|
|
|
|
112
|
|
|
// make sure emulate prepare is false |
113
|
|
|
//$this->pdo->setAttribute(\PDO::ATTR_EMULATE_PREPARES, false); |
114
|
|
|
//$this->pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION); |
115
|
|
|
} |
116
|
|
|
|
117
|
|
|
/** |
118
|
|
|
* Get last inserted id |
119
|
|
|
* |
120
|
|
|
* @access public |
121
|
|
|
* @return string |
122
|
|
|
*/ |
123
|
|
|
public function lastInsertedId(): string |
124
|
|
|
{ |
125
|
|
|
// Postgres does not set pdo->lastInsertedId |
126
|
|
|
// use sequence |
127
|
|
|
try { |
128
|
|
|
$rq = $this->pdo->prepare('SELECT LASTVAL()'); |
129
|
|
|
$rq->execute(); |
130
|
|
|
// return string |
131
|
|
|
return strval($rq->fetchColumn()); |
132
|
|
|
} |
133
|
|
|
catch (\PDOException $e) { |
134
|
|
|
return 0; |
135
|
|
|
} |
136
|
|
|
} |
137
|
|
|
|
138
|
|
|
/** |
139
|
|
|
* Enable foreign keys |
140
|
|
|
* |
141
|
|
|
* @access public |
142
|
|
|
* @return void |
143
|
|
|
*/ |
144
|
|
|
public function enableForeignKeys(): void |
145
|
|
|
{ |
146
|
|
|
} |
147
|
|
|
|
148
|
|
|
/** |
149
|
|
|
* Disable foreign keys |
150
|
|
|
* |
151
|
|
|
* @access public |
152
|
|
|
* @return void |
153
|
|
|
*/ |
154
|
|
|
public function disableForeignKeys(): void |
155
|
|
|
{ |
156
|
|
|
} |
157
|
|
|
|
158
|
|
|
/** |
159
|
|
|
* Get whether foreign keys are enabled or not |
160
|
|
|
* For compatibility with Sqlite, not implemented in that driver (allways enabled), return true |
161
|
|
|
|
162
|
|
|
* @access public |
163
|
|
|
* @return bool true if foreign keys are enabled, otherwise false |
164
|
|
|
*/ |
165
|
|
|
public function isForeignKeyEnabled(): bool |
166
|
|
|
{ |
167
|
|
|
return true; |
168
|
|
|
} |
169
|
|
|
|
170
|
|
|
/** |
171
|
|
|
* Add a foreign key |
172
|
|
|
* |
173
|
|
|
* @access public |
174
|
|
|
* @param string $fkName The constraint name |
175
|
|
|
* @param string $srcTable The source table |
176
|
|
|
* @param string $srcColumn The source column |
177
|
|
|
* @param string $refTable The referenced table |
178
|
|
|
* @param string $refColumn The referenced column |
179
|
|
|
* |
180
|
|
|
* @return bool True if the foreign key has been created, otherwise false |
181
|
|
|
*/ |
182
|
|
|
public function addForeignKey(string $fkName, string $srcTable, string $srcColumn, string $refTable, string $refColumn): bool |
183
|
|
|
{ |
184
|
|
|
$sql = sprintf('ALTER TABLE %s ADD CONSTRAINT %s FOREIGN KEY (%s) REFERENCES %s(%s)', |
185
|
|
|
$this->escape($srcTable), |
186
|
|
|
$fkName, |
187
|
|
|
$this->escape($srcColumn), |
188
|
|
|
$this->escape($refTable), |
189
|
|
|
$this->escape($refColumn) |
190
|
|
|
); |
191
|
|
|
return $this->prepareAndExecuteSql($sql); |
192
|
|
|
} |
193
|
|
|
|
194
|
|
|
/** |
195
|
|
|
* Drop a foreign key |
196
|
|
|
* |
197
|
|
|
* @access public |
198
|
|
|
* @param string $fkName The constraint name |
199
|
|
|
* @param string $tableName The source table |
200
|
|
|
* |
201
|
|
|
* @return bool True if the foreign key has been dropped, otherwise false |
202
|
|
|
*/ |
203
|
|
|
public function dropForeignKey(string $fkName, string $tableName, bool $ifExists = false): bool |
204
|
|
|
{ |
205
|
|
|
$sql = sprintf('ALTER TABLE %s DROP CONSTRAINT %s %s', |
206
|
|
|
$this->escape($tableName), |
207
|
|
|
$ifExists ? 'IF EXISTS' : '', |
208
|
|
|
$fkName |
209
|
|
|
); |
210
|
|
|
return $this->prepareAndExecuteSql($sql); |
211
|
|
|
} |
212
|
|
|
|
213
|
|
|
/** |
214
|
|
|
* Checks if a database exists |
215
|
|
|
* |
216
|
|
|
* @access public |
217
|
|
|
* @param string $databaseName The database name |
218
|
|
|
* |
219
|
|
|
* @return bool True if the given database exists, otherwise false. |
220
|
|
|
*/ |
221
|
|
|
public function databaseExists(string $databaseName): bool |
222
|
|
|
{ |
223
|
|
|
$sql = 'SELECT COUNT(*) FROM pg_database WHERE datname = :dbName'; |
224
|
|
|
$query = $this->pdo->prepare($sql); |
225
|
|
|
$query->bindValue(':dbName', $databaseName, \PDO::PARAM_STR); |
226
|
|
|
$query->execute(); |
227
|
|
|
return (bool) $query->fetchColumn(); |
228
|
|
|
} |
229
|
|
|
|
230
|
|
|
/** |
231
|
|
|
* Create a database |
232
|
|
|
* |
233
|
|
|
* @access public |
234
|
|
|
* @param string $databaseName The database name |
235
|
|
|
* @param string $owner The database owner. |
236
|
|
|
* @param string $template The template to use. Default is 'template0' |
237
|
|
|
* |
238
|
|
|
* @return bool True if the database has been created, otherwise false. |
239
|
|
|
*/ |
240
|
|
|
public function createDatabase(string $databaseName, ?string $owner= null, ?string $template = 'template0'): bool |
241
|
|
|
{ |
242
|
|
|
$sql = trim(sprintf('CREATE DATABASE %s %s TEMPLATE %s', |
243
|
|
|
$this->escape($databaseName), |
244
|
|
|
isset($owner) ? 'OWNER '. $this->escape($owner) : '', |
245
|
|
|
$template |
246
|
|
|
)); |
247
|
|
|
return $this->prepareAndExecuteSql($sql); |
248
|
|
|
} |
249
|
|
|
|
250
|
|
|
/** |
251
|
|
|
* Create a user |
252
|
|
|
* |
253
|
|
|
* @access public |
254
|
|
|
* @param string $userName The user name |
255
|
|
|
* @param string $userpassword The user password |
256
|
|
|
* |
257
|
|
|
* @return bool True if the user has been created, otherwise false. |
258
|
|
|
*/ |
259
|
|
|
public function createUser(string $userName, string $userPassword): bool |
260
|
|
|
{ |
261
|
|
|
$sql = trim(sprintf('CREATE USER %s PASSWORD %s', |
262
|
|
|
$this->escape($userName), |
263
|
|
|
"'" . $userPassword ."'" |
264
|
|
|
)); |
265
|
|
|
return $this->prepareAndExecuteSql($sql); |
266
|
|
|
} |
267
|
|
|
|
268
|
|
|
/** |
269
|
|
|
* Drop a user |
270
|
|
|
* |
271
|
|
|
* @access public |
272
|
|
|
* @param string $userName The user name |
273
|
|
|
* @param bool $ifExists (optional) True if the user must be deleted only when exists. Default is false. |
274
|
|
|
* |
275
|
|
|
* @return bool True if the user has been dropped or does not exist when $ifExists is set to True, otherwise false. |
276
|
|
|
*/ |
277
|
|
|
public function dropUser(string $userName, bool $ifExists = false): bool |
278
|
|
|
{ |
279
|
|
|
$sql = trim(sprintf('DROP USER %s %s', |
280
|
|
|
$ifExists === true ? 'IF EXISTS': '', |
281
|
|
|
$this->escape($userName) |
282
|
|
|
)); |
283
|
|
|
return $this->prepareAndExecuteSql($sql); |
284
|
|
|
} |
285
|
|
|
|
286
|
|
|
/** |
287
|
|
|
* Grant user permissions on given database |
288
|
|
|
* |
289
|
|
|
* @access public |
290
|
|
|
* @param string $databaseName The database name |
291
|
|
|
* @param string $userName The user name |
292
|
|
|
* |
293
|
|
|
* @return bool True if the user has been granted, otherwise false. |
294
|
|
|
*/ |
295
|
|
|
public function grantUser(string $databaseName, string $userName): bool |
296
|
|
|
{ |
297
|
|
|
// ALL PRIVILEGES Grant all of the available privileges at once. The PRIVILEGES keyword |
298
|
|
|
// is optional in PostgreSQL, though it is required by strict SQL. |
299
|
|
|
|
300
|
|
|
// GRANT CONNECT ON DATABASE database_name TO user_name; |
301
|
|
|
//$sql = trim(sprintf('GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA %s TO %s;', |
302
|
|
|
$sql = trim(sprintf('GRANT CONNECT ON DATABASE %s TO %s;', |
303
|
|
|
$this->escape($databaseName), |
304
|
|
|
$this->escape($userName) |
305
|
|
|
)); |
306
|
|
|
return $this->prepareAndExecuteSql($sql); |
307
|
|
|
} |
308
|
|
|
|
309
|
|
|
/** |
310
|
|
|
* Get the SQL for show databases |
311
|
|
|
* |
312
|
|
|
* @access public |
313
|
|
|
* @return string |
314
|
|
|
*/ |
315
|
|
|
public function sqlShowDatabases(): string |
316
|
|
|
{ |
317
|
|
|
return 'SELECT datname FROM pg_database WHERE datistemplate = false;'; |
318
|
|
|
} |
319
|
|
|
|
320
|
|
|
/** |
321
|
|
|
* Get the SQL for show tables |
322
|
|
|
* |
323
|
|
|
* @access public |
324
|
|
|
* @return string |
325
|
|
|
*/ |
326
|
|
|
public function sqlShowTables(): string |
327
|
|
|
{ |
328
|
|
|
return "SELECT table_name FROM information_schema.tables WHERE table_schema='public' AND table_type = 'BASE TABLE' ORDER BY table_name;"; |
329
|
|
|
} |
330
|
|
|
|
331
|
|
|
/** |
332
|
|
|
* Get the SQL for show users |
333
|
|
|
* |
334
|
|
|
* @access public |
335
|
|
|
* @return string |
336
|
|
|
*/ |
337
|
|
|
public function sqlShowUsers(): string |
338
|
|
|
{ |
339
|
|
|
return 'SELECT usename FROM pg_user'; |
340
|
|
|
} |
341
|
|
|
|
342
|
|
|
/** |
343
|
|
|
* Get the SQL for random function |
344
|
|
|
* |
345
|
|
|
* Parameter $seed is not honored in Postgres. |
346
|
|
|
* |
347
|
|
|
* @access public |
348
|
|
|
* @param int $seed The random seed. Default is null. |
349
|
|
|
* |
350
|
|
|
* @return string |
351
|
|
|
*/ |
352
|
|
|
public function sqlRandom($seed = null): string |
353
|
|
|
{ |
354
|
|
|
return 'random()'; |
355
|
|
|
} |
356
|
|
|
|
357
|
|
|
/** |
358
|
|
|
* Get the SQL for auto increment column |
359
|
|
|
* |
360
|
|
|
* @access public |
361
|
|
|
* @param string $type The sql column type |
362
|
|
|
* |
363
|
|
|
* @return string |
364
|
|
|
*/ |
365
|
|
|
public function sqlColumnAutoIncrement(string $type): string |
366
|
|
|
{ |
367
|
|
|
// SERIAL/BIGSERIAL is a type in postgres |
368
|
|
|
return strtolower($type) === 'bigint' ? 'bigserial' : 'serial'; |
369
|
|
|
} |
370
|
|
|
|
371
|
|
|
|
372
|
|
|
|
373
|
|
|
} |