A note on Database Events

Jul 13, '11 07:30:00AM

Contributed by: tedw

Database Events.app (one of the scriptable background apps that appeared in Tiger) is intended to give some basic access to the sqlite3 database system. It has been generally maligned as a not-too-useful app, and in fact I am one of the people who has maligned it that way. But while playing around with it today I discovered that it is actually much more powerful than anyone seems to realize. Thus, a quick hint to clue everyone in.

First, don't set your hopes too high: DE is not a full-featured SQL processor. There are no tables, no meta-structures, not even a built-in sorting system. The database DE makes is just a simple flat list of records with fields, much like single-page spreadsheet. So for instance, you build up a dataset like follows (this creates a database and populates it with random data):

tell application "Database Events"
  set db to make new database with properties {name:"tester", location:(path to desktop)}
  tell db
    repeat with i from 1 to 15
      set thisRec to make new record with properties {name:"Record " & i}
      tell thisRec
        -- A field called 'name' - e.g. name:"name" - is automatically linked of the 
        -- to the 'name' property of the record, above; doesn't matter which you set.
        make new field with properties {name:"first", value:(some item of {"alpha", "beta", "gamma"})}
        make new field with properties {name:"second", value:(some item of {1, 2, 3})}
        make new field with properties {name:"third", value:(some item of {"double-u", "ex", "why", "zee"})}
      end tell
    end repeat
    close saving yes -- this clears the database out of memory
  end tell
end tell
Run this and you'll see a file called 'tester.dbev' appear on your desktop. You can use this newly created database in the obvious ways, like so:
set db to (POSIX path of (path to desktop)) & "tester.dbev"
tell application "Database Events"
  tell database db
    --get the third record by index
    set a to name of record 3
    --get the value of field 'first' of every record
    set b to field "first"'s value of records
    -- set the value of field 'second' of the record "Record 11"
    set value of field "second" of record "Record 11" to "Boo"
  end tell
end tell
But what's not at all evident is that each search is itself an object that you can modify, search on, and otherwise work with. For example:
set db to (POSIX path of (path to desktop)) & "tester.dbev"
tell application "Database Events"
  set bill to database db
  tell database db
    -- set the value of field 'second' to 'hey' wherever third's value is 'why'
    tell (records whose field "third"'s value is "why")
      tell field "second" to set its value to "hey!"
    end tell
    -- set the value of field 'second' of any record whose name is "Record 11" (note the plural)
    set value of field "second" of records "Record 11" to "Boo"
    -- delete any record whose name contains '1'
    delete (records whose name contains "1")
    --get the name and the value of field 'second' where the value of field 'third' is 'ex'
    tell (records whose field "third"'s value is "ex")
      set c to {name, value of field "second"}
    end tell
    close saving yes
  end tell
end tell
So as you can see, once you get this trick of using compound objects Database Events becomes a reasonably powerful tool, at least for quick and simple databasing.

[crarko adds: The only other real reference I could find to Database Events.app is here. The application is found in System » Library » Core Services. The article referenced above gives more information about scripting Database Events.]

Comments (4)


Mac OS X Hints
http://hints.macworld.com/article.php?story=20110712192105228