upgrade_2-1_mysql.sql 36 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987988989990991992993994995996997998999100010011002100310041005100610071008100910101011101210131014101510161017101810191020102110221023102410251026102710281029103010311032103310341035103610371038103910401041104210431044104510461047104810491050105110521053105410551056105710581059106010611062106310641065106610671068106910701071107210731074107510761077107810791080108110821083108410851086108710881089109010911092109310941095109610971098109911001101110211031104110511061107110811091110111111121113111411151116111711181119112011211122112311241125112611271128112911301131113211331134113511361137113811391140114111421143114411451146114711481149
  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. // Need to know a few things first.
  62. $custom_av_dir = !empty($modSettings['custom_avatar_dir']) ? $modSettings['custom_avatar_dir'] : $GLOBALS['boarddir'] .'/custom_avatar';
  63. // This little fellow has to cooperate...
  64. if (!is_writable($custom_av_dir))
  65. @chmod($custom_av_dir, 0777);
  66. $request = upgrade_query("
  67. SELECT MAX(id_attach)
  68. FROM {$db_prefix}attachments");
  69. list ($step_progress['total']) = $smcFunc['db_fetch_row']($request);
  70. $smcFunc['db_free_result']($request);
  71. $_GET['a'] = isset($_GET['a']) ? (int) $_GET['a'] : 0;
  72. $step_progress['name'] = 'Converting legacy attachments';
  73. $step_progress['current'] = $_GET['a'];
  74. // We may be using multiple attachment directories.
  75. if (!empty($modSettings['currentAttachmentUploadDir']) && !is_array($modSettings['attachmentUploadDir']))
  76. $modSettings['attachmentUploadDir'] = unserialize($modSettings['attachmentUploadDir']);
  77. $is_done = false;
  78. while (!$is_done)
  79. {
  80. nextSubStep($substep);
  81. $fileHash = '';
  82. $request = upgrade_query("
  83. SELECT id_attach, id_folder, filename, file_hash
  84. FROM {$db_prefix}attachments
  85. WHERE attachment_type != 1
  86. LIMIT $_GET[a], 100");
  87. // Finished?
  88. if ($smcFunc['db_num_rows']($request) == 0)
  89. $is_done = true;
  90. while ($row = $smcFunc['db_fetch_assoc']($request))
  91. {
  92. // The current folder.
  93. $currentFolder = !empty($modSettings['currentAttachmentUploadDir']) ? $modSettings['attachmentUploadDir'][$row['id_folder']] : $modSettings['attachmentUploadDir'];
  94. // Old School?
  95. if (empty($row['file_hash']))
  96. {
  97. // Remove international characters (windows-1252)
  98. // These lines should never be needed again. Still, behave.
  99. if (empty($db_character_set) || $db_character_set != 'utf8')
  100. {
  101. $row['filename'] = strtr($row['filename'],
  102. "\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",
  103. 'SZszYAAAAAACEEEEIIIINOOOOOOUUUUYaaaaaaceeeeiiiinoooooouuuuyy');
  104. $row['filename'] = strtr($row['filename'], array("\xde" => 'TH', "\xfe" =>
  105. 'th', "\xd0" => 'DH', "\xf0" => 'dh', "\xdf" => 'ss', "\x8c" => 'OE',
  106. "\x9c" => 'oe', "\xc6" => 'AE', "\xe6" => 'ae', "\xb5" => 'u'));
  107. }
  108. // Sorry, no spaces, dots, or anything else but letters allowed.
  109. $row['filename'] = preg_replace(array('/\s/', '/[^\w_\.\-]/'), array('_', ''), $row['filename']);
  110. // Create a nice hash.
  111. $fileHash = sha1(md5($row['filename'] . time()) . mt_rand());
  112. // The old file, we need to know if the filename was encrypted or not.
  113. if (file_exists($currentFolder . '/' . $row['id_attach']. '_' . strtr($row['filename'], '.', '_') . md5($row['filename'])))
  114. $oldFile = $currentFolder . '/' . $row['id_attach']. '_' . strtr($row['filename'], '.', '_') . md5($row['filename']);
  115. else if (file_exists($currentFolder . '/' . $row['filename']));
  116. $oldFile = $currentFolder . '/' . $row['filename'];
  117. // Build the new file.
  118. $newFile = $currentFolder . '/' . $row['id_attach'] . '_' . $fileHash .'.dat';
  119. }
  120. // Just rename the file.
  121. else
  122. {
  123. $oldFile = $currentFolder . '/' . $row['id_attach'] . '_' . $row['file_hash'];
  124. $newFile = $currentFolder . '/' . $row['id_attach'] . '_' . $row['file_hash'] .'.dat';
  125. }
  126. // Check if the av is an attachment
  127. if ($row['id_member'] != 0)
  128. if (rename($oldFile, $custom_av_dir . '/' . $row['filename']))
  129. upgrade_query("
  130. UPDATE {$db_prefix}attachments
  131. SET file_hash = '', attachment_type = 1
  132. WHERE id_attach = $row[id_attach]");
  133. // Just a regular attachment.
  134. else
  135. rename($oldFile, $newFile);
  136. // Only update this if it was successful and the file was using the old system.
  137. if (empty($row['file_hash']) && !empty($fileHash) && file_exists($newFile) && !file_exists($oldFile))
  138. upgrade_query("
  139. UPDATE {$db_prefix}attachments
  140. SET file_hash = '$fileHash'
  141. WHERE id_attach = $row[id_attach]");
  142. }
  143. $smcFunc['db_free_result']($request);
  144. $_GET['a'] += 100;
  145. $step_progress['current'] = $_GET['a'];
  146. }
  147. unset($_GET['a']);
  148. ---}
  149. ---#
  150. /******************************************************************************/
  151. --- Adding support for IPv6...
  152. /******************************************************************************/
  153. ---# Adding new columns to ban items...
  154. ALTER TABLE {$db_prefix}ban_items
  155. ADD COLUMN ip_low5 smallint(255) unsigned NOT NULL DEFAULT '0',
  156. ADD COLUMN ip_high5 smallint(255) unsigned NOT NULL DEFAULT '0',
  157. ADD COLUMN ip_low6 smallint(255) unsigned NOT NULL DEFAULT '0',
  158. ADD COLUMN ip_high6 smallint(255) unsigned NOT NULL DEFAULT '0',
  159. ADD COLUMN ip_low7 smallint(255) unsigned NOT NULL DEFAULT '0',
  160. ADD COLUMN ip_high7 smallint(255) unsigned NOT NULL DEFAULT '0',
  161. ADD COLUMN ip_low8 smallint(255) unsigned NOT NULL DEFAULT '0',
  162. ADD COLUMN ip_high8 smallint(255) unsigned NOT NULL DEFAULT '0';
  163. ---#
  164. ---# Changing existing columns to ban items...
  165. ALTER TABLE {$db_prefix}ban_items
  166. CHANGE ip_low1 ip_low1 smallint(255) unsigned NOT NULL DEFAULT '0',
  167. CHANGE ip_high1 ip_high1 smallint(255) unsigned NOT NULL DEFAULT '0',
  168. CHANGE ip_low2 ip_low2 smallint(255) unsigned NOT NULL DEFAULT '0',
  169. CHANGE ip_high2 ip_high2 smallint(255) unsigned NOT NULL DEFAULT '0',
  170. CHANGE ip_low3 ip_low3 smallint(255) unsigned NOT NULL DEFAULT '0',
  171. CHANGE ip_high3 ip_high3 smallint(255) unsigned NOT NULL DEFAULT '0',
  172. CHANGE ip_low4 ip_low4 smallint(255) unsigned NOT NULL DEFAULT '0',
  173. CHANGE ip_high4 ip_high4 smallint(255) unsigned NOT NULL DEFAULT '0';
  174. ---#
  175. /******************************************************************************/
  176. --- Adding support for logging who fulfils a group request.
  177. /******************************************************************************/
  178. ---# Adding new columns to log_group_requests
  179. ALTER TABLE {$db_prefix}log_group_requests
  180. ADD COLUMN status tinyint(3) unsigned NOT NULL default '0',
  181. ADD COLUMN id_member_acted mediumint(8) unsigned NOT NULL default '0',
  182. ADD COLUMN member_name_acted varchar(255) NOT NULL default '',
  183. ADD COLUMN time_acted int(10) unsigned NOT NULL default '0',
  184. ADD COLUMN act_reason text NOT NULL;
  185. ---#
  186. ---# Adjusting the indexes for log_group_requests
  187. ALTER TABLE {$db_prefix}log_group_requests
  188. DROP INDEX `id_member`,
  189. ADD INDEX `id_member` (`id_member`, `id_group`);
  190. ---#
  191. /******************************************************************************/
  192. --- Adding support for <credits> tag in package manager
  193. /******************************************************************************/
  194. ---# Adding new columns to log_packages ..
  195. ALTER TABLE {$db_prefix}log_packages
  196. ADD COLUMN credits varchar(255) NOT NULL DEFAULT '';
  197. ---#
  198. /******************************************************************************/
  199. --- Adding more space for session ids
  200. /******************************************************************************/
  201. ---# Altering the session_id columns...
  202. ALTER TABLE {$db_prefix}log_online
  203. CHANGE `session` `session` varchar(64) NOT NULL DEFAULT '';
  204. ALTER TABLE {$db_prefix}log_errors
  205. CHANGE `session` `session` char(64) NOT NULL default ' ';
  206. ALTER TABLE {$db_prefix}sessions
  207. CHANGE `session_id` `session_id` char(64) NOT NULL;
  208. ---#
  209. /******************************************************************************/
  210. --- Adding support for MOVED topics enhancements
  211. /******************************************************************************/
  212. ---# Adding new columns to topics ..
  213. ALTER TABLE {$db_prefix}topics
  214. ADD COLUMN redirect_expires int(10) unsigned NOT NULL default '0',
  215. ADD COLUMN id_redirect_topic mediumint(8) unsigned NOT NULL default '0';
  216. ---#
  217. /******************************************************************************/
  218. --- Adding new scheduled tasks
  219. /******************************************************************************/
  220. ---# Adding new scheduled tasks
  221. INSERT INTO {$db_prefix}scheduled_tasks
  222. (next_time, time_offset, time_regularity, time_unit, disabled, task)
  223. VALUES
  224. (0, 120, 1, 'd', 0, 'remove_temp_attachments');
  225. INSERT INTO {$db_prefix}scheduled_tasks
  226. (next_time, time_offset, time_regularity, time_unit, disabled, task)
  227. VALUES
  228. (0, 180, 1, 'd', 0, 'remove_topic_redirect');
  229. INSERT INTO {$db_prefix}scheduled_tasks
  230. (next_time, time_offset, time_regularity, time_unit, disabled, task)
  231. VALUES
  232. (0, 240, 1, 'd', 0, 'remove_old_drafts');
  233. ---#
  234. /******************************************************************************/
  235. ---- Adding background tasks support
  236. /******************************************************************************/
  237. ---# Adding the new table
  238. CREATE TABLE IF NOT EXISTS {$db_prefix}background_tasks (
  239. id_task int(10) unsigned NOT NULL auto_increment,
  240. task_file varchar(255) NOT NULL default '',
  241. task_class varchar(255) NOT NULL default '',
  242. task_data mediumtext NOT NULL,
  243. claimed_time int(10) unsigned NOT NULL default '0',
  244. PRIMARY KEY (id_task)
  245. ) ENGINE=MyISAM;
  246. ---#
  247. /******************************************************************************/
  248. ---- Replacing MSN with Skype
  249. /******************************************************************************/
  250. ---# Modifying the "msn" column...
  251. ALTER TABLE {$db_prefix}members
  252. CHANGE msn skype varchar(255) NOT NULL DEFAULT '';
  253. ---#
  254. /******************************************************************************/
  255. --- Adding support for deny boards access
  256. /******************************************************************************/
  257. ---# Adding new columns to boards...
  258. ALTER TABLE {$db_prefix}boards
  259. ADD COLUMN deny_member_groups varchar(255) NOT NULL DEFAULT '';
  260. ---#
  261. /******************************************************************************/
  262. --- Updating board access rules
  263. /******************************************************************************/
  264. ---# Updating board access rules
  265. ---{
  266. $member_groups = array(
  267. 'allowed' => array(),
  268. 'denied' => array(),
  269. );
  270. $request = $smcFunc['db_query']('', '
  271. SELECT id_group, add_deny
  272. FROM {db_prefix}permissions
  273. WHERE permission = {string:permission}',
  274. array(
  275. 'permission' => 'manage_boards',
  276. )
  277. );
  278. while ($row = $smcFunc['db_fetch_assoc']($request))
  279. $member_groups[$row['add_deny'] === '1' ? 'allowed' : 'denied'][] = $row['id_group'];
  280. $smcFunc['db_free_result']($request);
  281. $member_groups = array_diff($member_groups['allowed'], $member_groups['denied']);
  282. if (!empty($member_groups))
  283. {
  284. $count = count($member_groups);
  285. $changes = array();
  286. $request = $smcFunc['db_query']('', '
  287. SELECT id_board, member_groups
  288. FROM {db_prefix}boards');
  289. while ($row = $smcFunc['db_fetch_assoc']($request))
  290. {
  291. $current_groups = explode(',', $row['member_groups']);
  292. if (count(array_intersect($current_groups, $member_groups)) != $count)
  293. {
  294. $new_groups = array_unique(array_merge($current_groups, $member_groups));
  295. $changes[$row['id_board']] = implode(',', $new_groups);
  296. }
  297. }
  298. $smcFunc['db_free_result']($request);
  299. if (!empty($changes))
  300. {
  301. foreach ($changes as $id_board => $member_groups)
  302. $smcFunc['db_query']('', '
  303. UPDATE {db_prefix}boards
  304. SET member_groups = {string:member_groups}
  305. WHERE id_board = {int:id_board}',
  306. array(
  307. 'member_groups' => $member_groups,
  308. 'id_board' => $id_board,
  309. )
  310. );
  311. }
  312. }
  313. ---}
  314. ---#
  315. /******************************************************************************/
  316. --- Adding support for category descriptions
  317. /******************************************************************************/
  318. ---# Adding new columns to categories...
  319. ALTER TABLE {$db_prefix}categories
  320. ADD COLUMN description text NOT NULL;
  321. ---#
  322. /******************************************************************************/
  323. --- Adding support for alerts
  324. /******************************************************************************/
  325. ---# Adding the count to the members table...
  326. ALTER TABLE {$db_prefix}members
  327. ADD COLUMN alerts int(10) unsigned NOT NULL default '0';
  328. ---#
  329. ---# Adding the new table for alerts.
  330. CREATE TABLE IF NOT EXISTS {$db_prefix}user_alerts (
  331. id_alert int(10) unsigned NOT NULL auto_increment,
  332. alert_time int(10) unsigned NOT NULL default '0',
  333. id_member mediumint(10) unsigned NOT NULL default '0',
  334. id_member_started mediumint(10) unsigned NOT NULL default '0',
  335. member_name varchar(255) NOT NULL default '',
  336. content_type varchar(255) NOT NULL default '',
  337. content_id int(10) unsigned NOT NULL default '0',
  338. content_action varchar(255) NOT NULL default '',
  339. is_read tinyint(3) unsigned NOT NULL default '0',
  340. extra text NOT NULL,
  341. PRIMARY KEY (id_alert),
  342. KEY id_member (id_member),
  343. KEY alert_time (alert_time)
  344. ) ENGINE=MyISAM;
  345. ---#
  346. ---# Adding alert preferences.
  347. CREATE TABLE IF NOT EXISTS {$db_prefix}user_alerts_prefs (
  348. id_member mediumint(8) unsigned NOT NULL default '0',
  349. alert_pref varchar(32) NOT NULL default '',
  350. alert_value tinyint(3) NOT NULL default '0',
  351. PRIMARY KEY (id_member, alert_pref)
  352. ) ENGINE=MyISAM;
  353. INSERT INTO {$db_prefix}user_alerts_prefs
  354. (id_member, alert_pref, alert_value)
  355. VALUES (0, 'member_group_request', 1),
  356. (0, 'member_register', 1),
  357. (0, 'msg_like', 1),
  358. (0, 'msg_report', 1),
  359. (0, 'msg_report_reply', 1);
  360. ---#
  361. /******************************************************************************/
  362. --- Adding support for topic unwatch
  363. /******************************************************************************/
  364. ---# Adding new columns to boards...
  365. ALTER TABLE {$db_prefix}log_topics
  366. ADD COLUMN unwatched tinyint(3) NOT NULL DEFAULT '0';
  367. UPDATE {$db_prefix}log_topics
  368. SET unwatched = 0;
  369. INSERT INTO {$db_prefix}settings
  370. (variable, value)
  371. VALUES
  372. ('enable_unwatch', 0);
  373. ---#
  374. ---# Fixing column name change...
  375. ALTER TABLE {$db_prefix}log_topics
  376. CHANGE COLUMN disregarded unwatched tinyint(3) NOT NULL DEFAULT '0';
  377. ---#
  378. /******************************************************************************/
  379. --- Fixing mail queue for long messages
  380. /******************************************************************************/
  381. ---# Altering mil_queue table...
  382. ALTER TABLE {$db_prefix}mail_queue
  383. CHANGE body body mediumtext NOT NULL;
  384. ---#
  385. /******************************************************************************/
  386. --- Name changes
  387. /******************************************************************************/
  388. ---# Altering the membergroup stars to icons
  389. ALTER TABLE {$db_prefix}membergroups
  390. CHANGE `stars` `icons` varchar(255) NOT NULL DEFAULT '';
  391. ---#
  392. ---# Renaming default theme...
  393. UPDATE {$db_prefix}themes
  394. SET value = 'SMF Default Theme - Curve2'
  395. WHERE value LIKE 'SMF Default Theme%';
  396. ---#
  397. ---# Adding the enableThemes setting.
  398. INSERT INTO {$db_prefix}settings
  399. (variable, value)
  400. VALUES
  401. ('enableThemes', '1');
  402. ---#
  403. ---# Setting "default" as the default...
  404. UPDATE {$db_prefix}settings
  405. SET value = '1'
  406. WHERE variable = 'theme_guests';
  407. UPDATE {$db_prefix}boards
  408. SET id_theme = 0;
  409. UPDATE {$db_prefix}members
  410. SET id_theme = 0;
  411. ---#
  412. /******************************************************************************/
  413. --- Cleaning up after old themes...
  414. /******************************************************************************/
  415. ---# Checking for "core" and removing it if necessary...
  416. ---{
  417. // Do they have "core" installed?
  418. if (file_exists($GLOBALS['boarddir'] . '/Themes/core'))
  419. {
  420. $core_dir = $GLOBALS['boarddir'] . '/Themes/core';
  421. $theme_request = upgrade_query("
  422. SELECT id_theme
  423. FROM {$db_prefix}themes
  424. WHERE variable = 'theme_dir'
  425. AND value ='$core_dir'");
  426. // Don't do anything if this theme is already uninstalled
  427. if ($smcFunc['db_num_rows']($theme_request) == 1)
  428. {
  429. // Only one row, so no loop needed
  430. $row = $smcFunc['db_fetch_array']($theme_request);
  431. $id_theme = $row[0];
  432. $smcFunc['db_free_result']($theme_request);
  433. $known_themes = explode(', ', $modSettings['knownThemes']);
  434. // Remove this value...
  435. $known_themes = array_diff($known_themes, array($id_theme));
  436. // Change back to a string...
  437. $known_themes = implode(', ', $known_themes);
  438. // Update the database
  439. upgrade_query("
  440. REPLACE INTO {$db_prefix}settings (variable, value)
  441. VALUES ('knownThemes', '$known_themes')");
  442. // Delete any info about this theme
  443. upgrade_query("
  444. DELETE FROM {$db_prefix}themes
  445. WHERE id_theme = $id_theme");
  446. }
  447. }
  448. ---}
  449. ---#
  450. /******************************************************************************/
  451. --- Adding support for drafts
  452. /******************************************************************************/
  453. ---# Creating draft table
  454. CREATE TABLE IF NOT EXISTS {$db_prefix}user_drafts (
  455. id_draft int(10) unsigned NOT NULL auto_increment,
  456. id_topic mediumint(8) unsigned NOT NULL default '0',
  457. id_board smallint(5) unsigned NOT NULL default '0',
  458. id_reply int(10) unsigned NOT NULL default '0',
  459. type tinyint(4) NOT NULL default '0',
  460. poster_time int(10) unsigned NOT NULL default '0',
  461. id_member mediumint(8) unsigned NOT NULL default '0',
  462. subject varchar(255) NOT NULL default '',
  463. smileys_enabled tinyint(4) NOT NULL default '1',
  464. body mediumtext NOT NULL,
  465. icon varchar(16) NOT NULL default 'xx',
  466. locked tinyint(4) NOT NULL default '0',
  467. is_sticky tinyint(4) NOT NULL default '0',
  468. to_list varchar(255) NOT NULL default '',
  469. PRIMARY KEY id_draft(id_draft),
  470. UNIQUE id_member (id_member, id_draft, type)
  471. ) ENGINE=MyISAM{$db_collation};
  472. ---#
  473. ---# Adding draft permissions...
  474. ---{
  475. // We cannot do this twice
  476. if (@$modSettings['smfVersion'] < '2.1')
  477. {
  478. // Anyone who can currently post unapproved topics we assume can create drafts as well ...
  479. $request = upgrade_query("
  480. SELECT id_group, id_board, add_deny, permission
  481. FROM {$db_prefix}board_permissions
  482. WHERE permission = 'post_unapproved_topics'");
  483. $inserts = array();
  484. while ($row = $smcFunc['db_fetch_assoc']($request))
  485. {
  486. $inserts[] = "($row[id_group], $row[id_board], 'post_draft', $row[add_deny])";
  487. $inserts[] = "($row[id_group], $row[id_board], 'post_autosave_draft', $row[add_deny])";
  488. }
  489. $smcFunc['db_free_result']($request);
  490. if (!empty($inserts))
  491. upgrade_query("
  492. INSERT IGNORE INTO {$db_prefix}board_permissions
  493. (id_group, id_board, permission, add_deny)
  494. VALUES
  495. " . implode(',', $inserts));
  496. // Next we find people who can send PMs, and assume they can save pm_drafts as well
  497. $request = upgrade_query("
  498. SELECT id_group, add_deny, permission
  499. FROM {$db_prefix}permissions
  500. WHERE permission = 'pm_send'");
  501. $inserts = array();
  502. while ($row = $smcFunc['db_fetch_assoc']($request))
  503. {
  504. $inserts[] = "($row[id_group], 'pm_draft', $row[add_deny])";
  505. $inserts[] = "($row[id_group], 'pm_autosave_draft', $row[add_deny])";
  506. }
  507. $smcFunc['db_free_result']($request);
  508. if (!empty($inserts))
  509. upgrade_query("
  510. INSERT IGNORE INTO {$db_prefix}permissions
  511. (id_group, permission, add_deny)
  512. VALUES
  513. " . implode(',', $inserts));
  514. }
  515. ---}
  516. INSERT INTO {$db_prefix}settings
  517. (variable, value)
  518. VALUES
  519. ('drafts_autosave_enabled', '1'),
  520. ('drafts_show_saved_enabled', '1'),
  521. ('drafts_keep_days', '7');
  522. INSERT INTO {$db_prefix}themes
  523. (id_theme, variable, value)
  524. VALUES
  525. ('1', 'drafts_autosave_enabled', '1'),
  526. ('1', 'drafts_show_saved_enabled', '1');
  527. ---#
  528. /******************************************************************************/
  529. --- Adding support for likes
  530. /******************************************************************************/
  531. ---# Creating likes table.
  532. CREATE TABLE IF NOT EXISTS {$db_prefix}user_likes (
  533. id_member mediumint(8) unsigned NOT NULL default '0',
  534. content_type char(6) default '',
  535. content_id int(10) unsigned NOT NULL default '0',
  536. like_time int(10) unsigned NOT NULL default '0',
  537. PRIMARY KEY (content_id, content_type, id_member),
  538. INDEX content (content_id, content_type),
  539. INDEX liker (id_member)
  540. ) ENGINE=MyISAM;
  541. ---#
  542. ---# Adding count to the messages table.
  543. ALTER TABLE {$db_prefix}messages
  544. ADD COLUMN likes smallint(5) unsigned NOT NULL DEFAULT '0';
  545. ---#
  546. /******************************************************************************/
  547. --- Adding support for group-based board moderation
  548. /******************************************************************************/
  549. ---# Creating moderator_groups table
  550. CREATE TABLE IF NOT EXISTS {$db_prefix}moderator_groups (
  551. id_board smallint(5) unsigned NOT NULL default '0',
  552. id_group smallint(5) unsigned NOT NULL default '0',
  553. PRIMARY KEY (id_board, id_group)
  554. ) ENGINE=MyISAM{$db_collation};
  555. ---#
  556. /******************************************************************************/
  557. --- Cleaning up integration hooks
  558. /******************************************************************************/
  559. ---# Deleting integration hooks
  560. DELETE FROM {$db_prefix}settings
  561. WHERE variable LIKE 'integrate_%';
  562. ---#
  563. /******************************************************************************/
  564. --- Cleaning up old settings
  565. /******************************************************************************/
  566. ---# Updating the default time format
  567. ---{
  568. if (!empty($modSettings['time_format']))
  569. {
  570. // First, use the shortened form of the month in the date.
  571. $time_format = str_replace('%B', '%b', $modSettings['time_format']);
  572. // Second, shorten the time to stop including seconds.
  573. $time_format = str_replace(':%S', '', $time_format);
  574. // Then, update the database.
  575. $smcFunc['db_query']('', '
  576. UPDATE {db_prefix}settings
  577. SET value = {string:new_format}
  578. WHERE variable = {literal:time_format}',
  579. array(
  580. 'new_format' => $time_format,
  581. )
  582. );
  583. }
  584. ---}
  585. ---#
  586. ---# Fixing a deprecated option.
  587. UPDATE {$db_prefix}settings
  588. SET value = 'option_css_resize'
  589. WHERE variable = 'avatar_action_too_large'
  590. AND (value = 'option_html_resize' OR value = 'option_js_resize');
  591. ---#
  592. ---# Cleaning up the old Core Features page.
  593. ---{
  594. // First get the original value
  595. $request = $smcFunc['db_query']('', '
  596. SELECT value
  597. FROM {db_prefix}settings
  598. WHERE variable = {literal:admin_features}');
  599. if ($smcFunc['db_num_rows']($request) > 0 && $row = $smcFunc['db_fetch_assoc']($request))
  600. {
  601. // Some of these *should* already be set but you never know.
  602. $new_settings = array();
  603. $admin_features = explode(',', $row['value']);
  604. // Now, let's just recap something.
  605. // cd = calendar, should also have set cal_enabled already
  606. // cp = custom profile fields, which already has several fields that cover tracking
  607. // k = karma, should also have set karmaMode already
  608. // ps = paid subs, should also have set paid_enabled already
  609. // rg = reports generation, which is now permanently on
  610. // sp = spider tracking, should also have set spider_mode already
  611. // w = warning system, which will be covered with warning_settings
  612. // The rest we have to deal with manually.
  613. // Moderation log - modlog_enabled itself should be set but we have others now
  614. if (in_array('ml', $admin_features))
  615. {
  616. $new_settings[] = array('adminlog_enabled', '1');
  617. $new_settings[] = array('userlog_enabled', '1');
  618. }
  619. // Post moderation
  620. if (in_array('pm', $admin_features))
  621. {
  622. $new_settings[] = array('postmod_active', '1');
  623. }
  624. // And now actually apply it.
  625. if (!empty($new_settings))
  626. {
  627. $smcFunc['db_insert']('replace',
  628. '{db_prefix}settings',
  629. array('variable' => 'string', 'value' => 'string'),
  630. $new_settings,
  631. array('variable')
  632. );
  633. }
  634. }
  635. $smcFunc['db_free_result']($request);
  636. ---}
  637. ---#
  638. ---# Cleaning up old settings.
  639. DELETE FROM {$db_prefix}settings
  640. WHERE variable IN ('enableStickyTopics', 'guest_hideContacts', 'notify_new_registration', 'attachmentEncryptFilenames', 'hotTopicPosts', 'hotTopicVeryPosts', 'fixLongWords', 'admin_features', 'topbottomEnable');
  641. ---#
  642. ---# Cleaning up old theme settings.
  643. DELETE FROM {$db_prefix}themes
  644. WHERE variable IN ('show_board_desc', 'no_new_reply_warning', 'display_quick_reply', 'show_mark_read', 'show_member_bar', 'linktree_link');
  645. ---#
  646. /******************************************************************************/
  647. --- Updating files that fetched from simplemachines.org
  648. /******************************************************************************/
  649. ---# We no longer call on several files.
  650. DELETE FROM {$db_prefix}admin_info_files
  651. WHERE filename IN ('latest-packages.js', 'latest-support.js', 'latest-themes.js')
  652. AND path = '/smf/';
  653. ---#
  654. ---# But we do need new files.
  655. ---{
  656. $smcFunc['db_insert']('',
  657. '{db_prefix}admin_info_files',
  658. array('filename' => 'string', 'path' => 'string', 'parameters' => 'string', 'data' => 'string', 'filetype' => 'string'),
  659. array('latest-versions.txt', '/smf/', 'version=%3$s', '', 'text/plain'),
  660. array('id_file')
  661. );
  662. ---}
  663. ---#
  664. /******************************************************************************/
  665. --- Upgrading "verification questions" feature
  666. /******************************************************************************/
  667. ---# Creating qanda table
  668. CREATE TABLE IF NOT EXISTS {$db_prefix}qanda (
  669. id_question smallint(5) unsigned NOT NULL auto_increment,
  670. lngfile varchar(255) NOT NULL default '',
  671. question varchar(255) NOT NULL default '',
  672. answers text NOT NULL,
  673. PRIMARY KEY (id_question),
  674. KEY lngfile (lngfile)
  675. ) ENGINE=MyISAM{$db_collation};
  676. ---#
  677. ---# Moving questions and answers to the new table
  678. ---{
  679. $questions = array();
  680. $get_questions = upgrade_query("
  681. SELECT body AS question, recipient_name AS answer
  682. FROM {$db_prefix}log_comments
  683. WHERE comment_type = 'ver_test'");
  684. while ($row = $smcFunc['db_fetch_assoc']($get_questions))
  685. $questions[] = array($language, $row['question'], serialize(array($row['answer'])));
  686. $smcFunc['db_free_result']($get_questions);
  687. if (!empty($questions))
  688. {
  689. $smcFunc['db_insert']('',
  690. '{db_prefix}qanda',
  691. array('lngfile' => 'string', 'question' => 'string', 'answers' => 'string'),
  692. $questions,
  693. array('id_question')
  694. );
  695. // Delete the questions from log_comments now
  696. upgrade_query("
  697. DELETE FROM {$db_prefix}log_comments
  698. WHERE comment_type = 'ver_test'
  699. ");
  700. }
  701. ---}
  702. ---#
  703. /******************************************************************************/
  704. --- Marking packages as uninstalled...
  705. /******************************************************************************/
  706. ---# Updating log_packages
  707. UPDATE {$db_prefix}log_packages
  708. SET install_state = 0;
  709. ---#
  710. /******************************************************************************/
  711. --- Updating profile permissions...
  712. /******************************************************************************/
  713. ---# Removing the old "view your own profile" permission
  714. DELETE FROM {$db_prefix}permissions
  715. WHERE permission = 'profile_view_own';
  716. ---#
  717. ---# Updating the old "view any profile" permission
  718. UPDATE {$db_prefix}permissions
  719. SET permission = 'profile_view'
  720. WHERE permission = 'profile_view_any';
  721. ---#
  722. ---# Removing the old notification permissions
  723. DELETE FROM {$db_prefix}board_permissions
  724. WHERE permission = 'mark_notify' OR permission = 'mark_any_notify';
  725. ---#
  726. ---# Removing the send-topic permission
  727. DELETE FROM {$db_prefix}board_permissions
  728. WHERE permission = 'send_topic';
  729. ---#
  730. ---# Adding "profile_password_own"
  731. ---{
  732. $inserts = array();
  733. $request = upgrade_query("
  734. SELECT id_group, add_deny
  735. FROM {$db_prefix}permissions
  736. WHERE permission = 'profile_identity_own'");
  737. while ($row = $smcFunc['db_fetch_assoc']($request))
  738. {
  739. $inserts[] = "($row[id_group], 'profile_password_own', $row[add_deny])";
  740. }
  741. $smcFunc['db_free_result']($request);
  742. if (!empty($inserts))
  743. {
  744. upgrade_query("
  745. INSERT INTO {$db_prefix}permissions
  746. (id_group, permission, add_deny)
  747. VALUES
  748. " . implode(',', $inserts)
  749. );
  750. }
  751. ---}
  752. ---#
  753. ---# Adding other profile permissions
  754. ---{
  755. $inserts = array();
  756. $request = upgrade_query("
  757. SELECT id_group, add_deny
  758. FROM {$db_prefix}permissions
  759. WHERE permission = 'profile_extra_own'");
  760. while ($row = $smcFunc['db_fetch_assoc']($request))
  761. {
  762. $inserts[] = "($row[id_group], 'profile_blurb_own', $row[add_deny])";
  763. $inserts[] = "($row[id_group], 'profile_displayed_name_own', $row[add_deny])";
  764. $inserts[] = "($row[id_group], 'profile_forum_own', $row[add_deny])";
  765. $inserts[] = "($row[id_group], 'profile_other_own', $row[add_deny])";
  766. $inserts[] = "($row[id_group], 'profile_signature_own', $row[add_deny])";
  767. }
  768. $smcFunc['db_free_result']($request);
  769. if (!empty($inserts))
  770. {
  771. upgrade_query("
  772. INSERT INTO {$db_prefix}permissions
  773. (id_group, permission, add_deny)
  774. VALUES
  775. " . implode(',', $inserts)
  776. );
  777. }
  778. ---}
  779. ---#
  780. /******************************************************************************/
  781. --- Upgrading PM labels...
  782. /******************************************************************************/
  783. ---# Adding pm_labels table...
  784. CREATE TABLE IF NOT EXISTS {$db_prefix}pm_labels (
  785. id_label int(10) unsigned NOT NULL auto_increment,
  786. id_member mediumint(8) unsigned NOT NULL default '0',
  787. name varchar(30) NOT NULL default '',
  788. PRIMARY KEY (id_label)
  789. ) ENGINE=MyISAM;
  790. ---#
  791. ---# Adding pm_labeled_messages table...
  792. CREATE TABLE IF NOT EXISTS {$db_prefix}pm_labeled_messages (
  793. id_label int(10) unsigned NOT NULL default '0',
  794. id_pm int(10) unsigned NOT NULL default '0',
  795. PRIMARY KEY (id_label, id_pm)
  796. ) ENGINE=MyISAM;
  797. ---#
  798. ---# Adding "in_inbox" column to pm_recipients
  799. ALTER TABLE {$db_prefix}pm_recipients
  800. ADD COLUMN in_inbox tinyint(3) NOT NULL default '1';
  801. ---#
  802. ---# Moving label info to new tables and updating rules...
  803. ---{
  804. // First see if we still have a message_labels column
  805. $results = $smcFunc['db_list_columns']('{db_prefix}members');
  806. if (in_array('message_labels', $results))
  807. {
  808. // They've still got it, so pull the label info
  809. $get_labels = $smcFunc['db_query']('', '
  810. SELECT id_member, message_labels
  811. FROM {db_prefix}members
  812. WHERE message_labels != {string:blank}',
  813. array(
  814. 'blank' => '',
  815. )
  816. );
  817. $inserts = array();
  818. $label_info = array();
  819. while ($row = $smcFunc['db_fetch_assoc']($get_labels))
  820. {
  821. // Stick this in an array
  822. $labels = explode(',', $row['message_labels']);
  823. // Build some inserts
  824. foreach ($labels AS $index => $label)
  825. {
  826. // Keep track of the index of this label - we'll need that in a bit...
  827. $label_info[$row['id_member']][$label] = $index;
  828. $inserts[] = array($row['id_member'], $label);
  829. }
  830. }
  831. $smcFunc['db_free_result']($get_labels);
  832. if (!empty($inserts))
  833. {
  834. $smcFunc['db_insert']('', '{db_prefix}pm_labels', array('id_member' => 'int', 'name' => 'string-30'), $inserts, array());
  835. // Clear this out for our next query below
  836. $inserts = array();
  837. }
  838. // This is the easy part - update the inbox stuff
  839. $smcFunc['db_query']('', '
  840. UPDATE {db_prefix}pm_recipients
  841. SET in_inbox = {int:in_inbox}
  842. WHERE FIND_IN_SET({int:minusone}, labels)',
  843. array(
  844. 'in_inbox' => 1,
  845. 'minusone' => -1,
  846. )
  847. );
  848. // Now we go pull the new IDs for each label
  849. $get_new_label_ids = $smcFunc['db_query']('', '
  850. SELECT *
  851. FROM {db_prefix}pm_labels',
  852. array(
  853. )
  854. );
  855. $label_info_2 = array();
  856. while ($label_row = $smcFunc['db_fetch_assoc']($get_new_label_ids))
  857. {
  858. // Map the old index values to the new ID values...
  859. $old_index = $label_info[$row['id_member']][$row['label_name']];
  860. $label_info_2[$row['id_member']][$old_index] = $row['id_label'];
  861. }
  862. $smcFunc['db_free_result']($get_new_label_ids);
  863. // Pull label info from pm_recipients
  864. // Ignore any that are only in the inbox
  865. $get_pm_labels = $smcFunc['db_query']('', '
  866. SELECT id_pm, id_member, labels
  867. FROM {db_prefix}pm_recipients
  868. WHERE deleted = {int:not_deleted}
  869. AND labels != {string:minus_one}',
  870. array(
  871. 'not_deleted' => 0,
  872. 'minus_one' => -1,
  873. )
  874. );
  875. while ($row = $smcFunc['db_fetch_assoc']($get_pm_labels))
  876. {
  877. $labels = explode(',', $row['labels']);
  878. foreach ($labels as $a_label)
  879. {
  880. if ($a_label == '-1')
  881. continue;
  882. $new_label_info = $label_info_2[$row['id_member']][$a_label];
  883. $inserts[] = array($row['id_pm'], $new_label_info);
  884. }
  885. }
  886. $smcFunc['db_free_result']($get_pm_labels);
  887. // Insert the new data
  888. if (!empty($inserts))
  889. {
  890. $smcFunc['db_insert']('', '{db_prefix}pm_labeled_messages', array('id_pm' => 'int', 'id_label' => 'int'), $inserts, array());
  891. }
  892. // Final step of this ridiculously massive process
  893. $get_pm_rules = $smcFunc['db_query']('', '
  894. SELECT id_member, id_rule, actions
  895. FROM {db_prefix}pm_rules',
  896. array(
  897. )
  898. );
  899. // Go through the rules, unserialize the actions, then figure out if there's anything we can use
  900. while ($row = $smcFunc['db_fetch_assoc']($get_pm_rules))
  901. {
  902. // Turn this into an array...
  903. $actions = unserialize($row['actions']);
  904. // Loop through the actions and see if we're applying a label anywhere
  905. foreach ($actions as $index => $action)
  906. {
  907. if ($action['t'] == 'lab')
  908. {
  909. // Update the value of this label...
  910. $actions[$index]['v'] = $label_info_2[$row['id_member']][$action['v']];
  911. }
  912. }
  913. // Put this back into a string
  914. $actions = serialize($actions);
  915. $smcFunc['db_query']('', '
  916. UPDATE {db_prefix}pm_rules
  917. SET actions = {string:actions}
  918. WHERE id_rule = {int:id_rule}',
  919. array(
  920. 'actions' => $actions,
  921. 'id_rule' => $row['id_rule'],
  922. )
  923. );
  924. }
  925. $smcFunc['db_free_result']($get_pm_rules);
  926. // Lastly, we drop the old columns
  927. $smcFunc['db_remove_column']('{db_prefix}members', 'message_labels');
  928. $smcFunc['db_remove_column']('{db_prefix}pm_recipients', 'labels');
  929. }
  930. ---}
  931. ---#
  932. /******************************************************************************/
  933. --- Adding support for edit reasons
  934. /******************************************************************************/
  935. ---# Adding "modified_reason" column to messages
  936. ALTER TABLE {$db_prefix}messages
  937. ADD COLUMN modified_reason varchar(255) NOT NULL;
  938. ---#
  939. /******************************************************************************/
  940. --- Cleaning up guest permissions
  941. /******************************************************************************/
  942. ---# Removing permissions guests can no longer have...
  943. ---{
  944. $illegal_board_permissions = array(
  945. 'announce_topic',
  946. 'delete_any',
  947. 'lock_any',
  948. 'make_sticky',
  949. 'merge_any',
  950. 'modify_any',
  951. 'modify_replies',
  952. 'move_any',
  953. 'poll_add_any',
  954. 'poll_edit_any',
  955. 'poll_lock_any',
  956. 'poll_remove_any',
  957. 'remove_any',
  958. 'report_any',
  959. 'split_any'
  960. );
  961. $illegal_permissions = array('calendar_edit_any', 'moderate_board', 'moderate_forum', 'send_email_to_members');
  962. $smcFunc['db_query']('', '
  963. DELETE FROM {db_prefix}board_permissions
  964. WHERE id_group = {int:guests}
  965. AND permission IN ({array_string:illegal_board_perms})',
  966. array(
  967. 'guests' => -1,
  968. 'illegal_board_perms' => $illegal_board_permissions,
  969. )
  970. );
  971. $smcFunc['db_query']('', '
  972. DELETE FROM {db_prefix}permissions
  973. WHERE id_group = {int:guests}
  974. AND permission IN ({array_string:illegal_perms})',
  975. array(
  976. 'guests' => -1,
  977. 'illegal_perms' => $illegal_permissions,
  978. )
  979. );
  980. ---}
  981. ---#
  982. /******************************************************************************/
  983. --- Adding mail queue settings
  984. /******************************************************************************/
  985. ---#
  986. ---{
  987. if (empty($modSettings['mail_limit']))
  988. {
  989. $smcFunc['db_insert']('replace',
  990. '{db_prefix}settings',
  991. array('variable' => 'string-255', 'value' => 'string'),
  992. array(
  993. array('mail_limit', '5'),
  994. array('mail_quantity', '5'),
  995. ),
  996. array('variable')
  997. );
  998. }
  999. ---}
  1000. ---#