According to Apple’s “Feature List” (http://www.apple.com/macosx/features/applescript/) there should be a new “Data Events Suite” for Applescript that provides an interface to SQLite. I’ve dug around everywhere and haven’t been able to find any documentation on this. Does it actually exist? Does anyone have experience interfacing SQLite databases with Applescript?
I’d like to avoid having to do:
do shell script “sqlite3 mydb ‘select * from table;’”
and then parsing the output.
There is a faceless app called ‘Database Events’. You can find it by opening a new dictionary from the file menu. But I’ve found no documentation about it. I’ve tried a few things (like creating a new database) but so far with no results. So if you find any documentation, please post an URL or something because I would love to read more about it.
Bart
Here’s a rough example:
set theFolder to choose folder
tell application "Database Events"
set theDB to make new database ¬
with properties {name:"Test", location:theFolder}
tell theDB
set theRecord to make new record ¬
with properties {name:"SomeRecord", id:1}
end tell
tell theRecord
set field1 to make new field ¬
with properties {name:"ID", value:1}
set field2 to make new field ¬
with properties {name:"Name", value:"foo"}
end tell
return {(value of field1), (value of field2)}
end tell
I also made a small diagram.
Model: Mac mini
AppleScript: 1.10
Browser: Safari 412
Operating System: Mac OS X (10.4)
Thank you very much for the info, that’s much more than I could find anywhere.
However, I’ve found some… difficulties… with the example code. This may actually be a problem with the “Data Events Suite” implementation itself.
Your example creates a record, and adds fields. However, this is different from a typical SQL database in that there was no table created. It appears the Data Events Suite doesn’t even support tables at all. I guess the expectation is a script would just keep creating records? Then when I later want to use these records I would increment through them all, searching for the record I want by looking at a particular field?
What’s even more, after creating a new database with the example Applescript, sqlite3 doesn’t even think it is a valid database. It opens, but there are no records since there are no tables. I verified this by trying out sqlitebrowser (http://sqlitebrowser.sourceforge.net/) which won’t open the db at all.
So I’m at a loss how this is truly useful? I really want to be able to do decent sql queries (which sqlite3 supports) such as “select * from table1” or “select name, date from table1 where amount > 100”.
I actually already have an sqlite db I’ve created from a larger mysql database. My goal is to open this db, grab records and display them in a list, and allow a user to add more records to the db for later use.
Any more suggestions on how to open an existing DB, search tables/records, and get their data? Thanks again!
Model: PowerMac G5, dual 1.8GHz
AppleScript: 1.10
Browser: Firefox 1.0.3
Operating System: Mac OS X (10.4)
I believe it’s supposed to look like this (as you touched on in your post):
Database “Info”
Table “Name”
Field “ID”, “Name”, “Type”
1 foo abc
2 bar xyz
Done via Database Events, it seems to look like this (in theory):
Database “Name”
Record “1”
Field Name = “Name”, Value = “foo”
Field Name = “Type”, Value = “abc”
Record “2”
Field Name = “Name”, Value = “bar”
Field Name = “Type”, Value = “xyz”
I’m not sure what else to tell you right now. Thanks for pointing out the SQLite browser.
This doesn’t quite work. For instance, in your “theory example” (nice name 
) you say the database is “Info”, but this can’t be set in the Applescript?
I’ve fiddled with it some more and found the following:
–
dbevLayout 0 --I can “attach” this file to an existing db by using the sqlite3 command “.import myDB.dbev myTable” using an existing table in an existing database created with sqlite. However, this pulls in the plist and binary data.
The dbevLayout = 0 in the plist concerns me. So does the fact that there is plist information embedded in what should be an SQLite db.
Well, that could be the filename (Name.dbev).
But it’s still leaving out information. There should be the specification of a database (“Info”), table(“name”), and then records. However, the standard SQL database name (“info” in the example) isn’t used. When in applescript you declare a new Database with the name:“Name”, it then saves the file as Name.dbev.
Anyways, I think what’s happening is Apple isn’t really saving the file out in an SQLite way that can be read by sqlite by default. Somehow they are storing a plist inside of a table that must get loaded/edited in an odd way by the “Database Events Application”
In the meantime (i.e. until Apple actually puts up some documentation - hopefully during/after WWDC) I’ll have to deal with trying to save/load in this odd format.
Actually, that’s something I haven’t done yet, trying to load this DB back with Applescript. Have you tried it?
Well, in the meantime, until more documentation/examples come out I’ve found a pretty good free toolkit. MySQL4Applescript is a succinct applescript library for making MySQL calls and handling the returned data. You can search for it at versiontracker or (http://www.macupdate.com/info.php/id/12597). The originator/company website is down, so I can’t offer more information.
Yes, there should be.
Hey Guys,
Just as a heads up, mySQL4AppleScript is still on Yara Valley’s website, you just have to hunt around for it, as the link is broken from MacUpdate.
mySQL4AppleScript is a library of AppleScript handlers for accessing mySQL databases. mySQL4AppleScript requires mySQL to be installed on Mac OS X. This interface simplifies mySQL access from AppleScript, however recommend some experience of mySQL before using this interface. Download mySQL4AppleScript here.
HTH
I wrote mysql4Applescript
The download link always worked, but the website link was broken on macupdate. I have just fixed that.
mysql4Applescript is quite useable but I would make a caveat. The main reason I never released beyond beta is because there is a couple of issues with using it. To make it completely robust it would have been necessary to pass every character being sent to the command line to create the proper escape sequence, I felt that this would have slowed it down too much and certainly for the uses I needed mysql4AppleScript for I didn’t need that level of checking. I also only tested it against one version of mysql v4.013.
I wouldn’t recommend mysql4Applescript for novice applescript or mysql users.
Jean Baptiste Le-Stang (I am sure I have gotten the spelling wrong) has written a osaxen that allows you to access a mysql database.
HTH.
Kevin
I can create a database, add some records and query those records, but I’m having trouble sharing the database across scripts. What’s the correct way to open an existing database?
None of several variations on this woked:
set theFile to choose file
tell "Database Events"
set theDB to open theFile
tell theDB
end tell
end tell
Likewise doing this in different scripts didn’t seem to share the records (that is, I’d get an empty database even though another script had created it and could re-open it and list its records):
tell application "Database Events"
set dbMap to make new database with properties {name:"myDatabase"}
tell dbMap
set record_count to count of records
end tell
end tell
display dialog "there are now " & record_count & " records in the database"
Model: PowerMac G5
AppleScript: 1.10
Browser: Safari 412
Operating System: Mac OS X (10.4)
The missing documentation might look like:
set my_db to choose file
tell application "Finder" to set my_db to POSIX path of my_db
tell application "Database Events" to get name of records of database my_db
--> {"Record 1"}
set my_location to choose folder with prompt "Choose a location in whch to save this DB"
set my_name to text returned of (display dialog "Choose a name for your database:" default answer "")
tell application "Database Events"
set my_db to make new database with properties {name:my_name, location:my_location}
set my_db_file to (location of my_db as Unicode text) & "/" & my_name & ".dbev"
tell my_db
set a_record to make new record with properties {name:"Record 1", id:1}
tell a_record to make new field with properties {name:"Field A", value:"Test Value"}
close saving yes -- this clears the database out of memory
end tell
set my_db to database my_db_file -- this reads the database from the file that was saved
tell my_db to get name of records whose value of field "Field A" starts with "Test"
--> {"Record 1"}
end tell
That did the trick. Thanks!