sqlite数据库操作类使用说明-基础篇

sqlite数据库操作类使用说明-基础篇

本文主要演示sqlite数据库类的基础用法。
本博客所演示的sqlite的数据库类，博主已开源，欢迎大家一起加入修改贡献。

本篇数据库类使用c++进行封装，但导出了C语言接口，本文测试代码使用的是C语言用法，c++用法基本一致，集成了一些常用的宏定义以及不常用的宏定义用法。
先上测试代码：

#include <Wdebug.h>
#include <stdio.h>

#include <sqlite/Wsqlite.h>
#include <stdlib.h>

#define		TABLE_NAME		"TEST_base"

extern int callback(void* pdata, int argc, char** argv, char** azColName);

void test_base(int argc, char* argv[])
{
  struct TsqlQuery* parse = NULL;
  struct TsqlCtrl* This = CreateSqlCtrl("database.db");
  if (This != NULL) {
  	Wlog("创建表");
  	TsqlCtrlExec (This, SQLCMD_DROP_TABLE(TABLE_NAME), NULL, NULL);
  	TsqlCtrlExec(This, SQLCMD_CREATE_TABLE(TABLE_NAME,
  	"ID INTEGER PRIMARY KEY  AUTOINCREMENT, "
      "NAME           TEXT    NOT NULL, "
      "AGE            INT     NOT NULL, "
      "ADDRESS        CHAR(50), "
      "SALARY         REAL"), NULL, NULL);
  	Wlog ("字段AGE:%s", TsqlCtrlCheckField(This, TABLE_NAME, "AGE")?"存在":"不存在");
  	Wlog ("字段AAAAAA:%s", TsqlCtrlCheckField(This, TABLE_NAME, "AAAAAA")?"存在":"不存在");

  	//Wlog ("查询索引：%d", TsqlCtrlRecordCnt(This, "SELECT COUNT(*) AS Cnt FROM sqlite_master WHERE type='index' AND name='test_idx'"));
  	if (TsqlCtrlRecordCnt(This, SQLCMD_IDX_EXIST("test_idx"))==0) {//判断索引是否存在
  		Wlog ("创建索引！");
  		TsqlCtrlExec(This, SQLCMD_CREATE_IDX(TABLE_NAME, "test_idx", "ID"), NULL, NULL);
  	}

  	TsqlCtrlExec(This, "INSERT INTO "TABLE_NAME" (ID,NAME,AGE,ADDRESS,SALARY) VALUES(1, '张三', 18, '珠海', 2500.0);", NULL, NULL);
  	TsqlCtrlExec(This, "INSERT INTO "TABLE_NAME" (ID,NAME,AGE,ADDRESS,SALARY) VALUES(NULL, '李四', 21, '广州', 6080.0);", NULL, NULL);
  	TsqlCtrlExec(This, "INSERT INTO "TABLE_NAME" (ID,NAME,AGE,ADDRESS,SALARY) VALUES(NULL, '啊五', 30, '深圳', 10800.0);", NULL, NULL);
  	TsqlCtrlExec(This, "INSERT INTO "TABLE_NAME" (ID,NAME,AGE,ADDRESS,SALARY) VALUES(NULL, '小哀', 10, '珠海', 0.0);", NULL, NULL);
  	TsqlCtrlExec(This, "SELECT * FROM "TABLE_NAME " WHERE AGE>10;", callback, NULL);
  	Wlog ("record=%d", TsqlCtrlRecordCnt (This, SQLCMD_RECORDCNT(TABLE_NAME)));
  	//TUint32 RecordCnt =  TsqlCtrlRecordCnt (This, SQLCMD_RECORDCNT(TABLE_NAME));
  	TsqlCtrlExec(This, "UPDATE " TABLE_NAME " SET AGE=31 WHERE NAME='啊五'", NULL, NULL);
  	TsqlCtrlExec(This, "SELECT AGE, NAME FROM "TABLE_NAME, callback, NULL);
  	TsqlCtrlExec(This, "DELETE FROM "TABLE_NAME" WHERE SALARY<3000.0", NULL, NULL);
  	TsqlCtrlExec(This, "SELECT NAME, AGE FROM "TABLE_NAME " WHERE ID>0;", callback, NULL);
  	Wlog("当前记录数为：%d", TsqlCtrlRecordCnt(This, "SELECT COUNT(*) AS Cnt FROM "TABLE_NAME));
  	TsqlCtrlExec(This, "INSERT INTO "TABLE_NAME " (ID,NAME,AGE,ADDRESS,SALARY) VALUES(NULL, '小刘', 28, '梅州', 8500.32);", NULL, NULL);
  	TsqlCtrlExec(This, "INSERT INTO "TABLE_NAME" (ID,NAME,AGE,ADDRESS,SALARY) VALUES(NULL, '小张', 29, '深圳', 7500.25);", NULL, NULL);
  	TsqlCtrlExec(This, "INSERT INTO "TABLE_NAME " (ID,NAME,AGE,ADDRESS,SALARY) VALUES(NULL, '小雪', 40, '广州', 10080.46);", NULL, NULL);
  	Wlog("当前记录数为：%d", TsqlCtrlRecordCnt(This, "SELECT COUNT(*) AS Cnt FROM "TABLE_NAME));
  	TsqlCtrlExec(This, "SELECT * FROM "TABLE_NAME, callback, NULL);
  	TsqlCtrlExec(This, "INSERT INTO "TABLE_NAME " (ID,NAME,AGE,ADDRESS,SALARY) VALUES(NULL, '小吴', 23, '上海', 9600.0);", NULL, NULL);
  	TsqlCtrlExec(This, "INSERT INTO "TABLE_NAME " (ID,NAME,AGE,ADDRESS,SALARY) VALUES(NULL, '阿奇', 35, '北京', 8500.21);", NULL, NULL);
  	TsqlCtrlExec(This, "INSERT INTO "TABLE_NAME " (ID,NAME,AGE,ADDRESS,SALARY) VALUES(NULL, '志勇', 29, '长沙', 6800.69);", NULL, NULL);
  	TsqlCtrlExec(This, "SELECT * FROM "TABLE_NAME " WHERE SALARY>3000.0;", callback, NULL);
  	parse = TsqlCtrlQuery(This, "SELECT * FROM "TABLE_NAME " WHERE SALARY>3000.0;");
  	if (parse) {
  		Wlog("跳转到记录2！");
  		TsqlQueryBegin (parse);
  		TsqlQueryNext (parse);
  		TsqlQueryNext (parse);
  		Wlog("记录2 NAME = %s", TsqlQueryGetString(parse, "NAME", ""));
  		Wlog("下一