Migrate your SMS from Palmdatabase.db3 to WebOS 2

[rcmarvin]

New Approach: https://bitbucket.org/rcmarvin/sms.d...a44d6d91f0.zip
Old Approach: https://bitbucket.org/rcmarvin/sms.d...01bd2c4b2a.zip

Tools required:
1. PalmDatabase.db3 to allow SMS to be exported using sqlite3.exe (included in the package)
2. Windows XP or newer
3. Developer mode + WebOS SDK or novacom to enable terminal access, transfer files from and to WebOS device
4. Firefox 3.6 or newer to run JS to do the conversion from db3 format to db8 format
m
Remarks:
My Pre- CPU (@500Mhz) can process about 100 message per minute using the new approach.

Warning:
Large number of message may cause database full error. (feedback of seguardado and Alaaddin)

Changes:
14 Sep 2011
Decrease message import from 100 to 25 at a time to avoid error (feedback of JSladen)

21 Aug 2011
Fix the SMS status that may contain cause pending message

7 Jun 2011
New approach: It seems that WebOS db8 should have some build-in mechanism to process imported message, this should fix the redundant conversation thread problem

25 May 2011
Update guide according to feedback of linwiz311
The SQL file is also modified (credit: linwiz311)

Background:
I've been looking for way to migrate my old Pre- SMS to the newer WebOS 2.1, tried to search in the forum, and there are only a few thread talk about it and there seems no solution at that moment. Also, as the structure of WebOS 2.1 is changed, it is not possible to use external tools to open the db file like before. However, after the Impostah from WebOS Internals is out, it is possible to study the structure of message in db8, with the help of WebOS Internals Wiki and Palm Developer Documentation, I have found out a way to transfer WebOS 1.4.5 sms from PalmDatabase.db3 to WebOS 2.1. Obviously it is still in very alpha stage and I can only confirm it is working for my device and on the emulator only, so it may not work for others. But I think my experience may provide some hints for others to do the conversion, any volunteers are welcome to try and see if it work with their data in the WebOS emulator.

[boarder1999]

This would totally answer the question I've posted to multiple forums about transferring data from a Pre- to a Pre3 when it launches (with no replies, positive or negative).

It sounds like you've only tested this on an emulator, so I'd be very interested to hear from someone who got it working on an actual phone. If it works even marginally well, I will be frankenPre-ing ASAP to be ready for the Pre3 when it comes.

[Rnp]

Hummm... there is some chance to work from Treo 680 to webOS?????

I have 6 years transfering my SMS from old to new cell/smartphone, but the Palm OS never gave me some option to send my messages back to SimCard, and I don't wanna to lose!

The story of my courtship, marriage, pregnancy of my daughter and everything else in this period are there; so, is VERY important to me! Serious!

If I can import my old SMS from my Treo or from the Treodesktop program, I leave the Palm OS and stay only with webOS...


Best Regards...

[rcmarvin]

Originally Posted by Rnp

Hummm... there is some chance to work from Treo 680 to webOS?????

I have 6 years transfering my SMS from old to new cell/smartphone, but the Palm OS never gave me some option to send my messages back to SimCard, and I don't wanna to lose!

The story of my courtship, marriage, pregnancy of my daughter and everything else in this period are there; so, is VERY important to me! Serious!

If I can import my old SMS from my Treo or from the Treodesktop program, I leave the Palm OS and stay only with webOS...


Best Regards...

There is a utility to convert sms message from PalmOS format to CSV format. In theory it is possible to do the conversion but I have not studied any of that. The converted data format is actually quick different from what in Web OS 1.4.5, and it really require extra effort to convert it for OS 2.1.

[Rnp]

Thanks for reply.

I'll gonna test and see the results, and after I leave here!


Best Regards...

[linwiz311]

Hi rcmarvin,

Unfortunately, it did not work well for me. I managed to get it to export from the palm database and load in Firefox with a few modifications that I've attached to this post. However, it had errors creating the ins2.sh part -- "Cannot match old ChatThread with current db8 ChatThread". Let me know if you have any ideas on how to fix this and thanks for all the work you've done!

[rcmarvin]

Originally Posted by linwiz311

Hi rcmarvin,

Unfortunately, it did not work well for me. I managed to get it to export from the palm database and load in Firefox with a few modifications that I've attached to this post. However, it had errors creating the ins2.sh part -- "Cannot match old ChatThread with current db8 ChatThread". Let me know if you have any ideas on how to fix this and thanks for all the work you've done!

Just wanna know the following points:
1. Are there any contacts inside the WebOS devices?
2. Are there any message on the device before running the script?
2. Is ins1.sh (step 3) created properly in Firefox? After you transferred the script to the device, any error occurred during the execution of ins1.sh?
3. Have you transferred the ct_current.json.js back to host after the execution of ins1.sh?

[linwiz311]

Originally Posted by rcmarvin

