[maildb-cvs] cvs commit: maildb/libmaildb/db/mysql/doc README cr_db.sql cr_maildb.sql message.sql mime.sql queries.sql test.sql users.sql

[Jeff S. <jsq...@us...>]

jsquyres    02/10/29 18:51:46

  Added:       libmaildb/db/mysql/doc README cr_db.sql cr_maildb.sql
                        message.sql mime.sql queries.sql test.sql users.sql
  Log:
  First CVS version of mysql stuff for mysql maildb schema

Revision  Changes    Path
1.1                  maildb/libmaildb/db/mysql/doc/README

Index: README
===================================================================
1) If new database, make sure you have a password set for root, and disallow
local access without a password.  This should do it...

	% mysql mysql
	> delete from user where host='localhost' and user='';
	> quit

	% mysqladmin -u root reload
	% mysqladmin -u root password <new-password>
	% myqladmin -u root -p reload

2) Create maildb database

	% mysql -u root -p mysql < cr_db.sql   /* provide root pw */
	% mysql -u maildb -p maildb < cr_maildb.sql /* provide maildb pw */

3) Populate with some sample data
	% mysql -u maildb -p maildb
	> source mime.sql
	> source users.sql
	> source message.sql




1.1                  maildb/libmaildb/db/mysql/doc/cr_db.sql

Index: cr_db.sql
===================================================================
-- MailDB database creation
-- 10/18/2002 lyw
-- run this as:
--     mysql --user root -p mysql < cr_db.sql
-- then:
--     mysql --user=maildb -p maildb < cr_maildb.sql

drop database if exists maildb;
create database maildb;
grant all privileges on maildb.* to maildb identified by 'maildb';




1.1                  maildb/libmaildb/db/mysql/doc/cr_maildb.sql

Index: cr_maildb.sql
===================================================================
-- maildb tables

-- common message data:
--	hdrs, mime_types
-- user data:
--	users, cats, views
-- message data:
--	msg_ids, msg_hdrs, msg_users, msg_attach
-- logs:
--	obj_log, msg_log

-- -------------------------
-- Common Message Data
-- -------------------------

-- hdrs
-- -> deleted 10/10/2002 in favor of de-normalizing

-- drop table if exists hdrs;
-- create table hdrs (
--   h_id		smallint unsigned not null auto_increment,
--   h_desc	varchar(20),
--   primary key 	(h_id)
-- );

-- mime_types

drop table if exists mime_types;
create table mime_types (
  mt_id		smallint unsigned not null auto_increment,
  mt_desc	varchar(127),
  primary key	(mt_id),
  index 	mt_desc_idx (mt_desc)
);


-- -------------------------
-- User Data
-- -------------------------

-- users

drop table if exists users;
create table users (
  u_id	 	smallint unsigned not null auto_increment,
  u_desc	varchar(127),
  primary key	(u_id),
  index 	u_desc_idx (u_desc)
);

-- cats

drop table if exists cats;
create table cats (
  ca_id		mediumint unsigned not null auto_increment,
  ca_u_id	smallint unsigned not null references users(u_id),
  ca_desc	varchar(127),
  ca_parent	mediumint unsigned references cats(ca_id),
  primary key 	(ca_id),
  index		ca_u_id_idx (ca_u_id)
);

-- views

drop table if exists views;
create table views (
  vw_id		mediumint unsigned not null auto_increment,
  vw_desc	varchar(127),
  vw_u_id	smallint unsigned not null references users(u_id),
  vw_query	text,
  primary key	(vw_id),
  index		vw_u_id_idx (vw_u_id)
);


-- -------------------------
-- Message Data
-- -------------------------

-- msg_ids

drop table if exists msg_ids;

create table msg_ids (
  m_id	       mediumint unsigned not null auto_increment,
  m_msg_id     varchar(255) not null,
  primary key  (m_id),
  index	       m_msg_id_idx (m_msg_id)
); 

-- msg_hdrs

drop table if exists msg_hdrs;
create table msg_hdrs (
  mh_id		mediumint unsigned not null auto_increment,
  mh_m_id	mediumint unsigned not null references msg_ids(m_id),
  mh_key	varchar(63) not null,
  mh_value	varchar(255),
  mh_sort_order	smallint unsigned,
  primary key	(mh_id),
  index		mh_m_id_idx (mh_m_id),
  index		mh_key_idx (mh_key),
  fulltext index mh_value_idx (mh_value)
);

-- msg_users

drop table if exists msg_users;
create table msg_users (
  mu_id		mediumint unsigned not null auto_increment,
  mu_m_id	mediumint unsigned not null not null references msg_ids(m_id),
  mu_u_id	smallint unsigned not null references users(u_id),
  mu_ca_id	mediumint unsigned not null references cats(ca_id),
  mu_date	mediumint unsigned not null references msg_hdrs(mh_id),
  mu_from	mediumint unsigned not null references msg_hdrs(mh_id),
  mu_subject	mediumint unsigned not null references msg_hdrs(mh_id),
  mu_flags	tinyint,
  primary key	(mu_id),
  index		mu_m_id_idx (mu_m_id),
  index		mu_u_id_idx (mu_u_id),
  index		mu_ca_id_idx (mu_ca_id)
);

