设为首页收藏本站
查看: 36|回复: 0

[PHP] linuxCsqlite3mysql_MySQL

[复制链接]

论坛元老

Rank: 6Rank: 6

积分
34274
主题
17031
UID
1347
M币
67
贡献
17176

  • 发表于 2017-5-12 16:11:00 | 显示全部楼层 |阅读模式
    /* ********** gcc main.c -lsqlite3 -I/usr/include/mysql -L/usr/lib/mysql -lmysqlclient ************ SQLITE_OK = 0; 返回成功 SQLITE_ERROR = 1; SQL错误或错误的数据库 SQLITE_INTERNAL = 2; An internal logic error in SQLite SQLITE_PERM = 3; 拒绝访问 SQLITE_ABORT = 4; 回调函数请求中断 SQLITE_BUSY = 5; 数据库文件被锁 SQLITE_LOCKED = 6; 数据库中的一个表被锁 SQLITE_NOMEM = 7; 内存分配失败 SQLITE_READOnLY= 8; 试图对一个只读数据库进行写操作 SQLITE_INTERRUPT = 9; 由sqlite_interrupt()结束操作 SQLITE_IOERR = 10; 磁盘I/O发生错误 SQLITE_CORRUPT = 11; 数据库磁盘镜像畸形 SQLITE_NOTFOUND = 12; (Internal Only)表或记录不存在 SQLITE_FULL = 13; 数据库满插入失败 SQLITE_CANTOPEN = 14; 不能打开数据库文件 SQLITE_PROTOCOL = 15; 数据库锁定协议错误 SQLITE_EMPTY = 16; (Internal Only)数据库表为空 SQLITE_SCHEMA = 17; 数据库模式改变 SQLITE_TOOBIG = 18; 对一个表数据行过多 SQLITE_COnSTRAINT= 19; 由于约束冲突而中止 SQLITE_MISMATCH = 20; 数据类型不匹配 SQLITE_MISUSE = 21; 数据库错误使用 SQLITE_NOLFS = 22; 使用主机操作系统不支持的特性 SQLITE_AUTH = 23; 非法授权 SQLITE_FORMAT = 24; 辅助数据库格式错误 SQLITE_RANGE = 25; 2nd parameter to sqlite_bind out of range SQLITE_NOTADB = 26; 打开的不是一个数据库文件 SQLITE_ROW = 100; sqlite_step() has another row ready SQLITE_DOnE= 101; sqlite_step() has finished executing *************************************************************************************************** */ #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;itype == 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 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;itype == 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 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_TEST void 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
    回复

    使用道具 举报

    您需要登录后才可以回帖 登录 | 立即注册

    本版积分规则

    在我站开通SVIP可同时获得17个站点VIP资源 立即登录 立即注册
    快速回复 返回顶部 返回列表