tbvdm / sigtop Goto Github PK

Getting this with signal-desktop 6.30.1-1

sigtop: cannot prepare statement: no such column: uuid

Hi,
i got your repository over reddit. I just built it and it expects some kind of signal directory. Could you include a link in the README, which directory is meant with that?

here is a sample entry in my messages.txt generated using
./sigtop export-messages messages.txt:

Conversation: Gunjan
Type: outgoing
To: Gunjan
Sent: Sat, 9 Apr 2022 22:12:15 -0400
Attachment: image.png (image/png, 140855 bytes)

how do I figure out which image this image.png is referring to? the image.png in the attachments directory is not the one that I sent in this message. But the attachments directory has many files of the form image-n.png for n 1,2,3...50 and I guess the correct image is one of them.
Is there a way to have the messages export precisely specify which image/document in attachments is being referred to?

Hi! Your app saved me so much troubles, thank you!
For snap-based installation on Ubuntu, the storage path will be different. But a simple
ln -s /home/$USER/snap/signal-desktop/current/.config/Signal Signal
in the terminal does the trick :)
Maybe this could be included in README.md.
Thank you again for this super convenient tool!
Best wishes

Hi,
Thanks for a great tool.
Just one question. I see no way to connect the extracted messages to their corresponding attachments? Is that the case?

One other question would it be possible to select the conversations we want to extract?

Thanks for a great tool!
Bob

Hi,
First of all, congrats for your tool which is very efficient to save our Signal history.

I have an issue with the incremental option (-i).
Whereas 'sigtop export-attachments -i attachments' works perfectly, 'sigtop export-messages -i messages' doesn't work:
sigtop: -i: invalid option
And yet it seems to be the correct syntax, according to your documentation.

How should I do?

Hi!

Thank you for sigtop, it's working great!

I've hacked up a shell script to implement an incremental backup, run by crontab.

Once finalized, I could post it here. But are you planning on extending sigtop to provide means to do incremental backups? [FeatureRequest] ;)

Cheers!

I'm trying to decrypt the database of a Signal fork (Session: https://github.com/oxen-io/session-desktop), unfortunately I'm met with this error:

sigtop: database version 11 not supported (yet)

Any idea what I could do about this?
Which database version is Signal currently using?

Edit: Never mind, commenting out these lines works for supporting Session:

Unfortunately it doesn't work with attachments, since Session is encrypting those too while Signal does not.

$ ~/go/bin/sigtop export-attachments -d /db/Session/ /db/Session/attachments
sigtop: cannot prepare statement: no such column: name

In order for sigtop to be packaged by distributions, it would be nice if you could tag a release once in a while. Adding a changelog would be extra nice.

Hi there,

Your module is awesome I love it! I could export in an easy way precious data for me!

I just had a suggestion for improvement, to export the messages without some of the fields:
"Type"
"Sent"
"Received"

And maybe add extra spacing after a message and the "from", like 2 lines of space so that we can properly read the data, even in a text file.

Thank you so much for your work!

Good afternoon, are there plans to add a function for selecting chats to export? That is, there are many chats on the account, but you need to download 2-3.

I have been using your module and it has helped me a lot with my Machine Learning projects. I have noted that the attachments in the messages look like this: Attachment: no filename (audio/aac, 1248473 bytes) but in the files they may have a name related with its date or just something like 'image-XX'

It makes the mapping between attachments and messages really difficult and I wonder if there's any reason why attachments cannot be more easily mapped?

Thank you for your work!

Not sure if you're aware of this but the binary is being detected as a virus (Wacatac.B!ml)

If it's a false-positive, carry on and thanks.

When running sigtop att attachments, sigtop runs mostly fine. But when I run sigtop att -M attachments-M it throws a ton of "sigtop.exe: utimes: unsupported flag" errors. The attachments still export the same, but the modified timestamps aren't changed and those errors are thrown.

Non-technical user here.

The app crashes a split second after opening. I have made a windows security exception (not that it was triggered in the first place) and still, it crashes.

This is my first time installing anything needing Go (Which is go1.19.8 linux/amd64).

After installing sigtop with GOBIN=~/sigtop go install github.com/tbvdm/sigtop@master ...
and opening the directory where the binary is, and trying "sigtop check-database" I get "Bash Command not found".

