半冷半暖秋天

开始的时候为了赶进度，项目中有些地方拿ini做数据库用，现在处理起来超别扭，于是想换成sqlite。

只要会点SQL就基本能使用sqlite了，不过有些语法还是属于sqlite的。语法查询页面：http://www.sqlite.org/lang.html

管理软件：SQLite Database Browser、Sqlite Dev、SQLite Spy

示例大杂烩：

#include "sqlite3.h"
int testSQL()
{
	sqlite3 * db;
	int result;
	char * errmsg = NULL;
	char **dbResult;
	int nRow, nColumn;
	int i,j;
	int index;

	result = sqlite3_open( "./users.db", &db );
	if( result != SQLITE_OK ){
		return -1;
	}

	//创建表
	result = sqlite3_exec( db, "CREATE TABLE [user]([uid] Integer,[name] Char(255) NOT NULL,[gid] Integer NOT NULL,[groups] Char(255) NOT NULL,[password] Char(255) NOT NULL,[disk] Char(255) NOT NULL,[quota] BigInt NOT NULL DEFAULT 0,[email] Char(255) NOT NULL,PRIMARY KEY([uid]))", NULL, NULL, &errmsg );
	if(result != SQLITE_OK ){
		printf("错误码:%d，错误原因:%s\n", result, errmsg );
	}

	//如果要在锁定状态写数据库,先"BEGIN IMMEDIATE",再"END",这样在此期间,其它进程或线程对同一数据库的写操作将被禁止。
	sqlite3_exec( db, "BEGIN IMMEDIATE", NULL, NULL, &errmsg );
	printf("locked\n");
	char sql[1024]="";
	if (1) {
		sprintf(sql, "insert into [user] ('name','gid','groups','password','disk','quota','email') values ('user%d',6000,'6000,7000','3.141590','/opt/xxx/disk', 1024, 'a@b.com')", GetTickCount());
		result = sqlite3_exec( db, sql, NULL, NULL, &errmsg );
		if(result != SQLITE_OK ){
			printf("插入记录失败，错误码:%d，错误原因:%s\n", result, errmsg );
		}
	}
	sqlite3_exec( db, "END", NULL, NULL, &errmsg );
	printf("unlocked\n");

	//刚才插入的记录ID
	int last_id = sqlite3_last_insert_rowid(db);
	printf("last_id=%d\n", last_id);

	//一次性获取所有结果
	result = sqlite3_get_table( db, "select name,email from user limit 0,5", &dbResult, &nRow, &nColumn, &errmsg );
	if( SQLITE_OK == result ){
		index = nColumn;
		printf( "查到%d条记录\n", nRow );
		for( i = 0; i < nRow ; i++ ){
			printf( "%d.\n", i+1 );
			for( j = 0 ; j < nColumn; j++ ){
				printf( "%s\t=>\t%s\n", dbResult[j], dbResult[index] );
				++index;
			}
			printf( "-------------\n" );

			//只为获取某个字段的值
			printf("email=%s\n", dbResult[(i+1)*nColumn + 0] );
		}
	}
	sqlite3_free_table( dbResult );

	//一条条地读记录
	if (1) {
		sqlite3_stmt *st;
		char *sql = "SELECT name from user limit 5";
		result = sqlite3_prepare(db, sql, strlen(sql), &st, 0);
		if (result != SQLITE_OK) {
			fprintf(stderr, "Error on sqlite3_prepare: %s\n", sqlite3_errmsg(db));
		} else {
			for (;;) {
				result = sqlite3_step(st);
				if (result == SQLITE_ROW) {
					int cols = sqlite3_column_count(st);
					//打印当前记录的值
					printf("Result=%s\n", sqlite3_column_text(st, 0) );
				} else if (result == SQLITE_DONE) {
					break;
				} else {
					fprintf(stderr, "Error on sqlite3_step: %s\n", sqlite3_errmsg(db));
				}
			}
		}
		result = sqlite3_finalize(st);
		if (result != SQLITE_OK) {
			fprintf(stderr, "Error on sqlite3_finalize: %s\n", sqlite3_errmsg(db));
		}
	}

	//更新数据
	result = sqlite3_exec( db, "UPDATE [user] SET 'email'='pansunyou@gmail.com'", NULL, NULL, &errmsg );
	if(result != SQLITE_OK ){
		printf("错误码:%d，错误原因:%s\n", result, errmsg );
	}

	//关闭文件
	sqlite3_close( db );

	return 0;
}