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;
