[Top][All Lists]

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

Re: [Pan-devel] DB code status

From: K. Haley
Subject: Re: [Pan-devel] DB code status
Date: Mon, 06 Dec 2004 16:19:13 -0700
User-agent: Mozilla Thunderbird 0.8 (Windows/20040913)

Charles Kerr wrote:

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


I've put the snaphot on my website . 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

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)

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)

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)
   article_max_old   (integer=0)
   permision         (char(1))  [m,y,n] moderated,post,no-post
 * id                (integer primary key)

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

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

FILE: pan_article.sqlite

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

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)

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)

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


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


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

Attachment: signature.asc
Description: OpenPGP digital signature

reply via email to

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