Generate an Android backup from the Desktop app

[kias-git]

I think this project is best placed to allow people to recover messages to their Android phone from the Desktop app.

If you were able to translate the DB for the Desktop app into the Android backup format and encrypt it, I think it would work.

Is there work started on this?

[bepaald]

Hi!

I think this project is best placed to allow people to recover messages to their Android phone from the Desktop app.

Yes, I agree. I remember I discussed this on Signal's community forum a long time ago and even did some investigation. Unfortunately the database schema's of the two applications are vastly different.

The main problems I see are

1. Writing a translation from desktop to android database is a huge undertaking
2. Keeping it up-to-date is a big extra burden. Keeping up with the changes in the android db is already almost too much. If the desktop db changes as often I'm gonna be in real trouble.

Having said that, I still think this would be nice to have, and I'm more familiar with the (android) database now than I was back then. So I will restart my investigation and try to get something together. It will absolutely not create a new backup file from scratch, best we can hope for is to import messages from the desktop app into an existing android backup.

Is there work started on this?

Well, apart from the different schema's the two databases also utilize different encryption, and this program already has the capability to decrypt and read the desktop database (https://github.com/bepaald/signalbackup-tools/tree/master/sqlcipherdecryptor). So that's the first step I guess. But the next step is the more difficult one.

I will work on this, but time a limiting factor for me, so for such a big feature, we should expect this to take months.

[kias-git]

I think I might be able to help but I have zero familiarity. Agreed it will take a while, and agreed that the maintainability will be challenging since schemas may change. Note, I think it would be hard even for SIGNAL to change schemas based on the code I saw 😂

[felix-engelmann]

I'm very much interested in having such a tool, as it allows transfer of messages from an iOS device (given that it was paired with a desktop app from the beginning). Most likely the best we can achieve with a reasonable effort is to inject messages, extracted from the desktop DB into an empty android backup. What I would appreciate most is having access to all my groups.

To get this started, a good point is to document the current schema of the android backup. There are multiple exports of the desktop DB. Is there a recent empty android backup available online to inspect? I did not find any test blob in this repo.

[bepaald]

@felix-engelmann Sorry for the late reply. Creating an 'empty' backup is actually not that easy. If you just export a backup from a fresh install it will still contain all your phone's contacts (names, phone numbers and all). So it would still require manually going through the database and deleting sensitive data. But then, when no actual data is in the database, it is hard (or impossible) to see how the various fields in tables actually reference each other (for example, that the 'sms.address' field references 'recipient._id').

Easiest thing to do is just extract a backup file and use sqlite to print the schema of the database. I realize you can't do that, being an IOS user, so here is one of mine:

