以下は、 ２０１６年１２月現在の sqlite 文書、An Introduction To The SQLite C/C++ Interface 、SQLite C/C++ インタフェース入門、の、kanda.motohiro@gmail.com による抄訳です。以下の条件で、公開します。

The author or authors of this code dedicate any and all copyright interest in this code to the public domain. We make this dedication for the benefit of the public at large and to the detriment of our heirs and successors. We intend this dedication to be an overt act of relinquishment in perpetuity of all present and future rights to this code under copyright law.

1. まとめ

The following two オブジェクトs and eight メソッドs comprise the essential 要素 of the SQLite インタフェース:

• • sqlite3 → The データベース コネクション オブジェクト. Created by sqlite3_open() and destroyed by sqlite3_close().

  • sqlite3_stmt → The 被準備文 オブジェクト. Created by sqlite3_prepare() and destroyed by sqlite3_finalize().

  • sqlite3_open() → Open a コネクション to a new or existing SQLite データベース. The constructor for sqlite3.

  • sqlite3_prepare() → Compile SQL text into byte-code that will do the work of querying or updating the データベース. The constructor for sqlite3_stmt.

  • sqlite3_bind() → Store アプリケーション データ into parameters of the original SQL.

  • sqlite3_step() → Advance an sqlite3_stmt to the next result 行 or to completion.

  • sqlite3_column() → Column 値s in the current result 行 for an sqlite3_stmt.

  • sqlite3_finalize() → Destructor for sqlite3_stmt.

  • sqlite3_close() → Destructor for sqlite3.

  • sqlite3_exec() → A wrapper 関数 that does sqlite3_prepare(), sqlite3_step(), sqlite3_column(), and sqlite3_finalize() for a string of one or more SQL 文s.

2. Introduction

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

A separate document, The SQLite C/C++ インタフェース, provides detailed specifications for all 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.

3. コア オブジェクトs And インタフェース

The principal task of an SQL データベース engine is to evaluate SQL 文s of SQL. To accomplish this, the developer needs two オブジェクトs:

• • The データベース コネクション オブジェクト: sqlite3

  • The 被準備文 オブジェクト: sqlite3_stmt

Strictly speaking, the 被準備文 オブジェクト is not required since the convenience wrapper インタフェース, sqlite3_exec or sqlite3_get_table, can be used and these convenience wrappers encapsulate and hide the 被準備文 オブジェクト. Nevertheless, an understanding of 被準備文 is needed to make full use of SQLite.

The データベース コネクション and 被準備文 オブジェクトs are controlled by a small set of C/C++ インタフェース ルーチン listed below.

• • sqlite3_open()

  • sqlite3_prepare()

  • sqlite3_step()

  • sqlite3_column()

  • sqlite3_finalize()

  • sqlite3_close()

Note that the list of ルーチンs above is conceptual rather than actual. Many of these ルーチンs come in multiple versions. For example, the list above shows a single ルーチン named sqlite3_open() when in fact there are three separate ルーチンs 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 ルーチン exists. The "sqlite3_column()" shown in the list is place holders for an entire family of ルーチンs to be used for extracting column データ in various データtypes.

Here is a summary of what the コア インタフェース do:

