Search options

#!/usr/bin/perl -w

#
# Recount number of topics and posts in each forum (PunBB.org)
#
# Edit first 4 assignments to setup DB connection
#
# Sergej Pupykin (pupykins@gmail.com), 2006
#

$DBNAME="vsuforum"; # PunBB database
$USERNAME="root";   # DB user name
$PASSWORD="";       # DB password
$UPDATE = 1;        # If 0, then do not update DB - just print info.

use DBI;

$total_posts = 0;
$total_topics = 0;

$db = DBI->connect("dbi:mysql:$DBNAME", $USERNAME, $PASSWORD,
                  { RaiseError => 1, AutoCommit => 0 });

$qf = $db->prepare("select id from forums");
$qf->execute();

while( ($forum_id) = $qf->fetchrow_array() )
{
        $qt = $db->prepare("select id from topics where forum_id=$forum_id");
        $qt->execute();

        $sum_topics = 0;
        $sum_posts = 0;

        while( ($topic_id) = $qt->fetchrow_array() )
        {
                $sum_topics++;
                $qp = $db->prepare("select count(*) from posts where topic_id=$topic_id");
                $qp->execute();
                if( ($posts_count) = $qp->fetchrow_array() )
                {
                        $sum_posts += $posts_count;
                }
                $qp->finish();
        }
        $qt->finish();

        printf("%4d %4d %4d", $forum_id, $sum_topics, $sum_posts);
        $total_posts += $sum_posts;
        $total_topics += $sum_topics;

        if($UPDATE)
        {
            print " updating...\n";
                $qu = $db->prepare("update forums set num_topics=$sum_topics, num_posts=$sum_posts where id=$forum_id");
                $qu->execute();
                $qu->finish();
        }
        else
        {
                print "\n";
        }
}

$qf->finish();

$db->disconnect();

print "---------------\n";
printf("     %4d %4d\n", $total_topics, $total_posts);

if($UPDATE)
{
        print "PunBB database updated\n\n";
}