Query Jolla's commhistory.db for SMS's

jolla-sms.sql

/* 
  jolla-sms.sql - Query Jolla's commhistory.db for SMS's. 

  - Shows sent/received from last 2 months, oldest to newest.
  - Messages are printed with newlines replaced by spaces. 

  ---

  Usage:
 
  1) Place this file in ~/.local/
 
  2) Put this function in your ~/.bashrc

    function smsread(){
        local SMS_SQL="$HOME/.local/jolla-sms.sql"
        local COMMHIST="$HOME/.local/share/commhistory/commhistory.db"
        cat "$SMS_SQL" | sqlite3 "$COMMHIST" -list -separator '|' -header | column -t -s '|'
    }

  3) Now you can run it via ssh:

    $ ssh nemo@jolla smsread 

  4) Vuala!

  ---

  Author: Markus H (MawKKe) ekkwam@gmail.com - 2016-02-16

*/


select 
    datetime(startTime, "unixepoch", "localtime") as timestamp,
    (case when direction = 1 then "RECV" else "SENT" end) as dir,
    printf("[%s]",remoteUid) as remote,
    replace(replace(freeText, char(13), ' '), char(10), ' ') as message
from 
    Events 
where 
    date("now","-2 month") < date(startTime,"unixepoch") 
and 
    type = 2
;