sqlite数据库方法类使用说明-检查列字段是否存在、添加列、删除列

本文主要演示sqlite开源项目中的数据库方法类使用说明。本项目旨在分享知识和经验，欢迎广大网友一起加入到开源项目中一起分享贡献知识和经验。

线上测试代码：

/******************************************************************************
  > File Name		: samples_addfield.cpp
  > Author			: Wseldom
  > Mail			: 3235459847@qq.com
  > Created Time	: Fri 01 Dec 2023 03:06:27 PM CST
******************************************************************************/
#include "./samples_addfield.hpp"
#include "sqlite/sqlite.h"

#define	RUN_TO_HERE printf ("run to here(%d)\n", __LINE__);



int samples_addfield (int argc, char** argv)
{
	if (argc < 2) {
		printf ("请输入一个参数以指定数据库文件名！\n");
		return -1;
	}
	sql::sqlite db(argv[1]);
	db.exec ("create table if not exists employee("
		"ID INTEGER PRIMARY KEY  AUTOINCREMENT, "
		"NAME           TEXT    NOT NULL UNIQUE, "
		"AGE            INT     NOT NULL, "
		"ADDRESS        CHAR(50)"
	");");
	db.exec ("INSERT INTO employee(ID,NAME,AGE,ADDRESS) VALUES(1, '张三', 18, '珠海');");
	db.exec ("INSERT INTO employee(ID,NAME,AGE,ADDRESS) VALUES(NULL, '李四', 21, '广州');");
	db.exec ("INSERT INTO employee(ID,NAME,AGE,ADDRESS) VALUES(NULL, '啊五', 32, '深圳');");
	db.exec ("INSERT INTO employee(ID,NAME,AGE,ADDRESS) VALUES(NULL, '小哀', 23, '珠海');");
	db.exec ("INSERT INTO employee(ID,NAME,AGE,ADDRESS) VALUES(NULL, '刘五', 18, '深圳');");
	db.exec ("INSERT INTO employee(ID,NAME,AGE,ADDRESS) VALUES(NULL, '柯南', 43, '广州');");
	db.exec ("INSERT INTO employee(ID,NAME,AGE,ADDRESS) VALUES(NULL, '苏周', 51, '广州');");
	db.exec ("INSERT INTO employee(ID,NAME,AGE,ADDRESS) VALUES(NULL, '啊二', 21, '珠海');");
	printf ("检查表中字段。\n");
	if (db.check_field ("employee", "SALARY") == E_SQLITE_FALSE) {
		printf ("表employee中不存在字段SALARY，添加字段SALARY，并设置默认值为10000.\n");
		db.exec (SQLCMD_ADD_COLUMN ("employee","SALARY REAL DEFAULT 10000"));
	} else {
		printf ("表employee中已存在字段SALARY.\n");
	}
	RUN_TO_HERE
	printf ("再次检查表中字段。\n");
	RUN_TO_HERE
	if (db.check_field ("employee", "SALARY") == E_SQLITE_FALSE) {
		printf ("表employee中不存在字段SALARY.\n");
	} else {
		printf ("表employee中已存在字段SALARY.\n");
	}
	RUN_TO_HERE
	sql::query* query;
	query = db.query ("select * from employee limit 1;");
	RUN_TO_HERE
	if (query) {
		query->begin ();
		do {
			printf ("ID=%ld,NAME=%s,AGE=%ld,ADDRESS=%s,SALARY=%f\n"
				, query->get_number ("ID", 0)
				, query->get_string ("NAME", "未知")
				, query->get_number ("AGE", 0)
				, query->get_string ("ADDRESS", "地址")
				, query->get_float ("SALARY", 5000)
			);
		} while (query->next () > 0);
		delete query;
	}
	return 0;
}

