search

BelledonneCommunications / linphone-desktop

Linphone is a free VoIP and video softphone based on the SIP protocol. Mirror of git://git.linphone.org/linphone-desktop.git
https://linphone.org/
GNU General Public License v3.0
391 stars 200 forks source link

`gnome-contacts` plugin, contact import/export and SQL manipulation of `friends.db` #797

Open leukimi opened 7 months ago

leukimi commented 7 months ago

Has anyone been able to compile a plugin that would be able to access gnome-contacts SIP-phone numbers?

Gnome calls probably already has the code needed to access gnome-contacts phone numbers. Maybe the code can be reused.

Gnome Contacts seems to use vCard 3.0 and does not support any specific SIP/SIPS field that would export like IMPP:sip or IMPP:sips. Gnome contacts allows Telephone field Other to hold text which is exported as TEL;TYPE=VOICE:demouser12@sip.domain.org.

A workaround would be to access friends.db SQLite3 database. See also this guide and also this other guide aimed at gnome-contacts SQLite3 database.

Connecting LibreOffice Base to friends.db

There is a possibility to use LibreOffice Base to manipulate friends.db. To make it possible, you will need an OBDC driver for SQLite3 and specify the path to friends.db.

Once sqliteobdc is installed, there will be a file *odbc.ini or odbcinst.ini somewhere on the system. Here some examples:

This file contains:

[PSQL]
Description=PostgreSQL
Driver64=/usr/lib64/psqlodbcw.so
UsageCount=1

[SQLITE3]
Description=SQLite ODBC 3.X
Driver=/usr/lib64/libsqlite3odbc.so
Setup=/usr/lib64/libsqlite3odbc.so
Threading=2
FileUsage=1
UsageCount=1

Now we have to specify the path to linphone contact database in $HOME/.local/share/linphone/friends.db

Create file $HOME/.odbc.ini with the following content. SQLITE3 refers to file odbcinst.ini:

[linphone contacts]
Description=linphone contacts SQLite3
Driver=SQLITE3
Database=.local/share/linphone/friends.db

Note: Writing Database=$HOME/.local/share/linphone/friends.db will not work. Absolute and relative paths work, no variables.

  1. Open program lobase or find LibreOffice Base in the application menu. Select:
  2. Connect to an existing database: ODBC
  3. Next
  4. Name of the ODBC data source: Click Browse button
  5. Select: linphone contacts
  6. Next
  7. Click Test connection button. It should display a pop-up with "The connection was established successfully."
  8. Click Finish button.
  9. Save as Linphone.odb

You should now be able to see table friends and table friends_lists

Modifications may require restart of Linphone before changes are seen in the contacts section since there is no reload-contacts-from-database-button.

Using DB_Browser_for_SQLite-v3 (AppImage) to modify friends.db

There is a user friendly SQLite database tool called DB Browser for SQLite that can be used to modify friends.db

vCard import example

You can probably export contacts in vCard format from your current contact app. The SIP-phone field is often not exported. Links to contact avatars are sometimes exported, which would make it possible to populate Linphone with the same hard copy avatars (no dynamic link is possible). Some cleaning and renaming of the fields will probably be needed to allow Linphone import. Linphone uses ROLE field for company name.

Create vCard contact(s) in file contacts.vcf that looks like this:

BEGIN:VCARD
VERSION:4.0
FN:First'N@meã±àåäöñ Second#"Name"
IMPP:sips:user@sip.domain.org
ORG:HomeCompany;Kitchen;Chef
TITLE:Le Chef
ROLE:CompanyB
ADR:;;Street 1 block 2;VillageA, ProvinceG;;12345;CountryB ContinentC
IMPP:sips:test@test.domain.org
ROLE:Customer Support
EMAIL:second@mail.org
EMAIL:webmaster@mail.org
TEL:+12345678
URL:https://www.domain.org
URL:https://www.extra.org
PHOTO:linphone-desktop:/a.png
END:VCARD
BEGIN:VCARD
VERSION:4.0
FN:Demo's User
IMPP:sips:demouser@sip.domain.org
ORG:HomeCompany;Livingroom
TITLE:TV Operator
ROLE:CompanyH
ADR:;;Street 2;VillageB;;23456;CountryT
ROLE:TV Support
EMAIL:tvsupport@mail.org
TEL:+23456789
URL:https://www.whatsontv.org
PHOTO:linphone-desktop:/b.jpg
END:VCARD

Add two square avatars of choice to directory $HOME/.local/share/linphone/avatars to test with, one PNG and one JPEG named as the following:

