TweetFollow Us on Twitter

Introduction to Database Events

Volume Number: 22 (2006)
Issue Number: 2
Column Tag: Programming

AppleScript Essentials

Introduction to Database Events

by Benjamin S. Waldie

Data storage and access is an important part of AppleScripting, particularly in complex AppleScript-based projects. Some scripts may need to store user-entered data for later reference, perhaps during an entirely new session. Some may need a location to log activity or errors during processing. Others may need to access structured data, in order to do something fairly complex, such as building a catalog.

In this month's column, we will discuss the use of Database Events, a new and exciting feature in Mac OS X, for storage and access of data during script execution.

Data Storage and Access Option

There are many techniques that are commonly used to store and access data with AppleScript. One common method is to simply write information to a text file on the user's hard drive, on a server, or elsewhere. This can be done with the File Read/Write Commands suite in the Standard Additions scripting addition, which is installed as part of Mac OS X. This technique of writing information to a text file may suffice for basic types of informational storage. However, depending on the complexity and amount of data being stored or accessed, you may need to write parsing code in order to work with the information.

In AppleScript Studio projects, another method of data storage and access is to make use of a project's preferences file, i.e. it's plist file. This is actually quite easily done, and is a common technique for storing such information as persistent user interface options and settings.

For complex data storage and access, a more robust solution may be necessary. Typically, developers will turn to a database of some kind. FileMaker Pro is usually quite popular among AppleScript developers, primarily due to its ease of use, customizable interface, and robust AppleScript support. For those not familiar with FileMaker Pro, I highly recommend downloading the 30 day demo from FileMaker's website at http://www.filemaker.com, and checking it out for yourself.

With Mac OS X Tiger, there is a new option - Database Events! If you have never heard of Database Events, that is not surprising. Database Events was released as part of Mac OS X 10.4 Tiger. However, it may have been missed by many, as the only mention of it that I could easily find was a small blurb on the AppleScript webpage at http://www.apple.com/macosx/features/applescript/. In this month's column, we will explore Database Events' syntax, and discuss ways that it can be integrated into your own scripts in order to store and access data during script execution.

What is Database Events?

Database Events is an AppleScriptable background application, which comes installed with Mac OS X 10.4 and higher. It is located in the System > Library > CoreServices folder, which you may recognize as the location of other scriptable background applications that are installed with Mac OS X, including Image Events and System Events.

Like its name implies, Database Events provides a way for AppleScript to perform basic events with databases, in particular, SQLite databases. Tasks that can be performed with Database Events include creating, opening, and saving databases, as well as accessing records and fields within databases.

By default, Database Events does not appear in the Script Editor's Library palette, which is accessible via the Window > Library menu. Click the + button in the Library palette, and navigate to Database Events in order to add it to the palette, thus making it quickly and easily accessible whenever Script Editor is launched. See figure 1.


Figure 1. Script Editor Library Palette

Once added to the Library palette, you may double click on Database Events in order to open its AppleScript dictionary. See figure 2.


Figure 2. Database Events AppleScript Dictionary

Getting Started with Scripting Database Events

If you launch the Activity Monitor application, located in the Applications > Utilities folder on your machine, immediately after logging in, you will notice that Database Events is not launched by default. To launch Database Events, use the launch command. For example:

tell application "Database Events"
   launch
end tell

As with any other application, when you are ready to quit Database Events, you may use the quit command. For example:

tell application "Database Events"
   quit
end tell

Please note that, in order to preserve memory, once launched, Database Events will automatically quit after 300 seconds, i.e. 5 minutes, of idle time or inactivity. When this occurs, it is important to note that any unsaved changes in any opened databases will be lost! Because of this, it is essential that you write your AppleScript code to save any database you may be modifying on a regular basis. We will discuss saving databases a little bit later.

If you require more than 5 minutes of inactivity, then it is actually possible to adjust the delay period by modifying the value of the quit delay property of the Database Events application. For example:

tell application "Database Events"
   set quit delay to 600
end tell

In the example code above, the quit delay is changed from the default 300 seconds to 600 seconds, or 10 minutes. Alternatively, you may also choose to decrease the default delay period by setting the quit delay property to a value of less than 300 seconds. Please note that, when modifying the value of this property, it will be reset to 300 seconds again the next time Database Events is launched.

You may check the value of the delay period by retrieving the quit delay property. For example:

