Mercurial > hg > vamp-website

annotate forum/Sources/DbPackages-mysql.php @ 87:df86d318892b website

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Link to SampleType doc
author Chris Cannam
date Mon, 10 Feb 2014 18:11:48 +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_calculate_type' => 'smf_db_calculate_type',
Chris@76 56 'db_change_column' => 'smf_db_change_column',
Chris@76 57 'db_create_table' => 'smf_db_create_table',
Chris@76 58 'db_drop_table' => 'smf_db_drop_table',
Chris@76 59 'db_table_structure' => 'smf_db_table_structure',
Chris@76 60 'db_list_columns' => 'smf_db_list_columns',
Chris@76 61 'db_list_indexes' => 'smf_db_list_indexes',
Chris@76 62 'db_remove_column' => 'smf_db_remove_column',
Chris@76 63 'db_remove_index' => 'smf_db_remove_index',
Chris@76 64 );
Chris@76 65 $db_package_log = array();
Chris@76 66 }
Chris@76 67
Chris@76 68 // We setup an array of SMF tables we can't do auto-remove on - in case a mod writer cocks it up!
Chris@76 69 $reservedTables = array('admin_info_files', 'approval_queue', 'attachments', 'ban_groups', 'ban_items',
Chris@76 70 'board_permissions', 'boards', 'calendar', 'calendar_holidays', 'categories', 'collapsed_categories',
Chris@76 71 'custom_fields', 'group_moderators', 'log_actions', 'log_activity', 'log_banned', 'log_boards',
Chris@76 72 'log_digest', 'log_errors', 'log_floodcontrol', 'log_group_requests', 'log_karma', 'log_mark_read',
Chris@76 73 'log_notify', 'log_online', 'log_packages', 'log_polls', 'log_reported', 'log_reported_comments',
Chris@76 74 'log_scheduled_tasks', 'log_search_messages', 'log_search_results', 'log_search_subjects',
Chris@76 75 'log_search_topics', 'log_topics', 'mail_queue', 'membergroups', 'members', 'message_icons',
Chris@76 76 'messages', 'moderators', 'package_servers', 'permission_profiles', 'permissions', 'personal_messages',
Chris@76 77 'pm_recipients', 'poll_choices', 'polls', 'scheduled_tasks', 'sessions', 'settings', 'smileys',
Chris@76 78 'themes', 'topics');
Chris@76 79 foreach ($reservedTables as $k => $table_name)
Chris@76 80 $reservedTables[$k] = strtolower($db_prefix . $table_name);
Chris@76 81
Chris@76 82 // We in turn may need the extra stuff.
Chris@76 83 db_extend('extra');
Chris@76 84 }
Chris@76 85
Chris@76 86 // Create a table.
Chris@76 87 function smf_db_create_table($table_name, $columns, $indexes = array(), $parameters = array(), $if_exists = 'ignore', $error = 'fatal')
Chris@76 88 {
Chris@76 89 global $reservedTables, $smcFunc, $db_package_log, $db_prefix, $db_character_set;
Chris@76 90
Chris@76 91 // Strip out the table name, we might not need it in some cases
Chris@76 92 $real_prefix = preg_match('~^(`?)(.+?)\\1\\.(.*?)$~', $db_prefix, $match) === 1 ? $match[3] : $db_prefix;
Chris@76 93
Chris@76 94 // With or without the database name, the fullname looks like this.
Chris@76 95 $full_table_name = str_replace('{db_prefix}', $real_prefix, $table_name);
Chris@76 96 $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
Chris@76 97
Chris@76 98 // First - no way do we touch SMF tables.
Chris@76 99 if (in_array(strtolower($table_name), $reservedTables))
Chris@76 100 return false;
Chris@76 101
Chris@76 102 // Log that we'll want to remove this on uninstall.
Chris@76 103 $db_package_log[] = array('remove_table', $table_name);
Chris@76 104
Chris@76 105 // Slightly easier on MySQL than the others...
Chris@76 106 $tables = $smcFunc['db_list_tables']();
Chris@76 107 if (in_array($full_table_name, $tables))
Chris@76 108 {
Chris@76 109 // This is a sad day... drop the table? If not, return false (error) by default.
Chris@76 110 if ($if_exists == 'overwrite')
Chris@76 111 $smcFunc['db_drop_table']($table_name);
Chris@76 112 else
Chris@76 113 return $if_exists == 'ignore';
Chris@76 114 }
Chris@76 115
Chris@76 116 // Righty - let's do the damn thing!
Chris@76 117 $table_query = 'CREATE TABLE ' . $table_name . "\n" . '(';
Chris@76 118 foreach ($columns as $column)
Chris@76 119 {
Chris@76 120 // Auto increment is easy here!
Chris@76 121 if (!empty($column['auto']))
Chris@76 122 {
Chris@76 123 $default = 'auto_increment';
Chris@76 124 }
Chris@76 125 elseif (isset($column['default']) && $column['default'] !== null)
Chris@76 126 $default = 'default \'' . $smcFunc['db_escape_string']($column['default']) . '\'';
Chris@76 127 else
Chris@76 128 $default = '';
Chris@76 129
Chris@76 130 // Sort out the size... and stuff...
Chris@76 131 $column['size'] = isset($column['size']) && is_numeric($column['size']) ? $column['size'] : null;
Chris@76 132 list ($type, $size) = $smcFunc['db_calculate_type']($column['type'], $column['size']);
Chris@76 133
Chris@76 134 // Allow unsigned integers (mysql only)
Chris@76 135 $unsigned = in_array($type, array('int', 'tinyint', 'smallint', 'mediumint', 'bigint')) && !empty($column['unsigned']) ? 'unsigned ' : '';
Chris@76 136
Chris@76 137 if ($size !== null)
Chris@76 138 $type = $type . '(' . $size . ')';
Chris@76 139
Chris@76 140 // Now just put it together!
Chris@76 141 $table_query .= "\n\t`" .$column['name'] . '` ' . $type . ' ' . (!empty($unsigned) ? $unsigned : '') . (!empty($column['null']) ? '' : 'NOT NULL') . ' ' . $default . ',';
Chris@76 142 }
Chris@76 143
Chris@76 144 // Loop through the indexes next...
Chris@76 145 foreach ($indexes as $index)
Chris@76 146 {
Chris@76 147 $columns = implode(',', $index['columns']);
Chris@76 148
Chris@76 149 // Is it the primary?
Chris@76 150 if (isset($index['type']) && $index['type'] == 'primary')
Chris@76 151 $table_query .= "\n\t" . 'PRIMARY KEY (' . implode(',', $index['columns']) . '),';
Chris@76 152 else
Chris@76 153 {
Chris@76 154 if (empty($index['name']))
Chris@76 155 $index['name'] = implode('_', $index['columns']);
Chris@76 156 $table_query .= "\n\t" . (isset($index['type']) && $index['type'] == 'unique' ? 'UNIQUE' : 'KEY') . ' ' . $index['name'] . ' (' . $columns . '),';
Chris@76 157 }
Chris@76 158 }
Chris@76 159
Chris@76 160 // No trailing commas!
Chris@76 161 if (substr($table_query, -1) == ',')
Chris@76 162 $table_query = substr($table_query, 0, -1);
Chris@76 163
Chris@76 164 $table_query .= ') ENGINE=MyISAM';
Chris@76 165 if (!empty($db_character_set) && $db_character_set == 'utf8')
Chris@76 166 $table_query .= ' DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci';
Chris@76 167
Chris@76 168 // Create the table!
Chris@76 169 $smcFunc['db_query']('', $table_query,
Chris@76 170 array(
Chris@76 171 'security_override' => true,
Chris@76 172 )
Chris@76 173 );
Chris@76 174 }
Chris@76 175
Chris@76 176 // Drop a table.
Chris@76 177 function smf_db_drop_table($table_name, $parameters = array(), $error = 'fatal')
Chris@76 178 {
Chris@76 179 global $reservedTables, $smcFunc, $db_prefix;
Chris@76 180
Chris@76 181 // After stripping away the database name, this is what's left.
Chris@76 182 $real_prefix = preg_match('~^(`?)(.+?)\\1\\.(.*?)$~', $db_prefix, $match) === 1 ? $match[3] : $db_prefix;
Chris@76 183
Chris@76 184 // Get some aliases.
Chris@76 185 $full_table_name = str_replace('{db_prefix}', $real_prefix, $table_name);
Chris@76 186 $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
Chris@76 187
Chris@76 188 // God no - dropping one of these = bad.
Chris@76 189 if (in_array(strtolower($table_name), $reservedTables))
Chris@76 190 return false;
Chris@76 191
Chris@76 192 // Does it exist?
Chris@76 193 if (in_array($full_table_name, $smcFunc['db_list_tables']()))
Chris@76 194 {
Chris@76 195 $query = 'DROP TABLE ' . $table_name;
Chris@76 196 $smcFunc['db_query']('',
Chris@76 197 $query,
Chris@76 198 array(
Chris@76 199 'security_override' => true,
Chris@76 200 )
Chris@76 201 );
Chris@76 202
Chris@76 203 return true;
Chris@76 204 }
Chris@76 205
Chris@76 206 // Otherwise do 'nout.
Chris@76 207 return false;
Chris@76 208 }
Chris@76 209
Chris@76 210 // Add a column.
Chris@76 211 function smf_db_add_column($table_name, $column_info, $parameters = array(), $if_exists = 'update', $error = 'fatal')
Chris@76 212 {
Chris@76 213 global $smcFunc, $db_package_log, $txt, $db_prefix;
Chris@76 214
Chris@76 215 $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
Chris@76 216
Chris@76 217 // Log that we will want to uninstall this!
Chris@76 218 $db_package_log[] = array('remove_column', $table_name, $column_info['name']);
Chris@76 219
Chris@76 220 // Does it exist - if so don't add it again!
Chris@76 221 $columns = $smcFunc['db_list_columns']($table_name, false);
Chris@76 222 foreach ($columns as $column)
Chris@76 223 if ($column == $column_info['name'])
Chris@76 224 {
Chris@76 225 // If we're going to overwrite then use change column.
Chris@76 226 if ($if_exists == 'update')
Chris@76 227 return $smcFunc['db_change_column']($table_name, $column_info['name'], $column_info);
Chris@76 228 else
Chris@76 229 return false;
Chris@76 230 }
Chris@76 231
Chris@76 232 // Get the specifics...
Chris@76 233 $column_info['size'] = isset($column_info['size']) && is_numeric($column_info['size']) ? $column_info['size'] : null;
Chris@76 234 list ($type, $size) = $smcFunc['db_calculate_type']($column_info['type'], $column_info['size']);
Chris@76 235
Chris@76 236 // Allow unsigned integers (mysql only)
Chris@76 237 $unsigned = in_array($type, array('int', 'tinyint', 'smallint', 'mediumint', 'bigint')) && !empty($column_info['unsigned']) ? 'unsigned ' : '';
Chris@76 238
Chris@76 239 if ($size !== null)
Chris@76 240 $type = $type . '(' . $size . ')';
Chris@76 241
Chris@76 242 // Now add the thing!
Chris@76 243 $query = '
Chris@76 244 ALTER TABLE ' . $table_name . '
Chris@76 245 ADD `' . $column_info['name'] . '` ' . $type . ' ' . (!empty($unsigned) ? $unsigned : '') . (empty($column_info['null']) ? 'NOT NULL' : '') . ' ' .
Chris@76 246 (!isset($column_info['default']) ? '' : 'default \'' . $smcFunc['db_escape_string']($column_info['default']) . '\'') . ' ' .
Chris@76 247 (empty($column_info['auto']) ? '' : 'auto_increment primary key') . ' ';
Chris@76 248 $smcFunc['db_query']('', $query,
Chris@76 249 array(
Chris@76 250 'security_override' => true,
Chris@76 251 )
Chris@76 252 );
Chris@76 253
Chris@76 254 return true;
Chris@76 255 }
Chris@76 256
Chris@76 257 // Remove a column.
Chris@76 258 function smf_db_remove_column($table_name, $column_name, $parameters = array(), $error = 'fatal')
Chris@76 259 {
Chris@76 260 global $smcFunc, $db_prefix;
Chris@76 261
Chris@76 262 $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
Chris@76 263
Chris@76 264 // Does it exist?
Chris@76 265 $columns = $smcFunc['db_list_columns']($table_name, true);
Chris@76 266 foreach ($columns as $column)
Chris@76 267 if ($column['name'] == $column_name)
Chris@76 268 {
Chris@76 269 $smcFunc['db_query']('', '
Chris@76 270 ALTER TABLE ' . $table_name . '
Chris@76 271 DROP COLUMN ' . $column_name,
Chris@76 272 array(
Chris@76 273 'security_override' => true,
Chris@76 274 )
Chris@76 275 );
Chris@76 276
Chris@76 277 return true;
Chris@76 278 }
Chris@76 279
Chris@76 280 // If here we didn't have to work - joy!
Chris@76 281 return false;
Chris@76 282 }
Chris@76 283
Chris@76 284 // Change a column.
Chris@76 285 function smf_db_change_column($table_name, $old_column, $column_info, $parameters = array(), $error = 'fatal')
Chris@76 286 {
Chris@76 287 global $smcFunc, $db_prefix;
Chris@76 288
Chris@76 289 $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
Chris@76 290
Chris@76 291 // Check it does exist!
Chris@76 292 $columns = $smcFunc['db_list_columns']($table_name, true);
Chris@76 293 $old_info = null;
Chris@76 294 foreach ($columns as $column)
Chris@76 295 if ($column['name'] == $old_column)
Chris@76 296 $old_info = $column;
Chris@76 297
Chris@76 298 // Nothing?
Chris@76 299 if ($old_info == null)
Chris@76 300 return false;
Chris@76 301
Chris@76 302 // Get the right bits.
Chris@76 303 if (!isset($column_info['name']))
Chris@76 304 $column_info['name'] = $old_column;
Chris@76 305 if (!isset($column_info['default']))
Chris@76 306 $column_info['default'] = $old_info['default'];
Chris@76 307 if (!isset($column_info['null']))
Chris@76 308 $column_info['null'] = $old_info['null'];
Chris@76 309 if (!isset($column_info['auto']))
Chris@76 310 $column_info['auto'] = $old_info['auto'];
Chris@76 311 if (!isset($column_info['type']))
Chris@76 312 $column_info['type'] = $old_info['type'];
Chris@76 313 if (!isset($column_info['size']) || !is_numeric($column_info['size']))
Chris@76 314 $column_info['size'] = $old_info['size'];
Chris@76 315 if (!isset($column_info['unsigned']) || !in_array($column_info['type'], array('int', 'tinyint', 'smallint', 'mediumint', 'bigint')))
Chris@76 316 $column_info['unsigned'] = '';
Chris@76 317
Chris@76 318 list ($type, $size) = $smcFunc['db_calculate_type']($column_info['type'], $column_info['size']);
Chris@76 319
Chris@76 320 // Allow for unsigned integers (mysql only)
Chris@76 321 $unsigned = in_array($type, array('int', 'tinyint', 'smallint', 'mediumint', 'bigint')) && !empty($column_info['unsigned']) ? 'unsigned ' : '';
Chris@76 322
Chris@76 323 if ($size !== null)
Chris@76 324 $type = $type . '(' . $size . ')';
Chris@76 325
Chris@76 326 $smcFunc['db_query']('', '
Chris@76 327 ALTER TABLE ' . $table_name . '
Chris@76 328 CHANGE COLUMN `' . $old_column . '` `' . $column_info['name'] . '` ' . $type . ' ' . (!empty($unsigned) ? $unsigned : '') . (empty($column_info['null']) ? 'NOT NULL' : '') . ' ' .
Chris@76 329 (!isset($column_info['default']) ? '' : 'default \'' . $smcFunc['db_escape_string']($column_info['default']) . '\'') . ' ' .
Chris@76 330 (empty($column_info['auto']) ? '' : 'auto_increment') . ' ',
Chris@76 331 array(
Chris@76 332 'security_override' => true,
Chris@76 333 )
Chris@76 334 );
Chris@76 335 }
Chris@76 336
Chris@76 337 // Add an index.
Chris@76 338 function smf_db_add_index($table_name, $index_info, $parameters = array(), $if_exists = 'update', $error = 'fatal')
Chris@76 339 {
Chris@76 340 global $smcFunc, $db_package_log, $db_prefix;
Chris@76 341
Chris@76 342 $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
Chris@76 343
Chris@76 344 // No columns = no index.
Chris@76 345 if (empty($index_info['columns']))
Chris@76 346 return false;
Chris@76 347 $columns = implode(',', $index_info['columns']);
Chris@76 348
Chris@76 349 // No name - make it up!
Chris@76 350 if (empty($index_info['name']))
Chris@76 351 {
Chris@76 352 // No need for primary.
Chris@76 353 if (isset($index_info['type']) && $index_info['type'] == 'primary')
Chris@76 354 $index_info['name'] = '';
Chris@76 355 else
Chris@76 356 $index_info['name'] = implode('_', $index_info['columns']);
Chris@76 357 }
Chris@76 358 else
Chris@76 359 $index_info['name'] = $index_info['name'];
Chris@76 360
Chris@76 361 // Log that we are going to want to remove this!
Chris@76 362 $db_package_log[] = array('remove_index', $table_name, $index_info['name']);
Chris@76 363
Chris@76 364 // Let's get all our indexes.
Chris@76 365 $indexes = $smcFunc['db_list_indexes']($table_name, true);
Chris@76 366 // Do we already have it?
Chris@76 367 foreach ($indexes as $index)
Chris@76 368 {
Chris@76 369 if ($index['name'] == $index_info['name'] || ($index['type'] == 'primary' && isset($index_info['type']) && $index_info['type'] == 'primary'))
Chris@76 370 {
Chris@76 371 // If we want to overwrite simply remove the current one then continue.
Chris@76 372 if ($if_exists != 'update' || $index['type'] == 'primary')
Chris@76 373 return false;
Chris@76 374 else
Chris@76 375 $smcFunc['db_remove_index']($table_name, $index_info['name']);
Chris@76 376 }
Chris@76 377 }
Chris@76 378
Chris@76 379 // If we're here we know we don't have the index - so just add it.
Chris@76 380 if (!empty($index_info['type']) && $index_info['type'] == 'primary')
Chris@76 381 {
Chris@76 382 $smcFunc['db_query']('', '
Chris@76 383 ALTER TABLE ' . $table_name . '
Chris@76 384 ADD PRIMARY KEY (' . $columns . ')',
Chris@76 385 array(
Chris@76 386 'security_override' => true,
Chris@76 387 )
Chris@76 388 );
Chris@76 389 }
Chris@76 390 else
Chris@76 391 {
Chris@76 392 $smcFunc['db_query']('', '
Chris@76 393 ALTER TABLE ' . $table_name . '
Chris@76 394 ADD ' . (isset($index_info['type']) && $index_info['type'] == 'unique' ? 'UNIQUE' : 'INDEX') . ' ' . $index_info['name'] . ' (' . $columns . ')',
Chris@76 395 array(
Chris@76 396 'security_override' => true,
Chris@76 397 )
Chris@76 398 );
Chris@76 399 }
Chris@76 400 }
Chris@76 401
Chris@76 402 // Remove an index.
Chris@76 403 function smf_db_remove_index($table_name, $index_name, $parameters = array(), $error = 'fatal')
Chris@76 404 {
Chris@76 405 global $smcFunc, $db_prefix;
Chris@76 406
Chris@76 407 $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
Chris@76 408
Chris@76 409 // Better exist!
Chris@76 410 $indexes = $smcFunc['db_list_indexes']($table_name, true);
Chris@76 411
Chris@76 412 foreach ($indexes as $index)
Chris@76 413 {
Chris@76 414 // If the name is primary we want the primary key!
Chris@76 415 if ($index['type'] == 'primary' && $index_name == 'primary')
Chris@76 416 {
Chris@76 417 // Dropping primary key?
Chris@76 418 $smcFunc['db_query']('', '
Chris@76 419 ALTER TABLE ' . $table_name . '
Chris@76 420 DROP PRIMARY KEY',
Chris@76 421 array(
Chris@76 422 'security_override' => true,
Chris@76 423 )
Chris@76 424 );
Chris@76 425
Chris@76 426 return true;
Chris@76 427 }
Chris@76 428 if ($index['name'] == $index_name)
Chris@76 429 {
Chris@76 430 // Drop the bugger...
Chris@76 431 $smcFunc['db_query']('', '
Chris@76 432 ALTER TABLE ' . $table_name . '
Chris@76 433 DROP INDEX ' . $index_name,
Chris@76 434 array(
Chris@76 435 'security_override' => true,
Chris@76 436 )
Chris@76 437 );
Chris@76 438
Chris@76 439 return true;
Chris@76 440 }
Chris@76 441 }
Chris@76 442
Chris@76 443 // Not to be found ;(
Chris@76 444 return false;
Chris@76 445 }
Chris@76 446
Chris@76 447 // Get the schema formatted name for a type.
Chris@76 448 function smf_db_calculate_type($type_name, $type_size = null, $reverse = false)
Chris@76 449 {
Chris@76 450 // MySQL is actually the generic baseline.
Chris@76 451 return array($type_name, $type_size);
Chris@76 452 }
Chris@76 453
Chris@76 454 // Get table structure.
Chris@76 455 function smf_db_table_structure($table_name, $parameters = array())
Chris@76 456 {
Chris@76 457 global $smcFunc, $db_prefix;
Chris@76 458
Chris@76 459 $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
Chris@76 460
Chris@76 461 return array(
Chris@76 462 'name' => $table_name,
Chris@76 463 'columns' => $smcFunc['db_list_columns']($table_name, true),
Chris@76 464 'indexes' => $smcFunc['db_list_indexes']($table_name, true),
Chris@76 465 );
Chris@76 466 }
Chris@76 467
Chris@76 468 // Return column information for a table.
Chris@76 469 function smf_db_list_columns($table_name, $detail = false, $parameters = array())
Chris@76 470 {
Chris@76 471 global $smcFunc, $db_prefix;
Chris@76 472
Chris@76 473 $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
Chris@76 474
Chris@76 475 $result = $smcFunc['db_query']('', '
Chris@76 476 SHOW FIELDS
Chris@76 477 FROM {raw:table_name}',
Chris@76 478 array(
Chris@76 479 'table_name' => substr($table_name, 0, 1) == '`' ? $table_name : '`' . $table_name . '`',
Chris@76 480 )
Chris@76 481 );
Chris@76 482 $columns = array();
Chris@76 483 while ($row = $smcFunc['db_fetch_assoc']($result))
Chris@76 484 {
Chris@76 485 if (!$detail)
Chris@76 486 {
Chris@76 487 $columns[] = $row['Field'];
Chris@76 488 }
Chris@76 489 else
Chris@76 490 {
Chris@76 491 // Is there an auto_increment?
Chris@76 492 $auto = strpos($row['Extra'], 'auto_increment') !== false ? true : false;
Chris@76 493
Chris@76 494 // Can we split out the size?
Chris@76 495 if (preg_match('~(.+?)\s*\((\d+)\)(?:(?:\s*)?(unsigned))?~i', $row['Type'], $matches) === 1)
Chris@76 496 {
Chris@76 497 $type = $matches[1];
Chris@76 498 $size = $matches[2];
Chris@76 499 if (!empty($matches[3]) && $matches[3] == 'unsigned')
Chris@76 500 $unsigned = true;
Chris@76 501 }
Chris@76 502 else
Chris@76 503 {
Chris@76 504 $type = $row['Type'];
Chris@76 505 $size = null;
Chris@76 506 }
Chris@76 507
Chris@76 508 $columns[$row['Field']] = array(
Chris@76 509 'name' => $row['Field'],
Chris@76 510 'null' => $row['Null'] != 'YES' ? false : true,
Chris@76 511 'default' => isset($row['Default']) ? $row['Default'] : null,
Chris@76 512 'type' => $type,
Chris@76 513 'size' => $size,
Chris@76 514 'auto' => $auto,
Chris@76 515 );
Chris@76 516
Chris@76 517 if (isset($unsigned))
Chris@76 518 {
Chris@76 519 $columns[$row['Field']]['unsigned'] = $unsigned;
Chris@76 520 unset($unsigned);
Chris@76 521 }
Chris@76 522 }
Chris@76 523 }
Chris@76 524 $smcFunc['db_free_result']($result);
Chris@76 525
Chris@76 526 return $columns;
Chris@76 527 }
Chris@76 528
Chris@76 529 // What about some index information?
Chris@76 530 function smf_db_list_indexes($table_name, $detail = false, $parameters = array())
Chris@76 531 {
Chris@76 532 global $smcFunc, $db_prefix;
Chris@76 533
Chris@76 534 $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
Chris@76 535
Chris@76 536 $result = $smcFunc['db_query']('', '
Chris@76 537 SHOW KEYS
Chris@76 538 FROM {raw:table_name}',
Chris@76 539 array(
Chris@76 540 'table_name' => substr($table_name, 0, 1) == '`' ? $table_name : '`' . $table_name . '`',
Chris@76 541 )
Chris@76 542 );
Chris@76 543 $indexes = array();
Chris@76 544 while ($row = $smcFunc['db_fetch_assoc']($result))
Chris@76 545 {
Chris@76 546 if (!$detail)
Chris@76 547 $indexes[] = $row['Key_name'];
Chris@76 548 else
Chris@76 549 {
Chris@76 550 // What is the type?
Chris@76 551 if ($row['Key_name'] == 'PRIMARY')
Chris@76 552 $type = 'primary';
Chris@76 553 elseif (empty($row['Non_unique']))
Chris@76 554 $type = 'unique';
Chris@76 555 elseif (isset($row['Index_type']) && $row['Index_type'] == 'FULLTEXT')
Chris@76 556 $type = 'fulltext';
Chris@76 557 else
Chris@76 558 $type = 'index';
Chris@76 559
Chris@76 560 // This is the first column we've seen?
Chris@76 561 if (empty($indexes[$row['Key_name']]))
Chris@76 562 {
Chris@76 563 $indexes[$row['Key_name']] = array(
Chris@76 564 'name' => $row['Key_name'],
Chris@76 565 'type' => $type,
Chris@76 566 'columns' => array(),
Chris@76 567 );
Chris@76 568 }
Chris@76 569
Chris@76 570 // Is it a partial index?
Chris@76 571 if (!empty($row['Sub_part']))
Chris@76 572 $indexes[$row['Key_name']]['columns'][] = $row['Column_name'] . '(' . $row['Sub_part'] . ')';
Chris@76 573 else
Chris@76 574 $indexes[$row['Key_name']]['columns'][] = $row['Column_name'];
Chris@76 575 }
Chris@76 576 }
Chris@76 577 $smcFunc['db_free_result']($result);
Chris@76 578
Chris@76 579 return $indexes;
Chris@76 580 }
Chris@76 581
Chris@76 582 ?>