DbExtra-mysql.php 12 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465
  1. <?php
  2. /**
  3. * This file contains rarely used extended database functionality.
  4. *
  5. * Simple Machines Forum (SMF)
  6. *
  7. * @package SMF
  8. * @author Simple Machines http://www.simplemachines.org
  9. * @copyright 2011 Simple Machines
  10. * @license http://www.simplemachines.org/about/smf/license.php BSD
  11. *
  12. * @version 2.1 Alpha 1
  13. */
  14. if (!defined('SMF'))
  15. die('Hacking attempt...');
  16. /**
  17. * Add the functions implemented in this file to the $smcFunc array.
  18. */
  19. function db_extra_init()
  20. {
  21. global $smcFunc;
  22. if (!isset($smcFunc['db_backup_table']) || $smcFunc['db_backup_table'] != 'smf_db_backup_table')
  23. $smcFunc += array(
  24. 'db_backup_table' => 'smf_db_backup_table',
  25. 'db_optimize_table' => 'smf_db_optimize_table',
  26. 'db_insert_sql' => 'smf_db_insert_sql',
  27. 'db_table_sql' => 'smf_db_table_sql',
  28. 'db_list_tables' => 'smf_db_list_tables',
  29. 'db_get_version' => 'smf_db_get_version',
  30. );
  31. }
  32. /**
  33. * Backup $table to $backup_table.
  34. * @param string $table
  35. * @param string $backup_table
  36. * @return resource -the request handle to the table creation query
  37. */
  38. function smf_db_backup_table($table, $backup_table)
  39. {
  40. global $smcFunc, $db_prefix;
  41. $table = str_replace('{db_prefix}', $db_prefix, $table);
  42. // First, get rid of the old table.
  43. $smcFunc['db_query']('', '
  44. DROP TABLE IF EXISTS {raw:backup_table}',
  45. array(
  46. 'backup_table' => $backup_table,
  47. )
  48. );
  49. // Can we do this the quick way?
  50. $result = $smcFunc['db_query']('', '
  51. CREATE TABLE {raw:backup_table} LIKE {raw:table}',
  52. array(
  53. 'backup_table' => $backup_table,
  54. 'table' => $table
  55. ));
  56. // If this failed, we go old school.
  57. if ($result)
  58. {
  59. $request = $smcFunc['db_query']('', '
  60. INSERT INTO {raw:backup_table}
  61. SELECT *
  62. FROM {raw:table}',
  63. array(
  64. 'backup_table' => $backup_table,
  65. 'table' => $table
  66. ));
  67. // Old school or no school?
  68. if ($request)
  69. return $request;
  70. }
  71. // At this point, the quick method failed.
  72. $result = $smcFunc['db_query']('', '
  73. SHOW CREATE TABLE {raw:table}',
  74. array(
  75. 'table' => $table,
  76. )
  77. );
  78. list (, $create) = $smcFunc['db_fetch_row']($result);
  79. $smcFunc['db_free_result']($result);
  80. $create = preg_split('/[\n\r]/', $create);
  81. $auto_inc = '';
  82. // Default engine type.
  83. $engine = 'MyISAM';
  84. $charset = '';
  85. $collate = '';
  86. foreach ($create as $k => $l)
  87. {
  88. // Get the name of the auto_increment column.
  89. if (strpos($l, 'auto_increment'))
  90. $auto_inc = trim($l);
  91. // For the engine type, see if we can work out what it is.
  92. if (strpos($l, 'ENGINE') !== false || strpos($l, 'TYPE') !== false)
  93. {
  94. // Extract the engine type.
  95. preg_match('~(ENGINE|TYPE)=(\w+)(\sDEFAULT)?(\sCHARSET=(\w+))?(\sCOLLATE=(\w+))?~', $l, $match);
  96. if (!empty($match[1]))
  97. $engine = $match[1];
  98. if (!empty($match[2]))
  99. $engine = $match[2];
  100. if (!empty($match[5]))
  101. $charset = $match[5];
  102. if (!empty($match[7]))
  103. $collate = $match[7];
  104. }
  105. // Skip everything but keys...
  106. if (strpos($l, 'KEY') === false)
  107. unset($create[$k]);
  108. }
  109. if (!empty($create))
  110. $create = '(
  111. ' . implode('
  112. ', $create) . ')';
  113. else
  114. $create = '';
  115. $request = $smcFunc['db_query']('', '
  116. CREATE TABLE {raw:backup_table} {raw:create}
  117. ENGINE={raw:engine}' . (empty($charset) ? '' : ' CHARACTER SET {raw:charset}' . (empty($collate) ? '' : ' COLLATE {raw:collate}')) . '
  118. SELECT *
  119. FROM {raw:table}',
  120. array(
  121. 'backup_table' => $backup_table,
  122. 'table' => $table,
  123. 'create' => $create,
  124. 'engine' => $engine,
  125. 'charset' => empty($charset) ? '' : $charset,
  126. 'collate' => empty($collate) ? '' : $collate,
  127. )
  128. );
  129. if ($auto_inc != '')
  130. {
  131. if (preg_match('~\`(.+?)\`\s~', $auto_inc, $match) != 0 && substr($auto_inc, -1, 1) == ',')
  132. $auto_inc = substr($auto_inc, 0, -1);
  133. $smcFunc['db_query']('', '
  134. ALTER TABLE {raw:backup_table}
  135. CHANGE COLUMN {raw:column_detail} {raw:auto_inc}',
  136. array(
  137. 'backup_table' => $backup_table,
  138. 'column_detail' => $match[1],
  139. 'auto_inc' => $auto_inc,
  140. )
  141. );
  142. }
  143. return $request;
  144. }
  145. /**
  146. * This function optimizes a table.
  147. * @param string $table - the table to be optimized
  148. * @return how much it was gained
  149. */
  150. function smf_db_optimize_table($table)
  151. {
  152. global $smcFunc, $db_name, $db_prefix;
  153. $table = str_replace('{db_prefix}', $db_prefix, $table);
  154. // Get how much overhead there is.
  155. $request = $smcFunc['db_query']('', '
  156. SHOW TABLE STATUS LIKE {string:table_name}',
  157. array(
  158. 'table_name' => str_replace('_', '\_', $table),
  159. )
  160. );
  161. $row = $smcFunc['db_fetch_assoc']($request);
  162. $smcFunc['db_free_result']($request);
  163. $data_before = isset($row['Data_free']) ? $row['Data_free'] : 0;
  164. $request = $smcFunc['db_query']('', '
  165. OPTIMIZE TABLE `{raw:table}`',
  166. array(
  167. 'table' => $table,
  168. )
  169. );
  170. if (!$request)
  171. return -1;
  172. // How much left?
  173. $request = $smcFunc['db_query']('', '
  174. SHOW TABLE STATUS LIKE {string:table}',
  175. array(
  176. 'table' => str_replace('_', '\_', $table),
  177. )
  178. );
  179. $row = $smcFunc['db_fetch_assoc']($request);
  180. $smcFunc['db_free_result']($request);
  181. $total_change = isset($row['Data_free']) && $data_before > $row['Data_free'] ? $data_before / 1024 : 0;
  182. return $total_change;
  183. }
  184. /**
  185. * This function lists all tables in the database.
  186. * The listing could be filtered according to $filter.
  187. * @param mixed $db, string holding the table name, or false, default false
  188. * @param mixed $filter, string to filter by, or false, default false
  189. * @return array, an array of table names. (strings)
  190. */
  191. function smf_db_list_tables($db = false, $filter = false)
  192. {
  193. global $db_name, $smcFunc;
  194. $db = $db == false ? $db_name : $db;
  195. $db = trim($db);
  196. $filter = $filter == false ? '' : ' LIKE \'' . $filter . '\'';
  197. $request = $smcFunc['db_query']('', '
  198. SHOW TABLES
  199. FROM `{raw:db}`
  200. {raw:filter}',
  201. array(
  202. 'db' => $db[0] == '`' ? strtr($db, array('`' => '')) : $db,
  203. 'filter' => $filter,
  204. )
  205. );
  206. $tables = array();
  207. while ($row = $smcFunc['db_fetch_row']($request))
  208. $tables[] = $row[0];
  209. $smcFunc['db_free_result']($request);
  210. return $tables;
  211. }
  212. /**
  213. * Gets all the necessary INSERTs for the table named table_name.
  214. * It goes in 250 row segments.
  215. * @param string $tableName - the table to create the inserts for.
  216. * @return string, the query to insert the data back in, or an empty
  217. * string if the table was empty.
  218. */
  219. function smf_db_insert_sql($tableName, $new_table = false)
  220. {
  221. global $smcFunc, $db_prefix, $detected_id;
  222. static $start = 0, $num_rows, $fields, $limit, $last_id;
  223. if ($new_table)
  224. {
  225. $limit = strstr($tableName, 'log_') !== false ? 500 : 250;
  226. $start = 0;
  227. $last_id = 0;
  228. }
  229. $data = '';
  230. $tableName = str_replace('{db_prefix}', $db_prefix, $tableName);
  231. // This will be handy...
  232. $crlf = "\r\n";
  233. // This is done this way because retrieve data only with LIMIT will become slower after each query
  234. // and for long tables (e.g. {db_prefix}messages) it could be a pain...
  235. // Instead using WHERE speeds up thing *a lot* (especially after the first 50'000 records)
  236. $result = $smcFunc['db_query']('', '
  237. SELECT /*!40001 SQL_NO_CACHE */ *
  238. FROM `' . $tableName . '`' .
  239. (!empty($last_id) && !empty($detected_id) ? '
  240. WHERE ' . $detected_id . ' > ' . $last_id : '') . '
  241. LIMIT ' . (empty($last_id) ? $start . ', ' : '') . $limit,
  242. array(
  243. 'security_override' => true,
  244. )
  245. );
  246. // The number of rows, just for record keeping and breaking INSERTs up.
  247. $num_rows = $smcFunc['db_num_rows']($result);
  248. if ($num_rows == 0)
  249. return '';
  250. if ($new_table)
  251. {
  252. $fields = array_keys($smcFunc['db_fetch_assoc']($result));
  253. $smcFunc['db_data_seek']($result, 0);
  254. }
  255. // Start it off with the basic INSERT INTO.
  256. $data = 'INSERT INTO `' . $tableName . '`' . $crlf . "\t" . '(`' . implode('`, `', $fields) . '`)' . $crlf . 'VALUES ';
  257. // Loop through each row.
  258. while ($row = $smcFunc['db_fetch_assoc']($result))
  259. {
  260. // Get the fields in this row...
  261. $field_list = array();
  262. foreach ($row as $key => $item)
  263. {
  264. // Try to figure out the type of each field. (NULL, number, or 'string'.)
  265. if (!isset($item))
  266. $field_list[] = 'NULL';
  267. elseif (is_numeric($item) && (int) $item == $item)
  268. $field_list[] = $item;
  269. else
  270. $field_list[] = '\'' . $smcFunc['db_escape_string']($item) . '\'';
  271. }
  272. if (!empty($detected_id) && isset($row[$detected_id]))
  273. $last_id = $row[$detected_id];
  274. $data .= '(' . implode(', ', $field_list) . ')' . ',' . $crlf . "\t";
  275. }
  276. $smcFunc['db_free_result']($result);
  277. $data .= ';' . $crlf;
  278. $start += $limit;
  279. return $data;
  280. }
  281. /**
  282. * Dumps the schema (CREATE) for a table.
  283. * @todo why is this needed for?
  284. * @param string $tableName - the table
  285. * @return string - the CREATE statement as string
  286. */
  287. function smf_db_table_sql($tableName)
  288. {
  289. global $smcFunc, $db_prefix, $detected_id;
  290. $tableName = str_replace('{db_prefix}', $db_prefix, $tableName);
  291. $detected_id = '';
  292. // This will be needed...
  293. $crlf = "\r\n";
  294. // Drop it if it exists.
  295. $schema_create = 'DROP TABLE IF EXISTS `' . $tableName . '`;' . $crlf . $crlf;
  296. // Start the create table...
  297. $schema_create .= 'CREATE TABLE `' . $tableName . '` (' . $crlf;
  298. // Find all the fields.
  299. $result = $smcFunc['db_query']('', '
  300. SHOW FIELDS
  301. FROM `{raw:table}`',
  302. array(
  303. 'table' => $tableName,
  304. )
  305. );
  306. while ($row = $smcFunc['db_fetch_assoc']($result))
  307. {
  308. // Make the CREATE for this column.
  309. $schema_create .= ' `' . $row['Field'] . '` ' . $row['Type'] . ($row['Null'] != 'YES' ? ' NOT NULL' : '');
  310. // Add a default...?
  311. if (!empty($row['Default']) || $row['Null'] !== 'YES')
  312. {
  313. // Make a special case of auto-timestamp.
  314. if ($row['Default'] == 'CURRENT_TIMESTAMP')
  315. $schema_create .= ' /*!40102 NOT NULL default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP */';
  316. // Text shouldn't have a default.
  317. elseif ($row['Default'] !== null)
  318. {
  319. // If this field is numeric the default needs no escaping.
  320. $type = strtolower($row['Type']);
  321. $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;
  322. $schema_create .= ' default ' . ($isNumericColumn ? $row['Default'] : '\'' . $smcFunc['db_escape_string']($row['Default']) . '\'');
  323. }
  324. }
  325. // And now any extra information. (such as auto_increment.)
  326. $schema_create .= ($row['Extra'] != '' ? ' ' . $row['Extra'] : '') . ',' . $crlf;
  327. if ($row['Extra'] == 'auto_increment')
  328. $detected_id = $row['Field'];
  329. }
  330. $smcFunc['db_free_result']($result);
  331. // Take off the last comma.
  332. $schema_create = substr($schema_create, 0, -strlen($crlf) - 1);
  333. // Find the keys.
  334. $result = $smcFunc['db_query']('', '
  335. SHOW KEYS
  336. FROM `{raw:table}`',
  337. array(
  338. 'table' => $tableName,
  339. )
  340. );
  341. $indexes = array();
  342. while ($row = $smcFunc['db_fetch_assoc']($result))
  343. {
  344. // IS this a primary key, unique index, or regular index?
  345. $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'] . '`';
  346. // Is this the first column in the index?
  347. if (empty($indexes[$row['Key_name']]))
  348. $indexes[$row['Key_name']] = array();
  349. // A sub part, like only indexing 15 characters of a varchar.
  350. if (!empty($row['Sub_part']))
  351. $indexes[$row['Key_name']][$row['Seq_in_index']] = '`' . $row['Column_name'] . '`(' . $row['Sub_part'] . ')';
  352. else
  353. $indexes[$row['Key_name']][$row['Seq_in_index']] = '`' . $row['Column_name'] . '`';
  354. }
  355. $smcFunc['db_free_result']($result);
  356. // Build the CREATEs for the keys.
  357. foreach ($indexes as $keyname => $columns)
  358. {
  359. // Ensure the columns are in proper order.
  360. ksort($columns);
  361. $schema_create .= ',' . $crlf . ' ' . $keyname . ' (' . implode($columns, ', ') . ')';
  362. }
  363. // Now just get the comment and type... (MyISAM, etc.)
  364. $result = $smcFunc['db_query']('', '
  365. SHOW TABLE STATUS
  366. LIKE {string:table}',
  367. array(
  368. 'table' => strtr($tableName, array('_' => '\\_', '%' => '\\%')),
  369. )
  370. );
  371. $row = $smcFunc['db_fetch_assoc']($result);
  372. $smcFunc['db_free_result']($result);
  373. // Probably MyISAM.... and it might have a comment.
  374. $schema_create .= $crlf . ') ENGINE=' . (isset($row['Type']) ? $row['Type'] : $row['Engine']) . ($row['Comment'] != '' ? ' COMMENT="' . $row['Comment'] . '"' : '');
  375. return $schema_create;
  376. }
  377. /**
  378. * Get the version number.
  379. * @return string - the version
  380. */
  381. function smf_db_get_version()
  382. {
  383. global $smcFunc;
  384. $request = $smcFunc['db_query']('', '
  385. SELECT VERSION()',
  386. array(
  387. )
  388. );
  389. list ($ver) = $smcFunc['db_fetch_row']($request);
  390. $smcFunc['db_free_result']($request);
  391. return $ver;
  392. }
  393. ?>