Yeah newsfeeds.com definately works. I tested it with that.

Well as you can see, the messages are being inserted into the thread table correctly but the threadid is not being returned by the mysql_insertid function. I'm not sure why this is happening. Maybe it's something to do with your mysql setup, I have no idea. But if it happened to you, it will probably happen for others.

I have added another function in there that checks if the threadid is null and if it is, look it up again using the message id. So it will try to use the mysl_insert_id first and if that fails, it will find it itself.

Try that and see what happens. In the meantime I'm going to see what information I can find out about this function at mysql.com.

Hey, it looks like THAT WORKED!!!!!

Can ya believe it?!?!??!

Kind of a weird thing happened on the first import...the forums showed 'xx threads and xx posts', but if you clicked on the forum, the display was empty. Running the script the second time seemed to work!!!!!!!!!!

I'm going to play around a bit more just to make sure, but thank you!!!!! I'll let you know how it goes tomorrow.

Here was another one this morning...I've added a total of 8 newsgroups. The first few are processed fine, but this one keeps bombing out:

Fetching article body 48392... OK
Processing article batch...
Requested 44 messages... 0 not available or rejected.

DBD::mysql::db do failed: You have an error in your SQL syntax near ')' at line
1 at ./newnews.pl line 444.
Query failed (INSERT INTO usenet_ref (msgid,ref,cnt,dtm) VALUES ('<09020102.3649
@hotmail.com>','(none)',0+1,)) at ./newnews.pl line 444.

???? Looks like a new one.

One the plus side, my posts make it to the newsgroups just fine!

You seem to be hitting all those unusual situations that should never happen! :D This problem is due the xhdr function not being able to determine the date from the message header. I have added a fix for this.

The other thing you uncovered was the '(none)' in the refs column. This means the message had no references and is a thread starter so there is no need to put it in the refs table as it is already in the thread table. It wasn't doing any harm but it was wasting space.

regards
PAJ
--
the latest version of this hack is always in the post of this thread

Just call me troublemaker ;)

I guess it's good and bad. Helps make your hack compatible with more systems, bad in the fact that I've put you through a bunch of hassle over the past couple of days.

I've installed a second test copy of vbulletin that I'm testing this hack with, and have previewed it to the other forum moderators on our site. They are impressed! Once we get the bugs worked out, it'll be great to roll this out to our users (many of which know nothing about newsgroups!)

I'll give the changes a whirl, and let you know how it goes.

Thanks again for your speedy reply!

When I run the script now, it stops after pulling the first group as follows:

Connecting to News.newsfeeds.com... Connected
Sending authentication info... Authenticated and logged in
Getting article batch from alt.test
Fetching headers of articles 849566 to 849665... done

It doesn't try to parse the articles. I don't think it's traffic related, as this happens each time I try it. I have to control-c to abort the script.

Sorry to be a pain...

I've seen this problem occasionally. It has to do with strange characters in the NNTPFrom header field. Once the batch is downloaded part of the processing is to parse this 'from' header to extract the email and real name. If there is something it doesn't like in here it can take forever to process. I've seen it take 10 minutes before. It does come back eventually though. This is very rare but I will try to determine exactly which characters it has trouble with.

Which newsgroup is it stopping on. I'll try the same one for testing. Try just letting it run for a while to see if it frees up.

regards

It's hanging on alt.test I bumped the article number up in the table to bypass the message, and it got much further along (I think it got to the 5th newsgroup), then bombed again:

DBD::mysql::db do failed: You have an error in your SQL syntax near ''wow $10 fo
r each referals and get paid for reading emails make money','skidrow' at line 1
at ./newnews.pl line 448.
Query failed (INSERT IGNORE INTO usenet_article (newsgroup,forum,msgid,dtm,subje
ct,poster,email,refs,body,msgnum,nntpposter,ord,th readid,postid) VALUES ('alt.fi
shing.catfish',8,'<09020102.3650@hotmail.com>',,'w ow $10 for each referals and
get paid for reading emails make money','skidrow1','skidrow1@hotmail.com','(none
)','hay guys check this out this is so cool u can make so much money just by ref
erals and reading emails its owsome its easy money very easy money all u have to
do is take out 5 mins daily and start makeing money \n\n\nhttp://www.inboxcash.
com/$10/referral.asp?id=521016',6731,'skidrow1@hotmail.com ',0+1,0,0)) at ./newn
ews.pl line 448.

Once again, got around this by bumping up the article number in the table. Clearly there is a character problem somewhere that the script doesn't like.

That SQL error is caused by not finding the date in the header again. There are two possible places for the date in the header, one is the 'Date' field and the other is the 'NNTPPostingDate'. Which ones it fills in is dependent on the news server and the posting client.

When I was using newsfeeds.com to test this script I was parsing the header myself to find any one of these. However, I rewrote that part to use the xhdr function as it's more reliable. Since the the rewite I haven't tested it on newsfeeds as I no longer have an account there. So it seems that newsfeeds is not consistent in which header field is used for the date.

I will add more logic to try date first (the client posting date), then if that's not there, use the NNTPpostingDate (the server date).

I'll have the fix done in an hour or so.

OK... here ya go.

I've tested this on around 600-700 messages from alt.test using onlynews.com and also a DNEWS server.

You will get a lot of crap from alt.test though as everyone is testing similar stuff to this. There'll also be a lot of binary and mime stuff in there.

let me know how you get on.

Bingo, that did it! Back in business. Got right around the post that was causing the crash. I've setup cron to pull every 30 minutes, so I'll give it a couple of days to see if it trips up any more.

Thank you again so much. If I can send you a contribution for your troubles, drop me an email!

Three days now without an error!!!!! Very well done!!!

I think there may be a problem with the email notification of new replies, but I'm still looking into that.

Cheers!

I think the email reply routine makes use of the mysql_insert_id function that you were having problems with before. I'll have a look and confirm it. If that's the case, it makes sense that you're having problems and I'll come up with a solution for you.

I've also had a play with vB version 2 and as far as I can see there should be no problems making it work. The backend should work with little or no changes. Just the changes to the files that handle new posts and replies will need to be redone. But I'm waiting for the final release before I do anything.

regards

looking forward to it :)

Just for my own time table - what are the current plans on this for the 2.0 release?

I actually thought it was ready this weekend but I came across a problem when dealing with mailing list archives such as the mailing.database.mysql newsgroup. Because these are posted to via an email program rather than a news client, I need to add more functionality to check for parent posts without relying on the X-References header.

At worst it will be out by Sunday, but as long as I have no major set-backs it should be ready mid to late of this week.

You can see how it's coming along at dBforums.com. You may see it filling up and emptying of posts at random while I'm testing.

There will be no control panel access in this release. I'll try and finish that by the final vB 2.0 but I'm not promising.

Some of the extra stuff that will be in this version are:

• Handling of quoted MIME printable headers (for all those funny foreign characters)
• Correct handling for mailing archive groups
• Color coding and indenting of quotes (customizable via replacement vars)
• vB style quote to usenet style quote conversions
• Usenet posters can be auto-added as special vB users to allow searching and post count tracking (they can easily be converted to real users by changing usergroup).
• email notification to vB users of 'replied to' usenet posts (uses existing vB template)
• Multiple news server support
• plus some other guff that I can't remember.

Would this script be compatible with a mailing list instead of just newsgroups? I have a mailing list archive (1994 to present) I want to put into a forum and then have email to a certain address be piped into a php or perl script to be inserted into the forum. The latter isn't as important since the main mailing list that I want to do is also a newsgroup, but there are a couple other mailing lists I'd like to put into their own forum. Anyways, being able to read a standard mail archive file (like what the mail spool file is) and stuff it into a forum all threaded and such would be nice.

You couldn't use it as is, but it wouldn't be too hard to modify. The script has four main sections at the moment:

1. Retrieve articles from server
2. Parse headers, format and spam control
3. Thread and sort
4. Insert into forums

2,3 & 4 can be pretty much left alone (maybe some unneeded stuff can even be taken out). Number one just needs to be changed to get the messages from the mail spool. If you don't fancy doing it yourself I will take a look after I've released a stable(ish) version.

will post some "more" :) things are i think of them...

* for usenet posters, somehow the offline/online thing should not happen as it makes no sense. I guess this is an edit in showthread.php based on usergroup.

* questionable value of "report to moderator" in usenet groups. I am not sure if moderating a usenet group is really something feasible or practical so perhaps there is a way to turn these off. I assume their is a field in forum table that says isUsenet type thing, perhaps we can do an if/elseif on report to moderator based on this?

* A way to restrict searches from usenet and non usenet forums. I guess this is an extra function that would be needed inside search. I know it could be done by setting the forumids manually inside search.php but a better way would be to autogenerate this on the isUsenet type field.

all for now...

Before you came out with this hack, I was planning on writing my own email <-> forum thing anyways... but no point in reinventing the wheel if yours does most of what I need. I haven't look at your script to see if it's what I need yet.

Anyways, for the mail to be processed by the script, it's pretty easy to do on *nix systems with sendmail. Just make an alias in the aliases file that calls the program like:Then the email to forumlist@whatever.com will be passed to the script as STDIN.
Then the path is /path/to/script.pl, but in a lot of systems a security measure is taken so that not just any program can be executed from an alias, so a link in the /etc/smrsh directory to the script needs to be made.

I cannot wait for the V2 version m8, already setup, ready for it togo!

fastforward, did u just reply??

I have been a bit busy at work this past couple of weeks and didn't manage to get all the promised features into this release. But not to worry as vB 2.0 is still only beta.

This release is for vB 2.0 beta3.

One major addition to this release is the ability to have usenet users added as forum users with a special usergroup and title to allow searching by username and display of post numbers. This option is still experimental and is not turned on by default. I do have the option on at dBforums.com and have had no major problems. You are welcome to try it yourself by setting the option in the script.

One omission from this release is control panel integration. I have been too busy to look at this and it will probably remain at the bottom of the 'to-do' list until all other functionality is in.

See the fist post in this thread for other additions and information.

The instructions for this release were rushed out so may contain errors. (although I did give them a test run and they seemed ok)

Download usenet_gateway.tar.gz

Regards

Yeah... I got confused while submiting a new post and editing the first post in the thread. I had two browser windows open and got V confused and posted the edit as a new post :confused: so I deleted it and restarted.

fastforward,

This is a massive hack! Thanks for the contribution. This should be great on my forum.

Just noticed the $usenet_usergroup_id=x from the script is not used, and a hardcoded group id of 8 is used in php code replacements. Similar variables are used like that, I think, like usenet user title. Just FYI, I'm still installing it with anticipation.

That's the only one. The problem is there is no way for the perl script to pass the variable to the php. I suppose I should really have set it up in config.php.

Another thing to add to my list. :(

I have a couple of newbie questions.. please be kind :)

> 3. Run the usenet.sql script.

How do I do that?
is it
mysql -uusername -ppasswd mydbname < usenet.sql

One thing that worries me is "... and adds some indexes and columns to existing tables."

Is it possible that there will be problems when I next time/in the future upgrade my vB when this hack has inserted new colums etc to existing tables.. for example if there will be new colums that go "on top" of these usenet-columns (yes, I know nothing about mysql/databases and I may be worrying fo nothing : )

Are there any other possible problems when upgrading to a new vB version besides need to modify all php-files again?

If i install this hack and want to remove it later what should I do?

or

If I install this hack but need to reinstall it after I have downloaded some newsgroups. What should I do to remove the existing news-messages so that I can start download them again?

Thanks!

This hack is NOT for the faint hearted!

You will definately have problems upgrading to a newer version of vB. The table changes are the least of your worries. Any vB code changes to the newreply or newthread files after this hack has been installed will make it impossible to post due to primary key violations. The changes are minor and it's easy to re-apply to any new release but I wouldn't advise you to implement this hack unless you are confident with mysql and php.

I doubt Jelsoft will offer much sympathy if you ask for support after installing a hack :)

