Convert and merge WhatsApp databases from iPhone format to Android

convert.sql

-- 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);

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

that would be fantastic!

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

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.

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

@residentsummer thanks, you made my day!

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

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

thanks :)

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

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?

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.