CREATE TABLE part (_id INTEGER PRIMARY KEY, mid INTEGER, seq INTEGER DEFAULT 0, ct TEXT, name TEXT, chset INTEGER, cd TEXT, fn TEXT, cid TEXT, cl TEXT, ctt_s INTEGER, ctt_t TEXT, encrypted INTEGER, pending_push INTEGER, _data TEXT, data_size INTEGER, file_name TEXT, thumbnail TEXT, aspect_ratio REAL, unique_id INTEGER NOT NULL, digest BLOB, fast_preflight_id TEXT, voice_note INTEGER DEFAULT 0, data_random BLOB, thumbnail_random BLOB, width INTEGER DEFAULT 0, height INTEGER DEFAULT 0, quote INTEGER DEFAULT 0, caption TEXT DEFAULT NULL, sticker_pack_id TEXT, sticker_pack_key TEXT, sticker_id INTEGER DEFAULT -1, data_hash TEXT DEFAULT NULL, blur_hash TEXT DEFAULT NULL, transform_properties TEXT DEFAULT NULL, transfer_file TEXT DEFAULT NULL, display_order INTEGER DEFAULT 0, upload_timestamp DEFAULT 0, cdn_number INTEGER DEFAULT 0, borderless INTEGER DEFAULT 0, sticker_emoji TEXT DEFAULT NULL, video_gif INTEGER DEFAULT 0);
CREATE TABLE drafts (_id INTEGER PRIMARY KEY, thread_id INTEGER, type TEXT, value TEXT);
CREATE TABLE push (_id INTEGER PRIMARY KEY, type INTEGER, source TEXT, device_id INTEGER, body TEXT, content TEXT, timestamp INTEGER, server_timestamp INTEGER DEFAULT 0, server_guid TEXT DEFAULT NULL, source_uuid TEXT DEFAULT NULL, server_delivered_timestamp INTEGER DEFAULT 0);
CREATE TABLE groups (_id INTEGER PRIMARY KEY, group_id TEXT, title TEXT, members TEXT, avatar BLOB, avatar_id INTEGER, avatar_key BLOB, avatar_content_type TEXT, avatar_relay TEXT, timestamp INTEGER, active INTEGER DEFAULT 1, avatar_digest BLOB, mms INTEGER DEFAULT 0, recipient_id INTEGER DEFAULT 0, master_key, revision, decrypted_group, expected_v2_id TEXT DEFAULT NULL, former_v1_members TEXT DEFAULT NULL, distribution_id TEXT DEFAULT NULL, display_as_story INTEGER DEFAULT 0);
CREATE TABLE group_receipts (_id INTEGER PRIMARY KEY, mms_id INTEGER, address TEXT, status INTEGER, timestamp INTEGER, unidentified INTEGER DEFAULT 0);
CREATE INDEX part_mms_id_index ON part (mid);
CREATE INDEX pending_push_index ON part (pending_push);
CREATE INDEX draft_thread_index ON drafts (thread_id);
CREATE UNIQUE INDEX group_id_index ON groups (group_id);
CREATE INDEX group_receipt_mms_id_index ON group_receipts (mms_id);
CREATE VIRTUAL TABLE sms_fts USING fts5(body, thread_id UNINDEXED, content=sms, content_rowid=_id)
/* sms_fts(body,thread_id) */;
CREATE TABLE IF NOT EXISTS 'sms_fts_data'(id INTEGER PRIMARY KEY, block BLOB);
CREATE TABLE IF NOT EXISTS 'sms_fts_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID;
CREATE TABLE IF NOT EXISTS 'sms_fts_docsize'(id INTEGER PRIMARY KEY, sz BLOB);
CREATE TABLE IF NOT EXISTS 'sms_fts_config'(k PRIMARY KEY, v) WITHOUT ROWID;
CREATE VIRTUAL TABLE mms_fts USING fts5(body, thread_id UNINDEXED, content=mms, content_rowid=_id)
/* mms_fts(body,thread_id) */;
CREATE TABLE IF NOT EXISTS 'mms_fts_data'(id INTEGER PRIMARY KEY, block BLOB);
CREATE TABLE IF NOT EXISTS 'mms_fts_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID;
CREATE TABLE IF NOT EXISTS 'mms_fts_docsize'(id INTEGER PRIMARY KEY, sz BLOB);
CREATE TABLE IF NOT EXISTS 'mms_fts_config'(k PRIMARY KEY, v) WITHOUT ROWID;
CREATE TABLE sticker (_id INTEGER PRIMARY KEY AUTOINCREMENT, pack_id TEXT NOT NULL, pack_key TEXT NOT NULL, pack_title TEXT NOT NULL, pack_author TEXT NOT NULL, sticker_id INTEGER, cover INTEGER, emoji TEXT NOT NULL, last_used INTEGER, installed INTEGER,file_path TEXT NOT NULL, file_length INTEGER, file_random BLOB, pack_order INTEGER DEFAULT 0, content_type TEXT DEFAULT NULL, UNIQUE(pack_id, sticker_id, cover) ON CONFLICT IGNORE);
CREATE TABLE sqlite_sequence(name,seq);
CREATE INDEX sticker_pack_id_index ON sticker (pack_id);
CREATE INDEX sticker_sticker_id_index ON sticker (sticker_id);
CREATE INDEX part_sticker_pack_id_index ON part (sticker_pack_id);
CREATE UNIQUE INDEX group_recipient_id_index ON groups (recipient_id);
CREATE TABLE recipient (_id INTEGER PRIMARY KEY AUTOINCREMENT, uuid TEXT UNIQUE DEFAULT NULL, phone TEXT UNIQUE DEFAULT NULL, email TEXT UNIQUE DEFAULT NULL, group_id TEXT UNIQUE DEFAULT NULL, blocked INTEGER DEFAULT 0, message_ringtone TEXT DEFAULT NULL, message_vibrate INTEGER DEFAULT 0, call_ringtone TEXT DEFAULT NULL, call_vibrate INTEGER DEFAULT 0, notification_channel TEXT DEFAULT NULL, mute_until INTEGER DEFAULT 0, color TEXT DEFAULT NULL, seen_invite_reminder INTEGER DEFAULT 0, default_subscription_id INTEGER DEFAULT -1, message_expiration_time INTEGER DEFAULT 0, registered INTEGER DEFAULT 0, system_display_name TEXT DEFAULT NULL, system_photo_uri TEXT DEFAULT NULL, system_phone_label TEXT DEFAULT NULL, system_contact_uri TEXT DEFAULT NULL, profile_key TEXT DEFAULT NULL, signal_profile_name TEXT DEFAULT NULL, signal_profile_avatar TEXT DEFAULT NULL, profile_sharing INTEGER DEFAULT 0, unidentified_access_mode INTEGER DEFAULT 0, force_sms_selection INTEGER DEFAULT 0, system_phone_type INTEGER DEFAULT -1, uuid_supported INTEGER DEFAULT 0, username TEXT DEFAULT NULL, system_info_pending INTEGER DEFAULT 0, storage_service_key TEXT DEFAULT NULL, dirty INTEGER DEFAULT 0, profile_family_name TEXT DEFAULT NULL, profile_joined_name TEXT DEFAULT NULL, profile_key_credential TEXT DEFAULT NULL, group_type INTEGER DEFAULT 0, gv2_capability INTEGER DEFAULT 0, last_profile_fetch INTEGER DEFAULT 0, mention_setting INTEGER DEFAULT 0, storage_proto TEXT DEFAULT NULL, capabilities INTEGER DEFAULT 0, last_gv1_migrate_reminder INTEGER DEFAULT 0, last_session_reset BLOB DEFAULT NULL, wallpaper BLOB DEFAULT NULL, wallpaper_file TEXT DEFAULT NULL, about TEXT DEFAULT NULL, about_emoji TEXT DEFAULT NULL, system_family_name TEXT DEFAULT NULL, system_given_name TEXT DEFAULT NULL, extras BLOB DEFAULT NULL, groups_in_common INTEGER DEFAULT 0, chat_colors BLOB DEFAULT NULL, custom_chat_colors_id INTEGER DEFAULT 0, badges BLOB DEFAULT NULL, pni TEXT DEFAULT NULL, distribution_list_id INTEGER DEFAULT NULL);
CREATE INDEX part_data_hash_index ON part (data_hash);
CREATE UNIQUE INDEX recipient_username_index ON recipient (username);
CREATE TABLE storage_key (_id INTEGER PRIMARY KEY AUTOINCREMENT, type INTEGER, key TEXT UNIQUE);
CREATE INDEX storage_key_type_index ON storage_key (type);
CREATE UNIQUE INDEX recipient_storage_service_key ON recipient (storage_service_key);
CREATE INDEX recipient_dirty_index ON recipient (dirty);
CREATE INDEX part_data_index ON part (_data);
CREATE INDEX recipient_group_type_index ON recipient (group_type);
CREATE TABLE remapped_recipients (_id INTEGER PRIMARY KEY AUTOINCREMENT, old_id INTEGER UNIQUE, new_id INTEGER);
CREATE TABLE remapped_threads (_id INTEGER PRIMARY KEY AUTOINCREMENT, old_id INTEGER UNIQUE, new_id INTEGER);
CREATE TABLE mention (_id INTEGER PRIMARY KEY AUTOINCREMENT, thread_id INTEGER, message_id INTEGER, recipient_id INTEGER, range_start INTEGER, range_length INTEGER);
CREATE INDEX mention_message_id_index ON mention (message_id);
CREATE INDEX mention_recipient_id_thread_id_index ON mention (recipient_id, thread_id);
CREATE UNIQUE INDEX expected_v2_id_index ON groups (expected_v2_id);
CREATE TABLE payments(_id INTEGER PRIMARY KEY, uuid TEXT DEFAULT NULL, recipient INTEGER DEFAULT 0, recipient_address TEXT DEFAULT NULL, timestamp INTEGER, note TEXT DEFAULT NULL, direction INTEGER, state INTEGER, failure_reason INTEGER, amount BLOB NOT NULL, fee BLOB NOT NULL, transaction_record BLOB DEFAULT NULL, receipt BLOB DEFAULT NULL, payment_metadata BLOB DEFAULT NULL, receipt_public_key TEXT DEFAULT NULL, block_index INTEGER DEFAULT 0, block_timestamp INTEGER DEFAULT 0, seen INTEGER, UNIQUE(uuid) ON CONFLICT ABORT);
CREATE INDEX timestamp_direction_index ON payments (timestamp, direction);
CREATE INDEX timestamp_index ON payments (timestamp);
CREATE UNIQUE INDEX receipt_public_key_index ON payments (receipt_public_key);
CREATE TABLE chat_colors (_id INTEGER PRIMARY KEY AUTOINCREMENT,chat_colors BLOB);
CREATE VIRTUAL TABLE emoji_search USING fts5(label, emoji UNINDEXED)
/* emoji_search(label,emoji) */;
CREATE TABLE IF NOT EXISTS 'emoji_search_data'(id INTEGER PRIMARY KEY, block BLOB);
CREATE TABLE IF NOT EXISTS 'emoji_search_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID;
CREATE TABLE IF NOT EXISTS 'emoji_search_content'(id INTEGER PRIMARY KEY, c0, c1);
CREATE TABLE IF NOT EXISTS 'emoji_search_docsize'(id INTEGER PRIMARY KEY, sz BLOB);
CREATE TABLE IF NOT EXISTS 'emoji_search_config'(k PRIMARY KEY, v) WITHOUT ROWID;
CREATE TABLE sender_key_shared (_id INTEGER PRIMARY KEY AUTOINCREMENT, distribution_id TEXT NOT NULL, address TEXT NOT NULL, device INTEGER NOT NULL, timestamp INTEGER DEFAULT 0, UNIQUE(distribution_id, address, device) ON CONFLICT REPLACE);
CREATE TABLE pending_retry_receipts (_id INTEGER PRIMARY KEY AUTOINCREMENT, author TEXT NOT NULL, device INTEGER NOT NULL, sent_timestamp INTEGER NOT NULL, received_timestamp TEXT NOT NULL, thread_id INTEGER NOT NULL, UNIQUE(author, sent_timestamp) ON CONFLICT REPLACE);
CREATE UNIQUE INDEX group_distribution_id_index ON groups (distribution_id);
CREATE TABLE msl_payload (_id INTEGER PRIMARY KEY, date_sent INTEGER NOT NULL, content BLOB NOT NULL, content_hint INTEGER NOT NULL);
CREATE INDEX msl_payload_date_sent_index ON msl_payload (date_sent);
CREATE TABLE msl_recipient (_id INTEGER PRIMARY KEY, payload_id INTEGER NOT NULL REFERENCES msl_payload (_id) ON DELETE CASCADE, recipient_id INTEGER NOT NULL, device INTEGER NOT NULL);
CREATE INDEX msl_recipient_recipient_index ON msl_recipient (recipient_id, device, payload_id);
CREATE INDEX msl_recipient_payload_index ON msl_recipient (payload_id);
CREATE TABLE msl_message (_id INTEGER PRIMARY KEY, payload_id INTEGER NOT NULL REFERENCES msl_payload (_id) ON DELETE CASCADE, message_id INTEGER NOT NULL, is_mms INTEGER NOT NULL);
CREATE INDEX msl_message_message_index ON msl_message (message_id, is_mms, payload_id);
CREATE TRIGGER msl_attachment_delete AFTER DELETE ON part BEGIN DELETE FROM msl_payload WHERE _id IN (SELECT payload_id FROM msl_message WHERE message_id = old.mid AND is_mms = 1); END;
CREATE TABLE IF NOT EXISTS "thread" (_id INTEGER PRIMARY KEY AUTOINCREMENT, date INTEGER DEFAULT 0, thread_recipient_id INTEGER, message_count INTEGER DEFAULT 0, snippet TEXT, snippet_charset INTEGER DEFAULT 0, snippet_type INTEGER DEFAULT 0, snippet_uri TEXT DEFAULT NULL, snippet_content_type INTEGER DEFAULT NULL, snippet_extras TEXT DEFAULT NULL, read INTEGER DEFAULT 1, type INTEGER DEFAULT 0, error INTEGER DEFAULT 0, archived INTEGER DEFAULT 0, status INTEGER DEFAULT 0, expires_in INTEGER DEFAULT 0, last_seen INTEGER DEFAULT 0, has_sent INTEGER DEFAULT 0, delivery_receipt_count INTEGER DEFAULT 0, read_receipt_count INTEGER DEFAULT 0, unread_count INTEGER DEFAULT 0, last_scrolled INTEGER DEFAULT 0, pinned INTEGER DEFAULT 0);
CREATE INDEX thread_recipient_id_index ON thread (thread_recipient_id);
CREATE INDEX archived_count_index ON thread (archived, message_count);
CREATE INDEX thread_pinned_index ON thread (pinned);
CREATE TABLE IF NOT EXISTS "mms" (_id INTEGER PRIMARY KEY AUTOINCREMENT, thread_id INTEGER, date INTEGER, date_received INTEGER, date_server INTEGER DEFAULT -1, msg_box INTEGER, read INTEGER DEFAULT 0, body TEXT, part_count INTEGER, ct_l TEXT, address INTEGER, address_device_id INTEGER, exp INTEGER, m_type INTEGER, m_size INTEGER, st INTEGER, tr_id TEXT, delivery_receipt_count INTEGER DEFAULT 0, mismatched_identities TEXT DEFAULT NULL, network_failures TEXT DEFAULT NULL, subscription_id INTEGER DEFAULT -1, expires_in INTEGER DEFAULT 0, expire_started INTEGER DEFAULT 0, notified INTEGER DEFAULT 0, read_receipt_count INTEGER DEFAULT 0, quote_id INTEGER DEFAULT 0, quote_author TEXT, quote_body TEXT, quote_attachment INTEGER DEFAULT -1, quote_missing INTEGER DEFAULT 0, quote_mentions BLOB DEFAULT NULL, shared_contacts TEXT, unidentified INTEGER DEFAULT 0, previews TEXT, reveal_duration INTEGER DEFAULT 0, reactions BLOB DEFAULT NULL, reactions_unread INTEGER DEFAULT 0, reactions_last_seen INTEGER DEFAULT -1, remote_deleted INTEGER DEFAULT 0, mentions_self INTEGER DEFAULT 0, notified_timestamp INTEGER DEFAULT 0, viewed_receipt_count INTEGER DEFAULT 0, server_guid TEXT DEFAULT NULL, receipt_timestamp INTEGER DEFAULT -1, ranges BLOB DEFAULT NULL, is_story INTEGER DEFAULT 0, parent_story_id INTEGER DEFAULT 0);
CREATE INDEX mms_read_and_notified_and_thread_id_index ON mms(read, notified, thread_id);
CREATE INDEX mms_message_box_index ON mms (msg_box);
CREATE INDEX mms_date_sent_index ON mms (date, address, thread_id);
CREATE INDEX mms_date_server_index ON mms (date_server);
CREATE INDEX mms_thread_date_index ON mms (thread_id, date_received);
CREATE INDEX mms_reactions_unread_index ON mms (reactions_unread);
CREATE TRIGGER mms_ai AFTER INSERT ON mms BEGIN INSERT INTO mms_fts(rowid, body, thread_id) VALUES (new._id, new.body, new.thread_id); END;
CREATE TRIGGER mms_ad AFTER DELETE ON mms BEGIN INSERT INTO mms_fts(mms_fts, rowid, body, thread_id) VALUES('delete', old._id, old.body, old.thread_id); END;
CREATE TRIGGER mms_au AFTER UPDATE ON mms BEGIN INSERT INTO mms_fts(mms_fts, rowid, body, thread_id) VALUES('delete', old._id, old.body, old.thread_id); INSERT INTO mms_fts(rowid, body, thread_id) VALUES (new._id, new.body, new.thread_id); END;
CREATE TRIGGER msl_mms_delete AFTER DELETE ON mms BEGIN DELETE FROM msl_payload WHERE _id IN (SELECT payload_id FROM msl_message WHERE message_id = old._id AND is_mms = 1); END;
CREATE TABLE IF NOT EXISTS "sms" (_id INTEGER PRIMARY KEY AUTOINCREMENT, thread_id INTEGER, address INTEGER, address_device_id INTEGER DEFAULT 1, person INTEGER, date INTEGER, date_sent INTEGER, date_server INTEGER DEFAULT -1, protocol INTEGER, read INTEGER DEFAULT 0, status INTEGER DEFAULT -1, type INTEGER, reply_path_present INTEGER, delivery_receipt_count INTEGER DEFAULT 0, subject TEXT, body TEXT, mismatched_identities TEXT DEFAULT NULL, service_center TEXT, subscription_id INTEGER DEFAULT -1, expires_in INTEGER DEFAULT 0, expire_started INTEGER DEFAULT 0, notified DEFAULT 0, read_receipt_count INTEGER DEFAULT 0, unidentified INTEGER DEFAULT 0, reactions BLOB DEFAULT NULL, reactions_unread INTEGER DEFAULT 0, reactions_last_seen INTEGER DEFAULT -1, remote_deleted INTEGER DEFAULT 0, notified_timestamp INTEGER DEFAULT 0, server_guid TEXT DEFAULT NULL, receipt_timestamp INTEGER DEFAULT -1);
CREATE INDEX sms_read_and_notified_and_thread_id_index ON sms(read, notified, thread_id);
CREATE INDEX sms_type_index ON sms (type);
CREATE INDEX sms_date_sent_index ON sms (date_sent, address, thread_id);
CREATE INDEX sms_date_server_index ON sms (date_server);
CREATE INDEX sms_thread_date_index ON sms (thread_id, date);
CREATE INDEX sms_reactions_unread_index ON sms (reactions_unread);
CREATE TRIGGER sms_ai AFTER INSERT ON sms BEGIN INSERT INTO sms_fts(rowid, body, thread_id) VALUES (new._id, new.body, new.thread_id); END;
CREATE TRIGGER sms_ad AFTER DELETE ON sms BEGIN INSERT INTO sms_fts(sms_fts, rowid, body, thread_id) VALUES('delete', old._id, old.body, old.thread_id); END;
CREATE TRIGGER sms_au AFTER UPDATE ON sms BEGIN INSERT INTO sms_fts(sms_fts, rowid, body, thread_id) VALUES('delete', old._id, old.body, old.thread_id); INSERT INTO sms_fts(rowid, body, thread_id) VALUES(new._id, new.body, new.thread_id); END;
CREATE TRIGGER msl_sms_delete AFTER DELETE ON sms BEGIN DELETE FROM msl_payload WHERE _id IN (SELECT payload_id FROM msl_message WHERE message_id = old._id AND is_mms = 0); END;
CREATE TABLE avatar_picker (_id INTEGER PRIMARY KEY AUTOINCREMENT, last_used INTEGER DEFAULT 0, group_id TEXT DEFAULT NULL, avatar BLOB NOT NULL);
CREATE TABLE IF NOT EXISTS "identities" (_id INTEGER PRIMARY KEY AUTOINCREMENT, address TEXT UNIQUE NOT NULL, identity_key TEXT, first_use INTEGER DEFAULT 0, timestamp INTEGER DEFAULT 0, verified INTEGER DEFAULT 0, nonblocking_approval INTEGER DEFAULT 0);
CREATE TABLE group_call_ring (_id INTEGER PRIMARY KEY, ring_id INTEGER UNIQUE, date_received INTEGER, ring_state INTEGER);
CREATE INDEX date_received_index on group_call_ring (date_received);
CREATE TABLE IF NOT EXISTS "sender_keys" (_id INTEGER PRIMARY KEY AUTOINCREMENT, address TEXT NOT NULL, device INTEGER NOT NULL, distribution_id TEXT NOT NULL, record BLOB NOT NULL, created_at INTEGER NOT NULL, UNIQUE(address, device, distribution_id) ON CONFLICT REPLACE);
CREATE TABLE reaction (
  _id INTEGER PRIMARY KEY,
  message_id INTEGER NOT NULL,
  is_mms INTEGER NOT NULL,
  author_id INTEGER NOT NULL REFERENCES recipient (_id) ON DELETE CASCADE,
  emoji TEXT NOT NULL,
  date_sent INTEGER NOT NULL,
  date_received INTEGER NOT NULL,
  UNIQUE(message_id, is_mms, author_id) ON CONFLICT REPLACE
);
CREATE TRIGGER reactions_sms_delete AFTER DELETE ON sms BEGIN DELETE FROM reaction WHERE message_id = old._id AND is_mms = 0; END;
CREATE UNIQUE INDEX recipient_pni_index ON recipient (pni);
CREATE TABLE notification_profile (
  _id INTEGER PRIMARY KEY AUTOINCREMENT, 
  name TEXT NOT NULL UNIQUE,
  emoji TEXT NOT NULL,
  color TEXT NOT NULL,
  created_at INTEGER NOT NULL,
  allow_all_calls INTEGER NOT NULL DEFAULT 0,
  allow_all_mentions INTEGER NOT NULL DEFAULT 0
);
CREATE TABLE notification_profile_schedule (
  _id INTEGER PRIMARY KEY AUTOINCREMENT,
  notification_profile_id INTEGER NOT NULL REFERENCES notification_profile (_id) ON DELETE CASCADE,
  enabled INTEGER NOT NULL DEFAULT 0,
  start INTEGER NOT NULL,
  end INTEGER NOT NULL,
  days_enabled TEXT NOT NULL
);
CREATE TABLE notification_profile_allowed_members (
  _id INTEGER PRIMARY KEY AUTOINCREMENT,
  notification_profile_id INTEGER NOT NULL REFERENCES notification_profile (_id) ON DELETE CASCADE,
  recipient_id INTEGER NOT NULL,
  UNIQUE(notification_profile_id, recipient_id) ON CONFLICT REPLACE);
