[ Signal 6.13.6 ] Doesn't export database to XML

[ghost]

Dear @bepaald,

Exporting my chats to xml worked in the past, but with the latest signalbackup-tools (source-build), the xml export is now empty using a Signal 6.13.6 backup.

I think that the latest Signal changed the table 'smses' to 'message' in the database backups. Could you verify that?

[d33psky]

I was looking into the same thing. But I see no table 'smses' to 'message' in the database backups :

sqlite> .tables
avatar_picker                         msl_recipient                       
call                                  notification_profile                
cds                                   notification_profile_allowed_members
chat_colors                           notification_profile_schedule       
distribution_list                     one_time_prekeys                    
distribution_list_member              part                                
donation_receipt                      payments                            
drafts                                pending_pni_signature_message       
emoji_search                          pending_retry_receipts              
group_call_ring                       push                                
group_membership                      reaction
group_receipts                        recipient
groups                                remapped_recipients
identities                            remapped_threads
mention                               remote_megaphone
message                               sender_key_shared
message_fts                           sender_keys
message_fts_config                    sessions
message_fts_data                      signed_prekeys
message_fts_docsize                   sticker
message_fts_idx                       storage_key
msl_message                           story_sends
msl_payload                           thread

and nothing in message table either : 

sqlite> .schema message
CREATE TABLE IF NOT EXISTS "message" (
  _id INTEGER PRIMARY KEY AUTOINCREMENT,
  date_sent INTEGER NOT NULL,
  date_received INTEGER NOT NULL,
  date_server INTEGER DEFAULT -1,
  thread_id INTEGER NOT NULL REFERENCES thread (_id) ON DELETE CASCADE,
  recipient_id INTEGER NOT NULL REFERENCES recipient (_id) ON DELETE CASCADE,
  recipient_device_id INTEGER,
  type INTEGER NOT NULL,
  body TEXT,
  read INTEGER DEFAULT 0,
  ct_l TEXT,
  exp INTEGER,
  m_type INTEGER,
  m_size INTEGER,
  st INTEGER,
  tr_id TEXT,
  subscription_id INTEGER DEFAULT -1,
  receipt_timestamp INTEGER DEFAULT -1,
  delivery_receipt_count INTEGER DEFAULT 0,
  read_receipt_count INTEGER DEFAULT 0,
  viewed_receipt_count INTEGER DEFAULT 0,
  mismatched_identities TEXT DEFAULT NULL,
  network_failures TEXT DEFAULT NULL,
  expires_in INTEGER DEFAULT 0,
  expire_started INTEGER DEFAULT 0,
  notified INTEGER DEFAULT 0,
  quote_id INTEGER DEFAULT 0,
  quote_author INTEGER DEFAULT 0,
  quote_body TEXT DEFAULT NULL,
  quote_missing INTEGER DEFAULT 0,
  quote_mentions BLOB DEFAULT NULL,
  quote_type INTEGER DEFAULT 0,
  shared_contacts TEXT DEFAULT NULL,
  unidentified INTEGER DEFAULT 0,
  link_previews TEXT DEFAULT NULL,
  view_once INTEGER DEFAULT 0,
  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,
  server_guid TEXT DEFAULT NULL,
  message_ranges BLOB DEFAULT NULL,
  story_type INTEGER DEFAULT 0,
  parent_story_id INTEGER DEFAULT 0,
  export_state BLOB DEFAULT NULL,
  exported INTEGER DEFAULT 0
, scheduled_date INTEGER DEFAULT -1);

(created with --output RAWBACKUP/) as the XML export no longer works which is mentioned on the homepage, but also the exportcsv for table mms no longer works, that errors out with :

Error : During sqlite3_prepare_v2(): no such table: mms
  Query: "SELECT * FROM mms"

This is with signalbackup-tools_win.exe (under Wine) source version 20230307.170355 from the downloads page

[bepaald]

@Fortress64SB

Exporting my chats to xml worked in the past, but with the latest signalbackup-tools (source-build), the xml export is now empty using a Signal 6.13.6 backup. I think that the latest Signal changed the table 'smses' to 'message' in the database backups. Could you verify that?

Yes, as mentioned in the README, the XML export used to only export the sms table, which is now removed. The code for exporting the mms table has actually been there for a long time (hidden behind --includemms), but it is untested I think? Maybe you want to try it out? Be aware, that since this is now exporting the former mms table, this includes attachments. As per the XML spec, they are encoded base64, so the output file may become very big (it will be bigger than the input backup file).