Create an import script, for instance this PHP-script add_contacts_with_vcard_data.php that can be run locally, adapted from this source:

<?php

$str = file_get_contents( "contacts.vcf" );
$tmp = explode( "\r\n", $str );
$number = $vcard = "";
$number2 = "";
foreach ( $tmp as $line )
{
// contacts.vcf probably has to be cleaned first
// Fields to skip
//if ( strpos($line, "N:;") !== false) continue;
//if ( strpos($line, "ADR:;") !== false) continue;
//if ( strpos($line, "CATEGORIES:;") !== false) continue;
//if ( strpos($line, "PHOTO:;") !== false) continue;
//if ( strpos($line, "item1.") !== false) continue;
//if ( strpos($line, "item2.") !== false) continue;
//if ( strpos($line, "item3.") !== false) continue;
//if ( strpos($line, "item4.") !== false) continue;

$line = str_replace( "VERSION:3.0", "VERSION:4.0", $line );
//Full name detection from another field
//$line = str_replace( "item1.ORG:", "FN:", $line );
$line = str_replace( "`", "''", $line );
$line = str_replace( "'", "''", $line );
$line = str_replace( ",", " ", $line );
$line = str_replace( "\\,", " ", $line );
$line = str_replace( "\\", " ", $line );

if ( $line == "BEGIN:VCARD" ) $vcard = "";
if ( strpos($line, "TEL:") !== false)
{
$number = explode(":", $line)[1];
if ( $number == "" ) $number = explode(";", $line)[1];

$number = str_replace( " ", "", $number );
$number = str_replace( "-", "", $number );
$number2 = str_replace( "+", "00", $number );

$vcard .= "IMPP:sips:" . $number . "@sip.tel.no' || char(10) || '";
$vcard .= "IMPP:sips:" . $number2 . "@1.2.3.3' || char(10) || '";
$vcard .= "IMPP:sips:" . $number2 . "@1.2.3.4' || char(10) || '";
continue;
}
$vcard .= $line;

if ( $line == "END:VCARD" )
{
$sipuri = "sips:$number@sip.tel.no";
$sipuri2 = "sips:$number2@sip.tel.no";
$cmd = "sqlite3 ~/.local/share/linphone/friends.db \"DELETE FROM friends WHERE sip_uri='$sipuri' OR sip_uri='$sipuri2'\" \".exit\" ";
shell_exec( $cmd );

$cmd = "sqlite3 ~/.local/share/linphone/friends.db \"INSERT INTO friends (friend_list_id, sip_uri, subscribe_policy, send_subscribe, ref_key, vCard, vCard_etag, vCard_url, presence_received) VALUES (1, '$sipuri', 1, 0, NULL, '" . $vcard . "', NULL, NULL, 0);\" \".exit\" \n";
shell_exec( $cmd );
}
$vcard .= "' || char(10) || '";
}

?>

Install the package that has file /usr/bin/php for your linux distribution. For instance for openSUSE Tumbleweed:

zypper install php8-cli

Start the import script.

php -S 127.0.0.1:8000 add_contacts_with_vcard_data.php

Open the address in a webbrowser to create the import by hitting refresh (often F5 button works as well to refresh):

xdg-open "http://127.0.0.1:8000/"

Restart Linphone and see if the contacts were imported.

SQL statement to insert new contact

sqlite3 $HOME/.local/share/linphone/friends.db "INSERT INTO friends (friend_list_id, sip_uri, subscribe_policy, send_subscribe, ref_key, vCard, vCard_etag, vCard_url, presence_received) VALUES (1, 'sips:+12345@sip.tel.org', 1, 0, NULL, 'BEGIN:VCARD' || char(10) || 'VERSION:4.0' || char(10) || 'IMPP:sips:+12345@sip.tel.org' || char(10) || 'FN:Demo12345' || char(10) || 'IMPP:sips:+12345@1.2.3.4' || char(10) || 'END:VCARD', NULL, NULL, 0);" ".exit"

Test files

contacts.csv contacts_lists.csv

add_contacts.csv add_contacts_lists.csv

Export contacts from friends.db

Create at lease one contact with multiple fields in Linphone.

Full export:

DATABASE=$HOME/.local/share/linphone/friends.db
sqlite3 -header -csv $DATABASE "select * from friends;" > contacts.csv
sqlite3 -header -csv $DATABASE "select * from friends_lists;" > contacts_lists.csv

Only contacts export with headers (without column id):

sqlite3 -header -csv $DATABASE "SELECT friend_list_id, sip_uri, subscribe_policy, send_subscribe, ref_key, vCard, vCard_etag, vCard_url, presence_received FROM friends;" > contacts.csv

