[Pan-devel] ancient DB schema

[K. Haley]

I'm attaching an old DB schema I came up with.  It is based on the one 
posted by Charles a long time ago.  There are still some unanswered 
questions as to where some of the info should go.  The biggest one is 
whether or not articles in folders should be in their own table.   FYI I 
chose to use integer primary keys for space and speed savings.

Here are my thoughts on a possible DB format.  This is based on a message 
Charles posted.

* primary key
! indexed

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?
(just remember to seperate servers that handle different groups.)

  ! name              (string NOT NULL)
    hostname          (string NOT NULL) 
    username          (string)
    password          (string)
    port              (unsigned int=)
    rank              (unsigned int=0)
  * id                (integer primary key)


GROUP
A newsgroup.
(it looks to me like pan treats folders the same as news groups. `folder` is 
not needed if seperate FOLDER and FOLDER_ARTICLE tables are used.)

  ! name              (string NOT NULL UNIQUE)
    description       (string)
    moderation flags  (unsigned int=0) 
  ! subscribed        (boolean=0)
    folder            (boolean=0)   
    qty               (unsigned long=0)
    qty_read          (unsigned long=0)
    sort              (int=0)  0 for default
    filter            (?) would this need more entries?
    identity          (string) null for default
  * id                (integer primary key)

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                (integer primary key)
  ! group_id          (xref to group::id)
  ! server_id         (xref to server::id)

ARTICLE
All these fields can be populated from an XOVER line, yay!
(not my additions, though they could be guessed.)

  ! message_id        (unique string)
    date              (timestamp)
    lines             (unsigned int)
    subject           (string)
    author            (string)
    references        (string)
    read              (boolean=0)
    flagged           (boolean=0)  here if we want persistent flagging across 
groups
    binary            (boolean=0)  guessed from subject, eventually from 
article text
    part              (int=0)
    part_num          (int=0)
    keep              (boolean=0)  DO NOT remove from cache or G_S_A EVER
  * id                (integer primary key)

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::id)
    index_number      (unsigned long)
  * id                (integer primary key)

-----
Notes: (mostly from the original post)

* 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.

Why would it make .newsrc problematic? Isn't this how it would be done:
 1. from G_S lookup all groups from server
 2. from G_S_A get all msgid's
 3. from ARTICLE get read status


* 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.

This would have to be done per group.  Think about a crossposted article that 
has non-crossposted replies.  While a parent pointer could be stored per 
article there is a problem if the child arrives before the parent.

signature.asc
Description: OpenPGP digital signature