Probably rookie mistake, but maybe someone could point me in the right direction?

Similar to the way snap is supported, it would maybe help some users to add support for flatpak installations: ~/.var/app/org.signal.Signal/config/Signal

Hi, is there any way to export a group chat with an emoji in the title? None of the things I've tried have worked.

What does this output mean? After running the command below, there are text files written to ~/Signal-Export/messages

I just want to make sure that it's not skipping anything.

Command: ./sigtop export-messages ~/Signal-Export/messages

Output:

sigtop: invalid mention
sigtop: open Unknown (group).txt: file exists

On Linux Mint 20.2

Both
go install github.com/tbvdm/sigtop@master
and
GOBIN=~/bin go install github.com/tbvdm/sigtop@master

give the error:
can't load package: package github.com/tbvdm/sigtop@master: cannot use path@version syntax in GOPATH mode

How to proceed?

Hi,
I pulled the most reccent github work and see that many things have changed!

I tried to follow the instructions on Xubuntu 18.04 and got :

`$ sudo apt-get install golang gcc
[sudo] password for bob:

Reading package lists...
gcc is already the newest version (4:7.4.0-1ubuntu2.3).
The following additional packages will be installed:
golang-1.10 golang-1.10-doc golang-1.10-go golang-1.10-race-detector-runtime
golang-1.10-src golang-doc golang-go golang-race-detector-runtime golang-src
Suggested packages:
bzr mercurial subversion
The following NEW packages will be installed:
golang golang-1.10 golang-1.10-doc golang-1.10-go
golang-1.10-race-detector-runtime golang-1.10-src golang-doc golang-go
golang-race-detector-runtime golang-src
0 upgraded, 10 newly installed, 0 to remove and 1 not upgraded.
Need to get 42.7 MB of archives.
After this operation, 229 MB of additional disk space will be used.
Do you want to continue? [Y/n]
...`

Worked fine, but then:
$ sudo go install github.com/tbvdm/sigtop@master can't load package: package github.com/tbvdm/sigtop@master: cannot find package "github.com/tbvdm/sigtop@master" in any of: /usr/lib/go-1.10/src/github.com/tbvdm/sigtop@master (from $GOROOT) /home/bob/go/src/github.com/tbvdm/sigtop@master (from $GOPATH)

I am sure that there is some detail I am not grasping...

Cheers,
Bob

Hi, just wanted to say thanks for this tool, it's so much simpler than others I've looked at.

I might be missing something really simple here but I'm trying to access the exported db file using DB Browser for SQLite and I am getting this error:

Reason: malformed database schema (messages_on_insert_insert_mentions) - near ">>": syntax error

I made sure Signal was not running when I exported the DB. I am on MacBook Pro, Apple M1 Pro, Ventura 13.4. Here are the outputs for what you mentioned in #26:

sigtop db signal.db (returns nothing, file is created)

sigtop check (returns nothing)

sigtop query 'pragma user_version'
87

sigtop query 'select name, type from sqlite_schema where tbl_name = "messages"'
messages|table
sqlite_autoindex_messages_1|index
messages_id|index
messages_receipt|index
messages_schemaVersion|index
messages_view_once|index
messages_sourceUuid|index
messages_searchOrder|index
messages_on_view_once_update|trigger
messages_unexpectedly_missing_expiration_start_timestamp|index
messages_hasAttachments|index
messages_hasFileAttachments|index
messages_on_insert|trigger
messages_on_update|trigger
messages_on_delete|trigger
messages_conversation|index
messages_unread|index
messages_conversation_no_story_id|index
messages_unread_no_story_id|index
messages_unseen_no_story|index
messages_unseen_with_story|index
expiring_message_by_conversation_and_received_at|index
messages_by_distribution_list|index
messages_by_storyId|index
messages_call|index
messages_expires_at|index
messages_hasVisualMediaAttachments|index
messages_preview|index
messages_preview_without_story|index
messages_activity|index
message_user_initiated|index
messages_unread_mentions|index
messages_unread_mentions_no_story_id|index
messages_on_insert_insert_mentions|trigger
messages_on_update_update_mentions|trigger
messages_story_replies|index

sqlite3 signal.db 'pragma integrity_check'
ok

