#include "DB.h"gseMutex dbMutex=GSE_MUTEX_INIT;static int IsUpdateAll = 0;static int iDelCount = 0;const int FREE_NUM = 1000;static int needVacuum = 0;int DBOpen(dbClass *sqlDB, const char *sqlName, char *localhost, char *user, char *password){    if(sqlDB == NULL || sqlName == NULL || 0 == strlen(sqlName))    {        gseLog(L_ERROR,"NULL *p");        return -1;    }    int ret = 0;    if(sqlDB->type == SQL_SQLITE3)    {        gseMutexLock(&dbMutex);        sqlDB->db = (sqlite3 *)sqlDB->db;        localhost = NULL;        user = NULL;        password = NULL;        char sqlPathName[64]={0};        sprintf(sqlPathName, "%s", sqlName);        ret = sqlite3_open(sqlPathName, (struct sqlite3 **)&(sqlDB->db));        if(ret != SQLITE_OK)        {            gseLog(L_ERROR,"ret = %d, Cannot open db: %s",ret,sqlite3_errmsg(sqlDB->db));        }        gseMutexUnlock(&dbMutex);//      gseLog(L_INFO,"Open database");    }    else if(sqlDB->type == SQL_MYSQL)    {#ifdef MYSQL_DEFINE        sqlDB->db = (MYSQL *)sqlDB->db;        if(localhost == NULL || user == NULL || password == NULL)        {            gseLog(L_ERROR,"localhost user password is NULL");            return -1;        }        char creatDbStr[64]={0};        char useDbStr[64]={0};        sprintf(creatDbStr, "create database %s", sqlName);        sprintf(useDbStr, "use %s", sqlName);        sqlDB->db= mysql_init(NULL);        if (sqlDB->db== NULL)         {            gseLog(L_ERROR,"sqlOpen Error 1 %u: %s", mysql_errno(sqlDB->db), mysql_error(sqlDB->db));            exit(1);        }        if (mysql_real_connect(sqlDB->db, localhost, user, password, NULL, 0, NULL, 0) == NULL)        {            gseLog(L_ERROR,"sqlOpen Error 2 %u: %s", mysql_errno(sqlDB->db), mysql_error(sqlDB->db));            exit(1);        }        ret = mysql_query(sqlDB->db, creatDbStr);        if (ret)         {            gseLog(L_ERROR,"sqlOpen Error 3 %u: %s", mysql_errno(sqlDB->db), mysql_error(sqlDB->db));        }        ret = mysql_query(sqlDB->db, useDbStr);        if (ret)         {            gseLog(L_ERROR,"sqlOpen Error 4 %u: %s", mysql_errno(sqlDB->db), mysql_error(sqlDB->db));            exit(1);        }#endif    }    else    {        return -2;    }    return ret;}int DBClose(dbClass *sqlDB){    if(NULL == sqlDB || NULL == sqlDB->db)    {        return -1;    }    int ret = 0;    if(sqlDB->type == SQL_SQLITE3)    {        gseMutexLock(&dbMutex);        sqlDB->db = (sqlite3 *)sqlDB->db;        ret = sqlite3_close(sqlDB->db);        gseMutexUnlock(&dbMutex);    }    else if(sqlDB->type == SQL_MYSQL)    {#ifdef MYSQL_DEFINE        sqlDB->db = (MYSQL *)sqlDB->db;        mysql_close(sqlDB->db);#endif    }    else    {        return -2;    }//  gseLog(L_INFO, "Close database");    return ret;}int DBCreate(dbClass *sqlDB, const char *tableName, char *creatValueStr){    if(sqlDB == NULL || tableName == NULL || creatValueStr == NULL || NULL == sqlDB->db || 0 == strlen(creatValueStr))    {        gseLog(L_ERROR,"*db is NULL");        return -1;    }    char *errmsg = 0;    int ret = 0;    char createString[MAX_SQLSTR]={0};    if(strlen(creatValueStr) >= 1)        creatValueStr[strlen(creatValueStr) - 1]='\0';    sprintf(createString, "create table %s(%s)", tableName,creatValueStr);    //create table tb_cardId(cardID KEY, cardNum INT, endDate INT, password NVARCHAR(8))    memset(creatValueStr, 0, strlen(creatValueStr));    if(sqlDB->type == SQL_SQLITE3)    {        gseMutexLock(&dbMutex);        sqlDB->db = (sqlite3 *)sqlDB->db;        ret = sqlite3_exec(sqlDB->db,createString,NULL,NULL,&errmsg);        if(ret != SQLITE_OK)        {            gseLog(L_ERROR,"ret = %d, create table fail: %s", ret, errmsg);        }        else            gseLog(L_INFO, "create table %s success.", tableName);        sqlite3_free(errmsg);        gseMutexUnlock(&dbMutex);    }    else if(sqlDB->type == SQL_MYSQL)    {#ifdef MYSQL_DEFINE        sqlDB->db = (MYSQL *)sqlDB->db;        ret = mysql_query(sqlDB->db, createString);        if (ret)        {            gseLog(L_ERROR,"sqlCreate Error  %u: %s", mysql_errno(sqlDB->db), mysql_error(sqlDB->db));        }#endif    }    else    {        return -2;    }    return ret;}int DBInsert(dbClass *sqlDB, const char *tableName, char *valuesString){    if(sqlDB == NULL || tableName == NULL || valuesString == NULL || NULL == sqlDB->db || 0 == strlen(valuesString))    {        gseLog(L_ERROR,"*db is NULL");        return -1;    }    char *errmsg = 0;    int ret = 0;    char sqlInsetTable[MAX_SQLSTR]={0};    if(strlen(valuesString) >= 1)        valuesString[strlen(valuesString)-1]='\0';    sprintf(sqlInsetTable, "insert into %s values(%s)",tableName,valuesString);    //insert into tb_cardId values(1,124343223,20130101,'12345678');    memset(valuesString, 0, strlen(valuesString));    if(sqlDB->type == SQL_SQLITE3)    {        gseMutexLock(&dbMutex);        sqlDB->db = (sqlite3 *)sqlDB->db;        ret = sqlite3_exec(sqlDB->db,sqlInsetTable,NULL,NULL,&errmsg);        if(ret != SQLITE_OK)        {            gseLog(L_ERROR, "%s",sqlInsetTable);            gseLog(L_ERROR,"ret = %d, inset table fail: %s",ret,errmsg);        }        sqlite3_free(errmsg);        gseMutexUnlock(&dbMutex);    }    else if(sqlDB->type == SQL_MYSQL)    {#ifdef MYSQL_DEFINE        sqlDB->db = (MYSQL *)sqlDB->db;        ret = mysql_query(sqlDB->db, sqlInsetTable);        if (ret)        {            gseLog(L_ERROR,"sqlInsert Error 4 %u: %s", mysql_errno(sqlDB->db), mysql_error(sqlDB->db));        }#endif    }    else    {        ret = -2;    }    if(1 == IsUpdateAll && 0 != ret)    {        rollBack(sqlDB);        return ret;    }    return ret;}int DBUpdate(dbClass *sqlDB, const char *tableName, char *updateSetStr, char *updateWhereStr){    if(sqlDB == NULL || tableName == NULL || updateSetStr == NULL || updateWhereStr == NULL)    {        gseLog(L_ERROR,"*db is NULL");        return -1;    }    if(strlen(updateSetStr) >= 1)        updateSetStr[strlen(updateSetStr)-1]='\0';    if(strlen(updateWhereStr) >= 3)        updateWhereStr[strlen(updateWhereStr)-3]='\0';    char *errmsg = 0;    int ret = 0;    char sqlUpdateTable[MAX_SQLSTR]={0};    sprintf(sqlUpdateTable, "update %s set %s where %s", tableName, updateSetStr, updateWhereStr);    //update tb_cardID set cardNum=1243423,password='323443' where cardID=1 and endDate=2013;    memset(updateSetStr, 0, strlen(updateSetStr));    memset(updateWhereStr, 0, strlen(updateWhereStr));    if(sqlDB->type == SQL_SQLITE3)    {        gseMutexLock(&dbMutex);        sqlDB->db = (sqlite3 *)sqlDB->db;        ret = sqlite3_exec(sqlDB->db,sqlUpdateTable,NULL,NULL,&errmsg);        if(ret != SQLITE_OK)        {            gseLog(L_ERROR, "%s",sqlUpdateTable);            gseLog(L_ERROR,"ret = %d, update table fail: %s", ret, errmsg);        }        sqlite3_free(errmsg);        gseMutexUnlock(&dbMutex);    }    else if(sqlDB->type == SQL_MYSQL)    {#ifdef MYSQL_DEFINE        sqlDB->db = (MYSQL *)sqlDB->db;        ret = mysql_query(sqlDB->db, sqlUpdateTable);        if (ret)        {            gseLog(L_ERROR,"sqlUpdate Error 4 %u: %s", mysql_errno(sqlDB->db), mysql_error(sqlDB->db));        }#endif    }    else    {        ret = -2;    }    if(1 == IsUpdateAll && 0 != ret)    {        rollBack(sqlDB);        return ret;    }    return ret;}int DBSearch(dbClass *sqlDB, const char *tableName, char *searchWhereStr, char (*returnValue)[SEARCH_MAXLEN]){    if(sqlDB == NULL || tableName == NULL || searchWhereStr == NULL)    {        gseLog(L_ERROR,"*db is NULL");        return -1;    }    char *errmsg = 0;    int ret = 0;    char sqlSearchTable[MAX_SQLSTR]={0};    int i;    int nRow=0,nColumn=0;    if(strlen(searchWhereStr) >= 3)        searchWhereStr[strlen(searchWhereStr)-3]='\0';    sprintf(sqlSearchTable, "select * from %s where %s",tableName,searchWhereStr);    //select * from tb_cardID where cardNum=323435;    memset(searchWhereStr, 0, strlen(searchWhereStr));    if(sqlDB->type == SQL_SQLITE3)    {        gseMutexLock(&dbMutex);        sqlDB->db = (sqlite3 *)sqlDB->db;        char **azResult;        ret = sqlite3_get_table(sqlDB->db, sqlSearchTable, &azResult, &nRow, &nColumn,&errmsg);        if(ret != SQLITE_OK)        {            gseLog(L_ERROR, "%s",sqlSearchTable);            gseLog(L_ERROR,"ret = %d, search table fail: %s", ret, errmsg);        }//      gseLog(L_INFO,"row:%d column=%d",nRow, nColumn);        for(i=nColumn;i<(nRow+1)*nColumn;i++)        {//          printf("azResult[%d] = %s\n", i, azResult[i]);            if(azResult[i] != NULL && (*returnValue + SEARCH_MAXLEN*(i-nColumn)) != NULL)                strcpy(*returnValue + SEARCH_MAXLEN*(i-nColumn), azResult[i]);        }        sqlite3_free_table(azResult);        sqlite3_free(errmsg);        gseMutexUnlock(&dbMutex);        if(0 == nRow)            return 1;        else if(1 <= nRow)            return 0;    }    else if(sqlDB->type == SQL_MYSQL)    {#ifdef MYSQL_DEFINE        sqlDB->db = (MYSQL *)sqlDB->db;        ret = mysql_query(sqlDB->db, sqlSearchTable);        if (ret)        {            gseLog(L_ERROR,"sqlSearch Error 4 %u: %s\n", mysql_errno(sqlDB->db), mysql_error(sqlDB->db));        }        MYSQL_RES *result;        MYSQL_ROW row;        MYSQL_FIELD *field;        int num_fields;        result = mysql_store_result(sqlDB->db);        num_fields = mysql_num_fields(result);        while ((row = mysql_fetch_row(result)))        {            nRow++;            for(i = 0; i < num_fields; i++)            {                if (i == 0) {                    while(field = mysql_fetch_field(result)) {                        printf("%s ", field->name);                    }                    printf("\n");                }                printf("%s  ", row[i] ? row[i] : "NULL");            }        }        printf("\n");        mysql_free_result(result);        if(nRow == 0)            return 0;        else            return nRow;#endif    }    return -2;}int DBDelete(dbClass *sqlDB, const char *tableName, char *searchWhereStr){    if(sqlDB == NULL || tableName == NULL || searchWhereStr == NULL)    {        gseLog(L_ERROR,"*db is NULL");        return -1;    }    char *errmsg = 0;    int ret = 0;    char sqlDeleteTable[MAX_SQLSTR]={0};    if(strlen(searchWhereStr) >= 3)    {        searchWhereStr[strlen(searchWhereStr)-3]='\0';        sprintf(sqlDeleteTable, "delete from %s where %s", tableName, searchWhereStr);        memset(searchWhereStr, 0, strlen(searchWhereStr));    }    else if(strlen(searchWhereStr) == 0)        sprintf(sqlDeleteTable, "delete from %s", tableName);    //delete from tb_cardID where cardID=1;//  gseLog(L_DEBUG, "%s",sqlDeleteTable);    if(sqlDB->type == SQL_SQLITE3)    {        gseMutexLock(&dbMutex);        sqlDB->db = (sqlite3 *)sqlDB->db;        ret = sqlite3_exec(sqlDB->db,sqlDeleteTable,NULL,NULL,&errmsg);        if(ret != SQLITE_OK)        {            gseLog(L_ERROR,"ret = %d, delete table fail: %s", ret, errmsg);        }        sqlite3_free(errmsg);        needVacuum = 1;#if 0        if (iDelCount++ > FREE_NUM)        {            iDelCount = 0;            ret = sqlite3_exec(sqlDB->db,"vacuum",NULL,NULL,&errmsg);            if(ret != SQLITE_OK)            {                gseLog(L_ERROR,"ret = %d, vacuum fail: %s", ret, errmsg);            }            sqlite3_free(errmsg);        }#endif        gseMutexUnlock(&dbMutex);    }    else if(sqlDB->type == SQL_MYSQL)    {#ifdef MYSQL_DEFINE        sqlDB->db = (MYSQL *)sqlDB->db;        ret = mysql_query(sqlDB->db, sqlDeleteTable);        if (ret)        {            printf("sqlDelete Error %u: %s\n", mysql_errno(sqlDB->db), mysql_error(sqlDB->db));        }#endif    }    else    {        ret = -2;    }    if(1 == IsUpdateAll && 0 != ret)    {        rollBack(sqlDB);        return ret;    }    return ret;}int DBVacuum(dbClass *sqlDB){    if(sqlDB == NULL)    {        gseLog(L_ERROR,"*db is NULL");        return -1;    }    char *errmsg = 0;    int ret = 0;    if(sqlDB->type == SQL_SQLITE3 && 1 == needVacuum)    {        gseMutexLock(&dbMutex);        sqlDB->db = (sqlite3 *)sqlDB->db;        ret = sqlite3_exec(sqlDB->db,"vacuum",NULL,NULL,&errmsg);        if(ret != SQLITE_OK)        {            gseLog(L_ERROR,"ret = %d, vacuum fail: %s", ret, errmsg);        }        else        {            needVacuum = 0;        }        sqlite3_free(errmsg);        gseMutexUnlock(&dbMutex);    }    return ret;}int DBIndex(dbClass *sqlDB, const char *tableName, char *indexName, char *columnName){    if(sqlDB == NULL || tableName == NULL || indexName == NULL || columnName == NULL)    {        gseLog(L_ERROR,"SQLITE_Index NULL *p");         return -1;    }    int ret = 0;    char *errmsg = 0;    char sqlCreateIndex[MAX_SQLSTR]={0};    sprintf(sqlCreateIndex, "create index %s on %s(%s)", indexName, tableName, columnName);//  gseLog(L_DEBUG, "%s",sqlCreateIndex);    if(sqlDB->type == SQL_SQLITE3)    {        sqlDB->db = (sqlite3 *)sqlDB->db;        ret = sqlite3_exec(sqlDB->db,sqlCreateIndex,NULL,NULL,&errmsg);        if(ret != SQLITE_OK)        {            gseLog(L_ERROR,"ret = %d, index table fail: %s", ret, errmsg);        }        sqlite3_free(errmsg);    }    else if(sqlDB->type == SQL_MYSQL)    {#ifdef MYSQL_DEFINE        sqlDB->db = (MYSQL *)sqlDB->db;        ret = mysql_query(sqlDB->db, sqlCreateIndex);        if (ret)        {            printf("sqlIndex Error %u: %s\n", mysql_errno(sqlDB->db), mysql_error(sqlDB->db));        }#endif    }    return ret;}int DBGetIdKey(dbClass *sqlDB, const char *tableName, const char *keyName, char (*returnValue)[ID_KEYLEN]){    if(sqlDB == NULL || tableName == NULL || keyName== NULL)    {        gseLog(L_ERROR,"*db is NULL");        return -1;    }    char *errmsg = 0;    int ret = 0;    char sqlSearchTable[MAX_SQLSTR]={0};    int i;    int nRow=0,nColumn=0;    sprintf(sqlSearchTable, "select %s from %s", keyName, tableName);    if(sqlDB->type == SQL_SQLITE3)    {        gseMutexLock(&dbMutex);        sqlDB->db = (sqlite3 *)sqlDB->db;        char **azResult;        ret = sqlite3_get_table(sqlDB->db, sqlSearchTable, &azResult, &nRow, &nColumn,&errmsg);        if(ret != SQLITE_OK)        {            gseLog(L_ERROR, "%s",sqlSearchTable);            gseLog(L_ERROR,"ret = %d, search table fail: %s", ret, errmsg);        }//      gseLog(L_INFO,"row:%d column=%d",nRow, nColumn);        for(i=nColumn;i<(nRow+1)*nColumn;i++)        {//          printf("azResult[%d] = %s\n", i, azResult[i]);            if(azResult[i] != NULL && (*returnValue + ID_KEYLEN*(i-nColumn)) != NULL)                strcpy(*returnValue + ID_KEYLEN*(i-nColumn), azResult[i]);            else                break;        }        sqlite3_free_table(azResult);        sqlite3_free(errmsg);        gseMutexUnlock(&dbMutex);        if(nRow == 0)            return 0;        else            return nRow;    }    else if(sqlDB->type == SQL_MYSQL)    {#ifdef MYSQL_DEFINE        sqlDB->db = (MYSQL *)sqlDB->db;        ret = mysql_query(sqlDB->db, sqlSearchTable);        if (ret)        {            gseLog(L_ERROR,"sqlSearch Error 4 %u: %s\n", mysql_errno(sqlDB->db), mysql_error(sqlDB->db));        }        MYSQL_RES *result;        MYSQL_ROW row;        MYSQL_FIELD *field;        int num_fields;        result = mysql_store_result(sqlDB->db);        num_fields = mysql_num_fields(result);        while ((row = mysql_fetch_row(result)))        {            nRow++;            for(i = 0; i < num_fields; i++)            {                if (i == 0) {                    while(field = mysql_fetch_field(result)) {                        printf("%s ", field->name);                    }                    printf("\n");                }                printf("%s  ", row[i] ? row[i] : "NULL");            }        }        printf("\n");        mysql_free_result(result);        if(nRow == 0)            return 0;        else            return nRow;#endif    }    return -2;}int DBExecSql(dbClass *sqlDB, const char *sqlStr){    if(sqlDB == NULL || sqlStr == NULL)    {        gseLog(L_ERROR,"*db is NULL");        return -1;    }    char *errmsg = 0;    int ret = 0;    if(sqlDB->type == SQL_SQLITE3)    {        sqlDB->db = (sqlite3 *)sqlDB->db;        ret = sqlite3_exec(sqlDB->db,sqlStr,NULL,NULL,&errmsg);        if(ret != SQLITE_OK)        {            gseLog(L_ERROR,"ret = %d, exec sql(%s) fail: %s", ret, sqlStr, errmsg);        }        sqlite3_free(errmsg);    }    else if(sqlDB->type == SQL_MYSQL)    {    }    return ret;}int dbAddColumn(char *createString, const char *keyName, const char *keyClass){    if(createString == NULL || keyName == NULL || keyClass == NULL)        return -1;    char tmpKey[128];    sprintf(tmpKey, "%s %s,", keyName, keyClass);    strcat(createString, tmpKey);    return 0;}int dbInsertColumn(char *insertString, char *value){    if(insertString == NULL || value == NULL)        return -1;    strcat(insertString,value);    strcat(insertString,",");    return 0;}int dbUpdateSet(char *updateSetStr, const char *setKey, char *setValue){    if(updateSetStr == NULL || setKey == NULL || setValue == NULL)        return -1;    char tmpStr[128]={0};    sprintf(tmpStr, " %s=%s,", setKey, setValue);    strcat(updateSetStr, tmpStr);    return 0;}int dbSearchWhere(char *searchWhereStr, const char *whereKey, char *wherevalue){    if(searchWhereStr == NULL || whereKey == NULL || wherevalue == NULL)        return -1;    char tmpStr[128]={0};    sprintf(tmpStr, " %s=%s and", whereKey, wherevalue);    strcat(searchWhereStr , tmpStr);    return 0;}int setFlagIsUpdateAll(dbClass *sqlDB, int n){    int ret= 0;    IsUpdateAll = n;    if(-1 == IsUpdateAll)        ret = rollBack(sqlDB);    return ret;}int getFlagIsUpdateAll(){    return IsUpdateAll;}int beginExclusive(dbClass *sqlDB){    int ret=0;    char *errmsg = 0;    sqlDB->db = (sqlite3 *)sqlDB->db;    ret = sqlite3_exec(sqlDB->db,"BEGIN EXCLUSIVE", NULL,NULL, &errmsg);        if(ret != SQLITE_OK)    {        gseLog(L_ERROR,"ret = %d, BEGIN EXCLUSIVE: %s", ret, errmsg);    }    sqlite3_free(errmsg);    return ret;}int commitSQL(dbClass *sqlDB){    int ret=0;    char *errmsg = 0;    sqlDB->db = (sqlite3 *)sqlDB->db;    ret=sqlite3_exec(sqlDB->db,"COMMIT", NULL,NULL, &errmsg);    if(ret != SQLITE_OK)    {        gseLog(L_ERROR,"ret = %d, COMMIT: %s", ret, errmsg);    }    sqlite3_free(errmsg);    return ret;}int rollBack(dbClass *sqlDB){    int ret=0;    char *errmsg = 0;    sqlDB->db = (sqlite3 *)sqlDB->db;    ret=sqlite3_exec(sqlDB->db,"ROLLBACK", NULL,NULL, &errmsg);    if(ret != SQLITE_OK)    {        gseLog(L_ERROR,"ret = %d, ROLLBACK: %s", ret, errmsg);    }    sqlite3_free(errmsg);    return ret;}#ifdef MAIN_TESTvoid main(){    char createvalueStr[MAX_SQLSTR]={0};    char insertString[MAX_SQLSTR]={0};    char updateSetStr[MAX_SQLSTR]={0};    char searchWhereStr[MAX_SQLSTR]={0};    char *dbFileName="lkdb";    char *tableName="tb";    char *colList[]={"ID","names","sexsex"};    int ret=0;    timeLog();    dbClass sqlDB;    sqlDB.type = SQL_SQLITE3;    DBOpen(&sqlDB, dbFileName, NULL, NULL, NULL);//  sqlDB.type = SQL_MYSQL;//  DBOpen(&sqlDB, dbFileName, "localhost", "root", "mima");#if 1    dbAddColumn(createvalueStr, colList[0], PRIMAY_KEY);    dbAddColumn(createvalueStr, colList[1], NVARCHAR_32);    dbAddColumn(createvalueStr, colList[2], NVARCHAR_32);    DBCreate(&sqlDB, tableName, createvalueStr);    dbInsertColumn(insertString, "1");    dbInsertColumn(insertString, "'y_jg'");    dbInsertColumn(insertString, "'man'");    DBInsert(&sqlDB,tableName,insertString);    dbInsertColumn(insertString, "2");    dbInsertColumn(insertString, "'lk'");    dbInsertColumn(insertString, "'man'");    DBInsert(&sqlDB,tableName,insertString);    dbInsertColumn(insertString, "3");    dbInsertColumn(insertString, "'fqq'");    dbInsertColumn(insertString, "'woman'");    DBInsert(&sqlDB,tableName,insertString);    dbInsertColumn(insertString, "4");    dbInsertColumn(insertString, "'xf'");    dbInsertColumn(insertString, "'man'");    DBInsert(&sqlDB,tableName,insertString);#endif    dbUpdateSet(updateSetStr,"names", "'xxxx'");    dbSearchWhere(searchWhereStr,"id","3");    DBUpdate(&sqlDB,tableName,updateSetStr,searchWhereStr);    DBClose(&sqlDB);    timeLog();}#endif