upgrade_2-1_mysql.sql 34 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076
  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 logging who fulfils a group request.
  164. /******************************************************************************/
  165. ---# Adding new columns to log_group_requests
  166. ALTER TABLE {$db_prefix}log_group_requests
  167. ADD COLUMN status tinyint(3) unsigned NOT NULL default '0',
  168. ADD COLUMN id_member_acted mediumint(8) unsigned NOT NULL default '0',
  169. ADD COLUMN member_name_acted varchar(255) NOT NULL default '',
  170. ADD COLUMN time_acted int(10) unsigned NOT NULL default '0',
  171. ADD COLUMN act_reason text NOT NULL;
  172. ---#
  173. ---# Adjusting the indexes for log_group_requests
  174. ALTER TABLE {$db_prefix}log_group_requests
  175. DROP INDEX `id_member`,
  176. ADD INDEX `id_member` (`id_member`, `id_group`);
  177. ---#
  178. /******************************************************************************/
  179. --- Adding support for <credits> tag in package manager
  180. /******************************************************************************/
  181. ---# Adding new columns to log_packages ..
  182. ALTER TABLE {$db_prefix}log_packages
  183. ADD COLUMN credits varchar(255) NOT NULL DEFAULT '';
  184. ---#
  185. /******************************************************************************/
  186. --- Adding more space for session ids
  187. /******************************************************************************/
  188. ---# Altering the session_id columns...
  189. ALTER TABLE {$db_prefix}log_online
  190. CHANGE `session` `session` varchar(64) NOT NULL DEFAULT '';
  191. ALTER TABLE {$db_prefix}log_errors
  192. CHANGE `session` `session` char(64) NOT NULL default ' ';
  193. ALTER TABLE {$db_prefix}sessions
  194. CHANGE `session_id` `session_id` char(64) NOT NULL;
  195. ---#
  196. /******************************************************************************/
  197. --- Adding support for MOVED topics enhancements
  198. /******************************************************************************/
  199. ---# Adding new columns to topics ..
  200. ALTER TABLE {$db_prefix}topics
  201. ADD COLUMN redirect_expires int(10) unsigned NOT NULL default '0',
  202. ADD COLUMN id_redirect_topic mediumint(8) unsigned NOT NULL default '0';
  203. ---#
  204. /******************************************************************************/
  205. --- Adding new scheduled tasks
  206. /******************************************************************************/
  207. ---# Adding new scheduled tasks
  208. INSERT INTO {$db_prefix}scheduled_tasks
  209. (next_time, time_offset, time_regularity, time_unit, disabled, task)
  210. VALUES
  211. (0, 120, 1, 'd', 0, 'remove_temp_attachments');
  212. INSERT INTO {$db_prefix}scheduled_tasks
  213. (next_time, time_offset, time_regularity, time_unit, disabled, task)
  214. VALUES
  215. (0, 180, 1, 'd', 0, 'remove_topic_redirect');
  216. INSERT INTO {$db_prefix}scheduled_tasks
  217. (next_time, time_offset, time_regularity, time_unit, disabled, task)
  218. VALUES
  219. (0, 240, 1, 'd', 0, 'remove_old_drafts');
  220. ---#
  221. /******************************************************************************/
  222. ---- Adding background tasks support
  223. /******************************************************************************/
  224. ---# Adding the new table
  225. CREATE TABLE IF NOT EXISTS {$db_prefix}background_tasks (
  226. id_task int(10) unsigned NOT NULL auto_increment,
  227. task_file varchar(255) NOT NULL default '',
  228. task_class varchar(255) NOT NULL default '',
  229. task_data mediumtext NOT NULL,
  230. claimed_time int(10) unsigned NOT NULL default '0',
  231. PRIMARY KEY (id_task)
  232. ) ENGINE=MyISAM;
  233. ---#
  234. /******************************************************************************/
  235. ---- Replacing MSN with Skype
  236. /******************************************************************************/
  237. ---# Modifying the "msn" column...
  238. ALTER TABLE {$db_prefix}members
  239. CHANGE msn skype varchar(255) NOT NULL DEFAULT '';
  240. ---#
  241. /******************************************************************************/
  242. --- Adding support for deny boards access
  243. /******************************************************************************/
  244. ---# Adding new columns to boards...
  245. ALTER TABLE {$db_prefix}boards
  246. ADD COLUMN deny_member_groups varchar(255) NOT NULL DEFAULT '';
  247. ---#
  248. /******************************************************************************/
  249. --- Updating board access rules
  250. /******************************************************************************/
  251. ---# Updating board access rules
  252. ---{
  253. $member_groups = array(
  254. 'allowed' => array(),
  255. 'denied' => array(),
  256. );
  257. $request = $smcFunc['db_query']('', '
  258. SELECT id_group, add_deny
  259. FROM {db_prefix}permissions
  260. WHERE permission = {string:permission}',
  261. array(
  262. 'permission' => 'manage_boards',
  263. )
  264. );
  265. while ($row = $smcFunc['db_fetch_assoc']($request))
  266. $member_groups[$row['add_deny'] === '1' ? 'allowed' : 'denied'][] = $row['id_group'];
  267. $smcFunc['db_free_result']($request);
  268. $member_groups = array_diff($member_groups['allowed'], $member_groups['denied']);
  269. if (!empty($member_groups))
  270. {
  271. $count = count($member_groups);
  272. $changes = array();
  273. $request = $smcFunc['db_query']('', '
  274. SELECT id_board, member_groups
  275. FROM {db_prefix}boards');
  276. while ($row = $smcFunc['db_fetch_assoc']($request))
  277. {
  278. $current_groups = explode(',', $row['member_groups']);
  279. if (count(array_intersect($current_groups, $member_groups)) != $count)
  280. {
  281. $new_groups = array_unique(array_merge($current_groups, $member_groups));
  282. $changes[$row['id_board']] = implode(',', $new_groups);
  283. }
  284. }
  285. $smcFunc['db_free_result']($request);
  286. if (!empty($changes))
  287. {
  288. foreach ($changes as $id_board => $member_groups)
  289. $smcFunc['db_query']('', '
  290. UPDATE {db_prefix}boards
  291. SET member_groups = {string:member_groups}
  292. WHERE id_board = {int:id_board}',
  293. array(
  294. 'member_groups' => $member_groups,
  295. 'id_board' => $id_board,
  296. )
  297. );
  298. }
  299. }
  300. ---}
  301. ---#
  302. /******************************************************************************/
  303. --- Adding support for category descriptions
  304. /******************************************************************************/
  305. ---# Adding new columns to categories...
  306. ALTER TABLE {$db_prefix}categories
  307. ADD COLUMN description text NOT NULL;
  308. ---#
  309. /******************************************************************************/
  310. --- Adding support for alerts
  311. /******************************************************************************/
  312. ---# Adding the count to the members table...
  313. ALTER TABLE {$db_prefix}members
  314. ADD COLUMN alerts int(10) unsigned NOT NULL default '0';
  315. ---#
  316. ---# Adding the new table for alerts.
  317. CREATE TABLE IF NOT EXISTS {$db_prefix}user_alerts (
  318. id_alert int(10) unsigned NOT NULL auto_increment,
  319. alert_time int(10) unsigned NOT NULL default '0',
  320. id_member mediumint(10) unsigned NOT NULL default '0',
  321. id_member_started mediumint(10) unsigned NOT NULL default '0',
  322. member_name varchar(255) NOT NULL default '',
  323. content_type varchar(255) NOT NULL default '',
  324. content_id int(10) unsigned NOT NULL default '0',
  325. content_action varchar(255) NOT NULL default '',
  326. is_read tinyint(3) unsigned NOT NULL default '0',
  327. extra text NOT NULL,
  328. PRIMARY KEY (id_alert),
  329. KEY id_member (id_member),
  330. KEY alert_time (alert_time)
  331. ) ENGINE=MyISAM;
  332. ---#
  333. ---# Adding alert preferences.
  334. CREATE TABLE IF NOT EXISTS {$db_prefix}user_alerts_prefs (
  335. id_member mediumint(8) unsigned NOT NULL default '0',
  336. alert_pref varchar(32) NOT NULL default '',
  337. alert_value tinyint(3) NOT NULL default '0',
  338. PRIMARY KEY (id_member, alert_pref)
  339. ) ENGINE=MyISAM;
  340. INSERT INTO {$db_prefix}user_alerts_prefs
  341. (id_member, alert_pref, alert_value)
  342. VALUES (0, 'member_group_request', 1),
  343. (0, 'member_register', 1),
  344. (0, 'msg_like', 1),
  345. (0, 'msg_report', 1),
  346. (0, 'msg_report_reply', 1);
  347. ---#
  348. /******************************************************************************/
  349. --- Adding support for topic unwatch
  350. /******************************************************************************/
  351. ---# Adding new columns to boards...
  352. ALTER TABLE {$db_prefix}log_topics
  353. ADD COLUMN unwatched tinyint(3) NOT NULL DEFAULT '0';
  354. UPDATE {$db_prefix}log_topics
  355. SET unwatched = 0;
  356. INSERT INTO {$db_prefix}settings
  357. (variable, value)
  358. VALUES
  359. ('enable_unwatch', 0);
  360. ---#
  361. ---# Fixing column name change...
  362. ALTER TABLE {$db_prefix}log_topics
  363. CHANGE COLUMN disregarded unwatched tinyint(3) NOT NULL DEFAULT '0';
  364. ---#
  365. /******************************************************************************/
  366. --- Fixing mail queue for long messages
  367. /******************************************************************************/
  368. ---# Altering mil_queue table...
  369. ALTER TABLE {$db_prefix}mail_queue
  370. CHANGE body body mediumtext NOT NULL;
  371. ---#
  372. /******************************************************************************/
  373. --- Name changes
  374. /******************************************************************************/
  375. ---# Altering the membergroup stars to icons
  376. ALTER TABLE {$db_prefix}membergroups
  377. CHANGE `stars` `icons` varchar(255) NOT NULL DEFAULT '';
  378. ---#
  379. ---# Renaming default theme...
  380. UPDATE {$db_prefix}themes
  381. SET value = 'SMF Default Theme - Curve2'
  382. WHERE value LIKE 'SMF Default Theme%';
  383. ---#
  384. ---# Adding the enableThemes setting.
  385. INSERT INTO {$db_prefix}settings
  386. (variable, value)
  387. VALUES
  388. ('enableThemes', '1');
  389. ---#
  390. ---# Setting "default" as the default...
  391. UPDATE {$db_prefix}settings
  392. SET value = '1'
  393. WHERE variable = 'theme_guests';
  394. UPDATE {$db_prefix}boards
  395. SET id_theme = 0;
  396. UPDATE {$db_prefix}members
  397. SET id_theme = 0;
  398. ---#
  399. /******************************************************************************/
  400. --- Cleaning up after old themes...
  401. /******************************************************************************/
  402. ---# Checking for "core" and removing it if necessary...
  403. ---{
  404. // Do they have "core" installed?
  405. if (file_exists($GLOBALS['boarddir'] . '/Themes/core'))
  406. {
  407. $core_dir = $GLOBALS['boarddir'] . '/Themes/core';
  408. $theme_request = upgrade_query("
  409. SELECT id_theme
  410. FROM {$db_prefix}themes
  411. WHERE variable = 'theme_dir'
  412. AND value ='$core_dir'");
  413. // Don't do anything if this theme is already uninstalled
  414. if ($smcFunc['db_num_rows']($theme_request) == 1)
  415. {
  416. // Only one row, so no loop needed
  417. $row = $smcFunc['db_fetch_array']($theme_request);
  418. $id_theme = $row[0];
  419. $smcFunc['db_free_result']($theme_request);
  420. $known_themes = explode(', ', $modSettings['knownThemes']);
  421. // Remove this value...
  422. $known_themes = array_diff($known_themes, array($id_theme));
  423. // Change back to a string...
  424. $known_themes = implode(', ', $known_themes);
  425. // Update the database
  426. upgrade_query("
  427. REPLACE INTO {$db_prefix}settings (variable, value)
  428. VALUES ('knownThemes', '$known_themes')");
  429. // Delete any info about this theme
  430. upgrade_query("
  431. DELETE FROM {$db_prefix}themes
  432. WHERE id_theme = $id_theme");
  433. }
  434. }
  435. ---}
  436. ---#
  437. /******************************************************************************/
  438. --- Adding support for drafts
  439. /******************************************************************************/
  440. ---# Creating draft table
  441. CREATE TABLE IF NOT EXISTS {$db_prefix}user_drafts (
  442. id_draft int(10) unsigned NOT NULL auto_increment,
  443. id_topic mediumint(8) unsigned NOT NULL default '0',
  444. id_board smallint(5) unsigned NOT NULL default '0',
  445. id_reply int(10) unsigned NOT NULL default '0',
  446. type tinyint(4) NOT NULL default '0',
  447. poster_time int(10) unsigned NOT NULL default '0',
  448. id_member mediumint(8) unsigned NOT NULL default '0',
  449. subject varchar(255) NOT NULL default '',
  450. smileys_enabled tinyint(4) NOT NULL default '1',
  451. body mediumtext NOT NULL,
  452. icon varchar(16) NOT NULL default 'xx',
  453. locked tinyint(4) NOT NULL default '0',
  454. is_sticky tinyint(4) NOT NULL default '0',
  455. to_list varchar(255) NOT NULL default '',
  456. PRIMARY KEY id_draft(id_draft),
  457. UNIQUE id_member (id_member, id_draft, type)
  458. ) ENGINE=MyISAM{$db_collation};
  459. ---#
  460. ---# Adding draft permissions...
  461. ---{
  462. // We cannot do this twice
  463. if (@$modSettings['smfVersion'] < '2.1')
  464. {
  465. // Anyone who can currently post unapproved topics we assume can create drafts as well ...
  466. $request = upgrade_query("
  467. SELECT id_group, id_board, add_deny, permission
  468. FROM {$db_prefix}board_permissions
  469. WHERE permission = 'post_unapproved_topics'");
  470. $inserts = array();
  471. while ($row = $smcFunc['db_fetch_assoc']($request))
  472. {
  473. $inserts[] = "($row[id_group], $row[id_board], 'post_draft', $row[add_deny])";
  474. $inserts[] = "($row[id_group], $row[id_board], 'post_autosave_draft', $row[add_deny])";
  475. }
  476. $smcFunc['db_free_result']($request);
  477. if (!empty($inserts))
  478. upgrade_query("
  479. INSERT IGNORE INTO {$db_prefix}board_permissions
  480. (id_group, id_board, permission, add_deny)
  481. VALUES
  482. " . implode(',', $inserts));
  483. // Next we find people who can send PMs, and assume they can save pm_drafts as well
  484. $request = upgrade_query("
  485. SELECT id_group, add_deny, permission
  486. FROM {$db_prefix}permissions
  487. WHERE permission = 'pm_send'");
  488. $inserts = array();
  489. while ($row = $smcFunc['db_fetch_assoc']($request))
  490. {
  491. $inserts[] = "($row[id_group], 'pm_draft', $row[add_deny])";
  492. $inserts[] = "($row[id_group], 'pm_autosave_draft', $row[add_deny])";
  493. }
  494. $smcFunc['db_free_result']($request);
  495. if (!empty($inserts))
  496. upgrade_query("
  497. INSERT IGNORE INTO {$db_prefix}permissions
  498. (id_group, permission, add_deny)
  499. VALUES
  500. " . implode(',', $inserts));
  501. }
  502. ---}
  503. INSERT INTO {$db_prefix}settings
  504. (variable, value)
  505. VALUES
  506. ('drafts_autosave_enabled', '1'),
  507. ('drafts_show_saved_enabled', '1'),
  508. ('drafts_keep_days', '7');
  509. INSERT INTO {$db_prefix}themes
  510. (id_theme, variable, value)
  511. VALUES
  512. ('1', 'drafts_autosave_enabled', '1'),
  513. ('1', 'drafts_show_saved_enabled', '1');
  514. ---#
  515. /******************************************************************************/
  516. --- Adding support for likes
  517. /******************************************************************************/
  518. ---# Creating likes table.
  519. CREATE TABLE IF NOT EXISTS {$db_prefix}user_likes (
  520. id_member mediumint(8) unsigned NOT NULL default '0',
  521. content_type char(6) default '',
  522. content_id int(10) unsigned NOT NULL default '0',
  523. like_time int(10) unsigned NOT NULL default '0',
  524. PRIMARY KEY (content_id, content_type, id_member),
  525. INDEX content (content_id, content_type),
  526. INDEX liker (id_member)
  527. ) ENGINE=MyISAM;
  528. ---#
  529. ---# Adding count to the messages table.
  530. ALTER TABLE {$db_prefix}messages
  531. ADD COLUMN likes smallint(5) unsigned NOT NULL DEFAULT '0';
  532. ---#
  533. /******************************************************************************/
  534. --- Adding support for group-based board moderation
  535. /******************************************************************************/
  536. ---# Creating moderator_groups table
  537. CREATE TABLE IF NOT EXISTS {$db_prefix}moderator_groups (
  538. id_board smallint(5) unsigned NOT NULL default '0',
  539. id_group smallint(5) unsigned NOT NULL default '0',
  540. PRIMARY KEY (id_board, id_group)
  541. ) ENGINE=MyISAM{$db_collation};
  542. ---#
  543. /******************************************************************************/
  544. --- Cleaning up integration hooks
  545. /******************************************************************************/
  546. ---# Deleting integration hooks
  547. DELETE FROM {$db_prefix}settings
  548. WHERE variable LIKE 'integrate_%';
  549. ---#
  550. /******************************************************************************/
  551. --- Cleaning up old settings
  552. /******************************************************************************/
  553. ---# Updating the default time format
  554. ---{
  555. if (!empty($modSettings['time_format']))
  556. {
  557. // First, use the shortened form of the month in the date.
  558. $time_format = str_replace('%B', '%b', $modSettings['time_format']);
  559. // Second, shorten the time to stop including seconds.
  560. $time_format = str_replace(':%S', '', $time_format);
  561. // Then, update the database.
  562. $smcFunc['db_query']('', '
  563. UPDATE {db_prefix}settings
  564. SET value = {string:new_format}
  565. WHERE variable = {literal:time_format}',
  566. array(
  567. 'new_format' => $time_format,
  568. )
  569. );
  570. }
  571. ---}
  572. ---#
  573. ---# Fixing a deprecated option.
  574. UPDATE {$db_prefix}settings
  575. SET value = 'option_css_resize'
  576. WHERE variable = 'avatar_action_too_large'
  577. AND (value = 'option_html_resize' OR value = 'option_js_resize');
  578. ---#
  579. ---# Cleaning up old settings.
  580. DELETE FROM {$db_prefix}settings
  581. WHERE variable IN ('enableStickyTopics', 'guest_hideContacts', 'notify_new_registration', 'attachmentEncryptFilenames', 'hotTopicPosts', 'hotTopicVeryPosts', 'fixLongWords');
  582. ---#
  583. ---# Cleaning up old theme settings.
  584. DELETE FROM {$db_prefix}themes
  585. WHERE variable IN ('show_board_desc', 'no_new_reply_warning', 'display_quick_reply', 'show_mark_read');
  586. ---#
  587. /******************************************************************************/
  588. --- Updating files that fetched from simplemachines.org
  589. /******************************************************************************/
  590. ---# We no longer call on several files.
  591. DELETE FROM {$db_prefix}admin_info_files
  592. WHERE filename IN ('latest-packages.js', 'latest-support.js', 'latest-themes.js')
  593. AND path = '/smf/';
  594. ---#
  595. ---# But we do need new files.
  596. ---{
  597. $smcFunc['db_insert']('',
  598. '{db_prefix}admin_info_files',
  599. array('filename' => 'string', 'path' => 'string', 'parameters' => 'string', 'data' => 'string', 'filetype' => 'string'),
  600. array('latest-versions.txt', '/smf/', 'version=%3$s', '', 'text/plain'),
  601. array('id_file')
  602. );
  603. ---}
  604. ---#
  605. /******************************************************************************/
  606. --- Upgrading "verification questions" feature
  607. /******************************************************************************/
  608. ---# Creating qanda table
  609. CREATE TABLE IF NOT EXISTS {$db_prefix}qanda (
  610. id_question smallint(5) unsigned NOT NULL auto_increment,
  611. lngfile varchar(255) NOT NULL default '',
  612. question varchar(255) NOT NULL default '',
  613. answers text NOT NULL,
  614. PRIMARY KEY (id_question),
  615. KEY lngfile (lngfile)
  616. ) ENGINE=MyISAM{$db_collation};
  617. ---#
  618. ---# Moving questions and answers to the new table
  619. ---{
  620. $questions = array();
  621. $get_questions = upgrade_query("
  622. SELECT body AS question, recipient_name AS answer
  623. FROM {$db_prefix}log_comments
  624. WHERE comment_type = 'ver_test'");
  625. while ($row = $smcFunc['db_fetch_assoc']($get_questions))
  626. $questions[] = array($language, $row['question'], serialize(array($row['answer'])));
  627. $smcFunc['db_free_result']($get_questions);
  628. if (!empty($questions))
  629. {
  630. $smcFunc['db_insert']('',
  631. '{db_prefix}qanda',
  632. array('lngfile' => 'string', 'question' => 'string', 'answers' => 'string'),
  633. $questions,
  634. array('id_question')
  635. );
  636. // Delete the questions from log_comments now
  637. upgrade_query("
  638. DELETE FROM {$db_prefix}log_comments
  639. WHERE comment_type = 'ver_test'
  640. ");
  641. }
  642. ---}
  643. ---#
  644. /******************************************************************************/
  645. --- Marking packages as uninstalled...
  646. /******************************************************************************/
  647. ---# Updating log_packages
  648. UPDATE {$db_prefix}log_packages
  649. SET install_state = 0;
  650. ---#
  651. /******************************************************************************/
  652. --- Updating profile permissions...
  653. /******************************************************************************/
  654. ---# Removing the old "view your own profile" permission
  655. DELETE FROM {$db_prefix}permissions
  656. WHERE permission = 'profile_view_own';
  657. ---#
  658. ---# Updating the old "view any profile" permission
  659. UPDATE {$db_prefix}permissions
  660. SET permission = 'profile_view'
  661. WHERE permission = 'profile_view_any';
  662. ---#
  663. ---# Removing the old notification permissions
  664. DELETE FROM {$db_prefix}board_permissions
  665. WHERE permission = 'mark_notify' OR permission = 'mark_any_notify';
  666. ---#
  667. ---# Adding "profile_password_own"
  668. ---{
  669. $inserts = array();
  670. $request = upgrade_query("
  671. SELECT id_group, add_deny
  672. FROM {$db_prefix}permissions
  673. WHERE permission = 'profile_identity_own'");
  674. while ($row = $smcFunc['db_fetch_assoc']($request))
  675. {
  676. $inserts[] = "($row[id_group], 'profile_password_own', $row[add_deny])";
  677. }
  678. $smcFunc['db_free_result']($request);
  679. if (!empty($inserts))
  680. {
  681. upgrade_query("
  682. INSERT INTO {$db_prefix}permissions
  683. (id_group, permission, add_deny)
  684. VALUES
  685. " . implode(',', $inserts)
  686. );
  687. }
  688. ---}
  689. ---#
  690. ---# Adding other profile permissions
  691. ---{
  692. $inserts = array();
  693. $request = upgrade_query("
  694. SELECT id_group, add_deny
  695. FROM {$db_prefix}permissions
  696. WHERE permission = 'profile_extra_own'");
  697. while ($row = $smcFunc['db_fetch_assoc']($request))
  698. {
  699. $inserts[] = "($row[id_group], 'profile_blurb_own', $row[add_deny])";
  700. $inserts[] = "($row[id_group], 'profile_displayed_name_own', $row[add_deny])";
  701. $inserts[] = "($row[id_group], 'profile_forum_own', $row[add_deny])";
  702. $inserts[] = "($row[id_group], 'profile_other_own', $row[add_deny])";
  703. $inserts[] = "($row[id_group], 'profile_signature_own', $row[add_deny])";
  704. }
  705. $smcFunc['db_free_result']($request);
  706. if (!empty($inserts))
  707. {
  708. upgrade_query("
  709. INSERT INTO {$db_prefix}permissions
  710. (id_group, permission, add_deny)
  711. VALUES
  712. " . implode(',', $inserts)
  713. );
  714. }
  715. ---}
  716. ---#
  717. /******************************************************************************/
  718. --- Upgrading PM labels...
  719. /******************************************************************************/
  720. ---# Adding pm_labels table...
  721. CREATE TABLE IF NOT EXISTS {$db_prefix}pm_labels (
  722. id_label int(10) unsigned NOT NULL auto_increment,
  723. id_member mediumint(8) unsigned NOT NULL default '0',
  724. name varchar(30) NOT NULL default '',
  725. PRIMARY KEY (id_label)
  726. ) ENGINE=MyISAM;
  727. ---#
  728. ---# Adding pm_labeled_messages table...
  729. CREATE TABLE IF NOT EXISTS {$db_prefix}pm_labeled_messages (
  730. id_label int(10) unsigned NOT NULL default '0',
  731. id_pm int(10) unsigned NOT NULL default '0',
  732. PRIMARY KEY (id_label, id_pm)
  733. ) ENGINE=MyISAM;
  734. ---#
  735. ---# Adding "in_inbox" column to pm_recipients
  736. ALTER TABLE {$db_prefix}pm_recipients
  737. ADD COLUMN in_inbox tinyint(3) NOT NULL default '1';
  738. ---#
  739. ---# Moving label info to new tables and updating rules...
  740. ---{
  741. // First see if we still have a message_labels column
  742. $results = $smcFunc['db_list_columns']('{db_prefix}members');
  743. if (in_array('message_labels', $results))
  744. {
  745. // They've still got it, so pull the label info
  746. $get_labels = $smcFunc['db_query']('', '
  747. SELECT id_member, message_labels
  748. FROM {db_prefix}members
  749. WHERE message_labels != {string:blank}',
  750. array(
  751. 'blank' => '',
  752. )
  753. );
  754. $inserts = array();
  755. $label_info = array();
  756. while ($row = $smcFunc['db_fetch_assoc']($get_labels))
  757. {
  758. // Stick this in an array
  759. $labels = explode(',', $row['message_labels']);
  760. // Build some inserts
  761. foreach ($labels AS $index => $label)
  762. {
  763. // Keep track of the index of this label - we'll need that in a bit...
  764. $label_info[$row['id_member']][$label] = $index;
  765. $inserts[] = array($row['id_member'], $label);
  766. }
  767. }
  768. $smcFunc['db_free_result']($get_labels);
  769. if (!empty($inserts))
  770. {
  771. $smcFunc['db_insert']('', '{db_prefix}pm_labels', array('id_member' => 'int', 'name' => 'string-30'), $inserts, array());
  772. // Clear this out for our next query below
  773. $inserts = array();
  774. }
  775. // This is the easy part - update the inbox stuff
  776. $smcFunc['db_query']('', '
  777. UPDATE {db_prefix}pm_recipients
  778. SET in_inbox = {int:in_inbox}
  779. WHERE FIND_IN_SET({int:minusone}, labels)',
  780. array(
  781. 'in_inbox' => 1,
  782. 'minusone' => -1,
  783. )
  784. );
  785. // Now we go pull the new IDs for each label
  786. $get_new_label_ids = $smcFunc['db_query']('', '
  787. SELECT *
  788. FROM {db_prefix}pm_labels',
  789. array(
  790. )
  791. );
  792. $label_info_2 = array();
  793. while ($label_row = $smcFunc['db_fetch_assoc']($get_new_label_ids))
  794. {
  795. // Map the old index values to the new ID values...
  796. $old_index = $label_info[$row['id_member']][$row['label_name']];
  797. $label_info_2[$row['id_member']][$old_index] = $row['id_label'];
  798. }
  799. $smcFunc['db_free_result']($get_new_label_ids);
  800. // Pull label info from pm_recipients
  801. // Ignore any that are only in the inbox
  802. $get_pm_labels = $smcFunc['db_query']('', '
  803. SELECT id_pm, id_member, labels
  804. FROM {db_prefix}pm_recipients
  805. WHERE deleted = {int:not_deleted}
  806. AND labels != {string:minus_one}',
  807. array(
  808. 'not_deleted' => 0,
  809. 'minus_one' => -1,
  810. )
  811. );
  812. while ($row = $smcFunc['db_fetch_assoc']($get_pm_labels))
  813. {
  814. $labels = explode(',', $row['labels']);
  815. foreach ($labels as $a_label)
  816. {
  817. if ($a_label == '-1')
  818. continue;
  819. $new_label_info = $label_info_2[$row['id_member']][$a_label];
  820. $inserts[] = array($row['id_pm'], $new_label_info);
  821. }
  822. }
  823. $smcFunc['db_free_result']($get_pm_labels);
  824. // Insert the new data
  825. if (!empty($inserts))
  826. {
  827. $smcFunc['db_insert']('', '{db_prefix}pm_labeled_messages', array('id_pm' => 'int', 'id_label' => 'int'), $inserts, array());
  828. }
  829. // Final step of this ridiculously massive process
  830. $get_pm_rules = $smcFunc['db_query']('', '
  831. SELECT id_member, id_rule, actions
  832. FROM {db_prefix}pm_rules',
  833. array(
  834. )
  835. );
  836. // Go through the rules, unserialize the actions, then figure out if there's anything we can use
  837. while ($row = $smcFunc['db_fetch_assoc']($get_pm_rules))
  838. {
  839. // Turn this into an array...
  840. $actions = unserialize($row['actions']);
  841. // Loop through the actions and see if we're applying a label anywhere
  842. foreach ($actions as $index => $action)
  843. {
  844. if ($action['t'] == 'lab')
  845. {
  846. // Update the value of this label...
  847. $actions[$index]['v'] = $label_info_2[$row['id_member']][$action['v']];
  848. }
  849. }
  850. // Put this back into a string
  851. $actions = serialize($actions);
  852. $smcFunc['db_query']('', '
  853. UPDATE {db_prefix}pm_rules
  854. SET actions = {string:actions}
  855. WHERE id_rule = {int:id_rule}',
  856. array(
  857. 'actions' => $actions,
  858. 'id_rule' => $row['id_rule'],
  859. )
  860. );
  861. }
  862. $smcFunc['db_free_result']($get_pm_rules);
  863. // Lastly, we drop the old columns
  864. $smcFunc['db_remove_column']('{db_prefix}members', 'message_labels');
  865. $smcFunc['db_remove_column']('{db_prefix}pm_recipients', 'labels');
  866. }
  867. ---}
  868. ---#
  869. /******************************************************************************/
  870. --- Adding support for edit reasons
  871. /******************************************************************************/
  872. ---# Adding "modified_reason" column to messages
  873. ALTER TABLE {$db_prefix}messages
  874. ADD COLUMN modified_reason varchar(255) NOT NULL;
  875. ---#
  876. /******************************************************************************/
  877. --- Cleaning up guest permissions
  878. /******************************************************************************/
  879. ---# Removing permissions guests can no longer have...
  880. ---{
  881. $illegal_board_permissions = array(
  882. 'announce_topic',
  883. 'delete_any',
  884. 'lock_any',
  885. 'make_sticky',
  886. 'merge_any',
  887. 'modify_any',
  888. 'modify_replies',
  889. 'move_any',
  890. 'poll_add_any',
  891. 'poll_edit_any',
  892. 'poll_lock_any',
  893. 'poll_remove_any',
  894. 'remove_any',
  895. 'report_any',
  896. 'split_any'
  897. );
  898. $illegal_permissions = array('calendar_edit_any', 'moderate_board', 'moderate_forum', 'send_email_to_members');
  899. $smcFunc['db_query']('', '
  900. DELETE FROM {db_prefix}board_permissions
  901. WHERE id_group = {int:guests}
  902. AND permission IN ({array_string:illegal_board_perms})',
  903. array(
  904. 'guests' => -1,
  905. 'illegal_board_perms' => $illegal_board_permissions,
  906. )
  907. );
  908. $smcFunc['db_query']('', '
  909. DELETE FROM {db_prefix}permissions
  910. WHERE id_group = {int:guests}
  911. AND permission IN ({array_string:illegal_perms})',
  912. array(
  913. 'guests' => -1,
  914. 'illegal_perms' => $illegal_permissions,
  915. )
  916. );
  917. ---}
  918. ---#
  919. /******************************************************************************/
  920. --- Adding mail queue settings
  921. /******************************************************************************/
  922. ---#
  923. ---{
  924. if (empty($modSettings['mail_limit']))
  925. {
  926. $smcFunc['db_insert']('replace',
  927. '{db_prefix}settings',
  928. array('variable' => 'string-255', 'value' => 'string'),
  929. array(
  930. array('mail_limit', '5'),
  931. array('mail_quantity', '5'),
  932. ),
  933. array('variable')
  934. );
  935. }
  936. ---}
  937. ---#