C++ CppSQLite3DB::execDMLEx方法代码示例

//##ModelId=474D30760272
bool CClip_ImportExport::ExportToSqliteDB(CppSQLite3DB &db)
{
	bool bRet = false;
	try
	{
		//Add to Main Table
		m_Desc.Replace(_T("'"), _T("''"));
		db.execDMLEx(_T("insert into Main values(NULL, %d, '%s');"), CURRENT_EXPORT_VERSION, m_Desc);
		long lId = (long)db.lastRowId();

		//Add to Data table
		CClipFormat* pCF;
		CppSQLite3Statement stmt = db.compileStatement(_T("insert into Data values (NULL, ?, ?, ?, ?);"));

		for(int i = m_Formats.GetSize()-1; i >= 0 ; i--)
		{
			pCF = & m_Formats.ElementAt(i);

			stmt.bind(1, lId);
			stmt.bind(2, GetFormatName(pCF->m_cfType));
			long lOriginalSize = GlobalSize(pCF->m_hgData);
			stmt.bind(3, lOriginalSize);

			const unsigned char *Data = (const unsigned char *)GlobalLock(pCF->m_hgData);
			if(Data)
			{
				//First compress the data
				long lZippedSize = compressBound(lOriginalSize);
				Bytef *pZipped = new Bytef[lZippedSize];
				if(pZipped)
				{				
					int nZipReturn = compress(pZipped, (uLongf *)&lZippedSize, (const Bytef *)Data, lOriginalSize);
					if(nZipReturn == Z_OK)
					{
						stmt.bind(4, pZipped, lZippedSize);
					}

					delete []pZipped;
					pZipped = NULL;
				}
			}
			GlobalUnlock(pCF->m_hgData);

			stmt.execDML();
			stmt.reset();

			m_Formats.RemoveAt(i);
		}

		bRet = true;
	}
	CATCH_SQLITE_EXCEPTION_AND_RETURN(false)

	return bRet;
}

