upgrade_2-1_mysql.sql 17 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507
  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_censored']))
  30. {
  31. $request = upgrade_query("
  32. SELECT value
  33. FROM {$db_prefix}themes
  34. WHERE variable='allow_no_censored'
  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_censored', 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 = 'theme_dir'
  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. PRIMARY KEY id_draft(id_draft),
  295. UNIQUE id_member (id_member, id_draft, type)
  296. ) ENGINE=MyISAM{$db_collation};
  297. ---#
  298. ---# Adding draft permissions...
  299. ---{
  300. // We cannot do this twice
  301. if (@$modSettings['smfVersion'] < '2.1')
  302. {
  303. // Anyone who can currently post unapproved topics we assume can create drafts as well ...
  304. $request = upgrade_query("
  305. SELECT id_group, id_board, add_deny, permission
  306. FROM {$db_prefix}board_permissions
  307. WHERE permission = 'post_unapproved_topics'");
  308. $inserts = array();
  309. while ($row = smf_mysql_fetch_assoc($request))
  310. {
  311. $inserts[] = "($row[id_group], $row[id_board], 'post_draft', $row[add_deny])";
  312. $inserts[] = "($row[id_group], $row[id_board], 'post_autosave_draft', $row[add_deny])";
  313. }
  314. smf_mysql_free_result($request);
  315. if (!empty($inserts))
  316. upgrade_query("
  317. INSERT IGNORE INTO {$db_prefix}board_permissions
  318. (id_group, id_board, permission, add_deny)
  319. VALUES
  320. " . implode(',', $inserts));
  321. // Next we find people who can send PMs, and assume they can save pm_drafts as well
  322. $request = upgrade_query("
  323. SELECT id_group, add_deny, permission
  324. FROM {$db_prefix}permissions
  325. WHERE permission = 'pm_send'");
  326. $inserts = array();
  327. while ($row = smf_mysql_fetch_assoc($request))
  328. {
  329. $inserts[] = "($row[id_group], 'pm_draft', $row[add_deny])";
  330. $inserts[] = "($row[id_group], 'pm_autosave_draft', $row[add_deny])";
  331. }
  332. smf_mysql_free_result($request);
  333. if (!empty($inserts))
  334. upgrade_query("
  335. INSERT IGNORE INTO {$db_prefix}permissions
  336. (id_group, permission, add_deny)
  337. VALUES
  338. " . implode(',', $inserts));
  339. }
  340. ---}
  341. INSERT INTO {$db_prefix}settings
  342. (variable, value)
  343. VALUES
  344. ('drafts_autosave_enabled', '1'),
  345. ('drafts_show_saved_enabled', '1'),
  346. ('drafts_keep_days', '7');
  347. INSERT INTO {$db_prefix}themes
  348. (id_theme, variable, value)
  349. VALUES
  350. ('1', 'drafts_autosave_enabled', '1'),
  351. ('1', 'drafts_show_saved_enabled', '1');
  352. ---#
  353. /******************************************************************************/
  354. --- Adding support for likes
  355. /******************************************************************************/
  356. ---# Creating likes table.
  357. CREATE TABLE IF NOT EXISTS {$db_prefix}user_likes (
  358. id_member mediumint(8) unsigned NOT NULL default '0',
  359. content_type char(6) default '',
  360. content_id int(10) unsigned NOT NULL default '0',
  361. like_time int(10) unsigned NOT NULL default '0',
  362. PRIMARY KEY (content_id, content_type, id_member),
  363. INDEX content (content_id, content_type),
  364. INDEX liker (id_member)
  365. ) ENGINE=MyISAM;
  366. ---#
  367. ---# Adding count to the messages table.
  368. ALTER TABLE {$db_prefix}messages
  369. ADD COLUMN likes smallint(5) unsigned NOT NULL DEFAULT '0';
  370. ---#
  371. /******************************************************************************/
  372. --- Adding support for group-based board moderation
  373. /******************************************************************************/
  374. ---# Creating moderator_groups table
  375. CREATE TABLE IF NOT EXISTS {$db_prefix}moderator_groups (
  376. id_board smallint(5) unsigned NOT NULL default '0',
  377. id_group smallint(5) unsigned NOT NULL default '0',
  378. PRIMARY KEY (id_board, id_group)
  379. ) ENGINE=MyISAM{$db_collation};
  380. ---#
  381. /******************************************************************************/
  382. --- Cleaning up integration hooks
  383. /******************************************************************************/
  384. ---# Deleting integration hooks
  385. DELETE FROM {$db_prefix}settings
  386. WHERE variable LIKE 'integrate_%';
  387. ---#
  388. /******************************************************************************/
  389. --- Cleaning up old settings
  390. /******************************************************************************/
  391. ---# Showing contact details to guests should never happen.
  392. DELETE FROM {$db_prefix}settings
  393. WHERE variable = 'guest_hideContacts';
  394. ---#
  395. /******************************************************************************/
  396. --- Upgrading "verification questions" feature
  397. /******************************************************************************/
  398. ---# Creating qanda table
  399. CREATE TABLE IF NOT EXISTS {$db_prefix}qanda (
  400. id_question smallint(5) unsigned NOT NULL auto_increment,
  401. lngfile varchar(255) NOT NULL default '',
  402. question varchar(255) NOT NULL default '',
  403. answers text NOT NULL,
  404. PRIMARY KEY (id_question),
  405. KEY lngfile (lngfile)
  406. ) ENGINE=MyISAM{$db_collation};
  407. ---#
  408. ---# Moving questions and answers to the new table
  409. ---{
  410. $questions = array();
  411. $get_questions = upgrade_query("
  412. SELECT body AS question, recipient_name AS answer
  413. FROM {$db_prefix}log_comments
  414. WHERE comment_type = 'ver_test'");
  415. while ($row = $smcFunc['db_fetch_assoc']($get_questions))
  416. {
  417. $questions[] = "($language, $row[question], serialize(array($row[answer])))";
  418. }
  419. $smcFunc['db_free_result']($get_questions);
  420. if (!empty($questions))
  421. {
  422. upgrade_query("
  423. INSERT INTO {$db_prefix}qanda
  424. (lngfile, question, answers)
  425. VALUES
  426. " . implode(',', $questions));
  427. // Delete the questions from log_comments now
  428. upgrade_query("
  429. DELETE FROM {$db_prefix}log_comments
  430. WHERE comment_type = 'ver_test'
  431. ");
  432. }
  433. ---}
  434. ---#
  435. /******************************************************************************/
  436. --- Marking packages as uninstalled...
  437. /******************************************************************************/
  438. ---# Updating log_packages
  439. UPDATE {$db_prefix}log_packages
  440. SET install_state = 0;
  441. ---#