33.29 Database

Emacs can be compiled with built-in support for accessing SQLite databases. This section describes the facilities available for accessing SQLite databases from Lisp programs.

Function: sqlite-available-p

The function returns non-nil if built-in SQLite support is available in this Emacs session.

When SQLite support is available, the following functions can be used.

Function: sqlite-open &optional file

This function opens file as an SQLite database file. If file doesn’t exist, a new database will be created and stored in that file. If file is omitted or nil, a new in-memory database is created instead.

The return value is a database object that can be used as the argument to most of the subsequent functions described below.

Function: sqlitep object

This predicate returns non-nil if object is an SQLite database object. The database object returned by the sqlite-open function satisfies this predicate.

Function: sqlite-close db

Close the database db. It’s usually not necessary to call this function explicitly—the database will automatically be closed if Emacs shuts down or the database object is garbage collected.

Function: sqlite-execute db statement &optional values

Execute the SQL statement. For instance:

(sqlite-execute db "insert into foo values ('bar', 2)")

If the optional values parameter is present, it should be either a list or a vector of values to bind while executing the statement. For instance:

(sqlite-execute db "insert into foo values (?, ?)" '("bar" 2))

This has exactly the same effect as the previous example, but is more efficient and safer (because it doesn’t involve any string parsing or interpolation).

sqlite-execute usually returns the number of affected rows. For instance, an ‘insert’ statement will typically return ‘1’, whereas an ‘update’ statement may return zero or a higher number. However, when using SQL statements like ‘insert into … returning … and the like, the values specified by ‘returning … will be returned instead.

Strings in SQLite are, by default, stored as utf-8, and selecting a text column will decode the string using that charset. Selecting a blob column will return the raw data without any decoding (i.e., it will return a unibyte string containing the bytes as stored in the database). Inserting binary data into blob columns, however, requires some care, as sqlite-execute will, by default, interpret all strings as utf-8.

So if you have, for instance, GIF data in a unibyte string called gif, you have to mark it specially to let sqlite-execute know this:

(put-text-property 0 1 'coding-system 'binary gif)
(sqlite-execute db "insert into foo values (?, ?)" (list gif 2))
Function: sqlite-select db query &optional values return-type

Select some data from db and return them. For instance:

(sqlite-select db "select * from foo where key = 2")
  ⇒ (("bar" 2))

As with the sqlite-execute, you can optionally pass in a list or a vector of values that will be bound before executing the select:

(sqlite-select db "select * from foo where key = ?" [2])
  ⇒ (("bar" 2))

This is usually more efficient and safer than the method used by the previous example.

By default, this function returns a list of matching rows, where each row is a list of column values. If return-type is full, the names of the columns (as a list of strings) will be returned as the first element in the return value.

If return-type is set, this function will return a statement object instead. This object can be examined by using the sqlite-next, sqlite-columns and sqlite-more-p functions. If the result set is small, it’s often more convenient to just return the data directly, but if the result set is large (or if you won’t be using all the data from the set), using the set method will allocate a lot less memory, and is therefore more memory-efficient.

Function: sqlite-next statement

This function returns the next row in the result set statement, typically an object returned by sqlite-select.

(sqlite-next stmt)
    ⇒ ("bar" 2)
Function: sqlite-columns statement

This function returns the column names of the result set statement, typically an object returned by sqlite-select.

(sqlite-columns stmt)
    ⇒ ("name" "issue")
Function: sqlite-more-p statement

This predicate says whether there is more data to be fetched from the result set statement, typically an object returned by sqlite-select.

Function: sqlite-finalize statement

If statement is not going to be used any more, calling this function will free the resources used by statement. This is usually not necessary—when the statement object is garbage-collected, Emacs will automatically free its resources.

Function: sqlite-transaction db

Start a transaction in db. When in a transaction, other readers of the database won’t access the results until the transaction has been committed by sqlite-commit.

Function: sqlite-commit db

End a transaction in db and write the data out to its file.

Function: sqlite-rollback db

End a transaction in db and discard any changes that have been made by the transaction.

Macro: with-sqlite-transaction db body…

Like progn (see Sequencing), but executes body with a transaction held, and commits the transaction at the end if body completes normally. If body signals an error, or committing the transaction fails, the changes in db performed by body are rolled back. The macro returns the value of body if it completes normally and commit succeeds.

Function: sqlite-pragma db pragma

Execute pragma in db. A pragma is usually a command that affects the database overall, instead of any particular table. For instance, to make SQLite automatically garbage collect data that’s no longer needed, you can say:

(sqlite-pragma db "auto_vacuum = FULL")

This function returns non-nil on success and nil if the pragma failed. Many pragmas can only be issued when the database is brand new and empty.

Function: sqlite-load-extension db module

Load the named extension module into the database db. Extensions are usually shared-library files; on GNU and Unix systems, they have the .so file-name extension.

Function: sqlite-version

Return a string denoting the version of the SQLite library in use.

If you wish to list the contents of an SQLite file, you can use the sqlite-mode-open-file command. This will pop to a buffer using sqlite-mode, which allows you to examine (and alter) the contents of an SQLite database.