[Top][All Lists]

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

[Pan-devel] ancient DB schema

From: K. Haley
Subject: [Pan-devel] ancient DB schema
Date: Fri, 04 Jun 2004 18:56:11 -0600
User-agent: Mozilla Thunderbird 0.6 (Windows/20040502)

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

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)

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)

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)

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 
    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)

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.

Attachment: signature.asc
Description: OpenPGP digital signature

reply via email to

[Prev in Thread] Current Thread [Next in Thread]