- NULL :空值
- INTEGER:整型
- REAL:浮点型
- TEXT:字符串
- BLOB:二进制
- DATETIME:日期
- 打开数据库 【sqlite3 dbname】
- 查看数据库版本 【 .version】
- 查看已打开的数据库 【 .database】
- 显示表 【.table】
- 显示表信息 【.schema】
- 格式化输出数据 【.header on】【.mode column】
- 导出数据库 【.dump】
- 清理压缩数据库 【vacuum】
int sqlite3_open ( const char dbname,sqlite3 *db)
功能打开或创建数据库文件,返回句柄参数 const char* dbname :数据库文件名, 为NULL时在RAM创建内存数据库 sqlite3 db :数据库指针句柄返回值成功 :SQLITE_OK失败 :ERROR_msg
sqlite3 *sqlite3_open_database()
{
sqlite3 *db;
int ret;
ret = sqlite3_open("MyDB", &db);
if (ret == SQLITE_OK) {
/* 打开/创建 成功*/
} else {
/* 打开/创建失败, 打印错误信息*/
sqlite3_errmsg(db);
return NULL;
}
return db;
}
char sqlite3_errmsg ( sqlite3 db )
功能显示错误信息参数 sqlite3* db:数据库文件句柄返回值const char* err_msg
printf ("Error message is: %s", sqlite3_errmsg(db));
int sqlite3_close ( sqlite3* db )
功能关闭数据库文件连接参数sqlite3* db :数据库文件句柄返回值成功:SQLITE_OK阻塞失败:SQLITE_BUSY
int sqlite3_close_database (sqlite3 *db)
{
if (db != NULL) {
sqlite3_close(db);
return 0;
} else {
return -1;
}
}
int sqlite3_exec(sqlite3 db,char sql,exec_callback,
void data,char *err_msg)
功能回调执行SQL,包含预编译—执行参数 sqlite3* db:数据库文件句柄 char* sql:执行的sql语句字符串 exec_callback:回调函数 (无回调函数则默认0) void* data:传入回调函数的参数 (无回调函数则默认0) char err_msg:错误信息返回值成功 :SQLITE_OK失败 :Error_msg
int exec_handle (void data,int argc,char argv,char *colname)
功能sqlite_exec回调函数,每次只返回单条结果参数 void* data:sqlite3_exec的传入参数 int argc:SQL功能返回结果的字段数量 char argv:SQL功能返回结果的内容,为一维char 数组 char cloname:SQL功能返回结果的字段名称,为一维char 数组返回值 注意:必须return 0(FALSE),负责报errmsg【query aborted】
int sqlite3_get_table (sqlite3 db,char sql,char* dbResult,
int nRow,int nColnm,char errmsg)**
功能非回调执行SQL参数 sqlite3* db:数据库文件句柄 char* sql:执行的sql语句 char* dbResult:执行sql返回的结果集,一维char *数组的指针 int nRow:返回结果集的总行数 int nColnm:返回结果集的字段数返回值SQLITE_OK:执行成功Error_code:执行失败注意事项dbResult使用完必须用sqlite3_free_table( )释放,否则会内存泄漏访问**dbResult要注意越界问题,否则程序会崩
int sqlite3_prepare_v2 (sqlite3 db,const char zSql,int nByte,
sqlite3_stmt ppStmt,const char pzTail )
功能将SQL语句文本转化为申明对象,返回对象指针参数 sqlite3* db:数据库文件句柄 const char* zSQL:SQL语句 int nByte:SQL字节数,< 0表示取到第一个\0终止符 sqlite3_stmt ppStmt:预编译语句句柄 const char pzTail:指向SQL语句中未使用的部分返回值成功:SQLITE_OK失败:Error_code,stmt =NULL
int sqlite3_bind_int (sqlite3_stmt* pStmt,int index,int data)
功能给预编译语句绑定整型参数参数 sqlite3_stmt *pStmt :预编译语句句柄 int index :序号 ,从1开始 int data:整型数值返回值成功:SQLITE_OK失败:Error_code
int sqlite3_bind_doubule (sqlite3_stmt* pStmt,int index,double data)
功能给预编译语句绑定双精度参数参数 sqlite3_stmt *pStmt :预编译语句句柄 int index :序号 ,从1开始 double data:双精度数值返回值成功:SQLITE_OK失败:Error_code
int sqlite3_bind_text (sqlite3_stmt pStmt,int index,const char str,
int strlen,void()(void))
功能给预编译语句绑定参数参数 sqlite3_stmt *pStmt :预编译语句句柄 int index :序号 ,从1开始 const char *str :绑定值 int strlen :str长度 void()(void) :NULL返回值成功:SQLITE_OK失败:Error_code
int sqlite3_step (sqlite3_stmt *pStmt)
功能执行预编译后的SQL对象参数 sqlite3_stmt *pStmt :预编译语句句柄返回值成功: SQLITE_OK | SQLITE_DONE阻塞: SQLITE_BUSY运行错误:SQLITE_ERROR查询成功:SQLITE_ROW
int sqlite3_finalize (sqlite3_stmt *pStmt)
功能销毁预编译对象参数 sqlite3_stmt *pStmt :预编译语句对象返回值成功:SQLITE_OK失败:Error_code
BEGIN TRANSACTION:启动事务,启动事务在内存中操作 。
COMMIT:提交事务,将在内存中的操作提交到库中,并无法回滚 。
ROLLBACK:回滚事务,对启动事务后的操作进行回滚
注意事项:
- 隐式事务:每次DML都为一次事务
- 显式事务:当需要插入、更新、删除大量数据时,可优化处理时间
- 效率:事务对查询操作效率无影响,对表操作无影响
SHARED:数据库处于读状态,多连接可同时持有,阻塞提交。
RESERVED:数据库处于写状态,多连接仅持有一个,不阻塞读
PENDING:数据库写完成,即将提交。其他连接不可再获取SHARED锁,并等待所有SHARED释放,不阻塞读
EXCLUSIVE:数据库提交事务,阻塞读
锁类型 状态共享互斥性级别 SHARED读n阻塞提交1 RESERVED写1不阻塞读2 PENDING预备提交1不阻塞读3 EXCLUSIVE提交1阻塞读4
原因 :连接中存在锁状态为PENDING或者EXCLUSIVE时,其他线程申请锁失败。
解决方式:
int sqlite3_busy_timeout (sqlite3* db, int ms) :等待 ms后申请锁
int sqlite3_busy_handler (sqlite3 db, int callback_db(void, int), void* db) :循环次数等待锁释放
PRAGMA synchronous = FULL :每个关键磁盘操作后同步(sqlite3默认)
PRAGMA synchronous = NORMAL:每个关键磁盘操作的序列后同步 (sqlite2默认)
PRAGMA synchronous = OFF:不进行同步,传递给操作系统完成
同步机制安全性效率性 PRAGMA synchronous = FULL高低 PRAGMA synchronous = NORMAL中中 PRAGMA synchronous = OFF低高
sqlite3* sqlite3_open_database(char* db_name)
{
int rc = 0;
sqlite3* db;
if (db_name == NULL || strlen(db_name) == 0 ) {
return NULL;
}
rc = sqlite3_open(db_name, &db);
if (rc != SQLITE_OK) {
printf("Open db error :%s", sqlite3_errmsg(db));
return NULL;
}
return db;
}
void sqlite3_close_database(sqlite3 *db)
{
if (db != NULL) {
sqlite3_close(db);
}
}
int sqlite3_IsExist_table(sqlite3* db, char* table_name)
{
int rc = 0;
int ret = -1;
char sql_str[128] = {0};
char* err_msg = NULL;
sprintf(sql_str, "SELECT * FROM %s", table_name);
rc = sqlite3_exec(db, sql_str, 0, 0, &err_msg);
if (rc != SQLITE_OK) {
ret = -1; /* 表不存在*/
sqlite_free(err_msg);
} else {
ret = 0; /* 表存在*/
}
return ret;
}
int sqlite3_Create_table(sqlite3* db, char* table_name)
{
int rc = 0;
int ret = -1;
char sql_str[128] = {0};
char* err_msg = NULL;
/* 字段:ID Data RealDate*/
sprinft(sql_str, "CREATE TABLE IF NOT EXISTS %s (""ID INTEGER PRIMARY KEY AUTOINCREMENT, ""Data TEXT, ""RealDate DATETIME)", table_name);
rc = sqlite3_exec(db, sql_str, 0, 0, err_msg);
if (rc != SQLITE_OK) {
printf("Create table %s error :%s", table_name, err_msg);
sqlite3_free(err_msg);
ret = -1;
} else {
ret = 0;
}
return ret;
}
int sqlite3_Delete_table(sqlite3* db, char* table_name)
{
int rc = 0;
int ret = -1;
char sql_str[128] = {0};
char* err_msg = NULL;
sprinft(sql_str, "DROP TABLE %s", table_name);
rc = sqlite3_exec(db, sql_str, 0, 0, err_msg);
if (rc != SQLITE_OK) {
printf("Delete table %s error :%s", table_name, err_msg);
sqlite3_free(err_msg);
ret = -1;
} else {
ret = 0;
}
return ret;
}
int IsExist_cal_handle(void *flag, int argc, char** argv, char **colname)
{
int i = *(int*)(flag);
*(int *)(flag) = i + 1;
return 0;
}
int sqlite3_IsExist_field(sqlite3* db, char* table_name, char* data_buf)
{
int ret = -1;
int rc = 0;
char sql_str[128] = {0};
char* err_msg = NULL;
int flag = 0; /* 用于回调函数计数*/
sprintf(sql_str, "Select * FROM %s WHERE Data = '%s'", table_name, data_buf);
rc = sqlite3_exec(db, sql_str, IsExist_cal_handle, &flag, &err_msg);
if (rc != SQLITE_OK) { /* 查询失败*/
printf("Check data exist error :%s", err_msg);
return ret;
} else {
if (flag == 0 ) { /* 结果不存在*/
printf("Check data Not exist");
return flag;
} else { /* 结果存在*/
printf("Check data exist, num is %d", flag);
return flag;
}
}
}
/* 添加单条数据*/
int sqlite3_insert_single(sqlite3* db, char* table_name, char* data_buf)
{
int rc = 0;
int ret = -1;
char sql_str[128] = {0};
char* err_msg = NULL;
sprintf(sql_str, "INSERT INTO %s (ID, Data, RealDate) values(NULL, '%s', DATETIME('now', 'localtime'))", table_name, data_buf);
sqlite3_busy_timeout(db, 30*1000);
rc = sqlite3_exec(db, sql_str, 0, 0, &err_msg);
if (rc != SQLITE_OK) {
printf("Insert error :%s", err_msg);
sqlite3_free(err_msg);
ret = -1;
} else {
ret = 0;
}
return ret;
}
/* 添加多条数据, 利用显示事务机制*/
/* 依次插入数据,若出现插入失败,则回滚事务;若全部成功,则提交事务*/
int sqlite3_Insert_multidata(sqlite3* db, const char* table_name, char* *buff, int len)
{
int rc = 0;
int ret = -1;
int i = 0;
char sql_str[128] = {0};
char* err_msg = NULL;
rc = sqlite3_exec(db, "BEGIN;", 0, 0, 0);
if (rc != SQLITE_OK) {
return ret;
}
while (buff[i] != NULL && i < len) {
memset(sql_str, 0, sizeof(sql_str));
sprintf(sql_str, "INSERT INTO %s (ID, Data, RealDate) values(NULL, '%s', DATETIME('now', 'localtime'))", table_name, buff[i]);
sqlite3_busy_timeout(db, 30*1000);
rc = sqlite3_exec(db, sql_str, 0, 0, &err_msg);
if (rc != SQLITE_OK) {
printf("Insert No%d data error: %s", i + 1, err_msg);
sqlite3_free(err_msg);
break;
} else {
i++;
}
}
if (i < len) {
sqlite3_exec(db, "ROLLBACK;", 0, 0, 0);
ret = -1;
}
if (i == len) {
sqlite3_exec(db, "COMMIT;", 0, 0, 0);
ret = 0;
}
return ret;
}
int sqlite3_Insert_multidata_v2 (sqlite3* db, const char* table_name, char* *buff, int len)
{
int rc = 0;
int ret = -1;
int i = 0;
char sql_str[128] = {0};
char* err_msg = NULL;
sqlite3_stmt* stmt;
sprintf(sql_str, "INSERT INTO %s (ID, Data, RealDate) values (?, ?, DATETIME('now', 'localtime'))");
sqlite3_exec(db, "BEGIN;", 0, 0, 0);
rc = sqlite3_prepare(db, sql_str, strlen(sql_str), &stmt, 0);
if (rc != SQLITE_OK) {
return ret;
}
while(buff[i] != NULL && i < len) {
sqlite3_bind_int(stmt, 1, NULL);
sqlite3_bind_text(stmt, 2, buff[i], strlen(buff), NULL);
rc = sqlite3_step(stmt);
if ((rc != SQLITE_OK) && (rc != SQLITE_DONE)) {
break;
}
sqlite3_reset(stmt);
i++;
}
sqlite3_finalize(stmt);
if (i < len) {
sqlite3_exec(db, "ROLLBACK;", 0, 0, 0);
ret = -1;
}
if (i == len) {
sqlite3_exec(db, "COMMIT;", 0, 0, 0);
ret = 0;
}
return ret;
}
/* 更新单条数据*/
int sqlite3_Update_single(sqlite3* db, char* table_name, char* data_buf)
{
int rc = 0;
int ret = -1;
char sql_str[128] = {0};
char* err_msg = NULL;
sprintf(sql_str, "UPDATE %s SET RealDate = DATETIME('now', 'localtime') WHERE Data = '%s'", table_name, data_buf);
sqlite3_busy_timeout(db, 30*1000);
rc = sqlite3_exec(db, sql_str, 0, 0, &err_msg);
if (rc != SQLITE_OK) {
printf("Update error :%s", err_msg);
sqlite3_free(err_msg);
ret = -1;
} else {
ret = 0;
}
return ret;
}
/* 更新多条数据----事务法*/
int sqlite3_Update_multidata(sqlite3* db, const char* table_name, char* *buff, int len)
{
int rc = 0;
int ret = -1;
int i = 0;
char sql_str[128] = {0};
char* err_msg = NULL;
rc = sqlite3_exec(db, "BEGIN;", 0, 0, 0);
if (rc != SQLITE_OK) {
return ret;
}
while (buff[i] != NULL && i < len) {
memset(sql_str, 0, sizeof(sql_str));
sprintf(sql_str, "UPDATE %s SET RealDate = DATETIME('now', 'localtime') WHERE Data = '%s'", table_name, buff[i]);
sqlite3_busy_timeout(db, 30*1000);
rc = sqlite3_exec(db, sql_str, 0, 0, &err_msg);
if (rc != SQLITE_OK) {
printf("Update No%d data error: %s", i + 1, err_msg);
sqlite3_free(err_msg);
break;
} else {
i++;
}
}
if (i < len) {
sqlite3_exec(db, "ROLLBACK;", 0, 0, 0);
ret = -1;
}
if (i == len) {
sqlite3_exec(db, "COMMIT;", 0, 0, 0);
ret = 0;
}
return ret;
}
与添加和更新逻辑一样,参考上。
/* 回调函数, 返回单次执行结果*/
int exec_handle(void *data, int argc, char** argv, char **colname)
{
/* 计数器*/
int i = *(int *)(data);
*(int *)(data) = i + 1;
/* 取出结果*/
printf("NO.%d message: [%s] is [%s], [%s] is [%s]...", *(int*)(data), colname[0], colname[1], argv[0], argv[1]);
return 0;
}
/* exec查找完成无论是否有结果返回的都是SQLITE_OK, 所以判断查询结果得用计数器判断*/
int sqlite3_select_exec (sqlite *db, char *table_name)
{
char sql[MAX_INPUT] = {0};
char *err_msg = NULL;
int data = 0;
int ret;
sprintf(sql, "select * from %s Where ID > 10", table_name);
sqlite3_busy_timeout(db, 30*1000);
ret = sqlite3_exec(db, sql, exec_handle, &data, &err_msg);
if (ret != SQLITE_OK) {
/* 查询失败*/
printf("exec error is:%s\n", err_msg);
return -1;
} else {
if (data == 0) {
/* 查询成功,无查询结果*/
return 0;
} else {
/* 查询成功,有查询结果*/
return 1;
}
}
}
/* 执行sql, 将结果集的某一字段进行存储*/
int sqlite3_select_table(sqlite3* db, char* table_name)
{
int ret = 0;
char sql_str[MAX_INPUT] = {0};
char **res;
char **data;
int* Idata;
int row, col;
char* err_msg = NULL;
int i, j;
/* 执行sql, 判断执行结果*/
sprintf(sql_str, "select * from %s", table_name);
sqlite3_busy_timeout(db, 30*1000);
ret = sqlite3_get_table(db, sql_str, &res, &row, &col, &err_msg);
if (ret != SQLITE_OK) {
printf("error msg is %s", err_msg);
sqlite3_close_database(db);
sqlite3_free(err_msg);
return -1;
} else if (row == 0 || col == 0){
sqlite3_close_database(db);
return 0;
}
/* 初始化动态数组*/
Idata = (int*)malloc(sizeof(int) * (row + 1));
data = (char **)malloc(sizeof(char *) * (row + 1));
if (data == NULL) {
/* fails to malloc */
sqlite3_close_database(db);
return -1;
} else {
for (i = 0; i
int sqlite3_select_prepare(sqlite3* db)
{
int ret, rc, i, j;
int send_flag = 0;
int record_num;
char package_id[16] = {0};
int msg_len;
char db_name[32] = "/data/device_property";
char db_table[32] = "device";
char sql_str[128] = {0};
char* err_msg = NULL;
char* *res_data;
struct deviceNumber* *device;
sqlite3* db;
sqlite3_stmt *stmt;
record_num = sqlite3_get_record_num(db_name, db_table);
if (record_num == 0) {
return -1;
}
db = sqlite3_open_database(db_name);
if (db == NULL) {
return -1;
}
memset(sql_str, 0, sizeof(sql_str));
sprintf(sql_str, "select * from %s", db_table);
rc = sqlite3_prepare_v2(db, sql_str, sizeof(sql_str), &stmt, NULL);
if (rc != SQLITE_OK) {
DEBUG_INFO("[read sqlite3 device] Select error");
sqlite3_close_database(db);
return -1;
}
/* 申请内存*/
/* 字符串数组*/
res_data = (char**)malloc(sizeof(char*) * record_num);
if (res_data == NULL) {
DEBUG_INFO("[read sqlite3 device] malloc error");
sqlite3_close_database(db);
return -1;
}
for (i = 0; i < record_num; i++) {
res_data[i] = (char*)malloc(MAX_DEVICE_PROPERTY_SIZE);
if (res_data[i] == NULL) {
sqlite3_close_database(db);
DEBUG_INFO("[read sqlite3 device] malloc2 error");
return -1;
}
}
/* 结构体内存*/
device = (struct deviceNumber**)malloc(sizeof(struct deviceNumber) * record_num);
if (device == NULL) {
DEBUG_INFO("[read sqlite3 device malloc error]");
sqlite3_close_database(db);
return -1;
}
for (i = 0; i < record_num; i++) {
device[i] = (struct deviceNumber*)malloc(sizeof(struct deviceNumber));
if (device[i] == NULL) {
sqlite3_close_database(db);
DEBUG_INFO("[read sqlite3 device] malloc2 error");
return -1;
}
}
/* 获取数据*/
j = 0;
while(sqlite3_step(stmt) == SQLITE_ROW) {
strcpy(res_data[j] ,sqlite3_column_text(stmt, 2));
strcpy(device[j]->product_key, sqlite3_column_text(stmt,3));
strcpy(device[j]->device_number, sqlite3_column_text(stmt,4));
strcpy(device[j]->device_secret, sqlite3_column_text(stmt,5));
device[j]->reporting_center = sqlite3_column_int(stmt, 1);
if (res_data[j] != NULL) {
//printf("%s\n", res_data[j]);
} else {
printf("get history text error\n");
}
j ++;
}
/* 删除数据*/
if (record_num > 0 && send_flag > 0) {
ret = sqlite3_delete_device_property(db_table, record_num);
if (ret < 0) {
DEBUG_INFO("sqlite3_delete device property error");
}
}
/* 释放内存*/
for (i = 0; i < record_num; i++) {
if (res_data[i] != NULL) {
free(res_data[i]);
}
if (device[i] != NULL) {
free(device[i]);
}
}
if (res_data != NULL) {
free(res_data);
}
if (device != NULL) {
free(device);
}
sqlite3_finalize(stmt);
sqlite3_close_database(db);
return 0;
}
/* 按照容量进行管理*/
void sqlite3_check_database(char* db_name, char* table_name, int size)
{
int ret;
int rc;
int record_num;
struct stat fstat;
char sql_str[128] = {0};
char* err_msg = NULL;
sqlite3* db;
ret = stat(db_name, &fstat);
if (ret == 0) {
/*数据库文件存在 */
if (fstat.st_size >= size) /* st_size 单位为byte*/
{
record_num = sqlite3_get_record_num(db_name, table_name);
db = sqlite3_open_database(db_name);
if (db != NULL && record_num > 0) {
memset(sql_str, 0, sizeof(sql_str));
sprintf(sql_str, "DELETE FROM realtime WHERE ID in(SELECT ID FROM realtime LIMIT %d);", record_num*0.4);
sqlite3_busy_timeout(db, 30*1000);
rc = sqlite3_exec(db, sql_str, &err_msg);
if (rc != SQLITE_OK) {
printf("Delete error :%s", err_msg);
sqlite3_free(err_msg);
}
sqlite3_busy_timeout(db, 50*1000);
rc = sqlite3_exec(db, "Vacuum", 0, 0, &err_msg);
if (rc != SQLITE_OK) {
printf("error msg is %s", err_msg);
sqlite3_free(err_msg);
}
sqlite3_close_database(db);
}
} else {
/* 未达到清理容量*/
}
}
}
总结于:
Original: https://blog.csdn.net/rushBonez/article/details/127450057
Author: 不锈钢蚊子
Title: 【Linux c】 Sqlite3 操作与功能实现
原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/815854/
转载文章受原作者版权保护。转载请注明原作者出处!