1
|
|
|
<?php |
2
|
|
|
|
3
|
|
|
namespace SilverStripe\PostgreSQL; |
4
|
|
|
|
5
|
|
|
use SilverStripe\Dev\Deprecation; |
6
|
|
|
use SilverStripe\ORM\Connect\DBSchemaManager; |
7
|
|
|
use SilverStripe\ORM\DB; |
8
|
|
|
|
9
|
|
|
/** |
10
|
|
|
* PostgreSQL schema manager |
11
|
|
|
* |
12
|
|
|
* @package sapphire |
13
|
|
|
* @subpackage model |
14
|
|
|
*/ |
15
|
|
|
class PostgreSQLSchemaManager extends DBSchemaManager |
16
|
|
|
{ |
17
|
|
|
/** |
18
|
|
|
* Identifier for this schema, used for configuring schema-specific table |
19
|
|
|
* creation options |
20
|
|
|
*/ |
21
|
|
|
const ID = 'PostgreSQL'; |
22
|
|
|
|
23
|
|
|
/** |
24
|
|
|
* Instance of the database controller this schema belongs to |
25
|
|
|
* |
26
|
|
|
* @var PostgreSQLDatabase |
27
|
|
|
*/ |
28
|
|
|
protected $database = null; |
29
|
|
|
|
30
|
|
|
/** |
31
|
|
|
* This holds a copy of all the constraint results that are returned |
32
|
|
|
* via the function constraintExists(). This is a bit faster than |
33
|
|
|
* repeatedly querying this column, and should allow the database |
34
|
|
|
* to use it's built-in caching features for better queries. |
35
|
|
|
* |
36
|
|
|
* @var array |
37
|
|
|
*/ |
38
|
|
|
protected static $cached_constraints = array(); |
39
|
|
|
|
40
|
|
|
/** |
41
|
|
|
* |
42
|
|
|
* This holds a copy of all the queries that run through the function fieldList() |
43
|
|
|
* This is one of the most-often called functions, and repeats itself a great deal in the unit tests. |
44
|
|
|
* |
45
|
|
|
* @var array |
46
|
|
|
*/ |
47
|
|
|
protected static $cached_fieldlists = array(); |
48
|
|
|
|
49
|
|
|
protected function indexKey($table, $index, $spec) |
50
|
|
|
{ |
51
|
|
|
return $this->buildPostgresIndexName($table, $index); |
52
|
|
|
} |
53
|
|
|
|
54
|
|
|
/** |
55
|
|
|
* Creates a postgres database, ignoring model_schema_as_database |
56
|
|
|
* |
57
|
|
|
* @param string $name |
58
|
|
|
*/ |
59
|
|
|
public function createPostgresDatabase($name) |
60
|
|
|
{ |
61
|
|
|
$this->query("CREATE DATABASE \"$name\";"); |
62
|
|
|
} |
63
|
|
|
|
64
|
|
|
public function createDatabase($name) |
65
|
|
|
{ |
66
|
|
|
if (PostgreSQLDatabase::model_schema_as_database()) { |
67
|
|
|
$schemaName = $this->database->databaseToSchemaName($name); |
68
|
|
|
return $this->createSchema($schemaName); |
|
|
|
|
69
|
|
|
} |
70
|
|
|
return $this->createPostgresDatabase($name); |
|
|
|
|
71
|
|
|
} |
72
|
|
|
|
73
|
|
|
/** |
74
|
|
|
* Determines if a postgres database exists, ignoring model_schema_as_database |
75
|
|
|
* |
76
|
|
|
* @param string $name |
77
|
|
|
* @return boolean |
78
|
|
|
*/ |
79
|
|
|
public function postgresDatabaseExists($name) |
80
|
|
|
{ |
81
|
|
|
$result = $this->preparedQuery("SELECT datname FROM pg_database WHERE datname = ?;", array($name)); |
82
|
|
|
return $result->first() ? true : false; |
|
|
|
|
83
|
|
|
} |
84
|
|
|
|
85
|
|
|
public function databaseExists($name) |
86
|
|
|
{ |
87
|
|
|
if (PostgreSQLDatabase::model_schema_as_database()) { |
88
|
|
|
$schemaName = $this->database->databaseToSchemaName($name); |
89
|
|
|
return $this->schemaExists($schemaName); |
90
|
|
|
} |
91
|
|
|
return $this->postgresDatabaseExists($name); |
92
|
|
|
} |
93
|
|
|
|
94
|
|
|
/** |
95
|
|
|
* Determines the list of all postgres databases, ignoring model_schema_as_database |
96
|
|
|
* |
97
|
|
|
* @return array |
98
|
|
|
*/ |
99
|
|
|
public function postgresDatabaseList() |
100
|
|
|
{ |
101
|
|
|
return $this->query("SELECT datname FROM pg_database WHERE datistemplate=false;")->column(); |
102
|
|
|
} |
103
|
|
|
|
104
|
|
|
public function databaseList() |
105
|
|
|
{ |
106
|
|
|
if (PostgreSQLDatabase::model_schema_as_database()) { |
107
|
|
|
$schemas = $this->schemaList(); |
108
|
|
|
$names = array(); |
109
|
|
|
foreach ($schemas as $schema) { |
110
|
|
|
$names[] = $this->database->schemaToDatabaseName($schema); |
111
|
|
|
} |
112
|
|
|
return array_unique($names); |
113
|
|
|
} |
114
|
|
|
return $this->postgresDatabaseList(); |
115
|
|
|
} |
116
|
|
|
|
117
|
|
|
/** |
118
|
|
|
* Drops a postgres database, ignoring model_schema_as_database |
119
|
|
|
* |
120
|
|
|
* @param string $name |
121
|
|
|
*/ |
122
|
|
|
public function dropPostgresDatabase($name) |
123
|
|
|
{ |
124
|
|
|
$nameSQL = $this->database->escapeIdentifier($name); |
125
|
|
|
$this->query("DROP DATABASE $nameSQL;"); |
126
|
|
|
} |
127
|
|
|
|
128
|
|
|
public function dropDatabase($name) |
129
|
|
|
{ |
130
|
|
|
if (PostgreSQLDatabase::model_schema_as_database()) { |
131
|
|
|
$schemaName = $this->database->databaseToSchemaName($name); |
132
|
|
|
$this->dropSchema($schemaName); |
133
|
|
|
return; |
134
|
|
|
} |
135
|
|
|
$this->dropPostgresDatabase($name); |
136
|
|
|
} |
137
|
|
|
|
138
|
|
|
/** |
139
|
|
|
* Returns true if the schema exists in the current database |
140
|
|
|
* |
141
|
|
|
* @param string $name |
142
|
|
|
* @return boolean |
143
|
|
|
*/ |
144
|
|
|
public function schemaExists($name) |
145
|
|
|
{ |
146
|
|
|
return $this->preparedQuery( |
|
|
|
|
147
|
|
|
"SELECT nspname FROM pg_catalog.pg_namespace WHERE nspname = ?;", |
148
|
|
|
array($name) |
149
|
|
|
)->first() ? true : false; |
150
|
|
|
} |
151
|
|
|
|
152
|
|
|
/** |
153
|
|
|
* Creates a schema in the current database |
154
|
|
|
* |
155
|
|
|
* @param string $name |
156
|
|
|
*/ |
157
|
|
|
public function createSchema($name) |
158
|
|
|
{ |
159
|
|
|
$nameSQL = $this->database->escapeIdentifier($name); |
160
|
|
|
$this->query("CREATE SCHEMA $nameSQL;"); |
161
|
|
|
} |
162
|
|
|
|
163
|
|
|
/** |
164
|
|
|
* Drops a schema from the database. Use carefully! |
165
|
|
|
* |
166
|
|
|
* @param string $name |
167
|
|
|
*/ |
168
|
|
|
public function dropSchema($name) |
169
|
|
|
{ |
170
|
|
|
$nameSQL = $this->database->escapeIdentifier($name); |
171
|
|
|
$this->query("DROP SCHEMA $nameSQL CASCADE;"); |
172
|
|
|
} |
173
|
|
|
|
174
|
|
|
/** |
175
|
|
|
* Returns the list of all available schemas on the current database |
176
|
|
|
* |
177
|
|
|
* @return array |
178
|
|
|
*/ |
179
|
|
|
public function schemaList() |
180
|
|
|
{ |
181
|
|
|
return $this->query( |
182
|
|
|
"SELECT nspname |
183
|
|
|
FROM pg_catalog.pg_namespace |
184
|
|
|
WHERE nspname <> 'information_schema' AND nspname !~ E'^pg_'" |
185
|
|
|
)->column(); |
186
|
|
|
} |
187
|
|
|
|
188
|
|
|
public function createTable($table, $fields = null, $indexes = null, $options = null, $advancedOptions = null) |
189
|
|
|
{ |
190
|
|
|
$fieldSchemas = ""; |
191
|
|
|
if ($fields) { |
192
|
|
|
foreach ($fields as $k => $v) { |
193
|
|
|
$fieldSchemas .= "\"$k\" $v,\n"; |
194
|
|
|
} |
195
|
|
|
} |
196
|
|
|
if (!empty($options[self::ID])) { |
197
|
|
|
$addOptions = $options[self::ID]; |
198
|
|
|
} else { |
199
|
|
|
$addOptions = null; |
200
|
|
|
} |
201
|
|
|
|
202
|
|
|
//First of all, does this table already exist |
203
|
|
|
$doesExist = $this->hasTable($table); |
204
|
|
|
if ($doesExist) { |
205
|
|
|
// Table already exists, just return the name, in line with baseclass documentation. |
206
|
|
|
return $table; |
207
|
|
|
} |
208
|
|
|
|
209
|
|
|
//If we have a fulltext search request, then we need to create a special column |
210
|
|
|
//for GiST searches |
211
|
|
|
$fulltexts = ''; |
212
|
|
|
$triggers = []; |
213
|
|
|
if ($indexes) { |
214
|
|
|
foreach ($indexes as $name => $this_index) { |
215
|
|
|
if (is_array($this_index) && $this_index['type'] == 'fulltext') { |
216
|
|
|
$ts_details = $this->fulltext($this_index, $table, $name); |
217
|
|
|
$fulltexts .= $ts_details['fulltexts'] . ', '; |
218
|
|
|
$triggers[] = $ts_details['triggers']; |
219
|
|
|
} |
220
|
|
|
} |
221
|
|
|
} |
222
|
|
|
|
223
|
|
|
$indexQueries = []; |
224
|
|
|
if ($indexes) { |
225
|
|
|
foreach ($indexes as $k => $v) { |
226
|
|
|
$indexQueries[] = $this->getIndexSqlDefinition($table, $k, $v); |
227
|
|
|
} |
228
|
|
|
} |
229
|
|
|
|
230
|
|
|
//Do we need to create a tablespace for this item? |
231
|
|
|
if ($advancedOptions && isset($advancedOptions['tablespace'])) { |
232
|
|
|
$this->createOrReplaceTablespace( |
233
|
|
|
$advancedOptions['tablespace']['name'], |
234
|
|
|
$advancedOptions['tablespace']['location'] |
235
|
|
|
); |
236
|
|
|
$tableSpace = ' TABLESPACE ' . $advancedOptions['tablespace']['name']; |
237
|
|
|
} else { |
238
|
|
|
$tableSpace = ''; |
239
|
|
|
} |
240
|
|
|
|
241
|
|
|
$this->query( |
242
|
|
|
"CREATE TABLE \"$table\" ( |
243
|
|
|
$fieldSchemas |
244
|
|
|
$fulltexts |
245
|
|
|
primary key (\"ID\") |
246
|
|
|
)$tableSpace $addOptions" |
247
|
|
|
); |
248
|
|
|
foreach ($indexQueries as $indexQuery) { |
249
|
|
|
$this->query($indexQuery); |
250
|
|
|
} |
251
|
|
|
|
252
|
|
|
foreach ($triggers as $trigger) { |
253
|
|
|
$this->query($trigger); |
254
|
|
|
} |
255
|
|
|
|
256
|
|
|
//If we have a partitioning requirement, we do that here: |
257
|
|
|
if ($advancedOptions && isset($advancedOptions['partitions'])) { |
258
|
|
|
$this->createOrReplacePartition($table, $advancedOptions['partitions'], $indexes, $advancedOptions); |
259
|
|
|
} |
260
|
|
|
|
261
|
|
|
//Lastly, clustering goes here: |
262
|
|
|
if ($advancedOptions && isset($advancedOptions['cluster'])) { |
263
|
|
|
$this->query("CLUSTER \"$table\" USING \"{$advancedOptions['cluster']}\""); |
264
|
|
|
} |
265
|
|
|
|
266
|
|
|
return $table; |
267
|
|
|
} |
268
|
|
|
|
269
|
|
|
/** |
270
|
|
|
* Builds the internal Postgres index name given the silverstripe table and index name |
271
|
|
|
* |
272
|
|
|
* @param string $tableName |
273
|
|
|
* @param string $indexName |
274
|
|
|
* @param string $prefix The optional prefix for the index. Defaults to "ix" for indexes. |
275
|
|
|
* @return string The postgres name of the index |
276
|
|
|
*/ |
277
|
|
|
protected function buildPostgresIndexName($tableName, $indexName, $prefix = 'ix') |
278
|
|
|
{ |
279
|
|
|
|
280
|
|
|
// Assume all indexes also contain the table name |
281
|
|
|
// MD5 the table/index name combo to keep it to a fixed length. |
282
|
|
|
// Exclude the prefix so that the trigger name can be easily generated from the index name |
283
|
|
|
$indexNamePG = "{$prefix}_" . md5("{$tableName}_{$indexName}"); |
284
|
|
|
|
285
|
|
|
// Limit to 63 characters |
286
|
|
|
if (strlen($indexNamePG) > 63) { |
287
|
|
|
return substr($indexNamePG, 0, 63); |
288
|
|
|
} else { |
289
|
|
|
return $indexNamePG; |
290
|
|
|
} |
291
|
|
|
} |
292
|
|
|
|
293
|
|
|
/** |
294
|
|
|
* Builds the internal Postgres trigger name given the silverstripe table and trigger name |
295
|
|
|
* |
296
|
|
|
* @param string $tableName |
297
|
|
|
* @param string $triggerName |
298
|
|
|
* @return string The postgres name of the trigger |
299
|
|
|
*/ |
300
|
|
|
public function buildPostgresTriggerName($tableName, $triggerName) |
301
|
|
|
{ |
302
|
|
|
// Kind of cheating, but behaves the same way as indexes |
303
|
|
|
return $this->buildPostgresIndexName($tableName, $triggerName, 'ts'); |
304
|
|
|
} |
305
|
|
|
|
306
|
|
|
public function alterTable( |
307
|
|
|
$table, |
308
|
|
|
$newFields = null, |
309
|
|
|
$newIndexes = null, |
310
|
|
|
$alteredFields = null, |
311
|
|
|
$alteredIndexes = null, |
312
|
|
|
$alteredOptions = null, |
313
|
|
|
$advancedOptions = null |
314
|
|
|
) { |
315
|
|
|
$alterList = []; |
316
|
|
|
if ($newFields) { |
317
|
|
|
foreach ($newFields as $fieldName => $fieldSpec) { |
318
|
|
|
$alterList[] = "ADD \"$fieldName\" $fieldSpec"; |
319
|
|
|
} |
320
|
|
|
} |
321
|
|
|
|
322
|
|
|
if ($alteredFields) { |
323
|
|
|
foreach ($alteredFields as $indexName => $indexSpec) { |
324
|
|
|
$val = $this->alterTableAlterColumn($table, $indexName, $indexSpec); |
325
|
|
|
if (!empty($val)) { |
326
|
|
|
$alterList[] = $val; |
327
|
|
|
} |
328
|
|
|
} |
329
|
|
|
} |
330
|
|
|
|
331
|
|
|
//Do we need to do anything with the tablespaces? |
332
|
|
|
if ($alteredOptions && isset($advancedOptions['tablespace'])) { |
333
|
|
|
$this->createOrReplaceTablespace( |
334
|
|
|
$advancedOptions['tablespace']['name'], |
335
|
|
|
$advancedOptions['tablespace']['location'] |
336
|
|
|
); |
337
|
|
|
$this->query("ALTER TABLE \"$table\" SET TABLESPACE {$advancedOptions['tablespace']['name']};"); |
338
|
|
|
} |
339
|
|
|
|
340
|
|
|
//DB ABSTRACTION: we need to change the constraints to be a separate 'add' command, |
341
|
|
|
//see http://www.postgresql.org/docs/8.1/static/sql-altertable.html |
342
|
|
|
$alterIndexList = []; |
343
|
|
|
//Pick up the altered indexes here: |
344
|
|
|
$fieldList = $this->fieldList($table); |
345
|
|
|
$fulltexts = []; |
346
|
|
|
$dropTriggers = []; |
347
|
|
|
$triggers = []; |
348
|
|
|
if ($alteredIndexes) { |
349
|
|
|
foreach ($alteredIndexes as $indexName => $indexSpec) { |
350
|
|
|
$indexNamePG = $this->buildPostgresIndexName($table, $indexName); |
351
|
|
|
|
352
|
|
|
if ($indexSpec['type'] == 'fulltext') { |
353
|
|
|
//For full text indexes, we need to drop the trigger, drop the index, AND drop the column |
354
|
|
|
|
355
|
|
|
//Go and get the tsearch details: |
356
|
|
|
$ts_details = $this->fulltext($indexSpec, $table, $indexName); |
357
|
|
|
|
358
|
|
|
//Drop this column if it already exists: |
359
|
|
|
|
360
|
|
|
//No IF EXISTS option is available for Postgres <9.0 |
361
|
|
|
if (array_key_exists($ts_details['ts_name'], $fieldList)) { |
362
|
|
|
$fulltexts[] = "ALTER TABLE \"{$table}\" DROP COLUMN \"{$ts_details['ts_name']}\";"; |
363
|
|
|
} |
364
|
|
|
|
365
|
|
|
// We'll execute these later: |
366
|
|
|
$triggerNamePG = $this->buildPostgresTriggerName($table, $indexName); |
367
|
|
|
$dropTriggers[] = "DROP TRIGGER IF EXISTS \"$triggerNamePG\" ON \"$table\";"; |
368
|
|
|
$fulltexts[] = "ALTER TABLE \"{$table}\" ADD COLUMN {$ts_details['fulltexts']};"; |
369
|
|
|
$triggers[] = $ts_details['triggers']; |
370
|
|
|
} |
371
|
|
|
|
372
|
|
|
// Create index action (including fulltext) |
373
|
|
|
$alterIndexList[] = "DROP INDEX IF EXISTS \"$indexNamePG\";"; |
374
|
|
|
$createIndex = $this->getIndexSqlDefinition($table, $indexName, $indexSpec); |
375
|
|
|
if ($createIndex) { |
376
|
|
|
$alterIndexList[] = $createIndex; |
377
|
|
|
} |
378
|
|
|
} |
379
|
|
|
} |
380
|
|
|
|
381
|
|
|
//Add the new indexes: |
382
|
|
|
if ($newIndexes) { |
383
|
|
|
foreach ($newIndexes as $indexName => $indexSpec) { |
384
|
|
|
$indexNamePG = $this->buildPostgresIndexName($table, $indexName); |
385
|
|
|
//If we have a fulltext search request, then we need to create a special column |
386
|
|
|
//for GiST searches |
387
|
|
|
//Pick up the new indexes here: |
388
|
|
|
if ($indexSpec['type'] == 'fulltext') { |
389
|
|
|
$ts_details = $this->fulltext($indexSpec, $table, $indexName); |
390
|
|
|
if (!isset($fieldList[$ts_details['ts_name']])) { |
391
|
|
|
$fulltexts[] = "ALTER TABLE \"{$table}\" ADD COLUMN {$ts_details['fulltexts']};"; |
392
|
|
|
$triggers[] = $ts_details['triggers']; |
393
|
|
|
} |
394
|
|
|
} |
395
|
|
|
|
396
|
|
|
//Check that this index doesn't already exist: |
397
|
|
|
$indexes = $this->indexList($table); |
398
|
|
|
if (isset($indexes[$indexName])) { |
399
|
|
|
$alterIndexList[] = "DROP INDEX IF EXISTS \"$indexNamePG\";"; |
400
|
|
|
} |
401
|
|
|
|
402
|
|
|
$createIndex = $this->getIndexSqlDefinition($table, $indexName, $indexSpec); |
403
|
|
|
if ($createIndex) { |
404
|
|
|
$alterIndexList[] = $createIndex; |
405
|
|
|
} |
406
|
|
|
} |
407
|
|
|
} |
408
|
|
|
|
409
|
|
|
if ($alterList) { |
410
|
|
|
$alterations = implode(",\n", $alterList); |
411
|
|
|
$this->query("ALTER TABLE \"$table\" " . $alterations); |
412
|
|
|
} |
413
|
|
|
|
414
|
|
|
//Do we need to create a tablespace for this item? |
415
|
|
|
if ($advancedOptions && isset($advancedOptions['extensions']['tablespace'])) { |
416
|
|
|
$extensions = $advancedOptions['extensions']; |
417
|
|
|
$this->createOrReplaceTablespace($extensions['tablespace']['name'], $extensions['tablespace']['location']); |
418
|
|
|
} |
419
|
|
|
|
420
|
|
|
if ($alteredOptions && isset($this->class) && isset($alteredOptions[$this->class])) { |
421
|
|
|
$this->query(sprintf("ALTER TABLE \"%s\" %s", $table, $alteredOptions[$this->class])); |
422
|
|
|
DB::alteration_message( |
423
|
|
|
sprintf("Table %s options changed: %s", $table, $alteredOptions[$this->class]), |
424
|
|
|
"changed" |
425
|
|
|
); |
426
|
|
|
} |
427
|
|
|
|
428
|
|
|
//Create any fulltext columns and triggers here: |
429
|
|
|
foreach ($fulltexts as $fulltext) { |
430
|
|
|
$this->query($fulltext); |
431
|
|
|
} |
432
|
|
|
foreach ($dropTriggers as $dropTrigger) { |
433
|
|
|
$this->query($dropTrigger); |
434
|
|
|
} |
435
|
|
|
|
436
|
|
|
foreach ($triggers as $trigger) { |
437
|
|
|
$this->query($trigger); |
438
|
|
|
$triggerFields = $this->triggerFieldsFromTrigger($trigger); |
439
|
|
|
if ($triggerFields) { |
440
|
|
|
//We need to run a simple query to force the database to update the triggered columns |
441
|
|
|
$this->query("UPDATE \"{$table}\" SET \"{$triggerFields[0]}\"=\"$triggerFields[0]\";"); |
442
|
|
|
} |
443
|
|
|
} |
444
|
|
|
|
445
|
|
|
foreach ($alterIndexList as $alteration) { |
446
|
|
|
$this->query($alteration); |
447
|
|
|
} |
448
|
|
|
|
449
|
|
|
//If we have a partitioning requirement, we do that here: |
450
|
|
|
if ($advancedOptions && isset($advancedOptions['partitions'])) { |
451
|
|
|
$this->createOrReplacePartition($table, $advancedOptions['partitions']); |
452
|
|
|
} |
453
|
|
|
|
454
|
|
|
//Lastly, clustering goes here: |
455
|
|
|
if ($advancedOptions && isset($advancedOptions['cluster'])) { |
456
|
|
|
$clusterIndex = $this->buildPostgresIndexName($table, $advancedOptions['cluster']); |
457
|
|
|
$this->query("CLUSTER \"$table\" USING \"$clusterIndex\";"); |
458
|
|
|
} else { |
459
|
|
|
//Check that clustering is not on this table, and if it is, remove it: |
460
|
|
|
|
461
|
|
|
//This is really annoying. We need the oid of this table: |
462
|
|
|
$stats = $this->preparedQuery( |
|
|
|
|
463
|
|
|
"SELECT relid FROM pg_stat_user_tables WHERE relname = ?;", |
464
|
|
|
array($table) |
465
|
|
|
)->first(); |
466
|
|
|
$oid = $stats['relid']; |
467
|
|
|
|
468
|
|
|
//Now we can run a long query to get the clustered status: |
469
|
|
|
//If anyone knows a better way to get the clustered status, then feel free to replace this! |
470
|
|
|
$clustered = $this->preparedQuery( |
|
|
|
|
471
|
|
|
" |
472
|
|
|
SELECT c2.relname, i.indisclustered |
473
|
|
|
FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i |
474
|
|
|
WHERE c.oid = ? AND c.oid = i.indrelid AND i.indexrelid = c2.oid AND indisclustered='t';", |
475
|
|
|
array($oid) |
476
|
|
|
)->first(); |
477
|
|
|
|
478
|
|
|
if ($clustered) { |
|
|
|
|
479
|
|
|
$this->query("ALTER TABLE \"$table\" SET WITHOUT CLUSTER;"); |
480
|
|
|
} |
481
|
|
|
} |
482
|
|
|
} |
483
|
|
|
|
484
|
|
|
/* |
485
|
|
|
* Creates an ALTER expression for a column in PostgreSQL |
486
|
|
|
* |
487
|
|
|
* @param $tableName Name of the table to be altered |
488
|
|
|
* @param $colName Name of the column to be altered |
|
|
|
|
489
|
|
|
* @param $colSpec String which contains conditions for a column |
490
|
|
|
* @return string |
491
|
|
|
*/ |
492
|
|
|
private function alterTableAlterColumn($tableName, $colName, $colSpec) |
493
|
|
|
{ |
494
|
|
|
// First, we split the column specifications into parts |
495
|
|
|
// TODO: this returns an empty array for the following string: int(11) not null auto_increment |
496
|
|
|
// on second thoughts, why is an auto_increment field being passed through? |
497
|
|
|
$pattern = '/^([\w(\,)]+)\s?((?:not\s)?null)?\s?(default\s[\w\.\'\\\\]+)?\s?(check\s[\w()\'",\s\\\\]+)?$/i'; |
498
|
|
|
preg_match($pattern, $colSpec, $matches); |
499
|
|
|
// example value this regex is expected to parse: |
500
|
|
|
// varchar(255) not null default 'SS\Test\Player' check ("ClassName" in ('SS\Test\Player', 'Player', null)) |
501
|
|
|
// split into: |
502
|
|
|
// * varchar(255) |
503
|
|
|
// * not null |
504
|
|
|
// * default 'SS\Test\Player' |
505
|
|
|
// * check ("ClassName" in ('SS\Test\Player', 'Player', null)) |
506
|
|
|
|
507
|
|
|
if (sizeof($matches) == 0) { |
508
|
|
|
return ''; |
509
|
|
|
} |
510
|
|
|
|
511
|
|
|
if ($matches[1] == 'serial8') { |
512
|
|
|
return ''; |
513
|
|
|
} |
514
|
|
|
|
515
|
|
|
if (isset($matches[1])) { |
516
|
|
|
$alterCol = "ALTER COLUMN \"$colName\" TYPE $matches[1] USING \"$colName\"::$matches[1]\n"; |
517
|
|
|
|
518
|
|
|
// SET null / not null |
519
|
|
|
if (!empty($matches[2])) { |
520
|
|
|
$alterCol .= ",\nALTER COLUMN \"$colName\" SET $matches[2]"; |
521
|
|
|
} |
522
|
|
|
|
523
|
|
|
// SET default (we drop it first, for reasons of precaution) |
524
|
|
|
if (!empty($matches[3])) { |
525
|
|
|
$alterCol .= ",\nALTER COLUMN \"$colName\" DROP DEFAULT"; |
526
|
|
|
$alterCol .= ",\nALTER COLUMN \"$colName\" SET $matches[3]"; |
527
|
|
|
} |
528
|
|
|
|
529
|
|
|
// SET check constraint (The constraint HAS to be dropped) |
530
|
|
|
$constraintName = "{$tableName}_{$colName}_check"; |
531
|
|
|
$constraintExists = $this->constraintExists($constraintName, false); |
532
|
|
|
if (isset($matches[4])) { |
533
|
|
|
//Take this new constraint and see what's outstanding from the target table: |
534
|
|
|
$constraint_bits = explode('(', $matches[4]); |
535
|
|
|
$constraint_values = trim($constraint_bits[2], ')'); |
536
|
|
|
$constraint_values_bits = explode(',', $constraint_values); |
537
|
|
|
$default = trim($constraint_values_bits[0], " '"); |
538
|
|
|
|
539
|
|
|
//Now go and convert anything that's not in this list to 'Page' |
540
|
|
|
//We have to run this as a query, not as part of the alteration queries due to the way they are constructed. |
541
|
|
|
$updateConstraint = ''; |
542
|
|
|
$updateConstraint .= "UPDATE \"{$tableName}\" SET \"$colName\"='$default' WHERE \"$colName\" NOT IN ($constraint_values);"; |
543
|
|
|
if ($this->hasTable("{$tableName}_Live")) { |
544
|
|
|
$updateConstraint .= "UPDATE \"{$tableName}_Live\" SET \"$colName\"='$default' WHERE \"$colName\" NOT IN ($constraint_values);"; |
545
|
|
|
} |
546
|
|
|
if ($this->hasTable("{$tableName}_Versions")) { |
547
|
|
|
$updateConstraint .= "UPDATE \"{$tableName}_Versions\" SET \"$colName\"='$default' WHERE \"$colName\" NOT IN ($constraint_values);"; |
548
|
|
|
} |
549
|
|
|
|
550
|
|
|
$this->query($updateConstraint); |
551
|
|
|
} |
552
|
|
|
|
553
|
|
|
//First, delete any existing constraint on this column, even if it's no longer an enum |
554
|
|
|
if ($constraintExists) { |
555
|
|
|
$alterCol .= ",\nDROP CONSTRAINT \"{$constraintName}\""; |
556
|
|
|
} |
557
|
|
|
|
558
|
|
|
//Now create the constraint (if we've asked for one) |
559
|
|
|
if (!empty($matches[4])) { |
560
|
|
|
$alterCol .= ",\nADD CONSTRAINT \"{$constraintName}\" $matches[4]"; |
561
|
|
|
} |
562
|
|
|
} |
563
|
|
|
|
564
|
|
|
return isset($alterCol) ? $alterCol : ''; |
565
|
|
|
} |
566
|
|
|
|
567
|
|
|
public function renameTable($oldTableName, $newTableName) |
568
|
|
|
{ |
569
|
|
|
$constraints = $this->getConstraintForTable($oldTableName); |
570
|
|
|
$this->query("ALTER TABLE \"$oldTableName\" RENAME TO \"$newTableName\""); |
571
|
|
|
|
572
|
|
|
if ($constraints) { |
573
|
|
|
foreach ($constraints as $old) { |
574
|
|
|
$new = preg_replace('/^' . $oldTableName . '/', $newTableName, $old); |
575
|
|
|
$this->query("ALTER TABLE \"$newTableName\" RENAME CONSTRAINT \"$old\" TO \"$new\";"); |
576
|
|
|
} |
577
|
|
|
} |
578
|
|
|
unset(self::$cached_fieldlists[$oldTableName]); |
579
|
|
|
unset(self::$cached_constraints[$oldTableName]); |
580
|
|
|
} |
581
|
|
|
|
582
|
|
|
public function checkAndRepairTable($tableName) |
583
|
|
|
{ |
584
|
|
|
$this->query("VACUUM FULL ANALYZE \"$tableName\""); |
585
|
|
|
$this->query("REINDEX TABLE \"$tableName\""); |
586
|
|
|
return true; |
587
|
|
|
} |
588
|
|
|
|
589
|
|
|
public function createField($table, $field, $spec) |
590
|
|
|
{ |
591
|
|
|
$this->query("ALTER TABLE \"$table\" ADD \"$field\" $spec"); |
592
|
|
|
} |
593
|
|
|
|
594
|
|
|
/** |
595
|
|
|
* Change the database type of the given field. |
596
|
|
|
* |
597
|
|
|
* @param string $tableName The name of the tbale the field is in. |
598
|
|
|
* @param string $fieldName The name of the field to change. |
599
|
|
|
* @param string $fieldSpec The new field specification |
600
|
|
|
*/ |
601
|
|
|
public function alterField($tableName, $fieldName, $fieldSpec) |
602
|
|
|
{ |
603
|
|
|
$this->query("ALTER TABLE \"$tableName\" CHANGE \"$fieldName\" \"$fieldName\" $fieldSpec"); |
604
|
|
|
} |
605
|
|
|
|
606
|
|
|
public function renameField($tableName, $oldName, $newName) |
607
|
|
|
{ |
608
|
|
|
$fieldList = $this->fieldList($tableName); |
609
|
|
|
if (array_key_exists($oldName, $fieldList)) { |
610
|
|
|
$this->query("ALTER TABLE \"$tableName\" RENAME COLUMN \"$oldName\" TO \"$newName\""); |
611
|
|
|
|
612
|
|
|
//Remove this from the cached list: |
613
|
|
|
unset(self::$cached_fieldlists[$tableName]); |
614
|
|
|
} |
615
|
|
|
} |
616
|
|
|
|
617
|
|
|
public function fieldList($table) |
618
|
|
|
{ |
619
|
|
|
//Query from http://www.alberton.info/postgresql_meta_info.html |
620
|
|
|
//This gets us more information than we need, but I've included it all for the moment.... |
621
|
|
|
|
622
|
|
|
//if(!isset(self::$cached_fieldlists[$table])){ |
623
|
|
|
$fields = $this->preparedQuery( |
624
|
|
|
" |
625
|
|
|
SELECT ordinal_position, column_name, data_type, column_default, |
626
|
|
|
is_nullable, character_maximum_length, numeric_precision, numeric_scale |
627
|
|
|
FROM information_schema.columns WHERE table_name = ? and table_schema = ? |
628
|
|
|
ORDER BY ordinal_position;", |
629
|
|
|
array($table, $this->database->currentSchema()) |
630
|
|
|
); |
631
|
|
|
|
632
|
|
|
$output = array(); |
633
|
|
|
if ($fields) { |
|
|
|
|
634
|
|
|
foreach ($fields as $field) { |
635
|
|
|
switch ($field['data_type']) { |
636
|
|
|
case 'character varying': |
637
|
|
|
//Check to see if there's a constraint attached to this column: |
638
|
|
|
//$constraint=$this->query("SELECT conname,pg_catalog.pg_get_constraintdef(r.oid, true) FROM pg_catalog.pg_constraint r WHERE r.contype = 'c' AND conname='" . $table . '_' . $field['column_name'] . "_check' ORDER BY 1;")->first(); |
639
|
|
|
$constraint = $this->constraintExists($table . '_' . $field['column_name'] . '_check'); |
640
|
|
|
if ($constraint) { |
641
|
|
|
//Now we need to break this constraint text into bits so we can see what we have: |
642
|
|
|
//Examples: |
643
|
|
|
//CHECK ("CanEditType"::text = ANY (ARRAY['LoggedInUsers'::character varying, 'OnlyTheseUsers'::character varying, 'Inherit'::character varying]::text[])) |
644
|
|
|
//CHECK ("ClassName"::text = 'PageComment'::text) |
645
|
|
|
|
646
|
|
|
//TODO: replace all this with a regular expression! |
647
|
|
|
$value = $constraint['pg_get_constraintdef']; |
648
|
|
|
$value = substr($value, strpos($value, '=')); |
649
|
|
|
$value = str_replace("''", "'", $value); |
650
|
|
|
|
651
|
|
|
$in_value = false; |
652
|
|
|
$constraints = array(); |
653
|
|
|
$current_value = ''; |
654
|
|
|
for ($i = 0; $i < strlen($value); $i++) { |
655
|
|
|
$char = substr($value, $i, 1); |
656
|
|
|
if ($in_value) { |
657
|
|
|
$current_value .= $char; |
658
|
|
|
} |
659
|
|
|
|
660
|
|
|
if ($char == "'") { |
661
|
|
|
if (!$in_value) { |
662
|
|
|
$in_value = true; |
663
|
|
|
} else { |
664
|
|
|
$in_value = false; |
665
|
|
|
$constraints[] = substr($current_value, 0, -1); |
666
|
|
|
$current_value = ''; |
667
|
|
|
} |
668
|
|
|
} |
669
|
|
|
} |
670
|
|
|
|
671
|
|
|
if (sizeof($constraints) > 0) { |
672
|
|
|
//Get the default: |
673
|
|
|
$default = trim(substr( |
674
|
|
|
$field['column_default'], |
675
|
|
|
0, |
676
|
|
|
strpos($field['column_default'], '::') |
677
|
|
|
), "'"); |
678
|
|
|
$output[$field['column_name']] = $this->enum(array( |
679
|
|
|
'default' => $default, |
680
|
|
|
'name' => $field['column_name'], |
681
|
|
|
'enums' => $constraints |
682
|
|
|
)); |
683
|
|
|
} |
684
|
|
|
} else { |
685
|
|
|
$output[$field['column_name']] = 'varchar(' . $field['character_maximum_length'] . ')'; |
686
|
|
|
} |
687
|
|
|
break; |
688
|
|
|
|
689
|
|
|
case 'numeric': |
690
|
|
|
$output[$field['column_name']] = 'decimal(' . $field['numeric_precision'] . ',' . $field['numeric_scale'] . ') default ' . floatval($field['column_default']); |
691
|
|
|
break; |
692
|
|
|
|
693
|
|
|
case 'integer': |
694
|
|
|
$output[$field['column_name']] = 'integer default ' . (int)$field['column_default']; |
695
|
|
|
break; |
696
|
|
|
|
697
|
|
|
case 'timestamp without time zone': |
698
|
|
|
$output[$field['column_name']] = 'timestamp'; |
699
|
|
|
break; |
700
|
|
|
|
701
|
|
|
case 'smallint': |
702
|
|
|
$output[$field['column_name']] = 'smallint default ' . (int)$field['column_default']; |
703
|
|
|
break; |
704
|
|
|
|
705
|
|
|
case 'time without time zone': |
706
|
|
|
$output[$field['column_name']] = 'time'; |
707
|
|
|
break; |
708
|
|
|
|
709
|
|
|
case 'double precision': |
710
|
|
|
$output[$field['column_name']] = 'float'; |
711
|
|
|
break; |
712
|
|
|
|
713
|
|
|
default: |
714
|
|
|
$output[$field['column_name']] = $field; |
715
|
|
|
} |
716
|
|
|
} |
717
|
|
|
} |
718
|
|
|
|
719
|
|
|
// self::$cached_fieldlists[$table]=$output; |
720
|
|
|
//} |
721
|
|
|
|
722
|
|
|
//return self::$cached_fieldlists[$table]; |
723
|
|
|
|
724
|
|
|
return $output; |
725
|
|
|
} |
726
|
|
|
|
727
|
|
|
public function clearCachedFieldlist($tableName = false) |
728
|
|
|
{ |
729
|
|
|
if ($tableName) { |
730
|
|
|
unset(self::$cached_fieldlists[$tableName]); |
731
|
|
|
} else { |
732
|
|
|
self::$cached_fieldlists = array(); |
733
|
|
|
} |
734
|
|
|
return true; |
735
|
|
|
} |
736
|
|
|
|
737
|
|
|
/** |
738
|
|
|
* Create an index on a table. |
739
|
|
|
* |
740
|
|
|
* @param string $tableName The name of the table. |
741
|
|
|
* @param string $indexName The name of the index. |
742
|
|
|
* @param string $indexSpec The specification of the index, see Database::requireIndex() for more details. |
743
|
|
|
*/ |
744
|
|
|
public function createIndex($tableName, $indexName, $indexSpec) |
745
|
|
|
{ |
746
|
|
|
$createIndex = $this->getIndexSqlDefinition($tableName, $indexName, $indexSpec); |
747
|
|
|
if ($createIndex !== false) { |
|
|
|
|
748
|
|
|
$this->query($createIndex); |
749
|
|
|
} |
750
|
|
|
} |
751
|
|
|
|
752
|
|
|
protected function getIndexSqlDefinition($tableName, $indexName, $indexSpec) |
753
|
|
|
{ |
754
|
|
|
|
755
|
|
|
//TODO: create table partition support |
756
|
|
|
//TODO: create clustering options |
757
|
|
|
|
758
|
|
|
//NOTE: it is possible for *_renamed tables to have indexes whose names are not updates |
759
|
|
|
//Therefore, we now check for the existance of indexes before we create them. |
760
|
|
|
//This is techically a bug, since new tables will not be indexed. |
761
|
|
|
|
762
|
|
|
// Determine index name |
763
|
|
|
$tableCol = $this->buildPostgresIndexName($tableName, $indexName); |
764
|
|
|
|
765
|
|
|
//Misc options first: |
766
|
|
|
$fillfactor = $where = ''; |
767
|
|
|
if (isset($indexSpec['fillfactor'])) { |
768
|
|
|
$fillfactor = 'WITH (FILLFACTOR = ' . $indexSpec['fillfactor'] . ')'; |
769
|
|
|
} |
770
|
|
|
if (isset($indexSpec['where'])) { |
771
|
|
|
$where = 'WHERE ' . $indexSpec['where']; |
772
|
|
|
} |
773
|
|
|
|
774
|
|
|
//create a type-specific index |
775
|
|
|
// NOTE: hash should be removed. This is only here to demonstrate how other indexes can be made |
776
|
|
|
// NOTE: Quote the index name to preserve case sensitivity |
777
|
|
|
switch ($indexSpec['type']) { |
778
|
|
|
case 'fulltext': |
779
|
|
|
// @see fulltext() for the definition of the trigger that ts_$IndexName uses for fulltext searching |
780
|
|
|
$clusterMethod = PostgreSQLDatabase::default_fts_cluster_method(); |
781
|
|
|
$spec = "create index \"$tableCol\" ON \"$tableName\" USING $clusterMethod(\"ts_" . $indexName . "\") $fillfactor $where"; |
782
|
|
|
break; |
783
|
|
|
|
784
|
|
|
case 'unique': |
785
|
|
|
$spec = "create unique index \"$tableCol\" ON \"$tableName\" (" . $this->implodeColumnList($indexSpec['columns']) . ") $fillfactor $where"; |
786
|
|
|
break; |
787
|
|
|
|
788
|
|
|
case 'btree': |
789
|
|
|
$spec = "create index \"$tableCol\" ON \"$tableName\" USING btree (" . $this->implodeColumnList($indexSpec['columns']) . ") $fillfactor $where"; |
790
|
|
|
break; |
791
|
|
|
|
792
|
|
|
case 'hash': |
793
|
|
|
//NOTE: this is not a recommended index type |
794
|
|
|
$spec = "create index \"$tableCol\" ON \"$tableName\" USING hash (" . $this->implodeColumnList($indexSpec['columns']) . ") $fillfactor $where"; |
795
|
|
|
break; |
796
|
|
|
|
797
|
|
|
case 'index': |
798
|
|
|
//'index' is the same as default, just a normal index with the default type decided by the database. |
799
|
|
|
default: |
800
|
|
|
$spec = "create index \"$tableCol\" ON \"$tableName\" (" . $this->implodeColumnList($indexSpec['columns']) . ") $fillfactor $where"; |
801
|
|
|
} |
802
|
|
|
return trim($spec) . ';'; |
803
|
|
|
} |
804
|
|
|
|
805
|
|
|
public function alterIndex($tableName, $indexName, $indexSpec) |
806
|
|
|
{ |
807
|
|
|
$indexSpec = trim($indexSpec); |
808
|
|
|
if ($indexSpec[0] != '(') { |
809
|
|
|
list($indexType, $indexFields) = explode(' ', $indexSpec, 2); |
810
|
|
|
} else { |
811
|
|
|
$indexType = null; |
812
|
|
|
$indexFields = $indexSpec; |
813
|
|
|
} |
814
|
|
|
|
815
|
|
|
if (!$indexType) { |
816
|
|
|
$indexType = "index"; |
817
|
|
|
} |
818
|
|
|
|
819
|
|
|
$this->query("DROP INDEX \"$indexName\""); |
820
|
|
|
$this->query("ALTER TABLE \"$tableName\" ADD $indexType \"$indexName\" $indexFields"); |
821
|
|
|
} |
822
|
|
|
|
823
|
|
|
/** |
824
|
|
|
* Given a trigger name attempt to determine the columns upon which it acts |
825
|
|
|
* |
826
|
|
|
* @param string $triggerName Postgres trigger name |
827
|
|
|
* @param string $table |
828
|
|
|
* @return array List of columns |
829
|
|
|
*/ |
830
|
|
|
protected function extractTriggerColumns($triggerName, $table) |
831
|
|
|
{ |
832
|
|
|
$trigger = $this->preparedQuery( |
|
|
|
|
833
|
|
|
"SELECT t.tgargs |
834
|
|
|
FROM pg_catalog.pg_trigger t |
835
|
|
|
INNER JOIN pg_catalog.pg_class c ON c.oid = t.tgrelid |
836
|
|
|
INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace |
837
|
|
|
WHERE c.relname = ? |
838
|
|
|
AND n.nspname = ? |
839
|
|
|
AND t.tgname = ?", |
840
|
|
|
[ |
841
|
|
|
$table, |
842
|
|
|
$this->database->currentSchema(), |
843
|
|
|
$triggerName |
844
|
|
|
] |
845
|
|
|
)->first(); |
846
|
|
|
|
847
|
|
|
// Convert stream to string |
848
|
|
|
if (is_resource($trigger['tgargs'])) { |
849
|
|
|
$trigger['tgargs'] = stream_get_contents($trigger['tgargs']); |
850
|
|
|
} |
851
|
|
|
|
852
|
|
|
if (strpos($trigger['tgargs'], "\000") !== false) { |
853
|
|
|
// Option 1: output as a string (PDO) |
854
|
|
|
$argList = array_filter(explode("\000", $trigger['tgargs'])); |
855
|
|
|
} else { |
856
|
|
|
// Option 2: hex-encoded (pg_sql non-pdo) |
857
|
|
|
$bytes = str_split($trigger['tgargs'], 2); |
858
|
|
|
$argList = array(); |
859
|
|
|
$nextArg = ""; |
860
|
|
|
foreach ($bytes as $byte) { |
861
|
|
|
if ($byte == "00") { |
862
|
|
|
$argList[] = $nextArg; |
863
|
|
|
$nextArg = ""; |
864
|
|
|
} else { |
865
|
|
|
$nextArg .= chr(hexdec($byte)); |
|
|
|
|
866
|
|
|
} |
867
|
|
|
} |
868
|
|
|
} |
869
|
|
|
|
870
|
|
|
// Drop first two arguments (trigger name and config name) and implode into nice list |
871
|
|
|
return array_slice($argList, 2); |
872
|
|
|
} |
873
|
|
|
|
874
|
|
|
public function indexList($table) |
875
|
|
|
{ |
876
|
|
|
//Retrieve a list of indexes for the specified table |
877
|
|
|
$indexes = $this->preparedQuery( |
878
|
|
|
" |
879
|
|
|
SELECT tablename, indexname, indexdef |
880
|
|
|
FROM pg_catalog.pg_indexes |
881
|
|
|
WHERE tablename = ? AND schemaname = ?;", |
882
|
|
|
array($table, $this->database->currentSchema()) |
883
|
|
|
); |
884
|
|
|
|
885
|
|
|
$indexList = array(); |
886
|
|
|
foreach ($indexes as $index) { |
887
|
|
|
// Key for the indexList array. Differs from other DB implementations, which is why |
888
|
|
|
// requireIndex() needed to be overridden |
889
|
|
|
$indexName = $index['indexname']; |
890
|
|
|
|
891
|
|
|
//We don't actually need the entire created command, just a few bits: |
892
|
|
|
$type = ''; |
893
|
|
|
|
894
|
|
|
//Check for uniques: |
895
|
|
|
if (substr($index['indexdef'], 0, 13) == 'CREATE UNIQUE') { |
896
|
|
|
$type = 'unique'; |
897
|
|
|
} |
898
|
|
|
|
899
|
|
|
//check for hashes, btrees etc: |
900
|
|
|
if (strpos(strtolower($index['indexdef']), 'using hash ') !== false) { |
901
|
|
|
$type = 'hash'; |
902
|
|
|
} |
903
|
|
|
|
904
|
|
|
//TODO: Fix me: btree is the default index type: |
905
|
|
|
//if(strpos(strtolower($index['indexdef']), 'using btree ')!==false) |
906
|
|
|
// $prefix='using btree '; |
907
|
|
|
|
908
|
|
|
if (strpos(strtolower($index['indexdef']), 'using rtree ') !== false) { |
909
|
|
|
$type = 'rtree'; |
910
|
|
|
} |
911
|
|
|
|
912
|
|
|
// For fulltext indexes we need to extract the columns from another source |
913
|
|
|
if (stristr($index['indexdef'], 'using gin')) { |
914
|
|
|
$type = 'fulltext'; |
915
|
|
|
// Extract trigger information from postgres |
916
|
|
|
$triggerName = preg_replace('/^ix_/', 'ts_', $index['indexname']); |
917
|
|
|
$columns = $this->extractTriggerColumns($triggerName, $table); |
918
|
|
|
$columnString = $this->implodeColumnList($columns); |
919
|
|
|
} else { |
920
|
|
|
$columnString = $this->quoteColumnSpecString($index['indexdef']); |
921
|
|
|
} |
922
|
|
|
|
923
|
|
|
$indexList[$indexName] = array( |
924
|
|
|
'name' => $indexName, // Not the correct name in the PHP, as this will be a mangled postgres-unique code |
925
|
|
|
'columns' => $this->explodeColumnString($columnString), |
926
|
|
|
'type' => $type ?: 'index', |
927
|
|
|
); |
928
|
|
|
} |
929
|
|
|
|
930
|
|
|
return $indexList; |
931
|
|
|
} |
932
|
|
|
|
933
|
|
|
public function tableList() |
934
|
|
|
{ |
935
|
|
|
$tables = array(); |
936
|
|
|
$result = $this->preparedQuery( |
937
|
|
|
"SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname = ? AND tablename NOT ILIKE 'pg\\\_%' AND tablename NOT ILIKE 'sql\\\_%'", |
938
|
|
|
array($this->database->currentSchema()) |
939
|
|
|
); |
940
|
|
|
foreach ($result as $record) { |
941
|
|
|
$table = reset($record); |
942
|
|
|
$tables[strtolower($table)] = $table; |
943
|
|
|
} |
944
|
|
|
return $tables; |
945
|
|
|
} |
946
|
|
|
|
947
|
|
|
/** |
948
|
|
|
* Find out what the constraint information is, given a constraint name. |
949
|
|
|
* We also cache this result, so the next time we don't need to do a |
950
|
|
|
* query all over again. |
951
|
|
|
* |
952
|
|
|
* @param string $constraint |
953
|
|
|
* @param bool $cache Flag whether a cached version should be used. Set to false to cache bust. |
954
|
|
|
* @return false|array Either false, if the constraint doesn't exist, or an array |
955
|
|
|
* with the keys conname and pg_get_constraintdef |
956
|
|
|
*/ |
957
|
|
|
protected function constraintExists($constraint, $cache = true) |
958
|
|
|
{ |
959
|
|
|
if (!$cache || !isset(self::$cached_constraints[$constraint])) { |
960
|
|
|
$value = $this->preparedQuery( |
|
|
|
|
961
|
|
|
" |
962
|
|
|
SELECT conname,pg_catalog.pg_get_constraintdef(r.oid, true) |
963
|
|
|
FROM pg_catalog.pg_constraint r |
964
|
|
|
INNER JOIN pg_catalog.pg_namespace n |
965
|
|
|
ON r.connamespace = n.oid |
966
|
|
|
WHERE r.contype = 'c' AND conname = ? AND n.nspname = ? |
967
|
|
|
ORDER BY 1;", |
968
|
|
|
array($constraint, $this->database->currentSchema()) |
969
|
|
|
)->first(); |
970
|
|
|
if (!$cache) { |
971
|
|
|
return $value; |
972
|
|
|
} |
973
|
|
|
self::$cached_constraints[$constraint] = $value; |
974
|
|
|
} |
975
|
|
|
|
976
|
|
|
return self::$cached_constraints[$constraint]; |
977
|
|
|
} |
978
|
|
|
|
979
|
|
|
/** |
980
|
|
|
* Retrieve a list of constraints for the provided table name. |
981
|
|
|
* @param string $tableName |
982
|
|
|
* @return array |
983
|
|
|
*/ |
984
|
|
|
private function getConstraintForTable($tableName) |
985
|
|
|
{ |
986
|
|
|
// Note the PostgreSQL `like` operator is case sensitive |
987
|
|
|
$constraints = $this->preparedQuery( |
988
|
|
|
" |
989
|
|
|
SELECT conname |
990
|
|
|
FROM pg_catalog.pg_constraint r |
991
|
|
|
INNER JOIN pg_catalog.pg_namespace n |
992
|
|
|
ON r.connamespace = n.oid |
993
|
|
|
WHERE r.contype = 'c' AND conname like ? AND n.nspname = ? |
994
|
|
|
ORDER BY 1;", |
995
|
|
|
array($tableName . '_%', $this->database->currentSchema()) |
996
|
|
|
)->column('conname'); |
997
|
|
|
|
998
|
|
|
return $constraints; |
999
|
|
|
} |
1000
|
|
|
|
1001
|
|
|
/** |
1002
|
|
|
* A function to return the field names and datatypes for the particular table |
1003
|
|
|
* |
1004
|
|
|
* @param string $tableName |
1005
|
|
|
* @return array List of columns an an associative array with the keys Column and DataType |
1006
|
|
|
*/ |
1007
|
|
|
public function tableDetails($tableName) |
1008
|
|
|
{ |
1009
|
|
|
$query = "SELECT a.attname as \"Column\", pg_catalog.format_type(a.atttypid, a.atttypmod) as \"Datatype\" |
1010
|
|
|
FROM pg_catalog.pg_attribute a |
1011
|
|
|
WHERE a.attnum > 0 AND NOT a.attisdropped AND a.attrelid = ( |
1012
|
|
|
SELECT c.oid |
1013
|
|
|
FROM pg_catalog.pg_class c |
1014
|
|
|
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace |
1015
|
|
|
WHERE c.relname = ? AND pg_catalog.pg_table_is_visible(c.oid) AND n.nspname = ? |
1016
|
|
|
);"; |
1017
|
|
|
|
1018
|
|
|
$result = $this->preparedQuery( |
1019
|
|
|
$query, |
1020
|
|
|
array($tableName, $this->database->currentSchema()) |
1021
|
|
|
); |
1022
|
|
|
|
1023
|
|
|
$table = array(); |
1024
|
|
|
foreach ($result as $row) { |
1025
|
|
|
$table[] = array( |
1026
|
|
|
'Column' => $row['Column'], |
1027
|
|
|
'DataType' => $row['DataType'] |
1028
|
|
|
); |
1029
|
|
|
} |
1030
|
|
|
|
1031
|
|
|
return $table; |
1032
|
|
|
} |
1033
|
|
|
|
1034
|
|
|
/** |
1035
|
|
|
* Pass a legit trigger name and it will be dropped |
1036
|
|
|
* This assumes that the trigger has been named in a unique fashion |
1037
|
|
|
* |
1038
|
|
|
* @param string $triggerName Name of the trigger |
1039
|
|
|
* @param string $tableName Name of the table |
1040
|
|
|
*/ |
1041
|
|
|
protected function dropTrigger($triggerName, $tableName) |
1042
|
|
|
{ |
1043
|
|
|
$exists = $this->preparedQuery( |
|
|
|
|
1044
|
|
|
" |
1045
|
|
|
SELECT trigger_name |
1046
|
|
|
FROM information_schema.triggers |
1047
|
|
|
WHERE trigger_name = ? AND trigger_schema = ?;", |
1048
|
|
|
array($triggerName, $this->database->currentSchema()) |
1049
|
|
|
)->first(); |
1050
|
|
|
if ($exists) { |
|
|
|
|
1051
|
|
|
$this->query("DROP trigger IF EXISTS $triggerName ON \"$tableName\";"); |
1052
|
|
|
} |
1053
|
|
|
} |
1054
|
|
|
|
1055
|
|
|
/** |
1056
|
|
|
* This will return the fields that the trigger is monitoring |
1057
|
|
|
* |
1058
|
|
|
* @param string $trigger Name of the trigger |
1059
|
|
|
* @return array |
1060
|
|
|
*/ |
1061
|
|
|
protected function triggerFieldsFromTrigger($trigger) |
1062
|
|
|
{ |
1063
|
|
|
if ($trigger) { |
1064
|
|
|
$tsvector = 'tsvector_update_trigger'; |
1065
|
|
|
$ts_pos = strpos($trigger, $tsvector); |
1066
|
|
|
$details = trim(substr($trigger, $ts_pos + strlen($tsvector)), '();'); |
1067
|
|
|
//Now split this into bits: |
1068
|
|
|
$bits = explode(',', $details); |
1069
|
|
|
|
1070
|
|
|
$fields = $bits[2]; |
1071
|
|
|
|
1072
|
|
|
$field_bits = explode(',', str_replace('"', '', $fields)); |
1073
|
|
|
$result = array(); |
1074
|
|
|
foreach ($field_bits as $field_bit) { |
1075
|
|
|
$result[] = trim($field_bit); |
1076
|
|
|
} |
1077
|
|
|
|
1078
|
|
|
return $result; |
1079
|
|
|
} else { |
1080
|
|
|
return false; |
|
|
|
|
1081
|
|
|
} |
1082
|
|
|
} |
1083
|
|
|
|
1084
|
|
|
/** |
1085
|
|
|
* Return a boolean type-formatted string |
1086
|
|
|
* |
1087
|
|
|
* @param array $values Contains a tokenised list of info about this data type |
1088
|
|
|
* @return string |
1089
|
|
|
*/ |
1090
|
|
|
public function boolean($values) |
1091
|
|
|
{ |
1092
|
|
|
$default = $values['default'] ? '1' : '0'; |
1093
|
|
|
return "smallint default {$default}"; |
1094
|
|
|
} |
1095
|
|
|
|
1096
|
|
|
/** |
1097
|
|
|
* Return a date type-formatted string |
1098
|
|
|
* |
1099
|
|
|
* @param array $values Contains a tokenised list of info about this data type |
1100
|
|
|
* @return string |
1101
|
|
|
*/ |
1102
|
|
|
public function date($values) |
1103
|
|
|
{ |
1104
|
|
|
return "date"; |
1105
|
|
|
} |
1106
|
|
|
|
1107
|
|
|
/** |
1108
|
|
|
* Return a decimal type-formatted string |
1109
|
|
|
* |
1110
|
|
|
* @param array $values Contains a tokenised list of info about this data type |
1111
|
|
|
* @return string |
1112
|
|
|
*/ |
1113
|
|
|
public function decimal($values) |
1114
|
|
|
{ |
1115
|
|
|
// Avoid empty strings being put in the db |
1116
|
|
|
if ($values['precision'] == '') { |
1117
|
|
|
$precision = 1; |
1118
|
|
|
} else { |
1119
|
|
|
$precision = $values['precision']; |
1120
|
|
|
} |
1121
|
|
|
|
1122
|
|
|
$defaultValue = ''; |
1123
|
|
|
if (isset($values['default']) && is_numeric($values['default'])) { |
1124
|
|
|
$defaultValue = ' default ' . floatval($values['default']); |
1125
|
|
|
} |
1126
|
|
|
|
1127
|
|
|
return "decimal($precision)$defaultValue"; |
1128
|
|
|
} |
1129
|
|
|
|
1130
|
|
|
/** |
1131
|
|
|
* Return a enum type-formatted string |
1132
|
|
|
* |
1133
|
|
|
* @param array $values Contains a tokenised list of info about this data type |
1134
|
|
|
* @return string |
1135
|
|
|
*/ |
1136
|
|
|
public function enum($values) |
1137
|
|
|
{ |
1138
|
|
|
$default = " default '{$values['default']}'"; |
1139
|
|
|
return "varchar(255)" . $default . " check (\"" . $values['name'] . "\" in ('" . implode( |
1140
|
|
|
'\', \'', |
1141
|
|
|
$values['enums'] |
1142
|
|
|
) . "', null))"; |
1143
|
|
|
} |
1144
|
|
|
|
1145
|
|
|
/** |
1146
|
|
|
* Return a float type-formatted string |
1147
|
|
|
* |
1148
|
|
|
* @param array $values Contains a tokenised list of info about this data type |
1149
|
|
|
* @return string |
1150
|
|
|
*/ |
1151
|
|
|
public function float($values) |
1152
|
|
|
{ |
1153
|
|
|
return "float"; |
1154
|
|
|
} |
1155
|
|
|
|
1156
|
|
|
/** |
1157
|
|
|
* Return a float type-formatted string cause double is not supported |
1158
|
|
|
* |
1159
|
|
|
* @param array $values Contains a tokenised list of info about this data type |
1160
|
|
|
* @return string |
1161
|
|
|
*/ |
1162
|
|
|
public function double($values) |
1163
|
|
|
{ |
1164
|
|
|
return $this->float($values); |
1165
|
|
|
} |
1166
|
|
|
|
1167
|
|
|
/** |
1168
|
|
|
* Return a int type-formatted string |
1169
|
|
|
* |
1170
|
|
|
* @param array $values Contains a tokenised list of info about this data type |
1171
|
|
|
* @return string |
1172
|
|
|
*/ |
1173
|
|
|
public function int($values) |
1174
|
|
|
{ |
1175
|
|
|
return "integer default " . (int)$values['default']; |
1176
|
|
|
} |
1177
|
|
|
|
1178
|
|
|
/** |
1179
|
|
|
* Return a bigint type-formatted string |
1180
|
|
|
* |
1181
|
|
|
* @param array $values Contains a tokenised list of info about this data type |
1182
|
|
|
* @return string |
1183
|
|
|
*/ |
1184
|
|
|
public function bigint($values) |
1185
|
|
|
{ |
1186
|
|
|
return "bigint default " . (int)$values['default']; |
1187
|
|
|
} |
1188
|
|
|
|
1189
|
|
|
/** |
1190
|
|
|
* Return a datetime type-formatted string |
1191
|
|
|
* For PostgreSQL, we simply return the word 'timestamp', no other parameters are necessary |
1192
|
|
|
* |
1193
|
|
|
* @param array $values Contains a tokenised list of info about this data type |
1194
|
|
|
* @return string |
1195
|
|
|
*/ |
1196
|
|
|
public function datetime($values) |
1197
|
|
|
{ |
1198
|
|
|
return "timestamp"; |
1199
|
|
|
} |
1200
|
|
|
|
1201
|
|
|
/** |
1202
|
|
|
* Return a text type-formatted string |
1203
|
|
|
* |
1204
|
|
|
* @param array $values Contains a tokenised list of info about this data type |
1205
|
|
|
* @return string |
1206
|
|
|
*/ |
1207
|
|
|
public function text($values) |
1208
|
|
|
{ |
1209
|
|
|
return "text"; |
1210
|
|
|
} |
1211
|
|
|
|
1212
|
|
|
/** |
1213
|
|
|
* Return a time type-formatted string |
1214
|
|
|
* |
1215
|
|
|
* @param array $values Contains a tokenised list of info about this data type |
1216
|
|
|
* @return string |
1217
|
|
|
*/ |
1218
|
|
|
public function time($values) |
1219
|
|
|
{ |
1220
|
|
|
return "time"; |
1221
|
|
|
} |
1222
|
|
|
|
1223
|
|
|
/** |
1224
|
|
|
* Return a varchar type-formatted string |
1225
|
|
|
* |
1226
|
|
|
* @param array $values Contains a tokenised list of info about this data type |
1227
|
|
|
* @return string |
1228
|
|
|
*/ |
1229
|
|
|
public function varchar($values) |
1230
|
|
|
{ |
1231
|
|
|
if (!isset($values['precision'])) { |
1232
|
|
|
$values['precision'] = 255; |
1233
|
|
|
} |
1234
|
|
|
|
1235
|
|
|
return "varchar({$values['precision']})"; |
1236
|
|
|
} |
1237
|
|
|
|
1238
|
|
|
/* |
1239
|
|
|
* Return a 4 digit numeric type. MySQL has a proprietary 'Year' type. |
1240
|
|
|
* For Postgres, we'll use a 4 digit numeric |
1241
|
|
|
* |
1242
|
|
|
* @param array $values Contains a tokenised list of info about this data type |
1243
|
|
|
* @return string |
1244
|
|
|
*/ |
1245
|
|
|
public function year($values) |
1246
|
|
|
{ |
1247
|
|
|
return "decimal(4,0)"; |
1248
|
|
|
} |
1249
|
|
|
|
1250
|
|
|
/** |
1251
|
|
|
* Create a fulltext search datatype for PostgreSQL |
1252
|
|
|
* This will also return a trigger to be applied to this table |
1253
|
|
|
* |
1254
|
|
|
* @todo: create custom functions to allow weighted searches |
1255
|
|
|
* |
1256
|
|
|
* @param array $this_index Index specification for the fulltext index |
1257
|
|
|
* @param string $tableName |
1258
|
|
|
* @param string $name |
1259
|
|
|
* @return array |
1260
|
|
|
*/ |
1261
|
|
|
protected function fulltext($this_index, $tableName, $name) |
1262
|
|
|
{ |
1263
|
|
|
//For full text search, we need to create a column for the index |
1264
|
|
|
$columns = $this->implodeColumnList($this_index['columns']); |
1265
|
|
|
|
1266
|
|
|
$fulltexts = "\"ts_$name\" tsvector"; |
1267
|
|
|
$triggerName = $this->buildPostgresTriggerName($tableName, $name); |
1268
|
|
|
$language = PostgreSQLDatabase::search_language(); |
1269
|
|
|
|
1270
|
|
|
$this->dropTrigger($triggerName, $tableName); |
1271
|
|
|
$triggers = "CREATE TRIGGER \"$triggerName\" BEFORE INSERT OR UPDATE |
1272
|
|
|
ON \"$tableName\" FOR EACH ROW EXECUTE PROCEDURE |
1273
|
|
|
tsvector_update_trigger(\"ts_$name\", 'pg_catalog.$language', $columns);"; |
1274
|
|
|
|
1275
|
|
|
return array( |
1276
|
|
|
'name' => $name, |
1277
|
|
|
'ts_name' => "ts_{$name}", |
1278
|
|
|
'fulltexts' => $fulltexts, |
1279
|
|
|
'triggers' => $triggers |
1280
|
|
|
); |
1281
|
|
|
} |
1282
|
|
|
|
1283
|
|
|
public function IdColumn($asDbValue = false, $hasAutoIncPK = true) |
1284
|
|
|
{ |
1285
|
|
|
if ($asDbValue) { |
1286
|
|
|
return 'bigint'; |
1287
|
|
|
} else { |
1288
|
|
|
return 'serial8 not null'; |
1289
|
|
|
} |
1290
|
|
|
} |
1291
|
|
|
|
1292
|
|
|
public function hasTable($tableName) |
1293
|
|
|
{ |
1294
|
|
|
$result = $this->preparedQuery( |
1295
|
|
|
"SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname = ? AND tablename = ?;", |
1296
|
|
|
array($this->database->currentSchema(), $tableName) |
1297
|
|
|
); |
1298
|
|
|
return ($result->numRecords() > 0); |
1299
|
|
|
} |
1300
|
|
|
|
1301
|
|
|
/** |
1302
|
|
|
* Returns the values of the given enum field |
1303
|
|
|
* |
1304
|
|
|
* @todo Make a proper implementation |
1305
|
|
|
* |
1306
|
|
|
* @param string $tableName Name of table to check |
1307
|
|
|
* @param string $fieldName name of enum field to check |
1308
|
|
|
* @return array List of enum values |
1309
|
|
|
*/ |
1310
|
|
|
public function enumValuesForField($tableName, $fieldName) |
1311
|
|
|
{ |
1312
|
|
|
//return array('SiteTree','Page'); |
1313
|
|
|
$constraints = $this->constraintExists("{$tableName}_{$fieldName}_check"); |
1314
|
|
|
if ($constraints) { |
1315
|
|
|
return $this->enumValuesFromConstraint($constraints['pg_get_constraintdef']); |
1316
|
|
|
} else { |
1317
|
|
|
return array(); |
1318
|
|
|
} |
1319
|
|
|
} |
1320
|
|
|
|
1321
|
|
|
/** |
1322
|
|
|
* Get the actual enum fields from the constraint value: |
1323
|
|
|
* |
1324
|
|
|
* @param string $constraint |
1325
|
|
|
* @return array |
1326
|
|
|
*/ |
1327
|
|
|
protected function enumValuesFromConstraint($constraint) |
1328
|
|
|
{ |
1329
|
|
|
$constraint = substr($constraint, strpos($constraint, 'ANY (ARRAY[') + 11); |
1330
|
|
|
$constraint = substr($constraint, 0, -11); |
1331
|
|
|
$constraints = array(); |
1332
|
|
|
$segments = explode(',', $constraint); |
1333
|
|
|
foreach ($segments as $this_segment) { |
1334
|
|
|
$bits = preg_split('/ *:: */', $this_segment); |
1335
|
|
|
array_unshift($constraints, trim($bits[0], " '")); |
1336
|
|
|
} |
1337
|
|
|
return $constraints; |
1338
|
|
|
} |
1339
|
|
|
|
1340
|
|
|
public function dbDataType($type) |
1341
|
|
|
{ |
1342
|
|
|
$values = array( |
1343
|
|
|
'unsigned integer' => 'INT' |
1344
|
|
|
); |
1345
|
|
|
|
1346
|
|
|
if (isset($values[$type])) { |
1347
|
|
|
return $values[$type]; |
1348
|
|
|
} else { |
1349
|
|
|
return ''; |
1350
|
|
|
} |
1351
|
|
|
} |
1352
|
|
|
|
1353
|
|
|
/* |
1354
|
|
|
* Given a tablespace and and location, either create a new one |
1355
|
|
|
* or update the existing one |
1356
|
|
|
* |
1357
|
|
|
* @param string $name |
1358
|
|
|
* @param string $location |
1359
|
|
|
*/ |
1360
|
|
|
public function createOrReplaceTablespace($name, $location) |
1361
|
|
|
{ |
1362
|
|
|
$existing = $this->preparedQuery( |
|
|
|
|
1363
|
|
|
"SELECT spcname, spclocation FROM pg_tablespace WHERE spcname = ?;", |
1364
|
|
|
array($name) |
1365
|
|
|
)->first(); |
1366
|
|
|
|
1367
|
|
|
//NOTE: this location must be empty for this to work |
1368
|
|
|
//We can't seem to change the location of the tablespace through any ALTER commands :( |
1369
|
|
|
|
1370
|
|
|
//If a tablespace with this name exists, but the location has changed, then drop the current one |
1371
|
|
|
//if($existing && $location!=$existing['spclocation']) |
1372
|
|
|
// DB::query("DROP TABLESPACE $name;"); |
1373
|
|
|
|
1374
|
|
|
//If this is a new tablespace, or we have dropped the current one: |
1375
|
|
|
if (!$existing || ($existing && $location != $existing['spclocation'])) { |
|
|
|
|
1376
|
|
|
$this->query("CREATE TABLESPACE $name LOCATION '$location';"); |
1377
|
|
|
} |
1378
|
|
|
} |
1379
|
|
|
|
1380
|
|
|
/** |
1381
|
|
|
* |
1382
|
|
|
* @param string $tableName |
1383
|
|
|
* @param array $partitions |
1384
|
|
|
* @param array $indexes |
1385
|
|
|
* @param array $extensions |
1386
|
|
|
*/ |
1387
|
|
|
public function createOrReplacePartition($tableName, $partitions, $indexes = [], $extensions = []) |
1388
|
|
|
{ |
1389
|
|
|
|
1390
|
|
|
//We need the plpgsql language to be installed for this to work: |
1391
|
|
|
$this->createLanguage('plpgsql'); |
1392
|
|
|
|
1393
|
|
|
$trigger = 'CREATE OR REPLACE FUNCTION ' . $tableName . '_insert_trigger() RETURNS TRIGGER AS $$ BEGIN '; |
1394
|
|
|
$first = true; |
1395
|
|
|
|
1396
|
|
|
//Do we need to create a tablespace for this item? |
1397
|
|
|
if ($extensions && isset($extensions['tablespace'])) { |
|
|
|
|
1398
|
|
|
$this->createOrReplaceTablespace($extensions['tablespace']['name'], $extensions['tablespace']['location']); |
1399
|
|
|
$tableSpace = ' TABLESPACE ' . $extensions['tablespace']['name']; |
1400
|
|
|
} else { |
1401
|
|
|
$tableSpace = ''; |
1402
|
|
|
} |
1403
|
|
|
|
1404
|
|
|
foreach ($partitions as $partition_name => $partition_value) { |
1405
|
|
|
//Check that this child table does not already exist: |
1406
|
|
|
if (!$this->hasTable($partition_name)) { |
1407
|
|
|
$this->query("CREATE TABLE \"$partition_name\" (CHECK (" . str_replace( |
1408
|
|
|
'NEW.', |
1409
|
|
|
'', |
1410
|
|
|
$partition_value |
1411
|
|
|
) . ")) INHERITS (\"$tableName\")$tableSpace;"); |
1412
|
|
|
} else { |
1413
|
|
|
//Drop the constraint, we will recreate in in the next line |
1414
|
|
|
$constraintName = "{$partition_name}_pkey"; |
1415
|
|
|
$constraintExists = $this->constraintExists($constraintName, false); |
1416
|
|
|
if ($constraintExists) { |
1417
|
|
|
$this->query("ALTER TABLE \"$partition_name\" DROP CONSTRAINT \"{$constraintName}\";"); |
1418
|
|
|
} |
1419
|
|
|
$this->dropTrigger(strtolower('trigger_' . $tableName . '_insert'), $tableName); |
1420
|
|
|
} |
1421
|
|
|
|
1422
|
|
|
$this->query("ALTER TABLE \"$partition_name\" ADD CONSTRAINT \"{$partition_name}_pkey\" PRIMARY KEY (\"ID\");"); |
1423
|
|
|
|
1424
|
|
|
if ($first) { |
1425
|
|
|
$trigger .= 'IF'; |
1426
|
|
|
$first = false; |
1427
|
|
|
} else { |
1428
|
|
|
$trigger .= 'ELSIF'; |
1429
|
|
|
} |
1430
|
|
|
|
1431
|
|
|
$trigger .= " ($partition_value) THEN INSERT INTO \"$partition_name\" VALUES (NEW.*);"; |
1432
|
|
|
|
1433
|
|
|
if ($indexes) { |
|
|
|
|
1434
|
|
|
// We need to propogate the indexes through to the child pages. |
1435
|
|
|
// Some of this code is duplicated, and could be tidied up |
1436
|
|
|
foreach ($indexes as $name => $this_index) { |
1437
|
|
|
if ($this_index['type'] == 'fulltext') { |
1438
|
|
|
$fillfactor = $where = ''; |
1439
|
|
|
if (isset($this_index['fillfactor'])) { |
1440
|
|
|
$fillfactor = 'WITH (FILLFACTOR = ' . $this_index['fillfactor'] . ')'; |
1441
|
|
|
} |
1442
|
|
|
if (isset($this_index['where'])) { |
1443
|
|
|
$where = 'WHERE ' . $this_index['where']; |
1444
|
|
|
} |
1445
|
|
|
$clusterMethod = PostgreSQLDatabase::default_fts_cluster_method(); |
1446
|
|
|
$this->query("CREATE INDEX \"" . $this->buildPostgresIndexName( |
1447
|
|
|
$partition_name, |
1448
|
|
|
$this_index['name'] |
1449
|
|
|
) . "\" ON \"" . $partition_name . "\" USING $clusterMethod(\"ts_" . $name . "\") $fillfactor $where"); |
1450
|
|
|
$ts_details = $this->fulltext($this_index, $partition_name, $name); |
1451
|
|
|
$this->query($ts_details['triggers']); |
1452
|
|
|
} else { |
1453
|
|
|
if (is_array($this_index)) { |
1454
|
|
|
$index_name = $this_index['name']; |
1455
|
|
|
} else { |
1456
|
|
|
$index_name = trim($this_index, '()'); |
1457
|
|
|
} |
1458
|
|
|
|
1459
|
|
|
$createIndex = $this->getIndexSqlDefinition($partition_name, $index_name, $this_index); |
1460
|
|
|
if ($createIndex !== false) { |
1461
|
|
|
$this->query($createIndex); |
1462
|
|
|
} |
1463
|
|
|
} |
1464
|
|
|
} |
1465
|
|
|
} |
1466
|
|
|
|
1467
|
|
|
//Lastly, clustering goes here: |
1468
|
|
|
if ($extensions && isset($extensions['cluster'])) { |
1469
|
|
|
$this->query("CLUSTER \"$partition_name\" USING \"{$extensions['cluster']}\";"); |
1470
|
|
|
} |
1471
|
|
|
} |
1472
|
|
|
|
1473
|
|
|
$trigger .= 'ELSE RAISE EXCEPTION \'Value id out of range. Fix the ' . $tableName . '_insert_trigger() function!\'; END IF; RETURN NULL; END; $$ LANGUAGE plpgsql;'; |
1474
|
|
|
$trigger .= 'CREATE TRIGGER trigger_' . $tableName . '_insert BEFORE INSERT ON "' . $tableName . '" FOR EACH ROW EXECUTE PROCEDURE ' . $tableName . '_insert_trigger();'; |
1475
|
|
|
|
1476
|
|
|
$this->query($trigger); |
1477
|
|
|
} |
1478
|
|
|
|
1479
|
|
|
/* |
1480
|
|
|
* This will create a language if it doesn't already exist. |
1481
|
|
|
* This is used by the createOrReplacePartition function, which needs plpgsql |
1482
|
|
|
* |
1483
|
|
|
* @param string $language Language name |
1484
|
|
|
*/ |
1485
|
|
|
public function createLanguage($language) |
1486
|
|
|
{ |
1487
|
|
|
$result = $this->preparedQuery( |
|
|
|
|
1488
|
|
|
"SELECT lanname FROM pg_language WHERE lanname = ?;", |
1489
|
|
|
array($language) |
1490
|
|
|
)->first(); |
1491
|
|
|
|
1492
|
|
|
if (!$result) { |
|
|
|
|
1493
|
|
|
$this->query("CREATE LANGUAGE $language;"); |
1494
|
|
|
} |
1495
|
|
|
} |
1496
|
|
|
|
1497
|
|
|
/** |
1498
|
|
|
* Return a set type-formatted string |
1499
|
|
|
* This is used for Multi-enum support, which isn't actually supported by Postgres. |
1500
|
|
|
* Throws a user error to show our lack of support, and return an "int", specifically for sapphire |
1501
|
|
|
* tests that test multi-enums. This results in a test failure, but not crashing the test run. |
1502
|
|
|
* |
1503
|
|
|
* @param array $values Contains a tokenised list of info about this data type |
1504
|
|
|
* @return string |
1505
|
|
|
*/ |
1506
|
|
|
public function set($values) |
1507
|
|
|
{ |
1508
|
|
|
user_error("PostGreSQL does not support multi-enum", E_USER_ERROR); |
1509
|
|
|
return "int"; |
1510
|
|
|
} |
1511
|
|
|
} |
1512
|
|
|
|
This check looks for function or method calls that always return null and whose return value is used.
The method
getObject()
can return nothing but null, so it makes no sense to use the return value.The reason is most likely that a function or method is imcomplete or has been reduced for debug purposes.