Need both export and import for contacts, sms history, call history

I am thinking about the following scenarios, both relevant for me:

  • migrating from one Librem 5 to another, or to/from PinePhone
  • reflashing PureOS for whatever reason, then restore selected things afterwards, but not restoring the whole system

I know the Tips&Tricks part in the wiki has a part about importing contacts, that’s good but I would like to also be able to do the following:

  • exporting contacts in the form that can later be imported with contacts importer
  • exporting SMS history
  • importing SMS history
  • exporting call history
  • importing call history

Probably these things can be achieved using the backup/restore tool as long as the idea is to restore to the same device, but I’m interested also in being able to move from one device to another, and also from one operating system to another, for example migrate between PureOS and postmarketOS or Mobian. Also, having the SMS history exported would be nice as it would allow me to work with something from my SMS history on another computer, like searching through the SMS history looking for some text string, things like that.

What do you think, how to do these things?


For import/export of contacts, search the forum for the tool syncevolution.


The contacts database should be able to be copied from one Librem 5 to another - as long as you don’t need to merge the migrating contacts in with other existing contacts. (I don’t necessarily think that exporting for use with the contacts importer is the right approach unless the export format is lossless.)

That approach might work for SMS history and call history too - but you would need to work out which files to copy across.

Even though I don’t know how to do any of those export/imports, or whether the software functions even exist right now, I take heart from the fact that with an open source phone, it is always going to be possible.

You might be the pathfinder here. If you work it out, let us know. :slight_smile:

1 Like

The thread is a little old, but I just received my second L5 and took the plunge to move my main sim from Android. After a couple of days messing around I managed to cobble together a PHP (sorry if anyone’s offended by that) script that reads a xml dump of Android sms messages and generates SQL statements to import them into Chatty’s DB - (after importing contacts from a .vcf file).

If anyone is still interested, here it is … comments and instructions in the script. As noted below, Chatty will only display the threads when you receive or compose a message from/to one of your contacts.

Don’t forget to take a backup first …

#!/usr/bin/php -q

 * Quick kludgy script to import Android messages into L5.
 * Not warrantied in any way - as the saying goes, if it breaks you get to keep the pieces.
 * Probably error/omissions/bugs exist - please check and correct.
 * Android message dump was created with SMS Backup & Restore
 * Syntech Pty Ltd
 * free version
 * The sms backup file is xml format.
 * Chatty uses a SQLite database
 *  ~/.purple/chatty/db/chatty-history.db
 * MAKE A BACKUP OF ~/.purple/chatty/db/chatty-history.db
 * copy your backup to at least two places just in case you're as dangerously inattentive as I am
 * - mind you, Portuguese wine is very good.
 * Here's what I did ...
 * Took a backup of my Android contacts and saved them to a .vcf file.
 * Imported the contacts into my L5 using the contacts gui.
 * Dumped Android sms messages and copied the xml file to a Linux laptop
 * copied the ~/.purple/chatty/db/chatty-history.db to the same laptop
 * installed Sqlite (sqlite3) onto the laptop and searched for how to use it on the command line
 * CLI Sqlite interface
    user@laptop: sqlite3 chatty-history.db

    SQLite version 3.44.0 2023-11-01 11:23:50
    Enter ".help" for usage hints.

    The most useful commands at this point (IMO) are

    .mode column

    The first avoids frustration trying to read pipe-separated non-width-formatted data.
    The second lists the tables.

    I also ran schemaspy against the database and checked out the relationships between the tables.

    This script will echo the SQL INSERT and UPDATE statements to STDOUT.
    It does not execute the SQL.

    I ran the script and redirected the output to a text file
    sms_android_to_l5.php > to_chatty.sql

    then invoked sqlite3 and passed this file to it, saving the output to a log file
    sqlite3 ~/path/to/copyof/chatty-history.db ".read to_chatty.sql" > to_chatty.log 2>&1

    The last few SQL statements of this script query the relevant tables so you can check the log file to
    satisfy yourself that everything looks OK.

    When you're happy (all relative) ... copy the updated database back to your L5

    It seems that Chatty will not display the threads until either,
        you receive a text from one of your contacts, or
        you start a new message and choose someone from the contacts in the xml file (i'e', the threads that you imported).


$x          = "/home/USER/Downloads/sms.xml";   // <=== set correct path to xml backup file
$users      = [];
$guid       = "lower(hex(randomblob(4))) || '-' || "    //generate guid for messages - found on t'internet
             ."lower(hex(randomblob(2))) || '-4' || "
             ."substr(lower(hex(randomblob(2))),2) || '-' || "
             ."substr('89ab',abs(random()) % 4 + 1, 1) || "
             ."substr(lower(hex(randomblob(2))),2) || '-' || "
$me_name    = 'invalid-0000000000000000';   //Chatty uses this name / id for the L5 user
$me_id      = 1;

