| [ Index ] |
PHP Cross Reference of phpBB-3.3.14-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 static public 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_table_exists($table_name) 103 { 104 $sql = "SELECT CAST(EXISTS( 105 SELECT * FROM information_schema.tables 106 WHERE table_schema = 'public' 107 AND table_name = '" . $this->db->sql_escape($table_name) . "' 108 ) AS INTEGER)"; 109 $result = $this->db->sql_query_limit($sql, 1); 110 $row = $this->db->sql_fetchrow($result); 111 $table_exists = (booL) $row['exists']; 112 $this->db->sql_freeresult($result); 113 114 return $table_exists; 115 } 116 117 /** 118 * {@inheritDoc} 119 */ 120 function sql_create_table($table_name, $table_data) 121 { 122 // holds the DDL for a column 123 $columns = $statements = array(); 124 125 if ($this->sql_table_exists($table_name)) 126 { 127 return $this->_sql_run_sql($statements); 128 } 129 130 // Begin transaction 131 $statements[] = 'begin'; 132 133 // Determine if we have created a PRIMARY KEY in the earliest 134 $primary_key_gen = false; 135 136 // Determine if the table requires a sequence 137 $create_sequence = false; 138 139 // Begin table sql statement 140 $table_sql = 'CREATE TABLE ' . $table_name . ' (' . "\n"; 141 142 // Iterate through the columns to create a table 143 foreach ($table_data['COLUMNS'] as $column_name => $column_data) 144 { 145 // here lies an array, filled with information compiled on the column's data 146 $prepared_column = $this->sql_prepare_column_data($table_name, $column_name, $column_data); 147 148 if (isset($prepared_column['auto_increment']) && $prepared_column['auto_increment'] && strlen($column_name) > 26) // "$column_name}_gen" 149 { 150 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); 151 } 152 153 // here we add the definition of the new column to the list of columns 154 $columns[] = "\t {$column_name} " . $prepared_column['column_type_sql']; 155 156 // see if we have found a primary key set due to a column definition if we have found it, we can stop looking 157 if (!$primary_key_gen) 158 { 159 $primary_key_gen = isset($prepared_column['primary_key_set']) && $prepared_column['primary_key_set']; 160 } 161 162 // create sequence DDL based off of the existence of auto incrementing columns 163 if (!$create_sequence && isset($prepared_column['auto_increment']) && $prepared_column['auto_increment']) 164 { 165 $create_sequence = $column_name; 166 } 167 } 168 169 // this makes up all the columns in the create table statement 170 $table_sql .= implode(",\n", $columns); 171 172 // we have yet to create a primary key for this table, 173 // this means that we can add the one we really wanted instead 174 if (!$primary_key_gen) 175 { 176 // Write primary key 177 if (isset($table_data['PRIMARY_KEY'])) 178 { 179 if (!is_array($table_data['PRIMARY_KEY'])) 180 { 181 $table_data['PRIMARY_KEY'] = array($table_data['PRIMARY_KEY']); 182 } 183 184 $table_sql .= ",\n\t PRIMARY KEY (" . implode(', ', $table_data['PRIMARY_KEY']) . ')'; 185 } 186 } 187 188 // do we need to add a sequence for auto incrementing columns? 189 if ($create_sequence) 190 { 191 $statements[] = "CREATE SEQUENCE {$table_name}_seq;"; 192 } 193 194 // close the table 195 $table_sql .= "\n);"; 196 $statements[] = $table_sql; 197 198 // Write Keys 199 if (isset($table_data['KEYS'])) 200 { 201 foreach ($table_data['KEYS'] as $key_name => $key_data) 202 { 203 if (!is_array($key_data[1])) 204 { 205 $key_data[1] = array($key_data[1]); 206 } 207 208 $old_return_statements = $this->return_statements; 209 $this->return_statements = true; 210 211 $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]); 212 213 foreach ($key_stmts as $key_stmt) 214 { 215 $statements[] = $key_stmt; 216 } 217 218 $this->return_statements = $old_return_statements; 219 } 220 } 221 222 // Commit Transaction 223 $statements[] = 'commit'; 224 225 return $this->_sql_run_sql($statements); 226 } 227 228 /** 229 * {@inheritDoc} 230 */ 231 function sql_list_columns($table_name) 232 { 233 $columns = array(); 234 235 $sql = "SELECT a.attname 236 FROM pg_class c, pg_attribute a 237 WHERE c.relname = '{$table_name}' 238 AND a.attnum > 0 239 AND a.attrelid = c.oid"; 240 $result = $this->db->sql_query($sql); 241 242 while ($row = $this->db->sql_fetchrow($result)) 243 { 244 $column = strtolower(current($row)); 245 $columns[$column] = $column; 246 } 247 $this->db->sql_freeresult($result); 248 249 return $columns; 250 } 251 252 /** 253 * {@inheritDoc} 254 */ 255 function sql_index_exists($table_name, $index_name) 256 { 257 $sql = "SELECT ic.relname as index_name 258 FROM pg_class bc, pg_class ic, pg_index i 259 WHERE (bc.oid = i.indrelid) 260 AND (ic.oid = i.indexrelid) 261 AND (bc.relname = '" . $table_name . "') 262 AND (i.indisunique != 't') 263 AND (i.indisprimary != 't')"; 264 $result = $this->db->sql_query($sql); 265 266 while ($row = $this->db->sql_fetchrow($result)) 267 { 268 // This DBMS prefixes index names with the table name 269 $row['index_name'] = $this->strip_table_name_from_index_name($table_name, $row['index_name']); 270 271 if (strtolower($row['index_name']) == strtolower($index_name)) 272 { 273 $this->db->sql_freeresult($result); 274 return true; 275 } 276 } 277 $this->db->sql_freeresult($result); 278 279 return false; 280 } 281 282 /** 283 * {@inheritDoc} 284 */ 285 function sql_unique_index_exists($table_name, $index_name) 286 { 287 $sql = "SELECT ic.relname as index_name, i.indisunique 288 FROM pg_class bc, pg_class ic, pg_index i 289 WHERE (bc.oid = i.indrelid) 290 AND (ic.oid = i.indexrelid) 291 AND (bc.relname = '" . $table_name . "') 292 AND (i.indisprimary != 't')"; 293 $result = $this->db->sql_query($sql); 294 295 while ($row = $this->db->sql_fetchrow($result)) 296 { 297 if ($row['indisunique'] != 't') 298 { 299 continue; 300 } 301 302 // This DBMS prefixes index names with the table name 303 $row['index_name'] = $this->strip_table_name_from_index_name($table_name, $row['index_name']); 304 305 if (strtolower($row['index_name']) == strtolower($index_name)) 306 { 307 $this->db->sql_freeresult($result); 308 return true; 309 } 310 } 311 $this->db->sql_freeresult($result); 312 313 return false; 314 } 315 316 /** 317 * Function to prepare some column information for better usage 318 * @access private 319 */ 320 function sql_prepare_column_data($table_name, $column_name, $column_data) 321 { 322 if (strlen($column_name) > 30) 323 { 324 trigger_error("Column name '$column_name' on table '$table_name' is too long. The maximum is 30 characters.", E_USER_ERROR); 325 } 326 327 // Get type 328 list($column_type, $orig_column_type) = $this->get_column_type($column_data[0]); 329 330 // Adjust default value if db-dependent specified 331 if (is_array($column_data[1])) 332 { 333 $column_data[1] = (isset($column_data[1][$this->sql_layer])) ? $column_data[1][$this->sql_layer] : $column_data[1]['default']; 334 } 335 336 $sql = " {$column_type} "; 337 338 $return_array = array( 339 'column_type' => $column_type, 340 'auto_increment' => false, 341 ); 342 343 if (isset($column_data[2]) && $column_data[2] == 'auto_increment') 344 { 345 $default_val = "nextval('{$table_name}_seq')"; 346 $return_array['auto_increment'] = true; 347 } 348 else if (!is_null($column_data[1])) 349 { 350 $default_val = "'" . $column_data[1] . "'"; 351 $return_array['null'] = 'NOT NULL'; 352 $sql .= 'NOT NULL '; 353 } 354 else 355 { 356 // Integers need to have 0 instead of empty string as default 357 if (strpos($column_type, 'INT') === 0) 358 { 359 $default_val = '0'; 360 } 361 else 362 { 363 $default_val = "'" . $column_data[1] . "'"; 364 } 365 $return_array['null'] = 'NULL'; 366 $sql .= 'NULL '; 367 } 368 369 $return_array['default'] = $default_val; 370 371 $sql .= "DEFAULT {$default_val}"; 372 373 // Unsigned? Then add a CHECK contraint 374 if (in_array($orig_column_type, $this->unsigned_types)) 375 { 376 $return_array['constraint'] = "CHECK ({$column_name} >= 0)"; 377 $sql .= " CHECK ({$column_name} >= 0)"; 378 } 379 380 $return_array['column_type_sql'] = $sql; 381 382 return $return_array; 383 } 384 385 /** 386 * {@inheritDoc} 387 */ 388 function sql_column_add($table_name, $column_name, $column_data, $inline = false) 389 { 390 $column_data = $this->sql_prepare_column_data($table_name, $column_name, $column_data); 391 $statements = array(); 392 393 // Does not support AFTER, only through temporary table 394 if (version_compare($this->db->sql_server_info(true), '8.0', '>=')) 395 { 396 $statements[] = 'ALTER TABLE ' . $table_name . ' ADD COLUMN "' . $column_name . '" ' . $column_data['column_type_sql']; 397 } 398 else 399 { 400 // old versions cannot add columns with default and null information 401 $statements[] = 'ALTER TABLE ' . $table_name . ' ADD COLUMN "' . $column_name . '" ' . $column_data['column_type'] . ' ' . $column_data['constraint']; 402 403 if (isset($column_data['null'])) 404 { 405 if ($column_data['null'] == 'NOT NULL') 406 { 407 $statements[] = 'ALTER TABLE ' . $table_name . ' ALTER COLUMN ' . $column_name . ' SET NOT NULL'; 408 } 409 } 410 411 if (isset($column_data['default'])) 412 { 413 $statements[] = 'ALTER TABLE ' . $table_name . ' ALTER COLUMN ' . $column_name . ' SET DEFAULT ' . $column_data['default']; 414 } 415 } 416 417 return $this->_sql_run_sql($statements); 418 } 419 420 /** 421 * {@inheritDoc} 422 */ 423 function sql_column_remove($table_name, $column_name, $inline = false) 424 { 425 $statements = array(); 426 427 $statements[] = 'ALTER TABLE ' . $table_name . ' DROP COLUMN "' . $column_name . '"'; 428 429 return $this->_sql_run_sql($statements); 430 } 431 432 /** 433 * {@inheritDoc} 434 */ 435 function sql_index_drop($table_name, $index_name) 436 { 437 $statements = array(); 438 439 $statements[] = 'DROP INDEX ' . $table_name . '_' . $index_name; 440 441 return $this->_sql_run_sql($statements); 442 } 443 444 /** 445 * {@inheritDoc} 446 */ 447 function sql_table_drop($table_name) 448 { 449 $statements = array(); 450 451 if (!$this->sql_table_exists($table_name)) 452 { 453 return $this->_sql_run_sql($statements); 454 } 455 456 // the most basic operation, get rid of the table 457 $statements[] = 'DROP TABLE ' . $table_name; 458 459 // PGSQL does not "tightly" bind sequences and tables, we must guess... 460 $sql = "SELECT relname 461 FROM pg_class 462 WHERE relkind = 'S' 463 AND relname = '{$table_name}_seq'"; 464 $result = $this->db->sql_query($sql); 465 466 // We don't even care about storing the results. We already know the answer if we get rows back. 467 if ($this->db->sql_fetchrow($result)) 468 { 469 $statements[] = "DROP SEQUENCE IF EXISTS {$table_name}_seq;\n"; 470 } 471 $this->db->sql_freeresult($result); 472 473 return $this->_sql_run_sql($statements); 474 } 475 476 /** 477 * {@inheritDoc} 478 */ 479 function sql_create_primary_key($table_name, $column, $inline = false) 480 { 481 $statements = array(); 482 483 $statements[] = 'ALTER TABLE ' . $table_name . ' ADD PRIMARY KEY (' . implode(', ', $column) . ')'; 484 485 return $this->_sql_run_sql($statements); 486 } 487 488 /** 489 * {@inheritDoc} 490 */ 491 function sql_create_unique_index($table_name, $index_name, $column) 492 { 493 $statements = array(); 494 495 $this->check_index_name_length($table_name, $index_name); 496 497 $statements[] = 'CREATE UNIQUE INDEX ' . $table_name . '_' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ')'; 498 499 return $this->_sql_run_sql($statements); 500 } 501 502 /** 503 * {@inheritDoc} 504 */ 505 function sql_create_index($table_name, $index_name, $column) 506 { 507 $statements = array(); 508 509 $this->check_index_name_length($table_name, $index_name); 510 511 // remove index length 512 $column = preg_replace('#:.*$#', '', $column); 513 514 $statements[] = 'CREATE INDEX ' . $table_name . '_' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ')'; 515 516 return $this->_sql_run_sql($statements); 517 } 518 519 520 /** 521 * {@inheritDoc} 522 */ 523 function sql_list_index($table_name) 524 { 525 $index_array = array(); 526 527 $sql = "SELECT ic.relname as index_name 528 FROM pg_class bc, pg_class ic, pg_index i 529 WHERE (bc.oid = i.indrelid) 530 AND (ic.oid = i.indexrelid) 531 AND (bc.relname = '" . $table_name . "') 532 AND (i.indisunique != 't') 533 AND (i.indisprimary != 't')"; 534 $result = $this->db->sql_query($sql); 535 536 while ($row = $this->db->sql_fetchrow($result)) 537 { 538 $row['index_name'] = $this->strip_table_name_from_index_name($table_name, $row['index_name']); 539 540 $index_array[] = $row['index_name']; 541 } 542 $this->db->sql_freeresult($result); 543 544 return array_map('strtolower', $index_array); 545 } 546 547 /** 548 * {@inheritDoc} 549 */ 550 function sql_column_change($table_name, $column_name, $column_data, $inline = false) 551 { 552 $column_data = $this->sql_prepare_column_data($table_name, $column_name, $column_data); 553 $statements = array(); 554 555 $sql = 'ALTER TABLE ' . $table_name . ' '; 556 557 $sql_array = array(); 558 $sql_array[] = 'ALTER COLUMN ' . $column_name . ' TYPE ' . $column_data['column_type']; 559 560 if (isset($column_data['null'])) 561 { 562 if ($column_data['null'] == 'NOT NULL') 563 { 564 $sql_array[] = 'ALTER COLUMN ' . $column_name . ' SET NOT NULL'; 565 } 566 else if ($column_data['null'] == 'NULL') 567 { 568 $sql_array[] = 'ALTER COLUMN ' . $column_name . ' DROP NOT NULL'; 569 } 570 } 571 572 if (isset($column_data['default'])) 573 { 574 $sql_array[] = 'ALTER COLUMN ' . $column_name . ' SET DEFAULT ' . $column_data['default']; 575 } 576 577 // we don't want to double up on constraints if we change different number data types 578 if (isset($column_data['constraint'])) 579 { 580 $constraint_sql = "SELECT pg_get_constraintdef(pc.oid) AS constraint_data 581 FROM pg_constraint pc, pg_class bc 582 WHERE conrelid = bc.oid 583 AND bc.relname = '" . $this->db->sql_escape($table_name) . "' 584 AND NOT EXISTS ( 585 SELECT * 586 FROM pg_constraint AS c, pg_inherits AS i 587 WHERE i.inhrelid = pc.conrelid 588 AND c.conname = pc.conname 589 AND pg_get_constraintdef(c.oid) = pg_get_constraintdef(pc.oid) 590 AND c.conrelid = i.inhparent 591 )"; 592 593 $constraint_exists = false; 594 595 $result = $this->db->sql_query($constraint_sql); 596 while ($row = $this->db->sql_fetchrow($result)) 597 { 598 if (trim($row['constraint_data']) == trim($column_data['constraint'])) 599 { 600 $constraint_exists = true; 601 break; 602 } 603 } 604 $this->db->sql_freeresult($result); 605 606 if (!$constraint_exists) 607 { 608 $sql_array[] = 'ADD ' . $column_data['constraint']; 609 } 610 } 611 612 $sql .= implode(', ', $sql_array); 613 614 $statements[] = $sql; 615 616 return $this->_sql_run_sql($statements); 617 } 618 619 /** 620 * Get a list with existing indexes for the column 621 * 622 * @param string $table_name 623 * @param string $column_name 624 * @param bool $unique Should we get unique indexes or normal ones 625 * @return array Array with Index name => columns 626 */ 627 public function get_existing_indexes($table_name, $column_name, $unique = false) 628 { 629 // Not supported 630 throw new \Exception('DBMS is not supported'); 631 } 632 }
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
| Generated: Mon Nov 25 19:05:08 2024 | Cross-referenced by PHPXref 0.7.1 |