Inside SQLite Section 1.1 应用实例

整个文档翻译完成，见  http://download.csdn.net/detail/popvip44/9654234 

1.1应用实例

1.1.1一个简单的SQLite应用

#include <stdio.h>

#include "sqlite3.h"

int main(void)

{
sqlite3* db = 0;
sqlite3_stmt* stmt = 0;
int retcode;
retcode = sqlite3_open("MyDB", &db); /* Open a database named MyDB */
if (retcode != SQLITE_OK){
sqlite3_close(db);
fprintf(stderr, "Could not open MyDB\n");
return retcode;
}
retcode = sqlite3_prepare(db, "select SID from Students order by SID",-1, &stmt, 0);
if (retcode != SQLITE_OK){
sqlite3_close(db);
fprintf(stderr, "Could not execute SELECT\n");
return retcode;
}
while (sqlite3_step(stmt) == SQLITE_ROW){
int i = sqlite3_column_int(stmt, 0);
printf("SID = %d\n", i);
}
sqlite3_finalize(stmt);
sqlite3_close(db);
return SQLITE_OK;
}

你可以编译并执行上面的例子。在本书中，例程的输出是由Linux系统产生的，但是这些例程也可以运行在其他平台上。

这些例程假定你已经准备好了sqlite3可执行程序、libsqlite3.so(windows平台的sqlite3.dll或者Mac OS X平台的libsqlite3.dylib)和sqlite3.h。你可以从http://www.sqlite.org获取这些源代码或者二进制可执行文件。把sqlite3、库文件、sqlite3.h这三个文件放在同一个目录下会让工作变得简单很多。

例如，在Linux环境下，将app1.c、libsqlite3.so、sqlite3、sqlite3.h放在同一个目录下，用以下命令编译可以得到二进制可执行文件：

gcc app1.c -o ./app1 -lsqlite3 –L

 

NOTE

SQLite的源代码和应用程序的源代码必须用同一个编译器编译。如果你已经把SQLite作为一个包安装到电脑上，编译的命令有所不同。例如，在ubuntu上可以使用sudo aptitude install sqlite3 libsqlite3-dev来安装SQLite，安装完成后使用cc app1.c -o ./app1 -lsqlite3编译文件。

因为SQLite已经支持最近的Mac OS X版本，所以上面的命令在Mac OS X上也能运行。

 

上例打开了当前目录下的数据库文件MyDB。一个数据库至少需要一个table，即student；这个table必须至少有一个整数列即SID。在下一个例子中，你将学习到如何创建一个新的table，并在其中插入一行数据，通过下面的命令，你可以创建并更新表格：

sqlite3 MyDB "create table students (SID integer)"

sqlite3 MyDB "insert into students values (200)"

sqlite3 MyDB "insert into students values (100)"

sqlite3 MyDB "insert into students values (300)"

运行app1（Linux下可能需要配置环境变量），将有如下的输出：

SID = 100

SID = 200

SID = 300

 

NOTE

在Linux、Unix、Mac OS X系统下，可能需要在命令行编辑器中加入前缀./:

./app1

 

该例程将执行SQL语言select SID from Students order by SID。依次查找SID的值并打印出来，最后关闭数据库。

SQLite是一种嵌入应用程序中的调用级别的接口库(call-level interface library)。这个库中所有的SQLite API前缀都是sqlite3_，它们的声明在sqlite3.h中。在上例中我们用到了一些SQLite API例如sqlite3_open，sqlite3_prepare，sqlite3_step，sqlite3_column_int，sqlite3_finalize和sqlite3_close。上例中也用了一些助记符用来作为API的返回值，例如SQLITE_OK，SQLITE_ROW。所以助记符的定义也在sqlite3.h中。

下面的几小节将介绍一些关键的API。

1.1.1.1sqlite3_open

通过运行这个函数，应用程序通过SQLite库建立一个与数据库文件的连接。（该应用程序对于这个数据库文件或者其他的数据库文件可能还有另外的连接。SQLite区别对待每个连接，每个连接都是独立的。）如果数据库文件不存在，SQLite自动创建一个数据库文件。

NOTE

当打开或者创建一个文件时，SQLite遵循懒惰原则：实际上文件的打开或者创建被推迟，直到SQLite要进入文件并读取数据。

SQLite_open返回一个指向sqlite3类型实体的句柄。这个句柄完全代表了这个连接，通过这个句柄可以对数据库连接进行操作。

1.1.1.2sqlite3_prepare

Sqlite3_prepare编译一条SQL语言，并产生一个等价内部字节码。SQLite内部的虚拟机负责读取并运行这个字节码。

Sqlite3_prepare返回一个语句句柄(Sqlite3_stmt)，通过这个句柄可以操作字节码。在上面的例子中，将select SID from Students order by SID编译成了字节码，并返回了句柄stmt。这个句柄的作用就像一个游标，可以获取table中每一行的信息。

1.1.1.3sqlite3_step

Sqlite3_step执行字节码，直到遇到断点（找到新的行）或者停止（没有更多的行），并分别返回SQLITE_ROW和SQLITE_DONE。对于不返回行的SQL语句（例如UPDATE, INSERT, DELETE, CREATE）,该函数返回SQLITE_DONE。初始化时，游标指向要输出的行的集合的第一行之前。每执行一次Sqlite3_step游标就向下移动一行。游标只能向前移动，不能后退。

1.1.1.4sqlite3_column_int

如果sqlite3_step返回SQLITE_ROW，通过API函数sqlite3_conlumn_*就能得到指定的数据。如果SQL语句请求读取的数据类型和数据库中的存储类型不一样，sqlite3_conlumn_*会把数据类型转换为SQL语句请求的数据类型。在上面的例子中，请求的数据是整型，使用sqlite3_conlumn_int函数后得到整型的数据。

1.1.1.5sqlite3_finalize

Sqlite3_finalize销毁预处理的SQL语句。即，擦除字节码程序，释放分配给SQL语句句柄的所有资源，该句柄变为无效。

1.1.1.6 sqlite3_close

Sqlite3_close关闭数据库连接，释放分配给该连接的所有资源。该连接句柄变为无效。

 

1.1.1.7 其他有用的函数

其他有用的函数是sqlite3_bind_*和sqlite3_reset。在输入的SQL语句中，一个或者多个参数可以被替换成 "?"。替换之后的结果可以作为sqlite3_prepare的输入。可以通过sqlite3_bind_*来设置这些参数的值。如果一个参数没有绑定任何值，那么要么将其设定为默认值，要么将其设置为SQL NULL（当数据库的schema没有生命默认值时）。sqlite3_reset重置SQL语句句柄为其初始状态，但绑定了值的参数并不会被重置，重置之后的句柄可以重复使用，sqlite3_bind_* 函数也可以改变参数绑定的值。

1.1.1.8 返回值

API返回0或者正整数，这些返回值以助记符的形式给出。SQLITE_OK表示执行成功，SQLITE_ROW表示sqlite3_step函数找到了一个新行，SQLITE_DONE表示SQL语句执行完毕。

1.1.2 SQLite命令行例程

运行下面的例子后，就可以直接在命令行下操作数据库了。该程序使用两个参数作为输入，第一个参数是数据库文件名字，第二个参数是SQL语句。程序的执行过程为：打开数据库文件，使用sqlite3_exec执行SQL语句，关闭数据库。sqlite3_exec中包含了sqlite3_prepare和sqlite3_step。

#include <stdio.h>
#include "sqlite3.h"
static int callback(void *NotUsed, int argc, char **argv, char **colName)
{
// Loop over each column in the current row
int i;
for (i = 0; i < argc; i++){
printf("%s = %s\n", colName[i], argv[i] ? argv[i] : "NULL");
}
return 0;
}

int main(int argc, char **argv)
{
sqlite3* db = 0;
char* zErrMsg = 0;
int rc;
if (argc != 3){
fprintf(stderr, "Usage: %s DATABASE-FILE-NAME SQL-STATEMENT\n", argv[0]);
return -1;
}

rc = sqlite3_open(argv[1], &db);
if (rc != SQLITE_OK){
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
sqlite3_close(db);
return -2;
}

rc = sqlite3_exec(db, argv[2], callback, 0, &zErrMsg);
if (rc != SQLITE_OK){
fprintf(stderr, "SQL error: %s\n", zErrMsg);
}

sqlite3_close(db);
return rc;

}

将上面的代码编译成app2，在命令行中输入下列命令就可以在Student表中插入新的行了。

./app2 MyDB "insert into Students values(100)"

./app2 MyDB "insert into Students values(10)"

./app2 MyDB "insert into Students values(1000)"

然后运行app1，就能得到下面的结果：

SID = 10

SID = 100

SID = 100

SID = 200

SID = 300

SID = 1000

你也可以在一个数据库中创建新的表，例如，执行./app2 MyDBExtn "create table Courses(name varchar, SID integer)"就能在 MyDBExtn数据库中创建Courses表。

NOTE

SQLite有一个内部的命令行程序（sqlite3），你可以从官网上下载这个程序的可执行文件或者源代码，app2是这个程序的核心代码，通过使用这个程序，你就能对数据库进行各种操作了。

1.1.3 多线程

SQLite是一个线程安全的库，所以同一应用程序中的多个线程可以并发地进入一个或多个数据库。

NOTE

为了实现线程安全，编译源代码时必须将预处理宏THREADSAFE设置为1。（编译时在配置脚本中写入--enable-threadsafe）。Linux平台上的编译设置默认是禁止多线程并发的，但是windows平台上是默认打开的。SQLite库是否具有并发特性，这在程序中无法查询得到的。

