Created
July 29, 2013 19:50
-
-
Save paracycle/6107205 to your computer and use it in GitHub Desktop.
Convert and merge WhatsApp databases from iPhone format to Android
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Clear tables | |
DROP TABLE chat_list; | |
DROP TABLE messages; | |
-- Create new tables | |
CREATE TABLE chat_list (_id INTEGER PRIMARY KEY AUTOINCREMENT, key_remote_jid TEXT UNIQUE, message_table_id INTEGER); | |
CREATE TABLE messages (_id INTEGER PRIMARY KEY AUTOINCREMENT, key_remote_jid TEXT NOT NULL, key_from_me INTEGER, key_id TEXT NOT NULL, status INTEGER, needs_push INTEGER, data TEXT, timestamp INTEGER, media_url TEXT, media_mime_type TEXT, media_wa_type TEXT, media_size INTEGER, media_name TEXT, latitude REAL, longitude REAL, thumb_image TEXT, remote_resource TEXT, received_timestamp INTEGER, send_timestamp INTEGER, receipt_server_timestamp INTEGER, receipt_device_timestamp INTEGER, raw_data BLOB, media_hash TEXT, recipient_count INTEGER, media_duration INTEGER, origin INTEGER); | |
-- Attach Android and iPhone databases | |
ATTACH 'msgstore.db' AS android; | |
ATTACH 'ChatStorage.sqlite' AS iphone; | |
-- Insert into new messages in timestamp order. | |
INSERT INTO messages (key_remote_jid, key_from_me, key_id, status, needs_push, data, timestamp, media_url, media_mime_type, media_wa_type, media_size, media_name, latitude, longitude, thumb_image, remote_resource, received_timestamp, send_timestamp, receipt_server_timestamp, receipt_device_timestamp, raw_data, media_hash, recipient_count, media_duration, origin) | |
SELECT key_remote_jid, key_from_me, key_id, status, needs_push, data, timestamp, media_url, media_mime_type, media_wa_type, media_size, media_name, latitude, longitude, thumb_image, remote_resource, received_timestamp, send_timestamp, receipt_server_timestamp, receipt_device_timestamp, raw_data, media_hash, recipient_count, media_duration, origin | |
FROM android.messages | |
UNION | |
SELECT | |
(CASE WHEN m.ZISFROMME=1 THEN m.ZTOJID ELSE m.ZFROMJID END) as key_remote_jid, | |
m.ZISFROMME as key_from_me, | |
m.ZSTANZAID as key_id, | |
CASE WHEN m.ZMESSAGETYPE=6 THEN 6 ELSE (CASE WHEN m.ZISFROMME=1 THEN 5 ELSE 0 END) END as status, | |
0 as needs_push, | |
m.ZTEXT as data, | |
CAST((978307200 + m.ZMESSAGEDATE) * 1000 AS INTEGER) as timestamp, | |
mi.ZMEDIAURL as media_url, | |
NULL as media_mime_type, | |
CASE WHEN m.ZMESSAGETYPE=6 THEN 0 ELSE m.ZMESSAGETYPE END as media_wa_type, | |
CASE WHEN m.ZMESSAGETYPE=6 THEN m.ZGROUPEVENTTYPE ELSE mi.ZFILESIZE END as media_size, | |
mi.ZMEDIALOCALPATH as media_name, | |
mi.ZLATITUDE as latitude, | |
mi.ZLONGITUDE as longitude, | |
mi.ZTHUMBNAILLOCALPATH as thumb_image, | |
gm.ZMEMBERJID as remote_resource, | |
CAST((978307200 + m.ZMESSAGEDATE) * 1000 AS INTEGER) as received_timestamp, | |
CASE WHEN m.ZISFROMME=1 THEN CAST((978307200 + m.ZMESSAGEDATE) * 1000 AS INTEGER) ELSE -1 END as send_timestamp, | |
CASE WHEN m.ZISFROMME=1 THEN CAST((978307200 + m.ZMESSAGEDATE) * 1000 AS INTEGER) ELSE -1 END as receipt_server_timestamp, | |
CASE WHEN m.ZISFROMME=1 THEN CAST((978307200 + m.ZMESSAGEDATE) * 1000 AS INTEGER) ELSE -1 END as receipt_device_timestamp, | |
"" as raw_data, | |
mi.ZVCARDNAME as media_hash, | |
0 as recipient_count, | |
mi.ZMOVIEDURATION as media_duration, | |
CASE WHEN m.ZISFROMME=1 THEN 0 ELSE 1 END as origin | |
FROM iphone.ZWAMESSAGE AS m | |
LEFT JOIN iphone.ZWAMEDIAITEM AS mi ON m.ZMEDIAITEM = mi.Z_PK | |
LEFT JOIN iphone.ZWAGROUPMEMBER AS gm ON m.ZGROUPMEMBER = gm.Z_PK | |
ORDER BY timestamp ASC; | |
--Insert the chat_list entries with the highest indices for messages. | |
INSERT INTO chat_list (key_remote_jid, message_table_id) | |
SELECT cur.key_remote_jid, cur._id | |
FROM messages AS cur | |
WHERE NOT EXISTS ( | |
SELECT * | |
FROM messages AS high | |
WHERE high.key_remote_jid = cur.key_remote_jid | |
AND high._id > cur._id | |
) | |
-- SELECT key_remote_jid, key_from_me, key_id, status, needs_push, data, timestamp, media_url, media_mime_type, media_wa_type, media_size, media_name, latitude, longitude, thumb_image, remote_resource, received_timestamp, send_timestamp, receipt_server_timestamp, receipt_device_timestamp, raw_data, media_hash, recipient_count, media_duration, origin FROM messages ORDER BY timestamp ASC | |
-- SELECT m.ZSTANZAID as key_id, m.ZTEXT as data FROM ZWAMESSAGE AS m | |
--INSERT INTO messages_new (key_remote_jid, key_from_me, key_id, status, needs_push, data, timestamp, media_url, media_mime_type, media_wa_type, media_size, media_name, latitude, longitude, thumb_image, remote_resource, received_timestamp, send_timestamp, receipt_server_timestamp, receipt_device_timestamp, raw_data, media_hash, recipient_count, media_duration, origin) VALUES SELECT key_remote_jid, key_from_me, key_id, status, needs_push, data, timestamp, media_url, media_mime_type, media_wa_type, media_size, media_name, latitude, longitude, thumb_image, remote_resource, received_timestamp, send_timestamp, receipt_server_timestamp, receipt_device_timestamp, raw_data, media_hash, recipient_count, media_duration, origin FROM messages ORDER BY timestamp ASC | |
--UPDATE merged.messages SET data = (SELECT m.ZTEXT FROM ZWAMESSAGE AS m WHERE m.ZSTANZAID = key_id) WHERE key_id IN (SELECT DISTINCT ZSTANZAID FROM ZWAMESSAGE); |
please can you tell me how to use the script
In general the approach still works (thanks!). The schema of the destination messages table changed a bit. Some triggers need to be recreated too after the table drop. If you want to transfer media, you'll need to populate message_media too and have file_path relative to /sdcard/WhatsApp/
. The chat view can crash on status 6 type messages (control messages) when it tries to render them. Just delete them or anything with data null and no media path. For faster scroll one can populate the raw_data field with thumbnail bytes, without the file_path from message_media is read.
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
HI, do you have an up to dat e script for merginf 2 whatsapp sql databases (from 2 dirrefrent accounts) and how to re-upload it to the iphone?