Searching Skype's SQLite Database
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
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 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
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 #)
Messagesrefers to a line of text such as "What's up?"
Conversationsrefers to a collection of Messages between 2 or more parties.
- I think
Chatsrefers to the logical time gaps separated with labels like 'yesterday', '7 days ago', 'March 24th', etc
Hopefully that'll help you in your quest as well. :-)
By AJ ONeal
Was this useful to you? Share it!give me a tip or hire me.