history.sqlite reconstruct threads

free.rage

Hi everyone,

I just migrated my E4.5 to ubports. When I restored my backup, the messaging app was not working. When I opened it, the rotating circle was present and nothing happened event after 10 hours... I realized that my old history.sqlite was probably the problem. So I had to import manually my messages from my old history.sqlite to the new one (probably because the table structure is not the same : other table/columns are in the ubports database).
So it's ok for the messages themselves. But I can't see them in the message app, I need to send a message to someone in order to get the thread displayed (or at least that an event is created with someone).
I tried to copy manually my old threads table in the new database, but it doesn't work.
And this is my question : Does someone know how to reconstruct correctly the thread ?

Tks.

advocatux

@free.rage, I just did:

adb push from_my_backup_directory/.local/share/history-service/history.sqlite /home/phablet/.local/share/history-service/

and it's working fine. I have all the old messages and I send and receive without problem.

You need to substitute "from_my_backup_directory" for whatever you need, obviously.

free.rage

@advocatux, that's obviously what I did first (with rsync instead of adb). But it is not working. May be My app messaging was not in the latest version when I migrated my device ? But I had no notifications...
Well I don't know the reason why it was not working.

advocatux

@free.rage did you reboot the phone? Re-scan is a necessary step.

free.rage

@advocatux Yes, I noticed that the service history needs a reboot to take into account a new sqlite file.

advocatux

@free.rage

free.rage

Well, may be I was not clear from my previous message. I noticed that a reboot was necessary but my problem is still there. All my messages are in the file but not all threads are displayed.

doniks

@free.rage Just thinking out loud here: Can you see a difference in the database between the threads that you DO see in the app and the ones that you DONT see?

advocatux

@free.rage, oh sorry. I don't know what's happening with that file. Has you check out the file permissions?

BadAngel

Hi !
I just manage to migrate my history.sqlite file from my BQ aquarius 4.5 (with latest ubuntu touch updates) to my Nexus 5 (with a fresh ubport install).
The "old" database is in version 12, the new one is in version 17. So some modifications must be applied.
In my case my fresh install IS fresh (I do not have any previous message on it => I drop tables)

BEFORE: make a copy of the original/old history AND a copy of the fresh installed history: thoose are located in ~/.local/share/history-service/ on each phone. If you don't know / success to make a copy please do not go further.

WARNING: this process will destroy any message on the "new" phone.

I think its OK for the legal warnings

Let's suppose we are on the new phone and we have copied the "old" database on the new phone in ~/.local/share/history-service_old/ (including attachements of course ;).

First, let update the database structure of the old DB:

sqlite3 ~/.local/share/history-service_old/history.sqlite
sqlite> update schema_version set version=17;
sqlite> ALTER TABLE threads ADD COLUMN chatType tinyint;
sqlite> update threads set chatType=1;
sqlite> ALTER TABLE thread_participants ADD COLUMN alias varchar(255);
sqlite> ALTER TABLE thread_participants ADD COLUMN state tinyint;
sqlite> ALTER TABLE thread_participants ADD COLUMN roles tinyint;
sqlite> update thread_participants set state=0;
sqlite> update thread_participants set roles=0;
sqlite> ALTER TABLE text_events ADD COLUMN informationType integer;
sqlite> update text_events set informationType=0;
Exit with "Ctrl+D"

This last "update" will take some time depending on the number of messages you have ...

Now dump the data to an SQL file:

sqlite3 ~/.local/share/history-service_old/history.sqlite .dump > history.sqlite_old.sql

Now let's clean the current ("fresh") database:

sqlite3 ~/.local/share/history-service/history.sqlite
sqlite> drop table threads;
sqlite> drop table thread_participants;
sqlite> drop table voice_events;
sqlite> drop table text_event_attachments;
sqlite> drop table text_events;
Exit with "Ctrl+D"

Now let's insert thoose old data:

sqlite3 ~/.local/share/history-service/history.sqlite < history.sqlite_old.sql
mkdir -p ~/.local/share/history-service/attachments/
rsync -av ~/.local/share/history-service_old/attachments/ ~/.local/share/history-service/attachments/

At last:

sudo reboot

Hope this will help ... It worked for me

hans1977se

@BadAngel Nice! This kind of code should run when the history service starts actually, I think. Whenever the stored version is different from the current version, just copy the stored version to a backup file and convert it to the current version in a new file.

BadAngel

@hans1977se Thanks ! I expected this to be automated
Copying databases for the contacts worked, but for the SMS the message application starts but seems to perform the DB migration for some seconds (maybe 30 seconds) then displays a screen "send your first message".
I think it is due to some timeout for the DB migration. The statement "update text_events set informationType=0;" took 2 or 3 minutes to complete on my telephone (I have a big amount of SMS / MMS). Maybe the automated DB migration works when you just have a small amount of messages ...

hans1977se

@BadAngel Hopefully it will be automated. A lot of good things happens in this project all the time.

I have only migrated from the Canonical image to the stable ubports image, and there was no conversion needed for that. I think @Flohack might be on top of this and it would probably be very interesting if he has time to put down a few comments regarding this.

flohack

Hmm few comments: We did not change anything that I know that would make necessary these huge steps to get the db working again, I totally dont understand what happened here. Backup/Restore of my data worked when wiping several phones...

BR