#! /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: