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 > OmniOutliner > OmniOutliner 3 for Mac
FAQ Members List Calendar Search Today's Posts Mark Forums Read

 
Selecting 003 rows with 'command-line' queries Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
Another draft script sketches a simple GUI approach to selecting a subset of OO3 rows which match certain criteria.

A limitation of that script is that while it can concatenate conditions simply linked by and, it doesn't allow for anything more complex, such as the use of or or the application of not to nested sub-clusters of conditions.
Another limitation is that it only gives access to a subset of oo3 row properties.

In cases where slightly more complex selection criteria would be helpful, or access to less-visited row properties is needed, I use a (very) quick and dirty piece of code which enables me to type and apply a query in an applescript syntax (to select a subset of rows) and then re-use the query later by selecting it from a Most Recently Used list.

Only useful to users who are fairly familiar with applescript, and with the properties and quirks of the 003 row class in the applescript library, but here it is, as a minor curiosity ...

(Note that the names of any user-defined columns must be entered in [square brackets]. There are some other comments on the query language in the script itself. In particular the possible values of the default status checkbox differ from those of user-defined columns of the checkbox type - this is just a quirk of the 003 applescript library ...)

(On my system this script runs as happily from the OO3 toolbar as from the OO3 script menu - by default it stores the MRU list in the user's Documents folder).



Code:
-- QUERY ROWS
-- Ver 0.7b 	provides a multi-choice list of row properties and user-defined fields from which 
--			a re-usable query can be built

-- Select OO3 rows by entering applescript-like command line queries
-- The syntax is that of an Applescript WHOSE / WHERE clause, 
-- (The words "whose"/"where" should NOT be included)
-- but the names of user-defined columns must be entered in square brackets
-- (they are internally translated to a "value of cell id ..." format)
-- A most-recently-used list enables reuse of recent queries

-- In addition to square-bracketed name of user-defined columns, 
-- the name of any row property listed in the applescript library may be used.
-- Note that the possible values for the state property of a row are the unquoted constants:
--		-- checked	
-- 		-- indeterminate	
--		-- none	
--		-- unchecked
-- whereas the possible values of a user-defined checkbox column are strings and need to be quoted:
-- 		-- "checked"
-- 		-- "indeterminate	"
-- 		-- "none"	
-- 		-- "unchecked"

-- 	Copyright © 2010, Robin Trew
--  All rights reserved.
-- 	
-- 	Redistribution and use in source and binary forms, with or without modification, 
-- 	are permitted provided that the following conditions are met:
-- 	
-- 		- Redistributions of source code must retain the above copyright notice, 
-- 		  this list of conditions and the following disclaimer.
-- 		- Redistributions in binary form must reproduce the above copyright notice, 
-- 		  this list of conditions and the following disclaimer in the documentation 
-- 		  and/or other materials provided with the distribution.
-- 		
-- 	THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS 
-- 	"AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, 
-- 	THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. 
-- 	IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR 
-- 	ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
-- 	 (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; 
-- 	 LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, 
-- 	 WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) 
-- 	 ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

property plngMRU : 10
property pOpen : "["
property pClose : "]"
property pTopic : "topic"
property pNewQuery : "ENTER NEW QUERY"
property pSpacer : "-------------------------------------------------------"
property pSeparator : " - "

property pstrObjFile : "OO3MRUQueries"
property pstrSuffix : ".scpt"
-- property pstrScriptPath : (path to temporary items folder as string) & ¬
-- 	pstrObjFile & pstrSuffix
property pstrScriptPath : (path to documents folder as string) & ¬
	pstrObjFile & pstrSuffix


-- SCRIPT OBJECT FOR HOLDING MRU queries BETWEEN RUNS
-- (PATH AS ABOVE)
script sMRUQueries
	property plstQueries : {}
end script

on run
	-- EXIT IF NO DOCUMENT IS OPEN IN 003	
	tell application id "com.omnigroup.OmniOutlinerPro3"
		if (count of documents) < 1 then return
		set oDoc to front document
	end tell
	
	-- CHOOSE A SAVED QUERY, OR WRITE A NEW ONE
	set lstSavedQueries to GetSavedQueries()
	set strLegibleQuery to ChooseOrWriteQuery(lstSavedQueries)
	if length of strLegibleQuery < 1 then return
	
	-- TRANSLATE THE FIELDS OF THE LEGIBLE QUERY TO 
	-- APPLESCRIPT CELL ID REFERENCES
	set strMachineQuery to TranslateFields(strLegibleQuery)
	
	-- RUN THE TRANSLATED QUERY, TO OBTAIN
	-- A REFERENCE TO A SUBSET OF ROWS
	tell application id "com.omnigroup.OmniOutlinerPro3"
		set strScript to "
script 
	on GetRows()
		set today to today()
		tell application " & quote & "OmniOutliner Professional" & quote & linefeed & "
			set oDoc to front document
			tell oDoc
					return a reference to rows where " & strMachineQuery & "
			end tell
		end tell
	end GetRows
	
	on today()
		set dteToday to current date
		set hours of dteToday to 0
		set minutes of dteToday to 0
		set seconds of dteToday to 0
		dteToday
	end today
end script
"
		
		-- GET A REFERENCE (FROM THE PATCHED SCRIPT)
		-- TO THE ROWS WHICH MATCH THE "WHERE" FILTER
		try
			set oScript to run script strScript
		on error
			display dialog "query language error in:" & return & return & quote & strMachineQuery & quote
			return
		end try
		
		try
			set refRows to GetRows() of oScript
		on error
			display dialog "query language error, or field not found, in:" & return & return & quote & strMachineQuery & quote
			return
		end try
		
		-- IN THE USER INTERFACE, SELECT ANY MATCHING ROWS 
		set blnFound to false
		try
			set blnFound to (count of refRows) > 0
		on error
			display dialog "query language logic error in:" & return & return & quote & strMachineQuery & quote
			set selected of rows of oDoc to false
			return
		end try
		if blnFound then
			set expanded of rows of oDoc to false
			set refParents to a reference to ancestors of refRows
			set expanded of refParents to true
			select refRows without extending
			activate
			-- IF ANY ROWS WERE MATCHED, STORE THE LEGIBLE VERSION OF THE QUERY
			my SaveQuery(strLegibleQuery)
		else
			-- IF *NO* ROWS WERE MATCHED, ALERT THE USER
			display dialog "No rows found in " & name of oDoc & return & return & ¬
				"with the query:" & return & tab & strLegibleQuery
		end if
	end tell
	
	--store script sMRUQueries in file pstrScriptPath replacing yes
end run

on QueryDraft(varSelns)
	set strDraft to ""
	set blnFirst to true
	if varSelns is not false then
		set lngFields to length of varSelns
		repeat with oField in varSelns
			set text item delimiters to " - "
			set strField to first text item of oField
			set strClass to text item 2 of oField
			if blnFirst then
				if lngFields > 1 then set strDraft to strDraft & "("
				set blnFirst to false
			else
				set strDraft to strDraft & " and ("
			end if
			set strDraft to strDraft & strField
			if strClass contains "text" then
				set strDraft to strDraft & " contains "
			else if strClass is "date" then
				set strDraft to strDraft & " ≥ (today  )) and (" & strField & " ≤ ((today  ) + 1 * day) -1"
			else
				set strDraft to strDraft & " is  "
			end if
			if lngFields > 1 then set strDraft to strDraft & ")"
		end repeat
	end if
	set text item delimiters to space
	strDraft
end QueryDraft


-- Build a list of the names and classes of the properties of an object
on PropList(oObj)
	set recProps to (properties of oObj)
	
	-- AND LIST THE CLASSES OF EACH PROPERTY
	set lstProps to recProps as list
	set lstClass to {}
	repeat with oValue in lstProps
		set end of lstClass to (class of oValue) as string
	end repeat
	
	-- EXTRACT THE NAME OF EACH PROPERTY
	-- BY CONVERTING THE PROPERTY LIST TO A STRING
	-- AND TRANSLATING EACH 4-LETTER CODE TO AN ENGLISH PROPERTY NAME
	set strDelim to text item delimiters
	
	-- Convert the record to a string (by decoding an error message :-)
	set strProps to my Rec2Str(recProps)
	set text item delimiters to ", «"
	set lstItems to text items of strProps
	set text item delimiters to ""
	set first item of lstItems to (characters 3 thru end of first item of lstItems) as text
	
	set lstPropName to {}
	repeat with oItem in lstItems
		-- Extract the class property from the start
		set text item delimiters to "»"
		set lstParts to text items of oItem
		set strClass to "«" & first item of lstParts & "»"
		set oClass to run script strClass
		set end of lstPropName to (oClass as string)
		
		-- Look for any other properties in the rest of the string
		set strRest to item 2 of lstParts
		set text item delimiters to ", "
		set lstRest to text items of strRest
		repeat with strPart in lstRest
			set text item delimiters to space
			set strPossible to first item of text items of strPart
			set text item delimiters to ":"
			set lstCommaParts to text items of strPossible
			if length of lstCommaParts is 2 then
				set strPropName to first item of lstCommaParts
				if length of strPropName > 0 then ¬
					set end of lstPropName to strPropName
			end if
		end repeat
	end repeat
	
	-- COMBINE THE PROPERTY NAMES WITH THE LIST OF CLASSES
	-- (ALLOWING FOR ALTERNATIVE CLASSES IN THE CASE OF THE 'PARENT' PROPERTY)
	
	repeat with iProp from 1 to count of lstPropName
		set (item iProp of lstPropName) to (item iProp of lstPropName) & pSeparator & item iProp of lstClass
	end repeat
	
	set text item delimiters to strDelim
	
	-- SORT THE LIST ALPHABETICALLY
	set lstPropName to ShellSort(lstPropName)
	return lstPropName
end PropList

-- Convert a record to a string by stealing the interesting part of an error message ...
on Rec2Str(rec)
	-- trigger the simplest error to get the measure of its message
	set recDummy to {a:1} as record
	try
		recDummy as string
	on error strShortMsg
		set strDelim to text item delimiters
		set text item delimiters to {"{a:1}"}
		set lngStart to (length of first text item of strShortMsg) + 1
		set lngEnd to length of last text item of strShortMsg
		set text item delimiters to strDelim
	end try
	-- trigger an informative error and grab its string representation
	-- of the record
	try
		rec as string
	on error strRecordMsg
		set lngEnd to ((length of strRecordMsg) - lngEnd)
		set strRecord to text lngStart thru lngEnd of strRecordMsg
	end try
	return strRecord
end Rec2Str

-- use a shell script to sort a list
on ShellSort(lst)
	set strDelim to text item delimiters
	set text item delimiters to {ASCII character 10} --  linefeed
	set strList to (lst as string)
	set strNew to do shell script "echo " & quoted form of strList & " | sort -f"
	set lstNew to (paragraphs of strNew)
	set text item delimiters to strDelim
	return lstNew
end ShellSort


on SaveQuery(strLegibleQuery)
	if (plstQueries of my sMRUQueries) does not contain strLegibleQuery then
		set lngMax to plngMRU - 1
		if length of (plstQueries of my sMRUQueries) > lngMax then ¬
			set (plstQueries of my sMRUQueries) to ¬
				items 1 thru lngMax of (plstQueries of my sMRUQueries)
		
		set beginning of (plstQueries of my sMRUQueries) to strLegibleQuery
	end if
	try
		store script my sMRUQueries in file pstrScriptPath replacing yes
	end try
end SaveQuery


on TranslateFields(strLegibleQuery)
	tell application id "com.omnigroup.OmniOutlinerPro3"
		
		-- list bracketed versions of user-defined column names
		set oDoc to front document
		tell oDoc
			set refCols to a reference to (columns where (name is not "") and (name is not pTopic))
			set lstColNames to name of refCols
			set lngCols to length of lstColNames
			repeat with iCol from 1 to lngCols
				set item iCol of lstColNames to pOpen & item iCol of lstColNames & pClose
			end repeat
			set lstColId to id of refCols
		end tell
	end tell
	
	-- translated bracketed field names to cell id references
	set strTransln to strLegibleQuery
	repeat with iCol from 1 to length of lstColId
		set strCellValue to "(value of cell id " & quote & item iCol of lstColId & quote & ")"
		item iCol of lstColNames
		strCellValue
		strTransln
		set strTransln to my FindReplace(strTransln, (item iCol of lstColNames), strCellValue)
		strTransln
	end repeat
	strTransln
end TranslateFields

on ChooseOrWriteQuery(lstSavedQueries)
	set blnNewQuery to true
	if length of (lstSavedQueries) > 0 then
		set varChoice to choose from list {pNewQuery} & lstSavedQueries default items {pNewQuery} with title "Select rows by querying"
		if varChoice is false then return ""
		set blnNewQuery to (varChoice is {pNewQuery})
	end if
	
	if blnNewQuery then
		-- Offer a prompt to build a draft query
		tell application "OmniOutliner Professional"
			set oDoc to front document
			
			-- GET A PROPERTIES RECORD FOR THE SAMPLE OBJECT
			set oRow to last item of rows of oDoc
			
			set lstProps to my PropList(oRow)
			
			-- ADD THE [SQUARED BRACKETED] NAMES, AND COLUMN TYPES, OF ANY USER-DEFINED COLUMNS
			set strID to id of topic column of oDoc
			set lstCols to (columns of oDoc where (name is not ""))
			set lstFields to {}
			repeat with oCol in lstCols
				set end of lstFields to "[" & name of oCol & "]" & pSeparator & type of oCol
			end repeat
		end tell
		
		set varSelns to choose from list (lstFields & lstProps) with title "choose properties to filter on" with prompt ¬
			"select multiple properties with Cmd-Click" with multiple selections allowed and empty selection allowed
		
		set strDraft to QueryDraft(varSelns)
		
		-- Get a query with user-defined fieldnames in square brackets from user
		set recResponse to display dialog "rows where:" default answer strDraft buttons {pSpacer & " Run query " & pSpacer}
		if recResponse is not false then
			set strQuery to text returned of recResponse
		else
			return
		end if
	else
		set strQuery to varChoice
	end if
	strQuery
end ChooseOrWriteQuery

on GetSavedQueries()
	-- RETRIEVE ANY STORED QUERIES FROM RECENT USE
	try
		set sMRUQueries to load script file pstrScriptPath
	on error
		store script my sMRUQueries in file pstrScriptPath replacing yes
	end try
	return plstQueries of my sMRUQueries
end GetSavedQueries

on FindReplace(strText, strFind, strReplacement)
	set {strText, strFind} to {strText as string, strFind as string}
	set AppleScript's text item delimiters to strFind
	set strText to text items of strText
	set AppleScript's text item delimiters to strReplacement
	tell strText to set strText to item 1 & ({""} & rest)
	set AppleScript's text item delimiters to space
	strText
end FindReplace

Last edited by RobTrew; 2010-07-08 at 03:54 AM.. Reason: ver 0.7b - Allows for use of the symbol 'today' in date queries
 
 


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Command Line Build Fails For OmniFileStore at OFSDocumentStore.m davidbitton Omni Frameworks 3 2012-06-13 12:03 AM
selecting rows by a date jamesafoster OmniOutliner 3 for Mac 2 2011-09-08 01:59 AM
Exporting omnigraffle canvases to PDF,SVG,PNG,EPS from command line fikovnik OmniGraffle General 2 2011-06-13 06:28 AM
Redirecting command line output into OO ptorngaard OmniOutliner 3 for Mac 3 2009-04-06 12:29 AM
Create new search through command line m-rick OmniWeb General 1 2007-03-19 04:27 PM


All times are GMT -8. The time now is 01:08 PM.


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