The Omni Group
These forums are now read-only. Please visit our new forums to participate in discussion. A new account will be required to post in the new forums. For more info on the switch, see this post. Thank you!

Go Back   The Omni Group Forums > OmniFocus > OmniFocus Extras
FAQ Members List Calendar Search Today's Posts Mark Forums Read

 
Script for a quick listing of OmniFocus statistics Thread Tools Search this Thread Display Modes
Had a closer look at it and tried some stuff out, but as I've never dabbled with SQLite before, I don't really have much clue about what I'm doing. Anyhow, my question now is:

How does this
Quote:
select \" Available actions\", count(*) from (task t left join context c on t.context=c.persistentIdentifier) where (projectInfo is null) and (childrenCount=0) and (dateCompleted is null) and (blocked=0) and (effectiveContainingProjectInfoActive=1) and (blockedByFutureStartDate=0) and ((context is null) or ((allowsNextAction=1) and (active=1)));
relate to this
Quote:
select \" Available actions\", count(*) from (task t left join context c on t.context=c.persistentIdentifier) where (t.projectInfo is null) and (t.childrenCount=0) and (t.dateCompleted is null) and (t.blocked=0) and (t.effectiveContainingProjectInfoActive=1) and (t.blockedByFutureStartDate=0) and ((t.context is null) or ((c.allowsNextAction=1) and (c.active=1)));
considering the latter gives an SQL error and the former doesn't. Does the former still give the intended result, and, if so, is that what the code should look like?
 
Quote:
Originally Posted by mrubenson View Post
my question now is:
How does this
relate to this
considering the latter gives an SQL error and the former doesn't
Well the former should raise an error, because "childrenCount" is ambiguous - the task and context tables both have a childrenCount field ...

(Did I ask you what version of OS X you are running ? It begins to feel as if there is a different version of Sqlite running here ...).

On the other hand, the other fields are not ambiguous, so if you can get this slightly modified version to run, you should be fine:

Code:
select \" Available actions\", count(*) 
from (task t left join context c on t.context=c.persistentIdentifier) 
where (projectInfo is null) and (t.childrenCount=0) and (dateCompleted is null) 
and (blocked=0) and (effectiveContainingProjectInfoActive=1) 
and (blockedByFutureStartDate=0) and ((context is null) 
or ((allowsNextAction=1) and (active=1)));
Anyway, I salute you for diving into the code ...

Last edited by RobTrew; 2011-03-28 at 02:15 PM.. Reason: adjusted the sqlite code
 
PS I would be interested to know what version number this code displays on your system:

Code:
display dialog (do shell script "sqlite3 -version")
 
Quote:
Originally Posted by RobTrew View Post
PS I would be interested to know what version number this code displays on your system:

Code:
display dialog (do shell script "sqlite3 -version")
3.4.0

I'm on OS X 10.5.8.
 
Quote:
Originally Posted by RobTrew View Post
Well the former should raise an error, because "childrenCount" is ambiguous - the task and context tables both have a childrenCount field ...

(Did I ask you what version of OS X you are running ? It begins to feel as if there is a different version of Sqlite running here ...).

On the other hand, the other fields are not ambiguous, so if you can get this slightly modified version to run, you should be fine:

Code:
select \" Available actions\", count(*) 
from (task t left join context c on t.context=c.persistentIdentifier) 
where (projectInfo is null) and (t.childrenCount=0) and (dateCompleted is null) 
and (blocked=0) and (effectiveContainingProjectInfoActive=1) 
and (blockedByFutureStartDate=0) and ((context is null) 
or ((allowsNextAction=1) and (active=1)));
Anyway, I salute you for diving into the code ...

Nope, that doesn't work. I get:

Code:
SQL error: no such column: t.childrenCount
 
Quote:
Originally Posted by mrubenson View Post
3.4.0

I'm on OS X 10.5.8.
That would seem to lend weight to the hypothesis that there may have been a change in the alias syntax for sqlite3.

I don't have a 10.5 machine at hand for testing, but on my system your alias-free field references appear to resolve the ambiguity in the right direction for the moment.

I'll do a bit of research, and see if I can find out how to write something that is backward-compatible. Technically, you could download and install the latest build of sqlite3, but I don't think it would be a good solution - just in case there are any dependencies within the 10.5 build.
 
Quote:
Originally Posted by RobTrew View Post
Try pasting this version into your applescript editor, running it, and reporting the error message that it gives. (A copy of the error message will also be placed in your clipboard).

Code:
property pTitle : "OmniFocus: Quick Stats"
property pVersion : "TEST"

-- 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

