直接用sqlite3处理iphone的短信

由于iphone用sqlite3数据进行短信的存储。可以用sqlite3对数据库进行直接操作，清理垃圾短信。

1. 用DiskAid连接iPhone,将 /private/var/mobile/Library/SMS/sms.db 文件下载到电脑里。
2. 用sqlite3打开数据库文件，

• 1. .table看到数据表
  2. select * from message
  3. delete from message where ROWID = xx; 提示 No such function: "read"

执行以下查询语句跳过触发器调用的read函数.欺骗一下数据库。

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;

就可以进行删除。

delete from message where xxxx

然后设置所有短信为已读

update msg_group set unread_count = 0

顺便整理一下

VACUUM;

还原触发器

drop trigger insert_unread_message;
drop trigger mark_message_unread;
drop trigger mark_message_read;
drop trigger delete_message;

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 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;

完成后将数据库文件传回手机。垃圾短信干净了。