Submit Hint Search The Forums LinksStatsPollsHeadlinesRSS
14,000 hints and counting!

A note on Database Events System
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.]
    •    
  • Currently 3.43 / 5
  You rated: 1 / 5 (7 votes cast)
 
[4,973 views]  

A note on Database Events | 4 comments | Create New Account
Click here to return to the 'A note on Database Events' hint
The following comments are owned by whoever posted them. This site is not responsible for what they say.
A note on Database Events
Authored by: llee on Jul 13, '11 09:47:45AM

I gave this a try when I became dissatisfied with my personal DVD collection database manager application a few years ago. All those kinds of programs want to help me manage all the information about the movies themselves such as studio, stars, release date, etc. I figure why not let somebody else keep track of all that, what I want is to know if I have it and what shelf or folder I have to go to so I can pull it out or put it back. I have 4 AppleScripts that handle it for me:

Add listing
Delete listing
Find Listing
Build HTML Listing (This one sorts the list alphabetically by title and formats it so I can easily print a booklet to keep next to the TV.)



[ Reply to This | # ]
addendum
Authored by: tedw on Jul 13, '11 11:21:33AM
I noticed this after I posted the hint last night. Apparently you can also do some cross-database queries. For example, if you have two databases d1 and d2 open, you can use language like the following:

(records of d1 whose (value of field "f1") is in (value of field "f2" of records of d2))

To get a list of all records in database d1 whose f1 value equals the f2 value of some record in database d2. I don't see any obvious (or un-obvious) way to do anything like an sql join, though. I wish there was some way to get a look at the source code for the app.

[ Reply to This | # ]

More Info on Database Events
Authored by: mr. applescript on Jul 13, '11 11:36:50AM
Here's another source for information about Database Events. Bill Cheeseman wrote a chapter about the app in AppleScript 1-2-3 from PeachPit Press.

http://www.peachpit.com/store/product.aspx?isbn=0321149319

Here's an example of how to generate a database:

set presidential_data to {
	{"Adams", "John", "", 2, 1797, 1801, "Federalist"}, 
	{"Adams", "John", "Quincy", 6, 1825, 1829, "Democratic-Republican"}, 
	{"Arthur", "Chester", "A.", 21, 1881, 1885, "Republican"}, 
	{"Buchanan", "James", "", 15, 1857, 1861, "Democrat"}, 
	{"Bush", "George", "H. W.", 41, 1989, 1993, "Republican"}, 
	{"Carter", "Jimmy", "", 39, 1977, 1981, "Democrat"}, 
	{"Cleveland", "Grover", "", 22, 1885, 1889, "Democratic"}, 
	{"Cleveland", "Grover", "", 24, 1893, 1897, "Democratic"}, 
	{"Clinton", "William", "J.", 42, 1993, 2001, "Democrat"}, 
	{"Coolidge", "Calvin", "", 30, 1923, 1929, "Republican"}, 
	{"Eisenhower", "Dwight", "D.", 34, 1953, 1961, "Republican"}, 
	{"Fillmore", "Millard", "", 13, 1850, 1853, "Whig"}, 
	{"Ford", "Gerald", "R.", 38, 1974, 1977, "Republican"}, 
	{"Garfield", "James", "A.", 20, 1881, 1881, "Republican"}, 
	{"Grant", "Ulysses", "S.", 18, 1869, 1877, "Republican"}, 
	{"Harding", "Warren", "G.", 29, 1921, 1923, "Republican"}, 
	{"Harrison", "Benjamin", "", 23, 1889, 1893, "Republican"}, 
	{"Harrison", "William", "Henry", 9, 1841, 1841, "Whig"}, 
	{"Hayes", "Rutherford", "B.", 19, 1877, 1881, "Republican"}, 
	{"Hoover", "Herbert", "C.", 31, 1929, 1933, "Republican"}, 
	{"Jackson", "Andrew", "", 7, 1829, 1837, "Democrat"}, 
	{"Jefferson", "Thomas", "", 3, 1801, 1809, "Democratic-Republican"}, 
	{"Johnson", "Andrew", "", 17, 1865, 1869, "Democrat"}, 
	{"Johnson", "Lyndon", "B.", 36, 1963, 1969, "Democrat"}, 
	{"Kennedy", "John", "F.", 35, 1961, 1963, "Democrat"}, 
	{"Lincoln", "Abraham", "", 16, 1861, 1863, "Republican"}, 
	{"Madison", "James", "", 4, 1809, 1817, "Democratic-Republican"}, 
	{"McKinley", "William", "", 25, 1897, 1901, "Republican"}, 
	{"Monroe", "James", "", 5, 1817, 1825, "Democratic-Republican"}, 
	{"Nixon", "Richard", "M.", 37, 1969, 1974, "Republican"}, 
	{"Pierce", "Frankin", "", 14, 1853, 1857, "Democrat"}, 
	{"Polk", "James", "K.", 11, 1845, 1849, "Democrat"}, 
	{"Reagan", "Ronald", "", 40, 1981, 1989, "Republican"}, 
	{"Roosevelt", "Frankin", "D.", 32, 1933, 1945, "Democrat"}, 
	{"Roosevelt", "Theodore", "", 26, 1901, 1909, "Republican"}, 
	{"Taft", "William", "H.", 27, 1909, 1913, "Republican"}, 
	{"Taylor", "Zachary", "", 12, 1849, 1860, "Whig"}, 
	{"Truman", "Harry", "S.", 33, 1945, 1953, "Democrat"}, 
	{"Tyler", "john", "", 10, 1841, 1845, "Whig"}, 
	{"Van Buren", "Martin", "", 8, 1837, 1841, "Democrat"}, 
	{"Washington", "George", "", 1, 1789, 1797, "None"}, 
	{"Wilson", "Woodrow", "", 28, 1913, 1921, "Democrat"}}

-- field names must match the order of the data
set the field_names to {"Last Name", "First Name", "Middle Initials", "Index", "Start Year", "End Year", "Party"}

-- database file names have a name extension of "dbev"
set database_name to "Presidents"

tell application "Database Events"
	if (count of databases) is greater than 0 then
		quit
	end if
end tell

-- build the database
-- NOTE: the database file is created with a name extension of "dbev" in a directory named Databases in the Documents folder
tell application "Database Events"
	set this_database to make new database with properties {name:database_name}
	tell this_database
		repeat with record_data in the presidential_data
			set this_record to make new record with properties {name:""}
			tell this_record
				repeat with i from 1 to the count of field_names
					set the field_name to item i of field_names
					set the field_data to item i of record_data
					make new field with properties {name:field_name, value:field_data}
				end repeat
			end tell
		end repeat
		save
	end tell
	quit
end tell
And how to query the database:

-- query the database
set database_file to (POSIX path of (path to documents folder)) & "Databases/presidents.dbev"

tell application "Database Events"
	tell database database_file
		get value of field "Last Name" of (every record whose value of field "Start Year" is greater than or equal to 1800 and value of field "Start Year" is less than or equal to 1900)
		-- {"Adams", "Arthur", "Buchanan", "Cleveland", "Cleveland", "Fillmore", "Garfield", "Grant", "Harrison", "Harrison", "Hayes", "Jackson", "Jefferson", "Johnson", "Lincoln", "Madison", "McKinley", "Monroe", "Pierce", "Polk", "Taylor", "Tyler", "Van Buren"}
		get value of field "Last Name" of (every record whose value of field "Party" is "Republican")
		get value of field "Last Name" of (every record whose value of field "Start Year" is greater than 1899)
		get the first record whose value of field "Last Name" contains "Adams"
		get the value of field "Last Name" of every record
	end tell
end tell


[ Reply to This | # ]
A note on Database Events
Authored by: DougAdams on Jul 13, '11 04:59:27PM
I don't want to sound like a party pooper (since this is quite a good hint), but I used Database Events for a while and found it slow for even fairly modest tasks. However, manipulating sqlite3 with do shell script in AppleScript is very powerful. I use it all the time. If interested, here's Adam Bell's MacScripter tutorial from 2005.
---
dougscripts.com


[ Reply to This | # ]