train50 2008-6-4 11:43 PM
技术帖IPHONE的数据库结构
%26lt;font color"purple">%26lt;font size"4">%26lt;b>被禁言的这两天闲来无事 研究了一下IPHONE的数据库结构 %26lt;font color"green">现在发出来跟有爱好的朋友交流 希望大家能喜欢%26lt;
>%26lt;
>%26lt;b>%26lt;font size"5">%26lt;font color"red">%26lt;p align"Center">iPhone数据库结构%26lt;/p>%26lt;
>%26lt;
>%26lt;font color"purple">%26lt;font size"4">By 紫色蜗牛 %268901; October 1, 2007 %268901; Post a comment%26lt;/b>%26lt;
>%26lt;
>%26lt;
>%26lt;font size"2">%26lt;font color"black">一、AddressBook.sqlitedb 通讯录数据库%26lt;
>%26lt;
>location: /private/var/root/Library/AddressBook/AddressBook.sqlitedb%26lt;
>%26lt;
>1.ABGroup 联系人分组信息%26lt;
>ROWID:组ID,自增PK%26lt;
>Name:组名%26lt;
>%26lt;
>2.ABGroupChanges 分组信息更新%26lt;
>record:%26lt;
>type:%26lt;
>%26lt;
>3.ABGroupMembers 组联系人%26lt;
>UID: PK%26lt;
>group_id：组ID,对应ABGroup.ROWID%26lt;
>member_type: 组员类别%26lt;
>member_id: 组员(联系人)ID,对应ABPerson.ROWID%26lt;
>注重:UNIQUE(group_id, member_type, member_id)%26lt;
>%26lt;
>4.ABMultiValue 存储联系人的各种联系方式%26lt;
>UID: PK%26lt;
>record_id: 联系人ID,对应ABPerson.ROWID%26lt;
>property: 属性值. 3.电话; 4.email; 待补充%26lt;
>identifier: 标识符.0,1,2,3,4,目前所知用于排序%26lt;
>label: 标志值. 1.mobile;2.home;3.work;4.other;5.homepage(URL) 对应ABMultiValueLabel.value%26lt;
>value: 值. 例如一个手机号码13800138000,或一个email地址%26lt;a href"mailto:wzc4768@sina.com.cn" target"_blank">wzc4768@sina.com.cn%26lt;/a>%26lt;
>%26lt;
>5.ABMultiValueEntry (未知)%26lt;
>parent_id: (未知)%26lt;
>key: (未知)%26lt;
>value: (未知)%26lt;
>注重:UNIQUE(parent_id, key)%26lt;
>%26lt;
>6.ABMultiValueEntryKey (未知)%26lt;
>value: (未知)%26lt;
>注重:UNIQUE(value)%26lt;
>%26lt;
>7.ABMultiValueLabel 联系方式标志值列表%26lt;
>value: 见ABMultiValue.label%26lt;
>%26lt;
>8.ABPerson%26lt;
>ROWID 自增PK,也是联系人的唯一标识%26lt;
>First 名字%26lt;
>Last 姓%26lt;
>Middle (未定)%26lt;
>FirstPhonetic (未定,貌似留作语音拨号用的)%26lt;
>MiddlePhonetic (未定,貌似留作语音拨号用的)%26lt;
>LastPhonetic (未定,貌似留作语音拨号用的)%26lt;
>Organization 所在公司,组织%26lt;
>Department 所在部门%26lt;
>Note 注释%26lt;
>Kind 未定%26lt;
>Birthday 生日%26lt;
>JobTitle 头衔%26lt;
>Nickname 昵称%26lt;
>Prefix 前缀%26lt;
>Suffix 后缀%26lt;
>FirstSort 排序用(具体未知)%26lt;
>LastSort 排序用(具体未知)%26lt;
>CreationDate 创建时间%26lt;
>ModificationDate 最后修改时间%26lt;
>CompositeNameFallback (未知)%26lt;
>%26lt;
>9.ABPersonChanges (未知)%26lt;
>record%26lt;
>type%26lt;
>%26lt;
>10.ABPersonMultiValueDeletes (未知)%26lt;
>record_id%26lt;
>property_id%26lt;
>identifier%26lt;
>%26lt;
>11.ABPhoneLastFour 电话号码后四位匹配表%26lt;
>multivalue_id 对应ABMultiValue.UID%26lt;
>value 电话号码后四位%26lt;
>%26lt;
>12.ABRecent (未知)%26lt;
>date%26lt;
>name%26lt;
>property%26lt;
>value%26lt;
>%26lt;
>13.sorting_first_section_list (未知)%26lt;
>character%26lt;
>number%26lt;
>%26lt;
>14.sorting_last_section_list (未知)%26lt;
>character%26lt;
>number%26lt;
>%26lt;
>15.sqlite_sequence (用于记录序列)%26lt;
>name:表命,如ABPerson%26lt;
>seq: 最新序列号%26lt;
>%26lt;
> 下面是建表语句%26lt;
>%26lt;
>%26lt;font color"red">CREATE TABLE %26lt;font color"black">ABGroup %26lt;font color"green">(%26lt;font color"black">ROWID INTEGER %26lt;font color"red">PRIMARY KEY %26lt;font color"black">AUTOINCREMENT, Name TEXT%26lt;font color"green">)%26lt;font color"black">;%26lt;
>%26lt;font color"red">CREATE TABLE %26lt;font color"black">ABGroupChanges %26lt;font color"green">(%26lt;font color"black">record INTEGER, type INTEGER%26lt;font color"green">)%26lt;font color"black">;%26lt;
>%26lt;font color"red">CREATE TABLE %26lt;font color"black">ABGroupMembers %26lt;font color"green">(%26lt;font color"black">UID INTEGER %26lt;font color"red">PRIMARY KEY%26lt;font color"black">, group_id INTEGER, member_type INTEGER, member_id INTEGER, UNIQUE%26lt;font color"green">(%26lt;font color"black">group_id, member_type, member_id%26lt;font color"green">))%26lt;font color"black">;%26lt;
>%26lt;font color"red">CREATE TABLE %26lt;font color"black">ABMultiValue %26lt;font color"green">(%26lt;font color"black">UID INTEGER %26lt;font color"red">PRIMARY KEY%26lt;font color"black">, record_id INTEGER, property INTEGER, identifier INTEGER, label INTEGER, value TEXT%26lt;font color"green">)%26lt;font color"black">;%26lt;
>%26lt;font color"red">CREATE TABLE %26lt;font color"black">ABMultiValueEntry %26lt;font color"green">(%26lt;font color"black">parent_id INTEGER, %26lt;font color"red">KEY %26lt;font color"black">INTEGER, value TEXT, %26lt;font color"red">UNIQUE%26lt;font color"green">(%26lt;font color"black">parent_id, %26lt;font color"red">KEY%26lt;font color"green">))%26lt;font color"black">;%26lt;
>%26lt;font color"red">CREATE TABLE %26lt;font color"black">ABMultiValueEntryKey %26lt;font color"green">(%26lt;font color"black">value TEXT, %26lt;font color"red">UNIQUE%26lt;font color"green">(%26lt;font color"black">value%26lt;font color"green">))%26lt;font color"black">;%26lt;
>%26lt;font color"red">CREATE TABLE %26lt;font color"black">ABMultiValueLabel %26lt;font color"green">(%26lt;font color"black">value TEXT, %26lt;font color"red">UNIQUE%26lt;font color"green">(%26lt;font color"black">value%26lt;font color"green">))%26lt;font color"black">;%26lt;
>%26lt;font color"red">CREATE TABLE %26lt;font color"black">ABPerson %26lt;font color"green">(%26lt;font color"black">ROWID INTEGER %26lt;font color"red">PRIMARY KEY %26lt;font color"black">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%26lt;font color"green">)%26lt;font color"black">;%26lt;
>%26lt;font color"red">CREATE TABLE %26lt;font color"black">ABPersonChanges %26lt;font color"green">(%26lt;font color"black">record INTEGER, type INTEGER%26lt;font color"green">)%26lt;font color"black">;%26lt;
>%26lt;font color"red">CREATE TABLE %26lt;font color"black">ABPersonMultiValueDeletes %26lt;font color"green">(%26lt;font color"black">record_id INTEGER, property_id INTEGER, identifier INTEGER%26lt;font color"green">)%26lt;font color"black">;%26lt;
>%26lt;font color"red">CREATE TABLE %26lt;font color"black">ABPhoneLastFour %26lt;font color"green">(%26lt;font color"black">multivalue_id INTEGER %26lt;font color"red">PRIMARY KEY%26lt;font color"black">, value TEXT%26lt;font color"green">)%26lt;font color"black">;%26lt;
>%26lt;font color"red">CREATE TABLE %26lt;font color"black">ABRecent%26lt;font color"green">(%26lt;font color"black">date INTEGER, name, property INTEGER, value%26lt;font color"green">)%26lt;font color"black">;%26lt;
>%26lt;font color"red">CREATE TABLE %26lt;font color"black">sorting_first_section_list%26lt;font color"green">(%26lt;font color"black">character, number, %26lt;font color"red">UNIQUE%26lt;font color"green">(%26lt;font color"black">character%26lt;font color"green">))%26lt;font color"black">;%26lt;
>%26lt;font color"red">CREATE TABLE %26lt;font color"black">sorting_last_section_list%26lt;font color"green">(%26lt;font color"black">character, number, %26lt;font color"red">UNIQUE%26lt;font color"green">(%26lt;font color"black">character%26lt;font color"green">))%26lt;font color"black">;%26lt;
>%26lt;font color"red">CREATE TABLE %26lt;font color"black">sqlite_sequence%26lt;font color"green">(%26lt;font color"black">name TEXT, seq INTEGER%26lt;font color"green">)%26lt;font color"black">;%26lt;
>%26lt;
> 下面是创建索引%26lt;
>%26lt;
>%26lt;font color"red">CREATE INDEX %26lt;font color"black">ABMultiValueRecordIDIndex %26lt;font color"red">ON %26lt;font color"black">ABMultiValue%26lt;font color"green">(%26lt;font color"black">record_id%26lt;font color"green">)%26lt;font color"black">;%26lt;
>%26lt;font color"red">CREATE INDEX %26lt;font color"black">ABMultiValueLabelIndex %26lt;font color"red">ON %26lt;font color"black">ABMultiValue%26lt;font color"green">(%26lt;font color"black">label%26lt;font color"green">)%26lt;font color"black">;%26lt;
>%26lt;font color"red">CREATE INDEX %26lt;font color"black">ABMultiValueEntryKeyIndex %26lt;font color"red">ON %26lt;font color"black">ABMultiValueEntry%26lt;font color"green">(%26lt;font color"red">KEY%26lt;font color"green">)%26lt;font color"black">;%26lt;
>%26lt;font color"red">CREATE INDEX %26lt;font color"black">ABFirstSortIndex %26lt;font color"red">ON %26lt;font color"black">ABPerson%26lt;font color"green">(%26lt;font color"black">FirstSort%26lt;font color"green">)%26lt;font color"black">;%26lt;
>%26lt;font color"red">CREATE INDEX %26lt;font color"black">ABLastSortIndex %26lt;font color"red">ON %26lt;font color"black">ABPerson%26lt;font color"green">(%26lt;font color"black">LastSort%26lt;font color"green">)%26lt;font color"black">;%26lt;
>%26lt;font color"red">CREATE INDEX %26lt;font color"black">ABPhoneLastFourIndex %26lt;font color"red">ON %26lt;font color"black">ABPhoneLastFour%26lt;font color"green">(%26lt;font color"black">value%26lt;font color"green">)%26lt;font color"black">;%26lt;
>%26lt;font color"red">CREATE INDEX %26lt;font color"black">ABRecent_value_index %26lt;font color"red">ON %26lt;font color"black">ABRecent%26lt;font color"green">(%26lt;font color"black">property, value%26lt;font color"green">)%26lt;font color"black">;%26lt;
>%26lt;font color"red">CREATE INDEX %26lt;font color"black">ABRecent_date_index %26lt;font color"red">ON %26lt;font color"black">ABRecent%26lt;font color"green">(%26lt;font color"black">property, date%26lt;font color"green">)%26lt;font color"black">;%26lt;
>%26lt;
> 下面是创建触发器%26lt;
>%26lt;
>%26lt;font color"red">CREATE TRIGGER %26lt;font color"black">delete_phone_last_four AFTER %26lt;font color"red">DELETE ON %26lt;font color"black">ABMultiValue%26lt;
>BEGIN%26lt;
>%26lt;font color"red">DELETE FROM %26lt;font color"black">ABPhoneLastFour %26lt;font color"red">WHERE %26lt;font color"black">multivalue_id OLD.UID;%26lt;
>END;%26lt;
>%26lt;
>%26lt;font color"red">CREATE TRIGGER %26lt;font color"black">sorting_first_prefix_trigger AFTER %26lt;font color"red">INSERT ON %26lt;font color"black">ABPerson%26lt;
>BEGIN%26lt;
>%26lt;font color"red">INSERT OR REPLACE INTO %26lt;font color"black">sorting_first_section_list %26lt;font color"red">VALUES%26lt;font color"green">(%26lt;font color"black">substr%26lt;font color"green">(%26lt;font color"black">IFNULL%26lt;font color"green">(%26lt;font color"black">NEW.FirstSort, ‘’%26lt;font color"green">)%26lt;font color"black">, %26lt;font color"purple">1%26lt;font color"black">, %26lt;font color"purple">1%26lt;font color"green">)%26lt;font color"black">, %26lt;font color"purple">1 %26lt;font color"black"> IFNULL%26lt;font color"green">((%26lt;font color"red">SELECT %26lt;font color"black">number %26lt;font color"red">FROM %26lt;font color"black">sorting_first_section_list %26lt;font color"red">WHERE %26lt;font color"black">character substr%26lt;font color"green">(%26lt;font color"black">IFNULL%26lt;font color"green">(%26lt;font color"black">NEW.FirstSort, ‘’%26lt;font color"green">)%26lt;font color"black">, %26lt;font color"purple">1%26lt;font color"black">, %26lt;font color"purple">1%26lt;font color"green">))%26lt;font color"black">, %26lt;font color"purple">0%26lt;font color"green">))%26lt;font color"black">;%26lt;
>END;%26lt;
>%26lt;
>%26lt;font color"red">CREATE TRIGGER %26lt;font color"black">update_first_prefix_trigger AFTER %26lt;font color"red">UPDATE ON %26lt;font color"black">ABPerson%26lt;
>BEGIN%26lt;
>%26lt;font color"red">INSERT OR REPLACE INTO %26lt;font color"black">sorting_first_section_list %26lt;font color"red">VALUES%26lt;font color"green">(%26lt;font color"black">substr%26lt;font color"green">(%26lt;font color"black">IFNULL%26lt;font color"green">(%26lt;font color"black">OLD.FirstSort, ‘’%26lt;font color"green">)%26lt;font color"black">, %26lt;font color"purple">1%26lt;font color"black">, %26lt;font color"purple">1%26lt;font color"green">)%26lt;font color"black">, %26lt;font color"green">(%26lt;font color"red">SELECT %26lt;font color"black">number %26lt;font color"red">FROM %26lt;font color"black">sorting_first_section_list %26lt;font color"red">WHERE %26lt;font color"black">character substr%26lt;font color"green">(%26lt;font color"black">IFNULL%26lt;font color"green">(%26lt;font color"black">OLD.FirstSort, ‘’%26lt;font color"green">)%26lt;font color"black">, %26lt;font color"purple">1%26lt;font color"black">, %26lt;font color"purple">1%26lt;font color"green">)) %26lt;font color"black">- %26lt;font color"purple">1%26lt;font color"green">)%26lt;font color"black">;%26lt;
>%26lt;font color"red">INSERT OR REPLACE INTO %26lt;font color"black">sorting_first_section_list %26lt;font color"red">VALUES%26lt;font color"green">(%26lt;font color"black">substr%26lt;font color"green">(%26lt;font color"black">IFNULL%26lt;font color"green">(%26lt;font color"black">NEW.FirstSort, ‘’%26lt;font color"green">)%26lt;font color"black">, %26lt;font color"purple">1%26lt;font color"black">, %26lt;font color"purple">1%26lt;font color"green">)%26lt;font color"black">, %26lt;font color"purple">1 %26lt;font color"black"> IFNULL%26lt;font color"green">((%26lt;font color"red">SELECT %26lt;font color"black">number %26lt;font color"red">FROM %26lt;font color"black">sorting_first_section_list %26lt;font color"red">WHERE %26lt;font color"black">character substr%26lt;font color"green">(%26lt;font color"black">IFNULL%26lt;font color"green">(%26lt;font color"black">NEW.FirstSort, ‘’%26lt;font color"green">)%26lt;font color"black">, %26lt;font color"purple">1%26lt;font color"black">, %26lt;font color"purple">1%26lt;font color"green">))%26lt;font color"black">, %26lt;font color"purple">0%26lt;font color"green">))%26lt;font color"black">;%26lt;
>END;%26lt;
>%26lt;
>%26lt;font color"red">CREATE TRIGGER %26lt;font color"black">delete_first_prefix_trigger AFTER %26lt;font color"red">DELETE ON %26lt;font color"black">ABPerson%26lt;
>BEGIN%26lt;
>%26lt;font color"red">INSERT OR REPLACE INTO %26lt;font color"black">sorting_first_section_list %26lt;font color"red">VALUES%26lt;font color"green">(%26lt;font color"black">substr%26lt;font color"green">(%26lt;font color"black">IFNULL%26lt;font color"green">(%26lt;font color"black">OLD.FirstSort, ‘’%26lt;font color"green">)%26lt;font color"black">, %26lt;font color"purple">1%26lt;font color"black">, %26lt;font color"purple">1%26lt;font color"green">)%26lt;font color"black">, %26lt;font color"green">(%26lt;font color"red">SELECT %26lt;font color"black">number %26lt;font color"red">FROM %26lt;font color"black">sorting_first_section_list %26lt;font color"red">WHERE %26lt;font color"black">character substr%26lt;font color"green">(%26lt;font color"black">IFNULL%26lt;font color"green">(%26lt;font color"black">OLD.FirstSort, ‘’%26lt;font color"green">)%26lt;font color"black">, %26lt;font color"purple">1%26lt;font color"black">, %26lt;font color"purple">1%26lt;font color"green">)) %26lt;font color"black">- %26lt;font color"purple">1%26lt;font color"green">)%26lt;font color"black">;%26lt;
>END;%26lt;
>%26lt;
>%26lt;font color"red">CREATE TRIGGER %26lt;font color"black">sorting_last_prefix_trigger AFTER %26lt;font color"red">INSERT ON %26lt;font color"black">ABPerson%26lt;
>BEGIN%26lt;
>%26lt;font color"red">INSERT OR REPLACE INTO %26lt;font color"black">sorting_last_section_list %26lt;font color"red">VALUES%26lt;font color"green">(%26lt;font color"black">substr%26lt;font color"green">(%26lt;font color"black">IFNULL%26lt;font color"green">(%26lt;font color"black">NEW.LastSort, ‘’%26lt;font color"green">)%26lt;font color"black">, %26lt;font color"purple">1%26lt;font color"black">, %26lt;font color"purple">1%26lt;font color"green">)%26lt;font color"black">, %26lt;font color"purple">1 %26lt;font color"black"> IFNULL%26lt;font color"green">((%26lt;font color"red">SELECT %26lt;font color"black">number %26lt;font color"red">FROM %26lt;font color"black">sorting_last_section_list %26lt;font color"red">WHERE %26lt;font color"black">character substr%26lt;font color"green">(%26lt;font color"black">IFNULL%26lt;font color"green">(%26lt;font color"black">NEW.LastSort, ‘’%26lt;font color"green">)%26lt;font color"black">, %26lt;font color"purple">1%26lt;font color"black">, %26lt;font color"purple">1%26lt;font color"green">))%26lt;font color"black">, %26lt;font color"purple">0%26lt;font color"green">))%26lt;font color"black">;%26lt;
>END;%26lt;
>%26lt;
>%26lt;font color"red">CREATE TRIGGER %26lt;font color"black">update_last_prefix_trigger AFTER %26lt;font color"red">UPDATE ON %26lt;font color"black">ABPerson%26lt;
>BEGIN%26lt;
>%26lt;font color"red">INSERT OR REPLACE INTO %26lt;font color"black">sorting_last_section_list %26lt;font color"red">VALUES%26lt;font color"green">(%26lt;font color"black">substr%26lt;font color"green">(%26lt;font color"black">IFNULL%26lt;font color"green">(OLD.LastSort, ‘’%26lt;font color"green">)%26lt;font color"black">, %26lt;font color"purple">1%26lt;font color"black">, %26lt;font color"purple">1%26lt;font color"green">)%26lt;font color"black">, %26lt;font color"green">(%26lt;font color"red">SELECT %26lt;font color"black">number %26lt;font color"red">FROM %26lt;font color"black">sorting_last_section_list %26lt;font color"red">WHERE %26lt;font color"black">character substr%26lt;font color"green">(%26lt;font color"black">IFNULL%26lt;font color"green">(%26lt;font color"black">OLD.LastSort, ‘’%26lt;font color"green">)%26lt;font color"black">, %26lt;font color"purple">1, %26lt;font color"purple">1%26lt;font color"green">)) %26lt;font color"black">- %26lt;font color"purple">1%26lt;font color"green">)%26lt;font color"black">;%26lt;
>%26lt;font color"red">INSERT OR REPLACE INTO %26lt;font color"black">sorting_last_section_list %26lt;font color"red">VALUES%26lt;font color"green">(%26lt;font color"black">substr%26lt;font color"green">(%26lt;font color"black">IFNULL%26lt;font color"green">(%26lt;font color"black">NEW.LastSort, ‘’%26lt;font color"green">)%26lt;font color"black">, %26lt;font color"purple">1%26lt;font color"black">, %26lt;font color"purple">1%26lt;font color"green">)%26lt;font color"black">, %26lt;font color"purple">1 %26lt;font color"black"> IFNULL%26lt;font color"green">((%26lt;font color"red">SELECT %26lt;font color"black">number %26lt;font color"red">FROM %26lt;font color"black">sorting_last_section_list %26lt;font color"red">WHERE %26lt;font color"black">character substr%26lt;font color"green">(%26lt;font color"black">IFNULL%26lt;font color"green">(%26lt;font color"black">NEW.LastSort, ‘’%26lt;font color"green">)%26lt;font color"black">, %26lt;font color"purple">1%26lt;font color"black">, %26lt;font color"purple">1%26lt;font color"green">))%26lt;font color"black">, %26lt;font color"purple">0%26lt;font color"green">))%26lt;font color"black">;%26lt;
>END;%26lt;
>%26lt;
>%26lt;font color"red">CREATE TRIGGER %26lt;font color"black">delete_last_prefix_trigger AFTER %26lt;font color"red">DELETE ON %26lt;font color"black">ABPerson%26lt;
>BEGIN%26lt;
>%26lt;font color"red">INSERT OR REPLACE INTO %26lt;font color"black">sorting_last_section_list %26lt;font color"red">VALUES%26lt;font color"green">(%26lt;font color"black">substr%26lt;font color"green">(%26lt;font color"black">IFNULL%26lt;font color"green">(%26lt;font color"black">Old.LastSort, ‘’%26lt;font color"green">)%26lt;font color"black">, %26lt;font color"purple">1%26lt;font color"black">, %26lt;font color"purple">1%26lt;font color"green">)%26lt;font color"black">, %26lt;font color"green">(%26lt;font color"red">SELECT %26lt;font color"black">number %26lt;font color"red">FROM %26lt;font color"black">sorting_last_section_list %26lt;font color"red">WHERE %26lt;font color"black">character substr%26lt;font color"green">(%26lt;font color"black">IFNULL%26lt;font color"green">(%26lt;font color"black">Old.LastSort, ‘’%26lt;font color"green">)%26lt;font color"black">, %26lt;font color"purple">1%26lt;font color"black">, %26lt;font color"purple">1%26lt;font color"green">)) %26lt;font color"black">- %26lt;font color"purple">1%26lt;font color"green">)%26lt;font color"black">;%26lt;
>END;%26lt;
>%26lt;
>二、notes.db 记事本数据库%26lt;
>%26lt;
>location: /private/var/root/Library/Notes/notes.db%26lt;
>%26lt;
>1.Note 摘要信息记录表%26lt;
>creation_date: 创建时间%26lt;
>title: 标题%26lt;
>summary: 摘要%26lt;
>%26lt;
>2.note_bodies 具体信息%26lt;
>note_id: note ID%26lt;
>data: 记事内容，包含标题%26lt;
>%26lt;
>– 下面是建表语句%26lt;
>%26lt;
>%26lt;font color"red">CREATE TABLE %26lt;font color"black">Note %26lt;font color"green">(%26lt;font color"black">creation_date INTEGER, title TEXT, summary TEXT%26lt;font color"green">)%26lt;font color"black">;%26lt;
>%26lt;font color"red">CREATE TABLE %26lt;font color"black">note_bodies %26lt;font color"green">(%26lt;font color"black">note_id INTEGER, %26lt;font color"red">DATA%26lt;font color"black">, %26lt;font color"red">UNIQUE%26lt;font color"green">(%26lt;font color"black">note_id%26lt;font color"green">))%26lt;font color"black">;%26lt;
>%26lt;
>– 下面是创建触发器%26lt;
>%26lt;
>%26lt;font color"red">CREATE TRIGGER %26lt;font color"black">delete_note_bodies AFTER %26lt;font color"red">DELETE ON %26lt;font color"black">Note%26lt;
>BEGIN%26lt;
>%26lt;font color"red">DELETE FROM %26lt;font color"black">note_bodies %26lt;font color"red">WHERE %26lt;font color"black">note_id OLD.ROWID;%26lt;
>END;%26lt;
>%26lt;
>三、sms.db 短信数据库%26lt;
>%26lt;
>location: /private/var/root/Library/SMS/sms.db%26lt;
>%26lt;
>1.message 短信表%26lt;
>ROWID: 自增PK%26lt;
>address: 对方手机号码(86)%26lt;
>date: 时间%26lt;
>text: 内容%26lt;
>flags: 标记. 2.收到的;3.自己发送的%26lt;
>replace: (未知)%26lt;
>svc_center: (未知)%26lt;
>%26lt;
>– 下面是建表语句%26lt;
>%26lt;
>%26lt;font color"red">CREATE TABLE %26lt;font color"black">message %26lt;font color"green">(%26lt;font color"black">ROWID INTEGER %26lt;font color"red">PRIMARY KEY %26lt;font color"black">AUTOINCREMENT, address TEXT, date INTEGER, text TEXT, flags INTEGER, %26lt;font color"red">REPLACE %26lt;font color"black">INTEGER, svc_center TEXT%26lt;font color"green">)%26lt;font color"black">;%26lt;
>%26lt;
>CallData DB%26lt;
>/System/Library/Frameworks/AppSupport.framework/calldata.db%26lt;
>%26lt;
>TABLE _SqliteDatabaseProperties%26lt;
>TABLE citycode%26lt;
>code (INTEGER)%26lt;
>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?%26lt;
>(3888)%26lt;
>city (TEXT)%26lt;
>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?%26lt;
>(RED CLOUD)%26lt;
>INDEX citycode_codeIndex%26lt;
>citycode (code)%26lt;
>TABLE npa%26lt;
>npa (TEXT)%26lt;
>Numbering Plan Area, aka Area Code%26lt;
>(415)%26lt;
>location (TEXT)%26lt;
>State/Province Assigned to the NPA%26lt;
>(CA)%26lt;
>country (TEXT)%26lt;
>Country Assigned to the NPA, may be null if “location” is specific enough%26lt;
>(USA)%26lt;
>TABLE npalocation%26lt;
>npa (TEXT)%26lt;
>Numbering Plan Area, aka Area Code%26lt;
>(415)%26lt;
>location (TEXT)%26lt;
>Descriptive location info%26lt;
>(San Francisco/North Bay Area)%26lt;
>TABLE npanxx%26lt;
>npa (INTEGER)%26lt;
>Numbering Plan Area, aka Area Code%26lt;
>(907)%26lt;
>nxx (INTEGER)%26lt;
>Unknown%26lt;
>(200)%26lt;
>rate_center (INTEGER)%26lt;
>Unknown%26lt;
>(1)%26lt;
>INDEX npanxx_npanxxIndex%26lt;
>npanxx (npa,nxx)%26lt;
>CallHistory DB%26lt;
>/private/var/root/Library/CallHistory/call_history.db%26lt;
>%26lt;
>TABLE _SqliteDatabaseProperties%26lt;
>your values will certainly be different herewhen you “restore” your iPhone from iTunes the counters all reset%26lt;
>key (TEXT) value (TEXT)%26lt;
>call_history_limit 100%26lt;
>timer_last 60%26lt;
>timer_outgoing 900%26lt;
>timer_incoming 540%26lt;
>timer_all 1440%26lt;
>timer_lifetime 1440%26lt;
>timer_last_reset%26lt;
>data_up_last 2.5439454125%26lt;
>data_down_last 20.86328125%26lt;
>data_up_all 719.9228515625%26lt;
>data_down_all 8677.8427734375%26lt;
>data_up_lifetime 719.9228515625%26lt;
>data_down_lifetime 8677.8427734375%26lt;
>data_last_reset%26lt;
>_ClientVersion 3%26lt;
>_UniqueIdentifier GUID%26lt;
>TABLE call%26lt;
>ROWID (INTEGER PRIMARY KEY AUTOINCREMENT)%26lt;
>Auto-incrementing field/counter%26lt;
>address (TEXT)%26lt;
>International-formatted foreign address%26lt;
>(18005551212)%26lt;
>date (INTEGER)%26lt;
>OSX-epoch based datetime, convertable via date -r%26lt;
>(1187200801)%26lt;
>duration (INTEGER)%26lt;
>Length of call in seconds rounded to next minute, 0 missed call%26lt;
>(60)%26lt;
>flags (INTEGER)%26lt;
>Flags controlling the type of record%26lt;
>5 - Outgoing call%26lt;
>4 - Incoming call%26lt;
>id (INTEGER)%26lt;
>AddressBook ID for outgoing calls selected from AddressBook, otherwise -1%26lt;
>(67)%26lt;
>INDEX date_index%26lt;
>call (date)%26lt;
>KeyChain DB%26lt;
>/private/var/root/Library/Keychains/keychain-2.db%26lt;
>Encrypted, I don’t know how to parse this yet%26lt;
>%26lt;
>Voicemail DB%26lt;
>/private/var/root/Library/Voicemail/voicemail.db%26lt;
>%26lt;
>TABLE _SqliteDatabaseProperties%26lt;
>key (TEXT) value (TEXT)%26lt;
>VMVersion 4%26lt;
>_UniqueIdentifier GUID%26lt;
>token string containing various values, including your phone number%26lt;
>uid_validity 1183172695%26lt;
>mailboxusage 57%26lt;
>TABLE voicemail%26lt;
>ROWID (INTEGER PRIMARY KEY AUTOINCREMENT)%26lt;
>Auto-incrementing field/counter%26lt;
>remote_uid (INTEGER)%26lt;
>International-formatted foreign address%26lt;
>(18005551212)%26lt;
>date (INTEGER)%26lt;
>OSX-epoch based datetime, convertable via date -r%26lt;
>(1187200801)%26lt;
>token (TEXT)%26lt;
>Always reads “Complete” from what I can tell%26lt;
>sender (TEXT)%26lt;
>CallerID from the calling party leaving the voicemail message%26lt;
>(8885551212)%26lt;
>callback_num (TEXT)%26lt;
>Callback number left by calling party, usually caller ID%26lt;
>(8885551212)%26lt;
>duration (INTEGER)%26lt;
>Duration in seconds%26lt;
>(5)%26lt;
>expiration (INTEGER)%26lt;
>OSX-epoch based datetime, convertable via date -r%26lt;
>(1189431482)%26lt;
>trashed_date (INTEGER)%26lt;
>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%26lt;
>flags (INTEGER)%26lt;
>Voicemail flags%26lt;
>0 - Not downloaded yet%26lt;
>1 - Partially downloaded%26lt;
>2 - New, unlistened or only partially listened to%26lt;
>3 - Listened completely%26lt;
>11 - Pending delete, in “Deleted Items”%26lt;
>15 - Deleted from iPhone, pending delete from voicemail hq%26lt;
>INDEX date_index%26lt;
>voicemail (date)%26lt;
>INDEX remote_uid_index%26lt;
>voicemail (remote_uid)%26lt;
>Other, more complicated DBs, involved in syncing%26lt;
>AddressBook DB%26lt;
>/private/var/root/Library/AddressBook/AddressBook.sqlitedb%26lt;
>AddressBook Images DB%26lt;
>/private/var/root/Library/AddressBook/AddressBookImages.sqlitedb%26lt;
>Maptiles DB%26lt;
>/private/var/root/Library/Caches/MapTiles/MapTiles.sqlitedb%26lt;
>Calendar DB%26lt;
>/private/var/root/Library/Calendar/Calendar.sqlitedb