SMS cleanup script for the iPhone
As previously promised, here is the python script I am currently using on my phone to keep the SMS database and application all nice and tidy. You can also download it here.
#! /usr/bin/env python
from sqlite3 import dbapi2 as sqlite
import os, shutil
max_messages = 400
q_del_nagios = "DELETE FROM message WHERE text LIKE 'FRM:nagios%'"
q_big_group = """
SELECT group_id, count(*) FROM message
GROUP BY group_id HAVING count(*) > ?
"""
q_find_address = """
SELECT address FROM message
WHERE group_id = ? AND address IS NOT NULL
LIMIT 1
"""
q_del_messages = """
DELETE FROM message WHERE group_id = ?
AND date <= (
SELECT date FROM message WHERE group_id = ?
ORDER BY date DESC LIMIT 1 OFFSET ?
)
"""
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
if os.uname()[4].find('iPhone') == 0:
sms_db = '/var/mobile/Library/SMS/sms.db'
else:
sms_db = '/tmp/sms.db'
backup_db = '/tmp/sms.old.db'
# make a backup just in case
shutil.copyfile(sms_db, backup_db)
db = sqlite.connect(sms_db)
# register the user-defined function used by delete trigger
db.create_function('read', 1, message_read)
c = db.cursor()
print "Deleting Nagios text messages..."
c.execute(q_del_nagios)
print " %d messages deleted." % c.rowcount
print "Getting groups with > %s messages..." % max_messages
c.execute(q_big_group, (max_messages,))
groups = c.fetchall()
for row in groups:
group = row[0]
count = row[1] - max_messages
c.execute(q_find_address, (group,))
address = c.fetchone()[0]
print "Removing %d old messages for group %s (address %s)..." \
% (count, group, address)
c.execute(q_del_messages, (group, group, max_messages))
print " %d messages deleted." % c.rowcount
db.commit()
print "Vacuuming..."
c.execute("VACUUM");
# vim:set ts=4 sw=4 ai et tw=80:
So what does this do? The first thing it runs is a delete for all Nagios monitoring messages I get from work. The slightly more complicated part is cleaning up messages with popular contacts. By adjusting the max_messages variable, you can purge the oldest texts with a contact when the total message count exceeds the given value. I rarely (never?) delete all messages with a contact on my phone because I like having the context around, so this satisfies that need for me while still giving me a way to clean up texts I will never read again.
See Also
- iPhone SMS database hacking - March 28, 2009
- iPhone OS 3.0 SMS database layout changes - September 4, 2009
- Django Proxy Models - August 1, 2009
- python-pgpdump, a PGP packet parser library - March 8, 2012
- Unstated coding style - December 21, 2011