upgrade_2-1_postgresql.sql 14 KB

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