upgrade_2-1_mysql.sql 30 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956
  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 = $smcFunc['db_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. ---# Adding new "topic_move_any" setting
  54. INSERT INTO {$db_prefix}settings (variable, value) VALUES ('topic_move_any', '1');
  55. ---#
  56. /******************************************************************************/
  57. --- Updating legacy attachments...
  58. /******************************************************************************/
  59. ---# Converting legacy attachments.
  60. ---{
  61. $request = upgrade_query("
  62. SELECT MAX(id_attach)
  63. FROM {$db_prefix}attachments");
  64. list ($step_progress['total']) = $smcFunc['db_fetch_row']($request);
  65. $smcFunc['db_free_result']($request);
  66. $_GET['a'] = isset($_GET['a']) ? (int) $_GET['a'] : 0;
  67. $step_progress['name'] = 'Converting legacy attachments';
  68. $step_progress['current'] = $_GET['a'];
  69. // We may be using multiple attachment directories.
  70. if (!empty($modSettings['currentAttachmentUploadDir']) && !is_array($modSettings['attachmentUploadDir']))
  71. $modSettings['attachmentUploadDir'] = unserialize($modSettings['attachmentUploadDir']);
  72. $is_done = false;
  73. while (!$is_done)
  74. {
  75. nextSubStep($substep);
  76. $fileHash = '';
  77. $request = upgrade_query("
  78. SELECT id_attach, id_folder, filename, file_hash
  79. FROM {$db_prefix}attachments
  80. WHERE attachment_type != 1
  81. LIMIT $_GET[a], 100");
  82. // Finished?
  83. if ($smcFunc['db_num_rows']($request) == 0)
  84. $is_done = true;
  85. while ($row = $smcFunc['db_fetch_assoc']($request))
  86. {
  87. // The current folder.
  88. $currentFolder = !empty($modSettings['currentAttachmentUploadDir']) ? $modSettings['attachmentUploadDir'][$row['id_folder']] : $modSettings['attachmentUploadDir'];
  89. // Old School?
  90. if (empty($row['file_hash']))
  91. {
  92. // Remove international characters (windows-1252)
  93. // These lines should never be needed again. Still, behave.
  94. if (empty($db_character_set) || $db_character_set != 'utf8')
  95. {
  96. $row['filename'] = strtr($row['filename'],
  97. "\x8a\x8e\x9a\x9e\x9f\xc0\xc1\xc2\xc3\xc4\xc5\xc7\xc8\xc9\xca\xcb\xcc\xcd\xce\xcf\xd1\xd2\xd3\xd4\xd5\xd6\xd8\xd9\xda\xdb\xdc\xdd\xe0\xe1\xe2\xe3\xe4\xe5\xe7\xe8\xe9\xea\xeb\xec\xed\xee\xef\xf1\xf2\xf3\xf4\xf5\xf6\xf8\xf9\xfa\xfb\xfc\xfd\xff",
  98. 'SZszYAAAAAACEEEEIIIINOOOOOOUUUUYaaaaaaceeeeiiiinoooooouuuuyy');
  99. $row['filename'] = strtr($row['filename'], array("\xde" => 'TH', "\xfe" =>
  100. 'th', "\xd0" => 'DH', "\xf0" => 'dh', "\xdf" => 'ss', "\x8c" => 'OE',
  101. "\x9c" => 'oe', "\xc6" => 'AE', "\xe6" => 'ae', "\xb5" => 'u'));
  102. }
  103. // Sorry, no spaces, dots, or anything else but letters allowed.
  104. $row['filename'] = preg_replace(array('/\s/', '/[^\w_\.\-]/'), array('_', ''), $row['filename']);
  105. // Create a nice hash.
  106. $fileHash = sha1(md5($row['filename'] . time()) . mt_rand());
  107. // The old file, we need to know if the filename was encrypted or not.
  108. if (file_exists($currentFolder . '/' . $row['id_attach']. '_' . strtr($row['filename'], '.', '_') . md5($row['filename'])))
  109. $oldFile = $currentFolder . '/' . $row['id_attach']. '_' . strtr($row['filename'], '.', '_') . md5($row['filename']);
  110. else if (file_exists($currentFolder . '/' . $row['filename']));
  111. $oldFile = $currentFolder . '/' . $row['filename'];
  112. // Build the new file.
  113. $newFile = $currentFolder . '/' . $row['id_attach'] . '_' . $fileHash .'.dat';
  114. }
  115. // Just rename the file.
  116. else
  117. {
  118. $oldFile = $currentFolder . '/' . $row['id_attach'] . '_' . $row['file_hash'];
  119. $newFile = $currentFolder . '/' . $row['id_attach'] . '_' . $row['file_hash'] .'.dat';
  120. }
  121. // And we try to move it.
  122. rename($oldFile, $newFile);
  123. // Only update this if it was successful and the file was using the old system.
  124. if (empty($row['file_hash']) && !empty($fileHash) && file_exists($newFile) && !file_exists($oldFile))
  125. upgrade_query("
  126. UPDATE {$db_prefix}attachments
  127. SET file_hash = '$fileHash'
  128. WHERE id_attach = $row[id_attach]");
  129. }
  130. $smcFunc['db_free_result']($request);
  131. $_GET['a'] += 100;
  132. $step_progress['current'] = $_GET['a'];
  133. }
  134. unset($_GET['a']);
  135. ---}
  136. ---#
  137. /******************************************************************************/
  138. --- Adding support for IPv6...
  139. /******************************************************************************/
  140. ---# Adding new columns to ban items...
  141. ALTER TABLE {$db_prefix}ban_items
  142. ADD COLUMN ip_low5 smallint(255) unsigned NOT NULL DEFAULT '0',
  143. ADD COLUMN ip_high5 smallint(255) unsigned NOT NULL DEFAULT '0',
  144. ADD COLUMN ip_low6 smallint(255) unsigned NOT NULL DEFAULT '0',
  145. ADD COLUMN ip_high6 smallint(255) unsigned NOT NULL DEFAULT '0',
  146. ADD COLUMN ip_low7 smallint(255) unsigned NOT NULL DEFAULT '0',
  147. ADD COLUMN ip_high7 smallint(255) unsigned NOT NULL DEFAULT '0',
  148. ADD COLUMN ip_low8 smallint(255) unsigned NOT NULL DEFAULT '0',
  149. ADD COLUMN ip_high8 smallint(255) unsigned NOT NULL DEFAULT '0';
  150. ---#
  151. ---# Changing existing columns to ban items...
  152. ALTER TABLE {$db_prefix}ban_items
  153. CHANGE ip_low1 ip_low1 smallint(255) unsigned NOT NULL DEFAULT '0',
  154. CHANGE ip_high1 ip_high1 smallint(255) unsigned NOT NULL DEFAULT '0',
  155. CHANGE ip_low2 ip_low2 smallint(255) unsigned NOT NULL DEFAULT '0',
  156. CHANGE ip_high2 ip_high2 smallint(255) unsigned NOT NULL DEFAULT '0',
  157. CHANGE ip_low3 ip_low3 smallint(255) unsigned NOT NULL DEFAULT '0',
  158. CHANGE ip_high3 ip_high3 smallint(255) unsigned NOT NULL DEFAULT '0',
  159. CHANGE ip_low4 ip_low4 smallint(255) unsigned NOT NULL DEFAULT '0',
  160. CHANGE ip_high4 ip_high4 smallint(255) unsigned NOT NULL DEFAULT '0';
  161. ---#
  162. /******************************************************************************/
  163. --- Adding support for <credits> tag in package manager
  164. /******************************************************************************/
  165. ---# Adding new columns to log_packages ..
  166. ALTER TABLE {$db_prefix}log_packages
  167. ADD COLUMN credits varchar(255) NOT NULL DEFAULT '';
  168. ---#
  169. /******************************************************************************/
  170. --- Adding more space for session ids
  171. /******************************************************************************/
  172. ---# Altering the session_id columns...
  173. ALTER TABLE {$db_prefix}log_online
  174. CHANGE `session` `session` varchar(64) NOT NULL DEFAULT '';
  175. ALTER TABLE {$db_prefix}log_errors
  176. CHANGE `session` `session` char(64) NOT NULL default ' ';
  177. ALTER TABLE {$db_prefix}sessions
  178. CHANGE `session_id` `session_id` char(64) NOT NULL;
  179. ---#
  180. /******************************************************************************/
  181. --- Adding support for MOVED topics enhancements
  182. /******************************************************************************/
  183. ---# Adding new columns to topics ..
  184. ALTER TABLE {$db_prefix}topics
  185. ADD COLUMN redirect_expires int(10) unsigned NOT NULL default '0',
  186. ADD COLUMN id_redirect_topic mediumint(8) unsigned NOT NULL default '0';
  187. ---#
  188. /******************************************************************************/
  189. --- Adding new scheduled tasks
  190. /******************************************************************************/
  191. ---# Adding new scheduled tasks
  192. INSERT INTO {$db_prefix}scheduled_tasks
  193. (next_time, time_offset, time_regularity, time_unit, disabled, task)
  194. VALUES
  195. (0, 120, 1, 'd', 0, 'remove_temp_attachments');
  196. INSERT INTO {$db_prefix}scheduled_tasks
  197. (next_time, time_offset, time_regularity, time_unit, disabled, task)
  198. VALUES
  199. (0, 180, 1, 'd', 0, 'remove_topic_redirect');
  200. INSERT INTO {$db_prefix}scheduled_tasks
  201. (next_time, time_offset, time_regularity, time_unit, disabled, task)
  202. VALUES
  203. (0, 240, 1, 'd', 0, 'remove_old_drafts');
  204. ---#
  205. /******************************************************************************/
  206. ---- Adding background tasks support
  207. /******************************************************************************/
  208. ---# Adding the new table
  209. CREATE TABLE IF NOT EXISTS {$db_prefix}background_tasks (
  210. id_task int(10) unsigned NOT NULL auto_increment,
  211. task_file varchar(255) NOT NULL default '',
  212. task_class varchar(255) NOT NULL default '',
  213. task_data mediumtext NOT NULL,
  214. claimed_time int(10) unsigned NOT NULL default '0',
  215. PRIMARY KEY (id_task)
  216. ) ENGINE=MyISAM;
  217. ---#
  218. /******************************************************************************/
  219. ---- Replacing MSN with Skype
  220. /******************************************************************************/
  221. ---# Modifying the "msn" column...
  222. ALTER TABLE {$db_prefix}members
  223. CHANGE msn skype varchar(255) NOT NULL DEFAULT '';
  224. ---#
  225. /******************************************************************************/
  226. --- Adding support for deny boards access
  227. /******************************************************************************/
  228. ---# Adding new columns to boards...
  229. ALTER TABLE {$db_prefix}boards
  230. ADD COLUMN deny_member_groups varchar(255) NOT NULL DEFAULT '';
  231. ---#
  232. /******************************************************************************/
  233. --- Adding support for category descriptions
  234. /******************************************************************************/
  235. ---# Adding new columns to categories...
  236. ALTER TABLE {$db_prefix}categories
  237. ADD COLUMN description text NOT NULL;
  238. ---#
  239. /******************************************************************************/
  240. --- Adding support for alerts
  241. /******************************************************************************/
  242. ---# Adding the count to the members table...
  243. ALTER TABLE {$db_prefix}members
  244. ADD COLUMN alerts int(10) unsigned NOT NULL default '0';
  245. ---#
  246. ---# Adding the new table for alerts.
  247. CREATE TABLE IF NOT EXISTS {$db_prefix}user_alerts (
  248. id_alert int(10) unsigned NOT NULL auto_increment,
  249. alert_time int(10) unsigned NOT NULL default '0',
  250. id_member mediumint(10) unsigned NOT NULL default '0',
  251. id_member_started mediumint(10) unsigned NOT NULL default '0',
  252. member_name varchar(255) NOT NULL default '',
  253. content_type varchar(255) NOT NULL default '',
  254. content_id int(10) unsigned NOT NULL default '0',
  255. content_action varchar(255) NOT NULL default '',
  256. is_read tinyint(3) unsigned NOT NULL default '0',
  257. extra text NOT NULL,
  258. PRIMARY KEY (id_alert),
  259. KEY id_member (id_member),
  260. KEY alert_time (alert_time)
  261. ) ENGINE=MyISAM;
  262. ---#
  263. ---# Adding alert preferences.
  264. CREATE TABLE IF NOT EXISTS {$db_prefix}user_alerts_prefs (
  265. id_member mediumint(8) unsigned NOT NULL default '0',
  266. alert_pref varchar(32) NOT NULL default '',
  267. alert_value tinyint(3) NOT NULL default '0',
  268. PRIMARY KEY (id_member, alert_pref)
  269. ) ENGINE=MyISAM;
  270. INSERT INTO {$db_prefix}user_alerts_prefs
  271. (id_member, alert_pref, alert_value)
  272. VALUES (0, 'member_group_request', 1),
  273. (0, 'member_register', 1),
  274. (0, 'msg_like', 1),
  275. (0, 'msg_report', 1),
  276. (0, 'msg_report_reply', 1);
  277. ---#
  278. /******************************************************************************/
  279. --- Adding support for topic unwatch
  280. /******************************************************************************/
  281. ---# Adding new columns to boards...
  282. ALTER TABLE {$db_prefix}log_topics
  283. ADD COLUMN unwatched tinyint(3) NOT NULL DEFAULT '0';
  284. UPDATE {$db_prefix}log_topics
  285. SET unwatched = 0;
  286. INSERT INTO {$db_prefix}settings
  287. (variable, value)
  288. VALUES
  289. ('enable_unwatch', 0);
  290. ---#
  291. ---# Fixing column name change...
  292. ALTER TABLE {$db_prefix}log_topics
  293. CHANGE COLUMN disregarded unwatched tinyint(3) NOT NULL DEFAULT '0';
  294. ---#
  295. /******************************************************************************/
  296. --- Fixing mail queue for long messages
  297. /******************************************************************************/
  298. ---# Altering mil_queue table...
  299. ALTER TABLE {$db_prefix}mail_queue
  300. CHANGE body body mediumtext NOT NULL;
  301. ---#
  302. /******************************************************************************/
  303. --- Name changes
  304. /******************************************************************************/
  305. ---# Altering the membergroup stars to icons
  306. ALTER TABLE {$db_prefix}membergroups
  307. CHANGE `stars` `icons` varchar(255) NOT NULL DEFAULT '';
  308. ---#
  309. ---# Renaming default theme...
  310. UPDATE {$db_prefix}themes
  311. SET value = 'SMF Default Theme - Curve2'
  312. WHERE value LIKE 'SMF Default Theme%';
  313. ---#
  314. ---# Adding the enableThemes setting.
  315. INSERT INTO {$db_prefix}settings
  316. (variable, value)
  317. VALUES
  318. ('enableThemes', '1');
  319. ---#
  320. ---# Setting "default" as the default...
  321. UPDATE {$db_prefix}settings
  322. SET value = '1'
  323. WHERE variable = 'theme_guests';
  324. UPDATE {$db_prefix}boards
  325. SET id_theme = 0;
  326. UPDATE {$db_prefix}members
  327. SET id_theme = 0;
  328. ---#
  329. /******************************************************************************/
  330. --- Cleaning up after old themes...
  331. /******************************************************************************/
  332. ---# Checking for "core" and removing it if necessary...
  333. ---{
  334. // Do they have "core" installed?
  335. if (file_exists($GLOBALS['boarddir'] . '/Themes/core'))
  336. {
  337. $core_dir = $GLOBALS['boarddir'] . '/Themes/core';
  338. $theme_request = upgrade_query("
  339. SELECT id_theme
  340. FROM {$db_prefix}themes
  341. WHERE variable = 'theme_dir'
  342. AND value ='$core_dir'");
  343. // Don't do anything if this theme is already uninstalled
  344. if ($smcFunc['db_num_rows']($theme_request) == 1)
  345. {
  346. // Only one row, so no loop needed
  347. $row = $smcFunc['db_fetch_array']($theme_request);
  348. $id_theme = $row[0];
  349. $smcFunc['db_free_result']($theme_request);
  350. $known_themes = explode(', ', $modSettings['knownThemes']);
  351. // Remove this value...
  352. $known_themes = array_diff($known_themes, array($id_theme));
  353. // Change back to a string...
  354. $known_themes = implode(', ', $known_themes);
  355. // Update the database
  356. upgrade_query("
  357. REPLACE INTO {$db_prefix}settings (variable, value)
  358. VALUES ('knownThemes', '$known_themes')");
  359. // Delete any info about this theme
  360. upgrade_query("
  361. DELETE FROM {$db_prefix}themes
  362. WHERE id_theme = $id_theme");
  363. }
  364. }
  365. ---}
  366. ---#
  367. /******************************************************************************/
  368. --- Adding support for drafts
  369. /******************************************************************************/
  370. ---# Creating draft table
  371. CREATE TABLE IF NOT EXISTS {$db_prefix}user_drafts (
  372. id_draft int(10) unsigned NOT NULL auto_increment,
  373. id_topic mediumint(8) unsigned NOT NULL default '0',
  374. id_board smallint(5) unsigned NOT NULL default '0',
  375. id_reply int(10) unsigned NOT NULL default '0',
  376. type tinyint(4) NOT NULL default '0',
  377. poster_time int(10) unsigned NOT NULL default '0',
  378. id_member mediumint(8) unsigned NOT NULL default '0',
  379. subject varchar(255) NOT NULL default '',
  380. smileys_enabled tinyint(4) NOT NULL default '1',
  381. body mediumtext NOT NULL,
  382. icon varchar(16) NOT NULL default 'xx',
  383. locked tinyint(4) NOT NULL default '0',
  384. is_sticky tinyint(4) NOT NULL default '0',
  385. to_list varchar(255) NOT NULL default '',
  386. PRIMARY KEY id_draft(id_draft),
  387. UNIQUE id_member (id_member, id_draft, type)
  388. ) ENGINE=MyISAM{$db_collation};
  389. ---#
  390. ---# Adding draft permissions...
  391. ---{
  392. // We cannot do this twice
  393. if (@$modSettings['smfVersion'] < '2.1')
  394. {
  395. // Anyone who can currently post unapproved topics we assume can create drafts as well ...
  396. $request = upgrade_query("
  397. SELECT id_group, id_board, add_deny, permission
  398. FROM {$db_prefix}board_permissions
  399. WHERE permission = 'post_unapproved_topics'");
  400. $inserts = array();
  401. while ($row = $smcFunc['db_fetch_assoc']($request))
  402. {
  403. $inserts[] = "($row[id_group], $row[id_board], 'post_draft', $row[add_deny])";
  404. $inserts[] = "($row[id_group], $row[id_board], 'post_autosave_draft', $row[add_deny])";
  405. }
  406. $smcFunc['db_free_result']($request);
  407. if (!empty($inserts))
  408. upgrade_query("
  409. INSERT IGNORE INTO {$db_prefix}board_permissions
  410. (id_group, id_board, permission, add_deny)
  411. VALUES
  412. " . implode(',', $inserts));
  413. // Next we find people who can send PMs, and assume they can save pm_drafts as well
  414. $request = upgrade_query("
  415. SELECT id_group, add_deny, permission
  416. FROM {$db_prefix}permissions
  417. WHERE permission = 'pm_send'");
  418. $inserts = array();
  419. while ($row = $smcFunc['db_fetch_assoc']($request))
  420. {
  421. $inserts[] = "($row[id_group], 'pm_draft', $row[add_deny])";
  422. $inserts[] = "($row[id_group], 'pm_autosave_draft', $row[add_deny])";
  423. }
  424. $smcFunc['db_free_result']($request);
  425. if (!empty($inserts))
  426. upgrade_query("
  427. INSERT IGNORE INTO {$db_prefix}permissions
  428. (id_group, permission, add_deny)
  429. VALUES
  430. " . implode(',', $inserts));
  431. }
  432. ---}
  433. INSERT INTO {$db_prefix}settings
  434. (variable, value)
  435. VALUES
  436. ('drafts_autosave_enabled', '1'),
  437. ('drafts_show_saved_enabled', '1'),
  438. ('drafts_keep_days', '7');
  439. INSERT INTO {$db_prefix}themes
  440. (id_theme, variable, value)
  441. VALUES
  442. ('1', 'drafts_autosave_enabled', '1'),
  443. ('1', 'drafts_show_saved_enabled', '1');
  444. ---#
  445. /******************************************************************************/
  446. --- Adding support for likes
  447. /******************************************************************************/
  448. ---# Creating likes table.
  449. CREATE TABLE IF NOT EXISTS {$db_prefix}user_likes (
  450. id_member mediumint(8) unsigned NOT NULL default '0',
  451. content_type char(6) default '',
  452. content_id int(10) unsigned NOT NULL default '0',
  453. like_time int(10) unsigned NOT NULL default '0',
  454. PRIMARY KEY (content_id, content_type, id_member),
  455. INDEX content (content_id, content_type),
  456. INDEX liker (id_member)
  457. ) ENGINE=MyISAM;
  458. ---#
  459. ---# Adding count to the messages table.
  460. ALTER TABLE {$db_prefix}messages
  461. ADD COLUMN likes smallint(5) unsigned NOT NULL DEFAULT '0';
  462. ---#
  463. /******************************************************************************/
  464. --- Adding support for group-based board moderation
  465. /******************************************************************************/
  466. ---# Creating moderator_groups table
  467. CREATE TABLE IF NOT EXISTS {$db_prefix}moderator_groups (
  468. id_board smallint(5) unsigned NOT NULL default '0',
  469. id_group smallint(5) unsigned NOT NULL default '0',
  470. PRIMARY KEY (id_board, id_group)
  471. ) ENGINE=MyISAM{$db_collation};
  472. ---#
  473. /******************************************************************************/
  474. --- Cleaning up integration hooks
  475. /******************************************************************************/
  476. ---# Deleting integration hooks
  477. DELETE FROM {$db_prefix}settings
  478. WHERE variable LIKE 'integrate_%';
  479. ---#
  480. /******************************************************************************/
  481. --- Cleaning up old settings
  482. /******************************************************************************/
  483. ---# Showing contact details to guests should never happen.
  484. DELETE FROM {$db_prefix}settings
  485. WHERE variable IN ('enableStickyTopics', 'guest_hideContacts', 'notify_new_registration', 'attachmentEncryptFilenames');
  486. ---#
  487. ---# Cleaning up old theme settings.
  488. DELETE FROM {$db_prefix}themes
  489. WHERE variable IN ('show_board_desc', 'no_new_reply_warning');
  490. ---#
  491. /******************************************************************************/
  492. --- Removing old Simple Machines files we do not need to fetch any more
  493. /******************************************************************************/
  494. ---# We no longer call on the latest packages list.
  495. DELETE FROM {$db_prefix}admin_info_files
  496. WHERE filename IN ('latest-packages.js', 'latest-support.js', 'latest-themes.js')
  497. AND path = '/smf/';
  498. ---#
  499. /******************************************************************************/
  500. --- Upgrading "verification questions" feature
  501. /******************************************************************************/
  502. ---# Creating qanda table
  503. CREATE TABLE IF NOT EXISTS {$db_prefix}qanda (
  504. id_question smallint(5) unsigned NOT NULL auto_increment,
  505. lngfile varchar(255) NOT NULL default '',
  506. question varchar(255) NOT NULL default '',
  507. answers text NOT NULL,
  508. PRIMARY KEY (id_question),
  509. KEY lngfile (lngfile)
  510. ) ENGINE=MyISAM{$db_collation};
  511. ---#
  512. ---# Moving questions and answers to the new table
  513. ---{
  514. $questions = array();
  515. $get_questions = upgrade_query("
  516. SELECT body AS question, recipient_name AS answer
  517. FROM {$db_prefix}log_comments
  518. WHERE comment_type = 'ver_test'");
  519. while ($row = $smcFunc['db_fetch_assoc']($get_questions))
  520. $questions[] = array($language, $row['question'], serialize(array($row['answer'])));
  521. $smcFunc['db_free_result']($get_questions);
  522. if (!empty($questions))
  523. {
  524. $smcFunc['db_insert']('',
  525. '{db_prefix}qanda',
  526. array('lngfile' => 'string', 'question' => 'string', 'answers' => 'string'),
  527. $questions,
  528. array('id_question')
  529. );
  530. // Delete the questions from log_comments now
  531. upgrade_query("
  532. DELETE FROM {$db_prefix}log_comments
  533. WHERE comment_type = 'ver_test'
  534. ");
  535. }
  536. ---}
  537. ---#
  538. /******************************************************************************/
  539. --- Marking packages as uninstalled...
  540. /******************************************************************************/
  541. ---# Updating log_packages
  542. UPDATE {$db_prefix}log_packages
  543. SET install_state = 0;
  544. ---#
  545. /******************************************************************************/
  546. --- Updating profile permissions...
  547. /******************************************************************************/
  548. ---# Removing the old "view your own profile" permission
  549. DELETE FROM {$db_prefix}permissions
  550. WHERE permission = 'profile_view_own';
  551. ---#
  552. ---# Updating the old "view any profile" permission
  553. UPDATE {$db_prefix}permissions
  554. SET permission = 'profile_view'
  555. WHERE permission = 'profile_view_any';
  556. ---#
  557. ---# Removing the old notification permissions
  558. DELETE FROM {$db_prefix}board_permissions
  559. WHERE permission = 'mark_notify' OR permission = 'mark_any_notify';
  560. ---#
  561. ---# Adding "profile_password_own"
  562. ---{
  563. $inserts = array();
  564. $request = upgrade_query("
  565. SELECT id_group, add_deny
  566. FROM {$db_prefix}permissions
  567. WHERE permission = 'profile_identity_own'");
  568. while ($row = $smcFunc['db_fetch_assoc']($request))
  569. {
  570. $inserts[] = "($row[id_group], 'profile_password_own', $row[add_deny])";
  571. }
  572. $smcFunc['db_free_result']($request);
  573. if (!empty($inserts))
  574. {
  575. upgrade_query("
  576. INSERT INTO {$db_prefix}permissions
  577. (id_group, permission, add_deny)
  578. VALUES
  579. " . implode(',', $inserts)
  580. );
  581. }
  582. ---}
  583. ---#
  584. ---# Adding other profile permissions
  585. ---{
  586. $inserts = array();
  587. $request = upgrade_query("
  588. SELECT id_group, add_deny
  589. FROM {$db_prefix}permissions
  590. WHERE permission = 'profile_extra_own'");
  591. while ($row = $smcFunc['db_fetch_assoc']($request))
  592. {
  593. $inserts[] = "($row[id_group], 'profile_blurb_own', $row[add_deny])";
  594. $inserts[] = "($row[id_group], 'profile_displayed_name_own', $row[add_deny])";
  595. $inserts[] = "($row[id_group], 'profile_forum_own', $row[add_deny])";
  596. $inserts[] = "($row[id_group], 'profile_other_own', $row[add_deny])";
  597. $inserts[] = "($row[id_group], 'profile_signature_own', $row[add_deny])";
  598. }
  599. $smcFunc['db_free_result']($request);
  600. if (!empty($inserts))
  601. {
  602. upgrade_query("
  603. INSERT INTO {$db_prefix}permissions
  604. (id_group, permission, add_deny)
  605. VALUES
  606. " . implode(',', $inserts)
  607. );
  608. }
  609. ---}
  610. ---#
  611. /******************************************************************************/
  612. --- Upgrading PM labels...
  613. /******************************************************************************/
  614. ---# Adding pm_labels table...
  615. CREATE TABLE IF NOT EXISTS {$db_prefix}pm_labels (
  616. id_label int(10) unsigned NOT NULL auto_increment,
  617. id_member mediumint(8) unsigned NOT NULL default '0',
  618. name varchar(30) NOT NULL default '',
  619. PRIMARY KEY (id_label)
  620. ) ENGINE=MyISAM;
  621. ---#
  622. ---# Adding pm_labeled_messages table...
  623. CREATE TABLE IF NOT EXISTS {$db_prefix}pm_labeled_messages (
  624. id_label int(10) unsigned NOT NULL default '0',
  625. id_pm int(10) unsigned NOT NULL default '0',
  626. PRIMARY KEY (id_label, id_pm)
  627. ) ENGINE=MyISAM;
  628. ---#
  629. ---# Adding "in_inbox" column to pm_recipients
  630. ALTER TABLE {$db_prefix}pm_recipients
  631. ADD COLUMN in_inbox tinyint(3) NOT NULL default '1';
  632. ---#
  633. ---# Moving label info to new tables and updating rules...
  634. ---{
  635. // First see if we still have a message_labels column
  636. $results = $smcFunc['db_list_columns']('{db_prefix}members');
  637. if (in_array('message_labels', $results))
  638. {
  639. // They've still got it, so pull the label info
  640. $get_labels = $smcFunc['db_query']('', '
  641. SELECT id_member, message_labels
  642. FROM {db_prefix}members
  643. WHERE message_labels != {string:blank}',
  644. array(
  645. 'blank' => '',
  646. )
  647. );
  648. $inserts = array();
  649. $label_info = array();
  650. while ($row = $smcFunc['db_fetch_assoc']($get_labels))
  651. {
  652. // Stick this in an array
  653. $labels = explode(',', $row['message_labels']);
  654. // Build some inserts
  655. foreach ($labels AS $index => $label)
  656. {
  657. // Keep track of the index of this label - we'll need that in a bit...
  658. $label_info[$row['id_member']][$label] = $index;
  659. $inserts[] = array($row['id_member'], $label);
  660. }
  661. }
  662. $smcFunc['db_free_result']($get_labels);
  663. if (!empty($inserts))
  664. {
  665. $smcFunc['db_insert']('', '{db_prefix}pm_labels', array('id_member' => 'int', 'name' => 'string-30'), $inserts, array());
  666. // Clear this out for our next query below
  667. $inserts = array();
  668. }
  669. // This is the easy part - update the inbox stuff
  670. $smcFunc['db_query']('', '
  671. UPDATE {db_prefix}pm_recipients
  672. SET in_inbox = {int:in_inbox}
  673. WHERE FIND_IN_SET({int:minusone}, labels)',
  674. array(
  675. 'in_inbox' => 1,
  676. 'minusone' => -1,
  677. )
  678. );
  679. // Now we go pull the new IDs for each label
  680. $get_new_label_ids = $smcFunc['db_query']('', '
  681. SELECT *
  682. FROM {db_prefix}pm_labels',
  683. array(
  684. )
  685. );
  686. $label_info_2 = array();
  687. while ($label_row = $smcFunc['db_fetch_assoc']($get_new_label_ids))
  688. {
  689. // Map the old index values to the new ID values...
  690. $old_index = $label_info[$row['id_member']][$row['label_name']];
  691. $label_info_2[$row['id_member']][$old_index] = $row['id_label'];
  692. }
  693. $smcFunc['db_free_result']($get_new_label_ids);
  694. // Pull label info from pm_recipients
  695. // Ignore any that are only in the inbox
  696. $get_pm_labels = $smcFunc['db_query']('', '
  697. SELECT id_pm, id_member, labels
  698. FROM {db_prefix}pm_recipients
  699. WHERE deleted = {int:not_deleted}
  700. AND labels != {string:minus_one}',
  701. array(
  702. 'not_deleted' => 0,
  703. 'minus_one' => -1,
  704. )
  705. );
  706. while ($row = $smcFunc['db_fetch_assoc']($get_pm_labels))
  707. {
  708. $labels = explode(',', $row['labels']);
  709. foreach ($labels as $a_label)
  710. {
  711. if ($a_label == '-1')
  712. continue;
  713. $new_label_info = $label_info_2[$row['id_member']][$a_label];
  714. $inserts[] = array($row['id_pm'], $new_label_info);
  715. }
  716. }
  717. $smcFunc['db_free_result']($get_pm_labels);
  718. // Insert the new data
  719. if (!empty($inserts))
  720. {
  721. $smcFunc['db_insert']('', '{db_prefix}pm_labeled_messages', array('id_pm' => 'int', 'id_label' => 'int'), $inserts, array());
  722. }
  723. // Final step of this ridiculously massive process
  724. $get_pm_rules = $smcFunc['db_query']('', '
  725. SELECT id_member, id_rule, actions
  726. FROM {db_prefix}pm_rules',
  727. array(
  728. )
  729. );
  730. // Go through the rules, unserialize the actions, then figure out if there's anything we can use
  731. while ($row = $smcFunc['db_fetch_assoc']($get_pm_rules))
  732. {
  733. // Turn this into an array...
  734. $actions = unserialize($row['actions']);
  735. // Loop through the actions and see if we're applying a label anywhere
  736. foreach ($actions as $index => $action)
  737. {
  738. if ($action['t'] == 'lab')
  739. {
  740. // Update the value of this label...
  741. $actions[$index]['v'] = $label_info_2[$row['id_member']][$action['v']];
  742. }
  743. }
  744. // Put this back into a string
  745. $actions = serialize($actions);
  746. $smcFunc['db_query']('', '
  747. UPDATE {db_prefix}pm_rules
  748. SET actions = {string:actions}
  749. WHERE id_rule = {int:id_rule}',
  750. array(
  751. 'actions' => $actions,
  752. 'id_rule' => $row['id_rule'],
  753. )
  754. );
  755. }
  756. $smcFunc['db_free_result']($get_pm_rules);
  757. // Lastly, we drop the old columns
  758. $smcFunc['db_remove_column']('{db_prefix}members', 'message_labels');
  759. $smcFunc['db_remove_column']('{db_prefix}pm_recipients', 'labels');
  760. }
  761. ---}
  762. ---#
  763. /******************************************************************************/
  764. --- Adding support for edit reasons
  765. /******************************************************************************/
  766. ---# Adding "modified_reason" column to messages
  767. ALTER TABLE {$db_prefix}messages
  768. ADD COLUMN modified_reason varchar(255) NOT NULL;
  769. ---#
  770. /******************************************************************************/
  771. --- Cleaning up guest permissions
  772. /******************************************************************************/
  773. ---# Removing permissions guests can no longer have...
  774. ---{
  775. $illegal_board_permissions = array(
  776. 'announce_topic',
  777. 'delete_any',
  778. 'lock_any',
  779. 'make_sticky',
  780. 'merge_any',
  781. 'modify_any',
  782. 'modify_replies',
  783. 'move_any',
  784. 'poll_add_any',
  785. 'poll_edit_any',
  786. 'poll_lock_any',
  787. 'poll_remove_any',
  788. 'remove_any',
  789. 'report_any',
  790. 'split_any'
  791. );
  792. $illegal_permissions = array('calendar_edit_any', 'moderate_board', 'moderate_forum', 'send_email_to_members');
  793. $smcFunc['db_query']('', '
  794. DELETE FROM {db_prefix}board_permissions
  795. WHERE id_group = {int:guests}
  796. AND permission IN ({array_string:illegal_board_perms})',
  797. array(
  798. 'guests' => -1,
  799. 'illegal_board_perms' => $illegal_board_permissions,
  800. )
  801. );
  802. $smcFunc['db_query']('', '
  803. DELETE FROM {db_prefix}permissions
  804. WHERE id_group = {int:guests}
  805. AND permission IN ({array_string:illegal_perms})',
  806. array(
  807. 'guests' => -1,
  808. 'illegal_perms' => $illegal_permissions,
  809. )
  810. );
  811. ---}
  812. ---#
  813. /******************************************************************************/
  814. --- Adding mail queue settings
  815. /******************************************************************************/
  816. ---#
  817. ---{
  818. if (empty($modSettings['mail_limit']))
  819. {
  820. $smcFunc['db_insert']('replace',
  821. '{db_prefix}settings',
  822. array('variable' => 'string-255', 'value' => 'string'),
  823. array(
  824. array('mail_limit', '5'),
  825. array('mail_quantity', '5'),
  826. ),
  827. array('variable')
  828. );
  829. }
  830. ---}
  831. ---#