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

SourceForge Headquarters 1320 Columbia Street Suite 310 San Diego, CA 92101 +1 (858) 422-6466

lweissler    02/11/05 15:15:48

  Modified:    libmaildb/db/mysql/doc README cr_db.sql cr_maildb.sql
                        design.html message.sql mime.sql queries.sql
                        test.sql users.sql
  Log:
  Added copyright notice to MySQL database files.

Revision  Changes    Path
1.2       +36 -22    maildb/libmaildb/db/mysql/doc/README

Index: README
===================================================================
RCS file: /cvsroot/maildb/maildb/libmaildb/db/mysql/doc/README,v
retrieving revision 1.1
retrieving revision 1.2
diff -c -r1.1 -r1.2
*** README	30 Oct 2002 02:51:46 -0000	1.1
--- README	5 Nov 2002 23:15:46 -0000	1.2
***************
*** 1,22 ****
! 1) If new database, make sure you have a password set for root, and disallow
! local access without a password.  This should do it...
! 
! 	% mysql mysql
! 	> delete from user where host='localhost' and user='';
! 	> quit
! 
! 	% mysqladmin -u root reload
! 	% mysqladmin -u root password <new-password>
! 	% myqladmin -u root -p reload
! 
! 2) Create maildb database
! 
! 	% mysql -u root -p mysql < cr_db.sql   /* provide root pw */
! 	% mysql -u maildb -p maildb < cr_maildb.sql /* provide maildb pw */
! 
! 3) Populate with some sample data
! 	% mysql -u maildb -p maildb
! 	> source mime.sql
! 	> source users.sql
! 	> source message.sql
! 
--- 1,36 ----
! # README
! #
! # 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: README,v 1.2 2002/11/05 23:15:46 lweissler Exp $
! # ---------------------------------------------------------
! 
! 1) If new database, make sure you have a password set for root, and disallow
! local access without a password.  This should do it...
! 
! 	% mysql mysql
! 	> delete from user where host='localhost' and user='';
! 	> quit
! 
! 	% mysqladmin -u root reload
! 	% mysqladmin -u root password <new-password>
! 	% mysqladmin -u root -p reload
! 
! 2) Create maildb database
! 
! 	- Edit cr_db.sql to set an appropriate password for the 
! 	- maildb user (don't use the default...)
! 
! 	% mysql -u root -p mysql < cr_db.sql   /* provide root pw */
! 	% mysql -u maildb -p maildb < cr_maildb.sql /* provide maildb pw */
! 
! 3) Populate with some sample data
! 	% mysql -u maildb -p maildb
! 	> source mime.sql
! 	> source users.sql
! 	> source message.sql
! 


1.2       +22 -11    maildb/libmaildb/db/mysql/doc/cr_db.sql

Index: cr_db.sql
===================================================================
RCS file: /cvsroot/maildb/maildb/libmaildb/db/mysql/doc/cr_db.sql,v
retrieving revision 1.1
retrieving revision 1.2
diff -c -r1.1 -r1.2
*** cr_db.sql	30 Oct 2002 02:51:46 -0000	1.1
--- cr_db.sql	5 Nov 2002 23:15:47 -0000	1.2
***************
*** 1,11 ****
! -- MailDB database creation
! -- 10/18/2002 lyw
! -- run this as:
! --     mysql --user root -p mysql < cr_db.sql
! -- then:
! --     mysql --user=maildb -p maildb < cr_maildb.sql
! 
! drop database if exists maildb;
! create database maildb;
! grant all privileges on maildb.* to maildb identified by 'maildb';
! 
--- 1,22 ----
! -- 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_db.sql,v 1.2 2002/11/05 23:15:47 lweissler Exp $
! -- -------------------------------------------------------
! 
! -- Edit the 'grant' line below to set a good password for the
! --   maildb user
! 
! -- Run this as:
! --     mysql --user=root -p mysql < cr_db.sql
! -- then:
! --     mysql --user=maildb -p maildb < cr_maildb.sql
! 
! drop database if exists maildb;
! create database maildb;
! grant all privileges on maildb.* to maildb identified by 'maildb';
! 


