| [ Index ] |
PHP Cross Reference of phpBB-3.1.12-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; 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 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 * Is the used MS SQL Server a SQL Server 2000? 40 * @var bool 41 */ 42 protected $is_sql_server_2000; 43 44 /** 45 * Get the column types for every database we support 46 * 47 * @return array 48 */ 49 public static function get_dbms_type_map() 50 { 51 return array( 52 'mysql_41' => array( 53 'INT:' => 'int(%d)', 54 'BINT' => 'bigint(20)', 55 'UINT' => 'mediumint(8) UNSIGNED', 56 'UINT:' => 'int(%d) UNSIGNED', 57 'TINT:' => 'tinyint(%d)', 58 'USINT' => 'smallint(4) UNSIGNED', 59 'BOOL' => 'tinyint(1) UNSIGNED', 60 'VCHAR' => 'varchar(255)', 61 'VCHAR:' => 'varchar(%d)', 62 'CHAR:' => 'char(%d)', 63 'XSTEXT' => 'text', 64 'XSTEXT_UNI'=> 'varchar(100)', 65 'STEXT' => 'text', 66 'STEXT_UNI' => 'varchar(255)', 67 'TEXT' => 'text', 68 'TEXT_UNI' => 'text', 69 'MTEXT' => 'mediumtext', 70 'MTEXT_UNI' => 'mediumtext', 71 'TIMESTAMP' => 'int(11) UNSIGNED', 72 'DECIMAL' => 'decimal(5,2)', 73 'DECIMAL:' => 'decimal(%d,2)', 74 'PDECIMAL' => 'decimal(6,3)', 75 'PDECIMAL:' => 'decimal(%d,3)', 76 'VCHAR_UNI' => 'varchar(255)', 77 'VCHAR_UNI:'=> 'varchar(%d)', 78 'VCHAR_CI' => 'varchar(255)', 79 'VARBINARY' => 'varbinary(255)', 80 ), 81 82 'mysql_40' => array( 83 'INT:' => 'int(%d)', 84 'BINT' => 'bigint(20)', 85 'UINT' => 'mediumint(8) UNSIGNED', 86 'UINT:' => 'int(%d) UNSIGNED', 87 'TINT:' => 'tinyint(%d)', 88 'USINT' => 'smallint(4) UNSIGNED', 89 'BOOL' => 'tinyint(1) UNSIGNED', 90 'VCHAR' => 'varbinary(255)', 91 'VCHAR:' => 'varbinary(%d)', 92 'CHAR:' => 'binary(%d)', 93 'XSTEXT' => 'blob', 94 'XSTEXT_UNI'=> 'blob', 95 'STEXT' => 'blob', 96 'STEXT_UNI' => 'blob', 97 'TEXT' => 'blob', 98 'TEXT_UNI' => 'blob', 99 'MTEXT' => 'mediumblob', 100 'MTEXT_UNI' => 'mediumblob', 101 'TIMESTAMP' => 'int(11) UNSIGNED', 102 'DECIMAL' => 'decimal(5,2)', 103 'DECIMAL:' => 'decimal(%d,2)', 104 'PDECIMAL' => 'decimal(6,3)', 105 'PDECIMAL:' => 'decimal(%d,3)', 106 'VCHAR_UNI' => 'blob', 107 'VCHAR_UNI:'=> array('varbinary(%d)', 'limit' => array('mult', 3, 255, 'blob')), 108 'VCHAR_CI' => 'blob', 109 'VARBINARY' => 'varbinary(255)', 110 ), 111 112 'mssql' => array( 113 'INT:' => '[int]', 114 'BINT' => '[float]', 115 'UINT' => '[int]', 116 'UINT:' => '[int]', 117 'TINT:' => '[int]', 118 'USINT' => '[int]', 119 'BOOL' => '[int]', 120 'VCHAR' => '[varchar] (255)', 121 'VCHAR:' => '[varchar] (%d)', 122 'CHAR:' => '[char] (%d)', 123 'XSTEXT' => '[varchar] (1000)', 124 'STEXT' => '[varchar] (3000)', 125 'TEXT' => '[varchar] (8000)', 126 'MTEXT' => '[text]', 127 'XSTEXT_UNI'=> '[varchar] (100)', 128 'STEXT_UNI' => '[varchar] (255)', 129 'TEXT_UNI' => '[varchar] (4000)', 130 'MTEXT_UNI' => '[text]', 131 'TIMESTAMP' => '[int]', 132 'DECIMAL' => '[float]', 133 'DECIMAL:' => '[float]', 134 'PDECIMAL' => '[float]', 135 'PDECIMAL:' => '[float]', 136 'VCHAR_UNI' => '[varchar] (255)', 137 'VCHAR_UNI:'=> '[varchar] (%d)', 138 'VCHAR_CI' => '[varchar] (255)', 139 'VARBINARY' => '[varchar] (255)', 140 ), 141 142 'mssqlnative' => array( 143 'INT:' => '[int]', 144 'BINT' => '[float]', 145 'UINT' => '[int]', 146 'UINT:' => '[int]', 147 'TINT:' => '[int]', 148 'USINT' => '[int]', 149 'BOOL' => '[int]', 150 'VCHAR' => '[varchar] (255)', 151 'VCHAR:' => '[varchar] (%d)', 152 'CHAR:' => '[char] (%d)', 153 'XSTEXT' => '[varchar] (1000)', 154 'STEXT' => '[varchar] (3000)', 155 'TEXT' => '[varchar] (8000)', 156 'MTEXT' => '[text]', 157 'XSTEXT_UNI'=> '[varchar] (100)', 158 'STEXT_UNI' => '[varchar] (255)', 159 'TEXT_UNI' => '[varchar] (4000)', 160 'MTEXT_UNI' => '[text]', 161 'TIMESTAMP' => '[int]', 162 'DECIMAL' => '[float]', 163 'DECIMAL:' => '[float]', 164 'PDECIMAL' => '[float]', 165 'PDECIMAL:' => '[float]', 166 'VCHAR_UNI' => '[varchar] (255)', 167 'VCHAR_UNI:'=> '[varchar] (%d)', 168 'VCHAR_CI' => '[varchar] (255)', 169 'VARBINARY' => '[varchar] (255)', 170 ), 171 172 'oracle' => array( 173 'INT:' => 'number(%d)', 174 'BINT' => 'number(20)', 175 'UINT' => 'number(8)', 176 'UINT:' => 'number(%d)', 177 'TINT:' => 'number(%d)', 178 'USINT' => 'number(4)', 179 'BOOL' => 'number(1)', 180 'VCHAR' => 'varchar2(255)', 181 'VCHAR:' => 'varchar2(%d)', 182 'CHAR:' => 'char(%d)', 183 'XSTEXT' => 'varchar2(1000)', 184 'STEXT' => 'varchar2(3000)', 185 'TEXT' => 'clob', 186 'MTEXT' => 'clob', 187 'XSTEXT_UNI'=> 'varchar2(300)', 188 'STEXT_UNI' => 'varchar2(765)', 189 'TEXT_UNI' => 'clob', 190 'MTEXT_UNI' => 'clob', 191 'TIMESTAMP' => 'number(11)', 192 'DECIMAL' => 'number(5, 2)', 193 'DECIMAL:' => 'number(%d, 2)', 194 'PDECIMAL' => 'number(6, 3)', 195 'PDECIMAL:' => 'number(%d, 3)', 196 'VCHAR_UNI' => 'varchar2(765)', 197 'VCHAR_UNI:'=> array('varchar2(%d)', 'limit' => array('mult', 3, 765, 'clob')), 198 'VCHAR_CI' => 'varchar2(255)', 199 'VARBINARY' => 'raw(255)', 200 ), 201 202 'sqlite' => array( 203 'INT:' => 'int(%d)', 204 'BINT' => 'bigint(20)', 205 'UINT' => 'INTEGER UNSIGNED', //'mediumint(8) UNSIGNED', 206 'UINT:' => 'INTEGER UNSIGNED', // 'int(%d) UNSIGNED', 207 'TINT:' => 'tinyint(%d)', 208 'USINT' => 'INTEGER UNSIGNED', //'mediumint(4) UNSIGNED', 209 'BOOL' => 'INTEGER UNSIGNED', //'tinyint(1) UNSIGNED', 210 'VCHAR' => 'varchar(255)', 211 'VCHAR:' => 'varchar(%d)', 212 'CHAR:' => 'char(%d)', 213 'XSTEXT' => 'text(65535)', 214 'STEXT' => 'text(65535)', 215 'TEXT' => 'text(65535)', 216 'MTEXT' => 'mediumtext(16777215)', 217 'XSTEXT_UNI'=> 'text(65535)', 218 'STEXT_UNI' => 'text(65535)', 219 'TEXT_UNI' => 'text(65535)', 220 'MTEXT_UNI' => 'mediumtext(16777215)', 221 'TIMESTAMP' => 'INTEGER UNSIGNED', //'int(11) UNSIGNED', 222 'DECIMAL' => 'decimal(5,2)', 223 'DECIMAL:' => 'decimal(%d,2)', 224 'PDECIMAL' => 'decimal(6,3)', 225 'PDECIMAL:' => 'decimal(%d,3)', 226 'VCHAR_UNI' => 'varchar(255)', 227 'VCHAR_UNI:'=> 'varchar(%d)', 228 'VCHAR_CI' => 'varchar(255)', 229 'VARBINARY' => 'blob', 230 ), 231 232 'sqlite3' => array( 233 'INT:' => 'INT(%d)', 234 'BINT' => 'BIGINT(20)', 235 'UINT' => 'INTEGER UNSIGNED', 236 'UINT:' => 'INTEGER UNSIGNED', 237 'TINT:' => 'TINYINT(%d)', 238 'USINT' => 'INTEGER UNSIGNED', 239 'BOOL' => 'INTEGER UNSIGNED', 240 'VCHAR' => 'VARCHAR(255)', 241 'VCHAR:' => 'VARCHAR(%d)', 242 'CHAR:' => 'CHAR(%d)', 243 'XSTEXT' => 'TEXT(65535)', 244 'STEXT' => 'TEXT(65535)', 245 'TEXT' => 'TEXT(65535)', 246 'MTEXT' => 'MEDIUMTEXT(16777215)', 247 'XSTEXT_UNI'=> 'TEXT(65535)', 248 'STEXT_UNI' => 'TEXT(65535)', 249 'TEXT_UNI' => 'TEXT(65535)', 250 'MTEXT_UNI' => 'MEDIUMTEXT(16777215)', 251 'TIMESTAMP' => 'INTEGER UNSIGNED', //'int(11) UNSIGNED', 252 'DECIMAL' => 'DECIMAL(5,2)', 253 'DECIMAL:' => 'DECIMAL(%d,2)', 254 'PDECIMAL' => 'DECIMAL(6,3)', 255 'PDECIMAL:' => 'DECIMAL(%d,3)', 256 'VCHAR_UNI' => 'VARCHAR(255)', 257 'VCHAR_UNI:'=> 'VARCHAR(%d)', 258 'VCHAR_CI' => 'VARCHAR(255)', 259 'VARBINARY' => 'BLOB', 260 ), 261 262 'postgres' => array( 263 'INT:' => 'INT4', 264 'BINT' => 'INT8', 265 'UINT' => 'INT4', // unsigned 266 'UINT:' => 'INT4', // unsigned 267 'USINT' => 'INT2', // unsigned 268 'BOOL' => 'INT2', // unsigned 269 'TINT:' => 'INT2', 270 'VCHAR' => 'varchar(255)', 271 'VCHAR:' => 'varchar(%d)', 272 'CHAR:' => 'char(%d)', 273 'XSTEXT' => 'varchar(1000)', 274 'STEXT' => 'varchar(3000)', 275 'TEXT' => 'varchar(8000)', 276 'MTEXT' => 'TEXT', 277 'XSTEXT_UNI'=> 'varchar(100)', 278 'STEXT_UNI' => 'varchar(255)', 279 'TEXT_UNI' => 'varchar(4000)', 280 'MTEXT_UNI' => 'TEXT', 281 'TIMESTAMP' => 'INT4', // unsigned 282 'DECIMAL' => 'decimal(5,2)', 283 'DECIMAL:' => 'decimal(%d,2)', 284 'PDECIMAL' => 'decimal(6,3)', 285 'PDECIMAL:' => 'decimal(%d,3)', 286 'VCHAR_UNI' => 'varchar(255)', 287 'VCHAR_UNI:'=> 'varchar(%d)', 288 'VCHAR_CI' => 'varchar_ci', 289 'VARBINARY' => 'bytea', 290 ), 291 ); 292 } 293 294 /** 295 * A list of types being unsigned for better reference in some db's 296 * @var array 297 */ 298 var $unsigned_types = array('UINT', 'UINT:', 'USINT', 'BOOL', 'TIMESTAMP'); 299 300 /** 301 * A list of supported DBMS. We change this class to support more DBMS, the DBMS itself only need to follow some rules. 302 * @var array 303 */ 304 var $supported_dbms = array('mssql', 'mssqlnative', 'mysql_40', 'mysql_41', 'oracle', 'postgres', 'sqlite', 'sqlite3'); 305 306 /** 307 * This is set to true if user only wants to return the 'to-be-executed' SQL statement(s) (as an array). 308 * This mode has no effect on some methods (inserting of data for example). This is expressed within the methods command. 309 */ 310 var $return_statements = false; 311 312 /** 313 * Constructor. Set DB Object and set {@link $return_statements return_statements}. 314 * 315 * @param \phpbb\db\driver\driver_interface $db Database connection 316 * @param bool $return_statements True if only statements should be returned and no SQL being executed 317 */ 318 public function __construct(\phpbb\db\driver\driver_interface $db, $return_statements = false) 319 { 320 $this->db = $db; 321 $this->return_statements = $return_statements; 322 323 $this->dbms_type_map = self::get_dbms_type_map(); 324 325 // Determine mapping database type 326 switch ($this->db->get_sql_layer()) 327 { 328 case 'mysql': 329 $this->sql_layer = 'mysql_40'; 330 break; 331 332 case 'mysql4': 333 if (version_compare($this->db->sql_server_info(true), '4.1.3', '>=')) 334 { 335 $this->sql_layer = 'mysql_41'; 336 } 337 else 338 { 339 $this->sql_layer = 'mysql_40'; 340 } 341 break; 342 343 case 'mysqli': 344 $this->sql_layer = 'mysql_41'; 345 break; 346 347 case 'mssql': 348 case 'mssql_odbc': 349 $this->sql_layer = 'mssql'; 350 break; 351 352 case 'mssqlnative': 353 $this->sql_layer = 'mssqlnative'; 354 break; 355 356 default: 357 $this->sql_layer = $this->db->get_sql_layer(); 358 break; 359 } 360 } 361 362 /** 363 * Setter for {@link $return_statements return_statements}. 364 * 365 * @param bool $return_statements True if SQL should not be executed but returned as strings 366 * @return null 367 */ 368 public function set_return_statements($return_statements) 369 { 370 $this->return_statements = $return_statements; 371 } 372 373 /** 374 * Gets a list of tables in the database. 375 * 376 * @return array Array of table names (all lower case) 377 */ 378 function sql_list_tables() 379 { 380 switch ($this->db->get_sql_layer()) 381 { 382 case 'mysql': 383 case 'mysql4': 384 case 'mysqli': 385 $sql = 'SHOW TABLES'; 386 break; 387 388 case 'sqlite': 389 $sql = 'SELECT name 390 FROM sqlite_master 391 WHERE type = "table"'; 392 break; 393 394 case 'sqlite3': 395 $sql = 'SELECT name 396 FROM sqlite_master 397 WHERE type = "table" 398 AND name <> "sqlite_sequence"'; 399 break; 400 401 case 'mssql': 402 case 'mssql_odbc': 403 case 'mssqlnative': 404 $sql = "SELECT name 405 FROM sysobjects 406 WHERE type='U'"; 407 break; 408 409 case 'postgres': 410 $sql = 'SELECT relname 411 FROM pg_stat_user_tables'; 412 break; 413 414 case 'oracle': 415 $sql = 'SELECT table_name 416 FROM USER_TABLES'; 417 break; 418 } 419 420 $result = $this->db->sql_query($sql); 421 422 $tables = array(); 423 while ($row = $this->db->sql_fetchrow($result)) 424 { 425 $name = current($row); 426 $tables[$name] = $name; 427 } 428 $this->db->sql_freeresult($result); 429 430 return $tables; 431 } 432 433 /** 434 * Check if table exists 435 * 436 * 437 * @param string $table_name The table name to check for 438 * @return bool true if table exists, else false 439 */ 440 function sql_table_exists($table_name) 441 { 442 $this->db->sql_return_on_error(true); 443 $result = $this->db->sql_query_limit('SELECT * FROM ' . $table_name, 1); 444 $this->db->sql_return_on_error(false); 445 446 if ($result) 447 { 448 $this->db->sql_freeresult($result); 449 return true; 450 } 451 452 return false; 453 } 454 455 /** 456 * Create SQL Table 457 * 458 * @param string $table_name The table name to create 459 * @param array $table_data Array containing table data. 460 * @return array Statements if $return_statements is true. 461 */ 462 function sql_create_table($table_name, $table_data) 463 { 464 // holds the DDL for a column 465 $columns = $statements = array(); 466 467 if ($this->sql_table_exists($table_name)) 468 { 469 return $this->_sql_run_sql($statements); 470 } 471 472 // Begin transaction 473 $statements[] = 'begin'; 474 475 // Determine if we have created a PRIMARY KEY in the earliest 476 $primary_key_gen = false; 477 478 // Determine if the table requires a sequence 479 $create_sequence = false; 480 481 // Begin table sql statement 482 switch ($this->sql_layer) 483 { 484 case 'mssql': 485 case 'mssqlnative': 486 $table_sql = 'CREATE TABLE [' . $table_name . '] (' . "\n"; 487 break; 488 489 default: 490 $table_sql = 'CREATE TABLE ' . $table_name . ' (' . "\n"; 491 break; 492 } 493 494 if ($this->sql_layer == 'mssql' || $this->sql_layer == 'mssqlnative') 495 { 496 if (!isset($table_data['PRIMARY_KEY'])) 497 { 498 $table_data['COLUMNS']['mssqlindex'] = array('UINT', null, 'auto_increment'); 499 $table_data['PRIMARY_KEY'] = 'mssqlindex'; 500 } 501 } 502 503 // Iterate through the columns to create a table 504 foreach ($table_data['COLUMNS'] as $column_name => $column_data) 505 { 506 // here lies an array, filled with information compiled on the column's data 507 $prepared_column = $this->sql_prepare_column_data($table_name, $column_name, $column_data); 508 509 if (isset($prepared_column['auto_increment']) && $prepared_column['auto_increment'] && strlen($column_name) > 26) // "$column_name}_gen" 510 { 511 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); 512 } 513 514 // here we add the definition of the new column to the list of columns 515 switch ($this->sql_layer) 516 { 517 case 'mssql': 518 case 'mssqlnative': 519 $columns[] = "\t [{$column_name}] " . $prepared_column['column_type_sql_default']; 520 break; 521 522 default: 523 $columns[] = "\t {$column_name} " . $prepared_column['column_type_sql']; 524 break; 525 } 526 527 // see if we have found a primary key set due to a column definition if we have found it, we can stop looking 528 if (!$primary_key_gen) 529 { 530 $primary_key_gen = isset($prepared_column['primary_key_set']) && $prepared_column['primary_key_set']; 531 } 532 533 // create sequence DDL based off of the existance of auto incrementing columns 534 if (!$create_sequence && isset($prepared_column['auto_increment']) && $prepared_column['auto_increment']) 535 { 536 $create_sequence = $column_name; 537 } 538 } 539 540 // this makes up all the columns in the create table statement 541 $table_sql .= implode(",\n", $columns); 542 543 // Close the table for two DBMS and add to the statements 544 switch ($this->sql_layer) 545 { 546 case 'mssql': 547 case 'mssqlnative': 548 $table_sql .= "\n);"; 549 $statements[] = $table_sql; 550 break; 551 } 552 553 // we have yet to create a primary key for this table, 554 // this means that we can add the one we really wanted instead 555 if (!$primary_key_gen) 556 { 557 // Write primary key 558 if (isset($table_data['PRIMARY_KEY'])) 559 { 560 if (!is_array($table_data['PRIMARY_KEY'])) 561 { 562 $table_data['PRIMARY_KEY'] = array($table_data['PRIMARY_KEY']); 563 } 564 565 switch ($this->sql_layer) 566 { 567 case 'mysql_40': 568 case 'mysql_41': 569 case 'postgres': 570 case 'sqlite': 571 case 'sqlite3': 572 $table_sql .= ",\n\t PRIMARY KEY (" . implode(', ', $table_data['PRIMARY_KEY']) . ')'; 573 break; 574 575 case 'mssql': 576 case 'mssqlnative': 577 // We need the data here 578 $old_return_statements = $this->return_statements; 579 $this->return_statements = true; 580 581 $primary_key_stmts = $this->sql_create_primary_key($table_name, $table_data['PRIMARY_KEY']); 582 foreach ($primary_key_stmts as $pk_stmt) 583 { 584 $statements[] = $pk_stmt; 585 } 586 587 $this->return_statements = $old_return_statements; 588 break; 589 590 case 'oracle': 591 $table_sql .= ",\n\t CONSTRAINT pk_{$table_name} PRIMARY KEY (" . implode(', ', $table_data['PRIMARY_KEY']) . ')'; 592 break; 593 } 594 } 595 } 596 597 // close the table 598 switch ($this->sql_layer) 599 { 600 case 'mysql_41': 601 // make sure the table is in UTF-8 mode 602 $table_sql .= "\n) CHARACTER SET `utf8` COLLATE `utf8_bin`;"; 603 $statements[] = $table_sql; 604 break; 605 606 case 'mysql_40': 607 case 'sqlite': 608 case 'sqlite3': 609 $table_sql .= "\n);"; 610 $statements[] = $table_sql; 611 break; 612 613 case 'postgres': 614 // do we need to add a sequence for auto incrementing columns? 615 if ($create_sequence) 616 { 617 $statements[] = "CREATE SEQUENCE {$table_name}_seq;"; 618 } 619 620 $table_sql .= "\n);"; 621 $statements[] = $table_sql; 622 break; 623 624 case 'oracle': 625 $table_sql .= "\n)"; 626 $statements[] = $table_sql; 627 628 // do we need to add a sequence and a tigger for auto incrementing columns? 629 if ($create_sequence) 630 { 631 // create the actual sequence 632 $statements[] = "CREATE SEQUENCE {$table_name}_seq"; 633 634 // the trigger is the mechanism by which we increment the counter 635 $trigger = "CREATE OR REPLACE TRIGGER t_{$table_name}\n"; 636 $trigger .= "BEFORE INSERT ON {$table_name}\n"; 637 $trigger .= "FOR EACH ROW WHEN (\n"; 638 $trigger .= "\tnew.{$create_sequence} IS NULL OR new.{$create_sequence} = 0\n"; 639 $trigger .= ")\n"; 640 $trigger .= "BEGIN\n"; 641 $trigger .= "\tSELECT {$table_name}_seq.nextval\n"; 642 $trigger .= "\tINTO :new.{$create_sequence}\n"; 643 $trigger .= "\tFROM dual;\n"; 644 $trigger .= "END;"; 645 646 $statements[] = $trigger; 647 } 648 break; 649 } 650 651 // Write Keys 652 if (isset($table_data['KEYS'])) 653 { 654 foreach ($table_data['KEYS'] as $key_name => $key_data) 655 { 656 if (!is_array($key_data[1])) 657 { 658 $key_data[1] = array($key_data[1]); 659 } 660 661 $old_return_statements = $this->return_statements; 662 $this->return_statements = true; 663 664 $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]); 665 666 foreach ($key_stmts as $key_stmt) 667 { 668 $statements[] = $key_stmt; 669 } 670 671 $this->return_statements = $old_return_statements; 672 } 673 } 674 675 // Commit Transaction 676 $statements[] = 'commit'; 677 678 return $this->_sql_run_sql($statements); 679 } 680 681 /** 682 * Handle passed database update array. 683 * Expected structure... 684 * Key being one of the following 685 * drop_tables: Drop tables 686 * add_tables: Add tables 687 * change_columns: Column changes (only type, not name) 688 * add_columns: Add columns to a table 689 * drop_keys: Dropping keys 690 * drop_columns: Removing/Dropping columns 691 * add_primary_keys: adding primary keys 692 * add_unique_index: adding an unique index 693 * add_index: adding an index (can be column:index_size if you need to provide size) 694 * 695 * The values are in this format: 696 * {TABLE NAME} => array( 697 * {COLUMN NAME} => array({COLUMN TYPE}, {DEFAULT VALUE}, {OPTIONAL VARIABLES}), 698 * {KEY/INDEX NAME} => array({COLUMN NAMES}), 699 * ) 700 * 701 * For more information have a look at /develop/create_schema_files.php (only available through SVN) 702 */ 703 function perform_schema_changes($schema_changes) 704 { 705 if (empty($schema_changes)) 706 { 707 return; 708 } 709 710 $statements = array(); 711 $sqlite = false; 712 713 // For SQLite we need to perform the schema changes in a much more different way 714 if (($this->db->get_sql_layer() == 'sqlite' || $this->db->get_sql_layer() == 'sqlite3') && $this->return_statements) 715 { 716 $sqlite_data = array(); 717 $sqlite = true; 718 } 719 720 // Drop tables? 721 if (!empty($schema_changes['drop_tables'])) 722 { 723 foreach ($schema_changes['drop_tables'] as $table) 724 { 725 // only drop table if it exists 726 if ($this->sql_table_exists($table)) 727 { 728 $result = $this->sql_table_drop($table); 729 if ($this->return_statements) 730 { 731 $statements = array_merge($statements, $result); 732 } 733 } 734 } 735 } 736 737 // Add tables? 738 if (!empty($schema_changes['add_tables'])) 739 { 740 foreach ($schema_changes['add_tables'] as $table => $table_data) 741 { 742 $result = $this->sql_create_table($table, $table_data); 743 if ($this->return_statements) 744 { 745 $statements = array_merge($statements, $result); 746 } 747 } 748 } 749 750 // Change columns? 751 if (!empty($schema_changes['change_columns'])) 752 { 753 foreach ($schema_changes['change_columns'] as $table => $columns) 754 { 755 foreach ($columns as $column_name => $column_data) 756 { 757 // If the column exists we change it, else we add it ;) 758 if ($column_exists = $this->sql_column_exists($table, $column_name)) 759 { 760 $result = $this->sql_column_change($table, $column_name, $column_data, true); 761 } 762 else 763 { 764 $result = $this->sql_column_add($table, $column_name, $column_data, true); 765 } 766 767 if ($sqlite) 768 { 769 if ($column_exists) 770 { 771 $sqlite_data[$table]['change_columns'][] = $result; 772 } 773 else 774 { 775 $sqlite_data[$table]['add_columns'][] = $result; 776 } 777 } 778 else if ($this->return_statements) 779 { 780 $statements = array_merge($statements, $result); 781 } 782 } 783 } 784 } 785 786 // Add columns? 787 if (!empty($schema_changes['add_columns'])) 788 { 789 foreach ($schema_changes['add_columns'] as $table => $columns) 790 { 791 foreach ($columns as $column_name => $column_data) 792 { 793 // Only add the column if it does not exist yet 794 if ($column_exists = $this->sql_column_exists($table, $column_name)) 795 { 796 continue; 797 // This is commented out here because it can take tremendous time on updates 798 // $result = $this->sql_column_change($table, $column_name, $column_data, true); 799 } 800 else 801 { 802 $result = $this->sql_column_add($table, $column_name, $column_data, true); 803 } 804 805 if ($sqlite) 806 { 807 if ($column_exists) 808 { 809 continue; 810 // $sqlite_data[$table]['change_columns'][] = $result; 811 } 812 else 813 { 814 $sqlite_data[$table]['add_columns'][] = $result; 815 } 816 } 817 else if ($this->return_statements) 818 { 819 $statements = array_merge($statements, $result); 820 } 821 } 822 } 823 } 824 825 // Remove keys? 826 if (!empty($schema_changes['drop_keys'])) 827 { 828 foreach ($schema_changes['drop_keys'] as $table => $indexes) 829 { 830 foreach ($indexes as $index_name) 831 { 832 if (!$this->sql_index_exists($table, $index_name)) 833 { 834 continue; 835 } 836 837 $result = $this->sql_index_drop($table, $index_name); 838 839 if ($this->return_statements) 840 { 841 $statements = array_merge($statements, $result); 842 } 843 } 844 } 845 } 846 847 // Drop columns? 848 if (!empty($schema_changes['drop_columns'])) 849 { 850 foreach ($schema_changes['drop_columns'] as $table => $columns) 851 { 852 foreach ($columns as $column) 853 { 854 // Only remove the column if it exists... 855 if ($this->sql_column_exists($table, $column)) 856 { 857 $result = $this->sql_column_remove($table, $column, true); 858 859 if ($sqlite) 860 { 861 $sqlite_data[$table]['drop_columns'][] = $result; 862 } 863 else if ($this->return_statements) 864 { 865 $statements = array_merge($statements, $result); 866 } 867 } 868 } 869 } 870 } 871 872 // Add primary keys? 873 if (!empty($schema_changes['add_primary_keys'])) 874 { 875 foreach ($schema_changes['add_primary_keys'] as $table => $columns) 876 { 877 $result = $this->sql_create_primary_key($table, $columns, true); 878 879 if ($sqlite) 880 { 881 $sqlite_data[$table]['primary_key'] = $result; 882 } 883 else if ($this->return_statements) 884 { 885 $statements = array_merge($statements, $result); 886 } 887 } 888 } 889 890 // Add unique indexes? 891 if (!empty($schema_changes['add_unique_index'])) 892 { 893 foreach ($schema_changes['add_unique_index'] as $table => $index_array) 894 { 895 foreach ($index_array as $index_name => $column) 896 { 897 if ($this->sql_unique_index_exists($table, $index_name)) 898 { 899 continue; 900 } 901 902 $result = $this->sql_create_unique_index($table, $index_name, $column); 903 904 if ($this->return_statements) 905 { 906 $statements = array_merge($statements, $result); 907 } 908 } 909 } 910 } 911 912 // Add indexes? 913 if (!empty($schema_changes['add_index'])) 914 { 915 foreach ($schema_changes['add_index'] as $table => $index_array) 916 { 917 foreach ($index_array as $index_name => $column) 918 { 919 if ($this->sql_index_exists($table, $index_name)) 920 { 921 continue; 922 } 923 924 $result = $this->sql_create_index($table, $index_name, $column); 925 926 if ($this->return_statements) 927 { 928 $statements = array_merge($statements, $result); 929 } 930 } 931 } 932 } 933 934 if ($sqlite) 935 { 936 foreach ($sqlite_data as $table_name => $sql_schema_changes) 937 { 938 // Create temporary table with original data 939 $statements[] = 'begin'; 940 941 $sql = "SELECT sql 942 FROM sqlite_master 943 WHERE type = 'table' 944 AND name = '{$table_name}' 945 ORDER BY type DESC, name;"; 946 $result = $this->db->sql_query($sql); 947 948 if (!$result) 949 { 950 continue; 951 } 952 953 $row = $this->db->sql_fetchrow($result); 954 $this->db->sql_freeresult($result); 955 956 // Create a backup table and populate it, destroy the existing one 957 $statements[] = preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $row['sql']); 958 $statements[] = 'INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name; 959 $statements[] = 'DROP TABLE ' . $table_name; 960 961 // Get the columns... 962 preg_match('#\((.*)\)#s', $row['sql'], $matches); 963 964 $plain_table_cols = trim($matches[1]); 965 $new_table_cols = preg_split('/,(?![\s\w]+\))/m', $plain_table_cols); 966 $column_list = array(); 967 968 foreach ($new_table_cols as $declaration) 969 { 970 $entities = preg_split('#\s+#', trim($declaration)); 971 if ($entities[0] == 'PRIMARY') 972 { 973 continue; 974 } 975 $column_list[] = $entities[0]; 976 } 977 978 // note down the primary key notation because sqlite only supports adding it to the end for the new table 979 $primary_key = false; 980 $_new_cols = array(); 981 982 foreach ($new_table_cols as $key => $declaration) 983 { 984 $entities = preg_split('#\s+#', trim($declaration)); 985 if ($entities[0] == 'PRIMARY') 986 { 987 $primary_key = $declaration; 988 continue; 989 } 990 $_new_cols[] = $declaration; 991 } 992 993 $new_table_cols = $_new_cols; 994 995 // First of all... change columns 996 if (!empty($sql_schema_changes['change_columns'])) 997 { 998 foreach ($sql_schema_changes['change_columns'] as $column_sql) 999 { 1000 foreach ($new_table_cols as $key => $declaration) 1001 { 1002 $entities = preg_split('#\s+#', trim($declaration)); 1003 if (strpos($column_sql, $entities[0] . ' ') === 0) 1004 { 1005 $new_table_cols[$key] = $column_sql; 1006 } 1007 } 1008 } 1009 } 1010 1011 if (!empty($sql_schema_changes['add_columns'])) 1012 { 1013 foreach ($sql_schema_changes['add_columns'] as $column_sql) 1014 { 1015 $new_table_cols[] = $column_sql; 1016 } 1017 } 1018 1019 // Now drop them... 1020 if (!empty($sql_schema_changes['drop_columns'])) 1021 { 1022 foreach ($sql_schema_changes['drop_columns'] as $column_name) 1023 { 1024 // Remove from column list... 1025 $new_column_list = array(); 1026 foreach ($column_list as $key => $value) 1027 { 1028 if ($value === $column_name) 1029 { 1030 continue; 1031 } 1032 1033 $new_column_list[] = $value; 1034 } 1035 1036 $column_list = $new_column_list; 1037 1038 // Remove from table... 1039 $_new_cols = array(); 1040 foreach ($new_table_cols as $key => $declaration) 1041 { 1042 $entities = preg_split('#\s+#', trim($declaration)); 1043 if (strpos($column_name . ' ', $entities[0] . ' ') === 0) 1044 { 1045 continue; 1046 } 1047 $_new_cols[] = $declaration; 1048 } 1049 $new_table_cols = $_new_cols; 1050 } 1051 } 1052 1053 // Primary key... 1054 if (!empty($sql_schema_changes['primary_key'])) 1055 { 1056 $new_table_cols[] = 'PRIMARY KEY (' . implode(', ', $sql_schema_changes['primary_key']) . ')'; 1057 } 1058 // Add a new one or the old primary key 1059 else if ($primary_key !== false) 1060 { 1061 $new_table_cols[] = $primary_key; 1062 } 1063 1064 $columns = implode(',', $column_list); 1065 1066 // create a new table and fill it up. destroy the temp one 1067 $statements[] = 'CREATE TABLE ' . $table_name . ' (' . implode(',', $new_table_cols) . ');'; 1068 $statements[] = 'INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;'; 1069 $statements[] = 'DROP TABLE ' . $table_name . '_temp'; 1070 1071 $statements[] = 'commit'; 1072 } 1073 } 1074 1075 if ($this->return_statements) 1076 { 1077 return $statements; 1078 } 1079 } 1080 1081 /** 1082 * Gets a list of columns of a table. 1083 * 1084 * @param string $table Table name 1085 * 1086 * @return array Array of column names (all lower case) 1087 */ 1088 function sql_list_columns($table) 1089 { 1090 $columns = array(); 1091 1092 switch ($this->sql_layer) 1093 { 1094 case 'mysql_40': 1095 case 'mysql_41': 1096 $sql = "SHOW COLUMNS FROM $table"; 1097 break; 1098 1099 // PostgreSQL has a way of doing this in a much simpler way but would 1100 // not allow us to support all versions of PostgreSQL 1101 case 'postgres': 1102 $sql = "SELECT a.attname 1103 FROM pg_class c, pg_attribute a 1104 WHERE c.relname = '{$table}' 1105 AND a.attnum > 0 1106 AND a.attrelid = c.oid"; 1107 break; 1108 1109 // same deal with PostgreSQL, we must perform more complex operations than 1110 // we technically could 1111 case 'mssql': 1112 case 'mssqlnative': 1113 $sql = "SELECT c.name 1114 FROM syscolumns c 1115 LEFT JOIN sysobjects o ON c.id = o.id 1116 WHERE o.name = '{$table}'"; 1117 break; 1118 1119 case 'oracle': 1120 $sql = "SELECT column_name 1121 FROM user_tab_columns 1122 WHERE LOWER(table_name) = '" . strtolower($table) . "'"; 1123 break; 1124 1125 case 'sqlite': 1126 case 'sqlite3': 1127 $sql = "SELECT sql 1128 FROM sqlite_master 1129 WHERE type = 'table' 1130 AND name = '{$table}'"; 1131 1132 $result = $this->db->sql_query($sql); 1133 1134 if (!$result) 1135 { 1136 return false; 1137 } 1138 1139 $row = $this->db->sql_fetchrow($result); 1140 $this->db->sql_freeresult($result); 1141 1142 preg_match('#\((.*)\)#s', $row['sql'], $matches); 1143 1144 $cols = trim($matches[1]); 1145 $col_array = preg_split('/,(?![\s\w]+\))/m', $cols); 1146 1147 foreach ($col_array as $declaration) 1148 { 1149 $entities = preg_split('#\s+#', trim($declaration)); 1150 if ($entities[0] == 'PRIMARY') 1151 { 1152 continue; 1153 } 1154 1155 $column = strtolower($entities[0]); 1156 $columns[$column] = $column; 1157 } 1158 1159 return $columns; 1160 break; 1161 } 1162 1163 $result = $this->db->sql_query($sql); 1164 1165 while ($row = $this->db->sql_fetchrow($result)) 1166 { 1167 $column = strtolower(current($row)); 1168 $columns[$column] = $column; 1169 } 1170 $this->db->sql_freeresult($result); 1171 1172 return $columns; 1173 } 1174 1175 /** 1176 * Check whether a specified column exist in a table 1177 * 1178 * @param string $table Table to check 1179 * @param string $column_name Column to check 1180 * 1181 * @return bool True if column exists, false otherwise 1182 */ 1183 function sql_column_exists($table, $column_name) 1184 { 1185 $columns = $this->sql_list_columns($table); 1186 1187 return isset($columns[$column_name]); 1188 } 1189 1190 /** 1191 * Check if a specified index exists in table. Does not return PRIMARY KEY and UNIQUE indexes. 1192 * 1193 * @param string $table_name Table to check the index at 1194 * @param string $index_name The index name to check 1195 * 1196 * @return bool True if index exists, else false 1197 */ 1198 function sql_index_exists($table_name, $index_name) 1199 { 1200 if ($this->sql_layer == 'mssql' || $this->sql_layer == 'mssqlnative') 1201 { 1202 $sql = "EXEC sp_statistics '$table_name'"; 1203 $result = $this->db->sql_query($sql); 1204 1205 while ($row = $this->db->sql_fetchrow($result)) 1206 { 1207 if ($row['TYPE'] == 3) 1208 { 1209 if (strtolower($row['INDEX_NAME']) == strtolower($index_name)) 1210 { 1211 $this->db->sql_freeresult($result); 1212 return true; 1213 } 1214 } 1215 } 1216 $this->db->sql_freeresult($result); 1217 1218 return false; 1219 } 1220 1221 switch ($this->sql_layer) 1222 { 1223 case 'postgres': 1224 $sql = "SELECT ic.relname as index_name 1225 FROM pg_class bc, pg_class ic, pg_index i 1226 WHERE (bc.oid = i.indrelid) 1227 AND (ic.oid = i.indexrelid) 1228 AND (bc.relname = '" . $table_name . "') 1229 AND (i.indisunique != 't') 1230 AND (i.indisprimary != 't')"; 1231 $col = 'index_name'; 1232 break; 1233 1234 case 'mysql_40': 1235 case 'mysql_41': 1236 $sql = 'SHOW KEYS 1237 FROM ' . $table_name; 1238 $col = 'Key_name'; 1239 break; 1240 1241 case 'oracle': 1242 $sql = "SELECT index_name 1243 FROM user_indexes 1244 WHERE table_name = '" . strtoupper($table_name) . "' 1245 AND generated = 'N' 1246 AND uniqueness = 'NONUNIQUE'"; 1247 $col = 'index_name'; 1248 break; 1249 1250 case 'sqlite': 1251 case 'sqlite3': 1252 $sql = "PRAGMA index_list('" . $table_name . "');"; 1253 $col = 'name'; 1254 break; 1255 } 1256 1257 $result = $this->db->sql_query($sql); 1258 while ($row = $this->db->sql_fetchrow($result)) 1259 { 1260 if (($this->sql_layer == 'mysql_40' || $this->sql_layer == 'mysql_41') && !$row['Non_unique']) 1261 { 1262 continue; 1263 } 1264 1265 // These DBMS prefix index name with the table name 1266 switch ($this->sql_layer) 1267 { 1268 case 'oracle': 1269 case 'postgres': 1270 case 'sqlite': 1271 case 'sqlite3': 1272 $row[$col] = substr($row[$col], strlen($table_name) + 1); 1273 break; 1274 } 1275 1276 if (strtolower($row[$col]) == strtolower($index_name)) 1277 { 1278 $this->db->sql_freeresult($result); 1279 return true; 1280 } 1281 } 1282 $this->db->sql_freeresult($result); 1283 1284 return false; 1285 } 1286 1287 /** 1288 * Check if a specified index exists in table. Does not return PRIMARY KEY indexes. 1289 * 1290 * @param string $table_name Table to check the index at 1291 * @param string $index_name The index name to check 1292 * 1293 * @return bool True if index exists, else false 1294 */ 1295 function sql_unique_index_exists($table_name, $index_name) 1296 { 1297 if ($this->sql_layer == 'mssql' || $this->sql_layer == 'mssqlnative') 1298 { 1299 $sql = "EXEC sp_statistics '$table_name'"; 1300 $result = $this->db->sql_query($sql); 1301 1302 while ($row = $this->db->sql_fetchrow($result)) 1303 { 1304 // Usually NON_UNIQUE is the column we want to check, but we allow for both 1305 if ($row['TYPE'] == 3) 1306 { 1307 if (strtolower($row['INDEX_NAME']) == strtolower($index_name)) 1308 { 1309 $this->db->sql_freeresult($result); 1310 return true; 1311 } 1312 } 1313 } 1314 $this->db->sql_freeresult($result); 1315 return false; 1316 } 1317 1318 switch ($this->sql_layer) 1319 { 1320 case 'postgres': 1321 $sql = "SELECT ic.relname as index_name, i.indisunique 1322 FROM pg_class bc, pg_class ic, pg_index i 1323 WHERE (bc.oid = i.indrelid) 1324 AND (ic.oid = i.indexrelid) 1325 AND (bc.relname = '" . $table_name . "') 1326 AND (i.indisprimary != 't')"; 1327 $col = 'index_name'; 1328 break; 1329 1330 case 'mysql_40': 1331 case 'mysql_41': 1332 $sql = 'SHOW KEYS 1333 FROM ' . $table_name; 1334 $col = 'Key_name'; 1335 break; 1336 1337 case 'oracle': 1338 $sql = "SELECT index_name, table_owner 1339 FROM user_indexes 1340 WHERE table_name = '" . strtoupper($table_name) . "' 1341 AND generated = 'N' 1342 AND uniqueness = 'UNIQUE'"; 1343 $col = 'index_name'; 1344 break; 1345 1346 case 'sqlite': 1347 case 'sqlite3': 1348 $sql = "PRAGMA index_list('" . $table_name . "');"; 1349 $col = 'name'; 1350 break; 1351 } 1352 1353 $result = $this->db->sql_query($sql); 1354 while ($row = $this->db->sql_fetchrow($result)) 1355 { 1356 if (($this->sql_layer == 'mysql_40' || $this->sql_layer == 'mysql_41') && ($row['Non_unique'] || $row[$col] == 'PRIMARY')) 1357 { 1358 continue; 1359 } 1360 1361 if (($this->sql_layer == 'sqlite' || $this->sql_layer == 'sqlite3') && !$row['unique']) 1362 { 1363 continue; 1364 } 1365 1366 if ($this->sql_layer == 'postgres' && $row['indisunique'] != 't') 1367 { 1368 continue; 1369 } 1370 1371 // These DBMS prefix index name with the table name 1372 switch ($this->sql_layer) 1373 { 1374 case 'oracle': 1375 // Two cases here... prefixed with U_[table_owner] and not prefixed with table_name 1376 if (strpos($row[$col], 'U_') === 0) 1377 { 1378 $row[$col] = substr($row[$col], strlen('U_' . $row['table_owner']) + 1); 1379 } 1380 else if (strpos($row[$col], strtoupper($table_name)) === 0) 1381 { 1382 $row[$col] = substr($row[$col], strlen($table_name) + 1); 1383 } 1384 break; 1385 1386 case 'postgres': 1387 case 'sqlite': 1388 case 'sqlite3': 1389 $row[$col] = substr($row[$col], strlen($table_name) + 1); 1390 break; 1391 } 1392 1393 if (strtolower($row[$col]) == strtolower($index_name)) 1394 { 1395 $this->db->sql_freeresult($result); 1396 return true; 1397 } 1398 } 1399 $this->db->sql_freeresult($result); 1400 1401 return false; 1402 } 1403 1404 /** 1405 * Private method for performing sql statements (either execute them or return them) 1406 * @access private 1407 */ 1408 function _sql_run_sql($statements) 1409 { 1410 if ($this->return_statements) 1411 { 1412 return $statements; 1413 } 1414 1415 // We could add error handling here... 1416 foreach ($statements as $sql) 1417 { 1418 if ($sql === 'begin') 1419 { 1420 $this->db->sql_transaction('begin'); 1421 } 1422 else if ($sql === 'commit') 1423 { 1424 $this->db->sql_transaction('commit'); 1425 } 1426 else 1427 { 1428 $this->db->sql_query($sql); 1429 } 1430 } 1431 1432 return true; 1433 } 1434 1435 /** 1436 * Function to prepare some column information for better usage 1437 * @access private 1438 */ 1439 function sql_prepare_column_data($table_name, $column_name, $column_data) 1440 { 1441 if (strlen($column_name) > 30) 1442 { 1443 trigger_error("Column name '$column_name' on table '$table_name' is too long. The maximum is 30 characters.", E_USER_ERROR); 1444 } 1445 1446 // Get type 1447 list($column_type, $orig_column_type) = $this->get_column_type($column_data[0]); 1448 1449 // Adjust default value if db-dependent specified 1450 if (is_array($column_data[1])) 1451 { 1452 $column_data[1] = (isset($column_data[1][$this->sql_layer])) ? $column_data[1][$this->sql_layer] : $column_data[1]['default']; 1453 } 1454 1455 $sql = ''; 1456 1457 $return_array = array(); 1458 1459 switch ($this->sql_layer) 1460 { 1461 case 'mssql': 1462 case 'mssqlnative': 1463 $sql .= " {$column_type} "; 1464 $sql_default = " {$column_type} "; 1465 1466 // For adding columns we need the default definition 1467 if (!is_null($column_data[1])) 1468 { 1469 // For hexadecimal values do not use single quotes 1470 if (strpos($column_data[1], '0x') === 0) 1471 { 1472 $return_array['default'] = 'DEFAULT (' . $column_data[1] . ') '; 1473 $sql_default .= $return_array['default']; 1474 } 1475 else 1476 { 1477 $return_array['default'] = 'DEFAULT (' . ((is_numeric($column_data[1])) ? $column_data[1] : "'{$column_data[1]}'") . ') '; 1478 $sql_default .= $return_array['default']; 1479 } 1480 } 1481 1482 if (isset($column_data[2]) && $column_data[2] == 'auto_increment') 1483 { 1484 // $sql .= 'IDENTITY (1, 1) '; 1485 $sql_default .= 'IDENTITY (1, 1) '; 1486 } 1487 1488 $return_array['textimage'] = $column_type === '[text]'; 1489 1490 if (!is_null($column_data[1]) || (isset($column_data[2]) && $column_data[2] == 'auto_increment')) 1491 { 1492 $sql .= 'NOT NULL'; 1493 $sql_default .= 'NOT NULL'; 1494 } 1495 else 1496 { 1497 $sql .= 'NULL'; 1498 $sql_default .= 'NULL'; 1499 } 1500 1501 $return_array['column_type_sql_default'] = $sql_default; 1502 1503 break; 1504 1505 case 'mysql_40': 1506 case 'mysql_41': 1507 $sql .= " {$column_type} "; 1508 1509 // For hexadecimal values do not use single quotes 1510 if (!is_null($column_data[1]) && substr($column_type, -4) !== 'text' && substr($column_type, -4) !== 'blob') 1511 { 1512 $sql .= (strpos($column_data[1], '0x') === 0) ? "DEFAULT {$column_data[1]} " : "DEFAULT '{$column_data[1]}' "; 1513 } 1514 1515 if (!is_null($column_data[1]) || (isset($column_data[2]) && $column_data[2] == 'auto_increment')) 1516 { 1517 $sql .= 'NOT NULL'; 1518 } 1519 else 1520 { 1521 $sql .= 'NULL'; 1522 } 1523 1524 if (isset($column_data[2])) 1525 { 1526 if ($column_data[2] == 'auto_increment') 1527 { 1528 $sql .= ' auto_increment'; 1529 } 1530 else if ($this->sql_layer === 'mysql_41' && $column_data[2] == 'true_sort') 1531 { 1532 $sql .= ' COLLATE utf8_unicode_ci'; 1533 } 1534 } 1535 1536 if (isset($column_data['after'])) 1537 { 1538 $return_array['after'] = $column_data['after']; 1539 } 1540 1541 break; 1542 1543 case 'oracle': 1544 $sql .= " {$column_type} "; 1545 $sql .= (!is_null($column_data[1])) ? "DEFAULT '{$column_data[1]}' " : ''; 1546 1547 // In Oracle empty strings ('') are treated as NULL. 1548 // Therefore in oracle we allow NULL's for all DEFAULT '' entries 1549 // Oracle does not like setting NOT NULL on a column that is already NOT NULL (this happens only on number fields) 1550 if (!preg_match('/number/i', $column_type)) 1551 { 1552 $sql .= ($column_data[1] === '' || $column_data[1] === null) ? '' : 'NOT NULL'; 1553 } 1554 1555 $return_array['auto_increment'] = false; 1556 if (isset($column_data[2]) && $column_data[2] == 'auto_increment') 1557 { 1558 $return_array['auto_increment'] = true; 1559 } 1560 1561 break; 1562 1563 case 'postgres': 1564 $return_array['column_type'] = $column_type; 1565 1566 $sql .= " {$column_type} "; 1567 1568 $return_array['auto_increment'] = false; 1569 if (isset($column_data[2]) && $column_data[2] == 'auto_increment') 1570 { 1571 $default_val = "nextval('{$table_name}_seq')"; 1572 $return_array['auto_increment'] = true; 1573 } 1574 else if (!is_null($column_data[1])) 1575 { 1576 $default_val = "'" . $column_data[1] . "'"; 1577 $return_array['null'] = 'NOT NULL'; 1578 $sql .= 'NOT NULL '; 1579 } 1580 else 1581 { 1582 // Integers need to have 0 instead of empty string as default 1583 if (strpos($column_type, 'INT') === 0) 1584 { 1585 $default_val = '0'; 1586 } 1587 else 1588 { 1589 $default_val = "'" . $column_data[1] . "'"; 1590 } 1591 $return_array['null'] = 'NULL'; 1592 $sql .= 'NULL '; 1593 } 1594 1595 $return_array['default'] = $default_val; 1596 1597 $sql .= "DEFAULT {$default_val}"; 1598 1599 // Unsigned? Then add a CHECK contraint 1600 if (in_array($orig_column_type, $this->unsigned_types)) 1601 { 1602 $return_array['constraint'] = "CHECK ({$column_name} >= 0)"; 1603 $sql .= " CHECK ({$column_name} >= 0)"; 1604 } 1605 1606 break; 1607 1608 case 'sqlite': 1609 case 'sqlite3': 1610 $return_array['primary_key_set'] = false; 1611 if (isset($column_data[2]) && $column_data[2] == 'auto_increment') 1612 { 1613 $sql .= ' INTEGER PRIMARY KEY'; 1614 $return_array['primary_key_set'] = true; 1615 1616 if ($this->sql_layer === 'sqlite3') 1617 { 1618 $sql .= ' AUTOINCREMENT'; 1619 } 1620 } 1621 else 1622 { 1623 $sql .= ' ' . $column_type; 1624 } 1625 1626 if (!is_null($column_data[1])) 1627 { 1628 $sql .= ' NOT NULL '; 1629 $sql .= "DEFAULT '{$column_data[1]}'"; 1630 } 1631 1632 break; 1633 } 1634 1635 $return_array['column_type_sql'] = $sql; 1636 1637 return $return_array; 1638 } 1639 1640 /** 1641 * Get the column's database type from the type map 1642 * 1643 * @param string $column_map_type 1644 * @return array column type for this database 1645 * and map type without length 1646 */ 1647 function get_column_type($column_map_type) 1648 { 1649 if (strpos($column_map_type, ':') !== false) 1650 { 1651 list($orig_column_type, $column_length) = explode(':', $column_map_type); 1652 if (!is_array($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':'])) 1653 { 1654 $column_type = sprintf($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':'], $column_length); 1655 } 1656 else 1657 { 1658 if (isset($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['rule'])) 1659 { 1660 switch ($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['rule'][0]) 1661 { 1662 case 'div': 1663 $column_length /= $this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['rule'][1]; 1664 $column_length = ceil($column_length); 1665 $column_type = sprintf($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':'][0], $column_length); 1666 break; 1667 } 1668 } 1669 1670 if (isset($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['limit'])) 1671 { 1672 switch ($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['limit'][0]) 1673 { 1674 case 'mult': 1675 $column_length *= $this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['limit'][1]; 1676 if ($column_length > $this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['limit'][2]) 1677 { 1678 $column_type = $this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['limit'][3]; 1679 } 1680 else 1681 { 1682 $column_type = sprintf($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':'][0], $column_length); 1683 } 1684 break; 1685 } 1686 } 1687 } 1688 $orig_column_type .= ':'; 1689 } 1690 else 1691 { 1692 $orig_column_type = $column_map_type; 1693 $column_type = $this->dbms_type_map[$this->sql_layer][$column_map_type]; 1694 } 1695 1696 return array($column_type, $orig_column_type); 1697 } 1698 1699 /** 1700 * Add new column 1701 */ 1702 function sql_column_add($table_name, $column_name, $column_data, $inline = false) 1703 { 1704 $column_data = $this->sql_prepare_column_data($table_name, $column_name, $column_data); 1705 $statements = array(); 1706 1707 switch ($this->sql_layer) 1708 { 1709 case 'mssql': 1710 case 'mssqlnative': 1711 // Does not support AFTER, only through temporary table 1712 $statements[] = 'ALTER TABLE [' . $table_name . '] ADD [' . $column_name . '] ' . $column_data['column_type_sql_default']; 1713 break; 1714 1715 case 'mysql_40': 1716 case 'mysql_41': 1717 $after = (!empty($column_data['after'])) ? ' AFTER ' . $column_data['after'] : ''; 1718 $statements[] = 'ALTER TABLE `' . $table_name . '` ADD COLUMN `' . $column_name . '` ' . $column_data['column_type_sql'] . $after; 1719 break; 1720 1721 case 'oracle': 1722 // Does not support AFTER, only through temporary table 1723 $statements[] = 'ALTER TABLE ' . $table_name . ' ADD ' . $column_name . ' ' . $column_data['column_type_sql']; 1724 break; 1725 1726 case 'postgres': 1727 // Does not support AFTER, only through temporary table 1728 if (version_compare($this->db->sql_server_info(true), '8.0', '>=')) 1729 { 1730 $statements[] = 'ALTER TABLE ' . $table_name . ' ADD COLUMN "' . $column_name . '" ' . $column_data['column_type_sql']; 1731 } 1732 else 1733 { 1734 // old versions cannot add columns with default and null information 1735 $statements[] = 'ALTER TABLE ' . $table_name . ' ADD COLUMN "' . $column_name . '" ' . $column_data['column_type'] . ' ' . $column_data['constraint']; 1736 1737 if (isset($column_data['null'])) 1738 { 1739 if ($column_data['null'] == 'NOT NULL') 1740 { 1741 $statements[] = 'ALTER TABLE ' . $table_name . ' ALTER COLUMN ' . $column_name . ' SET NOT NULL'; 1742 } 1743 } 1744 1745 if (isset($column_data['default'])) 1746 { 1747 $statements[] = 'ALTER TABLE ' . $table_name . ' ALTER COLUMN ' . $column_name . ' SET DEFAULT ' . $column_data['default']; 1748 } 1749 } 1750 1751 break; 1752 1753 case 'sqlite': 1754 if ($inline && $this->return_statements) 1755 { 1756 return $column_name . ' ' . $column_data['column_type_sql']; 1757 } 1758 1759 $recreate_queries = $this->sqlite_get_recreate_table_queries($table_name); 1760 if (empty($recreate_queries)) 1761 { 1762 break; 1763 } 1764 1765 $statements[] = 'begin'; 1766 1767 $sql_create_table = array_shift($recreate_queries); 1768 1769 // Create a backup table and populate it, destroy the existing one 1770 $statements[] = preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $sql_create_table); 1771 $statements[] = 'INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name; 1772 $statements[] = 'DROP TABLE ' . $table_name; 1773 1774 preg_match('#\((.*)\)#s', $sql_create_table, $matches); 1775 1776 $new_table_cols = trim($matches[1]); 1777 $old_table_cols = preg_split('/,(?![\s\w]+\))/m', $new_table_cols); 1778 $column_list = array(); 1779 1780 foreach ($old_table_cols as $declaration) 1781 { 1782 $entities = preg_split('#\s+#', trim($declaration)); 1783 if ($entities[0] == 'PRIMARY') 1784 { 1785 continue; 1786 } 1787 $column_list[] = $entities[0]; 1788 } 1789 1790 $columns = implode(',', $column_list); 1791 1792 $new_table_cols = $column_name . ' ' . $column_data['column_type_sql'] . ',' . $new_table_cols; 1793 1794 // create a new table and fill it up. destroy the temp one 1795 $statements[] = 'CREATE TABLE ' . $table_name . ' (' . $new_table_cols . ');'; 1796 $statements = array_merge($statements, $recreate_queries); 1797 1798 $statements[] = 'INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;'; 1799 $statements[] = 'DROP TABLE ' . $table_name . '_temp'; 1800 1801 $statements[] = 'commit'; 1802 break; 1803 1804 case 'sqlite3': 1805 if ($inline && $this->return_statements) 1806 { 1807 return $column_name . ' ' . $column_data['column_type_sql']; 1808 } 1809 1810 $statements[] = 'ALTER TABLE ' . $table_name . ' ADD ' . $column_name . ' ' . $column_data['column_type_sql']; 1811 break; 1812 } 1813 1814 return $this->_sql_run_sql($statements); 1815 } 1816 1817 /** 1818 * Drop column 1819 */ 1820 function sql_column_remove($table_name, $column_name, $inline = false) 1821 { 1822 $statements = array(); 1823 1824 switch ($this->sql_layer) 1825 { 1826 case 'mssql': 1827 case 'mssqlnative': 1828 // We need the data here 1829 $old_return_statements = $this->return_statements; 1830 $this->return_statements = true; 1831 1832 $indexes = $this->get_existing_indexes($table_name, $column_name); 1833 $indexes = array_merge($indexes, $this->get_existing_indexes($table_name, $column_name, true)); 1834 1835 // Drop any indexes 1836 $recreate_indexes = array(); 1837 if (!empty($indexes)) 1838 { 1839 foreach ($indexes as $index_name => $index_data) 1840 { 1841 $result = $this->sql_index_drop($table_name, $index_name); 1842 $statements = array_merge($statements, $result); 1843 if (sizeof($index_data) > 1) 1844 { 1845 // Remove this column from the index and recreate it 1846 $recreate_indexes[$index_name] = array_diff($index_data, array($column_name)); 1847 } 1848 } 1849 } 1850 1851 // Drop default value constraint 1852 $result = $this->mssql_get_drop_default_constraints_queries($table_name, $column_name); 1853 $statements = array_merge($statements, $result); 1854 1855 // Remove the column 1856 $statements[] = 'ALTER TABLE [' . $table_name . '] DROP COLUMN [' . $column_name . ']'; 1857 1858 if (!empty($recreate_indexes)) 1859 { 1860 // Recreate indexes after we removed the column 1861 foreach ($recreate_indexes as $index_name => $index_data) 1862 { 1863 $result = $this->sql_create_index($table_name, $index_name, $index_data); 1864 $statements = array_merge($statements, $result); 1865 } 1866 } 1867 1868 $this->return_statements = $old_return_statements; 1869 break; 1870 1871 case 'mysql_40': 1872 case 'mysql_41': 1873 $statements[] = 'ALTER TABLE `' . $table_name . '` DROP COLUMN `' . $column_name . '`'; 1874 break; 1875 1876 case 'oracle': 1877 $statements[] = 'ALTER TABLE ' . $table_name . ' DROP COLUMN ' . $column_name; 1878 break; 1879 1880 case 'postgres': 1881 $statements[] = 'ALTER TABLE ' . $table_name . ' DROP COLUMN "' . $column_name . '"'; 1882 break; 1883 1884 case 'sqlite': 1885 case 'sqlite3': 1886 1887 if ($inline && $this->return_statements) 1888 { 1889 return $column_name; 1890 } 1891 1892 $recreate_queries = $this->sqlite_get_recreate_table_queries($table_name, $column_name); 1893 if (empty($recreate_queries)) 1894 { 1895 break; 1896 } 1897 1898 $statements[] = 'begin'; 1899 1900 $sql_create_table = array_shift($recreate_queries); 1901 1902 // Create a backup table and populate it, destroy the existing one 1903 $statements[] = preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $sql_create_table); 1904 $statements[] = 'INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name; 1905 $statements[] = 'DROP TABLE ' . $table_name; 1906 1907 preg_match('#\((.*)\)#s', $sql_create_table, $matches); 1908 1909 $new_table_cols = trim($matches[1]); 1910 $old_table_cols = preg_split('/,(?![\s\w]+\))/m', $new_table_cols); 1911 $column_list = array(); 1912 1913 foreach ($old_table_cols as $declaration) 1914 { 1915 $entities = preg_split('#\s+#', trim($declaration)); 1916 if ($entities[0] == 'PRIMARY' || $entities[0] === $column_name) 1917 { 1918 continue; 1919 } 1920 $column_list[] = $entities[0]; 1921 } 1922 1923 $columns = implode(',', $column_list); 1924 1925 $new_table_cols = trim(preg_replace('/' . $column_name . '\b[^,]+(?:,|$)/m', '', $new_table_cols)); 1926 if (substr($new_table_cols, -1) === ',') 1927 { 1928 // Remove the comma from the last entry again 1929 $new_table_cols = substr($new_table_cols, 0, -1); 1930 } 1931 1932 // create a new table and fill it up. destroy the temp one 1933 $statements[] = 'CREATE TABLE ' . $table_name . ' (' . $new_table_cols . ');'; 1934 $statements = array_merge($statements, $recreate_queries); 1935 1936 $statements[] = 'INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;'; 1937 $statements[] = 'DROP TABLE ' . $table_name . '_temp'; 1938 1939 $statements[] = 'commit'; 1940 break; 1941 } 1942 1943 return $this->_sql_run_sql($statements); 1944 } 1945 1946 /** 1947 * Drop Index 1948 */ 1949 function sql_index_drop($table_name, $index_name) 1950 { 1951 $statements = array(); 1952 1953 switch ($this->sql_layer) 1954 { 1955 case 'mssql': 1956 case 'mssqlnative': 1957 $statements[] = 'DROP INDEX ' . $table_name . '.' . $index_name; 1958 break; 1959 1960 case 'mysql_40': 1961 case 'mysql_41': 1962 $statements[] = 'DROP INDEX ' . $index_name . ' ON ' . $table_name; 1963 break; 1964 1965 case 'oracle': 1966 case 'postgres': 1967 case 'sqlite': 1968 case 'sqlite3': 1969 $statements[] = 'DROP INDEX ' . $table_name . '_' . $index_name; 1970 break; 1971 } 1972 1973 return $this->_sql_run_sql($statements); 1974 } 1975 1976 /** 1977 * Drop Table 1978 */ 1979 function sql_table_drop($table_name) 1980 { 1981 $statements = array(); 1982 1983 if (!$this->sql_table_exists($table_name)) 1984 { 1985 return $this->_sql_run_sql($statements); 1986 } 1987 1988 // the most basic operation, get rid of the table 1989 $statements[] = 'DROP TABLE ' . $table_name; 1990 1991 switch ($this->sql_layer) 1992 { 1993 case 'oracle': 1994 $sql = 'SELECT A.REFERENCED_NAME 1995 FROM USER_DEPENDENCIES A, USER_TRIGGERS B 1996 WHERE A.REFERENCED_TYPE = \'SEQUENCE\' 1997 AND A.NAME = B.TRIGGER_NAME 1998 AND B.TABLE_NAME = \'' . strtoupper($table_name) . "'"; 1999 $result = $this->db->sql_query($sql); 2000 2001 // any sequences ref'd to this table's triggers? 2002 while ($row = $this->db->sql_fetchrow($result)) 2003 { 2004 $statements[] = "DROP SEQUENCE {$row['referenced_name']}"; 2005 } 2006 $this->db->sql_freeresult($result); 2007 break; 2008 2009 case 'postgres': 2010 // PGSQL does not "tightly" bind sequences and tables, we must guess... 2011 $sql = "SELECT relname 2012 FROM pg_class 2013 WHERE relkind = 'S' 2014 AND relname = '{$table_name}_seq'"; 2015 $result = $this->db->sql_query($sql); 2016 2017 // We don't even care about storing the results. We already know the answer if we get rows back. 2018 if ($this->db->sql_fetchrow($result)) 2019 { 2020 $statements[] = "DROP SEQUENCE {$table_name}_seq;\n"; 2021 } 2022 $this->db->sql_freeresult($result); 2023 break; 2024 } 2025 2026 return $this->_sql_run_sql($statements); 2027 } 2028 2029 /** 2030 * Add primary key 2031 */ 2032 function sql_create_primary_key($table_name, $column, $inline = false) 2033 { 2034 $statements = array(); 2035 2036 switch ($this->sql_layer) 2037 { 2038 case 'postgres': 2039 case 'mysql_40': 2040 case 'mysql_41': 2041 $statements[] = 'ALTER TABLE ' . $table_name . ' ADD PRIMARY KEY (' . implode(', ', $column) . ')'; 2042 break; 2043 2044 case 'mssql': 2045 case 'mssqlnative': 2046 $sql = "ALTER TABLE [{$table_name}] WITH NOCHECK ADD "; 2047 $sql .= "CONSTRAINT [PK_{$table_name}] PRIMARY KEY CLUSTERED ("; 2048 $sql .= '[' . implode("],\n\t\t[", $column) . ']'; 2049 $sql .= ')'; 2050 2051 $statements[] = $sql; 2052 break; 2053 2054 case 'oracle': 2055 $statements[] = 'ALTER TABLE ' . $table_name . ' add CONSTRAINT pk_' . $table_name . ' PRIMARY KEY (' . implode(', ', $column) . ')'; 2056 break; 2057 2058 case 'sqlite': 2059 case 'sqlite3': 2060 2061 if ($inline && $this->return_statements) 2062 { 2063 return $column; 2064 } 2065 2066 $recreate_queries = $this->sqlite_get_recreate_table_queries($table_name); 2067 if (empty($recreate_queries)) 2068 { 2069 break; 2070 } 2071 2072 $statements[] = 'begin'; 2073 2074 $sql_create_table = array_shift($recreate_queries); 2075 2076 // Create a backup table and populate it, destroy the existing one 2077 $statements[] = preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $sql_create_table); 2078 $statements[] = 'INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name; 2079 $statements[] = 'DROP TABLE ' . $table_name; 2080 2081 preg_match('#\((.*)\)#s', $sql_create_table, $matches); 2082 2083 $new_table_cols = trim($matches[1]); 2084 $old_table_cols = preg_split('/,(?![\s\w]+\))/m', $new_table_cols); 2085 $column_list = array(); 2086 2087 foreach ($old_table_cols as $declaration) 2088 { 2089 $entities = preg_split('#\s+#', trim($declaration)); 2090 if ($entities[0] == 'PRIMARY') 2091 { 2092 continue; 2093 } 2094 $column_list[] = $entities[0]; 2095 } 2096 2097 $columns = implode(',', $column_list); 2098 2099 // create a new table and fill it up. destroy the temp one 2100 $statements[] = 'CREATE TABLE ' . $table_name . ' (' . $new_table_cols . ', PRIMARY KEY (' . implode(', ', $column) . '));'; 2101 $statements = array_merge($statements, $recreate_queries); 2102 2103 $statements[] = 'INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;'; 2104 $statements[] = 'DROP TABLE ' . $table_name . '_temp'; 2105 2106 $statements[] = 'commit'; 2107 break; 2108 } 2109 2110 return $this->_sql_run_sql($statements); 2111 } 2112 2113 /** 2114 * Add unique index 2115 */ 2116 function sql_create_unique_index($table_name, $index_name, $column) 2117 { 2118 $statements = array(); 2119 2120 $table_prefix = substr(CONFIG_TABLE, 0, -6); // strlen(config) 2121 if (strlen($table_name . '_' . $index_name) - strlen($table_prefix) > 24) 2122 { 2123 $max_length = strlen($table_prefix) + 24; 2124 trigger_error("Index name '{$table_name}_$index_name' on table '$table_name' is too long. The maximum is $max_length characters.", E_USER_ERROR); 2125 } 2126 2127 switch ($this->sql_layer) 2128 { 2129 case 'postgres': 2130 case 'oracle': 2131 case 'sqlite': 2132 case 'sqlite3': 2133 $statements[] = 'CREATE UNIQUE INDEX ' . $table_name . '_' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ')'; 2134 break; 2135 2136 case 'mysql_40': 2137 case 'mysql_41': 2138 $statements[] = 'ALTER TABLE ' . $table_name . ' ADD UNIQUE INDEX ' . $index_name . '(' . implode(', ', $column) . ')'; 2139 break; 2140 2141 case 'mssql': 2142 case 'mssqlnative': 2143 $statements[] = 'CREATE UNIQUE INDEX [' . $index_name . '] ON [' . $table_name . ']([' . implode('], [', $column) . '])'; 2144 break; 2145 } 2146 2147 return $this->_sql_run_sql($statements); 2148 } 2149 2150 /** 2151 * Add index 2152 */ 2153 function sql_create_index($table_name, $index_name, $column) 2154 { 2155 $statements = array(); 2156 2157 $table_prefix = substr(CONFIG_TABLE, 0, -6); // strlen(config) 2158 if (strlen($table_name . $index_name) - strlen($table_prefix) > 24) 2159 { 2160 $max_length = strlen($table_prefix) + 24; 2161 trigger_error("Index name '{$table_name}_$index_name' on table '$table_name' is too long. The maximum is $max_length characters.", E_USER_ERROR); 2162 } 2163 2164 // remove index length unless MySQL4 2165 if ('mysql_40' != $this->sql_layer) 2166 { 2167 $column = preg_replace('#:.*$#', '', $column); 2168 } 2169 2170 switch ($this->sql_layer) 2171 { 2172 case 'postgres': 2173 case 'oracle': 2174 case 'sqlite': 2175 case 'sqlite3': 2176 $statements[] = 'CREATE INDEX ' . $table_name . '_' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ')'; 2177 break; 2178 2179 case 'mysql_40': 2180 // add index size to definition as required by MySQL4 2181 foreach ($column as $i => $col) 2182 { 2183 if (false !== strpos($col, ':')) 2184 { 2185 list($col, $index_size) = explode(':', $col); 2186 $column[$i] = "$col($index_size)"; 2187 } 2188 } 2189 // no break 2190 case 'mysql_41': 2191 $statements[] = 'ALTER TABLE ' . $table_name . ' ADD INDEX ' . $index_name . ' (' . implode(', ', $column) . ')'; 2192 break; 2193 2194 case 'mssql': 2195 case 'mssqlnative': 2196 $statements[] = 'CREATE INDEX [' . $index_name . '] ON [' . $table_name . ']([' . implode('], [', $column) . '])'; 2197 break; 2198 } 2199 2200 return $this->_sql_run_sql($statements); 2201 } 2202 2203 /** 2204 * List all of the indices that belong to a table, 2205 * does not count: 2206 * * UNIQUE indices 2207 * * PRIMARY keys 2208 */ 2209 function sql_list_index($table_name) 2210 { 2211 $index_array = array(); 2212 2213 if ($this->sql_layer == 'mssql' || $this->sql_layer == 'mssqlnative') 2214 { 2215 $sql = "EXEC sp_statistics '$table_name'"; 2216 $result = $this->db->sql_query($sql); 2217 while ($row = $this->db->sql_fetchrow($result)) 2218 { 2219 if ($row['TYPE'] == 3) 2220 { 2221 $index_array[] = $row['INDEX_NAME']; 2222 } 2223 } 2224 $this->db->sql_freeresult($result); 2225 } 2226 else 2227 { 2228 switch ($this->sql_layer) 2229 { 2230 case 'postgres': 2231 $sql = "SELECT ic.relname as index_name 2232 FROM pg_class bc, pg_class ic, pg_index i 2233 WHERE (bc.oid = i.indrelid) 2234 AND (ic.oid = i.indexrelid) 2235 AND (bc.relname = '" . $table_name . "') 2236 AND (i.indisunique != 't') 2237 AND (i.indisprimary != 't')"; 2238 $col = 'index_name'; 2239 break; 2240 2241 case 'mysql_40': 2242 case 'mysql_41': 2243 $sql = 'SHOW KEYS 2244 FROM ' . $table_name; 2245 $col = 'Key_name'; 2246 break; 2247 2248 case 'oracle': 2249 $sql = "SELECT index_name 2250 FROM user_indexes 2251 WHERE table_name = '" . strtoupper($table_name) . "' 2252 AND generated = 'N' 2253 AND uniqueness = 'NONUNIQUE'"; 2254 $col = 'index_name'; 2255 break; 2256 2257 case 'sqlite': 2258 case 'sqlite3': 2259 $sql = "PRAGMA index_info('" . $table_name . "');"; 2260 $col = 'name'; 2261 break; 2262 } 2263 2264 $result = $this->db->sql_query($sql); 2265 while ($row = $this->db->sql_fetchrow($result)) 2266 { 2267 if (($this->sql_layer == 'mysql_40' || $this->sql_layer == 'mysql_41') && !$row['Non_unique']) 2268 { 2269 continue; 2270 } 2271 2272 switch ($this->sql_layer) 2273 { 2274 case 'oracle': 2275 case 'postgres': 2276 case 'sqlite': 2277 case 'sqlite3': 2278 $row[$col] = substr($row[$col], strlen($table_name) + 1); 2279 break; 2280 } 2281 2282 $index_array[] = $row[$col]; 2283 } 2284 $this->db->sql_freeresult($result); 2285 } 2286 2287 return array_map('strtolower', $index_array); 2288 } 2289 2290 /** 2291 * Removes table_name from the index_name if it is at the beginning 2292 * 2293 * @param $table_name 2294 * @param $index_name 2295 * @return string 2296 */ 2297 protected function strip_table_name_from_index_name($table_name, $index_name) 2298 { 2299 return (strpos(strtoupper($index_name), strtoupper($table_name)) === 0) ? substr($index_name, strlen($table_name) + 1) : $index_name; 2300 } 2301 2302 /** 2303 * Change column type (not name!) 2304 */ 2305 function sql_column_change($table_name, $column_name, $column_data, $inline = false) 2306 { 2307 $original_column_data = $column_data; 2308 $column_data = $this->sql_prepare_column_data($table_name, $column_name, $column_data); 2309 $statements = array(); 2310 2311 switch ($this->sql_layer) 2312 { 2313 case 'mssql': 2314 case 'mssqlnative': 2315 // We need the data here 2316 $old_return_statements = $this->return_statements; 2317 $this->return_statements = true; 2318 2319 $indexes = $this->get_existing_indexes($table_name, $column_name); 2320 $unique_indexes = $this->get_existing_indexes($table_name, $column_name, true); 2321 2322 // Drop any indexes 2323 if (!empty($indexes) || !empty($unique_indexes)) 2324 { 2325 $drop_indexes = array_merge(array_keys($indexes), array_keys($unique_indexes)); 2326 foreach ($drop_indexes as $index_name) 2327 { 2328 $result = $this->sql_index_drop($table_name, $index_name); 2329 $statements = array_merge($statements, $result); 2330 } 2331 } 2332 2333 // Drop default value constraint 2334 $result = $this->mssql_get_drop_default_constraints_queries($table_name, $column_name); 2335 $statements = array_merge($statements, $result); 2336 2337 // Change the column 2338 $statements[] = 'ALTER TABLE [' . $table_name . '] ALTER COLUMN [' . $column_name . '] ' . $column_data['column_type_sql']; 2339 2340 if (!empty($column_data['default'])) 2341 { 2342 // Add new default value constraint 2343 $statements[] = 'ALTER TABLE [' . $table_name . '] ADD CONSTRAINT [DF_' . $table_name . '_' . $column_name . '_1] ' . $column_data['default'] . ' FOR [' . $column_name . ']'; 2344 } 2345 2346 if (!empty($indexes)) 2347 { 2348 // Recreate indexes after we changed the column 2349 foreach ($indexes as $index_name => $index_data) 2350 { 2351 $result = $this->sql_create_index($table_name, $index_name, $index_data); 2352 $statements = array_merge($statements, $result); 2353 } 2354 } 2355 2356 if (!empty($unique_indexes)) 2357 { 2358 // Recreate unique indexes after we changed the column 2359 foreach ($unique_indexes as $index_name => $index_data) 2360 { 2361 $result = $this->sql_create_unique_index($table_name, $index_name, $index_data); 2362 $statements = array_merge($statements, $result); 2363 } 2364 } 2365 2366 $this->return_statements = $old_return_statements; 2367 break; 2368 2369 case 'mysql_40': 2370 case 'mysql_41': 2371 $statements[] = 'ALTER TABLE `' . $table_name . '` CHANGE `' . $column_name . '` `' . $column_name . '` ' . $column_data['column_type_sql']; 2372 break; 2373 2374 case 'oracle': 2375 // We need the data here 2376 $old_return_statements = $this->return_statements; 2377 $this->return_statements = true; 2378 2379 // Get list of existing indexes 2380 $indexes = $this->get_existing_indexes($table_name, $column_name); 2381 $unique_indexes = $this->get_existing_indexes($table_name, $column_name, true); 2382 2383 // Drop any indexes 2384 if (!empty($indexes) || !empty($unique_indexes)) 2385 { 2386 $drop_indexes = array_merge(array_keys($indexes), array_keys($unique_indexes)); 2387 foreach ($drop_indexes as $index_name) 2388 { 2389 $result = $this->sql_index_drop($table_name, $this->strip_table_name_from_index_name($table_name, $index_name)); 2390 $statements = array_merge($statements, $result); 2391 } 2392 } 2393 2394 $temp_column_name = 'temp_' . substr(md5($column_name), 0, 25); 2395 // Add a temporary table with the new type 2396 $result = $this->sql_column_add($table_name, $temp_column_name, $original_column_data); 2397 $statements = array_merge($statements, $result); 2398 2399 // Copy the data to the new column 2400 $statements[] = 'UPDATE ' . $table_name . ' SET ' . $temp_column_name . ' = ' . $column_name; 2401 2402 // Drop the original column 2403 $result = $this->sql_column_remove($table_name, $column_name); 2404 $statements = array_merge($statements, $result); 2405 2406 // Recreate the original column with the new type 2407 $result = $this->sql_column_add($table_name, $column_name, $original_column_data); 2408 $statements = array_merge($statements, $result); 2409 2410 if (!empty($indexes)) 2411 { 2412 // Recreate indexes after we changed the column 2413 foreach ($indexes as $index_name => $index_data) 2414 { 2415 $result = $this->sql_create_index($table_name, $this->strip_table_name_from_index_name($table_name, $index_name), $index_data); 2416 $statements = array_merge($statements, $result); 2417 } 2418 } 2419 2420 if (!empty($unique_indexes)) 2421 { 2422 // Recreate unique indexes after we changed the column 2423 foreach ($unique_indexes as $index_name => $index_data) 2424 { 2425 $result = $this->sql_create_unique_index($table_name, $this->strip_table_name_from_index_name($table_name, $index_name), $index_data); 2426 $statements = array_merge($statements, $result); 2427 } 2428 } 2429 2430 // Copy the data to the original column 2431 $statements[] = 'UPDATE ' . $table_name . ' SET ' . $column_name . ' = ' . $temp_column_name; 2432 2433 // Drop the temporary column again 2434 $result = $this->sql_column_remove($table_name, $temp_column_name); 2435 $statements = array_merge($statements, $result); 2436 2437 $this->return_statements = $old_return_statements; 2438 break; 2439 2440 case 'postgres': 2441 $sql = 'ALTER TABLE ' . $table_name . ' '; 2442 2443 $sql_array = array(); 2444 $sql_array[] = 'ALTER COLUMN ' . $column_name . ' TYPE ' . $column_data['column_type']; 2445 2446 if (isset($column_data['null'])) 2447 { 2448 if ($column_data['null'] == 'NOT NULL') 2449 { 2450 $sql_array[] = 'ALTER COLUMN ' . $column_name . ' SET NOT NULL'; 2451 } 2452 else if ($column_data['null'] == 'NULL') 2453 { 2454 $sql_array[] = 'ALTER COLUMN ' . $column_name . ' DROP NOT NULL'; 2455 } 2456 } 2457 2458 if (isset($column_data['default'])) 2459 { 2460 $sql_array[] = 'ALTER COLUMN ' . $column_name . ' SET DEFAULT ' . $column_data['default']; 2461 } 2462 2463 // we don't want to double up on constraints if we change different number data types 2464 if (isset($column_data['constraint'])) 2465 { 2466 $constraint_sql = "SELECT consrc as constraint_data 2467 FROM pg_constraint, pg_class bc 2468 WHERE conrelid = bc.oid 2469 AND bc.relname = '{$table_name}' 2470 AND NOT EXISTS ( 2471 SELECT * 2472 FROM pg_constraint as c, pg_inherits as i 2473 WHERE i.inhrelid = pg_constraint.conrelid 2474 AND c.conname = pg_constraint.conname 2475 AND c.consrc = pg_constraint.consrc 2476 AND c.conrelid = i.inhparent 2477 )"; 2478 2479 $constraint_exists = false; 2480 2481 $result = $this->db->sql_query($constraint_sql); 2482 while ($row = $this->db->sql_fetchrow($result)) 2483 { 2484 if (trim($row['constraint_data']) == trim($column_data['constraint'])) 2485 { 2486 $constraint_exists = true; 2487 break; 2488 } 2489 } 2490 $this->db->sql_freeresult($result); 2491 2492 if (!$constraint_exists) 2493 { 2494 $sql_array[] = 'ADD ' . $column_data['constraint']; 2495 } 2496 } 2497 2498 $sql .= implode(', ', $sql_array); 2499 2500 $statements[] = $sql; 2501 break; 2502 2503 case 'sqlite': 2504 case 'sqlite3': 2505 2506 if ($inline && $this->return_statements) 2507 { 2508 return $column_name . ' ' . $column_data['column_type_sql']; 2509 } 2510 2511 $recreate_queries = $this->sqlite_get_recreate_table_queries($table_name); 2512 if (empty($recreate_queries)) 2513 { 2514 break; 2515 } 2516 2517 $statements[] = 'begin'; 2518 2519 $sql_create_table = array_shift($recreate_queries); 2520 2521 // Create a temp table and populate it, destroy the existing one 2522 $statements[] = preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $sql_create_table); 2523 $statements[] = 'INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name; 2524 $statements[] = 'DROP TABLE ' . $table_name; 2525 2526 preg_match('#\((.*)\)#s', $sql_create_table, $matches); 2527 2528 $new_table_cols = trim($matches[1]); 2529 $old_table_cols = preg_split('/,(?![\s\w]+\))/m', $new_table_cols); 2530 $column_list = array(); 2531 2532 foreach ($old_table_cols as $key => $declaration) 2533 { 2534 $declaration = trim($declaration); 2535 2536 // Check for the beginning of the constraint section and stop 2537 if (preg_match('/[^\(]*\s*PRIMARY KEY\s+\(/', $declaration) || 2538 preg_match('/[^\(]*\s*UNIQUE\s+\(/', $declaration) || 2539 preg_match('/[^\(]*\s*FOREIGN KEY\s+\(/', $declaration) || 2540 preg_match('/[^\(]*\s*CHECK\s+\(/', $declaration)) 2541 { 2542 break; 2543 } 2544 2545 $entities = preg_split('#\s+#', $declaration); 2546 $column_list[] = $entities[0]; 2547 if ($entities[0] == $column_name) 2548 { 2549 $old_table_cols[$key] = $column_name . ' ' . $column_data['column_type_sql']; 2550 } 2551 } 2552 2553 $columns = implode(',', $column_list); 2554 2555 // Create a new table and fill it up. destroy the temp one 2556 $statements[] = 'CREATE TABLE ' . $table_name . ' (' . implode(',', $old_table_cols) . ');'; 2557 $statements = array_merge($statements, $recreate_queries); 2558 2559 $statements[] = 'INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;'; 2560 $statements[] = 'DROP TABLE ' . $table_name . '_temp'; 2561 2562 $statements[] = 'commit'; 2563 2564 break; 2565 } 2566 2567 return $this->_sql_run_sql($statements); 2568 } 2569 2570 /** 2571 * Get queries to drop the default constraints of a column 2572 * 2573 * We need to drop the default constraints of a column, 2574 * before being able to change their type or deleting them. 2575 * 2576 * @param string $table_name 2577 * @param string $column_name 2578 * @return array Array with SQL statements 2579 */ 2580 protected function mssql_get_drop_default_constraints_queries($table_name, $column_name) 2581 { 2582 $statements = array(); 2583 if ($this->mssql_is_sql_server_2000()) 2584 { 2585 // http://msdn.microsoft.com/en-us/library/aa175912%28v=sql.80%29.aspx 2586 // Deprecated in SQL Server 2005 2587 $sql = "SELECT so.name AS def_name 2588 FROM sysobjects so 2589 JOIN sysconstraints sc ON so.id = sc.constid 2590 WHERE object_name(so.parent_obj) = '{$table_name}' 2591 AND so.xtype = 'D' 2592 AND sc.colid = (SELECT colid FROM syscolumns 2593 WHERE id = object_id('{$table_name}') 2594 AND name = '{$column_name}')"; 2595 } 2596 else 2597 { 2598 $sql = "SELECT dobj.name AS def_name 2599 FROM sys.columns col 2600 LEFT OUTER JOIN sys.objects dobj ON (dobj.object_id = col.default_object_id AND dobj.type = 'D') 2601 WHERE col.object_id = object_id('{$table_name}') 2602 AND col.name = '{$column_name}' 2603 AND dobj.name IS NOT NULL"; 2604 } 2605 2606 $result = $this->db->sql_query($sql); 2607 while ($row = $this->db->sql_fetchrow($result)) 2608 { 2609 $statements[] = 'ALTER TABLE [' . $table_name . '] DROP CONSTRAINT [' . $row['def_name'] . ']'; 2610 } 2611 $this->db->sql_freeresult($result); 2612 2613 return $statements; 2614 } 2615 2616 /** 2617 * Get a list with existing indexes for the column 2618 * 2619 * @param string $table_name 2620 * @param string $column_name 2621 * @param bool $unique Should we get unique indexes or normal ones 2622 * @return array Array with Index name => columns 2623 */ 2624 public function get_existing_indexes($table_name, $column_name, $unique = false) 2625 { 2626 switch ($this->sql_layer) 2627 { 2628 case 'mysql_40': 2629 case 'mysql_41': 2630 case 'postgres': 2631 case 'sqlite': 2632 case 'sqlite3': 2633 // Not supported 2634 throw new \Exception('DBMS is not supported'); 2635 break; 2636 } 2637 2638 $sql = ''; 2639 $existing_indexes = array(); 2640 2641 switch ($this->sql_layer) 2642 { 2643 case 'mssql': 2644 case 'mssqlnative': 2645 if ($this->mssql_is_sql_server_2000()) 2646 { 2647 // http://msdn.microsoft.com/en-us/library/aa175912%28v=sql.80%29.aspx 2648 // Deprecated in SQL Server 2005 2649 $sql = "SELECT DISTINCT ix.name AS phpbb_index_name 2650 FROM sysindexes ix 2651 INNER JOIN sysindexkeys ixc 2652 ON ixc.id = ix.id 2653 AND ixc.indid = ix.indid 2654 INNER JOIN syscolumns cols 2655 ON cols.colid = ixc.colid 2656 AND cols.id = ix.id 2657 WHERE ix.id = object_id('{$table_name}') 2658 AND cols.name = '{$column_name}' 2659 AND INDEXPROPERTY(ix.id, ix.name, 'IsUnique') = " . ($unique ? '1' : '0'); 2660 } 2661 else 2662 { 2663 $sql = "SELECT DISTINCT ix.name AS phpbb_index_name 2664 FROM sys.indexes ix 2665 INNER JOIN sys.index_columns ixc 2666 ON ixc.object_id = ix.object_id 2667 AND ixc.index_id = ix.index_id 2668 INNER JOIN sys.columns cols 2669 ON cols.column_id = ixc.column_id 2670 AND cols.object_id = ix.object_id 2671 WHERE ix.object_id = object_id('{$table_name}') 2672 AND cols.name = '{$column_name}' 2673 AND ix.is_unique = " . ($unique ? '1' : '0'); 2674 } 2675 break; 2676 2677 case 'oracle': 2678 $sql = "SELECT ix.index_name AS phpbb_index_name, ix.uniqueness AS is_unique 2679 FROM all_ind_columns ixc, all_indexes ix 2680 WHERE ix.index_name = ixc.index_name 2681 AND ixc.table_name = '" . strtoupper($table_name) . "' 2682 AND ixc.column_name = '" . strtoupper($column_name) . "'"; 2683 break; 2684 } 2685 2686 $result = $this->db->sql_query($sql); 2687 while ($row = $this->db->sql_fetchrow($result)) 2688 { 2689 if (!isset($row['is_unique']) || ($unique && $row['is_unique'] == 'UNIQUE') || (!$unique && $row['is_unique'] == 'NONUNIQUE')) 2690 { 2691 $existing_indexes[$row['phpbb_index_name']] = array(); 2692 } 2693 } 2694 $this->db->sql_freeresult($result); 2695 2696 if (empty($existing_indexes)) 2697 { 2698 return array(); 2699 } 2700 2701 switch ($this->sql_layer) 2702 { 2703 case 'mssql': 2704 case 'mssqlnative': 2705 if ($this->mssql_is_sql_server_2000()) 2706 { 2707 $sql = "SELECT DISTINCT ix.name AS phpbb_index_name, cols.name AS phpbb_column_name 2708 FROM sysindexes ix 2709 INNER JOIN sysindexkeys ixc 2710 ON ixc.id = ix.id 2711 AND ixc.indid = ix.indid 2712 INNER JOIN syscolumns cols 2713 ON cols.colid = ixc.colid 2714 AND cols.id = ix.id 2715 WHERE ix.id = object_id('{$table_name}') 2716 AND " . $this->db->sql_in_set('ix.name', array_keys($existing_indexes)); 2717 } 2718 else 2719 { 2720 $sql = "SELECT DISTINCT ix.name AS phpbb_index_name, cols.name AS phpbb_column_name 2721 FROM sys.indexes ix 2722 INNER JOIN sys.index_columns ixc 2723 ON ixc.object_id = ix.object_id 2724 AND ixc.index_id = ix.index_id 2725 INNER JOIN sys.columns cols 2726 ON cols.column_id = ixc.column_id 2727 AND cols.object_id = ix.object_id 2728 WHERE ix.object_id = object_id('{$table_name}') 2729 AND " . $this->db->sql_in_set('ix.name', array_keys($existing_indexes)); 2730 } 2731 break; 2732 2733 case 'oracle': 2734 $sql = "SELECT index_name AS phpbb_index_name, column_name AS phpbb_column_name 2735 FROM all_ind_columns 2736 WHERE table_name = '" . strtoupper($table_name) . "' 2737 AND " . $this->db->sql_in_set('index_name', array_keys($existing_indexes)); 2738 break; 2739 } 2740 2741 $result = $this->db->sql_query($sql); 2742 while ($row = $this->db->sql_fetchrow($result)) 2743 { 2744 $existing_indexes[$row['phpbb_index_name']][] = $row['phpbb_column_name']; 2745 } 2746 $this->db->sql_freeresult($result); 2747 2748 return $existing_indexes; 2749 } 2750 2751 /** 2752 * Is the used MS SQL Server a SQL Server 2000? 2753 * 2754 * @return bool 2755 */ 2756 protected function mssql_is_sql_server_2000() 2757 { 2758 if ($this->is_sql_server_2000 === null) 2759 { 2760 $sql = "SELECT CAST(SERVERPROPERTY('productversion') AS VARCHAR(25)) AS mssql_version"; 2761 $result = $this->db->sql_query($sql); 2762 $properties = $this->db->sql_fetchrow($result); 2763 $this->db->sql_freeresult($result); 2764 $this->is_sql_server_2000 = $properties['mssql_version'][0] == '8'; 2765 } 2766 2767 return $this->is_sql_server_2000; 2768 } 2769 2770 /** 2771 * Returns the Queries which are required to recreate a table including indexes 2772 * 2773 * @param string $table_name 2774 * @param string $remove_column When we drop a column, we remove the column 2775 * from all indexes. If the index has no other 2776 * column, we drop it completly. 2777 * @return array 2778 */ 2779 protected function sqlite_get_recreate_table_queries($table_name, $remove_column = '') 2780 { 2781 $queries = array(); 2782 2783 $sql = "SELECT sql 2784 FROM sqlite_master 2785 WHERE type = 'table' 2786 AND name = '{$table_name}'"; 2787 $result = $this->db->sql_query($sql); 2788 $sql_create_table = $this->db->sql_fetchfield('sql'); 2789 $this->db->sql_freeresult($result); 2790 2791 if (!$sql_create_table) 2792 { 2793 return array(); 2794 } 2795 $queries[] = $sql_create_table; 2796 2797 $sql = "SELECT sql 2798 FROM sqlite_master 2799 WHERE type = 'index' 2800 AND tbl_name = '{$table_name}'"; 2801 $result = $this->db->sql_query($sql); 2802 while ($sql_create_index = $this->db->sql_fetchfield('sql')) 2803 { 2804 if ($remove_column) 2805 { 2806 $match = array(); 2807 preg_match('#(?:[\w ]+)\((.*)\)#', $sql_create_index, $match); 2808 if (!isset($match[1])) 2809 { 2810 continue; 2811 } 2812 2813 // Find and remove $remove_column from the index 2814 $columns = explode(', ', $match[1]); 2815 $found_column = array_search($remove_column, $columns); 2816 if ($found_column !== false) 2817 { 2818 unset($columns[$found_column]); 2819 2820 // If the column list is not empty add the index to the list 2821 if (!empty($columns)) 2822 { 2823 $queries[] = str_replace($match[1], implode(', ', $columns), $sql_create_index); 2824 } 2825 } 2826 else 2827 { 2828 $queries[] = $sql_create_index; 2829 } 2830 } 2831 else 2832 { 2833 $queries[] = $sql_create_index; 2834 } 2835 } 2836 $this->db->sql_freeresult($result); 2837 2838 return $queries; 2839 } 2840 }
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
| Generated: Thu Jan 11 00:25:41 2018 | Cross-referenced by PHPXref 0.7.1 |