Hi!
I need to manualy edit sms.db in nate's true 1.1.3, but after running an UPDATE query, i get a "no such function: read" (running "UPDATE message SET text='someText'".
tried also via SQLite Database Browser and sqlite3 command line util
any idea why?

thanks in advance.

Forget about changing the database manually.
In 1.1.3 they put the triggers on the tables which use internal functions of sms framework.
So if you try changing values by hand, outside of SMS app, those functions are not defined and they will fail. If they fail, any operation you doing is cancelled.
The only way is to delete the triggers, apply the changes and then recreate the triggers. And that way has also catch22. If the triggers are not enabled, the content of the tables will go out of sync and you'll start running into various problems (sms out of sync, negative numbers of unread messages).

Is there another way of changing the sms database?

thanks a lot.

Not really

Originally Posted by matan.elgabsi

Is there another way of changing the sms database

Kind-of.. Not sure if it'll help you, but I noticed that if you place an old (pre-1.1.3) SMS.db onto your iPhone, it gets converted to the "new format" the next time you launch MobileSMS.

So if you have an backup from before you upgraded, maybe you could modify that one instead

With that, you could dump the database to a .sql file, chop off the 'CREATE TRIGGER' lines, import it to a new database, run the 'delete' queries, then swap the two around.
I know, not the greatest method, but it does work.

The only issue I ran into when doing that was that even after restarting the springboard, it said I had 80 new message (when I really only had 2, and both were read).
Getting past that was relatively simple too.

Code:

"UPDATE msg_group SET unread_count = 0 WHERE unread_count > 0;"

Hope this helps.

:Edit:
After doing exactly this, and even writing a script to take care of some of it for me, I realized that I'm not getting new messages, so it's sort of self defeating.

If anyone knows where I can get the 1.1.2 version of text.app, I'd be more than happy to test swapping the applications. I kinda need the ability to mass delete the emails (clearing 170/day manually sucks).

Hi,
is there a solution for this?
i get the same error as well,.. with INSERT and DELETE

The problem stems from the fact they're using triggers with apparently a C wrapper from somewhere that provides read(). Basically, all this does is look at message.flags to see if the second bit is set (so far as a I can tell). So it's very easy to simply drop and re-create the trigger without it and still have the same functionality.

Code:

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;

Once you do that, messages still work and you can modify the database freely from sqlite as you see fit. The catch to the db on anything post-1.1.2 is that the tables are now more interconnected and dependent on each other. So if you want to change the "from" phone number, you now actually need to change it in two tables for it to appear the way you want. Whats more is that you can no longer simply change the 'address' field on in message to have all those texts sorted into a conversation, you have to put them all in the same group_id from group_member.

Bottom line is, it's more of a pain but it has taught me some neat things about triggers so after a few late night hours of struggles, I've gotten my e-mail SMS renumbering system to work better than pre-1.1.3 where I was doing an sql batch from crontab every 5 minutes. This new way also has the added benefit of saving battery power from not running cron frequently and even displaying the changed "from" phone number in the SMS pop-up.

Here is an example trigger of what I'm using to renumber my e-mail SMSs into a single conversation, it's pretty self explanatory but basically just replace '55512345567' with the number you want the messages to appear from and 'domain.com' with that each message contains (most likely the from e-mail address).

Code:

create trigger update_emailsms after insert on message begin
insert or replace into group_member (ROWID, group_id, address) values (1, 1, 5551234567);
insert or ignore into msg_group (ROWID, type, newest_message, unread_count) values (1, 0, 1, 0);
update message set address = 5551234567, group_id = 1 where text like '%domain.com%';
update msg_group set newest_message = (select max(ROWID) from message where group_id = 1) where ROWID = 1;
update msg_group set unread_count = (select count() from message where flags = 0 and group_id = 1) where ROWID = 1;
delete from group_member where group_id not in (select group_id from message);
delete from msg_group where ROWID not in (select group_id from group_member);
end;

Lastly, during my experimentation, I restarted Springboard after modifying the triggers on the DB. I'm not positive but it seemed like changes didn't work until I did.

I hope this helps out anyone that was doing something similar to me. I spent many a frustrated hour searching the 'net for somebody that figured this stuff out to no avail. Good luck!

I'm getting the same error.

Dunno how to fix it without deleting/creating triggers each time..

aaronjang deal it in his application : WeTools
(http://www.hackint0sh.org/forum/showthread.php?t=17010)

I asked him, no response for the moment.

Nobody found how to fix it ?

Originally Posted by exd

I'm getting the same error.

Nobody found how to fix it ?

You totally didn't read anything I posted...

Anyway, here's a revision of the custom trigger I posted above... fixes some unread numbering problems...

Code:

create trigger update_smsemail after insert on message begin
insert or replace into group_member (ROWID, group_id, address) values (1, 1, 5551234567);
insert or ignore into msg_group (ROWID, type, newest_message, unread_count) values (1, 0, 1, 0);
update message set address = 5551234567, group_id = 1 where text like '%domain.com%';
update msg_group set newest_message = (select max(ROWID) from message where group_id = 1) where ROWID = 1;
update msg_group set unread_count = (select count() from message where flags = 0 and group_id = 1) where ROWID = 1;
delete from group_member where group_id not in (select group_id from message);
delete from msg_group where ROWID not in (select group_id from group_member);
insert or ignore into msg_group (ROWID, type, newest_message, unread_count) values (new.ROWID, 0, (select max(ROWID) from message where group_id = new.group_id), (select count() from message where flags = 0 and group_id = new.group_id));
end;

create trigger cleanup_msggroup after insert on msg_group begin
delete from msg_group where ROWID not in (select group_id from group_member);
end;