开发者

Creating and Indexing Email Database using SqlCe

开发者 https://www.devze.com 2022-12-31 15:42 出处:网络
I am creating a simple email client program. I am using MS SqlCe as a st开发者_如何转开发orage of emails. The database schema for storing the message is as follows:

I am creating a simple email client program. I am using MS SqlCe as a st开发者_如何转开发orage of emails. The database schema for storing the message is as follows:

StorageId int IDENTITY NOT NULL PRIMARY KEY,
FolderName nvarchar(255) NOT NULL,
MessageId nvarchar(3999) NOT NULL,
MessageDate datetime NOT NULL,
StorageData ntext NULL

In the StorageData field I am going to store the MIME message as byte array. But the problem arises when I am going to implement search on the stored messages. I have no idea how I am going to index the messages on top of this schema.

Can anyone please help me in suggesting a good but simple schema, so that it will be effective in terms of storage space and search friendliness as well?

Regards,

Anindya Chatterjee


Some notes, not too helpful, I'm afraid:

  • I believe rfc5322 limits the length of any individual line in an email message to 999 characters. While it is possible to extend a header field over multiple lines, it seems to me it's a reasonable upper bound for the length of a message-id.
  • SQL CE doesn't support full-text search, so basically you'll have to write your own search engine. Chop the text up into words, then create a table of words, paired with a field containing a list of the StorageId fields they link to. Quite a bit of work, and you're probably better off with a third-party solution
  • Consider adding a "parent" field that links threads together based on their message-ids and In-Reply-To/References headers.
0

精彩评论

暂无评论...
验证码 换一张
取 消