upgrade_2-1_postgresql.sql 43 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001011021031041051061071081091101111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521531541551561571581591601611621631641651661671681691701711721731741751761771781791801811821831841851861871881891901911921931941951961971981992002012022032042052062072082092102112122132142152162172182192202212222232242252262272282292302312322332342352362372382392402412422432442452462472482492502512522532542552562572582592602612622632642652662672682692702712722732742752762772782792802812822832842852862872882892902912922932942952962972982993003013023033043053063073083093103113123133143153163173183193203213223233243253263273283293303313323333343353363373383393403413423433443453463473483493503513523533543553563573583593603613623633643653663673683693703713723733743753763773783793803813823833843853863873883893903913923933943953963973983994004014024034044054064074084094104114124134144154164174184194204214224234244254264274284294304314324334344354364374384394404414424434444454464474484494504514524534544554564574584594604614624634644654664674684694704714724734744754764774784794804814824834844854864874884894904914924934944954964974984995005015025035045055065075085095105115125135145155165175185195205215225235245255265275285295305315325335345355365375385395405415425435445455465475485495505515525535545555565575585595605615625635645655665675685695705715725735745755765775785795805815825835845855865875885895905915925935945955965975985996006016026036046056066076086096106116126136146156166176186196206216226236246256266276286296306316326336346356366376386396406416426436446456466476486496506516526536546556566576586596606616626636646656666676686696706716726736746756766776786796806816826836846856866876886896906916926936946956966976986997007017027037047057067077087097107117127137147157167177187197207217227237247257267277287297307317327337347357367377387397407417427437447457467477487497507517527537547557567577587597607617627637647657667677687697707717727737747757767777787797807817827837847857867877887897907917927937947957967977987998008018028038048058068078088098108118128138148158168178188198208218228238248258268278288298308318328338348358368378388398408418428438448458468478488498508518528538548558568578588598608618628638648658668678688698708718728738748758768778788798808818828838848858868878888898908918928938948958968978988999009019029039049059069079089099109119129139149159169179189199209219229239249259269279289299309319329339349359369379389399409419429439449459469479489499509519529539549559569579589599609619629639649659669679689699709719729739749759769779789799809819829839849859869879889899909919929939949959969979989991000100110021003100410051006100710081009101010111012101310141015101610171018101910201021102210231024102510261027102810291030103110321033103410351036103710381039104010411042104310441045104610471048104910501051105210531054105510561057105810591060106110621063106410651066106710681069107010711072107310741075107610771078107910801081108210831084108510861087108810891090109110921093109410951096109710981099110011011102110311041105110611071108110911101111111211131114111511161117111811191120112111221123112411251126112711281129113011311132113311341135113611371138113911401141114211431144114511461147114811491150115111521153115411551156115711581159116011611162116311641165116611671168116911701171117211731174117511761177117811791180118111821183118411851186118711881189119011911192119311941195119611971198119912001201120212031204120512061207120812091210121112121213121412151216121712181219122012211222122312241225122612271228122912301231123212331234123512361237123812391240124112421243124412451246124712481249125012511252125312541255125612571258125912601261126212631264126512661267126812691270127112721273127412751276127712781279128012811282128312841285128612871288128912901291129212931294129512961297129812991300130113021303130413051306130713081309131013111312131313141315131613171318131913201321132213231324132513261327132813291330133113321333133413351336133713381339134013411342134313441345134613471348134913501351135213531354135513561357135813591360136113621363136413651366136713681369137013711372137313741375137613771378
  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 int 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. ---# Copying the current "allow users to disable word censor" setting...
  29. ---{
  30. if (!isset($modSettings['allow_no_censored']))
  31. {
  32. $request = upgrade_query("
  33. SELECT value
  34. FROM {$db_prefix}themes
  35. WHERE variable='allow_no_censored'
  36. AND id_theme = 1 OR id_theme = '$modSettings[theme_default]'
  37. ");
  38. // Is it set for either "default" or the one they've set as default?
  39. while ($row = $smcFunc['db_fetch_assoc']($request))
  40. {
  41. if ($row['value'] == 1)
  42. {
  43. upgrade_query("
  44. INSERT INTO {$db_prefix}settings
  45. VALUES ('allow_no_censored', 1)
  46. ");
  47. // Don't do this twice...
  48. break;
  49. }
  50. }
  51. }
  52. ---}
  53. ---#
  54. ---# Adding new "topic_move_any" setting
  55. INSERT INTO {$db_prefix}settings (variable, value) VALUES ('topic_move_any', '1');
  56. ---#
  57. ---# Adding new "browser_cache" setting
  58. INSERT INTO {$db_prefix}settings (variable, value) VALUES ('browser_cache', '?alph21');
  59. ---#
  60. /******************************************************************************/
  61. --- Updating legacy attachments...
  62. /******************************************************************************/
  63. ---# Converting legacy attachments.
  64. ---{
  65. // Need to know a few things first.
  66. $custom_av_dir = !empty($modSettings['custom_avatar_dir']) ? $modSettings['custom_avatar_dir'] : $GLOBALS['boarddir'] .'/custom_avatar';
  67. // This little fellow has to cooperate...
  68. if (!is_writable($custom_av_dir))
  69. @chmod($custom_av_dir, 0777);
  70. $request = upgrade_query("
  71. SELECT MAX(id_attach)
  72. FROM {$db_prefix}attachments");
  73. list ($step_progress['total']) = $smcFunc['db_fetch_row']($request);
  74. $smcFunc['db_free_result']($request);
  75. $_GET['a'] = isset($_GET['a']) ? (int) $_GET['a'] : 0;
  76. $step_progress['name'] = 'Converting legacy attachments';
  77. $step_progress['current'] = $_GET['a'];
  78. // We may be using multiple attachment directories.
  79. if (!empty($modSettings['currentAttachmentUploadDir']) && !is_array($modSettings['attachmentUploadDir']))
  80. $modSettings['attachmentUploadDir'] = unserialize($modSettings['attachmentUploadDir']);
  81. $is_done = false;
  82. while (!$is_done)
  83. {
  84. nextSubStep($substep);
  85. $fileHash = '';
  86. $request = upgrade_query("
  87. SELECT id_attach, id_folder, filename, file_hash, mime_type
  88. FROM {$db_prefix}attachments
  89. WHERE attachment_type != 1
  90. LIMIT $_GET[a], 100");
  91. // Finished?
  92. if ($smcFunc['db_num_rows']($request) == 0)
  93. $is_done = true;
  94. while ($row = $smcFunc['db_fetch_assoc']($request))
  95. {
  96. // The current folder.
  97. $currentFolder = !empty($modSettings['currentAttachmentUploadDir']) ? $modSettings['attachmentUploadDir'][$row['id_folder']] : $modSettings['attachmentUploadDir'];
  98. // Old School?
  99. if (empty($row['file_hash']))
  100. {
  101. // Remove international characters (windows-1252)
  102. // These lines should never be needed again. Still, behave.
  103. if (empty($db_character_set) || $db_character_set != 'utf8')
  104. {
  105. $row['filename'] = strtr($row['filename'],
  106. "\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",
  107. 'SZszYAAAAAACEEEEIIIINOOOOOOUUUUYaaaaaaceeeeiiiinoooooouuuuyy');
  108. $row['filename'] = strtr($row['filename'], array("\xde" => 'TH', "\xfe" =>
  109. 'th', "\xd0" => 'DH', "\xf0" => 'dh', "\xdf" => 'ss', "\x8c" => 'OE',
  110. "\x9c" => 'oe', "\xc6" => 'AE', "\xe6" => 'ae', "\xb5" => 'u'));
  111. }
  112. // Sorry, no spaces, dots, or anything else but letters allowed.
  113. $row['filename'] = preg_replace(array('/\s/', '/[^\w_\.\-]/'), array('_', ''), $row['filename']);
  114. // Create a nice hash.
  115. $fileHash = sha1(md5($row['filename'] . time()) . mt_rand());
  116. // The old file, we need to know if the filename was encrypted or not.
  117. if (file_exists($currentFolder . '/' . $row['id_attach']. '_' . strtr($row['filename'], '.', '_') . md5($row['filename'])))
  118. $oldFile = $currentFolder . '/' . $row['id_attach']. '_' . strtr($row['filename'], '.', '_') . md5($row['filename']);
  119. else if (file_exists($currentFolder . '/' . $row['filename']));
  120. $oldFile = $currentFolder . '/' . $row['filename'];
  121. // Build the new file.
  122. $newFile = $currentFolder . '/' . $row['id_attach'] . '_' . $fileHash .'.dat';
  123. }
  124. // Just rename the file.
  125. else
  126. {
  127. $oldFile = $currentFolder . '/' . $row['id_attach'] . '_' . $row['file_hash'];
  128. $newFile = $currentFolder . '/' . $row['id_attach'] . '_' . $row['file_hash'] .'.dat';
  129. }
  130. // Check if the av is an attachment
  131. if ($row['id_member'] != 0)
  132. if (rename($oldFile, $custom_av_dir . '/' . $row['filename']))
  133. upgrade_query("
  134. UPDATE {$db_prefix}attachments
  135. SET file_hash = '', attachment_type = 1
  136. WHERE id_attach = $row[id_attach]");
  137. // Just a regular attachment.
  138. else
  139. rename($oldFile, $newFile);
  140. // Only update this if it was successful and the file was using the old system.
  141. if (empty($row['file_hash']) && !empty($fileHash) && file_exists($newFile) && !file_exists($oldFile))
  142. upgrade_query("
  143. UPDATE {$db_prefix}attachments
  144. SET file_hash = '$fileHash'
  145. WHERE id_attach = $row[id_attach]");
  146. // While we're here, do we need to update the mime_type?
  147. if (empty($row['mime_type']) && file_exists($newFile))
  148. {
  149. $size = @getimagesize($newFile);
  150. if (!empty($size['mime']))
  151. $smcFunc['db_query']('', '
  152. UPDATE {db_prefix}attachments
  153. SET mime_type = {string:mime_type}
  154. WHERE id_attach = {int:id_attach}',
  155. array(
  156. 'id_attach' => $row['id_attach'],
  157. 'mime_type' => substr($size['mime'], 0, 20),
  158. )
  159. );
  160. }
  161. }
  162. $smcFunc['db_free_result']($request);
  163. $_GET['a'] += 100;
  164. $step_progress['current'] = $_GET['a'];
  165. }
  166. unset($_GET['a']);
  167. ---}
  168. ---#
  169. ---# Fixing invalid sizes on attachments
  170. ---{
  171. $attachs = array();
  172. // If id_member = 0, then it's not an avatar
  173. // If attachment_type = 0, then it's also not a thumbnail
  174. // Theory says there shouldn't be *that* many of these
  175. $request = $smcFunc['db_query']('', '
  176. SELECT id_attach, mime_type, width, height
  177. FROM {db_prefix}attachments
  178. WHERE id_member = 0
  179. AND attachment_type = 0');
  180. while ($row = $smcFunc['db_fetch_assoc']($request))
  181. {
  182. if (($row['width'] > 0 || $row['height'] > 0) && strpos($row['mime_type'], 'image') !== 0)
  183. $attachs[] = $row['id_attach'];
  184. }
  185. $smcFunc['db_free_result']($request);
  186. if (!empty($attachs))
  187. $smcFunc['db_query']('', '
  188. UPDATE {db_prefix}attachments
  189. SET width = 0,
  190. height = 0
  191. WHERE id_attach IN ({array_int:attachs})',
  192. array(
  193. 'attachs' => $attachs,
  194. )
  195. );
  196. ---}
  197. ---#
  198. /******************************************************************************/
  199. --- Adding support for IPv6...
  200. /******************************************************************************/
  201. ---# Adding new columns to ban items...
  202. ALTER TABLE {$db_prefix}ban_items
  203. ADD COLUMN ip_low5 smallint NOT NULL DEFAULT '0',
  204. ADD COLUMN ip_high5 smallint NOT NULL DEFAULT '0',
  205. ADD COLUMN ip_low6 smallint NOT NULL DEFAULT '0',
  206. ADD COLUMN ip_high6 smallint NOT NULL DEFAULT '0',
  207. ADD COLUMN ip_low7 smallint NOT NULL DEFAULT '0',
  208. ADD COLUMN ip_high7 smallint NOT NULL DEFAULT '0',
  209. ADD COLUMN ip_low8 smallint NOT NULL DEFAULT '0',
  210. ADD COLUMN ip_high8 smallint NOT NULL DEFAULT '0';
  211. ---#
  212. ---# Changing existing columns to ban items...
  213. ---{
  214. upgrade_query("
  215. ALTER TABLE {$db_prefix}ban_items
  216. ALTER COLUMN ip_low1 type smallint,
  217. ALTER COLUMN ip_high1 type smallint,
  218. ALTER COLUMN ip_low2 type smallint,
  219. ALTER COLUMN ip_high2 type smallint,
  220. ALTER COLUMN ip_low3 type smallint,
  221. ALTER COLUMN ip_high3 type smallint,
  222. ALTER COLUMN ip_low4 type smallint,
  223. ALTER COLUMN ip_high4 type smallint;"
  224. );
  225. upgrade_query("
  226. ALTER TABLE {$db_prefix}ban_items
  227. ALTER COLUMN ip_low1 SET DEFAULT '0',
  228. ALTER COLUMN ip_high1 SET DEFAULT '0',
  229. ALTER COLUMN ip_low2 SET DEFAULT '0',
  230. ALTER COLUMN ip_high2 SET DEFAULT '0',
  231. ALTER COLUMN ip_low3 SET DEFAULT '0',
  232. ALTER COLUMN ip_high3 SET DEFAULT '0',
  233. ALTER COLUMN ip_low4 SET DEFAULT '0',
  234. ALTER COLUMN ip_high4 SET DEFAULT '0';"
  235. );
  236. upgrade_query("
  237. ALTER TABLE {$db_prefix}ban_items
  238. ALTER COLUMN ip_low1 SET NOT NULL,
  239. ALTER COLUMN ip_high1 SET NOT NULL,
  240. ALTER COLUMN ip_low2 SET NOT NULL,
  241. ALTER COLUMN ip_high2 SET NOT NULL,
  242. ALTER COLUMN ip_low3 SET NOT NULL,
  243. ALTER COLUMN ip_high3 SET NOT NULL,
  244. ALTER COLUMN ip_low4 SET NOT NULL,
  245. ALTER COLUMN ip_high4 SET NOT NULL;"
  246. );
  247. ---}
  248. ---#
  249. /******************************************************************************/
  250. --- Adding support for logging who fulfils a group request.
  251. /******************************************************************************/
  252. ---# Adding new columns to log_group_requests
  253. ALTER TABLE {$db_prefix}log_group_requests
  254. ADD COLUMN status smallint NOT NULL default '0',
  255. ADD COLUMN id_member_acted int NOT NULL default '0',
  256. ADD COLUMN member_name_acted varchar(255) NOT NULL default '',
  257. ADD COLUMN time_acted int NOT NULL default '0',
  258. ADD COLUMN act_reason text NOT NULL;
  259. ---#
  260. ---# Adjusting the indexes for log_group_requests
  261. DROP INDEX {$db_prefix}log_group_requests_id_member;
  262. CREATE INDEX {$db_prefix}log_group_requests_id_member ON {$db_prefix}log_group_requests (id_member, id_group);
  263. ---#
  264. /******************************************************************************/
  265. --- Adding support for <credits> tag in package manager
  266. /******************************************************************************/
  267. ---# Adding new columns to log_packages ..
  268. ALTER TABLE {$db_prefix}log_packages
  269. ADD COLUMN credits varchar(255) NOT NULL DEFAULT '';
  270. ---#
  271. /******************************************************************************/
  272. --- Adding more space for session ids
  273. /******************************************************************************/
  274. ---# Altering the session_id columns...
  275. ---{
  276. upgrade_query("
  277. ALTER TABLE {$db_prefix}log_online
  278. ALTER COLUMN session type varchar(64);
  279. ALTER TABLE {$db_prefix}log_errors
  280. ALTER COLUMN session type char(64);
  281. ALTER TABLE {$db_prefix}sessions
  282. ALTER COLUMN session_id type char(64);");
  283. upgrade_query("
  284. ALTER TABLE {$db_prefix}log_online
  285. ALTER COLUMN session SET DEFAULT '';
  286. ALTER TABLE {$db_prefix}log_errors
  287. ALTER COLUMN session SET default ' ';");
  288. upgrade_query("
  289. ALTER TABLE {$db_prefix}log_online
  290. ALTER COLUMN session SET NOT NULL;
  291. ALTER TABLE {$db_prefix}log_errors
  292. ALTER COLUMN session SET NOT NULL;
  293. ALTER TABLE {$db_prefix}sessions
  294. ALTER COLUMN session_id SET NOT NULL;");
  295. ---}
  296. ---#
  297. /******************************************************************************/
  298. --- Adding support for MOVED topics enhancements
  299. /******************************************************************************/
  300. ---# Adding new columns to topics table
  301. ---{
  302. upgrade_query("
  303. ALTER TABLE {$db_prefix}topics
  304. ADD COLUMN redirect_expires int NOT NULL DEFAULT '0'");
  305. upgrade_query("
  306. ALTER TABLE {$db_prefix}topics
  307. ADD COLUMN id_redirect_topic int NOT NULL DEFAULT '0'");
  308. ---}
  309. ---#
  310. /******************************************************************************/
  311. --- Adding new scheduled tasks
  312. /******************************************************************************/
  313. ---# Adding new scheduled tasks
  314. INSERT INTO {$db_prefix}scheduled_tasks
  315. (next_time, time_offset, time_regularity, time_unit, disabled, task)
  316. VALUES
  317. (0, 120, 1, 'd', 0, 'remove_temp_attachments');
  318. INSERT INTO {$db_prefix}scheduled_tasks
  319. (next_time, time_offset, time_regularity, time_unit, disabled, task)
  320. VALUES
  321. (0, 180, 1, 'd', 0, 'remove_topic_redirect');
  322. INSERT INTO {$db_prefix}scheduled_tasks
  323. (next_time, time_offset, time_regularity, time_unit, disabled, task)
  324. VALUES
  325. (0, 240, 1, 'd', 0, 'remove_old_drafts');
  326. ---#
  327. /******************************************************************************/
  328. ---- Adding background tasks support
  329. /******************************************************************************/
  330. ---# Adding the sequence
  331. CREATE SEQUENCE {$db_prefix}background_tasks_seq;
  332. ---#
  333. ---# Adding the table
  334. CREATE TABLE {$db_prefix}background_tasks (
  335. id_task int default nextval('{$db_prefix}background_tasks_seq'),
  336. task_file varchar(255) NOT NULL default '',
  337. task_class varchar(255) NOT NULL default '',
  338. task_data text NOT NULL,
  339. claimed_time int NOT NULL default '0',
  340. PRIMARY KEY (id_task)
  341. );
  342. ---#
  343. /******************************************************************************/
  344. --- Adding support for deny boards access
  345. /******************************************************************************/
  346. ---# Adding new columns to boards...
  347. ---{
  348. upgrade_query("
  349. ALTER TABLE {$db_prefix}boards
  350. ADD COLUMN deny_member_groups varchar(255) NOT NULL DEFAULT ''");
  351. ---}
  352. ---#
  353. /******************************************************************************/
  354. --- Updating board access rules
  355. /******************************************************************************/
  356. ---# Updating board access rules
  357. ---{
  358. $member_groups = array(
  359. 'allowed' => array(),
  360. 'denied' => array(),
  361. );
  362. $request = $smcFunc['db_query']('', '
  363. SELECT id_group, add_deny
  364. FROM {db_prefix}permissions
  365. WHERE permission = {string:permission}',
  366. array(
  367. 'permission' => 'manage_boards',
  368. )
  369. );
  370. while ($row = $smcFunc['db_fetch_assoc']($request))
  371. $member_groups[$row['add_deny'] === '1' ? 'allowed' : 'denied'][] = $row['id_group'];
  372. $smcFunc['db_free_result']($request);
  373. $member_groups = array_diff($member_groups['allowed'], $member_groups['denied']);
  374. if (!empty($member_groups))
  375. {
  376. $count = count($member_groups);
  377. $changes = array();
  378. $request = $smcFunc['db_query']('', '
  379. SELECT id_board, member_groups
  380. FROM {db_prefix}boards');
  381. while ($row = $smcFunc['db_fetch_assoc']($request))
  382. {
  383. $current_groups = explode(',', $row['member_groups']);
  384. if (count(array_intersect($current_groups, $member_groups)) != $count)
  385. {
  386. $new_groups = array_unique(array_merge($current_groups, $member_groups));
  387. $changes[$row['id_board']] = implode(',', $new_groups);
  388. }
  389. }
  390. $smcFunc['db_free_result']($request);
  391. if (!empty($changes))
  392. {
  393. foreach ($changes as $id_board => $member_groups)
  394. $smcFunc['db_query']('', '
  395. UPDATE {db_prefix}boards
  396. SET member_groups = {string:member_groups}
  397. WHERE id_board = {int:id_board}',
  398. array(
  399. 'member_groups' => $member_groups,
  400. 'id_board' => $id_board,
  401. )
  402. );
  403. }
  404. }
  405. ---}
  406. ---#
  407. /******************************************************************************/
  408. --- Adding support for category descriptions
  409. /******************************************************************************/
  410. ---# Adding new columns to categories...
  411. ---{
  412. // Sadly, PostgreSQL whines if we add a NOT NULL column without a default value to an existing table...
  413. upgrade_query("
  414. ALTER TABLE {$db_prefix}categories
  415. ADD COLUMN description text");
  416. upgrade_query("
  417. UPDATE {$db_prefix}categories
  418. SET description = ''");
  419. upgrade_query("
  420. ALTER TABLE {$db_prefix}categories
  421. ALTER COLUMN description SET NOT NULL");
  422. ---}
  423. ---#
  424. /******************************************************************************/
  425. --- Adding support for alerts
  426. /******************************************************************************/
  427. ---# Adding the count to the members table...
  428. ALTER TABLE {$db_prefix}members
  429. ADD COLUMN alerts int NOT NULL default '0';
  430. ---#
  431. ---# Adding the new table for alerts.
  432. CREATE SEQUENCE {$db_prefix}user_alerts_seq;
  433. CREATE TABLE {$db_prefix}user_alerts (
  434. id_alert int default nextval('{$db_prefix}user_alerts_seq'),
  435. alert_time int NOT NULL default '0',
  436. id_member int NOT NULL default '0',
  437. id_member_started int NOT NULL default '0',
  438. member_name varchar(255) NOT NULL default '',
  439. content_type varchar(255) NOT NULL default '',
  440. content_id int NOT NULL default '0',
  441. content_action varchar(255) NOT NULL default '',
  442. is_read int NOT NULL default '0',
  443. extra text NOT NULL,
  444. PRIMARY KEY (id_alert)
  445. );
  446. CREATE INDEX {$db_prefix}user_alerts_id_member ON {$db_prefix}user_alerts (id_member);
  447. CREATE INDEX {$db_prefix}user_alerts_alert_time ON {$db_prefix}user_alerts (alert_time);
  448. ---#
  449. ---# Adding alert preferences.
  450. CREATE TABLE {$db_prefix}user_alerts_prefs (
  451. id_member int NOT NULL default '0',
  452. alert_pref varchar(32) NOT NULL default '',
  453. alert_value smallint NOT NULL default '0',
  454. PRIMARY KEY (id_member, alert_pref)
  455. );
  456. INSERT INTO {$db_prefix}user_alerts_prefs (id_member, alert_pref, alert_value) VALUES (0, 'member_group_request', 1);
  457. INSERT INTO {$db_prefix}user_alerts_prefs (id_member, alert_pref, alert_value) VALUES (0, 'member_register', 1);
  458. INSERT INTO {$db_prefix}user_alerts_prefs (id_member, alert_pref, alert_value) VALUES (0, 'msg_like', 1);
  459. INSERT INTO {$db_prefix}user_alerts_prefs (id_member, alert_pref, alert_value) VALUES (0, 'msg_report', 1);
  460. INSERT INTO {$db_prefix}user_alerts_prefs (id_member, alert_pref, alert_value) VALUES (0, 'msg_report_reply', 1);
  461. ---#
  462. /******************************************************************************/
  463. --- Adding support for topic unwatch
  464. /******************************************************************************/
  465. ---# Adding new columns to log_topics...
  466. ALTER TABLE {$db_prefix}log_topics
  467. ADD COLUMN unwatched int NOT NULL DEFAULT '0';
  468. UPDATE {$db_prefix}log_topics
  469. SET unwatched = 0;
  470. INSERT INTO {$db_prefix}settings
  471. (variable, value)
  472. VALUES
  473. ('enable_unwatch', 0);
  474. ---#
  475. ---# Fixing column name change...
  476. ---{
  477. upgrade_query("
  478. ALTER TABLE {$db_prefix}log_topics
  479. RENAME disregarded TO unwatched");
  480. ---}
  481. ---#
  482. /******************************************************************************/
  483. --- Name changes
  484. /******************************************************************************/
  485. ---# Altering the membergroup stars to icons
  486. ---{
  487. upgrade_query("
  488. ALTER TABLE {$db_prefix}membergroups
  489. RENAME stars TO icons");
  490. ---}
  491. ---#
  492. ---# Renaming default theme...
  493. UPDATE {$db_prefix}themes
  494. SET value = 'SMF Default Theme - Curve2'
  495. WHERE value LIKE 'SMF Default Theme%';
  496. ---#
  497. ---# Adding the enableThemes setting.
  498. INSERT INTO {$db_prefix}settings
  499. (variable, value)
  500. VALUES
  501. ('enableThemes', '1');
  502. ---#
  503. ---# Setting "default" as the default...
  504. UPDATE {$db_prefix}settings
  505. SET value = '1'
  506. WHERE variable = 'theme_guests';
  507. UPDATE {$db_prefix}boards
  508. SET id_theme = 0;
  509. UPDATE {$db_prefix}members
  510. SET id_theme = 0;
  511. ---#
  512. /******************************************************************************/
  513. --- Cleaning up after old themes...
  514. /******************************************************************************/
  515. ---# Checking for "core" and removing it if necessary...
  516. ---{
  517. // Do they have "core" installed?
  518. if (file_exists($GLOBALS['boarddir'] . '/Themes/core'))
  519. {
  520. $core_dir = $GLOBALS['boarddir'] . '/Themes/core';
  521. $theme_request = upgrade_query("
  522. SELECT id_theme
  523. FROM {$db_prefix}themes
  524. WHERE variable = 'theme_dir'
  525. AND value ='$core_dir'");
  526. // Don't do anything if this theme is already uninstalled
  527. if ($smcFunc['db_num_rows']($theme_request) == 1)
  528. {
  529. list($id_theme) = $smcFunc['db_fetch_row']($theme_request, 0);
  530. $smcFunc['db_free_result']($theme_request);
  531. $known_themes = explode(', ', $modSettings['knownThemes']);
  532. // Remove this value...
  533. $known_themes = array_diff($known_themes, array($id_theme));
  534. // Change back to a string...
  535. $known_themes = implode(', ', $known_themes);
  536. // Update the database
  537. upgrade_query("
  538. UPDATE {$db_prefix}settings
  539. SET value = '$known_themes'
  540. WHERE variable = 'knownThemes'");
  541. // Delete any info about this theme
  542. upgrade_query("
  543. DELETE FROM {$db_prefix}themes
  544. WHERE id_theme = $id_theme");
  545. }
  546. }
  547. ---}
  548. ---#
  549. /******************************************************************************/
  550. --- Messenger fields
  551. /******************************************************************************/
  552. ---# Adding new field_order column...
  553. ALTER TABLE {$db_prefix}custom_fields
  554. ADD COLUMN field_order smallint NOT NULL default '0';
  555. ---#
  556. ---# Insert fields
  557. INSERT INTO `{$db_prefix}custom_fields` (`col_name`, `field_name`, `field_desc`, `field_type`, `field_length`, `field_options`, `field_order`, `mask`, `show_reg`, `show_display`, `show_profile`, `private`, `active`, `bbc`, `can_search`, `default_value`, `enclose`, `placement`) VALUES
  558. ('cust_aolins', 'AOL Instant Messenger', 'This is your AOL Instant Messenger nickname.', 'text', 50, '', 1, 'regex~[a-z][0-9a-z.-]{1,31}~i', 0, 1, 'forumprofile', 0, 1, 0, 0, '', '<a class="aim" href="aim:goim?screenname={INPUT}&message=Hello!+Are+you+there?" target="_blank" title="AIM - {INPUT}"><img src="{IMAGES_URL}/fields/aim.gif" alt="AIM - {INPUT}"></a>', 1),
  559. ('cust_icq', 'ICQ', 'This is your ICQ number.', 'text', 12, '', 2, 'regex~[1-9][0-9]{4,9}~i', 0, 1, 'forumprofile', 0, 1, 0, 0, '', '<a class="icq" href="http://www.icq.com/whitepages/about_me.php?uin={INPUT}" target="_blank" title="ICQ - {INPUT}"><img src="http://status.icq.com/online.gif?img=5&icq={INPUT}" alt="ICQ - {INPUT}" width="18" height="18"></a>', 1),
  560. ('cust_skype', 'Skype', 'Your Skype name', 'text', 32, '', 3, 'nohtml', 0, 1, 'forumprofile', 0, 1, 0, 0, '', '<a href="skype:{INPUT}?call"><img src="{DEFAULT_IMAGES_URL}/skype.png" alt="{INPUT}" title="{INPUT}" /></a> ', 1),
  561. ('cust_yahoo', 'Yahoo! Messenger', 'This is your Yahoo! Instant Messenger nickname.', 'text', 50, '', 4, 'email', 0, 1, 'forumprofile', 0, 1, 0, 0, '', '<a class="yim" href="http://edit.yahoo.com/config/send_webmesg?.target={INPUT}" target="_blank" title="Yahoo! Messenger - {INPUT}"><img src="http://opi.yahoo.com/online?m=g&t=0&u={INPUT}" alt="Yahoo! Messenger - {INPUT}"></a>', 1),
  562. ('cust_loca', 'Location', 'Geographic location.', 'text', 50, '', 5, 'email', 0, 1, 'forumprofile', 0, 1, 0, 0, '', '', 1),
  563. ('cust_gender', 'Gender', 'Your gender.', 'radio', 255, 'Male,Female', 6, 'nohtml', 1, 1, 'forumprofile', 0, 1, 0, 0, 'Male', '<img src="{DEFAULT_IMAGES_URL}/{INPUT}.gif" alt="{INPUT}" title="{INPUT}" />', 1);
  564. ---#
  565. ---# Add an order value to each exiting cust profile field.
  566. ---{
  567. $old_cust_fields = upgrade_query("
  568. SELECT id_field
  569. FROM {$db_prefix}custom_fields");
  570. // We start counting from 7 because we already have the first 6 fields.
  571. $fields_count = 7;
  572. while ($row = mysql_fetch_assoc($old_cust_fields))
  573. {
  574. $fields_count++;
  575. upgrade_query("
  576. UPDATE {$db_prefix}custom_fields
  577. SET field_order = $fields_count,
  578. WHERE id_attach = $row[id_field]");
  579. }
  580. $smcFunc['db_free_result']($old_cust_fields);
  581. ---}
  582. ---#
  583. ---# Converting member values...
  584. ---{
  585. // We cannot do this twice
  586. if (@$modSettings['smfVersion'] < '2.1')
  587. {
  588. $request = upgrade_query("
  589. SELECT id_member, aim, icq, msn, yim, location, gender
  590. FROM {$db_prefix}members");
  591. $inserts = array();
  592. while ($row = mysql_fetch_assoc($request))
  593. {
  594. if (!empty($row[aim]))
  595. $inserts[] = "($row[id_member], -1, 'cust_aolins', $row[aim])";
  596. if (!empty($row[icq]))
  597. $inserts[] = "($row[id_member], -1, 'cust_icq', $row[icq])";
  598. if (!empty($row[msn]))
  599. $inserts[] = "($row[id_member], -1, 'cust_skype', $row[msn])";
  600. if (!empty($row[yim]))
  601. $inserts[] = "($row[id_member], -1, 'cust_yahoo', $row[yim])";
  602. if (!empty($row[location]))
  603. $inserts[] = "($row[id_member], -1, 'cust_loca', $row[location])";
  604. if (!empty($row[gender]))
  605. $inserts[] = "($row[id_member], -1, 'cust_gender', $row[gender])";
  606. }
  607. $smcFunc['db_free_result']($request);
  608. if (!empty($inserts))
  609. upgrade_query("
  610. INSERT INTO {$db_prefix}themes
  611. (id_member, id_theme, variable, value)
  612. VALUES
  613. " . implode(',', $inserts));
  614. }
  615. ---}
  616. ---#
  617. ---# Dropping old fields
  618. ALTER TABLE `{$db_prefix}members`
  619. DROP `icq`,
  620. DROP `aim`,
  621. DROP `yim`,
  622. DROP `msn`,
  623. DROP `location`,
  624. DROP `gender`;
  625. ---#
  626. /******************************************************************************/
  627. --- Adding support for drafts
  628. /******************************************************************************/
  629. ---# Creating drafts table.
  630. CREATE SEQUENCE {$db_prefix}user_drafts_seq;
  631. CREATE TABLE {$db_prefix}user_drafts (
  632. id_draft int NOT NULL default nextval('{$db_prefix}user_drafts_seq'),
  633. id_topic int NOT NULL default '0',
  634. id_board smallint NOT NULL default '0',
  635. id_reply int NOT NULL default '0',
  636. type smallint NOT NULL default '0',
  637. poster_time int NOT NULL default '0',
  638. id_member int NOT NULL default '0',
  639. subject varchar(255) NOT NULL default '',
  640. smileys_enabled smallint NOT NULL default '1',
  641. body text NOT NULL,
  642. icon varchar(16) NOT NULL default 'xx',
  643. locked smallint NOT NULL default '0',
  644. is_sticky smallint NOT NULL default '0',
  645. to_list varchar(255) NOT NULL default '',
  646. PRIMARY KEY (id_draft)
  647. );
  648. CREATE UNIQUE INDEX {$db_prefix}user_drafts_id_member ON {$db_prefix}user_drafts (id_member, id_draft, type);
  649. ---#
  650. ---# Adding draft permissions...
  651. ---{
  652. // We cannot do this twice
  653. if (@$modSettings['smfVersion'] < '2.1')
  654. {
  655. // Anyone who can currently post unapproved topics we assume can create drafts as well ...
  656. $request = upgrade_query("
  657. SELECT id_group, id_board, add_deny, permission
  658. FROM {$db_prefix}board_permissions
  659. WHERE permission = 'post_unapproved_topics'");
  660. $inserts = array();
  661. while ($row = $smcFunc['db_fetch_assoc']($request))
  662. {
  663. $inserts[] = "($row[id_group], $row[id_board], 'post_draft', $row[add_deny])";
  664. $inserts[] = "($row[id_group], $row[id_board], 'post_autosave_draft', $row[add_deny])";
  665. }
  666. $smcFunc['db_free_result']($request);
  667. if (!empty($inserts))
  668. {
  669. foreach ($inserts AS $insert)
  670. {
  671. upgrade_query("
  672. INSERT INTO {$db_prefix}board_permissions
  673. (id_group, id_board, permission, add_deny)
  674. VALUES
  675. " . $insert);
  676. }
  677. }
  678. // Next we find people who can send PMs, and assume they can save pm_drafts as well
  679. $request = upgrade_query("
  680. SELECT id_group, add_deny, permission
  681. FROM {$db_prefix}permissions
  682. WHERE permission = 'pm_send'");
  683. $inserts = array();
  684. while ($row = $smcFunc['db_fetch_assoc']($request))
  685. {
  686. $inserts[] = "($row[id_group], 'pm_draft', $row[add_deny])";
  687. $inserts[] = "($row[id_group], 'pm_autosave_draft', $row[add_deny])";
  688. }
  689. $smcFunc['db_free_result']($request);
  690. if (!empty($inserts))
  691. {
  692. foreach ($inserts AS $insert)
  693. {
  694. upgrade_query("
  695. INSERT INTO {$db_prefix}permissions
  696. (id_group, permission, add_deny)
  697. VALUES
  698. " . $insert);
  699. }
  700. }
  701. }
  702. ---}
  703. INSERT INTO {$db_prefix}settings (variable, value) VALUES ('drafts_autosave_enabled', '1');
  704. INSERT INTO {$db_prefix}settings (variable, value) VALUES ('drafts_show_saved_enabled', '1');
  705. INSERT INTO {$db_prefix}settings (variable, value) VALUES ('drafts_keep_days', '7');
  706. INSERT INTO {$db_prefix}themes (id_theme, variable, value) VALUES ('1', 'drafts_autosave_enabled', '1');
  707. INSERT INTO {$db_prefix}themes (id_theme, variable, value) VALUES ('1', 'drafts_show_saved_enabled', '1');
  708. ---#
  709. /******************************************************************************/
  710. --- Adding support for likes
  711. /******************************************************************************/
  712. ---# Creating likes table.
  713. CREATE TABLE {$db_prefix}user_likes (
  714. id_member int NOT NULL default '0',
  715. content_type char(6) default '',
  716. content_id int NOT NULL default '0',
  717. like_time int NOT NULL default '0',
  718. PRIMARY KEY (content_id, content_type, id_member)
  719. );
  720. CREATE INDEX {$db_prefix}user_likes_content ON {$db_prefix}user_likes (content_id, content_type);
  721. CREATE INDEX {$db_prefix}user_likes_liker ON {$db_prefix}user_likes (id_member);
  722. ---#
  723. ---# Adding count to the messages table.
  724. ALTER TABLE {$db_prefix}messages
  725. ADD COLUMN likes smallint NOT NULL default '0';
  726. ---#
  727. /******************************************************************************/
  728. --- Adding support for group-based board moderation
  729. /******************************************************************************/
  730. ---# Creating moderator_groups table
  731. CREATE TABLE {$db_prefix}moderator_groups (
  732. id_board smallint NOT NULL default '0',
  733. id_group smallint NOT NULL default '0',
  734. PRIMARY KEY (id_board, id_group)
  735. );
  736. ---#
  737. /******************************************************************************/
  738. --- Cleaning up integration hooks
  739. /******************************************************************************/
  740. ---#
  741. DELETE FROM {$db_prefix}settings
  742. WHERE variable LIKE 'integrate_%';
  743. ---#
  744. /******************************************************************************/
  745. --- Cleaning up old settings
  746. /******************************************************************************/
  747. ---# Updating the default time format
  748. ---{
  749. if (!empty($modSettings['time_format']))
  750. {
  751. // First, use the shortened form of the month in the date.
  752. $time_format = str_replace('%B', '%b', $modSettings['time_format']);
  753. // Second, shorten the time to stop including seconds.
  754. $time_format = str_replace(':%S', '', $time_format);
  755. // Then, update the database.
  756. $smcFunc['db_query']('', '
  757. UPDATE {db_prefix}settings
  758. SET value = {string:new_format}
  759. WHERE variable = {literal:time_format}',
  760. array(
  761. 'new_format' => $time_format,
  762. )
  763. );
  764. }
  765. ---}
  766. ---#
  767. ---# Fixing a deprecated option.
  768. UPDATE {$db_prefix}settings
  769. SET value = 'option_css_resize'
  770. WHERE variable = 'avatar_action_too_large'
  771. AND (value = 'option_html_resize' OR value = 'option_js_resize');
  772. ---#
  773. ---# Cleaning up the old Core Features page.
  774. ---{
  775. // First get the original value
  776. $request = $smcFunc['db_query']('', '
  777. SELECT value
  778. FROM {db_prefix}settings
  779. WHERE variable = {literal:admin_features}');
  780. if ($smcFunc['db_num_rows']($request) > 0 && $row = $smcFunc['db_fetch_assoc']($request))
  781. {
  782. // Some of these *should* already be set but you never know.
  783. $new_settings = array();
  784. $admin_features = explode(',', $row['value']);
  785. // Now, let's just recap something.
  786. // cd = calendar, should also have set cal_enabled already
  787. // cp = custom profile fields, which already has several fields that cover tracking
  788. // k = karma, should also have set karmaMode already
  789. // ps = paid subs, should also have set paid_enabled already
  790. // rg = reports generation, which is now permanently on
  791. // sp = spider tracking, should also have set spider_mode already
  792. // w = warning system, which will be covered with warning_settings
  793. // The rest we have to deal with manually.
  794. // Moderation log - modlog_enabled itself should be set but we have others now
  795. if (in_array('ml', $admin_features))
  796. {
  797. $new_settings[] = array('adminlog_enabled', '1');
  798. $new_settings[] = array('userlog_enabled', '1');
  799. }
  800. // Post moderation
  801. if (in_array('pm', $admin_features))
  802. {
  803. $new_settings[] = array('postmod_active', '1');
  804. }
  805. // And now actually apply it.
  806. if (!empty($new_settings))
  807. {
  808. $smcFunc['db_insert']('replace',
  809. '{db_prefix}settings',
  810. array('variable' => 'string', 'value' => 'string'),
  811. $new_settings,
  812. array('variable')
  813. );
  814. }
  815. }
  816. $smcFunc['db_free_result']($request);
  817. ---}
  818. ---#
  819. ---# Cleaning up old settings.
  820. DELETE FROM {$db_prefix}settings
  821. WHERE variable IN ('enableStickyTopics', 'guest_hideContacts', 'notify_new_registration', 'attachmentEncryptFilenames', 'hotTopicPosts', 'hotTopicVeryPosts', 'fixLongWords', 'admin_features', 'topbottomEnable', 'simpleSearch', 'enableVBStyleLogin');
  822. ---#
  823. ---# Cleaning up old theme settings.
  824. DELETE FROM {$db_prefix}themes
  825. WHERE variable IN ('show_board_desc', 'no_new_reply_warning', 'display_quick_reply', 'show_mark_read', 'show_member_bar', 'linktree_link');
  826. ---#
  827. /******************************************************************************/
  828. --- Updating files that fetched from simplemachines.org
  829. /******************************************************************************/
  830. ---# We no longer call on several files.
  831. DELETE FROM {$db_prefix}admin_info_files
  832. WHERE filename IN ('latest-packages.js', 'latest-support.js', 'latest-themes.js')
  833. AND path = '/smf/';
  834. ---#
  835. ---# But we do need new files.
  836. ---{
  837. $smcFunc['db_insert']('',
  838. '{db_prefix}admin_info_files',
  839. array('filename' => 'string', 'path' => 'string', 'parameters' => 'string', 'data' => 'string', 'filetype' => 'string'),
  840. array('latest-versions.txt', '/smf/', 'version=%3$s', '', 'text/plain'),
  841. array('id_file')
  842. );
  843. ---}
  844. ---#
  845. /******************************************************************************/
  846. --- Upgrading "verification questions" feature
  847. /******************************************************************************/
  848. ---# Creating qanda table
  849. CREATE SEQUENCE {$db_prefix}qanda_seq;
  850. CREATE TABLE {$db_prefix}qanda (
  851. id_question smallint NOT NULL default nextval('{$db_prefix}qanda_seq'),
  852. lngfile varchar(255) NOT NULL default '',
  853. question varchar(255) NOT NULL default '',
  854. answers text NOT NULL,
  855. PRIMARY KEY (id_question),
  856. KEY lngfile (lngfile)
  857. );
  858. ---#
  859. ---# Moving questions and answers to the new table
  860. ---{
  861. $questions = array();
  862. $get_questions = upgrade_query("
  863. SELECT body AS question, recipient_name AS answer
  864. FROM {$db_prefix}log_comments
  865. WHERE comment_type = 'ver_test'");
  866. while ($row = $smcFunc['db_fetch_assoc']($get_questions))
  867. $questions[] = array($language, $row['question'], serialize(array($row['answer'])));
  868. $smcFunc['db_free_result']($get_questions);
  869. if (!empty($questions))
  870. {
  871. $smcFunc['db_insert']('',
  872. '{db_prefix}qanda',
  873. array('lngfile' => 'string', 'question' => 'string', 'answers' => 'string'),
  874. $questions,
  875. array('id_question')
  876. );
  877. // Delete the questions from log_comments now
  878. upgrade_query("
  879. DELETE FROM {$db_prefix}log_comments
  880. WHERE comment_type = 'ver_test'
  881. ");
  882. }
  883. ---}
  884. ---#
  885. /******************************************************************************/
  886. --- Fixing log_online table
  887. /******************************************************************************/
  888. ---# Changing ip to bigint
  889. ALTER TABLE {$db_prefix}log_online ALTER ip TYPE bigint;
  890. ---#
  891. /******************************************************************************/
  892. --- Marking packages as uninstalled...
  893. /******************************************************************************/
  894. ---# Updating log_packages
  895. UPDATE {$db_prefix}log_packages
  896. SET install_state = 0;
  897. ---#
  898. /******************************************************************************/
  899. --- Updating profile permissions...
  900. /******************************************************************************/
  901. ---# Removing the old "view your own profile" permission
  902. DELETE FROM {$db_prefix}permissions
  903. WHERE permission = 'profile_view_own';
  904. ---#
  905. ---# Updating the old "view any profile" permission
  906. UPDATE {$db_prefix}permissions
  907. SET permission = 'profile_view'
  908. WHERE permission = 'profile_view_any';
  909. ---#
  910. ---# Removing the old notification permissions
  911. DELETE FROM {$db_prefix}board_permissions
  912. WHERE permission = 'mark_notify' OR permission = 'mark_any_notify';
  913. ---#
  914. ---# Removing the send-topic permission
  915. DELETE FROM {$db_prefix}board_permissions
  916. WHERE permission = 'send_topic';
  917. ---#
  918. ---# Adding "profile_password_own"
  919. ---{
  920. $inserts = array();
  921. $request = upgrade_query("
  922. SELECT id_group, add_deny
  923. FROM {$db_prefix}permissions
  924. WHERE permission = 'profile_identity_own'");
  925. while ($row = $smcFunc['db_fetch_assoc']($request))
  926. {
  927. $inserts[] = "($row[id_group], 'profile_password_own', $row[add_deny])";
  928. }
  929. $smcFunc['db_free_result']($request);
  930. if (!empty($inserts))
  931. {
  932. foreach ($inserts as $insert)
  933. {
  934. upgrade_query("
  935. INSERT INTO {$db_prefix}permissions
  936. (id_group, permission, add_deny)
  937. VALUES
  938. " . $insert);
  939. }
  940. }
  941. ---}
  942. ---#
  943. ---# Adding other profile permissions
  944. ---{
  945. $inserts = array();
  946. $request = upgrade_query("
  947. SELECT id_group, add_deny
  948. FROM {$db_prefix}permissions
  949. WHERE permission = 'profile_extra_own'");
  950. while ($row = $smcFunc['db_fetch_assoc']($request))
  951. {
  952. $inserts[] = "($row[id_group], 'profile_blurb_own', $row[add_deny])";
  953. $inserts[] = "($row[id_group], 'profile_displayed_name_own', $row[add_deny])";
  954. $inserts[] = "($row[id_group], 'profile_forum_own', $row[add_deny])";
  955. $inserts[] = "($row[id_group], 'profile_other_own', $row[add_deny])";
  956. $inserts[] = "($row[id_group], 'profile_signature_own', $row[add_deny])";
  957. }
  958. $smcFunc['db_free_result']($request);
  959. if (!empty($inserts))
  960. {
  961. foreach ($inserts as $insert)
  962. {
  963. upgrade_query("
  964. INSERT INTO {$db_prefix}permissions
  965. (id_group, permission, add_deny)
  966. VALUES
  967. " . $insert
  968. );
  969. }
  970. }
  971. ---}
  972. ---#
  973. /******************************************************************************/
  974. --- Upgrading PM labels...
  975. /******************************************************************************/
  976. ---# Creating pm_labels sequence...
  977. CREATE SEQUENCE {$db_prefix}pm_labels_seq;
  978. ---#
  979. ---# Adding pm_labels table...
  980. CREATE TABLE {$db_prefix}pm_labels (
  981. id_label int NOT NULL default nextval('{$db_prefix}pm_labels_seq'),
  982. id_member int NOT NULL default '0',
  983. name varchar(30) NOT NULL default '',
  984. PRIMARY KEY (id_label)
  985. );
  986. ---#
  987. ---# Adding pm_labeled_messages table...
  988. CREATE TABLE {$db_prefix}pm_labeled_messages (
  989. id_label int NOT NULL default '0',
  990. id_pm int NOT NULL default '0',
  991. PRIMARY KEY (id_label, id_pm)
  992. );
  993. ---#
  994. ---# Adding "in_inbox" column to pm_recipients
  995. ALTER TABLE {$db_prefix}pm_recipients
  996. ADD COLUMN in_inbox smallint NOT NULL default '1';
  997. ---#
  998. ---# Moving label info to new tables and updating rules...
  999. ---{
  1000. // First see if we still have a message_labels column
  1001. $results = $smcFunc['db_list_columns']('{db_prefix}members', false);
  1002. if (in_array('message_labels', $results))
  1003. {
  1004. // They've still got it, so pull the label info
  1005. $get_labels = $smcFunc['db_query']('', '
  1006. SELECT id_member, message_labels
  1007. FROM {db_prefix}members
  1008. WHERE message_labels != {string:blank}',
  1009. array(
  1010. 'blank' => '',
  1011. )
  1012. );
  1013. $inserts = array();
  1014. $label_info = array();
  1015. while ($row = $smcFunc['db_fetch_assoc']($get_labels))
  1016. {
  1017. // Stick this in an array
  1018. $labels = explode(',', $row['message_labels']);
  1019. // Build some inserts
  1020. foreach ($labels AS $index => $label)
  1021. {
  1022. // Keep track of the index of this label - we'll need that in a bit...
  1023. $label_info[$row['id_member']][$label] = $index;
  1024. $inserts[] = array($row['id_member'], $label);
  1025. }
  1026. }
  1027. $smcFunc['db_free_result']($get_labels);
  1028. if (!empty($inserts))
  1029. {
  1030. $smcFunc['db_insert']('', '{db_prefix}pm_labels', array('id_member' => 'int', 'name' => 'string-30'), $inserts, array());
  1031. // Clear this out for our next query below
  1032. $inserts = array();
  1033. }
  1034. // This is the easy part - update the inbox stuff
  1035. $smcFunc['db_query']('', '
  1036. UPDATE {db_prefix}pm_recipients
  1037. SET in_inbox = {int:in_inbox}
  1038. WHERE FIND_IN_SET({int:minus_one}, labels) != 0',
  1039. array(
  1040. 'in_inbox' => 1,
  1041. 'minus_one' => -1,
  1042. )
  1043. );
  1044. // Now we go pull the new IDs for each label
  1045. $get_new_label_ids = $smcFunc['db_query']('', '
  1046. SELECT *
  1047. FROM {db_prefix}pm_labels',
  1048. array(
  1049. )
  1050. );
  1051. $label_info_2 = array();
  1052. while ($label_row = $smcFunc['db_fetch_assoc']($get_new_label_ids))
  1053. {
  1054. // Map the old index values to the new ID values...
  1055. $old_index = $label_info[$row['id_member']][$row['label_name']];
  1056. $label_info_2[$row['id_member']][$old_index] = $row['id_label'];
  1057. }
  1058. $smcFunc['db_free_result']($get_new_label_ids);
  1059. // Pull label info from pm_recipients
  1060. // Ignore any that are only in the inbox
  1061. $get_pm_labels = $smcFunc['db_query']('', '
  1062. SELECT id_pm, id_member, labels
  1063. FROM {db_prefix}pm_recipients
  1064. WHERE deleted = {int:not_deleted}
  1065. AND labels != {string:minus_one}',
  1066. array(
  1067. 'not_deleted' => 0,
  1068. 'minus_one' => -1,
  1069. )
  1070. );
  1071. while ($row = $smcFunc['db_fetch_assoc']($get_pm_labels))
  1072. {
  1073. $labels = explode(',', $row['labels']);
  1074. foreach ($labels as $a_label)
  1075. {
  1076. if ($a_label == '-1')
  1077. continue;
  1078. $new_label_info = $label_info_2[$row['id_member']][$a_label];
  1079. $inserts[] = array($row['id_pm'], $new_label_info);
  1080. }
  1081. }
  1082. $smcFunc['db_free_result']($get_pm_labels);
  1083. // Insert the new data
  1084. if (!empty($inserts))
  1085. {
  1086. $smcFunc['db_insert']('', '{db_prefix}pm_labeled_messages', array('id_pm' => 'int', 'id_label' => 'int'), $inserts, array());
  1087. }
  1088. // Final step of this ridiculously massive process
  1089. $get_pm_rules = $smcFunc['db_query']('', '
  1090. SELECT id_member, id_rule, actions
  1091. FROM {db_prefix}pm_rules',
  1092. array(
  1093. )
  1094. );
  1095. // Go through the rules, unserialize the actions, then figure out if there's anything we can use
  1096. while ($row = $smcFunc['db_fetch_assoc']($get_pm_rules))
  1097. {
  1098. // Turn this into an array...
  1099. $actions = unserialize($row['actions']);
  1100. // Loop through the actions and see if we're applying a label anywhere
  1101. foreach ($actions as $index => $action)
  1102. {
  1103. if ($action['t'] == 'lab')
  1104. {
  1105. // Update the value of this label...
  1106. $actions[$index]['v'] = $label_info_2[$row['id_member']][$action['v']];
  1107. }
  1108. }
  1109. // Put this back into a string
  1110. $actions = serialize($actions);
  1111. $smcFunc['db_query']('', '
  1112. UPDATE {db_prefix}pm_rules
  1113. SET actions = {string:actions}
  1114. WHERE id_rule = {int:id_rule}',
  1115. array(
  1116. 'actions' => $actions,
  1117. 'id_rule' => $row['id_rule'],
  1118. )
  1119. );
  1120. }
  1121. $smcFunc['db_free_result']($get_pm_rules);
  1122. // Lastly, we drop the old columns
  1123. $smcFunc['db_remove_column']('{db_prefix}members', 'message_labels');
  1124. $smcFunc['db_remove_column']('{db_prefix}pm_recipients', 'labels');
  1125. }
  1126. ---}
  1127. ---#
  1128. /******************************************************************************/
  1129. --- Adding support for edit reasons
  1130. /******************************************************************************/
  1131. ---# Adding "modified_reason" column to messages
  1132. ALTER TABLE {$db_prefix}messages
  1133. ADD COLUMN modified_reason varchar(255) NOT NULL default '';
  1134. ---#
  1135. /******************************************************************************/
  1136. --- Cleaning up guest permissions
  1137. /******************************************************************************/
  1138. ---# Removing permissions guests can no longer have...
  1139. ---{
  1140. $illegal_board_permissions = array(
  1141. 'announce_topic',
  1142. 'delete_any',
  1143. 'lock_any',
  1144. 'make_sticky',
  1145. 'merge_any',
  1146. 'modify_any',
  1147. 'modify_replies',
  1148. 'move_any',
  1149. 'poll_add_any',
  1150. 'poll_edit_any',
  1151. 'poll_lock_any',
  1152. 'poll_remove_any',
  1153. 'remove_any',
  1154. 'report_any',
  1155. 'split_any'
  1156. );
  1157. $illegal_permissions = array('calendar_edit_any', 'moderate_board', 'moderate_forum', 'send_email_to_members');
  1158. $smcFunc['db_query']('', '
  1159. DELETE FROM {db_prefix}board_permissions
  1160. WHERE id_group = {int:guests}
  1161. AND permission IN ({array_string:illegal_board_perms})',
  1162. array(
  1163. 'guests' => -1,
  1164. 'illegal_board_perms' => $illegal_board_permissions,
  1165. )
  1166. );
  1167. $smcFunc['db_query']('', '
  1168. DELETE FROM {db_prefix}permissions
  1169. WHERE id_group = {int:guests}
  1170. AND permission IN ({array_string:illegal_perms})',
  1171. array(
  1172. 'guests' => -1,
  1173. 'illegal_perms' => $illegal_permissions,
  1174. )
  1175. );
  1176. ---}
  1177. ---#
  1178. /******************************************************************************/
  1179. --- Adding mail queue settings
  1180. /******************************************************************************/
  1181. ---#
  1182. ---{
  1183. if (empty($modSettings['mail_limit']))
  1184. {
  1185. $smcFunc['db_insert']('replace',
  1186. '{db_prefix}settings',
  1187. array('variable' => 'string-255', 'value' => 'string'),
  1188. array(
  1189. array('mail_limit', '5'),
  1190. array('mail_quantity', '5'),
  1191. ),
  1192. array('variable')
  1193. );
  1194. }
  1195. ---}
  1196. ---#