Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Convert and merge WhatsApp databases from iPhone format to Android
-- 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);
@solangek

This comment has been minimized.

Copy link

@solangek solangek commented Jul 19, 2014

great script, any chance you provide the android to iphone version?

@mcmaxl

This comment has been minimized.

Copy link

@mcmaxl mcmaxl commented Jul 8, 2015

that would be fantastic!

@carcabot

This comment has been minimized.

Copy link

@carcabot carcabot commented Dec 10, 2015

Can run this directly on Android device or this code should be included in an application ?
Btw, it's still working ?

@tweakerheart

This comment has been minimized.

Copy link

@tweakerheart tweakerheart commented Jan 19, 2016

Thanks for sharing. However, I would very much appreciate a version of this script to convert a android msgstore.db database into an iPhone ChatStorage.sqlite database.

@residentsummer

This comment has been minimized.

Copy link

@residentsummer residentsummer commented Apr 26, 2017

I hope my Android to iOS converter would be helpful to someone - residentsummer/watoi </shameless plug>

@dberzano

This comment has been minimized.

Copy link

@dberzano dberzano commented Jun 29, 2017

@residentsummer thanks, you made my day!

@dennislwy

This comment has been minimized.

Copy link

@dennislwy dennislwy commented Oct 14, 2017

@dberzano the script already 4 years old, does it still work for you?

@daniel-bluesea

This comment has been minimized.

Copy link

@daniel-bluesea daniel-bluesea commented Feb 23, 2018

where do i run it?
where physically should "ChatStorage.sqlite" be in order to correctly be "attached"?
i need to create some database?

thanks :)

@sameerhussain

This comment has been minimized.

Copy link

@sameerhussain sameerhussain commented Jun 11, 2018

@residentsummer , are the database tables still same in June, 2018.? please confirm....

@Matguil

This comment has been minimized.

Copy link

@Matguil Matguil commented Dec 13, 2018

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?

@beirutcell

This comment has been minimized.

Copy link

@beirutcell beirutcell commented Apr 15, 2019

please can you tell me how to use the script

@wbob

This comment has been minimized.

Copy link

@wbob wbob commented Jul 29, 2021

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