reportFix(__("Counting forum threads…")); startFix(); query("UPDATE {forums} f SET numposts =\n\t\t\t(SELECT SUM(replies+1) FROM {threads} t WHERE t.forum = f.id)\n\t\t"); reportFix(__("Counting forum posts…")); startFix(); //For some reason, this beautiful query will set MySQL to use 100% CPU and never finishes. /*query("UPDATE {threads} t SET lastpostid = (SELECT p.id FROM {posts} p WHERE p.thread = t.id ORDER BY date DESC LIMIT 0,1), lastposter = (SELECT p.user FROM {posts} p WHERE p.thread = t.id ORDER BY date DESC LIMIT 0,1), lastpostdate = (SELECT p.date FROM {posts} p WHERE p.thread = t.id ORDER BY date DESC LIMIT 0,1) ");*/ $aff = 0; $rForum = Query("select * from {forums}"); while ($forum = Fetch($rForum)) { $rThread = Query("select * from {threads} where forum = {0} order by lastpostdate desc", $forum['id']); $first = 1; while ($thread = Fetch($rThread)) { $lastPost = Fetch(Query("select * from {posts} where thread = {0} order by date desc limit 0,1", $thread['id'])); Query("update {threads} set lastpostid = {0}, lastposter = {1}, lastpostdate = {2} where id = {3}", (int) $lastPost['id'], (int) $lastPost['user'], (int) $lastPost['date'], $thread['id']); $aff += affectedRows(); if ($first) { Query("update {forums} set lastpostid = {0}, lastpostuser = {1}, lastpostdate = {2} where id = {3}", (int) $lastPost['id'], (int) $lastPost['user'], (int) $lastPost['date'], $forum['id']); $aff += affectedRows(); } $first = 0; } } reportFix(__("Updating threads last posts…")); $bucket = "recalc"; include BOARD_ROOT . "lib/pluginloader.php"; print "<br />All done!<br />";
<?php startFix(); Query("UPDATE {posts} p SET postplusones =\n\t\t\t(SELECT COUNT(*) FROM {postplusones} pp WHERE pp.post = p.id)\n\t\tWHERE 1"); reportFix(__("Counting post +1's…")); startFix(); Query("UPDATE {users} u SET postplusonesgiven =\n\t\t\t(SELECT COUNT(*) FROM {postplusones} pp WHERE pp.user = u.id)\n\t\tWHERE 1"); reportFix(__("Counting user +1's given…")); startFix(); Query("UPDATE {users} u SET postplusones =\n\t\t\t(SELECT COUNT(*) FROM {postplusones} pp \n\t\t\tLEFT JOIN {posts} p on pp.post = p.id\n\t\t\tWHERE p.user = u.id)\n\t\tWHERE 1"); reportFix(__("Counting user +1's received…"));
startFix(); query("UPDATE {forums} f SET numposts =\n\t\t\t(SELECT SUM(replies+1) FROM {threads} t WHERE t.forum = f.id)\n\t\t"); reportFix(__("Counting forum posts…")); startFix(); //For some reason, this beautiful query will set MySQL to use 100% CPU and never finishes. /*query("UPDATE {threads} t SET lastpostid = (SELECT p.id FROM {posts} p WHERE p.thread = t.id ORDER BY date DESC LIMIT 0,1), lastposter = (SELECT p.user FROM {posts} p WHERE p.thread = t.id ORDER BY date DESC LIMIT 0,1), lastpostdate = (SELECT p.date FROM {posts} p WHERE p.thread = t.id ORDER BY date DESC LIMIT 0,1) ");*/ $aff = 0; $rForum = Query("select * from {forums}"); while ($forum = Fetch($rForum)) { $rThread = Query("select * from {threads} where forum = {0} order by lastpostdate desc", $forum['id']); $first = 1; while ($thread = Fetch($rThread)) { $lastPost = Fetch(Query("select * from {posts} where thread = {0} order by date desc limit 0,1", $thread['id'])); $firstPost = Fetch(Query("select * from {posts} where thread = {0} order by date asc limit 0,1", $thread['id'])); Query("update {threads} set lastpostid = {0}, lastposter = {1}, lastpostdate = {2}, date = {3}, firstpostid={4}, user={5} where id = {6}", (int) $lastPost['id'], (int) $lastPost['user'], (int) $lastPost['date'], (int) $firstPost['date'], (int) $firstPost['id'], (int) $firstPost['user'], $thread['id']); $aff += affectedRows(); if ($first) { Query("update {forums} set lastpostid = {0}, lastpostuser = {1}, lastpostdate = {2} where id = {3}", (int) $lastPost['id'], (int) $lastPost['user'], (int) $lastPost['date'], $forum['id']); $aff += affectedRows(); } $first = 0; } } reportFix(__("Updating threads dates and post IDs…")); $bucket = "recalc"; include "./lib/pluginloader.php"; print "<br />All done!<br />";