Exporting text messages on Sailfish OS

I had to export text messages (SMS) from a Jolla phone running Sailfish (not sure the version is relevant, SailfishOS 1.1.6.27 (Aaslakkajärvi) (armv7hl)).

2019-04-17 update: these instructions have been corrected and updated for Sailfish OS 3.0.2.8 (Oulanka).

Here is the best solution I found, using a bash script (the groovy script used in the previous version of this post is the one having issues now, so i switched to a bash script).

All scripts tested came from this question on together.jolla.com.

Recipe

Activate dev mode on the Jolla phone (I’ll let you follow guides for this). In current versions this is as easy as going into Settings > Developer mode and switching it on. Make note of the password Sailfish gives you. It will also display the Sailfish phone’s local IP address, most likely in the 192.168.0.xxx range. Also make note of this, you’ll need it in a sec.

you’ll be asked to create a user, it’s actually not the user you’ll use to connect over SSH, it seems to be a local user. For simplicity and consistency’s sake, I suggest you name it nemo. (Current versions of Sailfish automatically create the nemo user)

Create a working directory on your computer and enter it:

mkdir -P ~/Jolla/workdir & cd ~/Jolla/workdir/ 

Extract the communications database, over SSH :

scp -R nemo@192.168.0.xxx:/home/nemo/.local/share/commhistory/* ~/Jolla/workdir

Create the script, using your favourite text editor. We’ll use gedit here, for simplicity:

gedit ~/Jolla/workdir/sms-export-sailfish.sh

And paste the following

#!/bin/bash

# A script to dump all text messages to stdout
# 
# Usage:
#    sms-dump-xml
# (or whatever you choose the name the script)

# The database with the SMS messages
sql_database="commhistory.db"

# The SQL query to count the messages
#    type      : Messages are of type 2
sql_count_query="SELECT COUNT(*) FROM Events WHERE type=2;"

# The SQL command to select which messages to retrieve
#    remoteUid : The contact's phone number
#    direction : The message direction, can be 1 (received) or 2 (sent)
#    startTime : When the message was sent (timestamp)
#    freeText  : The text of the actual message
#    isRead    : Whether the message is read (1) or not (0)
#    type      : Messages are of type 2
sql_list_query="SELECT remoteUid, direction, startTime, isRead, freeText FROM Events WHERE type=2;"

# Don't let root run this
if [ $UID -eq 0 ]; then
    echo "Do not run as root."
    exit 1
fi

# Count the messages
nb_messages=`sqlite3 "$sql_database" "$sql_count_query"`

# Display the XML header
echo '<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>'
echo '<?xml-stylesheet type="text/xsl" href="sms.xsl"?>'
echo "<smses count=\"$nb_messages\">"

# Get the message data from the phone's database
# Parse each line and print it, nice and pretty
firstLine=true
sqlite3 "$sql_database" "$sql_list_query" | while read line; do

    # Check for the desired format...
    format=`echo $line | sed -e 's/.*|[12]|[0-9].*|.*/CORRECTFORMAT/'`

    if [ "$format" == "CORRECTFORMAT" ]; then
        # write sms if it is complete
        if [[ ${firstLine} == false ]]; then
        echo "  <sms protocol=\"0\" address=\"$contactno\" date=\"$timestamp\""\
             "type=\"$direction\" subject=\"null\"" "body=\"$message\" toa=\"null\""\
             "sc_toa=\"null\" service_center=\"null\" read=\"$read\" status=\"-1\""\
             "locked=\"0\" />"
        fi

        # Get the contact's phone number
        contactno=`echo $line | cut -d '|' -f 1`
        # Get the message direction
        direction=`echo $line | cut -d '|' -f 2`

        # Convert the Unix timestamp to a millisecond-based timestamp
        unixtime=`echo $line | cut -d '|' -f 3`
        timestamp="${unixtime}000"

        # Get the read status
        read=`echo $line | cut -d '|' -f 4`

        # Get the actual text message
        message=`echo $line | cut -d '|' -f 5 | sed 's/&/\&/g' | sed 's/\"/\"/g'`
        firstLine=false
    else
        # append the next line
        message="${message}
${line}"

    fi
done

echo "</smses>"

(Script by hamsterbacke based on work by previous authors. No licence known)

Make the script executable:

chmod +x ~/Jolla/workdir/sms-export-sailfish.sh 

Rn the script on the SMS database and tell the shell to output the results in a given file, which we’ll call sms_export_$DATE.xml:

./sms_export_sailfish.sh > sms_export-`date -I`.xml  

That’s it, you now have an xml file with all your text messages, in a format compatible with Android’s SMS Backup & Restore.

If you want to restore the text messages to the Sailfish phone, you’d want to send the commhistory.db file (along with the /data folder) back to the phone. I suggest whenever you start manipulating a copy of the text messages database (commhistory.db) that you include the date, so you can have several in parallel and still have a clue which one contains what:

cp -a commhistory.db commhistory.db.`date -I`

Leave a Reply

Your email address will not be published. Required fields are marked *