upgrade_2-1_postgresql.sql 16 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536
  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 mediumint 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 = mysql_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. /******************************************************************************/
  55. --- Updating legacy attachments...
  56. /******************************************************************************/
  57. ---# Converting legacy attachments.
  58. ---{
  59. $request = upgrade_query("
  60. SELECT MAX(id_attach)
  61. FROM {$db_prefix}attachments");
  62. list ($step_progress['total']) = $smcFunc['db_fetch_row']($request);
  63. $smcFunc['db_free_result']($request);
  64. $_GET['a'] = isset($_GET['a']) ? (int) $_GET['a'] : 0;
  65. $step_progress['name'] = 'Converting legacy attachments';
  66. $step_progress['current'] = $_GET['a'];
  67. // We may be using multiple attachment directories.
  68. if (!empty($modSettings['currentAttachmentUploadDir']) && !is_array($modSettings['attachmentUploadDir']))
  69. $modSettings['attachmentUploadDir'] = unserialize($modSettings['attachmentUploadDir']);
  70. $is_done = false;
  71. while (!$is_done)
  72. {
  73. nextSubStep($substep);
  74. $request = upgrade_query("
  75. SELECT id_attach, id_folder, filename, file_hash
  76. FROM {$db_prefix}attachments
  77. WHERE file_hash = ''
  78. LIMIT $_GET[a], 100");
  79. // Finished?
  80. if ($smcFunc['db_num_rows']($request) == 0)
  81. $is_done = true;
  82. while ($row = $smcFunc['db_fetch_assoc']($request))
  83. {
  84. // The current folder.
  85. $current_folder = !empty($modSettings['currentAttachmentUploadDir']) ? $modSettings['attachmentUploadDir'][$row['id_folder']] : $modSettings['attachmentUploadDir'];
  86. // The old location of the file.
  87. $old_location = getLegacyAttachmentFilename($row['filename'], $row['id_attach'], $row['id_folder']);
  88. // The new file name.
  89. $file_hash = getAttachmentFilename($row['filename'], $row['id_attach'], $row['id_folder'], true);
  90. // And we try to move it.
  91. rename($old_location, $current_folder . '/' . $row['id_attach'] . '_' . $file_hash);
  92. // Only update thif if it was successful.
  93. if (file_exists($current_folder . '/' . $row['id_attach'] . '_' . $file_hash) && !file_exists($old_location))
  94. upgrade_query("
  95. UPDATE {$db_prefix}attachments
  96. SET file_hash = '$file_hash'
  97. WHERE id_attach = $row[id_attach]");
  98. }
  99. $smcFunc['db_free_result']($request);
  100. $_GET['a'] += 100;
  101. $step_progress['current'] = $_GET['a'];
  102. }
  103. unset($_GET['a']);
  104. ---}
  105. ---#
  106. /******************************************************************************/
  107. --- Adding support for IPv6...
  108. /******************************************************************************/
  109. ---# Adding new columns to ban items...
  110. ALTER TABLE {$db_prefix}ban_items
  111. ADD COLUMN ip_low5 smallint NOT NULL DEFAULT '0',
  112. ADD COLUMN ip_high5 smallint NOT NULL DEFAULT '0',
  113. ADD COLUMN ip_low6 smallint NOT NULL DEFAULT '0',
  114. ADD COLUMN ip_high6 smallint NOT NULL DEFAULT '0',
  115. ADD COLUMN ip_low7 smallint NOT NULL DEFAULT '0',
  116. ADD COLUMN ip_high7 smallint NOT NULL DEFAULT '0',
  117. ADD COLUMN ip_low8 smallint NOT NULL DEFAULT '0',
  118. ADD COLUMN ip_high8 smallint NOT NULL DEFAULT '0';
  119. ---#
  120. ---# Changing existing columns to ban items...
  121. ---{
  122. upgrade_query("
  123. ALTER TABLE {$db_prefix}ban_items
  124. ALTER COLUMN ip_low1 type smallint,
  125. ALTER COLUMN ip_high1 type smallint,
  126. ALTER COLUMN ip_low2 type smallint,
  127. ALTER COLUMN ip_high2 type smallint,
  128. ALTER COLUMN ip_low3 type smallint,
  129. ALTER COLUMN ip_high3 type smallint,
  130. ALTER COLUMN ip_low4 type smallint,
  131. ALTER COLUMN ip_high4 type smallint;"
  132. );
  133. upgrade_query("
  134. ALTER TABLE {$db_prefix}ban_items
  135. ALTER COLUMN ip_low1 SET DEFAULT '0',
  136. ALTER COLUMN ip_high1 SET DEFAULT '0',
  137. ALTER COLUMN ip_low2 SET DEFAULT '0',
  138. ALTER COLUMN ip_high2 SET DEFAULT '0',
  139. ALTER COLUMN ip_low3 SET DEFAULT '0',
  140. ALTER COLUMN ip_high3 SET DEFAULT '0',
  141. ALTER COLUMN ip_low4 SET DEFAULT '0',
  142. ALTER COLUMN ip_high4 SET DEFAULT '0';"
  143. );
  144. upgrade_query("
  145. ALTER TABLE {$db_prefix}ban_items
  146. ALTER COLUMN ip_low1 SET NOT NULL,
  147. ALTER COLUMN ip_high1 SET NOT NULL,
  148. ALTER COLUMN ip_low2 SET NOT NULL,
  149. ALTER COLUMN ip_high2 SET NOT NULL,
  150. ALTER COLUMN ip_low3 SET NOT NULL,
  151. ALTER COLUMN ip_high3 SET NOT NULL,
  152. ALTER COLUMN ip_low4 SET NOT NULL,
  153. ALTER COLUMN ip_high4 SET NOT NULL;"
  154. );
  155. ---}
  156. ---#
  157. /******************************************************************************/
  158. --- Adding support for <credits> tag in package manager
  159. /******************************************************************************/
  160. ---# Adding new columns to log_packages ..
  161. ALTER TABLE {$db_prefix}log_packages
  162. ADD COLUMN credits varchar(255) NOT NULL DEFAULT '';
  163. ---#
  164. /******************************************************************************/
  165. --- Adding more space for session ids
  166. /******************************************************************************/
  167. ---# Altering the session_id columns...
  168. ---{
  169. upgrade_query("
  170. ALTER TABLE {$db_prefix}log_online
  171. ALTER COLUMN session type varchar(64);
  172. ALTER TABLE {$db_prefix}log_errors
  173. ALTER COLUMN session type char(64);
  174. ALTER TABLE {$db_prefix}sessions
  175. ALTER COLUMN session_id type char(64);");
  176. upgrade_query("
  177. ALTER TABLE {$db_prefix}log_online
  178. ALTER COLUMN session SET DEFAULT '';
  179. ALTER TABLE {$db_prefix}log_errors
  180. ALTER COLUMN session SET default ' ';");
  181. upgrade_query("
  182. ALTER TABLE {$db_prefix}log_online
  183. ALTER COLUMN session SET NOT NULL;
  184. ALTER TABLE {$db_prefix}log_errors
  185. ALTER COLUMN session SET NOT NULL;
  186. ALTER TABLE {$db_prefix}sessions
  187. ALTER COLUMN session_id SET NOT NULL;");
  188. ---}
  189. ---#
  190. /******************************************************************************/
  191. --- Adding support for MOVED topics enhancements
  192. /******************************************************************************/
  193. ---# Adding new columns to topics table
  194. ---{
  195. upgrade_query("
  196. ALTER TABLE {$db_prefix}topics
  197. ADD COLUMN redirect_expires int NOT NULL DEFAULT '0'");
  198. upgrade_query("
  199. ALTER TABLE {$db_prefix}topics
  200. ADD COLUMN id_redirect_topic int NOT NULL DEFAULT '0'");
  201. ---}
  202. ---#
  203. /******************************************************************************/
  204. --- Adding new scheduled tasks
  205. /******************************************************************************/
  206. ---# Adding new scheduled tasks
  207. INSERT INTO {$db_prefix}scheduled_tasks
  208. (next_time, time_offset, time_regularity, time_unit, disabled, task)
  209. VALUES
  210. (0, 120, 1, 'd', 0, 'remove_temp_attachments');
  211. INSERT INTO {$db_prefix}scheduled_tasks
  212. (next_time, time_offset, time_regularity, time_unit, disabled, task)
  213. VALUES
  214. (0, 180, 1, 'd', 0, 'remove_topic_redirect');
  215. INSERT INTO {$db_prefix}scheduled_tasks
  216. (next_time, time_offset, time_regularity, time_unit, disabled, task)
  217. VALUES
  218. (0, 240, 1, 'd', 0, 'remove_old_drafts');
  219. ---#
  220. /******************************************************************************/
  221. ---- Replacing MSN with Skype
  222. /******************************************************************************/
  223. ---# Modifying the "msn" column...
  224. ALTER TABLE {$db_prefix}members
  225. CHANGE msn skype varchar(255) NOT NULL DEFAULT '';
  226. ---#
  227. /******************************************************************************/
  228. --- Adding support for deny boards access
  229. /******************************************************************************/
  230. ---# Adding new columns to boards...
  231. ---{
  232. upgrade_query("
  233. ALTER TABLE {$db_prefix}boards
  234. ADD COLUMN deny_member_groups varchar(255) NOT NULL DEFAULT ''");
  235. ---}
  236. ---#
  237. /******************************************************************************/
  238. --- Adding support for topic unwatch
  239. /******************************************************************************/
  240. ---# Adding new columns to log_topics...
  241. ---{
  242. upgrade_query("
  243. ALTER TABLE {$db_prefix}log_topics
  244. ADD COLUMN unwatched int NOT NULL DEFAULT '0'");
  245. UPDATE {$db_prefix}log_topics
  246. SET unwatched = 0;
  247. INSERT INTO {$db_prefix}settings
  248. (variable, value)
  249. VALUES
  250. ('enable_unwatch', 0);
  251. ---}
  252. ---#
  253. ---# Fixing column name change...
  254. ---{
  255. upgrade_query("
  256. ALTER TABLE {$db_prefix}log_topics
  257. CHANGE COLUMN disregarded unwatched tinyint(3) NOT NULL DEFAULT '0';");
  258. ---}
  259. ---#
  260. /******************************************************************************/
  261. --- Name changes
  262. /******************************************************************************/
  263. ---# Altering the membergroup stars to icons
  264. ---{
  265. upgrade_query("
  266. ALTER TABLE {$db_prefix}membergroups
  267. CHANGE `stars` `icons` varchar(255) NOT NULL DEFAULT ''");
  268. ---}
  269. ---#
  270. ---# Renaming default theme...
  271. UPDATE {$db_prefix}themes
  272. SET value = 'SMF Default Theme - Curve2'
  273. WHERE value LIKE 'SMF Default Theme%';
  274. ---#
  275. /******************************************************************************/
  276. --- Cleaning up after old themes...
  277. /******************************************************************************/
  278. ---# Checking for "core" and removing it if necessary...
  279. ---{
  280. // Do they have "core" installed?
  281. if (file_exists($GLOBALS['boarddir'] . '/Themes/core'))
  282. {
  283. $core_dir = $GLOBALS['boarddir'] . '/Themes/core';
  284. $theme_request = upgrade_query("
  285. SELECT id_theme
  286. FROM {$db_prefix}themes
  287. WHERE variable = 'theme_dir'
  288. AND value ='$core_dir'");
  289. // Don't do anything if this theme is already uninstalled
  290. if ($smcFunc['db_num_rows']($theme_request) == 1)
  291. {
  292. list($id_theme) = $smcFunc['db_fetch_row']($theme_request, 0);
  293. $smcFunc['db_free_result']($theme_request);
  294. $known_themes = explode(', ', $modSettings['knownThemes']);
  295. // Remove this value...
  296. $known_themes = array_diff($known_themes, array($id_theme));
  297. // Change back to a string...
  298. $known_themes = implode(', ', $known_themes);
  299. // Update the database
  300. upgrade_query("
  301. UPDATE {$db_prefix}settings
  302. SET value = '$known_themes'
  303. WHERE variable = 'knownThemes'");
  304. // Delete any info about this theme
  305. upgrade_query("
  306. DELETE FROM {$db_prefix}themes
  307. WHERE id_theme = $id_theme");
  308. // Set any members or boards using this theme to the default
  309. upgrade_query("
  310. UPDATE {$db_prefix}members
  311. SET id_theme = 0
  312. WHERE id_theme = $id_theme");
  313. upgrade_query("
  314. UPDATE {$db_prefix}boards
  315. SET id_theme = 0
  316. WHERE id_theme = $id_theme");
  317. if ($modSettings['theme_guests'] == $id_theme)
  318. {
  319. upgrade_query("
  320. UPDATE {$db_prefix}settings
  321. SET value = 0
  322. WHERE variable = 'theme_guests'");
  323. }
  324. }
  325. }
  326. ---}
  327. ---#
  328. /******************************************************************************/
  329. --- Adding support for drafts
  330. /******************************************************************************/
  331. ---# Creating drafts table.
  332. CREATE TABLE {$db_prefix}user_drafts (
  333. id_draft int unsigned NOT NULL auto_increment,
  334. id_topic int unsigned NOT NULL default '0',
  335. id_board smallint unsigned NOT NULL default '0',
  336. id_reply int unsigned NOT NULL default '0',
  337. type smallint NOT NULL default '0',
  338. poster_time int unsigned NOT NULL default '0',
  339. id_member int unsigned NOT NULL default '0',
  340. subject varchar(255) NOT NULL default '',
  341. smileys_enabled smallint NOT NULL default '1',
  342. body text NOT NULL,
  343. icon varchar(16) NOT NULL default 'xx',
  344. locked smallint NOT NULL default '0',
  345. is_sticky smallint NOT NULL default '0',
  346. to_list varchar(255) NOT NULL default '',
  347. PRIMARY KEY (id_draft)
  348. );
  349. ---#
  350. ---# Adding draft permissions...
  351. ---{
  352. // We cannot do this twice
  353. if (@$modSettings['smfVersion'] < '2.1')
  354. {
  355. // Anyone who can currently post unapproved topics we assume can create drafts as well ...
  356. $request = upgrade_query("
  357. SELECT id_group, id_board, add_deny, permission
  358. FROM {$db_prefix}board_permissions
  359. WHERE permission = 'post_unapproved_topics'");
  360. $inserts = array();
  361. while ($row = $smcFunc['db_fetch_assoc']($request))
  362. {
  363. $inserts[] = "($row[id_group], $row[id_board], 'post_draft', $row[add_deny])";
  364. $inserts[] = "($row[id_group], $row[id_board], 'post_autosave_draft', $row[add_deny])";
  365. }
  366. $smcFunc['db_free_result']($request);
  367. if (!empty($inserts))
  368. {
  369. foreach ($inserts AS $insert)
  370. {
  371. upgrade_query("
  372. INSERT INTO {$db_prefix}board_permissions
  373. (id_group, id_board, permission, add_deny)
  374. VALUES
  375. " . $insert);
  376. }
  377. }
  378. // Next we find people who can send PMs, and assume they can save pm_drafts as well
  379. $request = upgrade_query("
  380. SELECT id_group, add_deny, permission
  381. FROM {$db_prefix}permissions
  382. WHERE permission = 'pm_send'");
  383. $inserts = array();
  384. while ($row = $smcFunc['db_fetch_assoc']($request))
  385. {
  386. $inserts[] = "($row[id_group], 'pm_draft', $row[add_deny])";
  387. $inserts[] = "($row[id_group], 'pm_autosave_draft', $row[add_deny])";
  388. }
  389. $smcFunc['db_free_result']($request);
  390. if (!empty($inserts))
  391. {
  392. foreach ($inserts AS $insert)
  393. {
  394. upgrade_query("
  395. INSERT INTO {$db_prefix}permissions
  396. (id_group, permission, add_deny)
  397. VALUES
  398. " . $insert);
  399. }
  400. }
  401. }
  402. ---}
  403. INSERT INTO {$db_prefix}settings (variable, value) VALUES ('drafts_autosave_enabled', '1');
  404. INSERT INTO {$db_prefix}settings (variable, value) VALUES ('drafts_show_saved_enabled', '1');
  405. INSERT INTO {$db_prefix}settings (variable, value) VALUES ('drafts_keep_days', '7');
  406. INSERT INTO {$db_prefix}themes (id_theme, variable, value) VALUES ('1', 'drafts_autosave_enabled', '1');
  407. INSERT INTO {$db_prefix}themes (id_theme, variable, value) VALUES ('1', 'drafts_show_saved_enabled', '1');
  408. ---#
  409. /******************************************************************************/
  410. --- Adding support for group-based board moderation
  411. /******************************************************************************/
  412. ---# Creating moderator_groups table
  413. CREATE TABLE IF NOT EXISTS {$db_prefix}moderator_groups (
  414. id_board smallint NOT NULL default '0',
  415. id_group smallint NOT NULL default '0',
  416. PRIMARY KEY (id_board, id_group)
  417. );
  418. ---#
  419. /******************************************************************************/
  420. --- Cleaning up integration hooks
  421. /******************************************************************************/
  422. ---#
  423. DELETE FROM {$db_prefix}settings
  424. WHERE variable LIKE 'integrate_%';
  425. ---#
  426. /******************************************************************************/
  427. --- Upgrading "verification questions" feature
  428. /******************************************************************************/
  429. ---# Creating qanda table
  430. CREATE TABLE {$db_prefix}qanda (
  431. id_question smallint(5) unsigned NOT NULL auto_increment,
  432. lngfile varchar(255) NOT NULL default '',
  433. question varchar(255) NOT NULL default '',
  434. answers text NOT NULL,
  435. PRIMARY KEY (id_question),
  436. KEY lngfile (lngfile)
  437. );
  438. ---#
  439. ---# Moving questions and answers to the new table
  440. ---{
  441. $questions = array();
  442. $get_questions = upgrade_query("
  443. SELECT body AS question, recipient_name AS answer
  444. FROM {$db_prefix}log_comments
  445. WHERE comment_type = 'ver_test'");
  446. while ($row = $smcFunc['db_fetch_assoc']($get_questions))
  447. {
  448. $questions[] = array($language, $row['question'], serialize(array($row['answer'])));
  449. }
  450. $smcFunc['db_free_result']($get_questions);
  451. if (!empty($questions))
  452. {
  453. foreach ($questions as $question)
  454. {
  455. upgrade_query("
  456. INSERT INTO {$db_prefix}qanda
  457. (lngfile, question, answers)
  458. VALUES
  459. " . $question);
  460. }
  461. // Delete the questions from log_comments now
  462. upgrade_query("
  463. DELETE FROM {$db_prefix}log_comments
  464. WHERE comment_type = 'ver_test'
  465. ");
  466. }
  467. ---}
  468. ---#