• • sqlite3_open()

    • This ルーチン opens a コネクション to an SQLite データベース ファイル and returns a データベース コネクション オブジェクト. This is often the first SQLite API call that an アプリケーション makes and is a prerequisite for most other SQLite APIs. Many SQLite インタフェース require a pointer to the データベース コネクション オブジェクト as their first parameter and can be thought of as メソッドs on the データベース コネクション オブジェクト. This ルーチン is the constructor for the データベース コネクション オブジェクト.

  • sqlite3_prepare()

    • This ルーチン converts SQL text into a 被準備文 オブジェクト and returns a pointer to that オブジェクト. This インタフェース requires a データベース コネクション pointer created by a prior call to sqlite3_open() and a text string containing the SQL 文 to be prepared. This API does not actually evaluate the SQL 文. It merely prepares the SQL 文 for evaluation.

    • Think of each SQL 文 as a small computer プログラム. The purpose of sqlite3_prepare() is to compile that プログラム into オブジェクト code. The 被準備文 is the オブジェクト code. The sqlite3_step() インタフェース then runs the オブジェクト code to get a result.

    • New アプリケーションs should always invoke sqlite3_prepare_v2() instead of sqlite3_prepare(). The older sqlite3_prepare() is retained for backwards compatibility. But sqlite3_prepare_v2() provides a much better インタフェース.

  • sqlite3_step()

    • This ルーチン is used to evaluate a 被準備文 that has been previously created by the sqlite3_prepare() インタフェース. The 文 is evaluated up to the point where the first 行 of results are available. To advance to the second 行 of results, invoke sqlite3_step() again. Continue invoking sqlite3_step() until the 文 is complete. 文s that do not return results (ex: INSERT, UPDATE, or DELETE 文s) run to completion on a single call to sqlite3_step().

  • sqlite3_column()

    • This ルーチン returns a single column from the current 行 of a result set for a 被準備文 that is being evaluated by sqlite3_step(). Each time sqlite3_step() stops with a new result set 行, this ルーチン can be called multiple times to find the 値s of all columns in that 行.

    • As noted above, there really is no such thing as a "sqlite3_column()" 関数 in the SQLite API. Instead, what we here call "sqlite3_column()" is a place-holder for an entire family of 関数s that return a 値 from the result set in various データ types. There are also ルーチンs 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_column_blob()

      • sqlite3_column_bytes()

      • sqlite3_column_bytes16()

      • sqlite3_column_count()

      • sqlite3_column_double()

      • sqlite3_column_int()

      • sqlite3_column_int64()

      • sqlite3_column_text()

      • sqlite3_column_text16()

      • sqlite3_column_type()

      • sqlite3_column_値()

  • sqlite3_finalize()

    • This ルーチン destroys a 被準備文 created by a prior call to sqlite3_prepare(). Every 被準備文 must be destroyed using a call to this ルーチン in order to avoid memory leaks.

  • sqlite3_close()

    • This ルーチン closes a データベース コネクション previously opened by a call to sqlite3_open(). All 被準備文 associated with the コネクション should be finalized prior to closing the コネクション.

4. Typical Usage Of コア ルーチンs And オブジェクトs

An アプリケーション will typically use sqlite3_open() to create a single データベース コネクション during initialization. Note that sqlite3_open() can be used to either open existing データベース ファイルs or to create and open new データベース ファイルs. While many アプリケーションs use only a single データベース コネクション, there is no reason why an アプリケーション cannot call sqlite3_open() multiple times in order to open multiple データベース コネクションs - either to the same データベース or to different データベースs. Sometimes a multi-threaded アプリケーション will create separate データベース コネクションs for each threads. Note that a single データベース コネクション can access two or more データベースs using the ATTACH SQL command, so it is not necessary to have a separate データベース コネクション for each データベース ファイル.

Many アプリケーションs destroy their データベース コネクションs using calls to sqlite3_close() at shutdown. Or, for example, an アプリケーション that uses SQLite as its アプリケーション ファイル format might open データベース コネクションs in response to a ファイル/Open menu action and then destroy the corresponding データベース コネクション in response to the ファイル/Close menu.

To run an SQL 文, the アプリケーション follows these steps:

• 1. Create a 被準備文 using sqlite3_prepare().

  2. Evaluate the 被準備文 by calling sqlite3_step() one or more times.

  3. For queries, extract results by calling sqlite3_column() in between two calls to sqlite3_step().

  4. Destroy the 被準備文 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.

5. Convenience Wrappers Around コア ルーチンs

The sqlite3_exec() インタフェース is a convenience wrapper that carries out all four of the above steps with a single 関数 call. A callback 関数 passed into sqlite3_exec() is used to process each 行 of the result set. The sqlite3_get_table() is another convenience wrapper that does all four of the above steps. The sqlite3_get_table() インタフェース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 コア ルーチンs. In fact, these wrappers are implemented purely in terms of the コア ルーチンs.

