C++访问SQLite

1.安装SQLite 

sudo apt-get install sqlite3

2.安装SQLite开发包

sudo apt-get install libsqlite3-dev 

3.创建表

$sqlite3 Acceptor_0.db 
SQLite version 3.7.9 2011-11-01 00:52:41
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE maxPromised(id BIGINT auto_increment primary key, lock_name varchar(128), instance_no BIGINT, ballot_no BIGINT);

sqlite> CREATE TABLE maxAccepted(id BIGINT auto_increment primary key, lock_name varchar(128), instance_no BIGINT, ballot_no BIGINT, usid char(37), val_len int, val_str varchar(128));

4.代码片段

#include <sqlite3.h>

sqlite3 *conn;
char * sErrMsg = 0;

//database
string dbfile = Utils::string_format("%s/Acceptor_%d.db",LOG_DIR, id);

int result = sqlite3_open_v2( dbfile.c_str(), &conn, SQLITE_OPEN_READWRITE | SQLITE_OPEN_FULLMUTEX, NULL );
//sqlite3_exec(conn, "PRAGMA synchronous = OFF", NULL, NULL, &sErrMsg);
//sqlite3_exec(conn, "PRAGMA journal_mode = MEMORY", NULL, NULL, &sErrMsg);

if (result) {
	LOGGER(ERROR)<<"Can't open database: "<<sqlite3_errmsg(conn);
	exit(0);
}

//插入
//permanent media
sqlite3_prepare(acceptor->conn, 
	"INSERT INTO maxAccepted(lock_name, instance_no, ballot_no, usid, val_len, val_str) VALUES(?, ?, ?, ?, ?, NULL);", 
	-1, &stmt, &zTail
      );

sqlite3_bind_text(stmt, 1, field, -1, SQLITE_STATIC);
sqlite3_bind_int64(stmt,2, prepare->instanceNo);
sqlite3_bind_int64(stmt,3, accRec->bno);
sqlite3_bind_text(stmt, 4, accRec->usid, SessionId::size, SQLITE_STATIC);
sqlite3_bind_int(stmt,  5, accRec->vallen);
//sqlite3_bind_text(stmt, 6, accRec->value);

int result = sqlite3_step(stmt);

if (result != SQLITE_DONE) {
	LOGGER(ERROR)<<sqlite3_errmsg(acceptor->conn);
}

sqlite3_finalize(stmt);

//更新
sqlite3_prepare(acceptor->conn, 
	"UPDATE maxPromised set ballot_no = ? WHERE lock_name = ? AND instance_no = ?;", 
	-1, &stmt, &zTail
      );

sqlite3_bind_int64(stmt,1, prepare->ballotNo);
sqlite3_bind_text(stmt, 2, field, -1, SQLITE_STATIC);
sqlite3_bind_int64(stmt,3, prepare->instanceNo);

int result = sqlite3_step(stmt);

if (result != SQLITE_DONE) {
	LOGGER(ERROR)<<sqlite3_errmsg(acceptor->conn);
}

sqlite3_finalize(stmt);

//查询
const char* field = NULL;
uint64_t iid = 0;
uint64_t bno = 0;

sqlite3_stmt * stmt; 
const char *zTail; 

//recover maxAccepted

//permanent media
sqlite3_prepare(conn, 
	"SELECT * FROM maxAccepted",
	-1, &stmt, &zTail );

while ( (result = sqlite3_step(stmt)) == SQLITE_ROW) {
	field = (const char*)sqlite3_column_text(stmt,1);
	iid   = sqlite3_column_int64(stmt,2);
	bno   = sqlite3_column_int64(stmt,3);

	printf("%s %lu %lu\n", field, iid, bno);
}

sqlite3_finalize(stmt);

//close database
sqlite3_close(conn);

参考资料：

1.SQLite Embedded Database http://www.yolinux.com/TUTORIALS/SQLite.html

2.How do I improve the performance of SQLite? http://stackoverflow.com/questions/1711631/how-do-i-improve-the-performance-of-sqlite