upgrade_2-1_postgresql.sql 10 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320
  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. --- Adding support for deny boards access
  196. /******************************************************************************/
  197. ---# Adding new columns to boards...
  198. ---{
  199. upgrade_query("
  200. ALTER TABLE {$db_prefix}boards
  201. ADD COLUMN deny_member_groups varchar(255) NOT NULL DEFAULT ''");
  202. ---}
  203. ---#
  204. /******************************************************************************/
  205. --- Name changes
  206. /******************************************************************************/
  207. ---# Altering the membergroup stars to icons
  208. ---{
  209. upgrade_query("
  210. ALTER TABLE {$db_prefix}membergroups
  211. CHANGE `stars` `icons` varchar(255) NOT NULL DEFAULT ''");
  212. ---}
  213. ---#
  214. /******************************************************************************/
  215. --- Adding support for drafts
  216. /******************************************************************************/
  217. ---# Creating drafts table.
  218. CREATE TABLE {$db_prefix}user_drafts (
  219. id_draft int unsigned NOT NULL auto_increment,
  220. id_topic int unsigned NOT NULL default '0',
  221. id_board smallint unsigned NOT NULL default '0',
  222. id_reply int unsigned NOT NULL default '0',
  223. type smallint NOT NULL default '0',
  224. poster_time int unsigned NOT NULL default '0',
  225. id_member int unsigned NOT NULL default '0',
  226. subject varchar(255) NOT NULL default '',
  227. smileys_enabled smallint NOT NULL default '1',
  228. body text NOT NULL,
  229. icon varchar(16) NOT NULL default 'xx',
  230. locked smallint NOT NULL default '0',
  231. is_sticky smallint NOT NULL default '0',
  232. to_list varchar(255) NOT NULL default '',
  233. outbox smallint NOT NULL default '0',
  234. PRIMARY KEY (id_draft)
  235. );
  236. ---#
  237. ---# Adding draft permissions...
  238. ---{
  239. // We cannot do this twice
  240. if (@$modSettings['smfVersion'] < '2.1')
  241. {
  242. // Anyone who can currently post unapproved topics we assume can create drafts as well ...
  243. $request = upgrade_query("
  244. SELECT id_group, id_board, add_deny, permission
  245. FROM {$db_prefix}board_permissions
  246. WHERE permission = 'post_unapproved_topics'");
  247. $inserts = array();
  248. while ($row = mysql_fetch_assoc($request))
  249. {
  250. $inserts[] = "($row[id_group], $row[id_board], 'post_draft', $row[add_deny])";
  251. $inserts[] = "($row[id_group], $row[id_board], 'post_autosave_draft', $row[add_deny])";
  252. }
  253. mysql_free_result($request);
  254. if (!empty($inserts))
  255. upgrade_query("
  256. INSERT IGNORE INTO {$db_prefix}board_permissions
  257. (id_group, id_board, permission, add_deny)
  258. VALUES
  259. " . implode(',', $inserts));
  260. // Next we find people who can send PM's, and assume they can save pm_drafts as well
  261. $request = upgrade_query("
  262. SELECT id_group, add_deny, permission
  263. FROM {$db_prefix}permissions
  264. WHERE permission = 'pm_send'");
  265. $inserts = array();
  266. while ($row = mysql_fetch_assoc($request))
  267. {
  268. $inserts[] = "($row[id_group], 'pm_draft', $row[add_deny])";
  269. $inserts[] = "($row[id_group], 'pm_autosave_draft', $row[add_deny])";
  270. }
  271. mysql_free_result($request);
  272. if (!empty($inserts))
  273. upgrade_query("
  274. INSERT IGNORE INTO {$db_prefix}permissions
  275. (id_group, permission, add_deny)
  276. VALUES
  277. " . implode(',', $inserts));
  278. }
  279. ---}
  280. ---#