property pToClipboard : "Copy list to clipboard"
property pstrCmd : "sqlite3 -separator ': ' ~/Library/Caches/com.omnigroup.OmniFocus/OmniFocusDatabase2 " & quoted form of ("
	select \"ALL 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 \"ALL 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\");
	select null;	
	select \"ALL 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\");
	select null;
	select \"ALL 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 \"ALL 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 \"ALL 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 \"    Remaining actions\", count(*) from task where (projectinfo is null) and (dateCompleted is null) and (childrenCount=0);
	select \"            Available actions\", count(*) from (task t left join context c on t.context=c.persistentIdentifier) where (t.projectInfo is null) and (t.childrenCount=0) and (t.dateCompleted is null) and (t.blocked=0) and  (t.effectiveContainingProjectInfoActive=1) and (t.blockedByFutureStartDate=0) and ((t.context is null) or ((c.allowsNextAction=1) and (c.active=1)));
	select \"            Actions awaiting start date\", count(*) from (task t left join context c on t.context=c.persistentIdentifier) where (t.projectInfo is null) and (t.childrenCount=0) and (t.dateCompleted is null) and (t.effectiveContainingProjectInfoActive=1) and (t.blockedByFutureStartDate=1) and ((t.context is null) or ((c.allowsNextAction=1) and (c.active=1)));
	select \"            Sequentially blocked actions\", count(*) from (task t left join context c on t.context=c.persistentIdentifier) where (t.projectInfo is null) and (t.childrenCount=0) and (t.dateCompleted is null) and (t.blocked=1)  and (t.effectiveContainingProjectInfoActive=1)  and (t.blockedByFutureStartDate=0) and ((t.context is null) or ((c.allowsNextAction=1) and (c.active=1)));
	select \"            Actions of inactive projects\", count(*) from (task t left join context c on t.context=c.persistentIdentifier) where (t.projectInfo is null) and (t.childrenCount=0) and (t.dateCompleted is null) and  (t.effectiveContainingProjectInfoActive=0) and (t.blockedByFutureStartDate=0) and ((t.context is null) or ((c.allowsNextAction=1) and (c.active=1)));
	select \"            Actions of contexts which are on hold\", count(*) from (task t left join context c on t.context=c.persistentIdentifier) where (t.projectInfo is null) and (t.childrenCount=0) and (t.dateCompleted is null) and  (t.effectiveContainingProjectInfoActive=1) and (t.blockedByFutureStartDate=0) and (c.allowsNextAction=0);
	select \"            Actions of contexts which are dropped\", count(*) from (task t left join context c on t.context=c.persistentIdentifier) where (t.projectInfo is null) and (t.childrenCount=0) and (t.dateCompleted is null) and  (t.effectiveContainingProjectInfoActive=1) and (t.blockedByFutureStartDate=0) and (c.active=0);
	select null;
	select \"ALL 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;
	")

try
	set strList to do shell script pstrCmd
on error strMsg
	tell application id "com.omnigroup.omnifocus"
		set strMsg to strMsg & return & return & version & tab & build number
	end tell
	display dialog strMsg buttons {"OK"} with title pTitle & "Ver. " & pVersion
	set the clipboard to strMsg
	return
end try

activate
if button returned of (display dialog strList buttons {pToClipboard, "OK"} with title pTitle & " Ver. " & pVersion) ¬
	is pToClipboard then tell application id "com.apple.finder" to set the clipboard to strList
This worked great for me in OmniFocus/1.9/GM-v77.75.2.
Thanks for posting this is helpful.
 
I have updated this to Ver 1.7
  • The listing is slightly reorganized for better legibility
  • It now gives a warning that it requires OS X 10.6 (there are some differences in Sqlite SQL syntax between 10.5 and 10.6)
  • I have attempted to make it accessible to copies of OmniFocus purchased through the Mac App Store, which unfortunately have a variant bundle identifier and hence a variant cache path.



The attempt to enable AppStore customers to use it is experimental and untested, as I don't have an AppStore copy myself.

As a user who is not a developer, but has long had the semi-automatic reflex of sharing most of the scripts that I write for my own use, I have to say that attempting to adapt this script to make it equally accessible to App Store customers added real overheads of time and thought which frankly brought no compensating rewards - not even the satisfaction of feeling confident that it will work ...

(a slight feeling, in fact, of clearing up after somebody else's mess :-)

Very different from the situation when we had just one bundle identifier, and, barring OS X version differences, if something worked on my machine then I could feel reasonably confident that I could usefully and simply share it with most other OmniFocus users.

The conclusion I draw from this experiment is that while I am very happy to contribute to a general FAQ on the kinds of coding adjustments that AppStore customers will have to make for themselves, I don't feel that I can really justify the additional overhead of time and thought required to regularly attempt to write my scripts in ways that do their best to detect which version is being used, and add a related error-handling apparatus ...

Bottom line ... if you buy Omni products through the AppStore, the variant bundle identifiers which it bestows may be a source of some inconvenience ...

--

Last edited by RobTrew; 2011-06-30 at 10:16 AM..
 
I love this script, but I wonder how to interpret this table.
Specifically, the subtotals below "Remaining actions:" don't sum to 2279.
Does another subcategory other than the ones listed exist or is this capturing too large a universe.

This happens with Stats v1.5 or v1.8

I am using OF 1.9 & OSx10.6.7

Thanks for any advice on how to fix or interpret.


ALL ACTIONS: 2352
Completed actions: 73
Remaining actions: 2279
Available actions: 230
Actions awaiting start date: 138
Sequentially blocked actions: 5
Actions of inactive projects: 264
Actions of contexts which are on hold: 277
Actions of contexts which are dropped: 0
 
Interesting result :-)

It's summing OK with my data, so it will be good to find out what the issue is.

I may need to add another category or two to the Remaining Actions breakdown.

What do your project break-down statistics look like - do you have any dropped projects, for example ?

And dou have a lot of tasks in one or more single-action lists ?
(There may be a blind spot there)

--

Last edited by RobTrew; 2011-07-03 at 06:58 AM..
 
 


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
OmniFocus Statistics skylarkelly OmniFocus Extras 4 2012-02-29 06:44 AM
Script: listing tasks by their creation date RobTrew OmniFocus Extras 0 2011-07-26 01:29 AM
OmniFocus AppleScript for Statistics Creation (for download) digitalimago OmniFocus Extras 30 2011-01-12 02:06 PM
Help with Entourage->OF quick entry script magnum6 OmniFocus Extras 4 2007-12-14 08:50 AM
OmniFocus crash statistics Ken Case OmniFocus 1 for Mac 0 2007-07-25 03:05 PM


All times are GMT -8. The time now is 06:21 AM.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2024, vBulletin Solutions, Inc.