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