I was having a conversation with someone a few days ago on Skype, but I couldn't remember his name or Skype handle or what it was about - just that it was important (but not important enough to remember, apparently).

I found that the "History" function in Skype pretty much uses bogosort to order the messages kinda by recency, but not with any real rhyme or reason to it, so I set off to find the conversation the good ol' fashion way.... but grep wasn't working out for me so after some googling I got on a good lead for how to find the place Skype stores its messages and whatnot.

And now here we are!

Find your Skype DB

First you've got to find the correct skype db for your user:

ls ~/Library/Application\ Support/Skype/

sqlite3 ~/Library/Application\ Support/Skype/<<YOUR_USER_NAME>>/main.db

Learn them Tables Good!

You'll want to take a look at the available tables, and their descriptions:

.tables          " see the short table list
.schema Contacts " all about the Contacts table
.schema Messages " all about the Messages table

You'll probably need to use the good ol' ctrl+f to search in the output for things like time, author, and username.

Dive into the SQLs

Then you gotsta dive into the SQLs...

" List the 25 most recently contacted contacts
SELECT skypename, lastused_timestamp FROM Contacts ORDER BY lastused_timestamp DESC LIMIT 25;

" List the 100 most recent messages
SELECT id, convo_id, timestamp, type, author, body_xml FROM Messages ORDER BY timestamp DESC LIMIT 100;

" List the 100 most recent conversations (and all participants)
SELECT last_activity_timestamp, identity, type, given_displayname, displayname FROM Conversations ORDER BY last_activity_timestamp DESC LIMIT 100;

" Search for a message with the text 'home'
SELECT author, body_xml FROM Messages WHERE body_xml LIKE '%HOME%' ORDER BY timestamp ASC;

" Search for a contact named 'john'
SELECT (displayname || ' : ' || skypename || ' : ' || fullname) as names FROM Contacts WHERE names LIKE '%JOHN%' ORDER BY lastused_timestamp ASC;

(note comments are with a ", not a #)

Note that

• Messages refers to a line of text such as "What's up?"
• Conversations refers to a collection of Messages between 2 or more parties.
• I think Chats refers to the logical time gaps separated with labels like 'yesterday', '7 days ago', 'March 24th', etc

Be Happy

Hopefully that'll help you in your quest as well. :-)

By AJ ONeal


Did I make your day?
Buy me a coffee  

(you can learn about the bigger picture I'm working towards on my patreon page )

---

---