sqlite3 signal.db 'select name, type from sqlite_schema where tbl_name = "messages"'
messages|table
sqlite_autoindex_messages_1|index
messages_id|index
messages_receipt|index
messages_schemaVersion|index
messages_view_once|index
messages_sourceUuid|index
messages_searchOrder|index
messages_unexpectedly_missing_expiration_start_timestamp|index
messages_hasAttachments|index
messages_hasFileAttachments|index
messages_conversation|index
messages_unread|index
messages_conversation_no_story_id|index
messages_unread_no_story_id|index
messages_unseen_no_story|index
messages_unseen_with_story|index
expiring_message_by_conversation_and_received_at|index
messages_by_distribution_list|index
messages_by_storyId|index
messages_call|index
messages_expires_at|index
messages_hasVisualMediaAttachments|index
messages_preview|index
messages_preview_without_story|index
messages_activity|index
message_user_initiated|index
messages_unread_mentions|index
messages_unread_mentions_no_story_id|index
messages_story_replies|index
messages_on_view_once_update|trigger
messages_on_insert|trigger
messages_on_update|trigger
messages_on_delete|trigger
messages_on_insert_insert_mentions|trigger
messages_on_update_update_mentions|trigger

sqlite3 'select count(*) from messages' (starts sqlite3 command line utility but does nothing else)

sqlite3 -version
3.39.5 2022-10-14 20:58:05 554764a6e721fab307c63a4f98cd958c8428a5d9d8edfde951858d6fd02daapl

DB Browser for SQLite (version from GUI)
Version 3.12.2

Everything you mentioned seems to come back clean but the SQLite browser still won't open it for some reason. Would you happen to have any ideas?

Can I copy attachments.noindex to desktop and run sigtop on it? Are there any caveats to doing so, meaning does sigtop require access to any of the SQL databases or other Signal files that it expects to be somewhere specifically relative to the target directory?

Please advise

sigtop msg -s 2023-04-25T00:00:00 newmsg

Dont generate any files

Tnks

I'm looking into merging two backups of the ~Library/Application Support/Signal folder into one. I didn't find a way to do it after messing with some importing/exporting in the two SQLite files. I'm able to successfully join the tables in the database, but it seems as there is some check hindering me to merge the message histories.

So my thinking now is to use a tool like this to export the data, and then maybe there is a simple way to import the backup back into the Desktop app.

I stumbled upon sigtop because I want to save some conversation data have after an unfortunate data loss. Thanks for your work.

On MacOS, installed with:

brew install --HEAD tbvdm/tap/sigtop

Error:

% sigtop export-messages messages.txt
sigtop: Conversation recipient has null id
sigtop: Cannot parse quote JSON data

Sigtop for Signal Beta after the update on 08.22.2023 started giving an error - "sigtop.exe: cannot prepare SQL statement: no such column: uuid"

Thanks @tbvdm for this work! I'd like to report a bug.

I get File exists error when running export messages command. Directory is empty before I run this command. It seems an empty group is causing the bug. Perhaps application can create a new file when an expected file has already exists.

$ sigtop msg messsages
sigtop:   (group).txt: File exists
sigtop: Invalid mention

Contents of the file:

$ cat messages/\ \ \(group\).txt
Conversation:   (group)
Type: group-v2-change
Sent: Sat, 9 Jul 2022 14:13:24 +0200
Received: Thu, 28 Apr 2022 17:53:33 +0200

I've removed empty groups from the application. This time application failed with generic error.

$ sigtop msg asd
sigtop: Invalid mention

MacOS Monterey 12.4 (21F79)
Signal Desktop 5.49.0

I'm wondering if you looked into rearchiving the messages and attachments into a binary file readable by the signal android or ios apps - this way you would be able to reload the messages on mobile if you loose your previous mobile app but retain access to a desktop app?

I'd imagine existing signal open source code for archiving could be used, but there might be some complex wiring necessary.

Hello,

first of all thank you very much for the great program, it is multiple orders of magnitude faster than a python script with the same functionality I have been using so far. Could you please implement a timestamp option so that only messages and/or attachments younger than a given timestamp (either epoch or optionally human-readable) are exported? This would make cumulative backups much less cumbersome.

Related, but not as important (at least for me):

