Moving a contact in another addressbook on Sailfish OS
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-sucommand.
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
dbfile withsqlite 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 / fieldsselect * 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