多线程操作数据库

/*******sqlite.c******/
#include <sys/types.h>
#include <sys/socket.h>
#include <linux/in.h>
#include <linux/un.h>
#include <string.h>
#include <signal.h>
#include <stdio.h>
#include <stdlib.h>
#include <malloc.h>
#include <errno.h>
#include <unistd.h>
#include <unistd.h>
#include <signal.h>
#include <sys/ioctl.h>
#include <pthread.h>
#include "sqlite3.h"
#include <sys/time.h>
#include <math.h>
#include <sys/mman.h>
#include <sys/stat.h>
#include <fcntl.h>
#include <net/if.h>
#include <net/if_arp.h>
#include <termios.h>
#include "eventlog.h"


//****************db lock
pthread_mutex_t *foreventdb;

void SendToEventLogDb(int type,float value,long int ts)
{
    pthread_mutex_lock(foreventdb);

    switch(type)
    {
        case 10 : SaveToEventLogDb(type,value,ts);break;
        case 20 : SaveToEventLogDb(type,value,ts);break;
        default : break;        
    }
    printf("start unlock!\n");
    pthread_mutex_unlock(foreventdb);
    printf("end unlock!\n");


}


void * sqlite_ser_thread()
{
 while(1)
    {
     sleep(1);
     SendToEventLogDb(10,10.1,10);
     printf("save 11111111 success!\n");
   }
}
void * save_ser_thread()
{
 while(1)
    {
     sleep(1);
     SendToEventLogDb(20,20.2,20);
     printf("save 22222222 success!\n");
   }
}

static void pqm_db_init()
{
    //The following is added by wzl 2016-03-03  
     InitEventLogDb();
    /********end*********/
}
void main()
{
     pthread_t sqlite_thread,save_thread;
     foreventdb=malloc(sizeof(pthread_mutex_t));
    pthread_mutex_init(foreventdb,NULL);

     pqm_db_init();    
    pthread_create(&sqlite_thread, NULL, sqlite_ser_thread, NULL);
    pthread_create(&save_thread, NULL, save_ser_thread, NULL);
     pthread_join(sqlite_thread,NULL);

}
/******************/


/*******eventlog.h********/
//#ifndef __cplsuplus
//#define __cplsuplus
//#endif
#ifdef __cplsuplus
extern "C" {
#endif

    extern int InitEventLogDb();
    extern int SaveToEventLogDb(int db1,int db2,long int db3);

#ifdef __cplsuplus
}
#endif

/**************************/

/*********eventlog.c**********/
#include <sys/types.h>
#include <sys/socket.h>
#include <linux/in.h>
#include <linux/un.h>
#include <string.h>
#include <signal.h>
#include <stdio.h>
#include <stdlib.h>
#include <errno.h>
#include <unistd.h>
#include <sys/ioctl.h>
#include <sys/select.h>
#include <sys/time.h>
#include <unistd.h>
#include <time.h>
#include <string.h>
#include "sqlite3.h"
 #include "eventlog.h"

#define db_max 300        //最多可以保存多少个录波文件

static char sql[128];
static sqlite3 *eventlogdb = NULL;
static int result, nRow, nColumn;
static char * errmsg = NULL;
static char **dbResult;
static sqlite3_stmt *stmt;

static char *s= NULL;
static time_t current;


//****************db lock
//pthread_mutex_t *foreventdb;



