Mail.app’s email address database


Did you ever notice how Mac OS X’s Mail.app remembers a whole bunch of email addresses that are not in your Address Book?

Did you ever wonder where they were kept?

Wonder no more..


sqlite3 ~/Library/Mail/"Envelope Index" 'select address, comment from addresses'

Using SQLite‘s ‘.schema’ command, we get a table layout like this:


CREATE TABLE addresses (ROWID INTEGER PRIMARY KEY, address, comment, UNIQUE(address, comment));
CREATE TABLE attachments (ROWID INTEGER PRIMARY KEY, message_id INTEGER, name, type, UNIQUE(message_id, name));
CREATE TABLE mailboxes (ROWID INTEGER PRIMARY KEY, url UNIQUE);
CREATE TABLE messages (ROWID INTEGER PRIMARY KEY AUTOINCREMENT, message_id, in_reply_to, remote_id INTEGER, sender INTEGER, subject_prefix, subject INTEGER, date_sent INTEGER, date_received INTEGER, date_last_viewed INTEGER, mailbox INTEGER, remote_mailbox INTEGER, original_mailbox INTEGER, flags INTEGER, read, flagged, size INTEGER, color, encoding, pad);
CREATE TABLE properties (ROWID INTEGER PRIMARY KEY, key, value, UNIQUE (key));
CREATE TABLE recipients (ROWID INTEGER PRIMARY KEY, message_id INTEGER, type, address_id INTEGER);
CREATE TABLE sqlite_sequence(name,seq);
CREATE TABLE subjects (ROWID INTEGER PRIMARY KEY, subject);
CREATE TABLE threads (ROWID INTEGER PRIMARY KEY, message_id INTEGER, reference, is_originator);
CREATE INDEX address_address_index ON addresses(address);
CREATE INDEX attachments_type_index ON attachments(type);
CREATE INDEX date_index ON messages(date_received);
CREATE INDEX date_last_viewed_index ON messages(date_last_viewed);
CREATE INDEX message_flagged_index ON messages(flagged);
CREATE INDEX message_mailbox_index ON messages(mailbox, date_received);
CREATE INDEX message_message_id_index ON messages(message_id);
CREATE INDEX message_read_index ON messages(read);
CREATE INDEX message_remote_mailbox_index ON messages(remote_mailbox, remote_id);
CREATE INDEX message_sender_index ON messages(sender);
CREATE INDEX recipients_address_index ON recipients(address_id);
CREATE INDEX recipients_message_id_index ON recipients(message_id);
CREATE INDEX references_message_id_index ON threads(message_id);
CREATE INDEX references_reference_index ON threads(reference);
CREATE INDEX subject_index ON messages(subject);
CREATE INDEX subject_subject_index ON subjects(subject);
CREATE TRIGGER after_delete_message AFTER DELETE ON messages BEGIN DELETE FROM threads WHERE threads.message_id == OLD.ROWID; DELETE FROM attachments WHERE attachments.message_id == OLD.ROWID; DELETE FROM recipients WHERE recipients.message_id == OLD.ROWID; DELETE FROM subjects WHERE ROWID = OLD.subject AND (SELECT COUNT() FROM messages WHERE subject = OLD.subject) = 0; DELETE FROM addresses WHERE ROWID = OLD.sender AND (SELECT COUNT() FROM messages WHERE sender = OLD.sender) + (SELECT COUNT() FROM recipients WHERE address_id = OLD.sender) = 0; END;

Hm, that last line is pretty long and it has ‘;’s in it.
I wonder if it has to be all one line or not?
Ah well.

I’m sure there’s much more information to be mined from this.
No time to poke about right now though..

Published by

rae

Mac developer

2 thoughts on “Mail.app’s email address database”

  1. Wow; programming the database itself to clean up after itself; Who does that anymore? :-)

    (That last line is basically “when someone deletes a message, delete all unused data for that message from the other tables”).

Leave a Reply

Your email address will not be published. Required fields are marked *

To create code blocks or other preformatted text, indent by four spaces:

    This will be displayed in a monospaced font. The first four 
    spaces will be stripped off, but all other whitespace
    will be preserved.
    
    Markdown is turned off in code blocks:
     [This is not a link](http://example.com)

To create not a block, but an inline code span, use backticks:

Here is some inline `code`.

For more help see http://daringfireball.net/projects/markdown/syntax

This site uses Akismet to reduce spam. Learn how your comment data is processed.