CREATE INDEX notification_profile_schedule_profile_index ON notification_profile_schedule (notification_profile_id);
CREATE INDEX notification_profile_allowed_members_profile_index ON notification_profile_allowed_members (notification_profile_id);
CREATE TRIGGER reactions_mms_delete AFTER DELETE ON mms BEGIN DELETE FROM reaction WHERE message_id = old._id AND is_mms = 1; END;
CREATE TABLE IF NOT EXISTS "one_time_prekeys" (
  _id INTEGER PRIMARY KEY,
  account_id TEXT NOT NULL,
  key_id INTEGER,
  public_key TEXT NOT NULL,
  private_key TEXT NOT NULL,
  UNIQUE(account_id, key_id)
);
CREATE TABLE IF NOT EXISTS "signed_prekeys" (
  _id INTEGER PRIMARY KEY,
  account_id TEXT NOT NULL,
  key_id INTEGER,
  public_key TEXT NOT NULL,
  private_key TEXT NOT NULL,
  signature TEXT NOT NULL,
  timestamp INTEGER DEFAULT 0,
  UNIQUE(account_id, key_id)
);
CREATE TABLE IF NOT EXISTS "sessions" (
  _id INTEGER PRIMARY KEY AUTOINCREMENT,
  account_id TEXT NOT NULL,
  address TEXT NOT NULL,
  device INTEGER NOT NULL,
  record BLOB NOT NULL,
  UNIQUE(account_id, address, device)
);
CREATE TABLE donation_receipt (
  _id INTEGER PRIMARY KEY AUTOINCREMENT,
  receipt_type TEXT NOT NULL,
  receipt_date INTEGER NOT NULL,
  amount TEXT NOT NULL,
  currency TEXT NOT NULL,
  subscription_level INTEGER NOT NULL
);
CREATE INDEX donation_receipt_type_index ON donation_receipt (receipt_type);
CREATE INDEX donation_receipt_date_index ON donation_receipt (receipt_date);
CREATE INDEX mms_is_story_index ON mms (is_story);
CREATE INDEX mms_parent_story_id_index ON mms (parent_story_id);
CREATE TABLE distribution_list (
  _id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT UNIQUE NOT NULL,
  distribution_id TEXT UNIQUE NOT NULL,
  recipient_id INTEGER UNIQUE REFERENCES recipient (_id) ON DELETE CASCADE
, allows_replies INTEGER DEFAULT 1);
CREATE TABLE distribution_list_member (
  _id INTEGER PRIMARY KEY AUTOINCREMENT,
  list_id INTEGER NOT NULL REFERENCES distribution_list (_id) ON DELETE CASCADE,
  recipient_id INTEGER NOT NULL,
  UNIQUE(list_id, recipient_id) ON CONFLICT IGNORE
);
CREATE INDEX mms_thread_story_parent_story_index ON mms (thread_id, date_received, is_story, parent_story_id);