1. Could you please make the filename resolution logic configurable when exporting attachments, so that it is also possible to either overwrite existing files or only export attachments which are not already present in the filesystem (or let the user choose interactively, as is standard for cp)? The current behaviour of exporting everything again, possibly with some digit appended to the filename, is not very useful in my opinion.

2. Maybe it would be a good idea to optionally offer a more meaningful naming logic for exported attachments, for example by prepending the message timestamp formatted in a human-readable format (YYYY-MM-DD_HH_MM_SS). But this job is probably easier done in some post-processing script using python, Perl and the likes (of course sacrificing some speed).

I'm using export-attachments and recently, perhaps due to newer signal-desktop version? (i'm currently on 6.8.0), i'm getting multiple files for every attachment. If I start with an empty destination directory, it looks like the 2nd run of sigtop export-attachments destdir/ produces a new file with -2.ext suffix for every existing file.

I'm getting a couple of sigtop: Missing attachment path when exporting attachments. I tried to get hold of exactly which message it was, adding the below code. But in the output group at that time there is no message as all, let alone any attachment... Maybe my code is faulty. It would be nice to see exactly where the missing attachment path occurs. But also perhaps you know for a fact that there really isn't anything of importance missing? Or at least not anything which can be dug out. And the warning message could thus be ignored?

