Rebuild contacts and commhistory from contacts.db and commhistory.db

[pbhat]

I had to reset Jolla C to factory state.
Before reset I had saved contacts.db and commhistory.db under ~/Documents/Comm/ folder which I have retained on my linux PC. They can be opened with sqlite3. But the information appears to spread across many tables.

Is there anyway I can import restore my contacts and call-logs from there.

I am conversant with CLI and SQL.

Thanks in advance,

[ric9k]

Is it not working if you just copy them back to their original place?

I don’t know with your version of SFOS but with the 4.4:
If your username is defaultuser, the places are
/home/defaultuser/.local/share/commhistory/commhistory.db
and
/home/defaultuser/.local/share/system/privileged/Contacts/qtcontacts-sqlite/contacts.db

I’d do it when the user (nemo/defaultuser?) is not logged.
Either from recovery mode or as root through terminal while being logged as a secondary user that you can create in Settings > Uers.
This needs e.g. toeterm and su (instead of devel-su which isn’t enabled for secondary users).

Beware that these files rights have perhaps changed while being copied to the PC.
The owner must be defaultuser and the group has to be privileged.
E.g, change the rights with:

chown defaultuser:privileged /yout_path/your.file

Before to copy the files back, delete the contents of the directories if actuel databases contain no important data:

