Extract full SMS log as a text file (from PalmDatabase.db3 using sql)

[movingcranium]

I wrote a python script to extract SMS/text message conversations from the sqlite database on the Pre.

This isn't polished code, but it might help some people to understand how the tables work in the database.

You will need to get the .db3 file off the Pre (at /var/luna/data/dbdata I think). I just installed the Mojo SDK, connected with novaterm, then used scp on the Pre to copy the file to an internet-accessible computer over the data connection.

Also you will need the sqlite3 package for Python; this is very easy, easy_install sqlite3 at a shell prompt should get you it with no fuss.

Sample output:

Jib Jab: (2125551212)
Thu 16 Jul 09 02:24 Yo bizzatch
Thu 16 Jul 09 03:37 Come to mama
Wed 05 Aug 09 21:10 shoot. ur a crazy person

Person 2: (5125551212)
...

#!/usr/bin/env python

# extract SMS, write to std out

import os, sys, time
import sqlite3

DBFILE="PalmDatabase.db3"

# open database with sqlite3
if os.path.exists(DBFILE):
    conn = sqlite3.connect(DBFILE)
    c = conn.cursor()
else:
    print "No database"

classSent = 0;
classRcvd = 2;



# get all messages

currFrom = ()
recipDict = {}
nameDict = {}

for text, first, last, status, address, timeStamp in c.execute('''
    SELECT com_palm_pim_FolderEntry.messageText, 
           com_palm_pim_Recipient.firstName, 
           com_palm_pim_Recipient.lastName, 
           com_palm_pim_FolderEntry.status, 
           com_palm_pim_Recipient.address, 
           com_palm_pim_FolderEntry.timeStamp 
    FROM com_palm_pim_FolderEntry 
    JOIN com_palm_pim_Recipient 
      ON (com_palm_pim_FolderEntry.id = com_palm_pim_Recipient.com_palm_pim_FolderEntry_id) 
    ORDER BY com_palm_pim_FolderEntry.timeStamp'''):
    # get rid of crap rows
    if (text == '' or status is None 
        or (currFrom == () and first is None)):
        continue

    # now state machine, iterate through

    # save each
    if not recipDict.has_key(address):
        # no entries create lists
        recipDict[address] = []
        nameDict[address] = []

    recipDict[address].append((text, timeStamp))
    
    if not (first is None and last is None):
        nameDict[address].append((first,last))


# now extract from dictionaries and print to stdout
for number, textList in recipDict.iteritems():
    print '%s %s: (%s)' % (nameDict[number][0][0], nameDict[number][0][1], number)

    for text, timeStamp in textList:
        secs = int(timeStamp)/1000
        print '  %s %s' % (time.strftime("%a %d %b %y %H:%M", 
                                         time.gmtime(secs)),
                           text)
    print

[peng1can]

Thanks for working on this. It's only partly working for me, though. I backed up my entire device when I had to swap out my Pre a couple weeks ago, so it's *possible* that I've got a bad copy of the old PalmDatabase.db3 or that those messages really were missing on the old device, but it looks like large chunks of conversations are missing for some reason. Most often, my side of a conversation is missing, and sometimes chunks from both parties are missing.

I know python is touchy about spacing... is it possible that a problem with the copy & paste would have messed with this? Any other thoughts. I'll mess with some SQL queries myself here in a bit to see if I can confirm that the messages are in there *somewhere*.

[Laxidasical]

This is great work, but wouldn't it be easier to use Edit > Copy All (from within a messaging thread) and then past it into an e-mail to send to yourself???

[SirWill]

Originally Posted by Laxidasical

This is great work, but wouldn't it be easier to use Edit > Copy All (from within a messaging thread) and then past it into an e-mail to send to yourself???

It appears to not copy entire threads. the buffer is only so big. Also it doesn't copy the time stamps.

I have copied the db3 file to my pc, and use a graphical sqllite program to retrieve everything. In theory a process could be written for moving the db3 file to your PC, and then a script could be ran that would truncate all of the tables and then load all of the tables from a backup.

Hmmm, may have to explore this.

Update: One obstacle is that I have heard that database schema changes happen on revisions of the OS. An upgraded OS can have the columns in 1 order, and a webdoctored phone with the same os version can have the columns in a different order.

I see ways to backup the DB, and restore the DB using SQLITE3. I'm going to explore doing this I think.

[Laxidasical]

Gotcha! I only copied one thread as a test. I didn't notice that timestamps didn't show and it was only 15 or 20 messages long.

[danno1769]

If you guys find a way to backup texts and or create a basic text search within the messaging app, donations will be in order

[movingcranium]

Originally Posted by peng1can

Thanks for working on this. It's only partly working for me, though. I backed up my entire device when I had to swap out my Pre a couple weeks ago, so it's *possible* that I've got a bad copy of the old PalmDatabase.db3 or that those messages really were missing on the old device, but it looks like large chunks of conversations are missing for some reason. Most often, my side of a conversation is missing, and sometimes chunks from both parties are missing.

