Forums

Unfortunately no one can be told what FluxBB is - you have to see it for yourself.

You are not logged in.

#1 2014-04-28 14:42:55

Whoracle
New member
Registered: 2014-04-28
Posts: 4

Move closed topics to graveyard forum - database cleanup script

After my request over in the Feature Request forums (to which I can't yet post a link) I decided to hammer out a small script for cron to move closed topics to a graveyard/archive forum and share it with you all.

Notes:

- Don't use your database root user in this script
- Put the script somewhere well out of your webroot
- Best practise is to create a dedicated user for this script. Privileges needed are:
   - SELECT on tables _forums, _topics, _posts
   - UPDATE on tables _forums, _topics
- Try it out on a TestDB first. I can't stress this enough: This is almost completely untested! In fact, the last 2 functions are untested at this point! DO NOT USE THIS IN A PRODUCTION ENVIRONMENT WITHOUT TESTING!
- I know this is old-school, soon-to-be-deprecated mysql in PH>P. Couldn't be bothered to use pdo for now. Feel free to rewrite it.
- This should work with 1.5.6. Don't know about the DB schema on older versions, though.

To Do (some help appreciated):

- Determine last_post
- Find a way to only get topics that are older than X
- Write a logger

From a very cursory glance at the DB and the code, I don't yet understand how the stamp that's in last_post gets generated. Some insight on this would be useful.

With all that said, here's the code:

<?php

$dbhost = 'localhost';
$dbuser = 'fluxbb_db_maintenance_user';
$dbpass = 'password;
$dbname = 'fluxbb_db_name';

$tbl_prefix = 'flux_'; # prefix for your tables in the DB
$graveyardforum_id = 13; # id of the forum where you want to move your closed topics

$dbconn = mysql_connect($dbhost, $dbuser, $dbpass) or die('Error: Connection failed: ' . mysql_error());
$db = mysql_select_db($dbname, $dbconn) or die('Error: Database Select failed: ' . mysql_error());

function countClosedTopics($tbl_prefix, $graveyardforum_id) {
        # count closed topics
        $sql = 'SELECT COUNT(*) from '.$tbl_prefix.'topics WHERE closed = 1 AND forum_id != '.$graveyardforum_id;
        $query = mysql_query($sql) or die('Count Closed Topics Error: '.mysql_error());

        return $query;
        }

function getClosedTopicIds ($tbl_prefix, $graveyardforum_id) {
        # get IDs of closed topics. returns the IDs as an array
        $sql = 'SELECT id FROM '.$tbl_prefix.'topics WHERE closed = 1 AND forum_id != '.$graveyardforum_id;
        $query = mysql_query($sql) or die('Get Closed Topic IDs Error: '.mysql_error());
        $result = mysql_fetch_array($query, MYSQL_NUM);

        return $result;
}

function getClosedTopicsPostCount ($tbl_prefix, $topics) {
        # iterate over closed topics to get their post count. return overall postcount as INT.
        $post_count = 0;

        foreach ($topics as &$id) {
                $sql = 'SELECT COUNT(*) FROM '.$tbl_prefix.'topics WHERE id = '.$id;
                $query = mysql_query($sql) or die('Count Closed Topic Posts Error: '.mysql_error());

                $post_count = $post_count + $query;
        }

        return $post_count;
}

function getCurrentPostCount ($tbl_prefix, $graveyardforum_id) {
        # iterate over each topic in graveyardforum to get the current number of posts
        $sql = 'SELECT num_posts FROM '.$tbl_prefix.'forums WHERE id = '.$graveyardforum_id;
        $query = mysql_query($sql) or die('Get Current Postcount Error: '.mysql_error());

        return $query;
}

function getCurrentTopicCount ($tbl_prefix, $graveyardforum_id) {
        #  get the current number of topics in graveyardforum
        $sql = 'SELECT num_topics FROM '.$tbl_prefix.'forums WHERE id = '.$graveyardforum_id;
        $query = mysql_query($sql) or die('Get Current Topiccount Error: '.mysql_error());

        return $query;
}

function moveClosedTopics ($tbl_prefix, $graveyardforum_id, $new_topics, $new_posts) {
        # move all closed topics that are not already in graveyardforum tograveyard.
        $sql = 'UPDATE '.tbl_prefix.'topics SET forum_id = '.$graveyardforum_id.' WHERE closed = 1;';
        $query = mysql_query($sql) or die('Move Closed Topics Error: '.mysql_error());

        return $query;
}

function updateGraveyard ($tbl_prefix, $graveyardforum_id) {
        ä# update graveyardforums metadata (num_posts, num_topics, later on last_post, which is not yet implemented.
        $sql = 'UPDATE '.$tbl_prefix.'forums SET num_topics = '.$new_topics.', num_posts = '.$new_posts.' WHERE id = '.$graveyardforum_id;
        $query = mysql_query($sql) or die('Update Graveyard Error: '.mysql_error());

        return $query;
}

$new_posts = 0;
$new_posts = getCurrentPostCount($tbl_prefix, $graveyardforum_id) + getClosedTopicsPostCount($tbl_prefix, getClosedTopicIds($tbl_prefix, $graveyardforum_id));

$new_topics = 0;
$new_topics = getCurrentTopicCount($tbl_prefix, $graveyardforum_id) + count(getClosedTopicIds($tbl_prefix, $graveyardforum_id));

moveClosedTopics($tbl_prefix, $graveyardforum_id, $new_topics, $new_posts);
updateGraveyard($tbl_prefix, $graveyardforum_id);
?>

Dump that somewhere on your machine and add a cronjob that does

php /path/to/script/move_closed.php

.
All done.

Feedback, improvements, "forks", rewrites and public stonings about how this breaks X or Y and how my coding style sucks are most welcome.

Last edited by Whoracle (2014-04-28 16:04:15)

Offline

Board footer

Powered by FluxBB 1.5.8