Gossamer Forum
Home : Products : Gossamer Forum : Testing Forum :

perl code test

Quote Reply
perl code test
perl code below:


#!/usr/bin/perl

# Usenet Gateway hack for vBulletin 2.xx
#
# $Id: newnews.pl,v 20020323 2002-03-23 12:22:56-05 paul Exp $
#
# This script creates a gateway to selected usenet newsgroups and maintains
# an historical mirror of articles in your vBulletin forums.
#
# This script is free. You are welcome to do whatever you want with it as
# long as you don't try and sell it or claim it as your own.
# The copyright is retained by the author.
#
# Author:
# fastforward (Paul)
# contact me via the vBulletin forums at http://vbulletin.com
#
#

# uncomment the following line if you installed perl modules to a
# non-standard directory
#use lib "/usr/home/local/lib/perl5/site_perl/5.005";

my $iswin32=0; # set this to 1 if you are running on a win32 platform

my $db="db_name"; # mysql database name
my $db_host="localhost"; # mysql host
my $db_username="db_username"; # mysql username
my $db_password="db_password"; # mysql password


my $pid_file="newnews.pid";





##########################################################################
# No changes necessary below here
#
use strict;
use News::NNTPClient;
use Net::SMTP;
use MIME::WordDecoder;
use Text::Autoformat;
use Mail::Address;
use URI::Find;
use DBI;
use Date::Parse;
use HTML::Entities;


# check for previous instance and terminate if exists
# disabled for win32
unless ($iswin32) {
my $pid=$$;
my $old_pid=0;
if (-e $pid_file) {
open(W,"$pid_file");
$old_pid=<W>;
close(W);
}
open(W,">$pid_file");
print W $pid;
close(W);
my $ps_pid=`/bin/ps xaww | grep newnews.pl | grep -v "grep"`;
$pid=substr($ps_pid,0,5);
if($pid+0 == $old_pid+0){
system("kill -9 $old_pid");
}
}

# declare globals
my (@newsgroups,@spamfilters,@replacements);
my $dbh = db_connect();
my $c;
my $current_server="default";
my (%config,%vbconfig);

# get vB config and usenet settings
get_arrays();

# set default MIME character set
my $wd = supported MIME::WordDecoder "$config{charset}";

# connect to usenet
unless ($config{enable_multiple_news_servers}) {
my $ng = { server => $config{default_nntp_server},
username => $config{default_nntp_username},
password => $config{default_nntp_password}
};
$c = connect_usenet(\$ng);
}

# loop through newsgroups and do the business
foreach my $newsgroup (@newsgroups) {
if ($newsgroup->{enabled}) {
my $batch_end;
if($config{enable_multiple_news_servers}) {
if($newsgroup->{server} ne $current_server) {
$c && $c->quit;
$c = connect_usenet(\$newsgroup);
$current_server = $newsgroup->{server};
}
}
# post any thread starters bound for usenet
post_outgoing_t($c,\$newsgroup);
my ($svrfirstmsg,$svrlastmsg,$tmp) = $c->group($newsgroup->{newsgroup});
if ($newsgroup->{lastmsg} eq 0) {
$newsgroup->{lastmsg} = $svrfirstmsg;
} elsif ($newsgroup->{lastmsg} > $svrlastmsg) {
$newsgroup->{lastmsg} = $svrlastmsg;
}
if ($newsgroup->{lastmsg}+$config{batch_limit}-1 > $svrlastmsg) {
$batch_end = $svrlastmsg
} else {
$batch_end = $newsgroup->{lastmsg}+$config{batch_limit};
}
console("\nPulling $newsgroup->{newsgroup}...");
if ($svrlastmsg > $newsgroup->{lastmsg}) {
# get batch of articles from nntp server
my @articles = get_batch($newsgroup->{lastmsg},$batch_end,$c);
if ($#articles >=0) {
# process the batch
article_batch_process(\@articles,\$newsgroup);
} else {
db_execute("UPDATE usenet_group SET lastmsg = $batch_end WHERE forum = $newsgroup->{forumid}");
}
# load incoming thread starters
article_forum_load_t(\$newsgroup);
console(" -> finding replies... \n");
} else {
console(" No new messages.\n");
}
# load incoming replies
my @updated_threads = article_forum_load_p(\$newsgroup);
my $tc=@updated_threads;
# notify users of replies
if ($tc && $config{email_notify}) {
notify_new_posts(@updated_threads);
}
# post outgoing articles
post_outgoing_p($c,\$newsgroup);
}
}

# disconnect from usenet
unless ($config{enable_multiple_news_servers}) {
$c->quit;
console("Clean disconnection from $config{default_nntp_server}\n");
}

# delete orphans from usenet_article table
article_expire();

# expire old usenet messages from forums
if ($config{auto_purge}) {
article_purge();
}

# clean up and quit
db_execute("TRUNCATE TABLE usenet_article_loader");
$dbh->disconnect;
unlink $pid_file;







########################################################################
# Sub routines from here on

sub get_arrays {

my $qry = db_fetch("SELECT varname, value FROM usenet_setting");
while (my($varname,$value)=$qry->fetchrow_array()) {
$config{$varname} = $value;
}
$qry->finish;

$qry = db_fetch("SELECT varname, value FROM setting");
while (my($varname,$value)=$qry->fetchrow_array()) {
$vbconfig{$varname} = $value;
}
$qry->finish;

$qry = db_fetch("SELECT newsgroup,forum,lastmsg,server,username,password,footer,enabled FROM usenet_group");
while (my($newsgroup,$forumid,$lastmsg,$server,$username,$password,$footer,$enabled) = $qry->fetchrow_array) {
if (!$server) {
$server = $config{default_nntp_server};
$username = $config{default_nntp_username};
$password = $config{default_nntp_password};
}
push (@newsgroups,{newsgroup => $newsgroup,
forumid => $forumid,
lastmsg => $lastmsg,
server => $server,
username => $username,
password => $password,
footer => $footer,
enabled => $enabled});
}
$qry->finish;

$qry = db_fetch("SELECT scope, expression, regex FROM usenet_spam_filter");
while (my ($scope, $expression, $regex) = $qry->fetchrow_array) {
if (!$regex) {
$expression = quotemeta($expression);
}
push (@spamfilters, {scope => $scope, expression => $expression, regex => $regex});
}
$qry->finish;

$qry = db_fetch("SELECT linemode, original, new, regex FROM usenet_replace");
while (my ($linemode, $original, $replace, $regex) = $qry->fetchrow_array) {
if (!$regex) {
$original = quotemeta($original);
}
if ($linemode) {
$original='.*'.$original.'.*';
}
push (@replacements, {original => $original, replace => $replace, regex => $regex, linemode => $linemode});
}
$qry->finish;
}


sub article_batch_process {
my $a = $_[0];
my $newsgroup = $_[1];
my ($lastmsg);
my $cnt = 0;
my $missing = 0;
my @cleanarticles = ();
my @ra = ();
console(" -> processing article batch...\n");
db_execute("begin work");
foreach my $art (@$a) {
my $threadid = 0;
my $postid =0;
$cnt++;
my $postexists = 0;
my $r = $$art{refs};
my $b = $$art{body};
if ($b) {
$$art{body} = join("",@$b);
$$art{body} =~ s/begin\s+666.*\nend\n//gsoi;
$$art{body}=$wd->decode($$art{body});
$$art{body} = article_cleanup($$art{body});
}
if ($$art{refs}) {
$$art{refs} =~ s/,|\s//go; $$art{refs} =~ s/></> </go;
} else {
if ($$art{subject}) {
$$art{subject} =~ s/^\[GENERAL\] //gso;
$$art{subject} =~ s/\</\(/go; $$art{subject} =~ s/\>/\)/go;
$$art{subject} =~ s/^\s+//gso; $$art{subject} =~ s/\s+$//gso;
if ($$art{subject} =~ /^Re:\s+(.*)/o) {
my $qry = db_fetch("SELECT msgid FROM thread WHERE forumid = $$newsgroup->{forumid} AND title = ".$dbh->quote($1)." ORDER BY dateline LIMIT 1");
my $msgid = $qry->fetchrow_array; $qry->finish;
if($msgid) {
$$art{refs} = "$msgid";
}
}
}
}
$lastmsg = $$art{msgnum};
my $forumid = $$newsgroup->{forumid};
my $msgnum = $$art{msgnum};
if (!$$art{subject} || $$art{subject} =~ /^\s+$/o) {
$$art{subject} = "[No Subject]";
}
if ($$art{msgid} && $$art{body} && $$art{msgnum}) {
my $group = $dbh->quote($$newsgroup->{newsgroup});
my $msgid = $dbh->quote($$art{msgid});
my $nntpdtm = $$art{nntpdtm};
if ($nntpdtm > time()+600) {
$nntpdtm = time();
}
$nntpdtm = $dbh->quote($nntpdtm);
my $subject = $dbh->quote($$art{subject});
my $poster = $dbh->quote($$art{poster});
my $email = $dbh->quote($$art{email});
my $refs = $dbh->quote($$art{refs});
my $body = $dbh->quote($$art{body});
my $nntpposter = $dbh->quote(encode_entities($$art{from}));
if ($$art{refs}) {
@ra = split(/\s/,$$art{refs});
my $tmp = "";
foreach my $ref (@ra) {
my $re = $dbh->quote($ref);
$tmp .= "($msgid,$re,$#ra+1,$nntpdtm),";
}
chop($tmp);
db_execute("INSERT IGNORE INTO usenet_ref (msgid,ref,cnt,dtm) VALUES $tmp",1);
}
if ($$art{useragent} && ($$art{useragent} =~ /$config{useragent}/)) {
($threadid,$postid) = split(/\|/,$$art{xref});
my $dbpostexists = db_fetch("SELECT count(*) FROM post WHERE postid = '$postid'");
$postexists = $dbpostexists->fetchrow_array; $dbpostexists->finish;
if ($postexists) {
db_execute("UPDATE post SET msgid = $msgid WHERE postid = $postid");
db_execute("UPDATE usenet_outgoing_log SET msgid = $msgid WHERE postid = $postid");
db_execute("UPDATE usenet_outgoing SET refs = $msgid WHERE threadid = $threadid");
db_execute("UPDATE usenet_outgoing SET refs = concat(refs,' ',$msgid) WHERE inreplyto = $postid");
if (!$$art{refs}) {
db_execute("UPDATE thread SET msgid = $msgid WHERE threadid = $threadid");
db_execute("UPDATE usenet_outgoing SET refs = $msgid WHERE threadid = $threadid");
db_execute("UPDATE usenet_outgoing SET refs = concat(refs,' ',$msgid) WHERE inreplyto = $postid");
}
db_execute("DELETE FROM usenet_article WHERE msgid = $msgid");
db_execute("DELETE FROM usenet_ref WHERE msgid = $msgid");
}
}
unless ($postexists || ($postid && !$config{recover_deleted_localposts})) {
db_execute("INSERT IGNORE INTO usenet_article (newsgroup,forum,msgid,dtm,subject,poster,email,refs,body,msgnum,nntpposter,ord,threadid,postid) VALUES ($group,$forumid,$msgid,$nntpdtm,$subject,$poster,$email,$refs,$body,$msgnum,$nntpposter,$#ra+1,$threadid,$postid)",1);
}
} else {
$missing++;
}
db_execute("UPDATE usenet_group SET lastmsg = $msgnum WHERE forum = $forumid");
}
db_execute("commit");
console(" -> requested $cnt messages... $missing not available or rejected.\n");
}


sub connect_usenet {
my $newsgroup = $_[0];
my ($server, $port);
if ($$newsgroup->{server} =~ /:/) {
my $connectstring = $$newsgroup->{server}; $connectstring =~ /(.*):(.*)/;
$server = $1; $port = $2;
} else {
$server = $$newsgroup->{server}; $port = '119';
}
console("\n\nConnecting to $$newsgroup->{server}... ");
my $con = new News::NNTPClient($server,$port) or die "Unable to connect to $$newsgroup->{server}";
console("Connected\n");
if ($$newsgroup->{username} && $$newsgroup->{password}) {
console("Sending authentication info... ");
$con->authinfo("$$newsgroup->{username}","$$newsgroup->{password}") or die "Authentication failure";
console("Authenticated and logged in\n");
}
return $con;
}


sub get_batch {
my @m = ($_[0],$_[1]);
my $c = $_[2];
my @a=();
my $h;
console("\n -> fetching articles $m[0] to $m[1]... \n");
my $mid = $c->stat($m[0]);
for (my $msg=$m[0]+1;$msg<=$m[1];$msg++) {
undef($h);
my %article=();
if ($h = $c->head($mid)) {
console(" -> fetching $msg... ");
my($f,$s,$d,$i,$r,$u,$xr,$xn,$l,$p,$e,$xa,$b);
foreach (@$h) {
if (/^From: (.*)/) { $f = $wd->decode($1); }
if (/^Subject: (.*)/) { $s = $wd->decode($1); }
if (/^Date: (.*)/) { $d = str2time($1); }
if (/^Message-ID: (.*)/) { $i = $1; }
if (/^References: (.*)/) { $r = $1; }
if (/^User-Agent: (.*)/) { $u = $1; }
if (/^X-References: (.*)/) { $xr = $1; }
if (/^X-Newsreader: (.*)/) { $xn = $1; }
if (/^Lines: (.*)/) { $l = $1; }
if (/^X-No-Archive: (.*)/) { $xa = $1; }
decode_entities($f); decode_entities($s);
}
if (!$d || $d=~/^\s+/) {
$d=time();
}
($p,$e) = parse_from($f);
my $spam =0;
if (($l && $l < $config{max_msg_length}) || (!$l)) {
foreach my $filter (@spamfilters) {
if ($filter->{scope} =~ /^header$|^all$/o) {
if ($config{switched}) {
if ($s =~ /$filter->{expression}/sgi) {
$spam = 1;
}
} else {
if ($s =~ /$filter->{expression}/si) {
$spam = 1;
}
}
if ($config{switched}) {
if ($f =~ /$filter->{expression}/sgi) {
$spam = 1;
}
} else {
if ($f =~ /$filter->{expression}/si) {
$spam = 1; }
}
}
}
if (!$spam) {
if($b = $c->body($mid)) {
push (@a,{ from => $f,
subject => $s,
nntpdtm => $d,
msgid => $i,
refs => $r,
useragent => $u,
xref => $xr,
xreader => $xn,
lines => $l,
poster => $p,
email => $e,
msgnum => $msg,
body => $b
}
);
}
console(" OK\n");
} else {
console(" Not fetched - Spam\n");
}
} else {
console(" Not fetched - Binary post\n");
}
}
$mid = $c->next();
}
return @a;
}


