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
FieldData typePK
ROWIDintegerY
addresstext
dateinteger
texttext
flagsinteger
replaceinteger
svc_centertext
group_idinteger
association_idinteger
heightinteger
UIFlagsinteger
versioninteger
subjecttext
countrytext
headersblob
recipientsblob
readinteger
Indexes
IndexFields
message_flags_indexflags
message_group_indexgroup_id, ROWID
message_groupid_read_indexgroup_id, read
Triggers
TriggerEventsTypeWhen expression
delete_messageDELETEAfterNOT read(old.flags)
delete_newest_messageDELETEAfterold.ROWID = (SELECT newest_message FROM msg_group WHERE ROWID =old.group_id)
delete_piecesDELETEAfter
insert_newest_messageINSERTAfternew.ROWID >= IFNULL((SELECT newest_message FROM msg_groupWHERE ROWID = new.group_id), 0)
insert_unread_messageINSERTAfterNOT read(new.flags)
mark_message_readUPDATEAfterNOT read(old.flags) AND read(new.flags)
mark_message_unreadUPDATEAfterread(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;

1 comments:

Ethan Smith said...

Buy & sell new and used iPhones.
Wide selection of new and used iPhones for sale.
More at buy iphone used