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

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

jsquyres    04/05/30 04:56:55

  Modified:    libmaildb/db/mysql/doc cr_maildb.sql
  Log:
  Overhaul of the schema to be workable in the prototype code.  Comments
  included make at least some of design.html inaccurate.  Explanation e-mail
  coming to the maildb-devel list shortly.

Revision  Changes    Path
1.8       +222 -120  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.7
retrieving revision 1.8
diff -c -w -r1.7 -r1.8
*** cr_maildb.sql	30 May 2003 00:18:00 -0000	1.7
--- cr_maildb.sql	30 May 2004 11:56:54 -0000	1.8
***************
*** 1,83 ****
  -- 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),
--- 1,97 ----
  -- cr_db.sql - MailDB MySQL database creation
! -- Copyright (c) 2002-2004 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.8 2004/05/30 11:56:54 jsquyres Exp $
  -- -------------------------------------------------------

  -- maildb tables

  -- user data:
  --	users, cats, views
  -- message data:
! --	messages, msg_cats, msg_hdrs, msg_parts, msg_quick_search
  -- logs:
  --	obj_log, msg_log
  -- meta:
  --	config

! --========================================================================
  -- User Data
! --========================================================================

+ --------------------------------------------------------------------------
  -- users
+ --
+ -- Simple list of usernames and a textual gecos (mainly for
+ -- debugging/reference).  The user ID is used throughout other tables
+ -- for unique ownership identity.
+ --------------------------------------------------------------------------

  drop table if exists users;
  create table users (
+   -- Unique ID
    u_id	 	integer unsigned not null auto_increment,
! 
!   -- Username and gecos
!   u_username	varchar(127),
!   u_gecos       varchar(127),
! 
!   -- Indices
    primary key	(u_id),
!   index 	u_username_idx (u_username)
  );

+ --------------------------------------------------------------------------
  -- cats
+ --
+ -- Names and unique ID's of categories, including parent pointers and
+ -- user ID's of the user who owns them.
+ --------------------------------------------------------------------------

  drop table if exists cats;
  create table cats (
+   -- Unique ID
    ca_id		integer unsigned not null auto_increment,
+ 
+   -- User owning this category
    ca_u_id	integer unsigned not null references users(u_id),
! 
!   -- Just the name of this category
!   ca_basename   varchar(255),
! 
!   -- The full name of this category, including all parent names,
!   -- separated by "/".  Makes it easy and quick to search for a full
!   -- category name, rather than having to traverse all parent category
!   -- names.
!   ca_fullname   varchar(255),
! 
!   -- A textual description
!   ca_desc	varchar(255),
! 
!   -- Parent category reference
    ca_parent	integer unsigned references cats(ca_id),
+ 
+   -- Indices
    primary key 	(ca_id),
!   index		ca_u_id_idx (ca_u_id),
!   index         ca_fullname_idx (ca_fullname)
  );

+ --------------------------------------------------------------------------
  -- views
+ --
+ -- JMS Haven't done anything with this yet.  Will hold SQL rules for
+ -- saved searches.
+ --------------------------------------------------------------------------

  drop table if exists views;
  create table views (
    vw_id		integer unsigned not null auto_increment,
!   vw_desc	varchar(255),
    vw_u_id	integer unsigned not null references users(u_id),
    vw_query	text,
    primary key	(vw_id),
***************
*** 85,188 ****
  );

! -- -------------------------
  -- 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 (
--- 99,272 ----
  );

! --========================================================================
  -- Message Data
