upgrade_2-1_postgresql.sql 30 KB

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