Search | Sailfish OS | Running | PineTime | All Posts

Moving a contact in another addressbook on Sailfish OS

April 07, 2023 — Nico Cartron

The problem

Someone sent me a .vcf contact card via text, which I saved. I didn't pay attention and it got saved to the "local" addressbook on my Xperia 10iii.

Example:

Therefore, it doesn't appear on my other devices (e.g., my laptop) as it's not synchronised back to my CardDAV account.

I thought I could just edit the contact and change the addressbook used, but turns out you can't.

Fixing it

Locating the addressbook

The nice thing with having a Shell access to your phone, is that you can do pretty much everything you want.

A quick search on the SFOS forum told me that the addressbook:

  • was located in ~./local/share/system/privileged/Contacts/qtcontacts-sqlite (new location since SFOS implemented SailJail),
  • had to be accessed via the devel-su command.

You have a contacts.db file, which you can manipulate with sqlite.

Digging into the Sqlite file

  • You'll need to install sqlite with pkcon install sqlite.
  • You can then access the db file with sqlite contact.db, which will give you a prompt like below:


 sqlite>

Sqlite basics

I'm not a sqlite expert, however you need to know a few commands:

  • .tables: shows a list of tables
  • .schema <table>: shows the table's schema / fields
  • select * from <table>: a SQL command to show the content of a table

SFOS contacts tables

OK, so a .tables gives me:

sqlite> .tables
Addresses            Families             OnlineAccounts
Anniversaries        Favorites            Organizations
Avatars              Genders              OriginMetadata
Birthdays            GeoLocations         PhoneNumbers
Collections          GlobalPresences      Presences
CollectionsMetadata  Guids                Relationships
Contacts             Hobbies              Ringtones
DbSettings           Identities           SyncTargets
Details              Names                Tags
DisplayLabels        Nicknames            Urls
EmailAddresses       Notes
ExtendedDetails      OOB

Finding the right addressbook

I poke around and found that the Collections table was the one containing the addressbooks:

sqlite> .schema collections
CREATE TABLE Collections (
collectionId INTEGER PRIMARY KEY ASC AUTOINCREMENT,
aggregable BOOL DEFAULT 1,
name TEXT,
description TEXT,
color TEXT,
secondaryColor TEXT,
image TEXT,
applicationName TEXT,
accountId INTEGER,
remotePath TEXT,
changeFlags INTEGER DEFAULT 0,
recordUnhandledChangeFlags BOOL DEFAULT 0);

OK, so the interesting part will be collectionID, which is the first field:

sqlite> SELECT * FROM COLLECTIONS;
1|0|aggregate|Aggregate contacts whose data is merged from constituent (facet) contacts|blue|lightsteelblue|||0||0|0
2|1|local|Device-storage addressbook|red|pink|||0||0|0
7|1|Contacts|||||carddav|13|/remote.php/dav/addressbooks/users/nico/contacts/|0|1
8|1|Recently contacted|||||carddav|13|/remote.php/dav/addressbooks/users/nico/z-app-generated--contactsinteraction--recent/|0|1
9|1|SIM|Contacts stored on SIM card|green|lightgreen|image://theme/icon-m-sim-2|contactsd|0||1|0
10|1|SIM|Contacts stored on SIM card|green|lightgreen|image://theme/icon-m-sim-1|contactsd|0||1|0
11|1|Default Address Book|||||carddav|17|/card.php/addressbooks/nc/default/|0|1

Got it!

  • "local" (2nd line, with ID 2) is probably the adressbook I want to move my contacts from,
  • "Default Address Book" (last line, with ID 11) is the CardDAV one I want to use (the other CardDAV are leftover from an old configuration I no longer use).

Understanding the structure of contacts

To my surprise, a contact in Sailfish OS is not a single table with plenty of fields, with one line for each contact;
rather, there are multiple tables (as seen above) which store specific information about a contact, such as name, birthday or email address.

What looked to be common to all those tables, was the contactID, so all I had to do was find the table with the contactID, and I could fix my problem.

