Verzeichnisstruktur phpBB-3.1.0
- Veröffentlicht
- 27.10.2014
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;
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
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 * Is the used MS SQL Server a SQL Server 2000?
0040 * @var bool
0041 */
0042 protected $is_sql_server_2000;
0043
0044 /**
0045 * Get the column types for every database we support
0046 *
0047 * @return array
0048 */
0049 public static function get_dbms_type_map()
0050 {
0051 return array(
0052 'mysql_41' => array(
0053 'INT:' => 'int(%d)',
0054 'BINT' => 'bigint(20)',
0055 'UINT' => 'mediumint(8) UNSIGNED',
0056 'UINT:' => 'int(%d) UNSIGNED',
0057 'TINT:' => 'tinyint(%d)',
0058 'USINT' => 'smallint(4) UNSIGNED',
0059 'BOOL' => 'tinyint(1) UNSIGNED',
0060 'VCHAR' => 'varchar(255)',
0061 'VCHAR:' => 'varchar(%d)',
0062 'CHAR:' => 'char(%d)',
0063 'XSTEXT' => 'text',
0064 'XSTEXT_UNI'=> 'varchar(100)',
0065 'STEXT' => 'text',
0066 'STEXT_UNI' => 'varchar(255)',
0067 'TEXT' => 'text',
0068 'TEXT_UNI' => 'text',
0069 'MTEXT' => 'mediumtext',
0070 'MTEXT_UNI' => 'mediumtext',
0071 'TIMESTAMP' => 'int(11) UNSIGNED',
0072 'DECIMAL' => 'decimal(5,2)',
0073 'DECIMAL:' => 'decimal(%d,2)',
0074 'PDECIMAL' => 'decimal(6,3)',
0075 'PDECIMAL:' => 'decimal(%d,3)',
0076 'VCHAR_UNI' => 'varchar(255)',
0077 'VCHAR_UNI:'=> 'varchar(%d)',
0078 'VCHAR_CI' => 'varchar(255)',
0079 'VARBINARY' => 'varbinary(255)',
0080 ),
0081
0082 'mysql_40' => array(
0083 'INT:' => 'int(%d)',
0084 'BINT' => 'bigint(20)',
0085 'UINT' => 'mediumint(8) UNSIGNED',
0086 'UINT:' => 'int(%d) UNSIGNED',
0087 'TINT:' => 'tinyint(%d)',
0088 'USINT' => 'smallint(4) UNSIGNED',
0089 'BOOL' => 'tinyint(1) UNSIGNED',
0090 'VCHAR' => 'varbinary(255)',
0091 'VCHAR:' => 'varbinary(%d)',
0092 'CHAR:' => 'binary(%d)',
0093 'XSTEXT' => 'blob',
0094 'XSTEXT_UNI'=> 'blob',
0095 'STEXT' => 'blob',
0096 'STEXT_UNI' => 'blob',
0097 'TEXT' => 'blob',
0098 'TEXT_UNI' => 'blob',
0099 'MTEXT' => 'mediumblob',
0100 'MTEXT_UNI' => 'mediumblob',
0101 'TIMESTAMP' => 'int(11) UNSIGNED',
0102 'DECIMAL' => 'decimal(5,2)',
0103 'DECIMAL:' => 'decimal(%d,2)',
0104 'PDECIMAL' => 'decimal(6,3)',
0105 'PDECIMAL:' => 'decimal(%d,3)',
0106 'VCHAR_UNI' => 'blob',
0107 'VCHAR_UNI:'=> array('varbinary(%d)', 'limit' => array('mult', 3, 255, 'blob')),
0108 'VCHAR_CI' => 'blob',
0109 'VARBINARY' => 'varbinary(255)',
0110 ),
0111
0112 'mssql' => array(
0113 'INT:' => '[int]',
0114 'BINT' => '[float]',
0115 'UINT' => '[int]',
0116 'UINT:' => '[int]',
0117 'TINT:' => '[int]',
0118 'USINT' => '[int]',
0119 'BOOL' => '[int]',
0120 'VCHAR' => '[varchar] (255)',
0121 'VCHAR:' => '[varchar] (%d)',
0122 'CHAR:' => '[char] (%d)',
0123 'XSTEXT' => '[varchar] (1000)',
0124 'STEXT' => '[varchar] (3000)',
0125 'TEXT' => '[varchar] (8000)',
0126 'MTEXT' => '[text]',
0127 'XSTEXT_UNI'=> '[varchar] (100)',
0128 'STEXT_UNI' => '[varchar] (255)',
0129 'TEXT_UNI' => '[varchar] (4000)',
0130 'MTEXT_UNI' => '[text]',
0131 'TIMESTAMP' => '[int]',
0132 'DECIMAL' => '[float]',
0133 'DECIMAL:' => '[float]',
0134 'PDECIMAL' => '[float]',
0135 'PDECIMAL:' => '[float]',
0136 'VCHAR_UNI' => '[varchar] (255)',
0137 'VCHAR_UNI:'=> '[varchar] (%d)',
0138 'VCHAR_CI' => '[varchar] (255)',
0139 'VARBINARY' => '[varchar] (255)',
0140 ),
0141
0142 'mssqlnative' => array(
0143 'INT:' => '[int]',
0144 'BINT' => '[float]',
0145 'UINT' => '[int]',
0146 'UINT:' => '[int]',
0147 'TINT:' => '[int]',
0148 'USINT' => '[int]',
0149 'BOOL' => '[int]',
0150 'VCHAR' => '[varchar] (255)',
0151 'VCHAR:' => '[varchar] (%d)',
0152 'CHAR:' => '[char] (%d)',
0153 'XSTEXT' => '[varchar] (1000)',
0154 'STEXT' => '[varchar] (3000)',
0155 'TEXT' => '[varchar] (8000)',
0156 'MTEXT' => '[text]',
0157 'XSTEXT_UNI'=> '[varchar] (100)',
0158 'STEXT_UNI' => '[varchar] (255)',
0159 'TEXT_UNI' => '[varchar] (4000)',
0160 'MTEXT_UNI' => '[text]',
0161 'TIMESTAMP' => '[int]',
0162 'DECIMAL' => '[float]',
0163 'DECIMAL:' => '[float]',
0164 'PDECIMAL' => '[float]',
0165 'PDECIMAL:' => '[float]',
0166 'VCHAR_UNI' => '[varchar] (255)',
0167 'VCHAR_UNI:'=> '[varchar] (%d)',
0168 'VCHAR_CI' => '[varchar] (255)',
0169 'VARBINARY' => '[varchar] (255)',
0170 ),
0171
0172 'oracle' => array(
0173 'INT:' => 'number(%d)',
0174 'BINT' => 'number(20)',
0175 'UINT' => 'number(8)',
0176 'UINT:' => 'number(%d)',
0177 'TINT:' => 'number(%d)',
0178 'USINT' => 'number(4)',
0179 'BOOL' => 'number(1)',
0180 'VCHAR' => 'varchar2(255)',
0181 'VCHAR:' => 'varchar2(%d)',
0182 'CHAR:' => 'char(%d)',
0183 'XSTEXT' => 'varchar2(1000)',
0184 'STEXT' => 'varchar2(3000)',
0185 'TEXT' => 'clob',
0186 'MTEXT' => 'clob',
0187 'XSTEXT_UNI'=> 'varchar2(300)',
0188 'STEXT_UNI' => 'varchar2(765)',
0189 'TEXT_UNI' => 'clob',
0190 'MTEXT_UNI' => 'clob',
0191 'TIMESTAMP' => 'number(11)',
0192 'DECIMAL' => 'number(5, 2)',
0193 'DECIMAL:' => 'number(%d, 2)',
0194 'PDECIMAL' => 'number(6, 3)',
0195 'PDECIMAL:' => 'number(%d, 3)',
0196 'VCHAR_UNI' => 'varchar2(765)',
0197 'VCHAR_UNI:'=> array('varchar2(%d)', 'limit' => array('mult', 3, 765, 'clob')),
0198 'VCHAR_CI' => 'varchar2(255)',
0199 'VARBINARY' => 'raw(255)',
0200 ),
0201
0202 'sqlite' => array(
0203 'INT:' => 'int(%d)',
0204 'BINT' => 'bigint(20)',
0205 'UINT' => 'INTEGER UNSIGNED', //'mediumint(8) UNSIGNED',
0206 'UINT:' => 'INTEGER UNSIGNED', // 'int(%d) UNSIGNED',
0207 'TINT:' => 'tinyint(%d)',
0208 'USINT' => 'INTEGER UNSIGNED', //'mediumint(4) UNSIGNED',
0209 'BOOL' => 'INTEGER UNSIGNED', //'tinyint(1) UNSIGNED',
0210 'VCHAR' => 'varchar(255)',
0211 'VCHAR:' => 'varchar(%d)',
0212 'CHAR:' => 'char(%d)',
0213 'XSTEXT' => 'text(65535)',
0214 'STEXT' => 'text(65535)',
0215 'TEXT' => 'text(65535)',
0216 'MTEXT' => 'mediumtext(16777215)',
0217 'XSTEXT_UNI'=> 'text(65535)',
0218 'STEXT_UNI' => 'text(65535)',
0219 'TEXT_UNI' => 'text(65535)',
0220 'MTEXT_UNI' => 'mediumtext(16777215)',
0221 'TIMESTAMP' => 'INTEGER UNSIGNED', //'int(11) UNSIGNED',
0222 'DECIMAL' => 'decimal(5,2)',
0223 'DECIMAL:' => 'decimal(%d,2)',
0224 'PDECIMAL' => 'decimal(6,3)',
0225 'PDECIMAL:' => 'decimal(%d,3)',
0226 'VCHAR_UNI' => 'varchar(255)',
0227 'VCHAR_UNI:'=> 'varchar(%d)',
0228 'VCHAR_CI' => 'varchar(255)',
0229 'VARBINARY' => 'blob',
0230 ),
0231
0232 'sqlite3' => array(
0233 'INT:' => 'INT(%d)',
0234 'BINT' => 'BIGINT(20)',
0235 'UINT' => 'INTEGER UNSIGNED',
0236 'UINT:' => 'INTEGER UNSIGNED',
0237 'TINT:' => 'TINYINT(%d)',
0238 'USINT' => 'INTEGER UNSIGNED',
0239 'BOOL' => 'INTEGER UNSIGNED',
0240 'VCHAR' => 'VARCHAR(255)',
0241 'VCHAR:' => 'VARCHAR(%d)',
0242 'CHAR:' => 'CHAR(%d)',
0243 'XSTEXT' => 'TEXT(65535)',
0244 'STEXT' => 'TEXT(65535)',
0245 'TEXT' => 'TEXT(65535)',
0246 'MTEXT' => 'MEDIUMTEXT(16777215)',
0247 'XSTEXT_UNI'=> 'TEXT(65535)',
0248 'STEXT_UNI' => 'TEXT(65535)',
0249 'TEXT_UNI' => 'TEXT(65535)',
0250 'MTEXT_UNI' => 'MEDIUMTEXT(16777215)',
0251 'TIMESTAMP' => 'INTEGER UNSIGNED', //'int(11) UNSIGNED',
0252 'DECIMAL' => 'DECIMAL(5,2)',
0253 'DECIMAL:' => 'DECIMAL(%d,2)',
0254 'PDECIMAL' => 'DECIMAL(6,3)',
0255 'PDECIMAL:' => 'DECIMAL(%d,3)',
0256 'VCHAR_UNI' => 'VARCHAR(255)',
0257 'VCHAR_UNI:'=> 'VARCHAR(%d)',
0258 'VCHAR_CI' => 'VARCHAR(255)',
0259 'VARBINARY' => 'BLOB',
0260 ),
0261
0262 'postgres' => array(
0263 'INT:' => 'INT4',
0264 'BINT' => 'INT8',
0265 'UINT' => 'INT4', // unsigned
0266 'UINT:' => 'INT4', // unsigned
0267 'USINT' => 'INT2', // unsigned
0268 'BOOL' => 'INT2', // unsigned
0269 'TINT:' => 'INT2',
0270 'VCHAR' => 'varchar(255)',
0271 'VCHAR:' => 'varchar(%d)',
0272 'CHAR:' => 'char(%d)',
0273 'XSTEXT' => 'varchar(1000)',
0274 'STEXT' => 'varchar(3000)',
0275 'TEXT' => 'varchar(8000)',
0276 'MTEXT' => 'TEXT',
0277 'XSTEXT_UNI'=> 'varchar(100)',
0278 'STEXT_UNI' => 'varchar(255)',
0279 'TEXT_UNI' => 'varchar(4000)',
0280 'MTEXT_UNI' => 'TEXT',
0281 'TIMESTAMP' => 'INT4', // unsigned
0282 'DECIMAL' => 'decimal(5,2)',
0283 'DECIMAL:' => 'decimal(%d,2)',
0284 'PDECIMAL' => 'decimal(6,3)',
0285 'PDECIMAL:' => 'decimal(%d,3)',
0286 'VCHAR_UNI' => 'varchar(255)',
0287 'VCHAR_UNI:'=> 'varchar(%d)',
0288 'VCHAR_CI' => 'varchar_ci',
0289 'VARBINARY' => 'bytea',
0290 ),
0291 );
0292 }
0293
0294 /**
0295 * A list of types being unsigned for better reference in some db's
0296 * @var array
0297 */
0298 var $unsigned_types = array('UINT', 'UINT:', 'USINT', 'BOOL', 'TIMESTAMP');
0299
0300 /**
0301 * A list of supported DBMS. We change this class to support more DBMS, the DBMS itself only need to follow some rules.
0302 * @var array
0303 */
0304 var $supported_dbms = array('mssql', 'mssqlnative', 'mysql_40', 'mysql_41', 'oracle', 'postgres', 'sqlite', 'sqlite3');
0305
0306 /**
0307 * This is set to true if user only wants to return the 'to-be-executed' SQL statement(s) (as an array).
0308 * This mode has no effect on some methods (inserting of data for example). This is expressed within the methods command.
0309 */
0310 var $return_statements = false;
0311
0312 /**
0313 * Constructor. Set DB Object and set {@link $return_statements return_statements}.
0314 *
0315 * @param \phpbb\db\driver\driver_interface $db Database connection
0316 * @param bool $return_statements True if only statements should be returned and no SQL being executed
0317 */
0318 public function __construct(\phpbb\db\driver\driver_interface $db, $return_statements = false)
0319 {
0320 $this->db = $db;
0321 $this->return_statements = $return_statements;
0322
0323 $this->dbms_type_map = self::get_dbms_type_map();
0324
0325 // Determine mapping database type
0326 switch ($this->db->get_sql_layer())
0327 {
0328 case 'mysql':
0329 $this->sql_layer = 'mysql_40';
0330 break;
0331
0332 case 'mysql4':
0333 if (version_compare($this->db->sql_server_info(true), '4.1.3', '>='))
0334 {
0335 $this->sql_layer = 'mysql_41';
0336 }
0337 else
0338 {
0339 $this->sql_layer = 'mysql_40';
0340 }
0341 break;
0342
0343 case 'mysqli':
0344 $this->sql_layer = 'mysql_41';
0345 break;
0346
0347 case 'mssql':
0348 case 'mssql_odbc':
0349 $this->sql_layer = 'mssql';
0350 break;
0351
0352 case 'mssqlnative':
0353 $this->sql_layer = 'mssqlnative';
0354 break;
0355
0356 default:
0357 $this->sql_layer = $this->db->get_sql_layer();
0358 break;
0359 }
0360 }
0361
0362 /**
0363 * Setter for {@link $return_statements return_statements}.
0364 *
0365 * @param bool $return_statements True if SQL should not be executed but returned as strings
0366 * @return null
0367 */
0368 public function set_return_statements($return_statements)
0369 {
0370 $this->return_statements = $return_statements;
0371 }
0372
0373 /**
0374 * Gets a list of tables in the database.
0375 *
0376 * @return array Array of table names (all lower case)
0377 */
0378 function sql_list_tables()
0379 {
0380 switch ($this->db->get_sql_layer())
0381 {
0382 case 'mysql':
0383 case 'mysql4':
0384 case 'mysqli':
0385 $sql = 'SHOW TABLES';
0386 break;
0387
0388 case 'sqlite':
0389 $sql = 'SELECT name
0390 FROM sqlite_master
0391 WHERE type = "table"';
0392 break;
0393
0394 case 'sqlite3':
0395 $sql = 'SELECT name
0396 FROM sqlite_master
0397 WHERE type = "table"
0398 AND name <> "sqlite_sequence"';
0399 break;
0400
0401 case 'mssql':
0402 case 'mssql_odbc':
0403 case 'mssqlnative':
0404 $sql = "SELECT name
0405 FROM sysobjects
0406 WHERE type='U'";
0407 break;
0408
0409 case 'postgres':
0410 $sql = 'SELECT relname
0411 FROM pg_stat_user_tables';
0412 break;
0413
0414 case 'oracle':
0415 $sql = 'SELECT table_name
0416 FROM USER_TABLES';
0417 break;
0418 }
0419
0420 $result = $this->db->sql_query($sql);
0421
0422 $tables = array();
0423 while ($row = $this->db->sql_fetchrow($result))
0424 {
0425 $name = current($row);
0426 $tables[$name] = $name;
0427 }
0428 $this->db->sql_freeresult($result);
0429
0430 return $tables;
0431 }
0432
0433 /**
0434 * Check if table exists
0435 *
0436 *
0437 * @param string $table_name The table name to check for
0438 * @return bool true if table exists, else false
0439 */
0440 function sql_table_exists($table_name)
0441 {
0442 $this->db->sql_return_on_error(true);
0443 $result = $this->db->sql_query_limit('SELECT * FROM ' . $table_name, 1);
0444 $this->db->sql_return_on_error(false);
0445
0446 if ($result)
0447 {
0448 $this->db->sql_freeresult($result);
0449 return true;
0450 }
0451
0452 return false;
0453 }
0454
0455 /**
0456 * Create SQL Table
0457 *
0458 * @param string $table_name The table name to create
0459 * @param array $table_data Array containing table data.
0460 * @return array Statements if $return_statements is true.
0461 */
0462 function sql_create_table($table_name, $table_data)
0463 {
0464 // holds the DDL for a column
0465 $columns = $statements = array();
0466
0467 if ($this->sql_table_exists($table_name))
0468 {
0469 return $this->_sql_run_sql($statements);
0470 }
0471
0472 // Begin transaction
0473 $statements[] = 'begin';
0474
0475 // Determine if we have created a PRIMARY KEY in the earliest
0476 $primary_key_gen = false;
0477
0478 // Determine if the table requires a sequence
0479 $create_sequence = false;
0480
0481 // Begin table sql statement
0482 switch ($this->sql_layer)
0483 {
0484 case 'mssql':
0485 case 'mssqlnative':
0486 $table_sql = 'CREATE TABLE [' . $table_name . '] (' . "\n";
0487 break;
0488
0489 default:
0490 $table_sql = 'CREATE TABLE ' . $table_name . ' (' . "\n";
0491 break;
0492 }
0493
0494 if ($this->sql_layer == 'mssql' || $this->sql_layer == 'mssqlnative')
0495 {
0496 if (!isset($table_data['PRIMARY_KEY']))
0497 {
0498 $table_data['COLUMNS']['mssqlindex'] = array('UINT', null, 'auto_increment');
0499 $table_data['PRIMARY_KEY'] = 'mssqlindex';
0500 }
0501 }
0502
0503 // Iterate through the columns to create a table
0504 foreach ($table_data['COLUMNS'] as $column_name => $column_data)
0505 {
0506 // here lies an array, filled with information compiled on the column's data
0507 $prepared_column = $this->sql_prepare_column_data($table_name, $column_name, $column_data);
0508
0509 if (isset($prepared_column['auto_increment']) && $prepared_column['auto_increment'] && strlen($column_name) > 26) // "${column_name}_gen"
0510 {
0511 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);
0512 }
0513
0514 // here we add the definition of the new column to the list of columns
0515 switch ($this->sql_layer)
0516 {
0517 case 'mssql':
0518 case 'mssqlnative':
0519 $columns[] = "\t [{$column_name}] " . $prepared_column['column_type_sql_default'];
0520 break;
0521
0522 default:
0523 $columns[] = "\t {$column_name} " . $prepared_column['column_type_sql'];
0524 break;
0525 }
0526
0527 // see if we have found a primary key set due to a column definition if we have found it, we can stop looking
0528 if (!$primary_key_gen)
0529 {
0530 $primary_key_gen = isset($prepared_column['primary_key_set']) && $prepared_column['primary_key_set'];
0531 }
0532
0533 // create sequence DDL based off of the existance of auto incrementing columns
0534 if (!$create_sequence && isset($prepared_column['auto_increment']) && $prepared_column['auto_increment'])
0535 {
0536 $create_sequence = $column_name;
0537 }
0538 }
0539
0540 // this makes up all the columns in the create table statement
0541 $table_sql .= implode(",\n", $columns);
0542
0543 // Close the table for two DBMS and add to the statements
0544 switch ($this->sql_layer)
0545 {
0546 case 'mssql':
0547 case 'mssqlnative':
0548 $table_sql .= "\n);";
0549 $statements[] = $table_sql;
0550 break;
0551 }
0552
0553 // we have yet to create a primary key for this table,
0554 // this means that we can add the one we really wanted instead
0555 if (!$primary_key_gen)
0556 {
0557 // Write primary key
0558 if (isset($table_data['PRIMARY_KEY']))
0559 {
0560 if (!is_array($table_data['PRIMARY_KEY']))
0561 {
0562 $table_data['PRIMARY_KEY'] = array($table_data['PRIMARY_KEY']);
0563 }
0564
0565 switch ($this->sql_layer)
0566 {
0567 case 'mysql_40':
0568 case 'mysql_41':
0569 case 'postgres':
0570 case 'sqlite':
0571 case 'sqlite3':
0572 $table_sql .= ",\n\t PRIMARY KEY (" . implode(', ', $table_data['PRIMARY_KEY']) . ')';
0573 break;
0574
0575 case 'mssql':
0576 case 'mssqlnative':
0577 // We need the data here
0578 $old_return_statements = $this->return_statements;
0579 $this->return_statements = true;
0580
0581 $primary_key_stmts = $this->sql_create_primary_key($table_name, $table_data['PRIMARY_KEY']);
0582 foreach ($primary_key_stmts as $pk_stmt)
0583 {
0584 $statements[] = $pk_stmt;
0585 }
0586
0587 $this->return_statements = $old_return_statements;
0588 break;
0589
0590 case 'oracle':
0591 $table_sql .= ",\n\t CONSTRAINT pk_{$table_name} PRIMARY KEY (" . implode(', ', $table_data['PRIMARY_KEY']) . ')';
0592 break;
0593 }
0594 }
0595 }
0596
0597 // close the table
0598 switch ($this->sql_layer)
0599 {
0600 case 'mysql_41':
0601 // make sure the table is in UTF-8 mode
0602 $table_sql .= "\n) CHARACTER SET `utf8` COLLATE `utf8_bin`;";
0603 $statements[] = $table_sql;
0604 break;
0605
0606 case 'mysql_40':
0607 case 'sqlite':
0608 case 'sqlite3':
0609 $table_sql .= "\n);";
0610 $statements[] = $table_sql;
0611 break;
0612
0613 case 'postgres':
0614 // do we need to add a sequence for auto incrementing columns?
0615 if ($create_sequence)
0616 {
0617 $statements[] = "CREATE SEQUENCE {$table_name}_seq;";
0618 }
0619
0620 $table_sql .= "\n);";
0621 $statements[] = $table_sql;
0622 break;
0623
0624 case 'oracle':
0625 $table_sql .= "\n)";
0626 $statements[] = $table_sql;
0627
0628 // do we need to add a sequence and a tigger for auto incrementing columns?
0629 if ($create_sequence)
0630 {
0631 // create the actual sequence
0632 $statements[] = "CREATE SEQUENCE {$table_name}_seq";
0633
0634 // the trigger is the mechanism by which we increment the counter
0635 $trigger = "CREATE OR REPLACE TRIGGER t_{$table_name}\n";
0636 $trigger .= "BEFORE INSERT ON {$table_name}\n";
0637 $trigger .= "FOR EACH ROW WHEN (\n";
0638 $trigger .= "\tnew.{$create_sequence} IS NULL OR new.{$create_sequence} = 0\n";
0639 $trigger .= ")\n";
0640 $trigger .= "BEGIN\n";
0641 $trigger .= "\tSELECT {$table_name}_seq.nextval\n";
0642 $trigger .= "\tINTO :new.{$create_sequence}\n";
0643 $trigger .= "\tFROM dual;\n";
0644 $trigger .= "END;";
0645
0646 $statements[] = $trigger;
0647 }
0648 break;
0649 }
0650
0651 // Write Keys
0652 if (isset($table_data['KEYS']))
0653 {
0654 foreach ($table_data['KEYS'] as $key_name => $key_data)
0655 {
0656 if (!is_array($key_data[1]))
0657 {
0658 $key_data[1] = array($key_data[1]);
0659 }
0660
0661 $old_return_statements = $this->return_statements;
0662 $this->return_statements = true;
0663
0664 $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]);
0665
0666 foreach ($key_stmts as $key_stmt)
0667 {
0668 $statements[] = $key_stmt;
0669 }
0670
0671 $this->return_statements = $old_return_statements;
0672 }
0673 }
0674
0675 // Commit Transaction
0676 $statements[] = 'commit';
0677
0678 return $this->_sql_run_sql($statements);
0679 }
0680
0681 /**
0682 * Handle passed database update array.
0683 * Expected structure...
0684 * Key being one of the following
0685 * drop_tables: Drop tables
0686 * add_tables: Add tables
0687 * change_columns: Column changes (only type, not name)
0688 * add_columns: Add columns to a table
0689 * drop_keys: Dropping keys
0690 * drop_columns: Removing/Dropping columns
0691 * add_primary_keys: adding primary keys
0692 * add_unique_index: adding an unique index
0693 * add_index: adding an index (can be column:index_size if you need to provide size)
0694 *
0695 * The values are in this format:
0696 * {TABLE NAME} => array(
0697 * {COLUMN NAME} => array({COLUMN TYPE}, {DEFAULT VALUE}, {OPTIONAL VARIABLES}),
0698 * {KEY/INDEX NAME} => array({COLUMN NAMES}),
0699 * )
0700 *
0701 * For more information have a look at /develop/create_schema_files.php (only available through SVN)
0702 */
0703 function perform_schema_changes($schema_changes)
0704 {
0705 if (empty($schema_changes))
0706 {
0707 return;
0708 }
0709
0710 $statements = array();
0711 $sqlite = false;
0712
0713 // For SQLite we need to perform the schema changes in a much more different way
0714 if (($this->db->get_sql_layer() == 'sqlite' || $this->db->get_sql_layer() == 'sqlite3') && $this->return_statements)
0715 {
0716 $sqlite_data = array();
0717 $sqlite = true;
0718 }
0719
0720 // Drop tables?
0721 if (!empty($schema_changes['drop_tables']))
0722 {
0723 foreach ($schema_changes['drop_tables'] as $table)
0724 {
0725 // only drop table if it exists
0726 if ($this->sql_table_exists($table))
0727 {
0728 $result = $this->sql_table_drop($table);
0729 if ($this->return_statements)
0730 {
0731 $statements = array_merge($statements, $result);
0732 }
0733 }
0734 }
0735 }
0736
0737 // Add tables?
0738 if (!empty($schema_changes['add_tables']))
0739 {
0740 foreach ($schema_changes['add_tables'] as $table => $table_data)
0741 {
0742 $result = $this->sql_create_table($table, $table_data);
0743 if ($this->return_statements)
0744 {
0745 $statements = array_merge($statements, $result);
0746 }
0747 }
0748 }
0749
0750 // Change columns?
0751 if (!empty($schema_changes['change_columns']))
0752 {
0753 foreach ($schema_changes['change_columns'] as $table => $columns)
0754 {
0755 foreach ($columns as $column_name => $column_data)
0756 {
0757 // If the column exists we change it, else we add it ;)
0758 if ($column_exists = $this->sql_column_exists($table, $column_name))
0759 {
0760 $result = $this->sql_column_change($table, $column_name, $column_data, true);
0761 }
0762 else
0763 {
0764 $result = $this->sql_column_add($table, $column_name, $column_data, true);
0765 }
0766
0767 if ($sqlite)
0768 {
0769 if ($column_exists)
0770 {
0771 $sqlite_data[$table]['change_columns'][] = $result;
0772 }
0773 else
0774 {
0775 $sqlite_data[$table]['add_columns'][] = $result;
0776 }
0777 }
0778 else if ($this->return_statements)
0779 {
0780 $statements = array_merge($statements, $result);
0781 }
0782 }
0783 }
0784 }
0785
0786 // Add columns?
0787 if (!empty($schema_changes['add_columns']))
0788 {
0789 foreach ($schema_changes['add_columns'] as $table => $columns)
0790 {
0791 foreach ($columns as $column_name => $column_data)
0792 {
0793 // Only add the column if it does not exist yet
0794 if ($column_exists = $this->sql_column_exists($table, $column_name))
0795 {
0796 continue;
0797 // This is commented out here because it can take tremendous time on updates
0798 // $result = $this->sql_column_change($table, $column_name, $column_data, true);
0799 }
0800 else
0801 {
0802 $result = $this->sql_column_add($table, $column_name, $column_data, true);
0803 }
0804
0805 if ($sqlite)
0806 {
0807 if ($column_exists)
0808 {
0809 continue;
0810 // $sqlite_data[$table]['change_columns'][] = $result;
0811 }
0812 else
0813 {
0814 $sqlite_data[$table]['add_columns'][] = $result;
0815 }
0816 }
0817 else if ($this->return_statements)
0818 {
0819 $statements = array_merge($statements, $result);
0820 }
0821 }
0822 }
0823 }
0824
0825 // Remove keys?
0826 if (!empty($schema_changes['drop_keys']))
0827 {
0828 foreach ($schema_changes['drop_keys'] as $table => $indexes)
0829 {
0830 foreach ($indexes as $index_name)
0831 {
0832 if (!$this->sql_index_exists($table, $index_name))
0833 {
0834 continue;
0835 }
0836
0837 $result = $this->sql_index_drop($table, $index_name);
0838
0839 if ($this->return_statements)
0840 {
0841 $statements = array_merge($statements, $result);
0842 }
0843 }
0844 }
0845 }
0846
0847 // Drop columns?
0848 if (!empty($schema_changes['drop_columns']))
0849 {
0850 foreach ($schema_changes['drop_columns'] as $table => $columns)
0851 {
0852 foreach ($columns as $column)
0853 {
0854 // Only remove the column if it exists...
0855 if ($this->sql_column_exists($table, $column))
0856 {
0857 $result = $this->sql_column_remove($table, $column, true);
0858
0859 if ($sqlite)
0860 {
0861 $sqlite_data[$table]['drop_columns'][] = $result;
0862 }
0863 else if ($this->return_statements)
0864 {
0865 $statements = array_merge($statements, $result);
0866 }
0867 }
0868 }
0869 }
0870 }
0871
0872 // Add primary keys?
0873 if (!empty($schema_changes['add_primary_keys']))
0874 {
0875 foreach ($schema_changes['add_primary_keys'] as $table => $columns)
0876 {
0877 $result = $this->sql_create_primary_key($table, $columns, true);
0878
0879 if ($sqlite)
0880 {
0881 $sqlite_data[$table]['primary_key'] = $result;
0882 }
0883 else if ($this->return_statements)
0884 {
0885 $statements = array_merge($statements, $result);
0886 }
0887 }
0888 }
0889
0890 // Add unique indexes?
0891 if (!empty($schema_changes['add_unique_index']))
0892 {
0893 foreach ($schema_changes['add_unique_index'] as $table => $index_array)
0894 {
0895 foreach ($index_array as $index_name => $column)
0896 {
0897 if ($this->sql_unique_index_exists($table, $index_name))
0898 {
0899 continue;
0900 }
0901
0902 $result = $this->sql_create_unique_index($table, $index_name, $column);
0903
0904 if ($this->return_statements)
0905 {
0906 $statements = array_merge($statements, $result);
0907 }
0908 }
0909 }
0910 }
0911
0912 // Add indexes?
0913 if (!empty($schema_changes['add_index']))
0914 {
0915 foreach ($schema_changes['add_index'] as $table => $index_array)
0916 {
0917 foreach ($index_array as $index_name => $column)
0918 {
0919 if ($this->sql_index_exists($table, $index_name))
0920 {
0921 continue;
0922 }
0923
0924 $result = $this->sql_create_index($table, $index_name, $column);
0925
0926 if ($this->return_statements)
0927 {
0928 $statements = array_merge($statements, $result);
0929 }
0930 }
0931 }
0932 }
0933
0934 if ($sqlite)
0935 {
0936 foreach ($sqlite_data as $table_name => $sql_schema_changes)
0937 {
0938 // Create temporary table with original data
0939 $statements[] = 'begin';
0940
0941 $sql = "SELECT sql
0942 FROM sqlite_master
0943 WHERE type = 'table'
0944 AND name = '{$table_name}'
0945 ORDER BY type DESC, name;";
0946 $result = $this->db->sql_query($sql);
0947
0948 if (!$result)
0949 {
0950 continue;
0951 }
0952
0953 $row = $this->db->sql_fetchrow($result);
0954 $this->db->sql_freeresult($result);
0955
0956 // Create a backup table and populate it, destroy the existing one
0957 $statements[] = preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $row['sql']);
0958 $statements[] = 'INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name;
0959 $statements[] = 'DROP TABLE ' . $table_name;
0960
0961 // Get the columns...
0962 preg_match('#\((.*)\)#s', $row['sql'], $matches);
0963
0964 $plain_table_cols = trim($matches[1]);
0965 $new_table_cols = preg_split('/,(?![\s\w]+\))/m', $plain_table_cols);
0966 $column_list = array();
0967
0968 foreach ($new_table_cols as $declaration)
0969 {
0970 $entities = preg_split('#\s+#', trim($declaration));
0971 if ($entities[0] == 'PRIMARY')
0972 {
0973 continue;
0974 }
0975 $column_list[] = $entities[0];
0976 }
0977
0978 // note down the primary key notation because sqlite only supports adding it to the end for the new table
0979 $primary_key = false;
0980 $_new_cols = array();
0981
0982 foreach ($new_table_cols as $key => $declaration)
0983 {
0984 $entities = preg_split('#\s+#', trim($declaration));
0985 if ($entities[0] == 'PRIMARY')
0986 {
0987 $primary_key = $declaration;
0988 continue;
0989 }
0990 $_new_cols[] = $declaration;
0991 }
0992
0993 $new_table_cols = $_new_cols;
0994
0995 // First of all... change columns
0996 if (!empty($sql_schema_changes['change_columns']))
0997 {
0998 foreach ($sql_schema_changes['change_columns'] as $column_sql)
0999 {
1000 foreach ($new_table_cols as $key => $declaration)
1001 {
1002 $entities = preg_split('#\s+#', trim($declaration));
1003 if (strpos($column_sql, $entities[0] . ' ') === 0)
1004 {
1005 $new_table_cols[$key] = $column_sql;
1006 }
1007 }
1008 }
1009 }
1010
1011 if (!empty($sql_schema_changes['add_columns']))
1012 {
1013 foreach ($sql_schema_changes['add_columns'] as $column_sql)
1014 {
1015 $new_table_cols[] = $column_sql;
1016 }
1017 }
1018
1019 // Now drop them...
1020 if (!empty($sql_schema_changes['drop_columns']))
1021 {
1022 foreach ($sql_schema_changes['drop_columns'] as $column_name)
1023 {
1024 // Remove from column list...
1025 $new_column_list = array();
1026 foreach ($column_list as $key => $value)
1027 {
1028 if ($value === $column_name)
1029 {
1030 continue;
1031 }
1032
1033 $new_column_list[] = $value;
1034 }
1035
1036 $column_list = $new_column_list;
1037
1038 // Remove from table...
1039 $_new_cols = array();
1040 foreach ($new_table_cols as $key => $declaration)
1041 {
1042 $entities = preg_split('#\s+#', trim($declaration));
1043 if (strpos($column_name . ' ', $entities[0] . ' ') === 0)
1044 {
1045 continue;
1046 }
1047 $_new_cols[] = $declaration;
1048 }
1049 $new_table_cols = $_new_cols;
1050 }
1051 }
1052
1053 // Primary key...
1054 if (!empty($sql_schema_changes['primary_key']))
1055 {
1056 $new_table_cols[] = 'PRIMARY KEY (' . implode(', ', $sql_schema_changes['primary_key']) . ')';
1057 }
1058 // Add a new one or the old primary key
1059 else if ($primary_key !== false)
1060 {
1061 $new_table_cols[] = $primary_key;
1062 }
1063
1064 $columns = implode(',', $column_list);
1065
1066 // create a new table and fill it up. destroy the temp one
1067 $statements[] = 'CREATE TABLE ' . $table_name . ' (' . implode(',', $new_table_cols) . ');';
1068 $statements[] = 'INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;';
1069 $statements[] = 'DROP TABLE ' . $table_name . '_temp';
1070
1071 $statements[] = 'commit';
1072 }
1073 }
1074
1075 if ($this->return_statements)
1076 {
1077 return $statements;
1078 }
1079 }
1080
1081 /**
1082 * Gets a list of columns of a table.
1083 *
1084 * @param string $table Table name
1085 *
1086 * @return array Array of column names (all lower case)
1087 */
1088 function sql_list_columns($table)
1089 {
1090 $columns = array();
1091
1092 switch ($this->sql_layer)
1093 {
1094 case 'mysql_40':
1095 case 'mysql_41':
1096 $sql = "SHOW COLUMNS FROM $table";
1097 break;
1098
1099 // PostgreSQL has a way of doing this in a much simpler way but would
1100 // not allow us to support all versions of PostgreSQL
1101 case 'postgres':
1102 $sql = "SELECT a.attname
1103 FROM pg_class c, pg_attribute a
1104 WHERE c.relname = '{$table}'
1105 AND a.attnum > 0
1106 AND a.attrelid = c.oid";
1107 break;
1108
1109 // same deal with PostgreSQL, we must perform more complex operations than
1110 // we technically could
1111 case 'mssql':
1112 case 'mssqlnative':
1113 $sql = "SELECT c.name
1114 FROM syscolumns c
1115 LEFT JOIN sysobjects o ON c.id = o.id
1116 WHERE o.name = '{$table}'";
1117 break;
1118
1119 case 'oracle':
1120 $sql = "SELECT column_name
1121 FROM user_tab_columns
1122 WHERE LOWER(table_name) = '" . strtolower($table) . "'";
1123 break;
1124
1125 case 'sqlite':
1126 case 'sqlite3':
1127 $sql = "SELECT sql
1128 FROM sqlite_master
1129 WHERE type = 'table'
1130 AND name = '{$table}'";
1131
1132 $result = $this->db->sql_query($sql);
1133
1134 if (!$result)
1135 {
1136 return false;
1137 }
1138
1139 $row = $this->db->sql_fetchrow($result);
1140 $this->db->sql_freeresult($result);
1141
1142 preg_match('#\((.*)\)#s', $row['sql'], $matches);
1143
1144 $cols = trim($matches[1]);
1145 $col_array = preg_split('/,(?![\s\w]+\))/m', $cols);
1146
1147 foreach ($col_array as $declaration)
1148 {
1149 $entities = preg_split('#\s+#', trim($declaration));
1150 if ($entities[0] == 'PRIMARY')
1151 {
1152 continue;
1153 }
1154
1155 $column = strtolower($entities[0]);
1156 $columns[$column] = $column;
1157 }
1158
1159 return $columns;
1160 break;
1161 }
1162
1163 $result = $this->db->sql_query($sql);
1164
1165 while ($row = $this->db->sql_fetchrow($result))
1166 {
1167 $column = strtolower(current($row));
1168 $columns[$column] = $column;
1169 }
1170 $this->db->sql_freeresult($result);
1171
1172 return $columns;
1173 }
1174
1175 /**
1176 * Check whether a specified column exist in a table
1177 *
1178 * @param string $table Table to check
1179 * @param string $column_name Column to check
1180 *
1181 * @return bool True if column exists, false otherwise
1182 */
1183 function sql_column_exists($table, $column_name)
1184 {
1185 $columns = $this->sql_list_columns($table);
1186
1187 return isset($columns[$column_name]);
1188 }
1189
1190 /**
1191 * Check if a specified index exists in table. Does not return PRIMARY KEY and UNIQUE indexes.
1192 *
1193 * @param string $table_name Table to check the index at
1194 * @param string $index_name The index name to check
1195 *
1196 * @return bool True if index exists, else false
1197 */
1198 function sql_index_exists($table_name, $index_name)
1199 {
1200 if ($this->sql_layer == 'mssql' || $this->sql_layer == 'mssqlnative')
1201 {
1202 $sql = "EXEC sp_statistics '$table_name'";
1203 $result = $this->db->sql_query($sql);
1204
1205 while ($row = $this->db->sql_fetchrow($result))
1206 {
1207 if ($row['TYPE'] == 3)
1208 {
1209 if (strtolower($row['INDEX_NAME']) == strtolower($index_name))
1210 {
1211 $this->db->sql_freeresult($result);
1212 return true;
1213 }
1214 }
1215 }
1216 $this->db->sql_freeresult($result);
1217
1218 return false;
1219 }
1220
1221 switch ($this->sql_layer)
1222 {
1223 case 'postgres':
1224 $sql = "SELECT ic.relname as index_name
1225 FROM pg_class bc, pg_class ic, pg_index i
1226 WHERE (bc.oid = i.indrelid)
1227 AND (ic.oid = i.indexrelid)
1228 AND (bc.relname = '" . $table_name . "')
1229 AND (i.indisunique != 't')
1230 AND (i.indisprimary != 't')";
1231 $col = 'index_name';
1232 break;
1233
1234 case 'mysql_40':
1235 case 'mysql_41':
1236 $sql = 'SHOW KEYS
1237 FROM ' . $table_name;
1238 $col = 'Key_name';
1239 break;
1240
1241 case 'oracle':
1242 $sql = "SELECT index_name
1243 FROM user_indexes
1244 WHERE table_name = '" . strtoupper($table_name) . "'
1245 AND generated = 'N'
1246 AND uniqueness = 'NONUNIQUE'";
1247 $col = 'index_name';
1248 break;
1249
1250 case 'sqlite':
1251 case 'sqlite3':
1252 $sql = "PRAGMA index_list('" . $table_name . "');";
1253 $col = 'name';
1254 break;
1255 }
1256
1257 $result = $this->db->sql_query($sql);
1258 while ($row = $this->db->sql_fetchrow($result))
1259 {
1260 if (($this->sql_layer == 'mysql_40' || $this->sql_layer == 'mysql_41') && !$row['Non_unique'])
1261 {
1262 continue;
1263 }
1264
1265 // These DBMS prefix index name with the table name
1266 switch ($this->sql_layer)
1267 {
1268 case 'oracle':
1269 case 'postgres':
1270 case 'sqlite':
1271 case 'sqlite3':
1272 $row[$col] = substr($row[$col], strlen($table_name) + 1);
1273 break;
1274 }
1275
1276 if (strtolower($row[$col]) == strtolower($index_name))
1277 {
1278 $this->db->sql_freeresult($result);
1279 return true;
1280 }
1281 }
1282 $this->db->sql_freeresult($result);
1283
1284 return false;
1285 }
1286
1287 /**
1288 * Check if a specified index exists in table. Does not return PRIMARY KEY indexes.
1289 *
1290 * @param string $table_name Table to check the index at
1291 * @param string $index_name The index name to check
1292 *
1293 * @return bool True if index exists, else false
1294 */
1295 function sql_unique_index_exists($table_name, $index_name)
1296 {
1297 if ($this->sql_layer == 'mssql' || $this->sql_layer == 'mssqlnative')
1298 {
1299 $sql = "EXEC sp_statistics '$table_name'";
1300 $result = $this->db->sql_query($sql);
1301
1302 while ($row = $this->db->sql_fetchrow($result))
1303 {
1304 // Usually NON_UNIQUE is the column we want to check, but we allow for both
1305 if ($row['TYPE'] == 3)
1306 {
1307 if (strtolower($row['INDEX_NAME']) == strtolower($index_name))
1308 {
1309 $this->db->sql_freeresult($result);
1310 return true;
1311 }
1312 }
1313 }
1314 $this->db->sql_freeresult($result);
1315 return false;
1316 }
1317
1318 switch ($this->sql_layer)
1319 {
1320 case 'postgres':
1321 $sql = "SELECT ic.relname as index_name, i.indisunique
1322 FROM pg_class bc, pg_class ic, pg_index i
1323 WHERE (bc.oid = i.indrelid)
1324 AND (ic.oid = i.indexrelid)
1325 AND (bc.relname = '" . $table_name . "')
1326 AND (i.indisprimary != 't')";
1327 $col = 'index_name';
1328 break;
1329
1330 case 'mysql_40':
1331 case 'mysql_41':
1332 $sql = 'SHOW KEYS
1333 FROM ' . $table_name;
1334 $col = 'Key_name';
1335 break;
1336
1337 case 'oracle':
1338 $sql = "SELECT index_name, table_owner
1339 FROM user_indexes
1340 WHERE table_name = '" . strtoupper($table_name) . "'
1341 AND generated = 'N'
1342 AND uniqueness = 'UNIQUE'";
1343 $col = 'index_name';
1344 break;
1345
1346 case 'sqlite':
1347 case 'sqlite3':
1348 $sql = "PRAGMA index_list('" . $table_name . "');";
1349 $col = 'name';
1350 break;
1351 }
1352
1353 $result = $this->db->sql_query($sql);
1354 while ($row = $this->db->sql_fetchrow($result))
1355 {
1356 if (($this->sql_layer == 'mysql_40' || $this->sql_layer == 'mysql_41') && ($row['Non_unique'] || $row[$col] == 'PRIMARY'))
1357 {
1358 continue;
1359 }
1360
1361 if (($this->sql_layer == 'sqlite' || $this->sql_layer == 'sqlite3') && !$row['unique'])
1362 {
1363 continue;
1364 }
1365
1366 if ($this->sql_layer == 'postgres' && $row['indisunique'] != 't')
1367 {
1368 continue;
1369 }
1370
1371 // These DBMS prefix index name with the table name
1372 switch ($this->sql_layer)
1373 {
1374 case 'oracle':
1375 // Two cases here... prefixed with U_[table_owner] and not prefixed with table_name
1376 if (strpos($row[$col], 'U_') === 0)
1377 {
1378 $row[$col] = substr($row[$col], strlen('U_' . $row['table_owner']) + 1);
1379 }
1380 else if (strpos($row[$col], strtoupper($table_name)) === 0)
1381 {
1382 $row[$col] = substr($row[$col], strlen($table_name) + 1);
1383 }
1384 break;
1385
1386 case 'postgres':
1387 case 'sqlite':
1388 case 'sqlite3':
1389 $row[$col] = substr($row[$col], strlen($table_name) + 1);
1390 break;
1391 }
1392
1393 if (strtolower($row[$col]) == strtolower($index_name))
1394 {
1395 $this->db->sql_freeresult($result);
1396 return true;
1397 }
1398 }
1399 $this->db->sql_freeresult($result);
1400
1401 return false;
1402 }
1403
1404 /**
1405 * Private method for performing sql statements (either execute them or return them)
1406 * @access private
1407 */
1408 function _sql_run_sql($statements)
1409 {
1410 if ($this->return_statements)
1411 {
1412 return $statements;
1413 }
1414
1415 // We could add error handling here...
1416 foreach ($statements as $sql)
1417 {
1418 if ($sql === 'begin')
1419 {
1420 $this->db->sql_transaction('begin');
1421 }
1422 else if ($sql === 'commit')
1423 {
1424 $this->db->sql_transaction('commit');
1425 }
1426 else
1427 {
1428 $this->db->sql_query($sql);
1429 }
1430 }
1431
1432 return true;
1433 }
1434
1435 /**
1436 * Function to prepare some column information for better usage
1437 * @access private
1438 */
1439 function sql_prepare_column_data($table_name, $column_name, $column_data)
1440 {
1441 if (strlen($column_name) > 30)
1442 {
1443 trigger_error("Column name '$column_name' on table '$table_name' is too long. The maximum is 30 characters.", E_USER_ERROR);
1444 }
1445
1446 // Get type
1447 list($column_type, $orig_column_type) = $this->get_column_type($column_data[0]);
1448
1449 // Adjust default value if db-dependent specified
1450 if (is_array($column_data[1]))
1451 {
1452 $column_data[1] = (isset($column_data[1][$this->sql_layer])) ? $column_data[1][$this->sql_layer] : $column_data[1]['default'];
1453 }
1454
1455 $sql = '';
1456
1457 $return_array = array();
1458
1459 switch ($this->sql_layer)
1460 {
1461 case 'mssql':
1462 case 'mssqlnative':
1463 $sql .= " {$column_type} ";
1464 $sql_default = " {$column_type} ";
1465
1466 // For adding columns we need the default definition
1467 if (!is_null($column_data[1]))
1468 {
1469 // For hexadecimal values do not use single quotes
1470 if (strpos($column_data[1], '0x') === 0)
1471 {
1472 $return_array['default'] = 'DEFAULT (' . $column_data[1] . ') ';
1473 $sql_default .= $return_array['default'];
1474 }
1475 else
1476 {
1477 $return_array['default'] = 'DEFAULT (' . ((is_numeric($column_data[1])) ? $column_data[1] : "'{$column_data[1]}'") . ') ';
1478 $sql_default .= $return_array['default'];
1479 }
1480 }
1481
1482 if (isset($column_data[2]) && $column_data[2] == 'auto_increment')
1483 {
1484 // $sql .= 'IDENTITY (1, 1) ';
1485 $sql_default .= 'IDENTITY (1, 1) ';
1486 }
1487
1488 $return_array['textimage'] = $column_type === '[text]';
1489
1490 if (!is_null($column_data[1]) || (isset($column_data[2]) && $column_data[2] == 'auto_increment'))
1491 {
1492 $sql .= 'NOT NULL';
1493 $sql_default .= 'NOT NULL';
1494 }
1495 else
1496 {
1497 $sql .= 'NULL';
1498 $sql_default .= 'NULL';
1499 }
1500
1501 $return_array['column_type_sql_default'] = $sql_default;
1502
1503 break;
1504
1505 case 'mysql_40':
1506 case 'mysql_41':
1507 $sql .= " {$column_type} ";
1508
1509 // For hexadecimal values do not use single quotes
1510 if (!is_null($column_data[1]) && substr($column_type, -4) !== 'text' && substr($column_type, -4) !== 'blob')
1511 {
1512 $sql .= (strpos($column_data[1], '0x') === 0) ? "DEFAULT {$column_data[1]} " : "DEFAULT '{$column_data[1]}' ";
1513 }
1514
1515 if (!is_null($column_data[1]) || (isset($column_data[2]) && $column_data[2] == 'auto_increment'))
1516 {
1517 $sql .= 'NOT NULL';
1518 }
1519 else
1520 {
1521 $sql .= 'NULL';
1522 }
1523
1524 if (isset($column_data[2]))
1525 {
1526 if ($column_data[2] == 'auto_increment')
1527 {
1528 $sql .= ' auto_increment';
1529 }
1530 else if ($this->sql_layer === 'mysql_41' && $column_data[2] == 'true_sort')
1531 {
1532 $sql .= ' COLLATE utf8_unicode_ci';
1533 }
1534 }
1535
1536 break;
1537
1538 case 'oracle':
1539 $sql .= " {$column_type} ";
1540 $sql .= (!is_null($column_data[1])) ? "DEFAULT '{$column_data[1]}' " : '';
1541
1542 // In Oracle empty strings ('') are treated as NULL.
1543 // Therefore in oracle we allow NULL's for all DEFAULT '' entries
1544 // Oracle does not like setting NOT NULL on a column that is already NOT NULL (this happens only on number fields)
1545 if (!preg_match('/number/i', $column_type))
1546 {
1547 $sql .= ($column_data[1] === '' || $column_data[1] === null) ? '' : 'NOT NULL';
1548 }
1549
1550 $return_array['auto_increment'] = false;
1551 if (isset($column_data[2]) && $column_data[2] == 'auto_increment')
1552 {
1553 $return_array['auto_increment'] = true;
1554 }
1555
1556 break;
1557
1558 case 'postgres':
1559 $return_array['column_type'] = $column_type;
1560
1561 $sql .= " {$column_type} ";
1562
1563 $return_array['auto_increment'] = false;
1564 if (isset($column_data[2]) && $column_data[2] == 'auto_increment')
1565 {
1566 $default_val = "nextval('{$table_name}_seq')";
1567 $return_array['auto_increment'] = true;
1568 }
1569 else if (!is_null($column_data[1]))
1570 {
1571 $default_val = "'" . $column_data[1] . "'";
1572 $return_array['null'] = 'NOT NULL';
1573 $sql .= 'NOT NULL ';
1574 }
1575 else
1576 {
1577 $default_val = "'" . $column_data[1] . "'";
1578 $return_array['null'] = 'NULL';
1579 $sql .= 'NULL ';
1580 }
1581
1582 $return_array['default'] = $default_val;
1583
1584 $sql .= "DEFAULT {$default_val}";
1585
1586 // Unsigned? Then add a CHECK contraint
1587 if (in_array($orig_column_type, $this->unsigned_types))
1588 {
1589 $return_array['constraint'] = "CHECK ({$column_name} >= 0)";
1590 $sql .= " CHECK ({$column_name} >= 0)";
1591 }
1592
1593 break;
1594
1595 case 'sqlite':
1596 case 'sqlite3':
1597 $return_array['primary_key_set'] = false;
1598 if (isset($column_data[2]) && $column_data[2] == 'auto_increment')
1599 {
1600 $sql .= ' INTEGER PRIMARY KEY';
1601 $return_array['primary_key_set'] = true;
1602
1603 if ($this->sql_layer === 'sqlite3')
1604 {
1605 $sql .= ' AUTOINCREMENT';
1606 }
1607 }
1608 else
1609 {
1610 $sql .= ' ' . $column_type;
1611 }
1612
1613 if (!is_null($column_data[1]))
1614 {
1615 $sql .= ' NOT NULL ';
1616 $sql .= "DEFAULT '{$column_data[1]}'";
1617 }
1618
1619 break;
1620 }
1621
1622 $return_array['column_type_sql'] = $sql;
1623
1624 return $return_array;
1625 }
1626
1627 /**
1628 * Get the column's database type from the type map
1629 *
1630 * @param string $column_map_type
1631 * @return array column type for this database
1632 * and map type without length
1633 */
1634 function get_column_type($column_map_type)
1635 {
1636 if (strpos($column_map_type, ':') !== false)
1637 {
1638 list($orig_column_type, $column_length) = explode(':', $column_map_type);
1639 if (!is_array($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']))
1640 {
1641 $column_type = sprintf($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':'], $column_length);
1642 }
1643 else
1644 {
1645 if (isset($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['rule']))
1646 {
1647 switch ($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['rule'][0])
1648 {
1649 case 'div':
1650 $column_length /= $this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['rule'][1];
1651 $column_length = ceil($column_length);
1652 $column_type = sprintf($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':'][0], $column_length);
1653 break;
1654 }
1655 }
1656
1657 if (isset($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['limit']))
1658 {
1659 switch ($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['limit'][0])
1660 {
1661 case 'mult':
1662 $column_length *= $this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['limit'][1];
1663 if ($column_length > $this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['limit'][2])
1664 {
1665 $column_type = $this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['limit'][3];
1666 }
1667 else
1668 {
1669 $column_type = sprintf($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':'][0], $column_length);
1670 }
1671 break;
1672 }
1673 }
1674 }
1675 $orig_column_type .= ':';
1676 }
1677 else
1678 {
1679 $orig_column_type = $column_map_type;
1680 $column_type = $this->dbms_type_map[$this->sql_layer][$column_map_type];
1681 }
1682
1683 return array($column_type, $orig_column_type);
1684 }
1685
1686 /**
1687 * Add new column
1688 */
1689 function sql_column_add($table_name, $column_name, $column_data, $inline = false)
1690 {
1691 $column_data = $this->sql_prepare_column_data($table_name, $column_name, $column_data);
1692 $statements = array();
1693
1694 switch ($this->sql_layer)
1695 {
1696 case 'mssql':
1697 case 'mssqlnative':
1698 // Does not support AFTER, only through temporary table
1699 $statements[] = 'ALTER TABLE [' . $table_name . '] ADD [' . $column_name . '] ' . $column_data['column_type_sql_default'];
1700 break;
1701
1702 case 'mysql_40':
1703 case 'mysql_41':
1704 $after = (!empty($column_data['after'])) ? ' AFTER ' . $column_data['after'] : '';
1705 $statements[] = 'ALTER TABLE `' . $table_name . '` ADD COLUMN `' . $column_name . '` ' . $column_data['column_type_sql'] . $after;
1706 break;
1707
1708 case 'oracle':
1709 // Does not support AFTER, only through temporary table
1710 $statements[] = 'ALTER TABLE ' . $table_name . ' ADD ' . $column_name . ' ' . $column_data['column_type_sql'];
1711 break;
1712
1713 case 'postgres':
1714 // Does not support AFTER, only through temporary table
1715 if (version_compare($this->db->sql_server_info(true), '8.0', '>='))
1716 {
1717 $statements[] = 'ALTER TABLE ' . $table_name . ' ADD COLUMN "' . $column_name . '" ' . $column_data['column_type_sql'];
1718 }
1719 else
1720 {
1721 // old versions cannot add columns with default and null information
1722 $statements[] = 'ALTER TABLE ' . $table_name . ' ADD COLUMN "' . $column_name . '" ' . $column_data['column_type'] . ' ' . $column_data['constraint'];
1723
1724 if (isset($column_data['null']))
1725 {
1726 if ($column_data['null'] == 'NOT NULL')
1727 {
1728 $statements[] = 'ALTER TABLE ' . $table_name . ' ALTER COLUMN ' . $column_name . ' SET NOT NULL';
1729 }
1730 }
1731
1732 if (isset($column_data['default']))
1733 {
1734 $statements[] = 'ALTER TABLE ' . $table_name . ' ALTER COLUMN ' . $column_name . ' SET DEFAULT ' . $column_data['default'];
1735 }
1736 }
1737
1738 break;
1739
1740 case 'sqlite':
1741 if ($inline && $this->return_statements)
1742 {
1743 return $column_name . ' ' . $column_data['column_type_sql'];
1744 }
1745
1746 $recreate_queries = $this->sqlite_get_recreate_table_queries($table_name);
1747 if (empty($recreate_queries))
1748 {
1749 break;
1750 }
1751
1752 $statements[] = 'begin';
1753
1754 $sql_create_table = array_shift($recreate_queries);
1755
1756 // Create a backup table and populate it, destroy the existing one
1757 $statements[] = preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $sql_create_table);
1758 $statements[] = 'INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name;
1759 $statements[] = 'DROP TABLE ' . $table_name;
1760
1761 preg_match('#\((.*)\)#s', $sql_create_table, $matches);
1762
1763 $new_table_cols = trim($matches[1]);
1764 $old_table_cols = preg_split('/,(?![\s\w]+\))/m', $new_table_cols);
1765 $column_list = array();
1766
1767 foreach ($old_table_cols as $declaration)
1768 {
1769 $entities = preg_split('#\s+#', trim($declaration));
1770 if ($entities[0] == 'PRIMARY')
1771 {
1772 continue;
1773 }
1774 $column_list[] = $entities[0];
1775 }
1776
1777 $columns = implode(',', $column_list);
1778
1779 $new_table_cols = $column_name . ' ' . $column_data['column_type_sql'] . ',' . $new_table_cols;
1780
1781 // create a new table and fill it up. destroy the temp one
1782 $statements[] = 'CREATE TABLE ' . $table_name . ' (' . $new_table_cols . ');';
1783 $statements = array_merge($statements, $recreate_queries);
1784
1785 $statements[] = 'INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;';
1786 $statements[] = 'DROP TABLE ' . $table_name . '_temp';
1787
1788 $statements[] = 'commit';
1789 break;
1790
1791 case 'sqlite3':
1792 if ($inline && $this->return_statements)
1793 {
1794 return $column_name . ' ' . $column_data['column_type_sql'];
1795 }
1796
1797 $statements[] = 'ALTER TABLE ' . $table_name . ' ADD ' . $column_name . ' ' . $column_data['column_type_sql'];
1798 break;
1799 }
1800
1801 return $this->_sql_run_sql($statements);
1802 }
1803
1804 /**
1805 * Drop column
1806 */
1807 function sql_column_remove($table_name, $column_name, $inline = false)
1808 {
1809 $statements = array();
1810
1811 switch ($this->sql_layer)
1812 {
1813 case 'mssql':
1814 case 'mssqlnative':
1815 // We need the data here
1816 $old_return_statements = $this->return_statements;
1817 $this->return_statements = true;
1818
1819 $indexes = $this->get_existing_indexes($table_name, $column_name);
1820 $indexes = array_merge($indexes, $this->get_existing_indexes($table_name, $column_name, true));
1821
1822 // Drop any indexes
1823 $recreate_indexes = array();
1824 if (!empty($indexes))
1825 {
1826 foreach ($indexes as $index_name => $index_data)
1827 {
1828 $result = $this->sql_index_drop($table_name, $index_name);
1829 $statements = array_merge($statements, $result);
1830 if (sizeof($index_data) > 1)
1831 {
1832 // Remove this column from the index and recreate it
1833 $recreate_indexes[$index_name] = array_diff($index_data, array($column_name));
1834 }
1835 }
1836 }
1837
1838 // Drop default value constraint
1839 $result = $this->mssql_get_drop_default_constraints_queries($table_name, $column_name);
1840 $statements = array_merge($statements, $result);
1841
1842 // Remove the column
1843 $statements[] = 'ALTER TABLE [' . $table_name . '] DROP COLUMN [' . $column_name . ']';
1844
1845 if (!empty($recreate_indexes))
1846 {
1847 // Recreate indexes after we removed the column
1848 foreach ($recreate_indexes as $index_name => $index_data)
1849 {
1850 $result = $this->sql_create_index($table_name, $index_name, $index_data);
1851 $statements = array_merge($statements, $result);
1852 }
1853 }
1854
1855 $this->return_statements = $old_return_statements;
1856 break;
1857
1858 case 'mysql_40':
1859 case 'mysql_41':
1860 $statements[] = 'ALTER TABLE `' . $table_name . '` DROP COLUMN `' . $column_name . '`';
1861 break;
1862
1863 case 'oracle':
1864 $statements[] = 'ALTER TABLE ' . $table_name . ' DROP COLUMN ' . $column_name;
1865 break;
1866
1867 case 'postgres':
1868 $statements[] = 'ALTER TABLE ' . $table_name . ' DROP COLUMN "' . $column_name . '"';
1869 break;
1870
1871 case 'sqlite':
1872 case 'sqlite3':
1873
1874 if ($inline && $this->return_statements)
1875 {
1876 return $column_name;
1877 }
1878
1879 $recreate_queries = $this->sqlite_get_recreate_table_queries($table_name, $column_name);
1880 if (empty($recreate_queries))
1881 {
1882 break;
1883 }
1884
1885 $statements[] = 'begin';
1886
1887 $sql_create_table = array_shift($recreate_queries);
1888
1889 // Create a backup table and populate it, destroy the existing one
1890 $statements[] = preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $sql_create_table);
1891 $statements[] = 'INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name;
1892 $statements[] = 'DROP TABLE ' . $table_name;
1893
1894 preg_match('#\((.*)\)#s', $sql_create_table, $matches);
1895
1896 $new_table_cols = trim($matches[1]);
1897 $old_table_cols = preg_split('/,(?![\s\w]+\))/m', $new_table_cols);
1898 $column_list = array();
1899
1900 foreach ($old_table_cols as $declaration)
1901 {
1902 $entities = preg_split('#\s+#', trim($declaration));
1903 if ($entities[0] == 'PRIMARY' || $entities[0] === $column_name)
1904 {
1905 continue;
1906 }
1907 $column_list[] = $entities[0];
1908 }
1909
1910 $columns = implode(',', $column_list);
1911
1912 $new_table_cols = trim(preg_replace('/' . $column_name . '\b[^,]+(?:,|$)/m', '', $new_table_cols));
1913 if (substr($new_table_cols, -1) === ',')
1914 {
1915 // Remove the comma from the last entry again
1916 $new_table_cols = substr($new_table_cols, 0, -1);
1917 }
1918
1919 // create a new table and fill it up. destroy the temp one
1920 $statements[] = 'CREATE TABLE ' . $table_name . ' (' . $new_table_cols . ');';
1921 $statements = array_merge($statements, $recreate_queries);
1922
1923 $statements[] = 'INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;';
1924 $statements[] = 'DROP TABLE ' . $table_name . '_temp';
1925
1926 $statements[] = 'commit';
1927 break;
1928 }
1929
1930 return $this->_sql_run_sql($statements);
1931 }
1932
1933 /**
1934 * Drop Index
1935 */
1936 function sql_index_drop($table_name, $index_name)
1937 {
1938 $statements = array();
1939
1940 switch ($this->sql_layer)
1941 {
1942 case 'mssql':
1943 case 'mssqlnative':
1944 $statements[] = 'DROP INDEX ' . $table_name . '.' . $index_name;
1945 break;
1946
1947 case 'mysql_40':
1948 case 'mysql_41':
1949 $statements[] = 'DROP INDEX ' . $index_name . ' ON ' . $table_name;
1950 break;
1951
1952 case 'oracle':
1953 case 'postgres':
1954 case 'sqlite':
1955 case 'sqlite3':
1956 $statements[] = 'DROP INDEX ' . $table_name . '_' . $index_name;
1957 break;
1958 }
1959
1960 return $this->_sql_run_sql($statements);
1961 }
1962
1963 /**
1964 * Drop Table
1965 */
1966 function sql_table_drop($table_name)
1967 {
1968 $statements = array();
1969
1970 if (!$this->sql_table_exists($table_name))
1971 {
1972 return $this->_sql_run_sql($statements);
1973 }
1974
1975 // the most basic operation, get rid of the table
1976 $statements[] = 'DROP TABLE ' . $table_name;
1977
1978 switch ($this->sql_layer)
1979 {
1980 case 'oracle':
1981 $sql = 'SELECT A.REFERENCED_NAME
1982 FROM USER_DEPENDENCIES A, USER_TRIGGERS B
1983 WHERE A.REFERENCED_TYPE = \'SEQUENCE\'
1984 AND A.NAME = B.TRIGGER_NAME
1985 AND B.TABLE_NAME = \'' . strtoupper($table_name) . "'";
1986 $result = $this->db->sql_query($sql);
1987
1988 // any sequences ref'd to this table's triggers?
1989 while ($row = $this->db->sql_fetchrow($result))
1990 {
1991 $statements[] = "DROP SEQUENCE {$row['referenced_name']}";
1992 }
1993 $this->db->sql_freeresult($result);
1994 break;
1995
1996 case 'postgres':
1997 // PGSQL does not "tightly" bind sequences and tables, we must guess...
1998 $sql = "SELECT relname
1999 FROM pg_class
2000 WHERE relkind = 'S'
2001 AND relname = '{$table_name}_seq'";
2002 $result = $this->db->sql_query($sql);
2003
2004 // We don't even care about storing the results. We already know the answer if we get rows back.
2005 if ($this->db->sql_fetchrow($result))
2006 {
2007 $statements[] = "DROP SEQUENCE {$table_name}_seq;\n";
2008 }
2009 $this->db->sql_freeresult($result);
2010 break;
2011 }
2012
2013 return $this->_sql_run_sql($statements);
2014 }
2015
2016 /**
2017 * Add primary key
2018 */
2019 function sql_create_primary_key($table_name, $column, $inline = false)
2020 {
2021 $statements = array();
2022
2023 switch ($this->sql_layer)
2024 {
2025 case 'postgres':
2026 case 'mysql_40':
2027 case 'mysql_41':
2028 $statements[] = 'ALTER TABLE ' . $table_name . ' ADD PRIMARY KEY (' . implode(', ', $column) . ')';
2029 break;
2030
2031 case 'mssql':
2032 case 'mssqlnative':
2033 $sql = "ALTER TABLE [{$table_name}] WITH NOCHECK ADD ";
2034 $sql .= "CONSTRAINT [PK_{$table_name}] PRIMARY KEY CLUSTERED (";
2035 $sql .= '[' . implode("],\n\t\t[", $column) . ']';
2036 $sql .= ')';
2037
2038 $statements[] = $sql;
2039 break;
2040
2041 case 'oracle':
2042 $statements[] = 'ALTER TABLE ' . $table_name . ' add CONSTRAINT pk_' . $table_name . ' PRIMARY KEY (' . implode(', ', $column) . ')';
2043 break;
2044
2045 case 'sqlite':
2046 case 'sqlite3':
2047
2048 if ($inline && $this->return_statements)
2049 {
2050 return $column;
2051 }
2052
2053 $recreate_queries = $this->sqlite_get_recreate_table_queries($table_name);
2054 if (empty($recreate_queries))
2055 {
2056 break;
2057 }
2058
2059 $statements[] = 'begin';
2060
2061 $sql_create_table = array_shift($recreate_queries);
2062
2063 // Create a backup table and populate it, destroy the existing one
2064 $statements[] = preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $sql_create_table);
2065 $statements[] = 'INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name;
2066 $statements[] = 'DROP TABLE ' . $table_name;
2067
2068 preg_match('#\((.*)\)#s', $sql_create_table, $matches);
2069
2070 $new_table_cols = trim($matches[1]);
2071 $old_table_cols = preg_split('/,(?![\s\w]+\))/m', $new_table_cols);
2072 $column_list = array();
2073
2074 foreach ($old_table_cols as $declaration)
2075 {
2076 $entities = preg_split('#\s+#', trim($declaration));
2077 if ($entities[0] == 'PRIMARY')
2078 {
2079 continue;
2080 }
2081 $column_list[] = $entities[0];
2082 }
2083
2084 $columns = implode(',', $column_list);
2085
2086 // create a new table and fill it up. destroy the temp one
2087 $statements[] = 'CREATE TABLE ' . $table_name . ' (' . $new_table_cols . ', PRIMARY KEY (' . implode(', ', $column) . '));';
2088 $statements = array_merge($statements, $recreate_queries);
2089
2090 $statements[] = 'INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;';
2091 $statements[] = 'DROP TABLE ' . $table_name . '_temp';
2092
2093 $statements[] = 'commit';
2094 break;
2095 }
2096
2097 return $this->_sql_run_sql($statements);
2098 }
2099
2100 /**
2101 * Add unique index
2102 */
2103 function sql_create_unique_index($table_name, $index_name, $column)
2104 {
2105 $statements = array();
2106
2107 $table_prefix = substr(CONFIG_TABLE, 0, -6); // strlen(config)
2108 if (strlen($table_name . '_' . $index_name) - strlen($table_prefix) > 24)
2109 {
2110 $max_length = strlen($table_prefix) + 24;
2111 trigger_error("Index name '{$table_name}_$index_name' on table '$table_name' is too long. The maximum is $max_length characters.", E_USER_ERROR);
2112 }
2113
2114 switch ($this->sql_layer)
2115 {
2116 case 'postgres':
2117 case 'oracle':
2118 case 'sqlite':
2119 case 'sqlite3':
2120 $statements[] = 'CREATE UNIQUE INDEX ' . $table_name . '_' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ')';
2121 break;
2122
2123 case 'mysql_40':
2124 case 'mysql_41':
2125 $statements[] = 'ALTER TABLE ' . $table_name . ' ADD UNIQUE INDEX ' . $index_name . '(' . implode(', ', $column) . ')';
2126 break;
2127
2128 case 'mssql':
2129 case 'mssqlnative':
2130 $statements[] = 'CREATE UNIQUE INDEX [' . $index_name . '] ON [' . $table_name . ']([' . implode('], [', $column) . '])';
2131 break;
2132 }
2133
2134 return $this->_sql_run_sql($statements);
2135 }
2136
2137 /**
2138 * Add index
2139 */
2140 function sql_create_index($table_name, $index_name, $column)
2141 {
2142 $statements = array();
2143
2144 $table_prefix = substr(CONFIG_TABLE, 0, -6); // strlen(config)
2145 if (strlen($table_name . $index_name) - strlen($table_prefix) > 24)
2146 {
2147 $max_length = strlen($table_prefix) + 24;
2148 trigger_error("Index name '{$table_name}_$index_name' on table '$table_name' is too long. The maximum is $max_length characters.", E_USER_ERROR);
2149 }
2150
2151 // remove index length unless MySQL4
2152 if ('mysql_40' != $this->sql_layer)
2153 {
2154 $column = preg_replace('#:.*$#', '', $column);
2155 }
2156
2157 switch ($this->sql_layer)
2158 {
2159 case 'postgres':
2160 case 'oracle':
2161 case 'sqlite':
2162 case 'sqlite3':
2163 $statements[] = 'CREATE INDEX ' . $table_name . '_' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ')';
2164 break;
2165
2166 case 'mysql_40':
2167 // add index size to definition as required by MySQL4
2168 foreach ($column as $i => $col)
2169 {
2170 if (false !== strpos($col, ':'))
2171 {
2172 list($col, $index_size) = explode(':', $col);
2173 $column[$i] = "$col($index_size)";
2174 }
2175 }
2176 // no break
2177 case 'mysql_41':
2178 $statements[] = 'ALTER TABLE ' . $table_name . ' ADD INDEX ' . $index_name . '(' . implode(', ', $column) . ')';
2179 break;
2180
2181 case 'mssql':
2182 case 'mssqlnative':
2183 $statements[] = 'CREATE INDEX [' . $index_name . '] ON [' . $table_name . ']([' . implode('], [', $column) . '])';
2184 break;
2185 }
2186
2187 return $this->_sql_run_sql($statements);
2188 }
2189
2190 /**
2191 * List all of the indices that belong to a table,
2192 * does not count:
2193 * * UNIQUE indices
2194 * * PRIMARY keys
2195 */
2196 function sql_list_index($table_name)
2197 {
2198 $index_array = array();
2199
2200 if ($this->sql_layer == 'mssql' || $this->sql_layer == 'mssqlnative')
2201 {
2202 $sql = "EXEC sp_statistics '$table_name'";
2203 $result = $this->db->sql_query($sql);
2204 while ($row = $this->db->sql_fetchrow($result))
2205 {
2206 if ($row['TYPE'] == 3)
2207 {
2208 $index_array[] = $row['INDEX_NAME'];
2209 }
2210 }
2211 $this->db->sql_freeresult($result);
2212 }
2213 else
2214 {
2215 switch ($this->sql_layer)
2216 {
2217 case 'postgres':
2218 $sql = "SELECT ic.relname as index_name
2219 FROM pg_class bc, pg_class ic, pg_index i
2220 WHERE (bc.oid = i.indrelid)
2221 AND (ic.oid = i.indexrelid)
2222 AND (bc.relname = '" . $table_name . "')
2223 AND (i.indisunique != 't')
2224 AND (i.indisprimary != 't')";
2225 $col = 'index_name';
2226 break;
2227
2228 case 'mysql_40':
2229 case 'mysql_41':
2230 $sql = 'SHOW KEYS
2231 FROM ' . $table_name;
2232 $col = 'Key_name';
2233 break;
2234
2235 case 'oracle':
2236 $sql = "SELECT index_name
2237 FROM user_indexes
2238 WHERE table_name = '" . strtoupper($table_name) . "'
2239 AND generated = 'N'
2240 AND uniqueness = 'NONUNIQUE'";
2241 $col = 'index_name';
2242 break;
2243
2244 case 'sqlite':
2245 case 'sqlite3':
2246 $sql = "PRAGMA index_info('" . $table_name . "');";
2247 $col = 'name';
2248 break;
2249 }
2250
2251 $result = $this->db->sql_query($sql);
2252 while ($row = $this->db->sql_fetchrow($result))
2253 {
2254 if (($this->sql_layer == 'mysql_40' || $this->sql_layer == 'mysql_41') && !$row['Non_unique'])
2255 {
2256 continue;
2257 }
2258
2259 switch ($this->sql_layer)
2260 {
2261 case 'oracle':
2262 case 'postgres':
2263 case 'sqlite':
2264 case 'sqlite3':
2265 $row[$col] = substr($row[$col], strlen($table_name) + 1);
2266 break;
2267 }
2268
2269 $index_array[] = $row[$col];
2270 }
2271 $this->db->sql_freeresult($result);
2272 }
2273
2274 return array_map('strtolower', $index_array);
2275 }
2276
2277 /**
2278 * Removes table_name from the index_name if it is at the beginning
2279 *
2280 * @param $table_name
2281 * @param $index_name
2282 * @return string
2283 */
2284 protected function strip_table_name_from_index_name($table_name, $index_name)
2285 {
2286 return (strpos(strtoupper($index_name), strtoupper($table_name)) === 0) ? substr($index_name, strlen($table_name) + 1) : $index_name;
2287 }
2288
2289 /**
2290 * Change column type (not name!)
2291 */
2292 function sql_column_change($table_name, $column_name, $column_data, $inline = false)
2293 {
2294 $original_column_data = $column_data;
2295 $column_data = $this->sql_prepare_column_data($table_name, $column_name, $column_data);
2296 $statements = array();
2297
2298 switch ($this->sql_layer)
2299 {
2300 case 'mssql':
2301 case 'mssqlnative':
2302 // We need the data here
2303 $old_return_statements = $this->return_statements;
2304 $this->return_statements = true;
2305
2306 $indexes = $this->get_existing_indexes($table_name, $column_name);
2307 $unique_indexes = $this->get_existing_indexes($table_name, $column_name, true);
2308
2309 // Drop any indexes
2310 if (!empty($indexes) || !empty($unique_indexes))
2311 {
2312 $drop_indexes = array_merge(array_keys($indexes), array_keys($unique_indexes));
2313 foreach ($drop_indexes as $index_name)
2314 {
2315 $result = $this->sql_index_drop($table_name, $index_name);
2316 $statements = array_merge($statements, $result);
2317 }
2318 }
2319
2320 // Drop default value constraint
2321 $result = $this->mssql_get_drop_default_constraints_queries($table_name, $column_name);
2322 $statements = array_merge($statements, $result);
2323
2324 // Change the column
2325 $statements[] = 'ALTER TABLE [' . $table_name . '] ALTER COLUMN [' . $column_name . '] ' . $column_data['column_type_sql'];
2326
2327 if (!empty($column_data['default']))
2328 {
2329 // Add new default value constraint
2330 $statements[] = 'ALTER TABLE [' . $table_name . '] ADD CONSTRAINT [DF_' . $table_name . '_' . $column_name . '_1] ' . $this->db->sql_escape($column_data['default']) . ' FOR [' . $column_name . ']';
2331 }
2332
2333 if (!empty($indexes))
2334 {
2335 // Recreate indexes after we changed the column
2336 foreach ($indexes as $index_name => $index_data)
2337 {
2338 $result = $this->sql_create_index($table_name, $index_name, $index_data);
2339 $statements = array_merge($statements, $result);
2340 }
2341 }
2342
2343 if (!empty($unique_indexes))
2344 {
2345 // Recreate unique indexes after we changed the column
2346 foreach ($unique_indexes as $index_name => $index_data)
2347 {
2348 $result = $this->sql_create_unique_index($table_name, $index_name, $index_data);
2349 $statements = array_merge($statements, $result);
2350 }
2351 }
2352
2353 $this->return_statements = $old_return_statements;
2354 break;
2355
2356 case 'mysql_40':
2357 case 'mysql_41':
2358 $statements[] = 'ALTER TABLE `' . $table_name . '` CHANGE `' . $column_name . '` `' . $column_name . '` ' . $column_data['column_type_sql'];
2359 break;
2360
2361 case 'oracle':
2362 // We need the data here
2363 $old_return_statements = $this->return_statements;
2364 $this->return_statements = true;
2365
2366 // Get list of existing indexes
2367 $indexes = $this->get_existing_indexes($table_name, $column_name);
2368 $unique_indexes = $this->get_existing_indexes($table_name, $column_name, true);
2369
2370 // Drop any indexes
2371 if (!empty($indexes) || !empty($unique_indexes))
2372 {
2373 $drop_indexes = array_merge(array_keys($indexes), array_keys($unique_indexes));
2374 foreach ($drop_indexes as $index_name)
2375 {
2376 $result = $this->sql_index_drop($table_name, $this->strip_table_name_from_index_name($table_name, $index_name));
2377 $statements = array_merge($statements, $result);
2378 }
2379 }
2380
2381 $temp_column_name = 'temp_' . substr(md5($column_name), 0, 25);
2382 // Add a temporary table with the new type
2383 $result = $this->sql_column_add($table_name, $temp_column_name, $original_column_data);
2384 $statements = array_merge($statements, $result);
2385
2386 // Copy the data to the new column
2387 $statements[] = 'UPDATE ' . $table_name . ' SET ' . $temp_column_name . ' = ' . $column_name;
2388
2389 // Drop the original column
2390 $result = $this->sql_column_remove($table_name, $column_name);
2391 $statements = array_merge($statements, $result);
2392
2393 // Recreate the original column with the new type
2394 $result = $this->sql_column_add($table_name, $column_name, $original_column_data);
2395 $statements = array_merge($statements, $result);
2396
2397 if (!empty($indexes))
2398 {
2399 // Recreate indexes after we changed the column
2400 foreach ($indexes as $index_name => $index_data)
2401 {
2402 $result = $this->sql_create_index($table_name, $this->strip_table_name_from_index_name($table_name, $index_name), $index_data);
2403 $statements = array_merge($statements, $result);
2404 }
2405 }
2406
2407 if (!empty($unique_indexes))
2408 {
2409 // Recreate unique indexes after we changed the column
2410 foreach ($unique_indexes as $index_name => $index_data)
2411 {
2412 $result = $this->sql_create_unique_index($table_name, $this->strip_table_name_from_index_name($table_name, $index_name), $index_data);
2413 $statements = array_merge($statements, $result);
2414 }
2415 }
2416
2417 // Copy the data to the original column
2418 $statements[] = 'UPDATE ' . $table_name . ' SET ' . $column_name . ' = ' . $temp_column_name;
2419
2420 // Drop the temporary column again
2421 $result = $this->sql_column_remove($table_name, $temp_column_name);
2422 $statements = array_merge($statements, $result);
2423
2424 $this->return_statements = $old_return_statements;
2425 break;
2426
2427 case 'postgres':
2428 $sql = 'ALTER TABLE ' . $table_name . ' ';
2429
2430 $sql_array = array();
2431 $sql_array[] = 'ALTER COLUMN ' . $column_name . ' TYPE ' . $column_data['column_type'];
2432
2433 if (isset($column_data['null']))
2434 {
2435 if ($column_data['null'] == 'NOT NULL')
2436 {
2437 $sql_array[] = 'ALTER COLUMN ' . $column_name . ' SET NOT NULL';
2438 }
2439 else if ($column_data['null'] == 'NULL')
2440 {
2441 $sql_array[] = 'ALTER COLUMN ' . $column_name . ' DROP NOT NULL';
2442 }
2443 }
2444
2445 if (isset($column_data['default']))
2446 {
2447 $sql_array[] = 'ALTER COLUMN ' . $column_name . ' SET DEFAULT ' . $column_data['default'];
2448 }
2449
2450 // we don't want to double up on constraints if we change different number data types
2451 if (isset($column_data['constraint']))
2452 {
2453 $constraint_sql = "SELECT consrc as constraint_data
2454 FROM pg_constraint, pg_class bc
2455 WHERE conrelid = bc.oid
2456 AND bc.relname = '{$table_name}'
2457 AND NOT EXISTS (
2458 SELECT *
2459 FROM pg_constraint as c, pg_inherits as i
2460 WHERE i.inhrelid = pg_constraint.conrelid
2461 AND c.conname = pg_constraint.conname
2462 AND c.consrc = pg_constraint.consrc
2463 AND c.conrelid = i.inhparent
2464 )";
2465
2466 $constraint_exists = false;
2467
2468 $result = $this->db->sql_query($constraint_sql);
2469 while ($row = $this->db->sql_fetchrow($result))
2470 {
2471 if (trim($row['constraint_data']) == trim($column_data['constraint']))
2472 {
2473 $constraint_exists = true;
2474 break;
2475 }
2476 }
2477 $this->db->sql_freeresult($result);
2478
2479 if (!$constraint_exists)
2480 {
2481 $sql_array[] = 'ADD ' . $column_data['constraint'];
2482 }
2483 }
2484
2485 $sql .= implode(', ', $sql_array);
2486
2487 $statements[] = $sql;
2488 break;
2489
2490 case 'sqlite':
2491 case 'sqlite3':
2492
2493 if ($inline && $this->return_statements)
2494 {
2495 return $column_name . ' ' . $column_data['column_type_sql'];
2496 }
2497
2498 $recreate_queries = $this->sqlite_get_recreate_table_queries($table_name);
2499 if (empty($recreate_queries))
2500 {
2501 break;
2502 }
2503
2504 $statements[] = 'begin';
2505
2506 $sql_create_table = array_shift($recreate_queries);
2507
2508 // Create a temp table and populate it, destroy the existing one
2509 $statements[] = preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $sql_create_table);
2510 $statements[] = 'INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name;
2511 $statements[] = 'DROP TABLE ' . $table_name;
2512
2513 preg_match('#\((.*)\)#s', $sql_create_table, $matches);
2514
2515 $new_table_cols = trim($matches[1]);
2516 $old_table_cols = preg_split('/,(?![\s\w]+\))/m', $new_table_cols);
2517 $column_list = array();
2518
2519 foreach ($old_table_cols as $key => $declaration)
2520 {
2521 $declaration = trim($declaration);
2522
2523 // Check for the beginning of the constraint section and stop
2524 if (preg_match('/[^\(]*\s*PRIMARY KEY\s+\(/', $declaration) ||
2525 preg_match('/[^\(]*\s*UNIQUE\s+\(/', $declaration) ||
2526 preg_match('/[^\(]*\s*FOREIGN KEY\s+\(/', $declaration) ||
2527 preg_match('/[^\(]*\s*CHECK\s+\(/', $declaration))
2528 {
2529 break;
2530 }
2531
2532 $entities = preg_split('#\s+#', $declaration);
2533 $column_list[] = $entities[0];
2534 if ($entities[0] == $column_name)
2535 {
2536 $old_table_cols[$key] = $column_name . ' ' . $column_data['column_type_sql'];
2537 }
2538 }
2539
2540 $columns = implode(',', $column_list);
2541
2542 // Create a new table and fill it up. destroy the temp one
2543 $statements[] = 'CREATE TABLE ' . $table_name . ' (' . implode(',', $old_table_cols) . ');';
2544 $statements = array_merge($statements, $recreate_queries);
2545
2546 $statements[] = 'INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;';
2547 $statements[] = 'DROP TABLE ' . $table_name . '_temp';
2548
2549 $statements[] = 'commit';
2550
2551 break;
2552 }
2553
2554 return $this->_sql_run_sql($statements);
2555 }
2556
2557 /**
2558 * Get queries to drop the default constraints of a column
2559 *
2560 * We need to drop the default constraints of a column,
2561 * before being able to change their type or deleting them.
2562 *
2563 * @param string $table_name
2564 * @param string $column_name
2565 * @return array Array with SQL statements
2566 */
2567 protected function mssql_get_drop_default_constraints_queries($table_name, $column_name)
2568 {
2569 $statements = array();
2570 if ($this->mssql_is_sql_server_2000())
2571 {
2572 // http://msdn.microsoft.com/en-us/library/aa175912%28v=sql.80%29.aspx
2573 // Deprecated in SQL Server 2005
2574 $sql = "SELECT so.name AS def_name
2575 FROM sysobjects so
2576 JOIN sysconstraints sc ON so.id = sc.constid
2577 WHERE object_name(so.parent_obj) = '{$table_name}'
2578 AND so.xtype = 'D'
2579 AND sc.colid = (SELECT colid FROM syscolumns
2580 WHERE id = object_id('{$table_name}')
2581 AND name = '{$column_name}')";
2582 }
2583 else
2584 {
2585 $sql = "SELECT dobj.name AS def_name
2586 FROM sys.columns col
2587 LEFT OUTER JOIN sys.objects dobj ON (dobj.object_id = col.default_object_id AND dobj.type = 'D')
2588 WHERE col.object_id = object_id('{$table_name}')
2589 AND col.name = '{$column_name}'
2590 AND dobj.name IS NOT NULL";
2591 }
2592
2593 $result = $this->db->sql_query($sql);
2594 while ($row = $this->db->sql_fetchrow($result))
2595 {
2596 $statements[] = 'ALTER TABLE [' . $table_name . '] DROP CONSTRAINT [' . $row['def_name'] . ']';
2597 }
2598 $this->db->sql_freeresult($result);
2599
2600 return $statements;
2601 }
2602
2603 /**
2604 * Get a list with existing indexes for the column
2605 *
2606 * @param string $table_name
2607 * @param string $column_name
2608 * @param bool $unique Should we get unique indexes or normal ones
2609 * @return array Array with Index name => columns
2610 */
2611 public function get_existing_indexes($table_name, $column_name, $unique = false)
2612 {
2613 switch ($this->sql_layer)
2614 {
2615 case 'mysql_40':
2616 case 'mysql_41':
2617 case 'postgres':
2618 case 'sqlite':
2619 case 'sqlite3':
2620 // Not supported
2621 throw new \Exception('DBMS is not supported');
2622 break;
2623 }
2624
2625 $sql = '';
2626 $existing_indexes = array();
2627
2628 switch ($this->sql_layer)
2629 {
2630 case 'mssql':
2631 case 'mssqlnative':
2632 if ($this->mssql_is_sql_server_2000())
2633 {
2634 // http://msdn.microsoft.com/en-us/library/aa175912%28v=sql.80%29.aspx
2635 // Deprecated in SQL Server 2005
2636 $sql = "SELECT DISTINCT ix.name AS phpbb_index_name
2637 FROM sysindexes ix
2638 INNER JOIN sysindexkeys ixc
2639 ON ixc.id = ix.id
2640 AND ixc.indid = ix.indid
2641 INNER JOIN syscolumns cols
2642 ON cols.colid = ixc.colid
2643 AND cols.id = ix.id
2644 WHERE ix.id = object_id('{$table_name}')
2645 AND cols.name = '{$column_name}'
2646 AND INDEXPROPERTY(ix.id, ix.name, 'IsUnique') = " . ($unique) ? '1' : '0';
2647 }
2648 else
2649 {
2650 $sql = "SELECT DISTINCT ix.name AS phpbb_index_name
2651 FROM sys.indexes ix
2652 INNER JOIN sys.index_columns ixc
2653 ON ixc.object_id = ix.object_id
2654 AND ixc.index_id = ix.index_id
2655 INNER JOIN sys.columns cols
2656 ON cols.column_id = ixc.column_id
2657 AND cols.object_id = ix.object_id
2658 WHERE ix.object_id = object_id('{$table_name}')
2659 AND cols.name = '{$column_name}'
2660 AND ix.is_unique = " . ($unique) ? '1' : '0';
2661 }
2662 break;
2663
2664 case 'oracle':
2665 $sql = "SELECT ix.index_name AS phpbb_index_name, ix.uniqueness AS is_unique
2666 FROM all_ind_columns ixc, all_indexes ix
2667 WHERE ix.index_name = ixc.index_name
2668 AND ixc.table_name = '" . strtoupper($table_name) . "'
2669 AND ixc.column_name = '" . strtoupper($column_name) . "'";
2670 break;
2671 }
2672
2673 $result = $this->db->sql_query($sql);
2674 while ($row = $this->db->sql_fetchrow($result))
2675 {
2676 if (!isset($row['is_unique']) || ($unique && $row['is_unique'] == 'UNIQUE') || (!$unique && $row['is_unique'] == 'NONUNIQUE'))
2677 {
2678 $existing_indexes[$row['phpbb_index_name']] = array();
2679 }
2680 }
2681 $this->db->sql_freeresult($result);
2682
2683 if (empty($existing_indexes))
2684 {
2685 return array();
2686 }
2687
2688 switch ($this->sql_layer)
2689 {
2690 case 'mssql':
2691 case 'mssqlnative':
2692 if ($this->mssql_is_sql_server_2000())
2693 {
2694 $sql = "SELECT DISTINCT ix.name AS phpbb_index_name, cols.name AS phpbb_column_name
2695 FROM sysindexes ix
2696 INNER JOIN sysindexkeys ixc
2697 ON ixc.id = ix.id
2698 AND ixc.indid = ix.indid
2699 INNER JOIN syscolumns cols
2700 ON cols.colid = ixc.colid
2701 AND cols.id = ix.id
2702 WHERE ix.id = object_id('{$table_name}')
2703 AND " . $this->db->sql_in_set('ix.name', array_keys($existing_indexes));
2704 }
2705 else
2706 {
2707 $sql = "SELECT DISTINCT ix.name AS phpbb_index_name, cols.name AS phpbb_column_name
2708 FROM sys.indexes ix
2709 INNER JOIN sys.index_columns ixc
2710 ON ixc.object_id = ix.object_id
2711 AND ixc.index_id = ix.index_id
2712 INNER JOIN sys.columns cols
2713 ON cols.column_id = ixc.column_id
2714 AND cols.object_id = ix.object_id
2715 WHERE ix.object_id = object_id('{$table_name}')
2716 AND " . $this->db->sql_in_set('ix.name', array_keys($existing_indexes));
2717 }
2718 break;
2719
2720 case 'oracle':
2721 $sql = "SELECT index_name AS phpbb_index_name, column_name AS phpbb_column_name
2722 FROM all_ind_columns
2723 WHERE table_name = '" . strtoupper($table_name) . "'
2724 AND " . $this->db->sql_in_set('index_name', array_keys($existing_indexes));
2725 break;
2726 }
2727
2728 $result = $this->db->sql_query($sql);
2729 while ($row = $this->db->sql_fetchrow($result))
2730 {
2731 $existing_indexes[$row['phpbb_index_name']][] = $row['phpbb_column_name'];
2732 }
2733 $this->db->sql_freeresult($result);
2734
2735 return $existing_indexes;
2736 }
2737
2738 /**
2739 * Is the used MS SQL Server a SQL Server 2000?
2740 *
2741 * @return bool
2742 */
2743 protected function mssql_is_sql_server_2000()
2744 {
2745 if ($this->is_sql_server_2000 === null)
2746 {
2747 $sql = "SELECT CAST(SERVERPROPERTY('productversion') AS VARCHAR(25)) AS mssql_version";
2748 $result = $this->db->sql_query($sql);
2749 $properties = $this->db->sql_fetchrow($result);
2750 $this->db->sql_freeresult($result);
2751 $this->is_sql_server_2000 = $properties['mssql_version'][0] == '8';
2752 }
2753
2754 return $this->is_sql_server_2000;
2755 }
2756
2757 /**
2758 * Returns the Queries which are required to recreate a table including indexes
2759 *
2760 * @param string $table_name
2761 * @param string $remove_column When we drop a column, we remove the column
2762 * from all indexes. If the index has no other
2763 * column, we drop it completly.
2764 * @return array
2765 */
2766 protected function sqlite_get_recreate_table_queries($table_name, $remove_column = '')
2767 {
2768 $queries = array();
2769
2770 $sql = "SELECT sql
2771 FROM sqlite_master
2772 WHERE type = 'table'
2773 AND name = '{$table_name}'";
2774 $result = $this->db->sql_query($sql);
2775 $sql_create_table = $this->db->sql_fetchfield('sql');
2776 $this->db->sql_freeresult($result);
2777
2778 if (!$sql_create_table)
2779 {
2780 return array();
2781 }
2782 $queries[] = $sql_create_table;
2783
2784 $sql = "SELECT sql
2785 FROM sqlite_master
2786 WHERE type = 'index'
2787 AND tbl_name = '{$table_name}'";
2788 $result = $this->db->sql_query($sql);
2789 while ($sql_create_index = $this->db->sql_fetchfield('sql'))
2790 {
2791 if ($remove_column)
2792 {
2793 $match = array();
2794 preg_match('#(?:[\w ]+)\((.*)\)#', $sql_create_index, $match);
2795 if (!isset($match[1]))
2796 {
2797 continue;
2798 }
2799
2800 // Find and remove $remove_column from the index
2801 $columns = explode(', ', $match[1]);
2802 $found_column = array_search($remove_column, $columns);
2803 if ($found_column !== false)
2804 {
2805 unset($columns[$found_column]);
2806
2807 // If the column list is not empty add the index to the list
2808 if (!empty($columns))
2809 {
2810 $queries[] = str_replace($match[1], implode(', ', $columns), $sql_create_index);
2811 }
2812 }
2813 else
2814 {
2815 $queries[] = $sql_create_index;
2816 }
2817 }
2818 else
2819 {
2820 $queries[] = $sql_create_index;
2821 }
2822 }
2823 $this->db->sql_freeresult($result);
2824
2825 return $queries;
2826 }
2827 }
2828