diff --git a/cmd-export-attachments.c b/cmd-export-attachments.c
index 6cb913e..ab5dfa2 100644
--- a/cmd-export-attachments.c
+++ b/cmd-export-attachments.c
@@ -222,6 +222,14 @@ export_attachment_list(struct sbk_ctx *ctx, struct sbk_attachment_list *lst,

        TAILQ_FOREACH(att, lst, entries) {
                if ((src = sbk_get_attachment_path(ctx, att)) == NULL) {
+                       struct tm *tm;
+                       time_t tt = att->time_sent / 1000;
+                       if ((tm = localtime(&tt)) == NULL) {
+                               warnx("localtime() failed");
+                       } else {
+                               printf("%d-%02d-%02d %02d:%02d:%02d %d\n", tm->tm_year + 1900, tm->tm_mon, tm->tm_mday,
+                                      tm->tm_hour, tm->tm_min, tm->tm_sec, att->size);
+                       }
                        ret = -1;
                        continue;
                }
@@ -317,6 +325,14 @@ export_conversation_attachments(struct sbk_ctx *ctx,
                goto out;

        ret = export_attachment_list(ctx, lst, cnv_dfd, mode);
+       if (ret < 0) {
+               if (cnv->recipient->group != NULL) {
+                       printf("convo group: %s\n", cnv->recipient->group->name);
+               }
+               if (cnv->recipient->contact != NULL) {
+                       printf("convo contact: %s\n", cnv->recipient->contact->name);
+               }
+       }
        close(cnv_dfd);

 out:

Hi again :)

I tried to export the Signal Desktop database in an SQL file, but I actually have a binary file, impossible to treat.

I used sigtop export-database signal.sql

Let me know if I did something wrong

this is not ideal:

as any invalid UTF-8 justs get swapped for the unicode replacement character:

runes := []rune("\xA0\xA1") // [fffd fffd]

and since that character is not a control character, it would just wind up in the resulting file name as is.

Hello, love that this tool just works and the provided windows binaries!

One thing that is missing is HTML export so my non-tech friends can browse their chat history without parsing json or plain text, wondering what all the codes are for the emojis.

The implementation at https://github.com/bepaald/signalbackup-tools?tab=readme-ov-file#export-to-html seems to be nice output, hence I hope you can use it for sigtop.

Since sigtop does msg and att separately, is the way to do both with -i even though we're only doing it once (so far)?

The goal is to have the HTML output be more complete with images, a/v and other attachments linked inline.

Thanks!

Hello,

I have successfully exported several Signal Desktop instances, but encountered now the following issue with one of them:

> ~/go/bin/sigtop export-messages -f text messages
panic: runtime error: slice bounds out of range [2:1]

goroutine 1 [running]:
github.com/tbvdm/sigtop/signal.(*MessageBody).insertMentions(0xc000117ac0)
	/home/user/go/pkg/mod/github.com/tbvdm/[email protected]/signal/mention.go:100 +0x265
github.com/tbvdm/sigtop/signal.(*Context).messages(0xc000064040, 0x5ee2eb?)
	/home/user/go/pkg/mod/github.com/tbvdm/[email protected]/signal/message.go:265 +0x356
github.com/tbvdm/sigtop/signal.(*Context).allConversationMessages(0xc000064040, 0xc000117d40)
	/home/user/go/pkg/mod/github.com/tbvdm/[email protected]/signal/message.go:139 +0xb5
github.com/tbvdm/sigtop/signal.(*Context).ConversationMessages(0xc0000292e0?, 0xc000258000?, {{0x0, 0x0, 0x0}, {0x0, 0x0, 0x0}})
	/home/user/go/pkg/mod/github.com/tbvdm/[email protected]/signal/message.go:111 +0xd1
main.exportConversationMessages(0x50?, 0xc0000200c0?, 0x29?, 0x1, {{0x0, 0x0, 0x0}, {0x0, 0x0, 0x0}})
	/home/user/go/pkg/mod/github.com/tbvdm/[email protected]/cmd_export_messages.go:161 +0x96
main.exportMessages(0x453f1c?, {0x7fff6e2e05c1?, 0x1a?}, 0xc00004fe30?, {{0x0, 0x0, 0x0}, {0x0, 0x0, 0x0}})
	/home/user/go/pkg/mod/github.com/tbvdm/[email protected]/cmd_export_messages.go:151 +0x22f
main.cmdExportMessages({0xc0000120c0?, 0xc00003c710?, 0x4418c5?})
	/home/user/go/pkg/mod/github.com/tbvdm/[email protected]/cmd_export_messages.go:128 +0x6d4
main.main()
	/home/user/go/pkg/mod/github.com/tbvdm/[email protected]/main.go:62 +0x163

Similar with a try to do the export year by year for 2021:

> ~/go/bin/sigtop export-messages -f text -s 2018-01-01T00:00:00,2018-12-31T23:59:59 messages-2018
> ~/go/bin/sigtop export-messages -f text -s 2019-01-01T00:00:00,2019-12-31T23:59:59 messages-2019
> ~/go/bin/sigtop export-messages -f text -s 2020-01-01T00:00:00,2020-12-31T23:59:59 messages-2020
> ~/go/bin/sigtop export-messages -f text -s 2021-01-01T00:00:00,2021-12-31T23:59:59 messages-2021
panic: runtime error: slice bounds out of range [2:1]

goroutine 1 [running]:
github.com/tbvdm/sigtop/signal.(*MessageBody).insertMentions(0xc000115a40)
	/home/user/go/pkg/mod/github.com/tbvdm/[email protected]/signal/mention.go:100 +0x265
github.com/tbvdm/sigtop/signal.(*Context).messages(0xc000064040, 0x5ee552?)
	/home/user/go/pkg/mod/github.com/tbvdm/[email protected]/signal/message.go:265 +0x356
github.com/tbvdm/sigtop/signal.(*Context).conversationMessagesSentBetween(0xc000064040, 0xc000115d40, {0xc000115bb0?, 0x4a8b05?, 0x6f65e0?}, {0xc000115bc8?, 0x4fb446?, 0x6f65e0?})
	/home/user/go/pkg/mod/github.com/tbvdm/[email protected]/signal/message.go:218 +0x271
github.com/tbvdm/sigtop/signal.(*Context).ConversationMessages(0xc00014e3c0?, 0xc000126000?, {{0x0, 0xed7804ef0, 0x6f65e0}, {0x3b9ac9ff, 0xed961826f, 0x6f65e0}})
	/home/user/go/pkg/mod/github.com/tbvdm/[email protected]/signal/message.go:117 +0x19c
main.exportConversationMessages(0x50?, 0xc0000200c0?, 0x29?, 0x1, {{0x0, 0xed7804ef0, 0x6f65e0}, {0x3b9ac9ff, 0xed961826f, 0x6f65e0}})
	/home/user/go/pkg/mod/github.com/tbvdm/[email protected]/cmd_export_messages.go:161 +0x96
main.exportMessages(0x453f1c?, {0x7ffe8422e5bc?, 0x1a?}, 0xc00004fe30?, {{0x0, 0xed7804ef0, 0x6f65e0}, {0x3b9ac9ff, 0xed961826f, 0x6f65e0}})
	/home/user/go/pkg/mod/github.com/tbvdm/[email protected]/cmd_export_messages.go:151 +0x22f
main.cmdExportMessages({0xc000012090?, 0xc00003c710?, 0x4418c5?})
	/home/user/go/pkg/mod/github.com/tbvdm/[email protected]/cmd_export_messages.go:128 +0x6d4
main.main()
	/home/user/go/pkg/mod/github.com/tbvdm/[email protected]/main.go:62 +0x163

A database check returns no error:
> ~/go/bin/sigtop check

Is there a (hidden) verbose or debug mode to see whats going on?

Sigtop при єкспорті почав видавати помилки "cannot find mention recipient for UUID ""

sigtop msg -s 2023-07-10T00:00:00,2023-07-18T23:59:59 -d 'C:\Users\Admin\AppData\Roaming\Signal' -i -f json "C:\temp\Output"
sigtop msg -s 2023-07-10T00:00:00,2023-07-18T23:59:59 -d 'C:\Users\Admin\AppData\Roaming\Signal Beta' -i -f json "C:\temp\Output"
sigtop.exe: cannot find mention recipient for UUID ""
sigtop.exe: cannot find mention recipient for UUID ""
sigtop.exe: cannot find mention recipient for UUID ""
sigtop.exe: cannot find mention recipient for UUID ""
sigtop.exe: cannot find mention recipient for UUID ""
sigtop.exe: invalid mention (index: 1, body: 1348 2317, placeholders:, mentions: 0:230,8 1:126,20)
sigtop.exe: message with invalid mention (conversation: "", sent: 2023-07-16 16:04:07)
sigtop.exe: cannot find mention recipient for UUID ""
sigtop.exe: cannot find mention recipient for UUID ""
sigtop.exe: invalid mention (index: 1, body: 1348 2317, placeholders:, mentions: 0:230,8 1:126,20)
sigtop.exe: message with invalid mention in quote (conversation: "", sent: 2023-07-16 16:05:06)
sigtop.exe: cannot find mention recipient for UUID ""
sigtop.exe: cannot find mention recipient for UUID ""
sigtop.exe: cannot find mention recipient for UUID ""
sigtop.exe: cannot find mention recipient for UUID ""
sigtop.exe: cannot find mention recipient for UUID ""
sigtop.exe: cannot find mention recipient for UUID ""
sigtop.exe: cannot find mention recipient for UUID ""
sigtop.exe: invalid mention (index: 2, body: 74 132, placeholders: 0, mentions: 0:0,17 1:126,8 2:64,10 3:64,10)
sigtop.exe: message with invalid mention (conversation: "***", sent: 2023-07-17 20:39:04)
sigtop.exe: cannot find mention recipient for UUID ""
sigtop.exe: cannot find mention recipient for UUID ""
sigtop.exe: cannot find mention recipient for UUID ""
sigtop.exe: cannot find mention recipient for UUID ""
sigtop.exe: cannot find mention recipient for UUID ""
sigtop.exe: cannot find mention recipient for UUID ""
sigtop.exe: cannot find mention recipient for UUID ""
sigtop.exe: cannot find mention recipient for UUID ""
sigtop.exe: cannot find mention recipient for UUID ""

Problem, if there is \ or / in the name of the chat - the program cannot save messages.
"sigtop.exe: open Input\6 ШО\5ШО (group).json: The system cannot find the path specified."

I have a monthly backup task using sigtop which has been working fine, but sometime in the past month, they must have changed things. I now get the following error:

$ sigtop export-messages signal_backup
sigtop: Not enough tokens to parse JSON
sigtop: Not enough tokens to parse JSON
sigtop: Not enough tokens to parse JSON

On MacOS, installed with:

brew install --HEAD tbvdm/tap/sigtop

Error:
$ sigtop export-messages messages
sigtop: Cannot parse quote JSON data

$ sigtop export-attachments attachments
sigtop: Cannot parse quote JSON data

Messages and attachments are successfully exported for most threads and it seems like the error is related to one contact/thread.

[As discussed on https://github.com//issues/8, the latest version of sigtop still causes this error for me]

Hi, great project, thanks for making this.

It'd be great to have improved functionality on export-messages and export-attachments so that you can backup a conversation with only one friend. Is this something that's feasible?