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