upgrade_2-1_postgresql.sql 44 KB

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