01 August 2011

Nokia OVI Suite and marking SMS messages read

Wow! Nearly 8 months have gone by since the last post I made on this blog. Those of you who know me offline will also know that life has been a bit of a rollercoaster for about 6 months, which kind of explains the lack of activity both here and on my other blog.

So here goes...

How many of you have become irritated at Nokia OVI Suite's stubborn refusal to mark as read text messages that you have, well... read? Am I the only one?

You can click wherever you like on a message marked as unread and nothing will change that status. I'd read posts elsewhere that sugessted sorting the list of messages by date rather than grouped by contact but even that didn't work.

The only thing left to do was to go into the message database and change the status of the messages manually. For this, go and grab yourself a copy of SQLite Expert available from sqliteexpert.com. The free, "personal" version will work fine for this, although I'm sure the author would appreciate you purchasing a full version...

Note that the instructions given here will only work as of versions of Nokia OVI Suite that were released around June or July 2011. I'm using V3.1.1.80 here.

Before going any further, make sure OVI Suite is not running (click on the green OVI icon in the notification tray and select "Exit", simply closing the OVI Suite main window isn't enough). You don't want both it and SQLite Expert attempting to access the database at the same time and potentially lousing things up.

Once you've installed SQLite Expert, fire it up and then go to "File" / "Open database". Browse to the following location:

C:\Users\your_username\AppData\Local\Nokia\Nokia Ovi Suite\Messages\Database and load the database called msg_db.sqlite.

With that database open, you'll see a kind of tree view on the left and several tabs to its right. One of the tabs is called 'SQL'. Click on it.

Just below the tabs you'll now have a couple of lines in which to issue SQL queries to the database. Type in the following query and then click on the "Execute SQL" button. This will give you something like this (click on the image to see it in full resolution):

SELECT * FROM messages ORDER BY msg_time



Note that bits of the output that are personal and that can identify my correspondents have been blurred in order to protect their privacy.

The column that we're interested in here is the one headed "msg_status". I've figured out that the value in this column is a bitmask of sorts. Even though I've not been able to work out the meaning of every bit in the bitmask, I can say with relative certainty that the least but one significant bit (second from the right in binary notation) is the one that indicates whether the message is read or not. If the bit is set then the message is unread, if it is not set then the message is read.

So, the idea is to reset that second bit in all of the messages and thus mark all the messages in the database as read because OVI Suite seems incapable of doing so itself. This can be achieved by performing a logical AND operation with a number in which all the bits except the second bit are set. Without going into the details of two's complement signed integers, I can tell you that the number -3 is the one to use. This SQL statement will do the dirty deed for you, so enter it in the area for SQL input anc click again on the "Execute SQL" button:

UPDATE messages SET msg_status = msg_status & -3



That's it!

(Optionally) go into "File" / "Close database". Then exit SQLite Expert.

You can now fire up OVI Suite again and all of those unread messages will now appear as read.