upgrade_2-1_sqlite.sql 16 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526
  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. ---# Copying the current "allow users to disable word censor" setting...
  28. ---{
  29. if (!isset($modSettings['allow_no_censored']))
  30. {
  31. $request = upgrade_query("
  32. SELECT value
  33. FROM {$db_prefix}settings
  34. WHERE variable='allow_no_censored'
  35. AND id_theme = 1 OR id_theme = '$modSettings[theme_default]'
  36. ");
  37. // Is it set for either "default" or the one they've set as default?
  38. while ($row = mysql_fetch_assoc($request))
  39. {
  40. if ($row['value'] == 1)
  41. {
  42. upgrade_query("
  43. INSERT INTO {$db_prefix}settings
  44. VALUES ('allow_no_censored', 1)
  45. ");
  46. // Don't do this twice...
  47. break;
  48. }
  49. }
  50. }
  51. ---}
  52. ---#
  53. /******************************************************************************/
  54. --- Updating legacy attachments...
  55. /******************************************************************************/
  56. ---# Converting legacy attachments.
  57. ---{
  58. $request = upgrade_query("
  59. SELECT MAX(id_attach)
  60. FROM {$db_prefix}attachments");
  61. list ($step_progress['total']) = $smcFunc['db_fetch_row']($request);
  62. $smcFunc['db_free_result']($request);
  63. $_GET['a'] = isset($_GET['a']) ? (int) $_GET['a'] : 0;
  64. $step_progress['name'] = 'Converting legacy attachments';
  65. $step_progress['current'] = $_GET['a'];
  66. // We may be using multiple attachment directories.
  67. if (!empty($modSettings['currentAttachmentUploadDir']) && !is_array($modSettings['attachmentUploadDir']))
  68. $modSettings['attachmentUploadDir'] = unserialize($modSettings['attachmentUploadDir']);
  69. $is_done = false;
  70. while (!$is_done)
  71. {
  72. nextSubStep($substep);
  73. $request = upgrade_query("
  74. SELECT id_attach, id_folder, filename, file_hash
  75. FROM {$db_prefix}attachments
  76. WHERE file_hash = ''
  77. LIMIT $_GET[a], 100");
  78. // Finished?
  79. if ($smcFunc['db_num_rows']($request) == 0)
  80. $is_done = true;
  81. while ($row = $smcFunc['db_fetch_assoc']($request))
  82. {
  83. // The current folder.
  84. $current_folder = !empty($modSettings['currentAttachmentUploadDir']) ? $modSettings['attachmentUploadDir'][$row['id_folder']] : $modSettings['attachmentUploadDir'];
  85. // The old location of the file.
  86. $old_location = getLegacyAttachmentFilename($row['filename'], $row['id_attach'], $row['id_folder']);
  87. // The new file name.
  88. $file_hash = getAttachmentFilename($row['filename'], $row['id_attach'], $row['id_folder'], true);
  89. // And we try to move it.
  90. rename($old_location, $current_folder . '/' . $row['id_attach'] . '_' . $file_hash);
  91. // Only update thif if it was successful.
  92. if (file_exists($current_folder . '/' . $row['id_attach'] . '_' . $file_hash) && !file_exists($old_location))
  93. upgrade_query("
  94. UPDATE {$db_prefix}attachments
  95. SET file_hash = '$file_hash'
  96. WHERE id_attach = $row[id_attach]");
  97. }
  98. $smcFunc['db_free_result']($request);
  99. $_GET['a'] += 100;
  100. $step_progress['current'] = $_GET['a'];
  101. }
  102. unset($_GET['a']);
  103. ---}
  104. ---#
  105. /******************************************************************************/
  106. --- Adding support for IPv6...
  107. /******************************************************************************/
  108. ---# Adding new columns to ban items...
  109. ALTER TABLE {$db_prefix}ban_items
  110. ADD COLUMN ip_low5 smallint(255) unsigned NOT NULL DEFAULT '0',
  111. ADD COLUMN ip_high5 smallint(255) unsigned NOT NULL DEFAULT '0',
  112. ADD COLUMN ip_low6 smallint(255) unsigned NOT NULL DEFAULT '0',
  113. ADD COLUMN ip_high6 smallint(255) unsigned NOT NULL DEFAULT '0',
  114. ADD COLUMN ip_low7 smallint(255) unsigned NOT NULL DEFAULT '0',
  115. ADD COLUMN ip_high7 smallint(255) unsigned NOT NULL DEFAULT '0',
  116. ADD COLUMN ip_low8 smallint(255) unsigned NOT NULL DEFAULT '0',
  117. ADD COLUMN ip_high8 smallint(255) unsigned NOT NULL DEFAULT '0';
  118. ---#
  119. ---# Changing existing columns to ban items...
  120. ALTER TABLE {$db_prefix}ban_items
  121. CHANGE ip_low1 ip_low1 smallint(255) unsigned NOT NULL DEFAULT '0',
  122. CHANGE ip_high1 ip_high1 smallint(255) unsigned NOT NULL DEFAULT '0',
  123. CHANGE ip_low2 ip_low2 smallint(255) unsigned NOT NULL DEFAULT '0',
  124. CHANGE ip_high2 ip_high2 smallint(255) unsigned NOT NULL DEFAULT '0',
  125. CHANGE ip_low3 ip_low3 smallint(255) unsigned NOT NULL DEFAULT '0',
  126. CHANGE ip_high3 ip_high3 smallint(255) unsigned NOT NULL DEFAULT '0',
  127. CHANGE ip_low4 ip_low4 smallint(255) unsigned NOT NULL DEFAULT '0',
  128. CHANGE ip_high4 ip_high4 smallint(255) unsigned NOT NULL DEFAULT '0';
  129. ---#
  130. /******************************************************************************/
  131. --- Adding support for <credits> tag in package manager
  132. /******************************************************************************/
  133. ---# Adding new columns to log_packages ..
  134. ALTER TABLE {$db_prefix}log_packages
  135. ADD COLUMN credits varchar(255) NOT NULL DEFAULT '';
  136. ---#
  137. /******************************************************************************/
  138. --- Adding more space for session ids
  139. /******************************************************************************/
  140. ---# Altering the session_id columns...
  141. ALTER TABLE {$db_prefix}log_online
  142. CHANGE `session` `session` varchar(64) NOT NULL DEFAULT '';
  143. ALTER TABLE {$db_prefix}log_errors
  144. CHANGE `session` `session` char(64) NOT NULL default ' ';
  145. ALTER TABLE {$db_prefix}sessions
  146. CHANGE `session_id` `session_id` char(64) NOT NULL;
  147. ---#
  148. /******************************************************************************/
  149. --- Adding new columns for MOVED topic updates
  150. /******************************************************************************/
  151. ---# Adding new custom fields columns.
  152. ---{
  153. $smcFunc['db_alter_table']('{db_prefix}topics', array(
  154. 'add' => array(
  155. 'redirect_expires' => array(
  156. 'name' => 'redirect_expires',
  157. 'null' => false,
  158. 'default' => '0',
  159. 'type' => 'int',
  160. 'auto' => false,
  161. ),
  162. )
  163. ));
  164. $smcFunc['db_alter_table']('{db_prefix}topics', array(
  165. 'add' => array(
  166. 'id_redirect_topic' => array(
  167. 'name' => 'id_redirect_topic',
  168. 'null' => false,
  169. 'default' => '0',
  170. 'type' => 'int',
  171. 'auto' => false,
  172. ),
  173. )
  174. ));
  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. $smcFunc['db_alter_table']('{db_prefix}boards', array(
  207. 'add' => array(
  208. 'deny_member_groups' => array(
  209. 'name' => 'deny_member_groups',
  210. 'null' => false,
  211. 'default' => '',
  212. 'type' => varchar,
  213. 'size' => 255,
  214. 'auto' => false,
  215. ),
  216. )
  217. ));
  218. ---}
  219. ---#
  220. /******************************************************************************/
  221. --- Adding support for topic unwatch
  222. /******************************************************************************/
  223. ---# Adding new columns to log_topics...
  224. ---{
  225. $smcFunc['db_alter_table']('{db_prefix}log_topics', array(
  226. 'add' => array(
  227. 'unwatched' => array(
  228. 'name' => 'unwatched',
  229. 'null' => false,
  230. 'default' => 0,
  231. 'type' => 'int',
  232. 'auto' => false,
  233. ),
  234. )
  235. ));
  236. UPDATE {$db_prefix}log_topics
  237. SET unwatched = 0;
  238. INSERT INTO {$db_prefix}settings
  239. (variable, value)
  240. VALUES
  241. ('enable_unwatch', 0);
  242. ---}
  243. ---#
  244. ---# Fixing column name change...
  245. ---{
  246. $smcFunc['db_alter_table']('{db_prefix}log_topics', array(
  247. 'change' => array(
  248. 'disregarded' => array(
  249. 'name' => 'unwatched',
  250. 'null' => false,
  251. 'default' => 0,
  252. 'type' => 'int',
  253. 'auto' => false,
  254. ),
  255. )
  256. ));
  257. ---}
  258. ---#
  259. /******************************************************************************/
  260. --- Name changes
  261. /******************************************************************************/
  262. ---# Altering the membergroup stars to icons
  263. ---{
  264. upgrade_query("
  265. ALTER TABLE {$db_prefix}membergroups
  266. CHANGE `stars` `icons` varchar(255) NOT NULL DEFAULT ''");
  267. ---}
  268. ---#
  269. ---# Renaming default theme...
  270. UPDATE {$db_prefix}themes
  271. SET value = 'SMF Default Theme - Curve2'
  272. WHERE value LIKE 'SMF Default Theme%';
  273. ---#
  274. /******************************************************************************/
  275. --- Cleaning up after old themes...
  276. /******************************************************************************/
  277. ---# Checking for "core" and removing it if necessary...
  278. ---{
  279. // Do they have "core" installed?
  280. if (file_exists($GLOBALS['boarddir'] . '/Themes/core'))
  281. {
  282. $core_dir = $GLOBALS['boarddir'] . '/Themes/core';
  283. $theme_request = upgrade_query("
  284. SELECT id_theme
  285. FROM {$db_prefix}themes
  286. WHERE variable = 'theme_dir'
  287. AND value ='$core_dir'");
  288. // Don't do anything if this theme is already uninstalled
  289. if ($smcFunc['db_num_rows']($theme_request) == 1)
  290. {
  291. list($id_theme) = $smcFunc['db_fetch_row']($theme_request, 0);
  292. $smcFunc['db_free_result']($theme_request);
  293. $known_themes = explode(', ', $modSettings['knownThemes']);
  294. // Remove this value...
  295. $known_themes = array_diff($known_themes, array($id_theme));
  296. // Change back to a string...
  297. $known_themes = implode(', ', $known_themes);
  298. // Update the database
  299. upgrade_query("
  300. UPDATE {$db_prefix}settings
  301. SET value = '$known_themes'
  302. WHERE variable = 'knownThemes'");
  303. // Delete any info about this theme
  304. upgrade_query("
  305. DELETE FROM {$db_prefix}themes
  306. WHERE id_theme = $id_theme");
  307. // Set any members or boards using this theme to the default
  308. upgrade_query("
  309. UPDATE {$db_prefix}members
  310. SET id_theme = 0
  311. WHERE id_theme = $id_theme");
  312. upgrade_query("
  313. UPDATE {$db_prefix}boards
  314. SET id_theme = 0
  315. WHERE id_theme = $id_theme");
  316. if ($modSettings['theme_guests'] == $id_theme)
  317. {
  318. upgrade_query("
  319. UPDATE {$db_prefix}settings
  320. SET value = 0
  321. WHERE variable = 'theme_guests'");
  322. }
  323. }
  324. }
  325. /******************************************************************************/
  326. --- Adding support for drafts
  327. /******************************************************************************/
  328. ---# Creating drafts table.
  329. CREATE TABLE {$db_prefix}user_drafts (
  330. id_draft int unsigned NOT NULL auto_increment,
  331. id_topic int unsigned NOT NULL default '0',
  332. id_board smallint unsigned NOT NULL default '0',
  333. id_reply int unsigned NOT NULL default '0',
  334. type smallint NOT NULL default '0',
  335. poster_time int unsigned NOT NULL default '0',
  336. id_member int unsigned NOT NULL default '0',
  337. subject varchar(255) NOT NULL default '',
  338. smileys_enabled smallint NOT NULL default '1',
  339. body text NOT NULL,
  340. icon varchar(16) NOT NULL default 'xx',
  341. locked smallint NOT NULL default '0',
  342. is_sticky smallint NOT NULL default '0',
  343. to_list varchar(255) NOT NULL default '',
  344. PRIMARY KEY (id_draft)
  345. );
  346. ---#
  347. ---# Adding draft permissions...
  348. ---{
  349. // We cannot do this twice
  350. if (@$modSettings['smfVersion'] < '2.1')
  351. {
  352. // Anyone who can currently post unapproved topics we assume can create drafts as well ...
  353. $request = upgrade_query("
  354. SELECT id_group, id_board, add_deny, permission
  355. FROM {$db_prefix}board_permissions
  356. WHERE permission = 'post_unapproved_topics'");
  357. $inserts = array();
  358. while ($row = $smcFunc['db_fetch_assoc']($request))
  359. {
  360. $inserts[] = "($row[id_group], $row[id_board], 'post_draft', $row[add_deny])";
  361. $inserts[] = "($row[id_group], $row[id_board], 'post_autosave_draft', $row[add_deny])";
  362. }
  363. $smcFunc['db_free_result']($request);
  364. if (!empty($inserts))
  365. {
  366. foreach ($inserts AS $insert)
  367. {
  368. upgrade_query("
  369. INSERT INTO {$db_prefix}board_permissions
  370. (id_group, id_board, permission, add_deny)
  371. VALUES
  372. " . $insert);
  373. }
  374. }
  375. // Next we find people who can send PMs, and assume they can save pm_drafts as well
  376. $request = upgrade_query("
  377. SELECT id_group, add_deny, permission
  378. FROM {$db_prefix}permissions
  379. WHERE permission = 'pm_send'");
  380. $inserts = array();
  381. while ($row = $smcFunc['db_fetch_assoc']($request))
  382. {
  383. $inserts[] = "($row[id_group], 'pm_draft', $row[add_deny])";
  384. $inserts[] = "($row[id_group], 'pm_autosave_draft', $row[add_deny])";
  385. }
  386. $smcFunc['db_free_result']($request);
  387. if (!empty($inserts))
  388. {
  389. foreach ($inserts AS $insert)
  390. {
  391. upgrade_query("
  392. INSERT INTO {$db_prefix}permissions
  393. (id_group, permission, add_deny)
  394. VALUES
  395. " . $insert);
  396. }
  397. }
  398. }
  399. ---}
  400. INSERT INTO {$db_prefix}settings (variable, value) VALUES ('drafts_autosave_enabled', '1');
  401. INSERT INTO {$db_prefix}settings (variable, value) VALUES ('drafts_show_saved_enabled', '1');
  402. INSERT INTO {$db_prefix}settings (variable, value) VALUES ('drafts_keep_days', '7');
  403. INSERT INTO {$db_prefix}themes (id_theme, variable, value) VALUES ('1', 'drafts_autosave_enabled', '1');
  404. INSERT INTO {$db_prefix}themes (id_theme, variable, value) VALUES ('1', 'drafts_show_saved_enabled', '1');
  405. ---#
  406. /******************************************************************************/
  407. --- Adding support for group-based board moderation
  408. /******************************************************************************/
  409. ---# Creating moderator_groups table
  410. CREATE TABLE IF NOT EXISTS {$db_prefix}moderator_groups (
  411. id_board smallint NOT NULL default '0',
  412. id_group smallint NOT NULL default '0',
  413. PRIMARY KEY (id_board, id_group)
  414. );
  415. ---#
  416. /******************************************************************************/
  417. --- Cleaning up integration hooks
  418. /******************************************************************************/
  419. ---#
  420. DELETE FROM {$db_prefix}settings
  421. WHERE variable LIKE 'integrate_%';
  422. ---#
  423. /******************************************************************************/
  424. --- Upgrading "verification questions" feature
  425. /******************************************************************************/
  426. ---# Creating qanda table
  427. CREATE TABLE IF NOT EXISTS {$db_prefix}qanda (
  428. id_question smallint(5) unsigned NOT NULL auto_increment,
  429. lngfile varchar(255) NOT NULL default '',
  430. question varchar(255) NOT NULL default '',
  431. answers text NOT NULL,
  432. PRIMARY KEY (id_question),
  433. KEY lngfile (lngfile)
  434. );
  435. ---#
  436. ---# Moving questions and answers to the new table
  437. ---{
  438. $questions = array();
  439. $get_questions = upgrade_query("
  440. SELECT body AS question, recipient_name AS answer
  441. FROM {$db_prefix}log_comments
  442. WHERE comment_type = 'ver_test'");
  443. while ($row = $smcFunc['db_fetch_assoc']($get_questions))
  444. {
  445. $questions[] = array($language, $row['question'], serialize(array($row['answer'])));
  446. }
  447. $smcFunc['db_free_result']($get_questions);
  448. if (!empty($questions))
  449. {
  450. foreach ($questions as $question)
  451. {
  452. upgrade_query("
  453. INSERT INTO {$db_prefix}qanda
  454. (lngfile, question, answers)
  455. VALUES
  456. " . $question);
  457. }
  458. // Delete the questions from log_comments now
  459. upgrade_query("
  460. DELETE FROM {$db_prefix}log_comments
  461. WHERE comment_type = 'ver_test'
  462. ");
  463. }
  464. ---}
  465. ---#