webOS Nation Forums >  webOS Developers >  webOS Development > Extract full SMS log as a text file (from PalmDatabase.db3 using sql)
Extract full SMS log as a text file (from PalmDatabase.db3 using sql)
  Reply
 
Thread Tools Display Modes
Old 10/12/2009, 08:00 PM   #1 (permalink)
Member
 
Posts: 4
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:
Quote:
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)
...
Code:
#!/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
movingcranium is offline   Reply With Quote
Old 10/14/2009, 11:32 AM   #2 (permalink)
Member
 
Posts: 33
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*.
peng1can is offline   Reply With Quote
Old 10/14/2009, 02:23 PM   #3 (permalink)
Member
 
Laxidasical's Avatar
 
Posts: 573
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???
__________________
MoBill - Use your Authorize.net account to bill your customers with your webOS device!!
MoJack - Track your lost or stolen webOS device from anywhere!
Time to get VIRAL
Laxidasical is offline   Reply With Quote
Old 10/14/2009, 05:15 PM   #4 (permalink)
Member
 
SirWill's Avatar
 
Posts: 439
Quote:
Originally Posted by Laxidasical View Post
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.

Last edited by SirWill; 10/14/2009 at 05:35 PM.
SirWill is offline   Reply With Quote
Old 10/14/2009, 06:39 PM   #5 (permalink)
Member
 
Laxidasical's Avatar
 
Posts: 573
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.
__________________
MoBill - Use your Authorize.net account to bill your customers with your webOS device!!
MoJack - Track your lost or stolen webOS device from anywhere!
Time to get VIRAL
Laxidasical is offline   Reply With Quote
Old 10/14/2009, 06:44 PM   #6 (permalink)
Member
 
danno1769's Avatar
 
Posts: 244
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
danno1769 is offline   Reply With Quote
Old 10/15/2009, 01:45 PM   #7 (permalink)
Member
 
Posts: 4
Quote:
Originally Posted by peng1can View Post
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 is offline   Reply With Quote
Old 10/15/2009, 01:47 PM   #8 (permalink)
Member
 
Posts: 4
Quote:
Originally Posted by peng1can View Post
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...
movingcranium is offline   Reply With Quote
Old 02/13/2010, 05:25 PM   #9 (permalink)
Member
 
Posts: 1
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:

Code:
#!/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
audaciousbeaver is offline   Reply With Quote
Old 02/14/2010, 05:03 PM   #10 (permalink)
Member
 
Posts: 4
Nice! You dug deeper into the db schema than I did.
movingcranium is offline   Reply With Quote
Old 02/14/2010, 09:53 PM   #11 (permalink)
sko
Member
 
Posts: 68
Turn into a simple app ^_^
sko is offline   Reply With Quote
Old 03/05/2010, 05:34 PM   #12 (permalink)
Member
 
Posts: 84
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.
lakaw is offline   Reply With Quote
Old 03/22/2010, 04:17 PM   #13 (permalink)
Member
 
Posts: 1
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!
dleeabtech is offline   Reply With Quote
Old 03/22/2010, 09:21 PM   #14 (permalink)
Member
 
DrewPre's Avatar
 
Posts: 818
There are GUI SQLite Admin consoles that will extract tables to csv or excel. I'm just sayin.
__________________

Palm Pre Backup Utility...done!
Locate Pre....done!
DrewPre is offline   Reply With Quote
Old 04/10/2010, 11:41 AM   #15 (permalink)
Member
 
Posts: 5
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.
alexoliveira is offline   Reply With Quote
Thanked By: wado1
Old 04/10/2010, 07:02 PM   #16 (permalink)
Member
 
Posts: 3
I agree with Alex, we need that app.
wado1 is offline   Reply With Quote
Old 11/03/2010, 07:47 PM   #17 (permalink)
Member
 
Posts: 1
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.
wvolz is offline   Reply With Quote
Old 02/19/2011, 02:48 PM   #18 (permalink)
Member
 
Posts: 7
No Takers?
Barona4 is offline   Reply With Quote
Old 11/02/2011, 12:16 AM   #19 (permalink)
Moderator
 
Gaurav's Avatar
 
Posts: 8,277
<bump>
__________________
_________________
aka Gfunkmagic

Current device: Palm Pre
Device graveyard: Palm Vx, Cassiopeia E100, LG Phenom HPC, Palm M515, Treo 300, Treo 600, Treo 650, Treo 700p, Axim X50v, Treo 800w



Please don't PM me about my avatar. For more info go here.

Restore your Pre to factory settings using webos doctor and follow these instructions
Gaurav is offline   Reply With Quote
Old 11/03/2011, 07:32 AM   #20 (permalink)
Member
 
Posts: 500
I am interested in this as I want to transfere all my SMSs from Treo680 to this. Down to work I guess !
CvvB is offline   Reply With Quote
Reply

 

Tags
backup, sms, sqlite, text

Thread Tools
Display Modes



 


Content Relevant URLs by vBSEO 3.6.0