首先创建表employee，表中不包含字段SALARY，然后插入一些数据；
然后调用方法check_field检查字段SALARY是否灿在，若不存在则新增此字段，并且设置默认值为10000；若存在则提示已存在。插入后重新检查字段是否存在，并且查询一条记录打印其值，看默认值是否生效。执行结果如下：

注意“sqlite err info:query aborted”并非查询错误，此日志是因为check_field检查到字段存在时在回调函数中返回非0导致查询终止而形成的日志。

接下来是删除表中字段的例程。

/******************************************************************************
  > File Name		: samples_delfield.cpp
  > Author			: Wseldom
  > Mail			: 3235459847@qq.com
  > Created Time	: Fri 01 Dec 2023 03:59:25 PM CST
******************************************************************************/
#include "./samples_delfield.hpp"
#include "sqlite/sqlite.h"




int samples_delfield (int argc, char** argv)
{
	if (argc < 2) {
		printf ("请输入一个参数以指定数据库文件名！\n");
		return -1;
	}
	sql::sqlite db(argv[1]);
	db.exec ("create table if not exists employee("
		"ID INTEGER PRIMARY KEY  AUTOINCREMENT, "
		"NAME           TEXT    NOT NULL UNIQUE, "
		"AGE            INT     NOT NULL, "
		"ADDRESS        CHAR(50), "
		"SALARY         REAL"
	");");
	db.exec ("INSERT INTO employee(ID,NAME,AGE,ADDRESS,SALARY) VALUES(1, '张三', 18, '珠海', 2500.0);");
	db.exec ("INSERT INTO employee(ID,NAME,AGE,ADDRESS,SALARY) VALUES(NULL, '李四', 21, '广州', 6800.0);");
	db.exec ("INSERT INTO employee(ID,NAME,AGE,ADDRESS,SALARY) VALUES(NULL, '啊五', 32, '深圳', 16500.0);");
	db.exec ("INSERT INTO employee(ID,NAME,AGE,ADDRESS,SALARY) VALUES(NULL, '小哀', 23, '珠海', 13000.0);");
	db.exec ("INSERT INTO employee(ID,NAME,AGE,ADDRESS,SALARY) VALUES(NULL, '刘五', 18, '深圳', 4500.0);");
	db.exec ("INSERT INTO employee(ID,NAME,AGE,ADDRESS,SALARY) VALUES(NULL, '柯南', 43, '广州', 20500.0);");
	db.exec ("INSERT INTO employee(ID,NAME,AGE,ADDRESS,SALARY) VALUES(NULL, '苏周', 51, '广州', 22500.0);");
	db.exec ("INSERT INTO employee(ID,NAME,AGE,ADDRESS,SALARY) VALUES(NULL, '啊二', 21, '珠海', 12500.0);");


	printf ("删除字段SALARY");
	db.exec ("create table temp as select ID,NAME,AGE,ADDRESS from employee where 1 = 1;");
	db.exec ("drop table employee;");
	db.exec (SQLCMD_RENAME_TABLE("temp", "employee"));

	if (db.check_field ("employee", "SALARY") == E_SQLITE_FALSE) {
		printf ("表employee中已不存在字段SALARY.\n");
	} else {
		printf ("表employee中存在字段SALARY.\n");
	}

	sql::query* query = NULL;
	query = db.query ("select * from employee;");
	if (query == NULL) {
		printf ("查询失败或未查询到数据！\n");
		return -1;
	}
	if (query->size () <= 0) {
		delete query;
		printf ("未查询到数据！\n");
		return -1;
	}
	printf ("总共查询到%d条记录，记录如下：\n", query->size ());
	query->begin ();
	do {
		printf ("ID:%ld, name:%s, age:%ld, address:%s, salary:%f\n"
		, query->get_number ("ID", 0)
		, query->get_string ("name", "")
		, query->get_number ("age", 0)
		, query->get_string ("address", "")
		, query->get_float ("salary", 0)
		);
	} while (query->next () > 0);
	delete query;
	return 0;
}

执行结果如下: