Verzeichnisstruktur phpBB-3.2.0
- Veröffentlicht
- 06.01.2017
So funktioniert es
|
|
Auf das letzte Element klicken. Dies geht jeweils ein Schritt zurück |
Auf das Icon klicken, dies öffnet das Verzeichnis. Nochmal klicken schließt das Verzeichnis. |
|
|
(Beispiel Datei-Icons)
|
Auf das Icon klicken um den Quellcode anzuzeigen |
tools.php
0001 <?php
0002 /**
0003 *
0004 * This file is part of the phpBB Forum Software package.
0005 *
0006 * @copyright (c) phpBB Limited <https://www.phpbb.com>
0007 * @license GNU General Public License, version 2 (GPL-2.0)
0008 *
0009 * For full copyright and license information, please see
0010 * the docs/CREDITS.txt file.
0011 *
0012 */
0013
0014 namespace phpbb\db\tools;
0015
0016 /**
0017 * Database Tools for handling cross-db actions such as altering columns, etc.
0018 * Currently not supported is returning SQL for creating tables.
0019 */
0020 class tools implements tools_interface
0021 {
0022 /**
0023 * Current sql layer
0024 */
0025 var $sql_layer = '';
0026
0027 /**
0028 * @var object DB object
0029 */
0030 var $db = null;
0031
0032 /**
0033 * The Column types for every database we support
0034 * @var array
0035 */
0036 var $dbms_type_map = array();
0037
0038 /**
0039 * Get the column types for every database we support
0040 *
0041 * @return array
0042 */
0043 static public function get_dbms_type_map()
0044 {
0045 return array(
0046 'mysql_41' => array(
0047 'INT:' => 'int(%d)',
0048 'BINT' => 'bigint(20)',
0049 'ULINT' => 'INT(10) UNSIGNED',
0050 'UINT' => 'mediumint(8) UNSIGNED',
0051 'UINT:' => 'int(%d) UNSIGNED',
0052 'TINT:' => 'tinyint(%d)',
0053 'USINT' => 'smallint(4) UNSIGNED',
0054 'BOOL' => 'tinyint(1) UNSIGNED',
0055 'VCHAR' => 'varchar(255)',
0056 'VCHAR:' => 'varchar(%d)',
0057 'CHAR:' => 'char(%d)',
0058 'XSTEXT' => 'text',
0059 'XSTEXT_UNI'=> 'varchar(100)',
0060 'STEXT' => 'text',
0061 'STEXT_UNI' => 'varchar(255)',
0062 'TEXT' => 'text',
0063 'TEXT_UNI' => 'text',
0064 'MTEXT' => 'mediumtext',
0065 'MTEXT_UNI' => 'mediumtext',
0066 'TIMESTAMP' => 'int(11) UNSIGNED',
0067 'DECIMAL' => 'decimal(5,2)',
0068 'DECIMAL:' => 'decimal(%d,2)',
0069 'PDECIMAL' => 'decimal(6,3)',
0070 'PDECIMAL:' => 'decimal(%d,3)',
0071 'VCHAR_UNI' => 'varchar(255)',
0072 'VCHAR_UNI:'=> 'varchar(%d)',
0073 'VCHAR_CI' => 'varchar(255)',
0074 'VARBINARY' => 'varbinary(255)',
0075 ),
0076
0077 'mysql_40' => array(
0078 'INT:' => 'int(%d)',
0079 'BINT' => 'bigint(20)',
0080 'ULINT' => 'INT(10) UNSIGNED',
0081 'UINT' => 'mediumint(8) UNSIGNED',
0082 'UINT:' => 'int(%d) UNSIGNED',
0083 'TINT:' => 'tinyint(%d)',
0084 'USINT' => 'smallint(4) UNSIGNED',
0085 'BOOL' => 'tinyint(1) UNSIGNED',
0086 'VCHAR' => 'varbinary(255)',
0087 'VCHAR:' => 'varbinary(%d)',
0088 'CHAR:' => 'binary(%d)',
0089 'XSTEXT' => 'blob',
0090 'XSTEXT_UNI'=> 'blob',
0091 'STEXT' => 'blob',
0092 'STEXT_UNI' => 'blob',
0093 'TEXT' => 'blob',
0094 'TEXT_UNI' => 'blob',
0095 'MTEXT' => 'mediumblob',
0096 'MTEXT_UNI' => 'mediumblob',
0097 'TIMESTAMP' => 'int(11) UNSIGNED',
0098 'DECIMAL' => 'decimal(5,2)',
0099 'DECIMAL:' => 'decimal(%d,2)',
0100 'PDECIMAL' => 'decimal(6,3)',
0101 'PDECIMAL:' => 'decimal(%d,3)',
0102 'VCHAR_UNI' => 'blob',
0103 'VCHAR_UNI:'=> array('varbinary(%d)', 'limit' => array('mult', 3, 255, 'blob')),
0104 'VCHAR_CI' => 'blob',
0105 'VARBINARY' => 'varbinary(255)',
0106 ),
0107
0108 'oracle' => array(
0109 'INT:' => 'number(%d)',
0110 'BINT' => 'number(20)',
0111 'ULINT' => 'number(10)',
0112 'UINT' => 'number(8)',
0113 'UINT:' => 'number(%d)',
0114 'TINT:' => 'number(%d)',
0115 'USINT' => 'number(4)',
0116 'BOOL' => 'number(1)',
0117 'VCHAR' => 'varchar2(255)',
0118 'VCHAR:' => 'varchar2(%d)',
0119 'CHAR:' => 'char(%d)',
0120 'XSTEXT' => 'varchar2(1000)',
0121 'STEXT' => 'varchar2(3000)',
0122 'TEXT' => 'clob',
0123 'MTEXT' => 'clob',
0124 'XSTEXT_UNI'=> 'varchar2(300)',
0125 'STEXT_UNI' => 'varchar2(765)',
0126 'TEXT_UNI' => 'clob',
0127 'MTEXT_UNI' => 'clob',
0128 'TIMESTAMP' => 'number(11)',
0129 'DECIMAL' => 'number(5, 2)',
0130 'DECIMAL:' => 'number(%d, 2)',
0131 'PDECIMAL' => 'number(6, 3)',
0132 'PDECIMAL:' => 'number(%d, 3)',
0133 'VCHAR_UNI' => 'varchar2(765)',
0134 'VCHAR_UNI:'=> array('varchar2(%d)', 'limit' => array('mult', 3, 765, 'clob')),
0135 'VCHAR_CI' => 'varchar2(255)',
0136 'VARBINARY' => 'raw(255)',
0137 ),
0138
0139 'sqlite3' => array(
0140 'INT:' => 'INT(%d)',
0141 'BINT' => 'BIGINT(20)',
0142 'ULINT' => 'INTEGER UNSIGNED',
0143 'UINT' => 'INTEGER UNSIGNED',
0144 'UINT:' => 'INTEGER UNSIGNED',
0145 'TINT:' => 'TINYINT(%d)',
0146 'USINT' => 'INTEGER UNSIGNED',
0147 'BOOL' => 'INTEGER UNSIGNED',
0148 'VCHAR' => 'VARCHAR(255)',
0149 'VCHAR:' => 'VARCHAR(%d)',
0150 'CHAR:' => 'CHAR(%d)',
0151 'XSTEXT' => 'TEXT(65535)',
0152 'STEXT' => 'TEXT(65535)',
0153 'TEXT' => 'TEXT(65535)',
0154 'MTEXT' => 'MEDIUMTEXT(16777215)',
0155 'XSTEXT_UNI'=> 'TEXT(65535)',
0156 'STEXT_UNI' => 'TEXT(65535)',
0157 'TEXT_UNI' => 'TEXT(65535)',
0158 'MTEXT_UNI' => 'MEDIUMTEXT(16777215)',
0159 'TIMESTAMP' => 'INTEGER UNSIGNED', //'int(11) UNSIGNED',
0160 'DECIMAL' => 'DECIMAL(5,2)',
0161 'DECIMAL:' => 'DECIMAL(%d,2)',
0162 'PDECIMAL' => 'DECIMAL(6,3)',
0163 'PDECIMAL:' => 'DECIMAL(%d,3)',
0164 'VCHAR_UNI' => 'VARCHAR(255)',
0165 'VCHAR_UNI:'=> 'VARCHAR(%d)',
0166 'VCHAR_CI' => 'VARCHAR(255)',
0167 'VARBINARY' => 'BLOB',
0168 ),
0169 );
0170 }
0171
0172 /**
0173 * A list of types being unsigned for better reference in some db's
0174 * @var array
0175 */
0176 var $unsigned_types = array('ULINT', 'UINT', 'UINT:', 'USINT', 'BOOL', 'TIMESTAMP');
0177
0178 /**
0179 * This is set to true if user only wants to return the 'to-be-executed' SQL statement(s) (as an array).
0180 * This mode has no effect on some methods (inserting of data for example). This is expressed within the methods command.
0181 */
0182 var $return_statements = false;
0183
0184 /**
0185 * Constructor. Set DB Object and set {@link $return_statements return_statements}.
0186 *
0187 * @param \phpbb\db\driver\driver_interface $db Database connection
0188 * @param bool $return_statements True if only statements should be returned and no SQL being executed
0189 */
0190 public function __construct(\phpbb\db\driver\driver_interface $db, $return_statements = false)
0191 {
0192 $this->db = $db;
0193 $this->return_statements = $return_statements;
0194
0195 $this->dbms_type_map = self::get_dbms_type_map();
0196
0197 // Determine mapping database type
0198 switch ($this->db->get_sql_layer())
0199 {
0200 case 'mysql':
0201 $this->sql_layer = 'mysql_40';
0202 break;
0203
0204 case 'mysql4':
0205 if (version_compare($this->db->sql_server_info(true), '4.1.3', '>='))
0206 {
0207 $this->sql_layer = 'mysql_41';
0208 }
0209 else
0210 {
0211 $this->sql_layer = 'mysql_40';
0212 }
0213 break;
0214
0215 case 'mysqli':
0216 $this->sql_layer = 'mysql_41';
0217 break;
0218
0219 default:
0220 $this->sql_layer = $this->db->get_sql_layer();
0221 break;
0222 }
0223 }
0224
0225 /**
0226 * Setter for {@link $return_statements return_statements}.
0227 *
0228 * @param bool $return_statements True if SQL should not be executed but returned as strings
0229 * @return null
0230 */
0231 public function set_return_statements($return_statements)
0232 {
0233 $this->return_statements = $return_statements;
0234 }
0235
0236 /**
0237 * {@inheritDoc}
0238 */
0239 function sql_list_tables()
0240 {
0241 switch ($this->db->get_sql_layer())
0242 {
0243 case 'mysql':
0244 case 'mysql4':
0245 case 'mysqli':
0246 $sql = 'SHOW TABLES';
0247 break;
0248
0249 case 'sqlite3':
0250 $sql = 'SELECT name
0251 FROM sqlite_master
0252 WHERE type = "table"
0253 AND name <> "sqlite_sequence"';
0254 break;
0255
0256 case 'oracle':
0257 $sql = 'SELECT table_name
0258 FROM USER_TABLES';
0259 break;
0260 }
0261
0262 $result = $this->db->sql_query($sql);
0263
0264 $tables = array();
0265 while ($row = $this->db->sql_fetchrow($result))
0266 {
0267 $name = current($row);
0268 $tables[$name] = $name;
0269 }
0270 $this->db->sql_freeresult($result);
0271
0272 return $tables;
0273 }
0274
0275 /**
0276 * {@inheritDoc}
0277 */
0278 function sql_table_exists($table_name)
0279 {
0280 $this->db->sql_return_on_error(true);
0281 $result = $this->db->sql_query_limit('SELECT * FROM ' . $table_name, 1);
0282 $this->db->sql_return_on_error(false);
0283
0284 if ($result)
0285 {
0286 $this->db->sql_freeresult($result);
0287 return true;
0288 }
0289
0290 return false;
0291 }
0292
0293 /**
0294 * {@inheritDoc}
0295 */
0296 function sql_create_table($table_name, $table_data)
0297 {
0298 // holds the DDL for a column
0299 $columns = $statements = array();
0300
0301 if ($this->sql_table_exists($table_name))
0302 {
0303 return $this->_sql_run_sql($statements);
0304 }
0305
0306 // Begin transaction
0307 $statements[] = 'begin';
0308
0309 // Determine if we have created a PRIMARY KEY in the earliest
0310 $primary_key_gen = false;
0311
0312 // Determine if the table requires a sequence
0313 $create_sequence = false;
0314
0315 // Begin table sql statement
0316 $table_sql = 'CREATE TABLE ' . $table_name . ' (' . "\n";
0317
0318 // Iterate through the columns to create a table
0319 foreach ($table_data['COLUMNS'] as $column_name => $column_data)
0320 {
0321 // here lies an array, filled with information compiled on the column's data
0322 $prepared_column = $this->sql_prepare_column_data($table_name, $column_name, $column_data);
0323
0324 if (isset($prepared_column['auto_increment']) && $prepared_column['auto_increment'] && strlen($column_name) > 26) // "${column_name}_gen"
0325 {
0326 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);
0327 }
0328
0329 // here we add the definition of the new column to the list of columns
0330 $columns[] = "\t {$column_name} " . $prepared_column['column_type_sql'];
0331
0332 // see if we have found a primary key set due to a column definition if we have found it, we can stop looking
0333 if (!$primary_key_gen)
0334 {
0335 $primary_key_gen = isset($prepared_column['primary_key_set']) && $prepared_column['primary_key_set'];
0336 }
0337
0338 // create sequence DDL based off of the existance of auto incrementing columns
0339 if (!$create_sequence && isset($prepared_column['auto_increment']) && $prepared_column['auto_increment'])
0340 {
0341 $create_sequence = $column_name;
0342 }
0343 }
0344
0345 // this makes up all the columns in the create table statement
0346 $table_sql .= implode(",\n", $columns);
0347
0348 // we have yet to create a primary key for this table,
0349 // this means that we can add the one we really wanted instead
0350 if (!$primary_key_gen)
0351 {
0352 // Write primary key
0353 if (isset($table_data['PRIMARY_KEY']))
0354 {
0355 if (!is_array($table_data['PRIMARY_KEY']))
0356 {
0357 $table_data['PRIMARY_KEY'] = array($table_data['PRIMARY_KEY']);
0358 }
0359
0360 switch ($this->sql_layer)
0361 {
0362 case 'mysql_40':
0363 case 'mysql_41':
0364 case 'sqlite3':
0365 $table_sql .= ",\n\t PRIMARY KEY (" . implode(', ', $table_data['PRIMARY_KEY']) . ')';
0366 break;
0367
0368 case 'oracle':
0369 $table_sql .= ",\n\t CONSTRAINT pk_{$table_name} PRIMARY KEY (" . implode(', ', $table_data['PRIMARY_KEY']) . ')';
0370 break;
0371 }
0372 }
0373 }
0374
0375 // close the table
0376 switch ($this->sql_layer)
0377 {
0378 case 'mysql_41':
0379 // make sure the table is in UTF-8 mode
0380 $table_sql .= "\n) CHARACTER SET `utf8` COLLATE `utf8_bin`;";
0381 $statements[] = $table_sql;
0382 break;
0383
0384 case 'mysql_40':
0385 case 'sqlite3':
0386 $table_sql .= "\n);";
0387 $statements[] = $table_sql;
0388 break;
0389
0390 case 'oracle':
0391 $table_sql .= "\n)";
0392 $statements[] = $table_sql;
0393
0394 // do we need to add a sequence and a tigger for auto incrementing columns?
0395 if ($create_sequence)
0396 {
0397 // create the actual sequence
0398 $statements[] = "CREATE SEQUENCE {$table_name}_seq";
0399
0400 // the trigger is the mechanism by which we increment the counter
0401 $trigger = "CREATE OR REPLACE TRIGGER t_{$table_name}\n";
0402 $trigger .= "BEFORE INSERT ON {$table_name}\n";
0403 $trigger .= "FOR EACH ROW WHEN (\n";
0404 $trigger .= "\tnew.{$create_sequence} IS NULL OR new.{$create_sequence} = 0\n";
0405 $trigger .= ")\n";
0406 $trigger .= "BEGIN\n";
0407 $trigger .= "\tSELECT {$table_name}_seq.nextval\n";
0408 $trigger .= "\tINTO :new.{$create_sequence}\n";
0409 $trigger .= "\tFROM dual;\n";
0410 $trigger .= "END;";
0411
0412 $statements[] = $trigger;
0413 }
0414 break;
0415 }
0416
0417 // Write Keys
0418 if (isset($table_data['KEYS']))
0419 {
0420 foreach ($table_data['KEYS'] as $key_name => $key_data)
0421 {
0422 if (!is_array($key_data[1]))
0423 {
0424 $key_data[1] = array($key_data[1]);
0425 }
0426
0427 $old_return_statements = $this->return_statements;
0428 $this->return_statements = true;
0429
0430 $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]);
0431
0432 foreach ($key_stmts as $key_stmt)
0433 {
0434 $statements[] = $key_stmt;
0435 }
0436
0437 $this->return_statements = $old_return_statements;
0438 }
0439 }
0440
0441 // Commit Transaction
0442 $statements[] = 'commit';
0443
0444 return $this->_sql_run_sql($statements);
0445 }
0446
0447 /**
0448 * {@inheritDoc}
0449 */
0450 function perform_schema_changes($schema_changes)
0451 {
0452 if (empty($schema_changes))
0453 {
0454 return;
0455 }
0456
0457 $statements = array();
0458 $sqlite = false;
0459
0460 // For SQLite we need to perform the schema changes in a much more different way
0461 if ($this->db->get_sql_layer() == 'sqlite3' && $this->return_statements)
0462 {
0463 $sqlite_data = array();
0464 $sqlite = true;
0465 }
0466
0467 // Drop tables?
0468 if (!empty($schema_changes['drop_tables']))
0469 {
0470 foreach ($schema_changes['drop_tables'] as $table)
0471 {
0472 // only drop table if it exists
0473 if ($this->sql_table_exists($table))
0474 {
0475 $result = $this->sql_table_drop($table);
0476 if ($this->return_statements)
0477 {
0478 $statements = array_merge($statements, $result);
0479 }
0480 }
0481 }
0482 }
0483
0484 // Add tables?
0485 if (!empty($schema_changes['add_tables']))
0486 {
0487 foreach ($schema_changes['add_tables'] as $table => $table_data)
0488 {
0489 $result = $this->sql_create_table($table, $table_data);
0490 if ($this->return_statements)
0491 {
0492 $statements = array_merge($statements, $result);
0493 }
0494 }
0495 }
0496
0497 // Change columns?
0498 if (!empty($schema_changes['change_columns']))
0499 {
0500 foreach ($schema_changes['change_columns'] as $table => $columns)
0501 {
0502 foreach ($columns as $column_name => $column_data)
0503 {
0504 // If the column exists we change it, else we add it ;)
0505 if ($column_exists = $this->sql_column_exists($table, $column_name))
0506 {
0507 $result = $this->sql_column_change($table, $column_name, $column_data, true);
0508 }
0509 else
0510 {
0511 $result = $this->sql_column_add($table, $column_name, $column_data, true);
0512 }
0513
0514 if ($sqlite)
0515 {
0516 if ($column_exists)
0517 {
0518 $sqlite_data[$table]['change_columns'][] = $result;
0519 }
0520 else
0521 {
0522 $sqlite_data[$table]['add_columns'][] = $result;
0523 }
0524 }
0525 else if ($this->return_statements)
0526 {
0527 $statements = array_merge($statements, $result);
0528 }
0529 }
0530 }
0531 }
0532
0533 // Add columns?
0534 if (!empty($schema_changes['add_columns']))
0535 {
0536 foreach ($schema_changes['add_columns'] as $table => $columns)
0537 {
0538 foreach ($columns as $column_name => $column_data)
0539 {
0540 // Only add the column if it does not exist yet
0541 if ($column_exists = $this->sql_column_exists($table, $column_name))
0542 {
0543 continue;
0544 // This is commented out here because it can take tremendous time on updates
0545 // $result = $this->sql_column_change($table, $column_name, $column_data, true);
0546 }
0547 else
0548 {
0549 $result = $this->sql_column_add($table, $column_name, $column_data, true);
0550 }
0551
0552 if ($sqlite)
0553 {
0554 if ($column_exists)
0555 {
0556 continue;
0557 // $sqlite_data[$table]['change_columns'][] = $result;
0558 }
0559 else
0560 {
0561 $sqlite_data[$table]['add_columns'][] = $result;
0562 }
0563 }
0564 else if ($this->return_statements)
0565 {
0566 $statements = array_merge($statements, $result);
0567 }
0568 }
0569 }
0570 }
0571
0572 // Remove keys?
0573 if (!empty($schema_changes['drop_keys']))
0574 {
0575 foreach ($schema_changes['drop_keys'] as $table => $indexes)
0576 {
0577 foreach ($indexes as $index_name)
0578 {
0579 if (!$this->sql_index_exists($table, $index_name))
0580 {
0581 continue;
0582 }
0583
0584 $result = $this->sql_index_drop($table, $index_name);
0585
0586 if ($this->return_statements)
0587 {
0588 $statements = array_merge($statements, $result);
0589 }
0590 }
0591 }
0592 }
0593
0594 // Drop columns?
0595 if (!empty($schema_changes['drop_columns']))
0596 {
0597 foreach ($schema_changes['drop_columns'] as $table => $columns)
0598 {
0599 foreach ($columns as $column)
0600 {
0601 // Only remove the column if it exists...
0602 if ($this->sql_column_exists($table, $column))
0603 {
0604 $result = $this->sql_column_remove($table, $column, true);
0605
0606 if ($sqlite)
0607 {
0608 $sqlite_data[$table]['drop_columns'][] = $result;
0609 }
0610 else if ($this->return_statements)
0611 {
0612 $statements = array_merge($statements, $result);
0613 }
0614 }
0615 }
0616 }
0617 }
0618
0619 // Add primary keys?
0620 if (!empty($schema_changes['add_primary_keys']))
0621 {
0622 foreach ($schema_changes['add_primary_keys'] as $table => $columns)
0623 {
0624 $result = $this->sql_create_primary_key($table, $columns, true);
0625
0626 if ($sqlite)
0627 {
0628 $sqlite_data[$table]['primary_key'] = $result;
0629 }
0630 else if ($this->return_statements)
0631 {
0632 $statements = array_merge($statements, $result);
0633 }
0634 }
0635 }
0636
0637 // Add unique indexes?
0638 if (!empty($schema_changes['add_unique_index']))
0639 {
0640 foreach ($schema_changes['add_unique_index'] as $table => $index_array)
0641 {
0642 foreach ($index_array as $index_name => $column)
0643 {
0644 if ($this->sql_unique_index_exists($table, $index_name))
0645 {
0646 continue;
0647 }
0648
0649 $result = $this->sql_create_unique_index($table, $index_name, $column);
0650
0651 if ($this->return_statements)
0652 {
0653 $statements = array_merge($statements, $result);
0654 }
0655 }
0656 }
0657 }
0658
0659 // Add indexes?
0660 if (!empty($schema_changes['add_index']))
0661 {
0662 foreach ($schema_changes['add_index'] as $table => $index_array)
0663 {
0664 foreach ($index_array as $index_name => $column)
0665 {
0666 if ($this->sql_index_exists($table, $index_name))
0667 {
0668 continue;
0669 }
0670
0671 $result = $this->sql_create_index($table, $index_name, $column);
0672
0673 if ($this->return_statements)
0674 {
0675 $statements = array_merge($statements, $result);
0676 }
0677 }
0678 }
0679 }
0680
0681 if ($sqlite)
0682 {
0683 foreach ($sqlite_data as $table_name => $sql_schema_changes)
0684 {
0685 // Create temporary table with original data
0686 $statements[] = 'begin';
0687
0688 $sql = "SELECT sql
0689 FROM sqlite_master
0690 WHERE type = 'table'
0691 AND name = '{$table_name}'
0692 ORDER BY type DESC, name;";
0693 $result = $this->db->sql_query($sql);
0694
0695 if (!$result)
0696 {
0697 continue;
0698 }
0699
0700 $row = $this->db->sql_fetchrow($result);
0701 $this->db->sql_freeresult($result);
0702
0703 // Create a backup table and populate it, destroy the existing one
0704 $statements[] = preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $row['sql']);
0705 $statements[] = 'INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name;
0706 $statements[] = 'DROP TABLE ' . $table_name;
0707
0708 // Get the columns...
0709 preg_match('#\((.*)\)#s', $row['sql'], $matches);
0710
0711 $plain_table_cols = trim($matches[1]);
0712 $new_table_cols = preg_split('/,(?![\s\w]+\))/m', $plain_table_cols);
0713 $column_list = array();
0714
0715 foreach ($new_table_cols as $declaration)
0716 {
0717 $entities = preg_split('#\s+#', trim($declaration));
0718 if ($entities[0] == 'PRIMARY')
0719 {
0720 continue;
0721 }
0722 $column_list[] = $entities[0];
0723 }
0724
0725 // note down the primary key notation because sqlite only supports adding it to the end for the new table
0726 $primary_key = false;
0727 $_new_cols = array();
0728
0729 foreach ($new_table_cols as $key => $declaration)
0730 {
0731 $entities = preg_split('#\s+#', trim($declaration));
0732 if ($entities[0] == 'PRIMARY')
0733 {
0734 $primary_key = $declaration;
0735 continue;
0736 }
0737 $_new_cols[] = $declaration;
0738 }
0739
0740 $new_table_cols = $_new_cols;
0741
0742 // First of all... change columns
0743 if (!empty($sql_schema_changes['change_columns']))
0744 {
0745 foreach ($sql_schema_changes['change_columns'] as $column_sql)
0746 {
0747 foreach ($new_table_cols as $key => $declaration)
0748 {
0749 $entities = preg_split('#\s+#', trim($declaration));
0750 if (strpos($column_sql, $entities[0] . ' ') === 0)
0751 {
0752 $new_table_cols[$key] = $column_sql;
0753 }
0754 }
0755 }
0756 }
0757
0758 if (!empty($sql_schema_changes['add_columns']))
0759 {
0760 foreach ($sql_schema_changes['add_columns'] as $column_sql)
0761 {
0762 $new_table_cols[] = $column_sql;
0763 }
0764 }
0765
0766 // Now drop them...
0767 if (!empty($sql_schema_changes['drop_columns']))
0768 {
0769 foreach ($sql_schema_changes['drop_columns'] as $column_name)
0770 {
0771 // Remove from column list...
0772 $new_column_list = array();
0773 foreach ($column_list as $key => $value)
0774 {
0775 if ($value === $column_name)
0776 {
0777 continue;
0778 }
0779
0780 $new_column_list[] = $value;
0781 }
0782
0783 $column_list = $new_column_list;
0784
0785 // Remove from table...
0786 $_new_cols = array();
0787 foreach ($new_table_cols as $key => $declaration)
0788 {
0789 $entities = preg_split('#\s+#', trim($declaration));
0790 if (strpos($column_name . ' ', $entities[0] . ' ') === 0)
0791 {
0792 continue;
0793 }
0794 $_new_cols[] = $declaration;
0795 }
0796 $new_table_cols = $_new_cols;
0797 }
0798 }
0799
0800 // Primary key...
0801 if (!empty($sql_schema_changes['primary_key']))
0802 {
0803 $new_table_cols[] = 'PRIMARY KEY (' . implode(', ', $sql_schema_changes['primary_key']) . ')';
0804 }
0805 // Add a new one or the old primary key
0806 else if ($primary_key !== false)
0807 {
0808 $new_table_cols[] = $primary_key;
0809 }
0810
0811 $columns = implode(',', $column_list);
0812
0813 // create a new table and fill it up. destroy the temp one
0814 $statements[] = 'CREATE TABLE ' . $table_name . ' (' . implode(',', $new_table_cols) . ');';
0815 $statements[] = 'INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;';
0816 $statements[] = 'DROP TABLE ' . $table_name . '_temp';
0817
0818 $statements[] = 'commit';
0819 }
0820 }
0821
0822 if ($this->return_statements)
0823 {
0824 return $statements;
0825 }
0826 }
0827
0828 /**
0829 * {@inheritDoc}
0830 */
0831 function sql_list_columns($table_name)
0832 {
0833 $columns = array();
0834
0835 switch ($this->sql_layer)
0836 {
0837 case 'mysql_40':
0838 case 'mysql_41':
0839 $sql = "SHOW COLUMNS FROM $table_name";
0840 break;
0841
0842 case 'oracle':
0843 $sql = "SELECT column_name
0844 FROM user_tab_columns
0845 WHERE LOWER(table_name) = '" . strtolower($table_name) . "'";
0846 break;
0847
0848 case 'sqlite3':
0849 $sql = "SELECT sql
0850 FROM sqlite_master
0851 WHERE type = 'table'
0852 AND name = '{$table_name}'";
0853
0854 $result = $this->db->sql_query($sql);
0855
0856 if (!$result)
0857 {
0858 return false;
0859 }
0860
0861 $row = $this->db->sql_fetchrow($result);
0862 $this->db->sql_freeresult($result);
0863
0864 preg_match('#\((.*)\)#s', $row['sql'], $matches);
0865
0866 $cols = trim($matches[1]);
0867 $col_array = preg_split('/,(?![\s\w]+\))/m', $cols);
0868
0869 foreach ($col_array as $declaration)
0870 {
0871 $entities = preg_split('#\s+#', trim($declaration));
0872 if ($entities[0] == 'PRIMARY')
0873 {
0874 continue;
0875 }
0876
0877 $column = strtolower($entities[0]);
0878 $columns[$column] = $column;
0879 }
0880
0881 return $columns;
0882 break;
0883 }
0884
0885 $result = $this->db->sql_query($sql);
0886
0887 while ($row = $this->db->sql_fetchrow($result))
0888 {
0889 $column = strtolower(current($row));
0890 $columns[$column] = $column;
0891 }
0892 $this->db->sql_freeresult($result);
0893
0894 return $columns;
0895 }
0896
0897 /**
0898 * {@inheritDoc}
0899 */
0900 function sql_column_exists($table_name, $column_name)
0901 {
0902 $columns = $this->sql_list_columns($table_name);
0903
0904 return isset($columns[$column_name]);
0905 }
0906
0907 /**
0908 * {@inheritDoc}
0909 */
0910 function sql_index_exists($table_name, $index_name)
0911 {
0912 switch ($this->sql_layer)
0913 {
0914 case 'mysql_40':
0915 case 'mysql_41':
0916 $sql = 'SHOW KEYS
0917 FROM ' . $table_name;
0918 $col = 'Key_name';
0919 break;
0920
0921 case 'oracle':
0922 $sql = "SELECT index_name
0923 FROM user_indexes
0924 WHERE table_name = '" . strtoupper($table_name) . "'
0925 AND generated = 'N'
0926 AND uniqueness = 'NONUNIQUE'";
0927 $col = 'index_name';
0928 break;
0929
0930 case 'sqlite3':
0931 $sql = "PRAGMA index_list('" . $table_name . "');";
0932 $col = 'name';
0933 break;
0934 }
0935
0936 $result = $this->db->sql_query($sql);
0937 while ($row = $this->db->sql_fetchrow($result))
0938 {
0939 if (($this->sql_layer == 'mysql_40' || $this->sql_layer == 'mysql_41') && !$row['Non_unique'])
0940 {
0941 continue;
0942 }
0943
0944 // These DBMS prefix index name with the table name
0945 switch ($this->sql_layer)
0946 {
0947 case 'oracle':
0948 case 'sqlite3':
0949 $row[$col] = substr($row[$col], strlen($table_name) + 1);
0950 break;
0951 }
0952
0953 if (strtolower($row[$col]) == strtolower($index_name))
0954 {
0955 $this->db->sql_freeresult($result);
0956 return true;
0957 }
0958 }
0959 $this->db->sql_freeresult($result);
0960
0961 return false;
0962 }
0963
0964 /**
0965 * {@inheritDoc}
0966 */
0967 function sql_unique_index_exists($table_name, $index_name)
0968 {
0969 switch ($this->sql_layer)
0970 {
0971 case 'mysql_40':
0972 case 'mysql_41':
0973 $sql = 'SHOW KEYS
0974 FROM ' . $table_name;
0975 $col = 'Key_name';
0976 break;
0977
0978 case 'oracle':
0979 $sql = "SELECT index_name, table_owner
0980 FROM user_indexes
0981 WHERE table_name = '" . strtoupper($table_name) . "'
0982 AND generated = 'N'
0983 AND uniqueness = 'UNIQUE'";
0984 $col = 'index_name';
0985 break;
0986
0987 case 'sqlite3':
0988 $sql = "PRAGMA index_list('" . $table_name . "');";
0989 $col = 'name';
0990 break;
0991 }
0992
0993 $result = $this->db->sql_query($sql);
0994 while ($row = $this->db->sql_fetchrow($result))
0995 {
0996 if (($this->sql_layer == 'mysql_40' || $this->sql_layer == 'mysql_41') && ($row['Non_unique'] || $row[$col] == 'PRIMARY'))
0997 {
0998 continue;
0999 }
1000
1001 if ($this->sql_layer == 'sqlite3' && !$row['unique'])
1002 {
1003 continue;
1004 }
1005
1006 // These DBMS prefix index name with the table name
1007 switch ($this->sql_layer)
1008 {
1009 case 'oracle':
1010 // Two cases here... prefixed with U_[table_owner] and not prefixed with table_name
1011 if (strpos($row[$col], 'U_') === 0)
1012 {
1013 $row[$col] = substr($row[$col], strlen('U_' . $row['table_owner']) + 1);
1014 }
1015 else if (strpos($row[$col], strtoupper($table_name)) === 0)
1016 {
1017 $row[$col] = substr($row[$col], strlen($table_name) + 1);
1018 }
1019 break;
1020
1021 case 'sqlite3':
1022 $row[$col] = substr($row[$col], strlen($table_name) + 1);
1023 break;
1024 }
1025
1026 if (strtolower($row[$col]) == strtolower($index_name))
1027 {
1028 $this->db->sql_freeresult($result);
1029 return true;
1030 }
1031 }
1032 $this->db->sql_freeresult($result);
1033
1034 return false;
1035 }
1036
1037 /**
1038 * Private method for performing sql statements (either execute them or return them)
1039 * @access private
1040 */
1041 function _sql_run_sql($statements)
1042 {
1043 if ($this->return_statements)
1044 {
1045 return $statements;
1046 }
1047
1048 // We could add error handling here...
1049 foreach ($statements as $sql)
1050 {
1051 if ($sql === 'begin')
1052 {
1053 $this->db->sql_transaction('begin');
1054 }
1055 else if ($sql === 'commit')
1056 {
1057 $this->db->sql_transaction('commit');
1058 }
1059 else
1060 {
1061 $this->db->sql_query($sql);
1062 }
1063 }
1064
1065 return true;
1066 }
1067
1068 /**
1069 * Function to prepare some column information for better usage
1070 * @access private
1071 */
1072 function sql_prepare_column_data($table_name, $column_name, $column_data)
1073 {
1074 if (strlen($column_name) > 30)
1075 {
1076 trigger_error("Column name '$column_name' on table '$table_name' is too long. The maximum is 30 characters.", E_USER_ERROR);
1077 }
1078
1079 // Get type
1080 list($column_type) = $this->get_column_type($column_data[0]);
1081
1082 // Adjust default value if db-dependent specified
1083 if (is_array($column_data[1]))
1084 {
1085 $column_data[1] = (isset($column_data[1][$this->sql_layer])) ? $column_data[1][$this->sql_layer] : $column_data[1]['default'];
1086 }
1087
1088 $sql = '';
1089
1090 $return_array = array();
1091
1092 switch ($this->sql_layer)
1093 {
1094 case 'mysql_40':
1095 case 'mysql_41':
1096 $sql .= " {$column_type} ";
1097
1098 // For hexadecimal values do not use single quotes
1099 if (!is_null($column_data[1]) && substr($column_type, -4) !== 'text' && substr($column_type, -4) !== 'blob')
1100 {
1101 $sql .= (strpos($column_data[1], '0x') === 0) ? "DEFAULT {$column_data[1]} " : "DEFAULT '{$column_data[1]}' ";
1102 }
1103
1104 if (!is_null($column_data[1]) || (isset($column_data[2]) && $column_data[2] == 'auto_increment'))
1105 {
1106 $sql .= 'NOT NULL';
1107 }
1108 else
1109 {
1110 $sql .= 'NULL';
1111 }
1112
1113 if (isset($column_data[2]))
1114 {
1115 if ($column_data[2] == 'auto_increment')
1116 {
1117 $sql .= ' auto_increment';
1118 }
1119 else if ($this->sql_layer === 'mysql_41' && $column_data[2] == 'true_sort')
1120 {
1121 $sql .= ' COLLATE utf8_unicode_ci';
1122 }
1123 }
1124
1125 if (isset($column_data['after']))
1126 {
1127 $return_array['after'] = $column_data['after'];
1128 }
1129
1130 break;
1131
1132 case 'oracle':
1133 $sql .= " {$column_type} ";
1134 $sql .= (!is_null($column_data[1])) ? "DEFAULT '{$column_data[1]}' " : '';
1135
1136 // In Oracle empty strings ('') are treated as NULL.
1137 // Therefore in oracle we allow NULL's for all DEFAULT '' entries
1138 // Oracle does not like setting NOT NULL on a column that is already NOT NULL (this happens only on number fields)
1139 if (!preg_match('/number/i', $column_type))
1140 {
1141 $sql .= ($column_data[1] === '' || $column_data[1] === null) ? '' : 'NOT NULL';
1142 }
1143
1144 $return_array['auto_increment'] = false;
1145 if (isset($column_data[2]) && $column_data[2] == 'auto_increment')
1146 {
1147 $return_array['auto_increment'] = true;
1148 }
1149
1150 break;
1151
1152 case 'sqlite3':
1153 $return_array['primary_key_set'] = false;
1154 if (isset($column_data[2]) && $column_data[2] == 'auto_increment')
1155 {
1156 $sql .= ' INTEGER PRIMARY KEY AUTOINCREMENT';
1157 $return_array['primary_key_set'] = true;
1158 }
1159 else
1160 {
1161 $sql .= ' ' . $column_type;
1162 }
1163
1164 if (!is_null($column_data[1]))
1165 {
1166 $sql .= ' NOT NULL ';
1167 $sql .= "DEFAULT '{$column_data[1]}'";
1168 }
1169
1170 break;
1171 }
1172
1173 $return_array['column_type_sql'] = $sql;
1174
1175 return $return_array;
1176 }
1177
1178 /**
1179 * Get the column's database type from the type map
1180 *
1181 * @param string $column_map_type
1182 * @return array column type for this database
1183 * and map type without length
1184 */
1185 function get_column_type($column_map_type)
1186 {
1187 $column_type = '';
1188 if (strpos($column_map_type, ':') !== false)
1189 {
1190 list($orig_column_type, $column_length) = explode(':', $column_map_type);
1191 if (!is_array($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']))
1192 {
1193 $column_type = sprintf($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':'], $column_length);
1194 }
1195 else
1196 {
1197 if (isset($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['rule']))
1198 {
1199 switch ($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['rule'][0])
1200 {
1201 case 'div':
1202 $column_length /= $this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['rule'][1];
1203 $column_length = ceil($column_length);
1204 $column_type = sprintf($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':'][0], $column_length);
1205 break;
1206 }
1207 }
1208
1209 if (isset($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['limit']))
1210 {
1211 switch ($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['limit'][0])
1212 {
1213 case 'mult':
1214 $column_length *= $this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['limit'][1];
1215 if ($column_length > $this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['limit'][2])
1216 {
1217 $column_type = $this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['limit'][3];
1218 }
1219 else
1220 {
1221 $column_type = sprintf($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':'][0], $column_length);
1222 }
1223 break;
1224 }
1225 }
1226 }
1227 $orig_column_type .= ':';
1228 }
1229 else
1230 {
1231 $orig_column_type = $column_map_type;
1232 $column_type = $this->dbms_type_map[$this->sql_layer][$column_map_type];
1233 }
1234
1235 return array($column_type, $orig_column_type);
1236 }
1237
1238 /**
1239 * {@inheritDoc}
1240 */
1241 function sql_column_add($table_name, $column_name, $column_data, $inline = false)
1242 {
1243 $column_data = $this->sql_prepare_column_data($table_name, $column_name, $column_data);
1244 $statements = array();
1245
1246 switch ($this->sql_layer)
1247 {
1248 case 'mysql_40':
1249 case 'mysql_41':
1250 $after = (!empty($column_data['after'])) ? ' AFTER ' . $column_data['after'] : '';
1251 $statements[] = 'ALTER TABLE `' . $table_name . '` ADD COLUMN `' . $column_name . '` ' . $column_data['column_type_sql'] . $after;
1252 break;
1253
1254 case 'oracle':
1255 // Does not support AFTER, only through temporary table
1256 $statements[] = 'ALTER TABLE ' . $table_name . ' ADD ' . $column_name . ' ' . $column_data['column_type_sql'];
1257 break;
1258
1259 case 'sqlite3':
1260 if ($inline && $this->return_statements)
1261 {
1262 return $column_name . ' ' . $column_data['column_type_sql'];
1263 }
1264
1265 $statements[] = 'ALTER TABLE ' . $table_name . ' ADD ' . $column_name . ' ' . $column_data['column_type_sql'];
1266 break;
1267 }
1268
1269 return $this->_sql_run_sql($statements);
1270 }
1271
1272 /**
1273 * {@inheritDoc}
1274 */
1275 function sql_column_remove($table_name, $column_name, $inline = false)
1276 {
1277 $statements = array();
1278
1279 switch ($this->sql_layer)
1280 {
1281 case 'mysql_40':
1282 case 'mysql_41':
1283 $statements[] = 'ALTER TABLE `' . $table_name . '` DROP COLUMN `' . $column_name . '`';
1284 break;
1285
1286 case 'oracle':
1287 $statements[] = 'ALTER TABLE ' . $table_name . ' DROP COLUMN ' . $column_name;
1288 break;
1289
1290 case 'sqlite3':
1291
1292 if ($inline && $this->return_statements)
1293 {
1294 return $column_name;
1295 }
1296
1297 $recreate_queries = $this->sqlite_get_recreate_table_queries($table_name, $column_name);
1298 if (empty($recreate_queries))
1299 {
1300 break;
1301 }
1302
1303 $statements[] = 'begin';
1304
1305 $sql_create_table = array_shift($recreate_queries);
1306
1307 // Create a backup table and populate it, destroy the existing one
1308 $statements[] = preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $sql_create_table);
1309 $statements[] = 'INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name;
1310 $statements[] = 'DROP TABLE ' . $table_name;
1311
1312 preg_match('#\((.*)\)#s', $sql_create_table, $matches);
1313
1314 $new_table_cols = trim($matches[1]);
1315 $old_table_cols = preg_split('/,(?![\s\w]+\))/m', $new_table_cols);
1316 $column_list = array();
1317
1318 foreach ($old_table_cols as $declaration)
1319 {
1320 $entities = preg_split('#\s+#', trim($declaration));
1321 if ($entities[0] == 'PRIMARY' || $entities[0] === $column_name)
1322 {
1323 continue;
1324 }
1325 $column_list[] = $entities[0];
1326 }
1327
1328 $columns = implode(',', $column_list);
1329
1330 $new_table_cols = trim(preg_replace('/' . $column_name . '\b[^,]+(?:,|$)/m', '', $new_table_cols));
1331 if (substr($new_table_cols, -1) === ',')
1332 {
1333 // Remove the comma from the last entry again
1334 $new_table_cols = substr($new_table_cols, 0, -1);
1335 }
1336
1337 // create a new table and fill it up. destroy the temp one
1338 $statements[] = 'CREATE TABLE ' . $table_name . ' (' . $new_table_cols . ');';
1339 $statements = array_merge($statements, $recreate_queries);
1340
1341 $statements[] = 'INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;';
1342 $statements[] = 'DROP TABLE ' . $table_name . '_temp';
1343
1344 $statements[] = 'commit';
1345 break;
1346 }
1347
1348 return $this->_sql_run_sql($statements);
1349 }
1350
1351 /**
1352 * {@inheritDoc}
1353 */
1354 function sql_index_drop($table_name, $index_name)
1355 {
1356 $statements = array();
1357
1358 switch ($this->sql_layer)
1359 {
1360 case 'mysql_40':
1361 case 'mysql_41':
1362 $statements[] = 'DROP INDEX ' . $index_name . ' ON ' . $table_name;
1363 break;
1364
1365 case 'oracle':
1366 case 'sqlite3':
1367 $statements[] = 'DROP INDEX ' . $table_name . '_' . $index_name;
1368 break;
1369 }
1370
1371 return $this->_sql_run_sql($statements);
1372 }
1373
1374 /**
1375 * {@inheritDoc}
1376 */
1377 function sql_table_drop($table_name)
1378 {
1379 $statements = array();
1380
1381 if (!$this->sql_table_exists($table_name))
1382 {
1383 return $this->_sql_run_sql($statements);
1384 }
1385
1386 // the most basic operation, get rid of the table
1387 $statements[] = 'DROP TABLE ' . $table_name;
1388
1389 switch ($this->sql_layer)
1390 {
1391 case 'oracle':
1392 $sql = 'SELECT A.REFERENCED_NAME
1393 FROM USER_DEPENDENCIES A, USER_TRIGGERS B
1394 WHERE A.REFERENCED_TYPE = \'SEQUENCE\'
1395 AND A.NAME = B.TRIGGER_NAME
1396 AND B.TABLE_NAME = \'' . strtoupper($table_name) . "'";
1397 $result = $this->db->sql_query($sql);
1398
1399 // any sequences ref'd to this table's triggers?
1400 while ($row = $this->db->sql_fetchrow($result))
1401 {
1402 $statements[] = "DROP SEQUENCE {$row['referenced_name']}";
1403 }
1404 $this->db->sql_freeresult($result);
1405 break;
1406 }
1407
1408 return $this->_sql_run_sql($statements);
1409 }
1410
1411 /**
1412 * {@inheritDoc}
1413 */
1414 function sql_create_primary_key($table_name, $column, $inline = false)
1415 {
1416 $statements = array();
1417
1418 switch ($this->sql_layer)
1419 {
1420 case 'mysql_40':
1421 case 'mysql_41':
1422 $statements[] = 'ALTER TABLE ' . $table_name . ' ADD PRIMARY KEY (' . implode(', ', $column) . ')';
1423 break;
1424
1425 case 'oracle':
1426 $statements[] = 'ALTER TABLE ' . $table_name . ' add CONSTRAINT pk_' . $table_name . ' PRIMARY KEY (' . implode(', ', $column) . ')';
1427 break;
1428
1429 case 'sqlite3':
1430
1431 if ($inline && $this->return_statements)
1432 {
1433 return $column;
1434 }
1435
1436 $recreate_queries = $this->sqlite_get_recreate_table_queries($table_name);
1437 if (empty($recreate_queries))
1438 {
1439 break;
1440 }
1441
1442 $statements[] = 'begin';
1443
1444 $sql_create_table = array_shift($recreate_queries);
1445
1446 // Create a backup table and populate it, destroy the existing one
1447 $statements[] = preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $sql_create_table);
1448 $statements[] = 'INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name;
1449 $statements[] = 'DROP TABLE ' . $table_name;
1450
1451 preg_match('#\((.*)\)#s', $sql_create_table, $matches);
1452
1453 $new_table_cols = trim($matches[1]);
1454 $old_table_cols = preg_split('/,(?![\s\w]+\))/m', $new_table_cols);
1455 $column_list = array();
1456
1457 foreach ($old_table_cols as $declaration)
1458 {
1459 $entities = preg_split('#\s+#', trim($declaration));
1460 if ($entities[0] == 'PRIMARY')
1461 {
1462 continue;
1463 }
1464 $column_list[] = $entities[0];
1465 }
1466
1467 $columns = implode(',', $column_list);
1468
1469 // create a new table and fill it up. destroy the temp one
1470 $statements[] = 'CREATE TABLE ' . $table_name . ' (' . $new_table_cols . ', PRIMARY KEY (' . implode(', ', $column) . '));';
1471 $statements = array_merge($statements, $recreate_queries);
1472
1473 $statements[] = 'INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;';
1474 $statements[] = 'DROP TABLE ' . $table_name . '_temp';
1475
1476 $statements[] = 'commit';
1477 break;
1478 }
1479
1480 return $this->_sql_run_sql($statements);
1481 }
1482
1483 /**
1484 * {@inheritDoc}
1485 */
1486 function sql_create_unique_index($table_name, $index_name, $column)
1487 {
1488 $statements = array();
1489
1490 $this->check_index_name_length($table_name, $index_name);
1491
1492 switch ($this->sql_layer)
1493 {
1494 case 'oracle':
1495 case 'sqlite3':
1496 $statements[] = 'CREATE UNIQUE INDEX ' . $table_name . '_' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ')';
1497 break;
1498
1499 case 'mysql_40':
1500 case 'mysql_41':
1501 $statements[] = 'ALTER TABLE ' . $table_name . ' ADD UNIQUE INDEX ' . $index_name . '(' . implode(', ', $column) . ')';
1502 break;
1503 }
1504
1505 return $this->_sql_run_sql($statements);
1506 }
1507
1508 /**
1509 * {@inheritDoc}
1510 */
1511 function sql_create_index($table_name, $index_name, $column)
1512 {
1513 $statements = array();
1514
1515 $this->check_index_name_length($table_name, $index_name);
1516
1517 // remove index length unless MySQL4
1518 if ('mysql_40' != $this->sql_layer)
1519 {
1520 $column = preg_replace('#:.*$#', '', $column);
1521 }
1522
1523 switch ($this->sql_layer)
1524 {
1525 case 'oracle':
1526 case 'sqlite3':
1527 $statements[] = 'CREATE INDEX ' . $table_name . '_' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ')';
1528 break;
1529
1530 case 'mysql_40':
1531 // add index size to definition as required by MySQL4
1532 foreach ($column as $i => $col)
1533 {
1534 if (false !== strpos($col, ':'))
1535 {
1536 list($col, $index_size) = explode(':', $col);
1537 $column[$i] = "$col($index_size)";
1538 }
1539 }
1540 // no break
1541 case 'mysql_41':
1542 $statements[] = 'ALTER TABLE ' . $table_name . ' ADD INDEX ' . $index_name . ' (' . implode(', ', $column) . ')';
1543 break;
1544 }
1545
1546 return $this->_sql_run_sql($statements);
1547 }
1548
1549 /**
1550 * Check whether the index name is too long
1551 *
1552 * @param string $table_name
1553 * @param string $index_name
1554 */
1555 protected function check_index_name_length($table_name, $index_name)
1556 {
1557 $table_prefix = substr(CONFIG_TABLE, 0, -6); // strlen(config)
1558 if (strlen($table_name . $index_name) - strlen($table_prefix) > 24)
1559 {
1560 $max_length = strlen($table_prefix) + 24;
1561 trigger_error("Index name '{$table_name}_$index_name' on table '$table_name' is too long. The maximum is $max_length characters.", E_USER_ERROR);
1562 }
1563 }
1564
1565 /**
1566 * {@inheritDoc}
1567 */
1568 function sql_list_index($table_name)
1569 {
1570 $index_array = array();
1571
1572 switch ($this->sql_layer)
1573 {
1574 case 'mysql_40':
1575 case 'mysql_41':
1576 $sql = 'SHOW KEYS
1577 FROM ' . $table_name;
1578 $col = 'Key_name';
1579 break;
1580
1581 case 'oracle':
1582 $sql = "SELECT index_name
1583 FROM user_indexes
1584 WHERE table_name = '" . strtoupper($table_name) . "'
1585 AND generated = 'N'
1586 AND uniqueness = 'NONUNIQUE'";
1587 $col = 'index_name';
1588 break;
1589
1590 case 'sqlite3':
1591 $sql = "PRAGMA index_info('" . $table_name . "');";
1592 $col = 'name';
1593 break;
1594 }
1595
1596 $result = $this->db->sql_query($sql);
1597 while ($row = $this->db->sql_fetchrow($result))
1598 {
1599 if (($this->sql_layer == 'mysql_40' || $this->sql_layer == 'mysql_41') && !$row['Non_unique'])
1600 {
1601 continue;
1602 }
1603
1604 switch ($this->sql_layer)
1605 {
1606 case 'oracle':
1607 case 'sqlite3':
1608 $row[$col] = substr($row[$col], strlen($table_name) + 1);
1609 break;
1610 }
1611
1612 $index_array[] = $row[$col];
1613 }
1614 $this->db->sql_freeresult($result);
1615
1616 return array_map('strtolower', $index_array);
1617 }
1618
1619 /**
1620 * Removes table_name from the index_name if it is at the beginning
1621 *
1622 * @param $table_name
1623 * @param $index_name
1624 * @return string
1625 */
1626 protected function strip_table_name_from_index_name($table_name, $index_name)
1627 {
1628 return (strpos(strtoupper($index_name), strtoupper($table_name)) === 0) ? substr($index_name, strlen($table_name) + 1) : $index_name;
1629 }
1630
1631 /**
1632 * {@inheritDoc}
1633 */
1634 function sql_column_change($table_name, $column_name, $column_data, $inline = false)
1635 {
1636 $original_column_data = $column_data;
1637 $column_data = $this->sql_prepare_column_data($table_name, $column_name, $column_data);
1638 $statements = array();
1639
1640 switch ($this->sql_layer)
1641 {
1642 case 'mysql_40':
1643 case 'mysql_41':
1644 $statements[] = 'ALTER TABLE `' . $table_name . '` CHANGE `' . $column_name . '` `' . $column_name . '` ' . $column_data['column_type_sql'];
1645 break;
1646
1647 case 'oracle':
1648 // We need the data here
1649 $old_return_statements = $this->return_statements;
1650 $this->return_statements = true;
1651
1652 // Get list of existing indexes
1653 $indexes = $this->get_existing_indexes($table_name, $column_name);
1654 $unique_indexes = $this->get_existing_indexes($table_name, $column_name, true);
1655
1656 // Drop any indexes
1657 if (!empty($indexes) || !empty($unique_indexes))
1658 {
1659 $drop_indexes = array_merge(array_keys($indexes), array_keys($unique_indexes));
1660 foreach ($drop_indexes as $index_name)
1661 {
1662 $result = $this->sql_index_drop($table_name, $this->strip_table_name_from_index_name($table_name, $index_name));
1663 $statements = array_merge($statements, $result);
1664 }
1665 }
1666
1667 $temp_column_name = 'temp_' . substr(md5($column_name), 0, 25);
1668 // Add a temporary table with the new type
1669 $result = $this->sql_column_add($table_name, $temp_column_name, $original_column_data);
1670 $statements = array_merge($statements, $result);
1671
1672 // Copy the data to the new column
1673 $statements[] = 'UPDATE ' . $table_name . ' SET ' . $temp_column_name . ' = ' . $column_name;
1674
1675 // Drop the original column
1676 $result = $this->sql_column_remove($table_name, $column_name);
1677 $statements = array_merge($statements, $result);
1678
1679 // Recreate the original column with the new type
1680 $result = $this->sql_column_add($table_name, $column_name, $original_column_data);
1681 $statements = array_merge($statements, $result);
1682
1683 if (!empty($indexes))
1684 {
1685 // Recreate indexes after we changed the column
1686 foreach ($indexes as $index_name => $index_data)
1687 {
1688 $result = $this->sql_create_index($table_name, $this->strip_table_name_from_index_name($table_name, $index_name), $index_data);
1689 $statements = array_merge($statements, $result);
1690 }
1691 }
1692
1693 if (!empty($unique_indexes))
1694 {
1695 // Recreate unique indexes after we changed the column
1696 foreach ($unique_indexes as $index_name => $index_data)
1697 {
1698 $result = $this->sql_create_unique_index($table_name, $this->strip_table_name_from_index_name($table_name, $index_name), $index_data);
1699 $statements = array_merge($statements, $result);
1700 }
1701 }
1702
1703 // Copy the data to the original column
1704 $statements[] = 'UPDATE ' . $table_name . ' SET ' . $column_name . ' = ' . $temp_column_name;
1705
1706 // Drop the temporary column again
1707 $result = $this->sql_column_remove($table_name, $temp_column_name);
1708 $statements = array_merge($statements, $result);
1709
1710 $this->return_statements = $old_return_statements;
1711 break;
1712
1713 case 'sqlite3':
1714
1715 if ($inline && $this->return_statements)
1716 {
1717 return $column_name . ' ' . $column_data['column_type_sql'];
1718 }
1719
1720 $recreate_queries = $this->sqlite_get_recreate_table_queries($table_name);
1721 if (empty($recreate_queries))
1722 {
1723 break;
1724 }
1725
1726 $statements[] = 'begin';
1727
1728 $sql_create_table = array_shift($recreate_queries);
1729
1730 // Create a temp table and populate it, destroy the existing one
1731 $statements[] = preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $sql_create_table);
1732 $statements[] = 'INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name;
1733 $statements[] = 'DROP TABLE ' . $table_name;
1734
1735 preg_match('#\((.*)\)#s', $sql_create_table, $matches);
1736
1737 $new_table_cols = trim($matches[1]);
1738 $old_table_cols = preg_split('/,(?![\s\w]+\))/m', $new_table_cols);
1739 $column_list = array();
1740
1741 foreach ($old_table_cols as $key => $declaration)
1742 {
1743 $declaration = trim($declaration);
1744
1745 // Check for the beginning of the constraint section and stop
1746 if (preg_match('/[^\(]*\s*PRIMARY KEY\s+\(/', $declaration) ||
1747 preg_match('/[^\(]*\s*UNIQUE\s+\(/', $declaration) ||
1748 preg_match('/[^\(]*\s*FOREIGN KEY\s+\(/', $declaration) ||
1749 preg_match('/[^\(]*\s*CHECK\s+\(/', $declaration))
1750 {
1751 break;
1752 }
1753
1754 $entities = preg_split('#\s+#', $declaration);
1755 $column_list[] = $entities[0];
1756 if ($entities[0] == $column_name)
1757 {
1758 $old_table_cols[$key] = $column_name . ' ' . $column_data['column_type_sql'];
1759 }
1760 }
1761
1762 $columns = implode(',', $column_list);
1763
1764 // Create a new table and fill it up. destroy the temp one
1765 $statements[] = 'CREATE TABLE ' . $table_name . ' (' . implode(',', $old_table_cols) . ');';
1766 $statements = array_merge($statements, $recreate_queries);
1767
1768 $statements[] = 'INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;';
1769 $statements[] = 'DROP TABLE ' . $table_name . '_temp';
1770
1771 $statements[] = 'commit';
1772
1773 break;
1774 }
1775
1776 return $this->_sql_run_sql($statements);
1777 }
1778
1779 /**
1780 * Get a list with existing indexes for the column
1781 *
1782 * @param string $table_name
1783 * @param string $column_name
1784 * @param bool $unique Should we get unique indexes or normal ones
1785 * @return array Array with Index name => columns
1786 */
1787 public function get_existing_indexes($table_name, $column_name, $unique = false)
1788 {
1789 switch ($this->sql_layer)
1790 {
1791 case 'mysql_40':
1792 case 'mysql_41':
1793 case 'sqlite3':
1794 // Not supported
1795 throw new \Exception('DBMS is not supported');
1796 break;
1797 }
1798
1799 $sql = '';
1800 $existing_indexes = array();
1801
1802 switch ($this->sql_layer)
1803 {
1804 case 'oracle':
1805 $sql = "SELECT ix.index_name AS phpbb_index_name, ix.uniqueness AS is_unique
1806 FROM all_ind_columns ixc, all_indexes ix
1807 WHERE ix.index_name = ixc.index_name
1808 AND ixc.table_name = '" . strtoupper($table_name) . "'
1809 AND ixc.column_name = '" . strtoupper($column_name) . "'";
1810 break;
1811 }
1812
1813 $result = $this->db->sql_query($sql);
1814 while ($row = $this->db->sql_fetchrow($result))
1815 {
1816 if (!isset($row['is_unique']) || ($unique && $row['is_unique'] == 'UNIQUE') || (!$unique && $row['is_unique'] == 'NONUNIQUE'))
1817 {
1818 $existing_indexes[$row['phpbb_index_name']] = array();
1819 }
1820 }
1821 $this->db->sql_freeresult($result);
1822
1823 if (empty($existing_indexes))
1824 {
1825 return array();
1826 }
1827
1828 switch ($this->sql_layer)
1829 {
1830 case 'oracle':
1831 $sql = "SELECT index_name AS phpbb_index_name, column_name AS phpbb_column_name
1832 FROM all_ind_columns
1833 WHERE table_name = '" . strtoupper($table_name) . "'
1834 AND " . $this->db->sql_in_set('index_name', array_keys($existing_indexes));
1835 break;
1836 }
1837
1838 $result = $this->db->sql_query($sql);
1839 while ($row = $this->db->sql_fetchrow($result))
1840 {
1841 $existing_indexes[$row['phpbb_index_name']][] = $row['phpbb_column_name'];
1842 }
1843 $this->db->sql_freeresult($result);
1844
1845 return $existing_indexes;
1846 }
1847
1848 /**
1849 * Returns the Queries which are required to recreate a table including indexes
1850 *
1851 * @param string $table_name
1852 * @param string $remove_column When we drop a column, we remove the column
1853 * from all indexes. If the index has no other
1854 * column, we drop it completly.
1855 * @return array
1856 */
1857 protected function sqlite_get_recreate_table_queries($table_name, $remove_column = '')
1858 {
1859 $queries = array();
1860
1861 $sql = "SELECT sql
1862 FROM sqlite_master
1863 WHERE type = 'table'
1864 AND name = '{$table_name}'";
1865 $result = $this->db->sql_query($sql);
1866 $sql_create_table = $this->db->sql_fetchfield('sql');
1867 $this->db->sql_freeresult($result);
1868
1869 if (!$sql_create_table)
1870 {
1871 return array();
1872 }
1873 $queries[] = $sql_create_table;
1874
1875 $sql = "SELECT sql
1876 FROM sqlite_master
1877 WHERE type = 'index'
1878 AND tbl_name = '{$table_name}'";
1879 $result = $this->db->sql_query($sql);
1880 while ($sql_create_index = $this->db->sql_fetchfield('sql'))
1881 {
1882 if ($remove_column)
1883 {
1884 $match = array();
1885 preg_match('#(?:[\w ]+)\((.*)\)#', $sql_create_index, $match);
1886 if (!isset($match[1]))
1887 {
1888 continue;
1889 }
1890
1891 // Find and remove $remove_column from the index
1892 $columns = explode(', ', $match[1]);
1893 $found_column = array_search($remove_column, $columns);
1894 if ($found_column !== false)
1895 {
1896 unset($columns[$found_column]);
1897
1898 // If the column list is not empty add the index to the list
1899 if (!empty($columns))
1900 {
1901 $queries[] = str_replace($match[1], implode(', ', $columns), $sql_create_index);
1902 }
1903 }
1904 else
1905 {
1906 $queries[] = $sql_create_index;
1907 }
1908 }
1909 else
1910 {
1911 $queries[] = $sql_create_index;
1912 }
1913 }
1914 $this->db->sql_freeresult($result);
1915
1916 return $queries;
1917 }
1918 }
1919