tell application "Database Events"
   quit delay
end tell
--> 300

In some cases, you may not want Database Events to quit automatically at all. To disable automatic quitting entirely, set the quit delay property to a value of 0. For example:

tell application "Database Events"
   set quit delay to 0
end tell

Working with Databases

Now that we have discussed the basics of the Database Events application, let's move on to databases.

According to Database Events' AppleScript dictionary, the database class is an element of the application class. The dictionary also indicates that a database possesses a name property and a location property.

You can instruct the Database Events application to create a new database by using the make command, and specifying a name for the database. For example, the following sample code will create a new database named "Super Heroes", and you can see that the result of the command is a reference to the newly created database.

tell application "Database Events"
   make new database with properties {name:"Super Heroes"}
end tell
--> database "Super Heroes" of application "Database Events"

Once a database has been created, you can access it by its name, or by its index. For example:

tell database "Super Heroes"

-- OR

tell database 1

The following example code demonstrates how to retrieve the properties of an opened database:

tell application "Database Events"
   properties of database 1
end tell
--> {class:database, name:"Super Heroes", location:"~/Documents/Databases"}

When accessing the properties of a newly created database, you may notice that the location property of the database is set to a value of "~/Documents/Databases" by default. This does not mean that the database has actually been created in this location. In fact, this folder may not even exist yet. It simply means that this is the location in which the database will be created, once a save command has been issued, or once a record has been created.

Optionally, when a database is created, you may choose to indicate a custom location for the database by specifying a value for the location property. For example, the following code will prompt the user to select an output folder. It will then create a new database with a location property value of the folder specified by the user.

set theOutputFolder to choose folder with prompt "Please select an output folder:"
tell application "Database Events"
   make new database with properties {name:"Super Heroes", location:theOutputFolder}
end tell
--> database "Super Heroes" of application "Database Events"

To save an opened database at any time, use the save command. This will cause the database to be saved into the value specified in the database's location property, using the name specified in the name property. For example:

tell application "Database Events"
   save database 1
end tell
--> database "Super Heroes" of application "Database Events"

To open a saved database, use the open command, and specify the path of the database file to be opened. However, please note that you must specify this path as a POSIX style path. Otherwise, you will receive an error message. For example, the following code will open a database named Super Heroes.dbev on my desktop.

set theDBPath to POSIX path of "Macintosh HD:Users:bwaldie:Desktop:Super Heroes.dbev"
tell application "Database Events"
   open database theDBPath
end tell
--> database "Super Heroes" of application "Database Events"

To determine the number of opened databases, use the count command. For example:

tell application "Database Events"
   count databases
end tell
--> 1

To close a database, use the close command. In order to eliminate the possibility of receiving an error when attempting to close an unsaved modified database, specify a value for the saving parameter. For example, the following code will close a database without saving.

tell application "Database Events"
   close database 1 saving no
end tell

Working with Records

Once a database exists, you can now begin creating records within that database. Like creating databases, the make command is used to create new records. Records possess an ID property and a name property. A value for the ID property is automatically assigned a unique ID number when a record is created. However, a value for the name property must be specified whenever a record is created. The following example code demonstrates how a new record is created, using a specified name.

tell application "Database Events"
   tell database "Super Heroes"
      make new record with properties {name:"Batman"}
   end tell
end tell
--> record id 157660455 of database "Super Heroes" of application "Database Events"

You can see that, in this case, the result of the make command is a reference to the newly created record, and that a unique ID of 157660455 has been applied.

Once a record exists, it may be referred to by its index, its unique ID, or its name. For example:

tell record 1

-- OR

tell record id 157660455


-- OR

tell record "Batman"

To count the records of a database, use the count command. For example:

tell application "Database Events"
   tell database "Super Heroes"
      count records
   end tell
end tell
--> 1

You can delete records in a database by using the delete command. For example, the following code demonstrates how to delete every record in a specified database.

tell application "Database Events"
   tell database "Super Heroes"
      delete every record
   end tell
end tell

Working with Fields

Once a record has been created, you must create fields to be populated within that record. This must be done every time a new record is created. Fields may not be created at the database level in Database Events.

By default, a name field already exists when a record is created, and it contains the name of the record. You may create additional fields by using the make command. Fields have a name property and a value property, which may be specified at the time of field creation.

