[Top][All Lists]

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

[Pan-devel] Code & DB update notice

From: K. Haley
Subject: [Pan-devel] Code & DB update notice
Date: Sun, 19 Dec 2004 00:28:44 -0700
User-agent: Mozilla Thunderbird 1.0 (Windows/20041206)

There is a new snapshot on my site. The link to it and a list of changes can be found at . 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

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)

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)

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)

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

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

File: pan_article.sqlite3

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

List of authors.  Unique index on (real,address).
 ! real              (string  NOT NULL)
 ! address           (string NOT NULL)
 * 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 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)

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)

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

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

File: pan_cache.sqlite3


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


 ! 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)
 * id                (int primary key)

Attachment: signature.asc
Description: OpenPGP digital signature

reply via email to

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