upgrade_2-1_postgresql.sql 12 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372
  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 disregard
  213. /******************************************************************************/
  214. ---# Adding new columns to log_topics...
  215. ---{
  216. upgrade_query("
  217. ALTER TABLE {$db_prefix}log_topics
  218. ADD COLUMN disregarded int NOT NULL DEFAULT '0'");
  219. UPDATE {$db_prefix}log_topics
  220. SET disregarded = 0;
  221. INSERT INTO {$db_prefix}settings
  222. (variable, value)
  223. VALUES
  224. ('enable_disregard', 0);
  225. ---}
  226. ---#
  227. /******************************************************************************/
  228. --- Name changes
  229. /******************************************************************************/
  230. ---# Altering the membergroup stars to icons
  231. ---{
  232. upgrade_query("
  233. ALTER TABLE {$db_prefix}membergroups
  234. CHANGE `stars` `icons` varchar(255) NOT NULL DEFAULT ''");
  235. ---}
  236. ---#
  237. /******************************************************************************/
  238. --- Adding support for drafts
  239. /******************************************************************************/
  240. ---# Creating drafts table.
  241. CREATE TABLE {$db_prefix}user_drafts (
  242. id_draft int unsigned NOT NULL auto_increment,
  243. id_topic int unsigned NOT NULL default '0',
  244. id_board smallint unsigned NOT NULL default '0',
  245. id_reply int unsigned NOT NULL default '0',
  246. type smallint NOT NULL default '0',
  247. poster_time int unsigned NOT NULL default '0',
  248. id_member int unsigned NOT NULL default '0',
  249. subject varchar(255) NOT NULL default '',
  250. smileys_enabled smallint NOT NULL default '1',
  251. body text NOT NULL,
  252. icon varchar(16) NOT NULL default 'xx',
  253. locked smallint NOT NULL default '0',
  254. is_sticky smallint NOT NULL default '0',
  255. to_list varchar(255) NOT NULL default '',
  256. outbox smallint NOT NULL default '0',
  257. PRIMARY KEY (id_draft)
  258. );
  259. ---#
  260. ---# Adding draft permissions...
  261. ---{
  262. // We cannot do this twice
  263. if (@$modSettings['smfVersion'] < '2.1')
  264. {
  265. // Anyone who can currently post unapproved topics we assume can create drafts as well ...
  266. $request = upgrade_query("
  267. SELECT id_group, id_board, add_deny, permission
  268. FROM {$db_prefix}board_permissions
  269. WHERE permission = 'post_unapproved_topics'");
  270. $inserts = array();
  271. while ($row = $smcFunc['db_fetch_assoc']($request))
  272. {
  273. $inserts[] = "($row[id_group], $row[id_board], 'post_draft', $row[add_deny])";
  274. $inserts[] = "($row[id_group], $row[id_board], 'post_autosave_draft', $row[add_deny])";
  275. }
  276. $smcFunc['db_free_result']($request);
  277. if (!empty($inserts))
  278. {
  279. foreach ($inserts AS $insert)
  280. {
  281. upgrade_query("
  282. INSERT INTO {$db_prefix}board_permissions
  283. (id_group, id_board, permission, add_deny)
  284. VALUES
  285. " . $insert);
  286. }
  287. }
  288. // Next we find people who can send PMs, and assume they can save pm_drafts as well
  289. $request = upgrade_query("
  290. SELECT id_group, add_deny, permission
  291. FROM {$db_prefix}permissions
  292. WHERE permission = 'pm_send'");
  293. $inserts = array();
  294. while ($row = $smcFunc['db_fetch_assoc']($request))
  295. {
  296. $inserts[] = "($row[id_group], 'pm_draft', $row[add_deny])";
  297. $inserts[] = "($row[id_group], 'pm_autosave_draft', $row[add_deny])";
  298. }
  299. $smcFunc['db_free_result']($request);
  300. if (!empty($inserts))
  301. {
  302. foreach ($inserts AS $insert)
  303. {
  304. upgrade_query("
  305. INSERT INTO {$db_prefix}permissions
  306. (id_group, permission, add_deny)
  307. VALUES
  308. " . $insert);
  309. }
  310. }
  311. }
  312. ---}
  313. INSERT INTO {$db_prefix}settings (variable, value) VALUES ('drafts_autosave_enabled', '1');
  314. INSERT INTO {$db_prefix}settings (variable, value) VALUES ('drafts_show_saved_enabled', '1');
  315. INSERT INTO {$db_prefix}settings (variable, value) VALUES ('drafts_keep_days', '7');
  316. INSERT INTO {$db_prefix}themes (id_theme, variable, value) VALUES ('1', 'drafts_autosave_enabled', '1');
  317. INSERT INTO {$db_prefix}themes (id_theme, variable, value) VALUES ('1', 'drafts_show_saved_enabled', '1');
  318. ---#
  319. /******************************************************************************/
  320. --- Adding support for group-based board moderation
  321. /******************************************************************************/
  322. ---# Creating moderator_groups table
  323. CREATE TABLE IF NOT EXISTS {$db_prefix}moderator_groups (
  324. id_board smallint NOT NULL default '0',
  325. id_group smallint NOT NULL default '0',
  326. PRIMARY KEY (id_board, id_group)
  327. );