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 commented Jul 19, 2014

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

@mcmaxl

This comment has been minimized.

Copy link

mcmaxl commented Jul 8, 2015

that would be fantastic!

@carcabot

This comment has been minimized.

Copy link

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 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 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 commented Jun 29, 2017

@residentsummer thanks, you made my day!

@dennislwy

This comment has been minimized.

Copy link

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 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 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 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 commented Apr 15, 2019

please can you tell me how to use the script

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.