PageRenderTime 48ms CodeModel.GetById 18ms RepoModel.GetById 0ms app.codeStats 0ms

/sources/database/DbExtra-mysql.php

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