The most recent version of the script can be found in post #23. The original scripts have been deleted.
The problem:
Spotlight does not index the contents of your scripts and therefore you cannot search their code using spotlight. Now that my script library is growing I really need a way to search on the contents of my scripts. I found 2 solutions to this problem but neither worked for me.
I saw StefanK’s post here: http://bbs.applescript.net/viewtopic.php?id=21936. The problem for me is that StefanK’s solution relies on an application called “script debugger” to get the text of your scripts into spotlight so they can be searched. Unfortunately I do not have script debugger and it’s expensive.
I also saw Mark Hunte’s solution here: http://www.macosxhints.com/article.php?story=20060403083841944. Mark’s solution relies on saving your scripts as applescript text files rather than compiled scripts so that spotlight can index them. This is not practical for me because in order to run a script you need to first open the script’s text file in script editor, compile it, and then run the script. It seemed to me there must be a better way.
My solution:
I thought I’d take a different approach. Rather than try to force spotlight to index the contents of my scripts, I thought I’d make my own database of my script contents and then search that. Luckily, in OSX 10.4 Apple introduced applescript database events. Using them it’s fairly easy to create custom databases and search them quickly. As such my solution used this technique.
Any speed optimizations to the script would be appreciated. Updating the database takes over 8 minutes on my dual 2GHz G5 machine and the large group of scripts I’ve created/collected.
Thanks Mark! I was really excited when I came up with this, and I’m glad you are too. And by the way, I got the idea for presenting the search results as alias files from you, in your post from the discussion of StefanK’s method. That was a great idea and really adds to the effectiveness of my scripts. Thanks.
When I ran the first (create the database) script, it stopped at this line in the section to remove non-existent records from the database (looks like the db wasn’t created):
tell database DBName
with the error: Database Events got an error: Can’t make DBName into type reference. The ~/Library/Application Support/Script Search/ folder existed but was empty. The allFiles list was populated at that point.
I had a couple of odd results when I ran the script. At first it repeatedly worked as expected, but then when I trashed the database and ran it again, It either did not create the DB or it did but did not open any scripts.
I then killed the Database Events process with Activity Monitor, ran the script and it opened the scripts but did not close them. The DB was created but a search did not find anything even though I knew what I was searching for was there.
I think it maybe that there is nothing wrong with there script, but that Database Events is a bit flakey, although it might be an idea to put in a save Database after each repeat.
It is still a good script and I think we need to see if there are known issues with Database Events.
[i]In Matt Neuburg’s “AppleScript: The definitive Guide, Second Edition” he discusses a new background-only scriptable tool in Tiger’s CoreServices called “Database Events.app”. It is intended for creating and manipulating small databases - a scriptable background-only link to SQLite3. SQLite3 in turn, is a small, open-source database engine introduced in and part of Tiger.
Although the AppleScript Dictionary for Database Events.app is viewable from AppleScript editors, it provides only a subset of what SQLite can do and if there is any documentation for Database Events on Apple’s website, I didn’t find it. Dr. Neuburg says in his book that using Database Events for the creation and manipulation of databases is “nowhere near as powerful, coherent, and complete as the sqlite3 command line tool”, so it’s best in his view to “disregard Database Events as a bad job, and use the command line instead.”[/i]
It’s the last quote that grabbed me. I think Regulus’ script would work a treat if it could be crunched into SQLite3 directly. I think you’re right – Database Events is flakey.
Hi guys, thanks for the input. I can understand where you say database events is flaky. I’ve seen a couple odd things while writing and testing these scripts but with little tweaks I thought I had gotten it to work reliably. I’m sorry to hear you both had issues. I’ve been using my scripts for the last week and they’ve been running well for me… but I can see the advantage to bypassing database events and going directly with SQLite3. The problem is I don’t know how to use SQLite3 through applescript.
If either of you understand SQLite3 then feel free to modify the scripts to use it intead of database events. I’m sure that would make the scripts more robust. I’ll look into the tutorial Adam mentioned and see what I can some up with, but let me know if either of you are working on this change. I’d rather not duplicate the work if you’re already working it.
I have updated both of the original scripts above so please download and try the new versions. Let me know if you still see issues with them or if they’re working better. Database events seems to be behaving properly now.
I’ve been rewriting and testing these new versions of the scripts the last few days. I made some significant changes in how they operate. I changed how the “database events” application is handled, how/when the databases are saved, and even made some changes to speed up the scripts. The initial creation of the database is much faster. Also, I implemented some reporting statistics in the final dialog box of the script so it’s easier to tell exactly what has happened during the database operations. Bottom line is I think I found the errors and have fixed them, and at this point I’m hoping it isn’t necessary to change to using SQLite3.
One last note: I came across a very weird bug during my testing. I’m mentioning this in case it happens to you too. When I run the first script to update/create the database, I’m seeing problems with any script from the Scripts Folder that have “tell application “Script Editor”” in them. To explain: the update database script works by opening any script that isn’t in its database. The Script Editor application opens it so it can get the text of the script and add it to the database… so when a script opens with “Script Editor” somewhere in the contents the application dialog box comes up in Script Editor and asks you to tell Script Editor where “” application is. It’s really weird. It asks for “” instead of “Script Editor”. For some reason the word “Script Editor” is lost from the script contents when it’s opened, so Script Editor asks you to find the application for it, but it asks for “” application because the words “Script Editor” are missing from the script contents. I know it sounds strange, but that’s what has been happening on my machine. I don’t know if it’s only a problem on my machine or not. I just hit the “Cancel” button when it asks me to locate the application and everything continues normally. You may have realized by now that my create/update database script contains this bug! So I was seeing all kinds of problems with my database scripts until I figured this weird thing out. My fix, as you can see, is that I hard-coded the path to the Script Editor application into the script, and I had to do this to all the scripts in my scripts folder which have this issue. So if you see this problem let me know so I know I’m not crazy about this problem. It’s happened before and after restarts of my computer, so hard-coding seemed to be the only thing that fixed it. I don’t think this bug is caused by database events.
Anyway, thanks for listening and good luck with the new scripts! Let me know how they work.
Since you asked, I have also seen a problem with tell application “Script Editor” like the one you described in the past. I cannot get it to happen right now though (scripts with tell blocks like that open up just fine, it does not prompt for an app named “”).
FYI: When I came across the problem before, it was while I was trying to see if Script Editor supported adding entries to its context menu via scripts in the scripts folder from the user domain and not just the one form the local domain. I did not have any success with that at the time. Maybe a subsequent logout or reboot cleared up the ‘Wheres is ?’ problem.
Thank you for the verification. My problem was coming and going too, and it made it that much harder to track down and solve… especially since I was in the middle of troubleshooting the database events problems which were perplexing in themselves.
tested the first script.
I found that the delete routine did not work as expected.
if I had 20 records but 1 file could not be found, the script would delete it.
But the repeat count is still at 20, so it would still try and find the last record number 20, but the would only be 19.
It seems D.E is dynamic and reorders the records on delete.
So I changed it to put the record number in a list and then delete it at the end of the script.
Also I wanted the choice of deleting or not so I put that in, but that part is easy to take out.
(* This is used to help you search the contents of your applescripts. It is the first of 2 scripts. *)
(* This script creates a script database using "database events". The database is created by opening all of the scripts or "applescript created" applications found in your user's script folder. They're opened in Script Editor, and the script text from each is added to the database. The database is saved in your user's "application support" folder in a folder called "Script Search". This script takes time to run, so be patient. You will want to run this script periodically to update the database file with changes to your "Scripts" folder. *)
property ScriptEditorApp : ((path to applications folder) as string) & "AppleScript:Script Editor.app"
property dbName : "scriptDB"
property dbFolderName : "Script Search"
property appSupportFolder : (path to application support folder from user domain) as string
property dbFolderPath : appSupportFolder & dbFolderName
property dbPath : appSupportFolder & dbFolderName & ":" & dbName & ".dbev"
property scriptsFolder : (path to scripts folder from user domain) as string
set newRecs to 0
set updatedRecs to 0
set deletedRecs to 0
-- database events require at least MacOSX 10.4
if (system version of (system info)) < "10.4" then display dialog "This script requires the installation of Mac OS X 10.4 or higher." buttons {"Cancel"} default button 1 with icon 2
-- alert the user that the script takes a long time
set frontApp to displayed name of (info for (path to frontmost application))
tell application frontApp to display dialog "This script takes time to run, so please be patient. You will be alerted when the script is finished." buttons {"OK"} default button 1 with icon note giving up after 4
set inTime to current date
-- get a list of all the files in the scripts folder
set allFiles to my allFiles_ofFolder(scriptsFolder)
-- disable auto-quitting for database events and close all open databases
tell application "Database Events" to set quit delay to 0
my close_all_databases()
-- create the necessary folders and open the database
set updateDB to true
tell application "Finder"
if not (exists folder dbFolderPath) then make new folder at folder appSupportFolder with properties {name:dbFolderName}
if (exists file dbPath) then -- check for the database and create/open it
tell application "Database Events" to open database (POSIX path of dbPath)
else
tell application "Database Events"
make new database with properties {name:dbName, location:dbFolderPath}
save database dbName
set updateDB to false
end tell
end if
end tell
-- create or update the database
if updateDB then -- update the database so we need to check the DB to see if a record exists for each script
repeat with aFile in allFiles
set scriptText to ""
set fileInfo to (info for file aFile)
if kind of fileInfo is "script" or file creator of fileInfo is "aplt" then -- check for a "script editor" file
set {fileName, fileModDate} to {name of fileInfo, modification date of fileInfo}
tell application "Database Events" to tell database dbName
if (exists record aFile) then
tell (first record whose name = aFile)
set recordsModDate to value of field "modDate"
if fileModDate > recordsModDate then -- use modification date to see if a record needs updating
set scriptText to my get_scriptText(aFile)
if scriptText is not "" then
set updatedRecs to updatedRecs + 1
set value of field "fileName" to fileName
set value of field "modDate" to fileModDate
set value of field "scriptText" to scriptText
end if
end if
end tell
else -- a record doesn't exist for this script so create a new record for it
set scriptText to my get_scriptText(aFile)
if scriptText is not "" then
set newRecs to newRecs + 1
set thisRecord to make new record with properties {name:aFile}
tell thisRecord
make new field with properties {name:"fileName", value:fileName}
make new field with properties {name:"modDate", value:fileModDate}
make new field with properties {name:"scriptText", value:scriptText}
end tell
end if
end if
end tell
end if
end repeat
-- remove non-existent records from the database
tell application "Database Events" to tell database dbName
save
set biglist to {}
set recordCount to count of records
repeat with i from 1 to recordCount
--try
set shouldDelete to false
set thisPath to value of field "name" of record i
tell application "Finder" to if not (exists file thisPath) then copy thisPath to end of biglist
log biglist
--on error
--delete record i
--end try
end repeat
if biglist is not {} then
repeat with a from 1 to number of items in biglist
set this_item to item a of biglist
log this_item
set thisPath to value of field "name" of record this_item
tell application "System Events"
set target_app to item 1 of (get name of processes whose frontmost is true)
tell application target_app
display dialog "Record" & space & thisPath & " was not found" & return & "Delete or keep Record! " buttons {"Delete", "Keep"} default button 2
end tell
end tell
if the button returned of the result is "Delete" then
delete record this_item
set deletedRecs to deletedRecs + 1
else
-- action for 2nd button goes here
end if
end repeat
end if
save
end tell
else -- we're creating a new database so no checking of the records is necessary
repeat with aFile in allFiles
set scriptText to ""
set fileInfo to (info for file aFile)
if kind of fileInfo is "script" or file creator of fileInfo is "aplt" then -- check for a "script editor" file
set scriptText to my get_scriptText(aFile)
if scriptText is not "" then
set {fileName, fileModDate} to {name of fileInfo, modification date of fileInfo}
tell application "Database Events" to tell database dbName
set newRecs to newRecs + 1
set thisRecord to make new record with properties {name:aFile}
tell thisRecord
make new field with properties {name:"fileName", value:fileName}
make new field with properties {name:"modDate", value:fileModDate}
make new field with properties {name:"scriptText", value:scriptText}
end tell
end tell
end if
end if
end repeat
tell application "Database Events" to tell database dbName to save
end if
-- clean up database events
tell application "Database Events" to tell database dbName to set recordCount to count of records
my close_all_databases()
tell application "Database Events" to quit
-- notify user it's done updating the db and how long it took
set outTime to current date
set totaltime to (outTime - inTime)
set theTime to my secs_to_hms(totaltime)
set frontApp to displayed name of (info for (path to frontmost application))
tell application frontApp to display dialog "The database has been updated." & return & return & "Update time: " & theTime & return & "Total Records in DB: " & recordCount & return & "Records Deleted: " & deletedRecs & return & "Records Updated: " & updatedRecs & return & "Records New: " & newRecs buttons {"OK"} default button 1 with icon 1
(*=================== SUBROUTINES =====================*)
on close_all_databases()
tell application "Database Events"
set c to count of databases
if c > 0 then
repeat with i from 1 to c
close database 1
end repeat
end if
end tell
end close_all_databases
on get_scriptText(theScript)
tell application ScriptEditorApp
try
open theScript
set scriptText to characters of document 1 as string
on error
set scriptText to ""
end try
try
close document 1
end try
end tell
return scriptText
end get_scriptText
on secs_to_hms(the_secs)
if the_secs is less than 360000 then
set hr to text 2 thru 3 of ((100 + the_secs div hours) as string)
set min to text 2 thru 3 of ((100 + the_secs mod hours div minutes) as string)
set sec to text 2 thru 3 of ((100 + the_secs mod minutes div 1) as string)
set fraction to text 2 thru 3 of ((100 + the_secs mod 1 * 100) as string)
return hr & ":" & min & ":" & sec & "." & fraction
else
return false
end if
end secs_to_hms
on allFiles_ofFolder(aFolder)
set searchFolder to aFolder as string
set everyFile to {}
tell application "Finder"
try -- there's an error if nothing is in the folder when you get entire contents
set tempVar to files of entire contents of folder searchFolder
if (count of tempVar) is 0 then -- answer is 0 when there's only 1 item???
set end of everyFile to tempVar as string
else
repeat with i from 1 to (count of tempVar)
set end of everyFile to (item i of tempVar) as string
end repeat
end if
end try
end tell
return everyFile
end allFiles_ofFolder
Very good catch Mark. That explains why I needed that try/on error block. Deleting that record before the repeat loop finished would cause the error because of the dynamic reordering. I couldn’t quite figure that one out. Excellent.
I deleted my first script from above and am pointing everyone to your fix for the first script now. Thanks for finding that!
What do you think of this change,
It will ask if you want to Delete, ReCompile, or Skip the missing record.
the ReCompile opens a new AS document with the record as it text and the same name as the missing file.
tell application target_app
display dialog "Record" & space & thisPath & " was not found" & return & "Delete , ReCompile or Skip Record! " buttons {"Delete", "ReComplie", "Skip"} default button 2
end tell
end tell
if the button returned of the result is "Delete" then
delete record this_item
set deletedRecs to deletedRecs + 1
else if the button returned of the result is "ReComplie" then
set thisText to value of field "scriptText" of record this_item
set thisName to value of field "name" of record this_item
set thisName to do shell script "basename " & quoted form of (POSIX path of thisName)
tell application "Script Editor"
make document with properties {name:thisName, text:thisText}
end tell
I think that’s an interesting idea Mark. The reason for a script to be a “non-existent record” is because the script entry in the database no longer actually exists in the Finder. So what you are proposing is actually a new purpose for the script. Right now the purpose is for the database to mimic exactly your current state of your script folder in the Finder so that you can search for script code. But with your suggestion the script could also act as 1) a backup database in case you delete a script by accident (with the recompile option) or 2) as a storage facility for script code that you would like to keep, and be able to search, but no longer want as an actual script file in the Finder (with the keep/skip option).
I can certainly see a purpose for what you are proposing. Option #1 is good but I probably wouldn’t use it because I backup my computer and if I “lose” a script my backup would handle the issue. Option #2 is interesting though. I’m not sure if I would use it or not. I would probably use it but only if it didn’t get in the way of the original purpose of the script. If I started keeping code in the database I would be prompted for a keep/delete/recompile decision every time I updated my database. I’m wondering how you think this could be implemented such that it doesn’t become a hinderance to the user?
Possible ways could be:
as separate buttons in a dialog box such that we can access kept code in the future but not be asked to make a decision on the code each time. I think we would need a “delete all” option too.
as a separate database but linked and accessible from the main database
What are your thoughts about this? Do you see what I mean about it getting in the way of the user as you keep extra code in the database?
One other thing Mark. I was just looking at your code… is this line correct?
tell application “Finder” to if not (exists file thisPath) then copy i to end of biglist
Shouldn’t it be this?
tell application “Finder” to if not (exists file thisPath) then copy thisPath to end of biglist
By copying i you are only copying a number. If we get a list of record numbers, say {1,2,3}, then when record 1 is deleted doesn’t record 2 become record 1 because of the dynamic reordering of the database… so that when we delete record 2 in the second iteration we would really want to delete record 1?
I do have some ideas about how to not get in the way of the user, all your points are good.
I have already merged the two scripts starts with dialog {Update or Search}
And was thinking to introduce a skipped field.
Removeing the recompile .
The Last results dilog would change to the same result text but two buttons, {Show Skipped , OK}
The show skipped would pull up a choose dialog with the skipped files listed. and buttons {Delete, Recomiple, Cancel}
Multiple selections allowed.
It will I am afraid be sometime before I can do this though as I am about to not have access to my Mac for a while.
:rolleyes:
I had thought about doing that but decided against it. I figure I’ll only update my database weekly whereas I would be performing searches daily… so I kept it separate so I didn’t have to click an extra button every time I wanted to do a search. I might also end up using launchd to automate the update process so that part of the script might become irrelevant for me anyway.
I like how you’re proposing to do that. That sounds like it could work effectively. After you get it together please post it so I can try it out.
I hope it’s nothing serious. That would kill me! :o
I worked through the tutorial and tried using sqlite3 directly. I couldn’t get it to work. There were too many characters in the code of an applescript that had special meaning to sqlite3 that I kept getting errors when I tried to write it to the database. I worked through the problem characters one at a time and specially parsed the applescript text to get it in the proper format (quotes, brackets, parenthesis, forward slash’s etc. all gave me problems). But my undoing was I could’t get the backslash character to parse properly from applescript code to a format suitable for sqlite3. In the end it seemed silly to be parsing fixes for each of these characters and having to do it for every script. In databse events none of that parsing was needed. It’s ashame it didn’t work out because I really wanted to test the speed of each method against each other.
From my experience with learning both, database events was far simpler to learn even when using database entries which didn’t contain the problem characters.
And that may be what database events is all about (there’s no documentation for it): to encode the input data in a way that is always acceptable to SQLite3 which, as I understand it, is what database events uses as its db. I’m not sure it succeeds in every case, but I agree that direct interaction with SQLite3 via do shell scripting can be a pain - parsing for acceptability to AppleScript, then to the Shell, and finally to SQLite3 and back again. I hadn’t thought about that difficulty. :rolleyes:
One factor that confuses me when you say this is that the general formats for each are different. Database events uses a record type format (with records and fields inside of each record) while SQLite3 uses a spreadsheet table format (with standard column headings and rows). So if database events was a method within applescript to access the sqlite3 database engine, then I don’t understand why applescript changed the methodology for the database format. I realize one can be translated to the other but why bother?
Anyway, in 10.5 maybe database events will be more mature and we can find out more answers about how/why it works.
Very, very, sweet script.