The following code demonstrates how to create a record with two fields. The first field, called name, is created automatically and assigned a value of Batman when the record is created. The second field, called Secret Identity, is assigned a value of Bruce Wayne when the field is created.

tell application "Database Events"
   tell database "Super Heroes"
      set theRecord to make new record with properties {name:"Batman"}
      tell theRecord
         make new field with properties {name:"Secret Identity", value:"Bruce Wayne"}
      end tell
   end tell
end tell
--> field "Secret Identity" of record id 157660455 of database "Super Heroes" of 
   application "Database Events"

To count the fields of a specified record, use the count command. For example:

tell application "Database Events"
   tell database "Super Heroes"
      tell record "Batman"
         count fields
      end tell
   end tell
end tell
--> 2

Pulling it Together

Now that we have discussed creating databases, records, and fields, let's pull it all together. The following example code demonstrates how to create a new database of records, based on a list of data. The database's name will be Super Heroes, and it will consist of a record for each super hero. Each record will contain a name field, and a secret identity field. Once built, the database will be saved into the default location, the Documents > Databases folder within your user folder.

set theSuperHeroes to {{"Batman", "Bruce Wayne"}, {"Spiderman", "Peter Parker"}, 
   {"Superman", "Clark Kent"}}
tell application "Database Events"
   set theDatabase to make new database with properties {name:"Super Heroes"}
   tell theDatabase
      repeat with a from 1 to length of theSuperHeroes
         set theCurrentSuperHeroInfo to item a of theSuperHeroes
         set theRecord to make new record with properties {name:item 1 of theCurrentSuperHeroInfo}
         tell theRecord
            make new field with properties {name:"Secret Identity", value:item 2 of 
               theCurrentSuperHeroInfo}
         end tell
      end repeat
   end tell
end tell

Now that you have a database, complete with fields and records, you can search for records in a variety of ways. For example, the following code demonstrates how to locate a record by name:

tell application "Database Events"
   tell database "Super Heroes"
      first record whose name = "Superman"
   end tell
end tell
--> record id 157661083 of database "Super Heroes" of application "Database Events"

The following code demonstrates how to locate a record by the value of one of its fields:

tell application "Database Events"
   tell database "Super Heroes"
      first record whose value of field "Secret Identity" contains "Parker"
   end tell
end tell
--> record id 157672965 of database "Super Heroes" of application "Database Events"

You can also retrieve and modify the values of fields in existing records. The following example code demonstrates how to retrieve the value of a specified field:

tell application "Database Events"
   tell database "Super Heroes"
      set theRecord to first record whose name = "Superman"
      tell theRecord
         value of field "Secret Identity"
      end tell
   end tell
end tell
--> "Clark Kent"

The following code demonstrates how to modify the value of a specified field:

tell application "Database Events"
   tell database "Super Heroes"
      set theRecord to first record whose name = "Superman"
      tell theRecord
         set value of field "Secret Identity" to "Kent, Clark"
      end tell
   end tell
end tell

In Closing

As you can see, there is a lot that can be done with Database Events. I encourage you to begin using it, testing it on your own, and integrating it into your scripts. While Database Events is not as robust as a fully featured database application, it can provide a quick and easy way to store and access structured data.

Also, please be aware that Database Events is currently at version 1.0. As it continues to mature, I am sure that it will continue to grow and will become an important part of many AppleScript-based workflows.

Until next time, keep scripting!


Ben Waldie is author of the best selling books "AppleScripting the Finder" and the "Mac OS X Technology Guide to Automator", available from http://www.spiderworks.com. Ben is also president of Automated Workflows, LLC, a firm specializing in AppleScript and workflow automation consulting. For years, Ben has developed professional AppleScript-based solutions for businesses including Adobe, Apple, NASA, PC World, and TV Guide. For more information about Ben, please visit http://www.automatedworkflows.com, or email Ben at applescriptguru@mac.com.

 

Community Search:
MacTech Search:

Software Updates via MacUpdate

