OF2 change database schema? Impact Rob Trew apple scripts


#21

You are right… Looks like we need real fix


#22

Any updates on this since October?


#23

The Quick Stats script was originally written for OmniFocus 1. To make the whole thing work with OmniFocus 3, it would need to be updated to support the many-to-many relationship which now exists between between the task table (representing actions) and the context table (representing tags). (For the SQL-minded out there, you’d do this via the TaskToTag join table.)

I’m not sure that pursuing that sort of update is a great investment of effort right now, given that JavaScript-based scripting in OmniFocus is right around the corner. But here’s a quick-and-dirty update to the script which simply removes the problematic action stats that referenced contexts.

property pTitle : "OmniFocus: Quick Stats"
property pVersion : "3.0(kc-draft)"

property pstrDBPath : ""
property pblnToClipboard : true
property pblnSubTreeCounts : true
property pToClipboard : "Copy list to clipboard"

-- Ver 0.8 adds clipboard option to dialogue
-- Ver 0.9 gives an error message if the cache schema has changed, leading to an SQL error in the script
-- Ver 1.0 slight simplification of the code
-- Ver 1.1 added count of Pending projects
-- Ver 1.2 added a count of available actions
-- Ver 1.3 added a break-down of unavailable actions
-- Ver 1.4 added count of Current projects to complement Pending projects
-- ver 1.5 replaced Applescript time function with SQL time expression
-- Ver 1.7 Reorganizes menu, and attempts to enable access for .macappstore installations of OF
--Ver 1.8 Adjusts handling of variant bundle identifiers generally
-- Ver 2.00 Redraft of task breakdown, with progressive narrowing of criteria ...
-- Ver 2.01 Change for OF2.  pstrDBPath reset
-- Ver 3.0(kc) Updated for OmniFocus 3.

set pstrDBPath to GetCachePath()

