[-tm-]

I'd like to find all links to DEVONthink references in notes of OmniFocus projects or tasks. There seems to be no direct method in getting link destinations other than checking each note separately. The following script snippet does work, however, it takes much too long. Is there any better way in reading out hyperlinks? Thanks for your help!

tell application "OmniFocus"
	set lstProjects to flattened projects of default document where note is not ""
	set lstTasks to flattened tasks of default document where note is not ""
	set the end of lstNoteItems to lstProjects
	set the end of lstNoteItems to lstTasks
	repeat with myList in lstNoteItems
		repeat with theItem in myList
			tell note of theItem
				repeat with myPara in paragraphs
					set myLink to value of attribute "link" of style of myPara
					if myLink contains "x-devonthink-item://" then
						set the end of myDTuuids to my getIDfromLink(myLink as string)
					end if
				end repeat
			end tell
		end repeat
	end repeat
end tell

Roughly speaking, something like this:

set lstTasks to paragraphs of (do shell script "sqlite3 ~/Library/Caches/com.omnigroup.OmniFocus/OmniFocusDatabase2 " & quoted form of ("
SELECT persistentIdentifier FROM task WHERE CAST(noteXMLdata as text) like \"%x-devonthink-item:%\""))

tell application id "com.omnigroup.omnifocus"
	tell default document
		repeat with i from 1 to length of lstTasks
			set item i of lstTasks to task id (item i of lstTasks)
		end repeat
	end tell
end tell

lstTasks

Or, of course, for a list of the links themselves, something like:

property pstrDevLink : "x-devonthink-item://"

set text item delimiters to ""
set strNotes to (do shell script "sqlite3 ~/Library/Caches/com.omnigroup.OmniFocus/OmniFocusDatabase2 " & quoted form of ("
SELECT CAST(noteXMLdata as text)   FROM task WHERE CAST(noteXMLdata as text) like \"%" & pstrDevLink & "%\"")) as text

set text item delimiters to pstrDevLink
set lstSectns to text items of strNotes
set text item delimiters to space

set lngSectns to length of lstSectns
if lngSectns > 1 then
	repeat with i from 2 to lngSectns
		set item i of lstSectns to pstrDevLink & text 1 thru 36 of item i of lstSectns
	end repeat
	return items 2 thru end of lstSectns
else
	return {}
end if

[-tm-]

Brilliant, absolutely brilliant! Using SQL reduces processing time drastically. Thanks so much, Rob.

There is one more thing: What I would need in the end is a list containing three pieces of information: a handle to the tasks (like task id = your first answer), the DEVONthink link (=your second answer) and the task status (on hold, dropped, etc.).

I've seen that there is a discrepancy between the number of tasks and the number of links in executing your SQL commands. One reason is that there might be more than one link in one paragraph. I tried using "words" instead of "paragraphs", however the count does not match either. Is there an easy way to get the corresponding task (and task status) for each link using the method in your second reply?

Thanks!

Originally Posted by -tm-

What I would need in the end is a list containing three pieces of information: a handle to the tasks (like task id = your first answer), the DEVONthink link (=your second answer) and the task status (on hold, dropped, etc.).

Good - I'm glad that that seems to meet the need. Here is a sketch of an approach to getting your three fields for each link, including multiple links in any given note.

(Note that the status options you have mentioned are technically project status options rather than properties of raw tasks. Not sure whether you also need to collected the Completed status of the tasks - task.dateCompleted is null vs task.dateCompleted is not null Thus something like SELECT (dateCompleted is not null) FROM task to get 1 or 0 for Completed or Not Completed).

property pstrDevLink : "x-devonthink-item://"
property pstrDBPath : "~/Library/Caches/com.omnigroup.OmniFocus/OmniFocusDatabase2"
property pEOR : "<eor>"
property pFldDelim : "<fldelim>"
property plstResults : {}