I'm not sure how exactly to deal with the new situation in the Android backup, now that sms has been removed. It doesn't really make sense to save every message as mms in the xml file (even simple, 1-on-1 text message with no media), but all the columns that made sms different from mms have been removed, so I can only add them and leave them blank...

Anyway, let me know how ./signalbackup-tools [input] [passphrase] --exportxml [filename] --includemms works for you. (But please do update as I did just make some changes.)

@d33psky

I was looking into the same thing. But I see no table 'smses' to 'message' in the database backups : [...]

Not sure what you mean, the message table is right there. If you want to see its contents, do SELECT * FROM message;.

but also the exportcsv for table mms no longer works, that errors out with :

As mentioned, the mms table was removed and replaced with the message table. If you run --exportcsv message=output.csv you will get all your messages. But I should indeed update the README DONE.

This is with signalbackup-tools_win.exe (under Wine) source version 20230307.170355 from the downloads page

Fine by me of course, but just curious: why are you running the Windows version under Wine? What system are you using, are you having trouble compiling?

[d33psky]

I was looking into the same thing. But I see no table 'smses' to 'message' in the database backups : Not sure what you mean, the message table is right there. If you want to see its contents, do SELECT * FROM message;.

Sorry for the confusion, I thought that Fortress64SB was looking for a field named something like 'smses' in the 'message' table.

but also the exportcsv for table mms no longer works, that errors out with : As mentioned, the mms table was removed and replaced with the message table. If you run --exportcsv message=output.csv you will get all your messages.

Ah of course ! I should have thought of that. Thanks !

~But I should indeed update the README~ DONE.

Thanks for that too :)

This is with signalbackup-tools_win.exe (under Wine) source version 20230307.170355 from the downloads page Fine by me of course, but just curious: why are you running the Windows version under Wine? What system are you using, are you having trouble compiling?

I'm using Ubuntu 20.04 and was just lazy in that I did not chose to compile things.

Thanks for working on signalbackup-tools !

[ghost]

Thank you, I've tested both you methods using the latest build:

signalbackup-tools signal-2023-03-10-17-01-31.backup 000000000000000000000000000000 --exportcsv message=output.csv signalbackup-tools signal-2023-03-10-17-01-31.backup 000000000000000000000000000000 --exportxml mel.xml --setselfid --includemms

Both have their benefits while exporting, but I have to find a way to archive it like the older XML exports. Maybe I can combine all the XML exports of the last couple of years to better readable HTML export per user. Unfortunately, I don't have the old database backups anymore.

[bepaald]

Both have their benefits while exporting, but I have to find a way to archive it like the older XML exports. Maybe I can combine all the XML exports of the last couple of years to better readable HTML export per user. Unfortunately, I don't have the old database backups anymore.

Could you explain what it is you need from this XML output? I think (assuming there are no bugs), the current way (with --includemms) is better than the 'older XML exports', which left out a lot of messages (messages with attachments and group messages). The generated XML should be valid and thus perfectly machine-readable. If you are looking for human-readable, would it help if I added an option to exclude the binary attachment data (not the actual mms, just the non-text attachment data)? Cause I don't think that would be too difficult to implement.

If your goal was to convert the XML to 'readable HTML' output, have you tried the exporthtml option? Maybe that works for you.

[ghost]

I already glad that you can provide this tool for exporting the chats from Signal. Yes, the exporthtml option works fine on the current Signal database and current signalbackup-tools. It's more readable than any other option and the best way to archive chats.

Since I don't have the old Signal databases anymore, I'm fine with the older "single-line-per-chat" xml outputs I have in my archive.

I always recommend not to keep the Signal databases for the long before removing your account from their servers and starting from scratch with the app. Due to the many changes in the database, importing older versions into Signal can cause app crashes if the database is too large. And if you unlucky, you might be banned for 24h from their servers because the app crashes while starting/registering.

[bepaald]

Thanks. Can I interpret this issue as solved than?

(I will in the near future make --includemms default for the XML export and update the README, it is pretty useless without it anyway)

You can let me know, or just close the issue yourself. But, of course, also let me know if there is anything more I can do.

Thanks again!

[ghost]

Yes, I consider it as resolved. Thank you very much for your help and developing this tool.