hd_keywords {*cintro}

Summary

The following two objects and eight methods comprise the essential elements of the SQLite interface:

Introduction

SQLite has more than 225 APIs. However, most of the APIs are optional and very specialized and can be ignored by beginners. The core API is small, simple, and easy to learn. This article summarizes the core API.

A separate document, [capi3ref | The SQLite C/C++ Interface], provides detailed specifications for all C/C++ APIs for SQLite. Once the reader understands the basic principles of operation for SQLite, [capi3ref | that document] should be used as a reference guide. This article is intended as introduction only and is neither a complete nor authoritative reference for the SQLite API.

Core Objects And Interfaces

The principal task of an SQL database engine is to evaluate SQL statements of SQL. To accomplish this, the developer needs two objects:

Strictly speaking, the [prepared statement] object is not required since the convenience wrapper interfaces, [sqlite3_exec] or [sqlite3_get_table], can be used and these convenience wrappers encapsulate and hide the [prepared statement] object. Nevertheless, an understanding of [prepared statements] is needed to make full use of SQLite.

The [database connection] and [prepared statement] objects are controlled by a small set of C/C++ interface routine listed below.

Note that the list of routines above is conceptual rather than actual. Many of these routines come in multiple versions. For example, the list above shows a single routine named [sqlite3_open()] when in fact there are three separate routines that accomplish the same thing in slightly different ways: [sqlite3_open()], [sqlite3_open16()] and [sqlite3_open_v2()]. The list mentions [sqlite3_column_int | sqlite3_column()] when in fact no such routine exists. The "sqlite3_column()" shown in the list is a placeholder for an entire family of routines that extra column data in various datatypes.

Here is a summary of what the core interfaces do:

Typical Usage Of Core Routines And Objects

An application will typically use [sqlite3_open()] to create a single [database connection] during initialization. Note that [sqlite3_open()] can be used to either open existing database files or to create and open new database files. While many applications use only a single [database connection], there is no reason why an application cannot call [sqlite3_open()] multiple times in order to open multiple [database connections] - either to the same database or to different databases. Sometimes a multi-threaded application will create separate [database connections] for each thread. Note that a single [database connection] can access two or more databases using the [ATTACH] SQL command, so it is not necessary to have a separate database connection for each database file.

Many applications destroy their [database connections] using calls to [sqlite3_close()] at shutdown. Or, for example, an application that uses SQLite as its [application file format] might open [database connections] in response to a File/Open menu action and then destroy the corresponding [database connection] in response to the File/Close menu.

To run an SQL statement, the application follows these steps:

  1. Create a [prepared statement] using [sqlite3_prepare()].
  2. Evaluate the [prepared statement] by calling [sqlite3_step()] one or more times.
  3. For queries, extract results by calling [sqlite3_column_int | sqlite3_column()] in between two calls to [sqlite3_step()].
  4. Destroy the [prepared statement] using [sqlite3_finalize()].

The foregoing is all one really needs to know in order to use SQLite effectively. All the rest is optimization and detail.

Convenience Wrappers Around Core Routines

The [sqlite3_exec()] interface is a convenience wrapper that carries out all four of the above steps with a single function call. A callback function passed into [sqlite3_exec()] is used to process each row of the result set. The [sqlite3_get_table()] is another convenience wrapper that does all four of the above steps. The [sqlite3_get_table()] interface differs from [sqlite3_exec()] in that it stores the results of queries in heap memory rather than invoking a callback.

It is important to realize that neither [sqlite3_exec()] nor [sqlite3_get_table()] do anything that cannot be accomplished using the core routines. In fact, these wrappers are implemented purely in terms of the core routines.

Binding Parameters and Reusing Prepared Statements

In prior discussion, it was assumed that each SQL statement is prepared once, evaluated, then destroyed. However, SQLite allows the same [prepared statement] to be evaluated multiple times. This is accomplished using the following routines:

After a [prepared statement] has been evaluated by one or more calls to [sqlite3_step()], it can be reset in order to be evaluated again by a call to [sqlite3_reset()]. Think of [sqlite3_reset()] as rewinding the [prepared statement] program back to the beginning. Using [sqlite3_reset()] on an existing [prepared statement] rather than creating a new [prepared statement] avoids unnecessary calls to [sqlite3_prepare()]. For many SQL statements, the time needed to run [sqlite3_prepare()] equals or exceeds the time needed by [sqlite3_step()]. So avoiding calls to [sqlite3_prepare()] can give a significant performance improvement.

It is not commonly useful to evaluate the exact same SQL statement more than once. More often, one wants to evaluate similar statements. For example, you might want to evaluate an INSERT statement multiple times with different values. Or you might want to evaluate the same query multiple times using a different key in the WHERE clause. To accommodate this, SQLite allows SQL statements to contain [parameter | parameters] which are "bound" to values prior to being evaluated. These values can later be changed and the same [prepared statement] can be evaluated a second time using the new values.

SQLite allows a [parameter] wherever a string literal, numeric constant, or NULL is allowed. (Parameters may not be used for column or table names.) A [parameter] takes one of the following forms:

In the examples above, NNN is an integer value and AAA is an identifier. A parameter initially has a value of NULL. Prior to calling [sqlite3_step()] for the first time or immediately after [sqlite3_reset()], the application can invoke the [sqlite3_bind_int | sqlite3_bind()] interfaces to attach values to the parameters. Each call to [sqlite3_bind_int | sqlite3_bind()] overrides prior bindings on the same parameter.

An application is allowed to prepare multiple SQL statements in advance and evaluate them as needed. There is no arbitrary limit to the number of outstanding [prepared statements]. Some applications call [sqlite3_prepare()] multiple times at start-up to create all of the [prepared statements] they will ever need. Other applications keep a cache of the most recently used [prepared statements] and then reuse [prepared statements] out of the cache when available. Another approach is to only reuse [prepared statements] when they are inside of a loop.

Configuring SQLite

The default configuration for SQLite works great for most applications. But sometimes developers want to tweak the setup to try to squeeze out a little more performance, or take advantage of some obscure feature.

The [sqlite3_config()] interface is used to make global, process-wide configuration changes for SQLite. The [sqlite3_config()] interface must be called before any [database connections] are created. The [sqlite3_config()] interface allows the programmer to do things like:

After process-wide configuration is complete and [database connections] have been created, individual database connections can be configured using calls to [sqlite3_limit()] and [sqlite3_db_config()].

Extending SQLite

SQLite includes interfaces that can be used to extend its functionality. Such routines include:

The [sqlite3_create_collation()] interface is used to create new [collating sequences] for sorting text. The [sqlite3_create_module()] interface is used to register new [virtual table] implementations. The [sqlite3_vfs_register()] interface creates new [VFSes].

The [sqlite3_create_function()] interface creates new SQL functions - either scalar or aggregate. The new function implementation typically makes use of the following additional interfaces:

All of the built-in SQL functions of SQLite are created using exactly these same interfaces. Refer to the SQLite source code, and in particular the [http://www.sqlite.org/src/doc/trunk/src/date.c | date.c] and [http://www.sqlite.org/src/doc/trunk/src/func.c | func.c] source files for examples.

Shared libraries or DLLs can be used as [loadable extensions] to SQLite.

Other Interfaces

This article only mentions the most important and most commonly used SQLite interfaces. The SQLite library includes many other APIs implementing useful features that are not described here. A [capi3ref_funclist | complete list of functions] that form the SQLite application programming interface is found at the [capi3ref | C/C++ Interface Specification]. Refer to that document for complete and authoritative information about all SQLite interfaces.