-- NOTE: Three possible cases:
-- 1. The item is a task with a project from which it inherits its status (strParentStatus)
-- 2. The item is itself a project (ie "root task of a project"), with its own status (strOwnStatus)
-- 3. The item is an (Inbox or other) task which has not been assigned to a project, and both strParentStatus and strStatus are ""

on run
	set strSQL to "
SELECT tp.persistentIdentifier, tp.status, pp.status, CAST(tp.noteXMLData as text), " & quoted form of pEOR & "
FROM (task t left join projectinfo p on t.containingProjectInfo=p.pk) as tp left join projectinfo pp on tp.persistentidentifier=pp.pk  
WHERE CAST(tp.noteXMLdata as text) like \"%" & pstrDevLink & "%\";"
	
	RunSQL(strSQL)
	
	set lst to {}
	repeat with oTask in plstResults
		set text item delimiters to pFldDelim
		set {strID, strParentStatus, strOwnStatus, strXML} to text items of oTask
		set text item delimiters to pstrDevLink
		set lstParts to text items of strXML
		set lngParts to count of lstParts
		if lngParts > 1 then
			repeat with i from 2 to lngParts
				set strStatus to ""
				if strParentStatus is not "" then
					set strStatus to strParentStatus
				else if strOwnStatus is not "" then
					set strStatus to strOwnStatus
				end if
				set end of lst to {strID, strStatus, pstrDevLink & text 1 thru 36 of item i of lstParts}
			end repeat
		end if
	end repeat
	set text item delimiters to space
	
	-- Return a list of three-part lists
	return lst
end run

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

--

[-tm-]

Rob, thanks again for all your help!
I am sorry that I could not work any earlier on this script. It is running fine by now. I had to change only two things:

1. I really needed the completion status of the task as I do not need any DEVONthink references on my iOS device for tasks that are done.
2. Extracting the DEVONthink link by cutting 36 characters does not always work as e.g. emails do have longer links.

Here are the relevant functions for those who are interested:

-- Create list of DTPO links found in OF {OF-ID, OF-Status, DTPO-UUID}
-- see the help of RobTrew here: http://forums.omnigroup.com/showthread.php?t=20014
on GetInfoFromOF()
	set tid to AppleScript's text item delimiters
	set strSQL to "SELECT tp.persistentIdentifier, tp.status, pp.status, tp.dateCompleted, CAST(tp.noteXMLData as text), " & quoted form of pEOR & "
FROM (task t left join projectinfo p on t.containingProjectInfo=p.pk) as tp left join projectinfo pp on tp.persistentidentifier=pp.pk  
WHERE CAST(tp.noteXMLdata as text) like \"%" & pstrDevLink & "%\";"
	RunSQL(strSQL)
	repeat with oTask in plstResults
		set text item delimiters to pFldDelim
		set {strID, strParentStatus, strOwnStatus, strCompleted, strXML} to text items of oTask
		set text item delimiters to pstrDevLink
		set lstParts to text items of strXML
		set lngParts to count of lstParts
		if lngParts > 1 then
			repeat with i from 2 to lngParts
				set strStatus to ""
				if strParentStatus is not "" then
					set strStatus to strParentStatus
				else if strOwnStatus is not "" then
					set strStatus to strOwnStatus
				end if
				if strCompleted > 0 then set strStatus to "done"
				set text item delimiters to "</"
				set end of lstOF to {strID, strStatus, item 1 of text items of item i of lstParts}
			end repeat
		end if
	end repeat
	set tid to AppleScript's text item delimiters
end GetInfoFromOF

and

-- Clean DT UUIDs
on cleanDTid(myUUID)
	-- Email reference UUIDs are longer then 36 chars, start with %3C = < and end with %3E = >
	if myUUID contains "@" then
		if myUUID starts with "%3C" then set myUUID to my replace_chars(myUUID, "%3C", "<")
		if myUUID ends with "%3E" then set myUUID to my replace_chars(myUUID, "%3E", ">")
		return myUUID
	else
		return text 1 thru 36 of myUUID
	end if
end cleanDTid