SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. The code for SQLite is in the public domain and is thus free for use for any purpose, commercial or private. Its robust flexibility makes it a favorite database tool for businesses. And best of all, SQLite is a default component of OS X, ready to be used for your data-storage needs.
In addition to providing built-in support for SQLite, OS X also includes the Database Events application, an AppleScript interface for building and querying SQLite database files. Using the Database Events application and AppleScript you can easily transfer data between Numbers spreadsheets and SQLite databases!
The Database Events Dictionary
The scripting dictionary for Database Events is a simple but powerful scripting interface that includes all the elements necessary for creating and querying SQLite database files. It contains three classes: databases, records, and fields
database n [inh. item ]: A collection of records, residing at a location in the file system.
elements
contains records; contained by application.
properties
location ( alias , r/o ) : The folder that contains the database.
name ( text, r/o ) : The name of the database.
responds to
close, make, save.
field n [inh. item ]: A named piece of data, residing in a record.
elements
contained by records.
properties
id ( integer , r/o ) : The unique id of the field.
name ( text, r/o ) : The name of the field.
value ( any ) : The value of the field.
responds to
delete, exists, make.
record n [inh. item ]: A collection of fields, residing in a database.
elements
contains fields; contained by databases.
properties
id ( integer , r/o ) : The unique id of the record.
name ( text, r/o ) : The name of the record, equivalent to the value of the field named “name”.
responds to
delete, exists, make.
Using these classes you can use AppleScript statements to query SQLite database files, like this:
| 01 | tell application "Database Events" | |
| 02 | tell database "/Users/Johnny/Documents/Databases/U.S. Presidents.dbev" | |
| 03 | -- get the last name of every American president who served between 1800 and 1900 | |
| 04 | get the 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) | |
| 05 | --> {"Adams", "Arthur", "Buchanan", "Cleveland", "Cleveland", "Fillmore", "Garfield", "Grant", "Harrison", "Harrison", "Hayes", "Jackson", "Jefferson", "Johnson", "Lincoln", "Madison", "McKinley", "Monroe", "Pierce", "Polk", "Taylor", "Tyler", "Van Buren"} | |
| 06 | end tell | |
| 07 | end tell |
This section provides script examples for creating SQLite databases from Numbers tables, and for creating Numbers tables using the data in SQLite databases.