A collection of SQLite queries for the Zotero database

Zotero_queries

(* ZOTERO SQLITE QUERIES

--Stephen Margheim
--2/4/14
--open source

This is a collection of SQLite queries to be used with a user's Zotero database to extract most relevant pieces of information.
Each query will return a string list of all the item keys that match the query.
*)

--Be sure to hard-code the path to your Zotero .sqlite file
property zoteroDatabase : "'/Users/smargheim/Library/Application Support/Zotero/Profiles/096ftehj.default/zotero/zotero.sqlite'"

--Prepare backbone of sqlite query calls
--Thanks to Adam Bell (http://macscripter.net/viewtopic.php?id=24744)
property loc : space & zoteroDatabase & space
property head : "sqlite3" & loc & quote
property tail : quote

property tid : AppleScript's text item delimiters


set query to "semi"

on q_title(query)
	set title_query to "
		select items.key 
		from items, itemData, fields, itemDataValues, itemTypes 
		where 
			items.itemID = itemData.itemID 
			and itemData.fieldID = fields.fieldID 
			and itemData.valueID = itemDataValues.valueID 
			and items.itemTypeID = itemTypes.itemTypeID 
			and itemTypes.typeName != 'attachment' 
			and (fields.fieldName = 'title' 
				or fields.fieldName = 'publicationTitle' 
				or fields.fieldName = 'seriesTitle' 
				or fields.fieldName = 'series') 
			and itemDataValues.value LIKE '%" & query & "%'"
	
	--Every item with query in main title or container title
	set titles_str to do shell script head & title_query & tail
	
	set titles_l to my q_split(titles_str, return)
	return titles_l
end q_title

on q_author(query)
	set author_query to "
		select items.key
		from items, creatorData, creatorTypes, itemCreators, creators
		where
			itemCreators.creatorTypeID = creatorTypes.creatorTypeID
			and items.itemID = itemCreators.itemID
			and itemCreators.creatorID = creators.creatorID
			and creators.creatorDataID = creatorData.creatorDataID
			and itemCreators.creatorTypeID = creatorTypes.creatorTypeID
			and creatorData.lastName LIKE '%" & query & "%'"
	
	--Every item with query in author last name
	set authors_str to do shell script head & author_query & tail
	
	set authors_l to my q_split(authors_str, return)
	return authors_l
end q_author

on q_collection(query)
	set collection_query to "
		select items.key
		from items, collections, collectionItems
		where
			items.itemID = collectionItems.itemID
			and collections.collectionID = collectionItems.collectionID
			and collections.collectionName LIKE '%" & query & "%'"
	
	--Every item in a collection with query in title
	set collections_str to do shell script head & collection_query & tail
	
	set collections_l to my q_split(collections_str, return)
	return collections_l
end q_collection

on q_tag(query)
	set tag_query to "
		select items.key
		from items, tags, itemTags
		where
			items.itemID = itemTags.itemID
			and tags.tagID = itemTags.tagID
			and tags.name LIKE '%" & query & "%'"
	
	--Every item in a tag with query in title
	set tags_str to do shell script head & tag_query & tail
	
	set tags_l to my q_split(tags_str, return)
	return tags_l
end q_tag

on q_attachment(query)
	set attachment_query to "
		select items.key
		from items, itemAttachments
		where 
			items.itemID = itemAttachments.sourceItemID
			and itemAttachments.path LIKE '%" & query & "%'"
	
	--Every item with query in attachment path
	set attachments_str to do shell script head & attachment_query & tail
	
	set attachments_l to my q_split(attachments_str, return)
	return attachments_l
end q_attachment

on q_note(query)
	set note_query to "
		select items.key
		from items, itemNotes
		where 
			items.itemID = itemNotes.sourceItemID
			and itemNotes.note LIKE '%" & query & "%'"
	
	--Every item with query in item note
	set notes_str to do shell script head & note_query & tail
	
	set notes_l to my q_split(notes_str, return)
	return notes_l
end q_note

on q_general(query)
	set notes_l to my q_note(query)
	set attx_l to my q_attachment(query)
	set tags_l to my q_tag(query)
	set colls_l to my q_collection(query)
	set auths_l to my q_author(query)
	set titles_l to my q_title(query)
	
	set gen_l to notes_l & attx_l & tags_l & colls_l & auths_l & titles_l
	return gen_l
end q_general


(* SUB-ROUTINES *)

on q_split(s, delim)
	set oldDelims to AppleScript's text item delimiters
	set AppleScript's text item delimiters to delim
	set output to text items of s
	set AppleScript's text item delimiters to oldDelims
	return output
end q_split