BetterTouchTool 2.332 - Customize multi-...
BetterTouchTool adds many new, fully customizable gestures to the Magic Mouse, Multi-Touch MacBook trackpad, and Magic Trackpad. These gestures are customizable: Magic Mouse: Pinch in / out (zoom... Read more
BetterTouchTool 2.332 - Customize multi-...
BetterTouchTool adds many new, fully customizable gestures to the Magic Mouse, Multi-Touch MacBook trackpad, and Magic Trackpad. These gestures are customizable: Magic Mouse: Pinch in / out (zoom... Read more
Capture One 11.0.1.40 - RAW workflow sof...
Capture One is a professional RAW converter offering you ultimate image quality with accurate colors and incredible detail from more than 400 high-end cameras -- straight out of the box. It offers... Read more
Capture One 11.0.1.40 - RAW workflow sof...
Capture One is a professional RAW converter offering you ultimate image quality with accurate colors and incredible detail from more than 400 high-end cameras -- straight out of the box. It offers... Read more
GraphicConverter 10.5.4 - $39.95
GraphicConverter is an all-purpose image-editing program that can import 200 different graphic-based formats, edit the image, and export it to any of 80 available file formats. The high-end editing... Read more
Dash 4.1.3 - Instant search and offline...
Dash is an API documentation browser and code snippet manager. Dash helps you store snippets of code, as well as instantly search and browse documentation for almost any API you might use (for a full... Read more
Microsoft OneNote 16.9 - Free digital no...
OneNote is your very own digital notebook. With OneNote, you can capture that flash of genius, that moment of inspiration, or that list of errands that's too important to forget. Whether you're at... Read more
DEVONthink Pro 2.9.17 - Knowledge base,...
Save 10% with our exclusive coupon code: MACUPDATE10 DEVONthink Pro is your essential assistant for today's world, where almost everything is digital. From shopping receipts to important research... Read more
OmniGraffle 7.6 - Create diagrams, flow...
OmniGraffle helps you draw beautiful diagrams, family trees, flow charts, org charts, layouts, and (mathematically speaking) any other directed or non-directed graphs. We've had people use Graffle to... Read more
iFinance 4.3.7 - Comprehensively manage...
iFinance allows you to keep track of your income and spending -- from your lunchbreak coffee to your new car -- in the most convenient and fastest way. Clearly arranged transaction lists of all your... Read more

Latest Forum Discussions

See All

World of Warships Blitz: A guide to tact...
Ahoy mates! It's time to set out on the high seas for some PvP battles, and ... sorry, actually, World of Warships Blitz has nothing to do with pirates. Let's start over. [Read more] | Read more »
Around the Empire: What have you missed...
Around this time every week we're going to have a look at the comings and goings on the other sites in Steel Media's pocket-gaming empire. We'll round up the very best content you might have missed, so you're always going to be up to date with the... | Read more »
Everything about Hero Academy 2: Part 4...
In this part of our Hero Academy 2 guide, we're going to have a look at some of the tactics you're going to need to learn if you want to rise up the ranks. We're going to start off slow, then get more advanced in the next section. [Read more] | Read more »
All the best games on sale for iPhone an...
Another week has flown by. Sometimes it feels like the only truly unstoppable thing is time. Time will make dust of us all. But before it does, we should probably play as many awesome mobile videogames as we can. Am I right, or am I right? [Read... | Read more »
The 7 best games that came out for iPhon...
Well, it's that time of the week. You know what I mean. You know exactly what I mean. It's the time of the week when we take a look at the best games that have landed on the App Store over the past seven days. And there are some real doozies here... | Read more »
Popular MMO Strategy game Lords Mobile i...
Delve into the crowded halls of the Play Store and you’ll find mobile fantasy strategy MMOs-a-plenty. One that’s kicking off the new year in style however is IGG’s Lords Mobile, which has beaten out the fierce competition to receive Google Play’s... | Read more »
Blocky Racing is a funky and fresh new k...
Blocky Racing has zoomed onto the App Store and Google Play this week, bringing with it plenty of classic kart racing shenanigans that will take you straight back to your childhood. If you’ve found yourself hooked on games like Mario Kart or Crash... | Read more »
Cytus II (Games)
Cytus II 1.0.1 Device: iOS Universal Category: Games Price: $1.99, Version: 1.0.1 (iTunes) Description: "Cytus II" is a music rhythm game created by Rayark Games. It's our fourth rhythm game title, following the footsteps of three... | Read more »
JYDGE (Games)
JYDGE 1.0.0 Device: iOS Universal Category: Games Price: $4.99, Version: 1.0.0 (iTunes) Description: Build your JYDGE. Enter Edenbyrg. Get out alive. JYDGE is a lawful but awful roguehate top-down shooter where you get to build your... | Read more »
Tako Bubble guide - Tips and Tricks to S...
Tako Bubble is a pretty simple and fun puzzler, but the game can get downright devious with its puzzle design. If you insist on not paying for the game and want to manage your lives appropriately, check out these tips so you can avoid getting... | Read more »

Price Scanner via MacPrices.net

Deals on clearance 15″ Apple MacBook Pros wit...
B&H Photo has clearance 2016 15″ MacBook Pros available for up to $800 off original MSRP. Shipping is free, and B&H charges NY & NJ sales tax only: – 15″ 2.7GHz Touch Bar MacBook Pro... Read more
Apple restocked Certified Refurbished 13″ Mac...
Apple has restocked a full line of Certified Refurbished 2017 13″ MacBook Airs starting at $849. An Apple one-year warranty is included with each MacBook, and shipping is free: – 13″ 1.8GHz/8GB/128GB... Read more
How to find the lowest prices on 2017 Apple M...
Apple has Certified Refurbished 13″ and 15″ 2017 MacBook Pros available for $200 to $420 off the cost of new models. Apple’s refurbished prices are the lowest available for each model from any... Read more
The lowest prices anywhere on Apple 12″ MacBo...
Apple has Certified Refurbished 2017 12″ Retina MacBooks available for $200-$240 off the cost of new models. Apple will include a standard one-year warranty with each MacBook, and shipping is free.... Read more
Apple now offering a full line of Certified R...
Apple is now offering Certified Refurbished 2017 10″ and 12″ iPad Pros for $100-$190 off MSRP, depending on the model. An Apple one-year warranty is included with each model, and shipping is free: –... Read more
27″ iMacs on sale for $100-$130 off MSRP, pay...
B&H Photo has 27″ iMacs on sale for $100-$130 off MSRP. Shipping is free, and B&H charges sales tax for NY & NJ residents only: – 27″ 3.8GHz iMac (MNED2LL/A): $2199 $100 off MSRP – 27″ 3.... Read more
2.8GHz Mac mini on sale for $899, $100 off MS...
B&H Photo has the 2.8GHz Mac mini (model number MGEQ2LL/A) on sale for $899 including free shipping plus NY & NJ sales tax only. Their price is $100 off MSRP. Read more
Apple offers Certified Refurbished iPad minis...
Apple has Certified Refurbished 128GB iPad minis available today for $339 including free shipping. Apple’s standard one-year warranty is included. Their price is $60 off MSRP. Read more
Amazon offers 13″ 256GB MacBook Air for $1049...
Amazon has the 13″ 1.8GHz/256B #Apple #MacBook Air on sale today for $150 off MSRP including free shipping: – 13″ 1.8GHz/256GB MacBook Air (MQD42LL/A): $1049.99, $150 off MSRP Read more
9.7-inch 2017 WiFi iPads on sale starting at...
B&H Photo has 9.7″ 2017 WiFi #Apple #iPads on sale for $30 off MSRP for a limited time. Shipping is free, and pay sales tax in NY & NJ only: – 32GB iPad WiFi: $299, $30 off – 128GB iPad WiFi... Read more

Jobs Board

AppleCare Support Engineer for *Apple* Medi...
# AppleCare Support Engineer for Apple Media Products Job Number: 113222855 Santa Clara Valley, California, United States Posted: 14-Nov-2017 Weekly Hours: 40.00 Read more
QA Automation Engineer, *Apple* Pay - Apple...
# QA Automation Engineer, Apple Pay Job Number: 113202642 Santa Clara Valley, California, United States Posted: 11-Dec-2017 Weekly Hours: 40.00 **Job Summary** At Read more
*Apple* Video Partnerships - Apple (United S...
# Apple Video Partnerships Job Number: 113059126 Santa Clara Valley, California, United States Posted: 15-Nov-2017 Weekly Hours: 40.00 **Job Summary** Partnerships Read more
Sr. Engineering Manager- *Apple* Media Produ...
# Sr. Engineering Manager- Apple Media Products Job Number: 113335268 Santa Clara Valley, California, United States Posted: 17-Jan-2018 Weekly Hours: 40.00 **Job Read more
*Apple* Retail - Multiple Positions - Apple,...
Job Description:SalesSpecialist - Retail Customer Service and SalesTransform Apple Store visitors into loyal Apple customers. When customers enter the store, Read more
All contents are Copyright 1984-2011 by Xplain Corporation. All rights reserved. Theme designed by Icreon.