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

ian · March 11, 2026, 4:29pm

#!/bin/sh

get_fingerprint() {
cat << EOF |
select fingerprint
from keypairs join public_keys
on keypairs.public_key == public_keys.public_key
order by is_default
desc
limit 1
;
EOF
    sqlite3 -csv -batch "$1"
}

get_messages() {
  local PRINT

  readonly PRINT="`get_fingerprint "$DB"`"
cat << EOF |
.separator "&&"

select
 '${PRINT}' 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,char(10)),concat(char(10),'S=30',char(10)))>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,char(10)),concat(char(10),'S=30',char(10)))>0 then 2
  when instr(concat(char(10),m.param,char(10)),concat(char(10),'S=32',char(10)))>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>0 and 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,char(10)),concat(char(10),'S='))==0
 or 1==$NEEDWEBXDC
  and (
   instr(concat(char(10),m.param,char(10)),concat(char(10),'S=30',char(10)))>0
   or instr(concat(char(10),m.param,char(10)),concat(char(10),'S=32',char(10)))>0
  )
)
order by m.chat_id, m.id
;

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

cat_db() {
  local DCDIR DB
  find ~ -mount -type d -name "DeltaChat" |
  while read -r DCDIR; do
    find "$DCDIR" -mount -type f -name "dc.db" |
    while read -r DB; do
      echo "$DB" >&2
      get_messages "$DB"
    done
  done |
  sort -u --compress-program=gzip
}

main() {
  local OUTDIR OUTALL NEW
  [ -n "$NEEDWEBXDC" ] || NEEDWEBXDC=1
  readonly OUTDIR="$HOME/cache/chat/delta-chat"
  readonly OUTALL="$OUTDIR/mymsgall-3.csv.gz"
  mkdir -p "$OUTDIR" || 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 "$@"

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

Related