upgrade_2-1_mysql.sql 14 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426
  1. /* ATTENTION: You don't need to run or use this file! The upgrade.php script does everything for you! */
  2. /******************************************************************************/
  3. --- Adding new settings...
  4. /******************************************************************************/
  5. ---# Adding login history...
  6. CREATE TABLE IF NOT EXISTS {$db_prefix}member_logins (
  7. id_login int(10) NOT NULL auto_increment,
  8. id_member mediumint(8) NOT NULL,
  9. time int(10) NOT NULL,
  10. ip varchar(255) NOT NULL default '',
  11. ip2 varchar(255) NOT NULL default '',
  12. PRIMARY KEY id_login(id_login),
  13. KEY id_member (id_member),
  14. KEY time (time)
  15. ) ENGINE=MyISAM{$db_collation};
  16. ---#
  17. ---# Copying the current package backup setting...
  18. ---{
  19. if (!isset($modSettings['package_make_full_backups']) && isset($modSettings['package_make_backups']))
  20. upgrade_query("
  21. INSERT INTO {$db_prefix}settings
  22. (variable, value)
  23. VALUES
  24. ('package_make_full_backups', '" . $modSettings['package_make_backups'] . "')");
  25. ---}
  26. ---#
  27. ---# Copying the current "allow users to disable word censor" setting...
  28. ---{
  29. if (!isset($modSettings['allow_no_censor']))
  30. {
  31. $request = upgrade_query("
  32. SELECT value
  33. FROM {$db_prefix}themes
  34. WHERE variable='allow_no_censor'
  35. AND id_theme = 1 OR id_theme = '$modSettings[theme_default]'
  36. ");
  37. // Is it set for either "default" or the one they've set as default?
  38. while ($row = mysql_fetch_assoc($request))
  39. {
  40. if ($row['value'] == 1)
  41. {
  42. upgrade_query("
  43. INSERT INTO {$db_prefix}settings
  44. VALUES ('allow_no_censor', 1)
  45. ");
  46. // Don't do this twice...
  47. break;
  48. }
  49. }
  50. }
  51. ---}
  52. ---#
  53. /******************************************************************************/
  54. --- Updating legacy attachments...
  55. /******************************************************************************/
  56. ---# Converting legacy attachments.
  57. ---{
  58. $request = upgrade_query("
  59. SELECT MAX(id_attach)
  60. FROM {$db_prefix}attachments");
  61. list ($step_progress['total']) = $smcFunc['db_fetch_row']($request);
  62. $smcFunc['db_free_result']($request);
  63. $_GET['a'] = isset($_GET['a']) ? (int) $_GET['a'] : 0;
  64. $step_progress['name'] = 'Converting legacy attachments';
  65. $step_progress['current'] = $_GET['a'];
  66. // We may be using multiple attachment directories.
  67. if (!empty($modSettings['currentAttachmentUploadDir']) && !is_array($modSettings['attachmentUploadDir']))
  68. $modSettings['attachmentUploadDir'] = unserialize($modSettings['attachmentUploadDir']);
  69. $is_done = false;
  70. while (!$is_done)
  71. {
  72. nextSubStep($substep);
  73. $request = upgrade_query("
  74. SELECT id_attach, id_folder, filename, file_hash
  75. FROM {$db_prefix}attachments
  76. WHERE file_hash = ''
  77. LIMIT $_GET[a], 100");
  78. // Finished?
  79. if ($smcFunc['db_num_rows']($request) == 0)
  80. $is_done = true;
  81. while ($row = $smcFunc['db_fetch_assoc']($request))
  82. {
  83. // The current folder.
  84. $current_folder = !empty($modSettings['currentAttachmentUploadDir']) ? $modSettings['attachmentUploadDir'][$row['id_folder']] : $modSettings['attachmentUploadDir'];
  85. // The old location of the file.
  86. $old_location = getLegacyAttachmentFilename($row['filename'], $row['id_attach'], $row['id_folder']);
  87. // The new file name.
  88. $file_hash = getAttachmentFilename($row['filename'], $row['id_attach'], $row['id_folder'], true);
  89. // And we try to move it.
  90. rename($old_location, $current_folder . '/' . $row['id_attach'] . '_' . $file_hash);
  91. // Only update thif if it was successful.
  92. if (file_exists($current_folder . '/' . $row['id_attach'] . '_' . $file_hash) && !file_exists($old_location))
  93. upgrade_query("
  94. UPDATE {$db_prefix}attachments
  95. SET file_hash = '$file_hash'
  96. WHERE id_attach = $row[id_attach]");
  97. }
  98. $smcFunc['db_free_result']($request);
  99. $_GET['a'] += 100;
  100. $step_progress['current'] = $_GET['a'];
  101. }
  102. unset($_GET['a']);
  103. ---}
  104. ---#
  105. /******************************************************************************/
  106. --- Adding support for IPv6...
  107. /******************************************************************************/
  108. ---# Adding new columns to ban items...
  109. ALTER TABLE {$db_prefix}ban_items
  110. ADD COLUMN ip_low5 smallint(255) unsigned NOT NULL DEFAULT '0',
  111. ADD COLUMN ip_high5 smallint(255) unsigned NOT NULL DEFAULT '0',
  112. ADD COLUMN ip_low6 smallint(255) unsigned NOT NULL DEFAULT '0',
  113. ADD COLUMN ip_high6 smallint(255) unsigned NOT NULL DEFAULT '0',
  114. ADD COLUMN ip_low7 smallint(255) unsigned NOT NULL DEFAULT '0',
  115. ADD COLUMN ip_high7 smallint(255) unsigned NOT NULL DEFAULT '0',
  116. ADD COLUMN ip_low8 smallint(255) unsigned NOT NULL DEFAULT '0',
  117. ADD COLUMN ip_high8 smallint(255) unsigned NOT NULL DEFAULT '0';
  118. ---#
  119. ---# Changing existing columns to ban items...
  120. ALTER TABLE {$db_prefix}ban_items
  121. CHANGE ip_low1 ip_low1 smallint(255) unsigned NOT NULL DEFAULT '0',
  122. CHANGE ip_high1 ip_high1 smallint(255) unsigned NOT NULL DEFAULT '0',
  123. CHANGE ip_low2 ip_low2 smallint(255) unsigned NOT NULL DEFAULT '0',
  124. CHANGE ip_high2 ip_high2 smallint(255) unsigned NOT NULL DEFAULT '0',
  125. CHANGE ip_low3 ip_low3 smallint(255) unsigned NOT NULL DEFAULT '0',
  126. CHANGE ip_high3 ip_high3 smallint(255) unsigned NOT NULL DEFAULT '0',
  127. CHANGE ip_low4 ip_low4 smallint(255) unsigned NOT NULL DEFAULT '0',
  128. CHANGE ip_high4 ip_high4 smallint(255) unsigned NOT NULL DEFAULT '0';
  129. ---#
  130. /******************************************************************************/
  131. --- Adding support for <credits> tag in package manager
  132. /******************************************************************************/
  133. ---# Adding new columns to log_packages ..
  134. ALTER TABLE {$db_prefix}log_packages
  135. ADD COLUMN credits varchar(255) NOT NULL DEFAULT '';
  136. ---#
  137. /******************************************************************************/
  138. --- Adding more space for session ids
  139. /******************************************************************************/
  140. ---# Altering the session_id columns...
  141. ALTER TABLE {$db_prefix}log_online
  142. CHANGE `session` `session` varchar(64) NOT NULL DEFAULT '';
  143. ALTER TABLE {$db_prefix}log_errors
  144. CHANGE `session` `session` char(64) NOT NULL default ' ';
  145. ALTER TABLE {$db_prefix}sessions
  146. CHANGE `session_id` `session_id` char(64) NOT NULL;
  147. ---#
  148. /******************************************************************************/
  149. --- Adding support for MOVED topics enhancements
  150. /******************************************************************************/
  151. ---# Adding new columns to topics ..
  152. ALTER TABLE {$db_prefix}topics
  153. ADD COLUMN redirect_expires int(10) unsigned NOT NULL default '0',
  154. ADD COLUMN id_redirect_topic mediumint(8) unsigned NOT NULL default '0';
  155. ---#
  156. /******************************************************************************/
  157. --- Adding new scheduled tasks
  158. /******************************************************************************/
  159. ---# Adding new scheduled tasks
  160. INSERT INTO {$db_prefix}scheduled_tasks
  161. (next_time, time_offset, time_regularity, time_unit, disabled, task)
  162. VALUES
  163. (0, 120, 1, 'd', 0, 'remove_temp_attachments');
  164. INSERT INTO {$db_prefix}scheduled_tasks
  165. (next_time, time_offset, time_regularity, time_unit, disabled, task)
  166. VALUES
  167. (0, 180, 1, 'd', 0, 'remove_topic_redirect');
  168. INSERT INTO {$db_prefix}scheduled_tasks
  169. (next_time, time_offset, time_regularity, time_unit, disabled, task)
  170. VALUES
  171. (0, 240, 1, 'd', 0, 'remove_old_drafts');
  172. ---#
  173. /******************************************************************************/
  174. ---- Replacing MSN with Skype
  175. /******************************************************************************/
  176. ---# Modifying the "msn" column...
  177. ALTER TABLE {$db_prefix}members
  178. CHANGE msn skype varchar(255) NOT NULL DEFAULT '';
  179. ---#
  180. /******************************************************************************/
  181. --- Adding support for deny boards access
  182. /******************************************************************************/
  183. ---# Adding new columns to boards...
  184. ALTER TABLE {$db_prefix}boards
  185. ADD COLUMN deny_member_groups varchar(255) NOT NULL DEFAULT '';
  186. ---#
  187. /******************************************************************************/
  188. --- Adding support for topic unwatch
  189. /******************************************************************************/
  190. ---# Adding new columns to boards...
  191. ALTER TABLE {$db_prefix}log_topics
  192. ADD COLUMN unwatched tinyint(3) NOT NULL DEFAULT '0';
  193. UPDATE {$db_prefix}log_topics
  194. SET unwatched = 0;
  195. INSERT INTO {$db_prefix}settings
  196. (variable, value)
  197. VALUES
  198. ('enable_unwatch', 0);
  199. ---#
  200. ---# Fixing column name change...
  201. ALTER TABLE {$db_prefix}log_topics
  202. CHANGE COLUMN disregarded unwatched tinyint(3) NOT NULL DEFAULT '0';
  203. ---#
  204. /******************************************************************************/
  205. --- Fixing mail queue for long messages
  206. /******************************************************************************/
  207. ---# Altering mil_queue table...
  208. ALTER TABLE {$db_prefix}mail_queue
  209. CHANGE body body mediumtext NOT NULL;
  210. ---#
  211. /******************************************************************************/
  212. --- Name changes
  213. /******************************************************************************/
  214. ---# Altering the membergroup stars to icons
  215. ALTER TABLE {$db_prefix}membergroups
  216. CHANGE `stars` `icons` varchar(255) NOT NULL DEFAULT '';
  217. ---#
  218. ---# Renaming default theme...
  219. UPDATE {$db_prefix}themes
  220. SET value = 'SMF Default Theme - Curve2'
  221. WHERE value LIKE 'SMF Default Theme%';
  222. ---#
  223. /******************************************************************************/
  224. --- Cleaning up after old themes...
  225. /******************************************************************************/
  226. ---# Checking for "core" and removing it if necessary...
  227. ---{
  228. // Do they have "core" installed?
  229. if (file_exists($GLOBALS['boarddir'] . '/Themes/core'))
  230. {
  231. $core_dir = $GLOBALS['boarddir'] . '/Themes/core';
  232. $theme_request = upgrade_query("
  233. SELECT id_theme
  234. FROM {$db_prefix}themes
  235. WHERE variable = {str:variable}
  236. AND value ='$core_dir'");
  237. // Don't do anything if this theme is already uninstalled
  238. if (smf_mysql_num_rows($theme_request) == 1)
  239. {
  240. $id_theme = mysql_result($theme_request, 0);
  241. mysql_free_result($theme_request);
  242. $known_themes = explode(', ', $modSettings['knownThemes']);
  243. // Remove this value...
  244. $known_themes = array_diff($known_themes, array($id_theme));
  245. // Change back to a string...
  246. $known_themes = implode(', ', $known_themes);
  247. // Update the database
  248. upgrade_query("
  249. REPLACE INTO {$db_prefix}settings (variable, value)
  250. VALUES ('knownThemes', '$known_themes')");
  251. // Delete any info about this theme
  252. upgrade_query("
  253. DELETE FROM {$db_prefix}themes
  254. WHERE id_theme = $id_theme");
  255. // Set any members or boards using this theme to the default
  256. upgrade_query("
  257. UPDATE {$db_prefix}members
  258. SET id_theme = 0
  259. WHERE id_theme = $id_theme");
  260. upgrade_query("
  261. UPDATE {$db_prefix}boards
  262. SET id_theme = 0
  263. WHERE id_theme = $id_theme");
  264. if ($modSettings['theme_guests'] == $id_theme)
  265. {
  266. upgrade_query("
  267. REPLACE INTO {$db_prefix}settings
  268. (variable, value)
  269. VALUES('theme_guests', 0)");
  270. }
  271. }
  272. }
  273. ---}
  274. ---#
  275. /******************************************************************************/
  276. --- Adding support for drafts
  277. /******************************************************************************/
  278. ---# Creating draft table
  279. CREATE TABLE IF NOT EXISTS {$db_prefix}user_drafts (
  280. id_draft int(10) unsigned NOT NULL auto_increment,
  281. id_topic mediumint(8) unsigned NOT NULL default '0',
  282. id_board smallint(5) unsigned NOT NULL default '0',
  283. id_reply int(10) unsigned NOT NULL default '0',
  284. type tinyint(4) NOT NULL default '0',
  285. poster_time int(10) unsigned NOT NULL default '0',
  286. id_member mediumint(8) unsigned NOT NULL default '0',
  287. subject varchar(255) NOT NULL default '',
  288. smileys_enabled tinyint(4) NOT NULL default '1',
  289. body mediumtext NOT NULL,
  290. icon varchar(16) NOT NULL default 'xx',
  291. locked tinyint(4) NOT NULL default '0',
  292. is_sticky tinyint(4) NOT NULL default '0',
  293. to_list varchar(255) NOT NULL default '',
  294. outbox tinyint(4) NOT NULL default '0',
  295. PRIMARY KEY id_draft(id_draft),
  296. UNIQUE id_member (id_member, id_draft, type)
  297. ) ENGINE=MyISAM{$db_collation};
  298. ---#
  299. ---# Adding draft permissions...
  300. ---{
  301. // We cannot do this twice
  302. if (@$modSettings['smfVersion'] < '2.1')
  303. {
  304. // Anyone who can currently post unapproved topics we assume can create drafts as well ...
  305. $request = upgrade_query("
  306. SELECT id_group, id_board, add_deny, permission
  307. FROM {$db_prefix}board_permissions
  308. WHERE permission = 'post_unapproved_topics'");
  309. $inserts = array();
  310. while ($row = smf_mysql_fetch_assoc($request))
  311. {
  312. $inserts[] = "($row[id_group], $row[id_board], 'post_draft', $row[add_deny])";
  313. $inserts[] = "($row[id_group], $row[id_board], 'post_autosave_draft', $row[add_deny])";
  314. }
  315. smf_mysql_free_result($request);
  316. if (!empty($inserts))
  317. upgrade_query("
  318. INSERT IGNORE INTO {$db_prefix}board_permissions
  319. (id_group, id_board, permission, add_deny)
  320. VALUES
  321. " . implode(',', $inserts));
  322. // Next we find people who can send PMs, and assume they can save pm_drafts as well
  323. $request = upgrade_query("
  324. SELECT id_group, add_deny, permission
  325. FROM {$db_prefix}permissions
  326. WHERE permission = 'pm_send'");
  327. $inserts = array();
  328. while ($row = smf_mysql_fetch_assoc($request))
  329. {
  330. $inserts[] = "($row[id_group], 'pm_draft', $row[add_deny])";
  331. $inserts[] = "($row[id_group], 'pm_autosave_draft', $row[add_deny])";
  332. }
  333. smf_mysql_free_result($request);
  334. if (!empty($inserts))
  335. upgrade_query("
  336. INSERT IGNORE INTO {$db_prefix}permissions
  337. (id_group, permission, add_deny)
  338. VALUES
  339. " . implode(',', $inserts));
  340. }
  341. ---}
  342. INSERT INTO {$db_prefix}settings
  343. (variable, value)
  344. VALUES
  345. ('drafts_autosave_enabled', '1'),
  346. ('drafts_show_saved_enabled', '1'),
  347. ('drafts_keep_days', '7');
  348. INSERT INTO {$db_prefix}themes
  349. (id_theme, variable, value)
  350. VALUES
  351. ('1', 'drafts_autosave_enabled', '1'),
  352. ('1', 'drafts_show_saved_enabled', '1');
  353. ---#
  354. /******************************************************************************/
  355. --- Adding support for group-based board moderation
  356. /******************************************************************************/
  357. ---# Creating moderator_groups table
  358. CREATE TABLE IF NOT EXISTS {$db_prefix}moderator_groups (
  359. id_board smallint(5) unsigned NOT NULL default '0',
  360. id_group smallint(5) unsigned NOT NULL default '0',
  361. PRIMARY KEY (id_board, id_group)
  362. ) ENGINE=MyISAM;
  363. ---#
  364. /******************************************************************************/
  365. --- Cleaning up integration hooks
  366. /******************************************************************************/
  367. ---# Deleting integration hooks
  368. DELETE FROM {$db_prefix}settings
  369. WHERE variable LIKE 'integrate_%';
  370. ---#