We have moved to a new Sailfish OS Forum. Please start new discussions there.
30

[How-to] Saving SMS / text conversations

asked 2014-08-22 18:50:00 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

updated 2017-06-18 17:14:36 +0200

Asmir gravatar image

Here is a script to save SMS (text) messages on a Jolla mobile.

  • You can run the script from either Terminal or logged in through SSH.
  • Run the script as a regular user (do not use "devel-su")
  • This script will print the messages to stdout (after nicely formatting them).
  • Although this script may have bugs, it will not damage or delete any SMS messages. It only reads the messages from the internal communication database.

You can choose to name the script anything you like (for example, "sms-dump"). You can save the output to a text file by redirecting the output (for example, "sms-dump > sms-with-mom.txt"). Make the file executable ("chmod 755 sms-dump"). A convenient place to save it is in "/home/nemo/bin", which is already in the default path.

NOTE: This is a wiki page. Please feel free to contribute documentation, bug fixes, and any other improvements to this script.

#!/bin/bash

# A script to dump text messages to stdout
# 
# Usage:
#    sms-dump <phone-number> [Name]
#
#    phone-number : A phone number (example: 4565551234)
#
#    Name : (Option) The contact's name, as it will appear in the output.
#           If not provided then it will use the phone number.

# My name, as it will appear in the output
me="Me"

# The database with the SMS messages
sql_database="/home/nemo/.local/share/commhistory/commhistory.db"

# The SQL command to select which messages to retrieve
#    direction : Who sent the message, can be 1 (them) or 2 (me)
#    startTime : When the message was sent
#    freeText  : The text of the actual message
#    remoteUid : The contact's phone number
#    type      : Messages are of type 2
sql_command="SELECT direction, startTime, freeText FROM Events WHERE remoteUid LIKE '%$1' AND type=2;"

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

# Check for a phone number as a command line argument
if [ $# -lt 1 ]; then
    echo "usage: $(basename $0) <phone-number> [Name]"
    exit 1
fi

# Set the contact's name, if provided
if [ $# -gt 1 ]; then
    contact="$2"
else
    contact="$1"
fi

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

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

    if [ "$format" == "CORRECTFORMAT" ]; then

        # Get the name of the person who's texting
        fromwhom=`echo $line | cut -d '|' -f 1`

        if [ $fromwhom -eq 1 ]; then
            fromwhom="$contact"
        else
            fromwhom="$me"
        fi

        # Convert the Unix timestamp to a human readable format
        unixtime=`echo $line | cut -d '|' -f 2`
        datetime=`date -d@${unixtime} "+%Y-%m-%d %H:%M"`

        # Get the actual text message
        message=`echo $line | cut -d '|' -f 3`

        # Copy all the information into a new file
        echo "$fromwhom (${datetime}): $message"

    else

        # ...Fallback, just copy the line
        echo "$line"

    fi

done

Usage

sms-dump <phone-number> [Name]
sms-dump 4565551234 John # Example

Known Issues

  • Messages that have a newline character will still be printed but will cause an error message to appear.
edit retag flag offensive close delete

Comments

1

Thanks for this, much appreciated! It would be great if someone could wrap a simple UI around it as I suppose that would be trivial.

nthn ( 2014-08-22 20:32:29 +0200 )edit

Thanks!

Now I wanted to dump all my sms conversations, with all senders/recipients, to one text file. I managed to do this by modifying your script (removing the remoteUid LIKE '%$1'condition). But I'd like the script to also output the sender's number (so that every message would be output like [number] [timestamp] [message]). How should I modify the script to achieve this? (I have a hunch, but I'm really not good at this, so if you can easily give an answer I'd appreciate it :)

Update: Never mind, I managed to do it. Learned something about sed and cut while I was at it :) I'll soon post the modified script as an answer below in case someone's interested.

(Why I wanted to do this? My Jolla is going to care and I'm using another phone for the time being, so I wanted to save all my sms's in case it gets flashed. I also have the .vault backup stored on my computer, but I wanted to have the sms's in plain text also.)

ssahla ( 2014-08-22 21:52:07 +0200 )edit

This script does not support MMS. Since I wanted those as well, I made https://github.com/tkalliom/convert-to-sbr-xml (though I didn’t figure out how to access MMS without the parts stored locally, so even my script misses some MMS). Use mine at your own risk.

Timo K ( 2017-09-02 17:57:21 +0200 )edit

6 Answers

Sort by » oldest newest most voted
6

answered 2015-07-23 02:22:32 +0200

lOtR gravatar image

Here is a second variation of the script, that outputs in an XML format, as expected by the SMS Backup & Restore Android app.

It allows you to transfer your messages from your Jolla to an Android phone.

It still does not handle multi-line messages correctly: only the first line is exported, the other ones are only a comment in the file. It's pretty easy to fix the xml file manually afterwards (copy-paste the other lines in the body attribute), but it's a pain to do in bash...

#!/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="/home/nemo/.local/share/commhistory/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
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

        # 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/&/\&amp;/g' | sed 's/\"/\&quot;/g'`

        # Copy all the information into a new file
        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\" />"

    else

        # ...Fallback, just copy the line in a comment
        echo "<!--$line-->"

    fi

done

echo "</smses>"
edit flag offensive delete publish link more

Comments

1

Here is a Groovy version that correctly escapes everything (including multi-lines messages):

#!/usr/bin/env groovy

@GrabConfig(systemClassLoader=true)
@Grab('org.xerial:sqlite-jdbc:3.8.11.2')
import groovy.sql.Sql

import groovy.xml.MarkupBuilder

if (args.size() != 1) {
  println "Usage: dump.groovy <commhistory.db>"
  return
}

def (dbFile) = args

def sql = Sql.newInstance("jdbc:sqlite:$dbFile")
def messages = sql.rows('SELECT remoteUid, direction, startTime, isRead, freeText FROM Events WHERE type = 2')

System.out.withWriter('UTF-8') {w ->
  def xml = new MarkupBuilder(w)

  xml.mkp.xmlDeclaration(version: '1.0', encoding: 'UTF-8')
  xml.mkp.yieldUnescaped("<?xml-stylesheet type='text/xsl' href='sms.xsl'?>\n")
  xml.smses(count: messages.size()) {
    messages.each {
      sms(
        protocol: 0,
        address: it.remoteUid,
        date: "${it.startTime}000",
        type: it.direction,
        subject: 'null',
        body: it.freeText,
        toa: 'null',
        sc_toa: 'null',
        service_center: 'null',
        read: it.isRead,
        status: -1,
        locked: 0,
      )
    }
  }
}
sami.boukortt ( 2015-12-28 21:38:19 +0200 )edit
3

I modified your bash script so it is multi-line compatible. The groovy script didn't work on my machine.

#!/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/&/\&amp;/g' | sed 's/\"/\&quot;/g'`
        firstLine=false
    else
        # append the next line
        message="${message}&#10;${line}"

    fi
done

echo "</smses>"
hamsterbacke ( 2016-08-17 15:17:34 +0200 )edit

Hi, i tried your script but the terminal say that there is " a syntax error in the first line"... i ask if you could tell me how i can backup sms with your script. can you list the steps to do with the related commands? Also you can also tell me how to restore sms on Android 7.1.1? so you have a backup. thank you so much for your help.

Nick ( 2017-12-04 22:45:51 +0200 )edit

@Nick One possible reason could be that you saved the script as UTF-8 with BOM?

Filip K. ( 2018-02-03 22:07:22 +0200 )edit

@hamsterbacke sql_database="commhistory.db" should use full path for your script to work, it should be full path unless your current working directory is /home/nemo/.local/share/commhistory
sql_database="/home/nemo/.local/share/commhistory/commhistory.db"
After changing sql_database= to full path the script works.

abc123 ( 2019-11-16 15:54:50 +0200 )edit
5

answered 2014-08-23 19:23:52 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

updated 2014-08-23 19:27:42 +0200

ssahla gravatar image

Here's a variation of the above script to dump all your sms conversations to one text file.

It outputs lines in format [number] ([date and time]): [message], replacing [number] with "Me" when the message is from you.

#!/bin/bash

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

# My name, as it will appear in the output
me="Me"

# The database with the SMS messages
sql_database="/home/nemo/.local/share/commhistory/commhistory.db"

# The SQL command to select which messages to retrieve
#    remoteUid : The contact's phone number
#    direction : Who sent the message, can be 1 (them) or 2 (me)
#    startTime : When the message was sent
#    freeText  : The text of the actual message
#    type      : Messages are of type 2
sql_command="SELECT remoteUid, direction, startTime, 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

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

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

    if [ "$format" == "CORRECTFORMAT" ]; then

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

        # Get which one is the sender, me or my contact
        fromwhom=`echo $line | cut -d '|' -f 2`
        if [ $fromwhom -eq 1 ]; then
            fromwhom="$contactno"
        else
            fromwhom="$me"
        fi

        # Convert the Unix timestamp to a human readable format
        unixtime=`echo $line | cut -d '|' -f 3`
        datetime=`date -d@${unixtime} "+%Y-%m-%d %H:%M"`

        # Get the actual text message
        message=`echo $line | cut -d '|' -f 4`

        # Copy all the information into a new file
        echo "$fromwhom (${datetime}): $message"

    else

        # ...Fallback, just copy the line
        echo "$line"

    fi

done
edit flag offensive delete publish link more

Comments

I copy all text in a new file called 'sms-dumb' without extension. Paste it on /home/nemo/Documents ( is this an error ? ) Open terminal, going to the same directory So, which command should i type ? For backup all text message ( i'll do my selection after .. )

malibu1106 ( 2014-12-03 13:59:04 +0200 )edit
2

You copy the text to a file with .sh extension, for example sms-dump.sh, and place the while in Documents (for example). Then in Terminal you go the same directory and make the script executable with the command chmod 755 sms-dump. Now you can run the script with command sms-dump. However, this will output all the messages to the screen, so if you want them saved to a file, you run it with command sms-dump > allmessages.txt (for example), which will output all the messages to a file called allmessages.txt.

ssahla ( 2014-12-03 14:56:18 +0200 )edit

Thanks for ur answer. So i rename my file into "sms-dump.sh" i do "chmod 755 sms-dumb.sh" And tryed "sms-dump > sms.txt" It's saying "bash: sms-dump: command not found" But the .txt file is created, and empty. I'm using this script " #!/bin/bash

A script to dump all text messages to stdout

#

Usage:

sms-dump-all ... "

I tryed to put "-all" it doesn't work too .. I'm sorry about asking it to you, it's a quite difficult for me, don't know bash usage .. :(

malibu1106 ( 2014-12-03 15:13:45 +0200 )edit

@malibu1106 The script can not be found because it's not in one of the search paths. Try ./sms-dump > sms.txt (notice the ./ at the beginning). Another way would be to put the script in the directory /home/nemo/bin, then you can start it without the ./ at the beginning.

Yo ( 2014-12-03 15:28:53 +0200 )edit

Thanks another time. It's not working, i don't understand what is the search path. Should i move my script into another folder ?

malibu1106 ( 2014-12-03 15:31:08 +0200 )edit
5

answered 2016-05-19 14:35:32 +0200

iite gravatar image

I was moving out of Jolla (to Android/CyanogenMod), and wanted to take my SMS with me as well. I couldn't get the Groovy Version working, because my Jolla failed at the "#!/usr/bin/env groovy" part, not installed or somesuch.

So I had to use the version above it, which output all of my 2000+ SMS, but it had the MultiLine SMSs stored faultily as commented by the poster, the following lines as comments.

Hand-editing all of them into place neatly wasn't going to happen... (Tried,but after 10 edits I was like "there has gotta be a better way").

Ended up making the following perl script to do the job for me!

The earlier dumped XML file of my SMSs and the new script both on my linux box, did "./sorter", since that's the name I gave my wee script, and a fraction of a second later there was another version of the XML. Moved it to the phone, and clicked "Restore" from SMS Backup & Restore, and it found my backup and... ...failed to restore 16 SMS, but restored all the others. So I'm happy with this.

And if anyone else is doing something similar, aka "moving out of Sailfish", here's the perl script I used.

#!/usr/bin/perl

my @rivit = `cat all-messages1.xml`; # Your sms archive (will remain untouched)
my $filu = 'sms-backup-'.time().'.xml'; # File to create with the new multiline-supported SMSs



## No need to touch anything below


my $viime = shift(@rivit); # Priming the 'lastline' reference.

## Declaring all the rest of the string storages
my $alku;
my $keski;
my $loppu;
my $kesken;


## Opening the OutPut File

open(DAT,">>$filu") || die("Cannot Open Output File");


foreach $rivi (@rivit) {

   if ( ($rivi =~ /^<\!--/) and ($viime =~ /" toa=/) ) {
   # If the new line belongs to the lastline

    ($alku,$keski) = split(/" toa=/,$viime,2);
    $kesken = $alku.'&#10;';    # Prime the new MultiLineSms with the meta data from the beginning...
    $loppu = '" toa='.$keski;   # ...And store the trailing metadata here.

   } elsif ($rivi =~ /^<\!--/) { 
   # If the newline STILL should be appended to the previous line(s)...

    $viime =~ s/<\!--//;    # Remove comment-begin
    $viime =~ s/-->/&#10;/; # Substitute comment-end with NewLine Escape
    chomp($viime);      # Remove trailing NewLineChr
    $kesken .= $viime;  # Append to the building-up MultiLineSMS

   } elsif (length($kesken)>1) { 
   # And if not, did we just compile a new multi-line sms?

    $viime =~ s/<\!--//;        # Remove comment-begin
    $viime =~ s/-->/&#10;/;     # Substitute comment-end with NewLine Escape
    chomp($viime);          # Remove trailing NewLineChr
    $kesken .= $viime.$loppu;   # Append, and include the trailing MetaData

    $viime = $kesken;   # Move the completed MultiLine SMS to the lastline reference
    $kesken = '';       # clear it

   } else { 
   # This isn't required, ha. 
   # If we have no modified lastline in memory and the newline is a normal / new line.

   }

   # Store lastline unless we're currently compiling a MultiLine SMS...
   if (length($kesken)<1) {  print DAT $viime ; }
   $viime = $rivi;  # Next!
}

print DAT $viime ;  # The last line of the XML needs to be printed too
close(DAT);
print("\n ALL DONE! \n");

And despite the preview-mode seems to be confused by the quotes there which aren't escaped, it still works. ;P Cheers! I'm a happy camper. ( And a happy ex-sailor? :P )

edit flag offensive delete publish link more
4

answered 2018-03-01 14:45:38 +0200

kelvan gravatar image

I wrote an implementation in Python based on the bash script. I'm currently testing it.

https://github.com/kelvan/jollasms

edit flag offensive delete publish link more
1

answered 2020-06-01 16:04:06 +0200

zackbuffo gravatar image

I tried the multiline-capable bash script which seemed to work fine (didn't do the SMS import yet). As far as I observed the resulting content on the console, I'm missing something around the two last days, which are just a couple of messages.

Looking at the comhistory.db file on my Jolla phone, it is dated May 30 (two days ago) and the additional commhistory.db-shm file is dated as of just now.

My assumption is, that thecommhistory.db-shm file holds the latest messages, Can someone confirm this? If so, is this a solvable problem to add the option to also cover the remaining messages.

Another approach to solve this would be to trigger storage of ALL messages in the comhistory.db file by phone/app.

Is this possible?

edit flag offensive delete publish link more

Comments

1

Ok, I solved this myself by trial and error. What I did to have easy access from script to db, I copied the comhistory.db to my Linux system. As I didn't know about the purpose of the additional shm and wal file, I left them on the phone.

Solution is, when moving the db, to copy all three comhistory files to the new location. I assume sqlite3 knows how to handle them. Finally pretty easy.

zackbuffo ( 2020-06-01 16:44:05 +0200 )edit
0

answered 2014-12-03 16:18:40 +0200

malibu1106 gravatar image

I tryed a lot of things ! But still don't understand what is not working ( i hope it's not a noob error i do .. ) image description

I tried with and without the .sh extension because i don't understood why you say me to name the file as "sms-dump.sh" and chmod without it !

Hope the screenshot will help u .. and me :o Thanks a lot to help me ..

edit flag offensive delete publish link more

Comments

1

My mistake in the chmod command, should have been chmod 755 sms-dump.sh, with the extension.

But that's not the problem here. It seems that there are ctrl-M (^M) characters in the end of the lines in your script (see the error you're getting: /bin/bash^M: bad interpreter), making the script disfunctional. Ctrl-M characters are appended to a file when it's transferred from Windows to UNIX/Linux.

For some ways to strip the ^M characters, see http://linuxdemon.blogspot.fi/2011/04/how-to-remove-control-m-characters-in.html

For example, enter cat sms-dump.sh | col -b > sms-dump2.sh. The new file sms-dump2.sh should be stripped of Ctrl-M's. (I haven't tried that though, just copied that from the above link.) Then you make the new file executable in the same way as you did with the others and try again. :)

ssahla ( 2014-12-03 20:28:57 +0200 )edit
1

Okay .. i just tried. It seems to work, no error, and file is coming bigger ;) So .. THANKS A LOT ! I'm very happy to see that there's always someone to help another one. A last " Excuse my english " :)

malibu1106 ( 2014-12-04 23:15:41 +0200 )edit

Ok, great if it works now :) It takes a while for the script to finish.

ssahla ( 2014-12-04 23:17:34 +0200 )edit

Can i help u for something ?

malibu1106 ( 2014-12-04 23:21:55 +0200 )edit

No need :)

ssahla ( 2014-12-04 23:24:50 +0200 )edit
Login/Signup to Answer

Question tools

Follow
28 followers

subscribe to rss feed

Stats

Asked: 2014-08-22 18:50:00 +0200

Seen: 8,540 times

Last updated: Jun 01 '20

Related questions

How user can take backups out of device [released]

Howto import sms from nokia n9 [duplicate]

sms: call contact from incoming SMS message [answered]

Can I import my contacts from N9? [answered]

User wants to see certain part of picture as a Lock Screen background [answered]

We want to create our own TOH [answered]

What is the best maps/navigation application to use on Jolla? [answered]

How to change Ambiences really fast [answered]

Markdown basics [not relevant]

How to remove all contacts in one go? [answered]

Copyright © Jolla Ltd. 2013-2019. All rights reserved.
about | faq | help | privacy policy | terms of service | give feedback
Powered by Askbot version 0.7.49