下面的例子用pthreads库，该库只存在于Linux系统下。在windows下你有两个选择，使用Cygwin提供的工具和编译器(http://www.cygwin.com)，或者从官网(http://sourceware.org/pthreads-win32/)下载pthreads for windows。

#include <stdio.h>
#include <pthread.h>
#include "sqlite3.h"
void* myInsert(void* arg)
{
sqlite3* db = 0;
sqlite3_stmt* stmt = 0;
int val = (int)arg;
char SQL[100];
int rc;
rc = sqlite3_open("MyDB", &db); /* Open a database named MyDB */
if (rc != SQLITE_OK) {
fprintf(stderr, "Thread[%d] fails to open the database\n", val);
goto errorRet;
}

/* Create the SQL string. If you were using string values,
you would need to use sqlite3_prepare() and sqlite3_bind_*
to avoid an SQL injection vulnerability. However %d
guarantees an integer value, so this use of sprintf is
safe.
*/

sprintf(SQL, "insert into Students values(%d)", val);
/* Prepare the insert statement */
rc = sqlite3_prepare(db, SQL, -1, &stmt, 0);
if (rc != SQLITE_OK) {
fprintf(stderr, "Thread[%d] fails to prepare SQL: %s ->
return code %d\n", val, SQL, rc);
goto errorRet;
}

rc = sqlite3_step(stmt);
if (rc != SQLITE_DONE) {
fprintf(stderr,
"Thread[%d] fails to execute SQL: %s -> return code %d\n", val, SQL, rc);
}
else {
printf("Thread[%d] successfully executes SQL: %s\n", val,
SQL);
}

sqlite3_close(db);
return (void*)rc;
}

int main(void)
{
pthread_t t[10];
int i;
for (i=0; i < 10; i++)
pthread_create(&t[i], 0, myInsert, (void*)i); /* Pass the value of i */
/* wait for all threads to finish */
for (i=0; i<10; i++) pthread_join(t[i], 0);
return 0;
}

这个例子创建10个线程，每个线程都要在MyDB数据库的Student表中插入一行。SQLite使用的是基于锁的并发控制策略，所以一些或者全部的INSERT语句可能执行失败。应用程序无需关心并发控制，只需要检查失败的INSERT语句，并且在代码中处理这些失败情况（例如，当SQL语句执行失败后，你可以重新执行该语句，或者通知用户SQL语句执行失败）。

每个线程都需要创建数据库连接并建立自己的SQLite句柄。SQLite不推荐在线程间分享SQLite句柄。尽管分享句柄后程序依然可以运行，但是无法保证得到正确的结果。事实上，如果这样做的话，在一些Linux平台上SQLite可能会崩溃。同样的，在一些Unix/Linux平台上，不能通过fork函数将SQLite句柄传给子进程，否则也将导致SQLite崩溃。

NOTE

在SQLite 3.3.1以及之后的版本，放松了对"在线程间分享SQLite句柄"的限制。你可以将一个SQLite连接从一个线程转移给另一个线程，但是前提条件是前一个线程不再持有任何关于这个SQLite连接的本地文件锁。如果没有任何pending的事务、所有的SQL语句都被reset和finalized，那么这个线程就不持有任何本地文件锁。

1.1.4 操作多个数据库

下面的例程操作了两个数据库：

#include <stdio.h>
#include "sqlite3.h"
int main(void)
{
sqlite3* db = 0;
sqlite3_open("MyDB", &db); /* Open a database named MyDB */
sqlite3_exec(db, "attach database MyDBExtn as DB1", 0, 0, 0);
sqlite3_exec(db, "begin", 0, 0, 0);
sqlite3_exec(db, "insert into Students values(2000)", 0, 0, 0);
sqlite3_exec(db, "insert into Courses values('SQLite Database', 2000)", 0, 0, 0);
sqlite3_exec(db, "commit", 0, 0, 0);
sqlite3_close(db);
return SQLITE_OK;
}

上面的例子没有包括对错误的处理。该例子首先打开了MyDB数据库，然后将MyDBExtn附件到当前的连接上。MyDB需要有一个Student（SID）表，MyDBExtn需要有一个Courses（name，SID）表。Begin命令开始了一项事务，在student表中插入一行，然后在courses表中插入一行，最后通过commit命令提交事务。INSERT命令不需要回调函数，所以传给sqlite3_exec的回调函数参数是0。

NOTE

SQLite允许向单个sqlite3_exec函数传入多个SQL语句，例如：begin; insert into Students values(2000); insert into Courses values('SQLite Database', 2000); commit.

1.1.5 目录（catalog）

对于每个数据库，SQLite自己创建并维护一个叫做sqlite_master的主目录，里面存储着数据库的元信息（模型，表，索引，触发器，视图）。你可以向主目录申请查询（例如，select * from sqlite_master），但是不能直接修改主目录。所有的目录都以sqlite_为前缀，用户不能使用这个前缀命名变量。