Just wanna know the following points:
1. Are there any contacts inside the WebOS devices?
2. Are there any message on the device before running the script?
2. Is ins1.sh (step 3) created properly in Firefox? After you transferred the script to the device, any error occurred during the execution of ins1.sh?
3. Have you transferred the ct_current.json.js back to host after the execution of ins1.sh?

1) I'm actually trying to transfer my texts from a PalmDatabase.db3 file from a Pre- to a new Palm Pre 2 on a different profile. The new Pre 2 has most of my contacts (the ones from Google), but does not have the ones in my Palm Profile. However, the number that it had the error with is in my Google contacts and therefore was on the Pre 2.

2) There was one text on the new device before I ran the script
2.5) Ins1.sh was created and seemed to work when I ran it on device. I had to make sure to change all the window line endings to unix line endings.

3) Yep. Transferred it and replaced the one that was created by the bat file.

[Rnp]

Hi, rcmarvin.

Well, I have the .CSV file from Palm OS messages.

Now, can you explain how can I to convert from ".CSV" to ".DB3"?


Best Regards...

[linwiz311]

Hi rcmarvin,

Thanks again for all your work on this patch. I was finally able to transfer my texts. My issue was that I did not know I needed to have my phone connected before running the batch file as it retrieves data from the webos 2.0 device. The only thing I've noticed is that group texts seem to be repeated for each person the text was sent to. Also, it looks like there are frequent heading changes in the messaging program with the same number in a different format for each text i.e. 1630-XXX-XXXX or 630-XXX-XXXX.

[Rnp]

Well, rcmarvin...

I exported my message database from Treo 680 to a ".CSV" file. But I don't know what to do now... can you give me a help? Sorry for my newbie question! :P


Best Regards...

[boarder1999]

Rnp, I don't think you are going to be able to get your SMS database onto WebOS. I know when I went from a treo 755p I was only able to back up the texts to a text file.

The .CSV is just a text file that has the entries separated by commas. I think it is only for SMS messages, too, whereas the WebOS db3 database contains contact list information, SMS, IM convos, etc. I would highly, highly doubt there is any way you could possibly get the .CSV converted to the db3, and even less chance of converting it to db8, short of going into the database and manually entering all of those conversations contact by contact, message by message.

You do have a backup of all those SMS messages now, though... just not on your phone in the messaging app. You can probably convert that file from .CSV to plain text and load it on your phone to be viewable with a text viewer app. You might be able to find a program that converts the .CSV into html to be viewable by a web browser, but I don't know anything about that.

You have to ask yourself whether you really need access to 8 years of old texts on your phone or whether just having them backed up and readable on your computer (or possibly on your phone just not in the SMS app) is good enough.

Everyone who got an iPhone lost all their texts at first. Everyone who got an Android phone did as well. Everyone who got the first Pre lost them and will lose them a second time when they go to Pre2/Pre3.

[Rnp]

boarder1999, Thanks for your tips.

Well, I know that is not easy, but... I want to try!

Everyone who got an iPhone lost all their texts at first. Everyone who got an Android phone did as well. Everyone who got the first Pre lost them and will lose them a second time when they go to Pre2/Pre3.

So, for this details that my Treo 680 is my "official" smartphone until today...

But I want to change this, and this is my "final frontier"!

Thanks again.


Best Regards...

[boarder1999]

Well, there are no tools for you to do this conversion. You will have to open up the database, figure out how it links contacts and messages and time they were sent, then type in every single text message by hand. I'm going to guess that 6 years of SMS messages will take you a very long time indeed to type in.

Good luck.

The tool this thread is discussing is a way to convert from one database that is already linked to this information to another database that is linked in a similar way.

[rcmarvin]

Originally Posted by Rnp

Well, rcmarvin...

I exported my message database from Treo 680 to a ".CSV" file. But I don't know what to do now... can you give me a help? Sorry for my newbie question! :P


Best Regards...

I've looked at the result of the CSV and given the big difference between the structure of th CSV and db3, I have no easy way to do the conversion. In the current moment, I think I will work on the db3 and db8 conversion, until more people confirm that the conversion instruction indeed work for them. Sorry about that.

[rcmarvin]

Originally Posted by linwiz311

Hi rcmarvin,

Thanks again for all your work on this patch. I was finally able to transfer my texts. My issue was that I did not know I needed to have my phone connected before running the batch file as it retrieves data from the webos 2.0 device. The only thing I've noticed is that group texts seem to be repeated for each person the text was sent to. Also, it looks like there are frequent heading changes in the messaging program with the same number in a different format for each text i.e. 1630-XXX-XXXX or 630-XXX-XXXX.

If you didn't connect the device before running step3, I would recommended you to clear all the thread and start it all over again (I can PM you the command when you needed).
And I'm not sure I understand the meaning of header changes. Do you mean the header that contain both phone number and the date of message received? If that's the case, what is the correct number pattern? Is that 1630-XXX-XXXX? Sorry, I'm not familiar with phone number on another region.

