mirror of https://github.com/kacos2000/Queries
You cannot select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
66 lines
2.8 KiB
SQL
66 lines
2.8 KiB
SQL
-- 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
|