//初始化录波数据库
 int InitEventLogDb()
{
    int i;

     //foreventdb=malloc(sizeof(pthread_mutex_t));
    // pthread_mutex_init(foreventdb,NULL);


    //打开数据库

    result = sqlite3_open("/pqm/pqm/eventlog.db", &eventlogdb);
    if(result != SQLITE_OK) {
        return -1;
    }

    //创建数据库, 保存录波文件的名字
    result = sqlite3_exec(eventlogdb, "create table EventlogTable(ID integer primary key autoincrement,type,value,ts,time);", NULL, NULL, &errmsg);
    if(result != SQLITE_OK)
        return -1;

    //初始化
    result = sqlite3_exec(eventlogdb, "BEGIN", 0, 0, &errmsg);
    for(i = 1; i <= db_max; i++)
    {
        sprintf(sql,"insert into EventlogTable values(%d,'-','-','-','time')",i);
        result = sqlite3_exec(eventlogdb, sql, 0, 0, &errmsg);
        if(result != SQLITE_OK)
            return -1;
    }
    result = sqlite3_exec(eventlogdb, "COMMIT", 0, 0, &errmsg);

    //创建数据库, 保存索引
    result = sqlite3_exec(eventlogdb, "create table IndexTable(id integer primary key autoincrement, val);", NULL, NULL, &errmsg);
    if(result != SQLITE_OK)
        return -1;
    result = sqlite3_exec(eventlogdb, "insert into IndexTable values(1,1);", NULL, NULL, &errmsg);
    if(result != SQLITE_OK)
        return -1;

       sqlite3_close(eventlogdb);
    return 0;
}

//用于获取当前的索引值
static int GetIndex(int * index)
{
    result = sqlite3_get_table(eventlogdb,"select * from IndexTable", &dbResult, &nRow, &nColumn, &errmsg);
    if(SQLITE_OK == result)
    {
        *index = atoi(dbResult[3]);
        return 0;
    }
    else
    {    
       //pthread_mutex_unlock(foreventdb);
       printf("Getindex error\n");
       return -1;
    }

}

//用于保存当前的索引值
static int PutIndex(int index)
{
    sprintf(sql, "update IndexTable set val=%d where id=1", index);
    result = sqlite3_exec(eventlogdb, sql, NULL, NULL, &errmsg);
    if(result != SQLITE_OK)
    {
        //pthread_mutex_unlock(foreventdb);
        printf("Putindex error\n");
        return -1;
    }
    return 0;
}


//保存录波文件名, 并判断是否需要删除最早的一个录波文件
int SaveToEventLogDb(int db1,float db2,long int db3)
{
//    pthread_mutex_lock(foreventdb);
    int CurrentIndex;
    time(&current);
    s=ctime(&current);
    int i=0;
    while(s[i]!='\n')
      i++;
    s[i]='\0';
     result = sqlite3_open("/pqm/pqm/eventlog.db", &eventlogdb);
    if(result != SQLITE_OK) {
    //    pthread_mutex_unlock(foreventdb);
         printf("open error!\n");
        return -1;
    }

    GetIndex(&CurrentIndex);    //获取索引和总量
    printf("CurrentIndex=%d\n", CurrentIndex);


    //保存录波文件名
       sprintf(sql,"update EventlogTable set type=%d,value=%f,ts=%ld,time='%s' where id=%d;",db1,db2,db3,s,CurrentIndex);
    result=sqlite3_exec(eventlogdb, sql, NULL, NULL, &errmsg);
    if(result!=SQLITE_OK)
    {  // pthread_mutex_unlock(foreventdb);
         printf("update error!\n");
        return -1;
    }

    //更新索引
    if(CurrentIndex == db_max)
        CurrentIndex = 1;
    else
        CurrentIndex++;
    PutIndex(CurrentIndex);

    printf("start ..... 1111111111111111111\n");
    if(dbResult)
    {
     sqlite3_free_table(dbResult);
    }
    printf("end ..... 1111111111111111111\n");

    //pthread_mutex_unlock(foreventdb);
    sqlite3_close(eventlogdb);

    return 0;

}


/******************************/

/*************Makefile**************/
CG        = arm-linux-g++
CC      = arm-linux-gcc
objs := eventlog.c

lubodb.so: $(objs)
    ${CC} $(objs) -fPIC -shared -o libeventlogdb.so -lz

test:
    arm-linux-gcc -o test test.c -L . -leventlogdb -lsqlite3 

/************************************/