upgrade_2-1_sqlite.sql 11 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337
  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. ---# Adding login history...
  6. CREATE TABLE IF NOT EXISTS {$db_prefix}member_logins (
  7. id_login integer NOT NULL auto_increment,
  8. id_member integer NOT NULL,
  9. time integer NOT NULL,
  10. ip varchar(255) NOT NULL default '',
  11. ip2 varchar(255) NOT NULL default '',
  12. PRIMARY KEY id_login(id_login)
  13. KEY id_member (id_member)
  14. KEY time (time)
  15. );
  16. ---#
  17. ---# Copying the current package backup setting...
  18. ---{
  19. if (!isset($modSettings['package_make_full_backups']) && isset($modSettings['package_make_backups']))
  20. upgrade_query("
  21. INSERT INTO {$db_prefix}settings
  22. (variable, value)
  23. VALUES
  24. ('package_make_full_backups', '" . $modSettings['package_make_backups'] . "')");
  25. ---}
  26. ---#
  27. /******************************************************************************/
  28. --- Updating legacy attachments...
  29. /******************************************************************************/
  30. ---# Converting legacy attachments.
  31. ---{
  32. $request = upgrade_query("
  33. SELECT MAX(id_attach)
  34. FROM {$db_prefix}attachments");
  35. list ($step_progress['total']) = $smcFunc['db_fetch_row']($request);
  36. $smcFunc['db_free_result']($request);
  37. $_GET['a'] = isset($_GET['a']) ? (int) $_GET['a'] : 0;
  38. $step_progress['name'] = 'Converting legacy attachments';
  39. $step_progress['current'] = $_GET['a'];
  40. // We may be using multiple attachment directories.
  41. if (!empty($modSettings['currentAttachmentUploadDir']) && !is_array($modSettings['attachmentUploadDir']))
  42. $modSettings['attachmentUploadDir'] = unserialize($modSettings['attachmentUploadDir']);
  43. $is_done = false;
  44. while (!$is_done)
  45. {
  46. nextSubStep($substep);
  47. $request = upgrade_query("
  48. SELECT id_attach, id_folder, filename, file_hash
  49. FROM {$db_prefix}attachments
  50. WHERE file_hash = ''
  51. LIMIT $_GET[a], 100");
  52. // Finished?
  53. if ($smcFunc['db_num_rows']($request) == 0)
  54. $is_done = true;
  55. while ($row = $smcFunc['db_fetch_assoc']($request))
  56. {
  57. // The current folder.
  58. $current_folder = !empty($modSettings['currentAttachmentUploadDir']) ? $modSettings['attachmentUploadDir'][$row['id_folder']] : $modSettings['attachmentUploadDir'];
  59. // The old location of the file.
  60. $old_location = getLegacyAttachmentFilename($row['filename'], $row['id_attach'], $row['id_folder']);
  61. // The new file name.
  62. $file_hash = getAttachmentFilename($row['filename'], $row['id_attach'], $row['id_folder'], true);
  63. // And we try to move it.
  64. rename($old_location, $current_folder . '/' . $row['id_attach'] . '_' . $file_hash);
  65. // Only update thif if it was successful.
  66. if (file_exists($current_folder . '/' . $row['id_attach'] . '_' . $file_hash) && !file_exists($old_location))
  67. upgrade_query("
  68. UPDATE {$db_prefix}attachments
  69. SET file_hash = '$file_hash'
  70. WHERE id_attach = $row[id_attach]");
  71. }
  72. $smcFunc['db_free_result']($request);
  73. $_GET['a'] += 100;
  74. $step_progress['current'] = $_GET['a'];
  75. }
  76. unset($_GET['a']);
  77. ---}
  78. ---#
  79. /******************************************************************************/
  80. --- Adding support for IPv6...
  81. /******************************************************************************/
  82. ---# Adding new columns to ban items...
  83. ALTER TABLE {$db_prefix}ban_items
  84. ADD COLUMN ip_low5 smallint(255) unsigned NOT NULL DEFAULT '0',
  85. ADD COLUMN ip_high5 smallint(255) unsigned NOT NULL DEFAULT '0',
  86. ADD COLUMN ip_low6 smallint(255) unsigned NOT NULL DEFAULT '0',
  87. ADD COLUMN ip_high6 smallint(255) unsigned NOT NULL DEFAULT '0',
  88. ADD COLUMN ip_low7 smallint(255) unsigned NOT NULL DEFAULT '0',
  89. ADD COLUMN ip_high7 smallint(255) unsigned NOT NULL DEFAULT '0',
  90. ADD COLUMN ip_low8 smallint(255) unsigned NOT NULL DEFAULT '0',
  91. ADD COLUMN ip_high8 smallint(255) unsigned NOT NULL DEFAULT '0';
  92. ---#
  93. ---# Changing existing columns to ban items...
  94. ALTER TABLE {$db_prefix}ban_items
  95. CHANGE ip_low1 ip_low1 smallint(255) unsigned NOT NULL DEFAULT '0',
  96. CHANGE ip_high1 ip_high1 smallint(255) unsigned NOT NULL DEFAULT '0',
  97. CHANGE ip_low2 ip_low2 smallint(255) unsigned NOT NULL DEFAULT '0',
  98. CHANGE ip_high2 ip_high2 smallint(255) unsigned NOT NULL DEFAULT '0',
  99. CHANGE ip_low3 ip_low3 smallint(255) unsigned NOT NULL DEFAULT '0',
  100. CHANGE ip_high3 ip_high3 smallint(255) unsigned NOT NULL DEFAULT '0',
  101. CHANGE ip_low4 ip_low4 smallint(255) unsigned NOT NULL DEFAULT '0',
  102. CHANGE ip_high4 ip_high4 smallint(255) unsigned NOT NULL DEFAULT '0';
  103. ---#
  104. /******************************************************************************/
  105. --- Adding support for <credits> tag in package manager
  106. /******************************************************************************/
  107. ---# Adding new columns to log_packages ..
  108. ALTER TABLE {$db_prefix}log_packages
  109. ADD COLUMN credits varchar(255) NOT NULL DEFAULT '';
  110. ---#
  111. /******************************************************************************/
  112. --- Adding more space for session ids
  113. /******************************************************************************/
  114. ---# Altering the session_id columns...
  115. ALTER TABLE {$db_prefix}log_online
  116. CHANGE `session` `session` varchar(64) NOT NULL DEFAULT '';
  117. ALTER TABLE {$db_prefix}log_errors
  118. CHANGE `session` `session` char(64) NOT NULL default ' ';
  119. ALTER TABLE {$db_prefix}sessions
  120. CHANGE `session_id` `session_id` char(64) NOT NULL;
  121. ---#
  122. /******************************************************************************/
  123. --- Adding new columns for MOVED topic updates
  124. /******************************************************************************/
  125. ---# Adding new custom fields columns.
  126. ---{
  127. $smcFunc['db_alter_table']('{db_prefix}topics', array(
  128. 'add' => array(
  129. 'redirect_expires' => array(
  130. 'name' => 'redirect_expires',
  131. 'null' => false,
  132. 'default' => '0',
  133. 'type' => 'int',
  134. 'auto' => false,
  135. ),
  136. )
  137. ));
  138. $smcFunc['db_alter_table']('{db_prefix}topics', array(
  139. 'add' => array(
  140. 'id_redirect_topic' => array(
  141. 'name' => 'id_redirect_topic',
  142. 'null' => false,
  143. 'default' => '0',
  144. 'type' => 'int',
  145. 'auto' => false,
  146. ),
  147. )
  148. ));
  149. ---}
  150. ---#
  151. /******************************************************************************/
  152. --- Adding new scheduled tasks
  153. /******************************************************************************/
  154. ---# Adding new scheduled tasks
  155. INSERT INTO {$db_prefix}scheduled_tasks
  156. (next_time, time_offset, time_regularity, time_unit, disabled, task)
  157. VALUES
  158. (0, 120, 1, 'd', 0, 'remove_temp_attachments');
  159. INSERT INTO {$db_prefix}scheduled_tasks
  160. (next_time, time_offset, time_regularity, time_unit, disabled, task)
  161. VALUES
  162. (0, 180, 1, 'd', 0, 'remove_topic_redirect');
  163. INSERT INTO {$db_prefix}scheduled_tasks
  164. (next_time, time_offset, time_regularity, time_unit, disabled, task)
  165. VALUES
  166. (0, 240, 1, 'd', 0, 'remove_old_drafts');
  167. ---#
  168. /******************************************************************************/
  169. ---- Replacing MSN with Skype
  170. /******************************************************************************/
  171. ---# Modifying the "msn" column...
  172. ALTER TABLE {$db_prefix}members
  173. CHANGE msn skype varchar(255) NOT NULL DEFAULT '';
  174. ---#
  175. /******************************************************************************/
  176. --- Adding support for deny boards access
  177. /******************************************************************************/
  178. ---# Adding new columns to boards...
  179. ---{
  180. $smcFunc['db_alter_table']('{db_prefix}boards', array(
  181. 'add' => array(
  182. 'deny_member_groups' => array(
  183. 'name' => 'deny_member_groups',
  184. 'null' => false,
  185. 'default' => '',
  186. 'type' => varchar,
  187. 'size' => 255,
  188. 'auto' => false,
  189. ),
  190. )
  191. ));
  192. ---}
  193. ---#
  194. /******************************************************************************/
  195. --- Adding support for topic disregard
  196. /******************************************************************************/
  197. ---# Adding new columns to log_topics...
  198. ---{
  199. $smcFunc['db_alter_table']('{db_prefix}log_topics', array(
  200. 'add' => array(
  201. 'disregarded' => array(
  202. 'name' => 'disregarded',
  203. 'null' => false,
  204. 'default' => 0,
  205. 'type' => 'int',
  206. 'auto' => false,
  207. ),
  208. )
  209. ));
  210. INSERT INTO {$db_prefix}settings
  211. (variable, value)
  212. VALUES
  213. ('enable_disregard', 0);
  214. ---}
  215. ---#
  216. /******************************************************************************/
  217. --- Name changes
  218. /******************************************************************************/
  219. ---# Altering the membergroup stars to icons
  220. ---{
  221. upgrade_query("
  222. ALTER TABLE {$db_prefix}membergroups
  223. CHANGE `stars` `icons` varchar(255) NOT NULL DEFAULT ''");
  224. ---}
  225. ---#
  226. /******************************************************************************/
  227. --- Adding support for drafts
  228. /******************************************************************************/
  229. ---# Creating drafts table.
  230. CREATE TABLE {$db_prefix}user_drafts (
  231. id_draft int unsigned NOT NULL auto_increment,
  232. id_topic int unsigned NOT NULL default '0',
  233. id_board smallint unsigned NOT NULL default '0',
  234. id_reply int unsigned NOT NULL default '0',
  235. type smallint NOT NULL default '0',
  236. poster_time int unsigned NOT NULL default '0',
  237. id_member int unsigned NOT NULL default '0',
  238. subject varchar(255) NOT NULL default '',
  239. smileys_enabled smallint NOT NULL default '1',
  240. body text NOT NULL,
  241. icon varchar(16) NOT NULL default 'xx',
  242. locked smallint NOT NULL default '0',
  243. is_sticky smallint NOT NULL default '0',
  244. to_list varchar(255) NOT NULL default '',
  245. outbox smallint NOT NULL default '0',
  246. PRIMARY KEY (id_draft)
  247. );
  248. ---#
  249. ---# Adding draft permissions...
  250. ---{
  251. // We cannot do this twice
  252. if (@$modSettings['smfVersion'] < '2.1')
  253. {
  254. // Anyone who can currently post unapproved topics we assume can create drafts as well ...
  255. $request = upgrade_query("
  256. SELECT id_group, id_board, add_deny, permission
  257. FROM {$db_prefix}board_permissions
  258. WHERE permission = 'post_unapproved_topics'");
  259. $inserts = array();
  260. while ($row = $smcFunc['db_fetch_assoc']($request))
  261. {
  262. $inserts[] = "($row[id_group], $row[id_board], 'post_draft', $row[add_deny])";
  263. $inserts[] = "($row[id_group], $row[id_board], 'post_autosave_draft', $row[add_deny])";
  264. }
  265. $smcFunc['db_free_result']($request);
  266. if (!empty($inserts))
  267. upgrade_query("
  268. INSERT IGNORE INTO {$db_prefix}board_permissions
  269. (id_group, id_board, permission, add_deny)
  270. VALUES
  271. " . implode(',', $inserts));
  272. // Next we find people who can send PM's, and assume they can save pm_drafts as well
  273. $request = upgrade_query("
  274. SELECT id_group, add_deny, permission
  275. FROM {$db_prefix}permissions
  276. WHERE permission = 'pm_send'");
  277. $inserts = array();
  278. while ($row = $smcFunc['db_fetch_assoc']($request))
  279. {
  280. $inserts[] = "($row[id_group], 'pm_draft', $row[add_deny])";
  281. $inserts[] = "($row[id_group], 'pm_autosave_draft', $row[add_deny])";
  282. }
  283. $smcFunc['db_free_result']($request);
  284. if (!empty($inserts))
  285. upgrade_query("
  286. INSERT IGNORE INTO {$db_prefix}permissions
  287. (id_group, permission, add_deny)
  288. VALUES
  289. " . implode(',', $inserts));
  290. }
  291. ---}
  292. ---#
  293. /******************************************************************************/
  294. --- Adding support for group-based board moderation
  295. /******************************************************************************/
  296. ---# Creating moderator_groups table
  297. CREATE TABLE IF NOT EXISTS {$db_prefix}moderator_groups (
  298. id_board smallint NOT NULL default '0',
  299. id_group smallint NOT NULL default '0',
  300. PRIMARY KEY (id_board, id_group)
  301. );