嵌入式 sqlite3数据库创建、插入、更新、查询、删除、多线程等C编程示例以及多并发完成

/*
Author : kj
Time : 2014-09-07
Function :
joseph cvr manage by sqlite3 db
*/


#include <fcntl.h>
#include <stdio.h>
#include <ctype.h>  
#include <errno.h>
#include <stdlib.h>
#include <string.h>
#include <limits.h>  
#include <unistd.h>
#include <signal.h>
#include <dirent.h>
#include <pthread.h>


#include <asm/types.h>
#include <arpa/inet.h>


#include <sys/vfs.h>
#include <sys/time.h>
#include <sys/wait.h>
#include <sys/stat.h> 
#include <sys/ioctl.h>
#include <sys/types.h>
#include <sys/socket.h>
#include <sys/utsname.h> 


#include <netdb.h>  
#include <net/if.h>
#include <netinet/in.h>
#include <net/route.h>
#include <net/if_arp.h>


#include <linux/fs.h>
#include <linux/sockios.h>   
#include <linux/netlink.h>
#include <linux/rtnetlink.h>


#include <netinet/in.h>
#include <netinet/ip.h>   
#include <netinet/ether.h>
#include <netinet/ip_icmp.h>


#include "sqlite3.h"




#define JDB_SQLITE3 sqlite3
#define JDB_CVR_NAME "JIPNC_CVR.DB"
#define JDB_CVR_CONFIG_TABLE_NAME "JIPNC_CVR_CFG"
#define JDB_CVR_RECORD_TABLE_NAME "JIPNC_CVR_RECORD"


#define JDB_CVR_CONFIG_INTACT_THRESHOLD 28
#define JDB_CVR_DB_DEFAULT_VALUES "0"
#define JDB_CVR_CONFIG_PKEY_ONE "cWeekDay"
#define JDB_CVR_CONFIG_PKEY_TWO "cSection"
#define JDB_CVR_RECORD_PKEY "cStSeconds"


/*
the member of cvr config table
*/
typedef struct Joseph_Db_Cvr_Config
{
char cWeekDay[4];//range 1 ~ 7 not null primary key
char cSection[4];//range 1 ~ 4 not null primary key
char cStpSection[64];//start time per section ,contaion hour minute second
char cEtpSection[64];//end time per section ,contaion hour minute second
char cCvrType[4];//0~? 0 ~ plan, 1 ~ md alarm;
char cGDayPlan[4];//0 ~ close global;1 ~ open
char cCvrStaus[4];//0 ~ novalid;1 ~ valid

}JOSEPH_DB_CVR_CONFIG;


#define JOSEPH_CREATE_CVRCONFFIG_CMD "create table IF NOT EXISTS %s(\
%s char(4) NOT NULL, \
%s char(4) NOT NULL, \
%s char(64) default 0, \
%s char(64) default 0, \
%s char(4) default 0, \
%s char(4) default 0, \
%s char(4) default 0, \
constraint PK_WS primary key(cWeekDay,cSection)\
)"


#define JOSEPH_DB_CVRCONFIG_FIELD JDB_CVR_CONFIG_TABLE_NAME, \
 "cWeekDay", \
 "cSection", \
 "cStpSection", \
 "cEtpSection", \
 "cCvrType", \
 "cGDayPlan", \
 "cCvrStaus"


#define JOSEPH_INSERT_CVRCONFIG_DEFAULT_CMD "insert into %s(%s,%s) values(\"%s\", \"%s\")"




#define JOSEPH_UPDATE_CVRCONFIG_CMD "update %s set\
cStpSection = \"%s\", \
cEtpSection = \"%s\", \
cCvrType = \"%s\", \
cGDayPlan = \"%s\", \
cCvrStaus = \"%s\" \
where %s = %s \
and %s = %s\
"


#define JOSEPH_SELECT_CVRCONFIG_CMD "select * from %s\
where %s = %s\
and %s = %s\
"


#define JOSEPH_SELECT_CFG_ONE_CMD "select %s from %s\
where %s = %s\
and %s = %s\
"


#define JOSEPH_SELECT_CFG_TWO_CMD "select %s,%s from %s\
where %s = %s\
and %s = %s\
"




/*
the member of cvr record table
*/
typedef struct Joseph_Db_Cvr_Record
{
char cStSeconds[64];//start total second ,that relative to 1970-01-01 08:00:00
char cEtSeconds[64];//start total second ,that relative to 1970-01-01 08:00:00
char cDirFileName[256];//the cvr record file dir and name
char cFileSpShift[4];//real cvr file start addr shift in large file
char cFileEpShift[4];//real cvr file end addr shift in large file
char cFileStatus[4];//the file is exist or not ,check when client requst history record list,and update sql values
char cFileIntact[4];// make sure avi or mp4 intact

}JOSEPH_DB_CVR_RECORD;


#define JOSEPH_CREATE_CVRRECORD_CMD "create table IF NOT EXISTS %s(\
%s char(64) NOT NULL, \
%s char(64) NOT NULL, \
%s char(256) NOT NULL default 0, \
%s char(4) default 0, \
%s char(4) default 0, \
%s char(4) default 0, \
%s char(4) default 0 \
)"


