Note: Before you continue, you will need to install sqlite3 package from Cydia.
Note: The following guide assumes you are logged in to your iPhone using an SSH client. Google: iPhone connect via SSH
iPhone uses SQLite3 databases to store most of the important data, which you can easily view/modify/manage using sqlite3 shell command.
The main databases are:
- Emails database: /private/var/mobile/Library/Mail/Envelope Index -
iPhone# sqlite3 /private/var/mobile/Library/Mail/Envelope\ Index
sqlite> pragma table_info(messages);
0|ROWID|INTEGER|0||1
1|remote_id|INTEGER|0||0
2|sender||0||0
3|subject||0||0
4|_to||0||0
5|cc||0||0
6|date_sent|INTEGER|0||0
7|date_received|INTEGER|0||0
8|sort_order|INTEGER|0||0
9|mailbox|INTEGER|0||0
10|remote_mailbox|INTEGER|0||0
11|original_mailbox|INTEGER|0||0
12|flags|INTEGER|0||0
13|read||0||0
14|flagged||0||0
15|deleted||0||0
16|size|INTEGER|0||0
17|color||0||0
18|encoding||0||0
19|content_type||0||0
- Calendar database: /private/var/mobile/Library/Calendar/Calendar.sqlitedb -
iPhone# sqlite3 /private/var/mobile/Library/Calendar/Calendar.sqlitedb
sqlite> pragma table_info(event);
0|ROWID|INTEGER|0||1
1|summary|TEXT|0||0
2|location|TEXT|0||0
3|description|TEXT|0||0
4|start_date|INTEGER|0||0
5|start_tz|TEXT|0||0
6|end_date|INTEGER|0||0
7|all_day|INTEGER|0||0
8|calendar_id|INTEGER|0||0
9|orig_event_id|INTEGER|0||0
10|orig_start_date|INTEGER|0||0
11|organizer_id|INTEGER|0||0
12|organizer_is_self|INTEGER|0||0
13|status|INTEGER|0||0
14|external_status|INTEGER|0||0
15|availability|INTEGER|0||0
16|privacy_level|INTEGER|0||0
17|external_tracking_status|INTEGER|0||0
18|external_id|TEXT|0||0
19|external_mod_tag|TEXT|0||0
20|external_id_tag|TEXT|0||0
21|external_delivery_source_id|TEXT|0||0
22|external_delivery_item_id|TEXT|0||0
23|external_rep|BLOB|0||0
24|response_comment|TEXT|0||0
- Phone numbers database: /private/var/mobile/Library/AddressBook/AddressBook.sqlitedb -
iPhone# sqlite3 /private/var/mobile/Library/AddressBook/AddressBook.sqlitedb
sqlite> pragma table_info(ABPerson);
0|ROWID|INTEGER|0||1
1|First|TEXT|0||0
2|Last|TEXT|0||0
3|Middle|TEXT|0||0
4|FirstPhonetic|TEXT|0||0
5|MiddlePhonetic|TEXT|0||0
6|LastPhonetic|TEXT|0||0
7|Organization|TEXT|0||0
8|Department|TEXT|0||0
9|Note|TEXT|0||0
10|Kind|INTEGER|0||0
11|Birthday|TEXT|0||0
12|JobTitle|TEXT|0||0
13|Nickname|TEXT|0||0
14|Prefix|TEXT|0||0
15|Suffix|TEXT|0||0
16|FirstSort|TEXT|0||0
17|LastSort|TEXT|0||0
18|CreationDate|INTEGER|0||0
19|ModificationDate|INTEGER|0||0
20|CompositeNameFallback|TEXT|0||0
21|ExternalIdentifier|TEXT|0||0
22|StoreID|INTEGER|0||0
23|DisplayName|TEXT|0||0
24|ExternalRepresentation|BLOB|0||0
25|FirstSortSection|TEXT|0||0
26|LastSortSection|TEXT|0||0
27|FirstSortLanguageIndex|INTEGER|0|2147483647|0
28|LastSortLanguageIndex|INTEGER|0|2147483647|0
- Call History database: /private/var/mobile/Library/CallHistory/call_history.db -
iPhone# sqlite3 /private/var/mobile/Library/CallHistory/call_history.db
sqlite> pragma table_info(call);
0|ROWID|INTEGER|0||1
1|address|TEXT|0||0
2|date|INTEGER|0||0
3|duration|INTEGER|0||0
4|flags|INTEGER|0||0
5|id|INTEGER|0||0
- SMS database: /private/var/mobile/Library/SMS/sms.db -
iPhone# sqlite3 /private/var/mobile/Library/SMS/sms.db
sqlite> pragma table_info(message);
0|ROWID|INTEGER|0||1
1|address|TEXT|0||0
2|date|INTEGER|0||0
3|text|TEXT|0||0
4|flags|INTEGER|0||0
5|replace|INTEGER|0||0
6|svc_center|TEXT|0||0
7|group_id|INTEGER|0||0
8|association_id|INTEGER|0||0
9|height|INTEGER|0||0
10|UIFlags|INTEGER|0||0
11|version|INTEGER|0||0
12|subject|TEXT|0||0
13|country|TEXT|0||0
14|headers|BLOB|0||0
15|recipients|BLOB|0||0
16|read|INTEGER|0||0
So, if we want to see their structure/contents, we can run a simple sqlite3 query from shell:
To get the tables contained in database "Envelope Index" (our emails database):
iPhone# sqlite3 /private/var/mobile/Library/Mail/Envelope\ Index
SQLite version 3.6.12
Enter ".help" for instructions
sqlite> .tables
mailboxes messages properties
message_data pop_uids threads
To see the table structure of table "messages":
sqlite> pragma table_info(messages);
0|ROWID|INTEGER|0||1
1|remote_id|INTEGER|0||0
2|sender||0||0
3|subject||0||0
4|_to||0||0
5|cc||0||0
6|date_sent|INTEGER|0||0
7|date_received|INTEGER|0||0
8|sort_order|INTEGER|0||0
9|mailbox|INTEGER|0||0
10|remote_mailbox|INTEGER|0||0
11|original_mailbox|INTEGER|0||0
12|flags|INTEGER|0||0
13|read||0||0
14|flagged||0||0
15|deleted||0||0
16|size|INTEGER|0||0
17|color||0||0
18|encoding||0||0
19|content_type||0||0
To get results of email messages that are not read, not deleted, ordered by descending order (latest first) , limited by 5 (5 latest results only):
sqlite> select * from messages where read='0' and deleted='0' order by ROWID desc limit 5;
To delete (permanently) the email message at row index 123:
sqlite> delete from messages where ROWID='123';
To change the message subject of the email message at row index 87:
sqlite> UPDATE messages set subject='sqlite test' where ROWID='87';
A similar usage with Objective-C / Cycript:
[[MailMessageLibrary defaultInstance] messagesMatchingQuery:@"select * from messages where read='0' and deleted='0' order by ROWID desc limit 5" options:nil];
More on sqlite queries: http://www.sqlite.org/docs.html
Elias Limneos
Posted on: 21-02-2010 17:07 by Elias Limneos
Post here any comments or questions you might have on the above.
Posted on: 01-03-2010 13:14 by George J
Excellent work! This was missing from like...everywhere.
a question: how can I access AddressBook\'s pictures?
There is a db AddressBookImages.sqlitedb which seems to store the contact\'s pictures.
Posted on: 01-03-2010 13:17 by Elias Limneos
iPhone:/User/Library/AddressBook root# sqlite3 AddressBookImages.sqlitedb
SQLite version 3.6.12
Enter \".help\" for instructions
sqlite> .tables
ABImage _SqliteDatabaseProperties
sqlite> pragma table_info(ABImage);
0|ROWID|INTEGER|0||1
1|record_id|INTEGER|0||0
2|format|INTEGER|0||0
3|crop_x|INTEGER|0||0
4|crop_y|INTEGER|0||0
5|crop_width|INTEGER|0||0
6|crop_height|INTEGER|0||0
7|data|BLOB|0||0
You will see that the data fields contains images in binary format. To see them you will need to open this data as image.
I will post a guide as a seperate chapter here, as well.
Posted on: 02-04-2010 15:53 by Raoul Teeuwen
Hi Elias.
Sorry for using this space, but could not find a more appropriate one or contact-info.
I just installed Cydget Element. THANKS for your work on that.
Based on replies of others i read, and on my own experience, i wonder whether there will be a next version, and whether that will include:
- having the choice of only choosing unread sms, instead of all
- having the choice on what number of calendar events are shown (like with SMS, email etc)
- having the option to shuffle/re-arrange the element-parts, so for instance you can show calendar events before email and sms
Anyhow, again: thanks for the app!!!
raoul.teeuwen at gmail
Posted on: 24-05-2010 04:37 by Horoscope911
Nice thanks dude!
Posted on: 24-05-2010 19:07 by WOWzers
Very nice work ! this is great
