Small. Fast. Reliable.
Choose any three.
关于 网站地图 文档 下载 许可 新闻 支持

SQLite C/C++ 接口入门

本文是对SQLite的C/C++接口的综述。
This article provides an overview to the C/C++ interface to SQLite.

SQLite早期版本非常容易学习，因为它只有5个C/C++接口。但是随着SQLite功能的增加，许多新接口加入到了SQLite中，如今，已经有超过200个独立的API了。这能压倒一名新程序员了。不过好在大部分的C/C++接口都是非常专业的，几乎不需要关心。尽管有那么多的接口，但是核心API还是非常简单易用的。本文的目的就是提供理解SQLite工作原理的一切背景知识。
Early versions of SQLite were very easy to learn since they only supported 5 C/C++ interfaces. But as SQLite has grown in capability, new C/C++ interfaces have been added so that now there are over 200 distinct APIs. This can be overwhelming to a new programmer. Fortunately, most of the C/C++ interfaces in SQLite are very specialized and never need to be considered. Despite having so many entry points, the core API is still relatively simple and easy to code to. This article aims to provide all of the background information needed to easily understand how SQLite works.

另一篇文档SQLite C/C++ 接口提供了SQLite所有C/C++ API的详细说明。一旦读者理解了操作SQLite的基本原则，那么就可以使用前面那篇文档来作为参考指南了。本文旨在介绍入门，而不是全文详细的SQLite API参考。
A separate document, The SQLite C/C++ Interface, provides detailed specifications for all of the various C/C++ APIs for SQLite. Once the reader understands the basic principles of operation for SQLite, 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.

1.0 核心对象和接口
1.0 Core Objects And Interfaces

SQL数据库引擎的主要任务就是计算SQL表达式。为了达到这个目的，开发者需要了解两个对象：
The principal task of an SQL database engine is to evaluate statements of SQL. In order to accomplish this purpose, the developer needs to know about two objects:

严格的说，预编译表达式对象不需要的了解的，因为一个封装良好的接口，如sqlite3_exec 和 sqlite3_get_table将预编译表达式对象封装在了接口内。虽然如此，但是理解预编译表达式对完整使用SQLite来说是必须的。
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.

数据库连接对象和预编译表达式对象主要被下面这少数几个C/C++接口程序使用。
The database connection and prepared statement objects are controlled by a small set of C/C++ interface routine listed below.

这六个C/C++接口程序和上面的两个对象就是SQLite的核心函数了。对开发者来说，理解这些内容是使用SQLite的基础。
The six C/C++ interface routines and two objects listed above form the core functionality of SQLite. The developer who understands them will have a good foundation for using SQLite.

注意，上面这些函数只是个概念，而不是实际函数。大部分函数都有许多不同的版本。例如上面列出的sqlite3_open()函数，在实际使用中，会有三个不同的函数版本，通过不同的方式来实现相似的目的：sqlite3_open()、sqlite3_open16() 和 sqlite3_open_v2()。列表中提到的sqlite3_column()函数在实际中是不存在的，在列表中列出"sqlite3_column()" 是为了代替一系列用于提取不同类型的值的函数。
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() when in fact no such routine exists. The "sqlite3_column()" shown in the list is place holders for an entire family of routines to be used for extracting column data in various datatypes.

下面是核心接口的简介：
Here is a summary of what the core interfaces do:

sqlite3_open() 这个函数打开一个到SQLite数据库文件的连接并返回一个数据库连接对象。这通常是应用第一个调用的SQLite API，也是其他大多数SQLite API的先决条件。许多SQLite接口的第一个参数都是一个指向数据库连接对象的指针，这些函数通常可以认为是数据库连接对象的方法。这个方法就相当于数据库连接对象的构造函数。
This routine opens a connection to an SQLite database file and returns a database connection object. This is often the first SQLite API call that an application makes and is a prerequisite for most other SQLite APIs. Many SQLite interfaces require a pointer to the database connection object as their first parameter and can be thought of as methods on the database connection object. This routine is the constructor for the database connection object.
sqlite3_prepare() 这个函数是将SQL文本转换成预编译表达式对象并返回一个指向该对象的指针。这个函数需要一个指向sqlite3_open()函数创建的数据库连接对象的指针和一个包含了需要预编译的SQL表达式的文本字符串。这个API并不实际执行查询计算，仅仅是为后续的计算预编译SQL表达式。
This routine converts SQL text into a prepared statement object and returns a pointer to that object. This interface requires a database connection pointer created by a prior call to sqlite3_open() and a text string containing the SQL statement to be prepared. This API does not actually evaluate the SQL statement. It merely prepares the SQL statement for evaluation.

将每条SQL表达式看作为一个小程序，sqlite3_prepare()接口的目的就是将其编译成目标代码。预编译表达式对象就是目标代码，sqlite3_step()接口则负责执行目标代码以获得结果。
Think of each SQL statement as a small computer program. The purpose of sqlite3_prepare() is to compile that program into object code. The prepared statement is the object code. The sqlite3_step() interface then runs the object code to get a result.