-- msg_attach

drop table if exists msg_attach;
create table msg_attach (
  ma_id		mediumint unsigned not null auto_increment,
  ma_m_id	mediumint unsigned not null not null references msg_ids(m_id),
  ma_mt_id	smallint unsigned not null references mime_types(mt_id),
  ma_encode	varchar(127),
  ma_storage	bool,
  ma_body	mediumtext,
  ma_path	varchar(255),
  ma_sep_data	varchar(255),
  ma_sort_order	smallint unsigned,  
  primary key	(ma_id),
  index		ma_m_id_idx (ma_m_id),
  fulltext index ma_body_idx (ma_body)
);

-- -------------------------
-- Logs
-- -------------------------
 
drop table if exists obj_log;
create table obj_log (
  obj_id	mediumint unsigned not null auto_increment,
  obj_type	varchar(15),
  obj_type_id	mediumint unsigned not null,
  obj_desc	varchar(127),
  obj_action	char(1),
  obj_datetime	timestamp,
  primary key	(obj_id),
  index		obj_type_idx (obj_type),
  index		obj_type_id_idx (obj_type_id),
  index		obj_desc_idx (obj_desc)
);

drop table if exists msg_log;
create table msg_log (
  msl_id	mediumint unsigned not null auto_increment,
  msl_m_id	mediumint unsigned not null,
  msl_u_id	mediumint unsigned not null,
  msl_ca_id	mediumint unsigned not null,
  msl_action	char(1),
  msl_datetime	timestamp,
  primary key	(msl_id),
  index		msl_m_id_idx (msl_m_id),
  index		msl_u_id_idx (msl_u_id),
  index		msl_ca_id_idx (msl_ca_id)
);



1.1                  maildb/libmaildb/db/mysql/doc/message.sql

Index: message.sql
===================================================================
delete from msg_ids where m_id < 10;
delete from msg_hdrs where mh_id < 100;
delete from msg_attach where ma_id < 100;

insert into msg_ids values (NULL, '<001801c27523$aa5d46a0$af270444@CX434770b>');

insert into msg_hdrs values (NULL, 1,
       'From', 'lyu...@co... Wed Oct 16 10:53:33 2002', 0);
insert into msg_hdrs values (NULL, 1,
       'Return-path', '<lyu...@co...>', 1);
insert into msg_hdrs values (NULL, 1,
       'Envelope-to', 'li...@av...', 2);
insert into msg_hdrs values (NULL, 1,
       'Delivery-date', 'Wed, 16 Oct 2002 10:53:33 -0400', 3);