As far as emptying out the usenet posts to restart... I have a little script I knocked up for that. I'll post it when I get home this evening.

Ok, thanks for a quick reply.

I used to run UBB so hacking is pretty familiar thing to me and I'm just trying to estimate what risks might be involved here. It seems that at this point this is not for me :(

This hack is just so good that it would be more than great if Jelsoft makes this part of vB at some point in the future.. sooner or later, preferably ASAP! ;)

I hope that there will be some version of this hack that is suitable for us who are not that familiar with php or mysql. (easy to install and maybe some script to uninstall this hack before a vB upgrade and then somehow install it back to the system).

This script will empty the forums of all usenet posts, users and clean up the index tables. It will empty all posts in a usenet forum regardless of who posted it.

Make sure you have a working backup before running this for the first time! :)

What it does:

1. Deletes all usenet threads from thread table for all usenet forums specified in usenet_group.
2. Sets the replycount, lastpost details to null for all usenet forums.
3. Delete all posts that belonged to the threads we just deleted.
4. Deletes all searchindex entries for posts that we just deleted.
5. Resets the lastmsg numbers to zero in the usenet_group table.
6. Empties the usenet_article and usenet_ref tables
7. Deletes all users with a usertitle of 'Usenet Poster'
8. Deletes userfield table entries for the users we just deleted

