sqlite_master结构
1.sql语句:SELECT * from sqlite_master where name = '表名' and sql like '%字段名%'//查询表中有无要添加的字段
:alter table 表名 add column 字段 INTEGER default 1//在表的结尾添加新字段
2.demon节选
int sql_exec(sqlite3 *db, const char *sql)
{
int res;
sqlite3_stmt *stmt = NULL;
if ((NULL == db) || (NULL == sql)) {
printf("param_error");
return -1;
}
if (!stmt) {
res = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
if (SQLITE_OK != res) {
goto out;
}
}
/*遍历select执行的返回结果
**/
res = sqlite3_step(stmt);
if ((SQLITE_ROW == res)) {
return 0;
}
out:
if ((res != SQLITE_DONE) && (res != SQLITE_OK)) {
const char *err = sqlite3_errmsg(db);
if (err) {
printf("%s", err);
}
}
sqlite3_finalize(stmt);
return 1;
}
int class_table_confirm(sqlite3 *db, const char *sql)
{
int res;
sqlite3_stmt *stmt = NULL;
res = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
if (res != SQLITE_OK) {
goto out;
}
res = sqlite3_step(stmt);
if (res == SQLITE_ROW) {
sqlite3_finalize(stmt);
return 0;
}
out:
if (res != SQLITE_DONE && res != SQLITE_OK) {
printf("%s", sqlite3_errmsg(db));
}
if (stmt) {
sqlite3_finalize(stmt);
}
return 1;
}
//检查数据库中的某张表中的某个字段是否存在,不存在添加
void _check_column(sqlite3 *db)
{
int ret;
char *errmsg = NULL;
char *tmp =" SELECT * from sqlite_master where name = 'class' and sql like '%sex%' ";
ret = class_table_confirm(db, tmp);
if (0 != ret) {
tmp = "alter table class add column sex INTEGER default 1";
sql_exec(db, tmp);
}
}
完整可运行demon
在class表中添加sex字段
#include <stdio.h>
#include <sqlite3.h>
#include <string.h>
#include <errno.h>
/***********sql 语句**************************/
#define CREATE_SCOOL_TABLE \
"CREATE TABLE IF NOT EXISTS SCHOOL(students_number INTEGER DEFAULT 0,grade INTEGER DEFAULT 0)"
#define CREATE_CLASS_TABLE \
"CREATE TABLE IF NOT EXISTS class(student_name DEFAULT NULL,age INTEGER DEFAULT 0)"
#define INSERT_class "INSERT or IGNORE INTO class(age,student_name,sex) VALUES (?,?,?)"
#define EMPTY_STUDENT "DELETE FROM class"
/***********sql 语句**************************/
#define DB_PATH "./SQL.db"
static sqlite3 *db;
int sql_exec(sqlite3 *db, const char *sql)
{
int res;
sqlite3_stmt *stmt = NULL;
if ((NULL == db) || (NULL == sql)) {
printf("param_error");
return -1;
}
if (!stmt) {
res = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
if (SQLITE_OK != res) {
goto out;
}
}
/*遍历select执行的返回结果
**/
res = sqlite3_step(stmt);
if ((SQLITE_ROW == res)) {
return 0;
}
out:
if ((res != SQLITE_DONE) && (res != SQLITE_OK)) {
const char *err = sqlite3_errmsg(db);
if (err) {
printf("%s", err);
}
}
sqlite3_finalize(stmt);
return 1;
}
void sql_create_table(db)
{
sql_exec(db, EMPTY_STUDENT);
sql_exec(db, CREATE_SCOOL_TABLE);
sql_exec(db, CREATE_CLASS_TABLE);
}
void sql_class_insert(int age,const char *name,const char *sex)
{
int res, err = -1;
sqlite3_stmt *stmt;
//1.执行sql select语句
res = sqlite3_prepare_v2(db, INSERT_class, -1, &stmt, NULL);
if (SQLITE_OK != res) {
goto out;
}
//2.绑定数据 int类型
res = sqlite3_bind_int(stmt, 1, age);
if (res != SQLITE_OK)
{
goto out_free;
}
//2.绑定数据 字符型
//SQLITE_STATIC->传递给该字符串的指针将有效,直到执行查询为止
res = sqlite3_bind_text(stmt, 2, name, -1, SQLITE_STATIC);
if (res != SQLITE_OK)
{
goto out_free;
}
res = sqlite3_bind_text(stmt, 3, sex, -1, SQLITE_STATIC);
if (res != SQLITE_OK)
{
goto out_free;
}
//3.遍历select执行的返回结果
res = sqlite3_step(stmt);
if (SQLITE_DONE == res) {
err = 0;
}
out_free:
//4.销毁前面被sqlite3_prepare创建的准备语句
//【每个准备语句都必须使用这个函数去销毁以防止内存泄露】
sqlite3_finalize(stmt);
out:
if (err < 0) {
printf("%s\n", sqlite3_errmsg(db));
printf( "res = %d\n", res);
}
}
int class_table_confirm(sqlite3 *db, const char *sql)
{
int res;
sqlite3_stmt *stmt = NULL;
res = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
if (res != SQLITE_OK) {
goto out;
}
res = sqlite3_step(stmt);
if (res == SQLITE_ROW) {
sqlite3_finalize(stmt);
return 0;
}
out:
if (res != SQLITE_DONE && res != SQLITE_OK) {
printf("%s", sqlite3_errmsg(db));
}
if (stmt) {
sqlite3_finalize(stmt);
}
return 1;
}
//检查数据库中的某张表中的某个字段是否存在,不存在添加
void _check_column(sqlite3 *db)
{
int ret;
char *errmsg = NULL;
char *tmp =" SELECT * from sqlite_master where name = 'class' and sql like '%sex%' ";
ret = class_table_confirm(db, tmp);
if (0 != ret) {
tmp = "alter table class add column sex INTEGER default 1";
sql_exec(db, tmp);
}
}
int sqlite_init(const char *db_path)
{
int ret;
if (NULL == db_path) {
perror("sqlite_init error");
return -1;
}
//判断是否启用串行模式
if (sqlite3_threadsafe() != 1) {
ret = sqlite3_config(SQLITE_CONFIG_SERIALIZED);//设置为串行模式
if (ret != SQLITE_OK) {
printf("SQLite3 is not compiled with serialized threading mode!\n");
return -1;
}
}
//sqlite初始化
ret = sqlite3_initialize();
if (ret != SQLITE_OK) {
perror("database_init sqlite_initalize error");
return -1;;
}
ret = sqlite3_open(db_path,&db);
if (ret) {
printf("Can't open sql: %s", sqlite3_errmsg(db));
return -1;
}
sql_create_table(db);
_check_column(db);
return 0;
}
int main()
{
sqlite_init(DB_PATH);
sql_class_insert(18,"张三","男");
return 0;
}
运行结果