sub article_forum_load_t {
my $newsgroup = $_[0];
my ($littlesubj,$lastpostdtm);
my $tcount = 0;
# load thread starters
console(" -> inserting new threads from $$newsgroup->{newsgroup}\n");
my $q1 = db_fetch("SELECT a.nntpposter, a.forum, a.msgid, a.dtm, a.subject, a.poster, a.email, a.body, a.ord, a.threadid, a.postid FROM usenet_article AS a LEFT OUTER JOIN post AS b on (a.msgid=b.msgid) WHERE a.forum = $$newsgroup->{forumid} AND b.msgid is null AND (refs = '' OR refs is NULL OR refs = '(none)') AND LEFT(subject,3) <> 'Re:' ORDER BY dtm");
while ( my ($nntpposter,$forumid,$msgid,$nntpdtm,$subject,$poster,$email,$body,$ord,$threadid,$postid) = $q1->fetchrow_array ) {
db_execute("begin work");
$tcount++;
if ($body =~ /\w/o) {
$body = autoformat $body, {right=>$config{article_wrap},all=>1};
}
$body = article_add_bbcode($body);
$body = $dbh->quote($body);
if (!$email) {
$email = $config{nntp_from};
}
$email = $dbh->quote($email);
if(length($subject) > 35) {
$littlesubj = substr($subject,0,35)."...";
} else {
$littlesubj = $subject;
}
$littlesubj = $dbh->quote($littlesubj);
$subject = $dbh->quote($subject);
$poster =~ s/^\s+|\s+$//go;
if ($poster eq "") {
$poster = "Anonymous";
}
$poster = $dbh->quote($poster);
$msgid = $dbh->quote($msgid);
if (!$nntpposter) {
$nntpposter = "Unknown";
}
$nntpposter =~ s/<|>/\#/go; $nntpposter = $dbh->quote($nntpposter);
if ($threadid > 0) {
db_execute ("UPDATE thread SET msgid = $msgid WHERE threadid = $threadid");
} else {
if ($config{time_source} eq 'LOCAL') {
$lastpostdtm = time();
} else {
$lastpostdtm = $nntpdtm;
}
db_execute ("INSERT INTO thread (title,lastpost,forumid,postusername,lastposter,dateline,nntpdateline,msgid,visible,open,postuserid,isusenetpost,regpost) VALUES ($subject,$lastpostdtm,$forumid,$poster,$poster,".time().",$nntpdtm,$msgid,'1','1',0,1,0)");
$threadid = $dbh->{'mysql_insertid'};
db_execute("INSERT INTO post (title,allowsmilie,threadid,dateline,nntpdateline,pagetext,visible,msgid,ord,username,userid,nntpfrom,ipaddress,isusenetpost) VALUES ($subject,$config{allowsmilies},$threadid,".time().",$nntpdtm,$body,'1',$msgid,'0',$poster,0,$nntpposter,0,1)");
$postid = $dbh->{'mysql_insertid'};
my $q2 = db_fetch("SELECT lastpost FROM forum WHERE forumid=$forumid");
my $lastpost = $q2->fetchrow_array; $q2->finish;
if (!$lastpost) {
$lastpost = $lastpostdtm;
}
my $forums = db_fetch("SELECT parentlist FROM forum WHERE forumid = $forumid");
my $forumlist = $forums->fetchrow_array;
db_execute("UPDATE forum SET replycount=replycount+1, threadcount=threadcount+1 ".(($lastpostdtm >= $lastpost)?",lastpost=$lastpostdtm,lastposter=$poster,lastactivethread=$littlesubj":"")." WHERE forumid IN ($forumlist)");
$config{index_posts} && indexpost($postid);
}
db_execute("DELETE FROM usenet_article WHERE msgid = $msgid");
db_execute("DELETE FROM usenet_ref WHERE msgid = $msgid");
db_execute("commit");
}
$q1->finish;
console(" -> inserted $tcount threads\n");
}


sub article_forum_load_p {
my $newsgroup = $_[0];
my $t = time();
my $lastpostdtm;
if ($$newsgroup->{forumid} == 114 || $$newsgroup->{forumid} == 156) {
# check for orphan replies
db_execute("begin work");
my $qo = db_fetch("SELECT msgid, subject FROM usenet_article WHERE forum = $$newsgroup->{forumid} AND subject <> '(no subject)' AND LEFT(subject,3) = 'Re:' AND (refs = '' OR refs is NULL OR refs = '(none)')");
while ( my($msgid,$subject) = $qo->fetchrow_array ) {
my $m; my @am;
$msgid = $dbh->quote($msgid);
$subject =~ s/^Re:\s+//io;
my $tq = db_fetch("SELECT msgid FROM thread WHERE forumid=$$newsgroup->{forumid} AND title =".$dbh->quote($subject));
$m="";
while ( my ($tref) = $tq->fetchrow_array) {
$m .= " $tref";
$tref = $dbh->quote($tref);
db_execute("INSERT IGNORE INTO usenet_ref (msgid,ref,dtm) VALUES ($msgid,$tref,$t)");
} $tq->finish;
if ($m ne "") {
$m =~ s/^\s+|\s+$//gso;
@am = split(/ /,$m);
}
$m = $dbh->quote($m);
db_execute("UPDATE usenet_article SET refs = $m, ord = $#am+1 WHERE msgid = $msgid");
}
db_execute("commit");
$qo->finish;
}
# load replies
db_execute("begin work");
db_execute("TRUNCATE TABLE usenet_article_loader");
db_execute("INSERT IGNORE INTO usenet_article_loader SELECT DISTINCT a.newsgroup, a.forum, a.msgid, a.dtm, a.subject, a.poster, a.email, a.nntpposter, a.refs, a.body, a.msgnum, a.ord, a.threadid, a.postid FROM usenet_article AS a left outer join post AS b ON (a.msgid=b.msgid) WHERE b.msgid IS NULL AND a.forum=$$newsgroup->{forumid}");
db_execute("commit");
my $isreplies = 1;
my $showreplies = 1;
my @updated_threads=();
while ($isreplies) {
# if ($showreplies == 1) {
# console("\n -> loading replies...\n"); $showreplies = 0;
# }
my $q3 = db_fetch("SELECT count(distinct(a.msgid)) AS cnt FROM usenet_article_loader AS a, thread AS b, usenet_ref AS c, post AS d where b.threadid = d.threadid and c.ref = d.msgid and a.msgid = c.msgid AND b.forumid = $$newsgroup->{forumid} AND d.ord <= a.ord");
$isreplies = $q3->fetchrow_array; $q3->finish;
$isreplies && console(" -> loading $isreplies replies...\n ");
$q3 = db_fetch("SELECT DISTINCT a.ord, a.dtm, b.title, a.nntpposter, a.forum, a.msgid, a.subject, a.poster, a.body, a.postid, a.email, b.threadid FROM usenet_article_loader AS a, thread AS b, usenet_ref AS c, post AS d where b.threadid = d.threadid and c.ref = d.msgid and a.msgid = c.msgid AND b.forumid = $$newsgroup->{forumid} AND d.ord <= a.ord ORDER BY a.ord, a.dtm");
# $q3 = db_fetch("SELECT DISTINCT a.ord, a.dtm, b.title, a.nntpposter, a.forum, a.msgid, a.subject, a.poster, a.body, a.postid, a.email, b.threadid FROM usenet_article_loader AS a, thread AS b, usenet_ref AS c, post AS d where b.threadid = d.threadid and c.ref = d.msgid and a.msgid = c.msgid AND b.forumid = $$newsgroup->{forumid}");
my $fbody;
while ( my ($ord,$nntpdtm,$title,$nntpposter,$forumid,$msgid,$subject,$poster,$body,$postid,$email,$threadid) = $q3->fetchrow_array ) {
db_execute("begin work");
my $littlesubj;
if ($body =~ /\w/o) {
$fbody = autoformat $body, {right=>$config{article_wrap},all=>1};
}
$fbody = article_add_bbcode($fbody);
$fbody = $dbh->quote($fbody);
if(length($title) > 35) {
$littlesubj = substr($title,0,35)."...";
} else {
$littlesubj = $title;
}
$littlesubj = $dbh->quote($littlesubj);
$subject = $dbh->quote($subject);
if (!$poster) {
$poster = "Anonymous";
}
$poster = $dbh->quote($poster);
if (!$email) {
$email = $config{nntp_from};
}
$email = $dbh->quote($email);
$msgid = $dbh->quote($msgid);
if (!$nntpposter) {
$nntpposter = "Unknown";
}
$nntpposter =~ s/<|>/\#/go; $nntpposter = $dbh->quote($nntpposter);
if ($postid > 0) {
db_execute("UPDATE post SET msgid=$msgid WHERE postid=$postid");
} else {
db_execute ("UPDATE usenet_outgoing_log SET msgid = $msgid WHERE postid = $postid");
db_execute("INSERT INTO post (title,allowsmilie,threadid,dateline,nntpdateline,pagetext,visible,ord,msgid,username,userid,nntpfrom,ipaddress,isusenetpost) VALUES ($subject,$config{allowsmilies},$threadid,".time().",$nntpdtm,$fbody,'1',$ord,$msgid,$poster,0,$nntpposter,0,1)");
$postid = $dbh->{'mysql_insertid'};
}
db_execute("DELETE FROM usenet_article WHERE msgid = $msgid");
db_execute("DELETE FROM usenet_article_loader WHERE msgid = $msgid");
db_execute("DELETE FROM usenet_ref WHERE msgid = $msgid");
my $q4 = db_fetch("SELECT lastpost FROM thread WHERE threadid=$threadid");
my ($lastpost) = $q4->fetchrow_array; $q4->finish;
if ($config{time_source} eq 'LOCAL') {
$lastpostdtm = time();
} else {
$lastpostdtm = $nntpdtm;
}
if (!$lastpost) {
$lastpost = $lastpostdtm;
}
db_execute("UPDATE thread SET replycount = replycount+1 ".(($lastpostdtm >= $lastpost)?",lastpost=$lastpostdtm,lastposter=$poster":"")." WHERE threadid=$threadid");
my $q5 = db_fetch("SELECT lastpost FROM forum WHERE forumid=$forumid");
$lastpost = $q5->fetchrow_array; $q5->finish;
if (!$lastpost) {
$lastpost = $lastpostdtm;
}
my $forums = db_fetch("SELECT parentlist FROM forum WHERE forumid = $forumid");
my $forumlist = $forums->fetchrow_array;
db_execute("UPDATE forum SET replycount=replycount+1 ".(($lastpostdtm >= $lastpost)?",lastpost=$lastpostdtm,lastposter=$poster,lastactivethread=$littlesubj":"")." WHERE forumid IN ($forumlist)");
$config{index_posts} && indexpost($postid);
push(@updated_threads,$threadid);
}
db_execute("commit");
}
console("\n");
return @updated_threads;
}


sub article_expire {
my $expire = time() - ($config{expire_days} * 86400);
db_execute("begin work");
db_execute("DELETE FROM usenet_article WHERE dtm < $expire");
db_execute("DELETE FROM usenet_ref WHERE dtm < $expire");
db_execute("commit");
}


sub article_quote {
my $fbody = $_[0];
my @ibody = ();
my @abody = split(/\n/,$fbody);
foreach my $albody (@abody) {
if ($albody =~ /^>>>|^> > >/o) {
push(@ibody,'[q3]'.$albody."[/q3]\n");
} elsif ($albody =~ /^>>|^> >/o) {
push(@ibody,'[q2]'.$albody."[/q2]\n");
} elsif ($albody =~ /^>|^:|^\|/o) {
push(@ibody,'[q1]'.$albody."[/q1]\n");
} else {
push(@ibody,$albody."\n");
}
}
$fbody = join("",@ibody);
return $fbody;
}


sub cleantext {
my $text = $_[0];
$text =~ s/^\s+//o;
$text =~ s/\s+$//o;
return $text;
}


sub indexpost {
my $id = $_[0];
my ($intitle,$sth);
$sth = db_fetch("SELECT title, pagetext FROM post WHERE postid=$id");
my ($title, $pagetext) = $sth->fetchrow_array;
$sth->finish;
# index title
if ($title) {
my $text = remove_bb_code("$title");
$text = wordsonly("$text");
my @words = split(/\s+/,$text);
my $words_sel="";
my $words_ins="";
my $search_ins="";
foreach my $word (@words) {
if ($word && ((length($word) >= $vbconfig{minsearchlength})) && ((length($word) <= $vbconfig{maxsearchlength}))) {
$word = $dbh->quote($word);
$words_sel .= "$word,";
$words_ins .= "($word),";
}
}
chop $words_sel;
chop $words_ins;
if ($words_ins) {
db_execute("INSERT IGNORE INTO word (title) VALUES $words_ins");
}
if ($words_sel) {
my $wordids = db_fetch("SELECT wordid FROM word WHERE title in ($words_sel)");
while (my $wid = $wordids->fetchrow_array) {
$search_ins .= "($wid,$id,1),";
}
chop $search_ins;
db_execute("INSERT IGNORE INTO searchindex (wordid,postid,intitle) VALUES $search_ins");
}
}

# index post body
$pagetext =~ s/^\[q[1-9]\]>+.*$//go; # remove all quoted stuff
if (length($pagetext) < 10000) {
my $text = remove_bb_code("$pagetext");
$text = wordsonly("$text");
my @words = split(/\s+/,$text);
my $words_sel="";
foreach my $word (@words) {
if ($word && ((length($word) >= $vbconfig{minsearchlength})) && ((length($word) <= $vbconfig{maxsearchlength}))) {
$word = $dbh->quote($word);
$words_sel .= "$word,";
db_execute("INSERT IGNORE INTO word (title) VALUES ($word)");
}
}
chop $words_sel;
if ($words_sel) {
my $wordids = db_fetch("SELECT wordid FROM word WHERE title in ($words_sel)");
while (my $wid = $wordids->fetchrow_array) {
db_execute("INSERT IGNORE INTO searchindex (wordid,postid,intitle) VALUES ($wid,$id,0)");
}
}
} else {
console(" *-> Post $id skipped... (too long)\n");
}
}


sub console {
if ($config{output_to_console}) {
print $_[0];
}
}


sub db_connect {
my $dsn = "DBI:mysql:database=$db;host=$db_host";
my $dbh = DBI->connect( $dsn,
$db_username,
$db_password,
{ RaiseError => 1,
PrintError => 0}
) or die "Cant connect to $db using $db_username\@$db_host";
return $dbh;
}


sub db_fetch {
my $sql = $_[0];
my $qry;
$qry = $dbh->prepare($sql) or die $!;
$qry->execute() or die "Query failed ($sql) $!";
return $qry;
}


sub db_execute {
my $sql = $_[0];
my $ignore_when_fail = $_[1];
eval { $dbh->do($sql) };
if ($@) {
if(defined($ignore_when_fail)) { return 0;
} else {
die "\nQuery failed:\n$sql\n\n$@";
}
}
return 1;
}


sub notify_new_posts {
my $threads = join(',',@_);
my $q1 = db_fetch("SELECT template FROM template WHERE title = 'email_notify'");
my $template = $q1->fetchrow_array; $q1->finish;
$q1 = db_fetch("SELECT template FROM template WHERE title = 'emailsubject_notify'");
my $subjtemplate = $q1->fetchrow_array; $q1->finish;
$q1 = db_fetch("SELECT DISTINCT a.threadid,a.title,a.lastposter,b.title,c.userid,e.email,e.emailnotification,e.username FROM thread AS a, forum AS b, subscribethread AS c, usenet_group AS d, user AS e WHERE a.forumid=b.forumid AND b.forumid=d.forum AND a.threadid=c.threadid AND c.userid=e.userid AND (a.threadid IN($threads))");
while (my ($threadid,$subject,$poster,$forumtitle,$userid,$email,$notify,$username) = $q1->fetchrow_array) {
my $tpl = $template;
my $stpl = $subjtemplate;
if ($notify) {
$tpl =~ s/\$touser\[username\]/$username/g;
$tpl =~ s/\$bbuserinfo\[username\]/$poster/g;
$tpl =~ s/\$threadinfo\[title\]/$subject/g;
$tpl =~ s/\$foruminfo\[title\]/$forumtitle/g;
$tpl =~ s/\$bbtitle/$vbconfig{bbtitle}/g;
$tpl =~ s/\$bburl/$vbconfig{bburl}/g;
$tpl =~ s/\$threadinfo\[threadid\]/$threadid/g;
$stpl =~ s/\$threadinfo\[title\]/$subject/g;
if ($email) {
console("Sending email to $username regarding reply to threadid $threadid\n");
sendmail($email,$tpl,$stpl);
}
}
}
$q1->finish;
}