! --========================================================================
! 
! --------------------------------------------------------------------------
! -- messages
! --
! -- Main table for finding messages.  Contains a unique ID for each
! -- message (including RFC822 embedded messages), the user who owns
! -- them (should we do something so that more than one user can own a
! -- message?), the parent of the message (for RFC822 embedded
! -- messages), and a set of flags.  The flags are the IMAP flags as
! -- well as two maildb-specific flags: valid (only set to true when the
! -- entire message has been populated in all the other relevant
! -- tables), and has_children (indicating that a search for parent_id
! -- of this message's ID will turn up some messages).
! --------------------------------------------------------------------------
! 
! drop table if exists messages;
! create table messages (
!   -- Unique ID
!   msg_id	integer unsigned not null auto_increment,
! 
!   -- Cross reference to a specific user
!   msg_u_id	integer unsigned not null references users(u_id),
! 
!   -- This message may have a parent (e.g., if it's an embedded RFC 822
!   -- message)
!   msg_parent_msg_id integer unsigned,
! 
!   -- Bit flags on this message
!   msg_flags	integer,
! 
!   -- Indices
!   primary key	(msg_id),
!   index		msg_u_id_idx (msg_u_id),
!   index		msg_parent_msg_id_idx (msg_parent_msg_id)
! );
! 
! --------------------------------------------------------------------------
! -- msg_cats
! --
! -- Linking messages to categories.
! --------------------------------------------------------------------------
! 
! drop table if exists msg_cats;
! create table msg_cats (
!   -- Unique ID
!   mc_id         integer unsigned not null auto_increment,

!   -- Cross reference to a specific message
!   mc_msg_id	integer unsigned not null references messages(msg_id),

!   -- Cross reference to a specific category
!   mc_ca_id      integer unsigned not null references cats(ca_id),

!   -- Special inclusion and exclusion lists
!   mc_vw_incl    text,
!   mc_vw_excl    text,
! 
!   -- Indices
!   primary key   (mc_id),
!   index          mc_ca_id_idx (mc_ca_id)
  );

+ --------------------------------------------------------------------------
  -- msg_hdrs
+ --
+ -- Mainly a listing of key=value pairs from the headers of message
+ -- parts (yes, to include attachments).  No attempt at normalization
+ -- is made -- the pure key=value that was given as the message input
+ -- is saved.
+ --------------------------------------------------------------------------

  drop table if exists msg_hdrs;
  create table msg_hdrs (
+   -- Unique ID
    mh_id		integer unsigned not null auto_increment,
! 
!   -- Cross reference to a specific message and part
!   mh_msg_id	integer unsigned not null references messages(msg_id),
!   mh_part_id    integer unsigned not null references msg_part(mp_id),
! 
!   -- Header key=value lines
    mh_key	varchar(63) not null,
!   mh_value	text,
    mh_sort_order	integer unsigned,
+ 
+   -- Indices
    primary key	(mh_id),
    index		mh_key_idx (mh_key),
+   index		mh_msg_id_idx (mh_msg_id),
    fulltext index mh_value_idx (mh_value)
  );

! --------------------------------------------------------------------------
! -- msg_parts
! --
! -- A single part of a message.  This can contain a body here in the
! -- database (up to config/max_short_body_length bytes), or a filename
! -- somewhere in the filesystem indicating where the text of the body
! -- actually lives.
! --------------------------------------------------------------------------
! 
! drop table if exists msg_parts;
! create table msg_parts (
!   -- Unique ID
!   mp_id		integer unsigned not null auto_increment,
! 
!   -- Cross reference to a specific message
!   mp_msg_id	integer unsigned not null references msg_ids(msg_id),
!   mp_sort_order	integer unsigned,  
! 
!   -- Text of the part or a filename
!   mp_body	mediumtext,
!   mp_path	varchar(255),
! 
!   -- Indices
!   primary key	(mp_id),
!   index		ma_m_id_idx (mp_msg_id),
!   fulltext index mp_body_idx (mp_body)
! );

! --------------------------------------------------------------------------
! -- msg_quick_search
! --
! -- This data is mainly maintained for quick searches on "common"
! -- fields in the message header.  The goal is to be able to search on
! -- any of these fields and then resolve it back to one or more maildb
! -- message IDs.
! --------------------------------------------------------------------------
! 
! drop table if exists msg_quick_search;
! create table msg_quick_search (
!   -- Unique ID
!   mqs_id	 integer unsigned not null auto_increment,
! 
!   -- Cross referece to the maildb message ID
!   mqs_msg_id	integer unsigned not null references msg_ids(msg_id),
! 
!   -- Common fields
!   mqs_msg_id_str varchar(255) not null,
!   mqs_to	 text,
!   mqs_cc	 text,
!   mqs_bcc	 text,
!   mqs_subject    text,
!   mqs_date       datetime,
!   mqs_from       text,
!   mqs_sender     text,
!   mqs_in_reply_to text,
!   mqs_reply_to   text,
!   mqs_references text,
!   mqs_boundary   text,
! 
!   -- Indices
!   primary key  (mqs_id),
!   index	       mqs_msg_id_idx(mqs_msg_id),
!   index        mqs_date_idx(mqs_date),
!   index        mqs_from_idx(mqs_from(255)),
!   index	       mqs_to_idx(mqs_to(255)),
!   index	       mqs_subject_idx(mqs_subject(255))
  ); 

! --========================================================================
  -- Logs
! --
! -- JMS Nothing has been done here yet
! --========================================================================

  drop table if exists obj_log;
  create table obj_log (
***************
*** 212,224 ****
    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)
  );
--- 296,326 ----
    index		msl_ca_id_idx (msl_ca_id)
  );

! --========================================================================
  -- Meta
! --========================================================================
! 
! --------------------------------------------------------------------------
! -- config
! --
! -- Simple key=value
! --------------------------------------------------------------------------

  drop table if exists config;
  create table config (
!   -- Unique ID
!   cf_id		integer unsigned not null auto_increment,
! 
!   -- Cross reference to a specific user
!   cf_u_id	integer unsigned not null references users(u_id),
! 
!   -- Key=value pairs
!   cf_key	varchar(127) not null,
!   cf_value	varchar(255),
!   cf_desc	varchar(255),
! 
!   -- Indices
!   primary key	(cf_id),
!   index		cf_u_id_idx (cf_u_id),
!   index         cf_key_idx (cf_key)
  );