#define JOSEPH_DB_CVRRECORD_FIELD JDB_CVR_RECORD_TABLE_NAME, \
 "cStSeconds", \
 "cEtSeconds", \
 "cDirFileName", \
 "cFileSpShift", \
 "cFileEpShift", \
 "cFileStatus", \
 "cFileIntact"
 
#define JOSEPH_INSERT_CVRRECORD_CMD "insert into %s values(\"%s\", \"%s\", \"%s\", \"%s\", \"%s\", \"%s\", \"%s\")"


#define JOSEPH_UPDATE_CVRRECORD_CMD "update %s set\
cEtSeconds = \"%s\", \
cFileEpShift = \"%s\", \
cFileStatus = \"%s\", \
cFileIntact = \"%s\" \
where cStSeconds = %s \
"

#define JOSEPH_SELECT_CVRRECORD_CMD "select * from %s\
where %s = %s \
"


#define JOSEPH_SELECT_CRD_ONE_CMD "select %s from %s\
where %s = %s \
"


#define JOSEPH_SELECT_CRD_TWO_CMD "select %s,%s from %s\
where %s = %s \
"




#define JOSEPH_UPDATE_ONE_CMD "update %s set\
%s = \"%s\" \
where %s = %s \
and %s = %s \
"


#define JOSEPH_UPDATE_TWO_CMD "update %s set\
%s = \"%s\", \
%s = \"%s\" \
where %s = %s \
and %s = %s \
"


#define JOSEPH_DELETE_CMD "delete from %s where %s = %s"


#define JOSEPH_SELECT_CMD "select * from %s"


JDB_SQLITE3 *GP_JosephDB = NULL;




typedef struct {
pthread_t thread;
int ( *func)(void *);//function pointer *alter by kj
void *param;//send the param to the function *alter by kj
char lock;
} tsk_args;




void *tmp_func(void *in)
{
tsk_args tmp,*par;
par=in;
memcpy(&tmp,in,sizeof(tsk_args));
par->lock = 0;
tmp.func(tmp.param);
pthread_exit(NULL);//father thread exit *alter by kj 


return NULL;
}


void run_pthread(void *func,void *param)
{
tsk_args tmp_pa;
pthread_t thread;
pthread_attr_t     attr;
pthread_attr_init(&attr); 
pthread_attr_setdetachstate(&attr, PTHREAD_CREATE_DETACHED);
tmp_pa.func = func;
tmp_pa.param = param;
tmp_pa.lock = 1;
pthread_create(&thread, &attr, (void *)&tmp_func, (void *)&tmp_pa);
while(tmp_pa.lock){};
return;

}






/*
Author : kj
Time : 2014-09-07
Function :
get db point of cvr.db
*/
JDB_SQLITE3 *Joseph_Get_DB_Point(void)
{
return GP_JosephDB;
}


/*
Author : kj
Time : 2014-09-07
Function :
Open cvr db ,no the db will create auto
R_Value:
0 ~ succeed
-1 ~ failed
*/
int Joseph_Open_DB(JDB_SQLITE3 *db_in)
{
int Qy_Ret = 0;


#if 0
Qy_Ret = sqlite3_open(JDB_CVR_NAME,&GP_JosephDB);
if(Qy_Ret)
{
printf("%s:[%d] Can't open database: %s\n",\
__FUNCTION__,__LINE__,JDB_CVR_NAME);


sqlite3_close(GP_JosephDB);
Qy_Ret = -1;
return Qy_Ret;
}
else if(Qy_Ret == 0)
{
printf("%s:[%d] Open database:%s Succeed !\n",\
__FUNCTION__,__LINE__,JDB_CVR_NAME);
}


#else


sqlite3_initialize();


Qy_Ret = sqlite3_open_v2(JDB_CVR_NAME, &GP_JosephDB, \
SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_FULLMUTEX, NULL);
 
if(Qy_Ret)
{
printf("%s:[%d] Can't open database: %s\n",\
__FUNCTION__,__LINE__,JDB_CVR_NAME);
 
sqlite3_close(GP_JosephDB);
Qy_Ret = -1;
return Qy_Ret;
}
else if(Qy_Ret == 0)
{
printf("%s:[%d] Open database:%s Succeed !\n",\
__FUNCTION__,__LINE__,JDB_CVR_NAME);
}


#endif
db_in = GP_JosephDB;

return Qy_Ret;
}




/*
Author : kj
Time : 2014-09-07
Function :
close db
R_Value:
0 ~ succeed
-1 ~ failed
*/
int Joseph_Close_DB(JDB_SQLITE3 *db_in)
{
int Qy_Ret = 0;


#if 0
Qy_Ret = sqlite3_close(GP_JosephDB);
if(Qy_Ret)
{
printf("%s:[%d] Can't Close database: %s\n", \
__FUNCTION__,__LINE__,JDB_CVR_NAME);