6. Binding Parameters and Reusing 被準備文

In prior discussion, it was assumed that each SQL 文 is prepared once, evaluated, then destroyed. However, SQLite allows the same 被準備文 to be evaluated multiple times. This is accomplished using the following ルーチンs:

• • sqlite3_reset()

  • sqlite3_bind()

After a 被準備文 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 被準備文 プログラム back to the beginning. Using sqlite3_reset() on an existing 被準備文 rather than creating a new 被準備文 avoids unnecessary calls to sqlite3_prepare(). For many SQL 文s, 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 文 more than once. More often, one wants to evaluate similar 文s. For example, you might want to evaluate an INSERT 文 multiple times with different 値s. 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 文s to contain parameters which are "bound" to 値s prior to being evaluated. These 値s can later be changed and the same 被準備文 can be evaluated a second time using the new 値s.

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:

• • ?

  • ?NNN

  • :AAA

  • $AAA

  • @AAA

In the examples above, NNN is an integer 値 and AAA is an identifier. A parameter initially has a 値 of NULL. Prior to calling sqlite3_step() for the first time or immediately after sqlite3_reset(), the アプリケーション can invoke the sqlite3_bind() インタフェース to attach 値s to the parameters. Each call to sqlite3_bind() overrides prior bindings on the same parameter.

An アプリケーション is allowed to prepare multiple SQL 文s in advance and evaluate them as needed. There is no arbitrary limit to the number of outstanding 被準備文. Some アプリケーションs call sqlite3_prepare() multiple times at start-up to create all of the 被準備文 they will ever need. Other アプリケーションs keep a cache of the most recently used 被準備文 and then reuse 被準備文 out of the cache when available. Another approach is to only reuse 被準備文 when they are inside of a loop.

7. Configuring SQLite

The default 設定 for SQLite works great for most アプリケーションs. 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() インタフェース is used to make global, process-wide 設定 changes for SQLite. The sqlite3_config() インタフェース must be called before any データベース コネクションs are created. The sqlite3_config() インタフェース allows the プログラムmer to do things like:

• • Adjust how SQLite does memory allocation, including setting up alternative memory allocators appropriate for safety-critical real-time embedded systems and アプリケーション-defined memory allocators.

  • Set up a process-wide error log.

  • Specify an アプリケーション-defined page cache.

  • Adjust the use of mutexes so that they are appropriate for various threading models, or substitute an アプリケーション-defined mutex system.

After process-wide 設定 is complete and データベース コネクションs have been created, individual データベース コネクションs can be configured using calls to sqlite3_limit() and sqlite3_db_config().

8. Extending SQLite

SQLite includes インタフェース that can be used to extend its 関数ality. Such ルーチンs include:

• • sqlite3_create_collation()

  • sqlite3_create_function()

  • sqlite3_create_module()

  • sqlite3_vfs_register()

The sqlite3_create_collation() インタフェース is used to create new collating sequences for sorting text. The sqlite3_create_module() インタフェース is used to register new virtual table 実装s. The sqlite3_vfs_register() インタフェース creates new VFSes.

The sqlite3_create_関数() インタフェース creates new SQL 関数s - either scalar or aggregate. The new 関数 実装 typically makes use of the following additional インタフェース:

• • sqlite3_aggregate_context()

  • sqlite3_result()

  • sqlite3_user_データ()

  • sqlite3_値()

All of the built-in SQL 関数s of SQLite are created using exactly these same インタフェース. Refer to the SQLite source code, and in particular the date.c and func.c source ファイルs for examples.

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

9. Other インタフェース

This article only mentions the most important and most commonly used SQLite インタフェース. The SQLite library includes many other APIs implementing useful features that are not described here. A complete list of 関数s that form the SQLite アプリケーション プログラミング インタフェース is found at the C/C++ インタフェース Specification. Refer to that document for complete and authoritative information about all SQLite インタフェース.