Verzeichnisstruktur phpBB-3.0.0
- Veröffentlicht
- 12.12.2007
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 |
db_tools.php
0001 <?php
0002 /**
0003 *
0004 * @package dbal
0005 * @version $Id$
0006 * @copyright (c) 2007 phpBB Group
0007 * @license http://opensource.org/licenses/gpl-license.php GNU Public License
0008 *
0009 */
0010
0011 /**
0012 * @ignore
0013 */
0014 if (!defined('IN_PHPBB'))
0015 {
0016 exit;
0017 }
0018
0019 /**
0020 * Database Tools for handling cross-db actions such as altering columns, etc.
0021 * Currently not supported is returning SQL for creating tables.
0022 *
0023 * @package dbal
0024 * @note currently not used within phpBB3, but may be utilized later.
0025 */
0026 class phpbb_db_tools
0027 {
0028 /**
0029 * Current sql layer
0030 */
0031 var $sql_layer = '';
0032
0033 var $dbms_type_map = array(
0034 'mysql_41' => array(
0035 'INT:' => 'int(%d)',
0036 'BINT' => 'bigint(20)',
0037 'UINT' => 'mediumint(8) UNSIGNED',
0038 'UINT:' => 'int(%d) UNSIGNED',
0039 'TINT:' => 'tinyint(%d)',
0040 'USINT' => 'smallint(4) UNSIGNED',
0041 'BOOL' => 'tinyint(1) UNSIGNED',
0042 'VCHAR' => 'varchar(255)',
0043 'VCHAR:' => 'varchar(%d)',
0044 'CHAR:' => 'char(%d)',
0045 'XSTEXT' => 'text',
0046 'XSTEXT_UNI'=> 'varchar(100)',
0047 'STEXT' => 'text',
0048 'STEXT_UNI' => 'varchar(255)',
0049 'TEXT' => 'text',
0050 'TEXT_UNI' => 'text',
0051 'MTEXT' => 'mediumtext',
0052 'MTEXT_UNI' => 'mediumtext',
0053 'TIMESTAMP' => 'int(11) UNSIGNED',
0054 'DECIMAL' => 'decimal(5,2)',
0055 'DECIMAL:' => 'decimal(%d,2)',
0056 'PDECIMAL' => 'decimal(6,3)',
0057 'PDECIMAL:' => 'decimal(%d,3)',
0058 'VCHAR_UNI' => 'varchar(255)',
0059 'VCHAR_UNI:'=> 'varchar(%d)',
0060 'VCHAR_CI' => 'varchar(255)',
0061 'VARBINARY' => 'varbinary(255)',
0062 ),
0063
0064 'mysql_40' => array(
0065 'INT:' => 'int(%d)',
0066 'BINT' => 'bigint(20)',
0067 'UINT' => 'mediumint(8) UNSIGNED',
0068 'UINT:' => 'int(%d) UNSIGNED',
0069 'TINT:' => 'tinyint(%d)',
0070 'USINT' => 'smallint(4) UNSIGNED',
0071 'BOOL' => 'tinyint(1) UNSIGNED',
0072 'VCHAR' => 'varbinary(255)',
0073 'VCHAR:' => 'varbinary(%d)',
0074 'CHAR:' => 'binary(%d)',
0075 'XSTEXT' => 'blob',
0076 'XSTEXT_UNI'=> 'blob',
0077 'STEXT' => 'blob',
0078 'STEXT_UNI' => 'blob',
0079 'TEXT' => 'blob',
0080 'TEXT_UNI' => 'blob',
0081 'MTEXT' => 'mediumblob',
0082 'MTEXT_UNI' => 'mediumblob',
0083 'TIMESTAMP' => 'int(11) UNSIGNED',
0084 'DECIMAL' => 'decimal(5,2)',
0085 'DECIMAL:' => 'decimal(%d,2)',
0086 'PDECIMAL' => 'decimal(6,3)',
0087 'PDECIMAL:' => 'decimal(%d,3)',
0088 'VCHAR_UNI' => 'blob',
0089 'VCHAR_UNI:'=> array('varbinary(%d)', 'limit' => array('mult', 3, 255, 'blob')),
0090 'VCHAR_CI' => 'blob',
0091 'VARBINARY' => 'varbinary(255)',
0092 ),
0093
0094 'firebird' => array(
0095 'INT:' => 'INTEGER',
0096 'BINT' => 'DOUBLE PRECISION',
0097 'UINT' => 'INTEGER',
0098 'UINT:' => 'INTEGER',
0099 'TINT:' => 'INTEGER',
0100 'USINT' => 'INTEGER',
0101 'BOOL' => 'INTEGER',
0102 'VCHAR' => 'VARCHAR(255) CHARACTER SET NONE',
0103 'VCHAR:' => 'VARCHAR(%d) CHARACTER SET NONE',
0104 'CHAR:' => 'CHAR(%d) CHARACTER SET NONE',
0105 'XSTEXT' => 'BLOB SUB_TYPE TEXT CHARACTER SET NONE',
0106 'STEXT' => 'BLOB SUB_TYPE TEXT CHARACTER SET NONE',
0107 'TEXT' => 'BLOB SUB_TYPE TEXT CHARACTER SET NONE',
0108 'MTEXT' => 'BLOB SUB_TYPE TEXT CHARACTER SET NONE',
0109 'XSTEXT_UNI'=> 'VARCHAR(100) CHARACTER SET UTF8',
0110 'STEXT_UNI' => 'VARCHAR(255) CHARACTER SET UTF8',
0111 'TEXT_UNI' => 'BLOB SUB_TYPE TEXT CHARACTER SET UTF8',
0112 'MTEXT_UNI' => 'BLOB SUB_TYPE TEXT CHARACTER SET UTF8',
0113 'TIMESTAMP' => 'INTEGER',
0114 'DECIMAL' => 'DOUBLE PRECISION',
0115 'DECIMAL:' => 'DOUBLE PRECISION',
0116 'PDECIMAL' => 'DOUBLE PRECISION',
0117 'PDECIMAL:' => 'DOUBLE PRECISION',
0118 'VCHAR_UNI' => 'VARCHAR(255) CHARACTER SET UTF8',
0119 'VCHAR_UNI:'=> 'VARCHAR(%d) CHARACTER SET UTF8',
0120 'VCHAR_CI' => 'VARCHAR(255) CHARACTER SET UTF8',
0121 'VARBINARY' => 'CHAR(255) CHARACTER SET NONE',
0122 ),
0123
0124 'mssql' => array(
0125 'INT:' => '[int]',
0126 'BINT' => '[float]',
0127 'UINT' => '[int]',
0128 'UINT:' => '[int]',
0129 'TINT:' => '[int]',
0130 'USINT' => '[int]',
0131 'BOOL' => '[int]',
0132 'VCHAR' => '[varchar] (255)',
0133 'VCHAR:' => '[varchar] (%d)',
0134 'CHAR:' => '[char] (%d)',
0135 'XSTEXT' => '[varchar] (1000)',
0136 'STEXT' => '[varchar] (3000)',
0137 'TEXT' => '[varchar] (8000)',
0138 'MTEXT' => '[text]',
0139 'XSTEXT_UNI'=> '[varchar] (100)',
0140 'STEXT_UNI' => '[varchar] (255)',
0141 'TEXT_UNI' => '[varchar] (4000)',
0142 'MTEXT_UNI' => '[text]',
0143 'TIMESTAMP' => '[int]',
0144 'DECIMAL' => '[float]',
0145 'DECIMAL:' => '[float]',
0146 'PDECIMAL' => '[float]',
0147 'PDECIMAL:' => '[float]',
0148 'VCHAR_UNI' => '[varchar] (255)',
0149 'VCHAR_UNI:'=> '[varchar] (%d)',
0150 'VCHAR_CI' => '[varchar] (255)',
0151 'VARBINARY' => '[varchar] (255)',
0152 ),
0153
0154 'oracle' => array(
0155 'INT:' => 'number(%d)',
0156 'BINT' => 'number(20)',
0157 'UINT' => 'number(8)',
0158 'UINT:' => 'number(%d)',
0159 'TINT:' => 'number(%d)',
0160 'USINT' => 'number(4)',
0161 'BOOL' => 'number(1)',
0162 'VCHAR' => 'varchar2(255)',
0163 'VCHAR:' => 'varchar2(%d)',
0164 'CHAR:' => 'char(%d)',
0165 'XSTEXT' => 'varchar2(1000)',
0166 'STEXT' => 'varchar2(3000)',
0167 'TEXT' => 'clob',
0168 'MTEXT' => 'clob',
0169 'XSTEXT_UNI'=> 'varchar2(300)',
0170 'STEXT_UNI' => 'varchar2(765)',
0171 'TEXT_UNI' => 'clob',
0172 'MTEXT_UNI' => 'clob',
0173 'TIMESTAMP' => 'number(11)',
0174 'DECIMAL' => 'number(5, 2)',
0175 'DECIMAL:' => 'number(%d, 2)',
0176 'PDECIMAL' => 'number(6, 3)',
0177 'PDECIMAL:' => 'number(%d, 3)',
0178 'VCHAR_UNI' => 'varchar2(765)',
0179 'VCHAR_UNI:'=> array('varchar2(%d)', 'limit' => array('mult', 3, 765, 'clob')),
0180 'VCHAR_CI' => 'varchar2(255)',
0181 'VARBINARY' => 'raw(255)',
0182 ),
0183
0184 'sqlite' => array(
0185 'INT:' => 'int(%d)',
0186 'BINT' => 'bigint(20)',
0187 'UINT' => 'INTEGER UNSIGNED', //'mediumint(8) UNSIGNED',
0188 'UINT:' => 'INTEGER UNSIGNED', // 'int(%d) UNSIGNED',
0189 'TINT:' => 'tinyint(%d)',
0190 'USINT' => 'INTEGER UNSIGNED', //'mediumint(4) UNSIGNED',
0191 'BOOL' => 'INTEGER UNSIGNED', //'tinyint(1) UNSIGNED',
0192 'VCHAR' => 'varchar(255)',
0193 'VCHAR:' => 'varchar(%d)',
0194 'CHAR:' => 'char(%d)',
0195 'XSTEXT' => 'text(65535)',
0196 'STEXT' => 'text(65535)',
0197 'TEXT' => 'text(65535)',
0198 'MTEXT' => 'mediumtext(16777215)',
0199 'XSTEXT_UNI'=> 'text(65535)',
0200 'STEXT_UNI' => 'text(65535)',
0201 'TEXT_UNI' => 'text(65535)',
0202 'MTEXT_UNI' => 'mediumtext(16777215)',
0203 'TIMESTAMP' => 'INTEGER UNSIGNED', //'int(11) UNSIGNED',
0204 'DECIMAL' => 'decimal(5,2)',
0205 'DECIMAL:' => 'decimal(%d,2)',
0206 'PDECIMAL' => 'decimal(6,3)',
0207 'PDECIMAL:' => 'decimal(%d,3)',
0208 'VCHAR_UNI' => 'varchar(255)',
0209 'VCHAR_UNI:'=> 'varchar(%d)',
0210 'VCHAR_CI' => 'varchar(255)',
0211 'VARBINARY' => 'blob',
0212 ),
0213
0214 'postgres' => array(
0215 'INT:' => 'INT4',
0216 'BINT' => 'INT8',
0217 'UINT' => 'INT4', // unsigned
0218 'UINT:' => 'INT4', // unsigned
0219 'USINT' => 'INT2', // unsigned
0220 'BOOL' => 'INT2', // unsigned
0221 'TINT:' => 'INT2',
0222 'VCHAR' => 'varchar(255)',
0223 'VCHAR:' => 'varchar(%d)',
0224 'CHAR:' => 'char(%d)',
0225 'XSTEXT' => 'varchar(1000)',
0226 'STEXT' => 'varchar(3000)',
0227 'TEXT' => 'varchar(8000)',
0228 'MTEXT' => 'TEXT',
0229 'XSTEXT_UNI'=> 'varchar(100)',
0230 'STEXT_UNI' => 'varchar(255)',
0231 'TEXT_UNI' => 'varchar(4000)',
0232 'MTEXT_UNI' => 'TEXT',
0233 'TIMESTAMP' => 'INT4', // unsigned
0234 'DECIMAL' => 'decimal(5,2)',
0235 'DECIMAL:' => 'decimal(%d,2)',
0236 'PDECIMAL' => 'decimal(6,3)',
0237 'PDECIMAL:' => 'decimal(%d,3)',
0238 'VCHAR_UNI' => 'varchar(255)',
0239 'VCHAR_UNI:'=> 'varchar(%d)',
0240 'VCHAR_CI' => 'varchar_ci',
0241 'VARBINARY' => 'bytea',
0242 ),
0243 );
0244
0245 // A list of types being unsigned for better reference in some db's
0246 var $unsigned_types = array('UINT', 'UINT:', 'USINT', 'BOOL', 'TIMESTAMP');
0247 var $supported_dbms = array('firebird', 'mssql', 'mysql_40', 'mysql_41', 'oracle', 'postgres', 'sqlite');
0248
0249 /**
0250 * Set this to true if you only want to return the 'to-be-executed' SQL statement(s) (as an array).
0251 */
0252 var $return_statements = false;
0253
0254 /**
0255 */
0256 function phpbb_db_tools(&$db)
0257 {
0258 $this->db = $db;
0259
0260 // Determine mapping database type
0261 switch ($this->db->sql_layer)
0262 {
0263 case 'mysql':
0264 $this->sql_layer = 'mysql_40';
0265 break;
0266
0267 case 'mysql4':
0268 if (version_compare($this->db->mysql_version, '4.1.3', '>='))
0269 {
0270 $this->sql_layer = 'mysql_41';
0271 }
0272 else
0273 {
0274 $this->sql_layer = 'mysql_40';
0275 }
0276 break;
0277
0278 case 'mysqli':
0279 $this->sql_layer = 'mysql_41';
0280 break;
0281
0282 case 'mssql':
0283 case 'mssql_odbc':
0284 $this->sql_layer = 'mssql';
0285 break;
0286
0287 default:
0288 $this->sql_layer = $this->db->sql_layer;
0289 break;
0290 }
0291 }
0292
0293 /**
0294 * Handle passed database update array.
0295 * Expected structure...
0296 * Key being one of the following
0297 * change_columns: Column changes (only type, not name)
0298 * add_columns: Add columns to a table
0299 * drop_keys: Dropping keys
0300 * drop_columns: Removing/Dropping columns
0301 * add_primary_keys: adding primary keys
0302 * add_unique_index: adding an unique index
0303 * add_index: adding an index
0304 *
0305 * The values are in this format:
0306 * {TABLE NAME} => array(
0307 * {COLUMN NAME} => array({COLUMN TYPE}, {DEFAULT VALUE}, {OPTIONAL VARIABLES}),
0308 * {KEY/INDEX NAME} => array({COLUMN NAMES}),
0309 * )
0310 *
0311 * For more information have a look at /develop/create_schema_files.php (only available through CVS)
0312 */
0313 function perform_schema_changes($schema_changes)
0314 {
0315 if (empty($schema_changes))
0316 {
0317 return;
0318 }
0319
0320 $statements = array();
0321
0322 // Change columns?
0323 if (!empty($schema_changes['change_columns']))
0324 {
0325 foreach ($schema_changes['change_columns'] as $table => $columns)
0326 {
0327 foreach ($columns as $column_name => $column_data)
0328 {
0329 $result = $this->sql_column_change($table, $column_name, $column_data);
0330
0331 if ($this->return_statements)
0332 {
0333 $statements = array_merge($statements, $result);
0334 }
0335 }
0336 }
0337 }
0338
0339 // Add columns?
0340 if (!empty($schema_changes['add_columns']))
0341 {
0342 foreach ($schema_changes['add_columns'] as $table => $columns)
0343 {
0344 foreach ($columns as $column_name => $column_data)
0345 {
0346 // Only add the column if it does not exist yet
0347 if (!$this->sql_column_exists($table, $column_name))
0348 {
0349 $result = $this->sql_column_add($table, $column_name, $column_data);
0350
0351 if ($this->return_statements)
0352 {
0353 $statements = array_merge($statements, $result);
0354 }
0355 }
0356 }
0357 }
0358 }
0359
0360 // Remove keys?
0361 if (!empty($schema_changes['drop_keys']))
0362 {
0363 foreach ($schema_changes['drop_keys'] as $table => $indexes)
0364 {
0365 foreach ($indexes as $index_name)
0366 {
0367 $result = $this->sql_index_drop($table, $index_name);
0368
0369 if ($this->return_statements)
0370 {
0371 $statements = array_merge($statements, $result);
0372 }
0373 }
0374 }
0375 }
0376
0377 // Drop columns?
0378 if (!empty($schema_changes['drop_columns']))
0379 {
0380 foreach ($schema_changes['drop_columns'] as $table => $columns)
0381 {
0382 foreach ($columns as $column)
0383 {
0384 $result = $this->sql_column_remove($table, $column);
0385
0386 if ($this->return_statements)
0387 {
0388 $statements = array_merge($statements, $result);
0389 }
0390 }
0391 }
0392 }
0393
0394 // Add primary keys?
0395 if (!empty($schema_changes['add_primary_keys']))
0396 {
0397 foreach ($schema_changes['add_primary_keys'] as $table => $columns)
0398 {
0399 $result = $this->sql_create_primary_key($table, $columns);
0400
0401 if ($this->return_statements)
0402 {
0403 $statements = array_merge($statements, $result);
0404 }
0405 }
0406 }
0407
0408 // Add unqiue indexes?
0409 if (!empty($schema_changes['add_unique_index']))
0410 {
0411 foreach ($schema_changes['add_unique_index'] as $table => $index_array)
0412 {
0413 foreach ($index_array as $index_name => $column)
0414 {
0415 $result = $this->sql_create_unique_index($table, $index_name, $column);
0416
0417 if ($this->return_statements)
0418 {
0419 $statements = array_merge($statements, $result);
0420 }
0421 }
0422 }
0423 }
0424
0425 // Add indexes?
0426 if (!empty($schema_changes['add_index']))
0427 {
0428 foreach ($schema_changes['add_index'] as $table => $index_array)
0429 {
0430 foreach ($index_array as $index_name => $column)
0431 {
0432 $result = $this->sql_create_index($table, $index_name, $column);
0433
0434 if ($this->return_statements)
0435 {
0436 $statements = array_merge($statements, $result);
0437 }
0438 }
0439 }
0440 }
0441
0442 if ($this->return_statements)
0443 {
0444 return $statements;
0445 }
0446 }
0447
0448 /**
0449 * Check if a specified column exist
0450 * @return bool True if column exists, else false
0451 */
0452 function sql_column_exists($table, $column_name)
0453 {
0454 switch ($this->sql_layer)
0455 {
0456 case 'mysql_40':
0457 case 'mysql_41':
0458
0459 $sql = "SHOW COLUMNS FROM $table";
0460 $result = $this->db->sql_query($sql);
0461
0462 while ($row = $this->db->sql_fetchrow($result))
0463 {
0464 // lower case just in case
0465 if (strtolower($row['Field']) == $column_name)
0466 {
0467 $this->db->sql_freeresult($result);
0468 return true;
0469 }
0470 }
0471 $this->db->sql_freeresult($result);
0472 return false;
0473 break;
0474
0475 // PostgreSQL has a way of doing this in a much simpler way but would
0476 // not allow us to support all versions of PostgreSQL
0477 case 'postgres':
0478 $sql = "SELECT a.attname
0479 FROM pg_class c, pg_attribute a
0480 WHERE c.relname = '{$table}'
0481 AND a.attnum > 0
0482 AND a.attrelid = c.oid";
0483 $result = $this->db->sql_query($sql);
0484 while ($row = $this->db->sql_fetchrow($result))
0485 {
0486 // lower case just in case
0487 if (strtolower($row['attname']) == $column_name)
0488 {
0489 $this->db->sql_freeresult($result);
0490 return true;
0491 }
0492 }
0493 $this->db->sql_freeresult($result);
0494
0495 return false;
0496 break;
0497
0498 // same deal with PostgreSQL, we must perform more complex operations than
0499 // we technically could
0500 case 'mssql':
0501 $sql = "SELECT c.name
0502 FROM syscolumns c
0503 LEFT JOIN sysobjects o ON c.id = o.id
0504 WHERE o.name = '{$table}'";
0505 $result = $this->db->sql_query($sql);
0506 while ($row = $this->db->sql_fetchrow($result))
0507 {
0508 // lower case just in case
0509 if (strtolower($row['name']) == $column_name)
0510 {
0511 $this->db->sql_freeresult($result);
0512 return true;
0513 }
0514 }
0515 $this->db->sql_freeresult($result);
0516 return false;
0517 break;
0518
0519 case 'oracle':
0520 $sql = "SELECT column_name
0521 FROM user_tab_columns
0522 WHERE table_name = '{$table}'";
0523 $result = $this->db->sql_query($sql);
0524 while ($row = $this->db->sql_fetchrow($result))
0525 {
0526 // lower case just in case
0527 if (strtolower($row['column_name']) == $column_name)
0528 {
0529 $this->db->sql_freeresult($result);
0530 return true;
0531 }
0532 }
0533 $this->db->sql_freeresult($result);
0534 return false;
0535 break;
0536
0537 case 'firebird':
0538 $sql = "SELECT RDB\$FIELD_NAME as FNAME
0539 FROM RDB\$RELATION_FIELDS
0540 WHERE RDB\$RELATION_NAME = '{$table}'";
0541 $result = $this->db->sql_query($sql);
0542 while ($row = $this->db->sql_fetchrow($result))
0543 {
0544 // lower case just in case
0545 if (strtolower($row['fname']) == $column_name)
0546 {
0547 $this->db->sql_freeresult($result);
0548 return true;
0549 }
0550 }
0551 $this->db->sql_freeresult($result);
0552 return false;
0553 break;
0554
0555 // ugh, SQLite
0556 case 'sqlite':
0557 $sql = "SELECT sql
0558 FROM sqlite_master
0559 WHERE type = 'table'
0560 AND name = '{$table}'";
0561 $result = $this->db->sql_query($sql);
0562
0563 if (!$result)
0564 {
0565 return false;
0566 }
0567
0568 $row = $this->db->sql_fetchrow($result);
0569 $this->db->sql_freeresult($result);
0570
0571 preg_match('#\((.*)\)#s', $row['sql'], $matches);
0572
0573 $cols = trim($matches[1]);
0574 $col_array = preg_split('/,(?![\s\w]+\))/m', $cols);
0575
0576 foreach ($col_array as $declaration)
0577 {
0578 $entities = preg_split('#\s+#', trim($declaration));
0579 if ($entities[0] == 'PRIMARY')
0580 {
0581 continue;
0582 }
0583
0584 if (strtolower($entities[0]) == $column_name)
0585 {
0586 return true;
0587 }
0588 }
0589 return false;
0590 break;
0591 }
0592 }
0593
0594 /**
0595 * Private method for performing sql statements (either execute them or return them)
0596 * @private
0597 */
0598 function _sql_run_sql($statements)
0599 {
0600 if ($this->return_statements)
0601 {
0602 return $statements;
0603 }
0604
0605 // We could add error handling here...
0606 foreach ($statements as $sql)
0607 {
0608 if ($sql === 'begin')
0609 {
0610 $this->db->sql_transaction('begin');
0611 }
0612 else if ($sql === 'commit')
0613 {
0614 $this->db->sql_transaction('commit');
0615 }
0616 else
0617 {
0618 $this->db->sql_query($sql);
0619 }
0620 }
0621
0622 return true;
0623 }
0624
0625 /**
0626 * Function to prepare some column information for better usage
0627 * @private
0628 */
0629 function sql_prepare_column_data($table_name, $column_name, $column_data)
0630 {
0631 // Get type
0632 if (strpos($column_data[0], ':') !== false)
0633 {
0634 list($orig_column_type, $column_length) = explode(':', $column_data[0]);
0635
0636 if (!is_array($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']))
0637 {
0638 $column_type = sprintf($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':'], $column_length);
0639 }
0640 else
0641 {
0642 if (isset($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['rule']))
0643 {
0644 switch ($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['rule'][0])
0645 {
0646 case 'div':
0647 $column_length /= $this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['rule'][1];
0648 $column_length = ceil($column_length);
0649 $column_type = sprintf($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':'][0], $column_length);
0650 break;
0651 }
0652 }
0653
0654 if (isset($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['limit']))
0655 {
0656 switch ($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['limit'][0])
0657 {
0658 case 'mult':
0659 $column_length *= $this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['limit'][1];
0660 if ($column_length > $this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['limit'][2])
0661 {
0662 $column_type = $this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['limit'][3];
0663 }
0664 else
0665 {
0666 $column_type = sprintf($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':'][0], $column_length);
0667 }
0668 break;
0669 }
0670 }
0671 }
0672 $orig_column_type .= ':';
0673 }
0674 else
0675 {
0676 $orig_column_type = $column_data[0];
0677 $column_type = $this->dbms_type_map[$this->sql_layer][$column_data[0]];
0678 }
0679
0680 // Adjust default value if db-dependant specified
0681 if (is_array($column_data[1]))
0682 {
0683 $column_data[1] = (isset($column_data[1][$this->sql_layer])) ? $column_data[1][$this->sql_layer] : $column_data[1]['default'];
0684 }
0685
0686 $sql = '';
0687
0688 $return_array = array();
0689
0690 switch ($this->sql_layer)
0691 {
0692 case 'firebird':
0693 $sql .= " {$column_type} ";
0694
0695 if (!is_null($column_data[1]))
0696 {
0697 $sql .= 'DEFAULT ' . ((is_numeric($column_data[1])) ? $column_data[1] : "'{$column_data[1]}'") . ' ';
0698 }
0699
0700 $sql .= 'NOT NULL';
0701
0702 // This is a UNICODE column and thus should be given it's fair share
0703 if (preg_match('/^X?STEXT_UNI|VCHAR_(CI|UNI:?)/', $column_data[0]))
0704 {
0705 $sql .= ' COLLATE UNICODE';
0706 }
0707
0708 break;
0709
0710 case 'mssql':
0711 $sql .= " {$column_type} ";
0712 $sql_default = " {$column_type} ";
0713
0714 // For adding columns we need the default definition
0715 if (!is_null($column_data[1]))
0716 {
0717 // For hexadecimal values do not use single quotes
0718 if (strpos($column_data[1], '0x') === 0)
0719 {
0720 $sql_default .= 'DEFAULT (' . $column_data[1] . ') ';
0721 }
0722 else
0723 {
0724 $sql_default .= 'DEFAULT (' . ((is_numeric($column_data[1])) ? $column_data[1] : "'{$column_data[1]}'") . ') ';
0725 }
0726 }
0727
0728 $sql .= 'NOT NULL';
0729 $sql_default .= 'NOT NULL';
0730
0731 $return_array['column_type_sql_default'] = $sql_default;
0732 break;
0733
0734 case 'mysql_40':
0735 case 'mysql_41':
0736 $sql .= " {$column_type} ";
0737
0738 // For hexadecimal values do not use single quotes
0739 if (!is_null($column_data[1]) && substr($column_type, -4) !== 'text' && substr($column_type, -4) !== 'blob')
0740 {
0741 $sql .= (strpos($column_data[1], '0x') === 0) ? "DEFAULT {$column_data[1]} " : "DEFAULT '{$column_data[1]}' ";
0742 }
0743 $sql .= 'NOT NULL';
0744
0745 if (isset($column_data[2]))
0746 {
0747 if ($column_data[2] == 'auto_increment')
0748 {
0749 $sql .= ' auto_increment';
0750 }
0751 else if ($this->sql_layer === 'mysql_41' && $column_data[2] == 'true_sort')
0752 {
0753 $sql .= ' COLLATE utf8_unicode_ci';
0754 }
0755 }
0756
0757 break;
0758
0759 case 'oracle':
0760 $sql .= " {$column_type} ";
0761 $sql .= (!is_null($column_data[1])) ? "DEFAULT '{$column_data[1]}' " : '';
0762
0763 // In Oracle empty strings ('') are treated as NULL.
0764 // Therefore in oracle we allow NULL's for all DEFAULT '' entries
0765 // Oracle does not like setting NOT NULL on a column that is already NOT NULL (this happens only on number fields)
0766 if (preg_match('/number/i', $column_type))
0767 {
0768 $sql .= ($column_data[1] === '') ? '' : 'NOT NULL';
0769 }
0770 break;
0771
0772 case 'postgres':
0773 $return_array['column_type'] = $column_type;
0774
0775 $sql .= " {$column_type} ";
0776
0777 if (isset($column_data[2]) && $column_data[2] == 'auto_increment')
0778 {
0779 $default_val = "nextval('{$table_name}_seq')";
0780 }
0781 else if (!is_null($column_data[1]))
0782 {
0783 $default_val = "'" . $column_data[1] . "'";
0784 $return_array['null'] = 'NOT NULL';
0785 $sql .= 'NOT NULL ';
0786 }
0787
0788 $return_array['default'] = $default_val;
0789
0790 $sql .= "DEFAULT {$default_val}";
0791
0792 // Unsigned? Then add a CHECK contraint
0793 if (in_array($orig_column_type, $this->unsigned_types))
0794 {
0795 $return_array['constraint'] = "CHECK ({$column_name} >= 0)";
0796 $sql .= " CHECK ({$column_name} >= 0)";
0797 }
0798 break;
0799
0800 case 'sqlite':
0801 if (isset($column_data[2]) && $column_data[2] == 'auto_increment')
0802 {
0803 $sql .= ' INTEGER PRIMARY KEY';
0804 }
0805 else
0806 {
0807 $sql .= ' ' . $column_type;
0808 }
0809
0810 $sql .= ' NOT NULL ';
0811 $sql .= (!is_null($column_data[1])) ? "DEFAULT '{$column_data[1]}'" : '';
0812 break;
0813 }
0814
0815 $return_array['column_type_sql'] = $sql;
0816
0817 return $return_array;
0818 }
0819
0820 /**
0821 * Add new column
0822 */
0823 function sql_column_add($table_name, $column_name, $column_data)
0824 {
0825 $column_data = $this->sql_prepare_column_data($table_name, $column_name, $column_data);
0826 $statements = array();
0827
0828 switch ($this->sql_layer)
0829 {
0830 case 'firebird':
0831 $statements[] = 'ALTER TABLE "' . $table_name . '" ADD "' . $column_name . '" ' . $column_data['column_type_sql'];
0832 break;
0833
0834 case 'mssql':
0835 $statements[] = 'ALTER TABLE [' . $table_name . '] ADD [' . $column_name . '] ' . $column_data['column_type_sql_default'];
0836 break;
0837
0838 case 'mysql_40':
0839 case 'mysql_41':
0840 $statements[] = 'ALTER TABLE `' . $table_name . '` ADD COLUMN `' . $column_name . '` ' . $column_data['column_type_sql'];
0841 break;
0842
0843 case 'oracle':
0844 $statements[] = 'ALTER TABLE ' . $table_name . ' ADD ' . $column_name . ' ' . $column_data['column_type_sql'];
0845 break;
0846
0847 case 'postgres':
0848 $statements[] = 'ALTER TABLE ' . $table_name . ' ADD COLUMN "' . $column_name . '" ' . $column_data['column_type_sql'];
0849 break;
0850
0851 case 'sqlite':
0852 if (version_compare(sqlite_libversion(), '3.0') == -1)
0853 {
0854 $sql = "SELECT sql
0855 FROM sqlite_master
0856 WHERE type = 'table'
0857 AND name = '{$table_name}'
0858 ORDER BY type DESC, name;";
0859 $result = $this->db->sql_query($sql);
0860
0861 if (!$result)
0862 {
0863 break;
0864 }
0865
0866 $row = $this->db->sql_fetchrow($result);
0867 $this->db->sql_freeresult($result);
0868
0869 $statements[] = 'begin';
0870
0871 // Create a backup table and populate it, destroy the existing one
0872 $statements[] = preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $row['sql']);
0873 $statements[] = 'INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name;
0874 $statements[] = 'DROP TABLE ' . $table_name;
0875
0876 preg_match('#\((.*)\)#s', $row['sql'], $matches);
0877
0878 $new_table_cols = trim($matches[1]);
0879 $old_table_cols = preg_split('/,(?![\s\w]+\))/m', $new_table_cols);
0880 $column_list = array();
0881
0882 foreach ($old_table_cols as $declaration)
0883 {
0884 $entities = preg_split('#\s+#', trim($declaration));
0885 if ($entities[0] == 'PRIMARY')
0886 {
0887 continue;
0888 }
0889 $column_list[] = $entities[0];
0890 }
0891
0892 $columns = implode(',', $column_list);
0893
0894 $new_table_cols = $column_name . ' ' . $column_data['column_type_sql'] . ',' . $new_table_cols;
0895
0896 // create a new table and fill it up. destroy the temp one
0897 $statements[] = 'CREATE TABLE ' . $table_name . ' (' . $new_table_cols . ');';
0898 $statements[] = 'INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;';
0899 $statements[] = 'DROP TABLE ' . $table_name . '_temp';
0900
0901 $statements[] = 'commit';
0902 }
0903 else
0904 {
0905 $statements[] = 'ALTER TABLE ' . $table_name . ' ADD ' . $column_name . ' [' . $column_data['column_type_sql'] . ']';
0906 }
0907 break;
0908 }
0909
0910 return $this->_sql_run_sql($statements);
0911 }
0912
0913 /**
0914 * Drop column
0915 */
0916 function sql_column_remove($table_name, $column_name)
0917 {
0918 $statements = array();
0919
0920 switch ($this->sql_layer)
0921 {
0922 case 'firebird':
0923 $statements[] = 'ALTER TABLE "' . $table_name . '" DROP "' . $column_name . '"';
0924 break;
0925
0926 case 'mssql':
0927 $statements[] = 'ALTER TABLE [' . $table_name . '] DROP COLUMN [' . $column_name . ']';
0928 break;
0929
0930 case 'mysql_40':
0931 case 'mysql_41':
0932 $statements[] = 'ALTER TABLE `' . $table_name . '` DROP COLUMN `' . $column_name . '`';
0933 break;
0934
0935 case 'oracle':
0936 $statements[] = 'ALTER TABLE ' . $table_name . ' DROP ' . $column_name;
0937 break;
0938
0939 case 'postgres':
0940 $statements[] = 'ALTER TABLE ' . $table_name . ' DROP COLUMN "' . $column_name . '"';
0941 break;
0942
0943 case 'sqlite':
0944 if (version_compare(sqlite_libversion(), '3.0') == -1)
0945 {
0946 $sql = "SELECT sql
0947 FROM sqlite_master
0948 WHERE type = 'table'
0949 AND name = '{$table_name}'
0950 ORDER BY type DESC, name;";
0951 $result = $this->db->sql_query($sql);
0952
0953 if (!$result)
0954 {
0955 break;
0956 }
0957
0958 $row = $this->db->sql_fetchrow($result);
0959 $this->db->sql_freeresult($result);
0960
0961 $statements[] = 'begin';
0962
0963 // Create a backup table and populate it, destroy the existing one
0964 $statements[] = preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $row['sql']);
0965 $statements[] = 'INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name;
0966 $statements[] = 'DROP TABLE ' . $table_name;
0967
0968 preg_match('#\((.*)\)#s', $row['sql'], $matches);
0969
0970 $new_table_cols = trim($matches[1]);
0971 $old_table_cols = preg_split('/,(?![\s\w]+\))/m', $new_table_cols);
0972 $column_list = array();
0973
0974 foreach ($old_table_cols as $declaration)
0975 {
0976 $entities = preg_split('#\s+#', trim($declaration));
0977 if ($entities[0] == 'PRIMARY' || $entities[0] === $column_name)
0978 {
0979 continue;
0980 }
0981 $column_list[] = $entities[0];
0982 }
0983
0984 $columns = implode(',', $column_list);
0985
0986 $new_table_cols = $new_table_cols = preg_replace('/' . $column_name . '[^,]+(?:,|$)/m', '', $new_table_cols);
0987
0988 // create a new table and fill it up. destroy the temp one
0989 $statements[] = 'CREATE TABLE ' . $table_name . ' (' . $new_table_cols . ');';
0990 $statements[] = 'INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;';
0991 $statements[] = 'DROP TABLE ' . $table_name . '_temp';
0992
0993 $statements[] = 'commit';
0994 }
0995 else
0996 {
0997 $statements[] = 'ALTER TABLE ' . $table_name . ' DROP COLUMN ' . $column_name;
0998 }
0999 break;
1000 }
1001
1002 return $this->_sql_run_sql($statements);
1003 }
1004
1005 /**
1006 * Drop Index
1007 */
1008 function sql_index_drop($table_name, $index_name)
1009 {
1010 $statements = array();
1011
1012 switch ($this->sql_layer)
1013 {
1014 case 'mssql':
1015 $statements[] = 'DROP INDEX ' . $table_name . '.' . $index_name;
1016 break;
1017
1018 case 'mysql_40':
1019 case 'mysql_41':
1020 $statements[] = 'DROP INDEX ' . $index_name . ' ON ' . $table_name;
1021 break;
1022
1023 case 'firebird':
1024 case 'oracle':
1025 case 'postgres':
1026 case 'sqlite':
1027 $statements[] = 'DROP INDEX ' . $table_name . '_' . $index_name;
1028 break;
1029 }
1030
1031 return $this->_sql_run_sql($statements);
1032 }
1033
1034 /**
1035 * Add primary key
1036 */
1037 function sql_create_primary_key($table_name, $column)
1038 {
1039 $statements = array();
1040
1041 switch ($this->sql_layer)
1042 {
1043 case 'firebird':
1044 case 'postgres':
1045 $statements[] = 'ALTER TABLE ' . $table_name . ' ADD PRIMARY KEY (' . implode(', ', $column) . ')';
1046 break;
1047
1048 case 'mssql':
1049 $sql = "ALTER TABLE [{$table_name}] WITH NOCHECK ADD ";
1050 $sql .= "CONSTRAINT [PK_{$table_name}] PRIMARY KEY CLUSTERED (";
1051 $sql .= '[' . implode("],\n\t\t[", $column) . ']';
1052 $sql .= ') ON [PRIMARY]';
1053
1054 $statements[] = $sql;
1055 break;
1056
1057 case 'mysql_40':
1058 case 'mysql_41':
1059 $statements[] = 'ALTER TABLE ' . $table_name . ' ADD PRIMARY KEY (' . implode(', ', $column) . ')';
1060 break;
1061
1062 case 'oracle':
1063 $statements[] = 'ALTER TABLE ' . $table_name . 'add CONSTRAINT pk_' . $table_name . ' PRIMARY KEY (' . implode(', ', $column) . ')';
1064 break;
1065
1066 case 'sqlite':
1067 $sql = "SELECT sql
1068 FROM sqlite_master
1069 WHERE type = 'table'
1070 AND name = '{$table_name}'
1071 ORDER BY type DESC, name;";
1072 $result = $this->db->sql_query($sql);
1073
1074 if (!$result)
1075 {
1076 break;
1077 }
1078
1079 $row = $this->db->sql_fetchrow($result);
1080 $this->db->sql_freeresult($result);
1081
1082 $statements[] = 'begin';
1083
1084 // Create a backup table and populate it, destroy the existing one
1085 $statements[] = preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $row['sql']);
1086 $statements[] = 'INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name;
1087 $statements[] = 'DROP TABLE ' . $table_name;
1088
1089 preg_match('#\((.*)\)#s', $row['sql'], $matches);
1090
1091 $new_table_cols = trim($matches[1]);
1092 $old_table_cols = preg_split('/,(?![\s\w]+\))/m', $new_table_cols);
1093 $column_list = array();
1094
1095 foreach ($old_table_cols as $declaration)
1096 {
1097 $entities = preg_split('#\s+#', trim($declaration));
1098 if ($entities[0] == 'PRIMARY')
1099 {
1100 continue;
1101 }
1102 $column_list[] = $entities[0];
1103 }
1104
1105 $columns = implode(',', $column_list);
1106
1107 // create a new table and fill it up. destroy the temp one
1108 $statements[] = 'CREATE TABLE ' . $table_name . ' (' . $new_table_cols . ', PRIMARY KEY (' . implode(', ', $column) . '));';
1109 $statements[] = 'INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;';
1110 $statements[] = 'DROP TABLE ' . $table_name . '_temp';
1111
1112 $statements[] = 'commit';
1113 break;
1114 }
1115
1116 return $this->_sql_run_sql($statements);
1117 }
1118
1119 /**
1120 * Add unique index
1121 */
1122 function sql_create_unique_index($table_name, $index_name, $column)
1123 {
1124 $statements = array();
1125
1126 switch ($this->sql_layer)
1127 {
1128 case 'firebird':
1129 case 'postgres':
1130 case 'oracle':
1131 case 'sqlite':
1132 $statements[] = 'CREATE UNIQUE INDEX ' . $table_name . '_' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ')';
1133 break;
1134
1135 case 'mysql_40':
1136 case 'mysql_41':
1137 $statements[] = 'CREATE UNIQUE INDEX ' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ')';
1138 break;
1139
1140 case 'mssql':
1141 $statements[] = 'CREATE UNIQUE INDEX ' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ') ON [PRIMARY]';
1142 break;
1143 }
1144
1145 return $this->_sql_run_sql($statements);
1146 }
1147
1148 /**
1149 * Add index
1150 */
1151 function sql_create_index($table_name, $index_name, $column)
1152 {
1153 $statements = array();
1154
1155 switch ($this->sql_layer)
1156 {
1157 case 'firebird':
1158 case 'postgres':
1159 case 'oracle':
1160 case 'sqlite':
1161 $statements[] = 'CREATE INDEX ' . $table_name . '_' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ')';
1162 break;
1163
1164 case 'mysql_40':
1165 case 'mysql_41':
1166 $statements[] = 'CREATE INDEX ' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ')';
1167 break;
1168
1169 case 'mssql':
1170 $statements[] = 'CREATE INDEX ' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ') ON [PRIMARY]';
1171 break;
1172 }
1173
1174 return $this->_sql_run_sql($statements);
1175 }
1176
1177 /**
1178 * List all of the indices that belong to a table,
1179 * does not count:
1180 * * UNIQUE indices
1181 * * PRIMARY keys
1182 */
1183 function sql_list_index($table_name)
1184 {
1185 $index_array = array();
1186
1187 if ($this->sql_layer == 'mssql')
1188 {
1189 $sql = "EXEC sp_statistics '$table_name'";
1190 $result = $this->db->sql_query($sql);
1191 while ($row = $this->db->sql_fetchrow($result))
1192 {
1193 if ($row['TYPE'] == 3)
1194 {
1195 $index_array[] = $row['INDEX_NAME'];
1196 }
1197 }
1198 $this->db->sql_freeresult($result);
1199 }
1200 else
1201 {
1202 switch ($this->sql_layer)
1203 {
1204 case 'firebird':
1205 $sql = "SELECT LOWER(RDB\$INDEX_NAME) as index_name
1206 FROM RDB\$INDICES
1207 WHERE RDB\$RELATION_NAME = " . strtoupper($table_name) . "
1208 AND RDB\$UNIQUE_FLAG IS NULL
1209 AND RDB\$FOREIGN_KEY IS NULL";
1210 $col = 'index_name';
1211 break;
1212
1213 case 'postgres':
1214 $sql = "SELECT ic.relname as index_name
1215 FROM pg_class bc, pg_class ic, pg_index i
1216 WHERE (bc.oid = i.indrelid)
1217 AND (ic.oid = i.indexrelid)
1218 AND (bc.relname = '" . $table_name . "')
1219 AND (i.indisunique != 't')
1220 AND (i.indisprimary != 't')";
1221 $col = 'index_name';
1222 break;
1223
1224 case 'mysql_40':
1225 case 'mysql_41':
1226 $sql = 'SHOW KEYS
1227 FROM ' . $table_name;
1228 $col = 'Key_name';
1229 break;
1230
1231 case 'oracle':
1232 $sql = "SELECT index_name
1233 FROM user_indexes
1234 WHERE table_name = '" . $table_name . "'
1235 AND generated = 'N'";
1236 break;
1237
1238 case 'sqlite':
1239 $sql = "PRAGMA index_info('" . $table_name . "');";
1240 $col = 'name';
1241 break;
1242 }
1243
1244 $result = $this->db->sql_query($sql);
1245 while ($row = $this->db->sql_fetchrow($result))
1246 {
1247 if (($this->sql_layer == 'mysql_40' || $this->sql_layer == 'mysql_41') && !$row['Non_unique'])
1248 {
1249 continue;
1250 }
1251
1252 switch ($this->sql_layer)
1253 {
1254 case 'firebird':
1255 case 'oracle':
1256 case 'postgres':
1257 case 'sqlite':
1258 $row[$col] = substr($row[$col], strlen($table_name) + 1);
1259 break;
1260 }
1261
1262 $index_array[] = $row[$col];
1263 }
1264 $this->db->sql_freeresult($result);
1265 }
1266
1267 return array_map('strtolower', $index_array);
1268 }
1269
1270 /**
1271 * Change column type (not name!)
1272 */
1273 function sql_column_change($table_name, $column_name, $column_data)
1274 {
1275 $column_data = $this->sql_prepare_column_data($table_name, $column_name, $column_data);
1276 $statements = array();
1277
1278 switch ($this->sql_layer)
1279 {
1280 case 'firebird':
1281 // Change type...
1282 $statements[] = 'ALTER TABLE "' . $table_name . '" ALTER COLUMN "' . $column_name . '" TYPE ' . ' ' . $column_data['column_type_sql'];
1283 break;
1284
1285 case 'mssql':
1286 $statements[] = 'ALTER TABLE [' . $table_name . '] ALTER COLUMN [' . $column_name . '] ' . $column_data['column_type_sql'];
1287 break;
1288
1289 case 'mysql_40':
1290 case 'mysql_41':
1291 $statements[] = 'ALTER TABLE `' . $table_name . '` CHANGE `' . $column_name . '` `' . $column_name . '` ' . $column_data['column_type_sql'];
1292 break;
1293
1294 case 'oracle':
1295 $statements[] = 'ALTER TABLE ' . $table_name . ' MODIFY ' . $column_name . ' ' . $column_data['column_type_sql'];
1296 break;
1297
1298 case 'postgres':
1299 $sql = 'ALTER TABLE ' . $table_name . ' ';
1300
1301 $sql_array = array();
1302 $sql_array[] = 'ALTER COLUMN ' . $column_name . ' TYPE ' . $column_data['column_type'];
1303
1304 if (isset($column_data['null']))
1305 {
1306 if ($column_data['null'] == 'NOT NULL')
1307 {
1308 $sql_array[] = 'ALTER COLUMN ' . $column_name . ' SET NOT NULL';
1309 }
1310 else if ($column_data['null'] == 'NULL')
1311 {
1312 $sql_array[] = 'ALTER COLUMN ' . $column_name . ' DROP NOT NULL';
1313 }
1314 }
1315
1316 if (isset($column_data['default']))
1317 {
1318 $sql_array[] = 'ALTER COLUMN ' . $column_name . ' SET DEFAULT ' . $column_data['default'];
1319 }
1320
1321 // we don't want to double up on constraints if we change different number data types
1322 if (isset($column_data['constraint']))
1323 {
1324 $constraint_sql = "SELECT consrc as constraint_data
1325 FROM pg_constraint, pg_class bc
1326 WHERE conrelid = bc.oid
1327 AND bc.relname = '{$table_name}'
1328 AND NOT EXISTS (
1329 SELECT *
1330 FROM pg_constraint as c, pg_inherits as i
1331 WHERE i.inhrelid = pg_constraint.conrelid
1332 AND c.conname = pg_constraint.conname
1333 AND c.consrc = pg_constraint.consrc
1334 AND c.conrelid = i.inhparent
1335 )";
1336
1337 $constraint_exists = false;
1338
1339 $result = $this->db->sql_query($constraint_sql);
1340 while ($row = $this->db->sql_fetchrow($result))
1341 {
1342 if (trim($row['constraint_data']) == trim($column_data['constraint']))
1343 {
1344 $constraint_exists = true;
1345 break;
1346 }
1347 }
1348 $this->db->sql_freeresult($result);
1349
1350 if (!$constraint_exists)
1351 {
1352 $sql_array[] = 'ADD ' . $column_data['constraint'];
1353 }
1354 }
1355
1356 $sql .= implode(', ', $sql_array);
1357
1358 $statements[] = $sql;
1359 break;
1360
1361 case 'sqlite':
1362
1363 $sql = "SELECT sql
1364 FROM sqlite_master
1365 WHERE type = 'table'
1366 AND name = '{$table_name}'
1367 ORDER BY type DESC, name;";
1368 $result = $this->db->sql_query($sql);
1369
1370 if (!$result)
1371 {
1372 break;
1373 }
1374
1375 $row = $this->db->sql_fetchrow($result);
1376 $this->db->sql_freeresult($result);
1377
1378 $statements[] = 'begin';
1379
1380 // Create a temp table and populate it, destroy the existing one
1381 $statements[] = preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $row['sql']);
1382 $statements[] = 'INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name;
1383 $statements[] = 'DROP TABLE ' . $table_name;
1384
1385 preg_match('#\((.*)\)#s', $row['sql'], $matches);
1386
1387 $new_table_cols = trim($matches[1]);
1388 $old_table_cols = preg_split('/,(?![\s\w]+\))/m', $new_table_cols);
1389 $column_list = array();
1390
1391 foreach ($old_table_cols as $key => $declaration)
1392 {
1393 $entities = preg_split('#\s+#', trim($declaration));
1394 $column_list[] = $entities[0];
1395 if ($entities[0] == $column_name)
1396 {
1397 $old_table_cols[$key] = $column_name . ' ' . $column_data['column_type_sql'];
1398 }
1399 }
1400
1401 $columns = implode(',', $column_list);
1402
1403 // create a new table and fill it up. destroy the temp one
1404 $statements[] = 'CREATE TABLE ' . $table_name . ' (' . implode(',', $old_table_cols) . ');';
1405 $statements[] = 'INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;';
1406 $statements[] = 'DROP TABLE ' . $table_name . '_temp';
1407
1408 $statements[] = 'commit';
1409
1410 break;
1411 }
1412
1413 return $this->_sql_run_sql($statements);
1414 }
1415 }
1416
1417 ?>