mysql 高等套用 zimbra mail 得到 mail status 問題 - 有興趣的可以大家一起研究

ieting

我測試一個 zimbra mail server 他用的資料庫是 mysql 5.0

他有一些機制是不錯的 只是沒有整合起來  因此突發其想 做了一個 可以知道 發件者 發給對方的 mail 之 讀取 轉寄 刪除狀態

原始構想

原來的 zimbra schema 內有一個 mailbox table 他記錄了 user 的 id

user 的mail box 都開另一個 schema 為 mboxgroup[id] 來存放
當有新mail發送及進來時 皆會有一個 table 來存放 該table 是 mail_item
所以 我就 以這些方式 用 trigger 來取得一些資訊 放入另一個 schema mailstat 之 mailstatus內
這樣子 就可以知道 每一封寄出的mail 在 mail server 內 的status

不過 我一個對一個寄是 ok的 但是 一個對多個寄 只會第一個有效 其他的都不會寄進去(因為trigger寫的爛 無法 split)

想請問 我在 trigger 內 要如何 將 mboxgroup[id].mail_item.metadat 內的 資料如
d1:f0:1:s27:kscg <test2@test.tw>1:t20:admin@test.tw1:vi10ee
分出來兩比 各自 insert 進 mailstat.mailstatus.to 內

kscg <test2@test.tw> 一筆
admin@test.tw            一筆

因為 目前只有 一筆觸發 新增一個row 所以後面更新 只有 用 subject 來模糊判斷

我原始 trigger 如下 (每個 schema 都會建立一組)

發送時間  ==>  send_datetime
如收件者何時收到信件  ==>  get_datetime
收件者何時開啟閱讀  ==>  read_datetime
收件者何時收件者何時刪除  ==>  delete_dattetime
收件者何時轉寄  ==>  forward_datetime
發送者 id send_mailbox_id
發送者 郵件編號 send_id
收件者 id  ==>  get_mailbox_id
收件者 郵件編號  ==>  get_id
發送者 mail  ==>  sender
發送主旨 subject  ==>  subject

說明:
*_datetime 有時間的表示已經完成的

CREATE TABLE `mailstatus` (
        `ID_INDEX`                  MEDIUMINT NOT NULL AUTO_INCREMENT,
        `send_datetime`                        DATETIME,
        `get_datetime`                        DATETIME,
        `read_datetime`                        DATETIME,
        `delete_datetime`                DATETIME,
        `forward_datetime`                DATETIME,
        `send_mailbox_id`                INTEGER,
        `send_id`                                INTEGER,
        `get_mailbox_id`                INTEGER,
        `get_id`                                INTEGER,
        `sender`                                VARCHAR(128),
        `to`                                        VARCHAR(128),
        `subject`                                TEXT,
        PRIMARY KEY (ID_INDEX)
);

#Release Version 1.0.0.0
DROP TRIGGER mailsend_get
DROP TRIGGER mailread_forward
DROP TRIGGER maildelete

#mail 發送及收件者收到
CREATE TRIGGER `mailsend_get` AFTER INSERT ON `mail_item`
FOR EACH ROW BEGIN
IF NEW.`flags` = 1 THEN
  INSERT INTO `mailstat`.`mailstatus` SET `send_mailbox_id`=NEW.`mailbox_id`,`send_id`=NEW.`id`,`send_datetime`=date_format(now(),'%Y-%m-%d %H-%i-%S'),`sender`=NEW.`sender`,`subject`=NEW.`subject`;
END IF;
IF NEW.`flags` = 0 THEN
   UPDATE `mailstat`.`mailstatus` SET `get_datetime` = date_format(now(),'%Y-%m-%d %H-%i-%S'),`get_mailbox_id`=NEW.`mailbox_id`,`get_id`=NEW.`id` WHERE `mailstatus`.`subject` like NEW.`subject` AND `mailstatus`.`sender`=NEW.`sender`;
END IF;
END;

#收件者讀取及轉寄
CREATE TRIGGER `mailread_forward` AFTER UPDATE ON `mail_item`
FOR EACH ROW
BEGIN
IF NEW.`unread` = 0 THEN
UPDATE `mailstat`.`mailstatus` SET `mailstatus`.`read_datetime` = date_format(now(),'%Y-%m-%d %H-%i-%S') WHERE `mailstatus`.`get_mailbox_id`=NEW.`mailbox_id` AND `mailstatus`.`get_id`=NEW.`id`;
ELSE
IF NEW.`flags` = 8 THEN
UPDATE `mailstat`.`mailstatus` SET `mailstatus`.`forward_datetime` = date_format(now(),'%Y-%m-%d %H-%i-%S') WHERE `mailstatus`.`get_mailbox_id`=NEW.`mailbox_id` AND `mailstatus`.`get_id`=NEW.`id`;
END IF;
END IF;
END;

#收件者刪除信件(在垃圾桶不算刪除 因為信在垃圾桶裡 完全刪除才是刪除因為完全沒有資料)
CREATE TRIGGER `maildelete` AFTER DELETE ON `mail_item`
FOR EACH ROW UPDATE `mailstat`.`mailstatus` SET `mailstatus`.`delete_datetime` = date_format(now(),'%Y-%m-%d %H-%i-%S') WHERE `mailstatus`.`get_mailbox_id`=OLD.`mailbox_id` AND `mailstatus`.`get_id`=OLD.`id`;



希望大家有興趣的一起研究

附檔是 實際運行 後的 一對一ok的 一對多是不ok的 因為 trigger 只inser 一筆 如果能多筆的話就可以搞定了 (範例的to因為當時沒有找到 如何用 所以 沒有用)


多謝各為大大了 以後 大家用postfix+mysql的 應該很好用 因為 這是未來 mail 的一種新趨勢 可以知道 mail 的status狀態(這個機制目前只有 novell groupwise有 其他mail server都沒有)

[[i] 本帖最后由 ieting 于 2007-11-20 18:22 编辑 [/i]]

yejr

用substr()函数来分别截取，自己稍作处理一下，可以达到split的效果的