注意，sqlite3_prepare()函数现在已经不建议在新应用中使用了，应当替换为一个新函数sqlite3_prepare_v2()。
Note that the use of sqlite3_prepare() is not recommended for new applications. A newer alternative routine sqlite3_prepare_v2() should be used instead.
sqlite3_step() 这个函数用来计算由sqlite3_prepare()函数预编译的prepared statement对象。获取下下一条条记录只需要再一次调用sqlite3_step()即可，直到表达式执行完毕。不需要返回结果的调用（比如：INSERT、UPDATE、DELETE表达式）则调用一次sqlite3_step()就可以完成。
This routine is used to evaluate a prepared statement that has been previously created by the sqlite3_prepare() interface. The statement is evaluated up to the point where the first row of results are available. To advance to the second row of results, invoke sqlite3_step() again. Continue invoking sqlite3_step() until the statement is complete. Statements that do not return results (ex: INSERT, UPDATE, or DELETE statements) run to completion on a single call to sqlite3_step().
sqlite3_column() 这个函数会从sqlite3_step()计算的预编译表达式的结果集的当前行中返回一列的值。每次调用sqlite3_step()都会返回一个新的数据行，这时可以多次调用这个函数来获取这一行的所有列的值。正如上面所说，SQLite API中的确没有 "sqlite3_column()"这样的函数，我们这里调用 "sqlite3_column()"是用来替代一组可以从结果集中返回各种类型数据值的函数。这一组函数中还包括可以返回结果大小的函数（如果是stirng或者BLOB类型）和返回结果集包含的列数的函数。
This routine returns a single column from the current row of a result set for a prepared statement that is being evaluated by sqlite3_step(). Each time sqlite3_step() stops with a new result set row, this routine can be called multiple times to find the values of all columns in that row. As noted above, there really is no such thing as a "sqlite3_column()" function in the SQLite API. Instead, what we here call "sqlite3_column()" is a place-holder for an entire family of functions that return a value from the result set in various data types. There are also routines in this family that return the size of the result (if it is a string or BLOB) and the number of columns in the result set.

sqlite3_finalize() 这个函数是用来销毁由sqlite3_prepare()创建的预编译表达式对象的。每个预编译表达式都需要调用这个函数来销毁以防止发生内存泄露。
This routine destroys a prepared statement created by a prior call to sqlite3_prepare(). Every prepared statement must be destroyed using a call to this routine in order to avoid memory leaks.
sqlite3_close() 这个函数是用来关闭由sqlite3_open()打开的数据库连接对象的。在关闭连接之前所有的预编译表达式都需要调用finalized函数销毁。
This routine closes a database connection previously opened by a call to sqlite3_open(). All prepared statements associated with the connection should be finalized prior to closing the connection.

1.1 核心函数和对象的典型使用
1.1 Typical Usage Of Core Routines And Objects

一个应用想要使用SQLite，通常需要在初始化阶段使用sqlite3_open()创建一个唯一的数据库连接对象。注意sqlite3_open()既可以用来打开一个已有的数据库文件，也可以用来创建一个新的数据库文件。尽管很多应用都只使用唯一的一个数据库连接，但是并不限制应用在一个或多个数据库上多次调用sqlite3_open()来创建多个数据库连接。有时多线程应用会为每个线程创建一个数据库连接。注意及时访问两个以上数据库也不必分别打开多个数据库连接，在一个数据库连接上通过使用ATTACHSQL命令就能一次访问两个以上的数据库。
An application that wants to use SQLite 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 threads. Note too that is not necessary to open separate database connections in order to access two or more databases. A single database connection can be made to access two or more databases at one time using the ATTACH SQL command.

许多应用会在关闭时调用sqlite3_close()关闭数据库连接。或者，一个应用会在响应菜单 “文件->打开”事件时打开一个数据库连接，然后在响应菜单“文件->关闭”事件时关闭这个数据库连接。Many applications destroy their database connections using calls to sqlite3_close() at shutdown. Or, for example, an application 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.

应用依照如下步骤运行一条SQL表达式：
To run an SQL statement, the application follows these steps:

  1. 使用sqlite3_prepare()创建一个预编译表达式。
    Create a prepared statement using sqlite3_prepare().
  2. 一次或多次使用sqlite3_step()来计算预编译表达式。
    Evaluate the prepared statement by calling sqlite3_step() one or more times.
  3. 在两次sqlite3_step()调用之间使用sqlite3_column()来查询或者获取结果。
    For queries, extract results by calling sqlite3_column() in between two calls to sqlite3_step().
  4. 使用sqlite3_finalize()销毁使用预编译表达式对象。
    Destroy the prepared statement using sqlite3_finalize().

前面讲的如何使用SQLite最基础的内容。下面部分内容主要是一些修饰函数和一些细节。
The foregoing is all one really needs to know in order to use SQLite effectively. All the rest is just ornamentation and detail.

2.0 核心函数的易用封装
2.0 Convenience Wrappers Around Core Routines

sqlite3_exec()函数是一个将上述四个步骤分装到一个函数中的一个易用分装。传入sqlite3_exec()一个回调函数用户处理数据集的每一行。sqlite3_get_table()函数是另一个将上述四个步骤分装到一个函数中的一个易用分装。sqlite3_get_table()函数与sqlite3_exec()函数不同的是将查询结果存在堆内存中，而不是执行回调函数。
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.

需要着重注意的是无法使用核心函数完成的事儿，也同样无法使用sqlite3_exec() 和 sqlite3_get_table()实现。事实上，这两个分装的实现仅仅使用了核心函数。
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.

3.0 绑定参数和重用预编译表达式
3.0 Binding Parameters and Reusing Prepared Statements

前面的讨论都假设了每个SQL表达式预编译一次并执行，然后就销毁了。然而，SQLite是允许一个预编译表达式被多次执行计算的。这通过下面的函数完成：
In prior discussion, it was assumed that each SQL statement is prepared once, evaluated, then destroyed. However, the SQLite allows the same prepared statement to be evaluated multiple times. This is accomplished using the following routines:

当一个预编译表达式被求值并多次调用sqlite3_step()之后，可是通过使用sqlite3_reset()函数重置，以便再一次求值。使用sqlite3_reset()处理一个已有的预编译表达式而不是创建一个新的预编译表达式可以避免不必要的sqlite3_prepare()调用。对于许多SQL表达式，执行sqlite3_prepare()的次数等于或超过sqlite3_step()的执行次数。所以避免调用sqlite3_prepare()可以有效的提升性能。
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(). Using sqlite3_reset() on an existing prepared statement rather than creating a new prepared statement avoids unnecessary calls to sqlite3_prepare(). In 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 result in a significant performance improvement.

不过通常多次执行一条完全相同的SQL表达式是没有意义的。更常见的情况是，执行多条相似的表达式。例如，你想要多次执行INSERT表达式来插入不同的值，为了能灵活的适应这种场景，SQLite允许SQL表达式包含参数，等到需要求值时才将参数赋予实际值。这个值是可以修改的，并且一个预编译表达式可以在下一次求值时使用不同的值。
Usually, though, it is not useful to evaluate exactly the 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 though with different values to insert. To accommodate this kind of flexibility, SQLite allows SQL statements to contain 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中，无论是否允许包含字符串的位置都可以使用下列形式的参数：
In SQLite, wherever it is valid to include a string literal, one can use a parameter in one of the following forms:

上面的例子中，NNN是一个整数值，AAA是一个标示符。参数的初始值是NULL。在第一次调用sqlite3_step()或刚执行完sqlite3_reset()时，应用可以调用一个sqlite3_bind()接口为参数指定值。在同一个参数上每次调用sqlite3_bind()都会覆盖之前绑定的值。
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 one of the sqlite3_bind() interfaces to attach values to the parameters. Each call to sqlite3_bind() overrides prior bindings on the same parameter.

应用可以预先编译好许多SQL表达式，等到需要的时候再执行，对于未执行的预编译表达式数量没有硬性规定。
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.

4.0 配置SQLite
4.0 Configuring SQLite

对于大多数应用来说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.

sqlite3_config()接口就用来在进程内的全局范围内修改SQLite的配置。 sqlite3_config()接口必须在创建数据库连接之前调用。sqlite3_config()接口允许程序员做类似下面这些事儿：
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:

当完成进程全局设置后，对于已经创建出来的个别数据库连接还可以使用sqlite3_limit() 和 sqlite3_db_config()来进行配置。
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().

5.0 SQLite扩展
5.0 Extending SQLite

SQLite提供了用户扩展功能函数的接口，包括如下这些函数：
SQLite includes interfaces that can be used to extend its functionality. Such routines include:

sqlite3_create_collation()接口用来创建负责文本排序的排序器。sqlite3_create_module()接口负责注册一个新的虚表实现。sqlite3_vfs_register()接口用户创建新的VFSes。
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.

sqlite3_create_function()接口可以创建一个新的SQL函数——包括标量和统计函数。实现一个新函数通常需要用到下面这些附加接口：
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:

SQLite中所有内置的SQL函数也都完全是用这些同样的接口实现的，可以参考SQLite的源码，特别是date.c 和 func.c这两个源文件。
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 date.c and func.c source files for examples.

SQLite可以通过可加载的扩展模块的方式来使用共享库或者DLL。
Shared libraries or DLLs can be used as loadable extensions to SQLite.

6.0 其他接口
6.0 Other Interfaces

本文只提及了SQLite中的基础接口。SQLite库中还包含了很多可以实现强大特性的API，这里并没有提及。在C/C++ 接口说明中包含了一份SQLite的 完整函数列表。想了解有关SQLite接口最完整权威的信息，请参考这篇文档。
This article only mentions the foundational SQLite interfaces. The SQLite library includes many other APIs implementing useful features that are not described here. A complete list of functions that form the SQLite application programming interface is found at the C/C++ Interface Specification. Refer to that document for complete and authoritative information about all SQLite interfaces.