convert.sql

DROP TABLE IF EXISTS `messages`;
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
);

CREATE INDEX media_hash_index on messages (media_hash);
CREATE INDEX media_type_index on messages (media_wa_type);
CREATE UNIQUE INDEX messages_key_index on messages (key_remote_jid, key_from_me, key_id);

DROP TABLE IF EXISTS `chat_list`;
CREATE TABLE `chat_list` (
	`_id`	INTEGER PRIMARY KEY AUTOINCREMENT,
	`key_remote_jid`	TEXT UNIQUE,
	`message_table_id`	INTEGER,
	`subject`	TEXT,
	`creation`	INTEGER
);

DROP TABLE IF EXISTS `media_refs`;
CREATE TABLE `media_refs` (
	`_id`	INTEGER PRIMARY KEY AUTOINCREMENT,
	`path`	TEXT UNIQUE,
	`ref_count`	INTEGER
);

-- Attach Android and iPhone databases, replace string with current PATH
ATTACH 'C:/Users/zeus/Desktop/Whatsapp/Orig - copia/msgstore-old.db' AS android;
ATTACH 'C:/Users/zeus/Desktop/Whatsapp/Orig - copia/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,
      CASE WHEN mi.ZLATITUDE IS NULL THEN 0.0 ELSE mi.ZLATITUDE END as latitude,
      CASE WHEN mi.ZLONGITUDE IS NULL THEN 0.0 ELSE mi.ZLONGITUDE END 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,
      CASE WHEN mi.ZMOVIEDURATION IS NULL THEN 0 ELSE mi.ZMOVIEDURATION END 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, subject, creation)
SELECT
  m.key_remote_jid AS _id,
  max(m._id) AS message_table_id,
  CASE WHEN cs.ZGROUPINFO IS NOT NULL THEN cs.ZPARTNERNAME ELSE "" END AS subject,
  CAST((978307200 + gi.ZCREATIONDATE) * 1000 AS INTEGER) AS creation
  FROM messages AS m
  LEFT JOIN iphone.ZWACHATSESSION AS cs
  ON cs.ZCONTACTJID = m.key_remote_jid
  LEFT JOIN iphone.ZWAGROUPINFO AS gi
  ON gi.ZCHATSESSION = cs.Z_PK
  WHERE m.key_remote_jid != -1
  GROUP BY m.key_remote_jid;
  
INSERT INTO media_refs (_id, path, ref_count)
  SELECT _id, path, ref_count
  FROM android.media_refs;



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