iPhone SQLite Database Structure

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:

 

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 15:07 by Elias Limneos

Post here any comments or questions you might have on the above.



Posted on: 01-03-2010 11: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 11: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 12: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 01:37 by Horoscope911

Nice thanks dude!



Posted on: 24-05-2010 16:07 by WOWzers

Very nice work ! this is great



Posted on: 01-02-2011 16:14 by سضكه

غضسسثق



Posted on: 01-01-2012 23:38 by Rogerplisk

Dear Elias,

very nice post. have u successfully retrieve the content from the \"Data\" variable, used to store the contacts photo?

I did a dump from Data field using the export function over the SQLITE and I got the content from binary in a hex ascii coded like format.

Any suggestion on how retrieve the data field?



Posted on: 02-04-2015 22:12 by Ace

Hi Elias
First I want to tell you I love audio recorder app
It's very helpful with my business calls.

There is one bug that will be great if you be able to fix
When I use the iOS feature for auto dial (pause , or wait ;) in my contact
the app does not record any of the call
Like this phone number -
03-939-2899,0017025697191#
(When you call through operator)



Posted on: 17-05-2015 16:05 by Tom

quick question - will Audiorecorder 2 record if I'm using a bluetooth headset?



Posted on: 20-06-2015 13:00 by Silviu Dascal

Dear Elias,
congrats on your Audio Recorder App. I have purchased it and it works fine.
One question : how can I extract the recorded calls from my Iphone ? thanks,
Silviu



Posted on: 20-07-2015 18:27 by Steven

I need your help. How to adjust the CALLBAR screen darker because its very hard to see the words now. Thanks



Posted on: 17-10-2015 06:12 by Valentin

Dear Elias,
I really like your application AudioRecorder (8). I would like to know when you intend to release the App for ios 9.0.2 iPhone 5s.
Sincerely, Valentin



Posted on: 22-10-2015 04:56 by Wilbert

Would my LivePhotos be deleted if I jailbreak to iOS 9



Posted on: 17-12-2015 05:58 by Grafic from France

Hi
I ve purchased a licence for your fzmiys Audiorecorder but impossible to have the licence with my id purchase. The soft say "there s a licence but a problem occur so try again".
Please help me, i hve paid this app.

Thanx

Grafic



Posted on: 21-12-2015 16:17 by Rafal

Hello, I made transaction o Jan 03,2015 number 2UY46774AM9769626, but it does not work on my new iPhone. Can You help me?
Rafal
rafal.slusarz@wp.pl



Posted on: 09-02-2016 21:54 by Jim Brill

Hi Elias,
Your contact page does not work.
I am building an app that will save hundreds of thousands of lives each year. I need your assistance to do some of the programming for a Jailbroken iPhone to demonstrate the app. It requires some of the same programming you used to create your impressive call recorder. I will provide more detail in private. I will be happy to provide substantial compensation.
Please contact me at jimbrill@hotmail.com
Thanks, Jim Brill



Posted on: 17-03-2016 09:45 by Usman

I want to install smart tap application on ios 9.0.1 iphone 6. Need your help.If you have some email then its easy communicating brother.



Posted on: 26-03-2016 16:06 by tony yu

i want to get the transaction ID can you help me to get , pls send to my email:expertise8888@gmail,com
Audio Recorder iPhone Application
你已支付 $3.99 USD 的款項給 LIMNEOS DEVELOPMENT
(support@limneos.net)
2015年12月06日 13:37:04 GMT 08:00
交易 ID:3R395342MB065945U



Posted on: 28-07-2016 20:19 by Ferid

Hi, my device is an iPhone 6 iOS 9.02 and I bought AudioRecorder 2 for 2 devices but on one of them when I open the app after calls counter finishes it crashes everytime 😞
Can someone help me please 😕

Thank in advance.



Posted on: 20-08-2016 16:15 by an

musictranspotr,在9.3.2的系统下,通话时,不会自动激活,不会自动播放设置好的音乐,怎么办?



Posted on: 20-08-2016 16:23 by an

0.2-9 MusicTransport, ios9.3.2, phone calls, don't automatically play, does not automatically activate the music. What method? my:3488949###qq.com



Posted on: 29-09-2016 18:06 by Luke Rider

Brother please Email me I have an amazing project that is very similar to yours but i need your help and I want to make a lot of money with you. Please email me at luketheloanrider@yahoo.com

Thank you,



Posted on: 14-10-2016 01:58 by Ts2ttrung1987

iOS Ưindows =windowpos10000. Stars2



Posted on: 22-12-2016 07:41 by alexkhan

Thanks for the nice blog. It was very useful for me. I'm happy I found this blog. Thank you for sharing with us,I too always learn something new from your post. call recorder



Posted on: 04-02-2017 06:53 by insanity83NL

When are you going to update all the jailbreak tweaks?



Posted on: 24-07-2017 11:03 by Taylor Shaw

Thanks for providing the iPhone SQLite Database Structure here. I had been searching for the various databases used by iPhone to store different data. With the source code we could actually modify or manage it using sqlite3 shell command. wholesale tablet



Post a comment: