upgrade_2-1_mysql.sql 24 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775
  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. $questions[] = array($language, $row['question'], serialize(array($row['answer'])));
  425. $smcFunc['db_free_result']($get_questions);
  426. if (!empty($questions))
  427. {
  428. $smcFunc['db_insert']('',
  429. '{db_prefix}qanda',
  430. array('lngfile' => 'string', 'question' => 'string', 'answers' => 'string'),
  431. $questions,
  432. array('id_question')
  433. );
  434. // Delete the questions from log_comments now
  435. upgrade_query("
  436. DELETE FROM {$db_prefix}log_comments
  437. WHERE comment_type = 'ver_test'
  438. ");
  439. }
  440. ---}
  441. ---#
  442. /******************************************************************************/
  443. --- Marking packages as uninstalled...
  444. /******************************************************************************/
  445. ---# Updating log_packages
  446. UPDATE {$db_prefix}log_packages
  447. SET install_state = 0;
  448. ---#
  449. /******************************************************************************/
  450. --- Updating profile permissions...
  451. /******************************************************************************/
  452. ---# Removing the old "view your own profile" permission
  453. DELETE FROM {$db_prefix}permissions
  454. WHERE permission = 'profile_view_own';
  455. ---#
  456. ---# Updating the old "view any profile" permission
  457. UPDATE {$db_prefix}permissions
  458. SET permission = 'profile_view'
  459. WHERE permission = 'profile_view_any';
  460. ---#
  461. ---# Adding "profile_password_own"
  462. ---{
  463. $inserts = array();
  464. $request = upgrade_query("
  465. SELECT id_group, add_deny
  466. FROM {$db_prefix}permissions
  467. WHERE permission = 'profile_identity_own'");
  468. while ($row = $smcFunc['db_fetch_assoc']($request))
  469. {
  470. $inserts[] = "($row[id_group], 'profile_password_own', $row[add_deny])";
  471. }
  472. $smcFunc['db_free_result']($request);
  473. if (!empty($inserts))
  474. {
  475. upgrade_query("
  476. INSERT INTO {$db_prefix}permissions
  477. (id_group, permission, add_deny)
  478. VALUES
  479. " . implode(',', $inserts)
  480. );
  481. }
  482. ---}
  483. ---#
  484. ---# Adding other profile permissions
  485. ---{
  486. $inserts = array();
  487. $request = upgrade_query("
  488. SELECT id_group, add_deny
  489. FROM {$db_prefix}permissions
  490. WHERE permission = 'profile_extra_own'");
  491. while ($row = $smcFunc['db_fetch_assoc']($request))
  492. {
  493. $inserts[] = "($row[id_group], 'profile_blurb_own', $row[add_deny])";
  494. $inserts[] = "($row[id_group], 'profile_displayed_name_own', $row[add_deny])";
  495. $inserts[] = "($row[id_group], 'profile_forum_own', $row[add_deny])";
  496. $inserts[] = "($row[id_group], 'profile_other_own', $row[add_deny])";
  497. $inserts[] = "($row[id_group], 'profile_signature_own', $row[add_deny])";
  498. }
  499. $smcFunc['db_free_result']($request);
  500. if (!empty($inserts))
  501. {
  502. upgrade_query("
  503. INSERT INTO {$db_prefix}permissions
  504. (id_group, permission, add_deny)
  505. VALUES
  506. " . implode(',', $inserts)
  507. );
  508. }
  509. ---}
  510. ---#
  511. /******************************************************************************/
  512. --- Upgrading PM labels...
  513. /******************************************************************************/
  514. ---# Adding pm_labels table...
  515. CREATE TABLE IF NOT EXISTS {$db_prefix}pm_labels (
  516. id_label int(10) unsigned NOT NULL auto_increment,
  517. id_member mediumint(8) unsigned NOT NULL default '0',
  518. name varchar(30) NOT NULL default '',
  519. PRIMARY KEY (id_label)
  520. ) ENGINE=MyISAM;
  521. ---#
  522. ---# Adding pm_labeled_messages table...
  523. CREATE TABLE IF NOT EXISTS {$db_prefix}pm_labeled_messages (
  524. id_label int(10) unsigned NOT NULL default '0',
  525. id_pm int(10) unsigned NOT NULL default '0',
  526. PRIMARY KEY (id_label, id_pm)
  527. ) ENGINE=MyISAM;
  528. ---#
  529. ---# Adding "in_inbox" column to pm_recipients
  530. ALTER TABLE {$db_prefix}pm_recipients
  531. ADD COLUMN in_inbox tinyint(3) NOT NULL default '1';
  532. ---#
  533. ---# Moving label info to new tables and updating rules...
  534. ---{
  535. // First see if we still have a message_labels column
  536. $results = $smcFunc['db_list_columns']('{db_prefix}members');
  537. if (in_array('message_labels', $results))
  538. {
  539. // They've still got it, so pull the label info
  540. $get_labels = $smcFunc['db_query']('', '
  541. SELECT id_member, message_labels
  542. FROM {db_prefix}members
  543. WHERE message_labels != {string:blank}',
  544. array(
  545. 'blank' => '',
  546. )
  547. );
  548. $inserts = array();
  549. $label_info = array();
  550. while ($row = $smcFunc['db_fetch_assoc']($get_labels))
  551. {
  552. // Stick this in an array
  553. $labels = explode(',', $row['message_labels']);
  554. // Build some inserts
  555. foreach ($labels AS $index => $label)
  556. {
  557. // Keep track of the index of this label - we'll need that in a bit...
  558. $label_info[$row['id_member']][$label] = $index;
  559. $inserts[] = array($row['id_member'], $label);
  560. }
  561. }
  562. $smcFunc['db_free_result']($get_labels);
  563. if (!empty($inserts))
  564. {
  565. $smcFunc['db_insert']('', '{db_prefix}pm_labels', array('id_member' => 'int', 'name' => 'string-30'), $inserts, array());
  566. // Clear this out for our next query below
  567. $inserts = array();
  568. }
  569. // This is the easy part - update the inbox stuff
  570. $smcFunc['db_query']('', '
  571. UPDATE {db_prefix}pm_recipients
  572. SET in_inbox = {int:in_inbox}
  573. WHERE FIND_IN_SET({int:minusone}, labels)',
  574. array(
  575. 'in_inbox' => 1,
  576. 'minusone' => -1,
  577. )
  578. );
  579. // Now we go pull the new IDs for each label
  580. $get_new_label_ids = $smcFunc['db_query']('', '
  581. SELECT *
  582. FROM {db_prefix}pm_labels',
  583. array(
  584. )
  585. );
  586. $label_info_2 = array();
  587. while ($label_row = $smcFunc['db_fetch_assoc']($get_new_label_ids))
  588. {
  589. // Map the old index values to the new ID values...
  590. $old_index = $label_info[$row['id_member']][$row['label_name']];
  591. $label_info_2[$row['id_member']][$old_index] = $row['id_label'];
  592. }
  593. $smcFunc['db_free_result']($get_new_label_ids);
  594. // Pull label info from pm_recipients
  595. // Ignore any that are only in the inbox
  596. $get_pm_labels = $smcFunc['db_query']('', '
  597. SELECT id_pm, id_member, labels
  598. FROM {db_prefix}pm_recipients
  599. WHERE deleted = {int:not_deleted}
  600. AND labels != {string:minus_one}',
  601. array(
  602. 'not_deleted' => 0,
  603. 'minus_one' => -1,
  604. )
  605. );
  606. while ($row = $smcFunc['db_fetch_assoc']($get_pm_labels))
  607. {
  608. $labels = explode(',', $row['labels']);
  609. foreach ($labels as $a_label)
  610. {
  611. if ($a_label == '-1')
  612. continue;
  613. $new_label_info = $label_info_2[$row['id_member']][$a_label];
  614. $inserts[] = array($row['id_pm'], $new_label_info);
  615. }
  616. }
  617. $smcFunc['db_free_result']($get_pm_labels);
  618. // Insert the new data
  619. if (!empty($inserts))
  620. {
  621. $smcFunc['db_insert']('', '{db_prefix}pm_labeled_messages', array('id_pm' => 'int', 'id_label' => 'int'), $inserts, array());
  622. }
  623. // Final step of this ridiculously massive process
  624. $get_pm_rules = $smcFunc['db_query']('', '
  625. SELECT id_member, id_rule, actions
  626. FROM {db_prefix}pm_rules',
  627. array(
  628. )
  629. );
  630. // Go through the rules, unserialize the actions, then figure out if there's anything we can use
  631. while ($row = $smcFunc['db_fetch_assoc']($get_pm_rules))
  632. {
  633. // Turn this into an array...
  634. $actions = unserialize($row['actions']);
  635. // Loop through the actions and see if we're applying a label anywhere
  636. foreach ($actions as $index => $action)
  637. {
  638. if ($action['t'] == 'lab')
  639. {
  640. // Update the value of this label...
  641. $actions[$index]['v'] = $label_info_2[$row['id_member']][$action['v']];
  642. }
  643. }
  644. // Put this back into a string
  645. $actions = serialize($actions);
  646. $smcFunc['db_query']('', '
  647. UPDATE {db_prefix}pm_rules
  648. SET actions = {string:actions}
  649. WHERE id_rule = {int:id_rule}',
  650. array(
  651. 'actions' => $actions,
  652. 'id_rule' => $row['id_rule'],
  653. )
  654. );
  655. }
  656. $smcFunc['db_free_result']($get_pm_rules);
  657. // Lastly, we drop the old columns
  658. $smcFunc['db_remove_column']('{db_prefix}members', 'message_labels');
  659. $smcFunc['db_remove_column']('{db_prefix}pm_recipients', 'labels');
  660. }
  661. ---}
  662. ---#
  663. /******************************************************************************/
  664. --- Adding support for edit reasons
  665. /******************************************************************************/
  666. ---# Adding "modified_reason" column to messages
  667. ALTER TABLE {$db_prefix}messages
  668. ADD COLUMN modified_reason varchar(255) NOT NULL;
  669. ---#