| [ Index ] |
PHP Cross Reference of phpBB-3.2.11-deutsch |
[Summary view] [Print] [Text view]
1 <?php 2 /** 3 * 4 * This file is part of the phpBB Forum Software package. 5 * 6 * @copyright (c) phpBB Limited <https://www.phpbb.com> 7 * @license GNU General Public License, version 2 (GPL-2.0) 8 * 9 * For full copyright and license information, please see 10 * the docs/CREDITS.txt file. 11 * 12 */ 13 14 namespace phpbb\db\tools; 15 16 /** 17 * Database Tools for handling cross-db actions such as altering columns, etc. 18 * Currently not supported is returning SQL for creating tables. 19 */ 20 class postgres extends tools 21 { 22 /** 23 * Get the column types for postgres only 24 * 25 * @return array 26 */ 27 public static function get_dbms_type_map() 28 { 29 return array( 30 'postgres' => array( 31 'INT:' => 'INT4', 32 'BINT' => 'INT8', 33 'ULINT' => 'INT4', // unsigned 34 'UINT' => 'INT4', // unsigned 35 'UINT:' => 'INT4', // unsigned 36 'USINT' => 'INT2', // unsigned 37 'BOOL' => 'INT2', // unsigned 38 'TINT:' => 'INT2', 39 'VCHAR' => 'varchar(255)', 40 'VCHAR:' => 'varchar(%d)', 41 'CHAR:' => 'char(%d)', 42 'XSTEXT' => 'varchar(1000)', 43 'STEXT' => 'varchar(3000)', 44 'TEXT' => 'varchar(8000)', 45 'MTEXT' => 'TEXT', 46 'XSTEXT_UNI'=> 'varchar(100)', 47 'STEXT_UNI' => 'varchar(255)', 48 'TEXT_UNI' => 'varchar(4000)', 49 'MTEXT_UNI' => 'TEXT', 50 'TIMESTAMP' => 'INT4', // unsigned 51 'DECIMAL' => 'decimal(5,2)', 52 'DECIMAL:' => 'decimal(%d,2)', 53 'PDECIMAL' => 'decimal(6,3)', 54 'PDECIMAL:' => 'decimal(%d,3)', 55 'VCHAR_UNI' => 'varchar(255)', 56 'VCHAR_UNI:'=> 'varchar(%d)', 57 'VCHAR_CI' => 'varchar_ci', 58 'VARBINARY' => 'bytea', 59 ), 60 ); 61 } 62 63 /** 64 * Constructor. Set DB Object and set {@link $return_statements return_statements}. 65 * 66 * @param \phpbb\db\driver\driver_interface $db Database connection 67 * @param bool $return_statements True if only statements should be returned and no SQL being executed 68 */ 69 public function __construct(\phpbb\db\driver\driver_interface $db, $return_statements = false) 70 { 71 parent::__construct($db, $return_statements); 72 73 // Determine mapping database type 74 $this->sql_layer = 'postgres'; 75 76 $this->dbms_type_map = self::get_dbms_type_map(); 77 } 78 79 /** 80 * {@inheritDoc} 81 */ 82 function sql_list_tables() 83 { 84 $sql = 'SELECT relname 85 FROM pg_stat_user_tables'; 86 $result = $this->db->sql_query($sql); 87 88 $tables = array(); 89 while ($row = $this->db->sql_fetchrow($result)) 90 { 91 $name = current($row); 92 $tables[$name] = $name; 93 } 94 $this->db->sql_freeresult($result); 95 96 return $tables; 97 } 98 99 /** 100 * {@inheritDoc} 101 */ 102 function sql_create_table($table_name, $table_data) 103 { 104 // holds the DDL for a column 105 $columns = $statements = array(); 106 107 if ($this->sql_table_exists($table_name)) 108 { 109 return $this->_sql_run_sql($statements); 110 } 111 112 // Begin transaction 113 $statements[] = 'begin'; 114 115 // Determine if we have created a PRIMARY KEY in the earliest 116 $primary_key_gen = false; 117 118 // Determine if the table requires a sequence 119 $create_sequence = false; 120 121 // Begin table sql statement 122 $table_sql = 'CREATE TABLE ' . $table_name . ' (' . "\n"; 123 124 // Iterate through the columns to create a table 125 foreach ($table_data['COLUMNS'] as $column_name => $column_data) 126 { 127 // here lies an array, filled with information compiled on the column's data 128 $prepared_column = $this->sql_prepare_column_data($table_name, $column_name, $column_data); 129 130 if (isset($prepared_column['auto_increment']) && $prepared_column['auto_increment'] && strlen($column_name) > 26) // "$column_name}_gen" 131 { 132 trigger_error("Index name '$column_name}_gen' on table '$table_name' is too long. The maximum auto increment column length is 26 characters.", E_USER_ERROR); 133 } 134 135 // here we add the definition of the new column to the list of columns 136 $columns[] = "\t {$column_name} " . $prepared_column['column_type_sql']; 137 138 // see if we have found a primary key set due to a column definition if we have found it, we can stop looking 139 if (!$primary_key_gen) 140 { 141 $primary_key_gen = isset($prepared_column['primary_key_set']) && $prepared_column['primary_key_set']; 142 } 143 144 // create sequence DDL based off of the existance of auto incrementing columns 145 if (!$create_sequence && isset($prepared_column['auto_increment']) && $prepared_column['auto_increment']) 146 { 147 $create_sequence = $column_name; 148 } 149 } 150 151 // this makes up all the columns in the create table statement 152 $table_sql .= implode(",\n", $columns); 153 154 // we have yet to create a primary key for this table, 155 // this means that we can add the one we really wanted instead 156 if (!$primary_key_gen) 157 { 158 // Write primary key 159 if (isset($table_data['PRIMARY_KEY'])) 160 { 161 if (!is_array($table_data['PRIMARY_KEY'])) 162 { 163 $table_data['PRIMARY_KEY'] = array($table_data['PRIMARY_KEY']); 164 } 165 166 $table_sql .= ",\n\t PRIMARY KEY (" . implode(', ', $table_data['PRIMARY_KEY']) . ')'; 167 } 168 } 169 170 // do we need to add a sequence for auto incrementing columns? 171 if ($create_sequence) 172 { 173 $statements[] = "CREATE SEQUENCE {$table_name}_seq;"; 174 } 175 176 // close the table 177 $table_sql .= "\n);"; 178 $statements[] = $table_sql; 179 180 // Write Keys 181 if (isset($table_data['KEYS'])) 182 { 183 foreach ($table_data['KEYS'] as $key_name => $key_data) 184 { 185 if (!is_array($key_data[1])) 186 { 187 $key_data[1] = array($key_data[1]); 188 } 189 190 $old_return_statements = $this->return_statements; 191 $this->return_statements = true; 192 193 $key_stmts = ($key_data[0] == 'UNIQUE') ? $this->sql_create_unique_index($table_name, $key_name, $key_data[1]) : $this->sql_create_index($table_name, $key_name, $key_data[1]); 194 195 foreach ($key_stmts as $key_stmt) 196 { 197 $statements[] = $key_stmt; 198 } 199 200 $this->return_statements = $old_return_statements; 201 } 202 } 203 204 // Commit Transaction 205 $statements[] = 'commit'; 206 207 return $this->_sql_run_sql($statements); 208 } 209 210 /** 211 * {@inheritDoc} 212 */ 213 function sql_list_columns($table_name) 214 { 215 $columns = array(); 216 217 $sql = "SELECT a.attname 218 FROM pg_class c, pg_attribute a 219 WHERE c.relname = '{$table_name}' 220 AND a.attnum > 0 221 AND a.attrelid = c.oid"; 222 $result = $this->db->sql_query($sql); 223 224 while ($row = $this->db->sql_fetchrow($result)) 225 { 226 $column = strtolower(current($row)); 227 $columns[$column] = $column; 228 } 229 $this->db->sql_freeresult($result); 230 231 return $columns; 232 } 233 234 /** 235 * {@inheritDoc} 236 */ 237 function sql_index_exists($table_name, $index_name) 238 { 239 $sql = "SELECT ic.relname as index_name 240 FROM pg_class bc, pg_class ic, pg_index i 241 WHERE (bc.oid = i.indrelid) 242 AND (ic.oid = i.indexrelid) 243 AND (bc.relname = '" . $table_name . "') 244 AND (i.indisunique != 't') 245 AND (i.indisprimary != 't')"; 246 $result = $this->db->sql_query($sql); 247 248 while ($row = $this->db->sql_fetchrow($result)) 249 { 250 // This DBMS prefixes index names with the table name 251 $row['index_name'] = $this->strip_table_name_from_index_name($table_name, $row['index_name']); 252 253 if (strtolower($row['index_name']) == strtolower($index_name)) 254 { 255 $this->db->sql_freeresult($result); 256 return true; 257 } 258 } 259 $this->db->sql_freeresult($result); 260 261 return false; 262 } 263 264 /** 265 * {@inheritDoc} 266 */ 267 function sql_unique_index_exists($table_name, $index_name) 268 { 269 $sql = "SELECT ic.relname as index_name, i.indisunique 270 FROM pg_class bc, pg_class ic, pg_index i 271 WHERE (bc.oid = i.indrelid) 272 AND (ic.oid = i.indexrelid) 273 AND (bc.relname = '" . $table_name . "') 274 AND (i.indisprimary != 't')"; 275 $result = $this->db->sql_query($sql); 276 277 while ($row = $this->db->sql_fetchrow($result)) 278 { 279 if ($row['indisunique'] != 't') 280 { 281 continue; 282 } 283 284 // This DBMS prefixes index names with the table name 285 $row['index_name'] = $this->strip_table_name_from_index_name($table_name, $row['index_name']); 286 287 if (strtolower($row['index_name']) == strtolower($index_name)) 288 { 289 $this->db->sql_freeresult($result); 290 return true; 291 } 292 } 293 $this->db->sql_freeresult($result); 294 295 return false; 296 } 297 298 /** 299 * Function to prepare some column information for better usage 300 * @access private 301 */ 302 function sql_prepare_column_data($table_name, $column_name, $column_data) 303 { 304 if (strlen($column_name) > 30) 305 { 306 trigger_error("Column name '$column_name' on table '$table_name' is too long. The maximum is 30 characters.", E_USER_ERROR); 307 } 308 309 // Get type 310 list($column_type, $orig_column_type) = $this->get_column_type($column_data[0]); 311 312 // Adjust default value if db-dependent specified 313 if (is_array($column_data[1])) 314 { 315 $column_data[1] = (isset($column_data[1][$this->sql_layer])) ? $column_data[1][$this->sql_layer] : $column_data[1]['default']; 316 } 317 318 $sql = " {$column_type} "; 319 320 $return_array = array( 321 'column_type' => $column_type, 322 'auto_increment' => false, 323 ); 324 325 if (isset($column_data[2]) && $column_data[2] == 'auto_increment') 326 { 327 $default_val = "nextval('{$table_name}_seq')"; 328 $return_array['auto_increment'] = true; 329 } 330 else if (!is_null($column_data[1])) 331 { 332 $default_val = "'" . $column_data[1] . "'"; 333 $return_array['null'] = 'NOT NULL'; 334 $sql .= 'NOT NULL '; 335 } 336 else 337 { 338 // Integers need to have 0 instead of empty string as default 339 if (strpos($column_type, 'INT') === 0) 340 { 341 $default_val = '0'; 342 } 343 else 344 { 345 $default_val = "'" . $column_data[1] . "'"; 346 } 347 $return_array['null'] = 'NULL'; 348 $sql .= 'NULL '; 349 } 350 351 $return_array['default'] = $default_val; 352 353 $sql .= "DEFAULT {$default_val}"; 354 355 // Unsigned? Then add a CHECK contraint 356 if (in_array($orig_column_type, $this->unsigned_types)) 357 { 358 $return_array['constraint'] = "CHECK ({$column_name} >= 0)"; 359 $sql .= " CHECK ({$column_name} >= 0)"; 360 } 361 362 $return_array['column_type_sql'] = $sql; 363 364 return $return_array; 365 } 366 367 /** 368 * {@inheritDoc} 369 */ 370 function sql_column_add($table_name, $column_name, $column_data, $inline = false) 371 { 372 $column_data = $this->sql_prepare_column_data($table_name, $column_name, $column_data); 373 $statements = array(); 374 375 // Does not support AFTER, only through temporary table 376 if (version_compare($this->db->sql_server_info(true), '8.0', '>=')) 377 { 378 $statements[] = 'ALTER TABLE ' . $table_name . ' ADD COLUMN "' . $column_name . '" ' . $column_data['column_type_sql']; 379 } 380 else 381 { 382 // old versions cannot add columns with default and null information 383 $statements[] = 'ALTER TABLE ' . $table_name . ' ADD COLUMN "' . $column_name . '" ' . $column_data['column_type'] . ' ' . $column_data['constraint']; 384 385 if (isset($column_data['null'])) 386 { 387 if ($column_data['null'] == 'NOT NULL') 388 { 389 $statements[] = 'ALTER TABLE ' . $table_name . ' ALTER COLUMN ' . $column_name . ' SET NOT NULL'; 390 } 391 } 392 393 if (isset($column_data['default'])) 394 { 395 $statements[] = 'ALTER TABLE ' . $table_name . ' ALTER COLUMN ' . $column_name . ' SET DEFAULT ' . $column_data['default']; 396 } 397 } 398 399 return $this->_sql_run_sql($statements); 400 } 401 402 /** 403 * {@inheritDoc} 404 */ 405 function sql_column_remove($table_name, $column_name, $inline = false) 406 { 407 $statements = array(); 408 409 $statements[] = 'ALTER TABLE ' . $table_name . ' DROP COLUMN "' . $column_name . '"'; 410 411 return $this->_sql_run_sql($statements); 412 } 413 414 /** 415 * {@inheritDoc} 416 */ 417 function sql_index_drop($table_name, $index_name) 418 { 419 $statements = array(); 420 421 $statements[] = 'DROP INDEX ' . $table_name . '_' . $index_name; 422 423 return $this->_sql_run_sql($statements); 424 } 425 426 /** 427 * {@inheritDoc} 428 */ 429 function sql_table_drop($table_name) 430 { 431 $statements = array(); 432 433 if (!$this->sql_table_exists($table_name)) 434 { 435 return $this->_sql_run_sql($statements); 436 } 437 438 // the most basic operation, get rid of the table 439 $statements[] = 'DROP TABLE ' . $table_name; 440 441 // PGSQL does not "tightly" bind sequences and tables, we must guess... 442 $sql = "SELECT relname 443 FROM pg_class 444 WHERE relkind = 'S' 445 AND relname = '{$table_name}_seq'"; 446 $result = $this->db->sql_query($sql); 447 448 // We don't even care about storing the results. We already know the answer if we get rows back. 449 if ($this->db->sql_fetchrow($result)) 450 { 451 $statements[] = "DROP SEQUENCE IF EXISTS {$table_name}_seq;\n"; 452 } 453 $this->db->sql_freeresult($result); 454 455 return $this->_sql_run_sql($statements); 456 } 457 458 /** 459 * {@inheritDoc} 460 */ 461 function sql_create_primary_key($table_name, $column, $inline = false) 462 { 463 $statements = array(); 464 465 $statements[] = 'ALTER TABLE ' . $table_name . ' ADD PRIMARY KEY (' . implode(', ', $column) . ')'; 466 467 return $this->_sql_run_sql($statements); 468 } 469 470 /** 471 * {@inheritDoc} 472 */ 473 function sql_create_unique_index($table_name, $index_name, $column) 474 { 475 $statements = array(); 476 477 $this->check_index_name_length($table_name, $index_name); 478 479 $statements[] = 'CREATE UNIQUE INDEX ' . $table_name . '_' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ')'; 480 481 return $this->_sql_run_sql($statements); 482 } 483 484 /** 485 * {@inheritDoc} 486 */ 487 function sql_create_index($table_name, $index_name, $column) 488 { 489 $statements = array(); 490 491 $this->check_index_name_length($table_name, $index_name); 492 493 // remove index length 494 $column = preg_replace('#:.*$#', '', $column); 495 496 $statements[] = 'CREATE INDEX ' . $table_name . '_' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ')'; 497 498 return $this->_sql_run_sql($statements); 499 } 500 501 502 /** 503 * {@inheritDoc} 504 */ 505 function sql_list_index($table_name) 506 { 507 $index_array = array(); 508 509 $sql = "SELECT ic.relname as index_name 510 FROM pg_class bc, pg_class ic, pg_index i 511 WHERE (bc.oid = i.indrelid) 512 AND (ic.oid = i.indexrelid) 513 AND (bc.relname = '" . $table_name . "') 514 AND (i.indisunique != 't') 515 AND (i.indisprimary != 't')"; 516 $result = $this->db->sql_query($sql); 517 518 while ($row = $this->db->sql_fetchrow($result)) 519 { 520 $row['index_name'] = $this->strip_table_name_from_index_name($table_name, $row['index_name']); 521 522 $index_array[] = $row['index_name']; 523 } 524 $this->db->sql_freeresult($result); 525 526 return array_map('strtolower', $index_array); 527 } 528 529 /** 530 * {@inheritDoc} 531 */ 532 function sql_column_change($table_name, $column_name, $column_data, $inline = false) 533 { 534 $column_data = $this->sql_prepare_column_data($table_name, $column_name, $column_data); 535 $statements = array(); 536 537 $sql = 'ALTER TABLE ' . $table_name . ' '; 538 539 $sql_array = array(); 540 $sql_array[] = 'ALTER COLUMN ' . $column_name . ' TYPE ' . $column_data['column_type']; 541 542 if (isset($column_data['null'])) 543 { 544 if ($column_data['null'] == 'NOT NULL') 545 { 546 $sql_array[] = 'ALTER COLUMN ' . $column_name . ' SET NOT NULL'; 547 } 548 else if ($column_data['null'] == 'NULL') 549 { 550 $sql_array[] = 'ALTER COLUMN ' . $column_name . ' DROP NOT NULL'; 551 } 552 } 553 554 if (isset($column_data['default'])) 555 { 556 $sql_array[] = 'ALTER COLUMN ' . $column_name . ' SET DEFAULT ' . $column_data['default']; 557 } 558 559 // we don't want to double up on constraints if we change different number data types 560 if (isset($column_data['constraint'])) 561 { 562 $constraint_sql = "SELECT consrc as constraint_data 563 FROM pg_constraint, pg_class bc 564 WHERE conrelid = bc.oid 565 AND bc.relname = '{$table_name}' 566 AND NOT EXISTS ( 567 SELECT * 568 FROM pg_constraint as c, pg_inherits as i 569 WHERE i.inhrelid = pg_constraint.conrelid 570 AND c.conname = pg_constraint.conname 571 AND c.consrc = pg_constraint.consrc 572 AND c.conrelid = i.inhparent 573 )"; 574 575 $constraint_exists = false; 576 577 $result = $this->db->sql_query($constraint_sql); 578 while ($row = $this->db->sql_fetchrow($result)) 579 { 580 if (trim($row['constraint_data']) == trim($column_data['constraint'])) 581 { 582 $constraint_exists = true; 583 break; 584 } 585 } 586 $this->db->sql_freeresult($result); 587 588 if (!$constraint_exists) 589 { 590 $sql_array[] = 'ADD ' . $column_data['constraint']; 591 } 592 } 593 594 $sql .= implode(', ', $sql_array); 595 596 $statements[] = $sql; 597 598 return $this->_sql_run_sql($statements); 599 } 600 601 /** 602 * Get a list with existing indexes for the column 603 * 604 * @param string $table_name 605 * @param string $column_name 606 * @param bool $unique Should we get unique indexes or normal ones 607 * @return array Array with Index name => columns 608 */ 609 public function get_existing_indexes($table_name, $column_name, $unique = false) 610 { 611 // Not supported 612 throw new \Exception('DBMS is not supported'); 613 } 614 }
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
| Generated: Wed Nov 11 20:33:01 2020 | Cross-referenced by PHPXref 0.7.1 |