insert into msg_hdrs values (NULL, 1,
'Received', 'from fed1mtao02.cox.net ([68.6.19.243])
          by server627.peel3.com with esmtp (Exim 3.36 #1)
          id 181pYD-0007jN-00
          for li...@av...; Wed, 16 Oct 2002 10:53:33 -0400', 4);
insert into msg_hdrs values (NULL, 1,
'Received', 'from CX434770b ([68.4.39.175]) by fed1mtao02.cox.net
         (InterMail vM.5.01.04.05 201-253-122-122-105-20011231) with SMTP
         id <20021016145235.TLKB25985.fed1mtao02.cox.net@CX434770b>
         for <li...@av...>; Wed, 16 Oct 2002 10:52:35 -0400', 5);
insert into msg_hdrs values (NULL, 1,
       'Message-ID', 1, 6);
insert into msg_hdrs values (NULL, 1,
       'From', '"Luci Ursich" <lyu...@co...>', 7);
insert into msg_hdrs values (NULL, 1,
       'To', '"Liza Weissler" <li...@av...>', 8);
insert into msg_hdrs values (NULL, 1,
       'Subject', 'estee lauder', 9);
insert into msg_hdrs values (NULL, 1,
       'Date', 'Wed, 16 Oct 2002 07:52:36 -0700', 10);
insert into msg_hdrs values (NULL, 1,
       'MIME-Version', '1.0', 11);
insert into msg_hdrs values (NULL, 1,
       'Content-Type', 'multipart/alternative;
    boundary="----=_NextPart_000_0015_01C274E8.FDE8EAD0"', 12);
insert into msg_hdrs values (NULL, 1,
       'X-Priority', '3', 13);
insert into msg_hdrs values (NULL, 1,
       'X-MSMail-Priority', 'Normal', 14);
insert into msg_hdrs values (NULL, 1,
       'X-Mailer', 'Microsoft Outlook Express 6.00.2720.3000', 15);
insert into msg_hdrs values (NULL, 1,
       'X-MimeOLE', 'Produced By Microsoft MimeOLE V6.00.2600.0000', 16);
insert into msg_hdrs values (NULL, 1,
       'Status', 'R', 17);

insert into msg_attach values (NULL,
       1,
       1,
       '',
       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_users values (NULL, 1, 1, 1, 10, 7, 9, NULL);




1.1                  maildb/libmaildb/db/mysql/doc/mime.sql

Index: mime.sql
===================================================================
insert into mime_types values (NULL, 'text/plain');
insert into mime_types values (NULL, 'text/richtext');
insert into mime_types values (NULL, 'text/enriched');
insert into mime_types values (NULL, 'text/tab-separated-values');
insert into mime_types values (NULL, 'text/html');
insert into mime_types values (NULL, 'text/sgml');
insert into mime_types values (NULL, 'text/vnd.latex-z');
insert into mime_types values (NULL, 'text/vnd.fmi.flexstor');
insert into mime_types values (NULL, 'text/uri-list');
insert into mime_types values (NULL, 'text/vnd.abc');
insert into mime_types values (NULL, 'text/rfc822-headers');
insert into mime_types values (NULL, 'text/vnd.in3d.3dml');
insert into mime_types values (NULL, 'text/prs.lines.tag');
insert into mime_types values (NULL, 'text/vnd.in3d.spot');
insert into mime_types values (NULL, 'text/css');
insert into mime_types values (NULL, 'text/xml');
insert into mime_types values (NULL, 'text/xml-external-parsed-entity');
insert into mime_types values (NULL, 'text/rtf');
insert into mime_types values (NULL, 'text/directory');
insert into mime_types values (NULL, 'text/calendar');
insert into mime_types values (NULL, 'text/vnd.wap.wml');
insert into mime_types values (NULL, 'text/vnd.wap.wmlscript');
insert into mime_types values (NULL, 'text/vnd.motorola.reflex');
insert into mime_types values (NULL, 'text/vnd.fly');
insert into mime_types values (NULL, 'text/vnd.wap.sl');
insert into mime_types values (NULL, 'text/vnd.wap.si');
insert into mime_types values (NULL, 'text/t140');
insert into mime_types values (NULL, 'text/vnd.ms-mediapackage');
insert into mime_types values (NULL, 'text/vnd.IPTC.NewsML');
insert into mime_types values (NULL, 'text/vnd.IPTC.NITF');
insert into mime_types values (NULL, 'text/vnd.curl');
insert into mime_types values (NULL, 'text/vnd.DMClientScript');
insert into mime_types values (NULL, 'text/parityfec');

insert into mime_types values (NULL, 'multipart/mixed');
insert into mime_types values (NULL, 'multipart/alternative');
insert into mime_types values (NULL, 'multipart/digest');
insert into mime_types values (NULL, 'multipart/parallel');
insert into mime_types values (NULL, 'multipart/appledouble');
insert into mime_types values (NULL, 'multipart/header-set');
insert into mime_types values (NULL, 'multipart/form-data');
insert into mime_types values (NULL, 'multipart/related');
insert into mime_types values (NULL, 'multipart/report');
insert into mime_types values (NULL, 'multipart/voice-message');
insert into mime_types values (NULL, 'multipart/signed');
insert into mime_types values (NULL, 'multipart/encrypted');
insert into mime_types values (NULL, 'multipart/byteranges');

insert into mime_types values (NULL, 'message/rfc822');
insert into mime_types values (NULL, 'message/partial');
insert into mime_types values (NULL, 'message/external-body');
insert into mime_types values (NULL, 'message/news');
insert into mime_types values (NULL, 'message/http');
insert into mime_types values (NULL, 'message/delivery-status');
insert into mime_types values (NULL, 'message/disposition-notification');
insert into mime_types values (NULL, 'message/s-http');

insert into mime_types values (NULL, 'application/octet-stream');
insert into mime_types values (NULL, 'application/postscript');
insert into mime_types values (NULL, 'application/oda');
insert into mime_types values (NULL, 'application/atomicmail');
insert into mime_types values (NULL, 'application/andrew-inset');
insert into mime_types values (NULL, 'application/slate');
insert into mime_types values (NULL, 'application/wita');
insert into mime_types values (NULL, 'application/dec-dx');
insert into mime_types values (NULL, 'application/dca-rft');
insert into mime_types values (NULL, 'application/activemessage');
insert into mime_types values (NULL, 'application/rtf');
insert into mime_types values (NULL, 'application/applefile');
insert into mime_types values (NULL, 'application/mac-binhex40');
insert into mime_types values (NULL, 'application/news-message-id');
insert into mime_types values (NULL, 'application/news-transmission');
insert into mime_types values (NULL, 'application/wordperfect5.1');
insert into mime_types values (NULL, 'application/pdf');
insert into mime_types values (NULL, 'application/zip');
insert into mime_types values (NULL, 'application/macwriteii');
insert into mime_types values (NULL, 'application/msword');
insert into mime_types values (NULL, 'application/remote-printing');
insert into mime_types values (NULL, 'application/mathematica');
insert into mime_types values (NULL, 'application/cybercash');
insert into mime_types values (NULL, 'application/commonground');
insert into mime_types values (NULL, 'application/iges');
insert into mime_types values (NULL, 'application/riscos');
insert into mime_types values (NULL, 'application/eshop');
insert into mime_types values (NULL, 'application/x400-bp');
insert into mime_types values (NULL, 'application/sgml');
insert into mime_types values (NULL, 'application/cals-1840');
insert into mime_types values (NULL, 'application/pgp-encrypted');
insert into mime_types values (NULL, 'application/pgp-signature');
insert into mime_types values (NULL, 'application/pgp-keys');
insert into mime_types values (NULL, 'application/vnd.framemaker');
insert into mime_types values (NULL, 'application/vnd.mif');
insert into mime_types values (NULL, 'application/vnd.ms-excel');
insert into mime_types values (NULL, 'application/vnd.ms-powerpoint');
insert into mime_types values (NULL, 'application/vnd.ms-project');
insert into mime_types values (NULL, 'application/vnd.ms-works');
insert into mime_types values (NULL, 'application/vnd.ms-tnef');
insert into mime_types values (NULL, 'application/vnd.svd');
insert into mime_types values (NULL, 'application/vnd.music-niff');
insert into mime_types values (NULL, 'application/vnd.ms-artgalry');
insert into mime_types values (NULL, 'application/vnd.truedoc');
insert into mime_types values (NULL, 'application/vnd.koan');
insert into mime_types values (NULL, 'application/vnd.street-stream');
insert into mime_types values (NULL, 'application/vnd.fdf');
insert into mime_types values (NULL, 'application/set-payment-initiation');
insert into mime_types values (NULL, 'application/set-payment');
insert into mime_types values (NULL, 'application/set-registration-initiation');
insert into mime_types values (NULL, 'application/set-registration');
insert into mime_types values (NULL, 'application/vnd.seemail');
insert into mime_types values (NULL, 'application/vnd.businessobjects');
insert into mime_types values (NULL, 'application/vnd.meridian-slingshot');
insert into mime_types values (NULL, 'application/vnd.xara');
insert into mime_types values (NULL, 'application/sgml-open-catalog');
insert into mime_types values (NULL, 'application/vnd.rapid');
insert into mime_types values (NULL, 'application/vnd.enliven');
insert into mime_types values (NULL, 'application/vnd.japannet-registration-wakeup');
insert into mime_types values (NULL, 'application/vnd.japannet-verification-wakeup');
insert into mime_types values (NULL, 'application/vnd.japannet-payment-wakeup');
insert into mime_types values (NULL, 'application/vnd.japannet-directory-service');
insert into mime_types values (NULL, 'application/vnd.intertrust.digibox');
insert into mime_types values (NULL, 'application/vnd.intertrust.nncp');
insert into mime_types values (NULL, 'application/prs.alvestrand.titrax-sheet');
insert into mime_types values (NULL, 'application/vnd.noblenet-web');
insert into mime_types values (NULL, 'application/vnd.noblenet-sealer');
insert into mime_types values (NULL, 'application/vnd.noblenet-directory');
insert into mime_types values (NULL, 'application/prs.nprend');
insert into mime_types values (NULL, 'application/vnd.webturbo');
insert into mime_types values (NULL, 'application/hyperstudio');
insert into mime_types values (NULL, 'application/vnd.shana.informed.formtemplate');
insert into mime_types values (NULL, 'application/vnd.shana.informed.formdata');
insert into mime_types values (NULL, 'application/vnd.shana.informed.package');
insert into mime_types values (NULL, 'application/vnd.shana.informed.interchange');
insert into mime_types values (NULL, 'application/vnd.$commerce_battelle');
insert into mime_types values (NULL, 'application/vnd.osa.netdeploy');
insert into mime_types values (NULL, 'application/vnd.ibm.MiniPay');
insert into mime_types values (NULL, 'application/vnd.japannet-jpnstore-wakeup');
insert into mime_types values (NULL, 'application/vnd.japannet-setstore-wakeup');
insert into mime_types values (NULL, 'application/vnd.japannet-verification');
insert into mime_types values (NULL, 'application/vnd.japannet-registration');
insert into mime_types values (NULL, 'application/vnd.hp-HPGL');
insert into mime_types values (NULL, 'application/vnd.hp-PCL');
insert into mime_types values (NULL, 'application/vnd.hp-PCLXL');
insert into mime_types values (NULL, 'application/vnd.musician');
insert into mime_types values (NULL, 'application/vnd.FloGraphIt');
insert into mime_types values (NULL, 'application/vnd.intercon.formnet');
insert into mime_types values (NULL, 'application/vemmi');
insert into mime_types values (NULL, 'application/vnd.ms-asf');
insert into mime_types values (NULL, 'application/vnd.ecdis-update');
insert into mime_types values (NULL, 'application/vnd.powerbuilder6');
insert into mime_types values (NULL, 'application/vnd.powerbuilder6-s');
insert into mime_types values (NULL, 'application/vnd.lotus-wordpro');
insert into mime_types values (NULL, 'application/vnd.lotus-approach');
insert into mime_types values (NULL, 'application/vnd.lotus-1-2-3');
insert into mime_types values (NULL, 'application/vnd.lotus-organizer');
insert into mime_types values (NULL, 'application/vnd.lotus-screencam');
insert into mime_types values (NULL, 'application/vnd.lotus-freelance');
insert into mime_types values (NULL, 'application/vnd.fujitsu.oasys');
insert into mime_types values (NULL, 'application/vnd.fujitsu.oasys2');
insert into mime_types values (NULL, 'application/vnd.swiftview-ics');
insert into mime_types values (NULL, 'application/vnd.dna');
insert into mime_types values (NULL, 'application/prs.cww');
insert into mime_types values (NULL, 'application/vnd.wt.stf');
insert into mime_types values (NULL, 'application/vnd.dxr');
insert into mime_types values (NULL, 'application/vnd.mitsubishi.misty-guard.trustweb');
insert into mime_types values (NULL, 'application/vnd.ibm.modcap');
insert into mime_types values (NULL, 'application/vnd.acucobol');
insert into mime_types values (NULL, 'application/vnd.fujitsu.oasys3');
insert into mime_types values (NULL, 'application/marc');
insert into mime_types values (NULL, 'application/vnd.fujitsu.oasysprs');
insert into mime_types values (NULL, 'application/vnd.fujitsu.oasysgp');
insert into mime_types values (NULL, 'application/vnd.visio');
insert into mime_types values (NULL, 'application/vnd.netfpx');
insert into mime_types values (NULL, 'application/vnd.audiograph');
insert into mime_types values (NULL, 'application/vnd.epson.salt');
insert into mime_types values (NULL, 'application/vnd.3M.Post-it-Notes');
insert into mime_types values (NULL, 'application/vnd.novadigm.EDX');
insert into mime_types values (NULL, 'application/vnd.novadigm.EXT');
insert into mime_types values (NULL, 'application/vnd.novadigm.EDM');
insert into mime_types values (NULL, 'application/vnd.claymore');
insert into mime_types values (NULL, 'application/vnd.comsocaller');
insert into mime_types values (NULL, 'application/pkcs7-mime');
insert into mime_types values (NULL, 'application/pkcs7-signature');
insert into mime_types values (NULL, 'application/pkcs10');
insert into mime_types values (NULL, 'application/vnd.yellowriver-custom-menu');
insert into mime_types values (NULL, 'application/vnd.ecowin.chart');
insert into mime_types values (NULL, 'application/vnd.ecowin.series');
insert into mime_types values (NULL, 'application/vnd.ecowin.filerequest');
insert into mime_types values (NULL, 'application/vnd.ecowin.fileupdate');
insert into mime_types values (NULL, 'application/vnd.ecowin.seriesrequest');
insert into mime_types values (NULL, 'application/vnd.ecowin.seriesupdate');
insert into mime_types values (NULL, 'application/EDIFACT');
insert into mime_types values (NULL, 'application/EDI-X12');
insert into mime_types values (NULL, 'application/EDI-Consent');
insert into mime_types values (NULL, 'application/vnd.wrq-hp3000-labelled');
insert into mime_types values (NULL, 'application/vnd.minisoft-hp3000-save');
insert into mime_types values (NULL, 'application/vnd.ffsns');
insert into mime_types values (NULL, 'application/vnd.hp-hps');
insert into mime_types values (NULL, 'application/vnd.fujixerox.docuworks');
insert into mime_types values (NULL, 'application/xml');
insert into mime_types values (NULL, 'application/xml-external-parsed-entity');
insert into mime_types values (NULL, 'application/xml-dtd');
insert into mime_types values (NULL, 'application/vnd.anser-web-funds-transfer-initiation');
insert into mime_types values (NULL, 'application/vnd.anser-web-certificate-issue-initiation');
insert into mime_types values (NULL, 'application/vnd.is-xpr');
insert into mime_types values (NULL, 'application/vnd.intu.qbo');
insert into mime_types values (NULL, 'application/vnd.publishare-delta-tree');
insert into mime_types values (NULL, 'application/vnd.cybank');
insert into mime_types values (NULL, 'application/batch-SMTP');
insert into mime_types values (NULL, 'application/vnd.uplanet.alert');
insert into mime_types values (NULL, 'application/vnd.uplanet.cacheop');
insert into mime_types values (NULL, 'application/vnd.uplanet.list');
insert into mime_types values (NULL, 'application/vnd.uplanet.listcmd');
insert into mime_types values (NULL, 'application/vnd.uplanet.channel');
insert into mime_types values (NULL, 'application/vnd.uplanet.bearer-choice');
insert into mime_types values (NULL, 'application/vnd.uplanet.signal');
insert into mime_types values (NULL, 'application/vnd.uplanet.alert-wbxml');
insert into mime_types values (NULL, 'application/vnd.uplanet.cacheop-wbxml');
insert into mime_types values (NULL, 'application/vnd.uplanet.list-wbxml');
insert into mime_types values (NULL, 'application/vnd.uplanet.listcmd-wbxml');
insert into mime_types values (NULL, 'application/vnd.uplanet.channel-wbxml');
insert into mime_types values (NULL, 'application/vnd.uplanet.bearer-choice-wbxml');
insert into mime_types values (NULL, 'application/vnd.epson.quickanime');
insert into mime_types values (NULL, 'application/vnd.commonspace');
insert into mime_types values (NULL, 'application/vnd.fut-misnet');
insert into mime_types values (NULL, 'application/vnd.xfdl');
insert into mime_types values (NULL, 'application/vnd.intu.qfx');
insert into mime_types values (NULL, 'application/vnd.epson.ssf');
insert into mime_types values (NULL, 'application/vnd.epson.msf');
insert into mime_types values (NULL, 'application/vnd.powerbuilder7');
insert into mime_types values (NULL, 'application/vnd.powerbuilder7-s');
insert into mime_types values (NULL, 'application/vnd.lotus-notes');
insert into mime_types values (NULL, 'application/pkixcmp');
insert into mime_types values (NULL, 'application/vnd.wap.wmlc');
insert into mime_types values (NULL, 'application/vnd.wap.wmlscriptc');
insert into mime_types values (NULL, 'application/vnd.motorola.flexsuite');
insert into mime_types values (NULL, 'application/vnd.wap.wbxml');
insert into mime_types values (NULL, 'application/vnd.motorola.flexsuite.wem');
insert into mime_types values (NULL, 'application/vnd.motorola.flexsuite.kmr');
insert into mime_types values (NULL, 'application/vnd.motorola.flexsuite.adsi');
insert into mime_types values (NULL, 'application/vnd.motorola.flexsuite.fis');
insert into mime_types values (NULL, 'application/vnd.motorola.flexsuite.gotap');
insert into mime_types values (NULL, 'application/vnd.motorola.flexsuite.ttc');
insert into mime_types values (NULL, 'application/vnd.ufdl');
insert into mime_types values (NULL, 'application/vnd.accpac.simply.imp');
insert into mime_types values (NULL, 'application/vnd.accpac.simply.aso');
insert into mime_types values (NULL, 'application/vnd.vcx');
insert into mime_types values (NULL, 'application/ipp');
insert into mime_types values (NULL, 'application/ocsp-request');
insert into mime_types values (NULL, 'application/ocsp-response');
insert into mime_types values (NULL, 'application/vnd.previewsystems.box');
insert into mime_types values (NULL, 'application/vnd.mediastation.cdkey');
insert into mime_types values (NULL, 'application/vnd.pg.format');
insert into mime_types values (NULL, 'application/vnd.pg.osasli');
insert into mime_types values (NULL, 'application/vnd.hp-hpid');
insert into mime_types values (NULL, 'application/pkix-cert');
insert into mime_types values (NULL, 'application/pkix-crl');
insert into mime_types values (NULL, 'application/vnd.Mobius.TXF');
insert into mime_types values (NULL, 'application/vnd.Mobius.PLC');
insert into mime_types values (NULL, 'application/vnd.Mobius.DIS');
insert into mime_types values (NULL, 'application/vnd.Mobius.DAF');
insert into mime_types values (NULL, 'application/vnd.Mobius.MSL');
insert into mime_types values (NULL, 'application/vnd.cups-raster');
insert into mime_types values (NULL, 'application/vnd.cups-postscript');
insert into mime_types values (NULL, 'application/vnd.cups-raw');
insert into mime_types values (NULL, 'application/index');
insert into mime_types values (NULL, 'application/index.cmd');
insert into mime_types values (NULL, 'application/index.response');
insert into mime_types values (NULL, 'application/index.obj');
insert into mime_types values (NULL, 'application/index.vnd');
insert into mime_types values (NULL, 'application/vnd.triscape.mxs');
insert into mime_types values (NULL, 'application/vnd.powerbuilder75');
insert into mime_types values (NULL, 'application/vnd.powerbuilder75-s');
insert into mime_types values (NULL, 'application/vnd.dpgraph');
insert into mime_types values (NULL, 'application/http');
insert into mime_types values (NULL, 'application/sdp');
insert into mime_types values (NULL, 'application/vnd.eudora.data');
insert into mime_types values (NULL, 'application/vnd.fujixerox.docuworks.binder');
insert into mime_types values (NULL, 'application/vnd.vectorworks');
insert into mime_types values (NULL, 'application/vnd.grafeq');
insert into mime_types values (NULL, 'application/vnd.bmi');
insert into mime_types values (NULL, 'application/vnd.ericsson.quickcall');
insert into mime_types values (NULL, 'application/vnd.hzn-3d-crossword');
insert into mime_types values (NULL, 'application/vnd.wap.slc');
insert into mime_types values (NULL, 'application/vnd.wap.sic');
insert into mime_types values (NULL, 'application/vnd.groove-injector');
insert into mime_types values (NULL, 'application/vnd.fujixerox.ddd');
insert into mime_types values (NULL, 'application/vnd.groove-account');
insert into mime_types values (NULL, 'application/vnd.groove-identity-message');
insert into mime_types values (NULL, 'application/vnd.groove-tool-message');
insert into mime_types values (NULL, 'application/vnd.groove-tool-template');
insert into mime_types values (NULL, 'application/vnd.groove-vcard');
insert into mime_types values (NULL, 'application/vnd.ctc-posml');
insert into mime_types values (NULL, 'application/vnd.canon-lips');
insert into mime_types values (NULL, 'application/vnd.canon-cpdl');
insert into mime_types values (NULL, 'application/vnd.trueapp');
insert into mime_types values (NULL, 'application/vnd.s3sms');
insert into mime_types values (NULL, 'application/iotp');
insert into mime_types values (NULL, 'application/vnd.mcd');
insert into mime_types values (NULL, 'application/vnd.httphone');
insert into mime_types values (NULL, 'application/vnd.informix-visionary');
insert into mime_types values (NULL, 'application/vnd.msign');
insert into mime_types values (NULL, 'application/vnd.ms-lrm');
insert into mime_types values (NULL, 'application/vnd.contact.cmsg');
insert into mime_types values (NULL, 'application/vnd.epson.esf');
insert into mime_types values (NULL, 'application/whoispp-query');
insert into mime_types values (NULL, 'application/whoispp-response');
insert into mime_types values (NULL, 'application/vnd.mozilla.xul+xml');
insert into mime_types values (NULL, 'application/parityfec');
insert into mime_types values (NULL, 'application/vnd.palm');
insert into mime_types values (NULL, 'application/vnd.fsc.weblaunch');
insert into mime_types values (NULL, 'application/vnd.tve-trigger');
insert into mime_types values (NULL, 'application/dvcs');
insert into mime_types values (NULL, 'application/sieve');
insert into mime_types values (NULL, 'application/vnd.vividence.scriptfile');
insert into mime_types values (NULL, 'application/vnd.hhe.lesson-player');
insert into mime_types values (NULL, 'application/beep+xml');
insert into mime_types values (NULL, 'application/font-tdpfr');
insert into mime_types values (NULL, 'application/vnd.mseq');
insert into mime_types values (NULL, 'application/vnd.aether.imp');
insert into mime_types values (NULL, 'application/vnd.Mobius.MQY');
insert into mime_types values (NULL, 'application/vnd.Mobius.MBK');
insert into mime_types values (NULL, 'application/vnd.vidsoft.vidconference');
insert into mime_types values (NULL, 'application/vnd.ibm.afplinedata');
insert into mime_types values (NULL, 'application/vnd.irepository.package+xml');
insert into mime_types values (NULL, 'application/vnd.sss-ntf');
insert into mime_types values (NULL, 'application/vnd.sss-dtf');
insert into mime_types values (NULL, 'application/vnd.sss-cod');
insert into mime_types values (NULL, 'application/vnd.pvi.ptid1');
insert into mime_types values (NULL, 'application/isup');
insert into mime_types values (NULL, 'application/qsig');
insert into mime_types values (NULL, 'application/timestamp-query');
insert into mime_types values (NULL, 'application/timestamp-reply');
insert into mime_types values (NULL, 'application/vnd.pwg-xhtml-print+xml');

insert into mime_types values (NULL, 'image/jpeg');
insert into mime_types values (NULL, 'image/gif');
insert into mime_types values (NULL, 'image/ief');
insert into mime_types values (NULL, 'image/g3fax');
insert into mime_types values (NULL, 'image/tiff');
insert into mime_types values (NULL, 'image/cgm');
insert into mime_types values (NULL, 'image/naplps');
insert into mime_types values (NULL, 'image/vnd.dwg');
insert into mime_types values (NULL, 'image/vnd.svf');
insert into mime_types values (NULL, 'image/vnd.dxf');
insert into mime_types values (NULL, 'image/png');
insert into mime_types values (NULL, 'image/vnd.fpx');
insert into mime_types values (NULL, 'image/vnd.net-fpx');
insert into mime_types values (NULL, 'image/vnd.xiff');
insert into mime_types values (NULL, 'image/prs.btif');
insert into mime_types values (NULL, 'image/vnd.fastbidsheet');
insert into mime_types values (NULL, 'image/vnd.wap.wbmp');
insert into mime_types values (NULL, 'image/prs.pti');
insert into mime_types values (NULL, 'image/vnd.cns.inf2');
insert into mime_types values (NULL, 'image/vnd.mix');
insert into mime_types values (NULL, 'image/vnd.fujixerox.edmics-rlc');
insert into mime_types values (NULL, 'image/vnd.fujixerox.edmics-mmr');
insert into mime_types values (NULL, 'image/vnd.fst');

insert into mime_types values (NULL, 'audio/basic');
insert into mime_types values (NULL, 'audio/32kadpcm');
insert into mime_types values (NULL, 'audio/vnd.qcelp');
insert into mime_types values (NULL, 'audio/vnd.digital-winds');
insert into mime_types values (NULL, 'audio/vnd.lucent.voice');
insert into mime_types values (NULL, 'audio/vnd.octel.sbc');
insert into mime_types values (NULL, 'audio/vnd.rhetorex.32kadpcm');
insert into mime_types values (NULL, 'audio/vnd.vmx.cvsd');
insert into mime_types values (NULL, 'audio/vnd.nortel.vbk');
insert into mime_types values (NULL, 'audio/vnd.cns.anp1');
insert into mime_types values (NULL, 'audio/vnd.cns.inf1');
insert into mime_types values (NULL, 'audio/L16');
insert into mime_types values (NULL, 'audio/vnd.everad.plj');
insert into mime_types values (NULL, 'audio/telephone-event');
insert into mime_types values (NULL, 'audio/tone');
insert into mime_types values (NULL, 'audio/prs.sid');
insert into mime_types values (NULL, 'audio/vnd.nuera.ecelp4800');
insert into mime_types values (NULL, 'audio/vnd.nuera.ecelp7470');
insert into mime_types values (NULL, 'audio/mpeg');
insert into mime_types values (NULL, 'audio/parityfec');
insert into mime_types values (NULL, 'audio/MP4A-LATM');
insert into mime_types values (NULL, 'audio/vnd.nuera.ecelp9600');
insert into mime_types values (NULL, 'audio/G.722.1');
insert into mime_types values (NULL, 'audio/mpa-robust');
insert into mime_types values (NULL, 'audio/vnd.cisco.nse');
insert into mime_types values (NULL, 'audio/DAT12');
insert into mime_types values (NULL, 'audio/L20');
insert into mime_types values (NULL, 'audio/L24');

insert into mime_types values (NULL, 'video/mpeg');
insert into mime_types values (NULL, 'video/quicktime');
insert into mime_types values (NULL, 'video/vnd.vivo');
insert into mime_types values (NULL, 'video/vnd.motorola.video');
insert into mime_types values (NULL, 'video/vnd.motorola.videop');
insert into mime_types values (NULL, 'video/vnd.fvt');
insert into mime_types values (NULL, 'video/pointer');
insert into mime_types values (NULL, 'video/parityfec');
insert into mime_types values (NULL, 'video/vnd.mpegurl');
insert into mime_types values (NULL, 'video/MP4V-ES');
insert into mime_types values (NULL, 'video/vnd.nokia.interleaved-multimedia');
     
insert into mime_types values (NULL, 'model/iges');
insert into mime_types values (NULL, 'model/vrml');
insert into mime_types values (NULL, 'model/mesh');
insert into mime_types values (NULL, 'model/vnd.dwf');
insert into mime_types values (NULL, 'model/vnd.gtw');
insert into mime_types values (NULL, 'model/vnd.flatland.3dml');
insert into mime_types values (NULL, 'model/vnd.vtu');
insert into mime_types values (NULL, 'model/vnd.mts');
insert into mime_types values (NULL, 'model/vnd.gdl');
insert into mime_types values (NULL, 'model/vnd.gs-gdl');
insert into mime_types values (NULL, 'model/vnd.parasolid.transmit.text');
insert into mime_types values (NULL, 'model/vnd.parasolid.transmit.binary');



1.1                  maildb/libmaildb/db/mysql/doc/queries.sql

Index: queries.sql
===================================================================
-- -------------------------
-- Sample Queries
-- -------------------------

-- Category scan

  SELECT mu_m_id, m_msg_id, a.mh_value as date, b.mh_value as hfrom,
	 c.mh_value as subject, mu_flags
    FROM msg_users, msg_hdrs a, msg_hdrs b, msg_hdrs c, msg_ids
   WHERE mu_u_id = :u_id
     AND mu_ca_id = :ca_id
     AND mu_date = a.mh_id
     AND mu_from = b.mh_id
     AND mu_subject = c.mh_id
ORDER BY a.mh_value
;

-- Construct a message
-- Note this is two separate queries...to get the headers
-- and attachments in the correct sort order.  If the application
-- will parse out/sort the returned data we can get this all in one big
-- query.
  
  SELECT mh_m_id, m_msg_id, mh_key, mh_value, mh_sort_order
    FROM msg_hdrs, msg_ids
   WHERE mh_m_id = m_id
     AND m_id = :m_id
ORDER BY mh_sort_order
;

  SELECT ma_m_id, m_msg_id, mt_desc, ma_encode, ma_body, ma_path,
	 ma_sep_data, ma_storage, ma_sort_order
    FROM msg_attach, mime_types, msg_ids
   WHERE ma_m_id = :m_id
     AND m_id = ma_m_id
     AND ma_mt_id = mt_id
ORDER BY ma_sort_order
;

-- Scan list of all messages from lyursich
-- 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_users, msg_hdrs a, msg_hdrs b, msg_hdrs c
   WHERE mu_u_id = :u_id
     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

  SELECT count(*)
    FROM cats
   WHERE ca_u_id = :u_id
     AND ca_id = :ca_id
;








1.1                  maildb/libmaildb/db/mysql/doc/test.sql

Index: test.sql
===================================================================
-- -------------------------
-- Sample Queries
-- -------------------------

-- Category scan

  SELECT mu_m_id, m_msg_id, a.mh_value as date, b.mh_value as hfrom,
	 c.mh_value as subject, mu_flags
    FROM msg_users, msg_hdrs a, msg_hdrs b, msg_hdrs c, msg_ids
   WHERE mu_u_id = 1
     AND mu_ca_id = 1
     AND mu_date = a.mh_id
     AND mu_from = b.mh_id
     AND mu_subject = c.mh_id
ORDER BY a.mh_value
;

-- Construct a message
  
  SELECT mh_m_id, m_msg_id, mh_key, mh_value, mh_sort_order
    FROM msg_hdrs, msg_ids
   WHERE mh_m_id = m_id
     AND m_id = 1
ORDER BY mh_sort_order
;

  SELECT ma_m_id, m_msg_id, mt_desc, ma_encode, ma_body, ma_path,
	 ma_sep_data, ma_storage, ma_sort_order
    FROM msg_attach, mime_types, msg_ids
   WHERE ma_m_id = 1
     AND m_id = ma_m_id
     AND ma_mt_id = mt_id
ORDER BY ma_sort_order
;

-- Scan list of all messages from li...@av...
-- 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_users, 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

  SELECT count(*)
    FROM cats
   WHERE ca_u_id = 1
     AND ca_id = 1
;








1.1                  maildb/libmaildb/db/mysql/doc/users.sql

Index: users.sql
===================================================================
insert into users values (NULL, 'Liza Weissler');
insert into cats values (NULL, 1, 'Inbox', NULL);