Menu
▾
▴
maildb-cvs
|
From: James S. <jls...@us...> - 2003-05-30 00:18:14
|
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&sessionid=avesorg-session-0.0982186974297328&message_id=fd02e2f221c24eb3770452d3143b0894&folder=INBOX&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 & ! 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>int... [truncated message content] |
|
From: Jeff S. <jsq...@os...> - 2003-05-30 12:49:55
|
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&sessionid=avesorg-session-0.0982186974297328&message_id=fd02e2f221c24eb3770452d3143b0894&folder=INBOX&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 & > ! 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 heade... [truncated message content] |
|
From: James S. <jl...@do...> - 2003-05-30 13:06:35
|
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&sessionid=avesorg-session-0.0982186974297328&message_id=fd02e2f221c24eb3770452d3143b0894&folder=INBOX&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 & >>! 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"><... [truncated message content] |
|
From: Jeff S. <jsq...@os...> - 2003-05-30 13:15:14
|
On Fri, 30 May 2003, Jeff Squyres wrote:
> What were the essential changes that you made, since this diff is
> practically useless?
Here's that diff again, ignoring whitespace (I've updated the CVS
scripty-foo to ignore whitespace to avoid this problem in the future):
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 -w -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
***************
*** 5,11 ****
-- 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
--- 5,11 ----
-- 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
***************
*** 104,114 ****
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
--- 104,120 ----
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
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 -w -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
***************
*** 125,131 ****
</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>
--- 125,132 ----
</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>
***************
*** 378,384 ****
</td>
<td valign="top"><br>
</td>
! <td valign="top"><br>
</td>
<td valign="top">To header line contents<br>
</td>
--- 379,385 ----
</td>
<td valign="top"><br>
</td>
! <td valign="top">X<br>
</td>
<td valign="top">To header line contents<br>
</td>
***************
*** 414,420 ****
</td>
<td valign="top"><br>
</td>
! <td valign="top"><br>
</td>
<td valign="top">Subject header line contents<br>
</td>
--- 415,421 ----
</td>
<td valign="top"><br>
</td>
! <td valign="top">X<br>
</td>
<td valign="top">Subject header line contents<br>
</td>
***************
*** 422,432 ****
<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>
--- 423,433 ----
<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>
***************
*** 438,444 ****
</td>
<td valign="top"><br>
</td>
! <td valign="top"><br>
</td>
<td valign="top">From header line contents<br>
</td>
--- 439,445 ----
</td>
<td valign="top"><br>
</td>
! <td valign="top">X<br>
</td>
<td valign="top">From header line contents<br>
</td>
***************
*** 468,473 ****
--- 469,498 ----
</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>
***************
*** 793,799 ****
</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>
--- 818,824 ----
</p>
<p>...where (U) = unsigned, and L = length of text string. </p>
<p></p>
! <hr><i>Last update: 22 May 2003 by <a href="mailto:jls...@do...">James
! Scott</a></i><br>
</body>
</html>
--
{+} Jeff Squyres
{+} jsq...@os...
{+} Research Associate, Open Systems Lab, Indiana University
{+} http://www.osl.iu.edu/
|
Thanks for helping keep SourceForge clean.
X