Menu
▾
▴
[maildb-cvs] cvs commit: maildb/libmaildb/db/mysql/doc cr_maildb.sql design.html message.sql test.sql
|
From: Liza W. <lwe...@us...> - 2003-05-22 18:31:55
|
lweissler 03/05/22 11:31:55
Modified: libmaildb/db/mysql/doc cr_maildb.sql design.html message.sql
test.sql
Log:
Add common header fields to msg_ids, as well as parent_id field. msg_owners loses date, from, subject fields. msg_attach loses ma_in_fs, ma_in_msg fields.
Revision Changes Path
1.5 +29 -9 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.4
retrieving revision 1.5
diff -c -r1.4 -r1.5
*** cr_maildb.sql 21 May 2003 21:04:19 -0000 1.4
--- cr_maildb.sql 22 May 2003 18:31:54 -0000 1.5
***************
*** 5,11 ****
-- information, see the LICENSE file in the top level directory of the
-- MailDB source distribution.
--
! -- $Id: cr_maildb.sql,v 1.4 2003/05/21 21:04:19 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.5 2003/05/22 18:31:54 lweissler Exp $
-- -------------------------------------------------------
-- maildb tables
***************
*** 15,21 ****
-- user data:
-- users, cats, views
-- message data:
! -- msg_ids, msg_hdrs, msg_owners, msg_attach
-- logs:
-- obj_log, msg_log
-- meta:
--- 15,21 ----
-- user data:
-- users, cats, views
-- message data:
! -- msg_ids, msg_hdrs, msg_owners, msg_attach, msg_orig
-- logs:
-- obj_log, msg_log
-- meta:
***************
*** 96,101 ****
--- 96,110 ----
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 unsigned,
m_vw_incl text,
m_vw_excl text,
primary key (m_id),
***************
*** 122,133 ****
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 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),
--- 131,142 ----
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),
***************
*** 136,150 ****
);
-- msg_attach
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 not null references msg_ids(m_id),
ma_mt_id integer unsigned not null references mime_types(mt_id),
ma_encode varchar(127),
- ma_in_fs bool,
- ma_in_msg bool,
ma_body mediumtext,
ma_path varchar(255),
ma_sep_data varchar(255),
--- 145,159 ----
);
-- 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),
***************
*** 152,157 ****
--- 161,177 ----
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)
);
-- -------------------------
1.5 +118 -47 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.4
retrieving revision 1.5
diff -c -r1.4 -r1.5
*** design.html 21 May 2003 21:04:19 -0000 1.4
--- design.html 22 May 2003 18:31:54 -0000 1.5
***************
*** 118,124 ****
<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>
--- 118,127 ----
<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>
***************
*** 210,218 ****
</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
--- 213,218 ----
***************
*** 345,351 ****
<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>
--- 345,351 ----
<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>
***************
*** 372,386 ****
</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">X<br>
</td>
! <td valign="to">Specify views in which to include this message<br>
</td>
</tr>
<tr>
--- 372,494 ----
</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 unsigned<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>
***************
*** 390,396 ****
</td>
<td valign="top"><br>
</td>
! <td valign="top">X<br>
</td>
<td valign="top">Specify views from which to exclude this message<br>
</td>
--- 498,504 ----
</td>
<td valign="top"><br>
</td>
! <td valign="top"><br>
</td>
<td valign="top">Specify views from which to exclude this message<br>
</td>
***************
*** 434,440 ****
<td>Header sequence number.</td>
</tr>
<tr>
! <td valign="top" rowspan="8">msg_owners</td>
<td>mu_id</td>
<td>integer unsigned not null auto_increment</td>
<td>X</td>
--- 542,548 ----
<td>Header sequence number.</td>
</tr>
<tr>
! <td valign="top" rowspan="5">msg_owners</td>
<td>mu_id</td>
<td>integer unsigned not null auto_increment</td>
<td>X</td>
***************
*** 463,489 ****
<td>Category to which the user assigned the message.</td>
</tr>
<tr>
- <td>mu_date</td>
- <td>integer 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</td>
- <td>integer unsigned not null</td>
- <td> </td>
- <td> </td>
- <td>Foreign key ref to corresponding "From" header in msg_hdrs.</td>
- </tr>
- <tr>
- <td>mu_subject</td>
- <td>integer unsigned not null</td>
- <td> </td>
- <td> </td>
- <td>Foreign key ref to corresponding "Subject" header in msg_hdrs.</td>
- </tr>
- <tr>
<td>mu_flags</td>
<td>integer unsigned</td>
<td> </td>
--- 571,576 ----
***************
*** 491,497 ****
<td>Bit flags for the message.</td>
</tr>
<tr>
! <td valign="top" rowspan="9">msg_attach</td>
<td>ma_id</td>
<td>integer unsigned not null auto_increment</td>
<td>X</td>
--- 578,584 ----
<td>Bit flags for the message.</td>
</tr>
<tr>
! <td valign="top" rowspan="8">msg_attach</td>
<td>ma_id</td>
<td>integer unsigned not null auto_increment</td>
<td>X</td>
***************
*** 518,539 ****
<td> </td>
<td> </td>
<td>Attachment encoding</td>
- </tr>
- <tr>
- <td>ma_in_fs</td>
- <td>boolean</td>
- <td> </td>
- <td> </td>
- <td>Denotes whether attachment is stored within the database (0) or
- externally in the filesystem (1).</td>
- </tr>
- <tr>
- <td>ma_in_msg</td>
- <td>boolean</td>
- <td> </td>
- <td> </td>
- <td>Denotes whether attachment is stored within the database as
- a message. This being true assumes ma_in_fs is TRUE.</td>
</tr>
<tr>
<td>ma_body</td>
--- 605,610 ----
1.5 +15 -5 maildb/libmaildb/db/mysql/doc/message.sql
Index: message.sql
===================================================================
RCS file: /cvsroot/maildb/maildb/libmaildb/db/mysql/doc/message.sql,v
retrieving revision 1.4
retrieving revision 1.5
diff -c -r1.4 -r1.5
*** message.sql 21 May 2003 21:04:19 -0000 1.4
--- message.sql 22 May 2003 18:31:54 -0000 1.5
***************
*** 5,11 ****
-- information, see the LICENSE file in the top level directory of the
-- MailDB source distribution.
--
! -- $Id: message.sql,v 1.4 2003/05/21 21:04:19 lweissler Exp $
-- -------------------------------------------------------
delete from config where cf_name is not null;
--- 5,11 ----
-- information, see the LICENSE file in the top level directory of the
-- MailDB source distribution.
--
! -- $Id: message.sql,v 1.5 2003/05/22 18:31:54 lweissler Exp $
-- -------------------------------------------------------
delete from config where cf_name is not null;
***************
*** 17,23 ****
insert into config values ('fs_path', '/home/lyw/maildb/foo');
insert into config values ('max_msg_size', '99999999');
! insert into msg_ids values (NULL, '<001801c27523$aa5d46a0$af270444@CX434770b>',NULL,NULL);
insert into msg_hdrs values (NULL, 1,
'From', 'lyu...@co... Wed Oct 16 10:53:33 2002', 0);
--- 17,35 ----
insert into config values ('fs_path', '/home/lyw/maildb/foo');
insert into config values ('max_msg_size', '99999999');
! insert into msg_ids values (NULL,
! '<001801c27523$aa5d46a0$af270444@CX434770b>',
! '"Liza Weissler" <li...@av...>',
! NULL,
! NULL,
! 'estee lauder',
! 'Wed, 16 Oct 2002 07:52:36 -0700',
! '"Luci Ursich" <lyu...@co...>',
! NULL,
! NULL,
! NULL,
! NULL,
! NULL);
insert into msg_hdrs values (NULL, 1,
'From', 'lyu...@co... Wed Oct 16 10:53:33 2002', 0);
***************
*** 67,78 ****
1,
1,
'',
- 0,
- 0,
'The blush is called Blush All Day, Natural Cheek Color..I have one that\nis 04 Pink Cloud and one that is 01 Potpourri..one is older than the\nother and I remember a discontinued color... both are like an orangey\npink...a salmon type color...I don''t want pink, pink... it is a blue\nrectangular case about 1 3/4 inches by 3.5 inches...powder with a brush.\n\nThere is also tube of stuff call Spotlight Skin Tone Perfecter... I like\nthat...\nL',
'',
'',
0);
! insert into msg_owners values (NULL, 1, 1, 1, 10, 7, 9, NULL);
--- 79,88 ----
1,
1,
'',
'The blush is called Blush All Day, Natural Cheek Color..I have one that\nis 04 Pink Cloud and one that is 01 Potpourri..one is older than the\nother and I remember a discontinued color... both are like an orangey\npink...a salmon type color...I don''t want pink, pink... it is a blue\nrectangular case about 1 3/4 inches by 3.5 inches...powder with a brush.\n\nThere is also tube of stuff call Spotlight Skin Tone Perfecter... I like\nthat...\nL',
'',
'',
0);
! insert into msg_owners values (NULL, 1, 1, 1, NULL);
1.4 +8 -10 maildb/libmaildb/db/mysql/doc/test.sql
Index: test.sql
===================================================================
RCS file: /cvsroot/maildb/maildb/libmaildb/db/mysql/doc/test.sql,v
retrieving revision 1.3
retrieving revision 1.4
diff -c -r1.3 -r1.4
*** test.sql 21 Dec 2002 14:06:57 -0000 1.3
--- test.sql 22 May 2003 18:31:54 -0000 1.4
***************
*** 5,11 ****
-- information, see the LICENSE file in the top level directory of the
-- MailDB source distribution.
--
! -- $Id: test.sql,v 1.3 2002/12/21 14:06:57 jsquyres Exp $
-- -------------------------------------------------------
-- -------------------------
--- 5,11 ----
-- information, see the LICENSE file in the top level directory of the
-- MailDB source distribution.
--
! -- $Id: test.sql,v 1.4 2003/05/22 18:31:54 lweissler Exp $
-- -------------------------------------------------------
-- -------------------------
***************
*** 35,41 ****
;
SELECT ma_m_id, m_msg_id, mt_desc, ma_encode, ma_body, ma_path,
! ma_sep_data, ma_in_fs, ma_sort_order
FROM msg_attach, mime_types, msg_ids
WHERE ma_m_id = 1
AND m_id = ma_m_id
--- 35,41 ----
;
SELECT ma_m_id, m_msg_id, mt_desc, ma_encode, ma_body, ma_path,
! ma_sep_data, ma_sort_order
FROM msg_attach, mime_types, msg_ids
WHERE ma_m_id = 1
AND m_id = ma_m_id
***************
*** 47,61 ****
-- Basically same as category scan except drop the mu_ca_id check
-- and add in check of mu_from.
! SELECT mu_m_id, a.mh_value as date, b.mh_value as hfrom,
! c.mh_value as subject, mu_flags
! FROM msg_owners, msg_hdrs a, msg_hdrs b, msg_hdrs c
WHERE mu_u_id = 1
! AND mu_date = a.mh_id
! AND mu_from = b.mh_id
! AND mu_subject = c.mh_id
! AND b.mh_value like '%lyursich%'
! ORDER BY a.mh_value
;
-- Count messages in a category
--- 47,59 ----
-- Basically same as category scan except drop the mu_ca_id check
-- and add in check of mu_from.
! -- SELECT mu_m_id, a.mh_value as date, b.mh_value as hfrom,
! -- c.mh_value as subject, mu_flags
! SELECT mu_m_id, m_date, m_from, m_subject
! FROM msg_owners, msg_ids
WHERE mu_u_id = 1
! AND mu_m_id = m_id
! AND m_from like '%lyursich%'
;
-- Count messages in a category
|
