SQLite Lib 1.0.0


SQLite Lib is a script library for dealing with SQLite3 databases, and offers a more efficient and faster alternative than using the command-line tool sqlite3 via do shell script. It requires OS X 10.10 or later.

You can download SQLite Lib 1.0.0 here.

Using the library

You install SQLite Lib by putting it in ~/Library/Script Libraries (you may have to create this folder). Like any other script library, it can also be embedded in script applets and bundles.

You include the library in a script by adding a use statement:

use sqlLib : script "SQLite Lib" version "1.0.0"

If your script uses any scripting addition commands, you will also need to include a use scripting additions statement.

Accessing a SQLite database involves two steps: making a new database object, then opening the returned object. You make a new database like this:

set theDb to sqlLib's makeNewDbWith:fileOrPath

The value you pass in fileOrPath can be an alias, a file reference, an NSURL, a POSIX path (including those beginning with ~), or an HFS path.

Once you have the database object, you address further calls to it. There are three open handlers: openReadOnly(), openReadWrite() and openReadWriteCreate(). The latter will create the database if it does not exist. So:

theDb's openReadWriteCreate()

When you have finished with the database, you need to close it:

theDb's |close|()

Note the pipes to avoid a conflict with AppleScript's open command.

The SQL commands you use are of two kinds: queries, using the SQL select statement, and updates, which are any other statements. In both cases, you have several options, including batch updates.

Updates

The simplest update handler is doUpdate:. So, for example:

theDb's doUpdate:"create table test (a, b, c, d)"
theDb's doUpdate:"insert into test values (1, 2, 3, 4)"

You can also use SQL ? placeholders, like this:

theDb's doUpdate:"insert into test values (?, ?, ?, ?)" arguments:{1, 2, 3, 4}

In this case the result is the same as the previous example. But using arguments is often more flexible, and also lets you pass values as objects — integers, reals, dates and data — rather than having to convert everything to a string.

You can also use a record to update, where the record's labels match the column names:

theDb's doUpdate:"insert into test values (:a, :b, :c, :d)" parameterRecord:{a:1, b:2, c:3, d:4}

Queries

The simplest query handler is doQuery:, and all query handlers return a result. For example:

set theResult to theDb's doQuery:"select * from test")

The result of such a query is a list of lists. Each sublist represents the values for one row of the result.

As with updates, you also use ? placeholders in queries:

set theResult to theDb's doQuery:"select a, b from test where a = ?" arguments:{"hi"}

SQL databases generally store dates as floating-point values. SQLite Lib can automatically convert such values to dates for you. So:

set theResult to theDb's doQuery:"select * from test" arguments:{} dateColumns:{0, "d"}

The dateColumns parameter contains the indexes and/or names of columns to be treated as dates. Just remember that indexes are zero-based: the first column is 0, and so on.

If you prefer, you can also get the result as a list of records. In this case, each record will represent the values for one row of the result, with the column names used as labels/keys. You do this by adding an extra parameter, rowsAs:, to one of the three doQuery: handlers, like this:

set theResult to theDb's doQuery:"select * from test" rowsAs:"records"

set theResult to theDb's doQueryD:"select a, b from test where a = ?" arguments:{"hi"} rowsAs:"records"

set theResult to theDb's doQueryD:"select * from test" arguments:{} dateColumns:{0, "d"} rowsAs:"records"

The rowsAs: parameter will treat any string that begins with "r" (ignoring case) as signifying records.

If you are using AppleScriptObjC, you can also use a string beginning with "a" to have the values returned as Cocoa arrays or "d" to have the values returned as Cocoa dictionaries. In the case of "a" or "d", the arrays or dictionaries will be returned within a mutable array rather than an AppleScript list. This can be significantly faster when there are a lot of results.

If the parameter does not begin with "d", "a" or "r", the result will be returned as a list of lists.

Batch updates

SQLite Lib offers several handlers for performing multiple updates with a single command, the simplest of which is batchUpdate:arguments:. In this case arguments is a list of lists, where each sublist represents the values to be used to be replace ? placeholders. So this code:

theDb's batchUpdate:"insert into test values (?, ?, ?, ?)" arguments:{{1, 2, 3, 4}, {5, 6, 7, 8}}

produces the same result as using:

theDb's doUpdate:"insert into test values (?, ?, ?, ?)" arguments:{1, 2, 3, 4}
theDb's doUpdate:"insert into test values (?, ?, ?, ?)" arguments:{5, 6, 7, 8}

The difference is that there is less overhead with the batch version, which also performs the updates as a single transaction. Also, if an error is thrown during a batch update, the whole transaction is rolled back.

You can also perform a batch update using a list of records or dictionaries. So this code:

theDb's batchUpdate:"insert into test values (:a, :b, :c, :d)" parameterRecords:{{a:1, b:2, c:3, d:4}, {a:5, b:6, c:7, d:8}}

produces the same result as using:

theDb's doUpdate:"insert into test values (:a, :b, :c, :d)" parameterRecord:{a:1, b:2, c:3, d:4}
theDb's doUpdate:"insert into test values (:a, :b, :c, :d)" parameterRecord:{a:5, b:6, c:7, d:8}

Again, if an error is thrown during a batch update, the whole transaction is rolled back.

Batch updates can also be done from CSV data:

theDb's batchUpdate:"insert into test values (?, ?, ?, ?)" withCSV:csvString commaIs:","

This assumes csvString is a string of CSV text, and uses the comma as the separator. You can specify any other (single-character) separator; an empty string or missing value is assumed to represent a comma. Any leading and trailing linebreaks are trimmed first.

