phpBB old topics locker

December 9th, 2005. Tagged: phpBB

I had this request to implement a feature on a phpBB board whereby topics older than one month to get deleted. A quick search on phpbbhacks.com revealed a result - Auto Lock hack, but it had a disturbing note saying that it's not recommended for larger boards, although I couldn't figure out why. Anyway, the hack looked too involved, with DB changes, new Admin Panel options, etc., so I decided to hack something together quickly. Turned out to be pretty easy. Here' s the result.

The SQL query

UPDATE phpbb_posts AS p, phpbb_topics AS t
  SET t.topic_status = 1
WHERE p.post_id = t.topic_last_post_id
  AND FROM_UNIXTIME(p.post_time) + INTERVAL 1 MONTH < NOW()
  AND t.topic_status = 0

Basically the logic is so simple that it can be done in one query. The topics table contains the id of the last post. I join the posts table to figure out the date/time of the last post. And if it's old enough, I set the topic status to 1 (locked).

I like the INTERVAL MySQL thing so I used it here, converting the Unix timestamps to human dates. It makes it much more easier to see at a glance that the period is one month and quickly change it afterwards if need be, without starting the old and tired calculation: "OK, 60 seconds times 60 minutes times 24 hours... Wait a sec! Is it seconds or milliseconds?"

The common.php

Instead of dealing with cron jobs, I decided to execute the query in common.php, a script that is always loaded on every page. I execute the query and send myself an email of something wrong happens.

<?php
$sql = 'UPDATE phpbb_posts AS p, phpbb_topics AS t
            SET t.topic_status = 1
        WHERE p.post_id = t.topic_last_post_id
            AND FROM_UNIXTIME(p.post_time) + INTERVAL 3 MONTH < NOW()
            AND t.topic_status = 0';
if (!$db->sql_query($sql))
{

    @mail('me@example.org',
          '[thesite] Locker',
          'Error while locking old topics',
          'From: meagain@example.org'
         );
}
?>

A probabilistic touch

Now, executing this query is not necessary on every page load. So I added a touch of probability.

<?php
if (mt_rand(1, 10) == 5) { //chance one in ten to run
    // do the stuff
}
?>

That means that this code has a chance one in ten of being executed on a page load. Even this is a bit high, but can always be changed to 1 in a 100 for example.

Distribution?

This was done only for internal purposes, not designed to be made available to the larger phpBB community. Otherwise it would be better if it doesn't hard-code the "phpbb_" table prefix, as well as the table names in general and the topic status value. Instead, phpBB constants must be used - like POSTS_TABLE, TOPICS_TABLE and TOPIC_LOCKED.

Tell your friends about this post: Facebook, Twitter, Google+

13 Responses

  1. The reason it says that is because I tried it on my largest site and it reduced it killed it. :) I believe we may have made some small adjustments to it, though and added that word of caution, as well. Maybe I’ll try your way, although I think I’d prefer it to just run on a cron job.

  2. Hi Patrick, thanks for your input!

    My board is not so big (150K posts, 4K members) and my solution seems to work well. I still think the probability thing should be increased to 1 in 100, because 1 in 10 causes the code to be executed once or twice a minute which is a bit more often than necessary. The ideal solution would be cron job, of course, my solution is more like “poor man’s cron job alternative” :)

    I’ll implement some benchmarks in the code and will post the results here.

  3. Cool. :) Thanks.

  4. OK, ready to share some data.

    I changed the random factor to one in 1000 and let it run on my board. The board usually has 20 open sessions at slow times and about 60 at pick-hours. With this 1/1000 probability for the locking query to run, it was executed 27.55 times a day on average, which means a little bit more than once an hour.

    The run time is usually 0.02-0.03 seconds, but a few times during the nine days of testing it got as high as half a second and sometimes even 2 seconds. The number of locked topics does’t seem to affect the speed.

    Overall the results look OK to me and I’ll keep it running as is, until I implement this as a cron job. I’ll try to find the time to polish the script and share it on phpbbhacks.com :D

  5. Excellent. :)

  6. I’m unable to get the code to work in my common.php file. I currently have 9 locked topics, and after updating my commons page: 9 locked topics.

    It didn’t return any errors (and I’m not running the probability portion yet.

    Any suggestions?

  7. Hi Torin,

    Try copying only the SQL and executing it with phpMyAdmin, see if it works there or if you’re getting any errors.

    You might need to tweak the “INTERVAL 3 MONTH

  8. The FROM_UNIXTIME on a table field means that it won’t use an index on that field. Instead, invert the function like this:

    p.post_time < UNIX_TIME(NOW() – INTERVAL 1 MONTH)

    that way, if you have an index on (p.post_id, p.post_time), it will be used more effectively.

  9. don’t you think it would have been easier with a daily cronjob? when let’s say the sessions number increase above 60? the chances to run this script will be higher. with a cron job you avoid server’s load at peak times.

  10. hy there

  11. привет
    супер

  12. привет

  13. A vpn network other by the bdhfjeury and buy vpn openvpn doublevpn etc the rich private vpn ip proxy etc.

Leave a Reply