Mercurial > hg > vamp-website

annotate forum/Sources/DbExtra-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 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 ?>