MDL-37420 add support for more PostgreSQL schemas
[moodle.git] / lib / ddl / postgres_sql_generator.php
1 <?php
2 // This file is part of Moodle - http://moodle.org/
3 //
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.
8 //
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.
13 //
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/>.
17 /**
18  * PostgreSQL specific SQL code generator.
19  *
20  * @package    core_ddl
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
24  */
26 defined('MOODLE_INTERNAL') || die();
28 require_once($CFG->libdir.'/ddl/sql_generator.php');
30 /**
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.
34  *
35  * @package    core_ddl
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
39  */
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;
72     /**
73      * Reset a sequence to the id field of a table.
74      *
75      * @param xmldb_table|string $table name of table or the table object.
76      * @return array of sql statements
77      */
78     public function getResetSequenceSQL($table) {
80         if ($table instanceof xmldb_table) {
81             $tablename = $table->getName();
82         } else {
83             $tablename = $table;
84         }
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.'}');
88         $value++;
89         return array("ALTER SEQUENCE $this->prefix{$tablename}_id_seq RESTART WITH $value");
90     }
92     /**
93      * Given one correct xmldb_table, returns the SQL statements
94      * to create temporary table (inside one array).
95      *
96      * @param xmldb_table $xmldb_table The xmldb_table object instance.
97      * @return array of sql statements
98      */
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);
103         return $sqlarr;
104     }
106     /**
107      * Given one correct xmldb_table, returns the SQL statements
108      * to drop it (inside one array).
109      *
110      * @param xmldb_table $xmldb_table The table to drop.
111      * @return array SQL statement(s) for dropping the specified table.
112      */
113     public function getDropTableSQL($xmldb_table) {
114         $sqlarr = parent::getDropTableSQL($xmldb_table);
115         if ($this->temptables->is_temptable($xmldb_table->getName())) {
116             $this->temptables->delete_temptable($xmldb_table->getName());
117         }
118         return $sqlarr;
119     }
121     /**
122      * Given one XMLDB Type, length and decimals, returns the DB proper SQL type.
123      *
124      * @param int $xmldb_type The xmldb_type defined constant. XMLDB_TYPE_INTEGER and other XMLDB_TYPE_* constants.
125      * @param int $xmldb_length The length of that data type.
126      * @param int $xmldb_decimals The decimal places of precision of the data type.
127      * @return string The DB defined data type.
128      */
129     public function getTypeSQL($xmldb_type, $xmldb_length=null, $xmldb_decimals=null) {
131         switch ($xmldb_type) {
132             case XMLDB_TYPE_INTEGER:    // From http://www.postgresql.org/docs/7.4/interactive/datatype.html
133                 if (empty($xmldb_length)) {
134                     $xmldb_length = 10;
135                 }
136                 if ($xmldb_length > 9) {
137                     $dbtype = 'BIGINT';
138                 } else if ($xmldb_length > 4) {
139                     $dbtype = 'INTEGER';
140                 } else {
141                     $dbtype = 'SMALLINT';
142                 }
143                 break;
144             case XMLDB_TYPE_NUMBER:
145                 $dbtype = $this->number_type;
146                 if (!empty($xmldb_length)) {
147                     $dbtype .= '(' . $xmldb_length;
148                     if (!empty($xmldb_decimals)) {
149                         $dbtype .= ',' . $xmldb_decimals;
150                     }
151                     $dbtype .= ')';
152                 }
153                 break;
154             case XMLDB_TYPE_FLOAT:
155                 $dbtype = 'DOUBLE PRECISION';
156                 if (!empty($xmldb_decimals)) {
157                     if ($xmldb_decimals < 6) {
158                         $dbtype = 'REAL';
159                     }
160                 }
161                 break;
162             case XMLDB_TYPE_CHAR:
163                 $dbtype = 'VARCHAR';
164                 if (empty($xmldb_length)) {
165                     $xmldb_length='255';
166                 }
167                 $dbtype .= '(' . $xmldb_length . ')';
168                 break;
169             case XMLDB_TYPE_TEXT:
170                 $dbtype = 'TEXT';
171                 break;
172             case XMLDB_TYPE_BINARY:
173                 $dbtype = 'BYTEA';
174                 break;
175             case XMLDB_TYPE_DATETIME:
176                 $dbtype = 'TIMESTAMP';
177                 break;
178         }
179         return $dbtype;
180     }
182     /**
183      * Returns the code (array of statements) needed to add one comment to the table.
184      *
185      * @param xmldb_table $xmldb_table The xmldb_table object instance.
186      * @return array Array of SQL statements to add one comment to the table.
187      */
188     function getCommentSQL ($xmldb_table) {
190         $comment = "COMMENT ON TABLE " . $this->getTableName($xmldb_table);
191         $comment.= " IS '" . $this->addslashes(substr($xmldb_table->getComment(), 0, 250)) . "'";
193         return array($comment);
194     }
196     /**
197      * Returns the code (array of statements) needed to execute extra statements on table rename.
198      *
199      * @param xmldb_table $xmldb_table The xmldb_table object instance.
200      * @param string $newname The new name for the table.
201      * @return array Array of extra SQL statements to rename a table.
202      */
203     public function getRenameTableExtraSQL($xmldb_table, $newname) {
205         $results = array();
207         $newt = new xmldb_table($newname);
209         $xmldb_field = new xmldb_field('id'); // Fields having sequences should be exclusively, id.
211         $oldseqname = $this->getTableName($xmldb_table) . '_' . $xmldb_field->getName() . '_seq';
212         $newseqname = $this->getTableName($newt) . '_' . $xmldb_field->getName() . '_seq';
214         // Rename de sequence
215         $results[] = 'ALTER TABLE ' . $oldseqname . ' RENAME TO ' . $newseqname;
217         return $results;
218     }
220     /**
221      * Given one xmldb_table and one xmldb_field, return the SQL statements needed to alter the field in the table.
222      *
223      * PostgreSQL has some severe limits:
224      *     - Any change of type or precision requires a new temporary column to be created, values to
225      *       be transfered potentially casting them, to apply defaults if the column is not null and
226      *       finally, to rename it
227      *     - Changes in null/not null require the SET/DROP NOT NULL clause
228      *     - Changes in default require the SET/DROP DEFAULT clause
229      *
230      * @param xmldb_table $xmldb_table The table related to $xmldb_field.
231      * @param xmldb_field $xmldb_field The instance of xmldb_field to create the SQL from.
232      * @param string $skip_type_clause The type clause on alter columns, NULL by default.
233      * @param string $skip_default_clause The default clause on alter columns, NULL by default.
234      * @param string $skip_notnull_clause The null/notnull clause on alter columns, NULL by default.
235      * @return string The field altering SQL statement.
236      */
237     public function getAlterFieldSQL($xmldb_table, $xmldb_field, $skip_type_clause = NULL, $skip_default_clause = NULL, $skip_notnull_clause = NULL) {
238         $results = array();     // To store all the needed SQL commands
240         // Get the normal names of the table and field
241         $tablename = $xmldb_table->getName();
242         $fieldname = $xmldb_field->getName();
244         // Take a look to field metadata
245         $meta = $this->mdb->get_columns($tablename);
246         $metac = $meta[$xmldb_field->getName()];
247         $oldmetatype = $metac->meta_type;
248         $oldlength = $metac->max_length;
249         $olddecimals = empty($metac->scale) ? null : $metac->scale;
250         $oldnotnull = empty($metac->not_null) ? false : $metac->not_null;
251         $olddefault = empty($metac->has_default) ? null : $metac->default_value;
253         $typechanged = true;  //By default, assume that the column type has changed
254         $precisionchanged = true;  //By default, assume that the column precision has changed
255         $decimalchanged = true;  //By default, assume that the column decimal has changed
256         $defaultchanged = true;  //By default, assume that the column default has changed
257         $notnullchanged = true;  //By default, assume that the column notnull has changed
259         // Detect if we are changing the type of the column
260         if (($xmldb_field->getType() == XMLDB_TYPE_INTEGER && $oldmetatype == 'I') ||
261             ($xmldb_field->getType() == XMLDB_TYPE_NUMBER  && $oldmetatype == 'N') ||
262             ($xmldb_field->getType() == XMLDB_TYPE_FLOAT   && $oldmetatype == 'F') ||
263             ($xmldb_field->getType() == XMLDB_TYPE_CHAR    && $oldmetatype == 'C') ||
264             ($xmldb_field->getType() == XMLDB_TYPE_TEXT    && $oldmetatype == 'X') ||
265             ($xmldb_field->getType() == XMLDB_TYPE_BINARY  && $oldmetatype == 'B')) {
266             $typechanged = false;
267         }
268         // Detect if we are changing the precision
269         if (($xmldb_field->getType() == XMLDB_TYPE_TEXT) ||
270             ($xmldb_field->getType() == XMLDB_TYPE_BINARY) ||
271             ($oldlength == -1) ||
272             ($xmldb_field->getLength() == $oldlength)) {
273             $precisionchanged = false;
274         }
275         // Detect if we are changing the decimals
276         if (($xmldb_field->getType() == XMLDB_TYPE_INTEGER) ||
277             ($xmldb_field->getType() == XMLDB_TYPE_CHAR) ||
278             ($xmldb_field->getType() == XMLDB_TYPE_TEXT) ||
279             ($xmldb_field->getType() == XMLDB_TYPE_BINARY) ||
280             (!$xmldb_field->getDecimals()) ||
281             (!$olddecimals) ||
282             ($xmldb_field->getDecimals() == $olddecimals)) {
283             $decimalchanged = false;
284         }
285         // Detect if we are changing the default
286         if (($xmldb_field->getDefault() === null && $olddefault === null) ||
287             ($xmldb_field->getDefault() === $olddefault)) {
288             $defaultchanged = false;
289         }
290         // Detect if we are changing the nullability
291         if (($xmldb_field->getNotnull() === $oldnotnull)) {
292             $notnullchanged = false;
293         }
295         // Get the quoted name of the table and field
296         $tablename = $this->getTableName($xmldb_table);
297         $fieldname = $this->getEncQuoted($xmldb_field->getName());
299         // Decide if we have changed the column specs (type/precision/decimals)
300         $specschanged = $typechanged || $precisionchanged || $decimalchanged;
302         // if specs have changed, need to alter column
303         if ($specschanged) {
304             // Always drop any exiting default before alter column (some type changes can cause casting error in default for column)
305             if ($olddefault !== null) {
306                 $results[] = 'ALTER TABLE ' . $tablename . ' ALTER COLUMN ' . $fieldname . ' DROP DEFAULT';     // Drop default clause
307             }
308             $alterstmt = 'ALTER TABLE ' . $tablename . ' ALTER COLUMN ' . $this->getEncQuoted($xmldb_field->getName()) .
309                          ' TYPE' . $this->getFieldSQL($xmldb_table, $xmldb_field, null, true, true, null, false);
310             // Some castings must be performed explicitly (mainly from text|char to numeric|integer)
311             if (($oldmetatype == 'C' || $oldmetatype == 'X') &&
312                 ($xmldb_field->getType() == XMLDB_TYPE_NUMBER || $xmldb_field->getType() == XMLDB_TYPE_FLOAT)) {
313                 $alterstmt .= ' USING CAST('.$fieldname.' AS NUMERIC)'; // from char or text to number or float
314             } else if (($oldmetatype == 'C' || $oldmetatype == 'X') &&
315                 $xmldb_field->getType() == XMLDB_TYPE_INTEGER) {
316                 $alterstmt .= ' USING CAST(CAST('.$fieldname.' AS NUMERIC) AS INTEGER)'; // From char to integer
317             }
318             $results[] = $alterstmt;
319         }
321         // If the default has changed or we have performed one change in specs
322         if ($defaultchanged || $specschanged) {
323             $default_clause = $this->getDefaultClause($xmldb_field);
324             if ($default_clause) {
325                 $sql = 'ALTER TABLE ' . $tablename . ' ALTER COLUMN ' . $fieldname . ' SET' . $default_clause;     // Add default clause
326                 $results[] = $sql;
327             } else {
328                 if (!$specschanged) {     // Only drop default if we haven't performed one specs change
329                     $results[] = 'ALTER TABLE ' . $tablename . ' ALTER COLUMN ' . $fieldname . ' DROP DEFAULT';     // Drop default clause
330                 }
331             }
332         }
334         // If the not null has changed
335         if ($notnullchanged) {
336             if ($xmldb_field->getNotnull()) {
337                 $results[] = 'ALTER TABLE ' . $tablename . ' ALTER COLUMN ' . $fieldname . ' SET NOT NULL';
338             } else {
339                 $results[] = 'ALTER TABLE ' . $tablename . ' ALTER COLUMN ' . $fieldname . ' DROP NOT NULL';
340             }
341         }
343         // Return the results
344         return $results;
345     }
347     /**
348      * Given one xmldb_table and one xmldb_field, return the SQL statements needed to add its default
349      * (usually invoked from getModifyDefaultSQL()
350      *
351      * @param xmldb_table $xmldb_table The xmldb_table object instance.
352      * @param xmldb_field $xmldb_field The xmldb_field object instance.
353      * @return array Array of SQL statements to create a field's default.
354      */
355     public function getCreateDefaultSQL($xmldb_table, $xmldb_field) {
356         // Just a wrapper over the getAlterFieldSQL() function for PostgreSQL that
357         // is capable of handling defaults
358         return $this->getAlterFieldSQL($xmldb_table, $xmldb_field);
359     }
361     /**
362      * Given one xmldb_table and one xmldb_field, return the SQL statements needed to drop its default
363      * (usually invoked from getModifyDefaultSQL()
364      *
365      * Note that this method may be dropped in future.
366      *
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.
370      *
371      * @todo MDL-31147 Moodle 2.1 - Drop getDropDefaultSQL()
372      */
373     public function getDropDefaultSQL($xmldb_table, $xmldb_field) {
374         // Just a wrapper over the getAlterFieldSQL() function for PostgreSQL that
375         // is capable of handling defaults
376         return $this->getAlterFieldSQL($xmldb_table, $xmldb_field);
377     }
379     /**
380      * Adds slashes to string.
381      * @param string $s
382      * @return string The escaped string.
383      */
384     public function addslashes($s) {
385         // Postgres is gradually switching to ANSI quotes, we need to check what is expected
386         if (!isset($this->std_strings)) {
387             $this->std_strings = ($this->mdb->get_field_sql("select setting from pg_settings where name = 'standard_conforming_strings'") === 'on');
388         }
390         if ($this->std_strings) {
391             $s = str_replace("'",  "''", $s);
392         } else {
393             // do not use php addslashes() because it depends on PHP quote settings!
394             $s = str_replace('\\','\\\\',$s);
395             $s = str_replace("\0","\\\0", $s);
396             $s = str_replace("'",  "\\'", $s);
397         }
399         return $s;
400     }
402     /**
403      * Given one xmldb_table returns one string with the sequence of the table
404      * in the table (fetched from DB)
405      * The sequence name for Postgres has one standard name convention:
406      *     tablename_fieldname_seq
407      * so we just calculate it and confirm it's present in pg_class
408      *
409      * @param xmldb_table $xmldb_table The xmldb_table object instance.
410      * @return string|bool If no sequence is found, returns false
411      */
412     function getSequenceFromDB($xmldb_table) {
414         $tablename = $this->getTableName($xmldb_table);
415         $sequencename = $tablename . '_id_seq';
417         if (!$this->mdb->get_record_sql("SELECT c.*
418                                            FROM pg_catalog.pg_class c
419                                            JOIN pg_catalog.pg_namespace as ns ON ns.oid = c.relnamespace
420                                           WHERE c.relname = ? AND c.relkind = 'S'
421                                                 AND (ns.nspname = current_schema() OR ns.oid = pg_my_temp_schema())",
422                                         array($sequencename))) {
423             $sequencename = false;
424         }
426         return $sequencename;
427     }
429     /**
430      * Given one object name and it's type (pk, uk, fk, ck, ix, uix, seq, trg).
431      *
432      * (MySQL requires the whole xmldb_table object to be specified, so we add it always)
433      *
434      * This is invoked from getNameForObject().
435      * Only some DB have this implemented.
436      *
437      * @param string $object_name The object's name to check for.
438      * @param string $type The object's type (pk, uk, fk, ck, ix, uix, seq, trg).
439      * @param string $table_name The table's name to check in
440      * @return bool If such name is currently in use (true) or no (false)
441      */
442     public function isNameInUse($object_name, $type, $table_name) {
443         switch($type) {
444             case 'ix':
445             case 'uix':
446             case 'seq':
447                 if ($check = $this->mdb->get_records_sql("SELECT c.relname
448                                                                 FROM pg_class c
449                                                                 JOIN pg_catalog.pg_namespace as ns ON ns.oid = c.relnamespace
450                                                                WHERE lower(c.relname) = ?
451                                                                      AND (ns.nspname = current_schema() OR ns.oid = pg_my_temp_schema())", array(strtolower($object_name)))) {
452                     return true;
453                 }
454                 break;
455             case 'pk':
456             case 'uk':
457             case 'fk':
458             case 'ck':
459                 if ($check = $this->mdb->get_records_sql("SELECT c.conname
460                                                                 FROM pg_constraint c
461                                                                 JOIN pg_catalog.pg_namespace as ns ON ns.oid = c.connamespace
462                                                                WHERE lower(c.conname) = ?
463                                                                      AND (ns.nspname = current_schema() OR ns.oid = pg_my_temp_schema())", array(strtolower($object_name)))) {
464                     return true;
465                 }
466                 break;
467             case 'trg':
468                 if ($check = $this->mdb->get_records_sql("SELECT tgname
469                                                             FROM pg_trigger
470                                                            WHERE lower(tgname) = ?", array(strtolower($object_name)))) {
471                     return true;
472                 }
473                 break;
474         }
475         return false; //No name in use found
476     }
478     /**
479      * Returns an array of reserved words (lowercase) for this DB
480      * @return array An array of database specific reserved words
481      */
482     public static function getReservedWords() {
483         // This file contains the reserved words for PostgreSQL databases
484         // http://www.postgresql.org/docs/current/static/sql-keywords-appendix.html
485         $reserved_words = array (
486             'all', 'analyse', 'analyze', 'and', 'any', 'array', 'as', 'asc',
487             'asymmetric', 'authorization', 'between', 'binary', 'both', 'case',
488             'cast', 'check', 'collate', 'column', 'constraint', 'create', 'cross',
489             'current_date', 'current_role', 'current_time', 'current_timestamp',
490             'current_user', 'default', 'deferrable', 'desc', 'distinct', 'do',
491             'else', 'end', 'except', 'false', 'for', 'foreign', 'freeze', 'from',
492             'full', 'grant', 'group', 'having', 'ilike', 'in', 'initially', 'inner',
493             'intersect', 'into', 'is', 'isnull', 'join', 'leading', 'left', 'like',
494             'limit', 'localtime', 'localtimestamp', 'natural', 'new', 'not',
495             'notnull', 'null', 'off', 'offset', 'old', 'on', 'only', 'or', 'order',
496             'outer', 'overlaps', 'placing', 'primary', 'references', 'returning', 'right', 'select',
497             'session_user', 'similar', 'some', 'symmetric', 'table', 'then', 'to',
498             'trailing', 'true', 'union', 'unique', 'user', 'using', 'verbose',
499             'when', 'where', 'with'
500         );
501         return $reserved_words;
502     }