1.2       +181 -172  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.1
retrieving revision 1.2
diff -c -r1.1 -r1.2
*** cr_maildb.sql	30 Oct 2002 02:51:46 -0000	1.1
--- cr_maildb.sql	5 Nov 2002 23:15:47 -0000	1.2
***************
*** 1,172 ****
! -- maildb tables
! 
! -- common message data:
! --	hdrs, mime_types
! -- user data:
! --	users, cats, views
! -- message data:
! --	msg_ids, msg_hdrs, msg_users, msg_attach
! -- logs:
! --	obj_log, msg_log
! 
! -- -------------------------
! -- Common Message Data
! -- -------------------------
! 
! -- hdrs
! -- -> deleted 10/10/2002 in favor of de-normalizing
! 
! -- drop table if exists hdrs;
! -- create table hdrs (
! --   h_id		smallint 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		smallint 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	 	smallint 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		mediumint unsigned not null auto_increment,
!   ca_u_id	smallint unsigned not null references users(u_id),
!   ca_desc	varchar(127),
!   ca_parent	mediumint 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		mediumint unsigned not null auto_increment,
!   vw_desc	varchar(127),
!   vw_u_id	smallint 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	       mediumint unsigned not null auto_increment,
!   m_msg_id     varchar(255) not null,
!   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		mediumint unsigned not null auto_increment,
!   mh_m_id	mediumint unsigned not null references msg_ids(m_id),
!   mh_key	varchar(63) not null,
!   mh_value	varchar(255),
!   mh_sort_order	smallint 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_users
! 
! drop table if exists msg_users;
! create table msg_users (
!   mu_id		mediumint unsigned not null auto_increment,
!   mu_m_id	mediumint unsigned not null not null references msg_ids(m_id),
!   mu_u_id	smallint unsigned not null references users(u_id),
!   mu_ca_id	mediumint unsigned not null references cats(ca_id),
!   mu_date	mediumint unsigned not null references msg_hdrs(mh_id),
!   mu_from	mediumint unsigned not null references msg_hdrs(mh_id),
!   mu_subject	mediumint unsigned not null references msg_hdrs(mh_id),
!   mu_flags	tinyint,
!   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
! 
! drop table if exists msg_attach;
! create table msg_attach (
!   ma_id		mediumint unsigned not null auto_increment,
!   ma_m_id	mediumint unsigned not null not null references msg_ids(m_id),
!   ma_mt_id	smallint unsigned not null references mime_types(mt_id),
!   ma_encode	varchar(127),
!   ma_storage	bool,
!   ma_body	mediumtext,
!   ma_path	varchar(255),
!   ma_sep_data	varchar(255),
!   ma_sort_order	smallint unsigned,  
!   primary key	(ma_id),
!   index		ma_m_id_idx (ma_m_id),
!   fulltext index ma_body_idx (ma_body)
! );
! 
! -- -------------------------
! -- Logs
! -- -------------------------
!  
! drop table if exists obj_log;
! create table obj_log (
!   obj_id	mediumint unsigned not null auto_increment,
!   obj_type	varchar(15),
!   obj_type_id	mediumint 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	mediumint unsigned not null auto_increment,
!   msl_m_id	mediumint unsigned not null,
!   msl_u_id	mediumint unsigned not null,
!   msl_ca_id	mediumint 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)
! );
--- 1,181 ----
! -- cr_maildb.sql - MailDB MySQL database table 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.2 2002/11/05 23:15:47 lweissler Exp $
! -- -------------------------------------------------------
! -- Create maildb tables
! 
! -- common message data:
! --	hdrs, mime_types
! -- user data:
! --	users, cats, views
! -- message data:
! --	msg_ids, msg_hdrs, msg_users, msg_attach
! -- logs:
! --	obj_log, msg_log
! 
! -- -------------------------
! -- Common Message Data
! -- -------------------------
! 
! -- hdrs
! -- -> deleted 10/10/2002 in favor of de-normalizing
! 
! -- drop table if exists hdrs;
! -- create table hdrs (
! --   h_id		smallint 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		smallint 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	 	smallint 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		mediumint unsigned not null auto_increment,
!   ca_u_id	smallint unsigned not null references users(u_id),
!   ca_desc	varchar(127),
!   ca_parent	mediumint 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		mediumint unsigned not null auto_increment,
!   vw_desc	varchar(127),
!   vw_u_id	smallint 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	       mediumint unsigned not null auto_increment,
!   m_msg_id     varchar(255) not null,
!   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		mediumint unsigned not null auto_increment,
!   mh_m_id	mediumint unsigned not null references msg_ids(m_id),
!   mh_key	varchar(63) not null,
!   mh_value	varchar(255),
!   mh_sort_order	smallint 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_users
! 
! drop table if exists msg_users;
! create table msg_users (
!   mu_id		mediumint unsigned not null auto_increment,
!   mu_m_id	mediumint unsigned not null not null references msg_ids(m_id),
!   mu_u_id	smallint unsigned not null references users(u_id),
!   mu_ca_id	mediumint unsigned not null references cats(ca_id),
!   mu_date	mediumint unsigned not null references msg_hdrs(mh_id),
!   mu_from	mediumint unsigned not null references msg_hdrs(mh_id),
!   mu_subject	mediumint unsigned not null references msg_hdrs(mh_id),
!   mu_flags	tinyint,
!   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
! 
! drop table if exists msg_attach;
! create table msg_attach (
!   ma_id		mediumint unsigned not null auto_increment,
!   ma_m_id	mediumint unsigned not null not null references msg_ids(m_id),
!   ma_mt_id	smallint unsigned not null references mime_types(mt_id),
!   ma_encode	varchar(127),
!   ma_storage	bool,
!   ma_body	mediumtext,
!   ma_path	varchar(255),
!   ma_sep_data	varchar(255),
!   ma_sort_order	smallint unsigned,  
!   primary key	(ma_id),
!   index		ma_m_id_idx (ma_m_id),
!   fulltext index ma_body_idx (ma_body)
! );
! 
! -- -------------------------
! -- Logs
! -- -------------------------
!  
! drop table if exists obj_log;
! create table obj_log (
!   obj_id	mediumint unsigned not null auto_increment,
!   obj_type	varchar(15),
!   obj_type_id	mediumint 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	mediumint unsigned not null auto_increment,
!   msl_m_id	mediumint unsigned not null,
!   msl_u_id	mediumint unsigned not null,
!   msl_ca_id	mediumint 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)
! );


1.2       +702 -696  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.1
retrieving revision 1.2
diff -c -r1.1 -r1.2
*** design.html	30 Oct 2002 12:30:40 -0000	1.1
--- design.html	5 Nov 2002 23:15:47 -0000	1.2
***************
*** 1,696 ****
! <!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_users</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_users</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">Normalized table of message ids.<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_users, 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_users</td>
!     </tr>
!   <tr>
!     <td valign="top">msg_users</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>
!       <li>Size threshold for determining whether attachment is stored in
! the      database or as external file? (Need to know here only for reasonable
! sizing      of the ma_body field in msg_attach.) </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_users 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>smallint 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>smallint 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>mediumint 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>smallint 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>mediumint 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>mediumint 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>smallint 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="2">msg_ids<br>
!       </td>
!       <td valign="top">m_id<br>
!       </td>
!       <td valign="top">mediumint 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" rowspan="5">msg_hdrs</td>
!     <td>mh_id</td>
!     <td>mediumint 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>mediumint 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>smallint unsigned not null</td>
!     <td> </td>
!     <td> </td>
!     <td>Header sequence number.</td>
!     </tr>
!   <tr>
!     <td valign="top" rowspan="8">msg_users</td>
!     <td>mu_id</td>
!     <td>mediumint 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>mediumint unsigned not null</td>
!     <td> </td>
!     <td>X</td>
!     <td>Message identifier.</td>
!     </tr>
!   <tr>
!     <td>mu_u_id</td>
!     <td>smallint 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>mediumint unsigned not null</td>
!     <td> </td>
!     <td>X</td>
!     <td>Category to which the user assigned the message.</td>
!     </tr>
!   <tr>
!     <td>mu_date</td>
!     <td>mediumint unsigned not null</td>
!     <td> </td>
!     <td> </td>
!     <td>Foreign key ref to corresponding "Date" header in msg_hdrs.</td>
!     </tr>
!   <tr>
!     <td>mu_from<...
 
[truncated message content]