1
|
|
|
<?php |
2
|
|
|
/** |
3
|
|
|
* This is the Postgres database abstraction layer. |
4
|
|
|
* |
5
|
|
|
* This program is free software; you can redistribute it and/or modify |
6
|
|
|
* it under the terms of the GNU General Public License as published by |
7
|
|
|
* the Free Software Foundation; either version 2 of the License, or |
8
|
|
|
* (at your option) any later version. |
9
|
|
|
* |
10
|
|
|
* This program is distributed in the hope that it will be useful, |
11
|
|
|
* but WITHOUT ANY WARRANTY; without even the implied warranty of |
12
|
|
|
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the |
13
|
|
|
* GNU General Public License for more details. |
14
|
|
|
* |
15
|
|
|
* You should have received a copy of the GNU General Public License along |
16
|
|
|
* with this program; if not, write to the Free Software Foundation, Inc., |
17
|
|
|
* 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. |
18
|
|
|
* http://www.gnu.org/copyleft/gpl.html |
19
|
|
|
* |
20
|
|
|
* @file |
21
|
|
|
* @ingroup Database |
22
|
|
|
*/ |
23
|
|
|
use Wikimedia\WaitConditionLoop; |
24
|
|
|
|
25
|
|
|
/** |
26
|
|
|
* @ingroup Database |
27
|
|
|
*/ |
28
|
|
|
class DatabasePostgres extends Database { |
29
|
|
|
/** @var int|bool */ |
30
|
|
|
protected $port; |
31
|
|
|
|
32
|
|
|
/** @var resource */ |
33
|
|
|
protected $mLastResult = null; |
34
|
|
|
/** @var int The number of rows affected as an integer */ |
35
|
|
|
protected $mAffectedRows = null; |
36
|
|
|
|
37
|
|
|
/** @var int */ |
38
|
|
|
private $mInsertId = null; |
39
|
|
|
/** @var float|string */ |
40
|
|
|
private $numericVersion = null; |
41
|
|
|
/** @var string Connect string to open a PostgreSQL connection */ |
42
|
|
|
private $connectString; |
43
|
|
|
/** @var string */ |
44
|
|
|
private $mCoreSchema; |
45
|
|
|
|
46
|
|
|
public function __construct( array $params ) { |
47
|
|
|
$this->port = isset( $params['port'] ) ? $params['port'] : false; |
48
|
|
|
parent::__construct( $params ); |
49
|
|
|
} |
50
|
|
|
|
51
|
|
|
public function getType() { |
52
|
|
|
return 'postgres'; |
53
|
|
|
} |
54
|
|
|
|
55
|
|
|
public function implicitGroupby() { |
56
|
|
|
return false; |
57
|
|
|
} |
58
|
|
|
|
59
|
|
|
public function implicitOrderby() { |
60
|
|
|
return false; |
61
|
|
|
} |
62
|
|
|
|
63
|
|
|
public function hasConstraint( $name ) { |
64
|
|
|
$conn = $this->getBindingHandle(); |
65
|
|
|
|
66
|
|
|
$sql = "SELECT 1 FROM pg_catalog.pg_constraint c, pg_catalog.pg_namespace n " . |
67
|
|
|
"WHERE c.connamespace = n.oid AND conname = '" . |
68
|
|
|
pg_escape_string( $conn, $name ) . "' AND n.nspname = '" . |
69
|
|
|
pg_escape_string( $conn, $this->getCoreSchema() ) . "'"; |
70
|
|
|
$res = $this->doQuery( $sql ); |
71
|
|
|
|
72
|
|
|
return $this->numRows( $res ); |
73
|
|
|
} |
74
|
|
|
|
75
|
|
|
public function open( $server, $user, $password, $dbName ) { |
76
|
|
|
# Test for Postgres support, to avoid suppressed fatal error |
77
|
|
|
if ( !function_exists( 'pg_connect' ) ) { |
78
|
|
|
throw new DBConnectionError( |
79
|
|
|
$this, |
80
|
|
|
"Postgres functions missing, have you compiled PHP with the --with-pgsql\n" . |
81
|
|
|
"option? (Note: if you recently installed PHP, you may need to restart your\n" . |
82
|
|
|
"webserver and database)\n" |
83
|
|
|
); |
84
|
|
|
} |
85
|
|
|
|
86
|
|
|
$this->mServer = $server; |
87
|
|
|
$this->mUser = $user; |
88
|
|
|
$this->mPassword = $password; |
89
|
|
|
$this->mDBname = $dbName; |
90
|
|
|
|
91
|
|
|
$connectVars = [ |
92
|
|
|
'dbname' => $dbName, |
93
|
|
|
'user' => $user, |
94
|
|
|
'password' => $password |
95
|
|
|
]; |
96
|
|
|
if ( $server != false && $server != '' ) { |
|
|
|
|
97
|
|
|
$connectVars['host'] = $server; |
98
|
|
|
} |
99
|
|
|
if ( (int)$this->port > 0 ) { |
100
|
|
|
$connectVars['port'] = (int)$this->port; |
101
|
|
|
} |
102
|
|
|
if ( $this->mFlags & self::DBO_SSL ) { |
103
|
|
|
$connectVars['sslmode'] = 1; |
104
|
|
|
} |
105
|
|
|
|
106
|
|
|
$this->connectString = $this->makeConnectionString( $connectVars ); |
107
|
|
|
$this->close(); |
108
|
|
|
$this->installErrorHandler(); |
109
|
|
|
|
110
|
|
|
try { |
111
|
|
|
// Use new connections to let LoadBalancer/LBFactory handle reuse |
112
|
|
|
$this->mConn = pg_connect( $this->connectString, PGSQL_CONNECT_FORCE_NEW ); |
113
|
|
|
} catch ( Exception $ex ) { |
114
|
|
|
$this->restoreErrorHandler(); |
115
|
|
|
throw $ex; |
116
|
|
|
} |
117
|
|
|
|
118
|
|
|
$phpError = $this->restoreErrorHandler(); |
119
|
|
|
|
120
|
|
|
if ( !$this->mConn ) { |
121
|
|
|
$this->queryLogger->debug( |
122
|
|
|
"DB connection error\n" . |
123
|
|
|
"Server: $server, Database: $dbName, User: $user, Password: " . |
124
|
|
|
substr( $password, 0, 3 ) . "...\n" |
125
|
|
|
); |
126
|
|
|
$this->queryLogger->debug( $this->lastError() . "\n" ); |
127
|
|
|
throw new DBConnectionError( $this, str_replace( "\n", ' ', $phpError ) ); |
128
|
|
|
} |
129
|
|
|
|
130
|
|
|
$this->mOpened = true; |
131
|
|
|
|
132
|
|
|
# If called from the command-line (e.g. importDump), only show errors |
133
|
|
|
if ( $this->cliMode ) { |
134
|
|
|
$this->doQuery( "SET client_min_messages = 'ERROR'" ); |
135
|
|
|
} |
136
|
|
|
|
137
|
|
|
$this->query( "SET client_encoding='UTF8'", __METHOD__ ); |
138
|
|
|
$this->query( "SET datestyle = 'ISO, YMD'", __METHOD__ ); |
139
|
|
|
$this->query( "SET timezone = 'GMT'", __METHOD__ ); |
140
|
|
|
$this->query( "SET standard_conforming_strings = on", __METHOD__ ); |
141
|
|
|
if ( $this->getServerVersion() >= 9.0 ) { |
142
|
|
|
$this->query( "SET bytea_output = 'escape'", __METHOD__ ); // PHP bug 53127 |
143
|
|
|
} |
144
|
|
|
|
145
|
|
|
$this->determineCoreSchema( $this->mSchema ); |
146
|
|
|
// The schema to be used is now in the search path; no need for explicit qualification |
147
|
|
|
$this->mSchema = ''; |
148
|
|
|
|
149
|
|
|
return $this->mConn; |
150
|
|
|
} |
151
|
|
|
|
152
|
|
|
/** |
153
|
|
|
* Postgres doesn't support selectDB in the same way MySQL does. So if the |
154
|
|
|
* DB name doesn't match the open connection, open a new one |
155
|
|
|
* @param string $db |
156
|
|
|
* @return bool |
157
|
|
|
*/ |
158
|
|
|
public function selectDB( $db ) { |
159
|
|
|
if ( $this->mDBname !== $db ) { |
160
|
|
|
return (bool)$this->open( $this->mServer, $this->mUser, $this->mPassword, $db ); |
161
|
|
|
} else { |
162
|
|
|
return true; |
163
|
|
|
} |
164
|
|
|
} |
165
|
|
|
|
166
|
|
|
/** |
167
|
|
|
* @param string[] $vars |
168
|
|
|
* @return string |
169
|
|
|
*/ |
170
|
|
|
private function makeConnectionString( $vars ) { |
171
|
|
|
$s = ''; |
172
|
|
|
foreach ( $vars as $name => $value ) { |
173
|
|
|
$s .= "$name='" . str_replace( "'", "\\'", $value ) . "' "; |
174
|
|
|
} |
175
|
|
|
|
176
|
|
|
return $s; |
177
|
|
|
} |
178
|
|
|
|
179
|
|
|
protected function closeConnection() { |
180
|
|
|
return $this->mConn ? pg_close( $this->mConn ) : true; |
181
|
|
|
} |
182
|
|
|
|
183
|
|
|
public function doQuery( $sql ) { |
184
|
|
|
$conn = $this->getBindingHandle(); |
185
|
|
|
|
186
|
|
|
$sql = mb_convert_encoding( $sql, 'UTF-8' ); |
187
|
|
|
// Clear previously left over PQresult |
188
|
|
|
while ( $res = pg_get_result( $conn ) ) { |
189
|
|
|
pg_free_result( $res ); |
190
|
|
|
} |
191
|
|
|
if ( pg_send_query( $conn, $sql ) === false ) { |
192
|
|
|
throw new DBUnexpectedError( $this, "Unable to post new query to PostgreSQL\n" ); |
193
|
|
|
} |
194
|
|
|
$this->mLastResult = pg_get_result( $conn ); |
195
|
|
|
$this->mAffectedRows = null; |
196
|
|
|
if ( pg_result_error( $this->mLastResult ) ) { |
197
|
|
|
return false; |
198
|
|
|
} |
199
|
|
|
|
200
|
|
|
return $this->mLastResult; |
201
|
|
|
} |
202
|
|
|
|
203
|
|
|
protected function dumpError() { |
204
|
|
|
$diags = [ |
205
|
|
|
PGSQL_DIAG_SEVERITY, |
206
|
|
|
PGSQL_DIAG_SQLSTATE, |
207
|
|
|
PGSQL_DIAG_MESSAGE_PRIMARY, |
208
|
|
|
PGSQL_DIAG_MESSAGE_DETAIL, |
209
|
|
|
PGSQL_DIAG_MESSAGE_HINT, |
210
|
|
|
PGSQL_DIAG_STATEMENT_POSITION, |
211
|
|
|
PGSQL_DIAG_INTERNAL_POSITION, |
212
|
|
|
PGSQL_DIAG_INTERNAL_QUERY, |
213
|
|
|
PGSQL_DIAG_CONTEXT, |
214
|
|
|
PGSQL_DIAG_SOURCE_FILE, |
215
|
|
|
PGSQL_DIAG_SOURCE_LINE, |
216
|
|
|
PGSQL_DIAG_SOURCE_FUNCTION |
217
|
|
|
]; |
218
|
|
|
foreach ( $diags as $d ) { |
219
|
|
|
$this->queryLogger->debug( sprintf( "PgSQL ERROR(%d): %s\n", |
220
|
|
|
$d, pg_result_error_field( $this->mLastResult, $d ) ) ); |
221
|
|
|
} |
222
|
|
|
} |
223
|
|
|
|
224
|
|
|
public function reportQueryError( $error, $errno, $sql, $fname, $tempIgnore = false ) { |
225
|
|
|
if ( $tempIgnore ) { |
226
|
|
|
/* Check for constraint violation */ |
227
|
|
|
if ( $errno === '23505' ) { |
|
|
|
|
228
|
|
|
parent::reportQueryError( $error, $errno, $sql, $fname, $tempIgnore ); |
229
|
|
|
|
230
|
|
|
return; |
231
|
|
|
} |
232
|
|
|
} |
233
|
|
|
/* Transaction stays in the ERROR state until rolled back */ |
234
|
|
|
if ( $this->mTrxLevel ) { |
235
|
|
|
$ignore = $this->ignoreErrors( true ); |
236
|
|
|
$this->rollback( __METHOD__ ); |
237
|
|
|
$this->ignoreErrors( $ignore ); |
238
|
|
|
} |
239
|
|
|
parent::reportQueryError( $error, $errno, $sql, $fname, false ); |
240
|
|
|
} |
241
|
|
|
|
242
|
|
View Code Duplication |
public function freeResult( $res ) { |
243
|
|
|
if ( $res instanceof ResultWrapper ) { |
244
|
|
|
$res = $res->result; |
245
|
|
|
} |
246
|
|
|
MediaWiki\suppressWarnings(); |
247
|
|
|
$ok = pg_free_result( $res ); |
248
|
|
|
MediaWiki\restoreWarnings(); |
249
|
|
|
if ( !$ok ) { |
250
|
|
|
throw new DBUnexpectedError( $this, "Unable to free Postgres result\n" ); |
251
|
|
|
} |
252
|
|
|
} |
253
|
|
|
|
254
|
|
View Code Duplication |
public function fetchObject( $res ) { |
255
|
|
|
if ( $res instanceof ResultWrapper ) { |
256
|
|
|
$res = $res->result; |
257
|
|
|
} |
258
|
|
|
MediaWiki\suppressWarnings(); |
259
|
|
|
$row = pg_fetch_object( $res ); |
260
|
|
|
MediaWiki\restoreWarnings(); |
261
|
|
|
# @todo FIXME: HACK HACK HACK HACK debug |
262
|
|
|
|
263
|
|
|
# @todo hashar: not sure if the following test really trigger if the object |
264
|
|
|
# fetching failed. |
265
|
|
|
$conn = $this->getBindingHandle(); |
266
|
|
|
if ( pg_last_error( $conn ) ) { |
267
|
|
|
throw new DBUnexpectedError( |
268
|
|
|
$this, |
269
|
|
|
'SQL error: ' . htmlspecialchars( pg_last_error( $conn ) ) |
270
|
|
|
); |
271
|
|
|
} |
272
|
|
|
|
273
|
|
|
return $row; |
274
|
|
|
} |
275
|
|
|
|
276
|
|
View Code Duplication |
public function fetchRow( $res ) { |
277
|
|
|
if ( $res instanceof ResultWrapper ) { |
278
|
|
|
$res = $res->result; |
279
|
|
|
} |
280
|
|
|
MediaWiki\suppressWarnings(); |
281
|
|
|
$row = pg_fetch_array( $res ); |
282
|
|
|
MediaWiki\restoreWarnings(); |
283
|
|
|
|
284
|
|
|
$conn = $this->getBindingHandle(); |
285
|
|
|
if ( pg_last_error( $conn ) ) { |
286
|
|
|
throw new DBUnexpectedError( |
287
|
|
|
$this, |
288
|
|
|
'SQL error: ' . htmlspecialchars( pg_last_error( $conn ) ) |
289
|
|
|
); |
290
|
|
|
} |
291
|
|
|
|
292
|
|
|
return $row; |
293
|
|
|
} |
294
|
|
|
|
295
|
|
View Code Duplication |
public function numRows( $res ) { |
296
|
|
|
if ( $res instanceof ResultWrapper ) { |
297
|
|
|
$res = $res->result; |
298
|
|
|
} |
299
|
|
|
MediaWiki\suppressWarnings(); |
300
|
|
|
$n = pg_num_rows( $res ); |
301
|
|
|
MediaWiki\restoreWarnings(); |
302
|
|
|
|
303
|
|
|
$conn = $this->getBindingHandle(); |
304
|
|
|
if ( pg_last_error( $conn ) ) { |
305
|
|
|
throw new DBUnexpectedError( |
306
|
|
|
$this, |
307
|
|
|
'SQL error: ' . htmlspecialchars( pg_last_error( $conn ) ) |
308
|
|
|
); |
309
|
|
|
} |
310
|
|
|
|
311
|
|
|
return $n; |
312
|
|
|
} |
313
|
|
|
|
314
|
|
|
public function numFields( $res ) { |
315
|
|
|
if ( $res instanceof ResultWrapper ) { |
316
|
|
|
$res = $res->result; |
317
|
|
|
} |
318
|
|
|
|
319
|
|
|
return pg_num_fields( $res ); |
320
|
|
|
} |
321
|
|
|
|
322
|
|
|
public function fieldName( $res, $n ) { |
323
|
|
|
if ( $res instanceof ResultWrapper ) { |
324
|
|
|
$res = $res->result; |
325
|
|
|
} |
326
|
|
|
|
327
|
|
|
return pg_field_name( $res, $n ); |
328
|
|
|
} |
329
|
|
|
|
330
|
|
|
/** |
331
|
|
|
* Return the result of the last call to nextSequenceValue(); |
332
|
|
|
* This must be called after nextSequenceValue(). |
333
|
|
|
* |
334
|
|
|
* @return int|null |
335
|
|
|
*/ |
336
|
|
|
public function insertId() { |
337
|
|
|
return $this->mInsertId; |
338
|
|
|
} |
339
|
|
|
|
340
|
|
|
public function dataSeek( $res, $row ) { |
341
|
|
|
if ( $res instanceof ResultWrapper ) { |
342
|
|
|
$res = $res->result; |
343
|
|
|
} |
344
|
|
|
|
345
|
|
|
return pg_result_seek( $res, $row ); |
346
|
|
|
} |
347
|
|
|
|
348
|
|
|
public function lastError() { |
349
|
|
|
if ( $this->mConn ) { |
350
|
|
|
if ( $this->mLastResult ) { |
351
|
|
|
return pg_result_error( $this->mLastResult ); |
352
|
|
|
} else { |
353
|
|
|
return pg_last_error(); |
354
|
|
|
} |
355
|
|
|
} |
356
|
|
|
|
357
|
|
|
return $this->getLastPHPError() ?: 'No database connection'; |
358
|
|
|
} |
359
|
|
|
|
360
|
|
|
public function lastErrno() { |
361
|
|
|
if ( $this->mLastResult ) { |
362
|
|
|
return pg_result_error_field( $this->mLastResult, PGSQL_DIAG_SQLSTATE ); |
363
|
|
|
} else { |
364
|
|
|
return false; |
365
|
|
|
} |
366
|
|
|
} |
367
|
|
|
|
368
|
|
|
public function affectedRows() { |
369
|
|
|
if ( !is_null( $this->mAffectedRows ) ) { |
370
|
|
|
// Forced result for simulated queries |
371
|
|
|
return $this->mAffectedRows; |
372
|
|
|
} |
373
|
|
|
if ( empty( $this->mLastResult ) ) { |
374
|
|
|
return 0; |
375
|
|
|
} |
376
|
|
|
|
377
|
|
|
return pg_affected_rows( $this->mLastResult ); |
378
|
|
|
} |
379
|
|
|
|
380
|
|
|
/** |
381
|
|
|
* Estimate rows in dataset |
382
|
|
|
* Returns estimated count, based on EXPLAIN output |
383
|
|
|
* This is not necessarily an accurate estimate, so use sparingly |
384
|
|
|
* Returns -1 if count cannot be found |
385
|
|
|
* Takes same arguments as Database::select() |
386
|
|
|
* |
387
|
|
|
* @param string $table |
388
|
|
|
* @param string $vars |
389
|
|
|
* @param string $conds |
390
|
|
|
* @param string $fname |
391
|
|
|
* @param array $options |
392
|
|
|
* @return int |
393
|
|
|
*/ |
394
|
|
View Code Duplication |
public function estimateRowCount( $table, $vars = '*', $conds = '', |
395
|
|
|
$fname = __METHOD__, $options = [] |
396
|
|
|
) { |
397
|
|
|
$options['EXPLAIN'] = true; |
398
|
|
|
$res = $this->select( $table, $vars, $conds, $fname, $options ); |
399
|
|
|
$rows = -1; |
400
|
|
|
if ( $res ) { |
401
|
|
|
$row = $this->fetchRow( $res ); |
|
|
|
|
402
|
|
|
$count = []; |
403
|
|
|
if ( preg_match( '/rows=(\d+)/', $row[0], $count ) ) { |
404
|
|
|
$rows = (int)$count[1]; |
405
|
|
|
} |
406
|
|
|
} |
407
|
|
|
|
408
|
|
|
return $rows; |
409
|
|
|
} |
410
|
|
|
|
411
|
|
|
public function indexInfo( $table, $index, $fname = __METHOD__ ) { |
412
|
|
|
$sql = "SELECT indexname FROM pg_indexes WHERE tablename='$table'"; |
413
|
|
|
$res = $this->query( $sql, $fname ); |
414
|
|
|
if ( !$res ) { |
415
|
|
|
return null; |
416
|
|
|
} |
417
|
|
|
foreach ( $res as $row ) { |
|
|
|
|
418
|
|
|
if ( $row->indexname == $this->indexName( $index ) ) { |
419
|
|
|
return $row; |
420
|
|
|
} |
421
|
|
|
} |
422
|
|
|
|
423
|
|
|
return false; |
424
|
|
|
} |
425
|
|
|
|
426
|
|
|
public function indexAttributes( $index, $schema = false ) { |
427
|
|
|
if ( $schema === false ) { |
428
|
|
|
$schema = $this->getCoreSchema(); |
429
|
|
|
} |
430
|
|
|
/* |
431
|
|
|
* A subquery would be not needed if we didn't care about the order |
432
|
|
|
* of attributes, but we do |
433
|
|
|
*/ |
434
|
|
|
$sql = <<<__INDEXATTR__ |
435
|
|
|
|
436
|
|
|
SELECT opcname, |
437
|
|
|
attname, |
438
|
|
|
i.indoption[s.g] as option, |
439
|
|
|
pg_am.amname |
440
|
|
|
FROM |
441
|
|
|
(SELECT generate_series(array_lower(isub.indkey,1), array_upper(isub.indkey,1)) AS g |
442
|
|
|
FROM |
443
|
|
|
pg_index isub |
444
|
|
|
JOIN pg_class cis |
445
|
|
|
ON cis.oid=isub.indexrelid |
446
|
|
|
JOIN pg_namespace ns |
447
|
|
|
ON cis.relnamespace = ns.oid |
448
|
|
|
WHERE cis.relname='$index' AND ns.nspname='$schema') AS s, |
449
|
|
|
pg_attribute, |
450
|
|
|
pg_opclass opcls, |
451
|
|
|
pg_am, |
452
|
|
|
pg_class ci |
453
|
|
|
JOIN pg_index i |
454
|
|
|
ON ci.oid=i.indexrelid |
455
|
|
|
JOIN pg_class ct |
456
|
|
|
ON ct.oid = i.indrelid |
457
|
|
|
JOIN pg_namespace n |
458
|
|
|
ON ci.relnamespace = n.oid |
459
|
|
|
WHERE |
460
|
|
|
ci.relname='$index' AND n.nspname='$schema' |
461
|
|
|
AND attrelid = ct.oid |
462
|
|
|
AND i.indkey[s.g] = attnum |
463
|
|
|
AND i.indclass[s.g] = opcls.oid |
464
|
|
|
AND pg_am.oid = opcls.opcmethod |
465
|
|
|
__INDEXATTR__; |
466
|
|
|
$res = $this->query( $sql, __METHOD__ ); |
467
|
|
|
$a = []; |
468
|
|
|
if ( $res ) { |
469
|
|
|
foreach ( $res as $row ) { |
|
|
|
|
470
|
|
|
$a[] = [ |
471
|
|
|
$row->attname, |
472
|
|
|
$row->opcname, |
473
|
|
|
$row->amname, |
474
|
|
|
$row->option ]; |
475
|
|
|
} |
476
|
|
|
} else { |
477
|
|
|
return null; |
478
|
|
|
} |
479
|
|
|
|
480
|
|
|
return $a; |
481
|
|
|
} |
482
|
|
|
|
483
|
|
|
public function indexUnique( $table, $index, $fname = __METHOD__ ) { |
484
|
|
|
$sql = "SELECT indexname FROM pg_indexes WHERE tablename='{$table}'" . |
485
|
|
|
" AND indexdef LIKE 'CREATE UNIQUE%(" . |
486
|
|
|
$this->strencode( $this->indexName( $index ) ) . |
487
|
|
|
")'"; |
488
|
|
|
$res = $this->query( $sql, $fname ); |
489
|
|
|
if ( !$res ) { |
490
|
|
|
return null; |
491
|
|
|
} |
492
|
|
|
|
493
|
|
|
return $res->numRows() > 0; |
494
|
|
|
} |
495
|
|
|
|
496
|
|
|
public function selectSQLText( |
497
|
|
|
$table, $vars, $conds = '', $fname = __METHOD__, $options = [], $join_conds = [] |
498
|
|
|
) { |
499
|
|
|
// Change the FOR UPDATE option as necessary based on the join conditions. Then pass |
500
|
|
|
// to the parent function to get the actual SQL text. |
501
|
|
|
// In Postgres when using FOR UPDATE, only the main table and tables that are inner joined |
502
|
|
|
// can be locked. That means tables in an outer join cannot be FOR UPDATE locked. Trying to |
503
|
|
|
// do so causes a DB error. This wrapper checks which tables can be locked and adjusts it |
504
|
|
|
// accordingly. |
505
|
|
|
// MySQL uses "ORDER BY NULL" as an optimization hint, but that is illegal in PostgreSQL. |
506
|
|
|
if ( is_array( $options ) ) { |
507
|
|
|
$forUpdateKey = array_search( 'FOR UPDATE', $options, true ); |
508
|
|
|
if ( $forUpdateKey !== false && $join_conds ) { |
509
|
|
|
unset( $options[$forUpdateKey] ); |
510
|
|
|
|
511
|
|
|
foreach ( $join_conds as $table_cond => $join_cond ) { |
512
|
|
|
if ( 0 === preg_match( '/^(?:LEFT|RIGHT|FULL)(?: OUTER)? JOIN$/i', $join_cond[0] ) ) { |
513
|
|
|
$options['FOR UPDATE'][] = $table_cond; |
514
|
|
|
} |
515
|
|
|
} |
516
|
|
|
} |
517
|
|
|
|
518
|
|
|
if ( isset( $options['ORDER BY'] ) && $options['ORDER BY'] == 'NULL' ) { |
519
|
|
|
unset( $options['ORDER BY'] ); |
520
|
|
|
} |
521
|
|
|
} |
522
|
|
|
|
523
|
|
|
return parent::selectSQLText( $table, $vars, $conds, $fname, $options, $join_conds ); |
524
|
|
|
} |
525
|
|
|
|
526
|
|
|
/** |
527
|
|
|
* INSERT wrapper, inserts an array into a table |
528
|
|
|
* |
529
|
|
|
* $args may be a single associative array, or an array of these with numeric keys, |
530
|
|
|
* for multi-row insert (Postgres version 8.2 and above only). |
531
|
|
|
* |
532
|
|
|
* @param string $table Name of the table to insert to. |
533
|
|
|
* @param array $args Items to insert into the table. |
534
|
|
|
* @param string $fname Name of the function, for profiling |
535
|
|
|
* @param array|string $options String or array. Valid options: IGNORE |
536
|
|
|
* @return bool Success of insert operation. IGNORE always returns true. |
537
|
|
|
*/ |
538
|
|
|
public function insert( $table, $args, $fname = __METHOD__, $options = [] ) { |
539
|
|
|
if ( !count( $args ) ) { |
540
|
|
|
return true; |
541
|
|
|
} |
542
|
|
|
|
543
|
|
|
$table = $this->tableName( $table ); |
544
|
|
|
if ( !isset( $this->numericVersion ) ) { |
545
|
|
|
$this->getServerVersion(); |
546
|
|
|
} |
547
|
|
|
|
548
|
|
|
if ( !is_array( $options ) ) { |
549
|
|
|
$options = [ $options ]; |
550
|
|
|
} |
551
|
|
|
|
552
|
|
View Code Duplication |
if ( isset( $args[0] ) && is_array( $args[0] ) ) { |
553
|
|
|
$multi = true; |
554
|
|
|
$keys = array_keys( $args[0] ); |
555
|
|
|
} else { |
556
|
|
|
$multi = false; |
557
|
|
|
$keys = array_keys( $args ); |
558
|
|
|
} |
559
|
|
|
|
560
|
|
|
// If IGNORE is set, we use savepoints to emulate mysql's behavior |
561
|
|
|
$savepoint = $olde = null; |
562
|
|
|
$numrowsinserted = 0; |
563
|
|
|
if ( in_array( 'IGNORE', $options ) ) { |
564
|
|
|
$savepoint = new SavepointPostgres( $this, 'mw', $this->queryLogger ); |
565
|
|
|
$olde = error_reporting( 0 ); |
566
|
|
|
// For future use, we may want to track the number of actual inserts |
567
|
|
|
// Right now, insert (all writes) simply return true/false |
568
|
|
|
} |
569
|
|
|
|
570
|
|
|
$sql = "INSERT INTO $table (" . implode( ',', $keys ) . ') VALUES '; |
571
|
|
|
|
572
|
|
|
if ( $multi ) { |
573
|
|
|
if ( $this->numericVersion >= 8.2 && !$savepoint ) { |
574
|
|
|
$first = true; |
575
|
|
View Code Duplication |
foreach ( $args as $row ) { |
576
|
|
|
if ( $first ) { |
577
|
|
|
$first = false; |
578
|
|
|
} else { |
579
|
|
|
$sql .= ','; |
580
|
|
|
} |
581
|
|
|
$sql .= '(' . $this->makeList( $row ) . ')'; |
582
|
|
|
} |
583
|
|
|
$res = (bool)$this->query( $sql, $fname, $savepoint ); |
584
|
|
|
} else { |
585
|
|
|
$res = true; |
586
|
|
|
$origsql = $sql; |
587
|
|
|
foreach ( $args as $row ) { |
588
|
|
|
$tempsql = $origsql; |
589
|
|
|
$tempsql .= '(' . $this->makeList( $row ) . ')'; |
590
|
|
|
|
591
|
|
|
if ( $savepoint ) { |
592
|
|
|
$savepoint->savepoint(); |
593
|
|
|
} |
594
|
|
|
|
595
|
|
|
$tempres = (bool)$this->query( $tempsql, $fname, $savepoint ); |
596
|
|
|
|
597
|
|
View Code Duplication |
if ( $savepoint ) { |
598
|
|
|
$bar = pg_result_error( $this->mLastResult ); |
599
|
|
|
if ( $bar != false ) { |
|
|
|
|
600
|
|
|
$savepoint->rollback(); |
601
|
|
|
} else { |
602
|
|
|
$savepoint->release(); |
603
|
|
|
$numrowsinserted++; |
604
|
|
|
} |
605
|
|
|
} |
606
|
|
|
|
607
|
|
|
// If any of them fail, we fail overall for this function call |
608
|
|
|
// Note that this will be ignored if IGNORE is set |
609
|
|
|
if ( !$tempres ) { |
610
|
|
|
$res = false; |
611
|
|
|
} |
612
|
|
|
} |
613
|
|
|
} |
614
|
|
|
} else { |
615
|
|
|
// Not multi, just a lone insert |
616
|
|
|
if ( $savepoint ) { |
617
|
|
|
$savepoint->savepoint(); |
618
|
|
|
} |
619
|
|
|
|
620
|
|
|
$sql .= '(' . $this->makeList( $args ) . ')'; |
621
|
|
|
$res = (bool)$this->query( $sql, $fname, $savepoint ); |
622
|
|
View Code Duplication |
if ( $savepoint ) { |
623
|
|
|
$bar = pg_result_error( $this->mLastResult ); |
624
|
|
|
if ( $bar != false ) { |
|
|
|
|
625
|
|
|
$savepoint->rollback(); |
626
|
|
|
} else { |
627
|
|
|
$savepoint->release(); |
628
|
|
|
$numrowsinserted++; |
629
|
|
|
} |
630
|
|
|
} |
631
|
|
|
} |
632
|
|
|
if ( $savepoint ) { |
633
|
|
|
error_reporting( $olde ); |
634
|
|
|
$savepoint->commit(); |
635
|
|
|
|
636
|
|
|
// Set the affected row count for the whole operation |
637
|
|
|
$this->mAffectedRows = $numrowsinserted; |
638
|
|
|
|
639
|
|
|
// IGNORE always returns true |
640
|
|
|
return true; |
641
|
|
|
} |
642
|
|
|
|
643
|
|
|
return $res; |
644
|
|
|
} |
645
|
|
|
|
646
|
|
|
/** |
647
|
|
|
* INSERT SELECT wrapper |
648
|
|
|
* $varMap must be an associative array of the form [ 'dest1' => 'source1', ... ] |
649
|
|
|
* Source items may be literals rather then field names, but strings should |
650
|
|
|
* be quoted with Database::addQuotes() |
651
|
|
|
* $conds may be "*" to copy the whole table |
652
|
|
|
* srcTable may be an array of tables. |
653
|
|
|
* @todo FIXME: Implement this a little better (seperate select/insert)? |
654
|
|
|
* |
655
|
|
|
* @param string $destTable |
656
|
|
|
* @param array|string $srcTable |
657
|
|
|
* @param array $varMap |
658
|
|
|
* @param array $conds |
659
|
|
|
* @param string $fname |
660
|
|
|
* @param array $insertOptions |
661
|
|
|
* @param array $selectOptions |
662
|
|
|
* @return bool |
663
|
|
|
*/ |
664
|
|
|
public function nativeInsertSelect( |
665
|
|
|
$destTable, $srcTable, $varMap, $conds, $fname = __METHOD__, |
666
|
|
|
$insertOptions = [], $selectOptions = [] |
667
|
|
|
) { |
668
|
|
|
$destTable = $this->tableName( $destTable ); |
669
|
|
|
|
670
|
|
|
if ( !is_array( $insertOptions ) ) { |
671
|
|
|
$insertOptions = [ $insertOptions ]; |
672
|
|
|
} |
673
|
|
|
|
674
|
|
|
/* |
675
|
|
|
* If IGNORE is set, we use savepoints to emulate mysql's behavior |
676
|
|
|
* Ignore LOW PRIORITY option, since it is MySQL-specific |
677
|
|
|
*/ |
678
|
|
|
$savepoint = $olde = null; |
679
|
|
|
$numrowsinserted = 0; |
680
|
|
|
if ( in_array( 'IGNORE', $insertOptions ) ) { |
681
|
|
|
$savepoint = new SavepointPostgres( $this, 'mw', $this->queryLogger ); |
682
|
|
|
$olde = error_reporting( 0 ); |
683
|
|
|
$savepoint->savepoint(); |
684
|
|
|
} |
685
|
|
|
|
686
|
|
|
if ( !is_array( $selectOptions ) ) { |
687
|
|
|
$selectOptions = [ $selectOptions ]; |
688
|
|
|
} |
689
|
|
|
list( $startOpts, $useIndex, $tailOpts, $ignoreIndex ) = |
690
|
|
|
$this->makeSelectOptions( $selectOptions ); |
691
|
|
View Code Duplication |
if ( is_array( $srcTable ) ) { |
692
|
|
|
$srcTable = implode( ',', array_map( [ &$this, 'tableName' ], $srcTable ) ); |
693
|
|
|
} else { |
694
|
|
|
$srcTable = $this->tableName( $srcTable ); |
695
|
|
|
} |
696
|
|
|
|
697
|
|
|
$sql = "INSERT INTO $destTable (" . implode( ',', array_keys( $varMap ) ) . ')' . |
698
|
|
|
" SELECT $startOpts " . implode( ',', $varMap ) . |
699
|
|
|
" FROM $srcTable $useIndex $ignoreIndex "; |
700
|
|
|
|
701
|
|
|
if ( $conds != '*' ) { |
702
|
|
|
$sql .= ' WHERE ' . $this->makeList( $conds, LIST_AND ); |
703
|
|
|
} |
704
|
|
|
|
705
|
|
|
$sql .= " $tailOpts"; |
706
|
|
|
|
707
|
|
|
$res = (bool)$this->query( $sql, $fname, $savepoint ); |
708
|
|
|
if ( $savepoint ) { |
709
|
|
|
$bar = pg_result_error( $this->mLastResult ); |
710
|
|
|
if ( $bar != false ) { |
|
|
|
|
711
|
|
|
$savepoint->rollback(); |
712
|
|
|
} else { |
713
|
|
|
$savepoint->release(); |
714
|
|
|
$numrowsinserted++; |
715
|
|
|
} |
716
|
|
|
error_reporting( $olde ); |
717
|
|
|
$savepoint->commit(); |
718
|
|
|
|
719
|
|
|
// Set the affected row count for the whole operation |
720
|
|
|
$this->mAffectedRows = $numrowsinserted; |
721
|
|
|
|
722
|
|
|
// IGNORE always returns true |
723
|
|
|
return true; |
724
|
|
|
} |
725
|
|
|
|
726
|
|
|
return $res; |
727
|
|
|
} |
728
|
|
|
|
729
|
|
|
public function tableName( $name, $format = 'quoted' ) { |
730
|
|
|
// Replace reserved words with better ones |
731
|
|
|
$name = $this->remappedTableName( $name ); |
732
|
|
|
|
733
|
|
|
return parent::tableName( $name, $format ); |
734
|
|
|
} |
735
|
|
|
|
736
|
|
|
/** |
737
|
|
|
* @param string $name |
738
|
|
|
* @return string Value of $name or remapped name if $name is a reserved keyword |
739
|
|
|
* @TODO: dependency inject these... |
740
|
|
|
*/ |
741
|
|
|
public function remappedTableName( $name ) { |
742
|
|
|
if ( $name === 'user' ) { |
743
|
|
|
return 'mwuser'; |
744
|
|
|
} elseif ( $name === 'text' ) { |
745
|
|
|
return 'pagecontent'; |
746
|
|
|
} |
747
|
|
|
|
748
|
|
|
return $name; |
749
|
|
|
} |
750
|
|
|
|
751
|
|
|
/** |
752
|
|
|
* @param string $name |
753
|
|
|
* @param string $format |
754
|
|
|
* @return string Qualified and encoded (if requested) table name |
755
|
|
|
*/ |
756
|
|
|
public function realTableName( $name, $format = 'quoted' ) { |
757
|
|
|
return parent::tableName( $name, $format ); |
|
|
|
|
758
|
|
|
} |
759
|
|
|
|
760
|
|
|
public function nextSequenceValue( $seqName ) { |
761
|
|
|
$safeseq = str_replace( "'", "''", $seqName ); |
762
|
|
|
$res = $this->query( "SELECT nextval('$safeseq')" ); |
763
|
|
|
$row = $this->fetchRow( $res ); |
|
|
|
|
764
|
|
|
$this->mInsertId = $row[0]; |
765
|
|
|
|
766
|
|
|
return $this->mInsertId; |
767
|
|
|
} |
768
|
|
|
|
769
|
|
|
/** |
770
|
|
|
* Return the current value of a sequence. Assumes it has been nextval'ed in this session. |
771
|
|
|
* |
772
|
|
|
* @param string $seqName |
773
|
|
|
* @return int |
774
|
|
|
*/ |
775
|
|
|
public function currentSequenceValue( $seqName ) { |
776
|
|
|
$safeseq = str_replace( "'", "''", $seqName ); |
777
|
|
|
$res = $this->query( "SELECT currval('$safeseq')" ); |
778
|
|
|
$row = $this->fetchRow( $res ); |
|
|
|
|
779
|
|
|
$currval = $row[0]; |
780
|
|
|
|
781
|
|
|
return $currval; |
782
|
|
|
} |
783
|
|
|
|
784
|
|
|
public function textFieldSize( $table, $field ) { |
785
|
|
|
$table = $this->tableName( $table ); |
786
|
|
|
$sql = "SELECT t.typname as ftype,a.atttypmod as size |
787
|
|
|
FROM pg_class c, pg_attribute a, pg_type t |
788
|
|
|
WHERE relname='$table' AND a.attrelid=c.oid AND |
789
|
|
|
a.atttypid=t.oid and a.attname='$field'"; |
790
|
|
|
$res = $this->query( $sql ); |
791
|
|
|
$row = $this->fetchObject( $res ); |
|
|
|
|
792
|
|
|
if ( $row->ftype == 'varchar' ) { |
793
|
|
|
$size = $row->size - 4; |
794
|
|
|
} else { |
795
|
|
|
$size = $row->size; |
796
|
|
|
} |
797
|
|
|
|
798
|
|
|
return $size; |
799
|
|
|
} |
800
|
|
|
|
801
|
|
|
public function limitResult( $sql, $limit, $offset = false ) { |
802
|
|
|
return "$sql LIMIT $limit " . ( is_numeric( $offset ) ? " OFFSET {$offset} " : '' ); |
803
|
|
|
} |
804
|
|
|
|
805
|
|
|
public function wasDeadlock() { |
806
|
|
|
return $this->lastErrno() == '40P01'; |
807
|
|
|
} |
808
|
|
|
|
809
|
|
|
public function duplicateTableStructure( |
810
|
|
|
$oldName, $newName, $temporary = false, $fname = __METHOD__ |
811
|
|
|
) { |
812
|
|
|
$newName = $this->addIdentifierQuotes( $newName ); |
813
|
|
|
$oldName = $this->addIdentifierQuotes( $oldName ); |
814
|
|
|
|
815
|
|
|
return $this->query( 'CREATE ' . ( $temporary ? 'TEMPORARY ' : '' ) . " TABLE $newName " . |
816
|
|
|
"(LIKE $oldName INCLUDING DEFAULTS)", $fname ); |
817
|
|
|
} |
818
|
|
|
|
819
|
|
|
public function listTables( $prefix = null, $fname = __METHOD__ ) { |
820
|
|
|
$eschema = $this->addQuotes( $this->getCoreSchema() ); |
821
|
|
|
$result = $this->query( |
822
|
|
|
"SELECT tablename FROM pg_tables WHERE schemaname = $eschema", $fname ); |
823
|
|
|
$endArray = []; |
824
|
|
|
|
825
|
|
View Code Duplication |
foreach ( $result as $table ) { |
|
|
|
|
826
|
|
|
$vars = get_object_vars( $table ); |
827
|
|
|
$table = array_pop( $vars ); |
828
|
|
|
if ( !$prefix || strpos( $table, $prefix ) === 0 ) { |
|
|
|
|
829
|
|
|
$endArray[] = $table; |
830
|
|
|
} |
831
|
|
|
} |
832
|
|
|
|
833
|
|
|
return $endArray; |
834
|
|
|
} |
835
|
|
|
|
836
|
|
|
public function timestamp( $ts = 0 ) { |
837
|
|
|
$ct = new ConvertibleTimestamp( $ts ); |
838
|
|
|
|
839
|
|
|
return $ct->getTimestamp( TS_POSTGRES ); |
840
|
|
|
} |
841
|
|
|
|
842
|
|
|
/** |
843
|
|
|
* Posted by cc[plus]php[at]c2se[dot]com on 25-Mar-2009 09:12 |
844
|
|
|
* to http://www.php.net/manual/en/ref.pgsql.php |
845
|
|
|
* |
846
|
|
|
* Parsing a postgres array can be a tricky problem, he's my |
847
|
|
|
* take on this, it handles multi-dimensional arrays plus |
848
|
|
|
* escaping using a nasty regexp to determine the limits of each |
849
|
|
|
* data-item. |
850
|
|
|
* |
851
|
|
|
* This should really be handled by PHP PostgreSQL module |
852
|
|
|
* |
853
|
|
|
* @since 1.19 |
854
|
|
|
* @param string $text Postgreql array returned in a text form like {a,b} |
855
|
|
|
* @param string $output |
856
|
|
|
* @param int|bool $limit |
857
|
|
|
* @param int $offset |
858
|
|
|
* @return string |
859
|
|
|
*/ |
860
|
|
|
private function pg_array_parse( $text, &$output, $limit = false, $offset = 1 ) { |
861
|
|
|
if ( false === $limit ) { |
862
|
|
|
$limit = strlen( $text ) - 1; |
863
|
|
|
$output = []; |
864
|
|
|
} |
865
|
|
|
if ( '{}' == $text ) { |
866
|
|
|
return $output; |
867
|
|
|
} |
868
|
|
|
do { |
869
|
|
|
if ( '{' != $text[$offset] ) { |
870
|
|
|
preg_match( "/(\\{?\"([^\"\\\\]|\\\\.)*\"|[^,{}]+)+([,}]+)/", |
871
|
|
|
$text, $match, 0, $offset ); |
872
|
|
|
$offset += strlen( $match[0] ); |
873
|
|
|
$output[] = ( '"' != $match[1][0] |
874
|
|
|
? $match[1] |
875
|
|
|
: stripcslashes( substr( $match[1], 1, -1 ) ) ); |
876
|
|
|
if ( '},' == $match[3] ) { |
877
|
|
|
return $output; |
878
|
|
|
} |
879
|
|
|
} else { |
880
|
|
|
$offset = $this->pg_array_parse( $text, $output, $limit, $offset + 1 ); |
|
|
|
|
881
|
|
|
} |
882
|
|
|
} while ( $limit > $offset ); |
883
|
|
|
|
884
|
|
|
return $output; |
885
|
|
|
} |
886
|
|
|
|
887
|
|
|
public function aggregateValue( $valuedata, $valuename = 'value' ) { |
888
|
|
|
return $valuedata; |
889
|
|
|
} |
890
|
|
|
|
891
|
|
|
public function getSoftwareLink() { |
892
|
|
|
return '[{{int:version-db-postgres-url}} PostgreSQL]'; |
893
|
|
|
} |
894
|
|
|
|
895
|
|
|
/** |
896
|
|
|
* Return current schema (executes SELECT current_schema()) |
897
|
|
|
* Needs transaction |
898
|
|
|
* |
899
|
|
|
* @since 1.19 |
900
|
|
|
* @return string Default schema for the current session |
901
|
|
|
*/ |
902
|
|
|
public function getCurrentSchema() { |
903
|
|
|
$res = $this->query( "SELECT current_schema()", __METHOD__ ); |
904
|
|
|
$row = $this->fetchRow( $res ); |
|
|
|
|
905
|
|
|
|
906
|
|
|
return $row[0]; |
907
|
|
|
} |
908
|
|
|
|
909
|
|
|
/** |
910
|
|
|
* Return list of schemas which are accessible without schema name |
911
|
|
|
* This is list does not contain magic keywords like "$user" |
912
|
|
|
* Needs transaction |
913
|
|
|
* |
914
|
|
|
* @see getSearchPath() |
915
|
|
|
* @see setSearchPath() |
916
|
|
|
* @since 1.19 |
917
|
|
|
* @return array List of actual schemas for the current sesson |
918
|
|
|
*/ |
919
|
|
|
public function getSchemas() { |
920
|
|
|
$res = $this->query( "SELECT current_schemas(false)", __METHOD__ ); |
921
|
|
|
$row = $this->fetchRow( $res ); |
|
|
|
|
922
|
|
|
$schemas = []; |
923
|
|
|
|
924
|
|
|
/* PHP pgsql support does not support array type, "{a,b}" string is returned */ |
925
|
|
|
|
926
|
|
|
return $this->pg_array_parse( $row[0], $schemas ); |
927
|
|
|
} |
928
|
|
|
|
929
|
|
|
/** |
930
|
|
|
* Return search patch for schemas |
931
|
|
|
* This is different from getSchemas() since it contain magic keywords |
932
|
|
|
* (like "$user"). |
933
|
|
|
* Needs transaction |
934
|
|
|
* |
935
|
|
|
* @since 1.19 |
936
|
|
|
* @return array How to search for table names schemas for the current user |
937
|
|
|
*/ |
938
|
|
|
public function getSearchPath() { |
939
|
|
|
$res = $this->query( "SHOW search_path", __METHOD__ ); |
940
|
|
|
$row = $this->fetchRow( $res ); |
|
|
|
|
941
|
|
|
|
942
|
|
|
/* PostgreSQL returns SHOW values as strings */ |
943
|
|
|
|
944
|
|
|
return explode( ",", $row[0] ); |
945
|
|
|
} |
946
|
|
|
|
947
|
|
|
/** |
948
|
|
|
* Update search_path, values should already be sanitized |
949
|
|
|
* Values may contain magic keywords like "$user" |
950
|
|
|
* @since 1.19 |
951
|
|
|
* |
952
|
|
|
* @param array $search_path List of schemas to be searched by default |
953
|
|
|
*/ |
954
|
|
|
private function setSearchPath( $search_path ) { |
955
|
|
|
$this->query( "SET search_path = " . implode( ", ", $search_path ) ); |
956
|
|
|
} |
957
|
|
|
|
958
|
|
|
/** |
959
|
|
|
* Determine default schema for the current application |
960
|
|
|
* Adjust this session schema search path if desired schema exists |
961
|
|
|
* and is not alread there. |
962
|
|
|
* |
963
|
|
|
* We need to have name of the core schema stored to be able |
964
|
|
|
* to query database metadata. |
965
|
|
|
* |
966
|
|
|
* This will be also called by the installer after the schema is created |
967
|
|
|
* |
968
|
|
|
* @since 1.19 |
969
|
|
|
* |
970
|
|
|
* @param string $desiredSchema |
971
|
|
|
*/ |
972
|
|
|
public function determineCoreSchema( $desiredSchema ) { |
973
|
|
|
$this->begin( __METHOD__, self::TRANSACTION_INTERNAL ); |
974
|
|
|
if ( $this->schemaExists( $desiredSchema ) ) { |
975
|
|
|
if ( in_array( $desiredSchema, $this->getSchemas() ) ) { |
976
|
|
|
$this->mCoreSchema = $desiredSchema; |
977
|
|
|
$this->queryLogger->debug( |
978
|
|
|
"Schema \"" . $desiredSchema . "\" already in the search path\n" ); |
979
|
|
|
} else { |
980
|
|
|
/** |
981
|
|
|
* Prepend our schema (e.g. 'mediawiki') in front |
982
|
|
|
* of the search path |
983
|
|
|
* Fixes bug 15816 |
984
|
|
|
*/ |
985
|
|
|
$search_path = $this->getSearchPath(); |
986
|
|
|
array_unshift( $search_path, |
987
|
|
|
$this->addIdentifierQuotes( $desiredSchema ) ); |
988
|
|
|
$this->setSearchPath( $search_path ); |
989
|
|
|
$this->mCoreSchema = $desiredSchema; |
990
|
|
|
$this->queryLogger->debug( |
991
|
|
|
"Schema \"" . $desiredSchema . "\" added to the search path\n" ); |
992
|
|
|
} |
993
|
|
|
} else { |
994
|
|
|
$this->mCoreSchema = $this->getCurrentSchema(); |
995
|
|
|
$this->queryLogger->debug( |
996
|
|
|
"Schema \"" . $desiredSchema . "\" not found, using current \"" . |
997
|
|
|
$this->mCoreSchema . "\"\n" ); |
998
|
|
|
} |
999
|
|
|
/* Commit SET otherwise it will be rollbacked on error or IGNORE SELECT */ |
1000
|
|
|
$this->commit( __METHOD__, self::FLUSHING_INTERNAL ); |
1001
|
|
|
} |
1002
|
|
|
|
1003
|
|
|
/** |
1004
|
|
|
* Return schema name for core application tables |
1005
|
|
|
* |
1006
|
|
|
* @since 1.19 |
1007
|
|
|
* @return string Core schema name |
1008
|
|
|
*/ |
1009
|
|
|
public function getCoreSchema() { |
1010
|
|
|
return $this->mCoreSchema; |
1011
|
|
|
} |
1012
|
|
|
|
1013
|
|
|
public function getServerVersion() { |
1014
|
|
|
if ( !isset( $this->numericVersion ) ) { |
1015
|
|
|
$conn = $this->getBindingHandle(); |
1016
|
|
|
$versionInfo = pg_version( $conn ); |
1017
|
|
|
if ( version_compare( $versionInfo['client'], '7.4.0', 'lt' ) ) { |
1018
|
|
|
// Old client, abort install |
1019
|
|
|
$this->numericVersion = '7.3 or earlier'; |
1020
|
|
|
} elseif ( isset( $versionInfo['server'] ) ) { |
1021
|
|
|
// Normal client |
1022
|
|
|
$this->numericVersion = $versionInfo['server']; |
1023
|
|
|
} else { |
1024
|
|
|
// Bug 16937: broken pgsql extension from PHP<5.3 |
1025
|
|
|
$this->numericVersion = pg_parameter_status( $conn, 'server_version' ); |
1026
|
|
|
} |
1027
|
|
|
} |
1028
|
|
|
|
1029
|
|
|
return $this->numericVersion; |
1030
|
|
|
} |
1031
|
|
|
|
1032
|
|
|
/** |
1033
|
|
|
* Query whether a given relation exists (in the given schema, or the |
1034
|
|
|
* default mw one if not given) |
1035
|
|
|
* @param string $table |
1036
|
|
|
* @param array|string $types |
1037
|
|
|
* @param bool|string $schema |
1038
|
|
|
* @return bool |
1039
|
|
|
*/ |
1040
|
|
|
private function relationExists( $table, $types, $schema = false ) { |
1041
|
|
|
if ( !is_array( $types ) ) { |
1042
|
|
|
$types = [ $types ]; |
1043
|
|
|
} |
1044
|
|
|
if ( $schema === false ) { |
1045
|
|
|
$schema = $this->getCoreSchema(); |
1046
|
|
|
} |
1047
|
|
|
$etable = $this->addQuotes( $table ); |
1048
|
|
|
$eschema = $this->addQuotes( $schema ); |
1049
|
|
|
$sql = "SELECT 1 FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n " |
1050
|
|
|
. "WHERE c.relnamespace = n.oid AND c.relname = $etable AND n.nspname = $eschema " |
1051
|
|
|
. "AND c.relkind IN ('" . implode( "','", $types ) . "')"; |
1052
|
|
|
$res = $this->query( $sql ); |
1053
|
|
|
$count = $res ? $res->numRows() : 0; |
1054
|
|
|
|
1055
|
|
|
return (bool)$count; |
1056
|
|
|
} |
1057
|
|
|
|
1058
|
|
|
/** |
1059
|
|
|
* For backward compatibility, this function checks both tables and views. |
1060
|
|
|
* @param string $table |
1061
|
|
|
* @param string $fname |
1062
|
|
|
* @param bool|string $schema |
1063
|
|
|
* @return bool |
1064
|
|
|
*/ |
1065
|
|
|
public function tableExists( $table, $fname = __METHOD__, $schema = false ) { |
1066
|
|
|
return $this->relationExists( $table, [ 'r', 'v' ], $schema ); |
1067
|
|
|
} |
1068
|
|
|
|
1069
|
|
|
public function sequenceExists( $sequence, $schema = false ) { |
1070
|
|
|
return $this->relationExists( $sequence, 'S', $schema ); |
1071
|
|
|
} |
1072
|
|
|
|
1073
|
|
View Code Duplication |
public function triggerExists( $table, $trigger ) { |
1074
|
|
|
$q = <<<SQL |
1075
|
|
|
SELECT 1 FROM pg_class, pg_namespace, pg_trigger |
1076
|
|
|
WHERE relnamespace=pg_namespace.oid AND relkind='r' |
1077
|
|
|
AND tgrelid=pg_class.oid |
1078
|
|
|
AND nspname=%s AND relname=%s AND tgname=%s |
1079
|
|
|
SQL; |
1080
|
|
|
$res = $this->query( |
1081
|
|
|
sprintf( |
1082
|
|
|
$q, |
1083
|
|
|
$this->addQuotes( $this->getCoreSchema() ), |
1084
|
|
|
$this->addQuotes( $table ), |
1085
|
|
|
$this->addQuotes( $trigger ) |
1086
|
|
|
) |
1087
|
|
|
); |
1088
|
|
|
if ( !$res ) { |
1089
|
|
|
return null; |
1090
|
|
|
} |
1091
|
|
|
$rows = $res->numRows(); |
1092
|
|
|
|
1093
|
|
|
return $rows; |
1094
|
|
|
} |
1095
|
|
|
|
1096
|
|
|
public function ruleExists( $table, $rule ) { |
1097
|
|
|
$exists = $this->selectField( 'pg_rules', 'rulename', |
1098
|
|
|
[ |
1099
|
|
|
'rulename' => $rule, |
1100
|
|
|
'tablename' => $table, |
1101
|
|
|
'schemaname' => $this->getCoreSchema() |
1102
|
|
|
] |
1103
|
|
|
); |
1104
|
|
|
|
1105
|
|
|
return $exists === $rule; |
1106
|
|
|
} |
1107
|
|
|
|
1108
|
|
View Code Duplication |
public function constraintExists( $table, $constraint ) { |
1109
|
|
|
$sql = sprintf( "SELECT 1 FROM information_schema.table_constraints " . |
1110
|
|
|
"WHERE constraint_schema = %s AND table_name = %s AND constraint_name = %s", |
1111
|
|
|
$this->addQuotes( $this->getCoreSchema() ), |
1112
|
|
|
$this->addQuotes( $table ), |
1113
|
|
|
$this->addQuotes( $constraint ) |
1114
|
|
|
); |
1115
|
|
|
$res = $this->query( $sql ); |
1116
|
|
|
if ( !$res ) { |
1117
|
|
|
return null; |
1118
|
|
|
} |
1119
|
|
|
$rows = $res->numRows(); |
1120
|
|
|
|
1121
|
|
|
return $rows; |
1122
|
|
|
} |
1123
|
|
|
|
1124
|
|
|
/** |
1125
|
|
|
* Query whether a given schema exists. Returns true if it does, false if it doesn't. |
1126
|
|
|
* @param string $schema |
1127
|
|
|
* @return bool |
1128
|
|
|
*/ |
1129
|
|
|
public function schemaExists( $schema ) { |
1130
|
|
|
if ( !strlen( $schema ) ) { |
1131
|
|
|
return false; // short-circuit |
1132
|
|
|
} |
1133
|
|
|
|
1134
|
|
|
$exists = $this->selectField( |
1135
|
|
|
'"pg_catalog"."pg_namespace"', 1, [ 'nspname' => $schema ], __METHOD__ ); |
1136
|
|
|
|
1137
|
|
|
return (bool)$exists; |
1138
|
|
|
} |
1139
|
|
|
|
1140
|
|
|
/** |
1141
|
|
|
* Returns true if a given role (i.e. user) exists, false otherwise. |
1142
|
|
|
* @param string $roleName |
1143
|
|
|
* @return bool |
1144
|
|
|
*/ |
1145
|
|
|
public function roleExists( $roleName ) { |
1146
|
|
|
$exists = $this->selectField( '"pg_catalog"."pg_roles"', 1, |
1147
|
|
|
[ 'rolname' => $roleName ], __METHOD__ ); |
1148
|
|
|
|
1149
|
|
|
return (bool)$exists; |
1150
|
|
|
} |
1151
|
|
|
|
1152
|
|
|
/** |
1153
|
|
|
* @var string $table |
1154
|
|
|
* @var string $field |
1155
|
|
|
* @return PostgresField|null |
1156
|
|
|
*/ |
1157
|
|
|
public function fieldInfo( $table, $field ) { |
1158
|
|
|
return PostgresField::fromText( $this, $table, $field ); |
1159
|
|
|
} |
1160
|
|
|
|
1161
|
|
|
/** |
1162
|
|
|
* pg_field_type() wrapper |
1163
|
|
|
* @param ResultWrapper|resource $res ResultWrapper or PostgreSQL query result resource |
1164
|
|
|
* @param int $index Field number, starting from 0 |
1165
|
|
|
* @return string |
1166
|
|
|
*/ |
1167
|
|
|
public function fieldType( $res, $index ) { |
1168
|
|
|
if ( $res instanceof ResultWrapper ) { |
1169
|
|
|
$res = $res->result; |
1170
|
|
|
} |
1171
|
|
|
|
1172
|
|
|
return pg_field_type( $res, $index ); |
1173
|
|
|
} |
1174
|
|
|
|
1175
|
|
|
public function encodeBlob( $b ) { |
1176
|
|
|
return new PostgresBlob( pg_escape_bytea( $b ) ); |
1177
|
|
|
} |
1178
|
|
|
|
1179
|
|
|
public function decodeBlob( $b ) { |
1180
|
|
|
if ( $b instanceof PostgresBlob ) { |
1181
|
|
|
$b = $b->fetch(); |
1182
|
|
|
} elseif ( $b instanceof Blob ) { |
1183
|
|
|
return $b->fetch(); |
1184
|
|
|
} |
1185
|
|
|
|
1186
|
|
|
return pg_unescape_bytea( $b ); |
1187
|
|
|
} |
1188
|
|
|
|
1189
|
|
|
public function strencode( $s ) { |
1190
|
|
|
// Should not be called by us |
1191
|
|
|
return pg_escape_string( $this->getBindingHandle(), $s ); |
1192
|
|
|
} |
1193
|
|
|
|
1194
|
|
|
public function addQuotes( $s ) { |
1195
|
|
|
$conn = $this->getBindingHandle(); |
1196
|
|
|
|
1197
|
|
|
if ( is_null( $s ) ) { |
1198
|
|
|
return 'NULL'; |
1199
|
|
|
} elseif ( is_bool( $s ) ) { |
1200
|
|
|
return intval( $s ); |
1201
|
|
|
} elseif ( $s instanceof Blob ) { |
1202
|
|
|
if ( $s instanceof PostgresBlob ) { |
1203
|
|
|
$s = $s->fetch(); |
1204
|
|
|
} else { |
1205
|
|
|
$s = pg_escape_bytea( $conn, $s->fetch() ); |
1206
|
|
|
} |
1207
|
|
|
return "'$s'"; |
1208
|
|
|
} |
1209
|
|
|
|
1210
|
|
|
return "'" . pg_escape_string( $conn, $s ) . "'"; |
1211
|
|
|
} |
1212
|
|
|
|
1213
|
|
|
/** |
1214
|
|
|
* Postgres specific version of replaceVars. |
1215
|
|
|
* Calls the parent version in Database.php |
1216
|
|
|
* |
1217
|
|
|
* @param string $ins SQL string, read from a stream (usually tables.sql) |
1218
|
|
|
* @return string SQL string |
1219
|
|
|
*/ |
1220
|
|
|
protected function replaceVars( $ins ) { |
1221
|
|
|
$ins = parent::replaceVars( $ins ); |
1222
|
|
|
|
1223
|
|
|
if ( $this->numericVersion >= 8.3 ) { |
1224
|
|
|
// Thanks for not providing backwards-compatibility, 8.3 |
1225
|
|
|
$ins = preg_replace( "/to_tsvector\s*\(\s*'default'\s*,/", 'to_tsvector(', $ins ); |
1226
|
|
|
} |
1227
|
|
|
|
1228
|
|
|
if ( $this->numericVersion <= 8.1 ) { // Our minimum version |
1229
|
|
|
$ins = str_replace( 'USING gin', 'USING gist', $ins ); |
1230
|
|
|
} |
1231
|
|
|
|
1232
|
|
|
return $ins; |
1233
|
|
|
} |
1234
|
|
|
|
1235
|
|
|
public function makeSelectOptions( $options ) { |
1236
|
|
|
$preLimitTail = $postLimitTail = ''; |
1237
|
|
|
$startOpts = $useIndex = $ignoreIndex = ''; |
1238
|
|
|
|
1239
|
|
|
$noKeyOptions = []; |
1240
|
|
|
foreach ( $options as $key => $option ) { |
1241
|
|
|
if ( is_numeric( $key ) ) { |
1242
|
|
|
$noKeyOptions[$option] = true; |
1243
|
|
|
} |
1244
|
|
|
} |
1245
|
|
|
|
1246
|
|
|
$preLimitTail .= $this->makeGroupByWithHaving( $options ); |
1247
|
|
|
|
1248
|
|
|
$preLimitTail .= $this->makeOrderBy( $options ); |
1249
|
|
|
|
1250
|
|
|
// if ( isset( $options['LIMIT'] ) ) { |
1251
|
|
|
// $tailOpts .= $this->limitResult( '', $options['LIMIT'], |
1252
|
|
|
// isset( $options['OFFSET'] ) ? $options['OFFSET'] |
1253
|
|
|
// : false ); |
1254
|
|
|
// } |
1255
|
|
|
|
1256
|
|
|
if ( isset( $options['FOR UPDATE'] ) ) { |
1257
|
|
|
$postLimitTail .= ' FOR UPDATE OF ' . |
1258
|
|
|
implode( ', ', array_map( [ &$this, 'tableName' ], $options['FOR UPDATE'] ) ); |
1259
|
|
|
} elseif ( isset( $noKeyOptions['FOR UPDATE'] ) ) { |
1260
|
|
|
$postLimitTail .= ' FOR UPDATE'; |
1261
|
|
|
} |
1262
|
|
|
|
1263
|
|
|
if ( isset( $noKeyOptions['DISTINCT'] ) || isset( $noKeyOptions['DISTINCTROW'] ) ) { |
1264
|
|
|
$startOpts .= 'DISTINCT'; |
1265
|
|
|
} |
1266
|
|
|
|
1267
|
|
|
return [ $startOpts, $useIndex, $preLimitTail, $postLimitTail, $ignoreIndex ]; |
1268
|
|
|
} |
1269
|
|
|
|
1270
|
|
|
public function getDBname() { |
1271
|
|
|
return $this->mDBname; |
1272
|
|
|
} |
1273
|
|
|
|
1274
|
|
|
public function getServer() { |
1275
|
|
|
return $this->mServer; |
1276
|
|
|
} |
1277
|
|
|
|
1278
|
|
|
public function buildConcat( $stringList ) { |
1279
|
|
|
return implode( ' || ', $stringList ); |
1280
|
|
|
} |
1281
|
|
|
|
1282
|
|
View Code Duplication |
public function buildGroupConcatField( |
1283
|
|
|
$delimiter, $table, $field, $conds = '', $options = [], $join_conds = [] |
1284
|
|
|
) { |
1285
|
|
|
$fld = "array_to_string(array_agg($field)," . $this->addQuotes( $delimiter ) . ')'; |
1286
|
|
|
|
1287
|
|
|
return '(' . $this->selectSQLText( $table, $fld, $conds, null, [], $join_conds ) . ')'; |
1288
|
|
|
} |
1289
|
|
|
|
1290
|
|
|
public function buildStringCast( $field ) { |
1291
|
|
|
return $field . '::text'; |
1292
|
|
|
} |
1293
|
|
|
|
1294
|
|
|
public function streamStatementEnd( &$sql, &$newLine ) { |
1295
|
|
|
# Allow dollar quoting for function declarations |
1296
|
|
|
if ( substr( $newLine, 0, 4 ) == '$mw$' ) { |
1297
|
|
|
if ( $this->delimiter ) { |
1298
|
|
|
$this->delimiter = false; |
|
|
|
|
1299
|
|
|
} else { |
1300
|
|
|
$this->delimiter = ';'; |
1301
|
|
|
} |
1302
|
|
|
} |
1303
|
|
|
|
1304
|
|
|
return parent::streamStatementEnd( $sql, $newLine ); |
1305
|
|
|
} |
1306
|
|
|
|
1307
|
|
View Code Duplication |
public function lockIsFree( $lockName, $method ) { |
1308
|
|
|
// http://www.postgresql.org/docs/8.2/static/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS |
1309
|
|
|
$key = $this->addQuotes( $this->bigintFromLockName( $lockName ) ); |
1310
|
|
|
$result = $this->query( "SELECT (CASE(pg_try_advisory_lock($key)) |
1311
|
|
|
WHEN 'f' THEN 'f' ELSE pg_advisory_unlock($key) END) AS lockstatus", $method ); |
1312
|
|
|
$row = $this->fetchObject( $result ); |
|
|
|
|
1313
|
|
|
|
1314
|
|
|
return ( $row->lockstatus === 't' ); |
1315
|
|
|
} |
1316
|
|
|
|
1317
|
|
|
public function lock( $lockName, $method, $timeout = 5 ) { |
1318
|
|
|
// http://www.postgresql.org/docs/8.2/static/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS |
1319
|
|
|
$key = $this->addQuotes( $this->bigintFromLockName( $lockName ) ); |
1320
|
|
|
$loop = new WaitConditionLoop( |
1321
|
|
|
function () use ( $lockName, $key, $timeout, $method ) { |
1322
|
|
|
$res = $this->query( "SELECT pg_try_advisory_lock($key) AS lockstatus", $method ); |
1323
|
|
|
$row = $this->fetchObject( $res ); |
|
|
|
|
1324
|
|
|
if ( $row->lockstatus === 't' ) { |
1325
|
|
|
parent::lock( $lockName, $method, $timeout ); // record |
1326
|
|
|
return true; |
1327
|
|
|
} |
1328
|
|
|
|
1329
|
|
|
return WaitConditionLoop::CONDITION_CONTINUE; |
1330
|
|
|
}, |
1331
|
|
|
$timeout |
1332
|
|
|
); |
1333
|
|
|
|
1334
|
|
|
return ( $loop->invoke() === $loop::CONDITION_REACHED ); |
1335
|
|
|
} |
1336
|
|
|
|
1337
|
|
|
public function unlock( $lockName, $method ) { |
1338
|
|
|
// http://www.postgresql.org/docs/8.2/static/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS |
1339
|
|
|
$key = $this->addQuotes( $this->bigintFromLockName( $lockName ) ); |
1340
|
|
|
$result = $this->query( "SELECT pg_advisory_unlock($key) as lockstatus", $method ); |
1341
|
|
|
$row = $this->fetchObject( $result ); |
|
|
|
|
1342
|
|
|
|
1343
|
|
|
if ( $row->lockstatus === 't' ) { |
1344
|
|
|
parent::unlock( $lockName, $method ); // record |
1345
|
|
|
return true; |
1346
|
|
|
} |
1347
|
|
|
|
1348
|
|
|
$this->queryLogger->debug( __METHOD__ . " failed to release lock\n" ); |
1349
|
|
|
|
1350
|
|
|
return false; |
1351
|
|
|
} |
1352
|
|
|
|
1353
|
|
|
/** |
1354
|
|
|
* @param string $lockName |
1355
|
|
|
* @return string Integer |
1356
|
|
|
*/ |
1357
|
|
|
private function bigintFromLockName( $lockName ) { |
1358
|
|
|
return Wikimedia\base_convert( substr( sha1( $lockName ), 0, 15 ), 16, 10 ); |
1359
|
|
|
} |
1360
|
|
|
} |
1361
|
|
|
|