(In the meantime, I am slowly working on this functionality. But it will take quite a while longer)

[michaelsmoody]

So, looooong story short, I have a phone decide it no longer wants to boot (apparently, it won't validate the boot images), aaaaand my last Signal backup was from the last time I migrated phones, months ago. But I have an absolutely perfect Signal Desktop database (still going) with every message from day 1 of switching to Signal from years and years ago.

Obviously this is an important feature to me, and since I've switched phones and even laptops/desktops many times, and maintained my Signal Desktop copy (even through re-links), I'm actually reasonably familiar with the database structure now. I've delved into it as a result of migrating the same DB onto other devices (taking the same SQLite DB and copying it onto a different device, with a link, as well as learning about all of the foreign key constraints that it has (and wow (╯°□°）╯︵ ┻━┻).

So, how can I help? While I'm not familiar with your backup merge methodology, I am much more familiar with the underlying Signal Desktop database structure. Necessary and unnecessary tables, attachments and their folder structure, etc.

[bepaald]

After having left this issue sleep for a while, I am now back to slowly working on it. I have some free time this week and the next, hopefully I have something that's at least partially working by then.

Most of it shouldn't be too hard, just a matter of figuring out what info the Android db requires and where to find it in the dektop db. But so far I've been able to figure it out. Though there is a lot, I keep thinking of more things (emoji reactions, quotes, quotes-with-attachments). If I can't find some info, I'll definitely contact you @michaelsmoody. All the foreign key stuff on the desktop side shouldn't be an issue as this function will just be reading from it. Some things will probably not be in the first version of this function (like group status messages, they are a pain on the Android side).

There are also a few small things that need to be done outside of actually dealing with the backups, for instance command line args for this function and updating the readme and help files.

I'll let everyone here know as soon as I have something to test, hopefully in a week or two (though it may be delayed if I'm underestimating the work). Thanks!

[michaelsmoody]

@bepaald Not that it's super relevant, but your work here will be saving me a lot of lost messages because I had a Pixel 5a 5G decide to suddenly fail to boot back in October. Just "can't verify boot image". (I was....shall we say....upset). I'll be very happily making a series of donations that might otherwise have gone to data recovery efforts....

