Page 1 of 2 12 LastLast
Results 1 to 20 of 26
  1.    #1  
    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)
    ...
    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
  2. #2  
    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*.
  3. #3  
    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
  4. SirWill's Avatar
    Posts
    439 Posts
    Global Posts
    492 Global Posts
    #4  
    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 06:35 PM.
  5. #5  
    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
  6. #6  
    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
  7.    #7  
    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.
  8.    #8  
    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...
  9. #9  
    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
  10.    #10  
    Nice! You dug deeper into the db schema than I did.
  11. sko
    sko is offline
    sko's Avatar
    Posts
    68 Posts
    Global Posts
    109 Global Posts
    #11  
    Turn into a simple app ^_^
  12. lakaw's Avatar
    Posts
    84 Posts
    Global Posts
    244 Global Posts
    #12  
    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.
  13. #13  
    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!
  14. DrewPre's Avatar
    Posts
    818 Posts
    Global Posts
    829 Global Posts
    #14  
    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!
  15. #15  
    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.
  16. wado1's Avatar
    Posts
    3 Posts
    Global Posts
    78 Global Posts
    #16  
    I agree with Alex, we need that app.
  17. #17  
    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.
  18. Barona4's Avatar
    Posts
    7 Posts
    Global Posts
    8 Global Posts
    #18  
    No Takers?
  19. #19  
    <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
  20. #20  
    I am interested in this as I want to transfere all my SMSs from Treo680 to this. Down to work I guess !
Page 1 of 2 12 LastLast

Tags for this Thread

Posting Permissions