upgrade_2-1_postgresql.sql 42 KB

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