[Pan-devel] Code & DB update notice

[K. Haley]

There is a new snapshot on my site.  The link to it and a list of 
changes can be found at http://www.zianet.com/lost-coder .  I've 
followed Micheal's suggestion and made most of the DB fields NOT NULL.  
I've also added two new tables - headers and article_header.  Unless 
their are bugs or performance issues I expect this to be the last schema 
update I make.

* primary key
! indexed

File: pan.sqlite3

SERVER
A NNTP news server.
Rank will be used to tell which servers to hit first for a body.

 ! name              (string unique)
   hostname          (string NOT NULL)
   port              (unsigned int NOT NULL)
   username          (string NOT NULL)
   password          (string NOT NULL)
   rank              (unsigned int=0 NOT NULL)
   max_conn          (int NOT NULL)
   idle_timeout      (int NOT NULL)
   need_auth         (bool NOT NULL)
   newsrc            (bool NOT NULL)
   last_gl_update    (unsinged int NOT NULL)  last time the group list 
was fetched
   newrc_fn          (string NOT NULL)  newsrc filename
 * id                (integer primary key)


GROUPS
A newsgroup or a folder.

 ! name              (string NOT NULL UNIQUE)
   description       (string NOT NULL)
 ! subscribed        (int=0 NOT NULL) think ref count
 ! folder            (boolean=0 NOT NULL)
   qty               (uint32=0 NOT NULL)
   qty_read          (uint32=0 NOT NULL)
   filter_name       (string NOT NULL)
   filter_show       (uint32 NOT NULL)
   filter_bits       (uint32 NOT NULL)
   sort              (int8 NOT NULL)
   sort_old          (int8 NOT NULL)
   identity          (string NOT NULL) empty for default
   download_dir      (string NOT NULL)
   charset           (string NOT NULL)
 ! new               (boolean=1 NOT NULL)   
 * 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.
Also keep track of min and max article numbers.

 ! group_id          (xref to group::id NOT NULL)
 ! server_id         (xref to server::id NOT NULL)
   article_min       (integer=0 NOT NULL)
   article_max       (integer=0 NOT NULL)
   loaded_since_fetch(boolean=0 NOT NULL)
   article_max_old   (integer=0 NOT NULL)
   permision         (char(1) NOT NULL)  [m,y,n] moderated,post,no-post
 * id                (integer primary key)

VSERVERS
Name of each vserver except the default server.
   name              (string UNIQUE)
 * id                (int PRIMARY KEY)

VSERVER_GROUP
Maps vservers to groups.
   group_id          (int NOT NULL)  GROUP:id
 ! vserver           (int NOT NULL)  VSERVER:id
 * id

File: pan_article.sqlite3

REFS
 ! article_id        (int NOT NULL) ARTICLE:id
   reference         (BLOB NOT NULL) 16 byte MD5 hash of 1 reference
 * id                (int primary key)

AUTHORS
List of authors.  Unique index on (real,address).
 ! real              (string  NOT NULL)
 ! address           (string NOT NULL)
 * id                (int primary key)

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

   message_id        (string NOT NULL)
   date              (int NOT NULL)
   lines             (unsigned int NOT NULL)
   subject           (text NOT NULL)
   author            (int NOT NULL) AUTHORS:id
   read              (boolean=0 NOT NULL)
   new               (boolean=1 NOT NULL)
   byte_qty          (int NOT NULL)
   flagged           (boolean=0 NOT NULL)
   part              (int=0 NOT NULL)
   parts             (int=0 NOT NULL)
 ! refcnt            (int=0 NOT NULL)  reference count of G_S_A that 
refer here
   keep              (boolean=0 NOT NULL)  DO NOT remove from cache or 
G_S_A EVER
 ! hash              (BLOB unique not null) mid hash
 * id                (integer primary key)

GROUP_ARTICLE
Stores group specific article data.  Unique index on (gid,aid).

 ! group_id          (int NOT NULL) GROUP:id
 ! article_id        (int NOT NULL) ARTICLE:id
   score             (int=0 NOT NULL)
   score_date        (int=0 NOT NULL)
 ! parent            (int=0 NOT NULL) ARTICLE:id may not be 'real' parent
 * id                (int primary key)

G_S_A (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 NOT NULL)
 ! article_id        (xref to ARTICLE::id NOT NULL)
   uid               (unsigned long NOT NULL)
 * id                (integer primary key)

HEADERS
Stores additional headers.  There is a UNIQUE index on (header,text).
   header            (string NOT NULL)
   text              (string NOT NULL)
 * id                (integer primary key)

ARTICLE_HEADER
   aid               (int NOT NULL) ARTICLE:id
 ! hid               (int NOT NULL) HEADERS:id
 * id                (integer primary key)

File: pan_cache.sqlite3

CACHE_PATHS

 ! key               (string unique NOT NULL)
   path              (string NOT NULL)
 * id                (int primary key)

CACHE

 ! hash              (blob NOT NULL) msgid hash 16 bytes
   file              (string NOT NULL) file name
   size              (int NOT NULL)    file size
   date              (int NOT NULL)    file mod time
   refcnt            (int NOT NULL)
 ! keyid             (int NOT NULL) CACHE_PATHS.id
 * id                (int primary key)

signature.asc
Description: OpenPGP digital signature