phpBB2 Archiver

---

---

1. You must have a shell account on the phpBB2 server in order to run this utility. Many webhosts don't have this ability, so doublecheck on this first.
2. Log into the server.
3. Create a new file in your text editor of choice. I prefer Nano myself.
4. Cut and paste the entire script below into this file.
5. Go to the top of the file and, under the User Configuration Section, edit the variables so they relate to your install of phpBB2.
6. Save the file as a good name and exit your editor.
7. Type: perl -c filename to ensure that your installation of Perl has everything you need.
8. Type: chmod 755 filename to give the script permissions to be executed.
9. Go to your phpBB2 forum
10. Find the original forum you wish to move topics out of.
11. Click on the forum so you're viewing it.
12. Look at the URL bar of your browser. You'll see a section of the long address that has "?f=XX" where XX is a number.
13. Write this number down. This is the ID number for this forum.
14. Go to the "archive" forum -- the forum that you want to move posts into. Find its ID as well and write it down.
15. Go back to your shell on the server. Type: ./filename OID AID XX units where OID is the original forum ID, AID is the archive forum ID, XX is the number of units you want to specify, and 'units' is the type of units to use. For example, to move all posts older than 3 months from forum 2 to forum 34, I'd type: ./filename 2 34 3 months
16. You should see the script execute and tell you what it moved and didn't move. If not, something's not quite right.
17. I'm fond of setting up a crontab to run the script automatically each night. If you want to do this, look up how to make a crontab entry and give it a shot.

#!/usr/bin/perl -w
use strict;
use DBI;

###############################################
## phpBB2 Topic Archiver 
## (C)2007 Nathan Pralle (www.nathanpralle.com)
## See end of script for licensing
##
## This script will move topics from one topic in phpBB2 to another based on how old they are in months
## You must find the ID of the original and archive forum in order to run this script (under the Admin panel)
##
## USER CONFIGURATION SECTION
my $dbname			='phpbb2 database name goes here';
my $dbuser			='phpbb2 database username goes here';
my $dbpass			='phpbb2 database password goes here';
my $dbhost			='localhost';
my $table_prefix	='phpbb_';   #if your tables have a prefix
###############################################

