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
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
WHERE type = 4
You could also be interested in all previous topics which were set on your group chat:
SELECT chatname, timestamp, body_xml
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
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:
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.
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.
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
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 :)