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

[James S. <jl...@do...>]

Jeff Squyres wrote:
> James --
> 
> Heh -- gotta love editing on Unix vs. MS.  :-)

Crap...next time I'll use Emacs. cvs diff -w should give you the diff 
without whitespace.

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

Whaddaya mean "practically"? :-)

Per the log message, I added the following to msg_ids:
m_reply_to   text,
m_references text,

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

Plus the associated documentation. I think I fixed a typo in the html 
somewhere, but I don't remember what it was.

I limited the indices on the text fields to the first 255 chars due to 
performance concerns. That's fairly easy to tweak as we go forward.

JLS

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