I’ve been playing with SQLite lately. It’s quite usable from AppleScript via the command line tool sqlite3 and do shell script, but it seems to me that this approach has several drawbacks from AppleScript.
The biggest issue is that everything is reduced to text. So you can end up doing a lot of text manipulation just to get stuff in and out. This is especially the case where there are a lot of results. And while it’s not slow, it could be faster.
There is a third-party framework, FMDB, that puts an Objective-C wrapper around SQLite, so it can be called from AppleScriptObjC. Unfortunately some of it is lower level and more complicated than would be ideal for AppleScript.
So I’ve written a new framework, FMDBAS. This is the FMDB framework wrapped in a more AppleScript-friendly face (but maintaining access to the more advanced stuff for those who need it). Initial tests show it’s faster – in some cases considerably so. It’s main advantages are:
-
Easy use of SQL
?placeholders, so you can optionally pass parameters as lists containing differing classes. You can handle classes such as dates and images much more easily. -
Query results are returned in either arrays/lists or dictionaries/records for each row, so there’s no need to massage a string to extract the results.
-
Query results are returned initially as Cocoa arrays. You can coerce them to AppleScript lists (a simple “
as list”), but in the case of large results it’s often quicker to deal with the raw arrays. And because arrays are returned as a simple pointer, large result sets are returned much, much quicker. -
Support for transactions. You can batch updates together for greater efficiency.
-
Lower overhead than shell, and combined with transactions this means there’s less need to build long statements to perform things like multiple insertions.
-
Better error handling.
There are a few cons:
-
You need to add the framework to your system. I may wrap it in a library at some stage.
-
You need at least a basic understanding of AppleScriptObjC syntax to use it.
-
It won’t work under versions earlier than 10.10 at this stage.
It’s still at the early testing stage, and I’m looking for interested volunteers. If that’s you, please email me.
As a taster, here are two scripts borrowed from Adam Bell’s macscripter.net Introduction to SQLite3 tutorial http://www.macscripter.net/viewtopic.php?id=24744, one to build a database and another to read it. (I’ve modified the first slightly to avoid problems running it repeatedly.) Here’s the first script using the do shell script method:
set loc to space & "~/desktop/TestDB.db" & space
set head to "sqlite3" & loc & quote
set tail to quote
-- "head" tells SQLite where to put our db if it doesn't exist, identifies it if it does.
-- "head" is the opening statement of every future command to our db.
-- "tail" ends every query started with "head".
-- Next, we set up a table and give the columns labels (there can be several).
-- Note the space between the semicolon (which ends every line) and the quote.
set tableName to "mods"
set newTbl to "drop table " & tableName & ";create table " & tableName & "(firstname, lastname, country); "
-- Now we set up the data to be entered in the table
set d1 to "insert into mods values('Ray', 'Barber', 'USA'); "
set d2 to "insert into mods values('jj', 'Sancho', 'Spain'); "
set d3 to "insert into mods values('Adam', 'Bell', 'Canada'); "
set d4 to "insert into mods values('Bruce', 'Phillips', 'USA'); "
set d5 to "insert into mods values('Kim', 'Hunter', 'USA'); "
set d6 to "insert into mods values('Kevin', 'Bradley', 'USA'); "
-- And finally, build the SQLite query and execute it
do shell script head & newTbl & d1 & d2 & d3 & d4 & d5 & d6 & tail
And here’s the FMDBAS equivalent:
use AppleScript version "2.4" -- Yosemite (10.10) or later
use framework "Foundation"
use framework "FMDBAS"
use scripting additions
-- create and open db
set thePath to current application's NSString's stringWithString:"~/desktop/TestDB.db"
set theDb to current application's FMDatabase's databaseWithPathAS:(thePath's stringByExpandingTildeInPath())
theDb's openWithFlagsAS:(current application's FMDBASOpenReadWriteCreate)
-- create table, clearing if necessary it
set tableName to "mods"
theDb's executeUpdateASBool:("drop table if exists " & tableName)
theDb's executeUpdateAS:("create table " & tableName & " (firstname, lastname, country)")
-- enter data
theDb's beginTransactionAS()
repeat with aList in {{"Ray", "Barber", "USA"}, {"jj", "Sancho", "Spain"}, {"Adam", "Bell", "Canada"}, {"Bruce", "Phillips", "USA"}, {"Kim", "Hunter", "USA"}, {"Kevin", "Bradley", "USA"}}
(theDb's executeUpdateAS:"insert into mods values(?, ?, ?)" withArguments:aList)
end repeat
theDb's commitAS()
-- close database
theDb's closeAS()
Here’s the shell script reading example:
-- Combining the path into the head:
set head to "sqlite3 ~/desktop/TestDB.db " & quote
-- Now the query:
set Spain to "select firstname, lastname from mods where country = 'Spain'; "
-- Get the answer (which will appear in your script as a list, so we'll name the parts
set {firstname, lastName} to words of (do shell script head & Spain & quote)
--> {"jj", "Sancho"}
set tLast to "select lastname from mods where firstname = 'Bruce'; "
set surname to do shell script head & tLast & quote --> "Phillips "
And the FMDBAS version:
use AppleScript version "2.4" -- Yosemite (10.10) or later
use framework "Foundation"
use framework "FMDBAS"
use scripting additions
-- create and open db
set thePath to current application's NSString's stringWithString:"~/desktop/TestDB.db"
set theDb to current application's FMDatabase's databaseWithPathAS:(thePath's stringByExpandingTildeInPath())
theDb's openWithFlagsAS:(current application's FMDBASOpenReadOnly)
-- query it
set theResult to theDb's executeQueryAS:("select firstname, lastname from mods where country = 'Spain'")
-- *or*:
-- set otherResult to theDb's executeQueryAS:("select firstname, lastname from mods where country = ?") withArguments:{"Spain"}
set surname to theDb's executeQueryAS:("select lastname from mods where firstname = ?") withArguments:{"Bruce"}
theDb's closeAS()
return {theResult as list, surname as list}
These are tiny examples, but the first case is about 10x faster, and the second closer to 100x faster.
.