////////////////////////////////////////////////////////////////////////////////////////////////////// // Function:Table is or not exist // Input: inTabName: Table's name // Output: // Return: ErrorCode int CSQLite::TableExists( const char * inTabName ) { char szSQL[128] = ""; sprintf( szSQL,"select count(*) from sqlite_master where type='table' and name='%s'",inTabName ); for( unsigned int m = 0; m < m_nLimitNum; m++ ) { m_nState = sqlite3_prepare( m_pDb, szSQL, -1, &m_pStmt, NULL ); if ( SQLITE_OK == m_nState ) { break; } else if( SQLITE_BUSY == m_nState ) { if ( m_nLimitNum == m+1 ) { m_strErrInfo = sqlite3_errmsg( m_pDb ); goto __Exit; } //等待指定时间间隔继续执行 sqlite3_busy_handler( m_pDb, NULL,NULL ); Sleep( SLEEPTIME ); continue; } else { m_strErrInfo = sqlite3_errmsg( m_pDb ); goto __Exit; } } for( unsigned int m = 0; m < m_nLimitNum; m++ ) { m_nState = sqlite3_step( m_pStmt ); if ( SQLITE_ROW == m_nState ) { if ( sqlite3_column_int( m_pStmt, 0 ) > 0 ) { m_nState = DEF_TABLEEXIST; } else { m_nState = DEF_TABLENOTEXIST; } break; } else if ( SQLITE_BUSY == m_nState ) { if ( m_nLimitNum == m+1 ) { m_strErrInfo = sqlite3_errmsg( m_pDb ); goto __Exit; } sqlite3_busy_handler( m_pDb, NULL,NULL ); Sleep( SLEEPTIME ); } else { m_strErrInfo = sqlite3_errmsg( m_pDb ); goto __Exit; } } __Exit: if ( NULL != m_pStmt ) { sqlite3_finalize( m_pStmt ); m_pStmt = NULL; } return m_nState; }
////////////////////////////////////////////////////////////////////////////////////////////////////// // Function: Execute transaction // Input: inTransaction 输入的事务 // Output: // Return: ErrorCode int CSQLite::ExecDML(const vector<string> &inTransaction) { unsigned int nStrNum = (unsigned int)inTransaction.size(); if ( 0 == nStrNum ) { return SQLITE_OK; } //以下是事务执行,事务必须成功或者回滚 for( unsigned int m = 0; m < m_nLimitNum; m++ ) { m_nState = sqlite3_exec( m_pDb, STRBEGINTRANS, NULL, NULL, NULL ); if ( SQLITE_OK == m_nState ) { break; } else if ( SQLITE_BUSY == m_nState ) { if ( m_nLimitNum == m+1 ) { m_strErrInfo = sqlite3_errmsg( m_pDb ); return m_nState; } //等待指定时间间隔继续执行 sqlite3_busy_handler( m_pDb, NULL,NULL ); Sleep( SLEEPTIME ); } else { m_strErrInfo = sqlite3_errmsg(m_pDb); return m_nState; } } //执行相关SQL语句 for ( unsigned int i = 0; i < nStrNum; i++ ) { for( unsigned int m = 0; m < m_nLimitNum; m++ ) { m_nState = sqlite3_exec( m_pDb, inTransaction[i].c_str(), NULL, NULL, NULL ); if ( SQLITE_OK == m_nState ) { break; } else if( SQLITE_BUSY == m_nState ) { if ( m_nLimitNum == m+1 ) { m_strErrInfo = sqlite3_errmsg( m_pDb ); sqlite3_exec( m_pDb, STRROLLBACK, NULL, NULL, NULL ); return m_nState; } //等待指定时间间隔继续执行 sqlite3_busy_handler( m_pDb, NULL,NULL ); Sleep( SLEEPTIME ); } else { m_strErrInfo = sqlite3_errmsg( m_pDb ); sqlite3_exec( m_pDb, STRROLLBACK, NULL, NULL, NULL ); return m_nState; } } } //提交事务 for( unsigned int m = 0; m < m_nLimitNum; m++ ) { m_nState = sqlite3_exec( m_pDb, STRCOMMITTRANS, NULL, NULL, NULL ); if ( SQLITE_OK == m_nState ) { break; } else if( SQLITE_BUSY == m_nState ) { if ( m_nLimitNum == m+1 ) { m_strErrInfo = sqlite3_errmsg( m_pDb ); return m_nState; } //等待指定时间间隔继续执行 sqlite3_busy_handler( m_pDb, NULL,NULL ); Sleep( SLEEPTIME ); } else { m_strErrInfo = sqlite3_errmsg( m_pDb ); //调整执行顺序 modified by yinyong 2007.7.17 sqlite3_exec( m_pDb, STRROLLBACK, NULL, NULL, NULL ); return m_nState; } } return m_nState; }
////////////////////////////////////////////////////////////////////////////////////////////////////// // Function:获取一行数据内容 // Input: ioValue, 保存一行数据 // ioNull, 保存空值的标志 0 为空, 1 非空 // Output: // Return: SQLITE_BUSY: 数据库被锁 // SQLITE_ROW: 数据未取完, 需要继续读取数据 // SQLITE_DONE: 数据已经读取完成 // other: 读取数据库记录失败 int CSQLite::Fetch( vector<string> & ioValues, vector<int>& ioNull ) { //清空数组 ioValues.clear(); ioNull.clear(); bool bRelease = true; //是否释放游标 for( unsigned int m = 0; m < m_nLimitNum; m++ ) { m_nState = sqlite3_step( m_pStmt ); if ( SQLITE_DONE == m_nState ) { // no rows m_strErrInfo = sqlite3_errmsg( m_pDb ); goto __Exit; } else if ( SQLITE_ROW == m_nState ) { //循环获取字段的内容 for ( unsigned int i = 0; i < m_nCurFieldNum; i++ ) { const char* pTmp = NULL; //防止空值, modified by yinyong 2007.12.27 pTmp = (const char*)( sqlite3_column_text( m_pStmt, i ) ); //有空值 if ( NULL == pTmp ) { ioValues.push_back( "" ); ioNull.push_back(0); } else { ioValues.push_back( pTmp ); ioNull.push_back(1); } } bRelease = false; goto __Exit; } else if ( SQLITE_BUSY == m_nState ) { if ( m_nLimitNum == m+1 ) { m_strErrInfo = sqlite3_errmsg( m_pDb ); goto __Exit; } sqlite3_busy_handler( m_pDb,NULL,NULL); Sleep( SLEEPTIME ); continue; } else { //other exceptions m_strErrInfo = sqlite3_errmsg( m_pDb ); goto __Exit; } } __Exit: if ( NULL != m_pStmt && bRelease ) { sqlite3_finalize( m_pStmt ); m_pStmt = NULL; } return m_nState; }
IDatabase *SqDriver::Connect(const DatabaseInfo *info, bool persistent, char *error, size_t maxlength) { /* We wrap most of the open process in a mutex just to be safe */ m_pOpenLock->Lock(); /* Format our path */ char path[PLATFORM_MAX_PATH]; size_t len = libsys->PathFormat(path, sizeof(path), "sqlite/%s", info->database); /* Chop any filename off */ for (size_t i = len-1; i <= len-1; i--) { if (IsPathSepChar(path[i])) { path[i] = '\0'; break; } } /* Test the full path */ char fullpath[PLATFORM_MAX_PATH]; g_pSM->BuildPath(Path_SM, fullpath, sizeof(fullpath), "data/%s", path); if (!libsys->IsPathDirectory(fullpath)) { /* Make sure the data folder exists */ len = g_pSM->BuildPath(Path_SM, fullpath, sizeof(fullpath), "data"); if (!libsys->IsPathDirectory(fullpath)) { if (!libsys->CreateFolder(fullpath)) { strncopy(error, "Could not create or open \"data\" folder\"", maxlength); m_pOpenLock->Unlock(); return NULL; } } /* The data folder exists - create each subdir as needed! */ char *cur_ptr = path; do { /* Find the next suitable path */ char *next_ptr = cur_ptr; while (*next_ptr != '\0') { if (IsPathSepChar(*next_ptr)) { *next_ptr = '\0'; next_ptr++; break; } next_ptr++; } if (*next_ptr == '\0') { next_ptr = NULL; } len += libsys->PathFormat(&fullpath[len], sizeof(fullpath)-len, "/%s", cur_ptr); if (!libsys->IsPathDirectory(fullpath) && !libsys->CreateFolder(fullpath)) { break; } cur_ptr = next_ptr; } while (cur_ptr); } /* Build the FINAL path. */ g_pSM->BuildPath(Path_SM, fullpath, sizeof(fullpath), "data/sqlite/%s.sq3", info->database); /* If we're requesting a persistent connection, see if something is already open */ if (persistent) { /* See if anything in the cache matches */ List<SqDbInfo>::iterator iter; for (iter = m_Cache.begin(); iter != m_Cache.end(); iter++) { if ((*iter).path.compare(fullpath) == 0) { (*iter).db->IncReferenceCount(); m_pOpenLock->Unlock(); return (*iter).db; } } } /* Try to open a new connection */ sqlite3 *sql; int err = sqlite3_open(fullpath, &sql); if (err != SQLITE_OK) { strncopy(error, sqlite3_errmsg(sql), maxlength); sqlite3_close(sql); m_pOpenLock->Unlock(); return NULL; } sqlite3_busy_handler(sql, busy_handler, NULL); SqDatabase *pdb = new SqDatabase(sql, persistent); if (persistent) { SqDbInfo pinfo; pinfo.path = fullpath; pinfo.db = pdb; m_Cache.push_back(pinfo); } m_pOpenLock->Unlock(); return pdb; }
/* ** Obtain a superlock on the database file identified by zPath, using the ** locking primitives provided by VFS zVfs. If successful, SQLITE_OK is ** returned and output variable *ppLock is populated with an opaque handle ** that may be used with sqlite3demo_superunlock() to release the lock. ** ** If an error occurs, *ppLock is set to 0 and an SQLite error code ** (e.g. SQLITE_BUSY) is returned. ** ** If a required lock cannot be obtained immediately and the xBusy parameter ** to this function is not NULL, then xBusy is invoked in the same way ** as a busy-handler registered with SQLite (using sqlite3_busy_handler()) ** until either the lock can be obtained or the busy-handler function returns ** 0 (indicating "give up"). */ int sqlite3demo_superlock( const char *zPath, /* Path to database file to lock */ const char *zVfs, /* VFS to use to access database file */ int (*xBusy)(void*,int), /* Busy handler callback */ void *pBusyArg, /* Context arg for busy handler */ void **ppLock /* OUT: Context to pass to superunlock() */ ){ SuperlockBusy busy = {0, 0, 0}; /* Busy handler wrapper object */ int rc; /* Return code */ Superlock *pLock; pLock = sqlite3_malloc(sizeof(Superlock)); if( !pLock ) return SQLITE_NOMEM; memset(pLock, 0, sizeof(Superlock)); /* Open a database handle on the file to superlock. */ rc = sqlite3_open_v2( zPath, &pLock->db, SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE, zVfs ); /* Install a busy-handler and execute a BEGIN EXCLUSIVE. If this is not ** a WAL database, this is all we need to do. ** ** A wrapper function is used to invoke the busy-handler instead of ** registering the busy-handler function supplied by the user directly ** with SQLite. This is because the same busy-handler function may be ** invoked directly later on when attempting to obtain the extra locks ** required in WAL mode. By using the wrapper, we are able to guarantee ** that the "nBusy" integer parameter passed to the users busy-handler ** represents the total number of busy-handler invocations made within ** this call to sqlite3demo_superlock(), including any made during the ** "BEGIN EXCLUSIVE". */ if( rc==SQLITE_OK ){ busy.xBusy = xBusy; busy.pBusyArg = pBusyArg; sqlite3_busy_handler(pLock->db, superlockBusyHandler, (void *)&busy); rc = sqlite3_exec(pLock->db, "BEGIN EXCLUSIVE", 0, 0, 0); } /* If the BEGIN EXCLUSIVE was executed successfully and this is a WAL ** database, call superlockWalLock() to obtain the extra locks required ** to prevent readers, writers and/or checkpointers from accessing the ** db while this process is holding the superlock. ** ** Before attempting any WAL locks, commit the transaction started above ** to drop the WAL read and write locks currently held. Otherwise, the ** new WAL locks may conflict with the old. */ if( rc==SQLITE_OK ){ if( SQLITE_OK==(rc = superlockIsWal(pLock)) && pLock->bWal ){ rc = sqlite3_exec(pLock->db, "COMMIT", 0, 0, 0); if( rc==SQLITE_OK ){ rc = superlockWalLock(pLock->db, &busy); } } } if( rc!=SQLITE_OK ){ sqlite3demo_superunlock(pLock); *ppLock = 0; }else{ *ppLock = pLock; } return rc; }
int QSQLiteDriver::busyHandler(int(*handler)(void*, int), void* pArg) { return sqlite3_busy_handler(d->access, handler, pArg); }
int SQLITE_CDECL main(int argc, char **argv){ const char *zClient; int iClient; int n, i; int openFlags = SQLITE_OPEN_READWRITE; int rc; char *zScript; int taskId; const char *zTrace; const char *zCOption; const char *zJMode; const char *zNRep; int nRep = 1, iRep; int iTmout = 0; /* Default: no timeout */ const char *zTmout; g.argv0 = argv[0]; g.iTrace = 1; if( argc<2 ) usage(argv[0]); g.zDbFile = argv[1]; if( strglob("*.test", g.zDbFile) ) usage(argv[0]); if( strcmp(sqlite3_sourceid(), SQLITE_SOURCE_ID)!=0 ){ fprintf(stderr, "SQLite library and header mismatch\n" "Library: %s\n" "Header: %s\n", sqlite3_sourceid(), SQLITE_SOURCE_ID); exit(1); } n = argc-2; sqlite3_snprintf(sizeof(g.zName), g.zName, "%05d.mptest", GETPID()); zJMode = findOption(argv+2, &n, "journalmode", 1); zNRep = findOption(argv+2, &n, "repeat", 1); if( zNRep ) nRep = atoi(zNRep); if( nRep<1 ) nRep = 1; g.zVfs = findOption(argv+2, &n, "vfs", 1); zClient = findOption(argv+2, &n, "client", 1); g.zErrLog = findOption(argv+2, &n, "errlog", 1); g.zLog = findOption(argv+2, &n, "log", 1); zTrace = findOption(argv+2, &n, "trace", 1); if( zTrace ) g.iTrace = atoi(zTrace); if( findOption(argv+2, &n, "quiet", 0)!=0 ) g.iTrace = 0; zTmout = findOption(argv+2, &n, "timeout", 1); if( zTmout ) iTmout = atoi(zTmout); g.bSqlTrace = findOption(argv+2, &n, "sqltrace", 0)!=0; g.bSync = findOption(argv+2, &n, "sync", 0)!=0; if( g.zErrLog ){ g.pErrLog = fopen(g.zErrLog, "a"); }else{ g.pErrLog = stderr; } if( g.zLog ){ g.pLog = fopen(g.zLog, "a"); }else{ g.pLog = stdout; } sqlite3_config(SQLITE_CONFIG_LOG, sqlErrorCallback, 0); if( zClient ){ iClient = atoi(zClient); if( iClient<1 ) fatalError("illegal client number: %d\n", iClient); sqlite3_snprintf(sizeof(g.zName), g.zName, "%05d.client%02d", GETPID(), iClient); }else{ int nTry = 0; if( g.iTrace>0 ){ printf("BEGIN: %s", argv[0]); for(i=1; i<argc; i++) printf(" %s", argv[i]); printf("\n"); printf("With SQLite " SQLITE_VERSION " " SQLITE_SOURCE_ID "\n" ); for(i=0; (zCOption = sqlite3_compileoption_get(i))!=0; i++){ printf("-DSQLITE_%s\n", zCOption); } fflush(stdout); } iClient = 0; do{ if( (nTry%5)==4 ) printf("... %strying to unlink '%s'\n", nTry>5 ? "still " : "", g.zDbFile); rc = unlink(g.zDbFile); if( rc && errno==ENOENT ) rc = 0; }while( rc!=0 && (++nTry)<60 && sqlite3_sleep(1000)>0 ); if( rc!=0 ){ fatalError("unable to unlink '%s' after %d attempts\n", g.zDbFile, nTry); } openFlags |= SQLITE_OPEN_CREATE; } rc = sqlite3_open_v2(g.zDbFile, &g.db, openFlags, g.zVfs); if( rc ) fatalError("cannot open [%s]", g.zDbFile); if( iTmout>0 ) sqlite3_busy_timeout(g.db, iTmout); if( zJMode ){ #if defined(_WIN32) if( sqlite3_stricmp(zJMode,"persist")==0 || sqlite3_stricmp(zJMode,"truncate")==0 ){ printf("Changing journal mode to DELETE from %s", zJMode); zJMode = "DELETE"; } #endif runSql("PRAGMA journal_mode=%Q;", zJMode); } if( !g.bSync ) trySql("PRAGMA synchronous=OFF"); sqlite3_enable_load_extension(g.db, 1); sqlite3_busy_handler(g.db, busyHandler, 0); sqlite3_create_function(g.db, "vfsname", 0, SQLITE_UTF8, 0, vfsNameFunc, 0, 0); sqlite3_create_function(g.db, "eval", 1, SQLITE_UTF8, 0, evalFunc, 0, 0); g.iTimeout = DEFAULT_TIMEOUT; if( g.bSqlTrace ) sqlite3_trace(g.db, sqlTraceCallback, 0); if( iClient>0 ){ if( n>0 ) unrecognizedArguments(argv[0], n, argv+2); if( g.iTrace ) logMessage("start-client"); while(1){ char *zTaskName = 0; rc = startScript(iClient, &zScript, &taskId, &zTaskName); if( rc==SQLITE_DONE ) break; if( g.iTrace ) logMessage("begin %s (%d)", zTaskName, taskId); runScript(iClient, taskId, zScript, zTaskName); if( g.iTrace ) logMessage("end %s (%d)", zTaskName, taskId); finishScript(iClient, taskId, 0); sqlite3_free(zTaskName); sqlite3_sleep(10); } if( g.iTrace ) logMessage("end-client"); }else{ sqlite3_stmt *pStmt; int iTimeout; if( n==0 ){ fatalError("missing script filename"); } if( n>1 ) unrecognizedArguments(argv[0], n, argv+2); runSql( "DROP TABLE IF EXISTS task;\n" "DROP TABLE IF EXISTS counters;\n" "DROP TABLE IF EXISTS client;\n" "CREATE TABLE task(\n" " id INTEGER PRIMARY KEY,\n" " name TEXT,\n" " client INTEGER,\n" " starttime DATE,\n" " endtime DATE,\n" " script TEXT\n" ");" "CREATE INDEX task_i1 ON task(client, starttime);\n" "CREATE INDEX task_i2 ON task(client, endtime);\n" "CREATE TABLE counters(nError,nTest);\n" "INSERT INTO counters VALUES(0,0);\n" "CREATE TABLE client(id INTEGER PRIMARY KEY, wantHalt);\n" ); zScript = readFile(argv[2]); for(iRep=1; iRep<=nRep; iRep++){ if( g.iTrace ) logMessage("begin script [%s] cycle %d\n", argv[2], iRep); runScript(0, 0, zScript, argv[2]); if( g.iTrace ) logMessage("end script [%s] cycle %d\n", argv[2], iRep); } sqlite3_free(zScript); waitForClient(0, 2000, "during shutdown...\n"); trySql("UPDATE client SET wantHalt=1"); sqlite3_sleep(10); g.iTimeout = 0; iTimeout = 1000; while( ((rc = trySql("SELECT 1 FROM client"))==SQLITE_BUSY || rc==SQLITE_ROW) && iTimeout>0 ){ sqlite3_sleep(10); iTimeout -= 10; } sqlite3_sleep(100); pStmt = prepareSql("SELECT nError, nTest FROM counters"); iTimeout = 1000; while( (rc = sqlite3_step(pStmt))==SQLITE_BUSY && iTimeout>0 ){ sqlite3_sleep(10); iTimeout -= 10; } if( rc==SQLITE_ROW ){ g.nError += sqlite3_column_int(pStmt, 0); g.nTest += sqlite3_column_int(pStmt, 1); } sqlite3_finalize(pStmt); } sqlite3_close(g.db); maybeClose(g.pLog); maybeClose(g.pErrLog); if( iClient==0 ){ printf("Summary: %d errors out of %d tests\n", g.nError, g.nTest); printf("END: %s", argv[0]); for(i=1; i<argc; i++) printf(" %s", argv[i]); printf("\n"); } return g.nError>0; }
void SqliteDatabaseBackend::registerBusyHandler() { sqlite3_busy_handler(sqliteDatabaseHandle(), &busyHandlerCallback, nullptr); }