upgrade_2-1_postgresql.sql 35 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001011021031041051061071081091101111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521531541551561571581591601611621631641651661671681691701711721731741751761771781791801811821831841851861871881891901911921931941951961971981992002012022032042052062072082092102112122132142152162172182192202212222232242252262272282292302312322332342352362372382392402412422432442452462472482492502512522532542552562572582592602612622632642652662672682692702712722732742752762772782792802812822832842852862872882892902912922932942952962972982993003013023033043053063073083093103113123133143153163173183193203213223233243253263273283293303313323333343353363373383393403413423433443453463473483493503513523533543553563573583593603613623633643653663673683693703713723733743753763773783793803813823833843853863873883893903913923933943953963973983994004014024034044054064074084094104114124134144154164174184194204214224234244254264274284294304314324334344354364374384394404414424434444454464474484494504514524534544554564574584594604614624634644654664674684694704714724734744754764774784794804814824834844854864874884894904914924934944954964974984995005015025035045055065075085095105115125135145155165175185195205215225235245255265275285295305315325335345355365375385395405415425435445455465475485495505515525535545555565575585595605615625635645655665675685695705715725735745755765775785795805815825835845855865875885895905915925935945955965975985996006016026036046056066076086096106116126136146156166176186196206216226236246256266276286296306316326336346356366376386396406416426436446456466476486496506516526536546556566576586596606616626636646656666676686696706716726736746756766776786796806816826836846856866876886896906916926936946956966976986997007017027037047057067077087097107117127137147157167177187197207217227237247257267277287297307317327337347357367377387397407417427437447457467477487497507517527537547557567577587597607617627637647657667677687697707717727737747757767777787797807817827837847857867877887897907917927937947957967977987998008018028038048058068078088098108118128138148158168178188198208218228238248258268278288298308318328338348358368378388398408418428438448458468478488498508518528538548558568578588598608618628638648658668678688698708718728738748758768778788798808818828838848858868878888898908918928938948958968978988999009019029039049059069079089099109119129139149159169179189199209219229239249259269279289299309319329339349359369379389399409419429439449459469479489499509519529539549559569579589599609619629639649659669679689699709719729739749759769779789799809819829839849859869879889899909919929939949959969979989991000100110021003100410051006100710081009101010111012101310141015101610171018101910201021102210231024102510261027102810291030103110321033103410351036103710381039104010411042104310441045104610471048104910501051105210531054105510561057105810591060106110621063106410651066106710681069107010711072107310741075107610771078107910801081108210831084108510861087108810891090109110921093109410951096109710981099110011011102110311041105110611071108110911101111111211131114111511161117111811191120112111221123112411251126112711281129113011311132113311341135113611371138113911401141114211431144114511461147114811491150115111521153115411551156115711581159116011611162
  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. --- Updating board access rules
  302. /******************************************************************************/
  303. ---# Updating board access rules
  304. ---{
  305. $member_groups = array(
  306. 'allowed' => array(),
  307. 'denied' => array(),
  308. );
  309. $request = $smcFunc['db_query']('', '
  310. SELECT id_group, add_deny
  311. FROM {db_prefix}permissions
  312. WHERE permission = {string:permission}',
  313. array(
  314. 'permission' => 'manage_boards',
  315. )
  316. );
  317. while ($row = $smcFunc['db_fetch_assoc']($request))
  318. $member_groups[$row['add_deny'] === '1' ? 'allowed' : 'denied'][] = $row['id_group'];
  319. $smcFunc['db_free_result']($request);
  320. $member_groups = array_diff($member_groups['allowed'], $member_groups['denied']);
  321. if (!empty($member_groups))
  322. {
  323. $count = count($member_groups);
  324. $changes = array();
  325. $request = $smcFunc['db_query']('', '
  326. SELECT id_board, member_groups
  327. FROM {db_prefix}boards');
  328. while ($row = $smcFunc['db_fetch_assoc']($request))
  329. {
  330. $current_groups = explode(',', $row['member_groups']);
  331. if (count(array_intersect($current_groups, $member_groups)) != $count)
  332. {
  333. $new_groups = array_unique(array_merge($current_groups, $member_groups));
  334. $changes[$row['id_board']] = implode(',', $new_groups);
  335. }
  336. }
  337. $smcFunc['db_free_result']($request);
  338. if (!empty($changes))
  339. {
  340. foreach ($changes as $id_board => $member_groups)
  341. $smcFunc['db_query']('', '
  342. UPDATE {db_prefix}boards
  343. SET member_groups = {string:member_groups}
  344. WHERE id_board = {int:id_board}',
  345. array(
  346. 'member_groups' => $member_groups,
  347. 'id_board' => $id_board,
  348. )
  349. );
  350. }
  351. }
  352. ---}
  353. ---#
  354. /******************************************************************************/
  355. --- Adding support for category descriptions
  356. /******************************************************************************/
  357. ---# Adding new columns to categories...
  358. ---{
  359. // Sadly, PostgreSQL whines if we add a NOT NULL column without a default value to an existing table...
  360. upgrade_query("
  361. ALTER TABLE {$db_prefix}categories
  362. ADD COLUMN description text");
  363. upgrade_query("
  364. UPDATE {$db_prefix}categories
  365. SET description = ''");
  366. upgrade_query("
  367. ALTER TABLE {$db_prefix}categories
  368. ALTER COLUMN description SET NOT NULL");
  369. ---}
  370. ---#
  371. /******************************************************************************/
  372. --- Adding support for alerts
  373. /******************************************************************************/
  374. ---# Adding the count to the members table...
  375. ALTER TABLE {$db_prefix}members
  376. ADD COLUMN alerts int NOT NULL default '0';
  377. ---#
  378. ---# Adding the new table for alerts.
  379. CREATE SEQUENCE {$db_prefix}user_alerts_seq;
  380. CREATE TABLE {$db_prefix}user_alerts (
  381. id_alert int default nextval('{$db_prefix}user_alerts_seq'),
  382. alert_time int NOT NULL default '0',
  383. id_member int NOT NULL default '0',
  384. id_member_started int NOT NULL default '0',
  385. member_name varchar(255) NOT NULL default '',
  386. content_type varchar(255) NOT NULL default '',
  387. content_id int NOT NULL default '0',
  388. content_action varchar(255) NOT NULL default '',
  389. is_read smallint NOT NULL default '0',
  390. extra text NOT NULL,
  391. PRIMARY KEY (id_alert)
  392. );
  393. CREATE INDEX {$db_prefix}user_alerts_id_member ON {$db_prefix}user_alerts (id_member);
  394. CREATE INDEX {$db_prefix}user_alerts_alert_time ON {$db_prefix}user_alerts (alert_time);
  395. ---#
  396. ---# Adding alert preferences.
  397. CREATE TABLE {$db_prefix}user_alerts_prefs (
  398. id_member int NOT NULL default '0',
  399. alert_pref varchar(32) NOT NULL default '',
  400. alert_value smallint NOT NULL default '0',
  401. PRIMARY KEY (id_member, alert_pref)
  402. );
  403. INSERT INTO {$db_prefix}user_alerts_prefs (id_member, alert_pref, alert_value) VALUES (0, 'member_group_request', 1);
  404. INSERT INTO {$db_prefix}user_alerts_prefs (id_member, alert_pref, alert_value) VALUES (0, 'member_register', 1);
  405. INSERT INTO {$db_prefix}user_alerts_prefs (id_member, alert_pref, alert_value) VALUES (0, 'msg_like', 1);
  406. INSERT INTO {$db_prefix}user_alerts_prefs (id_member, alert_pref, alert_value) VALUES (0, 'msg_report', 1);
  407. INSERT INTO {$db_prefix}user_alerts_prefs (id_member, alert_pref, alert_value) VALUES (0, 'msg_report_reply', 1);
  408. ---#
  409. /******************************************************************************/
  410. --- Adding support for topic unwatch
  411. /******************************************************************************/
  412. ---# Adding new columns to log_topics...
  413. ---{
  414. upgrade_query("
  415. ALTER TABLE {$db_prefix}log_topics
  416. ADD COLUMN unwatched int NOT NULL DEFAULT '0'");
  417. UPDATE {$db_prefix}log_topics
  418. SET unwatched = 0;
  419. INSERT INTO {$db_prefix}settings
  420. (variable, value)
  421. VALUES
  422. ('enable_unwatch', 0);
  423. ---}
  424. ---#
  425. ---# Fixing column name change...
  426. ---{
  427. upgrade_query("
  428. ALTER TABLE {$db_prefix}log_topics
  429. RENAME disregarded TO unwatched");
  430. ---}
  431. ---#
  432. /******************************************************************************/
  433. --- Name changes
  434. /******************************************************************************/
  435. ---# Altering the membergroup stars to icons
  436. ---{
  437. upgrade_query("
  438. ALTER TABLE {$db_prefix}membergroups
  439. RENAME stars TO icons");
  440. ---}
  441. ---#
  442. ---# Renaming default theme...
  443. UPDATE {$db_prefix}themes
  444. SET value = 'SMF Default Theme - Curve2'
  445. WHERE value LIKE 'SMF Default Theme%';
  446. ---#
  447. ---# Adding the enableThemes setting.
  448. INSERT INTO {$db_prefix}settings
  449. (variable, value)
  450. VALUES
  451. ('enableThemes', '1');
  452. ---#
  453. ---# Setting "default" as the default...
  454. UPDATE {$db_prefix}settings
  455. SET value = '1'
  456. WHERE variable = 'theme_guests';
  457. UPDATE {$db_prefix}boards
  458. SET id_theme = 0;
  459. UPDATE {$db_prefix}members
  460. SET id_theme = 0;
  461. ---#
  462. /******************************************************************************/
  463. --- Cleaning up after old themes...
  464. /******************************************************************************/
  465. ---# Checking for "core" and removing it if necessary...
  466. ---{
  467. // Do they have "core" installed?
  468. if (file_exists($GLOBALS['boarddir'] . '/Themes/core'))
  469. {
  470. $core_dir = $GLOBALS['boarddir'] . '/Themes/core';
  471. $theme_request = upgrade_query("
  472. SELECT id_theme
  473. FROM {$db_prefix}themes
  474. WHERE variable = 'theme_dir'
  475. AND value ='$core_dir'");
  476. // Don't do anything if this theme is already uninstalled
  477. if ($smcFunc['db_num_rows']($theme_request) == 1)
  478. {
  479. list($id_theme) = $smcFunc['db_fetch_row']($theme_request, 0);
  480. $smcFunc['db_free_result']($theme_request);
  481. $known_themes = explode(', ', $modSettings['knownThemes']);
  482. // Remove this value...
  483. $known_themes = array_diff($known_themes, array($id_theme));
  484. // Change back to a string...
  485. $known_themes = implode(', ', $known_themes);
  486. // Update the database
  487. upgrade_query("
  488. UPDATE {$db_prefix}settings
  489. SET value = '$known_themes'
  490. WHERE variable = 'knownThemes'");
  491. // Delete any info about this theme
  492. upgrade_query("
  493. DELETE FROM {$db_prefix}themes
  494. WHERE id_theme = $id_theme");
  495. }
  496. }
  497. ---}
  498. ---#
  499. /******************************************************************************/
  500. --- Adding support for drafts
  501. /******************************************************************************/
  502. ---# Creating drafts table.
  503. CREATE SEQUENCE {$db_prefix}user_drafts_seq;
  504. CREATE TABLE {$db_prefix}user_drafts (
  505. id_draft int NOT NULL default nextval('{$db_prefix}user_drafts_seq'),
  506. id_topic int NOT NULL default '0',
  507. id_board smallint NOT NULL default '0',
  508. id_reply int NOT NULL default '0',
  509. type smallint NOT NULL default '0',
  510. poster_time int NOT NULL default '0',
  511. id_member int NOT NULL default '0',
  512. subject varchar(255) NOT NULL default '',
  513. smileys_enabled smallint NOT NULL default '1',
  514. body text NOT NULL,
  515. icon varchar(16) NOT NULL default 'xx',
  516. locked smallint NOT NULL default '0',
  517. is_sticky smallint NOT NULL default '0',
  518. to_list varchar(255) NOT NULL default '',
  519. PRIMARY KEY (id_draft)
  520. );
  521. CREATE UNIQUE INDEX {$db_prefix}user_drafts_id_member ON {$db_prefix}user_drafts (id_member, id_draft, type);
  522. ---#
  523. ---# Adding draft permissions...
  524. ---{
  525. // We cannot do this twice
  526. if (@$modSettings['smfVersion'] < '2.1')
  527. {
  528. // Anyone who can currently post unapproved topics we assume can create drafts as well ...
  529. $request = upgrade_query("
  530. SELECT id_group, id_board, add_deny, permission
  531. FROM {$db_prefix}board_permissions
  532. WHERE permission = 'post_unapproved_topics'");
  533. $inserts = array();
  534. while ($row = $smcFunc['db_fetch_assoc']($request))
  535. {
  536. $inserts[] = "($row[id_group], $row[id_board], 'post_draft', $row[add_deny])";
  537. $inserts[] = "($row[id_group], $row[id_board], 'post_autosave_draft', $row[add_deny])";
  538. }
  539. $smcFunc['db_free_result']($request);
  540. if (!empty($inserts))
  541. {
  542. foreach ($inserts AS $insert)
  543. {
  544. upgrade_query("
  545. INSERT INTO {$db_prefix}board_permissions
  546. (id_group, id_board, permission, add_deny)
  547. VALUES
  548. " . $insert);
  549. }
  550. }
  551. // Next we find people who can send PMs, and assume they can save pm_drafts as well
  552. $request = upgrade_query("
  553. SELECT id_group, add_deny, permission
  554. FROM {$db_prefix}permissions
  555. WHERE permission = 'pm_send'");
  556. $inserts = array();
  557. while ($row = $smcFunc['db_fetch_assoc']($request))
  558. {
  559. $inserts[] = "($row[id_group], 'pm_draft', $row[add_deny])";
  560. $inserts[] = "($row[id_group], 'pm_autosave_draft', $row[add_deny])";
  561. }
  562. $smcFunc['db_free_result']($request);
  563. if (!empty($inserts))
  564. {
  565. foreach ($inserts AS $insert)
  566. {
  567. upgrade_query("
  568. INSERT INTO {$db_prefix}permissions
  569. (id_group, permission, add_deny)
  570. VALUES
  571. " . $insert);
  572. }
  573. }
  574. }
  575. ---}
  576. INSERT INTO {$db_prefix}settings (variable, value) VALUES ('drafts_autosave_enabled', '1');
  577. INSERT INTO {$db_prefix}settings (variable, value) VALUES ('drafts_show_saved_enabled', '1');
  578. INSERT INTO {$db_prefix}settings (variable, value) VALUES ('drafts_keep_days', '7');
  579. INSERT INTO {$db_prefix}themes (id_theme, variable, value) VALUES ('1', 'drafts_autosave_enabled', '1');
  580. INSERT INTO {$db_prefix}themes (id_theme, variable, value) VALUES ('1', 'drafts_show_saved_enabled', '1');
  581. ---#
  582. /******************************************************************************/
  583. --- Adding support for likes
  584. /******************************************************************************/
  585. ---# Creating likes table.
  586. CREATE TABLE {$db_prefix}user_likes (
  587. id_member int NOT NULL default '0',
  588. content_type char(6) default '',
  589. content_id int NOT NULL default '0',
  590. like_time int NOT NULL default '0',
  591. PRIMARY KEY (content_id, content_type, id_member)
  592. );
  593. CREATE INDEX {$db_prefix}user_likes_content ON {$db_prefix}user_likes (content_id, content_type);
  594. CREATE INDEX {$db_prefix}user_likes_liker ON {$db_prefix}user_likes (id_member);
  595. ---#
  596. ---# Adding count to the messages table.
  597. ALTER TABLE {$db_prefix}messages
  598. ADD COLUMN likes smallint NOT NULL default '0';
  599. ---#
  600. /******************************************************************************/
  601. --- Adding support for group-based board moderation
  602. /******************************************************************************/
  603. ---# Creating moderator_groups table
  604. CREATE TABLE {$db_prefix}moderator_groups (
  605. id_board smallint NOT NULL default '0',
  606. id_group smallint NOT NULL default '0',
  607. PRIMARY KEY (id_board, id_group)
  608. );
  609. ---#
  610. /******************************************************************************/
  611. --- Cleaning up integration hooks
  612. /******************************************************************************/
  613. ---#
  614. DELETE FROM {$db_prefix}settings
  615. WHERE variable LIKE 'integrate_%';
  616. ---#
  617. /******************************************************************************/
  618. --- Cleaning up old settings
  619. /******************************************************************************/
  620. ---# Updating the default time format
  621. ---{
  622. if (!empty($modSettings['time_format']))
  623. {
  624. // First, use the shortened form of the month in the date.
  625. $time_format = str_replace('%B', '%b', $modSettings['time_format']);
  626. // Second, shorten the time to stop including seconds.
  627. $time_format = str_replace(':%S', '', $time_format);
  628. // Then, update the database.
  629. $smcFunc['db_query']('', '
  630. UPDATE {db_prefix}settings
  631. SET value = {string:new_format}
  632. WHERE variable = {literal:time_format}',
  633. array(
  634. 'new_format' => $time_format,
  635. )
  636. );
  637. }
  638. ---}
  639. ---#
  640. ---# Fixing a deprecated option.
  641. UPDATE {$db_prefix}settings
  642. SET value = 'option_css_resize'
  643. WHERE variable = 'avatar_action_too_large'
  644. AND (value = 'option_html_resize' OR value = 'option_js_resize');
  645. ---#
  646. ---# Cleaning up old settings.
  647. DELETE FROM {$db_prefix}settings
  648. WHERE variable IN ('enableStickyTopics', 'guest_hideContacts', 'notify_new_registration', 'attachmentEncryptFilenames', 'hotTopicPosts', 'hotTopicVeryPosts', 'fixLongWords');
  649. ---#
  650. ---# Cleaning up old theme settings.
  651. DELETE FROM {$db_prefix}themes
  652. WHERE variable IN ('show_board_desc', 'no_new_reply_warning', 'display_quick_reply');
  653. ---#
  654. /******************************************************************************/
  655. --- Removing old Simple Machines files we do not need to fetch any more
  656. /******************************************************************************/
  657. ---# We no longer call on the latest packages list.
  658. DELETE FROM {$db_prefix}admin_info_files
  659. WHERE filename IN ('latest-packages.js', 'latest-support.js', 'latest-themes.js')
  660. AND path = '/smf/';
  661. ---#
  662. /******************************************************************************/
  663. --- Upgrading "verification questions" feature
  664. /******************************************************************************/
  665. ---# Creating qanda table
  666. CREATE SEQUENCE {$db_prefix}qanda_seq;
  667. CREATE TABLE {$db_prefix}qanda (
  668. id_question smallint NOT NULL default nextval('{$db_prefix}qanda_seq'),
  669. lngfile varchar(255) NOT NULL default '',
  670. question varchar(255) NOT NULL default '',
  671. answers text NOT NULL,
  672. PRIMARY KEY (id_question),
  673. KEY lngfile (lngfile)
  674. );
  675. ---#
  676. ---# Moving questions and answers to the new table
  677. ---{
  678. $questions = array();
  679. $get_questions = upgrade_query("
  680. SELECT body AS question, recipient_name AS answer
  681. FROM {$db_prefix}log_comments
  682. WHERE comment_type = 'ver_test'");
  683. while ($row = $smcFunc['db_fetch_assoc']($get_questions))
  684. $questions[] = array($language, $row['question'], serialize(array($row['answer'])));
  685. $smcFunc['db_free_result']($get_questions);
  686. if (!empty($questions))
  687. {
  688. $smcFunc['db_insert']('',
  689. '{db_prefix}qanda',
  690. array('lngfile' => 'string', 'question' => 'string', 'answers' => 'string'),
  691. $questions,
  692. array('id_question')
  693. );
  694. // Delete the questions from log_comments now
  695. upgrade_query("
  696. DELETE FROM {$db_prefix}log_comments
  697. WHERE comment_type = 'ver_test'
  698. ");
  699. }
  700. ---}
  701. ---#
  702. /******************************************************************************/
  703. --- Fixing log_online table
  704. /******************************************************************************/
  705. ---# Changing ip to bigint
  706. ALTER TABLE {$db_prefix}log_online ALTER ip TYPE bigint;
  707. ---#
  708. /******************************************************************************/
  709. --- Marking packages as uninstalled...
  710. /******************************************************************************/
  711. ---# Updating log_packages
  712. UPDATE {$db_prefix}log_packages
  713. SET install_state = 0;
  714. ---#
  715. /******************************************************************************/
  716. --- Updating profile permissions...
  717. /******************************************************************************/
  718. ---# Removing the old "view your own profile" permission
  719. DELETE FROM {$db_prefix}permissions
  720. WHERE permission = 'profile_view_own';
  721. ---#
  722. ---# Updating the old "view any profile" permission
  723. UPDATE {$db_prefix}permissions
  724. SET permission = 'profile_view'
  725. WHERE permission = 'profile_view_any';
  726. ---#
  727. ---# Removing the old notification permissions
  728. DELETE FROM {$db_prefix}board_permissions
  729. WHERE permission = 'mark_notify' OR permission = 'mark_any_notify';
  730. ---#
  731. ---# Adding "profile_password_own"
  732. ---{
  733. $inserts = array();
  734. $request = upgrade_query("
  735. SELECT id_group, add_deny
  736. FROM {$db_prefix}permissions
  737. WHERE permission = 'profile_identity_own'");
  738. while ($row = $smcFunc['db_fetch_assoc']($request))
  739. {
  740. $inserts[] = "($row[id_group], 'profile_password_own', $row[add_deny])";
  741. }
  742. $smcFunc['db_free_result']($request);
  743. if (!empty($inserts))
  744. {
  745. foreach ($inserts as $insert)
  746. {
  747. upgrade_query("
  748. INSERT INTO {$db_prefix}permissions
  749. (id_group, permission, add_deny)
  750. VALUES
  751. " . $insert);
  752. }
  753. }
  754. ---}
  755. ---#
  756. ---# Adding other profile permissions
  757. ---{
  758. $inserts = array();
  759. $request = upgrade_query("
  760. SELECT id_group, add_deny
  761. FROM {$db_prefix}permissions
  762. WHERE permission = 'profile_extra_own'");
  763. while ($row = $smcFunc['db_fetch_assoc']($request))
  764. {
  765. $inserts[] = "($row[id_group], 'profile_blurb_own', $row[add_deny])";
  766. $inserts[] = "($row[id_group], 'profile_displayed_name_own', $row[add_deny])";
  767. $inserts[] = "($row[id_group], 'profile_forum_own', $row[add_deny])";
  768. $inserts[] = "($row[id_group], 'profile_other_own', $row[add_deny])";
  769. $inserts[] = "($row[id_group], 'profile_signature_own', $row[add_deny])";
  770. }
  771. $smcFunc['db_free_result']($request);
  772. if (!empty($inserts))
  773. {
  774. foreach ($inserts as $insert)
  775. {
  776. upgrade_query("
  777. INSERT INTO {$db_prefix}permissions
  778. (id_group, permission, add_deny)
  779. VALUES
  780. " . $insert
  781. );
  782. }
  783. }
  784. ---}
  785. ---#
  786. /******************************************************************************/
  787. --- Upgrading PM labels...
  788. /******************************************************************************/
  789. ---# Creating pm_labels sequence...
  790. CREATE SEQUENCE {$db_prefix}pm_labels_sequence;
  791. ---#
  792. ---# Adding pm_labels table...
  793. CREATE TABLE {$db_prefix}pm_labels (
  794. id_label int NOT NULL default nextval('{$db_prefix}pm_labels_seq'),
  795. id_member int NOT NULL default '0',
  796. name varchar(30) NOT NULL default '',
  797. PRIMARY KEY (id_label)
  798. );
  799. ---#
  800. ---# Adding pm_labeled_messages table...
  801. CREATE TABLE {$db_prefix}pm_labeled_messages (
  802. id_label int NOT NULL default '0',
  803. id_pm int NOT NULL default '0',
  804. PRIMARY KEY (id_label, id_pm)
  805. );
  806. ---#
  807. ---# Adding "in_inbox" column to pm_recipients
  808. ALTER TABLE {$db_prefix}pm_recipients
  809. ADD COLUMN in_inbox smallint NOT NULL default '1';
  810. ---#
  811. ---# Moving label info to new tables and updating rules...
  812. ---{
  813. // First see if we still have a message_labels column
  814. $results = $smcFunc['db_list_columns']('{db_prefix}members', false);
  815. if (in_array('message_labels', $results))
  816. {
  817. // They've still got it, so pull the label info
  818. $get_labels = $smcFunc['db_query']('', '
  819. SELECT id_member, message_labels
  820. FROM {db_prefix}members
  821. WHERE message_labels != {string:blank}',
  822. array(
  823. 'blank' => '',
  824. )
  825. );
  826. $inserts = array();
  827. $label_info = array();
  828. while ($row = $smcFunc['db_fetch_assoc']($get_labels))
  829. {
  830. // Stick this in an array
  831. $labels = explode(',', $row['message_labels']);
  832. // Build some inserts
  833. foreach ($labels AS $index => $label)
  834. {
  835. // Keep track of the index of this label - we'll need that in a bit...
  836. $label_info[$row['id_member']][$label] = $index;
  837. $inserts[] = array($row['id_member'], $label);
  838. }
  839. }
  840. $smcFunc['db_free_result']($get_labels);
  841. if (!empty($inserts))
  842. {
  843. $smcFunc['db_insert']('', '{db_prefix}pm_labels', array('id_member' => 'int', 'name' => 'string-30'), $inserts, array());
  844. // Clear this out for our next query below
  845. $inserts = array();
  846. }
  847. // This is the easy part - update the inbox stuff
  848. $smcFunc['db_query']('', '
  849. UPDATE {db_prefix}pm_recipients
  850. SET in_inbox = {int:in_inbox}
  851. WHERE FIND_IN_SET({int:minus_one}, labels)',
  852. array(
  853. 'in_inbox' => 1,
  854. 'minus_one' => -1,
  855. )
  856. );
  857. // Now we go pull the new IDs for each label
  858. $get_new_label_ids = $smcFunc['db_query']('', '
  859. SELECT *
  860. FROM {db_prefix}pm_labels',
  861. array(
  862. )
  863. );
  864. $label_info_2 = array();
  865. while ($label_row = $smcFunc['db_fetch_assoc']($get_new_label_ids))
  866. {
  867. // Map the old index values to the new ID values...
  868. $old_index = $label_info[$row['id_member']][$row['label_name']];
  869. $label_info_2[$row['id_member']][$old_index] = $row['id_label'];
  870. }
  871. $smcFunc['db_free_result']($get_new_label_ids);
  872. // Pull label info from pm_recipients
  873. // Ignore any that are only in the inbox
  874. $get_pm_labels = $smcFunc['db_query']('', '
  875. SELECT id_pm, id_member, labels
  876. FROM {db_prefix}pm_recipients
  877. WHERE deleted = {int:not_deleted}
  878. AND labels != {string:minus_one}',
  879. array(
  880. 'not_deleted' => 0,
  881. 'minus_one' => -1,
  882. )
  883. );
  884. while ($row = $smcFunc['db_fetch_assoc']($get_pm_labels))
  885. {
  886. $labels = explode(',', $row['labels']);
  887. foreach ($labels as $a_label)
  888. {
  889. if ($a_label == '-1')
  890. continue;
  891. $new_label_info = $label_info_2[$row['id_member']][$a_label];
  892. $inserts[] = array($row['id_pm'], $new_label_info);
  893. }
  894. }
  895. $smcFunc['db_free_result']($get_pm_labels);
  896. // Insert the new data
  897. if (!empty($inserts))
  898. {
  899. $smcFunc['db_insert']('', '{db_prefix}pm_labeled_messages', array('id_pm' => 'int', 'id_label' => 'int'), $inserts, array());
  900. }
  901. // Final step of this ridiculously massive process
  902. $get_pm_rules = $smcFunc['db_query']('', '
  903. SELECT id_member, id_rule, actions
  904. FROM {db_prefix}pm_rules',
  905. array(
  906. ),
  907. );
  908. // Go through the rules, unserialize the actions, then figure out if there's anything we can use
  909. while ($row = $smcFunc['db_fetch_assoc']($get_pm_rules))
  910. {
  911. // Turn this into an array...
  912. $actions = unserialize($row['actions']);
  913. // Loop through the actions and see if we're applying a label anywhere
  914. foreach ($actions as $index => $action)
  915. {
  916. if ($action['t'] == 'lab')
  917. {
  918. // Update the value of this label...
  919. $actions[$index]['v'] = $label_info_2[$row['id_member']][$action['v']];
  920. }
  921. }
  922. // Put this back into a string
  923. $actions = serialize($actions);
  924. $smcFunc['db_query']('', '
  925. UPDATE {db_prefix}pm_rules
  926. SET actions = {string:actions}
  927. WHERE id_rule = {int:id_rule}',
  928. array(
  929. 'actions' => $actions,
  930. 'id_rule' => $row['id_rule'],
  931. )
  932. );
  933. }
  934. $smcFunc['db_free_result']($get_pm_rules);
  935. // Lastly, we drop the old columns
  936. $smcFunc['db_remove_column']('{db_prefix}members', 'message_labels');
  937. $smcFunc['db_remove_column']('{db_prefix}pm_recipients', 'labels');
  938. }
  939. }
  940. /******************************************************************************/
  941. --- Adding support for edit reasons
  942. /******************************************************************************/
  943. ---# Adding "modified_reason" column to messages
  944. ALTER TABLE {$db_prefix}messages
  945. ADD COLUMN modified_reason varchar(255) NOT NULL default '';
  946. ---#
  947. /******************************************************************************/
  948. --- Cleaning up guest permissions
  949. /******************************************************************************/
  950. ---# Removing permissions guests can no longer have...
  951. ---{
  952. $illegal_board_permissions = array(
  953. 'announce_topic',
  954. 'delete_any',
  955. 'lock_any',
  956. 'make_sticky',
  957. 'merge_any',
  958. 'modify_any',
  959. 'modify_replies',
  960. 'move_any',
  961. 'poll_add_any',
  962. 'poll_edit_any',
  963. 'poll_lock_any',
  964. 'poll_remove_any',
  965. 'remove_any',
  966. 'report_any',
  967. 'split_any'
  968. );
  969. $illegal_permissions = array('calendar_edit_any', 'moderate_board', 'moderate_forum', 'send_email_to_members');
  970. $smcFunc['db_query']('', '
  971. DELETE FROM {db_prefix}board_permissions
  972. WHERE id_group = {int:guests}
  973. AND permission IN ({array_string:illegal_board_perms})',
  974. array(
  975. 'guests' => -1,
  976. 'illegal_board_perms' => $illegal_board_permissions,
  977. )
  978. );
  979. $smcFunc['db_query']('', '
  980. DELETE FROM {db_prefix}permissions
  981. WHERE id_group = {int:guests}
  982. AND permission IN ({array_string:illegal_perms})',
  983. array(
  984. 'guests' => -1,
  985. 'illegal_perms' => $illegal_permissions,
  986. )
  987. );
  988. ---}
  989. ---#
  990. /******************************************************************************/
  991. --- Adding mail queue settings
  992. /******************************************************************************/
  993. ---#
  994. ---{
  995. if (empty($modSettings['mail_limit']))
  996. {
  997. $smcFunc['db_insert']('replace',
  998. '{db_prefix}settings',
  999. array('variable' => 'string-255', 'value' => 'string'),
  1000. array(
  1001. array('mail_limit', '5'),
  1002. array('mail_quantity', '5'),
  1003. ),
  1004. array('variable')
  1005. );
  1006. }
  1007. ---}
  1008. ---#