/*
* iphone database structure
* @author zye
* @contact [email protected]
* @update 20070924
* @version 0.1
* @url http://yegq.yeax.com/?p=162
* @copyleft 此文檔可隨意分發。如果你發布的iphone應用得益於此文檔，建議注明，或給我email，共賀之。
*/
一、AddressBook.sqlitedb 通訊錄數據庫

location: /private/var/root/Library/AddressBook/AddressBook.sqlitedb

1.ABGroup 聯系人分組信息
ROWID:組ID,自增PK
Name:組名

2.ABGroupChanges 分組信息更新
record:
type:

3.ABGroupMembers 組聯系人
UID: PK
group_id：組ID,對應ABGroup.ROWID
member_type: 組員類別
member_id: 組員(聯系人)ID,對應ABPerson.ROWID
注意:UNIQUE(group_id, member_type, member_id)

4.ABMultiValue 存儲聯系人的各種聯系方式
UID: PK
record_id: 聯系人ID,對應ABPerson.ROWID
property: 屬性值. 3.電話; 4.email; 待補充…
identifier: 標識符.0,1,2,3,4,目前所知用於排序
label: 標志值. 1.mobile;2.home;3.work;4.other;5.homepage(URL) 對應ABMultiValueLabel.value
value: 值. 例如一個手機號碼13800138000,或一個email地址[email protected]

5.ABMultiValueEntry (未知)
parent_id: (未知)
key: (未知)
value: (未知)
注意:UNIQUE(parent_id, key)

6.ABMultiValueEntryKey (未知)
value: (未知)
注意:UNIQUE(value)

7.ABMultiValueLabel 聯系方式標志值列表
value: 見ABMultiValue.label

8.ABPerson
ROWID 自增PK,也是聯系人的唯一標識
First 名字
Last 姓
Middle (未定)
FirstPhonetic (未定,貌似留作語音撥號用的)
MiddlePhonetic (未定,貌似留作語音撥號用的)
LastPhonetic (未定,貌似留作語音撥號用的)
Organization 所在公司,組織
Department 所在部門
Note 注釋
Kind 未定
Birthday 生日
JobTitle 頭銜
Nickname 昵稱
Prefix 前綴
Suffix 後綴
FirstSort 排序用(具體未知)
LastSort 排序用(具體未知)
CreationDate 創建時間
ModificationDate 最後修改時間
CompositeNameFallback (未知)

9.ABPersonChanges (未知)
record
type

10.ABPersonMultiValueDeletes (未知)
record_id
property_id
identifier

11.ABPhoneLastFour 電話號碼後四位匹配表
multivalue_id 對應ABMultiValue.UID
value 電話號碼後四位

12.ABRecent (未知)
date
name
property
value

13.sorting_first_section_list (未知)
character
number

14.sorting_last_section_list (未知)
character
number

15.sqlite_sequence (用於記錄序列)
name:表命,如ABPerson
seq: 最新序列號

— ==========下面是建表語句==========

