Queries

The two database files of concern are:

FILE1:

~/Library/Containers/com.apple.iBooksX/Data/Documents/BKLibrary/BKLibrary-1-091020131601.sqlite

FILE2:

~/Library/Containers/com.apple.iBooksX/Data/Documents/AEAnnotation/AEAnnotation_v10312011_1727_local.sqlite

Query#1 in FILE1

Select all titles available in the iBooks library:

select ZTITLE from ZBKLIBRARYASSET;

Query#2 in FILE2

Get all asset ids that have at least one annotation (no duplicates):

select count(*), ZANNOTATIONASSETID from ZAEANNOTATION where ZANNOTATIONREPRESENTATIVETEXT IS NOT NULL group by ZANNOTATIONASSETID;

Query#3

attach 'BKLibrary/BKLibrary-1-091020131601.sqlite' as db1;
attach 'AEAnnotation/AEAnnotation_v10312011_1727_local.sqlite' as db2;

select a.ZTITLE, a.ZAUTHOR, b.ZANNOTATIONREPRESENTATIVETEXT 
from db1.ZBKLIBRARYASSET a
inner join 
db2.ZAEANNOTATION b
on b.ZANNOTATIONASSETID = a.ZASSETID 
where b.ZANNOTATIONREPRESENTATIVETEXT is not NULL;

I can run the above statements real quick by doing this:

1. Create blah.txt
2. Paste and save aforementioned statements in that file
3. Goto ~/Library/Containers/com.apple.iBooksX/Data/Documents/
4. Run sqlite3
5. In that shell, type .read blah.txt
6. That's it.