(Now, if only I could get into that phone for roughly 1 hour to recovery lost Google Photos from the last 6 months because I stupidly decided to disable backups during the summer at a summer camp (╯°□°）╯︵ ┻━┻ )

But let me know if you need anything about the structures of the Signal Desktop database. I've spent about 6 weeks investigating it between this event and also because of transferring my Signal Desktop DBs across devices for re-linking and new device purposes (everything Signal says not to do).....I've more than broken sessions a few times.

Anything I can do to help on readme, help, or command line args? If you have an active WIP branch, I'm happy to pull it down.

[eric-radiant]

Looking forward to seeing this work!

[thomassth]

Have you guys used https://github.com/tbvdm/sigtop ?

It would be nice if I can throw that sql in, and get a backup file for android apps

[bepaald]

Just a quick update:

I'm pretty much at the point where I wanted to be before I started testing this. It was actually a bit more involved than I thought it'd be and I expect a lot of problems before I'll get any of you to test it (after which I expect there will be many many more problems :-) ). There are also some things I am knowingly omitting for the moment (like read_receipts and group status messages), they will be added later if current functionality is working.

@michaelsmoody I promised I'd come to you if I needed help with the desktop database, so here goes: in the android backup, if an attachment is a voice note (audio recorded in the app), it gets a flag (voice_note) set to true, other audio attachments leave it set to false. I can't find anything analogous in the desktop database. It's probably not very important, but I'd thought I'd ask if you knew of something. Found it!

[bepaald]

So, I guess it's time for some feedback. Since my last message, I've tested this function (actually importing the resulting backup on hardware) and to my great surprise I have seen no crashes and everything I expected to work worked. I've since implemented only some small fixes and added support for call-type messages, and things still worked this morning. So, I'd like to get some feedback.

Note, I do not expect this to work without errors for everyone else yet. My desktop database is tiny (it was created specifically for this issue) and new. In the android database, there is lots of left over stuff from old versions and data in newer columns is not filled in on older messages. This regularly causes problems on the Android db, and I now expect similar problems with the desktop database that you might run in to if you have an older linked desktop. Also, there could very well be message-types I'm not aware of that will cause problems.

This function turns out to be a lot more involved than I originally expected and I'm curious how much effort it will be to get fully working and keep it up-to-date in the future. As any good programming job: it is in dire need of refactoring even before it's finished, lol.

What is implemented and i hope is working:

• All 'incoming' and 'outgoing' messages. Both in groups, and 1-on-1 conversations, including attachments, mentions, reactions and quotes (and attachments/mentions inside quotes). This should hopefully cover 99% of messages.
• Call-type messages.
• Limiting the import to a certain time frame, either explicitly or automatically. In automatic mode, the program finds the earliest message in the Android backup and only imports messages older than that.

Things I know are not working:

• Read/receive receipts. I find this pretty annoying, as the checkmarks are usually always visible in a conversation, and I find it very noticeable if they're not there. I'll definitely add these later. DONE
• Importing messages into a new thread. If no matching conversation is found in the Android database, the entire conversation is skipped. I think this will not be difficult to fix, as long as the contacts do exist. DONE (untested) If the contacts do not exist, it may be impossible to create and import the thread (but I'd need to investigate).
• Status messages other than call-messages. Ones I know about: all group updates (like group creation and membership changes, this will be a tough one (ref)), disappearing message timer changes, profile key changes. There may be more I'm forgetting (or don't know about).
• I have zero experience with 'stories', 'badges' and 'payments'. Best case scenario: these are skipped without crashing.
• Old groups. If a group was never updated to groupV2, it's probably gonna be trouble.

How to test: Create a backup file, save it to your computer. Run the program (get the latest version):

signalbackup-tools [backup file] [password] --importfromdesktop

That's it. The program should automatically find your desktop's database. If it doesn't, --importfromdesktop optionally takes two arguments that should be the location of the config.json file, and the /sql/db.sqlite directories^*.

If you want to limit the date range, add --autolimittodates or --limittodates [STARTDATE,ENDDATE] (format of the dates is the same as croptodates).

Check the programs output. Note any errors and warnings, check if it ends successfully (the last line should be Checking database integrity (full)... ok). If everything works and you're feeling very lucky, add -o [output] to the import command and import the resulting backup on your phone. Due to the missing features, you'll probably want to return to your original backup afterwards, so I realize this is a bit of a hassle. Make sure you ALWAYS keep a backup of your original backup file! You could loose all your messages! If restoring works, check some (or all) the imported messages, scroll around, long press them for details, play audio/video attachments. See if anything crashes and if you are missing things (other than the ones listed above).

Do not get your hopes up just yet, I still expect crashes, either in my program or in Signal when trying to restore. But as things are working for me, there's only one way to find out...

Thanks!

* _{Why two arguments? Before testing, I had read that windows has separate locations for these dirs, but after testing this turns out to not be true (on my W10 VM), so it'll probably change in the future. On linux, the default for both locations is ~/.config/Signal/. Fire up your favorite search engine for other platforms (though as mentioned, one of the top results is not correct/outdated).}

[duairc]

First of all, I just want to say thank you so much for working on this. I've just spent the last few hours trying this out.

The first issue I had was that the --importfromdesktop was crashing with:

 - Importing 35167 messages into thread._id 1
Finding recipient for uuid: xxxxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx
terminate called after throwing an instance of 'std::bad_any_cast'
  what():  bad any_cast

I eventually narrowed the cause down to this line of signalbackup/importfromdesktop.cc:

          mmsquote_type = (quote_results.getValueAs<long long int>(0, "quote_isgiftbadge") == false ? 0 : 1);

I worked around this by replacing it with just:

          mmsquote_type = 0;

That was enough to get me as far Checking database integrity (full)... ok.

Then when I started trying to run it with --output, I started hitting other issues. There were warnings related to attachments not being found (the following is the output of ---scanmissingattachments:

Got 15 attachments with data not found
Checking 1 of 15: 576,1580118511354... UNEXPECTED! details:
------------------------------------
| quote | ct        | pending_push |
------------------------------------
| 1     | image/png | 0            |
------------------------------------
Checking 2 of 15: 1008,1587692432569... OK, EXPECTED (pending_push = 2)
Checking 3 of 15: 1009,1587692509534... OK, EXPECTED (pending_push = 2)
Checking 4 of 15: 1010,1587692542088... OK, EXPECTED (pending_push = 2)
Checking 5 of 15: 1011,1587692818644... OK, EXPECTED (pending_push = 2)
Checking 6 of 15: 1012,1587692848592... OK, EXPECTED (pending_push = 2)
Checking 7 of 15: 1013,1587692886254... OK, EXPECTED (pending_push = 2)
Checking 8 of 15: 1021,1587695373116... OK, EXPECTED (pending_push = 2)
Checking 9 of 15: 1022,1587695433386... OK, EXPECTED (pending_push = 2)
Checking 10 of 15: 1045,1587926402754... OK, EXPECTED (pending_push = 2)
Checking 11 of 15: 1492,1593798200066... OK, EXPECTED (pending_push = 2)
Checking 12 of 15: 3172,1605477529231... OK, EXPECTED (pending_push = 3)
Checking 13 of 15: 4924,1616870647743... OK, EXPECTED (pending_push = 3)
Checking 14 of 15: 5520,1620055801717... UNEXPECTED! details:
-------------------------------------
| quote | ct         | pending_push |
-------------------------------------
| 0     | image/jpeg | 0            |
-------------------------------------
Checking 15 of 15: 5534,1620214434910... OK, EXPECTED (pending_push = 3)

But I don't think is what was causing the crash, the crash looked like this:

  Dealing with table 'part'... 576/14623 entries...Warning: attachment data not found (rowid: 576, uniqueid: 1580118511354)
  Dealing with table 'part'... 5433/14623 entries...Warning: attachment data not found (rowid: 5520, uniqueid: 1620055801717)
  Dealing with table 'part'... 7796/14623 entries...Failed to open backup file '' for reading attachment
Failed to export backup to '/tmp/merged.backup'

I must confess that I'm not a C++ programmer and I don't particularly understand the code or what's going wrong here, but I did attempt to work around this by calling setAttachmentData with malloc(1) and returning 0 where the code currently throws Failed to open backup file '' for reading attachment and returns 1. This "succeeded" and got all the way to the end with a merged backup. I then tried loading this onto my phone. Signal "successfully" imported this backup, asked me for my phone number etc. But then right after setting up the account, just before displaying the list of chats, it crashed, and when I opened it again, my chats were there, but only the messages that were older than the oldest message from the desktop backup.

Anyway, I guess what I'm saying is I'm happy to continue trying to test this and giving you any information you need to fix things, and I would also really appreciate if you had any insights into what's going wrong and could take a look at this, because otherwise I have lost a lot of messages.

Thank you!

[bepaald]

First of all, I just want to say thank you so much for working on this. I've just spent the last few hours trying this out.

Thank you so much for testing.

The first issue I had was that the --importfromdesktop was crashing with:

 - Importing 35167 messages into thread._id 1
Finding recipient for uuid: xxxxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx
terminate called after throwing an instance of 'std::bad_any_cast'
  what():  bad any_cast

I eventually narrowed the cause down to this line of signalbackup/importfromdesktop.cc:

          mmsquote_type = (quote_results.getValueAs<long long int>(0, "quote_isgiftbadge") == false ? 0 : 1);

I worked around this by replacing it with just:

          mmsquote_type = 0;

That was enough to get me as far Checking database integrity (full)... ok.

Ok, I think I fixed this. Pure speculation, but I'm guessing 'isGiftBadge' is not a property of older quotes (as gift badges did not exist when quotes were first introduced). I expected older databases would have problems like this. If this was indeed the problem, it should be ok now (if not, it should still be an easy fix, and your solution was fine as well).

Then when I started trying to run it with --output, I started hitting other issues. There were warnings related to attachments not being found (the following is the output of ---scanmissingattachments: [...]

Thanks for the output of scanmissingattachments. Unfortunately I'm not learning anything new from that, so that mystery will live on. Anyway, these warnings can always be safely ignored and - as you suspected - have nothing to do with the crash (or even with the desktop import).

But I don't think is what was causing the crash, the crash looked like this:

  Dealing with table 'part'... 576/14623 entries...Warning: attachment data not found (rowid: 576, uniqueid: 1580118511354)
  Dealing with table 'part'... 5433/14623 entries...Warning: attachment data not found (rowid: 5520, uniqueid: 1620055801717)
  Dealing with table 'part'... 7796/14623 entries...Failed to open backup file '' for reading attachment
Failed to export backup to '/tmp/merged.backup'

Now that one is unexpected. I'm not actually sure why this is happening. I have added some code that may have an effect on it. And added some more error checking and error messages that hopefully give some more helpful output in this case. I will continue to try and investigate, but if you have the time, it would also be great if you could try the current version and let me know the new output (no need to try restoring on a phone unless all errors are suddenly gone).

I must confess that I'm not a C++ programmer and I don't particularly understand the code or what's going wrong here, but I did attempt to work around this by calling setAttachmentData with malloc(1) and returning 0 where the code currently throws Failed to open backup file '' for reading attachment and returns 1. This "succeeded" and got all the way to the end with a merged backup. I then tried loading this onto my phone. Signal "successfully" imported this backup, asked me for my phone number etc. But then right after setting up the account, just before displaying the list of chats, it crashed, and when I opened it again, my chats were there, but only the messages that were older than the oldest message from the desktop backup.

That is also interesting. Does this mean the messages on your phone are older that the ones on the desktop? I think it's a safe bet the crash and missing messages have to do with the desktop-import problem, so I think we should try to get that fixed and not worry about this for now.

Anyway, I guess what I'm saying is I'm happy to continue trying to test this and giving you any information you need to fix things, and I would also really appreciate if you had any insights into what's going wrong and could take a look at this, because otherwise I have lost a lot of messages.

Again, thank you for testing. There's indeed a good chance I'll have more tests and questions for you as we work through this. But for now, whenever you have time, please just run again and report back. I'll try to investigate a little further as well.

[duairc]

Okay, thank you so much for responding! I'll test this out shortly. But just to answer this question:

That is also interesting. Does this mean the messages on your phone are older that the ones on the desktop? I think it's a safe bet the crash and missing messages have to do with the desktop-import problem, so I think we should try to get that fixed and not worry about this for now.

Yes, my situation is that I have a Signal backup from a phone from August 2021, but I hadn't copied the backup off of my phone since then (I have since lost this phone), so I'm trying to augment that backup with the history from my desktop Signal for the period between August 2021 and now.

By the way, my first attempt failed with some SQLite schema error inserting into some quote-related table (I don't have the exact error on hand), but I worked around this by restoring the backup on the latest (mobile) version of Signal, creating a new backup presumably with the latest schema, and then copying that back to my laptop and trying again with signalbackup-tools.

But yeah, I'll run with your latest commits and let you know how I get on.

[duairc]

Okay, well the good news is that signalbackup-tools ran without error (still a bunch of warnings though). But when I try to import it in Signal, I just get "cannot import backups from newer versions of Signal"

[aa755]

I guess you have already checked this but is your android running the latest version of signal?

[duairc]

Yes, it is, it's the running 6.2.4 (116407). I saw in the diff a change to autoversion.h, so I tried reverting that in case that changed something, but it didn't help.

[duairc]

This doesn't seem to be related to signalbackup-tools, I'm getting this error even on backup files that weren't created with it. I'll try to get to the bottom of that.

[bepaald]

Okay, well the good news is that signalbackup-tools ran without error (still a bunch of warnings though). But when I try to import it in Signal, I just get "cannot import backups from newer versions of Signal"

Ok, that's good I think, any new warnings or just the same missing attachment ones again?

My program does not touch the version of the backup file, it just imports messages into the (exisitng) sqlite database (without changing its schema). Any chance the backup you're importing into was created with a beta version of Signal? If you run the tool with --listthreads you get (among other things) the database version, which I think (but I'd need to check) should be at 163 currently.

[duairc]

The database version was at 164 somehow, so I guess that was it. I "solved" this by re-importing my original backup and then re-exporting that and working with that. Maybe I did end up with a beta version of Signal somehow at some point. Anyway, that's solved now.

Importing the resulting merged backup actually worked this time, kind of.

The main view that shows the chats the list of chats with the most recent message still shows the "most recent" message from my phone backup (from 2021), not any of the more recent ones from my desktop history. But if I click into the actual chat, I'm able to scroll down further to more recent messages that did come from the desktop. This doesn't actually update the order of the chats in the main view though. Also, last night at least, Signal would immediately crash after scrolling down in these chats. However, today it's not crashing anymore, and I can scroll to the bottom of these chats and see the actual most recent messages. The only thing I can think of that "changed" between last night and this morning was that I received a message from somebody.

The other (big) issue is that any chats that were created after August 2021, i.e., chats that exist only in my desktop history, do not seem to appear at all. And if I explicitly start a chat with one of these Signal contacts, there is no history there at all.

But yeah, progress.

And yes, there were more warnings/output from signalbackup-tools this time. I'm happy to provide you the full output but I'm a bit wary of posting it here publicly because I'm not sure what information it contains.

[duairc]

The main thing that stands out is that there a lot of errors like:

Finding recipient for uuid: xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx
Warning failed to get recipient id for message partner. Skipping message.
Finding recipient for uuid: xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx
Warning failed to get recipient id for message partner. Skipping message.

I'm guessing this is related to what I see above.

[duairc]

I wonder if I restored my original backup on my phone, created chats with all the contacts that didn't exist yet at that point, then created another backup and used that with signalbackup-tools, would it find the "recipient id for message partner" correctly?

[bepaald]

The main view that shows the chats the list of chats with the most recent message still shows the "most recent" message from my phone backup (from 2021), not any of the more recent ones from my desktop history. But if I click into the actual chat, I'm able to scroll down further to more recent messages that did come from the desktop. This doesn't actually update the order of the chats in the main view though. Also, last night at least, Signal would immediately crash after scrolling down in these chats. However, today it's not crashing anymore, and I can scroll to the bottom of these chats and see the actual most recent messages. The only thing I can think of that "changed" between last night and this morning was that I received a message from somebody.

Hmm, I more or less skipped updating the thread database on purpose, as I hadn't touched that function in so long I figured it would need updating, and I thought the app would just fill that data in (maybe after some delay) without actually crashing. But, I think I was probably wrong about that. (receiving a message will have triggered the app to update the thread table preventing the crash from reoccurring).

Anyway, there was already a function for updating the thread table, so I just added a call to that function after importing. I hope that fixes the crash.

The other (big) issue is that any chats that were created after August 2021, i.e., chats that exist only in my desktop history, do not seem to appear at all. And if I explicitly start a chat with one of these Signal contacts, there is no history there at all.

Ok, I had implemented creating threads if they were not present in the database, but on looking at it, there was at least 1 stupid mistake in there (maybe even two!). They should hopefully be fixed now (but you'll have to tell me ;-) )

Do note, that while creating a new thread for chats is implemented, the chat-partner needs to be present in the Android backup. If the chat partner can not be found, the conversation is skipped (and a warning is printed).

And yes, there were more warnings/output from signalbackup-tools this time. I'm happy to provide you the full output but I'm a bit wary of posting it here publicly because I'm not sure what information it contains.

You could always email me directly, though the summaries and snippets you've been posting have been helpful already. I don't think there is much sensitive information in the output. Just uuids I think (which are unique to an individual (or signal group) but as far as I know cannot be 'decrypted' into a name or phone number or anything like that).

The main thing that stands out is that there a lot of errors like: [...]

Yes, so as mentioned above, creating non-existing contacts is not (yet) supported. The entries in the recipient table contain a few things that I'd need to research and are linked to other data in other tables that would require even more research. I'm afraid just adding a new contact will go badly if the various keys (senderkey, storagekey, profilekey, signedprekey,...) are missing or incorrect. I might try doing that in the future though.

I wonder if I restored my original backup on my phone, created chats with all the contacts that didn't exist yet at that point, then created another backup and used that with signalbackup-tools, would it find the "recipient id for message partner" correctly?

Yes that would probably work. Though (assuming I fixed that bug at least) you shouldn't need to actually create a chat with those contacts, as long as they exist in Signal on your phone. That is, if you hit the 'new message' button in the lower right of the conversation list, these contacts should appear there so you could start a chat with them.

But yeah, progress.

Indeed :-) Sorry it's taking a few tries, but we'll get there!

I'd love for you to try again, and see if the new threads show up this time, and if the crash still happens.

[duairc]

Okay, this time it worked perfectly :) Thank you so much!

[bepaald]

@duairc That's excellent news, very happy to hear it! Did you end up (having to) start chats with every/some contacts? I hope no problems show themselves later, but please let me know if they do. And thank you for being the first brave soul to test this function.

@everyoneelse The first problems have been fixed, but other databases might contain other types of messages so more testing would certainly be helpful!

[duairc]

I did open chats with a couple of contacts just in case, but there were a few chats with contacts whose phone numbers are no longer even registered for Signal where this was not possible. However signalbackup-tools still managed to recover messages from these conversations, so from that I'd conclude that starting chats is not necessary.

[thomassth]

In terms of data preservation I'd say it's ready to ship 👍 Maybe label it as a beta function

[bepaald]

In terms of data preservation I'd say it's ready to ship +1

Yeah well, I guess it is shipping already. I'll make it 'official' by updating the readme and --help output as soon as I have time, hopefully next weekend.

Maybe label it as a beta function

Looking at the readme, most functions are already labeled 'experimental' or 'highly experimental', LOL. Maybe I should label this one 'super-duper-experimental' :-)

[thomassth]

@bepaald I'd like to thank you once again for saving my chat record

I now get to see those last messages from people I missed, which is beyond priceless to me

let me know if you have a kofi / want to open github donation, I'd like to chip in a few bucks for my gratitude

[bepaald]

@bepaald I'd like to thank you once again for saving my chat record

I now get to see those last messages from people I missed, which is beyond priceless to me

No problem, happy to help. I take it the import went well? Any problems that you noticed so far?

let me know if you have a kofi / want to open github donation, I'd like to chip in a few bucks for my gratitude

There are some donation options at the bottom of the readme. But don't feel obligated.

^{I didn't know about github sponsors, I'll look into it. But I'd feel bad if anyone starts a monthly donation, if I'm busy sometimes a month goes by without me working on the project at all. Added ko-fi links}

Thanks for your kind words!

[cat3na]

Very excited to see how much progress has been made on this! I've been working on restoring a few handfuls of messages that reached my desktop install but not my phone, and had been thinking of tackling some database translation work while doing that (though it's probably way out of my depth). Like you said earlier, they're pretty wildly different; having something even mostly functional so soon is great to see.

I've tested --importfromdesktop on my own backups, and I'm still getting the bad any_cast error mentioned previously. I am using the Windows binary, though it looks like that particular issue should be fixed in the latest version.

Reading backup file...
FRAME 270530 (100.0%)... Read entire backup file...

done!
Trying to match conversation (1/169)
Finding recipient for uuid: xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx
 - Importing 1550 messages into thread._id 93
terminate called after throwing an instance of 'std::bad_any_cast'
  what():  bad any_cast

One thing I'm noting after checking my recipient table is that the UUID mentioned corresponds with my own number (i.e.: Note to Self thread). This might have nothing to do with the problem, and may just be that my Note to Self is the first thread to import.

Thanks for all your work!

[bepaald]

Hi @cat3na! Thanks for testing this. When I got home from work, I immediately tried to replicate your issue (I hoped it had something to do with the note-to-self thread, which I hadn't tested). Unfortunately I ran into a ton of other issues since all my test-phones run Signal-beta and the database schema has suddenly changed quite a lot. I've spent the last few hours trying to fix that, but now I'm a too tired to continue. Just wanted to quickly let you know I read your message and I will look into it more, just not right now.

After all the fixing, I did quickly run the import with a note-to-self thread, but it seemed to work for me. I might have some custom version for you later to pinpoint the problem (there are a lot of any-casts) if you don't mind, but it will not be before tomorrow, because I'm beat.

Thanks again!

[bepaald]

@cat3na I have a version for you which will print out a lot of extra info around the any_casts. Could you try this one and let me know the output? I should be able to tell where it goes wrong from this. Thanks!

EDIT: the changes I mentioned in my previous post are out of beta. I don't remember what fixes were already in the debug version I attached to this post earlier, so this is just an up-to-date one. signalbackup-tools_win_debug.zip

[bepaald]

If there is anyone else with a particularly long existing desktop installation who wants to give feedback that would also be appreciated. No need to actually output a new backup or import on your phone, just run the program with --importfromdesktop to see if you see the same bug as cat3na. Thanks!

[michaelsmoody]

My deepest apologies @bepaald !! I apparently had broken notifications on this issue! I'm so sorry! I'll look into the question you asked me some time back (https://github.com/bepaald/signalbackup-tools/issues/57#issuecomment-1323706270 if it's still relevant?) as well as the question from two days ago (https://github.com/bepaald/signalbackup-tools/issues/57#issuecomment-1362981286)

I have an unbroken desktop installation dating back to 2017 on multiple devices linked to a mobile installation. Unfortunately, as mentioned, my mobile "broke", so, this is a perfect test!

I'll do it right away!

[bepaald]

No worries! The question about the voice note is no longer an issue, I found it. But testing is definitely very much appreciated. In your own time though, of course.

[cat3na]

@bepaald My apologies as well! I did a short writeup after running the first version you posted and then lost my draft, and I hadn't gotten back to it since then. It looks like the problem might lie with some quote message property?

Here is the link to the full output I got, and I also added a short (redacted) excerpt of the surrounding messages in the comments, in case that has relevant information. I'm not completely confident in this, but judging by the amount of times rowid appeared before the error, I'm guessing that it appeared in the 38th message in my Note to Self thread. That message quotes another message earlier in the thread (message 36), so the assumption would seem to hold. It appears to be from around July 2020 (schemaVersion 10).

Output (shortened):

[...]
Getting value of field 'rowid' (0)
Type: x Requested type: x
Getting value of field 'numattachments' (2)
Type: x Requested type: x
Getting value of field 'numreactions' (3)
Type: x Requested type: x
Getting value of field 'nummentions' (4)
Type: x Requested type: x
Getting value of field 'quote_referencedmessagenotfound' (6)
Type: Dn Requested type: x
terminate called after throwing an instance of 'std::bad_any_cast'
  what():  bad any_cast

I'll test the new debug version as well to see if it's still happening.

[cat3na]

I do still get the same error with the latest debug version.

[bepaald]

Thanks @cat3na! this one should be fixed. I hope it was the only problem in there, just in case it's not I have a new debug version here (not necessary to use this unless a similar problem occurs). Let me know if it works for you now.

signalbackup-tools_win_debug.zip

Thanks again!

[cat3na]

@bepaald Well, unfortunately I can't say that it works, but that particular problem seems to be solved. The program gets a lot further now, and seems to get to some of my other threads now before terminating again. I'm a little bit mystified at these new issues, though.

The logs are a lot longer this time, I have both the regular version and the debug version at this link.

I'm getting a lot of:

Trying to match conversation ([x]/169)
Error : Failed to base64 decode data: [garbled characters]
Error : Failed to determine uuid. Skipping.

including one where it alters the error line:

Trying to match conversation (4/169)
j¾F¦1'mfa2led to base64 decode data: ¡í™†

as well as several different warnings further down. That one appears 15 times in a row, for conversations 2-16, and then not again for the rest of the run.

---

Some of the other warnings in the log:

• appears 29 times: Warning: Unsupported messagetype 'keychange'. Skipping message.
• appears 36 times: Warning: Unsupported messagetype ''. Skipping message.
• appears 15 times: Warning: Unsupported messagetype 'profile-change'. Skipping message.
• appears 5 times: Warning: Failed to get number of attachment in quoted message. Skipping
• appears 5 times:

  Failed to open image for reading: F:\XXXXX\Signal/attachments.noindex/
  Error: Trying to set attachment data. Skipping.

• appears 218 times:

  Finding recipient for uuid:
  warning

  (I didn't take the UUID out here, it's actually blank)

• appears 259 times: Warning: Failed to find quote author. skipping
  • 7 of those 259 times are paired with Finding recipient for uuid:, where the UUID is blank, and the remaining 252 are paired with my own UUID.
  • (Maybe self-quoting runs into particular problems here?)

The keychanges and profile changes are expected, since I think you mentioned you haven't handled all message types yet, but I do wonder what all the other skipped messages and blank fields are about.

---

Up towards the end I noticed about 100 of the Warning: Failed to find quote author. skipping with my own UUID, and then the program ultimately terminates with another any_cast error, this time after the quote_id field. Quote_id resolved fine in all the previous cases, so I don't know what's going on here.

Getting value of field 'quote_referencedmessagenotfound' (6)
Type: x Requested type: x
Getting value of field 'quote_isgiftbadge' (7)
Type: x Requested type: x
Getting value of field 'quote_id' (0)
Type: NSt7__cxx1112basic_stringIcSt11char_traitsIcESaIcEEE Requested type: x
terminate called after throwing an instance of 'std::bad_any_cast'
  what():  bad any_cast

[bepaald]

Wow, thanks so much for your detailed report. There is a lot there to deal with it seems :(

About the Failed to base64 decode data: these are all group conversations. In my (modern) database with (modern) groups, the groupId is a base64 encoded string that matches the group_id in the Android database. My best guess is these are old V1 groups, and that these are saved differently. I will write something to check for that. I could probably support these quite easily. (the ones that garble the input line probably just happen to contain some random control characters that put the cursor at the beginning of the line).

As you rightly mention, the unknown message types are expected, though the '' is a little unexpected...

I need to think about the attachment-failure. I think there are some cases where this is expected, but I'm not immediately sure in what cases specifically. It would probably be useful if make the program print enough information for the user (you) to check the message in Signal Desktop to see if there is anything wrong with the attachment (maybe it's really missing).

The blank uuid is very strange, though there are a few threads that belong to conversation-partners with no uuid (the 'Signal release notes & news-channel' for example). I'm hoping this is that, and I intend to just skip that conversation. Unfortunately, there have not been any announcements on that channel since I linked my dev-phone so I don't have that conversation in my database yet.

The quote_id type mismatch is a real headscratcher. In all other cases, the quote_id is the timestamp of the quoted message (that's how it identifies which message is quoted), but in this case it is suddenly a string-type value. Very curious. I have a new debug version that prints the value of the mismatched type (and only prints when the type mismatches, so it should give a lot less output). I will post it here tomorrow after I hopefully also add some code to deal with old-type groups.

Sorry this is taking a while, some of these things would probably be fixed in minutes if I had a similar input database. But working in the dark like this is very difficult. I hope you're not tired of it already, as I don't think this will be the last time I will be asking you for feedback. Maybe I could also have you export the sqlite databases from your desktop and android backups and run some raw queries for me? I'd have instructions for you to export them (both can be done with signalbackup-tools) and what queries to run.

Anyway, again thank you so much. I will report back here with a new version that will give some more info given the errors found in you log.

[bepaald]

@cat3na I've just pushed some changes that hopefully give me a little more info on all the places things go wrong in your log. It will not fix anything yet, just hopefully tell me what's going on. No need for a separate debug version, the info should be printed when a problem is encountered (if I correctly found all the places, I can't really test it).

I've also made it so the uuid's are automatically masked with x's so you shouldn't need to do that manually anymore, but there may be more personal data in the data that's now printed at the error points (not sure, but please check it carefully).

Thanks!

[michaelsmoody]

@bepaald I wanted to let you know that I've spent a few weeks on this with various long running backup files, long running desktop installations, etc, and I'm experiencing issues that are slightly different. Mine might relate to the same author UUID problem however.

I've been looking into it, and I'm going to tear the databases apart.

I was wondering if you might be willing to throw the debug version into a specific branch for me to develop against / tag it?

[bepaald]

Hi @michaelsmoody, as mentioned in my last message there is no debug version anymore. The function should hopefully print out some relevant info when it encounters unexpected data (make sure you use the latest version).

I would be very interested in seeing (some of) its output, I have not seen any since I made my last changes.

Also, if you're up for it, just working with the raw sqlite databases might be a lot quicker than continuously coding, compiling and running the program. Once you know what to look for at least. You could get both the Desktop and Android databases by running signalbackup-tools [input] [passphrase] -o [outputdirectory] --onlydb --dumpdesktopdb [/path/to/signaldesktop/data] [/path/to/signaldesktop/data]. This will dump the Desktop database to the current directory at desktop.db, and the Android database to outputdirectory/database.sqlite, both decrypted.

Thanks for investigating!

[michaelsmoody]

As requested @bepaald (yesterday's build)

Signal backup from ~2021, also tried from 2022 from Android. Continuously running from 2017. Desktop instance linked since that same time frame. I'm going to attempt one from my MacBook (which has a FEW conversations that weren't "orphaned" just in case. The one on my Surface Pro 7+ has a few conversations that went missing, oddly.

The problem is that my MacBook became unlinked, and I'd really like to get the MacBook and Surface Pro 7+ Desktop instances .... combined .... (guidance?)

With that said, I'll make sure that's not the issue.

Trying to match conversation (1/79) (type: private)
Warning: Failed to find matching thread for conversation, creating. (id: 80c54f42-58bc-4a4c-8db8-5c01772b1bf8)
 - Importing 1 messages into thread._id 65
Error : During sqlite3_prepare_v2(): table sms has no column named server_guid
  Query: "INSERT INTO sms(thread_id, address, date, date_sent, date_server, type, body, remote_deleted, server_guid) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) RETURNING _id"
Error: Inserting into sms
Trying to match conversation (2/79) (type: private)
 - Importing 80 messages into thread._id 41
Error : During sqlite3_prepare_v2(): table sms has no column named server_guid
  Query: "INSERT INTO sms(thread_id, address, date, date_sent, date_server, type, body, remote_deleted, server_guid) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) RETURNING _id"
Error: Inserting into sms
Error : During sqlite3_prepare_v2(): table sms has no column named server_guid
  Query: "INSERT INTO sms(thread_id, address, date, date_sent, date_server, type, body, remote_deleted, server_guid) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) RETURNING _id"
Error: Inserting into sms
Error : During sqlite3_prepare_v2(): table sms has no column named server_guid
  Query: "INSERT INTO sms(thread_id, address, date, date_sent, date_server, type, body, remote_deleted, server_guid) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) RETURNING _id"
Error: Inserting into sms
Error : During sqlite3_prepare_v2(): table sms has no column named server_guid
  Query: "INSERT INTO sms(thread_id, address, date, date_sent, date_server, type, body, remote_deleted, server_guid) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) RETURNING _id"
Error: Inserting into sms
Error : During sqlite3_prepare_v2(): table sms has no column named server_guid
  Query: "INSERT INTO sms(thread_id, address, date, date_sent, date_server, type, body, remote_deleted, server_guid) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) RETURNING _id"
Error: Inserting into sms
Error : During sqlite3_prepare_v2(): table sms has no column named server_guid
  Query: "INSERT INTO sms(thread_id, address, date, date_sent, date_server, type, body, remote_deleted, server_guid) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) RETURNING _id"
Error: Inserting into sms
Error : During sqlite3_prepare_v2(): table sms has no column named server_guid
  Query: "INSERT INTO sms(thread_id, address, date, date_sent, date_server, type, body, remote_deleted, server_guid) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) RETURNING _id"
Error: Inserting into sms
Error : During sqlite3_prepare_v2(): table sms has no column named server_guid
  Query: "INSERT INTO sms(thread_id, address, date, date_sent, date_server, type, body, remote_deleted, server_guid) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) RETURNING _id"
Error: Inserting into sms
Error : During sqlite3_prepare_v2(): table sms has no column named server_guid
  Query: "INSERT INTO sms(thread_id, address, date, date_sent, date_server, type, body, remote_deleted, server_guid) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) RETURNING _id"
Error: Inserting into sms
Error : During sqlite3_prepare_v2(): table sms has no column named server_guid
  Query: "INSERT INTO sms(thread_id, address, date, date_sent, date_server, type, body, remote_deleted, server_guid) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) RETURNING _id"
Error: Inserting into sms
Error : During sqlite3_prepare_v2(): table sms has no column named server_guid
  Query: "INSERT INTO sms(thread_id, address, date, date_sent, date_server, type, body, remote_deleted, server_guid) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) RETURNING _id"
Error: Inserting into sms
Error : During sqlite3_prepare_v2(): table mms has no column named quote_type
  Query: "INSERT INTO mms(thread_id, date, date_received, date_server, msg_box, body, address, m_type, quote_id, quote_author, quote_body, quote_missing, quote_mentions, remote_deleted, quote_type) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) RETURNING _id"
Error: Inserting into mms
terminate called after throwing an instance of 'std::bad_any_cast'
  what():  bad any_cast

[michaelsmoody]

It's worth mentioning that I have a VERY old instance. Literally from when Signal had SMS and MMS support. Looking at the above.....who knows, that might be the issue. The few messages I might have sent with it using SMS and MMS support....

EDIT: disregard.

I should look at the DB

[bepaald]

All those Inserting into [sm]ms errors are because you are trying to import into such an old Android backup. I have just pushed a fix for this, but you'll still see a bunch of warnings. Any reason you're not importing into a current backup?

terminate called after throwing an instance of 'std::bad_any_cast'

Kind of surprised by this one. I thought I had fixed the last of those, or at least made it print more information (was this the full output or did any other lines precede it? I would expect something like reported in #91) (And absolutely sure it was with a current build?)

If this was the full output, you could try running with --verbose so hopefully I can tell around what part of the code this bad_any_cast happens (it will produce a lot of output, but I'd only need the last, say, 20 lines).

It's worth mentioning that I have a VERY old instance. Literally from when Signal had SMS and MMS support. Looking at the above.....who knows, that might be the issue. The few messages I might have sent with it using SMS and MMS support....

That will certainly be the source of some problems, I do not have such old databases to test, but I do know that older database did things differently. For example, I'm matching recipients in the two databases by their UUID, but older messages in the Desktop database only identifies them by phone number (even if the database is updated and UUID's are present). I'm slowly trying to work through these sorts of compatibility issues as they are reported to me. But I'm sure I'm not there yet.

[michaelsmoody]

All those Inserting into [sm]ms errors are because you are trying to import into such an old Android backup. I have just pushed a fix for this, but you'll still see a bunch of warnings. Any reason you're not importing into a current backup?

Attempting a backup that was "known good" from a phone that I had a "known good backup" on. I lost a Pixel 5a 5G to a motherboard failure. This is a backup I had from before that. I have that same phone (a Pixel 3 XL) with that same Signal instance (disconnected/migrated), and this backup was in Google Drive.

I therefore downloaded it. I took another "backup" with a fresh Signal install, but honestly, I don't know if that works. I'll be attempting it, but TBH, it could make things go....very badly. Who knows ¯_(ツ)_/¯

So basically, this is a totally "known good" backup, even if it is older, and the Desktop instance is from the same "linked phone", just with newer messages, that I'm hoping not to lose.... (hence....combine)

So basically, even if the "newer" backup file works, it contains the exact same messages (the Signal instance was migrated to a new phone at the exact same time), but Signal was just "updated", and Signal then apparently still ran an automated backup in the background and created a new backup file. I honestly don't know what that means for the backup file, or if it "borks" the database.....

terminate called after throwing an instance of 'std::bad_any_cast'

Kind of surprised by this one. I thought I had fixed the last of those, or at least made it print more information (was this the full output or did any other lines precede it? I would expect something like reported in #91) (And absolutely sure it was with a current build?)

Yes, completely sure.

If this was the full output, you could try running with --verbose so hopefully I can tell around what part of the code this bad_any_cast happens (it will produce a lot of output, but I'd only need the last, say, 20 lines).

I can do that. From the "yesterday" build? Because the "fixed" build is producing tens of thousands of lines of output.

It's worth mentioning that I have a VERY old instance. Literally from when Signal had SMS and MMS support. Looking at the above.....who knows, that might be the issue. The few messages I might have sent with it using SMS and MMS support....

That will certainly be the source of some problems, I do not have such old databases to test, but I do know that older database did things differently. For example, I'm matching recipients in the two databases by their UUID, but older messages in the Desktop database only identifies them by phone number (even if the database is updated and UUID's are present). I'm slowly trying to work through these sorts of compatibility issues as they are reported to me. But I'm sure I'm not there yet.

Yeah. The "fixed" version ..... miiiiiiight ..... be worse. TENS of THOUSANDS of lines of:

Warning: Table 'sms' does not contain any column 'server_guid'. Removing
Warning: Table 'sms' does not contain any column 'server_guid'. Removing
Warning: Table 'sms' does not contain any column 'server_guid'. Removing
Warning: Table 'mms' does not contain any column 'quote_type'. Removing
Warning: Table 'sms' does not contain any column 'server_guid'. Removing
Warning: Table 'sms' does not contain any column 'server_guid'. Removing
Warning: Table 'sms' does not contain any column 'server_guid'. Removing
Warning: Table 'mms' does not contain any column 'quote_type'. Removing
Warning: Table 'sms' does not contain any column 'server_guid'. Removing
Warning: Table 'mms' does not contain any column 'quote_type'. Removing
Warning: Table 'sms' does not contain any column 'server_guid'. Removing
Warning: Table 'sms' does not contain any column 'server_guid'. Removing
Warning: Table 'sms' does not contain any column 'server_guid'. Removing
Warning: Table 'sms' does not contain any column 'server_guid'. Removing
Warning: Table 'sms' does not contain any column 'server_guid'. Removing
Warning: Table 'sms' does not contain any column 'server_guid'. Removing
Warning: Table 'sms' does not contain any column 'server_guid'. Removing
Warning: Table 'sms' does not contain any column 'server_guid'. Removing
Warning: Table 'sms' does not contain any column 'server_guid'. Removing
Warning: Table 'mms' does not contain any column 'quote_type'. Removing

I'm going to try that possibly borked "newer" backup file. Who knows what happens.

[michaelsmoody]

Trying the newer one....all sorts of output:

Warning: Table 'mms' does not contain any column 'quote_type'. Removing
Inserting 1 message reactions.
Warning: Table 'mms' does not contain any column 'quote_type'. Removing
Warning: Table 'mms' does not contain any column 'quote_type'. Removing
Warning: Table 'mms' does not contain any column 'quote_type'. Removing
Warning: Unsupported messagetype 'keychange'. Skipping message.
Warning: Unsupported messagetype 'keychange'. Skipping message.
Warning: Table 'mms' does not contain any column 'quote_type'. Removing
Warning: Table 'mms' does not contain any column 'quote_type'. Removing
Trying to match conversation (76/79) (type: private)
Failed to finding recipient for uuid: 0b7exxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx
Warning: Chat partner was not found in recipient-table. Creating is not (yet?) supported. Skipping. (id: 0b7e2c8e-5870-45b6-badb-292d7bf39ead)
Trying to match conversation (77/79) (type: private)
Failed to finding recipient for uuid: 98c4xxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx
Warning: Chat partner was not found in recipient-table. Creating is not (yet?) supported. Skipping. (id: 98c4e25c-dc48-4e4f-bc28-11c90f6e3299)
Trying to match conversation (78/79) (type: private)
Failed to finding recipient for uuid: 0814xxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx
Warning: Chat partner was not found in recipient-table. Creating is not (yet?) supported. Skipping. (id: 081456f5-5dd8-4666-bdc8-f8b00346004c)
Trying to match conversation (79/79) (type: group)
Failed to finding recipient for uuid: __signal_group__v2__!c177xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Warning: Chat partner was not found in recipient-table. Creating is not (yet?) supported. Skipping. (id: __signal_group__v2__!c{REDACTED})
reorderMmsSmsIds

Warning: Unsupported messagetype 'group-v2-change'. Skipping.
Warning: Table 'mms' does not contain any column 'quote_type'. Removing
Warning: Table 'mms' does not contain any column 'quote_type'. Removing
Warning: Table 'mms' does not contain any column 'quote_type'. Removing
Warning: Table 'mms' does not contain any column 'quote_type'. Removing
Warning: Table 'mms' does not contain any column 'quote_type'. Removing
Warning: Table 'mms' does not contain any column 'quote_type'. Removing
Warning: Unsupported messagetype 'keychange'. Skipping message.

Warning: Unsupported messagetype 'profile-change'. Skipping message.

{"id":{REDACTED MESSAGED ID},"author":"+{REDACTED MY NUMBER}","authorUuid":"{REDACTED UUID}","text":"{REDACTED}, basically","attachments":[],"bodyRanges":[]}
Warning: Table 'mms' does not contain any column 'quote_type'. Removing
Failed to finding recipient for uuid: 8D6Axxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx
Warning: Failed to find quote author. skipping
Additional info:
json_extract(json, '$.quote')

Warning: Unsupported messagetype 'profile-change'. Skipping message.
Warning: Unsupported messagetype 'group-v1-migration'. Skipping message.
Warning: Unsupported messagetype 'group-v2-change'. Skipping.
Warning: Unsupported messagetype 'delivery-issue'. Skipping message.
Warning: Unsupported messagetype 'delivery-issue'. Skipping message.
Warning: Unsupported messagetype 'delivery-issue'. Skipping message.
Warning: Unsupported messagetype 'delivery-issue'. Skipping message.
Warning: Unsupported messagetype 'delivery-issue'. Skipping message.

Error : Asked to find recipient._id for empty uuid. Refusing
Error: Failed to set address of incoming group message. Skipping
Some more info:
 === Row 1/1 ===

Error : Asked to find recipient._id for empty uuid. Refusing
Error: Failed to set address of incoming group message. Skipping
Some more info:
 === Row 1/1 ===

[michaelsmoody]

{"id":{REDACTED MESSAGED ID},"author":"+{REDACTED MY NUMBER}","authorUuid":"{REDACTED UUID}","text":"{REDACTED}, basically","attachments":[],"bodyRanges":[]}
Warning: Table 'mms' does not contain any column 'quote_type'. Removing
Failed to finding recipient for uuid: 8D6Axxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx
Warning: Failed to find quote author. skipping
Additional info:
json_extract(json, '$.quote')

Checking, the 8D6AXX seems to be myself. recipient._id matches uuid to my own phone number. I suspect these might be "self" or "note" messages? Or perhaps quotes?

I'm trying to track this one down in the DB dump I did.