upgrade_2-1_postgresql.sql 7.6 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234
  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. ---#
  190. /******************************************************************************/
  191. --- Adding support for deny boards access
  192. /******************************************************************************/
  193. ---# Adding new columns to boards...
  194. ---{
  195. upgrade_query("
  196. ALTER TABLE {$db_prefix}boards
  197. ADD COLUMN deny_member_groups varchar(255) NOT NULL DEFAULT ''");
  198. ---}
  199. ---#