Thursday, December 14, 2017

#1 2007-10-29 06:00:15 am

Kevin Bradley
Administrator
From:: Independence, MO
Registered: 2006-03-13
Posts: 548
Website

All Your Database Are Belong To Applescript

by Kevin Bradley

(Data)Basic Instinct
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:

Applescript:


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

Applescript:


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

Applescript:


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

Applescript:


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


Nitewing '98
--
I distrust morning people, largely because I suspect them of getting together early one day while the rest of us were asleep and setting up the rules of civilization.


Filed under: Bradley, Database

Offline

 

Board footer

Powered by FluxBB

RSS (new topics) RSS (active topics)