upgrade_2-1_sqlite.sql 32 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001011021031041051061071081091101111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521531541551561571581591601611621631641651661671681691701711721731741751761771781791801811821831841851861871881891901911921931941951961971981992002012022032042052062072082092102112122132142152162172182192202212222232242252262272282292302312322332342352362372382392402412422432442452462472482492502512522532542552562572582592602612622632642652662672682692702712722732742752762772782792802812822832842852862872882892902912922932942952962972982993003013023033043053063073083093103113123133143153163173183193203213223233243253263273283293303313323333343353363373383393403413423433443453463473483493503513523533543553563573583593603613623633643653663673683693703713723733743753763773783793803813823833843853863873883893903913923933943953963973983994004014024034044054064074084094104114124134144154164174184194204214224234244254264274284294304314324334344354364374384394404414424434444454464474484494504514524534544554564574584594604614624634644654664674684694704714724734744754764774784794804814824834844854864874884894904914924934944954964974984995005015025035045055065075085095105115125135145155165175185195205215225235245255265275285295305315325335345355365375385395405415425435445455465475485495505515525535545555565575585595605615625635645655665675685695705715725735745755765775785795805815825835845855865875885895905915925935945955965975985996006016026036046056066076086096106116126136146156166176186196206216226236246256266276286296306316326336346356366376386396406416426436446456466476486496506516526536546556566576586596606616626636646656666676686696706716726736746756766776786796806816826836846856866876886896906916926936946956966976986997007017027037047057067077087097107117127137147157167177187197207217227237247257267277287297307317327337347357367377387397407417427437447457467477487497507517527537547557567577587597607617627637647657667677687697707717727737747757767777787797807817827837847857867877887897907917927937947957967977987998008018028038048058068078088098108118128138148158168178188198208218228238248258268278288298308318328338348358368378388398408418428438448458468478488498508518528538548558568578588598608618628638648658668678688698708718728738748758768778788798808818828838848858868878888898908918928938948958968978988999009019029039049059069079089099109119129139149159169179189199209219229239249259269279289299309319329339349359369379389399409419429439449459469479489499509519529539549559569579589599609619629639649659669679689699709719729739749759769779789799809819829839849859869879889899909919929939949959969979989991000100110021003100410051006100710081009101010111012101310141015101610171018101910201021102210231024102510261027102810291030103110321033103410351036103710381039104010411042104310441045104610471048
  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 = $smcFunc['db_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. ---# Adding new "topic_move_any" setting
  54. INSERT INTO {$db_prefix}settings (variable, value) VALUES ('topic_move_any', '1');
  55. ---#
  56. /******************************************************************************/
  57. --- Updating legacy attachments...
  58. /******************************************************************************/
  59. ---# Converting legacy attachments.
  60. ---{
  61. $request = upgrade_query("
  62. SELECT MAX(id_attach)
  63. FROM {$db_prefix}attachments");
  64. list ($step_progress['total']) = $smcFunc['db_fetch_row']($request);
  65. $smcFunc['db_free_result']($request);
  66. $_GET['a'] = isset($_GET['a']) ? (int) $_GET['a'] : 0;
  67. $step_progress['name'] = 'Converting legacy attachments';
  68. $step_progress['current'] = $_GET['a'];
  69. // We may be using multiple attachment directories.
  70. if (!empty($modSettings['currentAttachmentUploadDir']) && !is_array($modSettings['attachmentUploadDir']))
  71. $modSettings['attachmentUploadDir'] = unserialize($modSettings['attachmentUploadDir']);
  72. $is_done = false;
  73. while (!$is_done)
  74. {
  75. nextSubStep($substep);
  76. $fileHash = '';
  77. $request = upgrade_query("
  78. SELECT id_attach, id_folder, filename, file_hash
  79. FROM {$db_prefix}attachments
  80. WHERE attachment_type != 1
  81. LIMIT $_GET[a], 100");
  82. // Finished?
  83. if ($smcFunc['db_num_rows']($request) == 0)
  84. $is_done = true;
  85. while ($row = $smcFunc['db_fetch_assoc']($request))
  86. {
  87. // The current folder.
  88. $currentFolder = !empty($modSettings['currentAttachmentUploadDir']) ? $modSettings['attachmentUploadDir'][$row['id_folder']] : $modSettings['attachmentUploadDir'];
  89. // Old School?
  90. if (empty($row['file_hash']))
  91. {
  92. // Remove international characters (windows-1252)
  93. // These lines should never be needed again. Still, behave.
  94. if (empty($db_character_set) || $db_character_set != 'utf8')
  95. {
  96. $row['filename'] = strtr($row['filename'],
  97. "\x8a\x8e\x9a\x9e\x9f\xc0\xc1\xc2\xc3\xc4\xc5\xc7\xc8\xc9\xca\xcb\xcc\xcd\xce\xcf\xd1\xd2\xd3\xd4\xd5\xd6\xd8\xd9\xda\xdb\xdc\xdd\xe0\xe1\xe2\xe3\xe4\xe5\xe7\xe8\xe9\xea\xeb\xec\xed\xee\xef\xf1\xf2\xf3\xf4\xf5\xf6\xf8\xf9\xfa\xfb\xfc\xfd\xff",
  98. 'SZszYAAAAAACEEEEIIIINOOOOOOUUUUYaaaaaaceeeeiiiinoooooouuuuyy');
  99. $row['filename'] = strtr($row['filename'], array("\xde" => 'TH', "\xfe" =>
  100. 'th', "\xd0" => 'DH', "\xf0" => 'dh', "\xdf" => 'ss', "\x8c" => 'OE',
  101. "\x9c" => 'oe', "\xc6" => 'AE', "\xe6" => 'ae', "\xb5" => 'u'));
  102. }
  103. // Sorry, no spaces, dots, or anything else but letters allowed.
  104. $row['filename'] = preg_replace(array('/\s/', '/[^\w_\.\-]/'), array('_', ''), $row['filename']);
  105. // Create a nice hash.
  106. $fileHash = sha1(md5($row['filename'] . time()) . mt_rand());
  107. // The old file, we need to know if the filename was encrypted or not.
  108. if (file_exists($currentFolder . '/' . $row['id_attach']. '_' . strtr($row['filename'], '.', '_') . md5($row['filename'])))
  109. $oldFile = $currentFolder . '/' . $row['id_attach']. '_' . strtr($row['filename'], '.', '_') . md5($row['filename']);
  110. else if (file_exists($currentFolder . '/' . $row['filename']));
  111. $oldFile = $currentFolder . '/' . $row['filename'];
  112. // Build the new file.
  113. $newFile = $currentFolder . '/' . $row['id_attach'] . '_' . $fileHash .'.dat';
  114. }
  115. // Just rename the file.
  116. else
  117. {
  118. $oldFile = $currentFolder . '/' . $row['id_attach'] . '_' . $row['file_hash'];
  119. $newFile = $currentFolder . '/' . $row['id_attach'] . '_' . $row['file_hash'] .'.dat';
  120. }
  121. // And we try to move it.
  122. rename($oldFile, $newFile);
  123. // Only update this if it was successful and the file was using the old system.
  124. if (empty($row['file_hash']) && !empty($fileHash) && file_exists($newFile) && !file_exists($oldFile))
  125. upgrade_query("
  126. UPDATE {$db_prefix}attachments
  127. SET file_hash = '$fileHash'
  128. WHERE id_attach = $row[id_attach]");
  129. }
  130. $smcFunc['db_free_result']($request);
  131. $_GET['a'] += 100;
  132. $step_progress['current'] = $_GET['a'];
  133. }
  134. unset($_GET['a']);
  135. ---}
  136. ---#
  137. /******************************************************************************/
  138. --- Adding support for IPv6...
  139. /******************************************************************************/
  140. ---# Adding new columns to ban items...
  141. ALTER TABLE {$db_prefix}ban_items
  142. ADD COLUMN ip_low5 smallint(255) unsigned NOT NULL DEFAULT '0',
  143. ADD COLUMN ip_high5 smallint(255) unsigned NOT NULL DEFAULT '0',
  144. ADD COLUMN ip_low6 smallint(255) unsigned NOT NULL DEFAULT '0',
  145. ADD COLUMN ip_high6 smallint(255) unsigned NOT NULL DEFAULT '0',
  146. ADD COLUMN ip_low7 smallint(255) unsigned NOT NULL DEFAULT '0',
  147. ADD COLUMN ip_high7 smallint(255) unsigned NOT NULL DEFAULT '0',
  148. ADD COLUMN ip_low8 smallint(255) unsigned NOT NULL DEFAULT '0',
  149. ADD COLUMN ip_high8 smallint(255) unsigned NOT NULL DEFAULT '0';
  150. ---#
  151. ---# Changing existing columns to ban items...
  152. ALTER TABLE {$db_prefix}ban_items
  153. CHANGE ip_low1 ip_low1 smallint(255) unsigned NOT NULL DEFAULT '0',
  154. CHANGE ip_high1 ip_high1 smallint(255) unsigned NOT NULL DEFAULT '0',
  155. CHANGE ip_low2 ip_low2 smallint(255) unsigned NOT NULL DEFAULT '0',
  156. CHANGE ip_high2 ip_high2 smallint(255) unsigned NOT NULL DEFAULT '0',
  157. CHANGE ip_low3 ip_low3 smallint(255) unsigned NOT NULL DEFAULT '0',
  158. CHANGE ip_high3 ip_high3 smallint(255) unsigned NOT NULL DEFAULT '0',
  159. CHANGE ip_low4 ip_low4 smallint(255) unsigned NOT NULL DEFAULT '0',
  160. CHANGE ip_high4 ip_high4 smallint(255) unsigned NOT NULL DEFAULT '0';
  161. ---#
  162. /******************************************************************************/
  163. --- Adding support for logging who fulfils a group request.
  164. /******************************************************************************/
  165. ---# Adding new columns to log_group_requests
  166. ALTER TABLE {$db_prefix}log_group_requests
  167. ADD COLUMN status smallint unsigned NOT NULL default '0',
  168. ADD COLUMN id_member_acted int unsigned NOT NULL default '0',
  169. ADD COLUMN member_name_acted varchar(255) NOT NULL default '',
  170. ADD COLUMN time_acted int unsigned NOT NULL default '0',
  171. ADD COLUMN act_reason text NOT NULL;
  172. ---#
  173. ---# Adjusting the indexes for log_group_requests
  174. DROP INDEX {$db_prefix}log_group_requests_id_member;
  175. CREATE INDEX {$db_prefix}log_group_requests_id_member ON {$db_prefix}log_group_requests (id_member, id_group);
  176. ---#
  177. /******************************************************************************/
  178. --- Adding support for <credits> tag in package manager
  179. /******************************************************************************/
  180. ---# Adding new columns to log_packages ..
  181. ALTER TABLE {$db_prefix}log_packages
  182. ADD COLUMN credits varchar(255) NOT NULL DEFAULT '';
  183. ---#
  184. /******************************************************************************/
  185. --- Adding more space for session ids
  186. /******************************************************************************/
  187. ---# Altering the session_id columns...
  188. ALTER TABLE {$db_prefix}log_online
  189. CHANGE `session` `session` varchar(64) NOT NULL DEFAULT '';
  190. ALTER TABLE {$db_prefix}log_errors
  191. CHANGE `session` `session` char(64) NOT NULL default ' ';
  192. ALTER TABLE {$db_prefix}sessions
  193. CHANGE `session_id` `session_id` char(64) NOT NULL;
  194. ---#
  195. /******************************************************************************/
  196. --- Adding new columns for MOVED topic updates
  197. /******************************************************************************/
  198. ---# Adding new custom fields columns.
  199. ---{
  200. $smcFunc['db_alter_table']('{db_prefix}topics', array(
  201. 'add' => array(
  202. 'redirect_expires' => array(
  203. 'name' => 'redirect_expires',
  204. 'null' => false,
  205. 'default' => '0',
  206. 'type' => 'int',
  207. 'auto' => false,
  208. ),
  209. )
  210. ));
  211. $smcFunc['db_alter_table']('{db_prefix}topics', array(
  212. 'add' => array(
  213. 'id_redirect_topic' => array(
  214. 'name' => 'id_redirect_topic',
  215. 'null' => false,
  216. 'default' => '0',
  217. 'type' => 'int',
  218. 'auto' => false,
  219. ),
  220. )
  221. ));
  222. ---}
  223. ---#
  224. /******************************************************************************/
  225. --- Adding new scheduled tasks
  226. /******************************************************************************/
  227. ---# Adding new scheduled tasks
  228. INSERT INTO {$db_prefix}scheduled_tasks
  229. (next_time, time_offset, time_regularity, time_unit, disabled, task)
  230. VALUES
  231. (0, 120, 1, 'd', 0, 'remove_temp_attachments');
  232. INSERT INTO {$db_prefix}scheduled_tasks
  233. (next_time, time_offset, time_regularity, time_unit, disabled, task)
  234. VALUES
  235. (0, 180, 1, 'd', 0, 'remove_topic_redirect');
  236. INSERT INTO {$db_prefix}scheduled_tasks
  237. (next_time, time_offset, time_regularity, time_unit, disabled, task)
  238. VALUES
  239. (0, 240, 1, 'd', 0, 'remove_old_drafts');
  240. ---#
  241. /******************************************************************************/
  242. ---- Adding background tasks support
  243. /******************************************************************************/
  244. ---# Adding the new table
  245. CREATE TABLE IF NOT EXISTS {$db_prefix}background_tasks (
  246. id_task integer primary key,
  247. task_file varchar(255) NOT NULL default '',
  248. task_class varchar(255) NOT NULL default '',
  249. task_data text NOT NULL,
  250. claimed_time int NOT NULL default '0',
  251. PRIMARY KEY (id_task)
  252. );
  253. ---#
  254. /******************************************************************************/
  255. ---- Replacing MSN with Skype
  256. /******************************************************************************/
  257. ---# Modifying the "msn" column...
  258. ALTER TABLE {$db_prefix}members
  259. CHANGE msn skype varchar(255) NOT NULL DEFAULT '';
  260. ---#
  261. /******************************************************************************/
  262. --- Adding support for deny boards access
  263. /******************************************************************************/
  264. ---# Adding new columns to boards...
  265. ---{
  266. $smcFunc['db_alter_table']('{db_prefix}boards', array(
  267. 'add' => array(
  268. 'deny_member_groups' => array(
  269. 'name' => 'deny_member_groups',
  270. 'null' => false,
  271. 'default' => '',
  272. 'type' => varchar,
  273. 'size' => 255,
  274. 'auto' => false,
  275. ),
  276. )
  277. ));
  278. ---}
  279. ---#
  280. /******************************************************************************/
  281. --- Adding support for category descriptions
  282. /******************************************************************************/
  283. ---# Adding new columns to categories...
  284. ---{
  285. $smcFunc['db_alter_table']('{db_prefix}log_topics', array(
  286. 'add' => array(
  287. 'description' => array(
  288. 'name' => 'description',
  289. 'null' => false,
  290. 'type' => 'text',
  291. 'auto' => false,
  292. ),
  293. )
  294. ));
  295. ---}
  296. ---#
  297. /******************************************************************************/
  298. --- Adding support for alerts
  299. /******************************************************************************/
  300. ---# Adding the count to the members table...
  301. ---{
  302. $smcFunc['db_alter_table']('{db_prefix}members', array(
  303. 'add' => array(
  304. 'alerts' => array(
  305. 'name' => 'ualerts',
  306. 'null' => false,
  307. 'default' => 0,
  308. 'type' => 'int',
  309. 'auto' => false,
  310. ),
  311. )
  312. ));
  313. ---}
  314. ---#
  315. ---# Adding the new table for alerts.
  316. CREATE TABLE IF NOT EXISTS {$db_prefix}user_alerts (
  317. id_alert int primary key,
  318. alert_time int unsigned NOT NULL default '0',
  319. id_member int unsigned NOT NULL default '0',
  320. id_member_started int unsigned NOT NULL default '0',
  321. member_name varchar(255) NOT NULL default '',
  322. content_type varchar(255) NOT NULL default '',
  323. content_id int unsigned NOT NULL default '0',
  324. content_action varchar(255) NOT NULL default '',
  325. is_read smallint unsigned NOT NULL default '0',
  326. extra text NOT NULL,
  327. PRIMARY KEY (id_alert)
  328. );
  329. CREATE INDEX {$db_prefix}user_alerts_id_member ON {$db_prefix}user_alerts (id_member);
  330. CREATE INDEX {$db_prefix}user_alerts_alert_time ON {$db_prefix}user_alerts (alert_time);
  331. ---#
  332. ---# Adding alert preferences.
  333. CREATE TABLE IF NOT EXISTS {$db_prefix}user_alerts_prefs (
  334. id_member int unsigned NOT NULL default '0',
  335. alert_pref varchar(32) NOT NULL default '',
  336. alert_value tinyint(3) NOT NULL default '0',
  337. PRIMARY KEY (id_member, alert_pref)
  338. );
  339. INSERT INTO {$db_prefix}user_alerts_prefs (id_member, alert_pref, alert_value) VALUES (0, 'member_group_request', 1);
  340. INSERT INTO {$db_prefix}user_alerts_prefs (id_member, alert_pref, alert_value) VALUES (0, 'member_register', 1);
  341. INSERT INTO {$db_prefix}user_alerts_prefs (id_member, alert_pref, alert_value) VALUES (0, 'msg_like', 1);
  342. INSERT INTO {$db_prefix}user_alerts_prefs (id_member, alert_pref, alert_value) VALUES (0, 'msg_report', 1);
  343. INSERT INTO {$db_prefix}user_alerts_prefs (id_member, alert_pref, alert_value) VALUES (0, 'msg_report_reply', 1);
  344. ---#
  345. /******************************************************************************/
  346. --- Adding support for topic unwatch
  347. /******************************************************************************/
  348. ---# Adding new columns to log_topics...
  349. ---{
  350. $smcFunc['db_alter_table']('{db_prefix}log_topics', array(
  351. 'add' => array(
  352. 'unwatched' => array(
  353. 'name' => 'unwatched',
  354. 'null' => false,
  355. 'default' => 0,
  356. 'type' => 'int',
  357. 'auto' => false,
  358. ),
  359. )
  360. ));
  361. UPDATE {$db_prefix}log_topics
  362. SET unwatched = 0;
  363. INSERT INTO {$db_prefix}settings
  364. (variable, value)
  365. VALUES
  366. ('enable_unwatch', 0);
  367. ---}
  368. ---#
  369. ---# Fixing column name change...
  370. ---{
  371. $smcFunc['db_alter_table']('{db_prefix}log_topics', array(
  372. 'change' => array(
  373. 'disregarded' => array(
  374. 'name' => 'unwatched',
  375. 'null' => false,
  376. 'default' => 0,
  377. 'type' => 'int',
  378. 'auto' => false,
  379. ),
  380. )
  381. ));
  382. ---}
  383. ---#
  384. /******************************************************************************/
  385. --- Name changes
  386. /******************************************************************************/
  387. ---# Altering the membergroup stars to icons
  388. ---{
  389. upgrade_query("
  390. ALTER TABLE {$db_prefix}membergroups
  391. CHANGE `stars` `icons` varchar(255) NOT NULL DEFAULT ''");
  392. ---}
  393. ---#
  394. ---# Renaming default theme...
  395. UPDATE {$db_prefix}themes
  396. SET value = 'SMF Default Theme - Curve2'
  397. WHERE value LIKE 'SMF Default Theme%';
  398. ---#
  399. ---# Adding the enableThemes setting.
  400. INSERT INTO {$db_prefix}settings
  401. (variable, value)
  402. VALUES
  403. ('enableThemes', '1');
  404. ---#
  405. ---# Setting "default" as the default...
  406. UPDATE {$db_prefix}settings
  407. SET value = '1'
  408. WHERE variable = 'theme_guests';
  409. UPDATE {$db_prefix}boards
  410. SET id_theme = 0;
  411. UPDATE {$db_prefix}members
  412. SET id_theme = 0;
  413. ---#
  414. /******************************************************************************/
  415. --- Cleaning up after old themes...
  416. /******************************************************************************/
  417. ---# Checking for "core" and removing it if necessary...
  418. ---{
  419. // Do they have "core" installed?
  420. if (file_exists($GLOBALS['boarddir'] . '/Themes/core'))
  421. {
  422. $core_dir = $GLOBALS['boarddir'] . '/Themes/core';
  423. $theme_request = upgrade_query("
  424. SELECT id_theme
  425. FROM {$db_prefix}themes
  426. WHERE variable = 'theme_dir'
  427. AND value ='$core_dir'");
  428. // Don't do anything if this theme is already uninstalled
  429. if ($smcFunc['db_num_rows']($theme_request) == 1)
  430. {
  431. list($id_theme) = $smcFunc['db_fetch_row']($theme_request, 0);
  432. $smcFunc['db_free_result']($theme_request);
  433. $known_themes = explode(', ', $modSettings['knownThemes']);
  434. // Remove this value...
  435. $known_themes = array_diff($known_themes, array($id_theme));
  436. // Change back to a string...
  437. $known_themes = implode(', ', $known_themes);
  438. // Update the database
  439. upgrade_query("
  440. UPDATE {$db_prefix}settings
  441. SET value = '$known_themes'
  442. WHERE variable = 'knownThemes'");
  443. // Delete any info about this theme
  444. upgrade_query("
  445. DELETE FROM {$db_prefix}themes
  446. WHERE id_theme = $id_theme");
  447. }
  448. }
  449. /******************************************************************************/
  450. --- Adding support for drafts
  451. /******************************************************************************/
  452. ---# Creating drafts table.
  453. CREATE TABLE IF NOT EXISTS {$db_prefix}user_drafts (
  454. id_draft int unsigned NOT NULL auto_increment,
  455. id_topic int unsigned NOT NULL default '0',
  456. id_board smallint unsigned NOT NULL default '0',
  457. id_reply int unsigned NOT NULL default '0',
  458. type smallint NOT NULL default '0',
  459. poster_time int unsigned NOT NULL default '0',
  460. id_member int unsigned NOT NULL default '0',
  461. subject varchar(255) NOT NULL default '',
  462. smileys_enabled smallint NOT NULL default '1',
  463. body text NOT NULL,
  464. icon varchar(16) NOT NULL default 'xx',
  465. locked smallint NOT NULL default '0',
  466. is_sticky smallint NOT NULL default '0',
  467. to_list varchar(255) NOT NULL default '',
  468. PRIMARY KEY (id_draft)
  469. );
  470. CREATE UNIQUE INDEX {$db_prefix}user_drafts_id_member ON {$db_prefix}user_drafts (id_member, id_draft, type);
  471. ---#
  472. ---# Adding draft permissions...
  473. ---{
  474. // We cannot do this twice
  475. if (@$modSettings['smfVersion'] < '2.1')
  476. {
  477. // Anyone who can currently post unapproved topics we assume can create drafts as well ...
  478. $request = upgrade_query("
  479. SELECT id_group, id_board, add_deny, permission
  480. FROM {$db_prefix}board_permissions
  481. WHERE permission = 'post_unapproved_topics'");
  482. $inserts = array();
  483. while ($row = $smcFunc['db_fetch_assoc']($request))
  484. {
  485. $inserts[] = "($row[id_group], $row[id_board], 'post_draft', $row[add_deny])";
  486. $inserts[] = "($row[id_group], $row[id_board], 'post_autosave_draft', $row[add_deny])";
  487. }
  488. $smcFunc['db_free_result']($request);
  489. if (!empty($inserts))
  490. {
  491. foreach ($inserts AS $insert)
  492. {
  493. upgrade_query("
  494. INSERT INTO {$db_prefix}board_permissions
  495. (id_group, id_board, permission, add_deny)
  496. VALUES
  497. " . $insert);
  498. }
  499. }
  500. // Next we find people who can send PMs, and assume they can save pm_drafts as well
  501. $request = upgrade_query("
  502. SELECT id_group, add_deny, permission
  503. FROM {$db_prefix}permissions
  504. WHERE permission = 'pm_send'");
  505. $inserts = array();
  506. while ($row = $smcFunc['db_fetch_assoc']($request))
  507. {
  508. $inserts[] = "($row[id_group], 'pm_draft', $row[add_deny])";
  509. $inserts[] = "($row[id_group], 'pm_autosave_draft', $row[add_deny])";
  510. }
  511. $smcFunc['db_free_result']($request);
  512. if (!empty($inserts))
  513. {
  514. foreach ($inserts AS $insert)
  515. {
  516. upgrade_query("
  517. INSERT INTO {$db_prefix}permissions
  518. (id_group, permission, add_deny)
  519. VALUES
  520. " . $insert);
  521. }
  522. }
  523. }
  524. ---}
  525. INSERT INTO {$db_prefix}settings (variable, value) VALUES ('drafts_autosave_enabled', '1');
  526. INSERT INTO {$db_prefix}settings (variable, value) VALUES ('drafts_show_saved_enabled', '1');
  527. INSERT INTO {$db_prefix}settings (variable, value) VALUES ('drafts_keep_days', '7');
  528. INSERT INTO {$db_prefix}themes (id_theme, variable, value) VALUES ('1', 'drafts_autosave_enabled', '1');
  529. INSERT INTO {$db_prefix}themes (id_theme, variable, value) VALUES ('1', 'drafts_show_saved_enabled', '1');
  530. ---#
  531. /******************************************************************************/
  532. --- Adding support for likes
  533. /******************************************************************************/
  534. ---# Creating likes table.
  535. CREATE TABLE IF NOT EXISTS {$db_prefix}user_likes (
  536. id_member int NOT NULL default '0',
  537. content_type char(6) default '',
  538. content_id int NOT NULL default '0',
  539. like_time int NOT NULL default '0',
  540. PRIMARY KEY (content_id, content_type, id_member)
  541. );
  542. CREATE INDEX {$db_prefix}user_likes_content ON {$db_prefix}user_likes (content_id, content_type);
  543. CREATE INDEX {$db_prefix}user_likes_liker ON {$db_prefix}user_likes (id_member);
  544. ---#
  545. ---# Adding count to the messages table.
  546. ALTER TABLE {$db_prefix}messages
  547. ADD COLUMN likes smallint NOT NULL default '0';
  548. ---#
  549. /******************************************************************************/
  550. --- Adding support for group-based board moderation
  551. /******************************************************************************/
  552. ---# Creating moderator_groups table
  553. CREATE TABLE IF NOT EXISTS {$db_prefix}moderator_groups (
  554. id_board smallint NOT NULL default '0',
  555. id_group smallint NOT NULL default '0',
  556. PRIMARY KEY (id_board, id_group)
  557. );
  558. ---#
  559. /******************************************************************************/
  560. --- Cleaning up integration hooks
  561. /******************************************************************************/
  562. ---#
  563. DELETE FROM {$db_prefix}settings
  564. WHERE variable LIKE 'integrate_%';
  565. ---#
  566. /******************************************************************************/
  567. --- Cleaning up old settings
  568. /******************************************************************************/
  569. ---# Showing contact details to guests should never happen.
  570. DELETE FROM {$db_prefix}settings
  571. WHERE variable IN ('enableStickyTopics', 'guest_hideContacts', 'notify_new_registration', 'attachmentEncryptFilenames');
  572. ---#
  573. ---# Cleaning up old theme settings.
  574. DELETE FROM {$db_prefix}themes
  575. WHERE variable IN ('show_board_desc', 'no_new_reply_warning', 'display_quick_reply');
  576. ---#
  577. /******************************************************************************/
  578. --- Removing old Simple Machines files we do not need to fetch any more
  579. /******************************************************************************/
  580. ---# We no longer call on the latest packages list.
  581. DELETE FROM {$db_prefix}admin_info_files
  582. WHERE filename IN ('latest-packages.js', 'latest-support.js', 'latest-themes.js')
  583. AND path = '/smf/';
  584. ---#
  585. /******************************************************************************/
  586. --- Upgrading "verification questions" feature
  587. /******************************************************************************/
  588. ---# Creating qanda table
  589. CREATE TABLE IF NOT EXISTS {$db_prefix}qanda (
  590. id_question smallint(5) unsigned NOT NULL auto_increment,
  591. lngfile varchar(255) NOT NULL default '',
  592. question varchar(255) NOT NULL default '',
  593. answers text NOT NULL,
  594. PRIMARY KEY (id_question),
  595. KEY lngfile (lngfile)
  596. );
  597. ---#
  598. ---# Moving questions and answers to the new table
  599. ---{
  600. $questions = array();
  601. $get_questions = upgrade_query("
  602. SELECT body AS question, recipient_name AS answer
  603. FROM {$db_prefix}log_comments
  604. WHERE comment_type = 'ver_test'");
  605. while ($row = $smcFunc['db_fetch_assoc']($get_questions))
  606. $questions[] = array($language, $row['question'], serialize(array($row['answer'])));
  607. $smcFunc['db_free_result']($get_questions);
  608. if (!empty($questions))
  609. {
  610. $smcFunc['db_insert']('',
  611. '{db_prefix}qanda',
  612. array('lngfile' => 'string', 'question' => 'string', 'answers' => 'string'),
  613. $questions,
  614. array('id_question')
  615. );
  616. // Delete the questions from log_comments now
  617. upgrade_query("
  618. DELETE FROM {$db_prefix}log_comments
  619. WHERE comment_type = 'ver_test'
  620. ");
  621. }
  622. ---}
  623. ---#
  624. /******************************************************************************/
  625. --- Marking packages as uninstalled...
  626. /******************************************************************************/
  627. ---# Updating log_packages
  628. UPDATE {$db_prefix}log_packages
  629. SET install_state = 0;
  630. ---#
  631. /******************************************************************************/
  632. --- Updating profile permissions...
  633. /******************************************************************************/
  634. ---# Removing the old "view your own profile" permission
  635. DELETE FROM {$db_prefix}permissions
  636. WHERE permission = 'profile_view_own';
  637. ---#
  638. ---# Updating the old "view any profile" permission
  639. UPDATE {$db_prefix}permissions
  640. SET permission = 'profile_view'
  641. WHERE permission = 'profile_view_any';
  642. ---#
  643. ---# Removing the old notification permissions
  644. DELETE FROM {$db_prefix}board_permissions
  645. WHERE permission = 'mark_notify' OR permission = 'mark_any_notify';
  646. ---#
  647. ---# Adding "profile_password_own"
  648. ---{
  649. $inserts = array();
  650. $request = upgrade_query("
  651. SELECT id_group, add_deny
  652. FROM {$db_prefix}permissions
  653. WHERE permission = 'profile_identity_own'");
  654. while ($row = $smcFunc['db_fetch_assoc']($request))
  655. {
  656. $inserts[] = "($row[id_group], 'profile_password_own', $row[add_deny])";
  657. }
  658. $smcFunc['db_free_result']($request);
  659. if (!empty($inserts))
  660. {
  661. foreach ($inserts as $insert)
  662. {
  663. upgrade_query("
  664. INSERT INTO {$db_prefix}permissions
  665. (id_group, permission, add_deny)
  666. VALUES
  667. " . $insert);
  668. }
  669. }
  670. ---}
  671. ---#
  672. ---# Adding other profile permissions
  673. ---{
  674. $inserts = array();
  675. $request = upgrade_query("
  676. SELECT id_group, add_deny
  677. FROM {$db_prefix}permissions
  678. WHERE permission = 'profile_extra_own'");
  679. while ($row = $smcFunc['db_fetch_assoc']($request))
  680. {
  681. $inserts[] = "($row[id_group], 'profile_blurb_own', $row[add_deny])";
  682. $inserts[] = "($row[id_group], 'profile_displayed_name_own', $row[add_deny])";
  683. $inserts[] = "($row[id_group], 'profile_forum_own', $row[add_deny])";
  684. $inserts[] = "($row[id_group], 'profile_other_own', $row[add_deny])";
  685. $inserts[] = "($row[id_group], 'profile_signature_own', $row[add_deny])";
  686. }
  687. $smcFunc['db_free_result']($request);
  688. if (!empty($inserts))
  689. {
  690. foreach ($inserts as $insert)
  691. {
  692. upgrade_query("
  693. INSERT INTO {$db_prefix}permissions
  694. (id_group, permission, add_deny)
  695. VALUES
  696. " . $insert);
  697. }
  698. }
  699. ---}
  700. ---#
  701. /******************************************************************************/
  702. --- Upgrading PM labels...
  703. /******************************************************************************/
  704. ---# Adding pm_labels table...
  705. CREATE TABLE IF NOT EXISTS {$db_prefix}pm_labels (
  706. id_label integer primary key,
  707. id_member int NOT NULL default '0',
  708. name varchar(30) NOT NULL default '',
  709. );
  710. ---#
  711. ---# Adding pm_labeled_messages table...
  712. CREATE TABLE IF NOT EXISTS {$db_prefix}pm_labeled_messages (
  713. id_label int NOT NULL default '0',
  714. id_pm int NOT NULL default '0',
  715. PRIMARY KEY (id_label, id_pm)
  716. );
  717. ---#
  718. ---# Adding "in_inbox" column to pm_recipients
  719. ALTER TABLE {$db_prefix}pm_recipients
  720. ADD COLUMN in_inbox tinyint(3) NOT NULL default '1';
  721. ---#
  722. ---# Moving label info to new tables and updating rules...
  723. ---{
  724. // First see if we still have a message_labels column
  725. $results = $smcFunc['db_list_columns']('{db_prefix}members');
  726. if (in_array('message_labels', $results))
  727. {
  728. // They've still got it, so pull the label info
  729. $get_labels = $smcFunc['db_query']('', '
  730. SELECT id_member, message_labels
  731. FROM {db_prefix}members
  732. WHERE message_labels != {string:blank}',
  733. array(
  734. 'blank' => '',
  735. )
  736. );
  737. $inserts = array();
  738. $label_info = array();
  739. while ($row = $smcFunc['db_fetch_assoc']($get_labels))
  740. {
  741. // Stick this in an array
  742. $labels = explode(',', $row['message_labels']);
  743. // Build some inserts
  744. foreach ($labels AS $index => $label)
  745. {
  746. // Keep track of the index of this label - we'll need that in a bit...
  747. $label_info[$row['id_member']][$label] = $index;
  748. $inserts[] = array($row['id_member'], $label);
  749. }
  750. }
  751. $smcFunc['db_free_result']($get_labels);
  752. if (!empty($inserts))
  753. {
  754. $smcFunc['db_insert']('', '{db_prefix}pm_labels', array('id_member' => 'int', 'name' => 'int'), $inserts, array());
  755. // Clear this out for our next query below
  756. $inserts = array();
  757. }
  758. // This is the easy part - update the inbox stuff
  759. $smcFunc['db_query']('', '
  760. UPDATE {db_prefix}pm_recipients
  761. SET in_inbox = {int:in_inbox}
  762. WHERE FIND_IN_SET({int:minus_one}, labels)',
  763. array(
  764. 'in_inbox' => 1,
  765. 'minus_one' => -1,
  766. )
  767. );
  768. // Now we go pull the new IDs for each label
  769. $get_new_label_ids = $smcFunc['db_query']('', '
  770. SELECT *
  771. FROM {db_prefix}pm_labels',
  772. array(
  773. )
  774. );
  775. $label_info_2 = array();
  776. while ($label_row = $smcFunc['db_fetch_assoc']($get_new_label_ids))
  777. {
  778. // Map the old index values to the new ID values...
  779. $old_index = $label_info[$row['id_member']][$row['label_name']];
  780. $label_info_2[$row['id_member']][$old_index] = $row['id_label'];
  781. }
  782. $smcFunc['db_free_result']($get_new_label_ids);
  783. // Pull label info from pm_recipients
  784. // Ignore any that are only in the inbox
  785. $get_pm_labels = $smcFunc['db_query']('', '
  786. SELECT id_pm, id_member, labels
  787. FROM {db_prefix}pm_recipients
  788. WHERE deleted = {int:not_deleted}
  789. AND labels != {string:minus_one}',
  790. array(
  791. 'not_deleted' => 0,
  792. 'minus_one' => -1,
  793. )
  794. );
  795. while ($row = $smcFunc['db_fetch_assoc']($get_pm_labels))
  796. {
  797. $labels = explode(',', $row['labels']);
  798. foreach ($labels as $a_label)
  799. {
  800. if ($a_label == '-1')
  801. continue;
  802. $new_label_info = $label_info_2[$row['id_member']][$a_label];
  803. $inserts[] = array($row['id_pm'], $new_label_info);
  804. }
  805. }
  806. $smcFunc['db_free_result']($get_pm_labels);
  807. // Insert the new data
  808. if (!empty($inserts))
  809. {
  810. $smcFunc['db_insert']('', '{db_prefix}pm_labeled_messages', array('id_pm', 'id_label'), $inserts, array());
  811. }
  812. // Final step of this ridiculously massive process
  813. $get_pm_rules = $smcFunc['db_query']('', '
  814. SELECT id_member, id_rule, actions
  815. FROM {db_prefix}pm_rules',
  816. array(
  817. ),
  818. );
  819. // Go through the rules, unserialize the actions, then figure out if there's anything we can use
  820. while ($row = $smcFunc['db_fetch_assoc']($get_pm_rules))
  821. {
  822. // Turn this into an array...
  823. $actions = unserialize($row['actions']);
  824. // Loop through the actions and see if we're applying a label anywhere
  825. foreach ($actions as $index => $action)
  826. {
  827. if ($action['t'] == 'lab')
  828. {
  829. // Update the value of this label...
  830. $actions[$index]['v'] = $label_info_2[$row['id_member']][$action['v']];
  831. }
  832. }
  833. // Put this back into a string
  834. $actions = serialize($actions);
  835. $smcFunc['db_query']('', '
  836. UPDATE {db_prefix}pm_rules
  837. SET actions = {string:actions}
  838. WHERE id_rule = {int:id_rule}',
  839. array(
  840. 'actions' => $actions,
  841. 'id_rule' => $row['id_rule'],
  842. )
  843. );
  844. }
  845. $smcFunc['db_free_result']($get_pm_rules);
  846. // Lastly, we drop the old columns
  847. $smcFunc['db_remove_column']('{db_prefix}members', 'message_labels');
  848. $smcFunc['db_remove_column']('{db_prefix}pm_recipients', 'labels');
  849. }
  850. }
  851. ******************************************************************************/
  852. --- Adding support for edit reasons
  853. /******************************************************************************/
  854. ---# Adding "modified_reason" column to messages
  855. ALTER TABLE {$db_prefix}messages
  856. ADD COLUMN modified_reason varchar(255) NOT NULL default '';
  857. ---#
  858. /******************************************************************************/
  859. --- Cleaning up guest permissions
  860. /******************************************************************************/
  861. ---# Removing permissions guests can no longer have...
  862. ---{
  863. $illegal_board_permissions = array(
  864. 'announce_topic',
  865. 'delete_any',
  866. 'lock_any',
  867. 'make_sticky',
  868. 'merge_any',
  869. 'modify_any',
  870. 'modify_replies',
  871. 'move_any',
  872. 'poll_add_any',
  873. 'poll_edit_any',
  874. 'poll_lock_any',
  875. 'poll_remove_any',
  876. 'remove_any',
  877. 'report_any',
  878. 'split_any'
  879. );
  880. $illegal_permissions = array('calendar_edit_any', 'moderate_board', 'moderate_forum', 'send_email_to_members');
  881. $smcFunc['db_query']('', '
  882. DELETE FROM {db_prefix}board_permissions
  883. WHERE id_group = {int:guests}
  884. AND permission IN ({array_string:illegal_board_perms})',
  885. array(
  886. 'guests' => -1,
  887. 'illegal_board_perms' => $illegal_board_permissions,
  888. )
  889. );
  890. $smcFunc['db_query']('', '
  891. DELETE FROM {db_prefix}permissions
  892. WHERE id_group = {int:guests}
  893. AND permission IN ({array_string:illegal_perms})',
  894. array(
  895. 'guests' => -1,
  896. 'illegal_perms' => $illegal_permissions,
  897. )
  898. );
  899. ---}
  900. ---#
  901. /******************************************************************************/
  902. --- Adding mail queue settings
  903. /******************************************************************************/
  904. ---#
  905. ---{
  906. if (empty($modSettings['mail_limit']))
  907. {
  908. $smcFunc['db_insert']('replace',
  909. '{db_prefix}settings',
  910. array('variable' => 'string-255', 'value' => 'string'),
  911. array(
  912. array('mail_limit', '5'),
  913. array('mail_quantity', '5'),
  914. ),
  915. array('variable')
  916. );
  917. }
  918. ---}
  919. ---#