Extracting mail from database
Chris McCormick
Recently my mail server was wiped off the planet. I have backups from
one week ago, and luckily k9-mail had just checked mail moments before
the server went offline. So I have the latest mails in my inbox and
work-inbox sitting on my phone. What is the best way to get those
mails back in mbox (text) format so I can put them on my new server?
I have previously had to do something similar retrieving contacts off
my old Android phone and I used sqlite3 directly on the database. If I
have to do that any tips on what kind of queries I should be executing
to get the mail in the rawest text format possible?
Cheers,
Chris.
Dennis Rockwell
> I have previously had to do something similar retrieving contacts off
> my old Android phone and I used sqlite3 directly on the database. If I
> have to do that any tips on what kind of queries I should be executing
> to get the mail in the rawest text format possible?
A quick browse of /data/data/com.fsck.k9/databases (I'm rooted) shows a pretty simple layout, but you'll have to glue the parts back together with the appropriate MIME boilerplate and send them to another server. The headers, body parts, and attachments are all broken apart.
Somebody familiar with the perl sqlite, MIME, and SMTP packages could put something together in a day or three.
Of course, the more interesting path (for me, anyway) would be to rewrite the account database to indicate that those messages belong on a different server. I don't know if k-9 could deal with a client out of sync like that, though. Use a throwaway account and backup frequently.
Now, if K-9 could just move email from one account to another, you'd be set. I used Thunderbird to move several gig from my old work account to gmail, one folder at a time.
Dennis
Sent from my Nook Color with K-9 Mail. Please excuse my brevity.
Chris McCormick
On Sat, Oct 29, 2011 at 9:51 PM, Dennis Rockwell <prr6...@gmail.com> wrote:
> Chris McCormick <mcco...@gmail.com> wrote:
>> I have previously had to do something similar retrieving contacts off
>> my old Android phone and I used sqlite3 directly on the database. If I
>> have to do that any tips on what kind of queries I should be executing
>> to get the mail in the rawest text format possible?
>
> A quick browse of /data/data/com.fsck.k9/databases (I'm rooted) shows a pretty simple layout, but you'll have to glue the parts back together with the appropriate MIME boilerplate and send them to another server. The headers, body parts, and attachments are all broken apart.
>
> Somebody familiar with the perl sqlite, MIME, and SMTP packages could put something together in a day or three.
In the end I got access to the server where my old mails were and
managed to recover everything. Hooray!
Before that happened I mostly had the extraction working from the k9
database file. See the attached Python script for anyone else who ever
needs to recover email text from the k9 database. The script could be
much improved to support attachments and multipart HTML emails and I
have attached another script which shows how to do that but I hadn't
had time to integrate them before I got access again to my old mail.
Hope this helps someone one day.
Cheers,
Chris.
Dennis Rockwell
> In the end I got access to the server where my old mails were and
> managed to recover everything. Hooray!
Well, that's the best answer, isn't it? Great!
> Before that happened I mostly had the extraction working from the k9
> database file.
Excellent! I'm stashing this away for a rainy day. I sometimes miss my years of using MH, with complete access to everything.
Thanks for the update, and for sharing your work!
Anand C Rockzz
Just wanted to let you know that your sleep-less-night's effort did help "me" and that one day is "today" :)
Thanks again !!
Alexey Mochkin
Can anybody help me to modify this script or write new for parsing modern format of k9mail database.
Regards,
Alexey.
Alexey Mochkin
Alexey Mochkin
That's a pity, that nobody reply to my help request :-(
I wrote Perl script for myself and it restored 6482 email from k9 sqlite database.
Script(translator) actual only with current K9-Mail database schema and its usage by real app code.
As I can see, script can restore any kind of messages(multiparts ans others) include attachments which were downloaded by K9-Mail client.
Result is mbox files, one per folder. This mbox files I successfully imported to Thunderbird and then moved to my IMAP server.
Script distributed AS-IS.
p.s. script may have huge output, I recommend to redirect it to log file.
p.p.s. script is very slow... As I can understand it is due to Mail::Header Perl module and "magic" Perl "open" function...
Regards,
Alexey (aka Alukardd)