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
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
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
Mac OS X Hints
http://hints.macworld.com/article.php?story=20110712192105228