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

[Jeff S. <jsq...@os...>]

James --

Heh -- gotta love editing on Unix vs. MS.  :-)

What were the essential changes that you made, since this diff is
practically useless?


On Thu, 29 May 2003, James Scott wrote:

> 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>Att...
 
[truncated message content]