[clug] extracting mail from android k9mail

[Eyal Lebedinsky]

I used my tablet for email (K-9 Mail) on my recent travels and wanted to move the folders
back to my pc. Rather than grabbing some app (I rarely install any) I spent a short time
figuring it and writing a tiny script to do this.

My SQL foo is minimal (at best) so my script is probably as far from efficient or nice
as possible, but it worked for me. I expect a few lines of SQL can do this in one go...

cheers

#!/bin/bash

# [28 Mar 16 EL] Extract mail from 'K-9 Mail' (on a Samsung tablet) for thunderbird (on a PC).
#       Written by Eyal Lebedinsky <eyal at eyal.emu.id.au>
#       This is placed in the public domain
#
# The folder on the tablet (Galaxy Tab S, Android 5.0.2) is here (copy the *.db file over):
#       /media/SAMSUNG/Tablet/Android/data/com.fsck.k9/files/
#
# One issue is that the Content-Type header says
#       Content-Type|multipart/alternative; boundary="----HEDOZKSBML476WOO33I7HERCM4MCNU"
# which thunderbird does not like so it is replaced with
#       Content-Type: text/plain; charset=utf-8; format=flowed

# Copy any of the created files (e.g. Inbox.mbox) to the thunderbird mail directory
#   [renamed! to something else e.g. 'Tablet-Inbox'] will show it after tb restarts.

process_headers() {
         echo "From -" \
                 `sqlite3 "$db" "select value from headers where message_id=$mid and name='Date'"`

         sqlite3 "$db" "select name,value from headers where message_id=$mid" | \
                 sed 's/[|]/ /' | \
                 while read hname htext ; do
                         test 'Content-Type' = "$hname" && {
                                 echo 'Content-Type: text/plain; charset=utf-8; format=flowed'
                                 continue
                         }
                         echo "$hname: $htext"
                 done
}

process_message() {
         process_headers

         echo

         sqlite3 "$db" "select text_content from messages where id=$mid"
}

process_folder() {
         echo "Folder '$fname'" # (id $fid)"

         sqlite3 "$db" "select id from messages where deleted=0 and empty=0 and folder_id=$fid" | \
                 while read mid ; do
                         process_message
                 done >"$fname.mbox"
}

process_db() {
         sqlite3 "$db" "select name,id from folders" | \
                 sed 's/[|]/ /' | \
                 while read fname fid ; do
                         process_folder
         done
}

db="$1"
test -n "$db" || db='bb2af05c-5a58-4418-a827-f4b304a58df7.db'

process_db


-- 
Eyal Lebedinsky (eyal at eyal.emu.id.au)