-
-
Notifications
You must be signed in to change notification settings - Fork 11
/
Copy pathAddressBook_sqlite.sql
65 lines (60 loc) · 2.75 KB
/
AddressBook_sqlite.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
-- IOS
-- Database: Home\Library\AddressBook\AddressBook.sqlitedb
-- Z_PK = Primary Key (unique identifier) for the entity,
-- Z_ENT = is the entity ID (every entity of a particular type has the same entity ID)
-- Z_OPT = number of times an entity has been changed
Select
ABAccount.AccountIdentifier,
abstore.Name as 'Store',
abperson.ExternalIdentifier,
ABPerson.ImageURI,
abperson.Last,
abperson.First,
abperson.Middle,
abperson.Organization,
abperson.Department,
ABPerson.JobTitle,
ABPerson.Nickname,
abperson.Note,
date('2001-01-01', abperson.Birthday || ' seconds') as 'Birthdate',
ABPerson.Prefix,
ABPerson.Suffix,
(select
value from ABMultiValue where property = 3 and record_id = ABPerson.ROWID and
label = (select ROWID from ABMultiValueLabel where value = '_$!<Main>!$_')) as 'Main',
(select
value from ABMultiValue where property = 3 and record_id = ABPerson.ROWID and
label = (select ROWID from ABMultiValueLabel where value = 'iPhone')) as 'iPhone',
(select
value from ABMultiValue where property = 3 and record_id = ABPerson.ROWID and
label = (select ROWID from ABMultiValueLabel where value = '_$!<Other>!$_')) as 'Other',
-- Following part from https://gist.github.com/laacz/1180765
(select
value from ABMultiValue where property = 3 and record_id = ABPerson.ROWID and
label = (select ROWID from ABMultiValueLabel where value = '_$!<Mobile>!$_')) as 'Mobile',
(select
value from ABMultiValue where property = 3 and record_id = ABPerson.ROWID and
label = (select ROWID from ABMultiValueLabel where value = '_$!<Home>!$_')) as 'Home',
(select
value from ABMultiValue where property = 3 and record_id = ABPerson.ROWID and
label = (select ROWID from ABMultiValueLabel where value = '_$!<Work>!$_')) as 'Work',
(select
value from ABMultiValue where property = 4 and record_id = ABPerson.ROWID and
label is null) as 'email',
(select
value from ABMultiValueEntry where parent_id in (select ROWID from ABMultiValue
where record_id = ABPerson.ROWID) and key = (select ROWID from ABMultiValueEntryKey
where lower(value) = 'street')) as 'address',
(select
value from ABMultiValueEntry where parent_id in (select ROWID from ABMultiValue
where record_id = ABPerson.ROWID) and key = (select ROWID from ABMultiValueEntryKey
where lower(value) = 'city')) as 'city',
-- End code from https://gist.github.com/laacz/1180765
datetime('2001-01-01', abperson.CreationDate || ' seconds') as 'CreationDate',
datetime('2001-01-01', abperson.ModificationDate|| ' seconds') as 'ModificationDate',
abperson.MapsData,
ABPerson.ExternalRepresentation as 'External (blob)',
ABStore.ExternalSyncTag
from abperson
join ABStore on abperson.StoreID = ABStore.ROWID
join ABAccount on ABStore.AccountID = ABAccount.ROWID