# 第6章 核心C API
本章介紹用于數據庫操作的SQLite API。第5章已經介紹了API如何工作，本章關注細節。
本章從幾個例子開始，深入介紹C API。學完本章之后，你會看到每個C API函數都與常用的數據庫操作有關，包括執行命令、管理事務、取記錄、處理錯誤等等。
SQLite的版本3的API包括大約80個函數。只有8個函數在連接、查詢和斷開連接時是必須的，其它的函數用來完成特定的任務。
如第5章所述，版本3與2的API相比有較大改變。最值得關注的一個改變是增加了對UTF的支持。所有接受字符串做為參數或返回字符串的函數都同時具有UTF-8和UTF-16的相似體。例如，sqlite3_open()，接受一個UTF-8的數據庫名做參數；而sqlite3_open16()具有同樣的功能與格式，但參數使用UTF-16編碼。本章一般只介紹UTF-8的函數，UTF-16版本僅僅是在名字上有微小差別。
本章最好順序地讀，如果在細節上有問題，可以參考附錄B。
空注：第6、7兩章應該也是本書的精華了，主要介紹對SQLite進行編程的方法。大多數SQLite的使用者可能更關心這兩章，但我又不開發基于SQLite的應用程序，研究SQLite純粹出于興趣，個人更關心SQLite本身的實現方法，所以對這部分內容只是略做瀏覽。關心這部分內容的兄弟還是得自己看原文。
## 封裝的查詢
你已經熟悉了SQLite執行查詢的方法，包括在一個單獨的函數中執行封裝的SQL。我們從封裝的SQL開始介紹，因為這些函數簡單、獨立且易用。它們是好的起點，使你能得到樂趣，又不會被過多的細節所困擾。
## 連接和斷開連接
在執行SQL命令之前，首先要連接數據庫。因為SQLite數據庫存儲在一個單獨的操作系統文件當中，所以連接數據庫可以理解為“打開”數據庫。同樣，斷開連接也就是關閉數據庫。
打開數據庫用sqlite3_open()或sqlite3_open16()函數，它們的聲明如下：
```
int sqlite3_open(
??? const char *filename, ????????? /* Database filename (UTF-8) */
??? sqlite3 **ppDb ?????????????????? /* OUT: SQLite db handle */
);
int sqlite3_open16(
??? const void *filename,??????????? /* Database filename (UTF-16) */
??? sqlite3 **ppDb ?????????????????? /* OUT: SQLite db handle */
);
```
其中，filename參數可以是一個操作系統文件名，或字符串':memory:'，或一個空指針(NULL)。用后兩者將創建內存數據庫。如果filename不為空，先嘗試打開，如果文件不存在，則用這個名字創建一個新的數據庫。
關閉連接使用sqlite3_close()函數，它的聲明如下：
```
int sqlite3_close(sqlite3*);
```
為了sqlite3_close()能夠成功執行，所有與連接所關聯的已編譯的查詢必須被定案。如果仍然有查詢沒有定案，sqlite3_close()將返回SQLITE_BUSY和錯誤信息：Unable to close due to unfinalized statements。
## 執行Query
函數sqlite3_exec()提供了一種執行SQL命令的快速、簡單的方法，它特別適合處理對數據庫的修改操作(不需要返回數據)。sqlite3_exec()的聲明如下：
```
int sqlite3_exec(
??? sqlite3*, /* An open database */
??? const char *sql, /* SQL to be executed */
??? sqlite_callback, /* Callback function */
??? void *data /* 1st argument to callback function */
??? char **errmsg /* Error msg written here */
);
```
SQL命令由sql參數提供，它可以由多個SQL命令構成，sqlite3_exec()會對其中每個命令進行分析并執行，直到命令串結束或遇到一個錯誤。列表6-1(來自create.c)說明了sqlite3_exec()的用法：
列表6-1 對簡單的命令使用sqlite3_exec()
```
#include <stdio.h>
#include <stdlib.h>
#include "util.h"
#pragma comment(lib, "sqlite3.lib")
int main(int argc, char **argv)
{
??? sqlite3 *db;
??? char *zErr;
??? int rc;
??? char *sql;
??? rc = sqlite3_open("test.db", &db);
??? if (rc) {
??????? fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
??????? sqlite3_close(db);
??????? exit(1);
??? }
??? sql = "create table episodes( id integer primary key,"
??????? "??? ??????????????name text, cid int)";
??? rc = sqlite3_exec(db, sql, NULL, NULL, &zErr);
??? if (rc != SQLITE_OK) {
??????? if (zErr != NULL) {
??????????? fprintf(stderr, "SQL error: %s\n", zErr);
??????????? sqlite3_free(zErr);
??????? }
??? }
??? sql = "insert into episodes (name,id) values ('Cinnamon Babka2',1)";
??? rc = sqlite3_exec(db, sql, NULL, NULL, &zErr);
??? if (rc != SQLITE_OK) {
??????? if (zErr != NULL) {
??????????? fprintf(stderr, "SQL error: %s\n", zErr);
??????????? sqlite3_free(zErr);
??????? }
??? }
??? sqlite3_close(db);
??? return 0;???
}
```
### 處理記錄
如第5章所述，還是有可能從sqlite3_exec()取得記錄的。sqlite3_exec()包含一個回叫(callback)機制，提供了一種從SELECT語句得到結果的方法。這個機制由sqlite3_exec()函數的第3和第4個參數實現。第3個參數是一個指向回叫函數的指針，如果提供了回叫函數，SQLite則會在執行SELECT語句期間在遇到每一條記錄時調用回叫函數。回叫函數的聲明如下：
```
typedef int (*sqlite3_callback)(
??? void*, /* Data provided in the 4th argument of sqlite3_exec() */
??? int, /* The number of columns in row */
??? char**, /* An array of strings representing fields in the row */
??? char** /* An array of strings representing column names */
);
```
函數sqlite3_exec()的第4個參數是一個指向任何應用程序指定的數據的指針，這個數據是你準備提供給回叫函數使用的。SQLite將把這個數據作為回叫函數的第1個參數傳遞。
總之，sqlite3_exec()允許你處理一批命令，并且你可以使用回叫函數來收集所有返回的數據。例如，先向episodes表插入一條記錄，再從中查詢所有記錄，所有這些都在一個sqlite3_exec()調用中完成。完整的程序代碼見列表6-2，它來自exec.c。
列表6-2 將sqlite3_exec()用于記錄處理
```
#include <stdio.h>
#include <stdlib.h>
#include "util.h"
#pragma comment(lib, "sqlite3.lib")
int callback(void* data, int ncols, char** values, char** headers);
int main(int argc, char **argv)
{
??? sqlite3 *db;
??? int rc;
??? char *sql;
??? char *zErr;
?????? char* data;
??? rc = sqlite3_open("test.db", &db);
??? if(rc) {
??????? fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
??????? sqlite3_close(db);
??????? exit(1);
??? }
??? data = "Callback function called";
??? sql = "insert into episodes (name, cid) values ('Mackinaw Peaches', 1);"
????????? "select * from episodes;";
??? rc = sqlite3_exec(db, sql, callback, data, &zErr);
??? if(rc != SQLITE_OK) {
??????? if (zErr != NULL) {
??????????? fprintf(stderr, "SQL error: %s\n", zErr);
??????????? sqlite3_free(zErr);
??????? }
??? }
??? sqlite3_close(db);
??? return 0;???
}
int callback(void* data, int ncols, char** values, char** headers)
{
??? int i;
??? fprintf(stderr, "%s: ", (const char*)data);
??? for(i=0; i < ncols; i++) {
??????? fprintf(stderr, "%s=%s ", headers[i], values[i]);
??? }
??? fprintf(stderr, "\n");
??? return 0;
}
```
## 字符串處理
```
int main(int argc, char **argv)
{
??? char *sql;
??? char *trouble = "'Here's trouble'";
??? sql = sqlite3_mprintf("insert into x values('%q')", trouble);
??? fprintf(stdout, "%s\n", sql);
??? sqlite3_free(sql);
??? return 0;???
}
```
The result sql will contain
insert into x values('''Here''s trouble''')
Listing 6-3\. Using sqlite3_vmprintf()
```
int execute(sqlite3 *db, const char* sql, ...)
{
??? char *err, *tmp;
??? va_list ap;
??? va_start(ap, sql);
??? tmp = sqlite3_vmprintf(sql, ap);
??? va_end(ap);
??? int rc = sqlite3_exec(db, tmp, NULL, NULL, &err);
??? if(rc != SQLITE_OK) {
??????? if (err != NULL) {
??????????? fprintf(stdout, "execute() : Error %i : %s\n", rc, err);
??????????? sqlite3_free(err);
??????? }
??? }
??? sqlite3_free(tmp);
??? return rc;
}
```
## Get Table查詢
```
int sqlite3_get_table(
??? sqlite3*, /* An open database */
??? const char *sql, /* SQL to be executed */
??? char ***resultp, /* Result written to a char *[] that this points to */
??? int *nrow, /* Number of result rows written here */
??? int *ncolumn, /* Number of result columns written here */
??? char **errmsg /* Error msg written here */
);
```
Listing 6-4\. Using sqlite3_get_table
```
void main(int argc, char **argv)
{
??? sqlite3 *db;
??? char *zErr;
??? int rc,i;
??? char *sql;
??? char **result;
?????? int nrows, ncols;
??? /* Connect to database, etc. */
??? rc = sqlite3_open("test.db", &db);
??? sql = "select * from episodes;";
??? rc = sqlite3_get_table(db, sql, &result, &nrows, &ncols, &zErr);
??? /* Do something with data */
?????? printf("rows=%d,cols=%d\n",nrows,ncols);
?????? for (i=0;i<=nrows;i++)
????????????? printf("%-5s%-20s%-5s\n",result[3*i],result[3*i+1],result[3*i+2]);
??? /* Free memory */
??? sqlite3_free_table(result);
}
```
If, for example, the result set returned is of the form
rows=2,cols=3
```
id?? name??????????????? cid
1??? Cinnamon Babka2???? (null)
2??? Mackinaw Peaches??? 1
```
## 預處理的查詢
As you’ll recall from Chapter 5, prepared queries are performed in three basic steps: compilation, execution, and finalization. This process is illustrated in Figure 6-1\.
Figure 6-1\. Prepared query processing
Now that you’ve seen the whole process, let’s go through an example. A simple, complete program using a prepared query is listed in Listing 6-6\. It is taken from select.c in the examples.
Listing 6-6\. Using Prepared Queries
```
#include <string.h>
int main(int argc, char **argv)
{
??? int rc, i, ncols;
??? sqlite3 *db;
??? sqlite3_stmt *stmt;
??? char *sql;
??? const char *tail;
??? rc = sqlite3_open("test.db", &db);
??? if(rc) {
??????? fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
??????? sqlite3_close(db);
??????? exit(1);
??? }
??? sql = "select * from episodes;";
??? rc = sqlite3_prepare(db, sql, (int)strlen(sql), &stmt, &tail);
??? if(rc != SQLITE_OK) {
??????? fprintf(stderr, "SQL error: %s\n", sqlite3_errmsg(db));
??? }
??? rc = sqlite3_step(stmt);
??? ncols = sqlite3_column_count(stmt);
??? while(rc == SQLITE_ROW) {
? ??????for(i=0; i < ncols; i++) {
??????????? fprintf(stderr, "'%s' ", sqlite3_column_text(stmt, i));
??????? }
??????? fprintf(stderr, "\n");
??????? rc = sqlite3_step(stmt);
??? }
??? sqlite3_finalize(stmt);
??? sqlite3_close(db);
??? return 0;???
}
```
跟sqlite3_exec()相似，sqlite3_prepare()也可以接受一個包括多個SQL語句的字符串。不同的是sqlite3_prepare()只處理字符串中的第1個語句。But it does make it easy for you to process subsequent SQL statements in the string by providing the pzTailout parameter. After you call sqlite3_prepare(), it will point this parameter (if provided) to the starting position of the next statement in the zSQL string. Using pzTail, processing a batch of SQL commands in a given string can be executed in a loop as follows:
```
while(sqlite3_complete(sql) {
??? rc = sqlite3_prepare(db, sql, strlen(sql), &stmt, &tail);
??? /* Process query results */
??? /* Skip to next command in string. */
??? sql = tail;
}
```
## 取記錄
### 取字段信息
你可以使用sqlite3_column_name()來取得各字段的名稱：
```
??? const char *sqlite3_column_name( sqlite3_stmt*, /* statement handle */
??????? int iCol /* column ordinal */);
```
類似地，你可以使用sqlite3_column_type()取得各字段的存儲類：
```
??? int sqlite3_column_type( sqlite3_stmt*, /* statement handle */
??????? int iCol /* column ordinal */);
```
這個函數返回一個整數值，代表5個存儲類的代碼，定義如下：
```
#define SQLITE_INTEGER 1
#define SQLITE_FLOAT 2
#define SQLITE_TEXT 3
#define SQLITE_BLOB 4
#define SQLITE_NULL 5
```
這些是SQLite本身的類型，或稱存儲類，在第4章有詳細介紹。All data stored within a SQLite database is stored in one of these five forms, depending on its initial representation and the affinity of the column. For our purposes, the terms storage class and data type are synonymous. For more information on storage classes, see the sections “Storage Classes” and “Type Affinity” in Chapter 4\.
你可以使用sqlite3_column_decltype()函數獲得字段聲明的數據類型：
```
??? const char *sqlite3_column_decltype( sqlite3_stmt*, /* statement handle */
??????? int /* column ordinal */);
```
如果結果集中的一列不是來自一個實際的字段(如來自于表達式、函數或聚合的結果)，這個函數將返回NULL。For example, suppose you have a table in your database defined as
```
CREATE TABLE t1(c1 INTEGER);
```
Then you execute the following query:
```
SELECT c1 + 1, 0 FROM t1;
```
In this case, sqlite3_column_decltype() will return INTEGER for the first column and NULL for
the second.
還可以用下列函數獲得字段的其它信息：
```
const char *sqlite3_column_database_name(sqlite3_stmt *pStmt, int iCol);
const char *sqlite3_column_table_name(sqlite3_stmt *pStmt, int iCol);
const char *sqlite3_column_origin_name(sqlite3_stmt *pStmt, int iCol);
```
The first function will return the database associated with a column, the second its table, and
the last function returns the column’s actual name as defined in the schema. That is, if you
assigned the column an alias in the SQL statement, sqlite3_column_origin_name() will return
its actual name as defined in the schema. Note that these functions are only available if you
compile SQLite with the SQLITE_ENABLE_COLUMN_METADATA preprocessor directive.
列元數據：
字段的詳細信息可以從一個獨立的query獲得，使用sqlite3_table_column_metadata()函數，聲明如下：
```
SQLITE_API int sqlite3_table_column_metadata(
? sqlite3 *db,??????????????? /* Connection handle */
? const char *zDbName,??????? /* Database name or NULL */
? const char *zTableName,???? /* Table name */
? const char *zColumnName,??? /* Column name */
? char const **pzDataType,??? /* OUTPUT: Declared data type */
? char const **pzCollSeq,??? ?/* OUTPUT: Collation sequence name */
? int *pNotNull,????????????? /* OUTPUT: True if NOT NULL constraint exists */
? int *pPrimaryKey,?????????? /* OUTPUT: True if column part of PK */
? int *pAutoinc?????????????? /* OUTPUT: True if column is auto-increment */
);
```
這個函數包含輸入和輸出參數。它不在statement句柄下工作，但需要提供連接句柄、數據庫名、表名和列名。可選的數據庫名指明附加的邏輯數據庫名(一個連接上可能附加多個數據庫)。表名和字段名是必須的。
### 取字段值
可以使用sqlite3_column_xxx()函數取當前記錄中每個字段的值，其一般形式為：
```
xxx sqlite3_column_xxx( sqlite3_stmt*, /* statement handle */
??? int iCol /* column ordinal */);
```
xxx表示你希望得到的數據類型。sqlite3_column_xxx()包括以下函數：
```
int sqlite3_column_int(sqlite3_stmt*, int iCol);
double sqlite3_column_double(sqlite3_stmt*, int iCol);
long long int sqlite3_column_int64(sqlite3_stmt*, int iCol);
const void *sqlite3_column_blob(sqlite3_stmt*, int iCol);
const unsigned char *sqlite3_column_text(sqlite3_stmt*, int iCol);
const void *sqlite3_column_text16(sqlite3_stmt*, int iCol);
```
對每個函數，SQLite都會將字段值從存儲類轉化為函數指定的結果類型。Table 6-1中是轉換規則。
Table 6-1\. Column Type Conversion Rules
```
Internal Type???????? Requested Type???????????? Conversion
NULL?????????????????? INTEGER??????????????????? Result is 0\.
NULL?????????????????? FLOAT???????????????????????? Result is 0.0\.
NULL?????????????????? TEXT????????????????????????? Result is a NULL pointer.
```
### 一個實際的例子
To help solidify all of these column functions, Listing 6-7 (taken from columns.c) illustrates using the functions we’ve described to retrieve column information and values for a simple SELECT statement.
Listing 6-7\. Obtaining Column Information
```
#include <string.h>
int main(int argc, char **argv)
{
??? int rc, i, ncols, id, cid;
?????? char *name, *sql;
??? sqlite3 *db; sqlite3_stmt *stmt;
?????? sql = "select id,cid,name from episodes";
??? sqlite3_open("test.db", &db);
??? sqlite3_prepare(db, sql, strlen(sql), &stmt, NULL);
??? ncols = sqlite3_column_count(stmt);
??? rc = sqlite3_step(stmt);
??? /* Print column information */
??? for(i=0; i < ncols; i++) {
??????? fprintf(stdout, "Column: name=%s, storage class=%i, declared=%s\n",
???????????????????????? sqlite3_column_name(stmt, i),
???????????????????????? sqlite3_column_type(stmt, i),
???????????????????????? sqlite3_column_decltype(stmt, i));
??? }
??? fprintf(stdout, "\n");
??? while(rc == SQLITE_ROW) {
??????? id = sqlite3_column_int(stmt, 0);
??????? cid = sqlite3_column_int(stmt, 1);
??????? name = (char *)sqlite3_column_text(stmt, 2);
??????? if(name != NULL){
??????????? fprintf(stderr, "Row:? id=%i, cid=%i, name='%s'\n", id,cid,name);
??????? } else {
??????????? /* Field is NULL */
??????????? fprintf(stderr, "Row:? id=%i, cid=%i, name=NULL\n", id,cid);
??????? }
??????? rc = sqlite3_step(stmt);
??? }
??? sqlite3_finalize(stmt);
??? sqlite3_close(db);
??? return 0;
}
```
## 參數化的查詢
Figure 6-2\. SQL parameter binding
## 錯誤和意外
有些API是很可能出錯的，在編碼時總要記得3件事：錯誤、忙狀態和schema改變。
## 處理錯誤
很多API函數返回整數結果碼，這表示它們可以返回錯誤碼。在使用一個函數之前，應該仔細閱讀關于該函數的說明(見附錄B)，看它可能引發什么錯誤。API中定義了大約23種錯誤。所有的SQLite返回碼見表6-2。所有能夠返回這些碼的函數包括：
```
sqlite3_bind_xxx()
sqlite3_close()
sqlite3_create_collation()
sqlite3_collation_needed()
sqlite3_create_function()
sqlite3_prepare()
sqlite3_exec()
sqlite3_finalize()
sqlite3_get_table()
sqlite3_open()
sqlite3_reset()
sqlite3_step()
sqlite3_transfer_bindings()
```
可以使用函數sqlite3_errmsg()獲得附加的錯誤信息，其聲明如下：
```
const char *sqlite3_errmsg(sqlite3 *);
```
它以一個連接句柄作參數，返回該連接最近的一條錯誤信息。如果還沒有發生錯誤，它返回“not an error”。
表6-2 SQLit的返回碼
| 返回碼 | 說明 |
| --- | --- |
| SQLITE_OK | The operation was successful. |
| SQLITE_ERROR | General SQL error or missing database. It may be possible to obtain more error information depending on the error condition (SQLITE_SCHEMA, for example). |
| SQLITE_PERM | Access permission denied. Cannot read or write to the database file. |
| SQLITE_ABORT | A callback routine requested an abort. |
| SQLITE_BUSY | The database file is locked. |
| SQLITE_LOCKED | A table in the database is locked. |
| SQLITE_NOMEM | A call to malloc() has failed within a database operation. |
| SQLITE_READONLY | An attempt was made to write to a read-only database. |
| SQLITE_INTERRUPT | Operation was terminated by sqlite3_interrupt(). |
| SQLITE_IOERR | Some kind of disk I/O error occurred. |
| SQLITE_CORRUPT | The database disk image is malformed. This will also occur if an attempt is made to open a non-SQLite database file as a SQLite database. SQLITE_FULL Insertion failed because the database is full. There is no more space on the file system or the database file cannot be expanded. |
| SQLITE_CANTOPEN | SQLite was unable to open the database file. |
| SQLITE_PROTOCOL | The database is locked or there has been a protocol error. |
| SQLITE_EMPTY | (Internal only) The database table is empty. |
| SQLITE_SCHEMA | The database schema has changed. |
| SQLITE_CONSTRAINT | Abort due to constraint violation. This constant is returned if the SQL statement would have violated a database constraint (such as attempting to insert a value into a unique index that already exists in the index). |
| SQLITE_MISMATCH | Data type mismatch. An example of this is an attempt to insert non-integer data into a column labeled INTEGER PRIMARY KEY. For mostcolumns, SQLite ignores the data type and allows any kind of data to be stored. But an INTEGER PRIMARY KEY column is only allowed to store integer data. |
| SQLITE_MISUSE | Library was used incorrectly. This error might occur if one or more of the SQLite API routines is used incorrectly. Examples of incorrect usage include calling sqlite3_exec() after the database has been closed using sqlite3_close() or calling sqlite3_exec() with the same database pointer simultaneously from two separate threads. |
| SQLITE_NOLFS | Uses OS features not supported on host. This value is returned if the SQLite library was compiled with large file support (LFS) enabled butLFS isn’t supported on the host operating system. |
| SQLITE_AUTH | Authorization denied. This occurs when a callback function installed using sqlite3_set_authorizer() returns SQLITE_DENY. |
| SQLITE_ROW | sqlite3_step() has another row ready. |
| SQLITE_DONE | sqlite3_step() has finished executing. |
## 處理忙狀態
## 操作控制
API提供了幾個函數，可以用來監視或管理編譯期間和運行時的SQL命令。這些函數允許你建立回叫函數，并以此對不同的數據庫事件進行監視和控制(當事件發生時)。
## 提交Hook函數
使用sqlite3_commit_hook()函數可以在特定連接提交事務時對其進行監視。其聲明如下：
```
void *sqlite3_commit_hook( sqlite3 *cnx, /* database handle */
??? int(*xCallback)(void *data), /* callback function */
??? void *data); /* application data */
```
## 回卷Hook函數
回卷Hook函數與提交Hook函數相類似，但它在特定連接回卷事務時對其進行監視。
```
void *sqlite3_rollback_hook(sqlite3 *cnx, void(*xCallback)(void *data), void *data);
```
## 修改Hook函數
函數sqlite3_update_hook()用來監視特定數據庫連接所有的UPDATE、INSERT和DELETE操作，對這些操作中所涉及的每一行都進行監視，其聲明如下：
```
void *sqlite3_update_hook(
??? sqlite3 *cnx,
??? void(*)(void *, int, char const*, char const*, sqlite_int64),
??? void *data);
```
The first argument of the callback function is a pointer to application-specific data, which you provide in the third argument. The callback function has the following form:
```
void callback ( void * data,
??? int operation_code,
??? char const *db_name,
??? char const *table_name,
??? sqlite_int64 rowid),
```
## 授權函數
sqlite3_set_authorizer()是最強有力的事件過濾函數。用它可以在查詢編譯的時候對其進行監視和控制。其聲明如下：
```
int sqlite3_set_authorizer(
??? sqlite3*,
??? int (*xAuth)( void*,int,
??????? const char*, const char*,
??????? const char*,const char*),
??? void *pUserData
??? );
```
其中注冊了一個callback函數，作為授權函數。SQLite在一些數據庫事件的命令編譯階段將會調用它(不是在執行階段)。這個函數的用意是使用SQLite能夠安全地執行用戶提供的SQL(user-supplied SQL)。它提供了一種途徑將這類SQL限制在特定的操作上或拒絕對某些表或字段的存取。
Callback的聲明形式如下：
```
int auth( void*, /* user data */
??? int, /* event code */
??? const char*, /* event specific */
??? const char*, /* event specific */
??? const char*, /* database name */
??? const char* /* trigger or view name */ );
```
第1個參數是一個數據指針，它會傳遞給sqlite3_set_authorizer()函數的第4個參數。第2個參數是一個常量，可選值在表6-3中列出。這些常量值表示需要授權的是什么操作。第3、4個函數的含義決定于事件代碼(第2個參數，參表6-3)。
第5個參數是數據庫名。第6個參數是最內層觸發器或視圖的名稱，就是這個觸發器或視圖企圖存取數據庫。如果這個參數為NULL，則說明這種存取的企圖是直接由頂層的SQL引發的。
授權函數的返回值應該是SQLITE_OK、SQLITE_DENY或SQLITE_IGNORE之一。前兩個值的含義對所有事件都是確定的——接受或拒絕SQL。SQLITE_DENY將會取消整個SQL語句的執行并生成一個錯誤。
SQLITE_IGNORE的含義與事件有關。如果SQL語句是讀或改記錄，會在語句試圖操作的每個字段上產生SQLITE_READ或SQLITE_UPDATE事件。在這種情況下，如果回叫函數返回SQLITE_IGNORE，這些字段將從操作中被排除(高：別的字段繼續操作，這些字段就不操作了)。具體說，試圖讀的返回NULL，試圖寫的則什么也不做(silently fail)。
表6-3 SQLite的授權事件
| 事件代碼 | 參數3 | 參數4 |
| --- | --- | --- |
| SQLITE_CREATE_INDEX | Index name | Table name |
| SQLITE_CREATE_TABLE | Table name | NULL |
| SQLITE_CREATE_TEMP_INDEX | Index name | Table name |
| SQLITE_CREATE_TEMP_TABLE | Table name | NULL |
| SQLITE_CREATE_TEMP_TRIGGER | Trigger name | Table name |
| SQLITE_CREATE_TEMP_VIEW | View name | NULL |
| SQLITE_CREATE_TRIGGER | Trigger name | Table name |
| SQLITE_CREATE_VIEW | View name | NULL |
| SQLITE_DELETE | Table name | NULL |
| SQLITE_DROP_INDEX | Index name | Table name |
| SQLITE_DROP_TABLE | Table name | NULL |
| SQLITE_DROP_TEMP_INDEX | Index name | Table name |
| SQLITE_DROP_TEMP_TABLE | Table name | NULL |
| SQLITE_DROP_TEMP_TRIGGER | Trigger name | Table name |
| SQLITE_DROP_TEMP_VIEW | View name | NULL |
| SQLITE_DROP_TRIGGER | Trigger name | Table name |
| SQLITE_DROP_VIEW | View name | NULL |
| SQLITE_INSERT | Table name | NULL |
| SQLITE_PRAGMA | Pragma name | First argument or NULL |
| SQLITE_READ | Table name | Column name |
| SQLITE_SELECT | NULL | NULL |
| SQLITE_TRANSACTION | NULL | NULL |
| SQLITE_UPDATE | Table name | Column name |
| SQLITE_ATTACH | Filename | NULL |
| SQLITE_DETACH | Database name | NULL |
下面例子說明授權函數的使用(完整的程序在authorizer.c中)。
這是一個很長的例子，會用授權函數對很多不同的數據庫事件進行過濾，所以我們通過程序片段來進行說明。見列表6-10。
列表6-10 授權函數示例
授權函數的一般形式為：
```
int auth( void* x, int type,
????????? const char* a, const char* b,
????????? const char* c, const char* d )
{
??? const char* operation = a;
??? //printf( "??? %s ", event_description(type));
??? /* Filter for different database events
??? ** from SQLITE_TRANSACTION to SQLITE_INSERT,
??? ** UPDATE, DELETE, ATTACH, etc. and either allow or deny
??? ** them.
??? */
??? return SQLITE_OK;
}
```
授權函數做的第1件事是：看看事務狀態是否改變；如果改變，則輸出一個信息：
```
??? if((a != NULL) && (type == SQLITE_TRANSACTION)) {
??????? printf(": %s ê???", operation);
??? }
```
下一步是對引起schema改變的事件進行過濾：
```
??? switch(type) {
??????? case SQLITE_CREATE_INDEX:
??????? case SQLITE_CREATE_TABLE:
??????? case SQLITE_CREATE_TRIGGER:
??????? case SQLITE_CREATE_VIEW:
??????? case SQLITE_DROP_INDEX:
??????? case SQLITE_DROP_TABLE:
??????? case SQLITE_DROP_TRIGGER:
??????? case SQLITE_DROP_VIEW:
??????? {
??????????? printf(": Schema??±?á??￡");
??????? }
??? }
```
下一步是對讀的企圖進行檢查，這種企圖是基于字段的。這里，所有的讀都被允許，除了z字段。當要讀z字段時，函數返回SQLITE_IGNORE，這將導致SQLite在讀這個字段時返回NULL，從而有效地保護其數據。
```
??? if(type == SQLITE_READ) {
??????? printf(": Read of %s.%s ", a, b);
??????? /* Block attempts to read column z */
??????? if(strcmp(b,"z")==0) {
? ??????????printf("-> DENIED\n");
??????????? return SQLITE_IGNORE;
??????? }
??? }
```
下面是INSERT和UPDATE的過濾。所有的插入被允許。對x字段的修改被拒絕。這樣不會鎖住UPDATE的執行，而是簡單地過濾掉對x字段的修改企圖。
```
??? if(type == SQLITE_INSERT) {
??????? printf(": 2?è????? into %s ", a);
??? }
??? if(type == SQLITE_UPDATE) {
??????? printf(": Update of %s.%s ", a, b);
??????? /* Block updates of column x */
??????? if(strcmp(b,"x")==0) {
??????????? printf("-> DENIED\n");
??????????? return SQLITE_IGNORE;
??????? }
??? }
```
最后，對DELETE、ATTACH和DETACH進行過濾，在遇到這些事件時只是簡單地給出通知。
```
??? if(type == SQLITE_DELETE) {
??????? printf(": Delete from %s ", a);
??? }
??? if(type == SQLITE_ATTACH) {
??????? printf(": %s", a);
??? }
??? if(type == SQLITE_DETACH) {
??????? printf("-> %s", a);
??? }
```
下面是主程序，為了介紹的方便，也會分成多個片段。
```
int main(int argc, char **argv)
{
??? sqlite3 *db, *db2;
??? char *zErr;
??? int rc;
??? /* -------------------------------------------------------------------------
??? **? Setup?????????????????????????????????????????????????????????????????
??? ** -------------------------------------------------------------------------
??? */
??? /* Connect to test.db */
??? rc = sqlite3_open("test.db", &db);
??? if(rc) {
??????? fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
??????? sqlite3_close(db);
?????? ?exit(1);
??? }
??? /* -------------------------------------------------------------------------
??? **? Authorize and test
??? ** -------------------------------------------------------------------------
??? */
??? /* Register the authorizer function */
??? sqlite3_set_authorizer(db, auth, NULL);
??? /* Test transactions events */
??? printf("program : Starting transaction\n");
??? sqlite3_exec(db, "BEGIN", NULL, NULL, &zErr);
??? printf("program : Committing transaction\n");
??? sqlite3_exec(db, "COMMIT", NULL, NULL, &zErr);
??? printf("program : Starting transaction\n");
??? sqlite3_exec(db, "BEGIN", NULL, NULL, &zErr);
??? printf("program : Aborting transaction\n");
??? sqlite3_exec(db, "ROLLBACK", NULL, NULL, &zErr);
??? // Test table events
? ??printf("program : Creating table\n");
??? sqlite3_exec(db, "create table foo(x int, y int, z int)", NULL, NULL, &zErr);
??? printf("program : Inserting record\n");
??? sqlite3_exec(db, "insert into foo values (1,2,3)", NULL, NULL, &zErr);
??? printf("program : Selecting record (value for z should be NULL)\n");
??? print_sql_result(db, "select * from foo");
??? printf("program : Updating record (update of x should be denied)\n");
??? sqlite3_exec(db, "update foo set x=4, y=5, z=6", NULL, NULL, &zErr);
??? printf("program : Selecting record (notice x was not updated)\n");
??? print_sql_result(db, "select * from foo");
??? printf("program : Deleting record\n");
??? sqlite3_exec(db, "delete from foo", NULL, NULL, &zErr);
??? printf("program : Dropping table\n");
??? sqlite3_exec(db, "drop table foo", NULL, NULL, &zErr);
```
Several things are going on here. The program selects all records in the table, one of which is
column z. We should see in the output that column z’s value is NULL. All other fields should
contain data from the table. Next, the program attempts to update all fields, the most important
of which is column x. The update should succeed, but the value in column x should be
unchanged, as the authorizer denies it. This is confirmed on the following SELECT statement,
which shows that all columns were updated except for column x, which is unchanged. The
program then drops the foo table, which should issue a schema change notification from the
previous filter.
```
??? // Test ATTACH/DETACH
??? // Connect to test2.db
??? rc = sqlite3_open("test2.db", &db2);
??? if(rc) {
??????? fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db2));
??????? sqlite3_close(db2);
??????? exit(1);
???? }
??? // Drop table foo2 in test2 if exists
??? sqlite3_exec(db2, "drop table foo2", NULL, NULL, &zErr);
??? sqlite3_exec(db2, "create table foo2(x int, y int, z int)", NULL, NULL, &zErr);
??? // Attach database test2.db to test.db
??? printf("program : Attaching database test2.db\n");
??? sqlite3_exec(db, "attach 'test2.db' as test2", NULL, NULL, &zErr);
??? // Select record from test2.db foo2 in test.db
??? printf("program : Selecting record from attached database test2.db\n");
??? sqlite3_exec(db, "select * from foo2", NULL, NULL, &zErr);
??? printf("program : Detaching table\n");
??? sqlite3_exec(db, "detach test2", NULL, NULL, &zErr);
??? /* -------------------------------------------------------------------------
??? **? Cleanup
??? ** -------------------------------------------------------------------------
??? */
??? sqlite3_close(db);
??? sqlite3_close(db2);
??? return 0;???
}
```
## 線程
如第2章所述，SQLite支持線程。在多線程環境下使用SQLite時，有一些基本規則需要遵守。
## 共享緩沖區模式
Figure 6-3\. The shared cache model
共享緩沖區模式的目錄是為了節省內在，SQLite中有幾個函數是與線程和內存管理有關的。使用它們可以限制堆的尺寸或手工地發起內存清理。這些函數包括：
```
void sqlite3_soft_heap_limit(int N);
int sqlite3_release_memory(int N);
void sqlite3_thread_cleanup(void);
```
