[maildb-cvs] cvs commit: maildb/libmaildb/db/mysql/doc cr_maildb.sql design.html

[James S. <jls...@us...>]

jlscott3    03/05/29 17:18:01

  Modified:    libmaildb/db/mysql/doc cr_maildb.sql design.html
  Log:
  Added m_reply_to and m_references cols and indices on m_date, m_from,
  m_to and m_subject to msg_ids

Revision  Changes    Path
1.7       +224 -218  maildb/libmaildb/db/mysql/doc/cr_maildb.sql

Index: cr_maildb.sql
===================================================================
RCS file: /cvsroot/maildb/maildb/libmaildb/db/mysql/doc/cr_maildb.sql,v
retrieving revision 1.6
retrieving revision 1.7
diff -c -r1.6 -r1.7
*** cr_maildb.sql	22 May 2003 18:37:49 -0000	1.6
--- cr_maildb.sql	30 May 2003 00:18:00 -0000	1.7
***************
*** 1,218 ****
! -- cr_db.sql - MailDB MySQL database creation
! -- Copyright (c) 2002 MailDB Team
! --
! -- This file is part of the MailDB software package.  For license
! -- information, see the LICENSE file in the top level directory of the
! -- MailDB source distribution.
! --
! -- $Id: cr_maildb.sql,v 1.6 2003/05/22 18:37:49 lweissler Exp $
! -- -------------------------------------------------------
! 
! -- maildb tables
! 
! -- common message data:
! --	hdrs, mime_types
! -- user data:
! --	users, cats, views
! -- message data:
! --	msg_ids, msg_hdrs, msg_owners, msg_attach, msg_orig
! -- logs:
! --	obj_log, msg_log
! -- meta:
! --	config
! 
! -- -------------------------
! -- Common Message Data
! -- -------------------------
! 
! -- hdrs
! -- -> deleted 10/10/2002 in favor of de-normalizing
! 
! -- drop table if exists hdrs;
! -- create table hdrs (
! --   h_id		integer unsigned not null auto_increment,
! --   h_desc	varchar(20),
! --   primary key 	(h_id)
! -- );
! 
! -- mime_types
! 
! drop table if exists mime_types;
! create table mime_types (
!   mt_id		integer unsigned not null auto_increment,
!   mt_desc	varchar(127),
!   primary key	(mt_id),
!   index 	mt_desc_idx (mt_desc)
! );
! 
! 
! -- -------------------------
! -- User Data
! -- -------------------------
! 
! -- users
! 
! drop table if exists users;
! create table users (
!   u_id	 	integer unsigned not null auto_increment,
!   u_desc	varchar(127),
!   primary key	(u_id),
!   index 	u_desc_idx (u_desc)
! );
! 
! -- cats
! 
! drop table if exists cats;
! create table cats (
!   ca_id		integer unsigned not null auto_increment,
!   ca_u_id	integer unsigned not null references users(u_id),
!   ca_desc	varchar(127),
!   ca_parent	integer unsigned references cats(ca_id),
!   primary key 	(ca_id),
!   index		ca_u_id_idx (ca_u_id)
! );
! 
! -- views
! 
! drop table if exists views;
! create table views (
!   vw_id		integer unsigned not null auto_increment,
!   vw_desc	varchar(127),
!   vw_u_id	integer unsigned not null references users(u_id),
!   vw_query	text,
!   primary key	(vw_id),
!   index		vw_u_id_idx (vw_u_id)
! );
! 
! 
! -- -------------------------
! -- Message Data
! -- -------------------------
! 
! -- msg_ids
! 
! drop table if exists msg_ids;
! 
! create table msg_ids (
!   m_id	       integer unsigned not null auto_increment,
!   m_msg_id     varchar(255) not null,
!   m_to	       text,
!   m_cc	       text,
!   m_bcc	       text,
!   m_subject    text,
!   m_date       datetime,
!   m_from       text,
!   m_in_reply_to text,
!   m_sender     text,
!   m_parent_id  integer,
!   m_vw_incl    text,
!   m_vw_excl    text,
!   primary key  (m_id),
!   index	       m_msg_id_idx (m_msg_id)
! ); 
! 
! -- msg_hdrs
! 
! drop table if exists msg_hdrs;
! create table msg_hdrs (
!   mh_id		integer unsigned not null auto_increment,
!   mh_m_id	integer unsigned not null references msg_ids(m_id),
!   mh_key	varchar(63) not null,
!   mh_value	varchar(255),
!   mh_sort_order	integer unsigned,
!   primary key	(mh_id),
!   index		mh_m_id_idx (mh_m_id),
!   index		mh_key_idx (mh_key),
!   fulltext index mh_value_idx (mh_value)
! );
! 
! -- msg_owners
! 
! drop table if exists msg_owners;
! create table msg_owners (
!   mu_id		integer unsigned not null auto_increment,
!   mu_m_id	integer unsigned not null references msg_ids(m_id),
!   mu_u_id	integer unsigned not null references users(u_id),
!   mu_ca_id	integer unsigned not null references cats(ca_id),
! --  mu_date	integer unsigned not null references msg_hdrs(mh_id),
! --  mu_from	integer unsigned not null references msg_hdrs(mh_id),
! --  mu_subject	integer unsigned not null references msg_hdrs(mh_id),
!   mu_flags	integer,
!   primary key	(mu_id),
!   index		mu_m_id_idx (mu_m_id),
!   index		mu_u_id_idx (mu_u_id),
!   index		mu_ca_id_idx (mu_ca_id)
! );
! 
! -- msg_attach
! --    ma_in_fs	bool,  \ removed
! --    ma_in_msg	bool,  / 5/22/03
! 
! drop table if exists msg_attach;
! create table msg_attach (
!   ma_id		integer unsigned not null auto_increment,
!   ma_m_id	integer unsigned not null references msg_ids(m_id),
!   ma_mt_id	integer unsigned not null references mime_types(mt_id),
!   ma_encode	varchar(127),
!   ma_body	mediumtext,
!   ma_path	varchar(255),
!   ma_sep_data	varchar(255),
!   ma_sort_order	integer unsigned,  
!   primary key	(ma_id),
!   index		ma_m_id_idx (ma_m_id),
!   fulltext index ma_body_idx (ma_body)
! );
! 
! -- msg_orig
! 
! drop table if exists msg_orig;
! create table msg_orig (
!   mo_id		integer unsigned not null auto_increment,
!   mo_m_id	integer unsigned not null references msg_ids(m_id),
!   mo_text	longtext,
!   primary key	(mo_id),
!   index		mo_m_id_idx (mo_m_id)
! );
! 
! -- -------------------------
! -- Logs
! -- -------------------------
!  
! drop table if exists obj_log;
! create table obj_log (
!   obj_id	integer unsigned not null auto_increment,
!   obj_type	varchar(15),
!   obj_type_id	integer unsigned not null,
!   obj_desc	varchar(127),
!   obj_action	char(1),
!   obj_datetime	timestamp,
!   primary key	(obj_id),
!   index		obj_type_idx (obj_type),
!   index		obj_type_id_idx (obj_type_id),
!   index		obj_desc_idx (obj_desc)
! );
! 
! drop table if exists msg_log;
! create table msg_log (
!   msl_id	integer unsigned not null auto_increment,
!   msl_m_id	integer unsigned not null,
!   msl_u_id	integer unsigned not null,
!   msl_ca_id	integer unsigned not null,
!   msl_action	char(1),
!   msl_datetime	timestamp,
!   primary key	(msl_id),
!   index		msl_m_id_idx (msl_m_id),
!   index		msl_u_id_idx (msl_u_id),
!   index		msl_ca_id_idx (msl_ca_id)
! );
! 
! -- -------------------------
! -- Meta
! -- -------------------------
! 
! drop table if exists config;
! create table config (
!   cf_name	varchar(127) not null,
!   cf_value	varchar(127),
!   primary key	(cf_name)
! );
--- 1,224 ----
! -- cr_db.sql - MailDB MySQL database creation
! -- Copyright (c) 2002 MailDB Team
! --
! -- This file is part of the MailDB software package.  For license
! -- information, see the LICENSE file in the top level directory of the
! -- MailDB source distribution.
! --
! -- $Id: cr_maildb.sql,v 1.7 2003/05/30 00:18:00 jlscott3 Exp $
! -- -------------------------------------------------------
! 
! -- maildb tables
! 
! -- common message data:
! --	hdrs, mime_types
! -- user data:
! --	users, cats, views
! -- message data:
! --	msg_ids, msg_hdrs, msg_owners, msg_attach, msg_orig
! -- logs:
! --	obj_log, msg_log
! -- meta:
! --	config
! 
! -- -------------------------
! -- Common Message Data
! -- -------------------------
! 
! -- hdrs
! -- -> deleted 10/10/2002 in favor of de-normalizing
! 
! -- drop table if exists hdrs;
! -- create table hdrs (
! --   h_id		integer unsigned not null auto_increment,
! --   h_desc	varchar(20),
! --   primary key 	(h_id)
! -- );
! 
! -- mime_types
! 
! drop table if exists mime_types;
! create table mime_types (
!   mt_id		integer unsigned not null auto_increment,
!   mt_desc	varchar(127),
!   primary key	(mt_id),
!   index 	mt_desc_idx (mt_desc)
! );
! 
! 
! -- -------------------------
! -- User Data
! -- -------------------------
! 
! -- users
! 
! drop table if exists users;
! create table users (
!   u_id	 	integer unsigned not null auto_increment,
!   u_desc	varchar(127),
!   primary key	(u_id),
!   index 	u_desc_idx (u_desc)
! );
! 
! -- cats
! 
! drop table if exists cats;
! create table cats (
!   ca_id		integer unsigned not null auto_increment,
!   ca_u_id	integer unsigned not null references users(u_id),
!   ca_desc	varchar(127),
!   ca_parent	integer unsigned references cats(ca_id),
!   primary key 	(ca_id),
!   index		ca_u_id_idx (ca_u_id)
! );
! 
! -- views
! 
! drop table if exists views;
! create table views (
!   vw_id		integer unsigned not null auto_increment,
!   vw_desc	varchar(127),
!   vw_u_id	integer unsigned not null references users(u_id),
!   vw_query	text,
!   primary key	(vw_id),
!   index		vw_u_id_idx (vw_u_id)
! );
! 
! 
! -- -------------------------
! -- Message Data
! -- -------------------------
! 
! -- msg_ids
! 
! drop table if exists msg_ids;
! 
! create table msg_ids (
!   m_id	       integer unsigned not null auto_increment,
!   m_msg_id     varchar(255) not null,
!   m_to	       text,
!   m_cc	       text,
!   m_bcc	       text,
!   m_subject    text,
!   m_date       datetime,
!   m_from       text,
!   m_in_reply_to text,
!   m_sender     text,
!   m_reply_to   text,
!   m_references text,
!   m_parent_id  integer,
!   m_vw_incl    text,
!   m_vw_excl    text,
!   primary key  (m_id),
!   index	       m_msg_id_idx (m_msg_id),
!   index        m_date_idx(m_date),
!   index        m_from_idx(m_from(255)),
!   index	       m_to_idx (m_to(255)),
!   index	       m_subject_idx (m_subject(255))
! ); 
! 
! -- msg_hdrs
! 
! drop table if exists msg_hdrs;
! create table msg_hdrs (
!   mh_id		integer unsigned not null auto_increment,
!   mh_m_id	integer unsigned not null references msg_ids(m_id),
!   mh_key	varchar(63) not null,
!   mh_value	varchar(255),
!   mh_sort_order	integer unsigned,
!   primary key	(mh_id),
!   index		mh_m_id_idx (mh_m_id),
!   index		mh_key_idx (mh_key),
!   fulltext index mh_value_idx (mh_value)
! );
! 
! -- msg_owners
! 
! drop table if exists msg_owners;
! create table msg_owners (
!   mu_id		integer unsigned not null auto_increment,
!   mu_m_id	integer unsigned not null references msg_ids(m_id),
!   mu_u_id	integer unsigned not null references users(u_id),
!   mu_ca_id	integer unsigned not null references cats(ca_id),
! --  mu_date	integer unsigned not null references msg_hdrs(mh_id),
! --  mu_from	integer unsigned not null references msg_hdrs(mh_id),
! --  mu_subject	integer unsigned not null references msg_hdrs(mh_id),
!   mu_flags	integer,
!   primary key	(mu_id),
!   index		mu_m_id_idx (mu_m_id),
!   index		mu_u_id_idx (mu_u_id),
!   index		mu_ca_id_idx (mu_ca_id)
! );
! 
! -- msg_attach
! --    ma_in_fs	bool,  \ removed
! --    ma_in_msg	bool,  / 5/22/03
! 
! drop table if exists msg_attach;
! create table msg_attach (
!   ma_id		integer unsigned not null auto_increment,
!   ma_m_id	integer unsigned not null references msg_ids(m_id),
!   ma_mt_id	integer unsigned not null references mime_types(mt_id),
!   ma_encode	varchar(127),
!   ma_body	mediumtext,
!   ma_path	varchar(255),
!   ma_sep_data	varchar(255),
!   ma_sort_order	integer unsigned,  
!   primary key	(ma_id),
!   index		ma_m_id_idx (ma_m_id),
!   fulltext index ma_body_idx (ma_body)
! );
! 
! -- msg_orig
! 
! drop table if exists msg_orig;
! create table msg_orig (
!   mo_id		integer unsigned not null auto_increment,
!   mo_m_id	integer unsigned not null references msg_ids(m_id),
!   mo_text	longtext,
!   primary key	(mo_id),
!   index		mo_m_id_idx (mo_m_id)
! );
! 
! -- -------------------------
! -- Logs
! -- -------------------------
!  
! drop table if exists obj_log;
! create table obj_log (
!   obj_id	integer unsigned not null auto_increment,
!   obj_type	varchar(15),
!   obj_type_id	integer unsigned not null,
!   obj_desc	varchar(127),
!   obj_action	char(1),
!   obj_datetime	timestamp,
!   primary key	(obj_id),
!   index		obj_type_idx (obj_type),
!   index		obj_type_id_idx (obj_type_id),
!   index		obj_desc_idx (obj_desc)
! );
! 
! drop table if exists msg_log;
! create table msg_log (
!   msl_id	integer unsigned not null auto_increment,
!   msl_m_id	integer unsigned not null,
!   msl_u_id	integer unsigned not null,
!   msl_ca_id	integer unsigned not null,
!   msl_action	char(1),
!   msl_datetime	timestamp,
!   primary key	(msl_id),
!   index		msl_m_id_idx (msl_m_id),
!   index		msl_u_id_idx (msl_u_id),
!   index		msl_ca_id_idx (msl_ca_id)
! );
! 
! -- -------------------------
! -- Meta
! -- -------------------------
! 
! drop table if exists config;
! create table config (
!   cf_name	varchar(127) not null,
!   cf_value	varchar(127),
!   primary key	(cf_name)
! );



