upgrade_2-1_mysql.sql 24 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770
  1. /* ATTENTION: You don't need to run or use this file! The upgrade.php script does everything for you! */
  2. /******************************************************************************/
  3. --- Adding new settings...
  4. /******************************************************************************/
  5. ---# Adding login history...
  6. CREATE TABLE IF NOT EXISTS {$db_prefix}member_logins (
  7. id_login int(10) NOT NULL auto_increment,
  8. id_member mediumint(8) NOT NULL,
  9. time int(10) NOT NULL,
  10. ip varchar(255) NOT NULL default '',
  11. ip2 varchar(255) NOT NULL default '',
  12. PRIMARY KEY id_login(id_login),
  13. KEY id_member (id_member),
  14. KEY time (time)
  15. ) ENGINE=MyISAM{$db_collation};
  16. ---#
  17. ---# Copying the current package backup setting...
  18. ---{
  19. if (!isset($modSettings['package_make_full_backups']) && isset($modSettings['package_make_backups']))
  20. upgrade_query("
  21. INSERT INTO {$db_prefix}settings
  22. (variable, value)
  23. VALUES
  24. ('package_make_full_backups', '" . $modSettings['package_make_backups'] . "')");
  25. ---}
  26. ---#
  27. ---# Copying the current "allow users to disable word censor" setting...
  28. ---{
  29. if (!isset($modSettings['allow_no_censored']))
  30. {
  31. $request = upgrade_query("
  32. SELECT value
  33. FROM {$db_prefix}themes
  34. WHERE variable='allow_no_censored'
  35. AND id_theme = 1 OR id_theme = '$modSettings[theme_default]'
  36. ");
  37. // Is it set for either "default" or the one they've set as default?
  38. while ($row = mysql_fetch_assoc($request))
  39. {
  40. if ($row['value'] == 1)
  41. {
  42. upgrade_query("
  43. INSERT INTO {$db_prefix}settings
  44. VALUES ('allow_no_censored', 1)
  45. ");
  46. // Don't do this twice...
  47. break;
  48. }
  49. }
  50. }
  51. ---}
  52. ---#
  53. /******************************************************************************/
  54. --- Updating legacy attachments...
  55. /******************************************************************************/
  56. ---# Converting legacy attachments.
  57. ---{
  58. $request = upgrade_query("
  59. SELECT MAX(id_attach)
  60. FROM {$db_prefix}attachments");
  61. list ($step_progress['total']) = $smcFunc['db_fetch_row']($request);
  62. $smcFunc['db_free_result']($request);
  63. $_GET['a'] = isset($_GET['a']) ? (int) $_GET['a'] : 0;
  64. $step_progress['name'] = 'Converting legacy attachments';
  65. $step_progress['current'] = $_GET['a'];
  66. // We may be using multiple attachment directories.
  67. if (!empty($modSettings['currentAttachmentUploadDir']) && !is_array($modSettings['attachmentUploadDir']))
  68. $modSettings['attachmentUploadDir'] = unserialize($modSettings['attachmentUploadDir']);
  69. $is_done = false;
  70. while (!$is_done)
  71. {
  72. nextSubStep($substep);
  73. $request = upgrade_query("
  74. SELECT id_attach, id_folder, filename, file_hash
  75. FROM {$db_prefix}attachments
  76. WHERE file_hash = ''
  77. LIMIT $_GET[a], 100");
  78. // Finished?
  79. if ($smcFunc['db_num_rows']($request) == 0)
  80. $is_done = true;
  81. while ($row = $smcFunc['db_fetch_assoc']($request))
  82. {
  83. // The current folder.
  84. $current_folder = !empty($modSettings['currentAttachmentUploadDir']) ? $modSettings['attachmentUploadDir'][$row['id_folder']] : $modSettings['attachmentUploadDir'];
  85. // The old location of the file.
  86. $old_location = getLegacyAttachmentFilename($row['filename'], $row['id_attach'], $row['id_folder']);
  87. // The new file name.
  88. $file_hash = getAttachmentFilename($row['filename'], $row['id_attach'], $row['id_folder'], true);
  89. // And we try to move it.
  90. rename($old_location, $current_folder . '/' . $row['id_attach'] . '_' . $file_hash);
  91. // Only update thif if it was successful.
  92. if (file_exists($current_folder . '/' . $row['id_attach'] . '_' . $file_hash) && !file_exists($old_location))
  93. upgrade_query("
  94. UPDATE {$db_prefix}attachments
  95. SET file_hash = '$file_hash'
  96. WHERE id_attach = $row[id_attach]");
  97. }
  98. $smcFunc['db_free_result']($request);
  99. $_GET['a'] += 100;
  100. $step_progress['current'] = $_GET['a'];
  101. }
  102. unset($_GET['a']);
  103. ---}
  104. ---#
  105. /******************************************************************************/
  106. --- Adding support for IPv6...
  107. /******************************************************************************/
  108. ---# Adding new columns to ban items...
  109. ALTER TABLE {$db_prefix}ban_items
  110. ADD COLUMN ip_low5 smallint(255) unsigned NOT NULL DEFAULT '0',
  111. ADD COLUMN ip_high5 smallint(255) unsigned NOT NULL DEFAULT '0',
  112. ADD COLUMN ip_low6 smallint(255) unsigned NOT NULL DEFAULT '0',
  113. ADD COLUMN ip_high6 smallint(255) unsigned NOT NULL DEFAULT '0',
  114. ADD COLUMN ip_low7 smallint(255) unsigned NOT NULL DEFAULT '0',
  115. ADD COLUMN ip_high7 smallint(255) unsigned NOT NULL DEFAULT '0',
  116. ADD COLUMN ip_low8 smallint(255) unsigned NOT NULL DEFAULT '0',
  117. ADD COLUMN ip_high8 smallint(255) unsigned NOT NULL DEFAULT '0';
  118. ---#
  119. ---# Changing existing columns to ban items...
  120. ALTER TABLE {$db_prefix}ban_items
  121. CHANGE ip_low1 ip_low1 smallint(255) unsigned NOT NULL DEFAULT '0',
  122. CHANGE ip_high1 ip_high1 smallint(255) unsigned NOT NULL DEFAULT '0',
  123. CHANGE ip_low2 ip_low2 smallint(255) unsigned NOT NULL DEFAULT '0',
  124. CHANGE ip_high2 ip_high2 smallint(255) unsigned NOT NULL DEFAULT '0',
  125. CHANGE ip_low3 ip_low3 smallint(255) unsigned NOT NULL DEFAULT '0',
  126. CHANGE ip_high3 ip_high3 smallint(255) unsigned NOT NULL DEFAULT '0',
  127. CHANGE ip_low4 ip_low4 smallint(255) unsigned NOT NULL DEFAULT '0',
  128. CHANGE ip_high4 ip_high4 smallint(255) unsigned NOT NULL DEFAULT '0';
  129. ---#
  130. /******************************************************************************/
  131. --- Adding support for <credits> tag in package manager
  132. /******************************************************************************/
  133. ---# Adding new columns to log_packages ..
  134. ALTER TABLE {$db_prefix}log_packages
  135. ADD COLUMN credits varchar(255) NOT NULL DEFAULT '';
  136. ---#
  137. /******************************************************************************/
  138. --- Adding more space for session ids
  139. /******************************************************************************/
  140. ---# Altering the session_id columns...
  141. ALTER TABLE {$db_prefix}log_online
  142. CHANGE `session` `session` varchar(64) NOT NULL DEFAULT '';
  143. ALTER TABLE {$db_prefix}log_errors
  144. CHANGE `session` `session` char(64) NOT NULL default ' ';
  145. ALTER TABLE {$db_prefix}sessions
  146. CHANGE `session_id` `session_id` char(64) NOT NULL;
  147. ---#
  148. /******************************************************************************/
  149. --- Adding support for MOVED topics enhancements
  150. /******************************************************************************/
  151. ---# Adding new columns to topics ..
  152. ALTER TABLE {$db_prefix}topics
  153. ADD COLUMN redirect_expires int(10) unsigned NOT NULL default '0',
  154. ADD COLUMN id_redirect_topic mediumint(8) unsigned NOT NULL default '0';
  155. ---#
  156. /******************************************************************************/
  157. --- Adding new scheduled tasks
  158. /******************************************************************************/
  159. ---# Adding new scheduled tasks
  160. INSERT INTO {$db_prefix}scheduled_tasks
  161. (next_time, time_offset, time_regularity, time_unit, disabled, task)
  162. VALUES
  163. (0, 120, 1, 'd', 0, 'remove_temp_attachments');
  164. INSERT INTO {$db_prefix}scheduled_tasks
  165. (next_time, time_offset, time_regularity, time_unit, disabled, task)
  166. VALUES
  167. (0, 180, 1, 'd', 0, 'remove_topic_redirect');
  168. INSERT INTO {$db_prefix}scheduled_tasks
  169. (next_time, time_offset, time_regularity, time_unit, disabled, task)
  170. VALUES
  171. (0, 240, 1, 'd', 0, 'remove_old_drafts');
  172. ---#
  173. /******************************************************************************/
  174. ---- Replacing MSN with Skype
  175. /******************************************************************************/
  176. ---# Modifying the "msn" column...
  177. ALTER TABLE {$db_prefix}members
  178. CHANGE msn skype varchar(255) NOT NULL DEFAULT '';
  179. ---#
  180. /******************************************************************************/
  181. --- Adding support for deny boards access
  182. /******************************************************************************/
  183. ---# Adding new columns to boards...
  184. ALTER TABLE {$db_prefix}boards
  185. ADD COLUMN deny_member_groups varchar(255) NOT NULL DEFAULT '';
  186. ---#
  187. /******************************************************************************/
  188. --- Adding support for topic unwatch
  189. /******************************************************************************/
  190. ---# Adding new columns to boards...
  191. ALTER TABLE {$db_prefix}log_topics
  192. ADD COLUMN unwatched tinyint(3) NOT NULL DEFAULT '0';
  193. UPDATE {$db_prefix}log_topics
  194. SET unwatched = 0;
  195. INSERT INTO {$db_prefix}settings
  196. (variable, value)
  197. VALUES
  198. ('enable_unwatch', 0);
  199. ---#
  200. ---# Fixing column name change...
  201. ALTER TABLE {$db_prefix}log_topics
  202. CHANGE COLUMN disregarded unwatched tinyint(3) NOT NULL DEFAULT '0';
  203. ---#
  204. /******************************************************************************/
  205. --- Fixing mail queue for long messages
  206. /******************************************************************************/
  207. ---# Altering mil_queue table...
  208. ALTER TABLE {$db_prefix}mail_queue
  209. CHANGE body body mediumtext NOT NULL;
  210. ---#
  211. /******************************************************************************/
  212. --- Name changes
  213. /******************************************************************************/
  214. ---# Altering the membergroup stars to icons
  215. ALTER TABLE {$db_prefix}membergroups
  216. CHANGE `stars` `icons` varchar(255) NOT NULL DEFAULT '';
  217. ---#
  218. ---# Renaming default theme...
  219. UPDATE {$db_prefix}themes
  220. SET value = 'SMF Default Theme - Curve2'
  221. WHERE value LIKE 'SMF Default Theme%';
  222. ---#
  223. /******************************************************************************/
  224. --- Cleaning up after old themes...
  225. /******************************************************************************/
  226. ---# Checking for "core" and removing it if necessary...
  227. ---{
  228. // Do they have "core" installed?
  229. if (file_exists($GLOBALS['boarddir'] . '/Themes/core'))
  230. {
  231. $core_dir = $GLOBALS['boarddir'] . '/Themes/core';
  232. $theme_request = upgrade_query("
  233. SELECT id_theme
  234. FROM {$db_prefix}themes
  235. WHERE variable = 'theme_dir'
  236. AND value ='$core_dir'");
  237. // Don't do anything if this theme is already uninstalled
  238. if (smf_mysql_num_rows($theme_request) == 1)
  239. {
  240. $id_theme = mysql_result($theme_request, 0);
  241. mysql_free_result($theme_request);
  242. $known_themes = explode(', ', $modSettings['knownThemes']);
  243. // Remove this value...
  244. $known_themes = array_diff($known_themes, array($id_theme));
  245. // Change back to a string...
  246. $known_themes = implode(', ', $known_themes);
  247. // Update the database
  248. upgrade_query("
  249. REPLACE INTO {$db_prefix}settings (variable, value)
  250. VALUES ('knownThemes', '$known_themes')");
  251. // Delete any info about this theme
  252. upgrade_query("
  253. DELETE FROM {$db_prefix}themes
  254. WHERE id_theme = $id_theme");
  255. // Set any members or boards using this theme to the default
  256. upgrade_query("
  257. UPDATE {$db_prefix}members
  258. SET id_theme = 0
  259. WHERE id_theme = $id_theme");
  260. upgrade_query("
  261. UPDATE {$db_prefix}boards
  262. SET id_theme = 0
  263. WHERE id_theme = $id_theme");
  264. if ($modSettings['theme_guests'] == $id_theme)
  265. {
  266. upgrade_query("
  267. REPLACE INTO {$db_prefix}settings
  268. (variable, value)
  269. VALUES('theme_guests', 0)");
  270. }
  271. }
  272. }
  273. ---}
  274. ---#
  275. /******************************************************************************/
  276. --- Adding support for drafts
  277. /******************************************************************************/
  278. ---# Creating draft table
  279. CREATE TABLE IF NOT EXISTS {$db_prefix}user_drafts (
  280. id_draft int(10) unsigned NOT NULL auto_increment,
  281. id_topic mediumint(8) unsigned NOT NULL default '0',
  282. id_board smallint(5) unsigned NOT NULL default '0',
  283. id_reply int(10) unsigned NOT NULL default '0',
  284. type tinyint(4) NOT NULL default '0',
  285. poster_time int(10) unsigned NOT NULL default '0',
  286. id_member mediumint(8) unsigned NOT NULL default '0',
  287. subject varchar(255) NOT NULL default '',
  288. smileys_enabled tinyint(4) NOT NULL default '1',
  289. body mediumtext NOT NULL,
  290. icon varchar(16) NOT NULL default 'xx',
  291. locked tinyint(4) NOT NULL default '0',
  292. is_sticky tinyint(4) NOT NULL default '0',
  293. to_list varchar(255) NOT NULL default '',
  294. PRIMARY KEY id_draft(id_draft),
  295. UNIQUE id_member (id_member, id_draft, type)
  296. ) ENGINE=MyISAM{$db_collation};
  297. ---#
  298. ---# Adding draft permissions...
  299. ---{
  300. // We cannot do this twice
  301. if (@$modSettings['smfVersion'] < '2.1')
  302. {
  303. // Anyone who can currently post unapproved topics we assume can create drafts as well ...
  304. $request = upgrade_query("
  305. SELECT id_group, id_board, add_deny, permission
  306. FROM {$db_prefix}board_permissions
  307. WHERE permission = 'post_unapproved_topics'");
  308. $inserts = array();
  309. while ($row = smf_mysql_fetch_assoc($request))
  310. {
  311. $inserts[] = "($row[id_group], $row[id_board], 'post_draft', $row[add_deny])";
  312. $inserts[] = "($row[id_group], $row[id_board], 'post_autosave_draft', $row[add_deny])";
  313. }
  314. smf_mysql_free_result($request);
  315. if (!empty($inserts))
  316. upgrade_query("
  317. INSERT IGNORE INTO {$db_prefix}board_permissions
  318. (id_group, id_board, permission, add_deny)
  319. VALUES
  320. " . implode(',', $inserts));
  321. // Next we find people who can send PMs, and assume they can save pm_drafts as well
  322. $request = upgrade_query("
  323. SELECT id_group, add_deny, permission
  324. FROM {$db_prefix}permissions
  325. WHERE permission = 'pm_send'");
  326. $inserts = array();
  327. while ($row = smf_mysql_fetch_assoc($request))
  328. {
  329. $inserts[] = "($row[id_group], 'pm_draft', $row[add_deny])";
  330. $inserts[] = "($row[id_group], 'pm_autosave_draft', $row[add_deny])";
  331. }
  332. smf_mysql_free_result($request);
  333. if (!empty($inserts))
  334. upgrade_query("
  335. INSERT IGNORE INTO {$db_prefix}permissions
  336. (id_group, permission, add_deny)
  337. VALUES
  338. " . implode(',', $inserts));
  339. }
  340. ---}
  341. INSERT INTO {$db_prefix}settings
  342. (variable, value)
  343. VALUES
  344. ('drafts_autosave_enabled', '1'),
  345. ('drafts_show_saved_enabled', '1'),
  346. ('drafts_keep_days', '7');
  347. INSERT INTO {$db_prefix}themes
  348. (id_theme, variable, value)
  349. VALUES
  350. ('1', 'drafts_autosave_enabled', '1'),
  351. ('1', 'drafts_show_saved_enabled', '1');
  352. ---#
  353. /******************************************************************************/
  354. --- Adding support for likes
  355. /******************************************************************************/
  356. ---# Creating likes table.
  357. CREATE TABLE IF NOT EXISTS {$db_prefix}user_likes (
  358. id_member mediumint(8) unsigned NOT NULL default '0',
  359. content_type char(6) default '',
  360. content_id int(10) unsigned NOT NULL default '0',
  361. like_time int(10) unsigned NOT NULL default '0',
  362. PRIMARY KEY (content_id, content_type, id_member),
  363. INDEX content (content_id, content_type),
  364. INDEX liker (id_member)
  365. ) ENGINE=MyISAM;
  366. ---#
  367. ---# Adding count to the messages table.
  368. ALTER TABLE {$db_prefix}messages
  369. ADD COLUMN likes smallint(5) unsigned NOT NULL DEFAULT '0';
  370. ---#
  371. /******************************************************************************/
  372. --- Adding support for group-based board moderation
  373. /******************************************************************************/
  374. ---# Creating moderator_groups table
  375. CREATE TABLE IF NOT EXISTS {$db_prefix}moderator_groups (
  376. id_board smallint(5) unsigned NOT NULL default '0',
  377. id_group smallint(5) unsigned NOT NULL default '0',
  378. PRIMARY KEY (id_board, id_group)
  379. ) ENGINE=MyISAM{$db_collation};
  380. ---#
  381. /******************************************************************************/
  382. --- Cleaning up integration hooks
  383. /******************************************************************************/
  384. ---# Deleting integration hooks
  385. DELETE FROM {$db_prefix}settings
  386. WHERE variable LIKE 'integrate_%';
  387. ---#
  388. /******************************************************************************/
  389. --- Cleaning up old settings
  390. /******************************************************************************/
  391. ---# Showing contact details to guests should never happen.
  392. DELETE FROM {$db_prefix}settings
  393. WHERE variable IN ('enableStickyTopics', 'guest_hideContacts');
  394. ---#
  395. /******************************************************************************/
  396. --- Removing old Simple Machines files we do not need to fetch any more
  397. /******************************************************************************/
  398. ---# We no longer call on the latest packages list.
  399. DELETE FROM {$db_prefix}admin_info_files
  400. WHERE filename = 'latest-packages.js'
  401. AND path = '/smf/';
  402. ---#
  403. /******************************************************************************/
  404. --- Upgrading "verification questions" feature
  405. /******************************************************************************/
  406. ---# Creating qanda table
  407. CREATE TABLE IF NOT EXISTS {$db_prefix}qanda (
  408. id_question smallint(5) unsigned NOT NULL auto_increment,
  409. lngfile varchar(255) NOT NULL default '',
  410. question varchar(255) NOT NULL default '',
  411. answers text NOT NULL,
  412. PRIMARY KEY (id_question),
  413. KEY lngfile (lngfile)
  414. ) ENGINE=MyISAM{$db_collation};
  415. ---#
  416. ---# Moving questions and answers to the new table
  417. ---{
  418. $questions = array();
  419. $get_questions = upgrade_query("
  420. SELECT body AS question, recipient_name AS answer
  421. FROM {$db_prefix}log_comments
  422. WHERE comment_type = 'ver_test'");
  423. while ($row = $smcFunc['db_fetch_assoc']($get_questions))
  424. {
  425. $questions[] = "($language, $row[question], serialize(array($row[answer])))";
  426. }
  427. $smcFunc['db_free_result']($get_questions);
  428. if (!empty($questions))
  429. {
  430. upgrade_query("
  431. INSERT INTO {$db_prefix}qanda
  432. (lngfile, question, answers)
  433. VALUES
  434. " . implode(',', $questions));
  435. // Delete the questions from log_comments now
  436. upgrade_query("
  437. DELETE FROM {$db_prefix}log_comments
  438. WHERE comment_type = 'ver_test'
  439. ");
  440. }
  441. ---}
  442. ---#
  443. /******************************************************************************/
  444. --- Marking packages as uninstalled...
  445. /******************************************************************************/
  446. ---# Updating log_packages
  447. UPDATE {$db_prefix}log_packages
  448. SET install_state = 0;
  449. ---#
  450. /******************************************************************************/
  451. --- Updating profile permissions...
  452. /******************************************************************************/
  453. ---# Removing the old "view your own profile" permission
  454. DELETE FROM {$db_prefix}permissions
  455. WHERE permission = 'profile_view_own';
  456. ---#
  457. ---# Updating the old "view any profile" permission
  458. UPDATE {$db_prefix}permissions
  459. SET permission = 'profile_view'
  460. WHERE permission = 'profile_view_any';
  461. ---#
  462. ---# Adding "profile_password_own"
  463. ---{
  464. $inserts = array();
  465. $request = upgrade_query("
  466. SELECT id_group, add_deny
  467. FROM {$db_prefix}permissions
  468. WHERE permission = 'profile_identity_own'");
  469. while ($row = $smcFunc['db_fetch_assoc']($request))
  470. {
  471. $inserts[] = "($row[id_group], 'profile_password_own', $row[add_deny])";
  472. }
  473. $smcFunc['db_free_result']($request);
  474. if (!empty($inserts))
  475. {
  476. upgrade_query("
  477. INSERT INTO {$db_prefix}permissions
  478. (id_group, permission, add_deny)
  479. VALUES
  480. " . implode(',', $inserts)
  481. );
  482. }
  483. ---}
  484. ---#
  485. ---# Adding other profile permissions
  486. ---{
  487. $inserts = array();
  488. $request = upgrade_query("
  489. SELECT id_group, add_deny
  490. FROM {$db_prefix}permissions
  491. WHERE permission = 'profile_extra_own'");
  492. while ($row = $smcFunc['db_fetch_assoc']($request))
  493. {
  494. $inserts[] = "($row[id_group], 'profile_blurb_own', $row[add_deny])";
  495. $inserts[] = "($row[id_group], 'profile_displayed_name_own', $row[add_deny])";
  496. $inserts[] = "($row[id_group], 'profile_forum_own', $row[add_deny])";
  497. $inserts[] = "($row[id_group], 'profile_other_own', $row[add_deny])";
  498. $inserts[] = "($row[id_group], 'profile_signature_own', $row[add_deny])";
  499. }
  500. $smcFunc['db_free_result']($request);
  501. if (!empty($inserts))
  502. {
  503. upgrade_query("
  504. INSERT INTO {$db_prefix}permissions
  505. (id_group, permission, add_deny)
  506. VALUES
  507. " . implode(',', $inserts)
  508. );
  509. }
  510. ---}
  511. ---#
  512. /******************************************************************************/
  513. --- Upgrading PM labels...
  514. /******************************************************************************/
  515. ---# Adding pm_labels table...
  516. CREATE TABLE IF NOT EXISTS {$db_prefix}pm_labels (
  517. id_label int(10) unsigned NOT NULL auto_increment,
  518. id_member mediumint(8) unsigned NOT NULL default '0',
  519. name varchar(30) NOT NULL default '',
  520. PRIMARY KEY (id_label)
  521. ) ENGINE=MyISAM;
  522. ---#
  523. ---# Adding pm_labeled_messages table...
  524. CREATE TABLE IF NOT EXISTS {$db_prefix}pm_labeled_messages (
  525. id_label int(10) unsigned NOT NULL default '0',
  526. id_pm int(10) unsigned NOT NULL default '0',
  527. PRIMARY KEY (id_label, id_pm)
  528. );
  529. ---#
  530. ---# Adding "in_inbox" column to pm_recipients
  531. ALTER TABLE {$db_prefix}pm_recipients
  532. ADD COLUMN in_inbox tinyint(3) NOT NULL default '1';
  533. ---#
  534. ---# Moving label info to new tables and updating rules...
  535. ---{
  536. // First see if we still have a message_labels column
  537. $results = $smcFunc['db_list_columns']('{db_prefix}members');
  538. if (in_array('message_labels', $results))
  539. {
  540. // They've still got it, so pull the label info
  541. $get_labels = $smcFunc['db_query']('', '
  542. SELECT id_member, message_labels
  543. FROM {db_prefix}members
  544. WHERE message_labels != {string:blank}',
  545. array(
  546. 'blank' => '',
  547. )
  548. );
  549. $inserts = array();
  550. $label_info = array();
  551. while ($row = $smcFunc['db_fetch_assoc']($get_labels))
  552. {
  553. // Stick this in an array
  554. $labels = explode(',', $row['message_labels']);
  555. // Build some inserts
  556. foreach($labels AS $index => $label)
  557. {
  558. // Keep track of the index of this label - we'll need that in a bit...
  559. $label_info[$row['id_member']][$label] = $index;
  560. $inserts[] = array($row['id_member'], $label);
  561. }
  562. }
  563. $smcFunc['db_free_result']($get_labels);
  564. if (!empty($inserts))
  565. {
  566. $smcFunc['db_insert']('', '{db_prefix}pm_labels', array('id_member' => 'int', 'name' => 'string-30'), $inserts, array());
  567. }
  568. // This is the easy part - update the inbox stuff
  569. $smcFunc['db_query']('', '
  570. UPDATE {db_prefix}pm_recipients
  571. SET in_inbox = {int:in_inbox}
  572. WHERE FIND_IN_SET({int:minusone}, labels)',
  573. array(
  574. 'in_inbox' => 1,
  575. 'minusone' => -1,
  576. )
  577. );
  578. // Now we go pull the new IDs for each label
  579. $get_new_label_ids = $smcFunc['db_query']('', '
  580. SELECT *
  581. FROM {db_prefix}pm_labels',
  582. array(
  583. )
  584. );
  585. $label_info_2 = array();
  586. while ($label_row = $smcFunc['db_query']($get_new_label_ids))
  587. {
  588. // Map the old index values to the new ID values...
  589. $old_index = $label_info[$row['id_member']][$row['label_name']];
  590. $label_info_2[$row['id_member']][$old_index] = $row['id_label'];
  591. }
  592. $smcFunc['db_free_result']($get_new_label_ids);
  593. // Pull label info from pm_recipients
  594. // Ignore any that are only in the inbox
  595. $get_pm_labels = $smcFunc['db_query']('', '
  596. SELECT id_pm, id_member, labels
  597. FROM {db_prefix}pm_recipients
  598. WHERE deleted = {int:not_deleted}
  599. AND labels != {int:minus_one}',
  600. array(
  601. 'not_deleted' => 0,
  602. 'minus_one' => -1,
  603. )
  604. );
  605. while ($row = $smcFunc['db_fetch_assoc']($get_pm_labels))
  606. {
  607. $labels = explode(',', $row['labels']);
  608. foreach($labels as $a_label)
  609. {
  610. if ($a_label == '-1')
  611. continue;
  612. $new_label_info = $label_info_2[$row['id_member']][$a_label];
  613. $inserts[] = array($row['id_pm'], $new_label_info);
  614. }
  615. }
  616. $smcFunc['db_free_result']($get_pm_labels);
  617. // Insert the new data
  618. $smcFunc['db_insert']('', '{db_prefix}pm_labeled_messages', array('id_pm' => 'int', 'id_label' => 'int'), $inserts, array());
  619. // Final step of this ridiculously massive process
  620. $get_pm_rules = $smcFunc['db_query']('', '
  621. SELECT id_member, id_rule, actions
  622. FROM {db_prefix}pm_rules',
  623. array(
  624. )
  625. );
  626. // Go through the rules, unserialize the actions, then figure out if there's anything we can use
  627. while ($row = $smcFunc['db_fetch_assoc']($get_pm_rules))
  628. {
  629. // Turn this into an array...
  630. $actions = unserialize($row['actions']);
  631. // Loop through the actions and see if we're applying a label anywhere
  632. foreach ($actions as $index => $action)
  633. {
  634. if ($action['t'] == 'lab')
  635. {
  636. // Update the value of this label...
  637. $actions[$index]['v'] = $label_info_2[$row['id_member']][$action['v']];
  638. }
  639. }
  640. // Put this back into a string
  641. $actions = serialize($actions);
  642. $smcFunc['db_query']('', '
  643. UPDATE {db_prefix}rules
  644. SET actions = {string:actions}
  645. WHERE id_rule = {int:id_rule}',
  646. array(
  647. 'actions' => $actions,
  648. 'id_rule' => $row['id_rule'],
  649. )
  650. );
  651. }
  652. $smcFunc['db_free_result']($get_pm_rules);
  653. // Lastly, we drop the old columns
  654. $smcFunc['db_remove_column']('{db_prefix}members', 'message_labels');
  655. $smcFunc['db_remove_column']('{db_prefix}pm_recipients', 'labels');
  656. }
  657. ---}
  658. ---#
  659. /******************************************************************************/
  660. --- Adding support for edit reasons
  661. /******************************************************************************/
  662. ---# Adding "modified_reason" column to messages
  663. ALTER TABLE {$db_prefix}messages
  664. ADD COLUMN modified_reason varchar(255) NOT NULL;
  665. ---#