Mmmm.. I have a slight problem with 2.0b3... Everything runs smooth, the only flow is that in normal forums ( the vbb forums) I suddenly get Registered: and Location: field printed twice under username while in Usenet forums it's displayed only once. Went through instructions like 5 times already, everything seems to be just fine. I was just wondering if anyone else had simmilar issue...

fastfoward...

just one thing on the admin script. If you run the news script for a few weeks and then decide to ugrade to purge everything and do a re-install, the newsgroup posts sent via the forums won't be uploaded because it checks to ensure they are not there if you see what i mean. Is there a way to override this on a fresh install?

I thought of this myself while I was trying to figure out the best way of upgrading my boards. The easiest way is to add an option to bypass the check and just load everything in.

This release (2.1) fixes some bugs and made a few efficiency changes.

Files Changed:

1. config.php
2. newnews.pl
3. memberlist.php
4. newreply.php
5. functions.php

Changes:

• moved $usenetusergroupid to config.php to avoid hard coding the id
• added variable $lastactivethread_length in config.php to limit length of thread title if used in templates.
• corrected thread and forum replycount problems
• will re-import locally originated usenet posts if not already in forum
• added $recover_deleted_localposts option to re-import any locally originated posts that are on usenet but no longer in the forums due to being deleted. If this option is on, it will mean legitimately deleted posts will also be re-imported.
• fixed problem with outgoing messages being deleted from outgoing table before being sent!
• made multiple news server support an explicit option to prevent unnecessary connects/disconnects

The documentation has been updated and there is an upgrade set of instructions if you already installed version 2.0. Download The latest release is also in the first post of this thread

The link you just provided is broken. Can you check it and provided a new link please?

Ooops! :o
It's fixed now

Your installation instructions refer to "usenet.sql", but the file you provided for download does not have this file.

It contains "vB_sql_changes.sql". Do we use this file instead?

Yep. That's the one. I'll correct the docs now.