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