[linwiz311]

Originally Posted by rcmarvin

If you didn't connect the device before running step3, I would recommended you to clear all the thread and start it all over again (I can PM you the command when you needed).
And I'm not sure I understand the meaning of header changes. Do you mean the header that contain both phone number and the date of message received? If that's the case, what is the correct number pattern? Is that 1630-XXX-XXXX? Sorry, I'm not familiar with phone number on another region.

Sorry about that. My last post was a bit vague. There seems to appear to be a difference between the numbers in a sent vs received sms. For sent sms, the address includes the country code (+1 in the US) before the number, which looks like +1-XXX-XXX-XXXX, while the received sms only include the number XXX-XXX-XXXX. Looking through the sms.js code, I think lines 299 & 307-308 may be why. Is there a reason why sent addresses are normalized but not the received addresses? This may be creating the disparity.

I still haven't figured out why group texts are repeated for each person it was sent to, but I'm also looking into that. It looks like this is happening with the initial extract from the palm databse, so I'm thinking it has to do with the db3 sql statement.

[rcmarvin]

Originally Posted by linwiz311

I still haven't figured out why group texts are repeated for each person it was sent to, but I'm also looking into that. It looks like this is happening with the initial extract from the palm databse, so I'm thinking it has to do with the db3 sql statement.

I thought this error happened because the device wasn't connected when you were running the first two steps.

Originally Posted by linwiz311

... Is there a reason why sent addresses are normalized but not the received addresses? This may be creating the disparity.

However, the address error is not related with that. Actually I was just mimic the behavior of what normal WebOS 2.1 will do in my test. In my region, when my phone operator sent me SMS, the incoming address is not normalized, but when SMS is sent out, the address may have normalized value in database. My current implementation of normalized address is just taking out the last 8 digital of the original address. I will try sending to +1 address and see if I get different result.

[linwiz311]

I thought this error happened because the device wasn't connected when you were running the first two steps.

Actually, I had the "Cannot match old ChatThread with current db8 ChatThread" error because I didn't have the device connected. Once I connected it, I was able to successfully transfer all my texts. However, for texts I sent to multiple people, it appears to be repeated in the new database for each person (and the number of repeats match the number of people the text was sent to). For example, if I sent a text to 5 friends, then in Friend 1's conversation, I see the text repeated 5 times. And this is the same with the other 4 friends. Judging by the output in the message.json file, I think the query is what is causing the repeated group texts. It's not really a major issue, but I think it's something that is fixable. I'm still trying to understand that query -- it's so complicated with all the inner joins lol. It's been a while since I've done sophisticated SQL like that.
For the normalization issue, I'm not exactly sure what the correct behavior on webos 2.0 in the US. Maybe someone who sends and receives texts with webos 2.0 can look in Impostah and let us know? I still haven't upgraded my Pre- yet...

One more question: Does this program combine texts that are already in the db8 database to the old ones you're trying to insert? Or does it replace current texts in the db8 database with items from the db3 database?

I don't know if you've talked to Rod or Jason, but I'm sure they can probably take some of this work and integrate it into Save/Restore, Impostah or WQI since most of the script is written in javascript. This would make it more accessible to people... and I'm sure a lot of people will find this work very useful.

[rcmarvin]

Originally Posted by linwiz311

... However, for texts I sent to multiple people, it appears to be repeated in the new database for each person (and the number of repeats match the number of people the text was sent to).

Haven't thought about that, yeah I think it is probably caused by that.

Originally Posted by linwiz311

... For the normalization issue, I'm not exactly sure what the correct behavior on webos 2.0 in the US.

I've tested on emulator and found more details. I think the phone number display problem may not related with the normalizedAddress. I've tried to insert the message with different addr and normalizedAddress value (e.g. 22345678 & 45677) and found out that "(223) 45678" is displayed in the end. It is more noticeable if you change the phone number regional settings from US to Others. The phone number display will become "22345678" instead of "(223) 45678".

Originally Posted by linwiz311

... Or does it replace current texts in the db8 database with items from the db3 database?

It does not suppose to replace the current texts in the db8 database. It will try its best to see if any chat thread is already started for that person/number, and if existed it will then relate the old message with that thread.

The only problem I found in my new test is that if the phone number does not exist in the contact list, it may create yet another new thread, even if a chat thread already existed in db8. In that situation, no message is replaced either, but there is two thread for the same number. (i.e. one thread with old messages, one thread with new messages). The problem is also caused by the normalizedAddress. As I stated in last reply, my current normalizedAddress is just the last 8 digit of phone number, so the normalizedAddress of phone number 22345678 is still the same. But the correct normalizedAddress (of US region) should be 45678, and that's why my script think that there is not match for existing thread and create a new one.

I started looking into the original implementation of WebOS source and found out there is a list of phone number format defined for different region (/usr/palm/frameworks/globalization/version/1.0/formats), but the copyright of the script is reserved by Palm. I don't think I can just grab them and use.