| [ Index ] |
PHP Cross Reference of Unnamed Project |
[Summary view] [Print] [Text view]
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/>. 16 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 */ 25 26 defined('MOODLE_INTERNAL') || die(); 27 28 require_once($CFG->libdir.'/ddl/sql_generator.php'); 29 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 */ 40 41 class postgres_sql_generator extends sql_generator { 42 43 // Only set values that are different from the defaults present in XMLDBgenerator 44 45 /** @var string Proper type for NUMBER(x) in this DB. */ 46 public $number_type = 'NUMERIC'; 47 48 /** @var string To define the default to set for NOT NULLs CHARs without default (null=do nothing).*/ 49 public $default_for_char = ''; 50 51 /** @var bool True if the generator needs to add extra code to generate the sequence fields.*/ 52 public $sequence_extra_code = false; 53 54 /** @var string The particular name for inline sequences in this generator.*/ 55 public $sequence_name = 'BIGSERIAL'; 56 57 /** @var string The particular name for inline sequences in this generator.*/ 58 public $sequence_name_small = 'SERIAL'; 59 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; 62 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'; 65 66 /** @var string SQL sentence to rename one key 'TABLENAME', 'OLDKEYNAME' and 'NEWKEYNAME' are dynamically replaced.*/ 67 public $rename_key_sql = null; 68 69 /** @var string type of string quoting used - '' or \' quotes*/ 70 protected $std_strings = null; 71 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) { 79 80 if ($table instanceof xmldb_table) { 81 $tablename = $table->getName(); 82 } else { 83 $tablename = $table; 84 } 85 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 } 91 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 } 105 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 } 120 121 /** 122 * Given one correct xmldb_index, returns the SQL statements 123 * needed to create it (in array). 124 * 125 * @param xmldb_table $xmldb_table The xmldb_table instance to create the index on. 126 * @param xmldb_index $xmldb_index The xmldb_index to create. 127 * @return array An array of SQL statements to create the index. 128 * @throws coding_exception Thrown if the xmldb_index does not validate with the xmldb_table. 129 */ 130 public function getCreateIndexSQL($xmldb_table, $xmldb_index) { 131 $sqls = parent::getCreateIndexSQL($xmldb_table, $xmldb_index); 132 133 $hints = $xmldb_index->getHints(); 134 $fields = $xmldb_index->getFields(); 135 if (in_array('varchar_pattern_ops', $hints) and count($fields) == 1) { 136 // Add the pattern index and keep the normal one, keep unique only the standard index to improve perf. 137 foreach ($sqls as $sql) { 138 $field = reset($fields); 139 $count = 0; 140 $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); 141 if ($count != 1) { 142 debugging('Unexpected getCreateIndexSQL() structure.'); 143 continue; 144 } 145 $sqls[] = $newindex; 146 } 147 } 148 149 return $sqls; 150 } 151 152 /** 153 * Given one XMLDB Type, length and decimals, returns the DB proper SQL type. 154 * 155 * @param int $xmldb_type The xmldb_type defined constant. XMLDB_TYPE_INTEGER and other XMLDB_TYPE_* constants. 156 * @param int $xmldb_length The length of that data type. 157 * @param int $xmldb_decimals The decimal places of precision of the data type. 158 * @return string The DB defined data type. 159 */ 160 public function getTypeSQL($xmldb_type, $xmldb_length=null, $xmldb_decimals=null) { 161 162 switch ($xmldb_type) { 163 case XMLDB_TYPE_INTEGER: // From http://www.postgresql.org/docs/7.4/interactive/datatype.html 164 if (empty($xmldb_length)) { 165 $xmldb_length = 10; 166 } 167 if ($xmldb_length > 9) { 168 $dbtype = 'BIGINT'; 169 } else if ($xmldb_length > 4) { 170 $dbtype = 'INTEGER'; 171 } else { 172 $dbtype = 'SMALLINT'; 173 } 174 break; 175 case XMLDB_TYPE_NUMBER: 176 $dbtype = $this->number_type; 177 if (!empty($xmldb_length)) { 178 $dbtype .= '(' . $xmldb_length; 179 if (!empty($xmldb_decimals)) { 180 $dbtype .= ',' . $xmldb_decimals; 181 } 182 $dbtype .= ')'; 183 } 184 break; 185 case XMLDB_TYPE_FLOAT: 186 $dbtype = 'DOUBLE PRECISION'; 187 if (!empty($xmldb_decimals)) { 188 if ($xmldb_decimals < 6) { 189 $dbtype = 'REAL'; 190 } 191 } 192 break; 193 case XMLDB_TYPE_CHAR: 194 $dbtype = 'VARCHAR'; 195 if (empty($xmldb_length)) { 196 $xmldb_length='255'; 197 } 198 $dbtype .= '(' . $xmldb_length . ')'; 199 break; 200 case XMLDB_TYPE_TEXT: 201 $dbtype = 'TEXT'; 202 break; 203 case XMLDB_TYPE_BINARY: 204 $dbtype = 'BYTEA'; 205 break; 206 case XMLDB_TYPE_DATETIME: 207 $dbtype = 'TIMESTAMP'; 208 break; 209 } 210 return $dbtype; 211 } 212 213 /** 214 * Returns the code (array of statements) needed to add one comment to the table. 215 * 216 * @param xmldb_table $xmldb_table The xmldb_table object instance. 217 * @return array Array of SQL statements to add one comment to the table. 218 */ 219 function getCommentSQL ($xmldb_table) { 220 221 $comment = "COMMENT ON TABLE " . $this->getTableName($xmldb_table); 222 $comment.= " IS '" . $this->addslashes(substr($xmldb_table->getComment(), 0, 250)) . "'"; 223 224 return array($comment); 225 } 226 227 /** 228 * Returns the code (array of statements) needed to execute extra statements on table rename. 229 * 230 * @param xmldb_table $xmldb_table The xmldb_table object instance. 231 * @param string $newname The new name for the table. 232 * @return array Array of extra SQL statements to rename a table. 233 */ 234 public function getRenameTableExtraSQL($xmldb_table, $newname) { 235 236 $results = array(); 237 238 $newt = new xmldb_table($newname); 239 240 $xmldb_field = new xmldb_field('id'); // Fields having sequences should be exclusively, id. 241 242 $oldseqname = $this->getTableName($xmldb_table) . '_' . $xmldb_field->getName() . '_seq'; 243 $newseqname = $this->getTableName($newt) . '_' . $xmldb_field->getName() . '_seq'; 244 245 // Rename de sequence 246 $results[] = 'ALTER TABLE ' . $oldseqname . ' RENAME TO ' . $newseqname; 247 248 return $results; 249 } 250 251 /** 252 * Given one xmldb_table and one xmldb_field, return the SQL statements needed to alter the field in the table. 253 * 254 * PostgreSQL has some severe limits: 255 * - Any change of type or precision requires a new temporary column to be created, values to 256 * be transfered potentially casting them, to apply defaults if the column is not null and 257 * finally, to rename it 258 * - Changes in null/not null require the SET/DROP NOT NULL clause 259 * - Changes in default require the SET/DROP DEFAULT clause 260 * 261 * @param xmldb_table $xmldb_table The table related to $xmldb_field. 262 * @param xmldb_field $xmldb_field The instance of xmldb_field to create the SQL from. 263 * @param string $skip_type_clause The type clause on alter columns, NULL by default. 264 * @param string $skip_default_clause The default clause on alter columns, NULL by default. 265 * @param string $skip_notnull_clause The null/notnull clause on alter columns, NULL by default. 266 * @return string The field altering SQL statement. 267 */ 268 public function getAlterFieldSQL($xmldb_table, $xmldb_field, $skip_type_clause = NULL, $skip_default_clause = NULL, $skip_notnull_clause = NULL) { 269 $results = array(); // To store all the needed SQL commands 270 271 // Get the normal names of the table and field 272 $tablename = $xmldb_table->getName(); 273 $fieldname = $xmldb_field->getName(); 274 275 // Take a look to field metadata 276 $meta = $this->mdb->get_columns($tablename); 277 $metac = $meta[$xmldb_field->getName()]; 278 $oldmetatype = $metac->meta_type; 279 $oldlength = $metac->max_length; 280 $olddecimals = empty($metac->scale) ? null : $metac->scale; 281 $oldnotnull = empty($metac->not_null) ? false : $metac->not_null; 282 $olddefault = empty($metac->has_default) ? null : $metac->default_value; 283 284 $typechanged = true; //By default, assume that the column type has changed 285 $precisionchanged = true; //By default, assume that the column precision has changed 286 $decimalchanged = true; //By default, assume that the column decimal has changed 287 $defaultchanged = true; //By default, assume that the column default has changed 288 $notnullchanged = true; //By default, assume that the column notnull has changed 289 290 // Detect if we are changing the type of the column 291 if (($xmldb_field->getType() == XMLDB_TYPE_INTEGER && $oldmetatype == 'I') || 292 ($xmldb_field->getType() == XMLDB_TYPE_NUMBER && $oldmetatype == 'N') || 293 ($xmldb_field->getType() == XMLDB_TYPE_FLOAT && $oldmetatype == 'F') || 294 ($xmldb_field->getType() == XMLDB_TYPE_CHAR && $oldmetatype == 'C') || 295 ($xmldb_field->getType() == XMLDB_TYPE_TEXT && $oldmetatype == 'X') || 296 ($xmldb_field->getType() == XMLDB_TYPE_BINARY && $oldmetatype == 'B')) { 297 $typechanged = false; 298 } 299 // Detect if we are changing the precision 300 if (($xmldb_field->getType() == XMLDB_TYPE_TEXT) || 301 ($xmldb_field->getType() == XMLDB_TYPE_BINARY) || 302 ($oldlength == -1) || 303 ($xmldb_field->getLength() == $oldlength)) { 304 $precisionchanged = false; 305 } 306 // Detect if we are changing the decimals 307 if (($xmldb_field->getType() == XMLDB_TYPE_INTEGER) || 308 ($xmldb_field->getType() == XMLDB_TYPE_CHAR) || 309 ($xmldb_field->getType() == XMLDB_TYPE_TEXT) || 310 ($xmldb_field->getType() == XMLDB_TYPE_BINARY) || 311 (!$xmldb_field->getDecimals()) || 312 (!$olddecimals) || 313 ($xmldb_field->getDecimals() == $olddecimals)) { 314 $decimalchanged = false; 315 } 316 // Detect if we are changing the default 317 if (($xmldb_field->getDefault() === null && $olddefault === null) || 318 ($xmldb_field->getDefault() === $olddefault)) { 319 $defaultchanged = false; 320 } 321 // Detect if we are changing the nullability 322 if (($xmldb_field->getNotnull() === $oldnotnull)) { 323 $notnullchanged = false; 324 } 325 326 // Get the quoted name of the table and field 327 $tablename = $this->getTableName($xmldb_table); 328 $fieldname = $this->getEncQuoted($xmldb_field->getName()); 329 330 // Decide if we have changed the column specs (type/precision/decimals) 331 $specschanged = $typechanged || $precisionchanged || $decimalchanged; 332 333 // if specs have changed, need to alter column 334 if ($specschanged) { 335 // Always drop any exiting default before alter column (some type changes can cause casting error in default for column) 336 if ($olddefault !== null) { 337 $results[] = 'ALTER TABLE ' . $tablename . ' ALTER COLUMN ' . $fieldname . ' DROP DEFAULT'; // Drop default clause 338 } 339 $alterstmt = 'ALTER TABLE ' . $tablename . ' ALTER COLUMN ' . $this->getEncQuoted($xmldb_field->getName()) . 340 ' TYPE' . $this->getFieldSQL($xmldb_table, $xmldb_field, null, true, true, null, false); 341 // Some castings must be performed explicitly (mainly from text|char to numeric|integer) 342 if (($oldmetatype == 'C' || $oldmetatype == 'X') && 343 ($xmldb_field->getType() == XMLDB_TYPE_NUMBER || $xmldb_field->getType() == XMLDB_TYPE_FLOAT)) { 344 $alterstmt .= ' USING CAST('.$fieldname.' AS NUMERIC)'; // from char or text to number or float 345 } else if (($oldmetatype == 'C' || $oldmetatype == 'X') && 346 $xmldb_field->getType() == XMLDB_TYPE_INTEGER) { 347 $alterstmt .= ' USING CAST(CAST('.$fieldname.' AS NUMERIC) AS INTEGER)'; // From char to integer 348 } 349 $results[] = $alterstmt; 350 } 351 352 // If the default has changed or we have performed one change in specs 353 if ($defaultchanged || $specschanged) { 354 $default_clause = $this->getDefaultClause($xmldb_field); 355 if ($default_clause) { 356 $sql = 'ALTER TABLE ' . $tablename . ' ALTER COLUMN ' . $fieldname . ' SET' . $default_clause; // Add default clause 357 $results[] = $sql; 358 } else { 359 if (!$specschanged) { // Only drop default if we haven't performed one specs change 360 $results[] = 'ALTER TABLE ' . $tablename . ' ALTER COLUMN ' . $fieldname . ' DROP DEFAULT'; // Drop default clause 361 } 362 } 363 } 364 365 // If the not null has changed 366 if ($notnullchanged) { 367 if ($xmldb_field->getNotnull()) { 368 $results[] = 'ALTER TABLE ' . $tablename . ' ALTER COLUMN ' . $fieldname . ' SET NOT NULL'; 369 } else { 370 $results[] = 'ALTER TABLE ' . $tablename . ' ALTER COLUMN ' . $fieldname . ' DROP NOT NULL'; 371 } 372 } 373 374 // Return the results 375 return $results; 376 } 377 378 /** 379 * Given one xmldb_table and one xmldb_field, return the SQL statements needed to add its default 380 * (usually invoked from getModifyDefaultSQL() 381 * 382 * @param xmldb_table $xmldb_table The xmldb_table object instance. 383 * @param xmldb_field $xmldb_field The xmldb_field object instance. 384 * @return array Array of SQL statements to create a field's default. 385 */ 386 public function getCreateDefaultSQL($xmldb_table, $xmldb_field) { 387 // Just a wrapper over the getAlterFieldSQL() function for PostgreSQL that 388 // is capable of handling defaults 389 return $this->getAlterFieldSQL($xmldb_table, $xmldb_field); 390 } 391 392 /** 393 * Given one xmldb_table and one xmldb_field, return the SQL statements needed to drop its default 394 * (usually invoked from getModifyDefaultSQL() 395 * 396 * Note that this method may be dropped in future. 397 * 398 * @param xmldb_table $xmldb_table The xmldb_table object instance. 399 * @param xmldb_field $xmldb_field The xmldb_field object instance. 400 * @return array Array of SQL statements to create a field's default. 401 * 402 * @todo MDL-31147 Moodle 2.1 - Drop getDropDefaultSQL() 403 */ 404 public function getDropDefaultSQL($xmldb_table, $xmldb_field) { 405 // Just a wrapper over the getAlterFieldSQL() function for PostgreSQL that 406 // is capable of handling defaults 407 return $this->getAlterFieldSQL($xmldb_table, $xmldb_field); 408 } 409 410 /** 411 * Adds slashes to string. 412 * @param string $s 413 * @return string The escaped string. 414 */ 415 public function addslashes($s) { 416 // Postgres is gradually switching to ANSI quotes, we need to check what is expected 417 if (!isset($this->std_strings)) { 418 $this->std_strings = ($this->mdb->get_field_sql("select setting from pg_settings where name = 'standard_conforming_strings'") === 'on'); 419 } 420 421 if ($this->std_strings) { 422 $s = str_replace("'", "''", $s); 423 } else { 424 // do not use php addslashes() because it depends on PHP quote settings! 425 $s = str_replace('\\','\\\\',$s); 426 $s = str_replace("\0","\\\0", $s); 427 $s = str_replace("'", "\\'", $s); 428 } 429 430 return $s; 431 } 432 433 /** 434 * Given one xmldb_table returns one string with the sequence of the table 435 * in the table (fetched from DB) 436 * The sequence name for Postgres has one standard name convention: 437 * tablename_fieldname_seq 438 * so we just calculate it and confirm it's present in pg_class 439 * 440 * @param xmldb_table $xmldb_table The xmldb_table object instance. 441 * @return string|bool If no sequence is found, returns false 442 */ 443 function getSequenceFromDB($xmldb_table) { 444 445 $tablename = $this->getTableName($xmldb_table); 446 $sequencename = $tablename . '_id_seq'; 447 448 if (!$this->mdb->get_record_sql("SELECT c.* 449 FROM pg_catalog.pg_class c 450 JOIN pg_catalog.pg_namespace as ns ON ns.oid = c.relnamespace 451 WHERE c.relname = ? AND c.relkind = 'S' 452 AND (ns.nspname = current_schema() OR ns.oid = pg_my_temp_schema())", 453 array($sequencename))) { 454 $sequencename = false; 455 } 456 457 return $sequencename; 458 } 459 460 /** 461 * Given one object name and it's type (pk, uk, fk, ck, ix, uix, seq, trg). 462 * 463 * (MySQL requires the whole xmldb_table object to be specified, so we add it always) 464 * 465 * This is invoked from getNameForObject(). 466 * Only some DB have this implemented. 467 * 468 * @param string $object_name The object's name to check for. 469 * @param string $type The object's type (pk, uk, fk, ck, ix, uix, seq, trg). 470 * @param string $table_name The table's name to check in 471 * @return bool If such name is currently in use (true) or no (false) 472 */ 473 public function isNameInUse($object_name, $type, $table_name) { 474 switch($type) { 475 case 'ix': 476 case 'uix': 477 case 'seq': 478 if ($check = $this->mdb->get_records_sql("SELECT c.relname 479 FROM pg_class c 480 JOIN pg_catalog.pg_namespace as ns ON ns.oid = c.relnamespace 481 WHERE lower(c.relname) = ? 482 AND (ns.nspname = current_schema() OR ns.oid = pg_my_temp_schema())", array(strtolower($object_name)))) { 483 return true; 484 } 485 break; 486 case 'pk': 487 case 'uk': 488 case 'fk': 489 case 'ck': 490 if ($check = $this->mdb->get_records_sql("SELECT c.conname 491 FROM pg_constraint c 492 JOIN pg_catalog.pg_namespace as ns ON ns.oid = c.connamespace 493 WHERE lower(c.conname) = ? 494 AND (ns.nspname = current_schema() OR ns.oid = pg_my_temp_schema())", array(strtolower($object_name)))) { 495 return true; 496 } 497 break; 498 case 'trg': 499 if ($check = $this->mdb->get_records_sql("SELECT tgname 500 FROM pg_trigger 501 WHERE lower(tgname) = ?", array(strtolower($object_name)))) { 502 return true; 503 } 504 break; 505 } 506 return false; //No name in use found 507 } 508 509 /** 510 * Returns an array of reserved words (lowercase) for this DB 511 * @return array An array of database specific reserved words 512 */ 513 public static function getReservedWords() { 514 // This file contains the reserved words for PostgreSQL databases 515 // This file contains the reserved words for PostgreSQL databases 516 // http://www.postgresql.org/docs/current/static/sql-keywords-appendix.html 517 $reserved_words = array ( 518 'all', 'analyse', 'analyze', 'and', 'any', 'array', 'as', 'asc', 519 'asymmetric', 'authorization', 'between', 'binary', 'both', 'case', 520 'cast', 'check', 'collate', 'column', 'constraint', 'create', 'cross', 521 'current_date', 'current_role', 'current_time', 'current_timestamp', 522 'current_user', 'default', 'deferrable', 'desc', 'distinct', 'do', 523 'else', 'end', 'except', 'false', 'for', 'foreign', 'freeze', 'from', 524 'full', 'grant', 'group', 'having', 'ilike', 'in', 'initially', 'inner', 525 'intersect', 'into', 'is', 'isnull', 'join', 'leading', 'left', 'like', 526 'limit', 'localtime', 'localtimestamp', 'natural', 'new', 'not', 527 'notnull', 'null', 'off', 'offset', 'old', 'on', 'only', 'or', 'order', 528 'outer', 'overlaps', 'placing', 'primary', 'references', 'returning', 'right', 'select', 529 'session_user', 'similar', 'some', 'symmetric', 'table', 'then', 'to', 530 'trailing', 'true', 'union', 'unique', 'user', 'using', 'verbose', 531 'when', 'where', 'with' 532 ); 533 return $reserved_words; 534 } 535 }
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
| Generated: Thu Aug 11 10:00:09 2016 | Cross-referenced by PHPXref 0.7.1 |