[ 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 tools implements tools_interface 21 { 22 /** 23 * Current sql layer 24 */ 25 var $sql_layer = ''; 26 27 /** 28 * @var object DB object 29 */ 30 var $db = null; 31 32 /** 33 * The Column types for every database we support 34 * @var array 35 */ 36 var $dbms_type_map = array(); 37 38 /** 39 * Get the column types for every database we support 40 * 41 * @return array 42 */ 43 static public function get_dbms_type_map() 44 { 45 return array( 46 'mysql_41' => array( 47 'INT:' => 'int(%d)', 48 'BINT' => 'bigint(20)', 49 'ULINT' => 'INT(10) UNSIGNED', 50 'UINT' => 'mediumint(8) UNSIGNED', 51 'UINT:' => 'int(%d) UNSIGNED', 52 'TINT:' => 'tinyint(%d)', 53 'USINT' => 'smallint(4) UNSIGNED', 54 'BOOL' => 'tinyint(1) UNSIGNED', 55 'VCHAR' => 'varchar(255)', 56 'VCHAR:' => 'varchar(%d)', 57 'CHAR:' => 'char(%d)', 58 'XSTEXT' => 'text', 59 'XSTEXT_UNI'=> 'varchar(100)', 60 'STEXT' => 'text', 61 'STEXT_UNI' => 'varchar(255)', 62 'TEXT' => 'text', 63 'TEXT_UNI' => 'text', 64 'MTEXT' => 'mediumtext', 65 'MTEXT_UNI' => 'mediumtext', 66 'TIMESTAMP' => 'int(11) UNSIGNED', 67 'DECIMAL' => 'decimal(5,2)', 68 'DECIMAL:' => 'decimal(%d,2)', 69 'PDECIMAL' => 'decimal(6,3)', 70 'PDECIMAL:' => 'decimal(%d,3)', 71 'VCHAR_UNI' => 'varchar(255)', 72 'VCHAR_UNI:'=> 'varchar(%d)', 73 'VCHAR_CI' => 'varchar(255)', 74 'VARBINARY' => 'varbinary(255)', 75 ), 76 77 'mysql_40' => array( 78 'INT:' => 'int(%d)', 79 'BINT' => 'bigint(20)', 80 'ULINT' => 'INT(10) UNSIGNED', 81 'UINT' => 'mediumint(8) UNSIGNED', 82 'UINT:' => 'int(%d) UNSIGNED', 83 'TINT:' => 'tinyint(%d)', 84 'USINT' => 'smallint(4) UNSIGNED', 85 'BOOL' => 'tinyint(1) UNSIGNED', 86 'VCHAR' => 'varbinary(255)', 87 'VCHAR:' => 'varbinary(%d)', 88 'CHAR:' => 'binary(%d)', 89 'XSTEXT' => 'blob', 90 'XSTEXT_UNI'=> 'blob', 91 'STEXT' => 'blob', 92 'STEXT_UNI' => 'blob', 93 'TEXT' => 'blob', 94 'TEXT_UNI' => 'blob', 95 'MTEXT' => 'mediumblob', 96 'MTEXT_UNI' => 'mediumblob', 97 'TIMESTAMP' => 'int(11) UNSIGNED', 98 'DECIMAL' => 'decimal(5,2)', 99 'DECIMAL:' => 'decimal(%d,2)', 100 'PDECIMAL' => 'decimal(6,3)', 101 'PDECIMAL:' => 'decimal(%d,3)', 102 'VCHAR_UNI' => 'blob', 103 'VCHAR_UNI:'=> array('varbinary(%d)', 'limit' => array('mult', 3, 255, 'blob')), 104 'VCHAR_CI' => 'blob', 105 'VARBINARY' => 'varbinary(255)', 106 ), 107 108 'oracle' => array( 109 'INT:' => 'number(%d)', 110 'BINT' => 'number(20)', 111 'ULINT' => 'number(10)', 112 'UINT' => 'number(8)', 113 'UINT:' => 'number(%d)', 114 'TINT:' => 'number(%d)', 115 'USINT' => 'number(4)', 116 'BOOL' => 'number(1)', 117 'VCHAR' => 'varchar2(255)', 118 'VCHAR:' => 'varchar2(%d)', 119 'CHAR:' => 'char(%d)', 120 'XSTEXT' => 'varchar2(1000)', 121 'STEXT' => 'varchar2(3000)', 122 'TEXT' => 'clob', 123 'MTEXT' => 'clob', 124 'XSTEXT_UNI'=> 'varchar2(300)', 125 'STEXT_UNI' => 'varchar2(765)', 126 'TEXT_UNI' => 'clob', 127 'MTEXT_UNI' => 'clob', 128 'TIMESTAMP' => 'number(11)', 129 'DECIMAL' => 'number(5, 2)', 130 'DECIMAL:' => 'number(%d, 2)', 131 'PDECIMAL' => 'number(6, 3)', 132 'PDECIMAL:' => 'number(%d, 3)', 133 'VCHAR_UNI' => 'varchar2(765)', 134 'VCHAR_UNI:'=> array('varchar2(%d)', 'limit' => array('mult', 3, 765, 'clob')), 135 'VCHAR_CI' => 'varchar2(255)', 136 'VARBINARY' => 'raw(255)', 137 ), 138 139 'sqlite3' => array( 140 'INT:' => 'INT(%d)', 141 'BINT' => 'BIGINT(20)', 142 'ULINT' => 'INTEGER UNSIGNED', 143 'UINT' => 'INTEGER UNSIGNED', 144 'UINT:' => 'INTEGER UNSIGNED', 145 'TINT:' => 'TINYINT(%d)', 146 'USINT' => 'INTEGER UNSIGNED', 147 'BOOL' => 'INTEGER UNSIGNED', 148 'VCHAR' => 'VARCHAR(255)', 149 'VCHAR:' => 'VARCHAR(%d)', 150 'CHAR:' => 'CHAR(%d)', 151 'XSTEXT' => 'TEXT(65535)', 152 'STEXT' => 'TEXT(65535)', 153 'TEXT' => 'TEXT(65535)', 154 'MTEXT' => 'MEDIUMTEXT(16777215)', 155 'XSTEXT_UNI'=> 'TEXT(65535)', 156 'STEXT_UNI' => 'TEXT(65535)', 157 'TEXT_UNI' => 'TEXT(65535)', 158 'MTEXT_UNI' => 'MEDIUMTEXT(16777215)', 159 'TIMESTAMP' => 'INTEGER UNSIGNED', //'int(11) UNSIGNED', 160 'DECIMAL' => 'DECIMAL(5,2)', 161 'DECIMAL:' => 'DECIMAL(%d,2)', 162 'PDECIMAL' => 'DECIMAL(6,3)', 163 'PDECIMAL:' => 'DECIMAL(%d,3)', 164 'VCHAR_UNI' => 'VARCHAR(255)', 165 'VCHAR_UNI:'=> 'VARCHAR(%d)', 166 'VCHAR_CI' => 'VARCHAR(255)', 167 'VARBINARY' => 'BLOB', 168 ), 169 ); 170 } 171 172 /** 173 * A list of types being unsigned for better reference in some db's 174 * @var array 175 */ 176 var $unsigned_types = array('ULINT', 'UINT', 'UINT:', 'USINT', 'BOOL', 'TIMESTAMP'); 177 178 /** 179 * This is set to true if user only wants to return the 'to-be-executed' SQL statement(s) (as an array). 180 * This mode has no effect on some methods (inserting of data for example). This is expressed within the methods command. 181 */ 182 var $return_statements = false; 183 184 /** 185 * Constructor. Set DB Object and set {@link $return_statements return_statements}. 186 * 187 * @param \phpbb\db\driver\driver_interface $db Database connection 188 * @param bool $return_statements True if only statements should be returned and no SQL being executed 189 */ 190 public function __construct(\phpbb\db\driver\driver_interface $db, $return_statements = false) 191 { 192 $this->db = $db; 193 $this->return_statements = $return_statements; 194 195 $this->dbms_type_map = self::get_dbms_type_map(); 196 197 // Determine mapping database type 198 switch ($this->db->get_sql_layer()) 199 { 200 case 'mysql': 201 $this->sql_layer = 'mysql_40'; 202 break; 203 204 case 'mysql4': 205 if (version_compare($this->db->sql_server_info(true), '4.1.3', '>=')) 206 { 207 $this->sql_layer = 'mysql_41'; 208 } 209 else 210 { 211 $this->sql_layer = 'mysql_40'; 212 } 213 break; 214 215 case 'mysqli': 216 $this->sql_layer = 'mysql_41'; 217 break; 218 219 default: 220 $this->sql_layer = $this->db->get_sql_layer(); 221 break; 222 } 223 } 224 225 /** 226 * Setter for {@link $return_statements return_statements}. 227 * 228 * @param bool $return_statements True if SQL should not be executed but returned as strings 229 * @return null 230 */ 231 public function set_return_statements($return_statements) 232 { 233 $this->return_statements = $return_statements; 234 } 235 236 /** 237 * {@inheritDoc} 238 */ 239 function sql_list_tables() 240 { 241 switch ($this->db->get_sql_layer()) 242 { 243 case 'mysql': 244 case 'mysql4': 245 case 'mysqli': 246 $sql = 'SHOW TABLES'; 247 break; 248 249 case 'sqlite3': 250 $sql = 'SELECT name 251 FROM sqlite_master 252 WHERE type = "table" 253 AND name <> "sqlite_sequence"'; 254 break; 255 256 case 'oracle': 257 $sql = 'SELECT table_name 258 FROM USER_TABLES'; 259 break; 260 } 261 262 $result = $this->db->sql_query($sql); 263 264 $tables = array(); 265 while ($row = $this->db->sql_fetchrow($result)) 266 { 267 $name = current($row); 268 $tables[$name] = $name; 269 } 270 $this->db->sql_freeresult($result); 271 272 return $tables; 273 } 274 275 /** 276 * {@inheritDoc} 277 */ 278 function sql_table_exists($table_name) 279 { 280 $this->db->sql_return_on_error(true); 281 $result = $this->db->sql_query_limit('SELECT * FROM ' . $table_name, 1); 282 $this->db->sql_return_on_error(false); 283 284 if ($result) 285 { 286 $this->db->sql_freeresult($result); 287 return true; 288 } 289 290 return false; 291 } 292 293 /** 294 * {@inheritDoc} 295 */ 296 function sql_create_table($table_name, $table_data) 297 { 298 // holds the DDL for a column 299 $columns = $statements = array(); 300 301 if ($this->sql_table_exists($table_name)) 302 { 303 return $this->_sql_run_sql($statements); 304 } 305 306 // Begin transaction 307 $statements[] = 'begin'; 308 309 // Determine if we have created a PRIMARY KEY in the earliest 310 $primary_key_gen = false; 311 312 // Determine if the table requires a sequence 313 $create_sequence = false; 314 315 // Begin table sql statement 316 $table_sql = 'CREATE TABLE ' . $table_name . ' (' . "\n"; 317 318 // Iterate through the columns to create a table 319 foreach ($table_data['COLUMNS'] as $column_name => $column_data) 320 { 321 // here lies an array, filled with information compiled on the column's data 322 $prepared_column = $this->sql_prepare_column_data($table_name, $column_name, $column_data); 323 324 if (isset($prepared_column['auto_increment']) && $prepared_column['auto_increment'] && strlen($column_name) > 26) // "$column_name}_gen" 325 { 326 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); 327 } 328 329 // here we add the definition of the new column to the list of columns 330 $columns[] = "\t {$column_name} " . $prepared_column['column_type_sql']; 331 332 // see if we have found a primary key set due to a column definition if we have found it, we can stop looking 333 if (!$primary_key_gen) 334 { 335 $primary_key_gen = isset($prepared_column['primary_key_set']) && $prepared_column['primary_key_set']; 336 } 337 338 // create sequence DDL based off of the existance of auto incrementing columns 339 if (!$create_sequence && isset($prepared_column['auto_increment']) && $prepared_column['auto_increment']) 340 { 341 $create_sequence = $column_name; 342 } 343 } 344 345 // this makes up all the columns in the create table statement 346 $table_sql .= implode(",\n", $columns); 347 348 // we have yet to create a primary key for this table, 349 // this means that we can add the one we really wanted instead 350 if (!$primary_key_gen) 351 { 352 // Write primary key 353 if (isset($table_data['PRIMARY_KEY'])) 354 { 355 if (!is_array($table_data['PRIMARY_KEY'])) 356 { 357 $table_data['PRIMARY_KEY'] = array($table_data['PRIMARY_KEY']); 358 } 359 360 switch ($this->sql_layer) 361 { 362 case 'mysql_40': 363 case 'mysql_41': 364 case 'sqlite3': 365 $table_sql .= ",\n\t PRIMARY KEY (" . implode(', ', $table_data['PRIMARY_KEY']) . ')'; 366 break; 367 368 case 'oracle': 369 $table_sql .= ",\n\t CONSTRAINT pk_{$table_name} PRIMARY KEY (" . implode(', ', $table_data['PRIMARY_KEY']) . ')'; 370 break; 371 } 372 } 373 } 374 375 // close the table 376 switch ($this->sql_layer) 377 { 378 case 'mysql_41': 379 // make sure the table is in UTF-8 mode 380 $table_sql .= "\n) CHARACTER SET `utf8` COLLATE `utf8_bin`;"; 381 $statements[] = $table_sql; 382 break; 383 384 case 'mysql_40': 385 case 'sqlite3': 386 $table_sql .= "\n);"; 387 $statements[] = $table_sql; 388 break; 389 390 case 'oracle': 391 $table_sql .= "\n)"; 392 $statements[] = $table_sql; 393 394 // do we need to add a sequence and a tigger for auto incrementing columns? 395 if ($create_sequence) 396 { 397 // create the actual sequence 398 $statements[] = "CREATE SEQUENCE {$table_name}_seq"; 399 400 // the trigger is the mechanism by which we increment the counter 401 $trigger = "CREATE OR REPLACE TRIGGER t_{$table_name}\n"; 402 $trigger .= "BEFORE INSERT ON {$table_name}\n"; 403 $trigger .= "FOR EACH ROW WHEN (\n"; 404 $trigger .= "\tnew.{$create_sequence} IS NULL OR new.{$create_sequence} = 0\n"; 405 $trigger .= ")\n"; 406 $trigger .= "BEGIN\n"; 407 $trigger .= "\tSELECT {$table_name}_seq.nextval\n"; 408 $trigger .= "\tINTO :new.{$create_sequence}\n"; 409 $trigger .= "\tFROM dual;\n"; 410 $trigger .= "END;"; 411 412 $statements[] = $trigger; 413 } 414 break; 415 } 416 417 // Write Keys 418 if (isset($table_data['KEYS'])) 419 { 420 foreach ($table_data['KEYS'] as $key_name => $key_data) 421 { 422 if (!is_array($key_data[1])) 423 { 424 $key_data[1] = array($key_data[1]); 425 } 426 427 $old_return_statements = $this->return_statements; 428 $this->return_statements = true; 429 430 $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]); 431 432 foreach ($key_stmts as $key_stmt) 433 { 434 $statements[] = $key_stmt; 435 } 436 437 $this->return_statements = $old_return_statements; 438 } 439 } 440 441 // Commit Transaction 442 $statements[] = 'commit'; 443 444 return $this->_sql_run_sql($statements); 445 } 446 447 /** 448 * {@inheritDoc} 449 */ 450 function perform_schema_changes($schema_changes) 451 { 452 if (empty($schema_changes)) 453 { 454 return; 455 } 456 457 $statements = array(); 458 $sqlite = false; 459 460 // For SQLite we need to perform the schema changes in a much more different way 461 if ($this->db->get_sql_layer() == 'sqlite3' && $this->return_statements) 462 { 463 $sqlite_data = array(); 464 $sqlite = true; 465 } 466 467 // Drop tables? 468 if (!empty($schema_changes['drop_tables'])) 469 { 470 foreach ($schema_changes['drop_tables'] as $table) 471 { 472 // only drop table if it exists 473 if ($this->sql_table_exists($table)) 474 { 475 $result = $this->sql_table_drop($table); 476 if ($this->return_statements) 477 { 478 $statements = array_merge($statements, $result); 479 } 480 } 481 } 482 } 483 484 // Add tables? 485 if (!empty($schema_changes['add_tables'])) 486 { 487 foreach ($schema_changes['add_tables'] as $table => $table_data) 488 { 489 $result = $this->sql_create_table($table, $table_data); 490 if ($this->return_statements) 491 { 492 $statements = array_merge($statements, $result); 493 } 494 } 495 } 496 497 // Change columns? 498 if (!empty($schema_changes['change_columns'])) 499 { 500 foreach ($schema_changes['change_columns'] as $table => $columns) 501 { 502 foreach ($columns as $column_name => $column_data) 503 { 504 // If the column exists we change it, else we add it ;) 505 if ($column_exists = $this->sql_column_exists($table, $column_name)) 506 { 507 $result = $this->sql_column_change($table, $column_name, $column_data, true); 508 } 509 else 510 { 511 $result = $this->sql_column_add($table, $column_name, $column_data, true); 512 } 513 514 if ($sqlite) 515 { 516 if ($column_exists) 517 { 518 $sqlite_data[$table]['change_columns'][] = $result; 519 } 520 else 521 { 522 $sqlite_data[$table]['add_columns'][] = $result; 523 } 524 } 525 else if ($this->return_statements) 526 { 527 $statements = array_merge($statements, $result); 528 } 529 } 530 } 531 } 532 533 // Add columns? 534 if (!empty($schema_changes['add_columns'])) 535 { 536 foreach ($schema_changes['add_columns'] as $table => $columns) 537 { 538 foreach ($columns as $column_name => $column_data) 539 { 540 // Only add the column if it does not exist yet 541 if ($column_exists = $this->sql_column_exists($table, $column_name)) 542 { 543 continue; 544 // This is commented out here because it can take tremendous time on updates 545 // $result = $this->sql_column_change($table, $column_name, $column_data, true); 546 } 547 else 548 { 549 $result = $this->sql_column_add($table, $column_name, $column_data, true); 550 } 551 552 if ($sqlite) 553 { 554 if ($column_exists) 555 { 556 continue; 557 // $sqlite_data[$table]['change_columns'][] = $result; 558 } 559 else 560 { 561 $sqlite_data[$table]['add_columns'][] = $result; 562 } 563 } 564 else if ($this->return_statements) 565 { 566 $statements = array_merge($statements, $result); 567 } 568 } 569 } 570 } 571 572 // Remove keys? 573 if (!empty($schema_changes['drop_keys'])) 574 { 575 foreach ($schema_changes['drop_keys'] as $table => $indexes) 576 { 577 foreach ($indexes as $index_name) 578 { 579 if (!$this->sql_index_exists($table, $index_name) && !$this->sql_unique_index_exists($table, $index_name)) 580 { 581 continue; 582 } 583 584 $result = $this->sql_index_drop($table, $index_name); 585 586 if ($this->return_statements) 587 { 588 $statements = array_merge($statements, $result); 589 } 590 } 591 } 592 } 593 594 // Drop columns? 595 if (!empty($schema_changes['drop_columns'])) 596 { 597 foreach ($schema_changes['drop_columns'] as $table => $columns) 598 { 599 foreach ($columns as $column) 600 { 601 // Only remove the column if it exists... 602 if ($this->sql_column_exists($table, $column)) 603 { 604 $result = $this->sql_column_remove($table, $column, true); 605 606 if ($sqlite) 607 { 608 $sqlite_data[$table]['drop_columns'][] = $result; 609 } 610 else if ($this->return_statements) 611 { 612 $statements = array_merge($statements, $result); 613 } 614 } 615 } 616 } 617 } 618 619 // Add primary keys? 620 if (!empty($schema_changes['add_primary_keys'])) 621 { 622 foreach ($schema_changes['add_primary_keys'] as $table => $columns) 623 { 624 $result = $this->sql_create_primary_key($table, $columns, true); 625 626 if ($sqlite) 627 { 628 $sqlite_data[$table]['primary_key'] = $result; 629 } 630 else if ($this->return_statements) 631 { 632 $statements = array_merge($statements, $result); 633 } 634 } 635 } 636 637 // Add unique indexes? 638 if (!empty($schema_changes['add_unique_index'])) 639 { 640 foreach ($schema_changes['add_unique_index'] as $table => $index_array) 641 { 642 foreach ($index_array as $index_name => $column) 643 { 644 if ($this->sql_unique_index_exists($table, $index_name)) 645 { 646 continue; 647 } 648 649 $result = $this->sql_create_unique_index($table, $index_name, $column); 650 651 if ($this->return_statements) 652 { 653 $statements = array_merge($statements, $result); 654 } 655 } 656 } 657 } 658 659 // Add indexes? 660 if (!empty($schema_changes['add_index'])) 661 { 662 foreach ($schema_changes['add_index'] as $table => $index_array) 663 { 664 foreach ($index_array as $index_name => $column) 665 { 666 if ($this->sql_index_exists($table, $index_name)) 667 { 668 continue; 669 } 670 671 $result = $this->sql_create_index($table, $index_name, $column); 672 673 if ($this->return_statements) 674 { 675 $statements = array_merge($statements, $result); 676 } 677 } 678 } 679 } 680 681 if ($sqlite) 682 { 683 foreach ($sqlite_data as $table_name => $sql_schema_changes) 684 { 685 // Create temporary table with original data 686 $statements[] = 'begin'; 687 688 $sql = "SELECT sql 689 FROM sqlite_master 690 WHERE type = 'table' 691 AND name = '{$table_name}' 692 ORDER BY type DESC, name;"; 693 $result = $this->db->sql_query($sql); 694 695 if (!$result) 696 { 697 continue; 698 } 699 700 $row = $this->db->sql_fetchrow($result); 701 $this->db->sql_freeresult($result); 702 703 // Create a backup table and populate it, destroy the existing one 704 $statements[] = preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $row['sql']); 705 $statements[] = 'INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name; 706 $statements[] = 'DROP TABLE ' . $table_name; 707 708 // Get the columns... 709 preg_match('#\((.*)\)#s', $row['sql'], $matches); 710 711 $plain_table_cols = trim($matches[1]); 712 $new_table_cols = preg_split('/,(?![\s\w]+\))/m', $plain_table_cols); 713 $column_list = array(); 714 715 foreach ($new_table_cols as $declaration) 716 { 717 $entities = preg_split('#\s+#', trim($declaration)); 718 if ($entities[0] == 'PRIMARY') 719 { 720 continue; 721 } 722 $column_list[] = $entities[0]; 723 } 724 725 // note down the primary key notation because sqlite only supports adding it to the end for the new table 726 $primary_key = false; 727 $_new_cols = array(); 728 729 foreach ($new_table_cols as $key => $declaration) 730 { 731 $entities = preg_split('#\s+#', trim($declaration)); 732 if ($entities[0] == 'PRIMARY') 733 { 734 $primary_key = $declaration; 735 continue; 736 } 737 $_new_cols[] = $declaration; 738 } 739 740 $new_table_cols = $_new_cols; 741 742 // First of all... change columns 743 if (!empty($sql_schema_changes['change_columns'])) 744 { 745 foreach ($sql_schema_changes['change_columns'] as $column_sql) 746 { 747 foreach ($new_table_cols as $key => $declaration) 748 { 749 $entities = preg_split('#\s+#', trim($declaration)); 750 if (strpos($column_sql, $entities[0] . ' ') === 0) 751 { 752 $new_table_cols[$key] = $column_sql; 753 } 754 } 755 } 756 } 757 758 if (!empty($sql_schema_changes['add_columns'])) 759 { 760 foreach ($sql_schema_changes['add_columns'] as $column_sql) 761 { 762 $new_table_cols[] = $column_sql; 763 } 764 } 765 766 // Now drop them... 767 if (!empty($sql_schema_changes['drop_columns'])) 768 { 769 foreach ($sql_schema_changes['drop_columns'] as $column_name) 770 { 771 // Remove from column list... 772 $new_column_list = array(); 773 foreach ($column_list as $key => $value) 774 { 775 if ($value === $column_name) 776 { 777 continue; 778 } 779 780 $new_column_list[] = $value; 781 } 782 783 $column_list = $new_column_list; 784 785 // Remove from table... 786 $_new_cols = array(); 787 foreach ($new_table_cols as $key => $declaration) 788 { 789 $entities = preg_split('#\s+#', trim($declaration)); 790 if (strpos($column_name . ' ', $entities[0] . ' ') === 0) 791 { 792 continue; 793 } 794 $_new_cols[] = $declaration; 795 } 796 $new_table_cols = $_new_cols; 797 } 798 } 799 800 // Primary key... 801 if (!empty($sql_schema_changes['primary_key'])) 802 { 803 $new_table_cols[] = 'PRIMARY KEY (' . implode(', ', $sql_schema_changes['primary_key']) . ')'; 804 } 805 // Add a new one or the old primary key 806 else if ($primary_key !== false) 807 { 808 $new_table_cols[] = $primary_key; 809 } 810 811 $columns = implode(',', $column_list); 812 813 // create a new table and fill it up. destroy the temp one 814 $statements[] = 'CREATE TABLE ' . $table_name . ' (' . implode(',', $new_table_cols) . ');'; 815 $statements[] = 'INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;'; 816 $statements[] = 'DROP TABLE ' . $table_name . '_temp'; 817 818 $statements[] = 'commit'; 819 } 820 } 821 822 if ($this->return_statements) 823 { 824 return $statements; 825 } 826 } 827 828 /** 829 * {@inheritDoc} 830 */ 831 function sql_list_columns($table_name) 832 { 833 $columns = array(); 834 835 switch ($this->sql_layer) 836 { 837 case 'mysql_40': 838 case 'mysql_41': 839 $sql = "SHOW COLUMNS FROM $table_name"; 840 break; 841 842 case 'oracle': 843 $sql = "SELECT column_name 844 FROM user_tab_columns 845 WHERE LOWER(table_name) = '" . strtolower($table_name) . "'"; 846 break; 847 848 case 'sqlite3': 849 $sql = "SELECT sql 850 FROM sqlite_master 851 WHERE type = 'table' 852 AND name = '{$table_name}'"; 853 854 $result = $this->db->sql_query($sql); 855 856 if (!$result) 857 { 858 return false; 859 } 860 861 $row = $this->db->sql_fetchrow($result); 862 $this->db->sql_freeresult($result); 863 864 preg_match('#\((.*)\)#s', $row['sql'], $matches); 865 866 $cols = trim($matches[1]); 867 $col_array = preg_split('/,(?![\s\w]+\))/m', $cols); 868 869 foreach ($col_array as $declaration) 870 { 871 $entities = preg_split('#\s+#', trim($declaration)); 872 if ($entities[0] == 'PRIMARY') 873 { 874 continue; 875 } 876 877 $column = strtolower($entities[0]); 878 $columns[$column] = $column; 879 } 880 881 return $columns; 882 break; 883 } 884 885 $result = $this->db->sql_query($sql); 886 887 while ($row = $this->db->sql_fetchrow($result)) 888 { 889 $column = strtolower(current($row)); 890 $columns[$column] = $column; 891 } 892 $this->db->sql_freeresult($result); 893 894 return $columns; 895 } 896 897 /** 898 * {@inheritDoc} 899 */ 900 function sql_column_exists($table_name, $column_name) 901 { 902 $columns = $this->sql_list_columns($table_name); 903 904 return isset($columns[$column_name]); 905 } 906 907 /** 908 * {@inheritDoc} 909 */ 910 function sql_index_exists($table_name, $index_name) 911 { 912 switch ($this->sql_layer) 913 { 914 case 'mysql_40': 915 case 'mysql_41': 916 $sql = 'SHOW KEYS 917 FROM ' . $table_name; 918 $col = 'Key_name'; 919 break; 920 921 case 'oracle': 922 $sql = "SELECT index_name 923 FROM user_indexes 924 WHERE table_name = '" . strtoupper($table_name) . "' 925 AND generated = 'N' 926 AND uniqueness = 'NONUNIQUE'"; 927 $col = 'index_name'; 928 break; 929 930 case 'sqlite3': 931 $sql = "PRAGMA index_list('" . $table_name . "');"; 932 $col = 'name'; 933 break; 934 } 935 936 $result = $this->db->sql_query($sql); 937 while ($row = $this->db->sql_fetchrow($result)) 938 { 939 if (($this->sql_layer == 'mysql_40' || $this->sql_layer == 'mysql_41') && !$row['Non_unique']) 940 { 941 continue; 942 } 943 944 switch ($this->sql_layer) 945 { 946 // These DBMS prefix index name with the table name 947 case 'oracle': 948 case 'sqlite3': 949 $new_index_name = $this->check_index_name_length($table_name, $table_name . '_' . $index_name, false); 950 break; 951 default: 952 $new_index_name = $this->check_index_name_length($table_name, $index_name, false); 953 break; 954 } 955 956 if (strtolower($row[$col]) == strtolower($new_index_name)) 957 { 958 $this->db->sql_freeresult($result); 959 return true; 960 } 961 } 962 $this->db->sql_freeresult($result); 963 964 return false; 965 } 966 967 /** 968 * {@inheritDoc} 969 */ 970 function sql_unique_index_exists($table_name, $index_name) 971 { 972 switch ($this->sql_layer) 973 { 974 case 'mysql_40': 975 case 'mysql_41': 976 $sql = 'SHOW KEYS 977 FROM ' . $table_name; 978 $col = 'Key_name'; 979 break; 980 981 case 'oracle': 982 $sql = "SELECT index_name, table_owner 983 FROM user_indexes 984 WHERE table_name = '" . strtoupper($table_name) . "' 985 AND generated = 'N' 986 AND uniqueness = 'UNIQUE'"; 987 $col = 'index_name'; 988 break; 989 990 case 'sqlite3': 991 $sql = "PRAGMA index_list('" . $table_name . "');"; 992 $col = 'name'; 993 break; 994 } 995 996 $result = $this->db->sql_query($sql); 997 while ($row = $this->db->sql_fetchrow($result)) 998 { 999 if (($this->sql_layer == 'mysql_40' || $this->sql_layer == 'mysql_41') && ($row['Non_unique'] || $row[$col] == 'PRIMARY')) 1000 { 1001 continue; 1002 } 1003 1004 if ($this->sql_layer == 'sqlite3' && !$row['unique']) 1005 { 1006 continue; 1007 } 1008 1009 // These DBMS prefix index name with the table name 1010 switch ($this->sql_layer) 1011 { 1012 case 'oracle': 1013 // Two cases here... prefixed with U_[table_owner] and not prefixed with table_name 1014 if (strpos($row[$col], 'U_') === 0) 1015 { 1016 $row[$col] = substr($row[$col], strlen('U_' . $row['table_owner']) + 1); 1017 } 1018 else if (strpos($row[$col], strtoupper($table_name)) === 0) 1019 { 1020 $row[$col] = substr($row[$col], strlen($table_name) + 1); 1021 } 1022 break; 1023 1024 case 'sqlite3': 1025 $row[$col] = substr($row[$col], strlen($table_name) + 1); 1026 break; 1027 } 1028 1029 if (strtolower($row[$col]) == strtolower($index_name)) 1030 { 1031 $this->db->sql_freeresult($result); 1032 return true; 1033 } 1034 } 1035 $this->db->sql_freeresult($result); 1036 1037 return false; 1038 } 1039 1040 /** 1041 * Private method for performing sql statements (either execute them or return them) 1042 * @access private 1043 */ 1044 function _sql_run_sql($statements) 1045 { 1046 if ($this->return_statements) 1047 { 1048 return $statements; 1049 } 1050 1051 // We could add error handling here... 1052 foreach ($statements as $sql) 1053 { 1054 if ($sql === 'begin') 1055 { 1056 $this->db->sql_transaction('begin'); 1057 } 1058 else if ($sql === 'commit') 1059 { 1060 $this->db->sql_transaction('commit'); 1061 } 1062 else 1063 { 1064 $this->db->sql_query($sql); 1065 } 1066 } 1067 1068 return true; 1069 } 1070 1071 /** 1072 * Function to prepare some column information for better usage 1073 * @access private 1074 */ 1075 function sql_prepare_column_data($table_name, $column_name, $column_data) 1076 { 1077 if (strlen($column_name) > 30) 1078 { 1079 trigger_error("Column name '$column_name' on table '$table_name' is too long. The maximum is 30 characters.", E_USER_ERROR); 1080 } 1081 1082 // Get type 1083 list($column_type) = $this->get_column_type($column_data[0]); 1084 1085 // Adjust default value if db-dependent specified 1086 if (is_array($column_data[1])) 1087 { 1088 $column_data[1] = (isset($column_data[1][$this->sql_layer])) ? $column_data[1][$this->sql_layer] : $column_data[1]['default']; 1089 } 1090 1091 $sql = ''; 1092 1093 $return_array = array(); 1094 1095 switch ($this->sql_layer) 1096 { 1097 case 'mysql_40': 1098 case 'mysql_41': 1099 $sql .= " {$column_type} "; 1100 1101 // For hexadecimal values do not use single quotes 1102 if (!is_null($column_data[1]) && substr($column_type, -4) !== 'text' && substr($column_type, -4) !== 'blob') 1103 { 1104 $sql .= (strpos($column_data[1], '0x') === 0) ? "DEFAULT {$column_data[1]} " : "DEFAULT '{$column_data[1]}' "; 1105 } 1106 1107 if (!is_null($column_data[1]) || (isset($column_data[2]) && $column_data[2] == 'auto_increment')) 1108 { 1109 $sql .= 'NOT NULL'; 1110 } 1111 else 1112 { 1113 $sql .= 'NULL'; 1114 } 1115 1116 if (isset($column_data[2])) 1117 { 1118 if ($column_data[2] == 'auto_increment') 1119 { 1120 $sql .= ' auto_increment'; 1121 } 1122 else if ($this->sql_layer === 'mysql_41' && $column_data[2] == 'true_sort') 1123 { 1124 $sql .= ' COLLATE utf8_unicode_ci'; 1125 } 1126 } 1127 1128 if (isset($column_data['after'])) 1129 { 1130 $return_array['after'] = $column_data['after']; 1131 } 1132 1133 break; 1134 1135 case 'oracle': 1136 $sql .= " {$column_type} "; 1137 $sql .= (!is_null($column_data[1])) ? "DEFAULT '{$column_data[1]}' " : ''; 1138 1139 // In Oracle empty strings ('') are treated as NULL. 1140 // Therefore in oracle we allow NULL's for all DEFAULT '' entries 1141 // Oracle does not like setting NOT NULL on a column that is already NOT NULL (this happens only on number fields) 1142 if (!preg_match('/number/i', $column_type)) 1143 { 1144 $sql .= ($column_data[1] === '' || $column_data[1] === null) ? '' : 'NOT NULL'; 1145 } 1146 1147 $return_array['auto_increment'] = false; 1148 if (isset($column_data[2]) && $column_data[2] == 'auto_increment') 1149 { 1150 $return_array['auto_increment'] = true; 1151 } 1152 1153 break; 1154 1155 case 'sqlite3': 1156 $return_array['primary_key_set'] = false; 1157 if (isset($column_data[2]) && $column_data[2] == 'auto_increment') 1158 { 1159 $sql .= ' INTEGER PRIMARY KEY AUTOINCREMENT'; 1160 $return_array['primary_key_set'] = true; 1161 } 1162 else 1163 { 1164 $sql .= ' ' . $column_type; 1165 } 1166 1167 if (!is_null($column_data[1])) 1168 { 1169 $sql .= ' NOT NULL '; 1170 $sql .= "DEFAULT '{$column_data[1]}'"; 1171 } 1172 1173 break; 1174 } 1175 1176 $return_array['column_type_sql'] = $sql; 1177 1178 return $return_array; 1179 } 1180 1181 /** 1182 * Get the column's database type from the type map 1183 * 1184 * @param string $column_map_type 1185 * @return array column type for this database 1186 * and map type without length 1187 */ 1188 function get_column_type($column_map_type) 1189 { 1190 $column_type = ''; 1191 if (strpos($column_map_type, ':') !== false) 1192 { 1193 list($orig_column_type, $column_length) = explode(':', $column_map_type); 1194 if (!is_array($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':'])) 1195 { 1196 $column_type = sprintf($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':'], $column_length); 1197 } 1198 else 1199 { 1200 if (isset($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['rule'])) 1201 { 1202 switch ($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['rule'][0]) 1203 { 1204 case 'div': 1205 $column_length /= $this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['rule'][1]; 1206 $column_length = ceil($column_length); 1207 $column_type = sprintf($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':'][0], $column_length); 1208 break; 1209 } 1210 } 1211 1212 if (isset($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['limit'])) 1213 { 1214 switch ($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['limit'][0]) 1215 { 1216 case 'mult': 1217 $column_length *= $this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['limit'][1]; 1218 if ($column_length > $this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['limit'][2]) 1219 { 1220 $column_type = $this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['limit'][3]; 1221 } 1222 else 1223 { 1224 $column_type = sprintf($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':'][0], $column_length); 1225 } 1226 break; 1227 } 1228 } 1229 } 1230 $orig_column_type .= ':'; 1231 } 1232 else 1233 { 1234 $orig_column_type = $column_map_type; 1235 $column_type = $this->dbms_type_map[$this->sql_layer][$column_map_type]; 1236 } 1237 1238 return array($column_type, $orig_column_type); 1239 } 1240 1241 /** 1242 * {@inheritDoc} 1243 */ 1244 function sql_column_add($table_name, $column_name, $column_data, $inline = false) 1245 { 1246 $column_data = $this->sql_prepare_column_data($table_name, $column_name, $column_data); 1247 $statements = array(); 1248 1249 switch ($this->sql_layer) 1250 { 1251 case 'mysql_40': 1252 case 'mysql_41': 1253 $after = (!empty($column_data['after'])) ? ' AFTER ' . $column_data['after'] : ''; 1254 $statements[] = 'ALTER TABLE `' . $table_name . '` ADD COLUMN `' . $column_name . '` ' . $column_data['column_type_sql'] . $after; 1255 break; 1256 1257 case 'oracle': 1258 // Does not support AFTER, only through temporary table 1259 $statements[] = 'ALTER TABLE ' . $table_name . ' ADD ' . $column_name . ' ' . $column_data['column_type_sql']; 1260 break; 1261 1262 case 'sqlite3': 1263 if ($inline && $this->return_statements) 1264 { 1265 return $column_name . ' ' . $column_data['column_type_sql']; 1266 } 1267 1268 $statements[] = 'ALTER TABLE ' . $table_name . ' ADD ' . $column_name . ' ' . $column_data['column_type_sql']; 1269 break; 1270 } 1271 1272 return $this->_sql_run_sql($statements); 1273 } 1274 1275 /** 1276 * {@inheritDoc} 1277 */ 1278 function sql_column_remove($table_name, $column_name, $inline = false) 1279 { 1280 $statements = array(); 1281 1282 switch ($this->sql_layer) 1283 { 1284 case 'mysql_40': 1285 case 'mysql_41': 1286 $statements[] = 'ALTER TABLE `' . $table_name . '` DROP COLUMN `' . $column_name . '`'; 1287 break; 1288 1289 case 'oracle': 1290 $statements[] = 'ALTER TABLE ' . $table_name . ' DROP COLUMN ' . $column_name; 1291 break; 1292 1293 case 'sqlite3': 1294 1295 if ($inline && $this->return_statements) 1296 { 1297 return $column_name; 1298 } 1299 1300 $recreate_queries = $this->sqlite_get_recreate_table_queries($table_name, $column_name); 1301 if (empty($recreate_queries)) 1302 { 1303 break; 1304 } 1305 1306 $statements[] = 'begin'; 1307 1308 $sql_create_table = array_shift($recreate_queries); 1309 1310 // Create a backup table and populate it, destroy the existing one 1311 $statements[] = preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $sql_create_table); 1312 $statements[] = 'INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name; 1313 $statements[] = 'DROP TABLE ' . $table_name; 1314 1315 preg_match('#\((.*)\)#s', $sql_create_table, $matches); 1316 1317 $new_table_cols = trim($matches[1]); 1318 $old_table_cols = preg_split('/,(?![\s\w]+\))/m', $new_table_cols); 1319 $column_list = array(); 1320 1321 foreach ($old_table_cols as $declaration) 1322 { 1323 $entities = preg_split('#\s+#', trim($declaration)); 1324 if ($entities[0] == 'PRIMARY' || $entities[0] === $column_name) 1325 { 1326 continue; 1327 } 1328 $column_list[] = $entities[0]; 1329 } 1330 1331 $columns = implode(',', $column_list); 1332 1333 $new_table_cols = trim(preg_replace('/' . $column_name . '\b[^,]+(?:,|$)/m', '', $new_table_cols)); 1334 if (substr($new_table_cols, -1) === ',') 1335 { 1336 // Remove the comma from the last entry again 1337 $new_table_cols = substr($new_table_cols, 0, -1); 1338 } 1339 1340 // create a new table and fill it up. destroy the temp one 1341 $statements[] = 'CREATE TABLE ' . $table_name . ' (' . $new_table_cols . ');'; 1342 $statements = array_merge($statements, $recreate_queries); 1343 1344 $statements[] = 'INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;'; 1345 $statements[] = 'DROP TABLE ' . $table_name . '_temp'; 1346 1347 $statements[] = 'commit'; 1348 break; 1349 } 1350 1351 return $this->_sql_run_sql($statements); 1352 } 1353 1354 /** 1355 * {@inheritDoc} 1356 */ 1357 function sql_index_drop($table_name, $index_name) 1358 { 1359 $statements = array(); 1360 1361 switch ($this->sql_layer) 1362 { 1363 case 'mysql_40': 1364 case 'mysql_41': 1365 $index_name = $this->check_index_name_length($table_name, $index_name, false); 1366 $statements[] = 'DROP INDEX ' . $index_name . ' ON ' . $table_name; 1367 break; 1368 1369 case 'oracle': 1370 case 'sqlite3': 1371 $index_name = $this->check_index_name_length($table_name, $table_name . '_' . $index_name, false); 1372 $statements[] = 'DROP INDEX ' . $index_name; 1373 break; 1374 } 1375 1376 return $this->_sql_run_sql($statements); 1377 } 1378 1379 /** 1380 * {@inheritDoc} 1381 */ 1382 function sql_table_drop($table_name) 1383 { 1384 $statements = array(); 1385 1386 if (!$this->sql_table_exists($table_name)) 1387 { 1388 return $this->_sql_run_sql($statements); 1389 } 1390 1391 // the most basic operation, get rid of the table 1392 $statements[] = 'DROP TABLE ' . $table_name; 1393 1394 switch ($this->sql_layer) 1395 { 1396 case 'oracle': 1397 $sql = 'SELECT A.REFERENCED_NAME 1398 FROM USER_DEPENDENCIES A, USER_TRIGGERS B 1399 WHERE A.REFERENCED_TYPE = \'SEQUENCE\' 1400 AND A.NAME = B.TRIGGER_NAME 1401 AND B.TABLE_NAME = \'' . strtoupper($table_name) . "'"; 1402 $result = $this->db->sql_query($sql); 1403 1404 // any sequences ref'd to this table's triggers? 1405 while ($row = $this->db->sql_fetchrow($result)) 1406 { 1407 $statements[] = "DROP SEQUENCE {$row['referenced_name']}"; 1408 } 1409 $this->db->sql_freeresult($result); 1410 break; 1411 } 1412 1413 return $this->_sql_run_sql($statements); 1414 } 1415 1416 /** 1417 * {@inheritDoc} 1418 */ 1419 function sql_create_primary_key($table_name, $column, $inline = false) 1420 { 1421 $statements = array(); 1422 1423 switch ($this->sql_layer) 1424 { 1425 case 'mysql_40': 1426 case 'mysql_41': 1427 $statements[] = 'ALTER TABLE ' . $table_name . ' ADD PRIMARY KEY (' . implode(', ', $column) . ')'; 1428 break; 1429 1430 case 'oracle': 1431 $statements[] = 'ALTER TABLE ' . $table_name . ' add CONSTRAINT pk_' . $table_name . ' PRIMARY KEY (' . implode(', ', $column) . ')'; 1432 break; 1433 1434 case 'sqlite3': 1435 1436 if ($inline && $this->return_statements) 1437 { 1438 return $column; 1439 } 1440 1441 $recreate_queries = $this->sqlite_get_recreate_table_queries($table_name); 1442 if (empty($recreate_queries)) 1443 { 1444 break; 1445 } 1446 1447 $statements[] = 'begin'; 1448 1449 $sql_create_table = array_shift($recreate_queries); 1450 1451 // Create a backup table and populate it, destroy the existing one 1452 $statements[] = preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $sql_create_table); 1453 $statements[] = 'INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name; 1454 $statements[] = 'DROP TABLE ' . $table_name; 1455 1456 preg_match('#\((.*)\)#s', $sql_create_table, $matches); 1457 1458 $new_table_cols = trim($matches[1]); 1459 $old_table_cols = preg_split('/,(?![\s\w]+\))/m', $new_table_cols); 1460 $column_list = array(); 1461 1462 foreach ($old_table_cols as $declaration) 1463 { 1464 $entities = preg_split('#\s+#', trim($declaration)); 1465 if ($entities[0] == 'PRIMARY') 1466 { 1467 continue; 1468 } 1469 $column_list[] = $entities[0]; 1470 } 1471 1472 $columns = implode(',', $column_list); 1473 1474 // create a new table and fill it up. destroy the temp one 1475 $statements[] = 'CREATE TABLE ' . $table_name . ' (' . $new_table_cols . ', PRIMARY KEY (' . implode(', ', $column) . '));'; 1476 $statements = array_merge($statements, $recreate_queries); 1477 1478 $statements[] = 'INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;'; 1479 $statements[] = 'DROP TABLE ' . $table_name . '_temp'; 1480 1481 $statements[] = 'commit'; 1482 break; 1483 } 1484 1485 return $this->_sql_run_sql($statements); 1486 } 1487 1488 /** 1489 * {@inheritDoc} 1490 */ 1491 function sql_create_unique_index($table_name, $index_name, $column) 1492 { 1493 $statements = array(); 1494 1495 switch ($this->sql_layer) 1496 { 1497 case 'oracle': 1498 case 'sqlite3': 1499 $index_name = $this->check_index_name_length($table_name, $table_name . '_' . $index_name); 1500 $statements[] = 'CREATE UNIQUE INDEX ' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ')'; 1501 break; 1502 1503 case 'mysql_40': 1504 case 'mysql_41': 1505 $index_name = $this->check_index_name_length($table_name, $index_name); 1506 $statements[] = 'ALTER TABLE ' . $table_name . ' ADD UNIQUE INDEX ' . $index_name . '(' . implode(', ', $column) . ')'; 1507 break; 1508 } 1509 1510 return $this->_sql_run_sql($statements); 1511 } 1512 1513 /** 1514 * {@inheritDoc} 1515 */ 1516 function sql_create_index($table_name, $index_name, $column) 1517 { 1518 $statements = array(); 1519 1520 // remove index length unless MySQL4 1521 if ('mysql_40' != $this->sql_layer) 1522 { 1523 $column = preg_replace('#:.*$#', '', $column); 1524 } 1525 1526 switch ($this->sql_layer) 1527 { 1528 case 'oracle': 1529 case 'sqlite3': 1530 $index_name = $this->check_index_name_length($table_name, $table_name . '_' . $index_name); 1531 $statements[] = 'CREATE INDEX ' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ')'; 1532 break; 1533 1534 case 'mysql_40': 1535 // add index size to definition as required by MySQL4 1536 foreach ($column as $i => $col) 1537 { 1538 if (false !== strpos($col, ':')) 1539 { 1540 list($col, $index_size) = explode(':', $col); 1541 $column[$i] = "$col($index_size)"; 1542 } 1543 } 1544 // no break 1545 case 'mysql_41': 1546 $index_name = $this->check_index_name_length($table_name, $index_name); 1547 $statements[] = 'ALTER TABLE ' . $table_name . ' ADD INDEX ' . $index_name . ' (' . implode(', ', $column) . ')'; 1548 break; 1549 } 1550 1551 return $this->_sql_run_sql($statements); 1552 } 1553 1554 /** 1555 * Check whether the index name is too long 1556 * 1557 * @param string $table_name 1558 * @param string $index_name 1559 * @param bool $throw_error 1560 * @return string The index name, shortened if too long 1561 */ 1562 protected function check_index_name_length($table_name, $index_name, $throw_error = true) 1563 { 1564 $max_index_name_length = $this->get_max_index_name_length(); 1565 if (strlen($index_name) > $max_index_name_length) 1566 { 1567 // Try removing the table prefix if it's at the beginning 1568 $table_prefix = substr(CONFIG_TABLE, 0, -6); // strlen(config) 1569 if (strpos($index_name, $table_prefix) === 0) 1570 { 1571 $index_name = substr($index_name, strlen($table_prefix)); 1572 return $this->check_index_name_length($table_name, $index_name, $throw_error); 1573 } 1574 1575 // Try removing the remaining suffix part of table name then 1576 $table_suffix = substr($table_name, strlen($table_prefix)); 1577 if (strpos($index_name, $table_suffix) === 0) 1578 { 1579 // Remove the suffix and underscore separator between table_name and index_name 1580 $index_name = substr($index_name, strlen($table_suffix) + 1); 1581 return $this->check_index_name_length($table_name, $index_name, $throw_error); 1582 } 1583 1584 if ($throw_error) 1585 { 1586 trigger_error("Index name '$index_name' on table '$table_name' is too long. The maximum is $max_index_name_length characters.", E_USER_ERROR); 1587 } 1588 } 1589 1590 return $index_name; 1591 } 1592 1593 /** 1594 * Get maximum index name length. Might vary depending on db type 1595 * 1596 * @return int Maximum index name length 1597 */ 1598 protected function get_max_index_name_length() 1599 { 1600 return 30; 1601 } 1602 1603 /** 1604 * {@inheritDoc} 1605 */ 1606 function sql_list_index($table_name) 1607 { 1608 $index_array = array(); 1609 1610 switch ($this->sql_layer) 1611 { 1612 case 'mysql_40': 1613 case 'mysql_41': 1614 $sql = 'SHOW KEYS 1615 FROM ' . $table_name; 1616 $col = 'Key_name'; 1617 break; 1618 1619 case 'oracle': 1620 $sql = "SELECT index_name 1621 FROM user_indexes 1622 WHERE table_name = '" . strtoupper($table_name) . "' 1623 AND generated = 'N' 1624 AND uniqueness = 'NONUNIQUE'"; 1625 $col = 'index_name'; 1626 break; 1627 1628 case 'sqlite3': 1629 $sql = "PRAGMA index_info('" . $table_name . "');"; 1630 $col = 'name'; 1631 break; 1632 } 1633 1634 $result = $this->db->sql_query($sql); 1635 while ($row = $this->db->sql_fetchrow($result)) 1636 { 1637 if (($this->sql_layer == 'mysql_40' || $this->sql_layer == 'mysql_41') && !$row['Non_unique']) 1638 { 1639 continue; 1640 } 1641 1642 switch ($this->sql_layer) 1643 { 1644 case 'oracle': 1645 case 'sqlite3': 1646 $row[$col] = substr($row[$col], strlen($table_name) + 1); 1647 break; 1648 } 1649 1650 $index_array[] = $row[$col]; 1651 } 1652 $this->db->sql_freeresult($result); 1653 1654 return array_map('strtolower', $index_array); 1655 } 1656 1657 /** 1658 * Removes table_name from the index_name if it is at the beginning 1659 * 1660 * @param $table_name 1661 * @param $index_name 1662 * @return string 1663 */ 1664 protected function strip_table_name_from_index_name($table_name, $index_name) 1665 { 1666 return (strpos(strtoupper($index_name), strtoupper($table_name)) === 0) ? substr($index_name, strlen($table_name) + 1) : $index_name; 1667 } 1668 1669 /** 1670 * {@inheritDoc} 1671 */ 1672 function sql_column_change($table_name, $column_name, $column_data, $inline = false) 1673 { 1674 $original_column_data = $column_data; 1675 $column_data = $this->sql_prepare_column_data($table_name, $column_name, $column_data); 1676 $statements = array(); 1677 1678 switch ($this->sql_layer) 1679 { 1680 case 'mysql_40': 1681 case 'mysql_41': 1682 $statements[] = 'ALTER TABLE `' . $table_name . '` CHANGE `' . $column_name . '` `' . $column_name . '` ' . $column_data['column_type_sql']; 1683 break; 1684 1685 case 'oracle': 1686 // We need the data here 1687 $old_return_statements = $this->return_statements; 1688 $this->return_statements = true; 1689 1690 // Get list of existing indexes 1691 $indexes = $this->get_existing_indexes($table_name, $column_name); 1692 $unique_indexes = $this->get_existing_indexes($table_name, $column_name, true); 1693 1694 // Drop any indexes 1695 if (!empty($indexes) || !empty($unique_indexes)) 1696 { 1697 $drop_indexes = array_merge(array_keys($indexes), array_keys($unique_indexes)); 1698 foreach ($drop_indexes as $index_name) 1699 { 1700 $result = $this->sql_index_drop($table_name, $this->strip_table_name_from_index_name($table_name, $index_name)); 1701 $statements = array_merge($statements, $result); 1702 } 1703 } 1704 1705 $temp_column_name = 'temp_' . substr(md5($column_name), 0, 25); 1706 // Add a temporary table with the new type 1707 $result = $this->sql_column_add($table_name, $temp_column_name, $original_column_data); 1708 $statements = array_merge($statements, $result); 1709 1710 // Copy the data to the new column 1711 $statements[] = 'UPDATE ' . $table_name . ' SET ' . $temp_column_name . ' = ' . $column_name; 1712 1713 // Drop the original column 1714 $result = $this->sql_column_remove($table_name, $column_name); 1715 $statements = array_merge($statements, $result); 1716 1717 // Recreate the original column with the new type 1718 $result = $this->sql_column_add($table_name, $column_name, $original_column_data); 1719 $statements = array_merge($statements, $result); 1720 1721 if (!empty($indexes)) 1722 { 1723 // Recreate indexes after we changed the column 1724 foreach ($indexes as $index_name => $index_data) 1725 { 1726 $result = $this->sql_create_index($table_name, $this->strip_table_name_from_index_name($table_name, $index_name), $index_data); 1727 $statements = array_merge($statements, $result); 1728 } 1729 } 1730 1731 if (!empty($unique_indexes)) 1732 { 1733 // Recreate unique indexes after we changed the column 1734 foreach ($unique_indexes as $index_name => $index_data) 1735 { 1736 $result = $this->sql_create_unique_index($table_name, $this->strip_table_name_from_index_name($table_name, $index_name), $index_data); 1737 $statements = array_merge($statements, $result); 1738 } 1739 } 1740 1741 // Copy the data to the original column 1742 $statements[] = 'UPDATE ' . $table_name . ' SET ' . $column_name . ' = ' . $temp_column_name; 1743 1744 // Drop the temporary column again 1745 $result = $this->sql_column_remove($table_name, $temp_column_name); 1746 $statements = array_merge($statements, $result); 1747 1748 $this->return_statements = $old_return_statements; 1749 break; 1750 1751 case 'sqlite3': 1752 1753 if ($inline && $this->return_statements) 1754 { 1755 return $column_name . ' ' . $column_data['column_type_sql']; 1756 } 1757 1758 $recreate_queries = $this->sqlite_get_recreate_table_queries($table_name); 1759 if (empty($recreate_queries)) 1760 { 1761 break; 1762 } 1763 1764 $statements[] = 'begin'; 1765 1766 $sql_create_table = array_shift($recreate_queries); 1767 1768 // Create a temp table and populate it, destroy the existing one 1769 $statements[] = preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $sql_create_table); 1770 $statements[] = 'INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name; 1771 $statements[] = 'DROP TABLE ' . $table_name; 1772 1773 preg_match('#\((.*)\)#s', $sql_create_table, $matches); 1774 1775 $new_table_cols = trim($matches[1]); 1776 $old_table_cols = preg_split('/,(?![\s\w]+\))/m', $new_table_cols); 1777 $column_list = array(); 1778 1779 foreach ($old_table_cols as $key => $declaration) 1780 { 1781 $declaration = trim($declaration); 1782 1783 // Check for the beginning of the constraint section and stop 1784 if (preg_match('/[^\(]*\s*PRIMARY KEY\s+\(/', $declaration) || 1785 preg_match('/[^\(]*\s*UNIQUE\s+\(/', $declaration) || 1786 preg_match('/[^\(]*\s*FOREIGN KEY\s+\(/', $declaration) || 1787 preg_match('/[^\(]*\s*CHECK\s+\(/', $declaration)) 1788 { 1789 break; 1790 } 1791 1792 $entities = preg_split('#\s+#', $declaration); 1793 $column_list[] = $entities[0]; 1794 if ($entities[0] == $column_name) 1795 { 1796 $old_table_cols[$key] = $column_name . ' ' . $column_data['column_type_sql']; 1797 } 1798 } 1799 1800 $columns = implode(',', $column_list); 1801 1802 // Create a new table and fill it up. destroy the temp one 1803 $statements[] = 'CREATE TABLE ' . $table_name . ' (' . implode(',', $old_table_cols) . ');'; 1804 $statements = array_merge($statements, $recreate_queries); 1805 1806 $statements[] = 'INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;'; 1807 $statements[] = 'DROP TABLE ' . $table_name . '_temp'; 1808 1809 $statements[] = 'commit'; 1810 1811 break; 1812 } 1813 1814 return $this->_sql_run_sql($statements); 1815 } 1816 1817 /** 1818 * Get a list with existing indexes for the column 1819 * 1820 * @param string $table_name 1821 * @param string $column_name 1822 * @param bool $unique Should we get unique indexes or normal ones 1823 * @return array Array with Index name => columns 1824 */ 1825 public function get_existing_indexes($table_name, $column_name, $unique = false) 1826 { 1827 switch ($this->sql_layer) 1828 { 1829 case 'mysql_40': 1830 case 'mysql_41': 1831 case 'sqlite3': 1832 // Not supported 1833 throw new \Exception('DBMS is not supported'); 1834 break; 1835 } 1836 1837 $sql = ''; 1838 $existing_indexes = array(); 1839 1840 switch ($this->sql_layer) 1841 { 1842 case 'oracle': 1843 $sql = "SELECT ix.index_name AS phpbb_index_name, ix.uniqueness AS is_unique 1844 FROM all_ind_columns ixc, all_indexes ix 1845 WHERE ix.index_name = ixc.index_name 1846 AND ixc.table_name = '" . strtoupper($table_name) . "' 1847 AND ixc.column_name = '" . strtoupper($column_name) . "'"; 1848 break; 1849 } 1850 1851 $result = $this->db->sql_query($sql); 1852 while ($row = $this->db->sql_fetchrow($result)) 1853 { 1854 if (!isset($row['is_unique']) || ($unique && $row['is_unique'] == 'UNIQUE') || (!$unique && $row['is_unique'] == 'NONUNIQUE')) 1855 { 1856 $existing_indexes[$row['phpbb_index_name']] = array(); 1857 } 1858 } 1859 $this->db->sql_freeresult($result); 1860 1861 if (empty($existing_indexes)) 1862 { 1863 return array(); 1864 } 1865 1866 switch ($this->sql_layer) 1867 { 1868 case 'oracle': 1869 $sql = "SELECT index_name AS phpbb_index_name, column_name AS phpbb_column_name 1870 FROM all_ind_columns 1871 WHERE table_name = '" . strtoupper($table_name) . "' 1872 AND " . $this->db->sql_in_set('index_name', array_keys($existing_indexes)); 1873 break; 1874 } 1875 1876 $result = $this->db->sql_query($sql); 1877 while ($row = $this->db->sql_fetchrow($result)) 1878 { 1879 $existing_indexes[$row['phpbb_index_name']][] = $row['phpbb_column_name']; 1880 } 1881 $this->db->sql_freeresult($result); 1882 1883 return $existing_indexes; 1884 } 1885 1886 /** 1887 * Returns the Queries which are required to recreate a table including indexes 1888 * 1889 * @param string $table_name 1890 * @param string $remove_column When we drop a column, we remove the column 1891 * from all indexes. If the index has no other 1892 * column, we drop it completly. 1893 * @return array 1894 */ 1895 protected function sqlite_get_recreate_table_queries($table_name, $remove_column = '') 1896 { 1897 $queries = array(); 1898 1899 $sql = "SELECT sql 1900 FROM sqlite_master 1901 WHERE type = 'table' 1902 AND name = '{$table_name}'"; 1903 $result = $this->db->sql_query($sql); 1904 $sql_create_table = $this->db->sql_fetchfield('sql'); 1905 $this->db->sql_freeresult($result); 1906 1907 if (!$sql_create_table) 1908 { 1909 return array(); 1910 } 1911 $queries[] = $sql_create_table; 1912 1913 $sql = "SELECT sql 1914 FROM sqlite_master 1915 WHERE type = 'index' 1916 AND tbl_name = '{$table_name}'"; 1917 $result = $this->db->sql_query($sql); 1918 while ($sql_create_index = $this->db->sql_fetchfield('sql')) 1919 { 1920 if ($remove_column) 1921 { 1922 $match = array(); 1923 preg_match('#(?:[\w ]+)\((.*)\)#', $sql_create_index, $match); 1924 if (!isset($match[1])) 1925 { 1926 continue; 1927 } 1928 1929 // Find and remove $remove_column from the index 1930 $columns = explode(', ', $match[1]); 1931 $found_column = array_search($remove_column, $columns); 1932 if ($found_column !== false) 1933 { 1934 unset($columns[$found_column]); 1935 1936 // If the column list is not empty add the index to the list 1937 if (!empty($columns)) 1938 { 1939 $queries[] = str_replace($match[1], implode(', ', $columns), $sql_create_index); 1940 } 1941 } 1942 else 1943 { 1944 $queries[] = $sql_create_index; 1945 } 1946 } 1947 else 1948 { 1949 $queries[] = $sql_create_index; 1950 } 1951 } 1952 $this->db->sql_freeresult($result); 1953 1954 return $queries; 1955 } 1956 }
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 |