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.
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.
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.
This predicate returns non-nil if object is an SQLite
database object. The database object returned by the
sqlite-open function satisfies this predicate.
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.
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))
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.
This function returns the next row in the result set statement,
typically an object returned by sqlite-select.
(sqlite-next stmt)
⇒ ("bar" 2)
This function returns the column names of the result set
statement, typically an object returned by sqlite-select.
(sqlite-columns stmt)
⇒ ("name" "issue")
This predicate says whether there is more data to be fetched from the
result set statement, typically an object returned by
sqlite-select.
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.
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.
End a transaction in db and write the data out to its file.
End a transaction in db and discard any changes that have been made by the transaction.
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.
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.
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.
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.