contacts.csv with header (without column id) would look like this:

friend_list_id,sip_uri,subscribe_policy,send_subscribe,ref_key,vCard,vCard_etag,vCard_url,presence_received
1,"""User14"" <sips:user14@sip.tel.org>",1,0,,"BEGIN:VCARD
VERSION:4.0
FN:User14
IMPP:sips:user14@sip.tel.org
END:VCARD
",,,0

Only contacts export without headers (without column id):

sqlite3  -csv $DATABASE "SELECT friend_list_id, sip_uri, subscribe_policy, send_subscribe, ref_key, vCard, vCard_etag, vCard_url, presence_received FROM friends;" > contacts.csv

Import contacts into friends.db

Make sure friends.db is empty.

DATABASE=$HOME/.local/share/linphone/friends.db
rm -f $DATABASE
# Optionally:
touch $DATABASE

Restart Linphone to recreate the database headers.

Alternatively, you can reset all your personal Linphone settings with

rm -rf $HOME/.config/linphone $HOME/.local/share/linphone

and restart linphone to re-create all default settings.

Then import the previously exported contacts.

Populate database with new contacts (without id column) assuming that table friends_lists already exists with the following data on row where id=1:

sqlite3 $DATABASE "INSERT INTO friends_lists (id, display_name, rls_uri, uri, revision) VALUES (1, '_default', NULL, NULL, 0);" ".exit"

Executing the above SQL statement will cause an error if row id=1 already exists.

sqlite3 $DATABASE ".import --csv contacts.csv friends"
# Alternatively skipping first header row:
sqlite3 $DATABASE ".import --csv --skip 1 contacts.csv friends"

Alternatively, a completely clean database without headers can be recreated with importing the full export previously made with command (all columns including id):

# DATABASE=$HOME/.local/share/linphone/friends.db
# sqlite3 -header -csv $DATABASE "select * from friends;" > contacts.csv
# sqlite3 -header -csv $DATABASE "select * from friends_lists;" > contacts_lists.csv
DATABASE=$HOME/.local/share/linphone/friends.db
sqlite3 $DATABASE ".import --csv contacts.csv friends"
sqlite3 $DATABASE ".import --csv contacts_lists.csv friends_lists"

Import additional contacts into friends.db

Assuming there is a header row which will be skipped.

DATABASE=$HOME/.local/share/linphone/friends.db
sqlite3 $DATABASE ".import --csv --skip 1 add_contacts.csv friends"
sqlite3 $DATABASE ".import --csv --skip 1 add_contacts_lists.csv friends_lists"

Restart linphone if you don't yet see any of the imported contacts.

python3 and bash import export script

It seems that the gnome-contacts-vcard-importer python script could be adapted to Linphone vCard import. The avatars import/export would have to be dealt with separately.

Experimental tools to modify linphone contact database which include:

backup tar.xz archive with vCard, csv, (only used) avatars/*

linphone-contacts-export-archive
linphone-contacts-import-archive contacts_linphone_2024-01-01.tar.xz

vCard format

linphone-contacts-export-vcard
linphone-contacts-import-vcard contacts_linphone_2024-01-01.vcf

SQLITE3 csv format

linphone-contacts-export-csv
linphone-contacts-import-csv contacts_linphone_2024-01-01.csv

The tool exports a vCard that looks like this with all fields set:

BEGIN:VCARD
VERSION:4.0
FN:Test vCardUser Three
IMPP:sips:test.vcarduser.three@sip.linphone.org
IMPP:sips:+125@sip.linphone.org
PHOTO:linphone-desktop:/avatar.svg
ROLE:CompanyNameC
EMAIL:test.vcarduser.three@private.mail.org
EMAIL:info@mail.companynamec.org
URL:https://www.companynamec.org
ADR:;;Third Street 3C;NeighborhoodC;;123 48 Smalltown;UnspecifiedCountryC U
 nspecifiedContinentC
END:VCARD

TO CHECK: automatic update from gnome-contacts

It should (in theory) be possible to use an automatic flow of regularily updating linphone contacts with whatever gnome-contacts gathers from online sources:

gnome-contacts -> merge contact lists from online sources -> export as vCard -> clean the vCard with cleaning script and format as vCard 4.0 using tools such as python3 tool VCF_contacts_merger with GUI and perl tool Text-vCard-Precisely capable of dealing with v4.0 as well as python3 library vobject for vCard 3.0 but able to output fields also used in v4.0 -> use cron service with help of linphone-contacts-vcard-csv-import-export tools to regularily (or trigger based) update linphone contacts.