//##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 { Log(StrF(_T("Computer has not been idle long enough to delete clips, Min Idle: %d, current Idle: %d"), CGetSetOptions::GetIdleSecondsBeforeDelete(), idleSeconds)); break; } q.nextRow(); } toDeleteCount = db.execScalar(_T("SELECT COUNT(clipID) FROM MainDeletes")); Log(StrF(_T("After Deleting emptied out data rows, Count: %d, toDelete: %d"), deleteCount, toDeleteCount)); } CATCH_SQLITE_EXCEPTION Log(_T("End of RemoveOldEntries")); return TRUE; }
BOOL ValidDB(CString csPath, BOOL bUpgrade) { CDittoPopupWindow *popUpMsg = NULL; try { BOOL didBackup = FALSE; CString backupFilePrefix = _T("Before_Update_To"); CppSQLite3DB db; db.open(csPath); db.execQuery(_T("SELECT lID, lDate, mText, lShortCut, lDontAutoDelete, ") _T("CRC, bIsGroup, lParentID, QuickPasteText ") _T("FROM Main")); db.execQuery(_T("SELECT lID, lParentID, strClipBoardFormat, ooData FROM Data")); db.execQuery(_T("SELECT lID, TypeText FROM Types")); try { db.execDML(_T("DROP TRIGGER delete_data_trigger")); } catch(CppSQLite3Exception& e) { e.errorCode(); } try { db.execDML(_T("DROP TRIGGER delete_copy_buffer_trigger")); } catch(CppSQLite3Exception& e) { e.errorCode(); } //This was added later so try to add each time and catch the exception here try { db.execDML(_T("CREATE TRIGGER delete_data_trigger BEFORE DELETE ON Main FOR EACH ROW\n") _T("BEGIN\n") _T("INSERT INTO MainDeletes VALUES(old.lID, datetime('now'));\n") _T("END\n")); } catch(CppSQLite3Exception& e) { if(didBackup == FALSE) didBackup = BackupDB(csPath, backupFilePrefix, &popUpMsg); e.errorCode(); } //This was added later so try to add each time and catch the exception here try { db.execQuery(_T("SELECT lID, lClipID, lCopyBuffer FROM CopyBuffers")); } catch(CppSQLite3Exception& e) { if(didBackup == FALSE) didBackup = BackupDB(csPath, backupFilePrefix, &popUpMsg); e.errorCode(); db.execDML(_T("CREATE TABLE CopyBuffers(") _T("lID INTEGER PRIMARY KEY AUTOINCREMENT, ") _T("lClipID INTEGER,") _T("lCopyBuffer INTEGER)")); } //This was added later so try to add each time and catch the exception here try { db.execQuery(_T("SELECT clipId FROM MainDeletes")); } catch(CppSQLite3Exception& e) { if(didBackup == FALSE) didBackup = BackupDB(csPath, backupFilePrefix, &popUpMsg); e.errorCode(); db.execDML(_T("CREATE TABLE MainDeletes(") _T("clipID INTEGER,") _T("modifiedDate)")); db.execDML(_T("CREATE TRIGGER MainDeletes_delete_data_trigger BEFORE DELETE ON MainDeletes FOR EACH ROW\n") _T("BEGIN\n") _T("DELETE FROM CopyBuffers WHERE lClipID = old.clipID;\n") _T("DELETE FROM Data WHERE lParentID = old.clipID;\n") _T("END\n")); } try { db.execDML(_T("CREATE INDEX Main_ParentId on Main(lParentID DESC)")); db.execDML(_T("CREATE INDEX Main_IsGroup on Main(bIsGroup DESC)")); db.execDML(_T("CREATE INDEX Main_ShortCut on Main(lShortCut DESC)")); } catch(CppSQLite3Exception& e) { e.errorCode(); } try { db.execQuery(_T("SELECT clipOrder, clipGroupOrder FROM Main")); } catch(CppSQLite3Exception& e) { if(didBackup == FALSE) didBackup = BackupDB(csPath, backupFilePrefix, &popUpMsg); db.execDML(_T("ALTER TABLE Main ADD clipOrder REAL")); db.execDML(_T("ALTER TABLE Main ADD clipGroupOrder REAL")); 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; }