Mercurial > hg > vamp-website

annotate forum/Sources/DbExtra-mysql.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 rarely used extended database functionality.
Chris@76 18
Chris@76 19 void db_extra_init()
Chris@76 20 - add this file's functions to the $smcFunc array.
Chris@76 21
Chris@76 22 resource smf_db_backup_table($table, $backup_table)
Chris@76 23 - backup $table to $backup_table.
Chris@76 24 - returns the request handle to the table creation query
Chris@76 25
Chris@76 26 string function smf_db_get_version()
Chris@76 27 - get the version number.
Chris@76 28
Chris@76 29 string db_insert_sql(string table_name)
Chris@76 30 - gets all the necessary INSERTs for the table named table_name.
Chris@76 31 - goes in 250 row segments.
Chris@76 32 - returns the query to insert the data back in.
Chris@76 33 - returns an empty string if the table was empty.
Chris@76 34
Chris@76 35 array smf_db_list_tables($db = false, $filter = false)
Chris@76 36 - lists all tables in the database
Chris@76 37 - could be filtered according to $filter
Chris@76 38 - returns an array of table names. (strings)
Chris@76 39
Chris@76 40 float smf_db_optimize_table($table)
Chris@76 41 - optimize a table
Chris@76 42 - $table - the table to be optimized
Chris@76 43 - returns how much it was gained
Chris@76 44
Chris@76 45 string db_table_sql(string table_name)
Chris@76 46 - dumps the CREATE for the specified table. (by table_name.)
Chris@76 47 - returns the CREATE statement.
Chris@76 48
Chris@76 49 */
Chris@76 50
Chris@76 51 // Add the file functions to the $smcFunc array.
Chris@76 52 function db_extra_init()
Chris@76 53 {
Chris@76 54 global $smcFunc;
Chris@76 55
Chris@76 56 if (!isset($smcFunc['db_backup_table']) || $smcFunc['db_backup_table'] != 'smf_db_backup_table')
Chris@76 57 $smcFunc += array(
Chris@76 58 'db_backup_table' => 'smf_db_backup_table',
Chris@76 59 'db_optimize_table' => 'smf_db_optimize_table',
Chris@76 60 'db_insert_sql' => 'smf_db_insert_sql',
Chris@76 61 'db_table_sql' => 'smf_db_table_sql',
Chris@76 62 'db_list_tables' => 'smf_db_list_tables',
Chris@76 63 'db_get_version' => 'smf_db_get_version',
Chris@76 64 );
Chris@76 65 }
Chris@76 66
Chris@76 67 // Backup $table to $backup_table.
Chris@76 68 function smf_db_backup_table($table, $backup_table)
Chris@76 69 {
Chris@76 70 global $smcFunc, $db_prefix;
Chris@76 71
Chris@76 72 $table = str_replace('{db_prefix}', $db_prefix, $table);
Chris@76 73
Chris@76 74 // First, get rid of the old table.
Chris@76 75 $smcFunc['db_query']('', '
Chris@76 76 DROP TABLE IF EXISTS {raw:backup_table}',
Chris@76 77 array(
Chris@76 78 'backup_table' => $backup_table,
Chris@76 79 )
Chris@76 80 );
Chris@76 81
Chris@76 82 // Can we do this the quick way?
Chris@76 83 $result = $smcFunc['db_query']('', '
Chris@76 84 CREATE TABLE {raw:backup_table} LIKE {raw:table}',
Chris@76 85 array(
Chris@76 86 'backup_table' => $backup_table,
Chris@76 87 'table' => $table
Chris@76 88 ));
Chris@76 89 // If this failed, we go old school.
Chris@76 90 if ($result)
Chris@76 91 {
Chris@76 92 $request = $smcFunc['db_query']('', '
Chris@76 93 INSERT INTO {raw:backup_table}
Chris@76 94 SELECT *
Chris@76 95 FROM {raw:table}',
Chris@76 96 array(
Chris@76 97 'backup_table' => $backup_table,
Chris@76 98 'table' => $table
Chris@76 99 ));
Chris@76 100
Chris@76 101 // Old school or no school?
Chris@76 102 if ($request)
Chris@76 103 return $request;
Chris@76 104 }
Chris@76 105
Chris@76 106 // At this point, the quick method failed.
Chris@76 107 $result = $smcFunc['db_query']('', '
Chris@76 108 SHOW CREATE TABLE {raw:table}',
Chris@76 109 array(
Chris@76 110 'table' => $table,
Chris@76 111 )
Chris@76 112 );
Chris@76 113 list (, $create) = $smcFunc['db_fetch_row']($result);
Chris@76 114 $smcFunc['db_free_result']($result);
Chris@76 115
Chris@76 116 $create = preg_split('/[\n\r]/', $create);
Chris@76 117
Chris@76 118 $auto_inc = '';
Chris@76 119 // Default engine type.
Chris@76 120 $engine = 'MyISAM';
Chris@76 121 $charset = '';
Chris@76 122 $collate = '';
Chris@76 123
Chris@76 124 foreach ($create as $k => $l)
Chris@76 125 {
Chris@76 126 // Get the name of the auto_increment column.
Chris@76 127 if (strpos($l, 'auto_increment'))
Chris@76 128 $auto_inc = trim($l);
Chris@76 129
Chris@76 130 // For the engine type, see if we can work out what it is.
Chris@76 131 if (strpos($l, 'ENGINE') !== false || strpos($l, 'TYPE') !== false)
Chris@76 132 {
Chris@76 133 // Extract the engine type.
Chris@76 134 preg_match('~(ENGINE|TYPE)=(\w+)(\sDEFAULT)?(\sCHARSET=(\w+))?(\sCOLLATE=(\w+))?~', $l, $match);
Chris@76 135
Chris@76 136 if (!empty($match[1]))
Chris@76 137 $engine = $match[1];
Chris@76 138
Chris@76 139 if (!empty($match[2]))
Chris@76 140 $engine = $match[2];
Chris@76 141
Chris@76 142 if (!empty($match[5]))
Chris@76 143 $charset = $match[5];
Chris@76 144
Chris@76 145 if (!empty($match[7]))
Chris@76 146 $collate = $match[7];
Chris@76 147 }
Chris@76 148
Chris@76 149 // Skip everything but keys...
Chris@76 150 if (strpos($l, 'KEY') === false)
Chris@76 151 unset($create[$k]);
Chris@76 152 }
Chris@76 153
Chris@76 154 if (!empty($create))
Chris@76 155 $create = '(
Chris@76 156 ' . implode('
Chris@76 157 ', $create) . ')';
Chris@76 158 else
Chris@76 159 $create = '';
Chris@76 160
Chris@76 161 $request = $smcFunc['db_query']('', '
Chris@76 162 CREATE TABLE {raw:backup_table} {raw:create}
Chris@76 163 ENGINE={raw:engine}' . (empty($charset) ? '' : ' CHARACTER SET {raw:charset}' . (empty($collate) ? '' : ' COLLATE {raw:collate}')) . '
Chris@76 164 SELECT *
Chris@76 165 FROM {raw:table}',
Chris@76 166 array(
Chris@76 167 'backup_table' => $backup_table,
Chris@76 168 'table' => $table,
Chris@76 169 'create' => $create,
Chris@76 170 'engine' => $engine,
Chris@76 171 'charset' => empty($charset) ? '' : $charset,
Chris@76 172 'collate' => empty($collate) ? '' : $collate,
Chris@76 173 )
Chris@76 174 );
Chris@76 175
Chris@76 176 if ($auto_inc != '')
Chris@76 177 {
Chris@76 178 if (preg_match('~\`(.+?)\`\s~', $auto_inc, $match) != 0 && substr($auto_inc, -1, 1) == ',')
Chris@76 179 $auto_inc = substr($auto_inc, 0, -1);
Chris@76 180
Chris@76 181 $smcFunc['db_query']('', '
Chris@76 182 ALTER TABLE {raw:backup_table}
Chris@76 183 CHANGE COLUMN {raw:column_detail} {raw:auto_inc}',
Chris@76 184 array(
Chris@76 185 'backup_table' => $backup_table,
Chris@76 186 'column_detail' => $match[1],
Chris@76 187 'auto_inc' => $auto_inc,
Chris@76 188 )
Chris@76 189 );
Chris@76 190 }
Chris@76 191
Chris@76 192 return $request;
Chris@76 193 }
Chris@76 194
Chris@76 195 // Optimize a table - return data freed!
Chris@76 196 function smf_db_optimize_table($table)
Chris@76 197 {
Chris@76 198 global $smcFunc, $db_name, $db_prefix;
Chris@76 199
Chris@76 200 $table = str_replace('{db_prefix}', $db_prefix, $table);
Chris@76 201
Chris@76 202 // Get how much overhead there is.
Chris@76 203 $request = $smcFunc['db_query']('', '
Chris@76 204 SHOW TABLE STATUS LIKE {string:table_name}',
Chris@76 205 array(
Chris@76 206 'table_name' => str_replace('_', '\_', $table),
Chris@76 207 )
Chris@76 208 );
Chris@76 209 $row = $smcFunc['db_fetch_assoc']($request);
Chris@76 210 $smcFunc['db_free_result']($request);
Chris@76 211
Chris@76 212 $data_before = isset($row['Data_free']) ? $row['Data_free'] : 0;
Chris@76 213 $request = $smcFunc['db_query']('', '
Chris@76 214 OPTIMIZE TABLE `{raw:table}`',
Chris@76 215 array(
Chris@76 216 'table' => $table,
Chris@76 217 )
Chris@76 218 );
Chris@76 219 if (!$request)
Chris@76 220 return -1;
Chris@76 221
Chris@76 222 // How much left?
Chris@76 223 $request = $smcFunc['db_query']('', '
Chris@76 224 SHOW TABLE STATUS LIKE {string:table}',
Chris@76 225 array(
Chris@76 226 'table' => str_replace('_', '\_', $table),
Chris@76 227 )
Chris@76 228 );
Chris@76 229 $row = $smcFunc['db_fetch_assoc']($request);
Chris@76 230 $smcFunc['db_free_result']($request);
Chris@76 231
Chris@76 232 $total_change = isset($row['Data_free']) && $data_before > $row['Data_free'] ? $data_before / 1024 : 0;
Chris@76 233
Chris@76 234 return $total_change;
Chris@76 235 }
Chris@76 236
Chris@76 237 // List all the tables in the database.
Chris@76 238 function smf_db_list_tables($db = false, $filter = false)
Chris@76 239 {
Chris@76 240 global $db_name, $smcFunc;
Chris@76 241
Chris@76 242 $db = $db == false ? $db_name : $db;
Chris@76 243 $db = trim($db);
Chris@76 244 $filter = $filter == false ? '' : ' LIKE \'' . $filter . '\'';
Chris@76 245
Chris@76 246 $request = $smcFunc['db_query']('', '
Chris@76 247 SHOW TABLES
Chris@76 248 FROM `{raw:db}`
Chris@76 249 {raw:filter}',
Chris@76 250 array(
Chris@76 251 'db' => $db[0] == '`' ? strtr($db, array('`' => '')) : $db,
Chris@76 252 'filter' => $filter,
Chris@76 253 )
Chris@76 254 );
Chris@76 255 $tables = array();
Chris@76 256 while ($row = $smcFunc['db_fetch_row']($request))
Chris@76 257 $tables[] = $row[0];
Chris@76 258 $smcFunc['db_free_result']($request);
Chris@76 259
Chris@76 260 return $tables;
Chris@76 261 }
Chris@76 262
Chris@76 263 // Get the content (INSERTs) for a table.
Chris@76 264 function smf_db_insert_sql($tableName)
Chris@76 265 {
Chris@76 266 global $smcFunc, $db_prefix;
Chris@76 267
Chris@76 268 $tableName = str_replace('{db_prefix}', $db_prefix, $tableName);
Chris@76 269
Chris@76 270 // This will be handy...
Chris@76 271 $crlf = "\r\n";
Chris@76 272
Chris@76 273 // Get everything from the table.
Chris@76 274 $result = $smcFunc['db_query']('', '
Chris@76 275 SELECT /*!40001 SQL_NO_CACHE */ *
Chris@76 276 FROM `{raw:table}`',
Chris@76 277 array(
Chris@76 278 'table' => $tableName,
Chris@76 279 )
Chris@76 280 );
Chris@76 281
Chris@76 282 // The number of rows, just for record keeping and breaking INSERTs up.
Chris@76 283 $num_rows = $smcFunc['db_num_rows']($result);
Chris@76 284 $current_row = 0;
Chris@76 285
Chris@76 286 if ($num_rows == 0)
Chris@76 287 return '';
Chris@76 288
Chris@76 289 $fields = array_keys($smcFunc['db_fetch_assoc']($result));
Chris@76 290 $smcFunc['db_data_seek']($result, 0);
Chris@76 291
Chris@76 292 // Start it off with the basic INSERT INTO.
Chris@76 293 $data = 'INSERT INTO `' . $tableName . '`' . $crlf . "\t" . '(`' . implode('`, `', $fields) . '`)' . $crlf . 'VALUES ';
Chris@76 294
Chris@76 295 // Loop through each row.
Chris@76 296 while ($row = $smcFunc['db_fetch_row']($result))
Chris@76 297 {
Chris@76 298 $current_row++;
Chris@76 299
Chris@76 300 // Get the fields in this row...
Chris@76 301 $field_list = array();
Chris@76 302 for ($j = 0; $j < $smcFunc['db_num_fields']($result); $j++)
Chris@76 303 {
Chris@76 304 // Try to figure out the type of each field. (NULL, number, or 'string'.)
Chris@76 305 if (!isset($row[$j]))
Chris@76 306 $field_list[] = 'NULL';
Chris@76 307 elseif (is_numeric($row[$j]) && (int) $row[$j] == $row[$j])
Chris@76 308 $field_list[] = $row[$j];
Chris@76 309 else
Chris@76 310 $field_list[] = '\'' . $smcFunc['db_escape_string']($row[$j]) . '\'';
Chris@76 311 }
Chris@76 312
Chris@76 313 // 'Insert' the data.
Chris@76 314 $data .= '(' . implode(', ', $field_list) . ')';
Chris@76 315
Chris@76 316 // All done!
Chris@76 317 if ($current_row == $num_rows)
Chris@76 318 $data .= ';' . $crlf;
Chris@76 319 // Start a new INSERT statement after every 250....
Chris@76 320 elseif ($current_row > 249 && $current_row % 250 == 0)
Chris@76 321 $data .= ';' . $crlf . 'INSERT INTO `' . $tableName . '`' . $crlf . "\t" . '(`' . implode('`, `', $fields) . '`)' . $crlf . 'VALUES ';
Chris@76 322 // Otherwise, go to the next line.
Chris@76 323 else
Chris@76 324 $data .= ',' . $crlf . "\t";
Chris@76 325 }
Chris@76 326 $smcFunc['db_free_result']($result);
Chris@76 327
Chris@76 328 // Return an empty string if there were no rows.
Chris@76 329 return $num_rows == 0 ? '' : $data;
Chris@76 330 }
Chris@76 331
Chris@76 332 // Get the schema (CREATE) for a table.
Chris@76 333 function smf_db_table_sql($tableName)
Chris@76 334 {
Chris@76 335 global $smcFunc, $db_prefix;
Chris@76 336
Chris@76 337 $tableName = str_replace('{db_prefix}', $db_prefix, $tableName);
Chris@76 338
Chris@76 339 // This will be needed...
Chris@76 340 $crlf = "\r\n";
Chris@76 341
Chris@76 342 // Drop it if it exists.
Chris@76 343 $schema_create = 'DROP TABLE IF EXISTS `' . $tableName . '`;' . $crlf . $crlf;
Chris@76 344
Chris@76 345 // Start the create table...
Chris@76 346 $schema_create .= 'CREATE TABLE `' . $tableName . '` (' . $crlf;
Chris@76 347
Chris@76 348 // Find all the fields.
Chris@76 349 $result = $smcFunc['db_query']('', '
Chris@76 350 SHOW FIELDS
Chris@76 351 FROM `{raw:table}`',
Chris@76 352 array(
Chris@76 353 'table' => $tableName,
Chris@76 354 )
Chris@76 355 );
Chris@76 356 while ($row = $smcFunc['db_fetch_assoc']($result))
Chris@76 357 {
Chris@76 358 // Make the CREATE for this column.
Chris@76 359 $schema_create .= ' `' . $row['Field'] . '` ' . $row['Type'] . ($row['Null'] != 'YES' ? ' NOT NULL' : '');
Chris@76 360
Chris@76 361 // Add a default...?
Chris@76 362 if (!empty($row['Default']) || $row['Null'] !== 'YES')
Chris@76 363 {
Chris@76 364 // Make a special case of auto-timestamp.
Chris@76 365 if ($row['Default'] == 'CURRENT_TIMESTAMP')
Chris@76 366 $schema_create .= ' /*!40102 NOT NULL default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP */';
Chris@76 367 // Text shouldn't have a default.
Chris@76 368 elseif ($row['Default'] !== null)
Chris@76 369 {
Chris@76 370 // If this field is numeric the default needs no escaping.
Chris@76 371 $type = strtolower($row['Type']);
Chris@76 372 $isNumericColumn = strpos($type, 'int') !== false || strpos($type, 'bool') !== false || strpos($type, 'bit') !== false || strpos($type, 'float') !== false || strpos($type, 'double') !== false || strpos($type, 'decimal') !== false;
Chris@76 373
Chris@76 374 $schema_create .= ' default ' . ($isNumericColumn ? $row['Default'] : '\'' . $smcFunc['db_escape_string']($row['Default']) . '\'');
Chris@76 375 }
Chris@76 376 }
Chris@76 377
Chris@76 378 // And now any extra information. (such as auto_increment.)
Chris@76 379 $schema_create .= ($row['Extra'] != '' ? ' ' . $row['Extra'] : '') . ',' . $crlf;
Chris@76 380 }
Chris@76 381 $smcFunc['db_free_result']($result);
Chris@76 382
Chris@76 383 // Take off the last comma.
Chris@76 384 $schema_create = substr($schema_create, 0, -strlen($crlf) - 1);
Chris@76 385
Chris@76 386 // Find the keys.
Chris@76 387 $result = $smcFunc['db_query']('', '
Chris@76 388 SHOW KEYS
Chris@76 389 FROM `{raw:table}`',
Chris@76 390 array(
Chris@76 391 'table' => $tableName,
Chris@76 392 )
Chris@76 393 );
Chris@76 394 $indexes = array();
Chris@76 395 while ($row = $smcFunc['db_fetch_assoc']($result))
Chris@76 396 {
Chris@76 397 // IS this a primary key, unique index, or regular index?
Chris@76 398 $row['Key_name'] = $row['Key_name'] == 'PRIMARY' ? 'PRIMARY KEY' : (empty($row['Non_unique']) ? 'UNIQUE ' : ($row['Comment'] == 'FULLTEXT' || (isset($row['Index_type']) && $row['Index_type'] == 'FULLTEXT') ? 'FULLTEXT ' : 'KEY ')) . '`' . $row['Key_name'] . '`';
Chris@76 399
Chris@76 400 // Is this the first column in the index?
Chris@76 401 if (empty($indexes[$row['Key_name']]))
Chris@76 402 $indexes[$row['Key_name']] = array();
Chris@76 403
Chris@76 404 // A sub part, like only indexing 15 characters of a varchar.
Chris@76 405 if (!empty($row['Sub_part']))
Chris@76 406 $indexes[$row['Key_name']][$row['Seq_in_index']] = '`' . $row['Column_name'] . '`(' . $row['Sub_part'] . ')';
Chris@76 407 else
Chris@76 408 $indexes[$row['Key_name']][$row['Seq_in_index']] = '`' . $row['Column_name'] . '`';
Chris@76 409 }
Chris@76 410 $smcFunc['db_free_result']($result);
Chris@76 411
Chris@76 412 // Build the CREATEs for the keys.
Chris@76 413 foreach ($indexes as $keyname => $columns)
Chris@76 414 {
Chris@76 415 // Ensure the columns are in proper order.
Chris@76 416 ksort($columns);
Chris@76 417
Chris@76 418 $schema_create .= ',' . $crlf . ' ' . $keyname . ' (' . implode($columns, ', ') . ')';
Chris@76 419 }
Chris@76 420
Chris@76 421 // Now just get the comment and type... (MyISAM, etc.)
Chris@76 422 $result = $smcFunc['db_query']('', '
Chris@76 423 SHOW TABLE STATUS
Chris@76 424 LIKE {string:table}',
Chris@76 425 array(
Chris@76 426 'table' => strtr($tableName, array('_' => '\\_', '%' => '\\%')),
Chris@76 427 )
Chris@76 428 );
Chris@76 429 $row = $smcFunc['db_fetch_assoc']($result);
Chris@76 430 $smcFunc['db_free_result']($result);
Chris@76 431
Chris@76 432 // Probably MyISAM.... and it might have a comment.
Chris@76 433 $schema_create .= $crlf . ') ENGINE=' . (isset($row['Type']) ? $row['Type'] : $row['Engine']) . ($row['Comment'] != '' ? ' COMMENT="' . $row['Comment'] . '"' : '');
Chris@76 434
Chris@76 435 return $schema_create;
Chris@76 436 }
Chris@76 437
Chris@76 438 // Get the version number.
Chris@76 439 function smf_db_get_version()
Chris@76 440 {
Chris@76 441 global $smcFunc;
Chris@76 442
Chris@76 443 $request = $smcFunc['db_query']('', '
Chris@76 444 SELECT VERSION()',
Chris@76 445 array(
Chris@76 446 )
Chris@76 447 );
Chris@76 448 list ($ver) = $smcFunc['db_fetch_row']($request);
Chris@76 449 $smcFunc['db_free_result']($request);
Chris@76 450
Chris@76 451 return $ver;
Chris@76 452 }
Chris@76 453
Chris@76 454 ?>