2 // This file is part of Moodle - http://moodle.org/
4 // Moodle is free software: you can redistribute it and/or modify
5 // it under the terms of the GNU General Public License as published by
6 // the Free Software Foundation, either version 3 of the License, or
7 // (at your option) any later version.
9 // Moodle is distributed in the hope that it will be useful,
10 // but WITHOUT ANY WARRANTY; without even the implied warranty of
11 // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
12 // GNU General Public License for more details.
14 // You should have received a copy of the GNU General Public License
15 // along with Moodle. If not, see <http://www.gnu.org/licenses/>.
18 * PostgreSQL specific SQL code generator.
21 * @copyright 1999 onwards Martin Dougiamas http://dougiamas.com
22 * 2001-3001 Eloy Lafuente (stronk7) http://contiento.com
23 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
26 defined('MOODLE_INTERNAL') ||
die();
28 require_once($CFG->libdir
.'/ddl/sql_generator.php');
31 * This class generate SQL code to be used against PostgreSQL
32 * It extends XMLDBgenerator so everything can be
33 * overridden as needed to generate correct SQL.
36 * @copyright 1999 onwards Martin Dougiamas http://dougiamas.com
37 * 2001-3001 Eloy Lafuente (stronk7) http://contiento.com
38 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
41 class postgres_sql_generator
extends sql_generator
{
43 // Only set values that are different from the defaults present in XMLDBgenerator
45 /** @var string Proper type for NUMBER(x) in this DB. */
46 public $number_type = 'NUMERIC';
48 /** @var string To define the default to set for NOT NULLs CHARs without default (null=do nothing).*/
49 public $default_for_char = '';
51 /** @var bool True if the generator needs to add extra code to generate the sequence fields.*/
52 public $sequence_extra_code = false;
54 /** @var string The particular name for inline sequences in this generator.*/
55 public $sequence_name = 'BIGSERIAL';
57 /** @var string The particular name for inline sequences in this generator.*/
58 public $sequence_name_small = 'SERIAL';
60 /** @var bool To avoid outputting the rest of the field specs, leaving only the name and the sequence_name returned.*/
61 public $sequence_only = true;
63 /** @var string SQL sentence to rename one index where 'TABLENAME', 'OLDINDEXNAME' and 'NEWINDEXNAME' are dynamically replaced.*/
64 public $rename_index_sql = 'ALTER TABLE OLDINDEXNAME RENAME TO NEWINDEXNAME';
66 /** @var string SQL sentence to rename one key 'TABLENAME', 'OLDKEYNAME' and 'NEWKEYNAME' are dynamically replaced.*/
67 public $rename_key_sql = null;
69 /** @var string type of string quoting used - '' or \' quotes*/
70 protected $std_strings = null;
73 * Reset a sequence to the id field of a table.
75 * @param xmldb_table|string $table name of table or the table object.
76 * @return array of sql statements
78 public function getResetSequenceSQL($table) {
80 if ($table instanceof xmldb_table
) {
81 $tablename = $table->getName();
86 // From http://www.postgresql.org/docs/7.4/static/sql-altersequence.html
87 $value = (int)$this->mdb
->get_field_sql('SELECT MAX(id) FROM {'.$tablename.'}');
89 return array("ALTER SEQUENCE $this->prefix{$tablename}_id_seq RESTART WITH $value");
93 * Given one correct xmldb_table, returns the SQL statements
94 * to create temporary table (inside one array).
96 * @param xmldb_table $xmldb_table The xmldb_table object instance.
97 * @return array of sql statements
99 public function getCreateTempTableSQL($xmldb_table) {
100 $this->temptables
->add_temptable($xmldb_table->getName());
101 $sqlarr = $this->getCreateTableSQL($xmldb_table);
102 $sqlarr = preg_replace('/^CREATE TABLE/', "CREATE TEMPORARY TABLE", $sqlarr);
107 * Given one correct xmldb_index, returns the SQL statements
108 * needed to create it (in array).
110 * @param xmldb_table $xmldb_table The xmldb_table instance to create the index on.
111 * @param xmldb_index $xmldb_index The xmldb_index to create.
112 * @return array An array of SQL statements to create the index.
113 * @throws coding_exception Thrown if the xmldb_index does not validate with the xmldb_table.
115 public function getCreateIndexSQL($xmldb_table, $xmldb_index) {
116 $sqls = parent
::getCreateIndexSQL($xmldb_table, $xmldb_index);
118 $hints = $xmldb_index->getHints();
119 $fields = $xmldb_index->getFields();
120 if (in_array('varchar_pattern_ops', $hints) and count($fields) == 1) {
121 // Add the pattern index and keep the normal one, keep unique only the standard index to improve perf.
122 foreach ($sqls as $sql) {
123 $field = reset($fields);
125 $newindex = preg_replace("/^CREATE( UNIQUE)? INDEX ([a-z0-9_]+) ON ([a-z0-9_]+) \($field\)$/", "CREATE INDEX \\2_pattern ON \\3 USING btree ($field varchar_pattern_ops)", $sql, -1, $count);
127 debugging('Unexpected getCreateIndexSQL() structure.');
138 * Given one XMLDB Type, length and decimals, returns the DB proper SQL type.
140 * @param int $xmldb_type The xmldb_type defined constant. XMLDB_TYPE_INTEGER and other XMLDB_TYPE_* constants.
141 * @param int $xmldb_length The length of that data type.
142 * @param int $xmldb_decimals The decimal places of precision of the data type.
143 * @return string The DB defined data type.
145 public function getTypeSQL($xmldb_type, $xmldb_length=null, $xmldb_decimals=null) {
147 switch ($xmldb_type) {
148 case XMLDB_TYPE_INTEGER
: // From http://www.postgresql.org/docs/7.4/interactive/datatype.html
149 if (empty($xmldb_length)) {
152 if ($xmldb_length > 9) {
154 } else if ($xmldb_length > 4) {
157 $dbtype = 'SMALLINT';
160 case XMLDB_TYPE_NUMBER
:
161 $dbtype = $this->number_type
;
162 if (!empty($xmldb_length)) {
163 $dbtype .= '(' . $xmldb_length;
164 if (!empty($xmldb_decimals)) {
165 $dbtype .= ',' . $xmldb_decimals;
170 case XMLDB_TYPE_FLOAT
:
171 $dbtype = 'DOUBLE PRECISION';
172 if (!empty($xmldb_decimals)) {
173 if ($xmldb_decimals < 6) {
178 case XMLDB_TYPE_CHAR
:
180 if (empty($xmldb_length)) {
183 $dbtype .= '(' . $xmldb_length . ')';
185 case XMLDB_TYPE_TEXT
:
188 case XMLDB_TYPE_BINARY
:
191 case XMLDB_TYPE_DATETIME
:
192 $dbtype = 'TIMESTAMP';
199 * Returns the code (array of statements) needed to add one comment to the table.
201 * @param xmldb_table $xmldb_table The xmldb_table object instance.
202 * @return array Array of SQL statements to add one comment to the table.
204 function getCommentSQL($xmldb_table) {
206 $comment = "COMMENT ON TABLE " . $this->getTableName($xmldb_table);
207 $comment.= " IS '" . $this->addslashes(substr($xmldb_table->getComment(), 0, 250)) . "'";
209 return array($comment);
213 * Returns the code (array of statements) needed to execute extra statements on table rename.
215 * @param xmldb_table $xmldb_table The xmldb_table object instance.
216 * @param string $newname The new name for the table.
217 * @return array Array of extra SQL statements to rename a table.
219 public function getRenameTableExtraSQL($xmldb_table, $newname) {
223 $newt = new xmldb_table($newname);
225 $xmldb_field = new xmldb_field('id'); // Fields having sequences should be exclusively, id.
227 $oldseqname = $this->getTableName($xmldb_table) . '_' . $xmldb_field->getName() . '_seq';
228 $newseqname = $this->getTableName($newt) . '_' . $xmldb_field->getName() . '_seq';
230 // Rename de sequence
231 $results[] = 'ALTER TABLE ' . $oldseqname . ' RENAME TO ' . $newseqname;
237 * Given one xmldb_table and one xmldb_field, return the SQL statements needed to alter the field in the table.
239 * PostgreSQL has some severe limits:
240 * - Any change of type or precision requires a new temporary column to be created, values to
241 * be transfered potentially casting them, to apply defaults if the column is not null and
242 * finally, to rename it
243 * - Changes in null/not null require the SET/DROP NOT NULL clause
244 * - Changes in default require the SET/DROP DEFAULT clause
246 * @param xmldb_table $xmldb_table The table related to $xmldb_field.
247 * @param xmldb_field $xmldb_field The instance of xmldb_field to create the SQL from.
248 * @param string $skip_type_clause The type clause on alter columns, NULL by default.
249 * @param string $skip_default_clause The default clause on alter columns, NULL by default.
250 * @param string $skip_notnull_clause The null/notnull clause on alter columns, NULL by default.
251 * @return string The field altering SQL statement.
253 public function getAlterFieldSQL($xmldb_table, $xmldb_field, $skip_type_clause = NULL, $skip_default_clause = NULL, $skip_notnull_clause = NULL) {
254 $results = array(); // To store all the needed SQL commands
256 // Get the normal names of the table and field
257 $tablename = $xmldb_table->getName();
258 $fieldname = $xmldb_field->getName();
260 // Take a look to field metadata
261 $meta = $this->mdb
->get_columns($tablename);
262 $metac = $meta[$xmldb_field->getName()];
263 $oldmetatype = $metac->meta_type
;
264 $oldlength = $metac->max_length
;
265 $olddecimals = empty($metac->scale
) ?
null : $metac->scale
;
266 $oldnotnull = empty($metac->not_null
) ?
false : $metac->not_null
;
267 $olddefault = empty($metac->has_default
) ?
null : $metac->default_value
;
269 $typechanged = true; //By default, assume that the column type has changed
270 $precisionchanged = true; //By default, assume that the column precision has changed
271 $decimalchanged = true; //By default, assume that the column decimal has changed
272 $defaultchanged = true; //By default, assume that the column default has changed
273 $notnullchanged = true; //By default, assume that the column notnull has changed
275 // Detect if we are changing the type of the column
276 if (($xmldb_field->getType() == XMLDB_TYPE_INTEGER
&& $oldmetatype == 'I') ||
277 ($xmldb_field->getType() == XMLDB_TYPE_NUMBER
&& $oldmetatype == 'N') ||
278 ($xmldb_field->getType() == XMLDB_TYPE_FLOAT
&& $oldmetatype == 'F') ||
279 ($xmldb_field->getType() == XMLDB_TYPE_CHAR
&& $oldmetatype == 'C') ||
280 ($xmldb_field->getType() == XMLDB_TYPE_TEXT
&& $oldmetatype == 'X') ||
281 ($xmldb_field->getType() == XMLDB_TYPE_BINARY
&& $oldmetatype == 'B')) {
282 $typechanged = false;
284 // Detect if we are changing the precision
285 if (($xmldb_field->getType() == XMLDB_TYPE_TEXT
) ||
286 ($xmldb_field->getType() == XMLDB_TYPE_BINARY
) ||
287 ($oldlength == -1) ||
288 ($xmldb_field->getLength() == $oldlength)) {
289 $precisionchanged = false;
291 // Detect if we are changing the decimals
292 if (($xmldb_field->getType() == XMLDB_TYPE_INTEGER
) ||
293 ($xmldb_field->getType() == XMLDB_TYPE_CHAR
) ||
294 ($xmldb_field->getType() == XMLDB_TYPE_TEXT
) ||
295 ($xmldb_field->getType() == XMLDB_TYPE_BINARY
) ||
296 (!$xmldb_field->getDecimals()) ||
298 ($xmldb_field->getDecimals() == $olddecimals)) {
299 $decimalchanged = false;
301 // Detect if we are changing the default
302 if (($xmldb_field->getDefault() === null && $olddefault === null) ||
303 ($xmldb_field->getDefault() === $olddefault)) {
304 $defaultchanged = false;
306 // Detect if we are changing the nullability
307 if (($xmldb_field->getNotnull() === $oldnotnull)) {
308 $notnullchanged = false;
311 // Get the quoted name of the table and field
312 $tablename = $this->getTableName($xmldb_table);
313 $fieldname = $this->getEncQuoted($xmldb_field->getName());
315 // Decide if we have changed the column specs (type/precision/decimals)
316 $specschanged = $typechanged ||
$precisionchanged ||
$decimalchanged;
318 // if specs have changed, need to alter column
320 // Always drop any exiting default before alter column (some type changes can cause casting error in default for column)
321 if ($olddefault !== null) {
322 $results[] = 'ALTER TABLE ' . $tablename . ' ALTER COLUMN ' . $fieldname . ' DROP DEFAULT'; // Drop default clause
324 $alterstmt = 'ALTER TABLE ' . $tablename . ' ALTER COLUMN ' . $this->getEncQuoted($xmldb_field->getName()) .
325 ' TYPE' . $this->getFieldSQL($xmldb_table, $xmldb_field, null, true, true, null, false);
326 // Some castings must be performed explicitly (mainly from text|char to numeric|integer)
327 if (($oldmetatype == 'C' ||
$oldmetatype == 'X') &&
328 ($xmldb_field->getType() == XMLDB_TYPE_NUMBER ||
$xmldb_field->getType() == XMLDB_TYPE_FLOAT
)) {
329 $alterstmt .= ' USING CAST('.$fieldname.' AS NUMERIC)'; // from char or text to number or float
330 } else if (($oldmetatype == 'C' ||
$oldmetatype == 'X') &&
331 $xmldb_field->getType() == XMLDB_TYPE_INTEGER
) {
332 $alterstmt .= ' USING CAST(CAST('.$fieldname.' AS NUMERIC) AS INTEGER)'; // From char to integer
334 $results[] = $alterstmt;
337 // If the default has changed or we have performed one change in specs
338 if ($defaultchanged ||
$specschanged) {
339 $default_clause = $this->getDefaultClause($xmldb_field);
340 if ($default_clause) {
341 $sql = 'ALTER TABLE ' . $tablename . ' ALTER COLUMN ' . $fieldname . ' SET' . $default_clause; // Add default clause
344 if (!$specschanged) { // Only drop default if we haven't performed one specs change
345 $results[] = 'ALTER TABLE ' . $tablename . ' ALTER COLUMN ' . $fieldname . ' DROP DEFAULT'; // Drop default clause
350 // If the not null has changed
351 if ($notnullchanged) {
352 if ($xmldb_field->getNotnull()) {
353 $results[] = 'ALTER TABLE ' . $tablename . ' ALTER COLUMN ' . $fieldname . ' SET NOT NULL';
355 $results[] = 'ALTER TABLE ' . $tablename . ' ALTER COLUMN ' . $fieldname . ' DROP NOT NULL';
359 // Return the results
364 * Given one xmldb_table and one xmldb_field, return the SQL statements needed to add its default
365 * (usually invoked from getModifyDefaultSQL()
367 * @param xmldb_table $xmldb_table The xmldb_table object instance.
368 * @param xmldb_field $xmldb_field The xmldb_field object instance.
369 * @return array Array of SQL statements to create a field's default.
371 public function getCreateDefaultSQL($xmldb_table, $xmldb_field) {
372 // Just a wrapper over the getAlterFieldSQL() function for PostgreSQL that
373 // is capable of handling defaults
374 return $this->getAlterFieldSQL($xmldb_table, $xmldb_field);
378 * Given one xmldb_table and one xmldb_field, return the SQL statements needed to drop its default
379 * (usually invoked from getModifyDefaultSQL()
381 * Note that this method may be dropped in future.
383 * @param xmldb_table $xmldb_table The xmldb_table object instance.
384 * @param xmldb_field $xmldb_field The xmldb_field object instance.
385 * @return array Array of SQL statements to create a field's default.
387 * @todo MDL-31147 Moodle 2.1 - Drop getDropDefaultSQL()
389 public function getDropDefaultSQL($xmldb_table, $xmldb_field) {
390 // Just a wrapper over the getAlterFieldSQL() function for PostgreSQL that
391 // is capable of handling defaults
392 return $this->getAlterFieldSQL($xmldb_table, $xmldb_field);
396 * Adds slashes to string.
398 * @return string The escaped string.
400 public function addslashes($s) {
401 // Postgres is gradually switching to ANSI quotes, we need to check what is expected
402 if (!isset($this->std_strings
)) {
403 $this->std_strings
= ($this->mdb
->get_field_sql("select setting from pg_settings where name = 'standard_conforming_strings'") === 'on');
406 if ($this->std_strings
) {
407 $s = str_replace("'", "''", $s);
409 // do not use php addslashes() because it depends on PHP quote settings!
410 $s = str_replace('\\','\\\\',$s);
411 $s = str_replace("\0","\\\0", $s);
412 $s = str_replace("'", "\\'", $s);
419 * Given one xmldb_table returns one string with the sequence of the table
420 * in the table (fetched from DB)
421 * The sequence name for Postgres has one standard name convention:
422 * tablename_fieldname_seq
423 * so we just calculate it and confirm it's present in pg_class
425 * @param xmldb_table $xmldb_table The xmldb_table object instance.
426 * @return string|bool If no sequence is found, returns false
428 function getSequenceFromDB($xmldb_table) {
430 $tablename = $this->getTableName($xmldb_table);
431 $sequencename = $tablename . '_id_seq';
433 if (!$this->mdb
->get_record_sql("SELECT c.*
434 FROM pg_catalog.pg_class c
435 JOIN pg_catalog.pg_namespace as ns ON ns.oid = c.relnamespace
436 WHERE c.relname = ? AND c.relkind = 'S'
437 AND (ns.nspname = current_schema() OR ns.oid = pg_my_temp_schema())",
438 array($sequencename))) {
439 $sequencename = false;
442 return $sequencename;
446 * Given one object name and it's type (pk, uk, fk, ck, ix, uix, seq, trg).
448 * (MySQL requires the whole xmldb_table object to be specified, so we add it always)
450 * This is invoked from getNameForObject().
451 * Only some DB have this implemented.
453 * @param string $object_name The object's name to check for.
454 * @param string $type The object's type (pk, uk, fk, ck, ix, uix, seq, trg).
455 * @param string $table_name The table's name to check in
456 * @return bool If such name is currently in use (true) or no (false)
458 public function isNameInUse($object_name, $type, $table_name) {
463 if ($check = $this->mdb
->get_records_sql("SELECT c.relname
465 JOIN pg_catalog.pg_namespace as ns ON ns.oid = c.relnamespace
466 WHERE lower(c.relname) = ?
467 AND (ns.nspname = current_schema() OR ns.oid = pg_my_temp_schema())", array(strtolower($object_name)))) {
475 if ($check = $this->mdb
->get_records_sql("SELECT c.conname
477 JOIN pg_catalog.pg_namespace as ns ON ns.oid = c.connamespace
478 WHERE lower(c.conname) = ?
479 AND (ns.nspname = current_schema() OR ns.oid = pg_my_temp_schema())", array(strtolower($object_name)))) {
484 if ($check = $this->mdb
->get_records_sql("SELECT tgname
486 WHERE lower(tgname) = ?", array(strtolower($object_name)))) {
491 return false; //No name in use found
495 * Returns an array of reserved words (lowercase) for this DB
496 * @return array An array of database specific reserved words
498 public static function getReservedWords() {
499 // This file contains the reserved words for PostgreSQL databases
500 // This file contains the reserved words for PostgreSQL databases
501 // http://www.postgresql.org/docs/current/static/sql-keywords-appendix.html
502 $reserved_words = array (
503 'all', 'analyse', 'analyze', 'and', 'any', 'array', 'as', 'asc',
504 'asymmetric', 'authorization', 'between', 'binary', 'both', 'case',
505 'cast', 'check', 'collate', 'column', 'constraint', 'create', 'cross',
506 'current_date', 'current_role', 'current_time', 'current_timestamp',
507 'current_user', 'default', 'deferrable', 'desc', 'distinct', 'do',
508 'else', 'end', 'except', 'false', 'for', 'foreign', 'freeze', 'from',
509 'full', 'grant', 'group', 'having', 'ilike', 'in', 'initially', 'inner',
510 'intersect', 'into', 'is', 'isnull', 'join', 'leading', 'left', 'like',
511 'limit', 'localtime', 'localtimestamp', 'natural', 'new', 'not',
512 'notnull', 'null', 'off', 'offset', 'old', 'on', 'only', 'or', 'order',
513 'outer', 'overlaps', 'placing', 'primary', 'references', 'returning', 'right', 'select',
514 'session_user', 'similar', 'some', 'symmetric', 'table', 'then', 'to',
515 'trailing', 'true', 'union', 'unique', 'user', 'using', 'verbose',
516 'when', 'where', 'with'
518 return $reserved_words;