databaselayout.md: added code blocks

[citadel.git] / citadel / docs / databaselayout.md

The totally incomplete guide to Citadel internals
-----------------------------------------------------

Citadel has evolved quite a bit since its early days, and the data structures
have evolved with it.  This document provides a rough overview of how the
system works internally.  For details you're going to have to dig through the
code, but this'll get you started. 


DATABASE TABLES
---------------
As you probably already know by now, Citadel uses a group of tables stored
with a record manager (usually Berkeley DB).  Since we're using a record
manager rather than a relational database, all record structures are managed
by Citadel.  Here are some of the tables we keep on disk:


USER RECORDS
------------
This table contains all user records.  It's indexed by
user name (translated to lower case for indexing purposes).  The records in
this file look something like this:

```
    struct ctdluser {                   // User record
        int version;                    // Citadel version. which created this record
        uid_t uid;                      // Associate with a unix account?
        char password[32];              // Account password (unless using external authentication)
        unsigned flags;                 // See US_ flags below
        CIT_UBYTE axlevel;              // Access level
        long usernum;                   // User number (never recycled)
        time_t lastcall;                // Last time the user called
        int USuserpurge;                // Purge time (in days) for user
        char fullname[64];              // Name for Citadel messages & mail
    };
```
 
 Most fields here should be fairly self-explanatory.  The ones that might
deserve some attention are:

 - `uid` -- if uid is not the same as the *unix uid* Citadel is running as, then the
   account is assumed to belong to the user on the underlying Unix system with
   that uid.  This allows us to require the user's OS password instead of having
   a separate Citadel password.
 
 - `usernum` -- these are assigned sequentially, and **NEVER REUSED**. This is
  important because it allows us to use this number in other data structures
  without having to worry about users being added/removed later on, as you'll
  see later in this document.
 
 
ROOM RECORDS
------------
These are room records.  There is a room record for every room on the
system, public or private or mailbox.  It's indexed by room name (also in
lower case for easy indexing) and it contains records which look like this:

```
    struct ctdlroom {
        char QRname[ROOMNAMELEN];       /* Name of room                     */
        char QRpasswd[10];              /* Only valid if it's a private rm  */
        long QRroomaide;                /* User number of room aide         */
        long QRhighest;                 /* Highest message NUMBER in room   */
        time_t QRgen;                   /* Generation number of room        */
        unsigned QRflags;               /* See flag values below            */
        char QRdirname[15];             /* Directory name, if applicable    */
        long QRinfo;                    /* Info file update relative to msgs*/
        char QRfloor;                   /* Which floor this room is on      */
        time_t QRmtime;                 /* Date/time of last post           */
        struct ExpirePolicy QRep;       /* Message expiration policy        */
        long QRnumber;                  /* Globally unique room number      */
        char QRorder;                   /* Sort key for room listing order  */
        unsigned QRflags2;              /* Additional flags                 */
        int QRdefaultview;              /* How to display the contents      */
    };
```

Again, mostly self-explanatory.  Here are the interesting ones:
 
`QRnumber` is a globally unique room ID, while `QRgen` is the "generation number"
of the room (it's actually a timestamp).  The two combined produce a unique
value which identifies the room.  The reason for two separate fields will be
explained below when we discuss the visit table.  For now just remember that
`QRnumber` remains the same for the duration of the room's existence, and `QRgen`
is timestamped once during room creation but may be restamped later on when
certain circumstances exist.

FLOORTAB
--------
Floors.  This is so simplistic it's not worth going into detail about, except
to note that we keep a reference count of the number of rooms on each floor.
 
MSGLISTS
--------
Each record in this table consists of a bunch of message numbers
which represent the contents of a room.  A message can exist in more than one
room (for example, a mail message with multiple recipients -- 'single instance
store').  This table is never, ever traversed in its entirety.  When you do
any type of read operation, it fetches the msglist for the room you're in
(using the room's ID as the index key) and then you can go ahead and read
those messages one by one.

Each room is basically just a list of message numbers.  Each time
we enter a new message in a room, its message number is appended to the end
of the list.  If an old message is to be expired, we must delete it from the
message base.  Reading a room is just a matter of looking up the messages
one by one and sending them to the client for display, printing, or whatever.
 

VISIT
-----
This is the tough one.  Put on your thinking cap and grab a fresh cup of
coffee before attempting to grok the visit table.
 
This table contains records which establish the relationship between users
and rooms.  Its index is a hash of the user and room combination in question.
When looking for such a relationship, the record in this table can tell the
server things like "this user has zapped this room," "this user has access to
this private room," etc.  It's also where we keep track of which messages
the user has marked as "old" and which are "new" (which are not necessarily
contiguous; contrast with older Citadel implementations which simply kept a
"last read" pointer).
 

Here's what the records look like:
 
```
    struct visit {
        long v_roomnum;
        long v_roomgen;
        long v_usernum;
        long v_lastseen;
        unsigned int v_flags;
        char v_seen[SIZ];
        int v_view;
    };

    #define V_FORGET        1       /* User has zapped this room        */
    #define V_LOCKOUT       2       /* User is locked out of this room  */
    #define V_ACCESS        4       /* Access is granted to this room   */
```
 
This table is indexed by a concatenation of the first three fields.  Whenever
we want to learn the relationship between a user and a room, we feed that
data to a function which looks up the corresponding record.  The record is
designed in such a way that an "all zeroes" record (which is what you get if
the record isn't found) represents the default relationship.
 
With this data, we now know which private rooms we're allowed to visit: if
the `V_ACCESS` bit is set, the room is one which the user knows, and it may
appear in his/her known rooms list.  Conversely, we also know which rooms the
user has zapped: if the `V_FORGET` flag is set, we relegate the room to the
zapped list and don't bring it up during new message searches.  It's also
worth noting that the `V_LOCKOUT` flag works in a similar way to administratively
lock users out of rooms.
 
Implementing the "cause all users to forget room" command, then, becomes very
simple: we simply change the generation number of the room by putting a new
timestamp in the `QRgen` field.  This causes all relevant visit records to
become irrelevant, because they appear to point to a different room.  At the
same time, we don't lose the messages in the room, because the msglists table
is indexed by the room number (`QRnumber`), which never changes.
 
`v_seen` contains a string which represents the set of messages in this room
which the user has read (marked as 'seen' or 'old').  It follows the same
syntax used by IMAP and NNTP.  When we search for new messages, we simply
return any messages that are in the room that are **not** represented by this
set.  Naturally, when we do want to mark more messages as seen (or unmark
them), we change this string.  Citadel BBS client implementations are naive
and think linearly in terms of "everything is old up to this point," but IMAP
clients want to have more granularity.


DIRECTORY
---------
This table simply maps Internet e-mail addresses to Citadel network addresses
for quick lookup.  It is generated from data in the Global Address Book room.

USETABLE
--------
This table keeps track of message ID's of messages arriving over a network,
to prevent duplicates from being posted if someone misconfigures the network
and a loop is created.  This table goes unused on a non-networked Citadel.

THE MESSAGE STORE
-----------------
This is where all message text is stored.  It's indexed by message number:
give it a number, get back a message.  Messages are numbered sequentially, and
the message numbers are never reused.
 
We also keep a "metadata" record for each message.  This record is also stored
in the msgmain table, using the index (0 - msgnum).  We keep in the metadata
record, among other things, a reference count for each message.  Since a
message may exist in more than one room, it's important to keep this reference
count up to date, and to delete the message from disk when the reference count
reaches zero.

NoSQL pros might recognize the message store as a "document database".  Each
record is an arbitrary collection of keys and values, some of which may
appear all the time, and some which might appear infrequently.
 
# Here's the format for the message itself:

 - Each message begins with an 0xFF 'start of message' byte.
 
 - The next byte denotes whether this is an anonymous message.  The codes
   available are `MES_NORMAL`, `MES_ANON`, or `MES_AN2` (defined in `citadel.h`).
 
 - The third byte is a "message type" code.  The following codes are defined:
  - 0 - "Traditional" Citadel format.  Message is to be displayed "formatted."
  - 1 - Plain pre-formatted ASCII text (otherwise known as text/plain)
  - 4 - MIME formatted message.  The text of the message which follows is
        expected to begin with a "Content-type:" header.
 
 - After these three opening bytes, the remainder of
   the message consists of a sequence of character strings.  Each string
   begins with a type byte indicating the meaning of the string and is
   ended with a null.  All strings are printable ASCII: in particular,
   all numbers are in ASCII rather than binary.  This is for simplicity,
   both in implementing the system and in implementing other code to
   work with the system.  For instance, a database driven off Citadel archives
   can do wildcard matching without worrying about unpacking binary data such
   as message ID's first.  To provide later downward compatability
   all software should be written to IGNORE fields not currently defined.


# The type bytes currently defined are:


    | BYTE  |       Enum        | NW   | Mnemonic       |  Enum / Comments
    |-------|-------------------|------|----------------|---------------------------------------------------------
    | A     |    eAuthor        | from | Author         |  The display name of the Author of the message.
    | B     |    eBig_message   |      | Big message    |  This is a flag which indicates that the message is
    |       |                   |      |                |  big, and Citadel is storing the body in a separate
    |       |                   |      |                |  record.  You will never see this field because the
    |       |                   |      |                |  internal API handles it.
    | E     |    eExclusiveID   | exti | Exclusive ID   |  A persistent alphanumeric Message ID used for
    |       |                   |      |                |  replication control.  When a message arrives that
    |       |                   |      |                |  contains an Exclusive ID, any existing messages which
    |       |                   |      |                |  contain the same Exclusive ID and are *older* than this
    |       |                   |      |                |  message should be deleted.  If there exist any messages
    |       |                   |      |                |  with the same Exclusive ID that are *newer*, then this
    |       |                   |      |                |  message should be dropped.
    | F     |    erFc822Addr    | rfca | rFc822 address |  email address or user principal name of the message
    |       |                   |      |                |  author.
    | I     |    emessageId     | msgn | Message ID     |  An RFC822-compatible message ID for this message.
    |       |                   |      |                |  
    | J     |    eJournal       | jrnl | Journal        |  The presence of this field indicates that the message
    |       |                   |      |                |  is disqualified from being journaled, perhaps because
    |       |                   |      |                |  it is itself a journalized message and we wish to
    |       |                   |      |                |  avoid double journaling.
    | K     |    eReplyTo       | rep2 | Reply-To       |  the Reply-To header for mailinglist outbound messages
    | L     |    eListID        | list | List-ID        |  Mailing list identification, as per RFC 2919
    | M     |    eMesageText    | text | Message Text   |  Normal ASCII, newlines seperated by CR's or LF's,
    |       |                   |      |                |  null terminated as always.
    | O     |    eOriginalRoom  | room | Room           |  Room of origin.
    | P     |    eMessagePath   | path | Path           |  Complete path of message, as in the UseNet news
    |       |                   |      |                |  standard.  A user should be able to send Internet mail
    |       |                   |      |                |  to this path. (Note that your system name will not be
    |       |                   |      |                |  tacked onto this until you're sending the message to
    |       |                   |      |                |  someone else)
    | R     |    eRecipient     | rcpt | Recipient      |  Only present in Mail messages.
    | T     |    eTimestamp     | time | date/Time      |  Unix timestamp containing the creation date/time of
    |       |                   |      |                |  the message.
    | U     |    eMsgSubject    | subj | sUbject        |  Message subject.  Optional.
    |       |                   |      |                |  Developers may choose whether they wish to
    |       |                   |      |                |  generate or display subject fields.
    | V     |    eenVelopeTo    | nvto | enVelope-to    |  The recipient specified in incoming SMTP messages.
    | W     |    eWeferences    | wefw | Wefewences     |  Previous message ID's for conversation threading.  When
    |       |                   |      |                |  converting from RFC822 we use References: if present, or
    |       |                   |      |                |  In-Reply-To: otherwise.
    |       |                   |      |                |  (Who in extnotify spool messages which don't need to know
    |       |                   |      |                |  other message ids)
    | Y     |    eCarbonCopY    | cccc | carbon copY    |  Carbon copy (CC) recipients.
    |       |                   |      |                |  Optional, and only in Mail messages.
    | %     |    eHeaderOnly    | nhdr | oNlyHeader     |  we will just be sending headers. for the Wire protocol only.
    | %     |    eFormatType    | type | type           |  type of citadel message: (Wire protocol only)
    |       |                   |      |                |     FMT_CITADEL     0   Citadel vari-format (proprietary) 
    |       |                   |      |                |     FMT_FIXED       1   Fixed format (proprietary)
    |       |                   |      |                |     FMT_RFC822      4   Standard (headers are in M field)
    | %     |    eMessagePart   | part | emessagePart   |  eMessagePart is the id of this part in the mime hierachy
    | %     |    eSubFolder     | suff | eSubFolder     |  descend into a mime sub container
    | %     |    ePevious       | pref | ePevious       |  exit a mime sub container
    | 0     |    eErrorMsg      |      | Error          |  This field is typically never found in a message on
    |       |                   |      |                |  disk or in transit.  Message scanning modules are
    |       |                   |      |                |  expected to fill in this field when rejecting a message
    |       |                   |      |                |  with an explanation as to what happened (virus found,
    |       |                   |      |                |  message looks like spam, etc.)
    | 1     |    eSuppressIdx   |      | suppress index |  The presence of this field indicates that the message is
    |       |                   |      |                |  disqualified from being added to the full text index.
    | 2     |    eExtnotify     |      | extnotify      |  Used internally by the serv_extnotify module.
    | 3     |    eVltMsgNum     |      | msgnum         |  Used internally to pass the local message number in the
    |       |                   |      |                |  database to after-save hooks.  Discarded afterwards.
    |       |                   | locl |                |  The presence of this field indicates that the message
    |       |                   |      |                |  is believed to have originated on the local Citadel node,
    |       |                   |      |                |  not as an inbound email or some other outside source.

EXAMPLE
-------
Let `<FF>` be a `0xFF` byte, and `<0>` be a null `(0x00)` byte.  Then a message
which prints as...

```
    Apr 12, 1988 23:16 From Test User In Network Test> @lifesys (Life Central)
    Have a nice day!
```

might be stored as...

```
    <FF><40><0>I12345<0>Pneighbor!lifesys!test_user<0>T576918988<0>    (continued)
    -----------|Mesg ID#|--Message Path---------------|--Date------
    
    AThe Test User<0>ONetwork Test<0>Nlifesys<0>HLife Central<0>MHave a nice day!<0>
    |-----Author-----|-Room name-----|-nodename-|Human Name-|--Message text-----
```

Weird things can happen if fields are missing, especially if you use the
networker.  But basically, the date, author, room, and nodename may be in any
order.  But the leading fields and the message text must remain in the same
place.  The H field looks better when it is placed immediately after the N
field.


EUID (EXCLUSIVE MESSAGE ID'S)
-----------------------------
This is where the groupware magic happens.  Any message in any room may have
a field called the Exclusive message *ID*, or *EUID*.  We keep an index in the
table `CDB_EUIDINDEX` which knows the message number of any item that has an
*EUID*.  This allows us to do two things:
 
 - If a subsequent message arrives with the same *EUID*, it automatically
   *deletes* the existing one, because the new one is considered a replacement
   for the existing one.
 - If we know the *EUID* of the item we're looking for, we can fetch it by *EUID*
   and get the most up-to-date version, even if it's been updated several times.

This functionality is made more useful by server-side hooks.  For example,
when we save a vCard to an address book room, or an iCalendar item to a
calendar room, our server modules detect this condition, and automatically set
the *EUID* of the message to the *UUID* of the *vCard* or *iCalendar* item.
Therefore when you save an updated version of an address book entry or
a calendar item, the old one is automatically deleted.

PORTABILITY ISSUES
------------------
Citadel is 32/64 bit clean and architecture-independent.  The software is
developed and primarily run on the Linux operating system (which uses the
Linux kernel) but it should compile and run on any reasonably POSIX
compliant system.

On the client side, it's also POSIX compliant.  The client even seems to
build ok on non-POSIX systems with porting libraries (such as Cygwin and
WSL).

SUPPORTING PRIVATE MAIL
-----------------------
Can one have an elegant kludge?  This must come pretty close.

Private mail is sent and recieved in the `Mail>` room, which otherwise
behaves pretty much as any other room.        To make this work, we have a
separate Mail> room for each user behind the scenes.  The actual room name
in the database looks like `0000001234.Mail` (where `1234` is the user
number) and it's flagged with the `QR_MAILBOX` flag.  The user number is
stripped off by the server before the name is presented to the client.  This
provides the ability to give each user a separate namespace for mailboxes
and personal rooms.

This requires a little fiddling to get things just right. For example,
`make_message()` has to be kludged to ask for the name of the recipient
of the message whenever a message is entered in `Mail>`. But basically
it works pretty well, keeping the code and user interface simple and
regular.

PASSWORDS AND NAME VALIDATION
-----------------------------
This has changed a couple of times over the course of Citadel's history.  At
this point it's very simple, again due to the fact that record managers are
used for everything.    The user file (user) is indexed using the user's
name, converted to all lower-case.  Searching for a user, then, is easy.  We
just lowercase the name we're looking for and query the database.  If no
match is found, it is assumed that the user does not exist.

This makes it difficult to forge messages from an existing user.  (Fine
point: nonprinting characters are converted to printing characters, and
leading, trailing, and double blanks are deleted.)