Page 1 of 2 12 LastLast
Results 1 to 10 of 11
Discuss [SMS] Manualy editing sms.db on Nate's True 1.1.3 at the Tools - Hackint0sh.org; Hi! I need to manualy edit sms.db in nate's true 1.1.3, but after running an ...
  1. #1
    Newbie Array

    Join Date
    Jan 2008
    Posts
    3
    Post Thanks / Like
    Downloads
    0
    Uploads
    0
    Rep Power
    0

    Default [SMS] Manualy editing sms.db on Nate's True 1.1.3

    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.
    Last edited by matan.elgabsi; 01-31-2008 at 08:46 PM.



  2. #2
    Senior Professional Array piranha's Avatar

    Join Date
    Aug 2007
    Posts
    241
    Post Thanks / Like
    Downloads
    0
    Uploads
    0
    Rep Power
    27

    Default

    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).

  3. #3
    Newbie Array

    Join Date
    Jan 2008
    Posts
    3
    Post Thanks / Like
    Downloads
    0
    Uploads
    0
    Rep Power
    0

    Default

    Is there another way of changing the sms database?

    thanks a lot.

  4. #4
    Senior Professional Array piranha's Avatar

    Join Date
    Aug 2007
    Posts
    241
    Post Thanks / Like
    Downloads
    0
    Uploads
    0
    Rep Power
    27

    Default

    Not really

  5. #5
    Newbie Array

    Join Date
    Sep 2007
    Location
    Kolbotn/Oslo, Norway
    Posts
    9
    Post Thanks / Like
    Downloads
    0
    Uploads
    0
    Rep Power
    0

    Lightbulb

    Quote Originally Posted by matan.elgabsi View Post
    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


  6. #6
    Newbie Array

    Join Date
    Feb 2008
    Posts
    1
    Post Thanks / Like
    Downloads
    0
    Uploads
    0
    Rep Power
    0

    Default

    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).
    Last edited by namenlos; 02-15-2008 at 05:34 PM.

  7. #7
    Senior Professional Array

    Join Date
    Oct 2007
    Posts
    119
    Post Thanks / Like
    Downloads
    0
    Uploads
    0
    Rep Power
    12

    Default

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

  8. #8
    Rookie Array

    Join Date
    Apr 2008
    Posts
    18
    Post Thanks / Like
    Downloads
    0
    Uploads
    0
    Rep Power
    0

    Default

    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!

  9. #9
    exd
    exd is offline
    Newbie Array

    Join Date
    Apr 2008
    Posts
    2
    Post Thanks / Like
    Downloads
    0
    Uploads
    0
    Rep Power
    0

    Default

    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 ?

  10. #10
    Rookie Array

    Join Date
    Apr 2008
    Posts
    18
    Post Thanks / Like
    Downloads
    0
    Uploads
    0
    Rep Power
    0

    Default

    Quote Originally Posted by exd View Post
    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;


 

 
Page 1 of 2 12 LastLast

Similar Threads

  1. Diff b/w Nate true (1.1.3) vs itunes (1.1.3) ?
    By rajeevsharma8888 in forum iPhone "2G" (Rev. 1)
    Replies: 2
    Last Post: 02-14-2008, 04:40 PM
  2. Dev Team or Nate True 1.1.3 Jailbreak?
    By SpyHunter39 in forum General
    Replies: 12
    Last Post: 01-28-2008, 07:52 PM
  3. Replies: 21
    Last Post: 01-27-2008, 03:02 PM
  4. 1.1.3 - Nate True Hoax?
    By ddms in forum General
    Replies: 12
    Last Post: 12-31-2007, 08:04 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Powered by vBulletin®
Copyright © 2014 vBulletin Solutions, Inc. All rights reserved.
Search Engine Friendly URLs by vBSEO
(c) 2006-2012 Hackint0sh.org
All times are GMT +2. The time now is 03:27 PM.
twitter, follow us!