Re: [Pan-users] Multiple Server support for PAN: Need a spec

[Charles Kerr]

On Mon, Oct 21, 2002 at 02:29:36AM -0500, ByteEnable wrote:
> Hi all,
> 
> Is there a spec yet for multiple server support so we can start coding? 
> The biggest issue will be the database format.  Someone previously
> suggested MySql.  I have no inclinations to one db over another db.

By default we should use sqlite because we can embed it --
having a postgresql or mysql prerequisite for Pan is terrible overkill. :)
I don't mind making other DBs pluggable though.

> But this is really most of the work to get this feature (deciding on the
> database set) working.  Obviously when adding a feature like this where
> a user could in theory have a gigabyte or more of data to manage, the
> database methods need to be solid.  Little bugs in a big database means
> that a user would have to RE-DOWNLOAD headers again from pay servers
> that cost money.
> 
> I think this has been mentioned before, but there needs to be a
> bandwidth counter with download priority for servers.  Like,
> 
> Priority Server Name
> 2        pro.news.com
> 1        news.easynews.com  
> 3        some.news.net      3 has the least priority
> 0        my.isp.com         0 has highest priority
> 
> So for example,  when downloading bodies it will hit my.isp.com first,
> if it cannot find the body or attachment it will go to pro.news.com next
> and so on down the list.
> 
> As far as the records go:
> 
> I guess you would have a master/merged index that points to the seperate
> server database's.  That way if a server is removed it wouldn't effect
> the whole database, just that servers data in in its own subdirectory. 
> I'm just throwing stuff out on the db stuff.  Database maintenance could
> turn into a nightmare for an end user if it is not designed properly.

Off the top of my head, here's a rough draft of the tables.
Tuning changes, or flat-out rewrites, are welcomed. :)

SERVER
A NNTP news server.
Rank will be used to tell which servers to hit first for a body.
Unresolved: should we handle multiple hostname+port pairs?

  * name              (string)
    username          (string)
    password          (string)
    hostname          (string) 
    port              (unsigned int)
    rank              (unsigned int)

GROUP
A newsgroup.

  * name              (string)
    description       (string)
    moderation flags  (unsigned int) 
    

GROUP_SERVER
Pair each group with 1 or more server, and each server with 1 or more group.
This way "get new headers" will know which servers need to be hit.

  * id                (unsigned long)
    group_name        (xref to group::name)
    server_name       (xref to server::name)

ARTICLE
All these fields can be populated from an XOVER line, yay!

  * message_id        (string)
    date              (time_t)
    lines             (unsigned int)
    subject           (string)
    author            (string)
    references        (string)

GROUP_SERVER_ARTICLE
A tuple of group + server + article.
Used to retrieve the article, by its index number, from any GROUP_SERVER

    group_server_id   (xref to GROUP_SERVER::id)
    message_id        (xref to ARTICLE::message_id)
    index_number      (unsigned long)

-----
Notes:

* This still doesn't handle "what articles are new/unread in this group?"
  It may be easiet to just put 'read' and 'new' flags in ARTICLE,
  but it would make .newsrc support problematic.  This needs more thought.

* When deleting a server, we'd need to delete from GROUP_SERVER_ARTICLE
  everything where group_server_id has a server_name that we're deleting,
  then delete from GROUP_SERVER anything with that server name,
  then finally from SERVER itself.

* GROUP_SERVER::id will be an arbitrary unique number pulled from a
  sequence whenever new groups are added.

* We might also want to think about storing threading support somewhere
  in the database, so that we don't have to thread articles each time
  we load a group.  If we do, we'll need to think about how to support
  article deletion.