Missing Brace Blog

One of the first things I did after I got my iPhone 3G was to setup my emails.  In doing so, I soon came to realize one of the first shortcomings of this highly publicized device, which was the missing ability to mark all of my 792 messages as read.  If you too rather spend your time differently than simply sitting there and going through hundreds of messages in order to mark them as read, here is a solution for you.  However, let me first mention that this solution requires a jailbroken iPhone. Also let me say that if you know what you’re doing, all you have to do is to login to the SQLite database and then run the last two update commands at the end of this post.

Begin by installing SSH for your iPhone.  If you don’t know how to do this, visit this tutorial on how to install SSH on your iPhone.  Assuming you are now logged in as root on your iPhone, continue along with the instructions below.

Apple uses an SQLite database to keep track of your mailboxes, and their message counts and stats. Run the following command as root to login to this database:

sqlite3 /private/var/mobile/Library/Mail/Envelope\ Index

Once you’re in SQL console, list the tables buy simply running the following command:

.table

Out of the tables you see, you’re only concerned with messages and mailboxes. We start by examining the structure of the two tables. In SQLite, you can use the .schema followed by a table name to view the structure of a table. Below you can see what you should find when you run the .schema command.

sqlite> .schema mailboxes
CREATE TABLE mailboxes (ROWID INTEGER PRIMARY KEY,
                        url UNIQUE,
                        sequence_identifier TEXT,
                        total_count INTEGER DEFAULT 0,
                        unread_count INTEGER DEFAULT 0,
                        deleted_count INTEGER DEFAULT 0);
sqlite> .schema messages
CREATE TABLE messages (ROWID INTEGER PRIMARY KEY AUTOINCREMENT,
                       remote_id INTEGER, sender,
                       subject,
                       _to,
                       cc,
                       date_sent INTEGER,
                       date_received INTEGER,
                       sort_order INTEGER,
                       mailbox INTEGER,
                       remote_mailbox INTEGER,
                       original_mailbox INTEGER,
                       flags INTEGER,
                       read,
                       flagged,
                       deleted,
                       size INTEGER,
                       color,
                       encoding,
                       content_type);
CREATE INDEX date_index ON messages(date_received);
CREATE INDEX message_mailbox_index ON messages(mailbox, deleted, date_received DESC, sort_order DESC);
CREATE INDEX message_remote_mailbox_index ON messages(remote_mailbox, remote_id);
CREATE TRIGGER after_add_message AFTER INSERT ON messages
  BEGIN
    UPDATE mailboxes SET total_count = total_count + 1 WHERE mailboxes.ROWID = new.mailbox;
    UPDATE mailboxes SET unread_count = unread_count + 1 WHERE mailboxes.ROWID = new.mailbox AND new.flags&1 = 0;
    UPDATE mailboxes SET deleted_count = deleted_count + 1 WHERE mailboxes.ROWID = new.mailbox AND new.flags&2 > 0;
  END;
CREATE TRIGGER after_delete_message AFTER DELETE ON messages
  BEGIN
    DELETE FROM threads WHERE threads.message_id == OLD.ROWID;
    DELETE FROM message_data WHERE message_id = OLD.ROWID;

    UPDATE mailboxes SET total_count = total_count - 1 WHERE mailboxes.ROWID = old.mailbox;
    UPDATE mailboxes SET unread_count = unread_count - 1 WHERE mailboxes.ROWID = old.mailbox AND old.flags&1 = 0;
    UPDATE mailboxes SET deleted_count = deleted_count - 1 WHERE mailboxes.ROWID = old.mailbox AND old.flags&2 > 0;
  END;
CREATE TRIGGER after_update_message AFTER UPDATE ON messages
  BEGIN
    UPDATE mailboxes SET unread_count = unread_count - 1 WHERE mailboxes.ROWID = new.mailbox AND old.flags&1 = 0 AND new.flags&1 > 0;
    UPDATE mailboxes SET unread_count = unread_count + 1 WHERE mailboxes.ROWID = new.mailbox AND old.flags&1 > 0 AND new.flags&1 = 0;
    UPDATE mailboxes SET deleted_count = deleted_count - 1 WHERE mailboxes.ROWID = new.mailbox AND old.flags&2 > 0 AND new.flags&2 = 0;
    UPDATE mailboxes SET deleted_count = deleted_count + 1 WHERE mailboxes.ROWID = new.mailbox AND old.flags&2 = 0 AND new.flags&2 > 0;
  END;

From what gets printed, it should be easy to see how you can quickly mark all your messages as read. First notice that the triggers modify the mailboxes.unread_count. Therefore, what we ultimately want is that mailboxes.unread_count be zero. But there is a catch. Look at the where clause of the update statements and how the bitwise operation on messages.flags column is used to determine the mailboxes.unread_count. Thus if we simply run an update statement to set the mailboxes.unread_count, we’d only be there half way. This is because the next time any of the triggers are called, the mailboxes.unread_count would get updated incorrectly and thus leading numbers in negatives even.

To fix this, I sent an email to myself and looked at the messages.flags value before and after I read the message. Okay, so I do admit that I didn’t try to understand the full meaning of this column but I did notice that each time a message is marked as read, the flags is incremented by one unit. Based on this experience, I came up with these two commands below which so far have worked for me.

update messages set flags = flags + 1 where flags&1 = 0;
update mailboxes set unread_count = 0;

After you run the above command, fully restart your iPhone (i.e. power off, and on). After reboot, run the mail. You should see all your messages as read, and the little red unread count bubble all gone. Enjoy!!