If the CSV data is in a file, you can use it directly:

theDb's batchUpdate:"insert into test values (?, ?, ?, ?)" withCSVFile:csvFile commaIs:","

In this case csvFile can be an alias, file reference, NSURL, HFS path or POSIX path of a CSV file. The file must be UTF-8 or compatible format, and leading and trailing linebreaks will be trimmed.

Batch updates can also be done from other delimited data, such as tab-delimited data:

theDb's batchUpdate:"insert into test values (?, ?, ?, ?)" withString:delimitedString delimiterIs:tab

This assumes delimitedString is a string with each paragraph representing a row, and columns are formed by splitting each paragraph on the delimiter. The delimiter string is not restricted in length.

If the delimited data is in a file, you can use it directly:

theDb's batchUpdate:"insert into test values (?, ?, ?, ?)" withFile:delimitedFile delimiterIs:","

In this case delimitedFile can be an alias, file reference, NSURL, HFS path or POSIX path of a CSV file. The file must be UTF-8 or compatible format, and leading and trailing linebreaks will be trimmed.

Compound statements

You can execute compound SQL statements — a string containing multiple update statements, separated by semicolons — using the executeStatements: handler:

theDb's executeStatements:"insert into test values (1, 2, 3, 4); insert into test values (5, 6, 7, 8)"

Transactions

SQLite Lib supports the execution of updates in transactions. This can increase performance, and in the case of errors allows a complete transaction to be rolled back. To begin a transaction you call beginTransaction(), to commit or end a transaction you call commit(), and to roll back a transaction you call rollback(). So for example:

theDb's beginTransaction()
theDb's doUpdate:"insert into test values (?, ?, ?, ?)" arguments:{1, 2, 3, 4}
theDb's doUpdate:"insert into test values (?, ?, ?, ?)" arguments:{5, 6, 7, 8}
theDb's commit()

And:

theDb's beginTransaction()
theDb's doUpdate:"insert into test values (?, ?, ?, ?)" arguments:{1, 2, 3, 4}
try
theDb's doUpdate:"insert into test values (?, ?, ?, ?)" arguments:{5, 6, 7, 8}
on error errMessage
theDb's rollback()
theDb's |close|()
display dialog "Error: " & errorMessage buttons {"OK"} default button 1
error number -128
end try
theDb's commit()

Note that SQLite Lib's batch commands are always run as a single transaction. This means they cannot be called within a transaction you have initiated with beginTransaction().

In-memory databases

Using makeNewDbWith:, you can also create a temporary database that is deleted when it is closed. You do this by passing an empty string as the parameter. You can create a temporary database that is stored entirely in memory by passing missing value.

You can also clone an existing database into an in-memory database. You do this by calling the library's makeNewDbInMemoryAndOpenedFrom: handler, like this:

set theDb to sqlLib's makeNewDbInMemoryAndOpenedFrom:fileOrPath

This copies the on-disk database into a new in-memory database and opens it.

You can write the contents of an in-memory database to a file using the writeOpenInMemoryDbToFile: handler:

theDb's writeOpenInMemoryDbToFile:fileOrPath

With this, the in-memory database is saved to the file, the in-memory version closed, and the variable now refers to the on-disk database, which is also open (with read and write access). You need to close the on-disk database yourself.

Advanced use

SQLite Lib is an AppleScript wrapper around an Objective-C framework called FMDBAS, which in turn is a wrapper around the open-source FMDB framework. The FMDBAS framework makes the FMDB framework more AppleScript friendly, and the library gives it all a simple AppleScript interface.

The underlying frameworks have their own methods, which you can see in their enclosed header files. You can, if you wish, use the frameworks directly to access other methods, or use them as the basis of your own script library.

To use these methods while also using the library, you can use the underlyingFMDatabase() method, which returns the instance of the FMDatabase class being used. You will need to be using AppleScriptObjC in such cases. So if you wanted to call FMDatabase's -setDateFormat: method, you could do something like this:

theDb's underlyingFMDatabase()'s setDateFormat:someDateFormatter

Caveats

If you run a script that returns an unclosed database, you will get an error in your script editor. You may also be unable to save the script until you recompile it. Always close open databases, and use try blocks to be safe.

Like all libraries, when an error is thrown, script editors cannot usually pinpoint where in the calling script the error happened. If you are using Script Debugger, you can use stepping to narrow it down.

Most errors have the same error number (-10000). This is an AppleScript limitation.

With very large results, even fast queries can take a long time to return a result — in such cases it is the conversion of the data to large numbers of AppleScript lists or records that is time consuming. If you are using AppleScriptObjC, you should consider using the rowsAs: parameter to return arrays or dictionaries instead of lists or records.

You can embed this library in script bundles, but when developing and testing scripts it is better to use the ~/Libraries/Script Libraries folder. You can always add it to deployment versions later.

Feedback and updates

I welcome feedback. If you find this software useful, shareware donations are welcome.

When you update to a newer version, you should relaunch any applications that have used a previous version since they were launched. (This basically applies to script editors, script launchers like FastScripts, and applications with their own script menus or panels.)

Acknowledgements

Special thanks to the author of the included FMDB framework, Gus Mueller, and other contributors to that project. And thanks to those who tested the library.

Licence

SQLite Lib is Copyright © 2017 Shane Stanley The included FMDB Framework is Copyright © 2008-2017 Flying Meat Inc. See enclosed LICENSE.txt file.

Permission is hereby granted, free of charge, to any person obtaining a copy of this software to use, copy, and distribute copies, including within commercial software, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.