Random SQLite Commands.txt

#Get all messages for a person

SELECT * 
		FROM chat
	INNER JOIN handle 
		ON chat.chat_identifier = handle.id
	INNER JOIN chat_handle_join 
		ON  handle.ROWID= chat_handle_join.handle_id 
	INNER JOIN message
		ON message.handle_id = chat_handle_join.handle_id
			WHERE (chat_identifier LIKE '%4930' 
				OR chat_identifier LIKE '%915%' 
				OR chat_identifier LIKE '%dsouza%') 
				AND length(message.text) > 0



#Get counts for a person
SELECT COUNT(ROWID)
	FROM message AS messageT
	INNER JOIN chat_message_join AS chatMessageT
			ON chatMessageT.chat_id IN (1650)
					AND messageT.ROWID=chatMessageT.message_id
					AND (CASE
								WHEN LENGTH(date) >= 18
									THEN date / 1000000000
								ELSE date
							END)
							BETWEEN 0 AND 2147483647
	ORDER BY messageT.date;



#GET ALL MESSAGES FOR A PERSON - NO JOIN TABLES, REQUIRES PHONE NUMBER

SELECT * FROM chat WHERE guid LIKE '%7669%'

SELECT * FROM chat_handle_join WHERE chat_id=173 OR chat_id=198

SELECT * FROM message WHERE handle_id=167 OR handle_id=193 ORDER BY date

SELECT text, t3.GUID FROM message t1 INNER JOIN chat_handle_join t2 ON t1.handle_id=t2.handle_id INNER JOIN chat t3 ON t2.chat_id=t3.ROWID WHERE t3.GUID LIKE '%7669%' ORDER BY date



#GET ALL ATTACHMENT INFO FOR A CONVERSATION

SELECT messageT.*, attachmentT.ROWID, attachmentT.guid, attachmentT.filename, attachmentT.mime_type, attachmentT.start_date, attachmentT.total_bytes FROM message messageT INNER JOIN attachment attachmentT INNER JOIN message_attachment_join meAtJoinT ON attachmentT.ROWID= meAtJoinT.attachment_id WHERE meAtJoinT.message_id=messageT.ROWID


#GET ALL ATTACHMENT INFO GIVEN CHAT ID

SELECT messageT.ROWID, messageT.guid, attachmentT.ROWID, attachmentT.guid, attachmentT.filename, attachmentT.mime_type, attachmentT.start_date, attachmentT.total_bytes, attachmentT.transfer_name FROM message messageT INNER JOIN chat_message_join chatMessageT ON messageT.ROWID=chatMessageT.message_id INNER JOIN attachment attachmentT INNER JOIN message_attachment_join meAtJoinT ON attachmentT.ROWID=meAtJoinT.attachment_id WHERE meAtJoinT.message_id=messageT.ROWID AND (chatMessageT.chat_id=173 OR chatMessageT.chat_id=198)


#GET ALL MESSAGES GIVEN A CHAT ID

SELECT  ROWID, guid, text, service, account_guid, date, date_read, is_from_me, cache_has_attachments FROM message messageT INNER JOIN chat_message_join chatMessageT ON (chatMessageT.chat_id=173 OR chatMessageT.chat_id=198) AND messageT.ROWID=chatMessageT.message_id ORDER BY messageT.date

 
#NUM MESSAGES BETWEEN TIMES GIVEN CHAT ID

SELECT count(*) FROM message messageT INNER JOIN chat_message_join chatMessageT ON (chatMessageT.chat_id=173 OR chatMessageT.chat_id=198) AND messageT.ROWID=chatMessageT.message_id AND (messageT.date > 469268088 AND messageT.date < 469568288) ORDER BY messageT.date


#ALL MESSAGES NOT SENT BY A PERSON

SELECT messageT.ROWID, messageT.date, messageT.text, messageT.is_from_me, messageT.cache_has_attachments FROM message messageT INNER JOIN chat_message_join chatMessageT ON (chatMessageT.chat_id!=173 AND chatMessageT.chat_id!=198) AND messageT.ROWID=chatMessageT.message_id ORDER BY messageT.date


#GETS iPHONE CHAT.DB BACKUP
cp ~/Library/Application\ Support/MobileSync/Backup/490082a130793ab742c51812d176d262435cd6a5/3d0d7e5fb2ce288813306e4d4636395e047a3d28 ~/FLV\ MP4/iMessage/iphone_chat.db