[sql]
CREATE TABLE ABGroup (ROWID INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT); 
CREATE TABLE ABGroupChanges (record INTEGER, TYPE INTEGER); 
CREATE TABLE ABGroupMembers (UID INTEGER PRIMARY KEY, group_id INTEGER, member_type INTEGER, member_id INTEGER, UNIQUE(group_id, member_type, member_id)); 
CREATE TABLE ABMultiValue (UID INTEGER PRIMARY KEY, record_id INTEGER, property INTEGER, identifier INTEGER, label INTEGER, VALUE TEXT); 
CREATE TABLE ABMultiValueEntry (parent_id INTEGER, KEY INTEGER, VALUE TEXT, UNIQUE(parent_id, KEY)); 
CREATE TABLE ABMultiValueEntryKey (VALUE TEXT, UNIQUE(VALUE)); 
CREATE TABLE ABMultiValueLabel (VALUE TEXT, UNIQUE(VALUE)); 
CREATE TABLE ABPerson (ROWID INTEGER PRIMARY KEY AUTOINCREMENT, FIRST TEXT, LAST TEXT, Middle TEXT, FirstPhonetic TEXT, MiddlePhonetic TEXT, LastPhonetic TEXT, Organization TEXT, Department TEXT, Note TEXT, Kind INTEGER, Birthday TEXT, JobTitle TEXT, Nickname TEXT, Prefix TEXT, Suffix TEXT, FirstSort TEXT, LastSort TEXT, CreationDate INTEGER, ModificationDate INTEGER, CompositeNameFallback TEXT); 
CREATE TABLE ABPersonChanges (record INTEGER, TYPE INTEGER); 
CREATE TABLE ABPersonMultiValueDeletes (record_id INTEGER, property_id INTEGER, identifier INTEGER); 
CREATE TABLE ABPhoneLastFour (multivalue_id INTEGER PRIMARY KEY, VALUE TEXT); 
CREATE TABLE ABRecent(DATE INTEGER, name, property INTEGER, VALUE); 
CREATE TABLE sorting_first_section_list(CHARACTER, NUMBER, UNIQUE(CHARACTER)); 
CREATE TABLE sorting_last_section_list(CHARACTER, NUMBER, UNIQUE(CHARACTER)); 
CREATE TABLE sqlite_sequence(name TEXT, seq INTEGER); 
— ==========下面是創建索引==========

[sql]
CREATE INDEX ABMultiValueRecordIDIndex ON ABMultiValue(record_id); 
CREATE INDEX ABMultiValueLabelIndex ON ABMultiValue(label); 
CREATE INDEX ABMultiValueEntryKeyIndex ON ABMultiValueEntry(KEY); 
CREATE INDEX ABFirstSortIndex ON ABPerson(FirstSort); 
CREATE INDEX ABLastSortIndex ON ABPerson(LastSort); 
CREATE INDEX ABPhoneLastFourIndex ON ABPhoneLastFour(VALUE); 
CREATE INDEX ABRecent_value_index ON ABRecent(property, VALUE); 
CREATE INDEX ABRecent_date_index ON ABRecent(property, DATE); 
— ==========下面是創建觸發器==========

[sql]
CREATE TRIGGER delete_phone_last_four AFTER DELETE ON ABMultiValue 
BEGIN 
DELETE FROM ABPhoneLastFour WHERE multivalue_id = OLD.UID; 
END; 
  
CREATE TRIGGER sorting_first_prefix_trigger AFTER INSERT ON ABPerson 
BEGIN 
INSERT OR REPLACE INTO sorting_first_section_list VALUES(substr(IFNULL(NEW.FirstSort, ‘~’), 1, 1), 1 + IFNULL((SELECT NUMBER FROM sorting_first_section_list WHERE CHARACTER = substr(IFNULL(NEW.FirstSort, ‘~’), 1, 1)), 0)); 
END; 
  
CREATE TRIGGER update_first_prefix_trigger AFTER UPDATE ON ABPerson 
BEGIN 
INSERT OR REPLACE INTO sorting_first_section_list VALUES(substr(IFNULL(OLD.FirstSort, ‘~’), 1, 1), (SELECT NUMBER FROM sorting_first_section_list WHERE CHARACTER = substr(IFNULL(OLD.FirstSort, ‘~’), 1, 1)) - 1); 
INSERT OR REPLACE INTO sorting_first_section_list VALUES(substr(IFNULL(NEW.FirstSort, ‘~’), 1, 1), 1 + IFNULL((SELECT NUMBER FROM sorting_first_section_list WHERE CHARACTER = substr(IFNULL(NEW.FirstSort, ‘~’), 1, 1)), 0)); 
END; 
  
CREATE TRIGGER delete_first_prefix_trigger AFTER DELETE ON ABPerson 
BEGIN 
INSERT OR REPLACE INTO sorting_first_section_list VALUES(substr(IFNULL(OLD.FirstSort, ‘~’), 1, 1), (SELECT NUMBER FROM sorting_first_section_list WHERE CHARACTER = substr(IFNULL(OLD.FirstSort, ‘~’), 1, 1)) - 1); 
END; 
  