1.7       +824 -799  maildb/libmaildb/db/mysql/doc/design.html

Index: design.html
===================================================================
RCS file: /cvsroot/maildb/maildb/libmaildb/db/mysql/doc/design.html,v
retrieving revision 1.6
retrieving revision 1.7
diff -c -r1.6 -r1.7
*** design.html	22 May 2003 18:37:49 -0000	1.6
--- design.html	30 May 2003 00:18:00 -0000	1.7
***************
*** 1,799 ****
! <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
! <html>
! <head>
! <!-- saved from url=(0189)http://aves.org:2095/liza+aves.org/neomail.pl?action=viewattachment&sessionid=avesorg-session-0.0982186974297328&message_id=fd02e2f221c24eb3770452d3143b0894&folder=INBOX&attachment_number=1 -->
!   <meta http-equiv="Content-Type"
!  content="text/html; charset=windows-1252">
!   <meta content="MSHTML 5.50.4611.1300" name="GENERATOR">
!   <title>MailDB Design</title>
! </head>
! <body>
! <h1>MailDB Database Design</h1>
! <p><a href="#intro">Introduction</a><br>
! <a href="#tables">Table Descriptions</a><br>
! <a href="#issues">Open Questions/Issues</a><br>
! <a href="#details">Table Details</a><br>
! <a href="#storage">MySQL Storage Notes</a><br>
! </p>
! <p></p>
! <hr><a name="intro"></a>
! <h2>Introduction</h2>
! <p>This document describes the proposed design for the MailDB database. This
! design is a work-in-progress and is subject to change; outstanding  questions/issues
! are listed below in <a
!  href="http://aves.org:2095/liza+aves.org/neomail.pl?action=viewattachment&amp;sessionid=avesorg-session-0.0982186974297328&amp;message_id=fd02e2f221c24eb3770452d3143b0894&amp;folder=INBOX&amp;attachment_number=1#issues">Open
! Questions/Issues</a>. </p>
! <p>This initial design assumes use of MySQL as the backend RDBMS. The design
! is  generally normalized, the notable exception being message 
! header tags  (left of the colon) in the individual header records (which
! are stored in the  msg_hdrs table). In this case the benefits of normalizing
! the data seemed to  be outweighed by the slight increase in complexity of
! queries. [Note: I'm a bit  on the fence on this one...decision not final.
! :-) -- lyw] </p>
! <p>Table summary descriptions are provided in the following section. A detailed
! description of table columns, datatypes, and a brief description/use of columns
! is in <a href="#details">Table  Details</a>, below. The full SQL for table
! creation may be found in accompanying  file <a href="cr_maildb.sql">cr_maildb.sql</a>,
! and  sample queries are provided in accompanying file <a
!  href="queries.sql">queries.sql</a>. </p>
! <hr><a name="tables"></a>
! <h2>Table Descriptions</h2>
! <p>There are four basic categories of tables: data common to all messages,
! user  meta data, message data, and logs. </p>
! <p>
! <table border="1">
!   <tbody>
!   <tr>
!     <th colspan="6">Common message data</th>
!     </tr>
!   <tr>
!     <th>Table</th>
!     <th>Description</th>
!     <th>Primary key</th>
!     <th>Add'l Indexes</th>
!     <th>References</th>
!     <th>Referenced by</th>
!     </tr>
!   <tr>
!     <td valign="top">mime_types</td>
!     <td valign="top">Normalized table of mime_type definitions.</td>
!     <td valign="top">Mime type numeric identifier (mt_id)</td>
!     <td valign="top">Mime type (mt_desc)</td>
!     <td valign="top">n/a</td>
!     <td valign="top">msg_attach</td>
!     </tr>
!   <tr>
!     <th colspan="6">User meta data</th>
!     </tr>
!   <tr>
!     <th>Table</th>
!     <th>Description</th>
!     <th>Primary key</th>
!     <th>Add'l Indexes</th>
!     <th>References</th>
!     <th>Referenced by</th>
!     </tr>
!   <tr>
!     <td valign="top">users</td>
!     <td valign="top">Normalized table of MailDB users.</td>
!     <td valign="top">User numeric identifier (u_id)</td>
!     <td valign="top">User name (u_desc)</td>
!     <td valign="top">n/a</td>
!     <td valign="top">cats<br>
! views<br>
! msg_owners</td>
!     </tr>
!   <tr>
!     <td valign="top">cats</td>
!     <td valign="top">Message categories (i.e. "folders") created by users,
! to        which messages are assigned. Categories may be [multiply] nested,
! and        messages may assigned to multiple categories.</td>
!     <td valign="top">Category numeric identifier (ca_id)</td>
!     <td valign="top">Category owner (ca_u_id)</td>
!     <td valign="top">n/a</td>
!     <td valign="top">msg_owners</td>
!   </tr>
!     <tr>
!   </tr>
!     <tr>
!     <td valign="top">views</td>
!     <td valign="top">User views of messages...specifically, defined search
!       criteria and the SQL required to execute the searches.</td>
!     <td valign="top">View numeric identifier (vw_id)</td>
!     <td valign="top">View owner (vw_u_id)</td>
!     <td valign="top">users</td>
!     <td valign="top">n/a</td>
!     </tr>
!   <tr>
!     <th colspan="6">Message data</th>
!     </tr>
!   <tr>
!     <th>Table</th>
!     <th>Description</th>
!     <th>Primary key</th>
!     <th>Add'l Indexes</th>
!     <th>References</th>
!     <th>Referenced by</th>
!     </tr>
!   <tr>
!       <td valign="top">msg_ids<br>
!       </td>
!       <td valign="top">Semi-normalized table of message ids.  Also contains
! "well-known" message headers -- including to, cc, bcc, subject, date,
! from, in-reply-to, sender.  (Note all header lines are also stored in
! msg_hdrs.)<br>
!       </td>
!       <td valign="top">Numeric message record identifier (m_id)<br>
!       </td>
!       <td valign="top">Message id (m_msg_id)<br>
!       </td>
!       <td valign="top">n/a<br>
!       </td>
!       <td valign="top">msg_hdrs, msg_owners, msg_attach, msg_log<br>
!       </td>
!     </tr>
!     <tr>
!     <td valign="top">msg_hdrs</td>
!     <td valign="top">Message header information for a given message. Message
!       header sort order is maintained. Multiple records will exist for each
!       message, one per unique header line.      </td>
!       <td valign="top">Numeric message header record identifier (mh_id)</td>
!     <td valign="top">Normalized message id (mh_m_id), header key (mh_key),
! header value (mh_value) [fulltext index]</td>
!     <td valign="top">n/a</td>
!     <td valign="top">msg_owners</td>
!     </tr>
!   <tr>
!     <td valign="top">msg_owners</td>
!     <td valign="top">Records describing user instances (category filing)
! of        specific messages. Users may assign messages to one or more categories,
!       thus multiple records may exist in the table for a given message &amp;
!       user. Includes references into msg_hdrs table for faster retrieval
! of        message date, sender, subject for category "scans".</td>
!     <td valign="top">Message instance numeric identifier (mu_id).</td>
!     <td valign="top">Normalized message id (mu_m_id), user (mu_u_id), category
!     (mu_ca_id)</td>
!     <td valign="top">users, cats, msg_hdrs</td>
!     <td valign="top">n/a</td>
!     </tr>
!   <tr>
!     <td valign="top">msg_attach</td>
!     <td valign="top">Attachments to a given message. Message "body" not in
! an        attachment is treated within the database as attachment 0. Attachment
! sort        order is maintained. Records include mime type, file encoding,
! multi-part        separator data, flag for attachment storage (0 = internal,
! in the        database; 1 = external, in the filesystem).</td>
!     <td valign="top">Message attachment numeric identifier (ma_id).</td>
!     <td valign="top">Normalized message id (ma_m_id), body (ma_body) [fulltext
! index]</td>
!     <td valign="top">mime_types</td>
!     <td valign="top">n/a</td>
!     </tr>
!   <tr>
!     <th colspan="6">Logging</th>
!     </tr>
!   <tr>
!     <th>Table</th>
!     <th>Description</th>
!     <th>Primary key</th>
!     <th>Add'l Indexes</th>
!     <th>References</th>
!     <th>Referenced by</th>
!     </tr>
!   <tr>
!     <td valign="top">obj_log</td>
!     <td valign="top">History of actions on meta objects - namely, create,
!       update, delete of users, categories, views, mime_types. To be maintained
!       via trigger (to be defined).</td>
!     <td valign="top">Numeric log record identifier (obj_id)</td>
!     <td valign="top">Object type (obj_type), object identifier (obj_type_id),
!       object description (obj_desc)</td>
!     <td valign="top">n/a</td>
!     <td valign="top">n/a</td>
!     </tr>
!   <tr>
!     <td valign="top">msg_log</td>
!     <td valign="top">History of message activity - user adds, deletes from
!       categories. To be maintained via trigger (to be defined).</td>
!     <td valign="top">Numeric log record identifier (msl_id).</td>
!     <td valign="top">Normalized message id (msl_m_id), user (msl_u_id), category
!       (msl_ca_id)</td>
!     <td valign="top">n/a</td>
!     <td valign="top">n/a</td>
!     </tr>
!   </tbody>
! </table>
! </p>
! <hr><a name="issues"></a>
! <h2>Open Questions/Issues</h2>
! <ol>
!   <li>Design issues    
!     <ol type="a">
!     <li>Review all field sizing, especially msg_id and record identifiers.
!     </li>
!       <li>Should header info be normalized? Not done so above.      </li>
!       <li>Should users or mime_types be de-normalized?      </li>
!     </ol>
!   </li>
!   <li>MySQL specifics    
!     <ol type="a">
!     <li>Triggers for maintaining log (history) tables.  <b>Update: No trigger
! 	or stored procedure support in MySQL.  Estimate is that these items will
! 	be included in version 5.0.  Current stable MySQL is 3.23.x and 4.x is 	now
! available.</b>    </li>
!       <li>Check MySQL behavior when auto_incremented column "rolls over".
!     </li>
!       <li>Review MySQL regex pattern matching.      </li>
!       <li>Note vanilla MySQL has no true foreign key constraints. You can
! define      the foreign keys (foo integer references bar(foo)) but they aren't
! enforced.      </li>
!     </ol>
!   </li>
!   <li>Message questions    
!     <ol type="a">
!     <li>Multi-part separator data format?      </li>
!       <li>Which header line is used for message threading? Should it be included
!     in msg_owners table for quick category scanning? </li>
!     </ol>
!   </li>
!   <li>Miscellaneous    
!     <ol type="a">
!     <li>Construct and test sample queries... </li>
!     </ol>
!   </li>
! </ol>
! <hr><a name="details"></a>
! <h2>Table Details</h2>
! <table border="1">
!   <tbody>
!   <tr>
!     <th>Table</th>
!     <th>Column</th>
!     <th>Datatype</th>
!     <th>Primary Key</th>
!     <th>Indexed</th>
!     <th>Description</th>
!     </tr>
!   <tr>
!     <td valign="top" rowspan="2">mime_types</td>
!     <td>mt_id</td>
!     <td>integer unsigned not null auto_increment</td>
!     <td>X</td>
!     <td>X</td>
!     <td>Unique identifier for mime_type record</td>
!     </tr>
!   <tr>
!     <td>mt_desc</td>
!     <td>varchar(127)</td>
!     <td> </td>
!     <td>X</td>
!     <td>Message body/attachment MIME type</td>
!     </tr>
!   <tr>
!     <td valign="top" rowspan="2">users</td>
!     <td>u_id</td>
!     <td>integer unsigned not null auto_increment</td>
!     <td>X</td>
!     <td>X</td>
!     <td>Unique identifier for user record</td>
!     </tr>
!   <tr>
!     <td>u_desc</td>
!     <td>varchar(127)</td>
!     <td> </td>
!     <td>X</td>
!     <td>User description (login/reference name, e.g. 'weissler' or      
!  'li...@av...'</td>
!     </tr>
!   <tr>
!     <td valign="top" rowspan="4">cats</td>
!     <td>ca_id</td>
!     <td>integer unsigned not null auto_increment</td>
!     <td>X</td>
!     <td>X</td>
!     <td>Unique identifier for category record</td>
!     </tr>
!   <tr>
!     <td>ca_u_id</td>
!     <td>integer unsigned not null</td>
!     <td> </td>
!     <td>X</td>
!     <td>User owning this category. References users(u_id).</td>
!     </tr>
!   <tr>
!     <td>ca_desc</td>
!     <td>varchar(127)</td>
!     <td> </td>
!     <td> </td>
!     <td>Category name, e.g. "Inbox", "Deleted", "Cycling"...</td>
!     </tr>
!   <tr>
!     <td>ca_parent</td>
!     <td>integer unsigned</td>
!     <td> </td>
!     <td> </td>
!     <td>"Parent" category, allowing nesting of categories. Top-level    
!    categories have null parents. References cats(ca_id).</td>
!     </tr>
!   <tr>
!     <td valign="top" rowspan="4">views</td>
!     <td>vw_id</td>
!     <td>integer unsigned not null auto_increment</td>
!     <td>X</td>
!     <td>X</td>
!     <td>Unique identifier for view record</td>
!     </tr>
!   <tr>
!     <td>vw_desc</td>
!     <td>varchar(127)</td>
!     <td> </td>
!     <td> </td>
!     <td>View name, e.g. "Messages from Robert"</td>
!     </tr>
!   <tr>
!     <td>vw_u_id</td>
!     <td>integer unsigned not null</td>
!     <td> </td>
!     <td>X</td>
!     <td>User owning view. References users(u_id).</td>
!     </tr>
!   <tr>
!     <td>vw_query</td>
!     <td>text</td>
!     <td> </td>
!     <td> </td>
!     <td>SQL required to retrieve messages meeting user's search criteria</td>
!     </tr>
!   <tr>
!       <td valign="top" rowspan="13">msg_ids<br>
!       </td>
!       <td valign="top">m_id<br>
!       </td>
!       <td valign="top">integer unsigned not null auto_increment<br>
!       </td>
!       <td valign="top">X<br>
!       </td>
!       <td valign="top">X<br>
!       </td>
!       <td valign="top">Unique identifier for message id (using less storage
! than the string message_id's in messages)<br>
!       </td>
!     </tr>
!     <tr>
!       <td valign="top">m_msg_id<br>
!       </td>
!       <td valign="top">varchar(255)<br>
!       </td>
!       <td valign="top"><br>
!       </td>
!       <td valign="top">X<br>
!       </td>
!       <td valign="top">Message identifier (assigned by MTA)<br>
!       </td>
!     </tr>
!     <tr>
!       <td valign="top">m_to<br>
!       </td>
!       <td valign="top">text<br>
!       </td>
!       <td valign="top"><br>
!       </td>
!       <td valign="top"><br>
!       </td>
!       <td valign="top">To header line contents<br>
!       </td>
!     </tr>
!     <tr>
!       <td valign="top">m_cc<br>
!       </td>
!       <td valign="top">text<br>
!       </td>
!       <td valign="top"><br>
!       </td>
!       <td valign="top"><br>
!       </td>
!       <td valign="top">CC header line contents<br>
!       </td>
!     </tr>
!     <tr>
!       <td valign="top">m_bcc<br>
!       </td>
!       <td valign="top">text<br>
!       </td>
!       <td valign="top"><br>
!       </td>
!       <td valign="top"><br>
!       </td>
!       <td valign="top">BCC header line contents (outgoing msgs only)<br>
!       </td>
!     </tr>
!     <tr>
!       <td valign="top">m_subject<br>
!       </td>
!       <td valign="top">text<br>
!       </td>
!       <td valign="top"><br>
!       </td>
!       <td valign="top"><br>
!       </td>
!       <td valign="top">Subject header line contents<br>
!       </td>
!     </tr>
!     <tr>
!       <td valign="top">m_date<br>
!       </td>
!       <td valign="top">text<br>
!       </td>
!       <td valign="top"><br>
!       </td>
!       <td valign="top"><br>
!       </td>
!       <td valign="top">Date header line contents<br>
!       </td>
!     </tr>
!     <tr>
!       <td valign="top">m_from<br>
!       </td>
!       <td valign="top">text<br>
!       </td>
!       <td valign="top"><br>
!       </td>
!       <td valign="top"><br>
!       </td>
!       <td valign="top">From header line contents<br>
!       </td>
!     </tr>
!     <tr>
!       <td valign="top">m_in_reply_to<br>
!       </td>
!       <td valign="top">text<br>
!       </td>
!       <td valign="top"><br>
!       </td>
!       <td valign="top"><br>
!       </td>
!       <td valign="top">In-Reply-To header line contents<br>
!       </td>
!     </tr>
!     <tr>
!       <td valign="top">m_sender<br>
!       </td>
!       <td valign="top">text<br>
!       </td>
!       <td valign="top"><br>
!       </td>
!       <td valign="top"><br>
!       </td>
!       <td valign="top">Sender header line contents<br>
!       </td>
!     </tr>
!     <tr>
!       <td valign="top">m_parent_id<br>
!       </td>
!       <td valign="top">integer<br>
!       </td>
!       <td valign="top"><br>
!       </td>
!       <td valign="top"><br>
!       </td>
!       <td valign="top">If attachment, m_id of parent message<br>
!       </td>
!     </tr>
!     <tr>
!       <td valign="top">m_vw_incl<br>
!       </td>
!       <td valign="top">text<br>
!       </td>
!       <td valign="top"><br>
!       </td>
!       <td valign="top"><br>
!       </td>
!       <td valign="top">Specify views in which to include this message<br>
!       </td>
!     </tr>
!     <tr>
!       <td valign="top">m_vw_excl<br>
!       </td>
!       <td valign="top">text<br>
!       </td>
!       <td valign="top"><br>
!       </td>
!       <td valign="top"><br>
!       </td>
!       <td valign="top">Specify views from which to exclude this message<br>
!       </td>
!     </tr>
!     <tr>
!     <td valign="top" rowspan="5">msg_hdrs</td>
!     <td>mh_id</td>
!     <td>integer unsigned not null auto_increment</td>
!     <td>X</td>
!     <td>X</td>
!     <td>Unique identifier for message header record</td>
!     </tr>
!   <tr>
!     <td>mh_m_id</td>
!     <td>integer unsigned not null</td>
!     <td> </td>
!     <td>X</td>
!     <td>Message identifier</td>
!     </tr>
!   <tr>
!     <td>mh_key</td>
!     <td>varchar(63)</td>
!     <td> </td>
!     <td>X</td>
!     <td>Header tag - e.g. "To", "From", "Subject"...anything left of colon
! (:)        on header line. </td>
!   </tr>
!     <tr>
!     <td>mh_value</td>
!     <td>varchar(255)</td>
!     <td> </td>
!     <td>X</td>
!     <td>Header value - anything right of colon (:) on header line.  Fulltext
! index.</td>
!     </tr>
!   <tr>
!     <td>mh_sort_order</td>
!     <td>integer unsigned not null</td>
!     <td> </td>
!     <td> </td>
!     <td>Header sequence number.</td>
!     </tr>
!   <tr>
!     <td valign="top" rowspan="5">msg_owners</td>
!     <td>mu_id</td>
!     <td>integer unsigned not null auto_increment</td>
!     <td>X</td>
!     <td>X</td>
!     <td>Unique identifier for message instance record.</td>
!     </tr>
!   <tr>
!     <td>mu_m_id</td>
!     <td>integer unsigned not null</td>
!     <td> </td>
!     <td>X</td>
!     <td>Message identifier.</td>
!     </tr>
!   <tr>
!     <td>mu_u_id</td>
!     <td>integer unsigned not null</td>
!     <td> </td>
!     <td>X</td>
!     <td>User owning a copy of the message.</td>
!     </tr>
!   <tr>
!     <td>mu_ca_id</td>
!     <td>integer unsigned not null</td>
!     <td> </td>
!     <td>X</td>
!     <td>Category to which the user assigned the message.</td>
!     </tr>
!   <tr>
!     <td>mu_flags</td>
!     <td>integer unsigned</td>
!     <td> </td>
!     <td> </td>
!     <td>Bit flags for the message.</td>
!     </tr>
!   <tr>
!     <td valign="top" rowspan="8">msg_attach</td>
!     <td>ma_id</td>
!     <td>integer unsigned not null auto_increment</td>
!     <td>X</td>
!     <td>X</td>
!     <td>Unique identifier for message attachment record.</td>
!     </tr>
!   <tr>
!     <td>ma_m_id</td>
!     <td>integer unsigned not null</td>
!     <td> </td>
!     <td>X</td>
!     <td>Message identifier.</td>
!     </tr>
!   <tr>
!     <td>ma_mt_id</td>
!     <td>integer unsigned not null</td>
!     <td> </td>
!     <td> </td>
!     <td>Foreign key ref to mime_types(mt_id)</td>
!     </tr>
!   <tr>
!     <td>ma_encode</td>
!     <td>varchar(127)</td>
!     <td> </td>
!     <td> </td>
!     <td>Attachment encoding</td>
!     </tr>
!   <tr>
!     <td>ma_body</td>
!     <td>mediumtext</td>
!     <td> </td>
!     <td>X</td>
!     <td>Attachment body (for attachments stored within the database).  Fulltext
! index.</td>
!     </tr>
!   <tr>
!     <td>ma_path</td>
!     <td>varchar(255)</td>
!     <td> </td>
!     <td> </td>
!     <td>Full pathname to attachment (for attachments stored in the      filesystem).</td>
!     </tr>
!   <tr>
!     <td>ma_sep_data</td>
!     <td>varchar(255)</td>
!     <td> </td>
!     <td> </td>
!     <td>Multi-part separator data.</td>
!     </tr>
!   <tr>
!     <td>ma_sort_order</td>
!     <td>integer unsigned not null</td>
!     <td> </td>
!     <td> </td>
!     <td>Attachment sequence number.</td>
!     </tr>
!   <tr>
!     <td valign="top" rowspan="6">obj_log</td>
!     <td>obj_id</td>
!     <td>integer unsigned not null auto_increment</td>
!     <td>X</td>
!     <td>X</td>
!     <td>Unique identifier for object log record.</td>
!     </tr>
!   <tr>
!     <td>obj_type</td>
!     <td>varchar(15)</td>
!     <td> </td>
!     <td>X</td>
!     <td>Type of object - e.g. user, view, cat, mime_type</td>
!     </tr>
!   <tr>
!     <td>obj_type_id</td>
!     <td>integer unsigned not null</td>
!     <td> </td>
!     <td>X</td>
!     <td>Object identifier; if obj_type = user, obj_type_id corresponds to
!       users(u_id), obj_type = cat, obj_type_id corresponds to cats(ca_id),
!     etc.</td>
!     </tr>
!   <tr>
!     <td>obj_desc</td>
!     <td>varchar(127)</td>
!     <td> </td>
!     <td>X</td>
!     <td>Object description (username, viewname, category name...)</td>
!     </tr>
!   <tr>
!     <td>obj_action</td>
!     <td>char(1)</td>
!     <td> </td>
!     <td> </td>
!     <td>Action taken on this object. Values include C (create), U (update),
! D        (delete).</td>
!     </tr>
!   <tr>
!     <td>obj_datetime</td>
!     <td>timestamp(14)</td>
!     <td> </td>
!     <td> </td>
!     <td>Timestamp of object action.</td>
!     </tr>
!   <tr>
!     <td valign="top" rowspan="6">msg_log</td>
!     <td>msl_id</td>
!     <td>integer unsigned not null auto_increment</td>
!     <td>X</td>
!     <td>X</td>
!     <td>Unique identifier for message log record.</td>
!     </tr>
!   <tr>
!     <td>msl_m_id</td>
!     <td>integer unsigned not null</td>
!     <td> </td>
!     <td>X</td>
!     <td>Message identifier.</td>
!     </tr>
!   <tr>
!     <td>msl_u_id</td>
!     <td>integer unsigned not null</td>
!     <td> </td>
!     <td>X</td>
!     <td>User owning instance of the specified message.</td>
!     </tr>
!   <tr>
!     <td>msl_ca_id</td>
!     <td>integer unsigned not null</td>
!     <td> </td>
!     <td>X</td>
!     <td>Category to which the user assigned the message.</td>
!     </tr>
!   <tr>
!     <td>msl_action</td>
!     <td>char(1)</td>
!     <td> </td>
!     <td>X</td>
!     <td>Action taken on this message. Values include C (create), U (update),
! D        (delete).</td>
!     </tr>
!   <tr>
!     <td>msl_datetime</td>
!     <td>timestamp(14)</td>
!     <td> </td>
!     <td> </td>
!     <td>Timestamp of message action.</td>
!     </tr>
!   </tbody>
! </table>
! <p></p>
! <hr><a name="storage"></a>
! <h2>MySQL Storage Notes</h2>
! <p>
! <table border="1">
!   <tbody>
!     <tr>
!       <th>Datatype</th>
!       <th>Max</th>
!       <th>Storage</th>
!     </tr>
!     <tr>
!       <td>varchar</td>
!       <td>255</td>
!       <td>L+1</td>
!     </tr>
!     <tr>
!       <td>tinytext</td>
!       <td>255</td>
!       <td>L+1</td>
!     </tr>
!     <tr>
!       <td>text</td>
!       <td>64k</td>
!       <td>L+2</td>
!     </tr>
!     <tr>
!       <td>mediumtext</td>
!       <td>16m</td>
!       <td>L+3</td>
!     </tr>
!     <tr>
!       <td>longtext</td>
!       <td>4g</td>
!       <td>L+4</td>
!     </tr>
!     <tr>
!       <td>tinyint (U)</td>
!       <td>255</td>
!       <td>1</td>
!     </tr>
!     <tr>
!       <td>smallint (U)</td>
!       <td>65535</td>
!       <td>2</td>
!     </tr>
!     <tr>
!       <td>mediumint (U)</td>
!       <td>16777215</td>
!       <td>3</td>
!     </tr>
!     <tr>
!       <td>integer (U)</td>
!       <td>4294967295</td>
!       <td>4</td>
!     </tr>
!     <tr>
!       <td>timestamp</td>
!       <td>YYYYMMDDHHMISS</td>
!       <td>4</td>
!     </tr>
!   </tbody>
! </table>
! </p>
! <p>...where (U) = unsigned, and L = length of text string. </p>
! <p></p>
! <hr><i>Last update: 17 October 2002 by <a href="mailto:li...@av...">Liza
! Weissler</a></i><a href="mailto:li...@av..."> </a><br>
! </body>
! </html>
--- 1,824 ----
! <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
! <html>
! <head>
! <!-- saved from url=(0189)http://aves.org:2095/liza+aves.org/neomail.pl?action=viewattachment&sessionid=avesorg-session-0.0982186974297328&message_id=fd02e2f221c24eb3770452d3143b0894&folder=INBOX&attachment_number=1 -->
!   <meta http-equiv="Content-Type"
!  content="text/html; charset=windows-1252">
!   <meta content="MSHTML 5.50.4611.1300" name="GENERATOR">
!   <title>MailDB Design</title>
! </head>
! <body>
! <h1>MailDB Database Design</h1>
! <p><a href="#intro">Introduction</a><br>
! <a href="#tables">Table Descriptions</a><br>
! <a href="#issues">Open Questions/Issues</a><br>
! <a href="#details">Table Details</a><br>
! <a href="#storage">MySQL Storage Notes</a><br>
! </p>
! <p></p>
! <hr><a name="intro"></a>
! <h2>Introduction</h2>
! <p>This document describes the proposed design for the MailDB database. This
! design is a work-in-progress and is subject to change; outstanding  questions/issues
! are listed below in <a
!  href="http://aves.org:2095/liza+aves.org/neomail.pl?action=viewattachment&amp;sessionid=avesorg-session-0.0982186974297328&amp;message_id=fd02e2f221c24eb3770452d3143b0894&amp;folder=INBOX&amp;attachment_number=1#issues">Open
! Questions/Issues</a>. </p>
! <p>This initial design assumes use of MySQL as the backend RDBMS. The design
! is  generally normalized, the notable exception being message 
! header tags  (left of the colon) in the individual header records (which
! are stored in the  msg_hdrs table). In this case the benefits of normalizing
! the data seemed to  be outweighed by the slight increase in complexity of
! queries. [Note: I'm a bit  on the fence on this one...decision not final.
! :-) -- lyw] </p>
! <p>Table summary descriptions are provided in the following section. A detailed
! description of table columns, datatypes, and a brief description/use of columns
! is in <a href="#details">Table  Details</a>, below. The full SQL for table
! creation may be found in accompanying  file <a href="cr_maildb.sql">cr_maildb.sql</a>,
! and  sample queries are provided in accompanying file <a
!  href="queries.sql">queries.sql</a>. </p>
! <hr><a name="tables"></a>
! <h2>Table Descriptions</h2>
! <p>There are four basic categories of tables: data common to all messages,
! user  meta data, message data, and logs. </p>
! <p>
! <table border="1">
!   <tbody>
!   <tr>
!     <th colspan="6">Common message data</th>
!     </tr>
!   <tr>
!     <th>Table</th>
!     <th>Description</th>
!     <th>Primary key</th>
!     <th>Add'l Indexes</th>
!     <th>References</th>
!     <th>Referenced by</th>
!     </tr>
!   <tr>
!     <td valign="top">mime_types</td>
!     <td valign="top">Normalized table of mime_type definitions.</td>
!     <td valign="top">Mime type numeric identifier (mt_id)</td>
!     <td valign="top">Mime type (mt_desc)</td>
!     <td valign="top">n/a</td>
!     <td valign="top">msg_attach</td>
!     </tr>
!   <tr>
!     <th colspan="6">User meta data</th>
!     </tr>
!   <tr>
!     <th>Table</th>
!     <th>Description</th>
!     <th>Primary key</th>
!     <th>Add'l Indexes</th>
!     <th>References</th>
!     <th>Referenced by</th>
!     </tr>
!   <tr>
!     <td valign="top">users</td>
!     <td valign="top">Normalized table of MailDB users.</td>
!     <td valign="top">User numeric identifier (u_id)</td>
!     <td valign="top">User name (u_desc)</td>
!     <td valign="top">n/a</td>
!     <td valign="top">cats<br>
! views<br>
! msg_owners</td>
!     </tr>
!   <tr>
!     <td valign="top">cats</td>
!     <td valign="top">Message categories (i.e. "folders") created by users,
! to        which messages are assigned. Categories may be [multiply] nested,
! and        messages may assigned to multiple categories.</td>
!     <td valign="top">Category numeric identifier (ca_id)</td>
!     <td valign="top">Category owner (ca_u_id)</td>
!     <td valign="top">n/a</td>
!     <td valign="top">msg_owners</td>
!   </tr>
!     <tr>
!   </tr>
!     <tr>
!     <td valign="top">views</td>
!     <td valign="top">User views of messages...specifically, defined search
!       criteria and the SQL required to execute the searches.</td>
!     <td valign="top">View numeric identifier (vw_id)</td>
!     <td valign="top">View owner (vw_u_id)</td>
!     <td valign="top">users</td>
!     <td valign="top">n/a</td>
!     </tr>
!   <tr>
!     <th colspan="6">Message data</th>
!     </tr>
!   <tr>
!     <th>Table</th>
!     <th>Description</th>
!     <th>Primary key</th>
!     <th>Add'l Indexes</th>
!     <th>References</th>
!     <th>Referenced by</th>
!     </tr>
!   <tr>
!       <td valign="top">msg_ids<br>
!       </td>
!       <td valign="top">Semi-normalized table of message ids.  Also contains
! "well-known" message headers -- including to, cc, bcc, subject, date,
! from, in-reply-to, sender.  (Note all header lines are also stored in
! msg_hdrs.)<br>
!       </td>
!       <td valign="top">Numeric message record identifier (m_id)<br>
!       </td>
!       <td valign="top">Message id (m_msg_id)<br>Date (m_date)<br>
!                        From (m_from)<br>To (m_to)<br>Subject (m_subject)
!       </td>
!       <td valign="top">n/a<br>
!       </td>
!       <td valign="top">msg_hdrs, msg_owners, msg_attach, msg_log<br>
!       </td>
!     </tr>
!     <tr>
!     <td valign="top">msg_hdrs</td>
!     <td valign="top">Message header information for a given message. Message
!       header sort order is maintained. Multiple records will exist for each
!       message, one per unique header line.      </td>
!       <td valign="top">Numeric message header record identifier (mh_id)</td>
!     <td valign="top">Normalized message id (mh_m_id), header key (mh_key),
! header value (mh_value) [fulltext index]</td>
!     <td valign="top">n/a</td>
!     <td valign="top">msg_owners</td>
!     </tr>
!   <tr>
!     <td valign="top">msg_owners</td>
!     <td valign="top">Records describing user instances (category filing)
! of        specific messages. Users may assign messages to one or more categories,
!       thus multiple records may exist in the table for a given message &amp;
!       user. Includes references into msg_hdrs table for faster retrieval
! of        message date, sender, subject for category "scans".</td>
!     <td valign="top">Message instance numeric identifier (mu_id).</td>
!     <td valign="top">Normalized message id (mu_m_id), user (mu_u_id), category
!     (mu_ca_id)</td>
!     <td valign="top">users, cats, msg_hdrs</td>
!     <td valign="top">n/a</td>
!     </tr>
!   <tr>
!     <td valign="top">msg_attach</td>
!     <td valign="top">Attachments to a given message. Message "body" not in
! an        attachment is treated within the database as attachment 0. Attachment
! sort        order is maintained. Records include mime type, file encoding,
! multi-part        separator data, flag for attachment storage (0 = internal,
! in the        database; 1 = external, in the filesystem).</td>
!     <td valign="top">Message attachment numeric identifier (ma_id).</td>
!     <td valign="top">Normalized message id (ma_m_id), body (ma_body) [fulltext
! index]</td>
!     <td valign="top">mime_types</td>
!     <td valign="top">n/a</td>
!     </tr>
!   <tr>
!     <th colspan="6">Logging</th>
!     </tr>
!   <tr>
!     <th>Table</th>
!     <th>Description</th>
!     <th>Primary key</th>
!     <th>Add'l Indexes</th>
!     <th>References</th>
!     <th>Referenced by</th>
!     </tr>
!   <tr>
!     <td valign="top">obj_log</td>
!     <td valign="top">History of actions on meta objects - namely, create,
!       update, delete of users, categories, views, mime_types. To be maintained
!       via trigger (to be defined).</td>
!     <td valign="top">Numeric log record identifier (obj_id)</td>
!     <td valign="top">Object type (obj_type), object identifier (obj_type_id),
!       object description (obj_desc)</td>
!     <td valign="top">n/a</td>
!     <td valign="top">n/a</td>
!     </tr>
!   <tr>
!     <td valign="top">msg_log</td>
!     <td valign="top">History of message activity - user adds, deletes from
!       categories. To be maintained via trigger (to be defined).</td>
!     <td valign="top">Numeric log record identifier (msl_id).</td>
!     <td valign="top">Normalized message id (msl_m_id), user (msl_u_id), category
!       (msl_ca_id)</td>
!     <td valign="top">n/a</td>
!     <td valign="top">n/a</td>
!     </tr>
!   </tbody>
! </table>
! </p>
! <hr><a name="issues"></a>
! <h2>Open Questions/Issues</h2>
! <ol>
!   <li>Design issues    
!     <ol type="a">
!     <li>Review all field sizing, especially msg_id and record identifiers.
!     </li>
!       <li>Should header info be normalized? Not done so above.      </li>
!       <li>Should users or mime_types be de-normalized?      </li>
!     </ol>
!   </li>
!   <li>MySQL specifics    
!     <ol type="a">
!     <li>Triggers for maintaining log (history) tables.  <b>Update: No trigger
! 	or stored procedure support in MySQL.  Estimate is that these items will
! 	be included in version 5.0.  Current stable MySQL is 3.23.x and 4.x is 	now
! available.</b>    </li>
!       <li>Check MySQL behavior when auto_incremented column "rolls over".
!     </li>
!       <li>Review MySQL regex pattern matching.      </li>
!       <li>Note vanilla MySQL has no true foreign key constraints. You can
! define      the foreign keys (foo integer references bar(foo)) but they aren't
! enforced.      </li>
!     </ol>
!   </li>
!   <li>Message questions    
!     <ol type="a">
!     <li>Multi-part separator data format?      </li>
!       <li>Which header line is used for message threading? Should it be included
!     in msg_owners table for quick category scanning? </li>
!     </ol>
!   </li>
!   <li>Miscellaneous    
!     <ol type="a">
!     <li>Construct and test sample queries... </li>
!     </ol>
!   </li>
! </ol>
! <hr><a name="details"></a>
! <h2>Table Details</h2>
! <table border="1">
!   <tbody>
!   <tr>
!     <th>Table</th>
!     <th>Column</th>
!     <th>Datatype</th>
!     <th>Primary Key</th>
!     <th>Indexed</th>
!     <th>Description</th>
!     </tr>
!   <tr>
!     <td valign="top" rowspan="2">mime_types</td>
!     <td>mt_id</td>
!     <td>integer unsigned not null auto_increment</td>
!     <td>X</td>
!     <td>X</td>
!     <td>Unique identifier for mime_type record</td>
!     </tr>
!   <tr>
!     <td>mt_desc</td>
!     <td>varchar(127)</td>
!     <td> </td>
!     <td>X</td>
!     <td>Message body/attachment MIME type</td>
!     </tr>
!   <tr>
!     <td valign="top" rowspan="2">users</td>
!     <td>u_id</td>
!     <td>integer unsigned not null auto_increment</td>
!     <td>X</td>
!     <td>X</td>
!     <td>Unique identifier for user record</td>
!     </tr>
!   <tr>
!     <td>u_desc</td>
!     <td>varchar(127)</td>
!     <td> </td>
!     <td>X</td>
!     <td>User description (login/reference name, e.g. 'weissler' or      
!  'li...@av...'</td>
!     </tr>
!   <tr>
!     <td valign="top" rowspan="4">cats</td>
!     <td>ca_id</td>
!     <td>integer unsigned not null auto_increment</td>
!     <td>X</td>
!     <td>X</td>
!     <td>Unique identifier for category record</td>
!     </tr>
!   <tr>
!     <td>ca_u_id</td>
!     <td>integer unsigned not null</td>
!     <td> </td>
!     <td>X</td>
!     <td>User owning this category. References users(u_id).</td>
!     </tr>
!   <tr>
!     <td>ca_desc</td>
!     <td>varchar(127)</td>
!     <td> </td>
!     <td> </td>
!     <td>Category name, e.g. "Inbox", "Deleted", "Cycling"...</td>
!     </tr>
!   <tr>
!     <td>ca_parent</td>
!     <td>integer unsigned</td>
!     <td> </td>
!     <td> </td>
!     <td>"Parent" category, allowing nesting of categories. Top-level    
!    categories have null parents. References cats(ca_id).</td>
!     </tr>
!   <tr>
!     <td valign="top" rowspan="4">views</td>
!     <td>vw_id</td>
!     <td>integer unsigned not null auto_increment</td>
!     <td>X</td>
!     <td>X</td>
!     <td>Unique identifier for view record</td>
!     </tr>
!   <tr>
!     <td>vw_desc</td>
!     <td>varchar(127)</td>
!     <td> </td>
!     <td> </td>
!     <td>View name, e.g. "Messages from Robert"</td>
!     </tr>
!   <tr>
!     <td>vw_u_id</td>
!     <td>integer unsigned not null</td>
!     <td> </td>
!     <td>X</td>
!     <td>User owning view. References users(u_id).</td>
!     </tr>
!   <tr>
!     <td>vw_query</td>
!     <td>text</td>
!     <td> </td>
!     <td> </td>
!     <td>SQL required to retrieve messages meeting user's search criteria</td>
!     </tr>
!   <tr>
!       <td valign="top" rowspan="13">msg_ids<br>
!       </td>
!       <td valign="top">m_id<br>
!       </td>
!       <td valign="top">integer unsigned not null auto_increment<br>
!       </td>
!       <td valign="top">X<br>
!       </td>
!       <td valign="top">X<br>
!       </td>
!       <td valign="top">Unique identifier for message id (using less storage
! than the string message_id's in messages)<br>
!       </td>
!     </tr>
!     <tr>
!       <td valign="top">m_msg_id<br>
!       </td>
!       <td valign="top">varchar(255)<br>
!       </td>
!       <td valign="top"><br>
!       </td>
!       <td valign="top">X<br>
!       </td>
!       <td valign="top">Message identifier (assigned by MTA)<br>
!       </td>
!     </tr>
!     <tr>
!       <td valign="top">m_to<br>
!       </td>
!       <td valign="top">text<br>
!       </td>
!       <td valign="top"><br>
!       </td>
!       <td valign="top">X<br>
!       </td>
!       <td valign="top">To header line contents<br>
!       </td>
!     </tr>
!     <tr>
!       <td valign="top">m_cc<br>
!       </td>
!       <td valign="top">text<br>
!       </td>
!       <td valign="top"><br>
!       </td>
!       <td valign="top"><br>
!       </td>
!       <td valign="top">CC header line contents<br>
!       </td>
!     </tr>
!     <tr>
!       <td valign="top">m_bcc<br>
!       </td>
!       <td valign="top">text<br>
!       </td>
!       <td valign="top"><br>
!       </td>
!       <td valign="top"><br>
!       </td>
!       <td valign="top">BCC header line contents (outgoing msgs only)<br>
!       </td>
!     </tr>
!     <tr>
!       <td valign="top">m_subject<br>
!       </td>
!       <td valign="top">text<br>
!       </td>
!       <td valign="top"><br>
!       </td>
!       <td valign="top">X<br>
!       </td>
!       <td valign="top">Subject header line contents<br>
!       </td>
!     </tr>
!     <tr>
!       <td valign="top">m_date<br>
!       </td>
!       <td valign="top">datetime<br>
!       </td>
!       <td valign="top"><br>
!       </td>
!       <td valign="top">X<br>
!       </td>
!       <td valign="top">Date header line contents<br>
!       </td>
!     </tr>
!     <tr>
!       <td valign="top">m_from<br>
!       </td>
!       <td valign="top">text<br>
!       </td>
!       <td valign="top"><br>
!       </td>
!       <td valign="top">X<br>
!       </td>
!       <td valign="top">From header line contents<br>
!       </td>
!     </tr>
!     <tr>
!       <td valign="top">m_in_reply_to<br>
!       </td>
!       <td valign="top">text<br>
!       </td>
!       <td valign="top"><br>
!       </td>
!       <td valign="top"><br>
!       </td>
!       <td valign="top">In-Reply-To header line contents<br>
!       </td>
!     </tr>
!     <tr>
!       <td valign="top">m_sender<br>
!       </td>
!       <td valign="top">text<br>
!       </td>
!       <td valign="top"><br>
!       </td>
!       <td valign="top"><br>
!       </td>
!       <td valign="top">Sender header line contents<br>
!       </td>
!     </tr>
!     <tr>
!       <td valign="top">m_reply_to<br>
!       </td>
!       <td valign="top">text<br>
!       </td>
!       <td valign="top"><br>
!       </td>
!       <td valign="top"><br>
!       </td>
!       <td valign="top">Reply-To header line contents<br>
!       </td>
!     </tr>
!     <tr>
!       <td valign="top">m_references<br>
!       </td>
!       <td valign="top">text<br>
!       </td>
!       <td valign="top"><br>
!       </td>
!       <td valign="top"><br>
!       </td>
!       <td valign="top">References header line contents<br>
!       </td>
!     </tr>
!     <tr>
!       <td valign="top">m_parent_id<br>
!       </td>
!       <td valign="top">integer<br>
!       </td>
!       <td valign="top"><br>
!       </td>
!       <td valign="top"><br>
!       </td>
!       <td valign="top">If attachment, m_id of parent message<br>
!       </td>
!     </tr>
!     <tr>
!       <td valign="top">m_vw_incl<br>
!       </td>
!       <td valign="top">text<br>
!       </td>
!       <td valign="top"><br>
!       </td>
!       <td valign="top"><br>
!       </td>
!       <td valign="top">Specify views in which to include this message<br>
!       </td>
!     </tr>
!     <tr>
!       <td valign="top">m_vw_excl<br>
!       </td>
!       <td valign="top">text<br>
!       </td>
!       <td valign="top"><br>
!       </td>
!       <td valign="top"><br>
!       </td>
!       <td valign="top">Specify views from which to exclude this message<br>
!       </td>
!     </tr>
!     <tr>
!     <td valign="top" rowspan="5">msg_hdrs</td>
!     <td>mh_id</td>
!     <td>integer unsigned not null auto_increment</td>
!     <td>X</td>
!     <td>X</td>
!     <td>Unique identifier for message header record</td>
!     </tr>
!   <tr>
!     <td>mh_m_id</td>
!     <td>integer unsigned not null</td>
!     <td> </td>
!     <td>X</td>
!     <td>Message identifier</td>
!     </tr>
!   <tr>
!     <td>mh_key</td>
!     <td>varchar(63)</td>
!     <td> </td>
!     <td>X</td>
!     <td>Header tag - e.g. "To", "From", "Subject"...anything left of colon
! (:)        on header line. </td>
!   </tr>
!     <tr>
!     <td>mh_value</td>
!     <td>varchar(255)</td>
!     <td> </td>
!     <td>X</td>
!     <td>Header value - anything right of colon (:) on header line.  Fulltext
! index.</td>
!     </tr>
!   <tr>
!     <td>mh_sort_order</td>
!     <td>integer unsigned not null</td>
!     <td> </td>
!     <td> </td>
!     <td>Header sequence number.</td>
!     </tr>
!   <tr>
!     <td valign="top" rowspan="5">msg_owners</td>
!     <td>mu_id</td>
!     <td>integer unsigned not null auto_increment</td>
!     <td>X</td>
!     <td>X</td>
!     <td>Unique identifier for message instance record.</td>
!     </tr>
!   <tr>
!     <td>mu_m_id</td>
!     <td>integer unsigned not null</td>
!     <td> </td>
!     <td>X</td>
!     <td>Message identifier.</td>
!     </tr>
!   <tr>
!     <td>mu_u_id</td>
!     <td>integer unsigned not null</td>
!     <td> </td>
!     <td>X</td>
!     <td>User owning a copy of the message.</td>
!     </tr>
!   <tr>
!     <td>mu_ca_id</td>
!     <td>integer unsigned not null</td>
!     <td> </td>
!     <td>X</td>
!     <td>Category to which the user assigned the message.</td>
!     </tr>
!   <tr>
!     <td>mu_flags</td>
!     <td>integer unsigned</td>
!     <td> </td>
!     <td> </td>
!     <td>Bit flags for the message.</td>
!     </tr>
!   <tr>
!     <td valign="top" rowspan="8">msg_attach</td>
!     <td>ma_id</td>
!     <td>integer unsigned not null auto_increment</td>
!     <td>X</td>
!     <td>X</td>
!     <td>Unique identifier for message attachment record.</td>
!     </tr>
!   <tr>
!     <td>ma_m_id</td>
!     <td>integer unsigned not null</td>
!     <td> </td>
!     <td>X</td>
!     <td>Message identifier.</td>
!     </tr>
!   <tr>
!     <td>ma_mt_id</td>
!     <td>integer unsigned not null</td>
!     <td> </td>
!     <td> </td>
!     <td>Foreign key ref to mime_types(mt_id)</td>
!     </tr>
!   <tr>
!     <td>ma_encode</td>
!     <td>varchar(127)</td>
!     <td> </td>
!     <td> </td>
!     <td>Attachment encoding</td>
!     </tr>
!   <tr>
!     <td>ma_body</td>
!     <td>mediumtext</td>
!     <td> </td>
!     <td>X</td>
!     <td>Attachment body (for attachments stored within the database).  Fulltext
! index.</td>
!     </tr>
!   <tr>
!     <td>ma_path</td>
!     <td>varchar(255)</td>
!     <td> </td>
!     <td> </td>
!     <td>Full pathname to attachment (for attachments stored in the      filesystem).</td>
!     </tr>
!   <tr>
!     <td>ma_sep_data</td>
!     <td>varchar(255)</td>
!     <td> </td>
!     <td> </td>
!     <td>Multi-part separator data.</td>
!     </tr>
!   <tr>
!     <td>ma_sort_order</td>
!     <td>integer unsigned not null</td>
!     <td> </td>
!     <td> </td>
!     <td>Attachment sequence number.</td>
!     </tr>
!   <tr>
!     <td valign="top" rowspan="6">obj_log</td>
!     <td>obj_id</td>
!     <td>integer unsigned not null auto_increment</td>
!     <td>X</td>
!     <td>X</td>
!     <td>Unique identifier for object log record.</td>
!     </tr>
!   <tr>
!     <td>obj_type</td>
!     <td>varchar(15)</td>
!     <td> </td>
!     <td>X</td>
!     <td>Type of object - e.g. user, view, cat, mime_type</td>
!     </tr>
!   <tr>
!     <td>obj_type_id</td>
!     <td>integer unsigned not null</td>
!     <td> </td>
!     <td>X</td>
!     <td>Object identifier; if obj_type = user, obj_type_id corresponds to
!       users(u_id), obj_type = cat, obj_type_id corresponds to cats(ca_id),
!     etc.</td>
!     </tr>
!   <tr>
!     <td>obj_desc</td>
!     <td>varchar(127)</td>
!     <td> </td>
!     <td>X</td>
!     <td>Object description (username, viewname, category name...)</td>
!     </tr>
!   <tr>
!     <td>obj_action</td>
!     <td>char(1)</td>
!     <td> </td>
!     <td> </td>
!     <td>Action taken on this object. Values include C (create), U (update),
! D        (delete).</td>
!     </tr>
!   <tr>
!     <td>obj_datetime</td>
!     <td>timestamp(14)</td>
!     <td> </td>
!     <td> </td>
!     <td>Timestamp of object action.</td>
!     </tr>
!   <tr>
!     <td valign="top" rowspan="6">msg_log</td>
!     <td>msl_id</td>
!     <td>integer unsigned not null auto_increment</td>
!     <td>X</td>
!     <td>X</td>
!     <td>Unique identifier for message log record.</td>
!     </tr>
!   <tr>
!     <td>msl_m_id</td>
!     <td>integer unsigned not null</td>
!     <td> </td>
!     <td>X</td>
!     <td>Message identifier.</td>
!     </tr>
!   <tr>
!     <td>msl_u_id</td>
!     <td>integer unsigned not null</td>
!     <td> </td>
!     <td>X</td>
!     <td>User owning instance of the specified message.</td>
!     </tr>
!   <tr>
!     <td>msl_ca_id</td>
!     <td>integer unsigned not null</td>
!     <td> </td>
!     <td>X</td>
!     <td>Category to which the user assigned the message.</td>
!     </tr>
!   <tr>
!     <td>msl_action</td>
!     <td>char(1)</td>
!     <td> </td>
!     <td>X</td>
!     <td>Action taken on this message. Values include C (create), U (update),
! D        (delete).</td>
!     </tr>
!   <tr>
!     <td>msl_datetime</td>
!     <td>timestamp(14)</td>
!     <td> </td>
!     <td> </td>
!     <td>Timestamp of message action.</td>
!     </tr>
!   </tbody>
! </table>
! <p></p>
! <hr><a name="storage"></a>
! <h2>MySQL Storage Notes</h2>
! <p>
! <table border="1">
!   <tbody>
!     <tr>
!       <th>Datatype</th>
!       <th>Max</th>
!       <th>Storage</th>
!     </tr>
!     <tr>
!       <td>varchar</td>
!       <td>255</td>
!       <td>L+1</td>
!     </tr>
!     <tr>
!       <td>tinytext</td>
!       <td>255</td>
!       <td>L+1</td>
!     </tr>
!     <tr>
!       <td>text</td>
!       <td>64k</td>
!       <td>L+2</td>
!     </tr>
!     <tr...
 
[truncated message content]