BPI inside

Beginning with version 4.0 Skype changed its internal database format to SQLite (at least the Windows-client, could not yet check other platforms). Besides from better performance it also allows now any curious person to dig a bit into the internals of the local Skype account.

At first, you need a SQLite client to connect to the database. SQLite Database Browser is a good choice for this purpose. Then you need to open the Skype database in it. Shut down Skype (you won't be able to open the file otherwise) and load the file main.db from your Documents and Settings\<User>\Application Data\Skype\<Skype-account> directory. You can browse around in the database structure, have a look into the tables contents and even use a SQL editor to query the database. That's what we're going to need.

Now the fun begins :) In the following statements replace the values in angle brackets eg. <skypeid> with the appropiate correct values. Let's assume, you want to display all messages you exchanged with someone. Beware, that this only works gets the messages you exchanged in a private chat with someone and does not work on chats with more than two participants (you included).

SELECT author, timestamp, body_xml 
FROM messages 
WHERE dialog_partner = '<skypename>'

Or you want to list all chats, where there have ever been more than you and your chat-partner. So to list all group-chats:

SELECT name, participants, posters, activemembers, last_change 
FROM chats
WHERE type = 4

You could also be interested in all previous topics which were set on your group chat:

SELECT chatname, timestamp, body_xml 
FROM messages
WHERE chatmsg_type = 5
AND chatname = <chatname> -- get names from the previous query

By now you should have recognized, that the timestamps and change-values are not really readable dates in the database. Don't worry, it's not encrypted. Its just stored as plain Unix timestamp values. You can easily convert it to a readable time and date using for example the online UNIX Timestamp To Standard Time Calculator

When we're already with timestamps, you maybe also want to know when someone has also been online the last time when you have been online as well:

SELECT skypename, given_displayname, lastonline_timestamp
FROM contacts

So far we have fetched some interesting information from the database. But now we want to leverage our l33tness a bit and modify our mood message in our profile to include some rich formatting:

UPDATE accounts
SET profile_timestamp = current_timestamp, rich_mood_text = 'I feel <b>bold</b> now.'
WHERE skypename = '<skypename>'

Don't forget to save your database afterwards! This statement updates the profile-timestamp with the current time and adds some rich formatting to our mood message. In this case there is some bold text inside. You can look up some more possibilities (like smileys, font coloring, etc.) directly in the Skype API Documentation (from). Additionally I've found out, that using hyperlinks with the <a>-tag works as well as modifying font-size with the size-attribute of the <font>-tag.

Update 2010-02-14: Meanwhile you can edit your mood-message more easily using my online Skype mood message editor.

You can also delete the chat-history of only certain skype-contacts using the following statement

DELETE FROM messages
WHERE skypename = '<skypename>'

Again, don't forget to save your changes afterwards.

Update 2010-10-29: Skype has dropped the support for "/htmlhistory" since Skype 4.0. An alternate way to get all the messages for a chat is:

SELECT timestamp, author, chatmsg_type, body_xml  
FROM messages 
WHERE chatname = '<chatname>' 
ORDER BY id ASC;

You can find out the name of a chat when you write the command "/showname" in its chat-window.

I'll continue to play around with the database and if I find more interesting stuff, I'll keep this posting updated. Or I'll write a new one depending on what I dig up :)