Re: [Pan-devel] DB code status

[K. Haley]

Charles Kerr wrote:

> Could you mail me a snapshot of your code so that I can look at the DB 
> backend?
>
> cheers,
> Charles

I've put the snaphot on my website 
http://www.zianet.com/lost-coder/pan-snapshot.zip .  Here is the DB 
schema that I'm currently using, (I know why I put the cache data in a 
seperate file but I can't remember why I put the article data in one):

* primary key
! indexed

FILE: pan.sqlite

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)
   username          (string)
   password          (string)
   rank              (unsigned int=0)
   max_conn          (int)
   idle_timeout      (int)
   need_auth         (bool)
   newsrc            (bool)
   last_gl_update    (unsinged int)  last time the group list was fetched
   newrc_fn          (string)  newsrc filename
 * id                (integer primary key)


GROUPS
A newsgroup or a folder.

 ! name              (string NOT NULL UNIQUE)
   description       (string)
 ! subscribed        (int=0) think ref count
 ! folder            (boolean=0)
   qty               (uint32=0)
   qty_read          (uint32=0)
   filter_name       (string)
   filter_show       (uint32)
   filter_bits       (uint32)
   sort              (int8)
   sort_old          (int8)
   identity          (string) null for default
   download_dir      (string)
   charset           (string)
 ! new               (boolean=1)  
 * 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)
 ! server_id         (xref to server::id)
   article_min       (integer=0)
   article_max       (integer=0)
   loaded_since_fetch(boolean=0)
   article_max_old   (integer=0)
   permision         (char(1))  [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)  GROUP:id
 ! vserver           (int)  VSERVER:id
 * id

FILE: pan_article.sqlite

REFS
 ! article_id        (int) ARTICLE:id
   reference         (BLOB) 16 byte MD5 hash of 1 reference
 * 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)
   date              (int)
   lines             (unsigned int)
   subject           (text)
   author_r          (text)
   author_a          (text)
   read              (boolean=0)
   new               (boolean=1)
   byte_qty          (int)
   flagged           (boolean=0)
   part              (int=0)
   parts             (int=0)
 ! refcnt            (int)  reference count of G_S_A that refer here
   keep              (boolean=0)  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) GROUP:id
 ! article_id        (int) ARTICLE:id
   score             (int=0)
   score_date        (int=0)
 ! parent            (int=0) ARTICLE:id may not be 'real' parent
 * id                (int 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)
 ! article_id        (xref to ARTICLE::id)
   uid               (unsigned long)
 * id                (integer primary key)


FILE: pan_cache.sqlite

CACHE_PATHS

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

CACHE

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

signature.asc
Description: OpenPGP digital signature