DbPackages-mysql.php 22 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674
  1. <?php
  2. /**
  3. * This file contains database functionality specifically designed for packages (mods) to utilize.
  4. *
  5. * Simple Machines Forum (SMF)
  6. *
  7. * @package SMF
  8. * @author Simple Machines http://www.simplemachines.org
  9. * @copyright 2014 Simple Machines and individual contributors
  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 file functions to the $smcFunc array.
  18. */
  19. function db_packages_init()
  20. {
  21. global $smcFunc, $reservedTables, $db_package_log, $db_prefix;
  22. if (!isset($smcFunc['db_create_table']) || $smcFunc['db_create_table'] != 'smf_db_create_table')
  23. {
  24. $smcFunc += array(
  25. 'db_add_column' => 'smf_db_add_column',
  26. 'db_add_index' => 'smf_db_add_index',
  27. 'db_calculate_type' => 'smf_db_calculate_type',
  28. 'db_change_column' => 'smf_db_change_column',
  29. 'db_create_table' => 'smf_db_create_table',
  30. 'db_drop_table' => 'smf_db_drop_table',
  31. 'db_table_structure' => 'smf_db_table_structure',
  32. 'db_list_columns' => 'smf_db_list_columns',
  33. 'db_list_indexes' => 'smf_db_list_indexes',
  34. 'db_remove_column' => 'smf_db_remove_column',
  35. 'db_remove_index' => 'smf_db_remove_index',
  36. );
  37. $db_package_log = array();
  38. }
  39. // We setup an array of SMF tables we can't do auto-remove on - in case a mod writer cocks it up!
  40. $reservedTables = array('admin_info_files', 'approval_queue', 'attachments', 'ban_groups', 'ban_items',
  41. 'board_permissions', 'boards', 'calendar', 'calendar_holidays', 'categories', 'collapsed_categories',
  42. 'custom_fields', 'group_moderators', 'log_actions', 'log_activity', 'log_banned', 'log_boards',
  43. 'log_digest', 'log_errors', 'log_floodcontrol', 'log_group_requests', 'log_karma', 'log_mark_read',
  44. 'log_notify', 'log_online', 'log_packages', 'log_polls', 'log_reported', 'log_reported_comments',
  45. 'log_scheduled_tasks', 'log_search_messages', 'log_search_results', 'log_search_subjects',
  46. 'log_search_topics', 'log_topics', 'mail_queue', 'membergroups', 'members', 'message_icons',
  47. 'messages', 'moderators', 'package_servers', 'permission_profiles', 'permissions', 'personal_messages',
  48. 'pm_recipients', 'poll_choices', 'polls', 'scheduled_tasks', 'sessions', 'settings', 'smileys',
  49. 'themes', 'topics');
  50. foreach ($reservedTables as $k => $table_name)
  51. $reservedTables[$k] = strtolower($db_prefix . $table_name);
  52. // We in turn may need the extra stuff.
  53. db_extend('extra');
  54. }
  55. /**
  56. * This function can be used to create a table without worrying about schema
  57. * compatabilities across supported database systems.
  58. * - If the table exists will, by default, do nothing.
  59. * - Builds table with columns as passed to it - at least one column must be sent.
  60. * The columns array should have one sub-array for each column - these sub arrays contain:
  61. * 'name' = Column name
  62. * 'type' = Type of column - values from (smallint, mediumint, int, text, varchar, char, tinytext, mediumtext, largetext)
  63. * 'size' => Size of column (If applicable) - for example 255 for a large varchar, 10 for an int etc.
  64. * If not set SMF will pick a size.
  65. * - 'default' = Default value - do not set if no default required.
  66. * - 'null' => Can it be null (true or false) - if not set default will be false.
  67. * - 'auto' => Set to true to make it an auto incrementing column. Set to a numerical value to set from what
  68. * it should begin counting.
  69. * - Adds indexes as specified within indexes parameter. Each index should be a member of $indexes. Values are:
  70. * - 'name' => Index name (If left empty SMF will generate).
  71. * - 'type' => Type of index. Choose from 'primary', 'unique' or 'index'. If not set will default to 'index'.
  72. * - 'columns' => Array containing columns that form part of key - in the order the index is to be created.
  73. * - parameters: (None yet)
  74. * - if_exists values:
  75. * - 'ignore' will do nothing if the table exists. (And will return true)
  76. * - 'overwrite' will drop any existing table of the same name.
  77. * - 'error' will return false if the table already exists.
  78. *
  79. * @param string $table_name The name of the table to create
  80. * @param array $columns An array of column info in the specified format
  81. * @param array $indexes An array of index info in the specified format
  82. * @param array $parameters Currently not used
  83. * @param string $if_exists What to do if the table exists.
  84. * @param string $error
  85. * @return boolean Whether or not the operation was successful
  86. */
  87. function smf_db_create_table($table_name, $columns, $indexes = array(), $parameters = array(), $if_exists = 'ignore', $error = 'fatal')
  88. {
  89. global $reservedTables, $smcFunc, $db_package_log, $db_prefix, $db_character_set;
  90. // Strip out the table name, we might not need it in some cases
  91. $real_prefix = preg_match('~^(`?)(.+?)\\1\\.(.*?)$~', $db_prefix, $match) === 1 ? $match[3] : $db_prefix;
  92. // With or without the database name, the fullname looks like this.
  93. $full_table_name = str_replace('{db_prefix}', $real_prefix, $table_name);
  94. $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
  95. // First - no way do we touch SMF tables.
  96. if (in_array(strtolower($table_name), $reservedTables))
  97. return false;
  98. // Log that we'll want to remove this on uninstall.
  99. $db_package_log[] = array('remove_table', $table_name);
  100. // Slightly easier on MySQL than the others...
  101. $tables = $smcFunc['db_list_tables']();
  102. if (in_array($full_table_name, $tables))
  103. {
  104. // This is a sad day... drop the table? If not, return false (error) by default.
  105. if ($if_exists == 'overwrite')
  106. $smcFunc['db_drop_table']($table_name);
  107. else
  108. return $if_exists == 'ignore';
  109. }
  110. // Righty - let's do the damn thing!
  111. $table_query = 'CREATE TABLE ' . $table_name . "\n" . '(';
  112. foreach ($columns as $column)
  113. $table_query .= "\n\t" . smf_db_create_query_column($column) . ',';
  114. // Loop through the indexes next...
  115. foreach ($indexes as $index)
  116. {
  117. $columns = implode(',', $index['columns']);
  118. // Is it the primary?
  119. if (isset($index['type']) && $index['type'] == 'primary')
  120. $table_query .= "\n\t" . 'PRIMARY KEY (' . implode(',', $index['columns']) . '),';
  121. else
  122. {
  123. if (empty($index['name']))
  124. $index['name'] = implode('_', $index['columns']);
  125. $table_query .= "\n\t" . (isset($index['type']) && $index['type'] == 'unique' ? 'UNIQUE' : 'KEY') . ' ' . $index['name'] . ' (' . $columns . '),';
  126. }
  127. }
  128. // No trailing commas!
  129. if (substr($table_query, -1) == ',')
  130. $table_query = substr($table_query, 0, -1);
  131. $table_query .= ') ENGINE=MyISAM';
  132. if (!empty($db_character_set) && $db_character_set == 'utf8')
  133. $table_query .= ' DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci';
  134. // Create the table!
  135. $smcFunc['db_query']('', $table_query,
  136. array(
  137. 'security_override' => true,
  138. )
  139. );
  140. }
  141. /**
  142. * Drop a table.
  143. *
  144. * @param string $table_name The name of the table to drop
  145. * @param array $parameters Not used at the moment
  146. * @param string $error
  147. * @return boolean Whether or not the operation was successful
  148. */
  149. function smf_db_drop_table($table_name, $parameters = array(), $error = 'fatal')
  150. {
  151. global $reservedTables, $smcFunc, $db_prefix;
  152. // After stripping away the database name, this is what's left.
  153. $real_prefix = preg_match('~^(`?)(.+?)\\1\\.(.*?)$~', $db_prefix, $match) === 1 ? $match[3] : $db_prefix;
  154. // Get some aliases.
  155. $full_table_name = str_replace('{db_prefix}', $real_prefix, $table_name);
  156. $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
  157. // God no - dropping one of these = bad.
  158. if (in_array(strtolower($table_name), $reservedTables))
  159. return false;
  160. // Does it exist?
  161. if (in_array($full_table_name, $smcFunc['db_list_tables']()))
  162. {
  163. $query = 'DROP TABLE ' . $table_name;
  164. $smcFunc['db_query']('',
  165. $query,
  166. array(
  167. 'security_override' => true,
  168. )
  169. );
  170. return true;
  171. }
  172. // Otherwise do 'nout.
  173. return false;
  174. }
  175. /**
  176. * This function adds a column.
  177. *
  178. * @param string $table_name The name of the table to add the column to
  179. * @param array $column_info An array of column info ({@see smf_db_create_table})
  180. * @param array $parameters Not used?
  181. * @param string $if_exists What to do if the column exists. If 'update', column is updated.
  182. * @param string $error
  183. * @return boolean Whether or not the operation was successful
  184. */
  185. function smf_db_add_column($table_name, $column_info, $parameters = array(), $if_exists = 'update', $error = 'fatal')
  186. {
  187. global $smcFunc, $db_package_log, $db_prefix;
  188. $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
  189. // Log that we will want to uninstall this!
  190. $db_package_log[] = array('remove_column', $table_name, $column_info['name']);
  191. // Does it exist - if so don't add it again!
  192. $columns = $smcFunc['db_list_columns']($table_name, false);
  193. foreach ($columns as $column)
  194. if ($column == $column_info['name'])
  195. {
  196. // If we're going to overwrite then use change column.
  197. if ($if_exists == 'update')
  198. return $smcFunc['db_change_column']($table_name, $column_info['name'], $column_info);
  199. else
  200. return false;
  201. }
  202. // Get the specifics...
  203. $column_info['size'] = isset($column_info['size']) && is_numeric($column_info['size']) ? $column_info['size'] : null;
  204. list ($type, $size) = $smcFunc['db_calculate_type']($column_info['type'], $column_info['size']);
  205. // Allow unsigned integers (mysql only)
  206. $unsigned = in_array($type, array('int', 'tinyint', 'smallint', 'mediumint', 'bigint')) && !empty($column_info['unsigned']) ? 'unsigned ' : '';
  207. if ($size !== null)
  208. $type = $type . '(' . $size . ')';
  209. // Now add the thing!
  210. $query = '
  211. ALTER TABLE ' . $table_name . '
  212. ADD ' . smf_db_create_query_column($column_info) . (empty($column_info['auto']) ? '' : ' primary key');
  213. $smcFunc['db_query']('', $query,
  214. array(
  215. 'security_override' => true,
  216. )
  217. );
  218. return true;
  219. }
  220. /**
  221. * Removes a column.
  222. *
  223. * @param string $table_name The name of the table to drop the column from
  224. * @param string $column_name The name of the column to drop
  225. * @param array $parameters Not used?
  226. * @param string $error
  227. * @return boolean Whether or not the operation was successful
  228. */
  229. function smf_db_remove_column($table_name, $column_name, $parameters = array(), $error = 'fatal')
  230. {
  231. global $smcFunc, $db_prefix;
  232. $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
  233. // Does it exist?
  234. $columns = $smcFunc['db_list_columns']($table_name, true);
  235. foreach ($columns as $column)
  236. if ($column['name'] == $column_name)
  237. {
  238. $smcFunc['db_query']('', '
  239. ALTER TABLE ' . $table_name . '
  240. DROP COLUMN ' . $column_name,
  241. array(
  242. 'security_override' => true,
  243. )
  244. );
  245. return true;
  246. }
  247. // If here we didn't have to work - joy!
  248. return false;
  249. }
  250. /**
  251. * Change a column.
  252. *
  253. * @param string $table_name The name of the table this column is in
  254. * @param string $old_column The name of the column we want to change
  255. * @param array $column_info An array of info about the "new" column definition (see {@link smf_db_create_table()})
  256. * @param array $parameters Not used?
  257. * @param string $error
  258. */
  259. function smf_db_change_column($table_name, $old_column, $column_info, $parameters = array(), $error = 'fatal')
  260. {
  261. global $smcFunc, $db_prefix;
  262. $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
  263. // Check it does exist!
  264. $columns = $smcFunc['db_list_columns']($table_name, true);
  265. $old_info = null;
  266. foreach ($columns as $column)
  267. if ($column['name'] == $old_column)
  268. $old_info = $column;
  269. // Nothing?
  270. if ($old_info == null)
  271. return false;
  272. // Get the right bits.
  273. if (!isset($column_info['name']))
  274. $column_info['name'] = $old_column;
  275. if (!isset($column_info['default']))
  276. $column_info['default'] = $old_info['default'];
  277. if (!isset($column_info['null']))
  278. $column_info['null'] = $old_info['null'];
  279. if (!isset($column_info['auto']))
  280. $column_info['auto'] = $old_info['auto'];
  281. if (!isset($column_info['type']))
  282. $column_info['type'] = $old_info['type'];
  283. if (!isset($column_info['size']) || !is_numeric($column_info['size']))
  284. $column_info['size'] = $old_info['size'];
  285. if (!isset($column_info['unsigned']) || !in_array($column_info['type'], array('int', 'tinyint', 'smallint', 'mediumint', 'bigint')))
  286. $column_info['unsigned'] = '';
  287. list ($type, $size) = $smcFunc['db_calculate_type']($column_info['type'], $column_info['size']);
  288. // Allow for unsigned integers (mysql only)
  289. $unsigned = in_array($type, array('int', 'tinyint', 'smallint', 'mediumint', 'bigint')) && !empty($column_info['unsigned']) ? 'unsigned ' : '';
  290. if ($size !== null)
  291. $type = $type . '(' . $size . ')';
  292. $smcFunc['db_query']('', '
  293. ALTER TABLE ' . $table_name . '
  294. CHANGE COLUMN `' . $old_column . '` `' . $column_info['name'] . '` ' . $type . ' ' . (!empty($unsigned) ? $unsigned : '') . (empty($column_info['null']) ? 'NOT NULL' : '') . ' ' .
  295. (!isset($column_info['default']) ? '' : 'default \'' . $smcFunc['db_escape_string']($column_info['default']) . '\'') . ' ' .
  296. (empty($column_info['auto']) ? '' : 'auto_increment') . ' ',
  297. array(
  298. 'security_override' => true,
  299. )
  300. );
  301. }
  302. /**
  303. * Add an index.
  304. *
  305. * @param string $table_name The name of the table to add the index to
  306. * @param array $index_info An array of index info (see {@link smf_db_create_table()})
  307. * @param array $parameters Not used?
  308. * @param string $if_exists What to do if the index exists. If 'update', the definition will be updated.
  309. * @param string $error
  310. * @return boolean Whether or not the operation was successful
  311. */
  312. function smf_db_add_index($table_name, $index_info, $parameters = array(), $if_exists = 'update', $error = 'fatal')
  313. {
  314. global $smcFunc, $db_package_log, $db_prefix;
  315. $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
  316. // No columns = no index.
  317. if (empty($index_info['columns']))
  318. return false;
  319. $columns = implode(',', $index_info['columns']);
  320. // No name - make it up!
  321. if (empty($index_info['name']))
  322. {
  323. // No need for primary.
  324. if (isset($index_info['type']) && $index_info['type'] == 'primary')
  325. $index_info['name'] = '';
  326. else
  327. $index_info['name'] = implode('_', $index_info['columns']);
  328. }
  329. else
  330. $index_info['name'] = $index_info['name'];
  331. // Log that we are going to want to remove this!
  332. $db_package_log[] = array('remove_index', $table_name, $index_info['name']);
  333. // Let's get all our indexes.
  334. $indexes = $smcFunc['db_list_indexes']($table_name, true);
  335. // Do we already have it?
  336. foreach ($indexes as $index)
  337. {
  338. if ($index['name'] == $index_info['name'] || ($index['type'] == 'primary' && isset($index_info['type']) && $index_info['type'] == 'primary'))
  339. {
  340. // If we want to overwrite simply remove the current one then continue.
  341. if ($if_exists != 'update' || $index['type'] == 'primary')
  342. return false;
  343. else
  344. $smcFunc['db_remove_index']($table_name, $index_info['name']);
  345. }
  346. }
  347. // If we're here we know we don't have the index - so just add it.
  348. if (!empty($index_info['type']) && $index_info['type'] == 'primary')
  349. {
  350. $smcFunc['db_query']('', '
  351. ALTER TABLE ' . $table_name . '
  352. ADD PRIMARY KEY (' . $columns . ')',
  353. array(
  354. 'security_override' => true,
  355. )
  356. );
  357. }
  358. else
  359. {
  360. $smcFunc['db_query']('', '
  361. ALTER TABLE ' . $table_name . '
  362. ADD ' . (isset($index_info['type']) && $index_info['type'] == 'unique' ? 'UNIQUE' : 'INDEX') . ' ' . $index_info['name'] . ' (' . $columns . ')',
  363. array(
  364. 'security_override' => true,
  365. )
  366. );
  367. }
  368. }
  369. /**
  370. * Remove an index.
  371. *
  372. * @param string $table_name The name of the table to remove the index from
  373. * @param string $index_name The name of the index to remove
  374. * @param array $parameters Not used?
  375. * @param string $error
  376. * @return boolean Whether or not the operation was successful
  377. */
  378. function smf_db_remove_index($table_name, $index_name, $parameters = array(), $error = 'fatal')
  379. {
  380. global $smcFunc, $db_prefix;
  381. $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
  382. // Better exist!
  383. $indexes = $smcFunc['db_list_indexes']($table_name, true);
  384. foreach ($indexes as $index)
  385. {
  386. // If the name is primary we want the primary key!
  387. if ($index['type'] == 'primary' && $index_name == 'primary')
  388. {
  389. // Dropping primary key?
  390. $smcFunc['db_query']('', '
  391. ALTER TABLE ' . $table_name . '
  392. DROP PRIMARY KEY',
  393. array(
  394. 'security_override' => true,
  395. )
  396. );
  397. return true;
  398. }
  399. if ($index['name'] == $index_name)
  400. {
  401. // Drop the bugger...
  402. $smcFunc['db_query']('', '
  403. ALTER TABLE ' . $table_name . '
  404. DROP INDEX ' . $index_name,
  405. array(
  406. 'security_override' => true,
  407. )
  408. );
  409. return true;
  410. }
  411. }
  412. // Not to be found ;(
  413. return false;
  414. }
  415. /**
  416. * Get the schema formatted name for a type.
  417. *
  418. * @param string $type_name The data type (int, varchar, smallint, etc.)
  419. * @param int $type_size The size (8, 255, etc.)
  420. * @param boolean $reverse
  421. * @return An array containing the appropriate type and size for this DB type
  422. */
  423. function smf_db_calculate_type($type_name, $type_size = null, $reverse = false)
  424. {
  425. // MySQL is actually the generic baseline.
  426. return array($type_name, $type_size);
  427. }
  428. /**
  429. * Get table structure.
  430. *
  431. * @param string $table_name The name of the table
  432. * @param array $parameters Not used?
  433. * @return An array of table structure - the name, the column info from {@link smf_db_list_columns()} and the index info from {@link smf_db_list_indexes()}
  434. */
  435. function smf_db_table_structure($table_name, $parameters = array())
  436. {
  437. global $smcFunc, $db_prefix;
  438. $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
  439. return array(
  440. 'name' => $table_name,
  441. 'columns' => $smcFunc['db_list_columns']($table_name, true),
  442. 'indexes' => $smcFunc['db_list_indexes']($table_name, true),
  443. );
  444. }
  445. /**
  446. * Return column information for a table.
  447. *
  448. * @param string $table_name The name of the table to get column info for
  449. * @param bool $detail Whether or not to return detailed info. If true, returns the column info. If false, just returns the column names.
  450. * @param array $parameters Not used?
  451. * @return array An array of column names or detailed column info, depending on $detail
  452. */
  453. function smf_db_list_columns($table_name, $detail = false, $parameters = array())
  454. {
  455. global $smcFunc, $db_prefix;
  456. $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
  457. $result = $smcFunc['db_query']('', '
  458. SHOW FIELDS
  459. FROM {raw:table_name}',
  460. array(
  461. 'table_name' => substr($table_name, 0, 1) == '`' ? $table_name : '`' . $table_name . '`',
  462. )
  463. );
  464. $columns = array();
  465. while ($row = $smcFunc['db_fetch_assoc']($result))
  466. {
  467. if (!$detail)
  468. {
  469. $columns[] = $row['Field'];
  470. }
  471. else
  472. {
  473. // Is there an auto_increment?
  474. $auto = strpos($row['Extra'], 'auto_increment') !== false ? true : false;
  475. // Can we split out the size?
  476. if (preg_match('~(.+?)\s*\((\d+)\)(?:(?:\s*)?(unsigned))?~i', $row['Type'], $matches) === 1)
  477. {
  478. $type = $matches[1];
  479. $size = $matches[2];
  480. if (!empty($matches[3]) && $matches[3] == 'unsigned')
  481. $unsigned = true;
  482. }
  483. else
  484. {
  485. $type = $row['Type'];
  486. $size = null;
  487. }
  488. $columns[$row['Field']] = array(
  489. 'name' => $row['Field'],
  490. 'null' => $row['Null'] != 'YES' ? false : true,
  491. 'default' => isset($row['Default']) ? $row['Default'] : null,
  492. 'type' => $type,
  493. 'size' => $size,
  494. 'auto' => $auto,
  495. );
  496. if (isset($unsigned))
  497. {
  498. $columns[$row['Field']]['unsigned'] = $unsigned;
  499. unset($unsigned);
  500. }
  501. }
  502. }
  503. $smcFunc['db_free_result']($result);
  504. return $columns;
  505. }
  506. /**
  507. * Get index information.
  508. *
  509. * @param string $table_name The name of the table to get indexes for
  510. * @param bool $detail Whether or not to return detailed info.
  511. * @param array $parameters Not used?
  512. * @return array An array of index names or a detailed array of index info, depending on $detail
  513. */
  514. function smf_db_list_indexes($table_name, $detail = false, $parameters = array())
  515. {
  516. global $smcFunc, $db_prefix;
  517. $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
  518. $result = $smcFunc['db_query']('', '
  519. SHOW KEYS
  520. FROM {raw:table_name}',
  521. array(
  522. 'table_name' => substr($table_name, 0, 1) == '`' ? $table_name : '`' . $table_name . '`',
  523. )
  524. );
  525. $indexes = array();
  526. while ($row = $smcFunc['db_fetch_assoc']($result))
  527. {
  528. if (!$detail)
  529. $indexes[] = $row['Key_name'];
  530. else
  531. {
  532. // What is the type?
  533. if ($row['Key_name'] == 'PRIMARY')
  534. $type = 'primary';
  535. elseif (empty($row['Non_unique']))
  536. $type = 'unique';
  537. elseif (isset($row['Index_type']) && $row['Index_type'] == 'FULLTEXT')
  538. $type = 'fulltext';
  539. else
  540. $type = 'index';
  541. // This is the first column we've seen?
  542. if (empty($indexes[$row['Key_name']]))
  543. {
  544. $indexes[$row['Key_name']] = array(
  545. 'name' => $row['Key_name'],
  546. 'type' => $type,
  547. 'columns' => array(),
  548. );
  549. }
  550. // Is it a partial index?
  551. if (!empty($row['Sub_part']))
  552. $indexes[$row['Key_name']]['columns'][] = $row['Column_name'] . '(' . $row['Sub_part'] . ')';
  553. else
  554. $indexes[$row['Key_name']]['columns'][] = $row['Column_name'];
  555. }
  556. }
  557. $smcFunc['db_free_result']($result);
  558. return $indexes;
  559. }
  560. /**
  561. * Creates a query for a column
  562. *
  563. * @param array $column An array of column info
  564. * @return string The column definition
  565. */
  566. function smf_db_create_query_column($column)
  567. {
  568. global $smcFunc;
  569. // Auto increment is easy here!
  570. if (!empty($column['auto']))
  571. {
  572. $default = 'auto_increment';
  573. }
  574. elseif (isset($column['default']) && $column['default'] !== null)
  575. $default = 'default \'' . $smcFunc['db_escape_string']($column['default']) . '\'';
  576. else
  577. $default = '';
  578. // Sort out the size... and stuff...
  579. $column['size'] = isset($column['size']) && is_numeric($column['size']) ? $column['size'] : null;
  580. list ($type, $size) = $smcFunc['db_calculate_type']($column['type'], $column['size']);
  581. // Allow unsigned integers (mysql only)
  582. $unsigned = in_array($type, array('int', 'tinyint', 'smallint', 'mediumint', 'bigint')) && !empty($column['unsigned']) ? 'unsigned ' : '';
  583. if ($size !== null)
  584. $type = $type . '(' . $size . ')';
  585. // Now just put it together!
  586. return '`' .$column['name'] . '` ' . $type . ' ' . (!empty($unsigned) ? $unsigned : '') . (!empty($column['null']) ? '' : 'NOT NULL') . ' ' . $default;
  587. }
  588. ?>