You are right… Looks like we need real fix
Any updates on this since October?
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
3 Likes
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.
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
2 Likes
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
6 Likes
Thanks a whole bunch! I’ve been missing this functionality for a LONG time.
