mk-mode BLOG

このブログは自作の自宅サーバに構築した Debian GNU/Linux で運用しています。
PC・サーバ構築等の話題を中心に公開しております。(クローンサイト: GitHub Pages

ブログ開設日2009-01-05
サーバ連続稼働時間
Reading...
Page View 合計
Reading...
今日
Reading...
昨日
Reading...

UNIX MBOX メールデータ用 DB設計!

[ プログラミング ] [ Mail, MySQL, Ruby ]

こんばんは。

UNIX MBOX (Thunderbird) メールデータを DB (MySQL) へ登録すべく、最近各種検証を行ってきました。 本文・添付ファイル抽出・デコード部分はとりあえず考えず、DB登録(保存)することだけを考えています。 ※使用する言語は Ruby を想定。

まずは、DB (MySQL) を設計しないいけません。

色々考えた結果、とりあえずは以下のテーブルを用意しました。 ※本当に「とりあえず」です。今後作業していく中で変更が発生する可能性は大です。

テーブル一覧

テーブル名 名称 詳細・説明
mst_mbox メールボックスマスタ メールボックスの基本設定
dat_mail メール登録情報 メールデータの各種登録情報(登録日時等)
dat_header ヘッダデータ メールデータのヘッダデータ
dat_body ボディデータ メールデータの本文、添付ファイル

各テーブルの詳細

Indexは今後の作業の中で追加していくことになると思います。 他についても今後の作業で追加・変更が出てくると思います。

  • メールボックスマスタ

TABLE_MST_MBOX

  • メール登録情報

TABLE_DAT_MAIL_2

  • ヘッダデータ

TABLE_DAT_HEADER_2

  • ボディデータ

TABLE_DAT_BODY

【変更(2011/11/16)】メール登録情報・ヘッダデータを変更(INDEX追加等)

各テーブルのCreateスクリプト

参考までに各テーブルを作成するためのスクリプトも掲載しておきます。 ※DBエンジンは高速性を重視して「MyISAM」にしています。 【メールボックスマスタ】mst_mbox

1
2
3
4
5
6
7
8
9
CREATE TABLE IF NOT EXISTS `mst_mbox` (
  `mbox_id`      smallint(2) NOT NULL,
  `mbox_name`    varchar(50),
  `mbox_name_j`  varchar(50),
  `act_kbn`      smallint(1),
  `ins_datetime` datetime,
  `upd_datetime` datetime,
  PRIMARY KEY (`mbox_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

【メール登録情報】dat_mail

1
2
3
4
5
6
7
8
CREATE TABLE IF NOT EXISTS `dat_mail` (
  `mbox_id`      smallint(2) NOT NULL,
  `mail_id`      integer(8) NOT NULL,
  `act_kbn`      smallint(1),
  `ins_datetime` datetime,
  `upd_datetime` datetime,
  PRIMARY KEY (`mbox_id`, `mail_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

【ヘッダデータ】dat_header

1
2
3
4
5
6
7
8
9
CREATE TABLE IF NOT EXISTS `dat_header` (
  `mbox_id`    smallint(2) NOT NULL,
  `mail_id`    integer(8) NOT NULL,
  `part_no`    smallint(2) NOT NULL,
  `seq_no`     smallint(4) NOT NULL,
  `field_name` varchar(50),
  `field_text` varchar(2000),
  PRIMARY KEY (`mbox_id`, `mail_id`, `part_no`, `seq_no`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

【ボディデータ】dat_body

1
2
3
4
5
6
7
8
CREATE TABLE IF NOT EXISTS `dat_body` (
  `mbox_id`   smallint(2) NOT NULL,
  `mail_id`   integer(8) NOT NULL,
  `part_no`   smallint(2) NOT NULL,
  `line_no`   integer(8) NOT NULL,
  `line_text` varchar(2000),
  PRIMARY KEY (`mbox_id`, `mail_id`, `part_no`, `line_no`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

テーブル関連図(ER図)

TABLE_ER

DB登録の概要

メールヘッダの Content-Type のタイプに “multipart” が設定されている場合は、本文部分が分割されているので、各Part内もヘッダ部分・本文部分に分けて登録するようにしています。 以下を参照。

  • 「メールID」はメールボックス別に読み込んだ順にナンバリングする。
  • ヘッダの Content-Type に “multipart” が設定されていない場合、ボディ部分が単独であるので、「Part№」は 0 とする。(ヘッダ・ボディとも)
  • 1つのヘッダフィールドが複数行にわたる場合、間が半角スペース1つになるようにして結合する。
  • ヘッダを読み込んだ順に、「Seq№」をナンバリングする。( 0: “From "行 )
  • ボディ部分を読み込んだ順に、「行№」をナンバリングする。( 1 ~ )
  • ボディ部分が分割されている場合、各Partを1つのメールとみなして処理する。

  • Boundary文字列単位でPart№をナンバリング。( 1 ~ )

  • 最初のBoundary文字列より前に行が存在する場合は、Part№を 0 とする。

今後は、今までの検証作業で培ってきた Ruby スクリプトを流用しながら、コーディングしていこうと思っています。

以上。

Comments