I know python is touchy about spacing... is it possible that a problem with the copy & paste would have messed with this? Any other thoughts. I'll mess with some SQL queries myself here in a bit to see if I can confirm that the messages are in there *somewhere*.

Well, it's complicated. I figured out the database schema just by trial and error. If you look at the code, what I do is pull out all the SMS message text, sorted by date. Then, I identify all the senders/receipients by the
com_palm_pim_Recipient.address field, which contains mobile phone numbers. I then look up the name of the sender/recip by their phone number.

One thing that could screw this up is if the address field contains parens or spaces or dashes: ie (212) 555 1212 is different than 2125551212.

this works for me so I won't be making changes now, but a little python hacking could fix this.

[movingcranium]

Originally Posted by peng1can

Thanks for working on this. It's only partly working for me, though. I backed up my entire device when I had to swap out my Pre a couple weeks ago, so it's *possible* that I've got a bad copy of the old PalmDatabase.db3 .

Also the schema could change between webos revisiions (mine is from 1.2.1) as a previous poster mentioned. The SMS messages are almost definitely there in the old database: try Firefox SQLite Manager to browse...

[audaciousbeaver]

I had a need to extract text messages from my pre's database and found this script. It is working with OS 1.3.5.1 but had a couple of issues, plus I wanted some more information. Here is the revised script if it helps anyone:

#!/usr/bin/env python

# extract SMS, write to std out

import os, sys, time
import sqlite3

DBFILE="PalmDatabase.db3"

# open database with sqlite3
if os.path.exists(DBFILE):
    conn = sqlite3.connect(DBFILE)
    c = conn.cursor()
else:
	print "No database"

classSent = 0;
classRcvd = 2;


# get all messages

currFrom = ()
recipDict = {}
nameDict = {}

for text, first, last, status, address, errorCode, timeStamp, smsClass, messageType in c.execute('''
    SELECT com_palm_pim_FolderEntry.messageText, 
           com_palm_pim_Recipient.firstName, 
           com_palm_pim_Recipient.lastName, 
           com_palm_pim_FolderEntry.status, 
           com_palm_pim_Recipient.address,
           com_palm_pim_Recipient.errorCode,
           com_palm_pim_FolderEntry.timeStamp,
           com_palm_pim_FolderEntry.smsClass,
           com_palm_pim_FolderEntry.messageType
    FROM com_palm_pim_FolderEntry 
    JOIN com_palm_pim_Recipient 
      ON (com_palm_pim_FolderEntry.id = com_palm_pim_Recipient.com_palm_pim_FolderEntry_id) 
    ORDER BY com_palm_pim_FolderEntry.timeStamp'''):
    # get rid of crap rows
    if (text == '' or status is None):
        continue

    # now state machine, iterate through

    # save each
    if not recipDict.has_key(address):
        # no entries create lists
        recipDict[address] = []
        nameDict[address] = []
  
    direction = "" 
    if not (messageType == "IM"):
        if (smsClass == classRcvd):
	    direction = "Received"
        else:
	    direction = "Sent"

    if messageType == "IM":
        errorCode = ""

    recipDict[address].append((text, timeStamp, direction, errorCode))
    
    if not (first is None and last is None):
        nameDict[address] = (first, last)


# now extract from dictionaries and print to stdout
for address, textList in recipDict.iteritems():
    if (nameDict[address]):
        print '%s %s: (%s)' % (nameDict[address][0], nameDict[address][1], address)

    for text, timeStamp, direction, errorCode in textList:
        secs = int(timeStamp)/1000
        print '  %s %s %s %s' % (time.strftime("%a %d %b %y %H:%M", 
                                         time.gmtime(secs)),
                           direction, errorCode, text)
    print

[movingcranium]

Nice! You dug deeper into the db schema than I did.

[skohcl]

Turn into a simple app ^_^

[lakaw]

Anyone know how to run this? I've extracted my .db3 file, downloaded Python 2.6.4. Am I supposed to copy / paste the code into the python shell, replacing DBFILE=... with the correct location of my db3 file?

Thanks in advance for any help thrown my way.

[dleeabtech]

Is there any python / perl /sql script that will do the Calendar/Memo and Contacts dump as well? Thanks for this Message dump scripts... it helps! Thanks!

[DrewPre]

There are GUI SQLite Admin consoles that will extract tables to csv or excel. I'm just sayin.

[alexoliveira]

I will literally pay anyone who can build an app that does this nice money.

Seriously. Sometimes for legal reasons you need to extract a txt thread with time stamps.

Considering the RASH of useless apps, how is it possible that no one will write this?

I'm not developer, the processes described here are WAY out of my comfort zone.

[wado1]

I agree with Alex, we need that app.

[wvolz]

I've built on the work of movingcranium and audaciousbeaver and expanded this scripts abilities. My work and the current version are up on github under wvolz/webos-scripts. I'd post a link but it looks like I can't do that yet.

New functions include the ability to extract the call logs, and separate dumps for im, mms, and sms messages.

[Barona4]

No Takers?

[Gaurav]

<bump>

[CvvB]

I am interested in this as I want to transfere all my SMSs from Treo680 to this. Down to work I guess !