$xmlDoc     = new DOMDocument();                    //Read file into DOM document
$dom        = $xmlDoc->load($x);                    //
$e          = $xmlDoc->documentElement;             //Get top level node
$msg_array  = $e->getElementsByTagName('sms');      //Get all <sms> tags

foreach($msg_array as $msg) {                       //iterate through messages
    if($msg->hasAttributes()) {                     //ignore empty / malformed tags
        //Message details are held as attributes, not <tags>
        $address = preg_replace('/^0/', '+44', $msg->getAttribute('address'));  //address attribute = phone number
        $users[$address] = $users[$address] ?? [];                              //create array for phone numbers
        $users[$address]["user"] = "INSERT INTO users (username, alias, avatar_id, type) "  //create entry in users table - OK to overwrite for each message with the same phone #
                                ."VALUES ("
                                    ."'$address', "
                                    ."'".$msg->getAttribute('contact_name')."', NULL, 1"
        $users[$address]["thread"] = "INSERT INTO threads ("                                // messages are associated with message threads
                                        ."name, alias, avatar_id, "                         // - create an entry for this phone #
                                        ."account_id, type, encrypted, "                    // - set the last_read_id to the max msg id for this phone - number
                                        ."last_read_id, visibility, "                       //  it will be incremented for each new message for this phone #
                                        ."notification"                                     // visibility = 0 (from chatty source code : #define THREAD_VISIBILITY_VISIBLE  0)
                                    .") VALUES ("
                                        ."'".$address."', NULL, NULL, 1, 0, 0, "
                                        ."(SELECT max(id) FROM messages "
                                            ."WHERE sender_id = ("
                                                ."SELECT id FROM users "
                                                    ."WHERE username = '".$address
                                        .", 0, 1);"
        $users[$address]["thread_members"] = "INSERT INTO thread_members "                  // add phone number to this (i.e., the latest) thread.
                                                ."thread_id, user_id"
                                            .") VALUES ("
                                                ."(SELECT max(id) FROM threads), "
                                                ."(SELECT id FROM users "
                                                ."WHERE username = '$address')"
        $users[$address]['msgs'] = $users[$address]['msgs'] ?? [];                          //  create array for messages for this phone number
        $direction = $msg->getAttribute('type') == 2 ? -1:1;                                //  xml file has type="1" (inbound) or "2" (outbound)
                                                                                            //  chatty has 1 (inbound) or -1 (outbound)
        $users[$address]['msgs'][] = "INSERT INTO messages ("                               //  Add new message
                                    ."uid, thread_id, sender_id, "
                                    ."user_alias, body, body_type, "
                                    ."direction, time, status, "
                                    ."encrypted, preview_id, subject)"
                                ." VALUES ("
                                    ."$guid, (SELECT max(id) FROM threads), "               // the message belongs to the current (max) thread
                                    ."(SELECT id FROM users WHERE username =  '$address'), "// set sender_id to current phone number - Needs to be fixed later
                                    ."NULL, '"                                              // for outbound messages as they are from $me
                                    .str_replace("'", "''", $msg->getAttribute('body'))     //  Double any embedded single quotes '
                                    ."', 1, $direction, "
                                    .substr($msg->getAttribute('date'), 0, 10)
                                    .", ".($direction == -1 ? '4' : 'NULL').", 0, NULL, NULL);";    //  xml file has 13 digits for time, chatty uses 10
        $users[$address]['msgs_upd'] = "UPDATE threads SET last_read_id = "                         //  set current message as last one in thread
                                        ."(SELECT max(id) FROM messages) WHERE id = "               //  - incremented for each new message
                                        ."(SELECT max(id) FROM threads);";                  //  Final update to set the latest msg id for this thread.



echo "BEGIN;\n\n";

foreach($users as $user) {
    if (!array_key_exists('user', $user)) continue;
    echo $user['user']."\n";
    echo $user['thread']."\n";
    echo $user['thread_members'];
    foreach($user['msgs'] as $msg) {
        echo $msg."\n";
    echo $user['msgs_upd']."\n";

//Fix sender_id for outbound messages
echo "update messages set sender_id = $me_id WHERE direction = -1;\n";

//Admire your handiwork (or weep)
echo ".mode column\n";
echo "SELECT * FROM users;\n";
echo "SELECT * FROM threads;\n";
echo "SELECT * FROM messages ORDER BY sender_id;\n";

echo "\nCOMMIT;\n";


What’s wrong with PHP?

1 Like

Poor security reputation when used embedded within a web server to generate dynamic content i.e. when exposed to the internet?

I don’t have PHP in use anywhere and hence definitely not in use on any web server but … I do see a lot of hackers probing for PHP in my web server logs (along with many many other types of probing).

I don’t know. I guess it’s up to @laccata to explain why someone might be offended.

1 Like

It was a little tongue-in-cheek, but you’re right about its reputation - mostly unfair these days, like Italian cars. I saw it described as a fractal of poor design many years ago and although I don’t use it for any public web sites, I do use it quite often as a scritping language.