本文主要演示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;
}
执行结果如下: