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][1] 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
Application DataSkype** 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. * * 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 = ‘ ‘ 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 = — 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][2] 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 bold now.’ WHERE 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][3] ([from][4]). Additionally I’ve found out, that using hyperlinks with the -tag works as well as modifying font-size with the *size*-attribute of the -tag. **Update 2010-02-14:** Meanwhile you can edit your mood-message more easily using my [online Skype mood message editor][5]. You can also delete the chat-history of only certain skype-contacts using the following statement DELETE FROM messages WHERE 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 = ‘ ‘ ORDER BY id ASC; You can find out the name of a chat when you write the command “[/showname][6]” 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 🙂 [1]: http://sourceforge.net/projects/sqlitebrowser/ “SQLite Database Browser” [2]: http://www.csgnetwork.com/unixds2timecalc.html “Timestamp To Standard Time Calculator” [3]: https://developer.skype.com/Docs/ApiDoc/SET_PROFILE_RICH_MOOD_TEXT “Skype ApiDoc SET_PROFILE_RICH_MOOD_TEXT” [4]: http://forum.skype.com/index.php?s=&showtopic=260131&view=findpost&p=1160831 “Skype4Py” [5]: http://kosi2801.freepgs.com/skype/change-your-skype-mood-message-using-rich-formatting.html “Online Skype Mood Message Editor with Rich Formatting” [6]: http://kosi2801.freepgs.com/2010/10/29/more_mysterious_skype_chat_commands.html “More mysterious Skype chat commands”