property pstrDBPath : "~/Library/Caches/com.omnigroup.OmniFocus/OmniFocusDatabase2"
property pEOR : "<eor>"
property pstrFieldDelimiter : "<fld>"

property plstResults : {}

-- ADJUST THE LIST OF FIELDS REQUIRED, AND THE SORT ORDER ...
set strSQL to "select status, name from projectInfo p join task t on p.pk=t.projectInfo  order by status, name"

-- Specifically PROJECT fields:
-- containsSingletonActions	integer
-- folder	text
-- folderEffectiveActive	integer
-- lastReviewDate	timestamp
-- minimumDueDate	timestamp
-- nextReviewDate	timestamp
-- nextTask	text
-- numberOfAvailableTasks	integer
-- numberOfDueSoonTasks	integer
-- numberOfOverdueTasks	integer
-- numberOfRemainingTasks	integer
-- reviewRepetitionString	text
-- status	text
-- task	text
-- taskBlocked	integer
-- taskBlockedByFutureStartDate	integer
-- taskDateToStart	timestamp

-- Fields shared by projects and task
-- blocked	integer
-- blockedByFutureStartDate	integer
-- childrenCount	integer
-- childrenCountAvailable	integer
-- childrenCountCompleted	integer
-- completeWhenChildrenComplete	integer
-- containingProjectContainsSingletons	integer
-- containingProjectInfo	text
-- containsNextTask	integer
-- context	text
-- creationOrdinal	integer
-- dateAdded	timestamp
-- dateCompleted	timestamp
-- dateDue	timestamp
-- dateModified	timestamp
-- dateToStart	timestamp
-- effectiveContainingProjectInfoActive	integer
-- effectiveDateDue	timestamp
-- effectiveDateToStart	timestamp
-- effectiveFlagged	integer
-- estimatedMinutes	integer
-- flagged	integer
-- hasCompletedDescendant	integer
-- hasFlaggedTaskInTree	integer
-- hasUnestimatedLeafTaskInTree	integer
-- inInbox	integer
-- isDueSoon	integer
-- isOverdue	integer
-- maximumEstimateInTree	integer
-- minimumEstimateInTree	integer
-- name	text
-- nextTaskOfProjectInfo	text
-- noteXMLData	blob
-- parent	text
-- persistentIdentifier	text
-- projectInfo	text
-- rank	integer
-- repetitionString	text
-- sequential	integer


RunSQL(strSQL)

on RunSQL(strSQL)
	strSQL
	set strCmd to "sqlite3 -separator '" & pstrFieldDelimiter & "' " & pstrDBPath & space & quoted form of strSQL
	set text item delimiters to pEOR & return -- strip out the end of line \r as well as the record delimiter
	set plstResults to (paragraphs of (do shell script strCmd))
	
	set text item delimiters to pstrFieldDelimiter
	repeat with i from 1 to length of plstResults
		set item i of plstResults to text items of (item i of plstResults)
	end repeat
	set text item delimiters to space
end RunSQL


-- Do something with plstResults (a list of records, each of which is a list of fields)
set str to ""
set text item delimiters to tab
repeat with i from 1 to length of plstResults
	set str to (str & (item i of plstResults) as string) & return
end repeat

set text item delimiters to space
display dialog str