my $original_forum	=$ARGV[0]||die("Syntax:  $0 <original forum ID> <archive forum ID> 
	<archive after X> <days/months/years>\n");	
my $arch_forum		=$ARGV[1]||die("Syntax:  $0 <original forum ID> <archive forum ID> 
	<archive after X> <days/months/years>\n");
my $archive_interval=$ARGV[2]||die("Syntax:  $0 <original forum ID> <archive forum ID> 
	<archive after X> <days/months/years>\n");
my $archive_interval_units=$ARGV[3]||die("Syntax:  $0 <original forum ID> <archive forum ID> 
	<archive after X> <days/months/years>\n");

if(lc($archive_interval_units) ne "days" && 
	lc($archive_interval_units) ne "months" && 
	lc($archive_interval_units) ne "years"){
	die("Syntax:  $0 <original forum ID> <archive forum ID> 
		<archive after X> <days/months/years>\n");
}

$archive_interval_units=uc(substr($archive_interval_units,0,length($archive_interval_units) - 1));

my $DB_DEBUG=0;

my $dbh=DBConnect();

### Get the forum names, plus check that they actually exist in the first place
my $orig_forum_name;
my $request="SELECT ".$table_prefix."forums.forum_name 
	FROM ".$table_prefix."forums 
	WHERE forum_id=$original_forum";
my @row=GetOneRow($dbh,$request);
if(!defined($row[0])){
	print STDERR "Original Forum ID $original_forum does not exist!\n";
	exit;
}
$orig_forum_name=$row[0];

my $arch_forum_name;
$request="SELECT ".$table_prefix."forums.forum_name 
	FROM ".$table_prefix."forums 
	WHERE forum_id=$arch_forum";
@row=GetOneRow($dbh,$request);
if(!defined($row[0])){
	print STDERR "Archive Forum ID $arch_forum does not exist!\n";
	exit;
}
$arch_forum_name=$row[0];

### Shift rows from the Original forum to the Archive forum
print "Searching for topics older than $archive_interval ".lc($archive_interval_units).
	"s in $orig_forum_name...\n";
$request="UPDATE ".$table_prefix."topics,".$table_prefix."posts 
	SET ".$table_prefix."topics.forum_id=$arch_forum,
	".$table_prefix."posts.forum_id=$arch_forum 
	WHERE ".$table_prefix."topics.topic_last_post_id=".$table_prefix."posts.post_id AND 
	FROM_UNIXTIME(".$table_prefix."posts.post_time) + 
		INTERVAL $archive_interval $archive_interval_units < NOW() AND 
	".$table_prefix."topics.forum_id=$original_forum";
my $rows_affected=UpdateRow($dbh,$request);
print "$rows_affected topics were moved from $orig_forum_name (ID $original_forum) to 
	$arch_forum_name (ID $arch_forum).\n";

if($rows_affected<1){
	exit;  #No change, no need to resync
}

my $last_post;
my $total_posts;
my $total_topics;

######### RESET ORIGINAL FORUM POSTS
$request="SELECT MAX(post_id) AS last_post, COUNT(post_id) AS total 
	FROM ".$table_prefix."posts 
	WHERE forum_id = $original_forum";
@row=GetOneRow($dbh,$request);
if (defined($row[0])){
	if($row[0]){
		$last_post=$row[0]
	}
	else{
		$last_post=0
	};
	if($row[1]){
		$total_posts=$row[1];
	}
	else{
		$total_posts=0;
	}	
}
else{
	$last_post = 0;
	$total_posts = 0;
}


$request="SELECT COUNT(topic_id) AS total
	FROM ".$table_prefix."topics
	WHERE forum_id = $original_forum";
@row=GetOneRow($dbh,$request);
if(defined($row[0])){
	if($row[0]){
		$total_topics=$row[0];
	}
	else{
		$total_topics=0;
	}
}

$request="UPDATE ".$table_prefix."forums 
	SET forum_last_post_id = $last_post, forum_posts = $total_posts, forum_topics = $total_topics
	WHERE forum_id = $original_forum";
$rows_affected=UpdateRow($dbh,$request);
if($rows_affected){
	print "Original Forum ($orig_forum_name ID $original_forum) 
		resynched to $last_post as last post, $total_posts as total posts, $total_topics as total topics.\n";
}
else{
	print "Original Forum ($orig_forum_name ID $original_forum) not resynched properly!\n";
}

########## RESET ARCHIVE FORUM POSTS
$request="SELECT MAX(post_id) AS last_post, COUNT(post_id) AS total 
	FROM ".$table_prefix."posts 
	WHERE forum_id = $arch_forum";
@row=GetOneRow($dbh,$request);
if (defined($row[0])){
	if($row[0]){
		$last_post=$row[0];
	}
	else{
		$last_post=0;
	}
	if($row[1]){
		$total_posts=$row[1];
	}
	else{
		$total_posts=0;
	}
}
else{
	$last_post = 0;
	$total_posts = 0;
}


$request="SELECT COUNT(topic_id) AS total
	FROM ".$table_prefix."topics
	WHERE forum_id = $arch_forum";
@row=GetOneRow($dbh,$request);
if(defined($row[0])){
	if($row[0]){
		$total_topics=$row[0];
	}
	else{
		$total_topics=0;
	}
}

$request="UPDATE ".$table_prefix."forums 
	SET forum_last_post_id = $last_post, forum_posts = $total_posts, forum_topics = $total_topics
	WHERE forum_id = $arch_forum";
$rows_affected=UpdateRow($dbh,$request);
if($rows_affected){
	print "Archive Forum ($arch_forum_name ID $arch_forum) 
		resynched to $last_post as last post, $total_posts as total posts, $total_topics as total topics.\n";
}
else{
	print "Archive Forum ($arch_forum_name ID $arch_forum) not resynched properly!\n";
}

DBDisconnect($dbh);

exit;


sub DBConnect{
   if($DB_DEBUG){ print STDERR "DB: Connect\n"};
   return DBI->connect("dbi:mysql:$dbname:$dbhost","$dbuser","$dbpass",
      {'AutoCommit'=>1,'PrintError'=>1});
}

sub DBDisconnect{
   if($DB_DEBUG){ print STDERR "DB: Disconnect\n"};
   my $dbh=shift;
   $dbh->disconnect();
}

sub InsertRow{
   my($db_handle,$request)=@_;
   if($DB_DEBUG){ print STDERR "REQUEST: $request\n";}
   my $request_handle=$db_handle->prepare($request);
   $request_handle->execute()||die("DB ERROR: $DBI::errstr\nREQUEST: $request\n");
	my $rows=$request_handle->rows;
   $request_handle->finish();
   return $rows;
}

sub UpdateRow{   #Alias for InsertRow as they do the same damned thing
   my($db_handle,$request)=@_;
   return InsertRow($db_handle,$request);
}

sub GetOneRow{
   my($db_handle,$request)=@_;
   if($DB_DEBUG){ print STDERR "REQUEST: $request\n";}
   my $request_handle=$db_handle->prepare($request);
   $request_handle->execute()||die("DB ERROR: $DBI::errstr\nREQUEST: $request\n");
   my @row=$request_handle->fetchrow_array();
   $request_handle->finish();
   return @row;
}



#####################
# LICENSE
#
#Copyright (c)2007 Nathan E. Pralle (www.nathanpralle.com)
#
#Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated 
#documentation files (the "Software"), to deal in the Software without restriction, including without limitation 
#the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, 
#and to permit persons to whom the Software is furnished to do so, subject to the following conditions:
#
# * The above copyright notice and this permission notice shall be included in all copies or 
#   substantial portions of the Software. 
#
#THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED 
#TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL 
#THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF 
#CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER 
#DEALINGS IN THE SOFTWARE. 
####################