tell application "Finder"
	if pstrDBPath ≠ "" then
		set strCmd to "sqlite3 -separator ': ' \"" & pstrDBPath & "\" " & quoted form of ("
	select \"INBOX GROUPS & ACTIONS\", count(*) from task where (inInbox=1);
	select \"    Inbox action groups\", count(*) from task where (inInbox=1) and (childrenCount>0);
	select \"    Inbox actions\", count(*) from task where (inInbox=1) and (childrenCount=0);
	select null;
	select \"FOLDERS\"	, count(*) from folder;
	select \"    Active folders\", count(*) from folder where effectiveActive=1;
	select \"    Dropped folders\", count(*) from folder where effectiveActive=0;
	select null;
	select \"PROJECTS\", count(*) from projectInfo where containsSingletonActions=0;
	select \"    Active projects\", count(*) from projectInfo where (containsSingletonActions=0) and (status=\"active\");
	select \"            Current projects\", count(*) from projectInfo p join task t on t.projectinfo=p.pk where (p.containsSingletonActions=0) and (p.folderEffectiveActive=1) and (p.status=\"active\") and (t.dateToStart is null or t.dateToStart < (strftime('%s','now') - strftime('%s','2001-01-01')));
	select \"            Pending projects\", count(*) from projectInfo p join task t on t.projectinfo=p.pk where (p.containsSingletonActions=0) and (p.folderEffectiveActive=1) and (p.status=\"active\") and (t.dateToStart > (strftime('%s','now') - strftime('%s','2001-01-01')));
	select \"    On-hold projects\", count(*) from projectInfo where (containsSingletonActions=0) and (status=\"inactive\");
	select \"    Completed projects\", count(*) from projectInfo where (containsSingletonActions=0) and (status=\"done\");
	select \"    Dropped projects\", count(*) from projectInfo where (containsSingletonActions=0) and (( status=\"dropped\") or (folderEffectiveActive=0));
	select null;	
	select \"SINGLE ACTION LISTS\", count(*) from projectInfo where containsSingletonActions=1;
	select \"    Active single action lists\", count(*) from projectInfo where (containsSingletonActions=1) and (status=\"active\");
	select \"    On-hold single action lists\", count(*) from projectInfo where (containsSingletonActions=1) and (status=\"inactive\");
	select \"    Completed single action lists\", count(*) from projectInfo where (containsSingletonActions=1) and (status=\"done\");
	select \"    Dropped single action lists\", count(*) from projectInfo where (containsSingletonActions=1) and (( status=\"dropped\") or (folderEffectiveActive=0));
	select null;
	select \"CONTEXTS\", count(*) from context;
	select \"    Active contexts\", count(*) from context where (effectiveActive=1) and (allowsNextAction=1);
	select \"    On-hold contexts\", count(*) from context where (effectiveActive=1) and allowsNextAction=0;
	select \"    Dropped contexts\", count(*) from context where effectiveActive=0;
	select null;
	select \"ACTION GROUPS\", count(*) from task where (projectinfo is null) and (childrenCount>0);
	select \"    Remaining action groups\", count(*) from task where (projectinfo is null) and (dateCompleted is null) and (childrenCount>0);
	select \"    Completed action groups\", count(dateCompleted) from task where (projectinfo is null) and (childrenCount>0);
	select null;
	select \"ACTIONS\", count(*) from task where (projectinfo is null) and (childrenCount=0);
	select \"    Completed actions\", count(dateCompleted) from task where (projectinfo is null) and (childrenCount=0);
	select \"    Dropped project actions\", count(*) from (task t left join projectinfo p on t.containingProjectinfo=p.pk) tp where (projectinfo is null) and (childrenCount=0)  and (dateCompleted is null) 
				and (tp.containingProjectinfo is not null and (tp.status=\"dropped\" or tp.folderEffectiveActive=0));
")
		
		(* "
	select \"    Dropped context actions\", count(*) from (task t left join projectinfo p on t.containingProjectinfo=p.pk) tp left join context c on tp.context=c.persistentIdentifier where (projectinfo is null) and (tp.childrenCount=0)  and (dateCompleted is null) 
				and (tp.containingProjectinfo is null or (tp.status !=\"dropped\" and tp.folderEffectiveActive=1))
				and c.effectiveActive= 0;
	select \"    Remaining actions\", count(*) from (task t left join projectinfo p on t.containingProjectinfo=p.pk) tp left join context c on tp.context=c.persistentIdentifier where (projectinfo is null) and (tp.childrenCount=0)  and (dateCompleted is null) 
				and (tp.containingProjectinfo is null or (tp.status !=\"dropped\" and tp.folderEffectiveActive=1))
				and (tp.context is null or c.effectiveActive= 1);
	select null;
	select \"        Actions in Projects on hold\", count(*) from (task t left join projectinfo p on t.containingProjectinfo=p.pk) tp left join context c on tp.context=c.persistentIdentifier where (projectinfo is null) and (tp.childrenCount=0)  and (dateCompleted is null) 
				and (tp.containingProjectinfo is null or (tp.status !=\"dropped\" and tp.folderEffectiveActive=1))
				and (tp.context is null or c.effectiveActive= 1)
				and (tp.containingProjectInfo is not null and tp.status=\"inactive\");
	select \"        Actions in Contexts on hold\", count(*) from (task t left join projectinfo p on t.containingProjectinfo=p.pk) tp left join context c on tp.context=c.persistentIdentifier where (projectinfo is null) and (tp.childrenCount=0)  and (dateCompleted is null) 
				and (tp.containingProjectinfo is null or (tp.status !=\"dropped\" and tp.folderEffectiveActive=1))
				and (tp.context is null or c.effectiveActive= 1)
				and (tp.containingProjectInfo is null or tp.status!=\"inactive\")
				and (tp.context is not null and c.allowsNextAction=0);
	select null;
	select \"        Blocked actions\", count(*) from (task t left join projectinfo p on t.containingProjectinfo=p.pk) tp left join context c on tp.context=c.persistentIdentifier where (projectinfo is null) and (tp.childrenCount=0)  and (dateCompleted is null) 
				and (tp.containingProjectinfo is null or (tp.status !=\"dropped\" and tp.folderEffectiveActive=1))
				and (tp.context is null or c.effectiveActive= 1)
				and (tp.containingProjectInfo is null or tp.status!=\"inactive\")
				and (tp.context is null or c.allowsNextAction=1)
				and tp.blocked=1;
	select \"        	Sequentially blocked\", count(*) from (task t left join projectinfo p on t.containingProjectinfo=p.pk) tp left join context c on tp.context=c.persistentIdentifier where (projectinfo is null) and (tp.childrenCount=0)  and (dateCompleted is null) 
				and (tp.containingProjectinfo is null or (tp.status !=\"dropped\" and tp.folderEffectiveActive=1))
				and (tp.context is null or c.effectiveActive= 1)
				and (tp.containingProjectInfo is null or tp.status!=\"inactive\")
				and (tp.context is null or c.allowsNextAction=1)
				and tp.blocked=1
				and tp.blockedByFutureStartDate=0;
	select \"        	Awaiting start date\", count(*) from (task t left join projectinfo p on t.containingProjectinfo=p.pk) tp left join context c on tp.context=c.persistentIdentifier where (projectinfo is null) and (tp.childrenCount=0)  and (dateCompleted is null) 
				and (tp.containingProjectinfo is null or (tp.status !=\"dropped\" and tp.folderEffectiveActive=1))
				and (tp.context is null or c.effectiveActive= 1)
				and (tp.containingProjectInfo is null or tp.status!=\"inactive\")
				and (tp.context is null or c.allowsNextAction=1)
				and tp.blocked=1
				and tp.blockedByFutureStartDate=1;				
	select null;			
	select \"        Available actions\", count(*) from (task t left join projectinfo p on t.containingProjectinfo=p.pk) tp left join context c on tp.context=c.persistentIdentifier where (projectinfo is null) and (tp.childrenCount=0)  and (dateCompleted is null) 
				and (tp.containingProjectinfo is null or (tp.status !=\"dropped\" and tp.folderEffectiveActive=1))
				and (tp.context is null or c.effectiveActive= 1)
				and (tp.containingProjectInfo is null or tp.status!=\"inactive\")
				and (tp.context is null or c.allowsNextAction=1)
				and tp.blocked=0;
")
*)
		
		-- 		try
		
		set strList to do shell script strCmd
		-- 		on error
		-- 			display dialog "The SQL schema for the OmniFocus cache may have changed in a recent update of OF." & return & return & ¬
		-- 				"Look on the OmniFocus user forums for an updated version of this script." buttons {"OK"} with title pTitle & "Ver. " & pVersion
		-- 			return
		-- 		end try
		
		activate
		if button returned of (display dialog strList buttons {pToClipboard, "OK"} default button "OK" with title pTitle & " Ver. " & pVersion) ¬
			is pToClipboard then tell application id "com.apple.finder" to set the clipboard to strList
	else
		display dialog "OmniFocus cache not found ..." buttons {"OK"} default button 1 with title pTitle & " Ver. " & pVersion
	end if
end tell

on FileExists(strPath)
	(do shell script "test -e " & strPath & " ; echo $?") = "0"
end FileExists

on GetCachePath()
	try
		tell application "Finder" to tell (application file id "OFOC") to "$HOME/Library/Containers/" & its id & "/Data/Library/Application Support/OmniFocus/OmniFocus Caches/OmniFocusDatabase"
	on error
		error "OmniFocus not installed ..."
	end try
end GetCachePath

#24

Latest Omnifocus update broke this script now.

Error is “The operation couldn’t be completed. *** OmniFocus3/OFStats-revised.scpt: execution error: Finder got an error: Error: no such column: effectiveActive (1)”

Getting the statistics of numbers of active, dropped, stalled and completed projects, actions contexts etc. is highly useful.


#25

Here is a fully re-worked copy of this script that works with OmniFocus 3.0 – 3.3.2:

(*
	Ver 0.8 adds clipboard option to dialogue
	Ver 0.9 gives an error message if the cache schema has changed, leading to an SQL error in the script
	Ver 1.0 slight simplification of the code
	Ver 1.1 added count of Pending projects
	Ver 1.2 added a count of available actions
	Ver 1.3 added a break-down of unavailable actions
	Ver 1.4 added count of Current projects to complement Pending projects
	Ver 1.5 replaced Applescript time function with SQL time expression
	Ver 1.7 Reorganizes menu, and attempts to enable access for .macappstore installations of OF
	Ver 1.8 Adjusts handling of variant bundle identifiers generally
	Ver 2.00 Redraft of task breakdown, with progressive narrowing of criteria ...
	Ver 2.01 Change for OF2.  pstrDBPath reset
	Ver 3.0 Reworked for OF3: 
				- Updated query to match schema changes for tags
				- Automatically checks both Omni Store and MAS containers for SQL database
				- dialog presented when there is a SQL error now includes the specific SQL error message returned
				- Reorganized the code, mostly separating out the query string as a property 
*)
			

property pTitle : "OmniFocus: Quick Stats"
property pVersion : "3.0"

property pstrOmniStoreDBPath : "$HOME/Library/Containers/com.omnigroup.OmniFocus3/Data/Library/Application Support/OmniFocus/OmniFocus Caches/OmniFocusDatabase"
property pstrMacAppStoreDBPath : "$HOME/Library/Containers/com.omndigroup.OmniFocus3.MacAppStore/Data/Library/Application Support/OmniFocus/OmniFocus Caches/OmniFocusDatabase"

property pTimeout : 30
property pblnToClipboard : true
property pToClipboard : "Copy list to clipboard"

property pstrQuery : ¬
"	SELECT \"INBOX ITEMS\", COUNT(*) FROM task 
									WHERE (inInbox=1);
	SELECT \"		Inbox action groups\", COUNT(*) FROM task 
									WHERE (inInbox=1) AND (childrenCount>0);
	SELECT \"		Inbox actions\", COUNT(*) FROM task 
									WHERE (inInbox=1) AND (childrenCount=0);

	SELECT null;

	SELECT \"FOLDERS\", COUNT(*) FROM folder;
	SELECT \"		Active folders\", COUNT(*) FROM folder 
									WHERE effectiveActive=1;
	SELECT \"		Dropped folders\", COUNT(*) FROM folder 
									WHERE effectiveActive=0;

	SELECT null;

	SELECT \"PROJECTS\", COUNT(*) FROM projectInfo 
									WHERE containsSingletonActions=0;
	SELECT \"		Active projects\", COUNT(*) FROM projectInfo 
									WHERE (containsSingletonActions=0) AND (status=\"active\");
	SELECT \"			Current (available) projects\", COUNT(*) FROM projectInfo p 
									JOIN task t on t.projectinfo=p.pk 
									WHERE (p.containsSingletonActions=0) AND (p.folderEffectiveActive=1) AND (p.status=\"active\") 
									AND (t.dateToStart is null OR t.dateToStart < (strftime('%s','now') - strftime('%s','2001-01-01')));
	SELECT \"			Pending (deferred) projects\", COUNT(*) FROM projectInfo p 
									JOIN task t on t.projectinfo=p.pk 
									WHERE (p.containsSingletonActions=0) AND (p.folderEffectiveActive=1) AND (p.status=\"active\") 
									AND (t.dateToStart > (strftime('%s','now') - strftime('%s','2001-01-01')));
	SELECT \"		On Hold projects\", COUNT(*) FROM projectInfo 
									WHERE (containsSingletonActions=0) AND (status=\"inactive\");
	SELECT \"		Completed projects\", COUNT(*) FROM projectInfo 
									WHERE (containsSingletonActions=0) AND (status=\"done\");
	SELECT \"		Dropped projects\", COUNT(*) FROM projectInfo 
									WHERE (containsSingletonActions=0) AND (( status=\"dropped\") OR (folderEffectiveActive=0));

	SELECT null;

	SELECT \"SINGLE ACTION LISTS\", COUNT(*) FROM projectInfo 
									WHERE containsSingletonActions=1;
	SELECT \"		Active single action lists\", COUNT(*) FROM projectInfo 
									WHERE (containsSingletonActions=1) AND (status=\"active\");
	SELECT \"		On Hold single action lists\", COUNT(*) FROM projectInfo 
									WHERE (containsSingletonActions=1) AND (status=\"inactive\");
	SELECT \"		Completed single action lists\", COUNT(*) FROM projectInfo 
									WHERE (containsSingletonActions=1) AND (status=\"done\");
	SELECT \"		Dropped single action lists\", COUNT(*) FROM projectInfo 
									WHERE (containsSingletonActions=1) AND ((status=\"dropped\") OR (folderEffectiveActive=0));

	SELECT null;

	SELECT \"TAGS\", COUNT(*) FROM context;
	SELECT \"		Active tags\", COUNT(*) FROM context 
									WHERE (effectiveActive=1) AND (allowsNextAction=1);
	SELECT \"		On Hold tags\", COUNT(*) FROM context 
									WHERE (effectiveActive=1) AND (allowsNextAction=0);
	SELECT \"		Dropped tags\", COUNT(*) FROM context 
									WHERE effectiveActive=0;

	SELECT null;

	SELECT \"ACTION GROUPS\", COUNT(*) FROM task 
									WHERE (projectinfo is null) AND (childrenCount>0);
	SELECT \"		Remaining action groups\", COUNT(*) FROM task 
									WHERE (projectinfo is null) AND (dateCompleted is null) AND (childrenCount>0);
	SELECT \"		Completed action groups\", COUNT(*) FROM task 
									WHERE (projectinfo is null) AND (dateCompleted is not null) AND (childrenCount>0);
	SELECT \"		Dropped action groups\", COUNT(*) FROM task 
									WHERE (projectinfo is null) AND (childrenCount>0);

	SELECT null;
	
	SELECT \"ACTIONS\", COUNT(*) FROM task 
									WHERE (projectinfo is null) AND (childrenCount=0);
	SELECT \"		Remaining actions\", COUNT(*) FROM task t 
									LEFT JOIN projectinfo p on t.containingProjectinfo=p.pk 
									LEFT JOIN tasktotag tg on t.persistentIdentifier=tg.task 
									LEFT JOIN context c on tg.tag=c.persistentIdentifier 
									WHERE (t.projectinfo is null) AND (t.childrenCount=0) AND (t.dateCompleted is null) 
									AND (t.containingProjectinfo is null OR (p.status !=\"dropped\" AND p.folderEffectiveActive=1)) 
									AND (t.tagged=0 OR c.effectiveActive=1);
	SELECT \"			Available actions\", COUNT(*) FROM task t 
									LEFT JOIN projectinfo p on t.containingProjectinfo=p.pk 
									LEFT JOIN tasktotag tg on t.persistentIdentifier=tg.task 
									LEFT JOIN context c on tg.tag=c.persistentIdentifier 
									WHERE (t.projectinfo is null) AND (t.childrenCount=0)  AND (t.dateCompleted is null) 
									AND (t.containingProjectinfo is null OR (p.status !=\"dropped\" AND p.folderEffectiveActive=1)) 
									AND (t.tagged=0 OR c.effectiveActive=1) AND (t.containingProjectInfo is null OR p.status!=\"inactive\") 
									AND (t.tagged=0 OR c.allowsNextAction=1) AND t.blocked=0;
	SELECT \"			Actions in On Hold projects\", COUNT(*) FROM task t 
									LEFT JOIN projectinfo p on t.containingProjectinfo=p.pk 
									LEFT JOIN tasktotag tg on t.persistentIdentifier=tg.task 
									LEFT JOIN context c on tg.tag=c.persistentIdentifier 
									WHERE (t.projectinfo is null) AND (t.childrenCount=0) AND (t.dateCompleted is null) 
									AND (t.containingProjectinfo is null OR (p.status !=\"dropped\" AND p.folderEffectiveActive=1)) 
									AND (t.tagged=0 OR c.effectiveActive=1) AND (t.containingProjectInfo is not null AND p.status=\"inactive\");
	SELECT \"			Actions with On Hold tags\", COUNT(*) FROM task t 
									LEFT JOIN projectinfo p on t.containingProjectinfo=p.pk 
									LEFT JOIN tasktotag tg on t.persistentIdentifier=tg.task 
									LEFT JOIN context c on tg.tag=c.persistentIdentifier 
									WHERE (t.projectinfo is null) AND (t.childrenCount=0) AND (t.dateCompleted is null) 
									AND (t.containingProjectinfo is null OR (p.status !=\"dropped\" AND p.folderEffectiveActive=1)) 
									AND (t.tagged=0 OR c.effectiveActive=1) AND (t.containingProjectInfo is null OR p.status=\"inactive\") 
									AND (t.tagged=1 AND c.allowsNextAction=0);
	SELECT \"			Blocked actions\", COUNT(*) FROM task t 
									LEFT JOIN projectinfo p on t.containingProjectinfo=p.pk 
									LEFT JOIN tasktotag tg on t.persistentIdentifier=tg.task 
									LEFT JOIN context c on tg.tag=c.persistentIdentifier 
									WHERE (t.projectinfo is null) AND (t.childrenCount=0) AND (t.dateCompleted is null) 
									AND (t.containingProjectinfo is null OR (p.status !=\"dropped\" AND p.folderEffectiveActive=1)) 
									AND (t.tagged=0 OR c.effectiveActive=1) AND (t.containingProjectInfo is  null OR p.status!=\"inactive\") 
									AND (t.tagged=0 OR c.allowsNextAction=1) AND t.blocked=1;
	SELECT \"				Blocked by future defer date\", COUNT(*) FROM task t 
									LEFT JOIN projectinfo p on t.containingProjectinfo=p.pk 
									LEFT JOIN tasktotag tg on t.persistentIdentifier=tg.task 
									LEFT JOIN context c on tg.tag=c.persistentIdentifier 
									WHERE (t.projectinfo is null) AND (t.childrenCount=0) AND (t.dateCompleted is null) 
									AND (t.containingProjectinfo is null OR (p.status !=\"dropped\" AND p.folderEffectiveActive=1)) 
									AND (t.tagged=0 OR c.effectiveActive=1) AND (t.containingProjectInfo is null AND p.status!=\"inactive\") 
									AND (t.tagged=0 OR c.allowsNextAction=1) AND t.blocked=1 AND t.blockedByFutureStartDate=1;
	SELECT \"				Sequentially blocked\", COUNT(*) FROM task t 
									LEFT JOIN projectinfo p on t.containingProjectinfo=p.pk 
									LEFT JOIN tasktotag tg on t.persistentIdentifier=tg.task 
									LEFT JOIN context c on tg.tag=c.persistentIdentifier 
									WHERE (t.projectinfo is null) AND (t.childrenCount=0) AND (t.dateCompleted is null) 
									AND (t.containingProjectinfo is null OR (p.status !=\"dropped\" AND p.folderEffectiveActive=1)) 
									AND (t.tagged=0 OR c.effectiveActive=1) AND (t.containingProjectInfo is null OR p.status!=\"inactive\") 
									AND (t.tagged=0 OR c.allowsNextAction=1) AND t.blocked=1 AND t.blockedByFutureStartDate=0;
	SELECT \"		Completed actions\", COUNT(dateCompleted) FROM task 
									WHERE (projectinfo is null) AND (childrenCount=0);		
	SELECT \"		Dropped project actions\", COUNT(*) FROM task t 
									LEFT JOIN projectinfo p on t.containingProjectinfo=p.pk 
									WHERE (t.projectinfo is null) AND (childrenCount=0)  AND (t.dateCompleted is null) 
									AND (t.containingProjectinfo is not null AND (p.status=\"dropped\" OR p.folderEffectiveActive=0));
	SELECT \"		Dropped tag actions\", COUNT(*) FROM task t 
									LEFT JOIN projectinfo p on t.containingProjectinfo=p.pk  
									LEFT JOIN tasktotag tg on t.persistentIdentifier=tg.task  
									LEFT JOIN context c on tg.tag=c.persistentIdentifier  
									WHERE (t.projectinfo is null) AND (t.childrenCount=0)  AND (t.dateCompleted is null)  
									AND (t.containingProjectinfo is null OR (p.status !=\"dropped\" AND p.folderEffectiveActive=1)) 
									AND (t.tagged=1 AND c.effectiveActive=0);
"



on run
		
	-- Check to see if there is a OmniFocus SQL database cache
	set omniStorPathExists to checkPathExists(pstrOmniStoreDBPath)
	set macAppStorePathExists to checkPathExists(pstrMacAppStoreDBPath)
	
	if omniStorPathExists then
		set strDBPath to pstrOmniStoreDBPath
	else if macAppStorePathExists then
		set strDBPath to pstrMacAppStoreDBPath
	else
		display dialog "OmniFocus cache (SQL database) not found ..." buttons {"OK"} default button 1 ¬
			with title pTitle & " Ver. " & pVersion
		return
	end if
	
	try
		-- Run query to get list of stats
		set strStatsList to do shell script "sqlite3 -separator ': ' \"" & strDBPath & "\" " & quoted form of queryString
		
	on error errMsg
		display dialog ¬
			errMsg & ¬
			return & return & ¬
			"The SQL schema for the OmniFocus cache may have changed in a recent update of OF." & ¬
			return & return & ¬
			"Look on the OmniFocus user forums for an updated version of this script." buttons {"OK"} ¬
			with title pTitle & "Ver. " & pVersion
		return
	end try

	--Show the results	
	activate
	set statsWindow to display dialog strStatsList buttons {pToClipboard, "OK"} default button ¬	
		"OK" with title pTitle & " Ver. " & pVersion giving up after pTimeout
	
	if button returned of statsWindow is pToClipboard then set the clipboard to strStatsList
	
end run


on checkPathExists(strDBPath)
	set pathExists to do shell script "test -f \"" & strDBPath & "\" && echo 'true' || echo 'false'"
	
	if pathExists is "true" then
		return true
	else
		return false
	end if
end checkPathExists

#26

And then here is another copy updated to work with OmniFocus 3.4 +:

(*
	Ver 0.8		adds clipboard option to dialogue
	Ver 0.9 	gives an error message if the cache schema has changed, leading to an SQL error in the script
	Ver 1.0 	slight simplification of the code
	Ver 1.1 	added count of Pending projects
	Ver 1.2 	added a count of available actions
	Ver 1.3 	added a break-down of unavailable actions
	Ver 1.4 	added count of Current projects to complement Pending projects
	Ver 1.5 	replaced Applescript time function with SQL time expression
	Ver 1.7 	reorganizes menu, and attempts to enable access for .macappstore installations of OF
	Ver 1.8 	adjusts handling of variant bundle identifiers generally
	Ver 2.00 	redraft of task breakdown, with progressive narrowing of criteria ...
	Ver 2.01 	change for OF2.  pstrDBPath reset
	Ver 3.0 	reworked for OF3: 
					- Updated query to match schema changes for tags
					- Automatically checks both Omni Store and MAS containers for SQL database
					- dialog presented when there is a SQL error now includes the specific SQL error message returned
					- Reorganized the code, mostly separating out the query string as a property 
	Ver 3.1		adjusts for schema change from dropped task feature added in OF v3.4
*)


property pTitle : "OmniFocus: Quick Stats"
property pVersion : "3.1"

property pstrOmniStoreDBPath : "$HOME/Library/Containers/com.omnigroup.OmniFocus3/Data/Library/Application Support/OmniFocus/OmniFocus Caches/OmniFocusDatabase"
property pstrMacAppStoreDBPath : "$HOME/Library/Containers/com.omndigroup.OmniFocus3.MacAppStore/Data/Library/Application Support/OmniFocus/OmniFocus Caches/OmniFocusDatabase"

property pblnToClipboard : true
property pblnSubTreeCounts : true
property pToClipboard : "Copy list to clipboard"
property enableDebug : true

property pstrQuery : ¬
	"	SELECT \"INBOX ITEMS\", COUNT(*) FROM task 
									WHERE (inInbox=1);
	SELECT \"		Inbox action groups\", COUNT(*) FROM task 
									
									WHERE (inInbox=1) AND (childrenCount>0);
	SELECT \"		Inbox actions\", COUNT(*) FROM task 
									
									WHERE (inInbox=1) AND (childrenCount=0);
	
	SELECT null;

	SELECT \"FOLDERS\", COUNT(*) FROM folder;
	SELECT \"		Active folders\", COUNT(*) FROM folder 
									WHERE effectiveDateHidden is null;
	SELECT \"		Dropped folders\", COUNT(*) FROM folder 
									WHERE effectiveDateHidden is not null;

	SELECT null;

	SELECT \"PROJECTS\", COUNT(*) FROM projectInfo 
									WHERE containsSingletonActions=0;
	SELECT \"		Active projects\", COUNT(*) FROM projectInfo 
									WHERE (containsSingletonActions=0) AND (status=\"active\");
	SELECT \"			Current (available) projects\", COUNT(*) FROM projectInfo p join task t on t.projectinfo=p.pk 
									WHERE (p.containsSingletonActions=0) AND (p.folderEffectiveDateHidden is null) AND (p.status=\"active\") 
									AND (t.dateToStart is null OR t.dateToStart < (strftime('%s','now') - strftime('%s','2001-01-01')));
	SELECT \"			Pending (deferred) projects\", COUNT(*) FROM projectInfo p join task t on t.projectinfo=p.pk 
									WHERE (p.containsSingletonActions=0) AND (p.folderEffectiveDateHidden is null) AND (p.status=\"active\") 
									AND (t.dateToStart > (strftime('%s','now') - strftime('%s','2001-01-01')));
	SELECT \"		On Hold projects\", COUNT(*) FROM projectInfo 
									WHERE (containsSingletonActions=0) AND (status=\"inactive\");
	SELECT \"		Completed projects\", COUNT(*) FROM projectInfo 
									WHERE (containsSingletonActions=0) AND (status=\"done\");
	SELECT \"		Dropped projects\", COUNT(*) FROM projectInfo 
									WHERE (containsSingletonActions=0) AND (( status=\"dropped\") OR (folderEffectiveDateHidden is not null));

	SELECT null;

	SELECT \"SINGLE ACTION LISTS\", COUNT(*) FROM projectInfo 
									WHERE containsSingletonActions=1;
	SELECT \"		Active single action lists\", COUNT(*) FROM projectInfo 
									WHERE (containsSingletonActions=1) AND (status=\"active\");
	SELECT \"		On Hold single action lists\", COUNT(*) FROM projectInfo 
									WHERE (containsSingletonActions=1) AND (status=\"inactive\");
	SELECT \"		Completed single action lists\", COUNT(*) FROM projectInfo 
									WHERE (containsSingletonActions=1) AND (status=\"done\");
	SELECT \"		Dropped single action lists\", COUNT(*) FROM projectInfo 
									WHERE (containsSingletonActions=1) AND ((status=\"dropped\") OR (folderEffectiveDateHidden is not null));

	SELECT null;

	SELECT \"TAGS\", COUNT(*) FROM context;
	SELECT \"		Active tags\", COUNT(*) FROM context 
									WHERE (effectiveDateHidden is null) AND (allowsNextAction=1);
	SELECT \"		On Hold tags\", COUNT(*) FROM context 
									WHERE (effectiveDateHidden is null) AND (allowsNextAction=0);
	SELECT \"		Dropped tags\", COUNT(*) FROM context 
									WHERE effectiveDateHidden is not null;

	SELECT null;

	SELECT \"ACTION GROUPS\", COUNT(*) FROM task 
									WHERE (projectinfo is null) AND (childrenCount>0);
	SELECT \"		Remaining action groups\", COUNT(*) FROM task 
									WHERE (projectinfo is null) AND (dateCompleted is null) AND (effectiveDateHidden is null) AND (childrenCount>0);
	SELECT \"		Completed action groups\", COUNT(*) FROM task 
									WHERE (projectinfo is null) AND (dateCompleted is not null) AND (childrenCount>0);
	SELECT \"		Dropped action groups\", COUNT(*) FROM task 
									WHERE (projectinfo is null) AND (effectiveDateHidden is not null) AND (childrenCount>0);

	SELECT null;
		
	SELECT \"ACTIONS\", COUNT(*) FROM task 
									WHERE (projectinfo is null) AND (childrenCount=0);
	SELECT \"		Remaining actions\", COUNT(*) FROM task t 
									LEFT JOIN projectinfo p on t.containingProjectinfo=p.pk 
									LEFT JOIN tasktotag tg on t.persistentIdentifier=tg.task 
									LEFT JOIN context c on tg.tag=c.persistentIdentifier 
									WHERE (t.projectinfo is null) AND (t.childrenCount=0) AND (t.dateCompleted is null) 
									AND (t.containingProjectinfo is null OR (p.status !=\"dropped\" AND p.folderEffectiveDateHidden is null)) 
									AND (t.tagged=0 OR c.effectiveDateHidden is null);
	SELECT \"			Available actions\", COUNT(*) FROM task t 
									LEFT JOIN projectinfo p on t.containingProjectinfo=p.pk 
									LEFT JOIN tasktotag tg on t.persistentIdentifier=tg.task 
									LEFT JOIN context c on tg.tag=c.persistentIdentifier 
									WHERE (t.projectinfo is null) AND (t.childrenCount=0) AND (t.dateCompleted is null) 
									AND (t.containingProjectinfo is null OR (p.status !=\"dropped\" AND p.folderEffectiveDateHidden is null)) 
									AND (t.tagged=0 OR c.effectiveDateHidden is null) AND (t.containingProjectInfo is null OR p.status!=\"inactive\") 
									AND (t.tagged=0 OR c.allowsNextAction=1) AND t.blocked=0;
	SELECT \"			Actions in On Hold projects\", COUNT(*) FROM task t 
									LEFT JOIN projectinfo p on t.containingProjectinfo=p.pk 
									LEFT JOIN tasktotag tg on t.persistentIdentifier=tg.task 
									LEFT JOIN context c on tg.tag=c.persistentIdentifier 
									WHERE (t.projectinfo is null) AND (t.childrenCount=0) AND (t.dateCompleted is null) 
									AND (t.containingProjectinfo is null OR (p.status !=\"dropped\" AND p.folderEffectiveDateHidden is null)) 
									AND (t.tagged=0 OR c.effectiveDateHidden is null) AND (t.containingProjectInfo is not null AND p.status=\"inactive\");
	SELECT \"			Actions with On Hold tags\", COUNT(*) FROM task t 
									LEFT JOIN projectinfo p on t.containingProjectinfo=p.pk 
									LEFT JOIN tasktotag tg on t.persistentIdentifier=tg.task 
									LEFT JOIN context c on tg.tag=c.persistentIdentifier 
									WHERE (t.projectinfo is null) AND (t.childrenCount=0) AND (t.dateCompleted is null) 
									AND (t.containingProjectinfo is null OR (p.status !=\"dropped\" AND p.folderEffectiveDateHidden is null)) 
									AND (t.tagged=0 OR c.effectiveDateHidden is null) AND (t.containingProjectInfo is null OR p.status=\"inactive\") 
									AND (t.tagged=1 AND c.allowsNextAction=0);
	SELECT \"			Blocked actions\", COUNT(*) FROM task t 
									LEFT JOIN projectinfo p on t.containingProjectinfo=p.pk 
									LEFT JOIN tasktotag tg on t.persistentIdentifier=tg.task 
									LEFT JOIN context c on tg.tag=c.persistentIdentifier 
									WHERE (t.projectinfo is null) AND (t.childrenCount=0) AND (t.dateCompleted is null) 
									AND (t.containingProjectinfo is null OR (p.status !=\"dropped\" AND p.folderEffectiveDateHidden is null)) 
									AND (t.tagged=0 OR c.effectiveDateHidden is null) AND (t.containingProjectInfo is  null OR p.status!=\"inactive\") 
									AND (t.tagged=0 OR c.allowsNextAction=1) AND t.blocked=1;
	SELECT \"				Blocked by future defer date\", COUNT(*) FROM task t 
									LEFT JOIN projectinfo p on t.containingProjectinfo=p.pk 
									LEFT JOIN tasktotag tg on t.persistentIdentifier=tg.task 
									LEFT JOIN context c on tg.tag=c.persistentIdentifier 
									WHERE (t.projectinfo is null) AND (t.childrenCount=0) AND (t.dateCompleted is null) 
									AND (t.containingProjectinfo is null OR (p.status !=\"dropped\" AND p.folderEffectiveDateHidden is null)) 
									AND (t.tagged=0 OR c.effectiveDateHidden is null) AND (t.containingProjectInfo is null AND p.status!=\"inactive\") 
									AND (t.tagged=0 OR c.allowsNextAction=1) AND t.blocked=1 AND t.blockedByFutureStartDate=1;
	SELECT \"				Sequentially blocked\", COUNT(*) FROM task t 
									LEFT JOIN projectinfo p on t.containingProjectinfo=p.pk 
									LEFT JOIN tasktotag tg on t.persistentIdentifier=tg.task 
									LEFT JOIN context c on tg.tag=c.persistentIdentifier 
									WHERE (t.projectinfo is null) AND (t.childrenCount=0) AND (t.dateCompleted is null) 
									AND (t.containingProjectinfo is null OR (p.status !=\"dropped\" AND p.folderEffectiveDateHidden is null)) 
									AND (t.tagged=0 OR c.effectiveDateHidden is null) AND (t.containingProjectInfo is null OR p.status!=\"inactive\") 
									AND (t.tagged=0 OR c.allowsNextAction=1) AND t.blocked=1 AND t.blockedByFutureStartDate=0;
	SELECT \"		Completed actions\", COUNT(dateCompleted) FROM task 
									WHERE (projectinfo is null) AND (childrenCount=0);		
	SELECT \"		Dropped actions\", COUNT (*) FROM task t  
									LEFT JOIN projectinfo p on t.containingProjectinfo=p.pk  
									LEFT JOIN tasktotag tg on t.persistentIdentifier=tg.task  
									LEFT JOIN context c on tg.tag=c.persistentIdentifier 
									WHERE (t.projectinfo is null AND t.childrenCount=0  AND t.dateCompleted is null) 
									AND ((t.effectiveDateHidden is not null) OR ((t.containingProjectinfo is not null) 
									AND (p.status=\"dropped\" OR p.folderEffectiveDateHidden is not null)) OR ((t.containingProjectinfo is null) 
									OR ((p.status !=\"dropped\") AND (p.folderEffectiveDateHidden is null)) AND (c.effectiveDateHidden is not null)));
	SELECT \"			Dropped directly\", COUNT(*) FROM task 
									WHERE (projectinfo is null) AND (childrenCount=0)  AND (dateCompleted is null) AND (effectiveDateHidden is not null);
	SELECT \"			Dropped by project\", COUNT(*) FROM task t 
									LEFT JOIN projectinfo p on t.containingProjectinfo=p.pk 
									WHERE (t.projectinfo is null) AND (childrenCount=0)  AND (t.dateCompleted is null) 
									AND (t.containingProjectinfo is not null AND (p.status=\"dropped\" OR p.folderEffectiveDateHidden is not null));
	SELECT \"			Dropped by tag\", COUNT(*) FROM task t 
									LEFT JOIN projectinfo p on t.containingProjectinfo=p.pk  
									LEFT JOIN tasktotag tg on t.persistentIdentifier=tg.task  
									LEFT JOIN context c on tg.tag=c.persistentIdentifier  
									WHERE (t.projectinfo is null) AND (t.childrenCount=0)  AND (t.dateCompleted is null)  
									AND (t.containingProjectinfo is null OR (p.status !=\"dropped\" AND p.folderEffectiveDateHidden is null)) 
									AND (t.tagged=1 AND c.effectiveDateHidden is not null);
"

on run
	
	-- Check to see if there is a OmniFocus SQL database cache
	set omniStorPathExists to checkPathExists(pstrOmniStoreDBPath)
	set macAppStorePathExists to checkPathExists(pstrMacAppStoreDBPath)
	
	if omniStorPathExists then
		set strDBPath to pstrOmniStoreDBPath
	else if macAppStorePathExists then
		set strDBPath to pstrMacAppStoreDBPath
	else
		display dialog "OmniFocus cache (SQL database) not found ..." buttons {"OK"} default button 1 ¬
			with title pTitle & " Ver. " & pVersion
		return
	end if
	
	try
		-- Run query to get list of stats
		set strStatsList to do shell script "sqlite3 -separator ': ' \"" & strDBPath & "\" " & quoted form of pstrQuery
		
	on error errMsg
		display dialog ¬
			errMsg & ¬
			return & return & ¬
			"The SQL schema for the OmniFocus cache may have changed in a recent update of OF." & ¬
			return & return & ¬
			"Look on the OmniFocus user forums for an updated version of this script." buttons {"OK"} ¬
			with title pTitle & "Ver. " & pVersion
		return
	end try
	
	--Show the results
	activate
	set statsWindow to display dialog strStatsList buttons {pToClipboard, "OK"} default button ¬
		"OK" with title pTitle & " Ver. " & pVersion
	
	if button returned of statsWindow is pToClipboard then set the clipboard to strStatsList
	
end run


on checkPathExists(strDBPath)
	set pathExists to do shell script "test -f \"" & strDBPath & "\" && echo 'true' || echo 'false'"
	
	if pathExists is "true" then
		return true
	else
		return false
	end if
end checkPathExists

#27

Thanks a whole bunch! I’ve been missing this functionality for a LONG time.