rm -f `/home/defaultuser/.local/share/system/privileged/Contacts/qtcontacts-sqlite/*
rm -f /home/defaultuser/.local/share/commhistory/*

Just ask if I forgot something.

[nephros]

At least for commhistory, it should be enogh to stop the commhistory daemon service, you can be logged in as your user.

You do need root to copy and chown though.

[nephros]

Another way that doesn’t involve copying would be

sqlite3 /path/to/old.db .dump | sqlite3 /path/to/new.db

[ithier]

For the contacts, I’ve tried to copy (not tried when nemo/defaultuser is not logged in). It worked but the contacts doesn’t appear in all applications and after a while everything disappear.
So to have them permanently, just after the copy if the contacts are there in the people application, I have done an export through Bluetooth to another device (a Windows machine for example). Then I rebooted my device to be sure that every services were OK and if all the contacts were gone you can import then from Bluetooth.

[nas]

If the contacts database is from SailfishOS 3.* then you will have problem if you just copy it to SailfishOS 4. Check The following post:

[4.0.1.45] Hundreds of contacts lost after update

In the 3.x.x database, synchronised constituent contacts had a specific syncTarget field (and most will also have an associated accountId although this is usually stored in out-of-band data rather than easily queryable). In 4.x.x we now support addressbooks as a top-level concept, so each contact is associated with a particular addressbook (collection). See https://git.sailfishos.org/mer-core/qtcontacts-sqlite/ for details. For the backup/restore code, see https://git.sailfishos.org/mer-core…

I wrote a conversion script in php that opens the .sqlite contacts database from SailfishOS 3 and generates a VCARD file. Since I didn’t lost all contacts, I also checked with the SailfishOS 4 contacts database to see what was missing. I hope it will help you. For commhistory I don’t have any ideas. Good luck on both.

[pbhat]

Thanks @ric9k nephros ithier @nas for trying to help.

I have followed in the main @ric9k instructions. Now my saved contacts.db is in /home/nemo/.local/share/system/privileged/Contacts/qtcontacts-sqlite/ this folder.

It is also in /home/nemo/.local/share/system/Contacts/qtcontacts-sqlite/ folder. Both files are owned by nemo:privileged. At both places there are *.wal and *.shm accompanying files too.

But I do not have contacts appearing in people application.

I had luck with commhistory though. In a similar way, I copied commhistory.db and commhistory is showing up with just phone numbers.

Is there any step missing ?

pbhat

[pbhat]

Thanks for being helpful.

Where can I get the PHP script to convert contacts to VCARD ? Do you think it will work with SF 2.X.X ?
Somewhere here there is some procedure detailed for importing from Vcard file.

[ric9k]

I have no knowledge about SFOS 2.2. There is maybe something different e.g. the files ownerships/rights.
Are you changinf SFOS version or do you keep the same one?

Places: Were the original files in the same directories (at the moment you saved them).
Did you correctly delete the new directories contents (other .wal and .shm) before to paste your original files?
Are you sure there were no .wal and .shm in the original directories?
Were the services really stopped while you copied your files? (stopping with sytemctl or copy as root while being in another user)
If all these steps are correct, there might be something proper to the 2.2 that I ignore.

[pbhat]

While I copied commhistory.db to ~/.local/share/commhistory/commhistory.db, I deleted existing commhistory.db file first along with associated *.shm and *.wal files without taking care of the running process and then copied over my old commhistory.db file alone. I did not create or copy over .shm,.wal files.
When I rebooted I saw .shm,.wal file were created and commhistory was visible in calls application and messages application.
For the contacts, I copied over in Recovery mode and again I did not copy over .shm and .wal files. When I rebooted into phone environment, I saw those files(.shm,.wal) created by the process

But the people application does not see the database.

[pbhat]

@ric9k and others,

I just wanted to share an observation.

I added just one contact through people application. Neither files
/home/nemo/.local/share/system/privileged/Contacts/qtcontacts-sqlite/contacts.db or

/home/nemo/.local/share/system/Contacts/qtcontacts-sqlite/contacts.db

changed.
Another observation is, both the files are swollen from the time I copied them over. They are certainly accessed at the start and something added,something marked out.

[ric9k]

Indeed, it sounds strange that the files are not modified. Note that the changes are perhaps stored in the .wal and/or .shm files for a later integration in the db. I am not knowledgable enough here.
If you have the original .wal and .shm files, you must copy them along with the original files. They are (at least one of them, I don’t remember which one) part of the db. A quick “sqlite .wal and .shm” search in the internet will give you more info.

[nephros]

@ric9k

I am not knowledgable enough here.
If you have the original .wal and .shm files, you must copy them along with the original files.

Neither am I - but.

The existence of these files are indicators that a process has the database file open and is working with it. Neither copying away nor coping over any of those files is safe or reliable.

The method to use is to make them go away before manipulating any of them. You make them go away by stopping the process that are using them.
You find the processes using them using one of:

$ devel-su
# lsof .local/share/commhistory/*
# fuser .local/share/commhistory/*

In this example this yields three users: /usr/bin/commhistoryd, /usr/bin/jolla-messages -prestart, and /usr/bin/voicecall-ui -prestart.

All of these are launched by systemd services, so simply killing the processes will not yield the desired results. Instead, stop all the services, make sure the -wal and shm files are gone, and only then copy your files.

[nas]

Here it is:

GitHub - osnas/sailfish3_contacts_export_php: A php script that opens the...

[pbhat]

Thank you, @nas

Will I have to integrate with a webpage and backend code or can I use it like a bash script ?

I do not see an executable for PHP under /usr/bin/* or /usr/sbin/*.

Sorry for my ignorance of it.

[pbhat]

@nephros , what you say is the right way to do.
In my experience, for commhistory, I got away with a rude deletion of all the three files, .db,.shm,*.wal files under .local/share/commhistory/ and just copying commhistory.db to the same location. I did get back history.
For contacts.db, I was a little more careful, I went into recoverymode and deleted all the three files and copied over contacts.db. When I restarted, I could see *.shm and *.wal files created by the processes.
But I suspect contacts.db under .local/share/system/privileged/qt5contacts-sqlite/contacts.db may not be the file contactsd is looking to.

[ric9k]

If a wal and shm files are created in the same directory, this tends to indicate that the db file in that directory is used.
Is it possible that the contacts DB is half broken because the original shm and wal were not saved along?
…Just a supposition.

Anyway, you can try to open contacts.db on your PC with sqlitebrowser or with any sqlite gui program to see if it hase some contents.

[pbhat]

@ric9k ,
‘pragma integrity_check’ returns ‘ok’ on contacts.db

[ric9k]

…Hmm… But still not read by Contacts?
Here is a mistery for me, I’m sorry to have no more ideas at the moment.

[nas]

If you have php installed in your system you could run the script with the command:

php -f FILE.php

you may remove the top part that I do a simple username / password check, I didn’t test it to see if it works from command line. In the same directory you sould have the contacts3.db and contacts4.db sqlite files. (check line 56 and 62 or edit them accordingly).

Also, it will output the Vcards in the terminal, you could try something like

php -f FILE.php > contacts.vcard

The contacts.vcard may need then some editing to remove some html on the top.
Let me know if it worked for you.