CREATE TRIGGER sorting_last_prefix_trigger AFTER INSERT ON ABPerson 
BEGIN 
INSERT OR REPLACE INTO sorting_last_section_list VALUES(substr(IFNULL(NEW.LastSort, ‘~’), 1, 1), 1 + IFNULL((SELECT NUMBER FROM sorting_last_section_list WHERE CHARACTER = substr(IFNULL(NEW.LastSort, ‘~’), 1, 1)), 0)); 
END; 
  
CREATE TRIGGER update_last_prefix_trigger AFTER UPDATE ON ABPerson 
BEGIN 
INSERT OR REPLACE INTO sorting_last_section_list VALUES(substr(IFNULL(OLD.LastSort, ‘~’), 1, 1), (SELECT NUMBER FROM sorting_last_section_list WHERE CHARACTER = substr(IFNULL(OLD.LastSort, ‘~’), 1, 1)) - 1); 
INSERT OR REPLACE INTO sorting_last_section_list VALUES(substr(IFNULL(NEW.LastSort, ‘~’), 1, 1), 1 + IFNULL((SELECT NUMBER FROM sorting_last_section_list WHERE CHARACTER = substr(IFNULL(NEW.LastSort, ‘~’), 1, 1)), 0)); 
END; 
  
CREATE TRIGGER delete_last_prefix_trigger AFTER DELETE ON ABPerson 
BEGIN 
INSERT OR REPLACE INTO sorting_last_section_list VALUES(substr(IFNULL(OLD.LastSort, ‘~’), 1, 1), (SELECT NUMBER FROM sorting_last_section_list WHERE CHARACTER = substr(IFNULL(OLD.LastSort, ‘~’), 1, 1)) - 1); 
END; 
二、notes.db 記事本數據庫

location: /private/var/root/Library/Notes/notes.db

1.Note 摘要信息記錄表
creation_date: 創建時間
title: 標題 www.2cto.com
summary: 摘要

2.note_bodies 詳細信息
note_id: note ID
data: 記事內容，包含標題

– ==========下面是建表語句==========

[sql]
CREATE TABLE Note (creation_date INTEGER, title TEXT, summary TEXT); 
CREATE TABLE note_bodies (note_id INTEGER, DATA, UNIQUE(note_id)); 
– ==========下面是創建觸發器==========

[sql]
CREATE TRIGGER delete_note_bodies AFTER DELETE ON Note 
BEGIN 
DELETE FROM note_bodies WHERE note_id = OLD.ROWID; 
END; 
三、sms.db 短信數據庫

location: /private/var/root/Library/SMS/sms.db

1.message 短信表
ROWID: 自增PK
address: 對方手機號碼(+86)
date: 時間
text: 內容
flags: 標記. 2.收到的;3.自己發送的
replace: (未知)
svc_center: (未知)

– ==========下面是建表語句==========

[sql]
CREATE TABLE message (ROWID INTEGER PRIMARY KEY AUTOINCREMENT, address TEXT, DATE INTEGER, text TEXT, flags INTEGER, REPLACE INTEGER, svc_center TEXT); 
CallData DB
/System/Library/Frameworks/AppSupport.framework/calldata.db

