Notes on the iPhone Databases

The iPhone uses sqlite database to store its data. There doesn't seem to be too much documentation available on the structure of the tables or the triggers so below are some notes that I gathered during the Ndara iSMS Backup (http://code.google.com/p/ndara/) R&D phase.

Database Diagrams

Below are some database diagrams that could help: sms.db call_history.db Calendar.sqlitedb AddressBook.sqlitedb AddressBookImages.sqlitedb

Retrieving the Databases

To access the databases install OpenSSH on the iPhone using Cydia. Install WinSCP on your windows machine. Get the IP address of the iPhone and SSH into the phone with the default username root and default password alpine. Detailed Instructions: http://www.hackthatphone.com/2x/open_ssh.html The databases are located at /private/var/mobile/Library. Click into the sub folders to find the related databases.

SMS.db : Message Table

I see a lot of queries regarding the triggers on the message table of the SMS database. Below are some details on the table: Fields

Field	Data type	PK
ROWID	integer	Y
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
subject	text
country	text
headers	blob
recipients	blob
read	integer

Indexes

Index	Fields
message_flags_index	flags
message_group_index	group_id, ROWID
message_groupid_read_index	group_id, read

Triggers

Trigger	Events	Type	When expression
delete_message	DELETE	After	NOT read(old.flags)
delete_newest_message	DELETE	After	old.ROWID = (SELECT newest_message FROM msg_group WHERE ROWID =old.group_id)
delete_pieces	DELETE	After
insert_newest_message	INSERT	After	new.ROWID >= IFNULL((SELECT newest_message FROM msg_groupWHERE ROWID = new.group_id), 0)
insert_unread_message	INSERT	After	NOT read(new.flags)
mark_message_read	UPDATE	After	NOT read(old.flags) AND read(new.flags)
mark_message_unread	UPDATE	After	read(old.flags) AND NOT read(new.flags)

SMS.DB Message Table Definition

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,
 subject         text,
 country         text,

 headers         blob,
 recipients      blob,
 read            integer
);

CREATE INDEX message_flags_index
 ON message
 (flags);

CREATE INDEX message_group_index
 ON message
 (group_id, "ROWID"); 

CREATE INDEX message_groupid_read_index
 ON message
 (group_id, read);

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 delete_pieces
 AFTER DELETE
 ON message
BEGIN DELETE from msg_pieces
where old.ROWID == msg_pieces.message_id;
END;

CREATE TRIGGER insert_newest_message
 AFTER INSERT
 ON message
 WHEN new.ROWID >= IFNULL((
SELECT newest_message FROM msg_group
WHERE ROWID = 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;