upgrade_2-1_postgresql.sql 11 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344
  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. INSERT INTO {$db_prefix}settings
  220. (variable, value)
  221. VALUES
  222. ('enable_disregard', 0);
  223. ---}
  224. ---#
  225. /******************************************************************************/
  226. --- Name changes
  227. /******************************************************************************/
  228. ---# Altering the membergroup stars to icons
  229. ---{
  230. upgrade_query("
  231. ALTER TABLE {$db_prefix}membergroups
  232. CHANGE `stars` `icons` varchar(255) NOT NULL DEFAULT ''");
  233. ---}
  234. ---#
  235. /******************************************************************************/
  236. --- Adding support for drafts
  237. /******************************************************************************/
  238. ---# Creating drafts table.
  239. CREATE TABLE {$db_prefix}user_drafts (
  240. id_draft int unsigned NOT NULL auto_increment,
  241. id_topic int unsigned NOT NULL default '0',
  242. id_board smallint unsigned NOT NULL default '0',
  243. id_reply int unsigned NOT NULL default '0',
  244. type smallint NOT NULL default '0',
  245. poster_time int unsigned NOT NULL default '0',
  246. id_member int unsigned NOT NULL default '0',
  247. subject varchar(255) NOT NULL default '',
  248. smileys_enabled smallint NOT NULL default '1',
  249. body text NOT NULL,
  250. icon varchar(16) NOT NULL default 'xx',
  251. locked smallint NOT NULL default '0',
  252. is_sticky smallint NOT NULL default '0',
  253. to_list varchar(255) NOT NULL default '',
  254. outbox smallint NOT NULL default '0',
  255. PRIMARY KEY (id_draft)
  256. );
  257. ---#
  258. ---# Adding draft permissions...
  259. ---{
  260. // We cannot do this twice
  261. if (@$modSettings['smfVersion'] < '2.1')
  262. {
  263. // Anyone who can currently post unapproved topics we assume can create drafts as well ...
  264. $request = upgrade_query("
  265. SELECT id_group, id_board, add_deny, permission
  266. FROM {$db_prefix}board_permissions
  267. WHERE permission = 'post_unapproved_topics'");
  268. $inserts = array();
  269. while ($row = $smcFunc['db_fetch_assoc']($request))
  270. {
  271. $inserts[] = "($row[id_group], $row[id_board], 'post_draft', $row[add_deny])";
  272. $inserts[] = "($row[id_group], $row[id_board], 'post_autosave_draft', $row[add_deny])";
  273. }
  274. $smcFunc['db_free_result']($request);
  275. if (!empty($inserts))
  276. upgrade_query("
  277. INSERT IGNORE INTO {$db_prefix}board_permissions
  278. (id_group, id_board, permission, add_deny)
  279. VALUES
  280. " . implode(',', $inserts));
  281. // Next we find people who can send PM's, and assume they can save pm_drafts as well
  282. $request = upgrade_query("
  283. SELECT id_group, add_deny, permission
  284. FROM {$db_prefix}permissions
  285. WHERE permission = 'pm_send'");
  286. $inserts = array();
  287. while ($row = $smcFunc['db_fetch_assoc']($request))
  288. {
  289. $inserts[] = "($row[id_group], 'pm_draft', $row[add_deny])";
  290. $inserts[] = "($row[id_group], 'pm_autosave_draft', $row[add_deny])";
  291. }
  292. $smcFunc['db_free_result']($request);
  293. if (!empty($inserts))
  294. upgrade_query("
  295. INSERT IGNORE INTO {$db_prefix}permissions
  296. (id_group, permission, add_deny)
  297. VALUES
  298. " . implode(',', $inserts));
  299. }
  300. ---}
  301. ---#