script to delete all iphone sms messages
Posted on July 28, 2008
Filed Under iphone | 16 Comments
my coworker, dan smith, recently expressed frustration with apple’s lack of bulk message deletion in the iphone sms app. sometimes our monitoring scripts go a little crazy and send hundreds of emails to us when things go wrong and its extremely tedious to delete these extra useless messages one at a time.
after jailbreaking my iphone and installing several of the available packages i was poking around the filesystem and looking at the source code to the example python application. the gui stuff is beyond me for now but the sqlite access caught my eye – from /Applications/HelloPython.app/HelloPython.py (with the iphone-python package installed through cydia):
...
from sqlite3 import dbapi2 as sqlite
...
db = sqlite.connect(self.userHomeDirectory() + "/Library/AddressBook/AddressBook.sqlitedb")
...
next i did a search of /var/mobile for any other sqlite databases:
cd /var/mobile
find . -name '*.sqlitedb'
this returns:
./Library/AddressBook/AddressBook.sqlitedb
./Library/AddressBook/AddressBookImages.sqlitedb
./Library/Caches/MapTiles/MapTiles.sqlitedb
./Library/Calendar/Calendar.sqlitedb
hmm…no sms database. poking around /var/mobile/Library i found the SMS directory. in there is a file called “sms.db”. at first i thought it was a berkley database or something but i wasn’t able to open it using python’s anydbm module so i tried the “file” command:
iPhone:/var/mobile/Library/SMS root# file sms.db
sms.db: SQLite 3.x database
sweet – its an sqlite database after all. searching for other files ending with a .db in /var/mobile reveals several other sqlite databases including call_history.db, notes.db, voicemail.db, and databases for several of the installed applications.
using sqlite’s command line utility i inspected the schema:
iPhone:/var/mobile/Library/SMS root# sqlite3 sms.db
SQLite version 3.5.4
Enter ".help" for instructions
sqlite> .tables
_SqliteDatabaseProperties message
group_member msg_group
sqlite> .schema message
CREATE TABLE message (ROWID INTEGER PRIMARY KEY AUTOINCREMENT, address TEXT, date INTEGER, text TEXT, flags INTEGER, replace INTEGER, svc_center TEXT, group_id INTEGER, association_id INTEGER, height INTEGER, UIFlags INTEGER, version INTEGER);
CREATE INDEX message_flags_index ON message(flags);
CREATE INDEX message_group_index ON message(group_id, ROWID);
CREATE TRIGGER delete_message AFTER DELETE ON message WHEN NOT read(old.flags) 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;
CREATE TRIGGER delete_newest_message AFTER DELETE ON message WHEN old.ROWID = (SELECT newest_message FROM msg_group WHERE ROWID = old.group_id) BEGIN UPDATE msg_group SET newest_message = (SELECT ROWID FROM message WHERE group_id = old.group_id AND ROWID = (SELECT max(ROWID) FROM message WHERE group_id = old.group_id)) WHERE ROWID = old.group_id; END;
CREATE TRIGGER insert_newest_message AFTER INSERT ON message WHEN new.ROWID >= IFNULL((SELECT MAX(ROWID) FROM message WHERE message.group_id = new.group_id), 0) BEGIN UPDATE msg_group SET newest_message = new.ROWID WHERE ROWID = new.group_id; END;
CREATE TRIGGER insert_unread_message AFTER INSERT ON message WHEN NOT read(new.flags) 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 read(old.flags) AND read(new.flags) 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 read(old.flags) AND NOT read(new.flags) 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;
the hairy thing about this is that there are triggers on the table for deletes. a “select * from message;” shows all sms messages so i first tried the easy route:
sqlite> delete from message;
SQL error: no such function: read
looking more closely at the triggers i saw where it was calling the read() function in the triggers. googling for “sqlite user defined functions” provided some explanation on what they are and how to define them. one of the results showed an example in python.
i did some experiments and found that the flags column appears to be a bitmap and that the second bit indicates whether a message has been read or not. so the triggers update an unread message count in another table on deletion, the flags column is updated, or when a row is inserted.
so here’s my script to delete all sms messages:
#! /usr/bin/env python
from sqlite3 import dbapi2 as sqlite
def message_read(flags):
"""reimplementation of an sqlite user defined function called by a trigger
on the messages table.
the trigger checks the message flags to see if a message has been read to
see if the counter of unread messages in another needs to be updated when
a message is deleted.
"""
# second bit is the "was read" flag
return (int(flags) & 0x02) >> 1
db = sqlite.connect('/var/mobile/Library/SMS/sms.db')
# register the user-defined function used by delete trigger
db.create_function('read', 1, message_read)
c = db.cursor()
c.execute('delete from message;')
db.commit()
# vim:set ts=4 sw=4 ai et tw=80:
you can download the source here.
i’m not much of a gui programmer and have no experience at all with apple’s cocoa interface or objective-c so for now this is just a command-line script. i hope to spend some time learning how to use pyobjc and working with the cocoa libraries to make an interface for it.
its easy enough to run the script through the mobile terminal app after copying the script to the iphone, chmod 755 delete_sms.py, then run it:
./delete_sms.py
no output from the script is a good thing. if something goes wrong it should spew several lines of errors describing what happened.
Comments
16 Responses to “script to delete all iphone sms messages”
Leave a Reply
Hi,
this is interesting, i found also some informations on this database.
I have a GSM which is paid by my company….but only for professionnal calls.
For my private calls i have to put ‘2′ befor the number for that call to appear on my private bill.
This means that i never have the sms and answers on the same display.
No way : i open the database, delete the triggers (yeah this is a bit more brute than what you did), reorder the sms, insert the triggers back.
And that’s it.
But…….i have to reboot the iphone.
If i don’t do this reboot new messages are not inserted in the database, and i can’t send messages.
Any idea ?
have you tried doing an update instead of deleting the rows? you could either loop through the rows or you might be able to use a single statement if there’s some sort of substring function for mysql. i’ll see if i can come up with something.
Hi,
I have used some of your code to write a program that fixes the infamous “unread SMS count is always 1″ bug:
iPhone SMS Fixer – Fix wrong unread SMS count on an iPhone
===============================================================================
This program can fix a “corrupt” SMS database on an iPhone, which always
shows an unread message count of 1 (or possibly more). This issue seems to
happen quite frequently with the “missed call” SMS notifications sent by
Vodafone Italia and other operators. It seems present in all versions of the
iPhone firmware released so far (2.1 is the latest, at the time of writing).
See http://www.sukkology.net/blog/2008/10/09/iphone-sms-fixer/ for more details.
Because of the triggers does it mean that we can’t insert messages into the database?
I have years worth of SMS’s from old phones I’d like to insert to my iPhone, but is it possible?
you should be able to insert with the user defined ‘read’ function enabled. so it probably means you’d need to use python or another language with a robust sqlite interface instead of loading them using the sqlite cli.
Hi, I don’t know the meaning of UIFlags of message
Table, Do you know How to use the UIFlags ?
[...] days when I did a lot of stuff, and feel like I learned a lot, but I’m back where I started. This great post talks more about the SMS database and its table structure, from someone having the exact same [...]
[...] Bitmap, dessen zweites Bit das Flag “read” darstellt. Nach langem suchen habe ich ein Script gefunden, das alle SMS pauschal löscht. Zwei kleine Anpassungen (fett) und schon klappts sowohl mit [...]
Gracias por este aporte, estoy teniendo problemas con el OS 3, y esto evitó que tuviera que restaurar una vez más mi iPhone
Hey, just wanted to let you know that grabbing this script and modifying it helped me a *lot* with a very similar issue (my phone was flooded with 100+ SMS!). I basically made it delete if a certain word (which no one else ever said to me) was found in the SMS message rather than just deleting them all. Lo and behold, I reload SMS, and I still have every other message except for the ones that I got flooded with! Thanks very much for posting this. :)
SQL error: no such function: read
I am having same problem when i want to delte or insert in sms.db with a gui sqlite databae editor
i get hte error :
SQL error: no such function: read
I know nothing about programming and havent understood anything posted by author, is their any easy way to do it please ? My database is corrupted i think becuase of last SMS received, and i cant use SMS app in my iphone, i need to delte some last SMS from database
Anyone have solution how to delete them please ?
thanks in advance
which editor are you using? it might be tough to setup so it can properly edit sms.db because it needs to define that ‘read’ function with sqlite before the edits will work.
are you copying sms.db to your pc & running the editor there? i could probably write a quick script to do what you want if you don’t mind installing python on your pc.
Is there any way to access call history?
yeah, the call history database is at:
/var/mobile/Library/CallHistory/call_history.db
its also an sqlite database file
woah, my name’s dan smith and I’m also fustrated I can’t bulk delete my iphone sms’s.