TABLE _SqliteDatabaseProperties
TABLE citycode
code (INTEGER)
Not sure what the significance of these entries are, I’m wondering if they have something to do with the geocoding of calls not made from your phonebook?
(3888)
city (TEXT)
Not sure what the significance of these entries are, I’m wondering if they have something to do with the geocoding of calls not made from your phonebook?
(RED CLOUD)
INDEX citycode_codeIndex
citycode (code)
TABLE npa
npa (TEXT)
Numbering Plan Area, aka Area Code
(415)
location (TEXT)
State/Province Assigned to the NPA
(CA)
country (TEXT)
Country Assigned to the NPA, may be null if “location” is specific enough
(USA)
TABLE npalocation
npa (TEXT)
Numbering Plan Area, aka Area Code
(415)
location (TEXT)
Descriptive location info
(San Francisco/North Bay Area)
TABLE npanxx
npa (INTEGER)
Numbering Plan Area, aka Area Code
(907)
nxx (INTEGER)
Unknown
(200)
rate_center (INTEGER)
Unknown
(1)
INDEX npanxx_npanxxIndex
npanxx (npa,nxx)
CallHistory DB
/private/var/root/Library/CallHistory/call_history.db

TABLE _SqliteDatabaseProperties
your values will certainly be different here…when you “restore” your iPhone from iTunes the counters all reset
key (TEXT) value (TEXT)
call_history_limit 100
timer_last 60
timer_outgoing 900
timer_incoming 540
timer_all 1440
timer_lifetime 1440
timer_last_reset
data_up_last 2.5439454125
data_down_last 20.86328125
data_up_all 719.9228515625
data_down_all 8677.8427734375
data_up_lifetime 719.9228515625
data_down_lifetime 8677.8427734375
data_last_reset
_ClientVersion 3
_UniqueIdentifier GUID
TABLE call
ROWID (INTEGER PRIMARY KEY AUTOINCREMENT)
Auto-incrementing field/counter
address (TEXT)
International-formatted foreign address
(18005551212)
date (INTEGER)
OSX-epoch based datetime, convertable via date -r
(1187200801)
duration (INTEGER)
Length of call in seconds rounded to next minute, 0 = missed call
(60)
flags (INTEGER)
Flags controlling the type of record
5 – Outgoing call
4 – Incoming call
id (INTEGER)
AddressBook ID for outgoing calls selected from AddressBook, otherwise -1
(67)
INDEX date_index
call (date)
KeyChain DB
/private/var/root/Library/Keychains/keychain-2.db
Encrypted, I don’t know how to parse this yet

Voicemail DB
/private/var/root/Library/Voicemail/voicemail.db

TABLE _SqliteDatabaseProperties
key (TEXT) value (TEXT)
VMVersion 4
_UniqueIdentifier GUID
token string containing various values, including your phone number
uid_validity 1183172695
mailboxusage 57
TABLE voicemail
ROWID (INTEGER PRIMARY KEY AUTOINCREMENT)
Auto-incrementing field/counter
remote_uid (INTEGER)
International-formatted foreign address
(18005551212)
date (INTEGER)
OSX-epoch based datetime, convertable via date -r
(1187200801)
token (TEXT)
Always reads “Complete” from what I can tell
sender (TEXT)
CallerID from the calling party leaving the voicemail message
(8885551212)
callback_num (TEXT)
Callback number left by calling party, usually caller ID
(8885551212)
duration (INTEGER)
Duration in seconds
(5)
expiration (INTEGER)
OSX-epoch based datetime, convertable via date -r
(1189431482)
trashed_date (INTEGER)
definitely based in seconds, haven’t figured out the epoch yet or why it isn’t the same as the other dates based on OSX’s epoch
flags (INTEGER)
Voicemail flags
0 – Not downloaded yet
1 – Partially downloaded
2 – New, unlistened or only partially listened to
3 – Listened completely
11 – Pending delete, in “Deleted Items”
15 – Deleted from iPhone, pending delete from voicemail hq
INDEX date_index
voicemail (date)
INDEX remote_uid_index
voicemail (remote_uid)
Other, more complicated DBs, involved in syncing
AddressBook DB
/private/var/root/Library/AddressBook/AddressBook.sqlitedb
AddressBook Images DB
/private/var/root/Library/AddressBook/AddressBookImages.sqlitedb
Maptiles DB
/private/var/root/Library/Caches/MapTiles/MapTiles.sqlitedb
Calendar DB
/private/var/root/Library/Calendar/Calendar.sqlitedb

 

 

摘自 likendsl的專欄