Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.os.linux.advocacy > #373005

Re: Geographic breakdown of cola regs

Show all headers | View raw

On 9/27/2016 12:50 PM, Steve Carroll wrote:
> On Tuesday, September 27, 2016 at 9:59:07 AM UTC-6, Steve Carroll
> wrote: (snip)
>> This is a great response, exactly what I was looking for... thanks!
>>  I've had trouble with getting pip happening but I will solve it at
>> some point and will give this a try if I can load the proper
>> libraries required.
>
> I did get the script to work,  now I have to figure out WTF you were
> talking about with that other stuff ;)

python lists, baby!

Data comes back from the server in list objects, and you need to extract 
the data from the lists to add it to your relational db.



> How did you grab the entire
> history and put it into a db?

Took a long time, because you don't want to ask for 50K or 100K articles 
at a time.  You probably can, but the server admin might notice and 
scold you, or ban you if you do it a lot.

First I downloaded every articleID on the server (and the post date), to 
see what was there.  As I recall I grabbed 1000 at a time in a loop - 
with 375K posts it probably took a few minutes.  Then I saw that on 
eternal-sep, the articleID sequence went directly from 1 to 201860.  1 
was a 2007 post, and 201860 was a Oct 2010 post.  Not sure why he keeps 1.

So I used the range of 201860 to 595126 (or whatever was the latest ID 
at the time - this was a few months ago) to build my db.

Then I went through each header I was interested in (From, Subject, 
MessageID, User-Agent, Newsgroups, References, InjectionInfo) and 
downloaded the header info for that range of articles 201860-595126.

Then I went back and got the Body for each post in that range.  That was 
the slowest part by far.  I think I did 100 posts at a time, store each 
line separately, parse the new content out, etc.  I'd kick off the 
program and let it run for a range of 10,000 posts, 100 at a loop. 
Probably spent 2-3 days on it, off and on.

Sandman uses something called 'suck' which retrieves NNTP data 
differently.  Then he parses the data locally, I believe.  It might be 
worth looking into.



> Using this script with different parameters?

Similar script, but much larger.  To build the db initially, I used a 
few scripts.  After the db was populated I created a maintenance script, 
which just gets the latest posts since the last time it was run.

--------------------------------------------
  > python nntp_updatedb.py
connecting...

added new articles: 605335-605532
updated Subject
updated From
updated Message-ID
updated User-Agent
updated Newsgroups
updated injection info
updated References
updated message bodies
added new content
Updated USENET.sqlite: 2.6e+02 seconds
--------------------------------------------

It now takes several minutes to store 200 posts.  Too slow.

The message bodies and new content part took as much as all other parts 
combined.  I need to look at the BODY table, maybe drop an index.  And 
fix the timer code.

And the 'get new content' routine is a little fubar for GoogleGroup posts.

So there are a few tweaks needed.


> Wouldn't that be a HUGE file?

Pretty large.  The SQLite db is a little over 3GB now, including 
indexes.  And that's normalized about as much as I could.  Each From and 
Subject and User-Agent is stored only once.

I like the tight design, but some queries are fast and some are slow. It 
can be a pain to query, actually.

The BODY table is now 17.48M rows, and indexed.  It's getting slow to 
populate.

Sometimes the SQLiteStudio app even freezes and crashes.



> And then, after that, how
> do you ascertain how to get additional posts that you don't already
> have, by checking message IDs?

Not with message IDs, but with article IDs.  Check the latest (largest) 
articleID assigned by the NNTP server against the latest articleID 
stored in your system.

The server response to the group command tells you the latest articleID 
available on the server, for that group:

resp, IDs, begID, endID, grpNm = news.group('comp.os.linux.advocacy')

resp: 211 394563 1 605472 comp.os.linux.advocacy
211    = command completed OK
394563 = number of articles available for that group
1      = earliest articleID for that group
605472 = latest articleID for that group
comp.os.linux.advocacy = group name

And with a successful response, python has already parsed the response 
and assigned values to the variables in the command:

resp, IDs, begID, endID, grpNm = news.group('comp.os.linux.advocacy')

resp  = "211 394563 1 605472 comp.os.linux.advocacy"
IDs   = 394563
begID = 1
endID = 605472
grpNm = "comp.os.linux.advocacy"

So now you compare the max articleID in your database (say 605452) 
against the latest articleID on the server (in this example, it's endID 
= 605472).  So you're missing the last 20 posts.

You should /definitely/ store the article ID assigned by the server. The 
article ID is unique for that post for that group for that server, but 
since your Usenet server company could shut down, you also need to keep 
track of which server assigned the article ID.  So I ended up assigning 
my own PostID:

CREATE TABLE POSTID (
POSTID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
SERVERID INTEGER NOT NULL REFERENCES NNTP_SERVER (SERVERID) ON UPDATE 
CASCADE,
GROUPID INTEGER NOT NULL REFERENCES NNTP_GROUP (GROUPID) ON UPDATE CASCADE,
ARTICLEID INTEGER NOT NULL
);

With that structure, you only have to add one new entry to the 
NNTP_SERVER table, point the code to the new NNTP server, and continue 
on.  No other changes to the code or db required.  Theoretically.  It's 
possible the new server doesn't support the same headers (eg some use 
'User-Agent', others use 'X-Newsreader' I believe) so that would be a 
minor tweak.  But it's super easy to use the same script to download 
other groups from the same or other servers.

Back to comp.os.linux.advocacy | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

Geographic breakdown of cola regs DFS <nospam@dfs.com> - 2016-09-26 12:57 -0400
  Re: Geographic breakdown of cola regs Steve Carroll <fretwizzer@gmail.com> - 2016-09-26 10:14 -0700
    Re: Geographic breakdown of cola regs Silver Slimer <.m@nsn.s> - 2016-09-26 13:38 -0400
      Re: Geographic breakdown of cola regs Steve Carroll <fretwizzer@gmail.com> - 2016-09-26 10:48 -0700
        Re: Geographic breakdown of cola regs Silver Slimer <.m@nsn.s> - 2016-09-26 13:56 -0400
          Re: Geographic breakdown of cola regs Steve Carroll <fretwizzer@gmail.com> - 2016-09-26 11:09 -0700
            Re: Geographic breakdown of cola regs Silver Slimer <.m@nsn.s> - 2016-09-26 14:13 -0400
              Re: Geographic breakdown of cola regs "Octavian W. Lagrange" <olagrang@perch.invalid> - 2016-09-26 18:30 +0000
                Re: Geographic breakdown of cola regs Silver Slimer <.m@nsn.s> - 2016-09-26 16:51 -0400
              Re: Geographic breakdown of cola regs "Octavian W. Lagrange" <olagrang@perch.invalid> - 2016-09-27 19:06 +0000
                Re: Geographic breakdown of cola regs Snit <usenet@gallopinginsanity.com> - 2016-09-27 12:32 -0700
                Re: Geographic breakdown of cola regs "Octavian W. Lagrange" <olagrang@perch.invalid> - 2016-09-27 19:50 +0000
                Re: Geographic breakdown of cola regs Snit <usenet@gallopinginsanity.com> - 2016-09-27 13:19 -0700
                Re: Geographic breakdown of cola regs Silver Slimer <.m@nsn.s> - 2016-09-27 20:12 -0400
                Re: Geographic breakdown of cola regs Silver Slimer <.m@nsn.s> - 2016-09-28 08:01 -0400
                Re: Geographic breakdown of cola regs Jim Polaski <jpolaski@linuxmail.org> - 2016-09-28 14:18 +0000
                Re: Geographic breakdown of cola regs Snit <usenet@gallopinginsanity.com> - 2016-09-28 08:46 -0700
                Re: Geographic breakdown of cola regs Steve Carroll <fretwizzer@gmail.com> - 2016-09-28 10:00 -0700
                Re: Geographic breakdown of cola regs Silver Slimer <.m@nsn.s> - 2016-09-28 14:55 -0400
      Re: Geographic breakdown of cola regs Marek Novotny <marek.novotny@marspolar.com> - 2016-09-26 19:06 +0000
        Re: Geographic breakdown of cola regs chrisv <chrisv@nospam.invalid> - 2016-09-26 14:12 -0500
          Re: Geographic breakdown of cola regs GreyCloud <Cumulus@mist.com> - 2016-09-26 13:53 -0600
            Re: Geographic breakdown of cola regs DFS <nospam@dfs.com> - 2016-09-26 19:03 -0400
              Re: Geographic breakdown of cola regs GreyCloud <Cumulus@mist.com> - 2016-09-26 18:32 -0600
            Re: Geographic breakdown of cola regs -hh <recscuba_google@huntzinger.com> - 2016-09-27 15:35 -0700
              Re: Geographic breakdown of cola regs GreyCloud <Cumulus@mist.com> - 2016-09-27 16:45 -0600
          Re: Geographic breakdown of cola regs William Poaster <wp@dev.null> - 2016-09-26 22:35 +0100
            Re: Geographic breakdown of cola regs Chris Ahlstrom <OFeem1987@teleworm.us> - 2016-09-26 17:55 -0400
            Re: Geographic breakdown of cola regs DFS <nospam@dfs.com> - 2016-09-26 19:04 -0400
        Re: Geographic breakdown of cola regs Silver Slimer <.m@nsn.s> - 2016-09-26 16:55 -0400
          Re: Geographic breakdown of cola regs Snit <usenet@gallopinginsanity.com> - 2016-09-26 14:08 -0700
            Re: Geographic breakdown of cola regs Peter Köhlmann <peter-koehlmann@t-online.de> - 2016-09-27 00:19 +0200
              Re: Geographic breakdown of cola regs Silver Slimer <.m@nsn.s> - 2016-09-26 18:38 -0400
              Re: Geographic breakdown of cola regs Snit <usenet@gallopinginsanity.com> - 2016-09-26 15:27 -0700
    Re: Geographic breakdown of cola regs DFS <nospam@dfs.com> - 2016-09-26 19:18 -0400
      Re: Geographic breakdown of cola regs Silver Slimer <.m@nsn.s> - 2016-09-26 19:22 -0400
      Re: Geographic breakdown of cola regs Snit <usenet@gallopinginsanity.com> - 2016-09-26 16:36 -0700
        Re: Geographic breakdown of cola regs Steve Carroll <fretwizzer@gmail.com> - 2016-09-26 17:54 -0700
          Re: Geographic breakdown of cola regs DFS <nospam@dfs.com> - 2016-09-27 11:09 -0400
            Re: Geographic breakdown of cola regs Steve Carroll <fretwizzer@gmail.com> - 2016-09-27 08:58 -0700
              Re: Geographic breakdown of cola regs Steve Carroll <fretwizzer@gmail.com> - 2016-09-27 09:50 -0700
                Re: Geographic breakdown of cola regs DFS <nospam@dfs.com> - 2016-09-27 14:33 -0400
                Re: Geographic breakdown of cola regs Steve Carroll <fretwizzer@gmail.com> - 2016-09-27 12:24 -0700
                Re: Geographic breakdown of cola regs DFS <nospam@dfs.com> - 2016-09-29 19:51 -0400
                Re: Geographic breakdown of cola regs Steve Carroll <fretwizzer@gmail.com> - 2016-09-29 17:22 -0700
                Re: Geographic breakdown of cola regs DFS <nospam@dfs.com> - 2016-09-29 23:27 -0400
      Re: Geographic breakdown of cola regs GreyCloud <Cumulus@mist.com> - 2016-09-26 18:35 -0600
        Re: Geographic breakdown of cola regs "Octavian W. Lagrange" <olagrang@perch.invalid> - 2016-09-27 05:38 +0000
        Re: Geographic breakdown of cola regs DFS <nospam@dfs.com> - 2016-09-27 01:48 -0400
          Re: Geographic breakdown of cola regs GreyCloud <Cumulus@mist.com> - 2016-09-27 12:08 -0600
        Re: Geographic breakdown of cola regs Silver Slimer <.m@nsn.s> - 2016-09-27 08:48 -0400
          Re: Geographic breakdown of cola regs GreyCloud <Cumulus@mist.com> - 2016-09-27 12:10 -0600
            Re: Geographic breakdown of cola regs Silver Slimer <.m@nsn.s> - 2016-09-27 20:09 -0400
      Re: Geographic breakdown of cola regs "Octavian W. Lagrange" <olagrang@perch.invalid> - 2016-09-27 05:57 +0000
        Re: Geographic breakdown of cola regs DFS <nospam@dfs.com> - 2016-09-27 11:10 -0400
          Re: Geographic breakdown of cola regs "Octavian W. Lagrange" <olagrang@perch.invalid> - 2016-09-27 15:46 +0000
      Re: Geographic breakdown of cola regs Incubus <incubus9536612@gmail.com> - 2016-09-27 07:54 -0700
        Re: Geographic breakdown of cola regs Silver Slimer <.m@nsn.s> - 2016-09-27 11:18 -0400
          Re: Geographic breakdown of cola regs DFS <nospam@dfs.com> - 2016-09-27 11:28 -0400
  Re: Geographic breakdown of cola regs fr314159@gmail.com - 2016-09-26 10:23 -0700
  Re: Geographic breakdown of cola regs Sandman <mr@sandman.net> - 2016-09-27 09:06 +0000
    Re: Geographic breakdown of cola regs GreyCloud <Cumulus@mist.com> - 2016-09-27 12:11 -0600
      Re: Geographic breakdown of cola regs Peter Köhlmann <peter-koehlmann@t-online.de> - 2016-09-27 20:28 +0200
        Re: Geographic breakdown of cola regs GreyCloud <Cumulus@mist.com> - 2016-09-27 15:04 -0600
        Re: Geographic breakdown of cola regs Chris Ahlstrom <OFeem1987@teleworm.us> - 2016-09-27 19:00 -0400
      Re: Geographic breakdown of cola regs Sandman <mr@sandman.net> - 2016-09-27 20:32 +0000
        Re: Geographic breakdown of cola regs Snit <usenet@gallopinginsanity.com> - 2016-09-27 13:34 -0700

csiph-web