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



Posted on: 25-09-2017 07:36 by Berater Salzburg

If you don"t mind proceed with this extraordinary work and I anticipate a greater amount of your magnificent blog entries.  Berater Salzburg



Posted on: 28-09-2017 08:37 by frases bonitas de amor

Pretty good post. I just stumbled upon your blog and wanted to say that I have really enjoyed reading your blog posts. Any way I’ll be subscribing to your feed and I hope you post again soon. frases bonitas de amor



Posted on: 28-09-2017 08:52 by panistefanin

Vielen Dank für die nützlichen Informationen! Ich half Ihren Rat!
cbr



Posted on: 06-10-2017 11:27 by penetrex

These are beginning late a touch of the many fast food foundations to look at. It's never an astonishing arrangement to simply eat fast food, yet now and again, it makes for a delightful treat. fast food places near me



Posted on: 07-10-2017 07:39 by small business website de

I really enjoyed reading this post, big fan. Keep up the good work andplease tell me when can you publish more articles or where can I read more on the subject?
small business website design Brisbane



Posted on: 07-10-2017 11:39 by osama

I like viewing web sites which comprehend the price of delivering the excellent useful resource free of charge. I truly adored reading your posting. Thank you! completefacilitiesjersey.com



Posted on: 11-10-2017 07:37 by dentalclix

Just pure brilliance from you here. I have never expected something less than this from you and you have not disappointed me at all. I suppose you will keep the quality work going on.  dentalclix



Posted on: 11-10-2017 09:20 by voyance pure

I truly welcome this superb post that you have accommodated us. I guarantee this would be valuable for the vast majority of the general population. voyance pure



Posted on: 11-10-2017 09:31 by mastering chemistry

Waow this is quite pleasant article, my sister love to read such type of post, I am going to tell her and bookmarking this webpage. Thanks mastering chemistry



Post a comment: