upgrade_2-1_postgresql.sql 33 KB

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