sqlite3 + skype chat history + group chat history

Raw post, but very useful info for extracting messages from Skype chat SQLite databases. sqlite3.exe "$(cygpath 'C:\Users\kmilnes\Downloads\main-ml.db')"

Open a SQLite DB:

sqlite3 /path/to/your/main.db

Cygwin: sqlite3.exe "$(cygpath 'c:\Downloads\main.db')"

SHOW TABLES:

SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;

DESCRIBE a table:

PRAGMA table_info(Conversations);

Schema (CREATE SYNTAX) for a table:

.schema TABLENAME

A list of unique usernames you've chatted with:

SELECT DISTINCT(dialog_partner) FROM Messages;

Show a list of messages for a given chat partner, ordered by timestamp:

SELECT author, from_dispname, datetime(timestamp, 'unixepoch') as date, body_xml FROM Messages where dialog_partner = 'your_dialog_partner' ORDER BY timestamp;

Show a unique list of participants from group chats you've had:

SELECT DISTINCT(participants) FROM Chats;

Get the chat id for a given group conversation:

SELECT id FROM Chats WHERE participants = 'participant1 participant2';

Get the conversation id for a given group conversation:

SELECT id FROM Conversations WHERE chat_dbid = 17627;

Get the messages for a given conversation id, ordered by timestamp:

SELECT author, from_dispname, datetime(timestamp, 'unixepoch') as date, body_xml FROM Messages where convo_id = 17631 ORDER BY timestamp;