And guess what? there's a table called Contacts, whose schema is:

 CREATE TABLE Contacts (
 contactId INTEGER PRIMARY KEY ASC AUTOINCREMENT,
 collectionId INTEGER REFERENCES Collections (collectionId),
 created DATETIME,
 modified DATETIME,
 deleted DATETIME,
 hasPhoneNumber BOOL DEFAULT 0,
 hasEmailAddress BOOL DEFAULT 0,
 hasOnlineAccount BOOL DEFAULT 0,
 isOnline BOOL DEFAULT 0,
 isDeactivated BOOL DEFAULT 0,
 changeFlags INTEGER DEFAULT 0,
 unhandledChangeFlags INTEGER DEFAULT 0,
 type INTEGER DEFAULT 0);

Damned, I have a contactID, but no name - which is what I'm after.

BUT there is a Contacts table, which has a lastname field: let's try something simple to see whether I can find one of the contacts I'm interested in:

 sqlite> SELECT * FROM Names where lastname like '<NAME'>;
 28736|3111|Thierry|thierry|NAME|name||||
 28749|3112|Thierry|thierry|NAME|name||||

Spot on! Although I have 2 entries.

So we have the contactID (2nd field: 3112 and 3112):, let's try this against the Contacts table:

 sqlite> SELECT * FROM Contacts where contactid = 3111;
 3111|2|2022-10-26T13:03:12.796|2022-10-26T13:03:12.796||1|1|0|0|0|1|0|0

Excellent! As you can see, the 2nd field has 2 as value, which means it's the local addressbook - that I want to change to 11.

Let's do the same query with the other contactID:

 sqlite> SELECT * FROM Contacts where contactid = 3112;
 3112|1|2022-10-26T13:03:12.796|2022-10-26T13:03:12.796||1|1|0|0|0|0|0|0

This time, the addressbook is 1, which is "Aggregate contacts" - not sure what it is, but I'm now almost sure that the first result is what I want to change.

Let's do it!

The solution should now be simple: I want to change the collectionID for this contact from 2 to 11:

 sqlite> UPDATE Contacts set collectionid = 11 where contactid = 3111;
 sqlite>

Let's verify that the change was done:

 sqlite> SELECT * FROM Contacts where contactid = 3111;
 3111|11|2022-10-26T13:03:12.796|2022-10-26T13:03:12.796||1|1|0|0|0|1|0|0

YEAH! It has been taken into account, now the last thing I need to check is whether it is reflected in the SFOS People app:

Forcing a sync to my CardDAV server, and then synch'ing my laptop, effectively shows this contact in my Thunderbird address book!

Finding all local contacts

I checked how many contacts where in the local addressbook:

 sqlite> select * from Contacts where collectionid = 2;
 1|2||||0|0|0|0|0|0|0|0
 7|2|2022-10-01T00:31:46.607|2022-10-01T00:31:46.607||1|0|0|0|0|1|0|0
 9|2|2022-10-01T00:31:46.627|2022-10-26T12:21:24.274||1|1|0|0|0|3|0|0
 11|2|2022-10-01T00:31:46.639|2022-10-01T00:31:46.639||1|0|0|0|0|1|0|0
 13|2|2022-10-01T00:31:46.647|2022-10-01T00:31:46.647||1|1|0|0|0|1|0|0
 15|2|2022-10-01T00:31:46.656|2022-10-01T00:31:46.656||1|1|0|0|0|1|0|0
 3108|2|2022-10-17T19:22:07.099|2022-10-17T19:22:07.099||0|1|0|0|0|1|0|0
 3119|2|2022-11-30T09:05:41.944|2022-11-30T09:05:41.944||1|0|0|0|0|1|0|0
 6246|2|2023-03-24T09:10:50.981|2023-03-24T09:10:50.981||1|1|0|0|0|1|0|0

=> 9 contacts, with the first one looking not really used, so I fixed those by hand :)

Wrap Up

It took me a bit of poking around, but I'm glad I did it - and it once again confirms that I love Sailfish OS!!!


Tags: SailfishOS


I don't have any commenting system, but email me (nicolas at ncartron dot org) your comments!
If you like my work, you can buy me a coffee!