BOOL RemoveOldEntries(bool checkIdleTime)
{
	Log(StrF(_T("Beginning of RemoveOldEntries MaxEntries: %d - Keep days: %d"), CGetSetOptions::GetMaxEntries(), CGetSetOptions::GetExpiredEntries()));

	try
	{
		CppSQLite3DB db;
		CString csDbPath = CGetSetOptions::GetDBPath();
		db.open(csDbPath);

		if(CGetSetOptions::GetCheckForMaxEntries())
		{
			long lMax = CGetSetOptions::GetMaxEntries();
			if(lMax >= 0)
			{
				CClipIDs IDs;
				int clipId;
				
				CppSQLite3Query q = db.execQueryEx(_T("SELECT lID, lShortCut, lParentID, lDontAutoDelete, stickyClipOrder, stickyClipGroupOrder FROM Main WHERE bIsGroup = 0 ORDER BY clipOrder DESC LIMIT -1 OFFSET %d"), lMax);
				while(q.eof() == false)
				{
					int shortcut = q.getIntField(_T("lShortCut"));
					int dontDelete = q.getIntField(_T("lDontAutoDelete"));
					int parentId = q.getIntField(_T("lParentID"));
					double stickyClipOrder = q.getFloatField(_T("stickyClipOrder"));
					double stickyClipGroupOrder = q.getFloatField(_T("stickyClipGroupOrder"));

					//Only delete entries that have no shortcut and don't have the flag set and aren't in groups and 
					if(shortcut == 0 && 
						dontDelete == 0 &&
						parentId <= 0 &&
						stickyClipOrder == 0.0 &&
						stickyClipGroupOrder == 0.0)
					{
						clipId = q.getIntField(_T("lID"));
						IDs.Add(clipId);
						Log(StrF(_T("From MaxEntries - Deleting Id: %d"), clipId));
					}

					q.nextRow();
				}

				if(IDs.GetCount() > 0)
				{
					IDs.DeleteIDs(false, db);
				}
			}
		}
		
		if(CGetSetOptions::GetCheckForExpiredEntries())
		{
			long lExpire = CGetSetOptions::GetExpiredEntries();
			
			if(lExpire)
			{
				CTime now = CTime::GetCurrentTime();
				now -= CTimeSpan(lExpire, 0, 0, 0);
				
				CClipIDs IDs;
				
				CppSQLite3Query q = db.execQueryEx(_T("SELECT lID FROM Main ")
													_T("WHERE lastPasteDate < %d AND ")
													_T("bIsGroup = 0 AND lShortCut = 0 AND lParentID <= 0 AND lDontAutoDelete = 0 AND stickyClipOrder = 0 AND stickyClipGroupOrder = 0"), (int)now.GetTime());

				while(q.eof() == false)
				{
					IDs.Add(q.getIntField(_T("lID")));

					Log(StrF(_T("From Clips Expire - Deleting Id: %d"), q.getIntField(_T("lID"))));

					q.nextRow();
				}
				
				if(IDs.GetCount() > 0)
				{
					IDs.DeleteIDs(false, db);
				}
			}
		}

		int toDeleteCount = db.execScalar(_T("SELECT COUNT(clipID) FROM MainDeletes"));

		Log(StrF(_T("Before Deleting emptied out data, count: %d, Idle Seconds: %f"), toDeleteCount, IdleSeconds()));

		//Only delete 1 at a time, was finding that it was taking a long time to delete clips, locking the db and causing other queries
		//to lock up
		CppSQLite3Query q = db.execQueryEx(_T("SELECT * FROM MainDeletes LIMIT %d"), CGetSetOptions::GetMainDeletesDeleteCount());
		int deleteCount = 0;

		while(q.eof() == false)
		{
			double idleSeconds = IdleSeconds();
			if(checkIdleTime == false || idleSeconds > CGetSetOptions::GetIdleSecondsBeforeDelete())
			{
				//delete any data items sitting out there that the main table data was deleted
				//this was done to speed up deleted from the main table
				deleteCount = db.execDMLEx(_T("DELETE FROM MainDeletes WHERE clipID=%d"), q.getIntField(_T("clipID")));
			}
			else
			{
//.........这里部分代码省略.........

//.........这里部分代码省略.........
			db.execDML(_T("Update Main set clipOrder = lDate, clipGroupOrder = lDate"));

			db.execDML(_T("CREATE INDEX Main_ClipOrder on Main(clipOrder DESC)"));
			db.execDML(_T("CREATE INDEX Main_ClipGroupOrder on Main(clipGroupOrder DESC)"));

			db.execDML(_T("DROP INDEX Main_Date"));

			e.errorCode();
		}

		try
		{
			db.execQuery(_T("SELECT globalShortCut FROM Main"));
		}
		catch(CppSQLite3Exception& e)
		{
			if(didBackup == FALSE)
				didBackup = BackupDB(csPath, backupFilePrefix, &popUpMsg);


			db.execDML(_T("ALTER TABLE Main ADD globalShortCut INTEGER"));

			e.errorCode();
		}

		try
		{
			db.execQuery(_T("SELECT lastPasteDate FROM Main"));
		}
		catch(CppSQLite3Exception& e)
		{
			if(didBackup == FALSE)
				didBackup = BackupDB(csPath, backupFilePrefix, &popUpMsg);

			db.execDML(_T("ALTER TABLE Main ADD lastPasteDate INTEGER"));
			db.execDML(_T("Update Main set lastPasteDate = lDate"));
			db.execDMLEx(_T("Update Main set lastPasteDate = %d where lastPasteDate <= 0"), (int)CTime::GetCurrentTime().GetTime());

			e.errorCode();
		}

		try
		{
			db.execQuery(_T("SELECT stickyClipOrder FROM Main"));
		}
		catch (CppSQLite3Exception& e)
		{
			if (didBackup == FALSE)
				didBackup = BackupDB(csPath, backupFilePrefix, &popUpMsg);

			db.execDML(_T("ALTER TABLE Main ADD stickyClipOrder REAL"));
			db.execDML(_T("ALTER TABLE Main ADD stickyClipGroupOrder REAL"));

			e.errorCode();
		}

		try
		{			
			CppSQLite3Query q = db.execQuery(_T("PRAGMA index_info(Main_NoGroup);"));
			int count = 0;
			while (q.eof() == false)
			{
				count++;
				q.nextRow();
			}

			if(count == 0)
			{
				if (didBackup == FALSE)
					didBackup = BackupDB(csPath, backupFilePrefix, &popUpMsg);

				db.execDML(_T("Update Main set stickyClipOrder = -(2147483647) where stickyClipOrder IS NULL;"));
				db.execDML(_T("Update Main set stickyClipGroupOrder = -(2147483647) where stickyClipGroupOrder IS NULL;"));
				db.execDML(_T("Update Main set stickyClipOrder = -(2147483647) where stickyClipOrder = 0;"));
				db.execDML(_T("Update Main set stickyClipGroupOrder = -(2147483647) where stickyClipGroupOrder = 0;"));

				db.execDML(_T("CREATE INDEX Main_NoGroup ON Main(bIsGroup ASC, stickyClipOrder DESC, clipOrder DESC);"));
				db.execDML(_T("CREATE INDEX Main_InGroup ON Main(lParentId ASC, bIsGroup ASC, stickyClipGroupOrder DESC, clipGroupOrder DESC);"));
				db.execDML(_T("CREATE INDEX Data_ParentId_Format ON Data(lParentID COLLATE BINARY ASC, strClipBoardFormat COLLATE NOCASE ASC);"));
			}
		}
		catch (CppSQLite3Exception& e)
		{
			if (didBackup == FALSE)
				didBackup = BackupDB(csPath, backupFilePrefix, &popUpMsg);

			e.errorCode();
		}
	}
	CATCH_SQLITE_EXCEPTION_AND_RETURN(FALSE)

	if(popUpMsg != NULL &&
		IsWindow(popUpMsg->m_hWnd))
	{
		popUpMsg->CloseWindow();
		popUpMsg->DestroyWindow();
		popUpMsg = NULL;
	}
	return TRUE;                                                     
}