by Kevin Bradley
bBasic Instinct[/b]
In a now well-known gaffe, the translators of a Japanese game called “Zero Wing” mistranslated a line of dialog as “All your base are belong to us.” And while scripters sometimes feel that the coders at Apple created a similar Frankensteinian grammar for Applescript, sometimes the gang at 1 Infinite Loop come up with something that makes us forget all about their bad grammar. Database Events is one of those dandy applications that got little to no fanfare when Tiger was released. But like System Events, Image Events, and the Speech Recognition Server, it can help you to write smarter, more capable scripts.
Make A Base Accusation
Located in System/Library/CoreServices, Database Events is an application that interfaces with sqlite 3(already loaded in your Mac’s hidden folders with the other BSD commands) to create and manage databases via Applescript. The application’s dictionary only defines a handful of classes and one command, along with the standard definitions and the text suite. Those few commands give you the ability, though, to create, open and close databases, create new records and fields, and set values for fields and records. Everything you need for a simple database!
First, using the standard make command you can create a new database:
--set default path to the databases folder
set thePath to ((path to the documents folder from user domain) as text) & "Databases:"
--get user's choice of file name
set theDB to POSIX path of (choose file name default location thePath as alias default name "computers")
--separate the folder from the file name
set astid to AppleScript's text item delimiters
set AppleScript's text item delimiters to "/"
set {theList, theCount} to {text items of theDB, count text items of theDB}
set AppleScript's text item delimiters to astid
set theName to (item theCount of theList) as Unicode text
set pathLength to length of theDB
set nameLength to length of theName
set theFolder to POSIX file (text 1 thru (pathLength - nameLength) of theDB) as text
tell application "Database Events"
--create database
set theDB to make new database with properties {name:theName, location:theFolder}
--Count databases to be sure we created it
set theCount to count databases
log theCount
--be sure to save it!
close database theName saving yes
end tell
Note that while you can use location as one of the properties used to open a database, by default Database Events creates a “Databases” folder in your Documents folder and will place new databases there unless you specify a different place.
You use the standard commands open and close to access databases and to put them away when you are done. By default, Database Events has a delay of 5 minutes (300 seconds) before it quietly quits, and if you haven’t saved a database you created before the application quits, you’ll lose your data. You should always make a habit of saving your data after each operation. You can change the quit delay using the quit delay command. Here we’ll check the quit delay and open and close a database, just for practice:
--set default path to the databases folder
set thePath to ((path to the documents folder from user domain) as text) & "Databases:"
--get user's choice
set theDB to POSIX path of (choose file default location thePath as alias without invisibles and multiple selections allowed)
tell application "Database Events"
--get the quit delay
set theDelay to quit delay
--open the database
open database theDB
--now close it, but don't save since we made no changes
close database 1 saving no
end tell
display dialog "Delay is " & theDelay
We must put the display dialog command outside of the Database Events tell block because the application has no user interface, and so can’t handle UI requests.
A Script Outstanding Its Field(s)
OK, now we get to the meat! Let’s create some records and some fields, and input some data. You access the fields and records the same way that you would any nested object in Applescript, using of or tell blocks directed at each level. Take a look at the script below and see if you can’t read it as is:
--set default path to the databases folder
set thePath to ((path to the documents folder from user domain) as text) & "Databases:"
--get user's choice of file name
set theDB to POSIX path of (choose file name default location thePath as alias default name "computers")
--separate the folder from the file name
set astid to AppleScript's text item delimiters
set AppleScript's text item delimiters to "/"
set {theList, theCount} to {text items of theDB, count text items of theDB}
set AppleScript's text item delimiters to astid
set theName to (item theCount of theList) as Unicode text
set pathLength to length of theDB
set nameLength to length of theName
set theFolder to POSIX file (text 1 thru (pathLength - nameLength) of theDB) as text
set theComputers to {{"iMac", "128mb", "PPC G3"}, {"PowerMac G4", "512mb", "PPC G4"}, {"PowerMac G5", "1GB", "PPC G5"}, {"Mac Pro", "2GB", "Intel Core 2 Duo"}}
tell application "Database Events"
set theDatabase to make new database with properties {name:theName, location:theFolder}
tell theDatabase
repeat with aRec from 1 to length of theComputers
set theCurrentComputerInfo to item aRec of theComputers
set theRecord to make new record with properties {name:item 1 of theCurrentComputerInfo}
tell theRecord
make new field with properties {name:"Memory", value:item 2 of theCurrentComputerInfo}
make new field with properties {name:"CPU", value:item 3 of theCurrentComputerInfo}
end tell
end repeat
close saving yes
end tell
end tell
This should all be familiar to moderately-experienced scripters. When would you use a database instead of just a text file? Any time that you need access to a record without wanting to read an entire file is a good candidate for a database.
One of the oddities of the Applescript dictionary for Database Events is that every record has a name as well as an ID number. While that probably doesn’t seem odd to most scripters, to anyone with a SQL background it will seem unusual.
Another oddity is that you create fields on a per record basis. In most database applications you create a set of fields and every record has the same fields. While the make command makes perfect sense to scripters, the non-standard way of handling data may disturb SQL programmers.
That being said, the accessing of records and fields is, like the creation of them, fairly straightforward. You select records using the familiar whose…(is/is not) clause. Once your record (or subset of records) has been selected, you can access the fields just as you would expect, using value of field fieldname.
--set default path to the databases folder
set thePath to ((path to the documents folder from user domain) as text) & "Databases:"
--get user's choice
set theDB to POSIX path of (choose file default location thePath as alias without invisibles and multiple selections allowed)
set DB1 to ""
--get record list
tell application "Database Events"
launch
set DB1 to open database theDB
tell DB1
set theRecords to name of every record
end tell
close DB1
end tell
--display list for choice
set theRec to (choose from list theRecords with prompt "Choose a Record by Name:" without multiple selections allowed and empty selection allowed) as text
if theRec is not "false" then
tell application "Database Events"
set DB1 to open database theDB
tell DB1
set aRecord to first record whose name is theRec
tell aRecord
set {Num, Mem, Proc} to {id, value of field "Memory", value of field "CPU"}
end tell
end tell
close DB1
end tell
display dialog "ID: " & Num & return & "Record: " & theRec & return & "Memory: " & Mem & return & "CPU: " & Proc
end if
Home (Data)Base
That’s it! Database Events is one of the easiest of the “Events” applications, but it can be very powerful, especially if you need to build large databases quickly. Using Applescript you could read in a text file, parse it out and build a database that would be much more usable than the text file was.
I should also add that this application makes Applescript Studio applications much more useful, too. In fact, you could build a Studio application that would create and manage databases!
While the commands and classes are simple, I found that it takes a while to get used to accessing data you can’t see without help. Take some time and work with a test database before you try to do anything critical. But you should find that the learning curve is short, especially if you have any database experience.
Have fun and crunch some code!