Merge 2 SMS databases (sms.db) from 1 iPhone 3GS running on iOS 4.0.1 and putting the output file back on the iPhone without the SMS App crashing

Question

First of all, I've never really done any heavy programming, just fooling around a bit with Python. If any of my questions sound odd or so, please try to understand me.

I have a following problem:

My iPhone 3GS running on iOS 4.0.1 had problems with making outgoing calls. The only way to solve this problem was to restore iPhone to its factory settings. As it's obvious to iPhone owners, doing so I lost things such as my SMS messages history. I was able to retrieve the sms.db file from the backup by adding the *.db extension to a numerically named file containing the whole SMS history.

What I have now are 2 such SMS databases: The retrieved one and the one with new messages already on the phone. What I would like to do is to merge data from these 2 databases into one, single file (for the purpose of my goal my iPhone is, obviosly, jailbroken).

The iPhone sms.db database contains various tables, one of which is called "messages" and contains information such the telephone numbers, SMS message text, flags and so on. I was able to export the "message" table from the newer database into the older one by using the SQL Database Browser 2.0 b1, and calling it "message2". Later, having some problems with executing the

INSERT INTO

statement in SQLite Manager for Firefox, I found out that first I had to drop and recreate the triggers causing the

No such function: "read"

error by executing the following statement:

drop trigger insert_unread_message;
drop trigger mark_message_unread;
drop trigger mark_message_read;
drop trigger delete_message;
CREATE TRIGGER insert_unread_message AFTER INSERT ON message WHEN NOT new.flags = 2 BEGIN UPDATE msg_group SET unread_count = (SELECT unread_count FROM msg_group WHERE ROWID = new.group_id) + 1 WHERE ROWID = new.group_id; END;
CREATE TRIGGER mark_message_unread AFTER UPDATE ON message WHEN old.flags = 2 AND NOT new.flags = 2 BEGIN UPDATE msg_group SET unread_count = (SELECT unread_count FROM msg_group WHERE ROWID = new.group_id) + 1 WHERE ROWID = new.group_id; END;
CREATE TRIGGER mark_message_read AFTER UPDATE ON message WHEN NOT old.flags = 2 AND new.flags = 2 BEGIN UPDATE msg_group SET unread_count = (SELECT unread_count FROM msg_group WHERE ROWID = new.group_id) - 1 WHERE ROWID = new.group_id; END;
CREATE TRIGGER delete_message AFTER DELETE ON message WHEN NOT old.flags = 2 BEGIN UPDATE msg_group SET unread_count = (SELECT unread_count FROM msg_group WHERE ROWID = old.group_id) - 1 WHERE ROWID = old.group_id; END;

After all of this, I was able to finally add the newer SMS messages into the older database. This, however, didn't mark the end of different issues. First of all, the newer messages I added got placed at the top of the table (above the older ones) with numbers in the "ROWID" column lower than the older messages. The correct (and logical) format of the table should be for these newer messages to be placed at the very bottom of the table, having higher numbers assigned to them. I don't know what kind of SQL statement to execute to be returned only the "ROWID" column in the following format:

ROWID
1
2
3

and so on, these numbers being the ones assigned to the newer messages. I've tried to achieve this by doing the following:

SELECT * FROM message
WHERE ROWID='1' AND '2' AND '3'

and so on, but SQLite Manager returned only the row with the "1", not the rest. What sort of statement could be used to achieve what I want?

Also, when I SSH the edited sms.db I already have back on the iPhone, the SMS app crashes each time I compose a message and tap the "OK" button to send it (the length or any signs within the message don't matter). I've read that the SMS app may crash when it renders and displays some characters incorrectly (e.g. UNICODE). I found 1 SMS message in which the UNICODE characters were render incorrectly and corrected it manually, but the app still crashes after the correction. I'm just not sure if what I found was the only error with the rendering. Is there any way (either in SQL or other) to find only texts with such errors?

Besides, the SSH-ed sms.db operates a bit oddly, since it doesn't display all the messages in the list. I can find some of those it doesn't display by using the search function and typing a part of a message. Then iPhone finds it, displaying only the preview of the text without any name or phone number, but can't oen it when I tap on it. It either doesn't respond to the tapping at all or it redirects my to a different conversation history.

If anyone has more insight in modifying the sms.db or has any thoughts on what else may be causing the SMS app to crash, feel free to post what you have to say :)

EDIT: Just as I expected, assigning higher numbers to the newer messages and placing them at the bottom of te list worked, and now the conversations I cared the most about are all displayed :) Besides, the SMS app doesn't crash anymore :) The only problem I still have, however, is that there are some messages which aren't on the list while you view it, but are displayed when key words typed while searching match the contents of these messages. Just as it was before, They neither have no numbers while being displayed, nor can they be opened from the search view. As these messages in particular weren't important to me, I deleted them from the sms.db "message" table, and put back the altered file back onto the iPhone. Although these messages aren't in the sms.bd file anymore, they are still displayed while searching :/ It's not such a big problem, because everything else works again, but it's a bit irritating to have them in the search results. Does anyone have any idea how I could fix this?

Thank you for help I got so far, and also thank you for a bit more of it in advance :)

Answer 1

Hi i have 2 iphones 3gs: an old one where all my sms are and a new one without any usefull sms.

Both of them are jailbroken. I want to transfer the sms frome the old iphone to the new one (because i won't use the first iphone anymore).

I tried with cyberduck to copy the old sms database (/private/var/mobile/Library/SMS/) to the new iphone. But first i did not recover the old sms, and second i am not able to send or receive any sms with my new iphone. I red that i had to modify the permission to 777 into the sms.db file of the new iphone, but it didn't change anything.

Do you have an idea to allow me to recover the old database and to be able to use anew the sms on my new iphone?

Thanks

Regards Athime

Answer 2

I worked on the first problem and finally i manage to recover the old database on the new device However i still can not send/receive any sms Do you have an idea?

Answer 3

When you say

WHERE ROWID='1' AND '2' AND '3'

You actually are saying

WHERE (ROWID='1') AND ('2') AND ('3')

I suspect '2' and '3' are being interpreted as TRUE. Also, ROWIDs are supposed to be integers. Comparing them to strings works because sqlite3 automatically converts ints to strings.

You probably mean something like

WHERE ROWID IN (1,2,3)