[Top][All Lists]

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

[Pan-devel] DB backend

From: K. Haley
Subject: [Pan-devel] DB backend
Date: Tue, 04 Jan 2005 00:37:00 -0700
User-agent: Mozilla Thunderbird 1.0 (Windows/20041206)

I've put a new snapshot on my site.  In this one pan/base is, I believe,
nearly feature complete.  The rest of pan needs to be updated of
course.  The first thing to do would be to work on the test suite since
I don't know if my could is functional.  This is probably as far as I
can take it on my own.

article-thread: one function to thread a group in the db, I suspect it
will have a problem with replies to binaries.  Needs a function to
(re)thread specific articles.
article: support for threading, new & unread children, loading of
children, article deletion.
group: article loading now uses function from article.
others: various updates and fixes.

Here is the latest db schema, it does have a couple of changes.

* 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=0 NOT NULL)
   filter_bits       (uint32=0 NOT NULL)
   sort              (int8=0 NOT NULL)
   sort_old          (int8=0 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)

normalized subject, without reply leaders & LAST multipart
   subject           (string UNIQUE NOT NULL)
 * id                (int primary key)

Most these fields can be populated from an XOVER line, yay!

   message_id        (string NOT NULL)
   date              (int NOT NULL)
   lines             (unsigned int 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
 ! hash              (BLOB unique not null) mid hash
   pre_subj          (string NOT NULL)
   post_subj         (string NOT NULL)
 ! norm_subj         (int NOT NULL) NORM_SUBJECT:id
 * 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]