Daily exporting of all of your messages in a compact textual archive

Show and Tell
1 

#!/bin/sh

cat_db() {
  local DB PROF
  for DB in \
    $HOME/snap/deltachat-desktop/current/.config/DeltaChat/accounts/*-*/dc.db
  do
    PROF="`printf -- %s "$DB" | sed -r "s~^.*/([^/]+)/dc[.]db$~\1~"`"

cat << EOF |
.separator "&&"

select
 '${PROF}' as profile,
 m.chat_id as chat_id,
 m.id as m_id,
 datetime(m.timestamp, 'unixepoch') as date,
 replace(replace(replace(replace(replace(
  case when instr(concat(char(10),m.param),concat(char(10),'S=30'))>0 then m.param else m.txt end,
  '&','&amp;'),'\"','&quot;'),'<','&lt;'),'>','&gt;'),char(10),'<br>') as data,
 case
  when instr(concat(char(10),m.param),concat(char(10),'S=30'))>0 then 2
  when instr(concat(char(10),m.param),concat(char(10),'S=32'))>0 then 1
  else 0 end as xdc,
 coalesce(c.fingerprint, '') as fingerprint,
 m.timestamp as timestamp
from msgs as m join contacts as c
on m.to_id==c.id
or m.to_id==0 and c.id==1
where from_id==1
and deleted==0
and (
 hidden==0
 and instr(concat(char(10),m.param),concat(char(10),'S='))==0
 or 1==$NEEDWEBXDC
  and (
   instr(concat(char(10),m.param),concat(char(10),'S=30'))>0
   or instr(concat(char(10),m.param),concat(char(10),'S=32'))>0
  )
)
order by m.chat_id, m.id
;

EOF
    sqlite3 -csv -batch "$DB"
  done |
  sed -r '
    s~(&)""(&)~\1\2~g
    s~"(.)~\1~g
  '
}

main() {
  local DIR OUTALL NEW
  [ -n "$NEEDWEBXDC" ] || NEEDWEBXDC=1
  readonly DIR="$HOME/cache/chat/delta-chat"
  readonly OUTALL="$DIR/mymsgall2.csv.gz"
  mkdir -p "$DIR" || exit 1
  readonly NEW="`mktemp --suffix="dcmsg.tmp.csv.gz"`"

  {
    cat_db

    zcat "$OUTALL" 2>/dev/null
  } |
  sort -u --compress-program=gzip |
  gzip -9 > "$NEW"

  mv "$NEW" "$OUTALL"
}

main "$@"

Related

2 Likes
Advanced contact-adding tutorial