[Pan-devel] DB backend

[K. Haley]

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.

http://www.zianet.com/lost-coder/pan-snapshot.zip

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

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=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)

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)

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

ARTICLE
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
G_S_A EVER
 ! 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)

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