Information Technology Reference
In-Depth Information
$sqlite3 AddressBook.sqlitedb SQLite version 3.7.12
2012-04-03 19:43:07
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>.mode csv
sqlite>.output AddressBook.csv
sqlite>.headers on
sqlite> SELECT p.rowid, p.first, p.middle, p.last,
datetime(p.creationDate+978307200,'unixepoch') as
creationdate,
case when m.label in (SELECT rowid from
ABMultiValueLabel)
then (SELECT value from ABMultiValueLabel where
m.label=rowid)
else
m.label end as Type, m.value, p.organization,
p.department, p.note, p.birthday, p.nickname,
p.jobtitle,datetime(p.modificationDate + 978307200,
'unixepoch') as modificationdate
FROM ABPerson p,ABMultiValue m
WHERE p.rowid=m.record_id and m.value not null
ORDER by p.rowid ASC;
sqlite>.exit
The preceding query cross-references the data across the three tables and retrieves the
contact information stored in the database. The query also converts the Mac absolute time
into a readable form using the SQLite
datetime
function.
Address book images
In addition to the address book's data, each contact may contain an image associated with
it. This image is displayed on the screen whenever the user receives an incoming call from
a particular contact. The address book images database is a
HomeDomain
file and can be
found at
/private/var/mobile/Library/AddressBook/Ad-
dressBookImages.sqlitedb
.
The
ABFullSizeImage
table in the
AddressBookImages.sqlitedb
file con-
tains images in binary data. To extract the images, use SQLite's
.output
and
.dump
