Mercurial > hg > vamp-website

annotate forum/Sources/DbPackages-sqlite.php @ 85:6d7b61434be7 website

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Add a copy of this here, just in case!
author Chris Cannam
date Mon, 20 Jan 2014 11:02:36 +0000
parents e3e11437ecea
children
rev   line source
Chris@76 1 <?php
Chris@76 2
Chris@76 3 /**
Chris@76 4 * Simple Machines Forum (SMF)
Chris@76 5 *
Chris@76 6 * @package SMF
Chris@76 7 * @author Simple Machines http://www.simplemachines.org
Chris@76 8 * @copyright 2011 Simple Machines
Chris@76 9 * @license http://www.simplemachines.org/about/smf/license.php BSD
Chris@76 10 *
Chris@76 11 * @version 2.0
Chris@76 12 */
Chris@76 13
Chris@76 14 if (!defined('SMF'))
Chris@76 15 die('Hacking attempt...');
Chris@76 16
Chris@76 17 /* This file contains database functionality specifically designed for packages to utilize.
Chris@76 18
Chris@76 19 bool smf_db_create_table(string table_name, array columns, array indexes = array(),
Chris@76 20 array parameters = array(), string if_exists = 'ignore')
Chris@76 21 - Can be used to create a table without worrying about schema compatabilities.
Chris@76 22 - If the table exists will, by default, do nothing.
Chris@76 23 - Builds table with columns as passed to it - at least one column must be sent.
Chris@76 24 The columns array should have one sub-array for each column - these sub arrays contain:
Chris@76 25 + 'name' = Column name
Chris@76 26 + 'type' = Type of column - values from (smallint,mediumint,int,text,varchar,char,tinytext,mediumtext,largetext)
Chris@76 27 + 'size' => Size of column (If applicable) - for example 255 for a large varchar, 10 for an int etc. If not
Chris@76 28 set SMF will pick a size.
Chris@76 29 + 'default' = Default value - do not set if no default required.
Chris@76 30 + 'null' => Can it be null (true or false) - if not set default will be false.
Chris@76 31 + 'auto' => Set to true to make it an auto incrementing column. Set to a numerical value to set
Chris@76 32 from what it should begin counting.
Chris@76 33 - Adds indexes as specified within indexes parameter. Each index should be a member of $indexes. Values are:
Chris@76 34 + 'name' => Index name (If left empty SMF will generate).
Chris@76 35 + 'type' => Type of index. Choose from 'primary', 'unique' or 'index'. If not set will default to 'index'.
Chris@76 36 + 'columns' => Array containing columns that form part of key - in the order the index is to be created.
Chris@76 37 - parameters: (None yet)
Chris@76 38 - if_exists values:
Chris@76 39 + 'ignore' will do nothing if the table exists. (And will return true)
Chris@76 40 + 'overwrite' will drop any existing table of the same name.
Chris@76 41 + 'error' will return false if the table already exists.
Chris@76 42
Chris@76 43 */
Chris@76 44
Chris@76 45 // Add the file functions to the $smcFunc array.
Chris@76 46 function db_packages_init()
Chris@76 47 {
Chris@76 48 global $smcFunc, $reservedTables, $db_package_log, $db_prefix;
Chris@76 49
Chris@76 50 if (!isset($smcFunc['db_create_table']) || $smcFunc['db_create_table'] != 'smf_db_create_table')
Chris@76 51 {
Chris@76 52 $smcFunc += array(
Chris@76 53 'db_add_column' => 'smf_db_add_column',
Chris@76 54 'db_add_index' => 'smf_db_add_index',
Chris@76 55 'db_alter_table' => 'smf_db_alter_table',
Chris@76 56 'db_calculate_type' => 'smf_db_calculate_type',
Chris@76 57 'db_change_column' => 'smf_db_change_column',
Chris@76 58 'db_create_table' => 'smf_db_create_table',
Chris@76 59 'db_drop_table' => 'smf_db_drop_table',
Chris@76 60 'db_table_structure' => 'smf_db_table_structure',
Chris@76 61 'db_list_columns' => 'smf_db_list_columns',
Chris@76 62 'db_list_indexes' => 'smf_db_list_indexes',
Chris@76 63 'db_remove_column' => 'smf_db_remove_column',
Chris@76 64 'db_remove_index' => 'smf_db_remove_index',
Chris@76 65 );
Chris@76 66 $db_package_log = array();
Chris@76 67 }
Chris@76 68
Chris@76 69 // We setup an array of SMF tables we can't do auto-remove on - in case a mod writer cocks it up!
Chris@76 70 $reservedTables = array('admin_info_files', 'approval_queue', 'attachments', 'ban_groups', 'ban_items',
Chris@76 71 'board_permissions', 'boards', 'calendar', 'calendar_holidays', 'categories', 'collapsed_categories',
Chris@76 72 'custom_fields', 'group_moderators', 'log_actions', 'log_activity', 'log_banned', 'log_boards',
Chris@76 73 'log_digest', 'log_errors', 'log_floodcontrol', 'log_group_requests', 'log_karma', 'log_mark_read',
Chris@76 74 'log_notify', 'log_online', 'log_packages', 'log_polls', 'log_reported', 'log_reported_comments',
Chris@76 75 'log_scheduled_tasks', 'log_search_messages', 'log_search_results', 'log_search_subjects',
Chris@76 76 'log_search_topics', 'log_topics', 'mail_queue', 'membergroups', 'members', 'message_icons',
Chris@76 77 'messages', 'moderators', 'package_servers', 'permission_profiles', 'permissions', 'personal_messages',
Chris@76 78 'pm_recipients', 'poll_choices', 'polls', 'scheduled_tasks', 'sessions', 'settings', 'smileys',
Chris@76 79 'themes', 'topics');
Chris@76 80 foreach ($reservedTables as $k => $table_name)
Chris@76 81 $reservedTables[$k] = strtolower($db_prefix . $table_name);
Chris@76 82
Chris@76 83 // We in turn may need the extra stuff.
Chris@76 84 db_extend('extra');
Chris@76 85 }
Chris@76 86
Chris@76 87 // Create a table.
Chris@76 88 function smf_db_create_table($table_name, $columns, $indexes = array(), $parameters = array(), $if_exists = 'ignore', $error = 'fatal')
Chris@76 89 {
Chris@76 90 global $reservedTables, $smcFunc, $db_package_log, $db_prefix;
Chris@76 91
Chris@76 92 // With or without the database name, the full name looks like this.
Chris@76 93 $real_prefix = preg_match('~^(`?)(.+?)\\1\\.(.*?)$~', $db_prefix, $match) === 1 ? $match[3] : $db_prefix;
Chris@76 94 $full_table_name = str_replace('{db_prefix}', $real_prefix, $table_name);
Chris@76 95 $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
Chris@76 96
Chris@76 97 // First - no way do we touch SMF tables.
Chris@76 98 // Commented out for now. We need to alter SMF tables in order to use this in the upgrade.
Chris@76 99 /*
Chris@76 100 if (in_array(strtolower($table_name), $reservedTables))
Chris@76 101 return false;
Chris@76 102 */
Chris@76 103
Chris@76 104 // Log that we'll want to remove this on uninstall.
Chris@76 105 $db_package_log[] = array('remove_table', $table_name);
Chris@76 106
Chris@76 107 // Does this table exist or not?
Chris@76 108 $tables = $smcFunc['db_list_tables']();
Chris@76 109 if (in_array($full_table_name, $tables))
Chris@76 110 {
Chris@76 111 // This is a sad day... drop the table? If not, return false (error) by default.
Chris@76 112 if ($if_exists == 'overwrite')
Chris@76 113 $smcFunc['db_drop_table']($table_name);
Chris@76 114 else
Chris@76 115 return $if_exists == 'ignore';
Chris@76 116 }
Chris@76 117
Chris@76 118 // Righty - let's do the damn thing!
Chris@76 119 $table_query = 'CREATE TABLE ' . $table_name . "\n" . '(';
Chris@76 120 $done_primary = false;
Chris@76 121 foreach ($columns as $column)
Chris@76 122 {
Chris@76 123 // Auto increment is special
Chris@76 124 if (!empty($column['auto']))
Chris@76 125 {
Chris@76 126 $table_query .= "\n" . $column['name'] . ' integer PRIMARY KEY,';
Chris@76 127 $done_primary = true;
Chris@76 128 continue;
Chris@76 129 }
Chris@76 130 elseif (isset($column['default']) && $column['default'] !== null)
Chris@76 131 $default = 'default \'' . $smcFunc['db_escape_string']($column['default']) . '\'';
Chris@76 132 else
Chris@76 133 $default = '';
Chris@76 134
Chris@76 135 // Sort out the size... and stuff...
Chris@76 136 $column['size'] = isset($column['size']) && is_numeric($column['size']) ? $column['size'] : null;
Chris@76 137 list ($type, $size) = $smcFunc['db_calculate_type']($column['type'], $column['size']);
Chris@76 138 if ($size !== null)
Chris@76 139 $type = $type . '(' . $size . ')';
Chris@76 140
Chris@76 141 // Now just put it together!
Chris@76 142 $table_query .= "\n\t" . $column['name'] . ' ' . $type . ' ' . (!empty($column['null']) ? '' : 'NOT NULL') . ' ' . $default . ',';
Chris@76 143 }
Chris@76 144
Chris@76 145 // Loop through the indexes next...
Chris@76 146 $index_queries = array();
Chris@76 147 foreach ($indexes as $index)
Chris@76 148 {
Chris@76 149 $columns = implode(',', $index['columns']);
Chris@76 150
Chris@76 151 // Is it the primary?
Chris@76 152 if (isset($index['type']) && $index['type'] == 'primary')
Chris@76 153 {
Chris@76 154 // If we've done the primary via auto_inc, don't do it again!
Chris@76 155 if (!$done_primary)
Chris@76 156 $table_query .= "\n\t" . 'PRIMARY KEY (' . implode(',', $index['columns']) . '),';
Chris@76 157 }
Chris@76 158 else
Chris@76 159 {
Chris@76 160 if (empty($index['name']))
Chris@76 161 $index['name'] = implode('_', $index['columns']);
Chris@76 162 $index_queries[] = 'CREATE ' . (isset($index['type']) && $index['type'] == 'unique' ? 'UNIQUE' : '') . ' INDEX ' . $table_name . '_' . $index['name'] . ' ON ' . $table_name . ' (' . $columns . ')';
Chris@76 163 }
Chris@76 164 }
Chris@76 165
Chris@76 166 // No trailing commas!
Chris@76 167 if (substr($table_query, -1) == ',')
Chris@76 168 $table_query = substr($table_query, 0, -1);
Chris@76 169
Chris@76 170 $table_query .= ')';
Chris@76 171
Chris@76 172 if (empty($parameters['skip_transaction']))
Chris@76 173 $smcFunc['db_transaction']('begin');
Chris@76 174
Chris@76 175 // Do the table and indexes...
Chris@76 176 $smcFunc['db_query']('', $table_query,
Chris@76 177 array(
Chris@76 178 'security_override' => true,
Chris@76 179 )
Chris@76 180 );
Chris@76 181 foreach ($index_queries as $query)
Chris@76 182 $smcFunc['db_query']('', $query,
Chris@76 183 array(
Chris@76 184 'security_override' => true,
Chris@76 185 )
Chris@76 186 );
Chris@76 187
Chris@76 188 if (empty($parameters['skip_transaction']))
Chris@76 189 $smcFunc['db_transaction']('commit');
Chris@76 190 }
Chris@76 191
Chris@76 192 // Drop a table.
Chris@76 193 function smf_db_drop_table($table_name, $parameters = array(), $error = 'fatal')
Chris@76 194 {
Chris@76 195 global $reservedTables, $smcFunc, $db_prefix;
Chris@76 196
Chris@76 197 // Strip out the table name, we might not need it in some cases
Chris@76 198 $real_prefix = preg_match('~^(`?)(.+?)\\1\\.(.*?)$~', $db_prefix, $match) === 1 ? $match[3] : $db_prefix;
Chris@76 199 $full_table_name = str_replace('{db_prefix}', $real_prefix, $table_name);
Chris@76 200 $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
Chris@76 201
Chris@76 202 // God no - dropping one of these = bad.
Chris@76 203 if (in_array(strtolower($table_name), $reservedTables))
Chris@76 204 return false;
Chris@76 205
Chris@76 206 // Does it exist?
Chris@76 207 if (in_array($full_table_name, $smcFunc['db_list_tables']()))
Chris@76 208 {
Chris@76 209 $query = 'DROP TABLE ' . $table_name;
Chris@76 210 $smcFunc['db_query']('', $query,
Chris@76 211 array(
Chris@76 212 'security_override' => true,
Chris@76 213 )
Chris@76 214 );
Chris@76 215
Chris@76 216 return true;
Chris@76 217 }
Chris@76 218
Chris@76 219 // Otherwise do 'nout.
Chris@76 220 return false;
Chris@76 221 }
Chris@76 222
Chris@76 223 // Add a column.
Chris@76 224 function smf_db_add_column($table_name, $column_info, $parameters = array(), $if_exists = 'update', $error = 'fatal')
Chris@76 225 {
Chris@76 226 global $smcFunc, $db_package_log, $txt, $db_prefix;
Chris@76 227
Chris@76 228 $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
Chris@76 229
Chris@76 230 // Log that we will want to uninstall this!
Chris@76 231 $db_package_log[] = array('remove_column', $table_name, $column_info['name']);
Chris@76 232
Chris@76 233 // Does it exist - if so don't add it again!
Chris@76 234 $columns = $smcFunc['db_list_columns']($table_name, false);
Chris@76 235 foreach ($columns as $column)
Chris@76 236 if ($column == $column_info['name'])
Chris@76 237 {
Chris@76 238 // If we're going to overwrite then use change column.
Chris@76 239 if ($if_exists == 'update')
Chris@76 240 return $smcFunc['db_change_column']($table_name, $column_info['name'], $column_info);
Chris@76 241 else
Chris@76 242 return false;
Chris@76 243 }
Chris@76 244
Chris@76 245 // Alter the table to add the column.
Chris@76 246 if ($smcFunc['db_alter_table']($table_name, array('add' => array($column_info))) === false)
Chris@76 247 return false;
Chris@76 248
Chris@76 249 return true;
Chris@76 250 }
Chris@76 251
Chris@76 252 // We can't reliably do this on SQLite - damn!
Chris@76 253 function smf_db_remove_column($table_name, $column_name, $parameters = array(), $error = 'fatal')
Chris@76 254 {
Chris@76 255 global $smcFunc, $db_prefix;
Chris@76 256
Chris@76 257 $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
Chris@76 258
Chris@76 259 if ($smcFunc['db_alter_table']($table_name, array('remove' => array(array('name' => $column_name)))))
Chris@76 260 return true;
Chris@76 261 else
Chris@76 262 return false;
Chris@76 263 }
Chris@76 264
Chris@76 265 // Change a column.
Chris@76 266 function smf_db_change_column($table_name, $old_column, $column_info, $parameters = array(), $error = 'fatal')
Chris@76 267 {
Chris@76 268 global $smcFunc, $db_prefix;
Chris@76 269
Chris@76 270 $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
Chris@76 271
Chris@76 272 if ($smcFunc['db_alter_table']($table_name, array('change' => array(array('name' => $old_column) + $column_info))))
Chris@76 273 return true;
Chris@76 274 else
Chris@76 275 return false;
Chris@76 276 }
Chris@76 277
Chris@76 278 // Add an index.
Chris@76 279 function smf_db_add_index($table_name, $index_info, $parameters = array(), $if_exists = 'update', $error = 'fatal')
Chris@76 280 {
Chris@76 281 global $smcFunc, $db_package_log, $db_prefix;
Chris@76 282
Chris@76 283 $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
Chris@76 284
Chris@76 285 // No columns = no index.
Chris@76 286 if (empty($index_info['columns']))
Chris@76 287 return false;
Chris@76 288 $columns = implode(',', $index_info['columns']);
Chris@76 289
Chris@76 290 // No name - make it up!
Chris@76 291 if (empty($index_info['name']))
Chris@76 292 {
Chris@76 293 // No need for primary.
Chris@76 294 if (isset($index_info['type']) && $index_info['type'] == 'primary')
Chris@76 295 $index_info['name'] = '';
Chris@76 296 else
Chris@76 297 $index_info['name'] = implode('_', $index_info['columns']);
Chris@76 298 }
Chris@76 299 else
Chris@76 300 $index_info['name'] = $index_info['name'];
Chris@76 301
Chris@76 302 // Log that we are going to want to remove this!
Chris@76 303 $db_package_log[] = array('remove_index', $table_name, $index_info['name']);
Chris@76 304
Chris@76 305 // Let's get all our indexes.
Chris@76 306 $indexes = $smcFunc['db_list_indexes']($table_name, true);
Chris@76 307 // Do we already have it?
Chris@76 308 foreach ($indexes as $index)
Chris@76 309 {
Chris@76 310 if ($index['name'] == $index_info['name'] || ($index['type'] == 'primary' && isset($index_info['type']) && $index_info['type'] == 'primary'))
Chris@76 311 {
Chris@76 312 // If we want to overwrite simply remove the current one then continue.
Chris@76 313 if ($if_exists != 'update' || $index['type'] == 'primary')
Chris@76 314 return false;
Chris@76 315 else
Chris@76 316 $smcFunc['db_remove_index']($table_name, $index_info['name']);
Chris@76 317 }
Chris@76 318 }
Chris@76 319
Chris@76 320 // If we're here we know we don't have the index - so just add it.
Chris@76 321 if (!empty($index_info['type']) && $index_info['type'] == 'primary')
Chris@76 322 {
Chris@76 323 //!!! Doesn't work with PRIMARY KEY yet.
Chris@76 324 }
Chris@76 325 else
Chris@76 326 {
Chris@76 327 $smcFunc['db_query']('', '
Chris@76 328 CREATE ' . (isset($index_info['type']) && $index_info['type'] == 'unique' ? 'UNIQUE' : '') . ' INDEX ' . $index_info['name'] . ' ON ' . $table_name . ' (' . $columns . ')',
Chris@76 329 array(
Chris@76 330 'security_override' => true,
Chris@76 331 )
Chris@76 332 );
Chris@76 333 }
Chris@76 334 }
Chris@76 335
Chris@76 336 // Remove an index.
Chris@76 337 function smf_db_remove_index($table_name, $index_name, $parameters = array(), $error = 'fatal')
Chris@76 338 {
Chris@76 339 global $smcFunc, $db_prefix;
Chris@76 340
Chris@76 341 $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
Chris@76 342
Chris@76 343 // Better exist!
Chris@76 344 $indexes = $smcFunc['db_list_indexes']($table_name, true);
Chris@76 345
Chris@76 346 foreach ($indexes as $index)
Chris@76 347 {
Chris@76 348 //!!! Doesn't do primary key at the moment!
Chris@76 349 if ($index['type'] != 'primary' && $index['name'] == $index_name)
Chris@76 350 {
Chris@76 351 // Drop the bugger...
Chris@76 352 $smcFunc['db_query']('', '
Chris@76 353 DROP INDEX ' . $index_name,
Chris@76 354 array(
Chris@76 355 'security_override' => true,
Chris@76 356 )
Chris@76 357 );
Chris@76 358
Chris@76 359 return true;
Chris@76 360 }
Chris@76 361 }
Chris@76 362
Chris@76 363 // Not to be found ;(
Chris@76 364 return false;
Chris@76 365 }
Chris@76 366
Chris@76 367 // Get the schema formatted name for a type.
Chris@76 368 function smf_db_calculate_type($type_name, $type_size = null, $reverse = false)
Chris@76 369 {
Chris@76 370 // Generic => Specific.
Chris@76 371 if (!$reverse)
Chris@76 372 {
Chris@76 373 $types = array(
Chris@76 374 'mediumint' => 'int',
Chris@76 375 'tinyint' => 'smallint',
Chris@76 376 'mediumtext' => 'text',
Chris@76 377 'largetext' => 'text',
Chris@76 378 );
Chris@76 379 }
Chris@76 380 else
Chris@76 381 {
Chris@76 382 $types = array(
Chris@76 383 'integer' => 'int',
Chris@76 384 );
Chris@76 385 }
Chris@76 386
Chris@76 387 // Got it? Change it!
Chris@76 388 if (isset($types[$type_name]))
Chris@76 389 {
Chris@76 390 if ($type_name == 'tinytext')
Chris@76 391 $type_size = 255;
Chris@76 392 $type_name = $types[$type_name];
Chris@76 393 }
Chris@76 394 // Numbers don't have a size.
Chris@76 395 if (strpos($type_name, 'int') !== false)
Chris@76 396 $type_size = null;
Chris@76 397
Chris@76 398 return array($type_name, $type_size);
Chris@76 399 }
Chris@76 400
Chris@76 401 // Get table structure.
Chris@76 402 function smf_db_table_structure($table_name, $parameters = array())
Chris@76 403 {
Chris@76 404 global $smcFunc, $db_prefix;
Chris@76 405
Chris@76 406 $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
Chris@76 407
Chris@76 408 return array(
Chris@76 409 'name' => $table_name,
Chris@76 410 'columns' => $smcFunc['db_list_columns']($table_name, true),
Chris@76 411 'indexes' => $smcFunc['db_list_indexes']($table_name, true),
Chris@76 412 );
Chris@76 413 }
Chris@76 414
Chris@76 415 // Harder than it should be on sqlite!
Chris@76 416 function smf_db_list_columns($table_name, $detail = false, $parameters = array())
Chris@76 417 {
Chris@76 418 global $smcFunc, $db_prefix;
Chris@76 419
Chris@76 420 $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
Chris@76 421
Chris@76 422 $result = $smcFunc['db_query']('', '
Chris@76 423 PRAGMA table_info(' . $table_name . ')',
Chris@76 424 array(
Chris@76 425 'security_override' => true,
Chris@76 426 )
Chris@76 427 );
Chris@76 428 $columns = array();
Chris@76 429
Chris@76 430 $primaries = array();
Chris@76 431 while ($row = $smcFunc['db_fetch_assoc']($result))
Chris@76 432 {
Chris@76 433 if (!$detail)
Chris@76 434 {
Chris@76 435 $columns[] = $row['name'];
Chris@76 436 }
Chris@76 437 else
Chris@76 438 {
Chris@76 439 // Auto increment is hard to tell really... if there's only one primary it probably is.
Chris@76 440 if ($row['pk'])
Chris@76 441 $primaries[] = $row['name'];
Chris@76 442
Chris@76 443 // Can we split out the size?
Chris@76 444 if (preg_match('~(.+?)\s*\((\d+)\)~i', $row['type'], $matches))
Chris@76 445 {
Chris@76 446 $type = $matches[1];
Chris@76 447 $size = $matches[2];
Chris@76 448 }
Chris@76 449 else
Chris@76 450 {
Chris@76 451 $type = $row['type'];
Chris@76 452 $size = null;
Chris@76 453 }
Chris@76 454
Chris@76 455 $columns[$row['name']] = array(
Chris@76 456 'name' => $row['name'],
Chris@76 457 'null' => $row['notnull'] ? false : true,
Chris@76 458 'default' => $row['dflt_value'],
Chris@76 459 'type' => $type,
Chris@76 460 'size' => $size,
Chris@76 461 'auto' => false,
Chris@76 462 );
Chris@76 463 }
Chris@76 464 }
Chris@76 465 $smcFunc['db_free_result']($result);
Chris@76 466
Chris@76 467 // Put in our guess at auto_inc.
Chris@76 468 if (count($primaries) == 1)
Chris@76 469 $columns[$primaries[0]]['auto'] = true;
Chris@76 470
Chris@76 471 return $columns;
Chris@76 472 }
Chris@76 473
Chris@76 474 // What about some index information?
Chris@76 475 function smf_db_list_indexes($table_name, $detail = false, $parameters = array())
Chris@76 476 {
Chris@76 477 global $smcFunc, $db_prefix;
Chris@76 478
Chris@76 479 $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
Chris@76 480
Chris@76 481 $result = $smcFunc['db_query']('', '
Chris@76 482 PRAGMA index_list(' . $table_name . ')',
Chris@76 483 array(
Chris@76 484 'security_override' => true,
Chris@76 485 )
Chris@76 486 );
Chris@76 487 $indexes = array();
Chris@76 488 while ($row = $smcFunc['db_fetch_assoc']($result))
Chris@76 489 {
Chris@76 490 if (!$detail)
Chris@76 491 $indexes[] = $row['name'];
Chris@76 492 else
Chris@76 493 {
Chris@76 494 $result2 = $smcFunc['db_query']('', '
Chris@76 495 PRAGMA index_info(' . $row['name'] . ')',
Chris@76 496 array(
Chris@76 497 'security_override' => true,
Chris@76 498 )
Chris@76 499 );
Chris@76 500 while ($row2 = $smcFunc['db_fetch_assoc']($result2))
Chris@76 501 {
Chris@76 502 // What is the type?
Chris@76 503 if ($row['unique'])
Chris@76 504 $type = 'unique';
Chris@76 505 else
Chris@76 506 $type = 'index';
Chris@76 507
Chris@76 508 // This is the first column we've seen?
Chris@76 509 if (empty($indexes[$row['name']]))
Chris@76 510 {
Chris@76 511 $indexes[$row['name']] = array(
Chris@76 512 'name' => $row['name'],
Chris@76 513 'type' => $type,
Chris@76 514 'columns' => array(),
Chris@76 515 );
Chris@76 516 }
Chris@76 517
Chris@76 518 // Add the column...
Chris@76 519 $indexes[$row['name']]['columns'][] = $row2['name'];
Chris@76 520 }
Chris@76 521 $smcFunc['db_free_result']($result2);
Chris@76 522 }
Chris@76 523 }
Chris@76 524 $smcFunc['db_free_result']($result);
Chris@76 525
Chris@76 526 return $indexes;
Chris@76 527 }
Chris@76 528
Chris@76 529 function smf_db_alter_table($table_name, $columns)
Chris@76 530 {
Chris@76 531 global $smcFunc, $db_prefix, $db_name, $boarddir;
Chris@76 532
Chris@76 533 $db_file = substr($db_name, -3) === '.db' ? $db_name : $db_name . '.db';
Chris@76 534
Chris@76 535 $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
Chris@76 536
Chris@76 537 // Let's get the current columns for the table.
Chris@76 538 $current_columns = $smcFunc['db_list_columns']($table_name, true);
Chris@76 539
Chris@76 540 // Let's get a list of columns for the temp table.
Chris@76 541 $temp_table_columns = array();
Chris@76 542
Chris@76 543 // Let's see if we have columns to remove or columns that are being added that already exist.
Chris@76 544 foreach ($current_columns as $key => $column)
Chris@76 545 {
Chris@76 546 $exists = false;
Chris@76 547 if (isset($columns['remove']))
Chris@76 548 foreach ($columns['remove'] as $drop)
Chris@76 549 if ($drop['name'] == $column['name'])
Chris@76 550 {
Chris@76 551 $exists = true;
Chris@76 552 break;
Chris@76 553 }
Chris@76 554
Chris@76 555 if (isset($columns['add']))
Chris@76 556 foreach ($columns['add'] as $key2 => $add)
Chris@76 557 if ($add['name'] == $column['name'])
Chris@76 558 {
Chris@76 559 unset($columns['add'][$key2]);
Chris@76 560 break;
Chris@76 561 }
Chris@76 562
Chris@76 563 // Doesn't exist then we 'remove'.
Chris@76 564 if (!$exists)
Chris@76 565 $temp_table_columns[] = $column['name'];
Chris@76 566 }
Chris@76 567
Chris@76 568 // If they are equal then that means that the column that we are adding exists or it doesn't exist and we are not looking to change any one of them.
Chris@76 569 if (count($temp_table_columns) == count($current_columns) && empty($columns['change']) && empty($columns['add']))
Chris@76 570 return true;
Chris@76 571
Chris@76 572 // Drop the temp table.
Chris@76 573 $smcFunc['db_query']('', '
Chris@76 574 DROP TABLE {raw:temp_table_name}',
Chris@76 575 array(
Chris@76 576 'temp_table_name' => $table_name . '_tmp',
Chris@76 577 'db_error_skip' => true,
Chris@76 578 )
Chris@76 579 );
Chris@76 580
Chris@76 581 // Let's make a backup of the current database.
Chris@76 582 // We only want the first backup of a table modification. So if there is a backup file and older than an hour just delete and back up again
Chris@76 583 $db_backup_file = $boarddir . '/Packages/backups/backup_' . $table_name . '_' . basename($db_file) . md5($table_name . $db_file);
Chris@76 584 if (file_exists($db_backup_file) && time() - filemtime($db_backup_file) > 3600)
Chris@76 585 {
Chris@76 586 @unlink($db_backup_file);
Chris@76 587 @copy($db_file, $db_backup_file);
Chris@76 588 }
Chris@76 589 elseif (!file_exists($db_backup_file))
Chris@76 590 @copy($db_file, $db_backup_file);
Chris@76 591
Chris@76 592 // If we don't have temp tables then everything crapped out. Just exit.
Chris@76 593 if (empty($temp_table_columns))
Chris@76 594 return false;
Chris@76 595
Chris@76 596 // Start
Chris@76 597 $smcFunc['db_transaction']('begin');
Chris@76 598
Chris@76 599 // Let's create the temporary table.
Chris@76 600 $createTempTable = $smcFunc['db_query']('', '
Chris@76 601 CREATE TEMPORARY TABLE {raw:temp_table_name}
Chris@76 602 (
Chris@76 603 {raw:columns}
Chris@76 604 );',
Chris@76 605 array(
Chris@76 606 'temp_table_name' => $table_name . '_tmp',
Chris@76 607 'columns' => implode(', ', $temp_table_columns),
Chris@76 608 'db_error_skip' => true,
Chris@76 609 )
Chris@76 610 ) !== false;
Chris@76 611
Chris@76 612 if (!$createTempTable)
Chris@76 613 return false;
Chris@76 614
Chris@76 615 // Insert into temp table.
Chris@76 616 $smcFunc['db_query']('', '
Chris@76 617 INSERT INTO {raw:temp_table_name}
Chris@76 618 ({raw:columns})
Chris@76 619 SELECT {raw:columns}
Chris@76 620 FROM {raw:table_name}',
Chris@76 621 array(
Chris@76 622 'table_name' => $table_name,
Chris@76 623 'columns' => implode(', ', $temp_table_columns),
Chris@76 624 'temp_table_name' => $table_name . '_tmp',
Chris@76 625 )
Chris@76 626 );
Chris@76 627
Chris@76 628 // Drop the current table.
Chris@76 629 $dropTable = $smcFunc['db_query']('', '
Chris@76 630 DROP TABLE {raw:table_name}',
Chris@76 631 array(
Chris@76 632 'table_name' => $table_name,
Chris@76 633 'db_error_skip' => true,
Chris@76 634 )
Chris@76 635 ) !== false;
Chris@76 636
Chris@76 637 // If you can't drop the main table then there is no where to go from here. Just return.
Chris@76 638 if (!$dropTable)
Chris@76 639 return false;
Chris@76 640
Chris@76 641 // We need to keep track of the structure for the current columns and the new columns.
Chris@76 642 $new_columns = array();
Chris@76 643 $column_names = array();
Chris@76 644
Chris@76 645 // Let's get the ones that we already have first.
Chris@76 646 foreach ($current_columns as $name => $column)
Chris@76 647 {
Chris@76 648 if (in_array($name, $temp_table_columns))
Chris@76 649 {
Chris@76 650 $new_columns[$name] = array(
Chris@76 651 'name' => $name,
Chris@76 652 'type' => $column['type'],
Chris@76 653 'size' => isset($column['size']) ? (int) $column['size'] : null,
Chris@76 654 'null' => !empty($column['null']),
Chris@76 655 'auto' => isset($column['auto']) ? $column['auto'] : false,
Chris@76 656 'default' => isset($column['default']) ? $column['default'] : '',
Chris@76 657 );
Chris@76 658
Chris@76 659 // Lets keep track of the name for the column.
Chris@76 660 $column_names[$name] = $name;
Chris@76 661 }
Chris@76 662 }
Chris@76 663
Chris@76 664 // Now the new.
Chris@76 665 if (!empty($columns['add']))
Chris@76 666 foreach ($columns['add'] as $add)
Chris@76 667 {
Chris@76 668 $new_columns[$add['name']] = array(
Chris@76 669 'name' => $add['name'],
Chris@76 670 'type' => $add['type'],
Chris@76 671 'size' => isset($add['size']) ? (int) $add['size'] : null,
Chris@76 672 'null' => !empty($add['null']),
Chris@76 673 'auto' => isset($add['auto']) ? $add['auto'] : false,
Chris@76 674 'default' => isset($add['default']) ? $add['default'] : '',
Chris@76 675 );
Chris@76 676
Chris@76 677 // Let's keep track of the name for the column.
Chris@76 678 $column_names[$add['name']] = strstr('int', $add['type']) ? ' 0 AS ' . $add['name'] : ' {string:empty_string} AS ' . $add['name'];
Chris@76 679 }
Chris@76 680
Chris@76 681 // Now to change a column. Not drop but change it.
Chris@76 682 if (isset($columns['change']))
Chris@76 683 foreach ($columns['change'] as $change)
Chris@76 684 if (isset($new_columns[$change['name']]))
Chris@76 685 $new_columns[$change['name']] = array(
Chris@76 686 'name' => $change['name'],
Chris@76 687 'type' => $change['type'],
Chris@76 688 'size' => isset($change['size']) ? (int) $change['size'] : null,
Chris@76 689 'null' => !empty($change['null']),
Chris@76 690 'auto' => isset($change['auto']) ? $change['auto'] : false,
Chris@76 691 'default' => isset($change['default']) ? $change['default'] : '',
Chris@76 692 );
Chris@76 693
Chris@76 694 // Now let's create the table.
Chris@76 695 $createTable = $smcFunc['db_create_table']($table_name, $new_columns, array(), array('skip_transaction' => true));
Chris@76 696
Chris@76 697 // Did it create correctly?
Chris@76 698 if ($createTable === false)
Chris@76 699 return false;
Chris@76 700
Chris@76 701 // Back to it's original table.
Chris@76 702 $insertData = $smcFunc['db_query']('', '
Chris@76 703 INSERT INTO {raw:table_name}
Chris@76 704 ({raw:columns})
Chris@76 705 SELECT ' . implode(', ', $column_names) . '
Chris@76 706 FROM {raw:temp_table_name}',
Chris@76 707 array(
Chris@76 708 'table_name' => $table_name,
Chris@76 709 'columns' => implode(', ', array_keys($new_columns)),
Chris@76 710 'columns_select' => implode(', ', $column_names),
Chris@76 711 'temp_table_name' => $table_name . '_tmp',
Chris@76 712 'empty_string' => '',
Chris@76 713 )
Chris@76 714 );
Chris@76 715
Chris@76 716 // Did everything insert correctly?
Chris@76 717 if (!$insertData)
Chris@76 718 return false;
Chris@76 719
Chris@76 720 // Drop the temp table.
Chris@76 721 $smcFunc['db_query']('', '
Chris@76 722 DROP TABLE {raw:temp_table_name}',
Chris@76 723 array(
Chris@76 724 'temp_table_name' => $table_name . '_tmp',
Chris@76 725 'db_error_skip' => true,
Chris@76 726 )
Chris@76 727 );
Chris@76 728
Chris@76 729 // Commit or else there is no point in doing the previous steps.
Chris@76 730 $smcFunc['db_transaction']('commit');
Chris@76 731
Chris@76 732 // We got here so we're good. The temp table should be deleted, if not it will be gone later on >:D.
Chris@76 733 return true;
Chris@76 734 }
Chris@76 735
Chris@76 736 ?>