iPhone数据库结构
/*
* iphone database structure
* @author zye
* @contact zye@tudou.com
* @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地址foo@bar.com
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), (SELEC
补充：移动开发 , IOS ,