sub sendmail {
my $smtp;
my $from = $vbconfig{bbtitle}." Mailer <".$vbconfig{webmasteremail}.">";
my ($recipient,$message,$subject) = @_;
$smtp = Net::SMTP->new($config{smtp_server});
$smtp->mail($recipient);
$smtp->to($recipient);
$smtp->data();
$smtp->datasend("Subject: $subject\n");
$smtp->datasend("From: $from\n");
$smtp->datasend("To: $recipient\n");
$smtp->datasend("\n");
$smtp->datasend("$message\n");
$smtp->dataend();
$smtp->quit;
console("email notification sent to $recipient\n");
}


sub article_add_bbcode {
my $text = $_[0];
$text =~ s/^\s+//gio; $text =~ s/\s+$//gio; $text =~ s/\n+$//gio;
$text =~ s/>$//gos; $text =~ s/^\s+//gio; $text =~ s/\s+$//gio;
$text =~ s/\n+$//gio;
$text =~ s/\n\n\n/\n\n/gso; $text =~ s/\n\n\n/\n\n/gso;
if ($config{hyperlinked_uri}) {
find_uris($text, sub { my ($uri, $orig_uri) = @_;
return '[url="'.$uri.'"]'.$orig_uri.'[/url]';
});
}
if ($config{custom_quotes}) {
$text = article_quote($text);
}
return $text;
}


sub article_cleanup {
my $s = $_[0];
foreach my $filter (@spamfilters) {
if ($filter->{scope} =~ /^all$|^body$/o) {
if ($config{switched}) {
if ($s =~ /$filter->{expression}/gsi) {
$s = "";
}
} else {
if ($s =~ /$filter->{expression}/gi) {
$s = "";
}
}
}
}
foreach my $replacement (@replacements) {
if ($replacement->{switched}) {
$s =~ s/$replacement->{original}/$replacement->{replace}/gsi;
} else {
$s =~ s/$replacement->{original}/$replacement->{replace}/gi;
}
}
$s =~ s/^\s+//gso; $s =~ s/\s+$//gso; $s =~ s/^://so; $s =~ s/^\s+//go;
$s =~ s/\s+$//go; $s =~ s/^\n$//go; $s =~ s/^>$//gso; $s =~ s/^:$//go;
$s =~ s/^\:$//go; $s =~ s/\n\n\n/\n\n/go; $s =~ s/>$//gso;
$s =~ s/\s+>$//gso; $s =~ s/^>\s+\n//gso; $s =~ s/^>\n//gso;
$s =~ s/^\s+>\s+\n//gso; $s =~ s/^\s+>\n//gso;
return $s;
}


sub post_outgoing_t {
my $c = $_[0];
my $newsgroup = $_[1];
my ($email,$signature,$fld,$sfld);
my $fldids = db_fetch("SELECT profilefieldid FROM profilefield WHERE title = '$config{'email_profile_id'}'");
my $fldid = $fldids->fetchrow_array;
my $sfldids = db_fetch("SELECT profilefieldid FROM profilefield WHERE title = '$config{'signature_profile_id'}'");
my $sfldid = $sfldids->fetchrow_array;
if($fldid) {
$fld = "field$fldid";
}
if($sfldid) {
$sfld = "field$sfldid";
}
my $threads = db_fetch("SELECT ". ($sfldid ? "e.$sfld AS csig," : "") . ($fldid ? "e.$fld AS cemail," : "") ." b.email, b.showemail, b.signature, a.threadid, a.postusername, a.title, d.newsgroup FROM thread AS a, user AS b, usergroup AS c, usenet_group AS d, userfield AS e WHERE a.visible=1 AND forumid=$$newsgroup->{forumid} AND a.postuserid=b.userid AND b.usergroupid=c.usergroupid AND b.userid=e.userid AND a.isusenetpost=0 AND a.forumid=d.forum");
db_execute("begin work");
while (my $thread = $threads->fetchrow_hashref()) {
my $poster = $dbh->quote($thread->{postusername});
my $subject = $dbh->quote($thread->{title});
my $newsgroup = $dbh->quote($thread->{newsgroup});
if ($config{include_sig}) {
if ($thread->{csig}) {
$signature = $thread->{csig};
} else {
$signature = $thread->{signature};
}
$signature = remove_bb_code($signature);
} else {
$signature = "";
}
if ($config{use_custom_email}) {
if($thread->{cemail}) {
$email = $thread->{cemail};
} else {
$email = $config{nntp_from};
}
} elsif ($config{allow_user_email_address} && $thread->{showmail} == 1) {
$email = $config{email};
} else {
$email = $config{nntp_from};
}
$email = $dbh->quote($email);
$signature = $dbh->quote($signature);
my $posts = db_fetch("SELECT showsignature, postid, pagetext FROM post WHERE threadid=$thread->{threadid} ORDER BY postid LIMIT 1");
my $post = $posts->fetchrow_hashref();
if (!$post->{showsignature}) {
$signature = "''";
}
my $body = $post->{pagetext};
if($config{spam_check_outgoing}) {
$body=spam_check($body);
}
if($config{replace_on_outgoing}) {
$body=replace_filters($body);
}
unless ($body eq "") {
$body = $dbh->quote($body);
db_execute("INSERT IGNORE INTO usenet_outgoing(poster,email,signature,newsgroup,subject,body,threadid,postid) VALUES ($poster,$email,$signature,$newsgroup,$subject,$body,$thread->{'threadid'},$post->{'postid'})");
}
db_execute("UPDATE thread SET isusenetpost = -1, nntpdateline=dateline WHERE threadid = '$thread->{threadid}'");
db_execute("UPDATE post SET isusenetpost = -1, nntpdateline=dateline WHERE postid = '$post->{postid}'");
$posts->finish;
}
$threads->finish;
db_execute("commit");
post_outgoing($c,$newsgroup);
}


sub post_outgoing_p {
my $c = $_[0];
my $newsgroup = $_[1];
my ($email,$signature,$fld,$sfld);
my $fldids = db_fetch("SELECT profilefieldid FROM profilefield WHERE title = '$config{'email_profile_id'}'");
my $fldid = $fldids->fetchrow_array;
my $sfldids = db_fetch("SELECT profilefieldid FROM profilefield WHERE title = '$config{'signature_profile_id'}'");
my $sfldid = $sfldids->fetchrow_array;
if ($fldid) {
$fld = "field$fldid";
}
if($sfldid) {
$sfld = "field$sfldid";
}
my $posts = db_fetch("SELECT ".($sfldid ? "f.$sfld AS csig," : "") . ($fldid ? "f.$fld AS cemail," : "")." b.email, b.showemail, b.signature, a.postid, a.threadid, a.inreplyto, b.username, e.newsgroup, d.title, a.pagetext FROM post AS a, user AS b, usergroup AS c, thread AS d, usenet_group AS e, userfield AS f WHERE a.visible=1 AND a.userid=b.userid AND b.usergroupid=c.usergroupid AND a.threadid=d.threadid AND d.forumid=e.forum AND b.userid=f.userid AND e.forum=$$newsgroup->{'forumid'} AND a.isusenetpost=0");
db_execute("begin work");
while (my $post = $posts->fetchrow_hashref()) {
my $poster = $dbh->quote($post->{'username'});
my $newsgroup = $dbh->quote($post->{'newsgroup'});
my $subject = $dbh->quote("Re: ".$post->{'title'});
my $body = $post->{'pagetext'};
if ($config{include_sig}) {
if ($post->{csig}) {
$signature = $post->{csig};
} else {
$signature = $post->{signature};
}
$signature = remove_bb_code($signature);
} else {
$signature = "";
}
if ($config{use_custom_email}) {
if($post->{'cemail'}) {
$email = $post->{'cemail'};
} else {
$email = $config{nntp_from};
}
} elsif ($config{allow_user_email_address} && $post->{'showmail'} == 1) {
$email = $config{email};
} else {
$email = $config{nntp_from};
}
$email = $dbh->quote($email);
$signature = $dbh->quote($signature);
if($config{spam_check_outgoing}) {
$body=spam_check($body);
}
if($config{replace_on_outgoing}) {
$body=replace_filters($body);
}
my $threads = db_fetch("SELECT msgid,title FROM thread WHERE threadid = $post->{'threadid'}");
my $thread = $threads->fetchrow_hashref();
my $inreplys = db_fetch("SELECT msgid FROM post WHERE postid=$post->{inreplyto}");
my $replytomsgid = $inreplys->fetchrow_array;
my $refs = $dbh->quote("$thread->{msgid} $replytomsgid");
unless ($body eq "") {
$body = $dbh->quote($body);
db_execute("INSERT IGNORE INTO usenet_outgoing(poster,email,signature,newsgroup,subject,refs,body,threadid,postid,inreplyto) VALUES ($poster,$email,$signature,$newsgroup,$subject,$refs,$body,$post->{'threadid'},$post->{'postid'},$post->{inreplyto})");
}
db_execute("UPDATE post SET title=".$dbh->quote("Re: $thread->{title}").", isusenetpost = -1, nntpdateline=dateline WHERE postid = $post->{postid}");
}
db_execute("commit");
$posts->finish;
post_outgoing($c,$newsgroup);
}


sub post_outgoing {
my $c = $_[0];
my $newsgroup = $_[1];
my @article=();
my (@r,$fbody,$fquote,$qp);
my $q2 = db_fetch("SELECT poster,email,signature,newsgroup,subject,refs,body,threadid,postid,inreplyto FROM usenet_outgoing WHERE (refs <> ' ' OR subject NOT LIKE 'Re: %') AND newsgroup = ". $dbh->quote($$newsgroup->{'newsgroup'}));
while ( my ($poster,$email,$signature,$group,$subject,$refs,$body,$threadid,$postid,$inreplyto) = $q2->fetchrow_array ) {
my $quote = $body;
$quote =~ m/\[QUOTE\](.*)\[\/QUOTE\]/iso; $quote = $1;
if ($quote) {
my $regex = quotemeta($quote);
$body =~ s/$regex//s;
$quote =~ m/Originally posted by (.*)\[\/i\]/so; $qp = $1;
$quote =~ s/\[i\]Originally posted by.*\[\/i\]//iso;
$quote = remove_bb_code($quote);
}
if ($refs) {
@r = split(/\s/,$refs);
}
my $re = "";
foreach my $ref (@r) {
if ($ref =~ /^</o) {$re = $re . " " . $ref;}
}
push(@article,"Subject: $subject\n");
push(@article,"From: $poster <$email>\n");
push(@article,"Sender: $poster\n");
push(@article,"Newsgroups: $group\n");
push(@article,"User-Agent: $config{useragent}\n");
if ($re) {
push(@article,"References: $re\n");
}
push(@article,"Organization: $config{useragent}\n");
push(@article,"X-Newsreader: $config{useragent}\n");
push(@article,"X-References: $threadid|$postid\n");
$body = remove_bb_code($body);
if ($body =~ /\w/o) {
$fbody = autoformat $body, {right=>72,all=>1};
}
if ($quote) {
$fquote = autoformat $quote, {right=>72,all=>1};
$fquote =~ s/^\n\s+$|^\n$|^>$|^>\s+$|^\s+>\s+$//gos;
my @quotes = split(/\n/,$fquote);
push(@article,"\n");
push(@article,"$qp wrote:\n");
foreach my $q (@quotes) {
if ($q) {
push(@article," > $q\n");
}
}
}
push(@article,"\n$fbody\n");
if (!$$newsgroup->{footer}) {
$$newsgroup->{footer} = $config{outgoing_post_footer};
}
if ($signature) {
$$newsgroup->{footer} = "$signature\n\n".$$newsgroup->{footer};
}
$$newsgroup->{footer} =~ s/\\n/\n/go;
push(@article,"\n\n--\n".$$newsgroup->{footer}."\n");
if ($config{postingenabled}) {
console("Posting message by $poster to $group...");
if ($poster && $body) {
$c->post(@article);
console(" Sent\n");
}
}
db_execute("DELETE FROM usenet_outgoing WHERE postid = $postid");
$poster = $dbh->quote($poster);
$group = $dbh->quote($group);
$subject = $dbh->quote($subject);
$refs = $dbh->quote($refs);
$body = $dbh->quote($body);
$signature = $dbh->quote($signature);
$email = $dbh->quote($email);
my $dtm = time();
db_execute("INSERT INTO usenet_outgoing_log (dtm,poster,email,signature,newsgroup,subject,refs,body,threadid,postid,inreplyto) VALUES ($dtm,$poster,$email,$signature,$group,$subject,$refs,$body,$threadid,$postid,$inreplyto)");
@article=(); @r=();
} $q2->finish;
}


sub spam_check {
my $s = $_[0];
foreach my $filter (@spamfilters) {
if ($filter->{scope} =~ /^all$|^body$/o) {
if ($config{switched}) {
if ($s =~ /$filter->{expression}/gsi) {
$s = "";
}
} else {
if ($s =~ /$filter->{expression}/gi) {
$s = "";
}
}
}
}
return $s;
}


sub replace_filters {
my $s = $_[0];
foreach my $replacement (@replacements) {
if ($replacement->{switched}) {
$s =~ s/$replacement->{original}/$replacement->{replace}/gsi;
} else {
$s =~ s/$replacement->{original}/$replacement->{replace}/gi;
}
}
return $s;
}


sub article_purge {
console("\nExpiring usenet articles older than $config{auto_purge_days} days...\n");
my @post_batch=();
my @thread_batch=();
my ($pin_clause, $tin_clause);
db_execute("begin work");
my $forums = db_fetch("SELECT forum, newsgroup FROM usenet_group");
while (my($forumid,$newsgroup) = $forums->fetchrow_array) {
my $delt =0; my $delp =0;
my $purge = time() - ($config{auto_purge_days} * 86400);
console(" -> $newsgroup...");
my $threads = db_fetch("SELECT threadid FROM thread WHERE forumid=$forumid AND isusenetpost <> 0 AND dateline < $purge".(!$config{auto_purge_reg}?" AND regpost = 0":""));
while (my($threadid) = $threads->fetchrow_array) {
$delt++;
my $posts = db_fetch("SELECT postid FROM post WHERE threadid = $threadid");
while (my($postid) = $posts->fetchrow_array) {
db_execute("delete from searchindex where postid = $postid");
$delp++;
# push(@post_batch, $postid);
# if($#post_batch > 1000) {
# $pin_clause = join(",",@post_batch);
# db_execute("DELETE FROM searchindex WHERE postid IN ($pin_clause)");
# @post_batch=(); $pin_clause="";
# }
}
db_execute("DELETE FROM post where threadid = $threadid");
$posts->finish;
db_execute("DELETE FROM thread where threadid = $threadid");
}
$threads->finish;
# db_execute("DELETE FROM thread WHERE forumid = $forumid AND lastpost < $purge".(!$config{auto_purge_reg}?" AND regpost = 0":""));
db_execute("UPDATE forum SET threadcount=threadcount-$delt, replycount=replycount-$delp WHERE forumid=$forumid");
db_execute("commit");
console(" OK\n");
}
$forums->finish;
if(@post_batch) {
$pin_clause = join(",",@post_batch);
db_execute("DELETE FROM searchindex WHERE postid IN ($pin_clause)");
}
if(@thread_batch) {
$tin_clause = join(",",@thread_batch);
db_execute("DELETE FROM post WHERE threadid IN ($tin_clause)");
}
db_execute("commit");
console("Expire completed.\n");
}


