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?



Post a comment: