[maildb-devel] Re: messages in/out of database

[<li...@av...>]

Ok, so here are today's changes made on queeg. 

 -- msg_ids adds some "header" cols, and m_parent_id (which would map to 
another m_id, or be -1 if message is standalone). 

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),
 index        m_msg_id_idx (m_msg_id)
); 

 -- msg_owners loses date, from, subject field - an earlier attempt (that I 
forgot about completely) to put a few "common" fields somewhere to avoid a 
join.  I wish I remembered why I put those in this table and not in msg_ids. 
Oh well. 

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 loses ma_in_fs, ma_in_msg fields. 

These changes are reflected in cr_maildb.sql and also documented in the 
design.html document in the mysql part of the cvs tree. 

I need to review the indexing though.  Well, lots to be reviewed and 
fine-tuned as we go along (including text vs varchar as James notes, etc.). 

 - Liza