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



Posted on: 07-09-2017 06:51 by uali20

I truly like you're composing style, incredible data, thankyou for posting.
Cyber security training London



Posted on: 07-09-2017 07:10 by excel courses london

Thanks for the informative and helpful post, obviously in your blog everything is good..
excel courses london



Posted on: 07-09-2017 07:12 by java training london

This website and I conceive this internet site is really informative ! Keep on putting up! java training london



Posted on: 07-09-2017 07:15 by ccnp training uk

I don’t think many of websites provide this type of information.
ccnp training uk



Posted on: 07-09-2017 07:18 by a course london

wow, great, I was wondering how to cure acne naturally. and found your site by google, learned a lot, now i’m a bit clear. I’ve bookmark your site and also add rss. keep us updated.
a course london



Posted on: 07-09-2017 07:26 by portal perlancongan

Amazing knowledge and I like to share this kind of information with my friends and hope they like it they why I do
portal perlancongan



Posted on: 07-09-2017 07:32 by mcse course

You've really made my day today with this. I hope you keep this up!
mcse course



Posted on: 07-09-2017 07:44 by cyber security training

I want to say thanks for this post. Thank you...
cyber security training



Posted on: 07-09-2017 09:23 by cenderahati korporat

I admire the valuable information you offer in your articles. Thanks for posting it.
cenderahati korporat



Posted on: 07-09-2017 09:26 by percutian

wow... what a great blog, this writter who wrote this article it's realy a great blogger, this article so inspiring me to be a better person
percutian



Posted on: 07-09-2017 09:31 by pengalaman melancong

Thanks for posting this info. I just want to let you know that I just check out your site and I find it very interesting and informative. pengalaman melancong



Posted on: 07-09-2017 09:33 by rahsia ebay

Thanks for your post. I’ve been thinking about writing a very comparable post over the last couple of weeks, I’ll probably keep it short and sweet and link to this instead if thats cool. Thanks.
rahsia ebay



Posted on: 07-09-2017 09:41 by cari duit

This is a smart blog. I mean it. You have so much knowledge about this issue, and so much passion. cari duit



Posted on: 07-09-2017 09:47 by sap courses

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.
sap courses



Posted on: 07-09-2017 09:49 by Childcare Courses

I have read your article, it is very informative and helpful for me.I admire the valuable information you offer in your articles. Thanks for posting it..
Childcare Courses



Posted on: 07-09-2017 09:52 by percutian

Thanks for sharing this information. I really like your blog post very much. You have really shared a informative and interesting blog post . percutian



Posted on: 07-09-2017 10:48 by Las Vegas strip tattoos

It seems to me all of them are really brilliant!
Las Vegas strip tattoos



Posted on: 07-09-2017 10:51 by New Orleans Baby Gear Ren

I just couldn't leave your website before telling you that I truly enjoyed the top quality info you present to your visitors? Will be back again frequently to check up on new posts.
New Orleans Baby Gear Rental



Posted on: 07-09-2017 10:54 by Customised Engagement Rin

I would like to thank you for the efforts you have made in writing this article. I am hoping the same best work from you in the future as well.. Customised Engagement Ring



Post a comment: