by Adam Bell
Ever wanted to create a little database as part of a script? Ever agonized over setting up associations in an array of records or needed a huge collection of properties in your script? Ever used external text files or complex .pref files to store your script’s persistent data? If you’re running OS X 10.4 (Tiger) then SQLite might be just what you need. This tutorial is an introduction to using SQLite from an AppleScript; it is not a tutorial on Standard Query Language (SQL).
Introduction
In Matt Neuburg’s “AppleScript: The definitive Guide, Second Edition” he discusses a new background-only scriptable tool in Tiger’s CoreServices called “Database Events.app”. It is intended for creating and manipulating small databases - a scriptable background-only link to SQLite3. SQLite3 in turn, is a small, open-source database engine introduced in and part of Tiger.
Although the AppleScript Dictionary for Database Events.app is viewable from AppleScript editors, it provides only a subset of what SQLite can do and if there is any documentation for Database Events on Apple’s website, I didn’t find it. Dr. Neuburg says in his book that using Database Events for the creation and manipulation of databases is “nowhere near as powerful, coherent, and complete as the sqlite3 command line tool”, so it’s best in his view to “disregard Database Events as a bad job, and use the command line instead.” After some experiments, I heartily concur. Database Events doesn’t even have a way for scripting the location of the database SQLite creates. This tutorial is therefore an introduction to using SQLite directly from the command line in a ‘do shell script “…”’ command; there is no further mention of Database Events here.
SQLite
What is and isn’t SQLite? it is a mini, single-user open-source unix database engine that understands a simple subset of SQL (Standard Query Language). SQLite is small, fast, reliable, and best of all, simple to use, and simple to learn. If you have never used a database before, SQLite is a good introduction. It should work well for low and medium traffic websites and kiosks; as a replacement for ad hoc textual data files created from your script with “open for access blah with write permission” and later read back (because it is so easy to search a database, but you will have to write a handler to search your own data file), and it is great for scripts that have a lot of data that must be changed, sifted and sorted in diverse ways.
SQLite is not a replacement for a preference file created with a shell “defaults write” statement, but it’s an excellent way to store a bunch of property constants or a complex dictionary of terms required for your script without rolling your own array of records. It is not for multiple simultaneous users; if that’s your situation, then one of the Client/Server databases (like mySQL) is the only way to go. Finally, it is not fast over the internet: it is meant to be used on the same machine as the script calling it.
Getting Started
NOTE: It’s really useful to refer to the SQLite language guide in a separate window (or tab) as you follow along. Most of the instructions are fairly straight-forward.
First, we create and then populate a database. Since SQLite instructions can be concatinated strings of statements separated by semicolons and spaces, we can build up a whole string of commands and then execute them in a single do shell script. Here’s an example:
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 tblName to "mods"
set newTbl to "create table " & tblName & "(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
-- a new db called TestDB.db should appear on your desktop!
Checking the db Table
Now to see what we just did, we can use one of several approaches. If the db is not too large (and ours is certainly not) then we can view it in a dialog:
-- First, the same path, head and tail we used before:
set loc to space & "~/desktop/TestDB.db" & space
set head to "sqlite3 -line" & loc & quote -- the "-line" option outputs the column data and heading one line at a time - useful for parsing the output for particular data items.
set tail to quote
set All to "select * from mods; " -- the "*" means get all rows. columns are separated by pipes ("|") in the result.
set modsTable to do shell script head & All & tail
set title to "So far, the table contents are: " & return & return
display dialog title & modsTable buttons {"Done"} default button 1 with icon 1
If your db is getting too long for a dialog box, you could have put it in the clipboard and pasted it into any text processor or to a new document in the script editor to check it:
set head to "sqlite3 -line ~/desktop/TestDB.db " & quote
set getAll to "select * from mods; "
set modsTable to do shell script head & getAll & quote
set title to "(*" & return & "Your 'mods' Table" & return & return
set the clipboard to title & modsTable & return & "*)"
tell application "Script Editor" to make new document with properties {contents:the clipboard}
Or, for aficionados of one-liners:
tell application "Script Editor" to make new document with properties {name:"Your 'mods' table", contents:"set db to \"" & return & (do shell script "sqlite3 -line ~/desktop/TestDB.db 'select * from mods; '") & "\""}
Querying Your Table
When we want some particular piece (or pieces of data back from our db, we construct a query like this:
-- Combining the path into the head:
set head to "sqlite3 -column ~/desktop/TestDB.db " & quote
-- "-column" displays results in table-like form using whitespace to separate the columns. In a monofont like courier, they will be lined up; not so in a variable width font.
-- 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"
Of course, if we had searched for country = ‘USA’, the list for this table would contain eight items: {“Ray”, “Barber”, “Bruce”, “Phillips”, “Kim”, “Hunter”, “Kevin”, “Bradley”} and looking for {firstname, lastname} would only get the first pair of them. If you suspect multiple answers are possible count them and extract them from their list in a repeat block.
Query Conditions Can Be Compounded with AND or OR:
set head to "sqlite3 -column ~/desktop/TestDB.db " & quote
-- example of a query with an "OR" condition (Spain or Canada):
set getNames to "select firstname, lastname from mods where country = 'Spain' or country = 'Canada'; "
set N to words of (do shell script head & getNames & quote)
set tNames to {}
repeat with k from 1 to (count N) - 1 by 2
tell N to set end of tNames to item (k + 1) & "," & space & item k
end repeat
tNames --> {"Sancho, jj", "Bell, Adam"}
Adding to, Editing, and Altering a DB Table
Now that we have created a db, populated it, looked at it, and queried it, the obvious question is “Can we make changes?” – Yes and no. Yes, we can easily add more data or change the date in our table(s), but no, we can’t alter the structure of a table by adding a column, for example. Note: Newer versions of SQLite3 (3.2+) have two commands for altering the layout of a table: "alter table table_name rename to new_table_name; ", and “alter table table_name add new_col”, but these are not available in the Tiger version (3.1.3). Bear in mind, however, that even with the latest versions, you cannot delete a column once it is created, you can only delete rows.
Adding to the Database
Adding new data is straight-forward. To add Nigel Garvey and Kai Edwards who both reside in the UK, for example:
set head to "sqlite3 -column ~/desktop/TestDB.db " & quote
set newData to "insert into mods values ('Nigel', 'Garvey', 'UK'); "
set moreData to "insert into mods values ('Kai', 'Edwards', 'UK'); "
-- and then to check the result in the same command, add this line:
set myResult to "select * from mods; " -- the check is optional, of course.
display dialog (do shell script head & newData & moreData & myResult & quote)
Removing a Row from a Table
If we want to remove a row of a table, we use the command “delete from” like this:
set head to "sqlite3 -column ~/desktop/TestDB.db " & quote
-- identify the row with a condition unique to it:
set trashME to "delete from mods where firstname = 'Adam'; " -- there's only one.
-- check the result - always optional
set myResult to "select * from mods; "
display dialog (do shell script head & trashME & myResult & quote)
A word of caution: the command “delete from mods;” by itself (with no identifying “where” clause) will delete all of the data in the table. Also, deleting a row or rows from a table does not reduce the size of the data base - the blank line is left in place. To compress the table after alterations, it is useful to include the line: "vacuum mods; ", which will compress the table, and we could have included that instruction immediately following the deletion. To delete the whole table, we would run the instruction: "drop table mods; "
Editing a Row of a Table
SQLite3 supports the “update” statement. This statement is used to change the value of the data in columns of selected rows of a table (Not the column headings - they are cast in stone when the table is created). Selection is by means of a where clause to single out those rows that fit. For example to change “UK” to “England” as the country for Nigel and Kai, we would do this:
set head to "sqlite3 -column ~/desktop/TestDB.db " & quote
set changes to "update mods set country = 'England' where country='UK'; "
-- note that all such matches are changed.
-- and then to check the result in the same command set, add this line:
set myResult to "select * from mods; " -- optional in a serious script, of course.
display dialog (do shell script head & changes & myResult & quote)
Adding a Second Table to a Database
Since the Tiger version of SQLite3 won’t let us add columns, one way to add data is to add a new table; but so they can be jointly searched, make sure all the new column titles are different from the first. The script below creates a new table in our TestDB.db file that includes the lastname from the first table under the column heading “surName” and a new column “aNum” (in this case a made-up number as text) The script also adds my name and data back to ‘mods’ (remember that we deleted it earlier), so it will now appear at the end of the mods table.
set head to "sqlite3 -line" & " ~/desktop/TestDB.db " & quote
-- get my name back on the original list (mods)
set MeAgain to "insert into mods values ('Adam', 'Bell', 'Canada'); "
-- Make the new table with column titles that are unique to it.
set newTable to "create table " & "mods2" & "(surName, aNum); "
-- populate the new list (see comment below)
set d1 to "insert into mods2 values ('Barber', '1'); "
set d2 to "insert into mods2 values ('Sancho', '23'); "
set d3 to "insert into mods2 values ('Bell', '45'); "
set d4 to "insert into mods2 values ('Phillips', '67'); "
set d5 to "insert into mods2 values ('Hunter', '89'); "
set d6 to "insert into mods2 values ('Bradley', '101'); "
set d7 to "insert into mods2 values ('Garvey', '102'); "
set d8 to "insert into mods2 values ('Edwards', '103'); "
-- collect stuff
set All to MeAgain & newTable & d1 & d2 & d3 & d4 & d6 & d7 & d8
-- do it
do shell script head & All & quote
(* Clearly, we could have extracted the last names as a list from mods and then constructed the entries for the new table (mods2) by adding the relevant data in a second list and iterating through the two to form the text for the inserts. Here, for the sake of simplicity, I've just added the data in a series of "insert" statements.*)
If you want to check the new db, use this one-liner:
display dialog (do shell script "sqlite3 -line ~/desktop/TestDB.db 'select * from mods2;'") -- easy enough?
To get a specific person’s number, try this:
set head to "sqlite3" & " ~/desktop/TestDB.db " & quote
set getNames to "select surName from mods2; "
set theWho to words of (do shell script head & getNames & quote)
set who to (choose from list theWho with prompt "Choose a Name to Get a Number") as text
set getIt to "select aNum from mods2 where surName ='" & who & "'; "
set gotIt to (do shell script head & getIt & quote)
display dialog who & " is number " & gotIt
Combining Data from Both Tables
Suppose we wanted to discover something with parts in two tables; for example we might want both first name and number for the person living in Spain. First name and country are in one table (mods), but number is in the other (mods2). We can search both if those tables have a data column in common but those columns have different column labels so we can equate one to the other meaningfully (saying where myCol = myCol doesn’t do it).
Our tables have one column’s data in common: mods:lastname holds the same data as mods2:surname - so that’s our connection. We construct our query as follows with a where clause that specifies both the common items and our specific targets (firstname, aNum):
set head to "sqlite3" & " ~/desktop/TestDB.db " & quote
-- note we include both tables in the "from"
-- and that we ask for a match between the data in common
-- and finally [b]AND[/b] on our condition for an individual name
set Q to "select firstName, aNum from mods, mods2 where lastname=surName and country='Spain'; "
set ans to (do shell script head & Q & quote) --> "jj|23"
display dialog word 1 of ans & " is number " & word 3 of ans
There’s much more can be done, of course, but lest this tutorial grow to groaning proportions, we’ll stop here for now, perhaps doing more in a later article. Happy database scripting.