sub parse_from {
my $from = $_[0];
my ($email,$name,$qe,$epref);
if (defined($from)) {
$from =~ s/^\s+//gos; $from =~ s/\s+$//gos;
foreach my $replacement (@replacements) {
if ($replacement->{switched}) {
$from =~ s/$replacement->{original}/$replacement->{replace}/gsi;
} else {
$from =~ s/$replacement->{original}/$replacement->{replace}/gi;
}
}
$from =~ s/\[/-lsq/g; $from =~ s/\[/-rsq/g;
if (my @mail_address = Mail::Address->parse($from)) {
$email = $mail_address[0]->address();
$from = $mail_address[0]->name();
if (!$from){
$from = $mail_address[0]->user();
}
$from =~ s/-rsq/[/g; $from =~ s/-lsq/]/g;
$from = substr($from,0,$vbconfig{maxuserlength});
}
} else {
$from = "Anonymous";
}
return ($from,$email);
}

sub wordsonly {
my $text = lc($_[0]);
$text =~ s/\%20/ /gso;
$text =~ s/(__)+//g;
$text =~ s/\bagree\b|\bcuz\b|\bcoz\b|\bhtmlwww\b|\btesttesttest\b|\btesttest\b|\bwww\b|\bcom\b|\bheh\b|\bsupposed\b|\bsupposed\b|\bdont\b|\badvice\b|\boccur\b|\boccurs\b|\bguys|\b|\byep\b|\byes\b|\byeah\b|\busenetquotecolor\b|\bthings\b|\bsolved\b|\bquestion\b|\bthinks\b|\bthink\b|\bthought\b|\bhappens\b|\bhappen\b|\bnope\b|\bheard\b|\bbtw\b|\biirc\b|\bafair\b|\bhth\b|\bregards\b|\bcould\b|\bplenty\b|\ba\b|\ba\'s\b|\bable\b|\babout\b|\babove\b|\baccording\b|\baccordingly\b|\bacross\b|\bactually\b|\bafter\b|\bafterwards\b|\bagain\b|\bagainst\b|\bain\'t\b|\ball\b|\ballow\b|\ballows\b|\balmost\b|\balone\b|\balong\b|\balready\b|\balso\b|\balthough\b|\balways\b|\bam\b|\bamong\b|\bamongst\b|\ban\b|\band\b|\banother\b|\bany\b|\banybody\b|\banyhow\b|\banyone\b|\banything\b|\banyway\b|\banyways\b|\banywhere\b|\bapart\b|\bappear\b|\bappreciate\b|\bappropriate\b|\bare\b|\baren\'t\b|\baround\b|\bas\b|\baside\b|\bask\b|\basking\b|\bassociated\b|\bat\b|\bavailable\b|\baway\b|\bawfully\b|\bb\b|\bbe\b|\bbecame\b|\bbecause\b|\bbecome\b|\bbecomes\b|\bbecoming\b|\bbeen\b|\bbefore\b|\bbeforehand\b|\bbehind\b|\bbeing\b|\bbelieve\b|\bbelow\b|\bbeside\b|\bbesides\b|\bbest\b|\bbetter\b|\bbetween\b|\bbeyond\b|\bboth\b|\bbrief\b|\bbut\b|\bby\b|\bc\b|\bc\'mon\b|\bc\'s\b|\bcame\b|\bcan\b|\bcan\'t\b|\bcannot\b|\bcant\b|\bcause\b|\bcauses\b|\bcertain\b|\bcertainly\b|\bchanges\b|\bclearly\b|\bco\b|\bcom\b|\bcome\b|\bcomes\b|\bconcerning\b|\bconsequently\b|\bconsider\b|\bconsidering\b|\bcontain\b|\bcontaining\b|\bcontains\b|\bcorresponding\b\bcould\b|\bcouldn\'t\b|\bcourse\b|\bcurrently\b|\bd\b|\bdefinitely\b|\bdescribed\b|\bdespite\b|\bdid\b|\bdidn\'t\b|\bdifferent\b|\bdo\b|\bdoes\b|\bdoesn\'t\b|\bdoing\b|\bdon\'t\b|\bdone\b|\bdown\b|\bdownwards\b|\bduring\b|\be\b|\beach\b|\bedu\b|\beg\b|\beight\b|\beither\b|\belse\b|\belsewhere\b|\benough\b|\bentirely\b|\bespecially\b|\bet\b|\betc\b|\beven\b|\bever\b|\bevery\b|\beverybody\b|\beveryone\b|\beverything\b|\beverywhere\b|\bex\b|\bexactly\b|\bexample\b|\bexcept\b|\bf\b|\bfar\b|\bfew\b|\bfifth\b|\bfirst\b|\bfive\b|\bfollowed\b|\bfollowing\b|\bfollows\b|\bfor\b|\bformer\b|\bformerly\b|\bforth\b|\bfour\b|\bfrom\b|\bfurther\b|\bfurthermore\b|\bg\b|\bget\b|\bgets\b|\bgetting\b|\bgiven\b|\bgives\b|\bgo\b|\bgoes\b|\bgoing\b|\bgone\b|\bgot\b|\bgotten\b|\bgreetings\b|\bh\b|\bhad\b|\bhadn\'t\b|\bhappens\b|\bhardly\b|\bhas\b|\bhasn\'t\b|\bhave\b|\bhaven\'t\b|\bhaving\b|\bhe\b|\bhe\'s\b|\bhello\b|\bhelp\b|\bhence\b|\bher\b|\bhere\b|\bhere\'s\b|\bhereafter\b|\bhereby\b|\bherein\b|\bhereupon\b|\bhers\b|\bherself\b|\bhi\b|\bhim\b|\bhimself\b|\bhis\b|\bhither\b|\bhopefully\b|\bhow\b|\bhowbeit\b|\bhowever\b|\bi\b|\bi\'d\b|\bi\'ll\b|\bi\'m\b|\bi\'ve\b|\bie\b|\bif\b|\bignored\b|\bimmediate\b|\bin\b|\binasmuch\b|\binc\b|\bindeed\b|\bindicate\b|\bindicated\b|\bindicates\b|\binner\b|\binsofar\b|\binstead\b|\binto\b|\binward\b|\bis\b|\bisn\'t\b|\bit\b|\bit\'d\b|\bit\'ll\b|\bit\'s\b|\bits\b|\bitself\b|\bj\b|\bjust\b|\bk\b|\bkeep\b|\bkeeps\b|\bkept\b|\bknow\b|\bknows\b|\bknown\b|\bl\b|\blast\b|\blately\b|\blater\b|\blatter\b|\blatterly\b|\bleast\b|\bless\b|\blest\b|\blet\b|\blet\'s\b|\blike\b|\bliked\b|\blikely\b|\blittle\b|\blook\b|\blooking\b|\blooks\b|\bltd\b|\bm\b|\bmainly\b|\bmany\b|\bmay\b|\bmaybe\b|\bme\b|\bmean\b|\bmeanwhile\b|\bmerely\b|\bmight\b|\bmore\b|\bmoreover\b|\bmost\b|\bmostly\b|\bmuch\b|\bmust\b|\bmy\b|\bmyself\b|\bn\b|\bname\b|\bnamely\b|\bnd\b|\bnear\b|\bnearly\b|\bnecessary\b|\bneed\b|\bneeds\b|\bneither\b|\bnever\b|\bnevertheless\b|\bnew\b|\bnext\b|\bnine\b|\bno\b|\bnobody\b|\bnon\b|\bnone\b|\bnoone\b|\bnor\b|\bnormally\b|\bnot\b|\bnothing\b|\bnovel\b|\bnow\b|\bnowhere\b|\bo\b|\bobviously\b|\bof\b|\boff\b|\boften\b|\boh\b|\bok\b|\bokay\b|\bold\b|\bon\b|\bonce\b|\bone\b|\bones\b|\bonly\b|\bonto\b|\bor\b|\bother\b|\bothers\b|\botherwise\b|\bought\b|\bour\b|\bours\b|\bourselves\b|\bout\b|\boutside\b|\bover\b|\boverall\b|\bown\b|\bp\b|\bparticular\b|\bparticularly\b|\bper\b|\bperhaps\b|\bplaced\b|\bplease\b|\bplus\b|\bpossible\b|\bpresumably\b|\bprobably\b|\bprovides\b|\bq\b|\bque\b|\bquite\b|\bqv\b|\br\b|\brather\b|\brd\b|\bre\b|\breally\b|\breasonably\b|\bregarding\b|\bregardless\b|\bregards\b|\brelatively\b|\brespectively\b|\bright\b|\bs\b|\bsaid\b|\bsame\b|\bsaw\b|\bsay\b|\bsaying\b|\bsays\b|\bsecond\b|\bsecondly\b|\bsee\b|\bseeing\b|\bseem\b|\bseemed\b|\bseeming\b|\bseems\b|\bseen\b|\bself\b|\bselves\b|\bsensible\b|\bsent\b|\bseriously\b|\bseven\b|\bseveral\b|\bshall\b|\bshe\b|\bshould\b|\bshouldn\'t\b|\bsince\b|\bsix\b|\bso\b|\bsome\b|\bsomebody\b|\bsomehow\b|\bsomeone\b|\bsomething\b|\bsometime\b|\bsometimes\b|\bsomewhat\b|\bsomewhere\b|\bsoon\b|\bsorry\b|\bspecified\b|\bspecify\b|\bspecifying\b|\bstill\b|\bsub\b|\bsuch\b|\bsup\b|\bsure\b|\bt\b|\bt\'s\b|\btake\b|\btaken\b|\btell\b|\btends\b|\bth\b|\bthan\b|\bthank\b|\bthanks\b|\bthanx\b|\bthat\b|\bthat\'s\b|\bthats\b|\bthe\b|\btheir\b|\btheirs\b|\bthem\b|\bthemselves\b|\bthen\b|\bthence\b|\bthere\b|\bthere\'s\b|\bthereafter\b|\bthereby\b|\btherefore\b|\btherein\b|\btheres\b|\bthereupon\b|\bthese\b|\bthey\b|\bthey\'d\b|\bthey\'ll\b|\bthey\'re\b|\bthey\'ve\b|\bthink\b|\bthird\b|\bthis\b|\bthorough\b|\bthoroughly\b|\bthose\b|\bthough\b|\bthree\b|\bthrough\b|\bthroughout\b|\bthru\b|\bthus\b|\bto\b|\btogether\b|\btoo\b|\btook\b|\btoward\b|\btowards\b|\btried\b|\btries\b|\btruly\b|\btry\b|\btrying\b|\btwice\b|\btwo\b|\bu\b|\bun\b|\bunder\b|\bunfortunately\b|\bunless\b|\bunlikely\b|\buntil\b|\bunto\b|\bup\b|\bupon\b|\bus\b|\buse\b|\bused\b|\buseful\b|\buses\b|\busing\b|\busually\b|\bv\b|\bvalue\b|\bvarious\b|\bvery\b|\bvia\b|\bviz\b|\bvs\b|\bw\b|\bwant\b|\bwants\b|\bwas\b|\bwasn\'t\b|\bway\b|\bwe\b|\bwe\'d\b|\bwe\'ll\b|\bwe\'re\b|\bwe\'ve\b|\bwelcome\b|\bwell\b|\bwent\b|\bwere\b|\bweren\'t\b|\bwhat\b|\bwhat\'s\b|\bwhatever\b|\bwhen\b|\bwhence\b|\bwhenever\b|\bwhere\b|\bwhere\'s\b|\bwhereafter\b|\bwhereas\b|\bwhereby\b|\bwherein\b|\bwhereupon\b|\bwherever\b|\bwhether\b|\bwhich\b|\bwhile\b|\bwhither\b|\bwho\b|\bwho\'s\b|\bwhoever\b|\bwhole\b|\bwhom\b|\bwhose\b|\bwhy\b|\bwill\b|\bwilling\b|\bwish\b|\bwith\b|\bwithin\b|\bwithout\b|\bwon\'t\b|\bwonder\b|\bwould\b|\bwould\b|\bwouldn\'t\b|\bx\b|\by\b|\byes\b|\byet\b|\byou\b|\byou\'d\b|\byou\'ll\b|\byou\'re\b|\byou\'ve\b|\byour\b|\byours\b|\byourself\b|\byourselves\b|\bz\b|\bzero\b//gsio;
$text =~ s/\bwrote\b|\bmessage\b|\bproblem\b|\bserver\b|\bsql\b|\bdont\b|\bdatabase\b|\bcode\b|\btable\b|\bfile\b|\bcolor\b|\bdata\b|\busenetquotecolor\b|\bwork\b|\baccess\b|\busenetindent\b|\btime\b|\bset\b|\berror\b|\bmake\b|\b2001\b|\bquestion\b|\bcreate\b|\bfind\b|\bquery\b|\bselect\b|\bcheck\b|\bemail\b|\brun\b|\bsystem\b|\buser\b|\bgood\b|\blist\b|\bive\b|\bdoesnt\b|\bfunction\b|\brunning\b|\bnumber\b|\bread\b|\bposting\b|\bworks\b|\bend\b|\bversion\b|\btype\b|\btest\b|\bprogram\b|\b2000\b|\bfiles\b|\bline\b|\bfield\b|\btables\b|\binformation\b|\bcase\b|\btext\b|\bchange\b|\bcommand\b|\barticle\b|\bnews\b|\breturn\b|\bproblems\b|\bwindows\b|\bstring\b|\bopen\b|\bhttp\b|\bsupport\b|\bwrite\b|\bform\b|\badd\b|\bfound\b|\burl\b|\bback\b|\bstart\b|\bapplication\b|\bthing\b|\bscript\b|\boracle\b|\bpost\b|\bpoint\b|\bwrites\b|\breply\b|\bgive\b|\busers\b|\bworking\b|\baddress\b|\bstatement\b|\bmicrosoft\b|\bthread\b|\bput\b|\bgroup\b|\bcall\b|\bwrong\b|\border\b|\bdate\b|\bfine\b|\bsimple\b|\bpeople\b|\bthings\b|\bvalues\b|\byoure\b|\bweb\b//gsio;
$text =~ s/\W/ /gso;
return $text;
}


sub remove_bb_code {
my $text = $_[0];
my ($bbo,$bbc);
my $bbcodes = db_fetch("SELECT bbcodetag FROM bbcode");
while (my $bbcode = $bbcodes->fetchrow_array) {
$bbo=quotemeta("[".$bbcode."]");
$bbc=quotemeta("[/".$bbcode."]");
$text =~ s/$bbo|$bbc//gi; # easy stuff
}
$text =~ s/&quot;|&lt;|&gt;/ /gsio;
$text =~ s/&amp;|<br>|<(\/)?body>|<p>|<(\/)?html>//gsoi;
$text =~ s/\[size=[0-9]+\]|\[\/size\]//ig; # size
$text =~ s/\[color=(\"\#)?[A-Za-z0-9]+(\")?\]|\[\/color\]//ig; # color
$text =~ s/\[url(=)?(")?//ig;
$text =~ s/(\")?\](.+)\[\/url\]/$2/gi;
$text =~ s/\[email(=)?(\")?//ig;
$text =~ s/(\")?\](.+)\[\/email\]/$2/gi;
$text =~ s/\[font=(\"\#)?[A-Za-z]+(\")?\]|\[\/font\]//ig; # font
$text =~ s/\[list(=)?[1Aa]?\]|\[\/list(=)?[1Aa]?\]//ig; # list
$text =~ s/\[\*\]/ - /ig;
$text =~ s/\[(\/)?code\]//ig;
return $text;
}

my MSN: perlchina_at_hotmail.com
Quote Reply
Re: [tsingson] perl code test In reply to
Code:
#!/usr/bin/perl

# Usenet Gateway hack for vBulletin 2.xx
#
# $Id: newnews.pl,v 20020323 2002-03-23 12:22:56-05 paul Exp $
#
# This script creates a gateway to selected usenet newsgroups and maintains
# an historical mirror of articles in your vBulletin forums.
#
# This script is free. You are welcome to do whatever you want with it as
# long as you don't try and sell it or claim it as your own.
# The copyright is retained by the author.
#
# Author:
# fastforward (Paul)
# contact me via the vBulletin forums at http://vbulletin.com
#
#

# uncomment the following line if you installed perl modules to a
# non-standard directory
#use lib "/usr/home/local/lib/perl5/site_perl/5.005";

my $iswin32=0; # set this to 1 if you are running on a win32 platform

my $db="db_name"; # mysql database name
my $db_host="localhost"; # mysql host
my $db_username="db_username"; # mysql username
my $db_password="db_password"; # mysql password


my $pid_file="newnews.pid";





##########################################################################
# No changes necessary below here
#
use strict;
use News::NNTPClient;
use Net::SMTP;
use MIME::WordDecoder;
use Text::Autoformat;
use Mail::Address;
use URI::Find;
use DBI;
use Date::Parse;
use HTML::Entities;


# check for previous instance and terminate if exists
# disabled for win32
unless ($iswin32) {
my $pid=$$;
my $old_pid=0;
if (-e $pid_file) {
open(W,"$pid_file");
$old_pid=<W>;
close(W);
}
open(W,">$pid_file");
print W $pid;
close(W);
my $ps_pid=`/bin/ps xaww | grep newnews.pl | grep -v "grep"`;
$pid=substr($ps_pid,0,5);
if($pid+0 == $old_pid+0){
system("kill -9 $old_pid");
}
}

# declare globals
my (@newsgroups,@spamfilters,@replacements);
my $dbh = db_connect();
my $c;
my $current_server="default";
my (%config,%vbconfig);

# get vB config and usenet settings
get_arrays();

# set default MIME character set
my $wd = supported MIME::WordDecoder "$config{charset}";

# connect to usenet
unless ($config{enable_multiple_news_servers}) {
my $ng = { server => $config{default_nntp_server},
username => $config{default_nntp_username},
password => $config{default_nntp_password}
};
$c = connect_usenet(\$ng);
}

# loop through newsgroups and do the business
foreach my $newsgroup (@newsgroups) {
if ($newsgroup->{enabled}) {
my $batch_end;
if($config{enable_multiple_news_servers}) {
if($newsgroup->{server} ne $current_server) {
$c && $c->quit;
$c = connect_usenet(\$newsgroup);
$current_server = $newsgroup->{server};
}
}
# post any thread starters bound for usenet
post_outgoing_t($c,\$newsgroup);
my ($svrfirstmsg,$svrlastmsg,$tmp) = $c->group($newsgroup->{newsgroup});
if ($newsgroup->{lastmsg} eq 0) {
$newsgroup->{lastmsg} = $svrfirstmsg;
} elsif ($newsgroup->{lastmsg} > $svrlastmsg) {
$newsgroup->{lastmsg} = $svrlastmsg;
}
if ($newsgroup->{lastmsg}+$config{batch_limit}-1 > $svrlastmsg) {
$batch_end = $svrlastmsg
} else {
$batch_end = $newsgroup->{lastmsg}+$config{batch_limit};
}
console("\nPulling $newsgroup->{newsgroup}...");
if ($svrlastmsg > $newsgroup->{lastmsg}) {
# get batch of articles from nntp server
my @articles = get_batch($newsgroup->{lastmsg},$batch_end,$c);
if ($#articles >=0) {
# process the batch
article_batch_process(\@articles,\$newsgroup);
} else {
db_execute("UPDATE usenet_group SET lastmsg = $batch_end WHERE forum = $newsgroup->{forumid}");
}
# load incoming thread starters
article_forum_load_t(\$newsgroup);
console(" -> finding replies... \n");
} else {
console(" No new messages.\n");
}
# load incoming replies
my @updated_threads = article_forum_load_p(\$newsgroup);
my $tc=@updated_threads;
# notify users of replies
if ($tc && $config{email_notify}) {
notify_new_posts(@updated_threads);
}
# post outgoing articles
post_outgoing_p($c,\$newsgroup);
}
}

# disconnect from usenet
unless ($config{enable_multiple_news_servers}) {
$c->quit;
console("Clean disconnection from $config{default_nntp_server}\n");
}

# delete orphans from usenet_article table
article_expire();

# expire old usenet messages from forums
if ($config{auto_purge}) {
article_purge();
}

# clean up and quit
db_execute("TRUNCATE TABLE usenet_article_loader");
$dbh->disconnect;
unlink $pid_file;







########################################################################
# Sub routines from here on

sub get_arrays {

my $qry = db_fetch("SELECT varname, value FROM usenet_setting");
while (my($varname,$value)=$qry->fetchrow_array()) {
$config{$varname} = $value;
}
$qry->finish;

$qry = db_fetch("SELECT varname, value FROM setting");
while (my($varname,$value)=$qry->fetchrow_array()) {
$vbconfig{$varname} = $value;
}
$qry->finish;

$qry = db_fetch("SELECT newsgroup,forum,lastmsg,server,username,password,footer,enabled FROM usenet_group");
while (my($newsgroup,$forumid,$lastmsg,$server,$username,$password,$footer,$enabled) = $qry->fetchrow_array) {
if (!$server) {
$server = $config{default_nntp_server};
$username = $config{default_nntp_username};
$password = $config{default_nntp_password};
}
push (@newsgroups,{newsgroup => $newsgroup,
forumid => $forumid,
lastmsg => $lastmsg,
server => $server,
username => $username,
password => $password,
footer => $footer,
enabled => $enabled});
}
$qry->finish;

$qry = db_fetch("SELECT scope, expression, regex FROM usenet_spam_filter");
while (my ($scope, $expression, $regex) = $qry->fetchrow_array) {
if (!$regex) {
$expression = quotemeta($expression);
}
push (@spamfilters, {scope => $scope, expression => $expression, regex => $regex});
}
$qry->finish;

$qry = db_fetch("SELECT linemode, original, new, regex FROM usenet_replace");
while (my ($linemode, $original, $replace, $regex) = $qry->fetchrow_array) {
if (!$regex) {
$original = quotemeta($original);
}
if ($linemode) {
$original='.*'.$original.'.*';
}
push (@replacements, {original => $original, replace => $replace, regex => $regex, linemode => $linemode});
}
$qry->finish;
}


sub article_batch_process {
my $a = $_[0];
my $newsgroup = $_[1];
my ($lastmsg);
my $cnt = 0;
my $missing = 0;
my @cleanarticles = ();
my @ra = ();
console(" -> processing article batch...\n");
db_execute("begin work");
foreach my $art (@$a) {
my $threadid = 0;
my $postid =0;
$cnt++;
my $postexists = 0;
my $r = $$art{refs};
my $b = $$art{body};
if ($b) {
$$art{body} = join("",@$b);
$$art{body} =~ s/begin\s+666.*\nend\n//gsoi;
$$art{body}=$wd->decode($$art{body});
$$art{body} = article_cleanup($$art{body});
}
if ($$art{refs}) {
$$art{refs} =~ s/,|\s//go; $$art{refs} =~ s/></> </go;
} else {
if ($$art{subject}) {
$$art{subject} =~ s/^\[GENERAL\] //gso;
$$art{subject} =~ s/\</\(/go; $$art{subject} =~ s/\>/\)/go;
$$art{subject} =~ s/^\s+//gso; $$art{subject} =~ s/\s+$//gso;
if ($$art{subject} =~ /^Re:\s+(.*)/o) {
my $qry = db_fetch("SELECT msgid FROM thread WHERE forumid = $$newsgroup->{forumid} AND title = ".$dbh->quote($1)." ORDER BY dateline LIMIT 1");
my $msgid = $qry->fetchrow_array; $qry->finish;
if($msgid) {
$$art{refs} = "$msgid";
}
}
}
}
$lastmsg = $$art{msgnum};
my $forumid = $$newsgroup->{forumid};
my $msgnum = $$art{msgnum};
if (!$$art{subject} || $$art{subject} =~ /^\s+$/o) {
$$art{subject} = "[No Subject]";
}
if ($$art{msgid} && $$art{body} && $$art{msgnum}) {
my $group = $dbh->quote($$newsgroup->{newsgroup});
my $msgid = $dbh->quote($$art{msgid});
my $nntpdtm = $$art{nntpdtm};
if ($nntpdtm > time()+600) {
$nntpdtm = time();
}
$nntpdtm = $dbh->quote($nntpdtm);
my $subject = $dbh->quote($$art{subject});
my $poster = $dbh->quote($$art{poster});
my $email = $dbh->quote($$art{email});
my $refs = $dbh->quote($$art{refs});
my $body = $dbh->quote($$art{body});
my $nntpposter = $dbh->quote(encode_entities($$art{from}));
if ($$art{refs}) {
@ra = split(/\s/,$$art{refs});
my $tmp = "";
foreach my $ref (@ra) {
my $re = $dbh->quote($ref);
$tmp .= "($msgid,$re,$#ra+1,$nntpdtm),";
}
chop($tmp);
db_execute("INSERT IGNORE INTO usenet_ref (msgid,ref,cnt,dtm) VALUES $tmp",1);
}
if ($$art{useragent} && ($$art{useragent} =~ /$config{useragent}/)) {
($threadid,$postid) = split(/\|/,$$art{xref});
my $dbpostexists = db_fetch("SELECT count(*) FROM post WHERE postid = '$postid'");
$postexists = $dbpostexists->fetchrow_array; $dbpostexists->finish;
if ($postexists) {
db_execute("UPDATE post SET msgid = $msgid WHERE postid = $postid");
db_execute("UPDATE usenet_outgoing_log SET msgid = $msgid WHERE postid = $postid");
db_execute("UPDATE usenet_outgoing SET refs = $msgid WHERE threadid = $threadid");
db_execute("UPDATE usenet_outgoing SET refs = concat(refs,' ',$msgid) WHERE inreplyto = $postid");
if (!$$art{refs}) {
db_execute("UPDATE thread SET msgid = $msgid WHERE threadid = $threadid");
db_execute("UPDATE usenet_outgoing SET refs = $msgid WHERE threadid = $threadid");
db_execute("UPDATE usenet_outgoing SET refs = concat(refs,' ',$msgid) WHERE inreplyto = $postid");
}
db_execute("DELETE FROM usenet_article WHERE msgid = $msgid");
db_execute("DELETE FROM usenet_ref WHERE msgid = $msgid");
}
}
unless ($postexists || ($postid && !$config{recover_deleted_localposts})) {
db_execute("INSERT IGNORE INTO usenet_article (newsgroup,forum,msgid,dtm,subject,poster,email,refs,body,msgnum,nntpposter,ord,threadid,postid) VALUES ($group,$forumid,$msgid,$nntpdtm,$subject,$poster,$email,$refs,$body,$msgnum,$nntpposter,$#ra+1,$threadid,$postid)",1);
}
} else {
$missing++;
}
db_execute("UPDATE usenet_group SET lastmsg = $msgnum WHERE forum = $forumid");
}
db_execute("commit");
console(" -> requested $cnt messages... $missing not available or rejected.\n");
}


sub connect_usenet {
my $newsgroup = $_[0];
my ($server, $port);
if ($$newsgroup->{server} =~ /:/) {
my $connectstring = $$newsgroup->{server}; $connectstring =~ /(.*):(.*)/;
$server = $1; $port = $2;
} else {
$server = $$newsgroup->{server}; $port = '119';
}
console("\n\nConnecting to $$newsgroup->{server}... ");
my $con = new News::NNTPClient($server,$port) or die "Unable to connect to $$newsgroup->{server}";
console("Connected\n");
if ($$newsgroup->{username} && $$newsgroup->{password}) {
console("Sending authentication info... ");
$con->authinfo("$$newsgroup->{username}","$$newsgroup->{password}") or die "Authentication failure";
console("Authenticated and logged in\n");
}
return $con;
}


sub get_batch {
my @m = ($_[0],$_[1]);
my $c = $_[2];
my @a=();
my $h;
console("\n -> fetching articles $m[0] to $m[1]... \n");
my $mid = $c->stat($m[0]);
for (my $msg=$m[0]+1;$msg<=$m[1];$msg++) {
undef($h);
my %article=();
if ($h = $c->head($mid)) {
console(" -> fetching $msg... ");
my($f,$s,$d,$i,$r,$u,$xr,$xn,$l,$p,$e,$xa,$b);
foreach (@$h) {
if (/^From: (.*)/) { $f = $wd->decode($1); }
if (/^Subject: (.*)/) { $s = $wd->decode($1); }
if (/^Date: (.*)/) { $d = str2time($1); }
if (/^Message-ID: (.*)/) { $i = $1; }
if (/^References: (.*)/) { $r = $1; }
if (/^User-Agent: (.*)/) { $u = $1; }
if (/^X-References: (.*)/) { $xr = $1; }
if (/^X-Newsreader: (.*)/) { $xn = $1; }
if (/^Lines: (.*)/) { $l = $1; }
if (/^X-No-Archive: (.*)/) { $xa = $1; }
decode_entities($f); decode_entities($s);
}
if (!$d || $d=~/^\s+/) {
$d=time();
}
($p,$e) = parse_from($f);
my $spam =0;
if (($l && $l < $config{max_msg_length}) || (!$l)) {
foreach my $filter (@spamfilters) {
if ($filter->{scope} =~ /^header$|^all$/o) {
if ($config{switched}) {
if ($s =~ /$filter->{expression}/sgi) {
$spam = 1;
}
} else {
if ($s =~ /$filter->{expression}/si) {
$spam = 1;
}
}
if ($config{switched}) {
if ($f =~ /$filter->{expression}/sgi) {
$spam = 1;
}
} else {
if ($f =~ /$filter->{expression}/si) {
$spam = 1; }
}
}
}
if (!$spam) {
if($b = $c->body($mid)) {
push (@a,{ from => $f,
subject => $s,
nntpdtm => $d,
msgid => $i,
refs => $r,
useragent => $u,
xref => $xr,
xreader => $xn,
lines => $l,
poster => $p,
email => $e,
msgnum => $msg,
body => $b
}
);
}
console(" OK\n");
} else {
console(" Not fetched - Spam\n");
}
} else {
console(" Not fetched - Binary post\n");
}
}
$mid = $c->next();
}
return @a;
}


sub article_forum_load_t {
my $newsgroup = $_[0];
my ($littlesubj,$lastpostdtm);
my $tcount = 0;
# load thread starters
console(" -> inserting new threads from $$newsgroup->{newsgroup}\n");
my $q1 = db_fetch("SELECT a.nntpposter, a.forum, a.msgid, a.dtm, a.subject, a.poster, a.email, a.body, a.ord, a.threadid, a.postid FROM usenet_article AS a LEFT OUTER JOIN post AS b on (a.msgid=b.msgid) WHERE a.forum = $$newsgroup->{forumid} AND b.msgid is null AND (refs = '' OR refs is NULL OR refs = '(none)') AND LEFT(subject,3) <> 'Re:' ORDER BY dtm");
while ( my ($nntpposter,$forumid,$msgid,$nntpdtm,$subject,$poster,$email,$body,$ord,$threadid,$postid) = $q1->fetchrow_array ) {
db_execute("begin work");
$tcount++;
if ($body =~ /\w/o) {
$body = autoformat $body, {right=>$config{article_wrap},all=>1};
}
$body = article_add_bbcode($body);
$body = $dbh->quote($body);
if (!$email) {
$email = $config{nntp_from};
}
$email = $dbh->quote($email);
if(length($subject) > 35) {
$littlesubj = substr($subject,0,35)."...";
} else {
$littlesubj = $subject;
}
$littlesubj = $dbh->quote($littlesubj);
$subject = $dbh->quote($subject);
$poster =~ s/^\s+|\s+$//go;
if ($poster eq "") {
$poster = "Anonymous";
}
$poster = $dbh->quote($poster);
$msgid = $dbh->quote($msgid);
if (!$nntpposter) {
$nntpposter = "Unknown";
}
$nntpposter =~ s/<|>/\#/go; $nntpposter = $dbh->quote($nntpposter);
if ($threadid > 0) {
db_execute ("UPDATE thread SET msgid = $msgid WHERE threadid = $threadid");
} else {
if ($config{time_source} eq 'LOCAL') {
$lastpostdtm = time();
} else {
$lastpostdtm = $nntpdtm;
}
db_execute ("INSERT INTO thread (title,lastpost,forumid,postusername,lastposter,dateline,nntpdateline,msgid,visible,open,postuserid,isusenetpost,regpost) VALUES ($subject,$lastpostdtm,$forumid,$poster,$poster,".time().",$nntpdtm,$msgid,'1','1',0,1,0)");
$threadid = $dbh->{'mysql_insertid'};
db_execute("INSERT INTO post (title,allowsmilie,threadid,dateline,nntpdateline,pagetext,visible,msgid,ord,username,userid,nntpfrom,ipaddress,isusenetpost) VALUES ($subject,$config{allowsmilies},$threadid,".time().",$nntpdtm,$body,'1',$msgid,'0',$poster,0,$nntpposter,0,1)");
$postid = $dbh->{'mysql_insertid'};
my $q2 = db_fetch("SELECT lastpost FROM forum WHERE forumid=$forumid");
my $lastpost = $q2->fetchrow_array; $q2->finish;
if (!$lastpost) {
$lastpost = $lastpostdtm;
}
my $forums = db_fetch("SELECT parentlist FROM forum WHERE forumid = $forumid");
my $forumlist = $forums->fetchrow_array;
db_execute("UPDATE forum SET replycount=replycount+1, threadcount=threadcount+1 ".(($lastpostdtm >= $lastpost)?",lastpost=$lastpostdtm,lastposter=$poster,lastactivethread=$littlesubj":"")." WHERE forumid IN ($forumlist)");
$config{index_posts} && indexpost($postid);
}
db_execute("DELETE FROM usenet_article WHERE msgid = $msgid");
db_execute("DELETE FROM usenet_ref WHERE msgid = $msgid");
db_execute("commit");
}
$q1->finish;
console(" -> inserted $tcount threads\n");
}


sub article_forum_load_p {
my $newsgroup = $_[0];
my $t = time();
my $lastpostdtm;
if ($$newsgroup->{forumid} == 114 || $$newsgroup->{forumid} == 156) {
# check for orphan replies
db_execute("begin work");
my $qo = db_fetch("SELECT msgid, subject FROM usenet_article WHERE forum = $$newsgroup->{forumid} AND subject <> '(no subject)' AND LEFT(subject,3) = 'Re:' AND (refs = '' OR refs is NULL OR refs = '(none)')");
while ( my($msgid,$subject) = $qo->fetchrow_array ) {
my $m; my @am;
$msgid = $dbh->quote($msgid);
$subject =~ s/^Re:\s+//io;
my $tq = db_fetch("SELECT msgid FROM thread WHERE forumid=$$newsgroup->{forumid} AND title =".$dbh->quote($subject));
$m="";
while ( my ($tref) = $tq->fetchrow_array) {
$m .= " $tref";
$tref = $dbh->quote($tref);
db_execute("INSERT IGNORE INTO usenet_ref (msgid,ref,dtm) VALUES ($msgid,$tref,$t)");
} $tq->finish;
if ($m ne "") {
$m =~ s/^\s+|\s+$//gso;
@am = split(/ /,$m);
}
$m = $dbh->quote($m);
db_execute("UPDATE usenet_article SET refs = $m, ord = $#am+1 WHERE msgid = $msgid");
}
db_execute("commit");
$qo->finish;
}
# load replies
db_execute("begin work");
db_execute("TRUNCATE TABLE usenet_article_loader");
db_execute("INSERT IGNORE INTO usenet_article_loader SELECT DISTINCT a.newsgroup, a.forum, a.msgid, a.dtm, a.subject, a.poster, a.email, a.nntpposter, a.refs, a.body, a.msgnum, a.ord, a.threadid, a.postid FROM usenet_article AS a left outer join post AS b ON (a.msgid=b.msgid) WHERE b.msgid IS NULL AND a.forum=$$newsgroup->{forumid}");
db_execute("commit");
my $isreplies = 1;
my $showreplies = 1;
my @updated_threads=();
while ($isreplies) {
# if ($showreplies == 1) {
# console("\n -> loading replies...\n"); $showreplies = 0;
# }
my $q3 = db_fetch("SELECT count(distinct(a.msgid)) AS cnt FROM usenet_article_loader AS a, thread AS b, usenet_ref AS c, post AS d where b.threadid = d.threadid and c.ref = d.msgid and a.msgid = c.msgid AND b.forumid = $$newsgroup->{forumid} AND d.ord <= a.ord");
$isreplies = $q3->fetchrow_array; $q3->finish;
$isreplies && console(" -> loading $isreplies replies...\n ");
$q3 = db_fetch("SELECT DISTINCT a.ord, a.dtm, b.title, a.nntpposter, a.forum, a.msgid, a.subject, a.poster, a.body, a.postid, a.email, b.threadid FROM usenet_article_loader AS a, thread AS b, usenet_ref AS c, post AS d where b.threadid = d.threadid and c.ref = d.msgid and a.msgid = c.msgid AND b.forumid = $$newsgroup->{forumid} AND d.ord <= a.ord ORDER BY a.ord, a.dtm");
# $q3 = db_fetch("SELECT DISTINCT a.ord, a.dtm, b.title, a.nntpposter, a.forum, a.msgid, a.subject, a.poster, a.body, a.postid, a.email, b.threadid FROM usenet_article_loader AS a, thread AS b, usenet_ref AS c, post AS d where b.threadid = d.threadid and c.ref = d.msgid and a.msgid = c.msgid AND b.forumid = $$newsgroup->{forumid}");
my $fbody;
while ( my ($ord,$nntpdtm,$title,$nntpposter,$forumid,$msgid,$subject,$poster,$body,$postid,$email,$threadid) = $q3->fetchrow_array ) {
db_execute("begin work");
my $littlesubj;
if ($body =~ /\w/o) {
$fbody = autoformat $body, {right=>$config{article_wrap},all=>1};
}
$fbody = article_add_bbcode($fbody);
$fbody = $dbh->quote($fbody);
if(length($title) > 35) {
$littlesubj = substr($title,0,35)."...";
} else {
$littlesubj = $title;
}
$littlesubj = $dbh->quote($littlesubj);
$subject = $dbh->quote($subject);
if (!$poster) {
$poster = "Anonymous";
}
$poster = $dbh->quote($poster);
if (!$email) {
$email = $config{nntp_from};
}
$email = $dbh->quote($email);
$msgid = $dbh->quote($msgid);
if (!$nntpposter) {
$nntpposter = "Unknown";
}
$nntpposter =~ s/<|>/\#/go; $nntpposter = $dbh->quote($nntpposter);
if ($postid > 0) {
db_execute("UPDATE post SET msgid=$msgid WHERE postid=$postid");
} else {
db_execute ("UPDATE usenet_outgoing_log SET msgid = $msgid WHERE postid = $postid");
db_execute("INSERT INTO post (title,allowsmilie,threadid,dateline,nntpdateline,pagetext,visible,ord,msgid,username,userid,nntpfrom,ipaddress,isusenetpost) VALUES ($subject,$config{allowsmilies},$threadid,".time().",$nntpdtm,$fbody,'1',$ord,$msgid,$poster,0,$nntpposter,0,1)");
$postid = $dbh->{'mysql_insertid'};
}
db_execute("DELETE FROM usenet_article WHERE msgid = $msgid");
db_execute("DELETE FROM usenet_article_loader WHERE msgid = $msgid");
db_execute("DELETE FROM usenet_ref WHERE msgid = $msgid");
my $q4 = db_fetch("SELECT lastpost FROM thread WHERE threadid=$threadid");
my ($lastpost) = $q4->fetchrow_array; $q4->finish;
if ($config{time_source} eq 'LOCAL') {
$lastpostdtm = time();
} else {
$lastpostdtm = $nntpdtm;
}
if (!$lastpost) {
$lastpost = $lastpostdtm;
}
db_execute("UPDATE thread SET replycount = replycount+1 ".(($lastpostdtm >= $lastpost)?",lastpost=$lastpostdtm,lastposter=$poster":"")." WHERE threadid=$threadid");
my $q5 = db_fetch("SELECT lastpost FROM forum WHERE forumid=$forumid");
$lastpost = $q5->fetchrow_array; $q5->finish;
if (!$lastpost) {
$lastpost = $lastpostdtm;
}
my $forums = db_fetch("SELECT parentlist FROM forum WHERE forumid = $forumid");
my $forumlist = $forums->fetchrow_array;
db_execute("UPDATE forum SET replycount=replycount+1 ".(($lastpostdtm >= $lastpost)?",lastpost=$lastpostdtm,lastposter=$poster,lastactivethread=$littlesubj":"")." WHERE forumid IN ($forumlist)");
$config{index_posts} && indexpost($postid);
push(@updated_threads,$threadid);
}
db_execute("commit");
}
console("\n");
return @updated_threads;
}


sub article_expire {
my $expire = time() - ($config{expire_days} * 86400);
db_execute("begin work");
db_execute("DELETE FROM usenet_article WHERE dtm < $expire");
db_execute("DELETE FROM usenet_ref WHERE dtm < $expire");
db_execute("commit");
}


sub article_quote {
my $fbody = $_[0];
my @ibody = ();
my @abody = split(/\n/,$fbody);
foreach my $albody (@abody) {
if ($albody =~ /^>>>|^> > >/o) {
push(@ibody,'[q3]'.$albody."[/q3]\n");
} elsif ($albody =~ /^>>|^> >/o) {
push(@ibody,'[q2]'.$albody."[/q2]\n");
} elsif ($albody =~ /^>|^:|^\|/o) {
push(@ibody,'[q1]'.$albody."[/q1]\n");
} else {
push(@ibody,$albody."\n");
}
}
$fbody = join("",@ibody);
return $fbody;
}


sub cleantext {
my $text = $_[0];
$text =~ s/^\s+//o;
$text =~ s/\s+$//o;
return $text;
}


sub indexpost {
my $id = $_[0];
my ($intitle,$sth);
$sth = db_fetch("SELECT title, pagetext FROM post WHERE postid=$id");
my ($title, $pagetext) = $sth->fetchrow_array;
$sth->finish;
# index title
if ($title) {
my $text = remove_bb_code("$title");
$text = wordsonly("$text");
my @words = split(/\s+/,$text);
my $words_sel="";
my $words_ins="";
my $search_ins="";
foreach my $word (@words) {
if ($word && ((length($word) >= $vbconfig{minsearchlength})) && ((length($word) <= $vbconfig{maxsearchlength}))) {
$word = $dbh->quote($word);
$words_sel .= "$word,";
$words_ins .= "($word),";
}
}
chop $words_sel;
chop $words_ins;
if ($words_ins) {
db_execute("INSERT IGNORE INTO word (title) VALUES $words_ins");
}
if ($words_sel) {
my $wordids = db_fetch("SELECT wordid FROM word WHERE title in ($words_sel)");
while (my $wid = $wordids->fetchrow_array) {
$search_ins .= "($wid,$id,1),";
}
chop $search_ins;
db_execute("INSERT IGNORE INTO searchindex (wordid,postid,intitle) VALUES $search_ins");
}
}

# index post body
$pagetext =~ s/^\[q[1-9]\]>+.*$//go; # remove all quoted stuff
if (length($pagetext) < 10000) {
my $text = remove_bb_code("$pagetext");
$text = wordsonly("$text");
my @words = split(/\s+/,$text);
my $words_sel="";
foreach my $word (@words) {
if ($word && ((length($word) >= $vbconfig{minsearchlength})) && ((length($word) <= $vbconfig{maxsearchlength}))) {
$word = $dbh->quote($word);
$words_sel .= "$word,";
db_execute("INSERT IGNORE INTO word (title) VALUES ($word)");
}
}
chop $words_sel;
if ($words_sel) {
my $wordids = db_fetch("SELECT wordid FROM word WHERE title in ($words_sel)");
while (my $wid = $wordids->fetchrow_array) {
db_execute("INSERT IGNORE INTO searchindex (wordid,postid,intitle) VALUES ($wid,$id,0)");
}
}
} else {
console(" *-> Post $id skipped... (too long)\n");
}
}


sub console {
if ($config{output_to_console}) {
print $_[0];
}
}


sub db_connect {
my $dsn = "DBI:mysql:database=$db;host=$db_host";
my $dbh = DBI->connect( $dsn,
$db_username,
$db_password,
{ RaiseError => 1,
PrintError => 0}
) or die "Cant connect to $db using $db_username\@$db_host";
return $dbh;
}


sub db_fetch {
my $sql = $_[0];
my $qry;
$qry = $dbh->prepare($sql) or die $!;
$qry->execute() or die "Query failed ($sql) $!";
return $qry;
}


sub db_execute {
my $sql = $_[0];
my $ignore_when_fail = $_[1];
eval { $dbh->do($sql) };
if ($@) {
if(defined($ignore_when_fail)) { return 0;
} else {
die "\nQuery failed:\n$sql\n\n$@";
}
}
return 1;
}


sub notify_new_posts {
my $threads = join(',',@_);
my $q1 = db_fetch("SELECT template FROM template WHERE title = 'email_notify'");
my $template = $q1->fetchrow_array; $q1->finish;
$q1 = db_fetch("SELECT template FROM template WHERE title = 'emailsubject_notify'");
my $subjtemplate = $q1->fetchrow_array; $q1->finish;
$q1 = db_fetch("SELECT DISTINCT a.threadid,a.title,a.lastposter,b.title,c.userid,e.email,e.emailnotification,e.username FROM thread AS a, forum AS b, subscribethread AS c, usenet_group AS d, user AS e WHERE a.forumid=b.forumid AND b.forumid=d.forum AND a.threadid=c.threadid AND c.userid=e.userid AND (a.threadid IN($threads))");
while (my ($threadid,$subject,$poster,$forumtitle,$userid,$email,$notify,$username) = $q1->fetchrow_array) {
my $tpl = $template;
my $stpl = $subjtemplate;
if ($notify) {
$tpl =~ s/\$touser\[username\]/$username/g;
$tpl =~ s/\$bbuserinfo\[username\]/$poster/g;
$tpl =~ s/\$threadinfo\[title\]/$subject/g;
$tpl =~ s/\$foruminfo\[title\]/$forumtitle/g;
$tpl =~ s/\$bbtitle/$vbconfig{bbtitle}/g;
$tpl =~ s/\$bburl/$vbconfig{bburl}/g;
$tpl =~ s/\$threadinfo\[threadid\]/$threadid/g;
$stpl =~ s/\$threadinfo\[title\]/$subject/g;
if ($email) {
console("Sending email to $username regarding reply to threadid $threadid\n");
sendmail($email,$tpl,$stpl);
}
}
}
$q1->finish;
}


sub sendmail {
my $smtp;
my $from = $vbconfig{bbtitle}." Mailer <".$vbconfig{webmasteremail}.">";
my ($recipient,$message,$subject) = @_;
$smtp = Net::SMTP->new($config{smtp_server});
$smtp->mail($recipient);
$smtp->to($recipient);
$smtp->data();
$smtp->datasend("Subject: $subject\n");
$smtp->datasend("From: $from\n");
$smtp->datasend("To: $recipient\n");
$smtp->datasend("\n");
$smtp->datasend("$message\n");
$smtp->dataend();
$smtp->quit;
console("email notification sent to $recipient\n");
}


sub article_add_bbcode {
my $text = $_[0];
$text =~ s/^\s+//gio; $text =~ s/\s+$//gio; $text =~ s/\n+$//gio;
$text =~ s/>$//gos; $text =~ s/^\s+//gio; $text =~ s/\s+$//gio;
$text =~ s/\n+$//gio;
$text =~ s/\n\n\n/\n\n/gso; $text =~ s/\n\n\n/\n\n/gso;
if ($config{hyperlinked_uri}) {
find_uris($text, sub { my ($uri, $orig_uri) = @_;
return '[url="'.$uri.'"]'.$orig_uri.'[/url]';
});
}
if ($config{custom_quotes}) {
$text = article_quote($text);
}
return $text;
}


sub article_cleanup {
my $s = $_[0];
foreach my $filter (@spamfilters) {
if ($filter->{scope} =~ /^all$|^body$/o) {
if ($config{switched}) {
if ($s =~ /$filter->{expression}/gsi) {
$s = "";
}
} else {
if ($s =~ /$filter->{expression}/gi) {
$s = "";
}
}
}
}
foreach my $replacement (@replacements) {
if ($replacement->{switched}) {
$s =~ s/$replacement->{original}/$replacement->{replace}/gsi;
} else {
$s =~ s/$replacement->{original}/$replacement->{replace}/gi;
}
}
$s =~ s/^\s+//gso; $s =~ s/\s+$//gso; $s =~ s/^://so; $s =~ s/^\s+//go;
$s =~ s/\s+$//go; $s =~ s/^\n$//go; $s =~ s/^>$//gso; $s =~ s/^:$//go;
$s =~ s/^\:$//go; $s =~ s/\n\n\n/\n\n/go; $s =~ s/>$//gso;
$s =~ s/\s+>$//gso; $s =~ s/^>\s+\n//gso; $s =~ s/^>\n//gso;
$s =~ s/^\s+>\s+\n//gso; $s =~ s/^\s+>\n//gso;
return $s;
}


sub post_outgoing_t {
my $c = $_[0];
my $newsgroup = $_[1];
my ($email,$signature,$fld,$sfld);
my $fldids = db_fetch("SELECT profilefieldid FROM profilefield WHERE title = '$config{'email_profile_id'}'");
my $fldid = $fldids->fetchrow_array;
my $sfldids = db_fetch("SELECT profilefieldid FROM profilefield WHERE title = '$config{'signature_profile_id'}'");
my $sfldid = $sfldids->fetchrow_array;
if($fldid) {
$fld = "field$fldid";
}
if($sfldid) {
$sfld = "field$sfldid";
}
my $threads = db_fetch("SELECT ". ($sfldid ? "e.$sfld AS csig," : "") . ($fldid ? "e.$fld AS cemail," : "") ." b.email, b.showemail, b.signature, a.threadid, a.postusername, a.title, d.newsgroup FROM thread AS a, user AS b, usergroup AS c, usenet_group AS d, userfield AS e WHERE a.visible=1 AND forumid=$$newsgroup->{forumid} AND a.postuserid=b.userid AND b.usergroupid=c.usergroupid AND b.userid=e.userid AND a.isusenetpost=0 AND a.forumid=d.forum");
db_execute("begin work");
while (my $thread = $threads->fetchrow_hashref()) {
my $poster = $dbh->quote($thread->{postusername});
my $subject = $dbh->quote($thread->{title});
my $newsgroup = $dbh->quote($thread->{newsgroup});
if ($config{include_sig}) {
if ($thread->{csig}) {
$signature = $thread->{csig};
} else {
$signature = $thread->{signature};
}
$signature = remove_bb_code($signature);
} else {
$signature = "";
}
if ($config{use_custom_email}) {
if($thread->{cemail}) {
$email = $thread->{cemail};
} else {
$email = $config{nntp_from};
}
} elsif ($config{allow_user_email_address} && $thread->{showmail} == 1) {
$email = $config{email};
} else {
$email = $config{nntp_from};
}
$email = $dbh->quote($email);
$signature = $dbh->quote($signature);
my $posts = db_fetch("SELECT showsignature, postid, pagetext FROM post WHERE threadid=$thread->{threadid} ORDER BY postid LIMIT 1");
my $post = $posts->fetchrow_hashref();
if (!$post->{showsignature}) {
$signature = "''";
}
my $body = $post->{pagetext};
if($config{spam_check_outgoing}) {
$body=spam_check($body);
}
if($config{replace_on_outgoing}) {
$body=replace_filters($body);
}
unless ($body eq "") {
$body = $dbh->quote($body);
db_execute("INSERT IGNORE INTO usenet_outgoing(poster,email,signature,newsgroup,subject,body,threadid,postid) VALUES ($poster,$email,$signature,$newsgroup,$subject,$body,$thread->{'threadid'},$post->{'postid'})");
}
db_execute("UPDATE thread SET isusenetpost = -1, nntpdateline=dateline WHERE threadid = '$thread->{threadid}'");
db_execute("UPDATE post SET isusenetpost = -1, nntpdateline=dateline WHERE postid = '$post->{postid}'");
$posts->finish;
}
$threads->finish;
db_execute("commit");
post_outgoing($c,$newsgroup);
}


sub post_outgoing_p {
my $c = $_[0];
my $newsgroup = $_[1];
my ($email,$signature,$fld,$sfld);
my $fldids = db_fetch("SELECT profilefieldid FROM profilefield WHERE title = '$config{'email_profile_id'}'");
my $fldid = $fldids->fetchrow_array;
my $sfldids = db_fetch("SELECT profilefieldid FROM profilefield WHERE title = '$config{'signature_profile_id'}'");
my $sfldid = $sfldids->fetchrow_array;
if ($fldid) {
$fld = "field$fldid";
}
if($sfldid) {
$sfld = "field$sfldid";
}
my $posts = db_fetch("SELECT ".($sfldid ? "f.$sfld AS csig," : "") . ($fldid ? "f.$fld AS cemail," : "")." b.email, b.showemail, b.signature, a.postid, a.threadid, a.inreplyto, b.username, e.newsgroup, d.title, a.pagetext FROM post AS a, user AS b, usergroup AS c, thread AS d, usenet_group AS e, userfield AS f WHERE a.visible=1 AND a.userid=b.userid AND b.usergroupid=c.usergroupid AND a.threadid=d.threadid AND d.forumid=e.forum AND b.userid=f.userid AND e.forum=$$newsgroup->{'forumid'} AND a.isusenetpost=0");
db_execute("begin work");
while (my $post = $posts->fetchrow_hashref()) {
my $poster = $dbh->quote($post->{'username'});
my $newsgroup = $dbh->quote($post->{'newsgroup'});
my $subject = $dbh->quote("Re: ".$post->{'title'});
my $body = $post->{'pagetext'};
if ($config{include_sig}) {
if ($post->{csig}) {
$signature = $post->{csig};
} else {
$signature = $post->{signature};
}
$signature = remove_bb_code($signature);
} else {
$signature = "";
}
if ($config{use_custom_email}) {
if($post->{'cemail'}) {
$email = $post->{'cemail'};
} else {
$email = $config{nntp_from};
}
} elsif ($config{allow_user_email_address} && $post->{'showmail'} == 1) {
$email = $config{email};
} else {
$email = $config{nntp_from};
}
$email = $dbh->quote($email);
$signature = $dbh->quote($signature);
if($config{spam_check_outgoing}) {
$body=spam_check($body);
}
if($config{replace_on_outgoing}) {
$body=replace_filters($body);
}
my $threads = db_fetch("SELECT msgid,title FROM thread WHERE threadid = $post->{'threadid'}");
my $thread = $threads->fetchrow_hashref();
my $inreplys = db_fetch("SELECT msgid FROM post WHERE postid=$post->{inreplyto}");
my $replytomsgid = $inreplys->fetchrow_array;
my $refs = $dbh->quote("$thread->{msgid} $replytomsgid");
unless ($body eq "") {
$body = $dbh->quote($body);
db_execute("INSERT IGNORE INTO usenet_outgoing(poster,email,signature,newsgroup,subject,refs,body,threadid,postid,inreplyto) VALUES ($poster,$email,$signature,$newsgroup,$subject,$refs,$body,$post->{'threadid'},$post->{'postid'},$post->{inreplyto})");
}
db_execute("UPDATE post SET title=".$dbh->quote("Re: $thread->{title}").", isusenetpost = -1, nntpdateline=dateline WHERE postid = $post->{postid}");
}
db_execute("commit");
$posts->finish;
post_outgoing($c,$newsgroup);
}


sub post_outgoing {
my $c = $_[0];
my $newsgroup = $_[1];
my @article=();
my (@r,$fbody,$fquote,$qp);
my $q2 = db_fetch("SELECT poster,email,signature,newsgroup,subject,refs,body,threadid,postid,inreplyto FROM usenet_outgoing WHERE (refs <> ' ' OR subject NOT LIKE 'Re: %') AND newsgroup = ". $dbh->quote($$newsgroup->{'newsgroup'}));
while ( my ($poster,$email,$signature,$group,$subject,$refs,$body,$threadid,$postid,$inreplyto) = $q2->fetchrow_array ) {
my $quote = $body;
$quote =~ m/\[QUOTE\](.*)\[\/QUOTE\]/iso; $quote = $1;
if ($quote) {
my $regex = quotemeta($quote);
$body =~ s/$regex//s;
$quote =~ m/Originally posted by (.*)\[\/i\]/so; $qp = $1;
$quote =~ s/\[i\]Originally posted by.*\[\/i\]//iso;
$quote = remove_bb_code($quote);
}
if ($refs) {
@r = split(/\s/,$refs);
}
my $re = "";
foreach my $ref (@r) {
if ($ref =~ /^</o) {$re = $re . " " . $ref;}
}
push(@article,"Subject: $subject\n");
push(@article,"From: $poster <$email>\n");
push(@article,"Sender: $poster\n");
push(@article,"Newsgroups: $group\n");
push(@article,"User-Agent: $config{useragent}\n");
if ($re) {
push(@article,"References: $re\n");
}
push(@article,"Organization: $config{useragent}\n");
push(@article,"X-Newsreader: $config{useragent}\n");
push(@article,"X-References: $threadid|$postid\n");
$body = remove_bb_code($body);
if ($body =~ /\w/o) {
$fbody = autoformat $body, {right=>72,all=>1};
}
if ($quote) {
$fquote = autoformat $quote, {right=>72,all=>1};
$fquote =~ s/^\n\s+$|^\n$|^>$|^>\s+$|^\s+>\s+$//gos;
my @quotes = split(/\n/,$fquote);
push(@article,"\n");
push(@article,"$qp wrote:\n");
foreach my $q (@quotes) {
if ($q) {
push(@article," > $q\n");
}
}
}
push(@article,"\n$fbody\n");
if (!$$newsgroup->{footer}) {
$$newsgroup->{footer} = $config{outgoing_post_footer};
}
if ($signature) {
$$newsgroup->{footer} = "$signature\n\n".$$newsgroup->{footer};
}
$$newsgroup->{footer} =~ s/\\n/\n/go;
push(@article,"\n\n--\n".$$newsgroup->{footer}."\n");
if ($config{postingenabled}) {
console("Posting message by $poster to $group...");
if ($poster && $body) {
$c->post(@article);
console(" Sent\n");
}
}
db_execute("DELETE FROM usenet_outgoing WHERE postid = $postid");
$poster = $dbh->quote($poster);
$group = $dbh->quote($group);
$subject = $dbh->quote($subject);
$refs = $dbh->quote($refs);
$body = $dbh->quote($body);
$signature = $dbh->quote($signature);
$email = $dbh->quote($email);
my $dtm = time();
db_execute("INSERT INTO usenet_outgoing_log (dtm,poster,email,signature,newsgroup,subject,refs,body,threadid,postid,inreplyto) VALUES ($dtm,$poster,$email,$signature,$group,$subject,$refs,$body,$threadid,$postid,$inreplyto)");
@article=(); @r=();
} $q2->finish;
}


sub spam_check {
my $s = $_[0];
foreach my $filter (@spamfilters) {
if ($filter->{scope} =~ /^all$|^body$/o) {
if ($config{switched}) {
if ($s =~ /$filter->{expression}/gsi) {
$s = "";
}
} else {
if ($s =~ /$filter->{expression}/gi) {
$s = "";
}
}
}
}
return $s;
}


sub replace_filters {
my $s = $_[0];
foreach my $replacement (@replacements) {
if ($replacement->{switched}) {
$s =~ s/$replacement->{original}/$replacement->{replace}/gsi;
} else {
$s =~ s/$replacement->{original}/$replacement->{replace}/gi;
}
}
return $s;
}


sub article_purge {
console("\nExpiring usenet articles older than $config{auto_purge_days} days...\n");
my @post_batch=();
my @thread_batch=();
my ($pin_clause, $tin_clause);
db_execute("begin work");
my $forums = db_fetch("SELECT forum, newsgroup FROM usenet_group");
while (my($forumid,$newsgroup) = $forums->fetchrow_array) {
my $delt =0; my $delp =0;
my $purge = time() - ($config{auto_purge_days} * 86400);
console(" -> $newsgroup...");
my $threads = db_fetch("SELECT threadid FROM thread WHERE forumid=$forumid AND isusenetpost <> 0 AND dateline < $purge".(!$config{auto_purge_reg}?" AND regpost = 0":""));
while (my($threadid) = $threads->fetchrow_array) {
$delt++;
my $posts = db_fetch("SELECT postid FROM post WHERE threadid = $threadid");
while (my($postid) = $posts->fetchrow_array) {
db_execute("delete from searchindex where postid = $postid");
$delp++;
# push(@post_batch, $postid);
# if($#post_batch > 1000) {
# $pin_clause = join(",",@post_batch);
# db_execute("DELETE FROM searchindex WHERE postid IN ($pin_clause)");
# @post_batch=(); $pin_clause="";
# }
}
db_execute("DELETE FROM post where threadid = $threadid");
$posts->finish;
db_execute("DELETE FROM thread where threadid = $threadid");
}
$threads->finish;
# db_execute("DELETE FROM thread WHERE forumid = $forumid AND lastpost < $purge".(!$config{auto_purge_reg}?" AND regpost = 0":""));
db_execute("UPDATE forum SET threadcount=threadcount-$delt, replycount=replycount-$delp WHERE forumid=$forumid");
db_execute("commit");
console(" OK\n");
}
$forums->finish;
if(@post_batch) {
$pin_clause = join(",",@post_batch);
db_execute("DELETE FROM searchindex WHERE postid IN ($pin_clause)");
}
if(@thread_batch) {
$tin_clause = join(",",@thread_batch);
db_execute("DELETE FROM post WHERE threadid IN ($tin_clause)");
}
db_execute("commit");
console("Expire completed.\n");
}


sub parse_from {
my $from = $_[0];
my ($email,$name,$qe,$epref);
if (defined($from)) {
$from =~ s/^\s+//gos; $from =~ s/\s+$//gos;
foreach my $replacement (@replacements) {
if ($replacement->{switched}) {
$from =~ s/$replacement->{original}/$replacement->{replace}/gsi;
} else {
$from =~ s/$replacement->{original}/$replacement->{replace}/gi;
}
}
$from =~ s/\[/-lsq/g; $from =~ s/\[/-rsq/g;
if (my @mail_address = Mail::Address->parse($from)) {
$email = $mail_address[0]->address();
$from = $mail_address[0]->name();
if (!$from){
$from = $mail_address[0]->user();
}
$from =~ s/-rsq/[/g; $from =~ s/-lsq/]/g;
$from = substr($from,0,$vbconfig{maxuserlength});
}
} else {
$from = "Anonymous";
}
return ($from,$email);
}

sub wordsonly {
my $text = lc($_[0]);
$text =~ s/\%20/ /gso;
$text =~ s/(__)+//g;
$text =~ s/\bagree\b|\bcuz\b|\bcoz\b|\bhtmlwww\b|\btesttesttest\b|\btesttest\b|\bwww\b|\bcom\b|\bheh\b|\bsupposed\b|\bsupposed\b|\bdont\b|\badvice\b|\boccur\b|\boccurs\b|\bguys|\b|\byep\b|\byes\b|\byeah\b|\busenetquotecolor\b|\bthings\b|\bsolved\b|\bquestion\b|\bthinks\b|\bthink\b|\bthought\b|\bhappens\b|\bhappen\b|\bnope\b|\bheard\b|\bbtw\b|\biirc\b|\bafair\b|\bhth\b|\bregards\b|\bcould\b|\bplenty\b|\ba\b|\ba\'s\b|\bable\b|\babout\b|\babove\b|\baccording\b|\baccordingly\b|\bacross\b|\bactually\b|\bafter\b|\bafterwards\b|\bagain\b|\bagainst\b|\bain\'t\b|\ball\b|\ballow\b|\ballows\b|\balmost\b|\balone\b|\balong\b|\balready\b|\balso\b|\balthough\b|\balways\b|\bam\b|\bamong\b|\bamongst\b|\ban\b|\band\b|\banother\b|\bany\b|\banybody\b|\banyhow\b|\banyone\b|\banything\b|\banyway\b|\banyways\b|\banywhere\b|\bapart\b|\bappear\b|\bappreciate\b|\bappropriate\b|\bare\b|\baren\'t\b|\baround\b|\bas\b|\baside\b|\bask\b|\basking\b|\bassociated\b|\bat\b|\bavailable\b|\baway\b|\bawfully\b|\bb\b|\bbe\b|\bbecame\b|\bbecause\b|\bbecome\b|\bbecomes\b|\bbecoming\b|\bbeen\b|\bbefore\b|\bbeforehand\b|\bbehind\b|\bbeing\b|\bbelieve\b|\bbelow\b|\bbeside\b|\bbesides\b|\bbest\b|\bbetter\b|\bbetween\b|\bbeyond\b|\bboth\b|\bbrief\b|\bbut\b|\bby\b|\bc\b|\bc\'mon\b|\bc\'s\b|\bcame\b|\bcan\b|\bcan\'t\b|\bcannot\b|\bcant\b|\bcause\b|\bcauses\b|\bcertain\b|\bcertainly\b|\bchanges\b|\bclearly\b|\bco\b|\bcom\b|\bcome\b|\bcomes\b|\bconcerning\b|\bconsequently\b|\bconsider\b|\bconsidering\b|\bcontain\b|\bcontaining\b|\bcontains\b|\bcorresponding\b\bcould\b|\bcouldn\'t\b|\bcourse\b|\bcurrently\b|\bd\b|\bdefinitely\b|\bdescribed\b|\bdespite\b|\bdid\b|\bdidn\'t\b|\bdifferent\b|\bdo\b|\bdoes\b|\bdoesn\'t\b|\bdoing\b|\bdon\'t\b|\bdone\b|\bdown\b|\bdownwards\b|\bduring\b|\be\b|\beach\b|\bedu\b|\beg\b|\beight\b|\beither\b|\belse\b|\belsewhere\b|\benough\b|\bentirely\b|\bespecially\b|\bet\b|\betc\b|\beven\b|\bever\b|\bevery\b|\beverybody\b|\beveryone\b|\beverything\b|\beverywhere\b|\bex\b|\bexactly\b|\bexample\b|\bexcept\b|\bf\b|\bfar\b|\bfew\b|\bfifth\b|\bfirst\b|\bfive\b|\bfollowed\b|\bfollowing\b|\bfollows\b|\bfor\b|\bformer\b|\bformerly\b|\bforth\b|\bfour\b|\bfrom\b|\bfurther\b|\bfurthermore\b|\bg\b|\bget\b|\bgets\b|\bgetting\b|\bgiven\b|\bgives\b|\bgo\b|\bgoes\b|\bgoing\b|\bgone\b|\bgot\b|\bgotten\b|\bgreetings\b|\bh\b|\bhad\b|\bhadn\'t\b|\bhappens\b|\bhardly\b|\bhas\b|\bhasn\'t\b|\bhave\b|\bhaven\'t\b|\bhaving\b|\bhe\b|\bhe\'s\b|\bhello\b|\bhelp\b|\bhence\b|\bher\b|\bhere\b|\bhere\'s\b|\bhereafter\b|\bhereby\b|\bherein\b|\bhereupon\b|\bhers\b|\bherself\b|\bhi\b|\bhim\b|\bhimself\b|\bhis\b|\bhither\b|\bhopefully\b|\bhow\b|\bhowbeit\b|\bhowever\b|\bi\b|\bi\'d\b|\bi\'ll\b|\bi\'m\b|\bi\'ve\b|\bie\b|\bif\b|\bignored\b|\bimmediate\b|\bin\b|\binasmuch\b|\binc\b|\bindeed\b|\bindicate\b|\bindicated\b|\bindicates\b|\binner\b|\binsofar\b|\binstead\b|\binto\b|\binward\b|\bis\b|\bisn\'t\b|\bit\b|\bit\'d\b|\bit\'ll\b|\bit\'s\b|\bits\b|\bitself\b|\bj\b|\bjust\b|\bk\b|\bkeep\b|\bkeeps\b|\bkept\b|\bknow\b|\bknows\b|\bknown\b|\bl\b|\blast\b|\blately\b|\blater\b|\blatter\b|\blatterly\b|\bleast\b|\bless\b|\blest\b|\blet\b|\blet\'s\b|\blike\b|\bliked\b|\blikely\b|\blittle\b|\blook\b|\blooking\b|\blooks\b|\bltd\b|\bm\b|\bmainly\b|\bmany\b|\bmay\b|\bmaybe\b|\bme\b|\bmean\b|\bmeanwhile\b|\bmerely\b|\bmight\b|\bmore\b|\bmoreover\b|\bmost\b|\bmostly\b|\bmuch\b|\bmust\b|\bmy\b|\bmyself\b|\bn\b|\bname\b|\bnamely\b|\bnd\b|\bnear\b|\bnearly\b|\bnecessary\b|\bneed\b|\bneeds\b|\bneither\b|\bnever\b|\bnevertheless\b|\bnew\b|\bnext\b|\bnine\b|\bno\b|\bnobody\b|\bnon\b|\bnone\b|\bnoone\b|\bnor\b|\bnormally\b|\bnot\b|\bnothing\b|\bnovel\b|\bnow\b|\bnowhere\b|\bo\b|\bobviously\b|\bof\b|\boff\b|\boften\b|\boh\b|\bok\b|\bokay\b|\bold\b|\bon\b|\bonce\b|\bone\b|\bones\b|\bonly\b|\bonto\b|\bor\b|\bother\b|\bothers\b|\botherwise\b|\bought\b|\bour\b|\bours\b|\bourselves\b|\bout\b|\boutside\b|\bover\b|\boverall\b|\bown\b|\bp\b|\bparticular\b|\bparticularly\b|\bper\b|\bperhaps\b|\bplaced\b|\bplease\b|\bplus\b|\bpossible\b|\bpresumably\b|\bprobably\b|\bprovides\b|\bq\b|\bque\b|\bquite\b|\bqv\b|\br\b|\brather\b|\brd\b|\bre\b|\breally\b|\breasonably\b|\bregarding\b|\bregardless\b|\bregards\b|\brelatively\b|\brespectively\b|\bright\b|\bs\b|\bsaid\b|\bsame\b|\bsaw\b|\bsay\b|\bsaying\b|\bsays\b|\bsecond\b|\bsecondly\b|\bsee\b|\bseeing\b|\bseem\b|\bseemed\b|\bseeming\b|\bseems\b|\bseen\b|\bself\b|\bselves\b|\bsensible\b|\bsent\b|\bseriously\b|\bseven\b|\bseveral\b|\bshall\b|\bshe\b|\bshould\b|\bshouldn\'t\b|\bsince\b|\bsix\b|\bso\b|\bsome\b|\bsomebody\b|\bsomehow\b|\bsomeone\b|\bsomething\b|\bsometime\b|\bsometimes\b|\bsomewhat\b|\bsomewhere\b|\bsoon\b|\bsorry\b|\bspecified\b|\bspecify\b|\bspecifying\b|\bstill\b|\bsub\b|\bsuch\b|\bsup\b|\bsure\b|\bt\b|\bt\'s\b|\btake\b|\btaken\b|\btell\b|\btends\b|\bth\b|\bthan\b|\bthank\b|\bthanks\b|\bthanx\b|\bthat\b|\bthat\'s\b|\bthats\b|\bthe\b|\btheir\b|\btheirs\b|\bthem\b|\bthemselves\b|\bthen\b|\bthence\b|\bthere\b|\bthere\'s\b|\bthereafter\b|\bthereby\b|\btherefore\b|\btherein\b|\btheres\b|\bthereupon\b|\bthese\b|\bthey\b|\bthey\'d\b|\bthey\'ll\b|\bthey\'re\b|\bthey\'ve\b|\bthink\b|\bthird\b|\bthis\b|\bthorough\b|\bthoroughly\b|\bthose\b|\bthough\b|\bthree\b|\bthrough\b|\bthroughout\b|\bthru\b|\bthus\b|\bto\b|\btogether\b|\btoo\b|\btook\b|\btoward\b|\btowards\b|\btried\b|\btries\b|\btruly\b|\btry\b|\btrying\b|\btwice\b|\btwo\b|\bu\b|\bun\b|\bunder\b|\bunfortunately\b|\bunless\b|\bunlikely\b|\buntil\b|\bunto\b|\bup\b|\bupon\b|\bus\b|\buse\b|\bused\b|\buseful\b|\buses\b|\busing\b|\busually\b|\bv\b|\bvalue\b|\bvarious\b|\bvery\b|\bvia\b|\bviz\b|\bvs\b|\bw\b|\bwant\b|\bwants\b|\bwas\b|\bwasn\'t\b|\bway\b|\bwe\b|\bwe\'d\b|\bwe\'ll\b|\bwe\'re\b|\bwe\'ve\b|\bwelcome\b|\bwell\b|\bwent\b|\bwere\b|\bweren\'t\b|\bwhat\b|\bwhat\'s\b|\bwhatever\b|\bwhen\b|\bwhence\b|\bwhenever\b|\bwhere\b|\bwhere\'s\b|\bwhereafter\b|\bwhereas\b|\bwhereby\b|\bwherein\b|\bwhereupon\b|\bwherever\b|\bwhether\b|\bwhich\b|\bwhile\b|\bwhither\b|\bwho\b|\bwho\'s\b|\bwhoever\b|\bwhole\b|\bwhom\b|\bwhose\b|\bwhy\b|\bwill\b|\bwilling\b|\bwish\b|\bwith\b|\bwithin\b|\bwithout\b|\bwon\'t\b|\bwonder\b|\bwould\b|\bwould\b|\bwouldn\'t\b|\bx\b|\by\b|\byes\b|\byet\b|\byou\b|\byou\'d\b|\byou\'ll\b|\byou\'re\b|\byou\'ve\b|\byour\b|\byours\b|\byourself\b|\byourselves\b|\bz\b|\bzero\b//gsio;
$text =~ s/\bwrote\b|\bmessage\b|\bproblem\b|\bserver\b|\bsql\b|\bdont\b|\bdatabase\b|\bcode\b|\btable\b|\bfile\b|\bcolor\b|\bdata\b|\busenetquotecolor\b|\bwork\b|\baccess\b|\busenetindent\b|\btime\b|\bset\b|\berror\b|\bmake\b|\b2001\b|\bquestion\b|\bcreate\b|\bfind\b|\bquery\b|\bselect\b|\bcheck\b|\bemail\b|\brun\b|\bsystem\b|\buser\b|\bgood\b|\blist\b|\bive\b|\bdoesnt\b|\bfunction\b|\brunning\b|\bnumber\b|\bread\b|\bposting\b|\bworks\b|\bend\b|\bversion\b|\btype\b|\btest\b|\bprogram\b|\b2000\b|\bfiles\b|\bline\b|\bfield\b|\btables\b|\binformation\b|\bcase\b|\btext\b|\bchange\b|\bcommand\b|\barticle\b|\bnews\b|\breturn\b|\bproblems\b|\bwindows\b|\bstring\b|\bopen\b|\bhttp\b|\bsupport\b|\bwrite\b|\bform\b|\badd\b|\bfound\b|\burl\b|\bback\b|\bstart\b|\bapplication\b|\bthing\b|\bscript\b|\boracle\b|\bpost\b|\bpoint\b|\bwrites\b|\breply\b|\bgive\b|\busers\b|\bworking\b|\baddress\b|\bstatement\b|\bmicrosoft\b|\bthread\b|\bput\b|\bgroup\b|\bcall\b|\bwrong\b|\border\b|\bdate\b|\bfine\b|\bsimple\b|\bpeople\b|\bthings\b|\bvalues\b|\byoure\b|\bweb\b//gsio;
$text =~ s/\W/ /gso;
return $text;
}


sub remove_bb_code {
my $text = $_[0];
my ($bbo,$bbc);
my $bbcodes = db_fetch("SELECT bbcodetag FROM bbcode");
while (my $bbcode = $bbcodes->fetchrow_array) {
$bbo=quotemeta("[".$bbcode."]");
$bbc=quotemeta("[/".$bbcode."]");
$text =~ s/$bbo|$bbc//gi; # easy stuff
}
$text =~ s/&quot;|&lt;|&gt;/ /gsio;
$text =~ s/&amp;|<br>|<(\/)?body>|<p>|<(\/)?html>//gsoi;
$text =~ s/\[size=[0-9]+\]|\[\/size\]//ig; # size
$text =~ s/\[color=(\"\#)?[A-Za-z0-9]+(\")?\]|\[\/color\]//ig; # color
$text =~ s/\[url(=)?(")?//ig;
$text =~ s/(\")?\](.+)\[\/url\]/$2/gi;
$text =~ s/\[email(=)?(\")?//ig;
$text =~ s/(\")?\](.+)\[\/email\]/$2/gi;
$text =~ s/\[font=(\"\#)?[A-Za-z]+(\")?\]|\[\/font\]//ig; # font
$text =~ s/\[list(=)?[1Aa]?\]|\[\/list(=)?[1Aa]?\]//ig; # list
$text =~ s/\[\*\]/ - /ig;
$text =~ s/\[(\/)?code\]//ig;
return $text;
}

my MSN: perlchina_at_hotmail.com
Quote Reply
Re: [tsingson] perl code test In reply to
I am what you call fun and cool.Tongue
Quote Reply
Re: [Anonymous] perl code test In reply to
In Reply To:
I am what you call fun and cool.Tongue


<a href=mailto:bobba@fett.com>Test</a>
Quote Reply
Re: [Anonymous] perl code test In reply to
In Reply To:
In Reply To:
I am what you call fun and cool.Tongue


<a href=mailto:bobba@fett.com>Test</a>

test