iBooks_to_Evernote.scpt

-- Globals
global books_db
global annotations_db

-- Customize for your machine
set books_db to "~/Library/Containers/com.apple.iBooksX/Data/Documents/BKLibrary/BKLibrary-1-091020131601.sqlite"
set annotations_db to "~/Library/Containers/com.apple.iBooksX/Data/Documents/AEAnnotation/AEAnnotation_v10312011_1727_local.sqlite"
-------------------------------------------------------

set as_tid to AppleScript's text item delimiters
runProgram()
set the text item delimiters to as_tid



on runProgram()
	set ids to getAnnotationIds() -- All ids for annotations
	log "Processing " & (count of ids) & " items"
	
	repeat with i from 1 to (count of ids)
		set current_id to item i of ids
		set needs_processing to checkIfNeedsProcessing(current_id)
		
		if needs_processing then
			processId(current_id)
		else
			log "Skipped " & current_id
		end if
		
	end repeat
end runProgram


on checkIfNeedsProcessing(annotation_id)
	set needs_processing to true
	
	tell application "Evernote"
		set evernote_query to "any: " & annotation_id
		set quote_count to count of (find notes evernote_query)
		log "Searching for " & annotation_id & " - Found: " & quote_count
		if quote_count > 0 then
			set needs_processing to false
		end if
	end tell
	
	return needs_processing
end checkIfNeedsProcessing



on processId(annotation_id)
	log "*************************"
	log "Processing " & annotation_id
	try
		-- Get the details
		
		set asset_id to getAssetId(annotation_id)
		set annotation_text to getAnnotationText(annotation_id)
		
		set unix_timestamp to getAnnotationDate(annotation_id)
		set date_script to "date -r " & unix_timestamp & " '+%m/%d/%Y %I:%M %p'"
		set annotation_date to date (do shell script date_script)
		
		set book_title to getBookTitle(asset_id)
		set book_author to getBookAuthor(asset_id)
		
		checkNotebook(book_title)
		
		-- Create the Evernote entry text
		set note_title to getXWords(annotation_text, 6) & "..."
		log note_title
		set blockquote_style to "margin: 0 1.5em 0 0; padding: 0 10px; border-left: 5px solid #ddd; color: #777; font-size: 1.5em; font-family: Avenir Next;"
		set attribution_style to "padding: 1.5em 0 3em 0; text-align: right; font-family: Avenir Next; clear: both; width: 100%;"
		set uuid_style to "font-size: 0px;"
		
		set blockquote to "<blockquote style='" & blockquote_style & "'>" & annotation_text & "</blockquote>"
		set attribution to "<div style='" & attribution_style & "'><strong>" & book_author & "</strong> | <em>" & book_title & "</em></div>"
		set hidden_uuid to "<div style='" & uuid_style & "'>" & annotation_id & "</div>"
		
		set entry to blockquote & attribution & hidden_uuid
		
		tell application "Evernote"
			log "Creating note: " & note_title
			try
				create note title note_title with html entry notebook book_title created annotation_date
			on error errMsg
				display dialog "ERROR: " & errMsg
			end try
		end tell
		
		log "Processed " & note_title
	end try
	
end processId




on splitLines(theText)
	set the text item delimiters to (ASCII character 13)
	set theLines to the text items of theText
	return theLines
end splitLines


-- Get all the annotations in the DB
on getAnnotationIds()
	set annotations_sql to "select ZANNOTATIONUUID from ZAEANNOTATION where ZANNOTATIONTYPE = '2' and ZANNOTATIONDELETED = '0';"
	set annotations_list to runAnnotationQuery(annotations_sql)
	
	return splitLines(annotations_list)
end getAnnotationIds


-- Get the asset ID
on getAssetId(annotationId)
	set annotationSql to "select ZANNOTATIONASSETID from ZAEANNOTATION where ZANNOTATIONUUID = '" & annotationId & "'; "
	set assetId to runAnnotationQuery(annotationSql)
	
	return assetId
end getAssetId


-- Get the annotation text
on getAnnotationText(annotationId)
	set annotationSql to "select ZANNOTATIONSELECTEDTEXT from ZAEANNOTATION where ZANNOTATIONUUID = '" & annotationId & "'; "
	set annotationText to runAnnotationQuery(annotationSql)
	
	return annotationText
end getAnnotationText


-- Get the annotation date
on getAnnotationDate(annotation_id)
	set annotation_sql to "select strftime('%s', ZANNOTATIONCREATIONDATE + 978307200, 'unixepoch') from ZAEANNOTATION where ZANNOTATIONUUID = '" & annotation_id & "'; "
	set annotation_date to runAnnotationQuery(annotation_sql)
	
	return annotation_date
end getAnnotationDate


-- Get the book title
on getBookTitle(asset_id)
	set title_sql to "select ZTITLE from ZBKLIBRARYASSET where ZASSETID = '" & asset_id & "';"
	set book_title to runBookQuery(title_sql)
	
	return book_title
end getBookTitle


-- Get the book author
on getBookAuthor(assetId)
	set author_sql to "select ZAUTHOR from ZBKLIBRARYASSET where ZASSETID = '" & assetId & "';"
	set book_author to runBookQuery(author_sql)
	
	return book_author
end getBookAuthor


-- Run a query against the asset DB
on runBookQuery(sql)
	set books_result to runQuery(sql, books_db)
	return books_result
end runBookQuery


-- Run a query against the annotation DB
on runAnnotationQuery(sql)
	set annotations_result to runQuery(sql, annotations_db)
	
	return annotations_result
end runAnnotationQuery


on runQuery(sql, db_location)
	log "--- SQL QUERY: " & sql
	set head to "sqlite3 " & db_location & space
	set sqlite_command to head & quote & sql & quote
	
	set retval to do shell script sqlite_command
	
	log "--- SQL RESULT: " & retval
	log "--- "
	return retval
end runQuery



on checkNotebook(notebook_name)
	tell application "Evernote"
		set all_notebooks to every notebook
		
		set notebook_exists to false
		
		log "Looking for " & notebook_name
		repeat with current_noteBook in all_notebooks
			set current_name to (the name of current_noteBook)
			if current_name is equal to notebook_name then
				set notebook_exists to true
			end if
		end repeat
		
		if notebook_exists then
			-- move along, nothing to see here
			log "Found notebook: " & notebook_name
		else
			log "Creating notebook: " & notebook_name
			create notebook notebook_name
		end if
		
	end tell
	
end checkNotebook


on getXWords(text_to_process, target_number)
	set {old_tid, AppleScript's text item delimiters} to {AppleScript's text item delimiters, space}
	set retval to words 1 thru target_number of text_to_process as string
	set AppleScript's text item delimiters to old_tid
	
	return retval
end getXWords