DbPackages-sqlite.php 26 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831
  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_alter_table' => 'smf_db_alter_table',
  28. 'db_calculate_type' => 'smf_db_calculate_type',
  29. 'db_change_column' => 'smf_db_change_column',
  30. 'db_create_table' => 'smf_db_create_table',
  31. 'db_drop_table' => 'smf_db_drop_table',
  32. 'db_table_structure' => 'smf_db_table_structure',
  33. 'db_list_columns' => 'smf_db_list_columns',
  34. 'db_list_indexes' => 'smf_db_list_indexes',
  35. 'db_remove_column' => 'smf_db_remove_column',
  36. 'db_remove_index' => 'smf_db_remove_index',
  37. );
  38. $db_package_log = array();
  39. }
  40. // We setup an array of SMF tables we can't do auto-remove on - in case a mod writer cocks it up!
  41. $reservedTables = array('admin_info_files', 'approval_queue', 'attachments', 'ban_groups', 'ban_items',
  42. 'board_permissions', 'boards', 'calendar', 'calendar_holidays', 'categories', 'collapsed_categories',
  43. 'custom_fields', 'group_moderators', 'log_actions', 'log_activity', 'log_banned', 'log_boards',
  44. 'log_digest', 'log_errors', 'log_floodcontrol', 'log_group_requests', 'log_karma', 'log_mark_read',
  45. 'log_notify', 'log_online', 'log_packages', 'log_polls', 'log_reported', 'log_reported_comments',
  46. 'log_scheduled_tasks', 'log_search_messages', 'log_search_results', 'log_search_subjects',
  47. 'log_search_topics', 'log_topics', 'mail_queue', 'membergroups', 'members', 'message_icons',
  48. 'messages', 'moderators', 'package_servers', 'permission_profiles', 'permissions', 'personal_messages',
  49. 'pm_recipients', 'poll_choices', 'polls', 'scheduled_tasks', 'sessions', 'settings', 'smileys',
  50. 'themes', 'topics');
  51. foreach ($reservedTables as $k => $table_name)
  52. $reservedTables[$k] = strtolower($db_prefix . $table_name);
  53. // We in turn may need the extra stuff.
  54. db_extend('extra');
  55. }
  56. /**
  57. * This function can be used to create a table without worrying about schema
  58. * compatabilities across supported database systems.
  59. * - If the table exists will, by default, do nothing.
  60. * - Builds table with columns as passed to it - at least one column must be sent.
  61. * The columns array should have one sub-array for each column - these sub arrays contain:
  62. * 'name' = Column name
  63. * 'type' = Type of column - values from (smallint, mediumint, int, text, varchar, char, tinytext, mediumtext, largetext)
  64. * 'size' => Size of column (If applicable) - for example 255 for a large varchar, 10 for an int etc.
  65. * If not set SMF will pick a size.
  66. * - 'default' = Default value - do not set if no default required.
  67. * - 'null' => Can it be null (true or false) - if not set default will be false.
  68. * - 'auto' => Set to true to make it an auto incrementing column. Set to a numerical value to set from what
  69. * it should begin counting.
  70. * - Adds indexes as specified within indexes parameter. Each index should be a member of $indexes. Values are:
  71. * - 'name' => Index name (If left empty SMF will generate).
  72. * - 'type' => Type of index. Choose from 'primary', 'unique' or 'index'. If not set will default to 'index'.
  73. * - 'columns' => Array containing columns that form part of key - in the order the index is to be created.
  74. * - parameters: (None yet)
  75. * - if_exists values:
  76. * - 'ignore' will do nothing if the table exists. (And will return true)
  77. * - 'overwrite' will drop any existing table of the same name.
  78. * - 'error' will return false if the table already exists.
  79. *
  80. * @param string $table_name The name of the table to create
  81. * @param array $columns An array of column info in the specified format
  82. * @param array $indexes An array of index info in the specified format
  83. * @param array $parameters Currently not used
  84. * @param string $if_exists What to do if the table exists.
  85. * @param string $error
  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;
  90. // With or without the database name, the full name looks like this.
  91. $real_prefix = preg_match('~^(`?)(.+?)\\1\\.(.*?)$~', $db_prefix, $match) === 1 ? $match[3] : $db_prefix;
  92. $full_table_name = str_replace('{db_prefix}', $real_prefix, $table_name);
  93. $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
  94. // First - no way do we touch SMF tables.
  95. // Commented out for now. We need to alter SMF tables in order to use this in the upgrade.
  96. /*
  97. if (in_array(strtolower($table_name), $reservedTables))
  98. return false;
  99. */
  100. // Log that we'll want to remove this on uninstall.
  101. $db_package_log[] = array('remove_table', $table_name);
  102. // Does this table exist or not?
  103. $tables = $smcFunc['db_list_tables']();
  104. if (in_array($full_table_name, $tables))
  105. {
  106. // This is a sad day... drop the table? If not, return false (error) by default.
  107. if ($if_exists == 'overwrite')
  108. $smcFunc['db_drop_table']($table_name);
  109. else
  110. return $if_exists == 'ignore';
  111. }
  112. // Righty - let's do the damn thing!
  113. $table_query = 'CREATE TABLE ' . $table_name . "\n" . '(';
  114. $done_primary = false;
  115. foreach ($columns as $column)
  116. {
  117. // Auto increment is special
  118. if (!empty($column['auto']))
  119. {
  120. $table_query .= "\n" . $column['name'] . ' integer PRIMARY KEY,';
  121. $done_primary = true;
  122. continue;
  123. }
  124. elseif (isset($column['default']) && $column['default'] !== null)
  125. $default = 'default \'' . $smcFunc['db_escape_string']($column['default']) . '\'';
  126. else
  127. $default = '';
  128. // Sort out the size... and stuff...
  129. $column['size'] = isset($column['size']) && is_numeric($column['size']) ? $column['size'] : null;
  130. list ($type, $size) = $smcFunc['db_calculate_type']($column['type'], $column['size']);
  131. if ($size !== null)
  132. $type = $type . '(' . $size . ')';
  133. // Now just put it together!
  134. $table_query .= "\n\t" . $column['name'] . ' ' . $type . ' ' . (!empty($column['null']) ? '' : 'NOT NULL') . ' ' . $default . ',';
  135. }
  136. // Loop through the indexes next...
  137. $index_queries = array();
  138. foreach ($indexes as $index)
  139. {
  140. $columns = implode(',', $index['columns']);
  141. // Is it the primary?
  142. if (isset($index['type']) && $index['type'] == 'primary')
  143. {
  144. // If we've done the primary via auto_inc, don't do it again!
  145. if (!$done_primary)
  146. $table_query .= "\n\t" . 'PRIMARY KEY (' . implode(',', $index['columns']) . '),';
  147. }
  148. else
  149. {
  150. if (empty($index['name']))
  151. $index['name'] = implode('_', $index['columns']);
  152. $index_queries[] = 'CREATE ' . (isset($index['type']) && $index['type'] == 'unique' ? 'UNIQUE' : '') . ' INDEX ' . $table_name . '_' . $index['name'] . ' ON ' . $table_name . ' (' . $columns . ')';
  153. }
  154. }
  155. // No trailing commas!
  156. if (substr($table_query, -1) == ',')
  157. $table_query = substr($table_query, 0, -1);
  158. $table_query .= ')';
  159. if (empty($parameters['skip_transaction']))
  160. $smcFunc['db_transaction']('begin');
  161. // Do the table and indexes...
  162. $smcFunc['db_query']('', $table_query,
  163. array(
  164. 'security_override' => true,
  165. )
  166. );
  167. foreach ($index_queries as $query)
  168. $smcFunc['db_query']('', $query,
  169. array(
  170. 'security_override' => true,
  171. )
  172. );
  173. if (empty($parameters['skip_transaction']))
  174. $smcFunc['db_transaction']('commit');
  175. return true;
  176. }
  177. /**
  178. * Drop a table.
  179. *
  180. * @param string $table_name The name of the table to drop
  181. * @param array $parameters Not used at the moment
  182. * @param string $error
  183. * @return boolean Whether or not the operation was successful
  184. */
  185. function smf_db_drop_table($table_name, $parameters = array(), $error = 'fatal')
  186. {
  187. global $reservedTables, $smcFunc, $db_prefix;
  188. // Strip out the table name, we might not need it in some cases
  189. $real_prefix = preg_match('~^(`?)(.+?)\\1\\.(.*?)$~', $db_prefix, $match) === 1 ? $match[3] : $db_prefix;
  190. $full_table_name = str_replace('{db_prefix}', $real_prefix, $table_name);
  191. $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
  192. // God no - dropping one of these = bad.
  193. if (in_array(strtolower($table_name), $reservedTables))
  194. return false;
  195. // Does it exist?
  196. if (in_array($full_table_name, $smcFunc['db_list_tables']()))
  197. {
  198. $query = 'DROP TABLE ' . $table_name;
  199. $smcFunc['db_query']('', $query,
  200. array(
  201. 'security_override' => true,
  202. )
  203. );
  204. return true;
  205. }
  206. // Otherwise do 'nout.
  207. return false;
  208. }
  209. /**
  210. * This function adds a column.
  211. *
  212. * @param string $table_name The name of the table to add the column to
  213. * @param array $column_info An array of column info ({@see smf_db_create_table})
  214. * @param array $parameters Not used?
  215. * @param string $if_exists What to do if the column exists. If 'update', column is updated.
  216. * @param string $error
  217. * @return boolean Whether or not the operation was successful
  218. */
  219. function smf_db_add_column($table_name, $column_info, $parameters = array(), $if_exists = 'update', $error = 'fatal')
  220. {
  221. global $smcFunc, $db_package_log, $txt, $db_prefix;
  222. $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
  223. // Log that we will want to uninstall this!
  224. $db_package_log[] = array('remove_column', $table_name, $column_info['name']);
  225. // Does it exist - if so don't add it again!
  226. $columns = $smcFunc['db_list_columns']($table_name, false);
  227. foreach ($columns as $column)
  228. if ($column == $column_info['name'])
  229. {
  230. // If we're going to overwrite then use change column.
  231. if ($if_exists == 'update')
  232. return $smcFunc['db_change_column']($table_name, $column_info['name'], $column_info);
  233. else
  234. return false;
  235. }
  236. // Alter the table to add the column.
  237. if ($smcFunc['db_alter_table']($table_name, array('add' => array($column_info))) === false)
  238. return false;
  239. return true;
  240. }
  241. /**
  242. * Removes a column.
  243. *
  244. * We can't reliably do this on SQLite - damn!
  245. * @param string $table_name The name of the table to drop the column from
  246. * @param string $column_name The name of the column to drop
  247. * @param array $parameters Not used?
  248. * @param string $error
  249. * @return boolean Whether or not the operation was successful
  250. */
  251. function smf_db_remove_column($table_name, $column_name, $parameters = array(), $error = 'fatal')
  252. {
  253. global $smcFunc, $db_prefix;
  254. $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
  255. if ($smcFunc['db_alter_table']($table_name, array('remove' => array(array('name' => $column_name)))))
  256. return true;
  257. else
  258. return false;
  259. }
  260. /**
  261. * Change a column.
  262. *
  263. * @param string $table_name The name of the table this column is in
  264. * @param string $old_column The name of the column we want to change
  265. * @param array $column_info An array of info about the "new" column definition (see {@link smf_db_create_table()})
  266. * @param array $parameters Not used?
  267. * @param string $error
  268. */
  269. function smf_db_change_column($table_name, $old_column, $column_info, $parameters = array(), $error = 'fatal')
  270. {
  271. global $smcFunc, $db_prefix;
  272. $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
  273. if ($smcFunc['db_alter_table']($table_name, array('change' => array(array('name' => $old_column) + $column_info))))
  274. return true;
  275. else
  276. return false;
  277. }
  278. /**
  279. * Add an index.
  280. *
  281. * @param string $table_name The name of the table to add the index to
  282. * @param array $index_info An array of index info (see {@link smf_db_create_table()})
  283. * @param array $parameters Not used?
  284. * @param string $if_exists What to do if the index exists. If 'update', the definition will be updated.
  285. * @param string $error
  286. * @return boolean Whether or not the operation was successful
  287. */
  288. function smf_db_add_index($table_name, $index_info, $parameters = array(), $if_exists = 'update', $error = 'fatal')
  289. {
  290. global $smcFunc, $db_package_log, $db_prefix;
  291. $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
  292. // No columns = no index.
  293. if (empty($index_info['columns']))
  294. return false;
  295. $columns = implode(',', $index_info['columns']);
  296. // No name - make it up!
  297. if (empty($index_info['name']))
  298. {
  299. // No need for primary.
  300. if (isset($index_info['type']) && $index_info['type'] == 'primary')
  301. $index_info['name'] = '';
  302. else
  303. $index_info['name'] = implode('_', $index_info['columns']);
  304. }
  305. else
  306. $index_info['name'] = $index_info['name'];
  307. // Log that we are going to want to remove this!
  308. $db_package_log[] = array('remove_index', $table_name, $index_info['name']);
  309. // Let's get all our indexes.
  310. $indexes = $smcFunc['db_list_indexes']($table_name, true);
  311. // Do we already have it?
  312. foreach ($indexes as $index)
  313. {
  314. if ($index['name'] == $index_info['name'] || ($index['type'] == 'primary' && isset($index_info['type']) && $index_info['type'] == 'primary'))
  315. {
  316. // If we want to overwrite simply remove the current one then continue.
  317. if ($if_exists != 'update' || $index['type'] == 'primary')
  318. return false;
  319. else
  320. $smcFunc['db_remove_index']($table_name, $index_info['name']);
  321. }
  322. }
  323. // If we're here we know we don't have the index - so just add it.
  324. if (!empty($index_info['type']) && $index_info['type'] == 'primary')
  325. {
  326. // @todo Doesn't work with PRIMARY KEY yet.
  327. }
  328. else
  329. {
  330. $smcFunc['db_query']('', '
  331. CREATE ' . (isset($index_info['type']) && $index_info['type'] == 'unique' ? 'UNIQUE' : '') . ' INDEX ' . $index_info['name'] . ' ON ' . $table_name . ' (' . $columns . ')',
  332. array(
  333. 'security_override' => true,
  334. )
  335. );
  336. }
  337. }
  338. /**
  339. * Remove an index.
  340. *
  341. * @param string $table_name The name of the table to remove the index from
  342. * @param string $index_name The name of the index to remove
  343. * @param array $parameters Not used?
  344. * @param string $error
  345. * @return boolean Whether or not the operation was successful
  346. */
  347. function smf_db_remove_index($table_name, $index_name, $parameters = array(), $error = 'fatal')
  348. {
  349. global $smcFunc, $db_prefix;
  350. $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
  351. // Better exist!
  352. $indexes = $smcFunc['db_list_indexes']($table_name, true);
  353. foreach ($indexes as $index)
  354. {
  355. // @todo Doesn't do primary key at the moment!
  356. if ($index['type'] != 'primary' && $index['name'] == $index_name)
  357. {
  358. // Drop the bugger...
  359. $smcFunc['db_query']('', '
  360. DROP INDEX ' . $index_name,
  361. array(
  362. 'security_override' => true,
  363. )
  364. );
  365. return true;
  366. }
  367. }
  368. // Not to be found ;(
  369. return false;
  370. }
  371. /**
  372. * Get the schema formatted name for a type.
  373. *
  374. * @param string $type_name The data type (int, varchar, smallint, etc.)
  375. * @param int $type_size The size (8, 255, etc.)
  376. * @param boolean $reverse If true, returns specific types for a generic type
  377. * @return An array containing the appropriate type and size for this DB type
  378. */
  379. function smf_db_calculate_type($type_name, $type_size = null, $reverse = false)
  380. {
  381. // Let's be sure it's lowercase MySQL likes both, others no.
  382. $type_name = strtolower($type_name);
  383. // Generic => Specific.
  384. if (!$reverse)
  385. {
  386. $types = array(
  387. 'mediumint' => 'int',
  388. 'tinyint' => 'smallint',
  389. 'mediumtext' => 'text',
  390. 'largetext' => 'text',
  391. );
  392. }
  393. else
  394. {
  395. $types = array(
  396. 'integer' => 'int',
  397. );
  398. }
  399. // Got it? Change it!
  400. if (isset($types[$type_name]))
  401. {
  402. if ($type_name == 'tinytext')
  403. $type_size = 255;
  404. $type_name = $types[$type_name];
  405. }
  406. // Numbers don't have a size.
  407. if (strpos($type_name, 'int') !== false)
  408. $type_size = null;
  409. return array($type_name, $type_size);
  410. }
  411. /**
  412. * Get table structure.
  413. *
  414. * @param string $table_name The name of the table
  415. * @param array $parameters Not used?
  416. * @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()}
  417. */
  418. function smf_db_table_structure($table_name, $parameters = array())
  419. {
  420. global $smcFunc, $db_prefix;
  421. $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
  422. return array(
  423. 'name' => $table_name,
  424. 'columns' => $smcFunc['db_list_columns']($table_name, true),
  425. 'indexes' => $smcFunc['db_list_indexes']($table_name, true),
  426. );
  427. }
  428. /**
  429. * Return column information for a table.
  430. * Harder than it should be, on sqlite!
  431. *
  432. * @param string $table_name The name of the table to get column info for
  433. * @param bool $detail Whether or not to return detailed info. If true, returns the column info. If false, just returns the column names.
  434. * @param array $parameters Not used?
  435. * @return array An array of column names or detailed column info, depending on $detail
  436. */
  437. function smf_db_list_columns($table_name, $detail = false, $parameters = array())
  438. {
  439. global $smcFunc, $db_prefix;
  440. $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
  441. $result = $smcFunc['db_query']('', '
  442. PRAGMA table_info(' . $table_name . ')',
  443. array(
  444. 'security_override' => true,
  445. )
  446. );
  447. $columns = array();
  448. $primaries = array();
  449. while ($row = $smcFunc['db_fetch_assoc']($result))
  450. {
  451. if (!$detail)
  452. {
  453. $columns[] = $row['name'];
  454. }
  455. else
  456. {
  457. // Auto increment is hard to tell really... if there's only one primary it probably is.
  458. if ($row['pk'])
  459. $primaries[] = $row['name'];
  460. // Can we split out the size?
  461. if (preg_match('~(.+?)\s*\((\d+)\)~i', $row['type'], $matches))
  462. {
  463. $type = $matches[1];
  464. $size = $matches[2];
  465. }
  466. else
  467. {
  468. $type = $row['type'];
  469. $size = null;
  470. }
  471. $columns[$row['name']] = array(
  472. 'name' => $row['name'],
  473. 'null' => $row['notnull'] ? false : true,
  474. 'default' => $row['dflt_value'],
  475. 'type' => $type,
  476. 'size' => $size,
  477. 'auto' => false,
  478. );
  479. }
  480. }
  481. $smcFunc['db_free_result']($result);
  482. // Put in our guess at auto_inc.
  483. if (count($primaries) == 1)
  484. $columns[$primaries[0]]['auto'] = true;
  485. return $columns;
  486. }
  487. /**
  488. * Get index information.
  489. *
  490. * @param string $table_name The name of the table to get indexes for
  491. * @param bool $detail Whether or not to return detailed info.
  492. * @param array $parameters Not used?
  493. * @return array An array of index names or a detailed array of index info, depending on $detail
  494. */
  495. function smf_db_list_indexes($table_name, $detail = false, $parameters = array())
  496. {
  497. global $smcFunc, $db_prefix;
  498. $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
  499. $result = $smcFunc['db_query']('', '
  500. PRAGMA index_list(' . $table_name . ')',
  501. array(
  502. 'security_override' => true,
  503. )
  504. );
  505. $indexes = array();
  506. while ($row = $smcFunc['db_fetch_assoc']($result))
  507. {
  508. if (!$detail)
  509. $indexes[] = $row['name'];
  510. else
  511. {
  512. $result2 = $smcFunc['db_query']('', '
  513. PRAGMA index_info(' . $row['name'] . ')',
  514. array(
  515. 'security_override' => true,
  516. )
  517. );
  518. while ($row2 = $smcFunc['db_fetch_assoc']($result2))
  519. {
  520. // What is the type?
  521. if ($row['unique'])
  522. $type = 'unique';
  523. else
  524. $type = 'index';
  525. // This is the first column we've seen?
  526. if (empty($indexes[$row['name']]))
  527. {
  528. $indexes[$row['name']] = array(
  529. 'name' => $row['name'],
  530. 'type' => $type,
  531. 'columns' => array(),
  532. );
  533. }
  534. // Add the column...
  535. $indexes[$row['name']]['columns'][] = $row2['name'];
  536. }
  537. $smcFunc['db_free_result']($result2);
  538. }
  539. }
  540. $smcFunc['db_free_result']($result);
  541. return $indexes;
  542. }
  543. /**
  544. * Alter table on SQLite.
  545. *
  546. * @param string $table_name The name of the table
  547. * @param array $columns An array of arrays of column info and what to do. Can contain 'remove', an array of names of columns to remove, and/or 'add', an array of details of columns to add (see {@link smf_db_list_columns()})
  548. * @return boolean Whether or not the operations were successful
  549. */
  550. function smf_db_alter_table($table_name, $columns)
  551. {
  552. global $smcFunc, $db_prefix, $db_name, $boarddir, $packagesdir;
  553. $db_file = substr($db_name, -3) === '.db' ? $db_name : $db_name . '.db';
  554. $table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
  555. // Let's get the current columns for the table.
  556. $current_columns = $smcFunc['db_list_columns']($table_name, true);
  557. // Let's get a list of columns for the temp table.
  558. $temp_table_columns = array();
  559. // Let's see if we have columns to remove or columns that are being added that already exist.
  560. foreach ($current_columns as $key => $column)
  561. {
  562. $exists = false;
  563. if (isset($columns['remove']))
  564. foreach ($columns['remove'] as $drop)
  565. if ($drop['name'] == $column['name'])
  566. {
  567. $exists = true;
  568. break;
  569. }
  570. if (isset($columns['add']))
  571. foreach ($columns['add'] as $key2 => $add)
  572. if ($add['name'] == $column['name'])
  573. {
  574. unset($columns['add'][$key2]);
  575. break;
  576. }
  577. // Doesn't exist then we 'remove'.
  578. if (!$exists)
  579. $temp_table_columns[] = $column['name'];
  580. }
  581. // If they are equal then that means that the column that we are adding exists or it doesn't exist and we are not looking to change any one of them.
  582. if (count($temp_table_columns) == count($current_columns) && empty($columns['change']) && empty($columns['add']))
  583. return true;
  584. // Drop the temp table.
  585. $smcFunc['db_query']('', '
  586. DROP TABLE {raw:temp_table_name}',
  587. array(
  588. 'temp_table_name' => $table_name . '_tmp',
  589. 'db_error_skip' => true,
  590. )
  591. );
  592. // Let's make a backup of the current database.
  593. // We only want the first backup of a table modification. So if there is a backup file and older than an hour just delete and back up again
  594. $db_backup_file = $packagesdir . '/backups/backup_' . $table_name . '_' . basename($db_file) . md5($table_name . $db_file);
  595. if (file_exists($db_backup_file) && time() - filemtime($db_backup_file) > 3600)
  596. {
  597. @unlink($db_backup_file);
  598. @copy($db_file, $db_backup_file);
  599. }
  600. elseif (!file_exists($db_backup_file))
  601. @copy($db_file, $db_backup_file);
  602. // If we don't have temp tables then everything crapped out. Just exit.
  603. if (empty($temp_table_columns))
  604. return false;
  605. // Start
  606. $smcFunc['db_transaction']('begin');
  607. // Let's create the temporary table.
  608. $createTempTable = $smcFunc['db_query']('', '
  609. CREATE TEMPORARY TABLE {raw:temp_table_name}
  610. (
  611. {raw:columns}
  612. );',
  613. array(
  614. 'temp_table_name' => $table_name . '_tmp',
  615. 'columns' => implode(', ', $temp_table_columns),
  616. 'db_error_skip' => true,
  617. )
  618. ) !== false;
  619. if (!$createTempTable)
  620. return false;
  621. // Insert into temp table.
  622. $smcFunc['db_query']('', '
  623. INSERT INTO {raw:temp_table_name}
  624. ({raw:columns})
  625. SELECT {raw:columns}
  626. FROM {raw:table_name}',
  627. array(
  628. 'table_name' => $table_name,
  629. 'columns' => implode(', ', $temp_table_columns),
  630. 'temp_table_name' => $table_name . '_tmp',
  631. )
  632. );
  633. // Drop the current table.
  634. $dropTable = $smcFunc['db_query']('', '
  635. DROP TABLE {raw:table_name}',
  636. array(
  637. 'table_name' => $table_name,
  638. 'db_error_skip' => true,
  639. )
  640. ) !== false;
  641. // If you can't drop the main table then there is no where to go from here. Just return.
  642. if (!$dropTable)
  643. return false;
  644. // We need to keep track of the structure for the current columns and the new columns.
  645. $new_columns = array();
  646. $column_names = array();
  647. // Let's get the ones that we already have first.
  648. foreach ($current_columns as $name => $column)
  649. {
  650. if (in_array($name, $temp_table_columns))
  651. {
  652. $new_columns[$name] = array(
  653. 'name' => $name,
  654. 'type' => $column['type'],
  655. 'size' => isset($column['size']) ? (int) $column['size'] : null,
  656. 'null' => !empty($column['null']),
  657. 'auto' => isset($column['auto']) ? $column['auto'] : false,
  658. 'default' => isset($column['default']) ? $column['default'] : '',
  659. );
  660. // Lets keep track of the name for the column.
  661. $column_names[$name] = $name;
  662. }
  663. }
  664. // Now the new.
  665. if (!empty($columns['add']))
  666. foreach ($columns['add'] as $add)
  667. {
  668. $new_columns[$add['name']] = array(
  669. 'name' => $add['name'],
  670. 'type' => $add['type'],
  671. 'size' => isset($add['size']) ? (int) $add['size'] : null,
  672. 'null' => !empty($add['null']),
  673. 'auto' => isset($add['auto']) ? $add['auto'] : false,
  674. 'default' => isset($add['default']) ? $add['default'] : '',
  675. );
  676. // Let's keep track of the name for the column.
  677. $column_names[$add['name']] = strstr('int', $add['type']) ? ' 0 AS ' . $add['name'] : ' {string:empty_string} AS ' . $add['name'];
  678. }
  679. // Now to change a column. Not drop but change it.
  680. if (isset($columns['change']))
  681. foreach ($columns['change'] as $change)
  682. if (isset($new_columns[$change['name']]))
  683. $new_columns[$change['name']] = array(
  684. 'name' => $change['name'],
  685. 'type' => $change['type'],
  686. 'size' => isset($change['size']) ? (int) $change['size'] : null,
  687. 'null' => !empty($change['null']),
  688. 'auto' => isset($change['auto']) ? $change['auto'] : false,
  689. 'default' => isset($change['default']) ? $change['default'] : '',
  690. );
  691. // Now let's create the table.
  692. $createTable = $smcFunc['db_create_table']($table_name, $new_columns, array(), array('skip_transaction' => true));
  693. // Did it create correctly?
  694. if ($createTable === false)
  695. return false;
  696. // Back to it's original table.
  697. $insertData = $smcFunc['db_query']('', '
  698. INSERT INTO {raw:table_name}
  699. ({raw:columns})
  700. SELECT ' . implode(', ', $column_names) . '
  701. FROM {raw:temp_table_name}',
  702. array(
  703. 'table_name' => $table_name,
  704. 'columns' => implode(', ', array_keys($new_columns)),
  705. 'columns_select' => implode(', ', $column_names),
  706. 'temp_table_name' => $table_name . '_tmp',
  707. 'empty_string' => '',
  708. )
  709. );
  710. // Did everything insert correctly?
  711. if (!$insertData)
  712. return false;
  713. // Drop the temp table.
  714. $smcFunc['db_query']('', '
  715. DROP TABLE {raw:temp_table_name}',
  716. array(
  717. 'temp_table_name' => $table_name . '_tmp',
  718. 'db_error_skip' => true,
  719. )
  720. );
  721. // Commit or else there is no point in doing the previous steps.
  722. $smcFunc['db_transaction']('commit');
  723. // We got here so we're good. The temp table should be deleted, if not it will be gone later on >:D.
  724. return true;
  725. }
  726. ?>