DbExtra-mysql.php 12 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456
  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 2012 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('No direct access...');
  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_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. *
  188. * @param mixed $db string holding the table name, or false, default false
  189. * @param mixed $filter string to filter by, or false, default false
  190. * @return array, an array of table names. (strings)
  191. */
  192. function smf_db_list_tables($db = false, $filter = false)
  193. {
  194. global $db_name, $smcFunc;
  195. $db = $db == false ? $db_name : $db;
  196. $db = trim($db);
  197. $filter = $filter == false ? '' : ' LIKE \'' . $filter . '\'';
  198. $request = $smcFunc['db_query']('', '
  199. SHOW TABLES
  200. FROM `{raw:db}`
  201. {raw:filter}',
  202. array(
  203. 'db' => $db[0] == '`' ? strtr($db, array('`' => '')) : $db,
  204. 'filter' => $filter,
  205. )
  206. );
  207. $tables = array();
  208. while ($row = $smcFunc['db_fetch_row']($request))
  209. $tables[] = $row[0];
  210. $smcFunc['db_free_result']($request);
  211. return $tables;
  212. }
  213. /**
  214. * Gets all the necessary INSERTs for the table named table_name.
  215. * It goes in 250 row segments.
  216. *
  217. * @param string $tableName - the table to create the inserts for.
  218. * @param bool $new_table
  219. * @return string the query to insert the data back in, or an empty string if the table was empty.
  220. */
  221. function smf_db_insert_sql($tableName, $new_table = false)
  222. {
  223. global $smcFunc, $db_prefix;
  224. static $start = 0, $num_rows, $fields, $limit;
  225. if ($new_table)
  226. {
  227. $limit = strstr($tableName, 'log_') !== false ? 500 : 250;
  228. $start = 0;
  229. }
  230. $data = '';
  231. $tableName = str_replace('{db_prefix}', $db_prefix, $tableName);
  232. // This will be handy...
  233. $crlf = "\r\n";
  234. $result = $smcFunc['db_query']('', '
  235. SELECT /*!40001 SQL_NO_CACHE */ *
  236. FROM `' . $tableName . '`
  237. LIMIT ' . $start . ', ' . $limit,
  238. array(
  239. 'security_override' => true,
  240. )
  241. );
  242. // The number of rows, just for record keeping and breaking INSERTs up.
  243. $num_rows = $smcFunc['db_num_rows']($result);
  244. if ($num_rows == 0)
  245. return '';
  246. if ($new_table)
  247. {
  248. $fields = array_keys($smcFunc['db_fetch_assoc']($result));
  249. $smcFunc['db_data_seek']($result, 0);
  250. }
  251. // Start it off with the basic INSERT INTO.
  252. $data = 'INSERT INTO `' . $tableName . '`' . $crlf . "\t" . '(`' . implode('`, `', $fields) . '`)' . $crlf . 'VALUES ';
  253. // Loop through each row.
  254. while ($row = $smcFunc['db_fetch_assoc']($result))
  255. {
  256. // Get the fields in this row...
  257. $field_list = array();
  258. foreach ($row as $key => $item)
  259. {
  260. // Try to figure out the type of each field. (NULL, number, or 'string'.)
  261. if (!isset($item))
  262. $field_list[] = 'NULL';
  263. elseif (is_numeric($item) && (int) $item == $item)
  264. $field_list[] = $item;
  265. else
  266. $field_list[] = '\'' . $smcFunc['db_escape_string']($item) . '\'';
  267. }
  268. $data .= '(' . implode(', ', $field_list) . ')' . ',' . $crlf . "\t";
  269. }
  270. $smcFunc['db_free_result']($result);
  271. $data = substr(trim($data), 0, -1) . ';' . $crlf . $crlf;
  272. $start += $limit;
  273. return $data;
  274. }
  275. /**
  276. * Dumps the schema (CREATE) for a table.
  277. * @todo why is this needed for?
  278. * @param string $tableName - the table
  279. * @return string - the CREATE statement as string
  280. */
  281. function smf_db_table_sql($tableName)
  282. {
  283. global $smcFunc, $db_prefix;
  284. $tableName = str_replace('{db_prefix}', $db_prefix, $tableName);
  285. // This will be needed...
  286. $crlf = "\r\n";
  287. // Drop it if it exists.
  288. $schema_create = 'DROP TABLE IF EXISTS `' . $tableName . '`;' . $crlf . $crlf;
  289. // Start the create table...
  290. $schema_create .= 'CREATE TABLE `' . $tableName . '` (' . $crlf;
  291. // Find all the fields.
  292. $result = $smcFunc['db_query']('', '
  293. SHOW FIELDS
  294. FROM `{raw:table}`',
  295. array(
  296. 'table' => $tableName,
  297. )
  298. );
  299. while ($row = $smcFunc['db_fetch_assoc']($result))
  300. {
  301. // Make the CREATE for this column.
  302. $schema_create .= ' `' . $row['Field'] . '` ' . $row['Type'] . ($row['Null'] != 'YES' ? ' NOT NULL' : '');
  303. // Add a default...?
  304. if (!empty($row['Default']) || $row['Null'] !== 'YES')
  305. {
  306. // Make a special case of auto-timestamp.
  307. if ($row['Default'] == 'CURRENT_TIMESTAMP')
  308. $schema_create .= ' /*!40102 NOT NULL default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP */';
  309. // Text shouldn't have a default.
  310. elseif ($row['Default'] !== null)
  311. {
  312. // If this field is numeric the default needs no escaping.
  313. $type = strtolower($row['Type']);
  314. $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;
  315. $schema_create .= ' default ' . ($isNumericColumn ? $row['Default'] : '\'' . $smcFunc['db_escape_string']($row['Default']) . '\'');
  316. }
  317. }
  318. // And now any extra information. (such as auto_increment.)
  319. $schema_create .= ($row['Extra'] != '' ? ' ' . $row['Extra'] : '') . ',' . $crlf;
  320. }
  321. $smcFunc['db_free_result']($result);
  322. // Take off the last comma.
  323. $schema_create = substr($schema_create, 0, -strlen($crlf) - 1);
  324. // Find the keys.
  325. $result = $smcFunc['db_query']('', '
  326. SHOW KEYS
  327. FROM `{raw:table}`',
  328. array(
  329. 'table' => $tableName,
  330. )
  331. );
  332. $indexes = array();
  333. while ($row = $smcFunc['db_fetch_assoc']($result))
  334. {
  335. // IS this a primary key, unique index, or regular index?
  336. $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'] . '`';
  337. // Is this the first column in the index?
  338. if (empty($indexes[$row['Key_name']]))
  339. $indexes[$row['Key_name']] = array();
  340. // A sub part, like only indexing 15 characters of a varchar.
  341. if (!empty($row['Sub_part']))
  342. $indexes[$row['Key_name']][$row['Seq_in_index']] = '`' . $row['Column_name'] . '`(' . $row['Sub_part'] . ')';
  343. else
  344. $indexes[$row['Key_name']][$row['Seq_in_index']] = '`' . $row['Column_name'] . '`';
  345. }
  346. $smcFunc['db_free_result']($result);
  347. // Build the CREATEs for the keys.
  348. foreach ($indexes as $keyname => $columns)
  349. {
  350. // Ensure the columns are in proper order.
  351. ksort($columns);
  352. $schema_create .= ',' . $crlf . ' ' . $keyname . ' (' . implode($columns, ', ') . ')';
  353. }
  354. // Now just get the comment and type... (MyISAM, etc.)
  355. $result = $smcFunc['db_query']('', '
  356. SHOW TABLE STATUS
  357. LIKE {string:table}',
  358. array(
  359. 'table' => strtr($tableName, array('_' => '\\_', '%' => '\\%')),
  360. )
  361. );
  362. $row = $smcFunc['db_fetch_assoc']($result);
  363. $smcFunc['db_free_result']($result);
  364. // Probably MyISAM.... and it might have a comment.
  365. $schema_create .= $crlf . ') ENGINE=' . (isset($row['Type']) ? $row['Type'] : $row['Engine']) . ($row['Comment'] != '' ? ' COMMENT="' . $row['Comment'] . '"' : '');
  366. return $schema_create;
  367. }
  368. /**
  369. * Get the version number.
  370. * @return string - the version
  371. */
  372. function smf_db_get_version()
  373. {
  374. global $smcFunc;
  375. $request = $smcFunc['db_query']('', '
  376. SELECT VERSION()',
  377. array(
  378. )
  379. );
  380. list ($ver) = $smcFunc['db_fetch_row']($request);
  381. $smcFunc['db_free_result']($request);
  382. return $ver;
  383. }
  384. ?>