DatabaseUtilities.cpp 26 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987988989990991992993994995996
  1. // DatabaseUtilites.cpp: implementation of the CDatabaseUtilites class.
  2. //
  3. //////////////////////////////////////////////////////////////////////
  4. #include "stdafx.h"
  5. #include "CP_Main.h"
  6. #include "DatabaseUtilities.h"
  7. #include "ProcessPaste.h"
  8. #include <io.h>
  9. #include "Path.h"
  10. #include "InternetUpdate.h"
  11. //////////////////////////////////////////////////////////////////////
  12. // Construction/Destruction
  13. //////////////////////////////////////////////////////////////////////
  14. BOOL CreateBackup(CString csPath)
  15. {
  16. CString csOriginal;
  17. int count = 0;
  18. // create a backup of the existing database
  19. do
  20. {
  21. count++;
  22. csOriginal = csPath + StrF(_T(".%03d"), count);
  23. // in case of some weird infinite loop
  24. if( count > 50 )
  25. {
  26. ASSERT(0);
  27. return FALSE;
  28. }
  29. } while( !::CopyFile(csPath, csOriginal, TRUE));
  30. return TRUE;
  31. }
  32. CString GetDBName()
  33. {
  34. return CGetSetOptions::GetDBPath();
  35. }
  36. CString GetOLDDefaultDBName()
  37. {
  38. CString csDefaultPath;
  39. LPMALLOC pMalloc;
  40. if(SUCCEEDED(::SHGetMalloc(&pMalloc)))
  41. {
  42. LPITEMIDLIST pidlPrograms;
  43. SHGetSpecialFolderLocation(NULL, CSIDL_APPDATA, &pidlPrograms);
  44. TCHAR string[MAX_PATH];
  45. SHGetPathFromIDList(pidlPrograms, string);
  46. pMalloc->Free(pidlPrograms);
  47. pMalloc->Release();
  48. csDefaultPath = string;
  49. csDefaultPath += "\\Ditto\\";
  50. csDefaultPath += "DittoDB.mdb";
  51. }
  52. return csDefaultPath;
  53. }
  54. CString GetDefaultDBName()
  55. {
  56. CString csDefaultPath = _T("c:\\program files\\Ditto\\");
  57. //If portable then default to the running path
  58. if(CGetSetOptions::GetIsPortableDitto())
  59. {
  60. csDefaultPath.Empty();
  61. }
  62. else
  63. {
  64. csDefaultPath = CGetSetOptions::GetAppDataPath();
  65. }
  66. CString csTempName = csDefaultPath + "Ditto.db";
  67. int i = 1;
  68. while(FileExists(csTempName))
  69. {
  70. csTempName.Format(_T("%sDitto_%d.db"), csDefaultPath, i);
  71. i++;
  72. }
  73. csDefaultPath = csTempName;
  74. return csDefaultPath;
  75. }
  76. BOOL CheckDBExists(CString csDBPath)
  77. {
  78. //If this is the first time running this version then convert the old database to the new db
  79. if(csDBPath.IsEmpty())
  80. {
  81. csDBPath = GetDefaultDBName();
  82. CGetSetOptions::SetDBPath(csDBPath);
  83. }
  84. BOOL bRet = FALSE;
  85. if(FileExists(csDBPath) == FALSE)
  86. {
  87. //first try and create create a db at the same path that was selectd
  88. bRet = CreateDB(csDBPath);
  89. //if that didn't work then go back to the default location
  90. if (FileExists(csDBPath) == FALSE)
  91. {
  92. csDBPath = GetDefaultDBName();
  93. nsPath::CPath FullPath(csDBPath);
  94. CString csPath = FullPath.GetPath().GetStr();
  95. if (csPath.IsEmpty() == false && FileExists(csDBPath) == FALSE)
  96. {
  97. CreateDirectory(csPath, NULL);
  98. }
  99. CGetSetOptions::SetDBPath(csDBPath);
  100. bRet = CreateDB(csDBPath);
  101. }
  102. }
  103. else
  104. {
  105. if(ValidDB(csDBPath) == FALSE)
  106. {
  107. //Db existed but was bad
  108. CString csMarkAsBad;
  109. csMarkAsBad = csDBPath;
  110. csMarkAsBad.Replace(_T("."), _T("_BAD."));
  111. CString csPath = GetDefaultDBName();
  112. CString cs;
  113. cs.Format(_T("%s \"%s\",\n")
  114. _T("%s \"%s\",\n")
  115. _T("%s,\n")
  116. _T("\"%s\""),
  117. theApp.m_Language.GetString("Database_Format", "Unrecognized Database Format"),
  118. csDBPath,
  119. theApp.m_Language.GetString("File_Renamed", "the file will be renamed"),
  120. csMarkAsBad,
  121. theApp.m_Language.GetString("New_Database", "and a new database will be created"),
  122. csPath);
  123. AfxMessageBox(cs);
  124. CFile::Rename(csDBPath, csMarkAsBad);
  125. csDBPath = csPath;
  126. bRet = CreateDB(csDBPath);
  127. CGetSetOptions::SetDBPath(csDBPath);
  128. }
  129. else
  130. {
  131. bRet = TRUE;
  132. }
  133. }
  134. if(bRet)
  135. {
  136. bRet = OpenDatabase(csDBPath);
  137. }
  138. return bRet;
  139. }
  140. BOOL OpenDatabase(CString csDB)
  141. {
  142. try
  143. {
  144. theApp.m_db.close();
  145. theApp.m_db.open(csDB);
  146. theApp.m_db.setBusyTimeout(CGetSetOptions::GetDbTimeout());
  147. return TRUE;
  148. }
  149. CATCH_SQLITE_EXCEPTION
  150. return FALSE;
  151. }
  152. void ReOrderStickyClips(int parentID, CppSQLite3DB &db)
  153. {
  154. try
  155. {
  156. Log(StrF(_T("Start of ReOrderStickyClips, ParentId %d"), parentID));
  157. //groups where created with 0 in these fields, fix them up if they are 0
  158. if(parentID == -1)
  159. {
  160. db.execDMLEx(_T("Update Main Set stickyClipOrder = -(2147483647) where bIsGroup = 1 AND stickyClipOrder = 0"));
  161. db.execDMLEx(_T("Update Main Set stickyClipGroupOrder = -(2147483647) where bIsGroup = 1 AND stickyClipGroupOrder = 0"));
  162. }
  163. CppSQLite3Query qGroup = db.execQueryEx(_T("SELECT lID, mText FROM Main WHERE bIsGroup = 1 AND lParentID = %d"), parentID);
  164. if (qGroup.eof() == false)
  165. {
  166. while (!qGroup.eof())
  167. {
  168. //Get all sticky clips at the top level or group
  169. CString sql = StrF(_T("SELECT lID FROM Main WHERE stickyClipOrder <> -(2147483647) AND lParentID = %d ORDER BY stickyClipOrder DESC"), parentID);
  170. if (parentID > -1)
  171. {
  172. sql = StrF(_T("SELECT lID FROM Main WHERE stickyClipGroupOrder <> -(2147483647) AND lParentID = %d ORDER BY stickyClipGroupOrder DESC"), parentID);
  173. }
  174. CppSQLite3Query qSticky = db.execQueryEx(sql);
  175. int order = 1;
  176. if (qSticky.eof() == false)
  177. {
  178. while (!qSticky.eof())
  179. {
  180. //set the new order
  181. if (parentID > -1)
  182. {
  183. db.execDMLEx(_T("Update Main Set stickyClipGroupOrder = %d where lID = %d"), order, qSticky.getIntField(_T("lID")));
  184. }
  185. else
  186. {
  187. db.execDMLEx(_T("Update Main Set stickyClipOrder = %d where lID = %d"), order, qSticky.getIntField(_T("lID")));
  188. }
  189. qSticky.nextRow();
  190. order--;
  191. }
  192. }
  193. ReOrderStickyClips(qGroup.getIntField(_T("lID")), db);
  194. qGroup.nextRow();
  195. }
  196. }
  197. Log(StrF(_T("End of ReOrderStickyClips, ParentId %d"), parentID));
  198. }
  199. CATCH_SQLITE_EXCEPTION
  200. }
  201. BOOL ValidDB(CString csPath, BOOL bUpgrade)
  202. {
  203. CDittoPopupWindow *popUpMsg = NULL;
  204. try
  205. {
  206. BOOL didBackup = FALSE;
  207. CString backupFilePrefix = _T("Before_Update_To");
  208. CppSQLite3DB db;
  209. db.open(csPath);
  210. db.execQuery(_T("SELECT lID, lDate, mText, lShortCut, lDontAutoDelete, ")
  211. _T("CRC, bIsGroup, lParentID, QuickPasteText ")
  212. _T("FROM Main"));
  213. db.execQuery(_T("SELECT lID, lParentID, strClipBoardFormat, ooData FROM Data"));
  214. db.execQuery(_T("SELECT lID, TypeText FROM Types"));
  215. try
  216. {
  217. db.execDML(_T("DROP TRIGGER delete_data_trigger"));
  218. }
  219. catch(CppSQLite3Exception& e)
  220. {
  221. e.errorCode();
  222. }
  223. try
  224. {
  225. db.execDML(_T("DROP TRIGGER delete_copy_buffer_trigger"));
  226. }
  227. catch(CppSQLite3Exception& e)
  228. {
  229. e.errorCode();
  230. }
  231. //This was added later so try to add each time and catch the exception here
  232. try
  233. {
  234. db.execDML(_T("CREATE TRIGGER delete_data_trigger BEFORE DELETE ON Main FOR EACH ROW\n")
  235. _T("BEGIN\n")
  236. _T("INSERT INTO MainDeletes VALUES(old.lID, datetime('now'));\n")
  237. _T("END\n"));
  238. }
  239. catch(CppSQLite3Exception& e)
  240. {
  241. if(didBackup == FALSE)
  242. didBackup = BackupDB(csPath, backupFilePrefix, &popUpMsg);
  243. e.errorCode();
  244. }
  245. //This was added later so try to add each time and catch the exception here
  246. try
  247. {
  248. db.execQuery(_T("SELECT lID, lClipID, lCopyBuffer FROM CopyBuffers"));
  249. }
  250. catch(CppSQLite3Exception& e)
  251. {
  252. if(didBackup == FALSE)
  253. didBackup = BackupDB(csPath, backupFilePrefix, &popUpMsg);
  254. e.errorCode();
  255. db.execDML(_T("CREATE TABLE CopyBuffers(")
  256. _T("lID INTEGER PRIMARY KEY AUTOINCREMENT, ")
  257. _T("lClipID INTEGER,")
  258. _T("lCopyBuffer INTEGER)"));
  259. }
  260. //This was added later so try to add each time and catch the exception here
  261. try
  262. {
  263. db.execQuery(_T("SELECT clipId FROM MainDeletes"));
  264. }
  265. catch(CppSQLite3Exception& e)
  266. {
  267. if(didBackup == FALSE)
  268. didBackup = BackupDB(csPath, backupFilePrefix, &popUpMsg);
  269. e.errorCode();
  270. db.execDML(_T("CREATE TABLE MainDeletes(")
  271. _T("clipID INTEGER,")
  272. _T("modifiedDate)"));
  273. db.execDML(_T("CREATE TRIGGER MainDeletes_delete_data_trigger BEFORE DELETE ON MainDeletes FOR EACH ROW\n")
  274. _T("BEGIN\n")
  275. _T("DELETE FROM CopyBuffers WHERE lClipID = old.clipID;\n")
  276. _T("DELETE FROM Data WHERE lParentID = old.clipID;\n")
  277. _T("END\n"));
  278. }
  279. try
  280. {
  281. db.execDML(_T("CREATE INDEX Main_ParentId on Main(lParentID DESC)"));
  282. db.execDML(_T("CREATE INDEX Main_IsGroup on Main(bIsGroup DESC)"));
  283. db.execDML(_T("CREATE INDEX Main_ShortCut on Main(lShortCut DESC)"));
  284. }
  285. catch(CppSQLite3Exception& e)
  286. {
  287. e.errorCode();
  288. }
  289. try
  290. {
  291. db.execQuery(_T("SELECT clipOrder, clipGroupOrder FROM Main"));
  292. }
  293. catch(CppSQLite3Exception& e)
  294. {
  295. if(didBackup == FALSE)
  296. didBackup = BackupDB(csPath, backupFilePrefix, &popUpMsg);
  297. db.execDML(_T("ALTER TABLE Main ADD clipOrder REAL"));
  298. db.execDML(_T("ALTER TABLE Main ADD clipGroupOrder REAL"));
  299. db.execDML(_T("Update Main set clipOrder = lDate, clipGroupOrder = lDate"));
  300. db.execDML(_T("CREATE INDEX Main_ClipOrder on Main(clipOrder DESC)"));
  301. db.execDML(_T("CREATE INDEX Main_ClipGroupOrder on Main(clipGroupOrder DESC)"));
  302. db.execDML(_T("DROP INDEX Main_Date"));
  303. e.errorCode();
  304. }
  305. try
  306. {
  307. db.execQuery(_T("SELECT globalShortCut FROM Main"));
  308. }
  309. catch(CppSQLite3Exception& e)
  310. {
  311. if(didBackup == FALSE)
  312. didBackup = BackupDB(csPath, backupFilePrefix, &popUpMsg);
  313. db.execDML(_T("ALTER TABLE Main ADD globalShortCut INTEGER"));
  314. e.errorCode();
  315. }
  316. try
  317. {
  318. db.execQuery(_T("SELECT lastPasteDate FROM Main"));
  319. }
  320. catch(CppSQLite3Exception& e)
  321. {
  322. if(didBackup == FALSE)
  323. didBackup = BackupDB(csPath, backupFilePrefix, &popUpMsg);
  324. db.execDML(_T("ALTER TABLE Main ADD lastPasteDate INTEGER"));
  325. db.execDML(_T("Update Main set lastPasteDate = lDate"));
  326. db.execDMLEx(_T("Update Main set lastPasteDate = %d where lastPasteDate <= 0"), (int)CTime::GetCurrentTime().GetTime());
  327. e.errorCode();
  328. }
  329. try
  330. {
  331. db.execQuery(_T("SELECT stickyClipOrder FROM Main"));
  332. }
  333. catch (CppSQLite3Exception& e)
  334. {
  335. if (didBackup == FALSE)
  336. didBackup = BackupDB(csPath, backupFilePrefix, &popUpMsg);
  337. db.execDML(_T("ALTER TABLE Main ADD stickyClipOrder REAL"));
  338. db.execDML(_T("ALTER TABLE Main ADD stickyClipGroupOrder REAL"));
  339. e.errorCode();
  340. }
  341. try
  342. {
  343. CppSQLite3Query q = db.execQuery(_T("PRAGMA index_info(Main_NoGroup);"));
  344. int count = 0;
  345. while (q.eof() == false)
  346. {
  347. count++;
  348. q.nextRow();
  349. }
  350. if(count == 0)
  351. {
  352. if (didBackup == FALSE)
  353. didBackup = BackupDB(csPath, backupFilePrefix, &popUpMsg);
  354. db.execDML(_T("Update Main set stickyClipOrder = -(2147483647) where stickyClipOrder IS NULL;"));
  355. db.execDML(_T("Update Main set stickyClipGroupOrder = -(2147483647) where stickyClipGroupOrder IS NULL;"));
  356. db.execDML(_T("Update Main set stickyClipOrder = -(2147483647) where stickyClipOrder = 0;"));
  357. db.execDML(_T("Update Main set stickyClipGroupOrder = -(2147483647) where stickyClipGroupOrder = 0;"));
  358. db.execDML(_T("CREATE INDEX Main_NoGroup ON Main(bIsGroup ASC, stickyClipOrder DESC, clipOrder DESC);"));
  359. db.execDML(_T("CREATE INDEX Main_InGroup ON Main(lParentId ASC, bIsGroup ASC, stickyClipGroupOrder DESC, clipGroupOrder DESC);"));
  360. db.execDML(_T("CREATE INDEX Data_ParentId_Format ON Data(lParentID COLLATE BINARY ASC, strClipBoardFormat COLLATE NOCASE ASC);"));
  361. }
  362. }
  363. catch (CppSQLite3Exception& e)
  364. {
  365. if (didBackup == FALSE)
  366. didBackup = BackupDB(csPath, backupFilePrefix, &popUpMsg);
  367. e.errorCode();
  368. }
  369. try
  370. {
  371. db.execQuery(_T("SELECT MoveToGroupShortCut FROM Main"));
  372. db.execQuery(_T("SELECT GlobalMoveToGroupShortCut FROM Main"));
  373. }
  374. catch(CppSQLite3Exception& e)
  375. {
  376. if(didBackup == FALSE)
  377. didBackup = BackupDB(csPath, backupFilePrefix, &popUpMsg);
  378. db.execDML(_T("ALTER TABLE Main ADD MoveToGroupShortCut INTEGER"));
  379. db.execDML(_T("ALTER TABLE Main ADD GlobalMoveToGroupShortCut INTEGER"));
  380. e.errorCode();
  381. }
  382. }
  383. CATCH_SQLITE_EXCEPTION_AND_RETURN(FALSE)
  384. if(popUpMsg != NULL &&
  385. IsWindow(popUpMsg->m_hWnd))
  386. {
  387. popUpMsg->CloseWindow();
  388. popUpMsg->DestroyWindow();
  389. delete popUpMsg;
  390. popUpMsg = NULL;
  391. }
  392. return TRUE;
  393. }
  394. BOOL BackupDB(CString dbPath, CString prefix, CDittoPopupWindow **popUpMsg)
  395. {
  396. if ((*popUpMsg) == NULL)
  397. {
  398. CRect r = DefaultMonitorRect();
  399. *popUpMsg = new CDittoPopupWindow();
  400. (*popUpMsg)->Create(CRect(r.right - 400, r.bottom - 130, r.right - 10, r.bottom - 10), NULL);
  401. ::SetWindowPos((*popUpMsg)->m_hWnd, HWND_TOPMOST, 0, 0, 0, 0, SWP_NOSIZE | SWP_NOMOVE | SWP_SHOWWINDOW);
  402. (*popUpMsg)->ShowWindow(SW_SHOW);
  403. (*popUpMsg)->UpdateText(_T("Backing up Ditto's Database"));
  404. }
  405. CString backup = GetFilePath(dbPath);
  406. CInternetUpdate update;
  407. auto runningVersion = update.GetRunningVersion();
  408. CString versionString = update.GetVersionString(runningVersion);
  409. backup += GetFileName(dbPath) += _T("_") + prefix + _T("_") + versionString;
  410. backup.Replace(_T(".db"), _T(""));
  411. backup.Replace(_T("."), _T("_"));
  412. CString temp = backup;
  413. temp += _T(".db");
  414. int i = 1;
  415. while(FileExists(temp))
  416. {
  417. temp.Format(_T("%s_%d.db"), backup, i);
  418. i++;
  419. }
  420. backup = temp;
  421. BOOL ret = FALSE;
  422. try
  423. {
  424. CFile file;
  425. CFileException ex;
  426. if(file.Open(dbPath, CFile::modeRead|CFile::typeBinary|CFile::shareDenyNone, &ex))
  427. {
  428. ULONGLONG fileSize = file.GetLength();
  429. ULONGLONG totalReadSize = 0;
  430. int percentageComplete = 0;
  431. UINT readBytes = 0;
  432. char *pBuffer = new char[65536];
  433. if(pBuffer != NULL)
  434. {
  435. CFile writeFile;
  436. if(writeFile.Open(backup, CFile::modeCreate|CFile::modeWrite|CFile::typeBinary|CFile::shareDenyNone, &ex))
  437. {
  438. do
  439. {
  440. readBytes = file.Read(pBuffer, 65536);
  441. writeFile.Write(pBuffer, readBytes);
  442. totalReadSize+= readBytes;
  443. int percent = (int)((totalReadSize * 100) / fileSize);
  444. if(percent != percentageComplete)
  445. {
  446. percentageComplete = percent;
  447. (*popUpMsg)->SetProgressBarPercent(percentageComplete);
  448. }
  449. }while(readBytes >= 65536);
  450. writeFile.Close();
  451. ret = TRUE;
  452. }
  453. }
  454. file.Close();
  455. }
  456. }
  457. catch(...)
  458. {
  459. }
  460. //BOOL ret = CopyFile(dbPath, backup, TRUE);
  461. if ((*popUpMsg) != NULL)
  462. {
  463. (*popUpMsg)->HideProgressBar();
  464. (*popUpMsg)->UpdateText(_T("Running Ditto database scripts ..."));
  465. }
  466. return ret;
  467. }
  468. BOOL CreateDB(CString csFile)
  469. {
  470. try
  471. {
  472. CppSQLite3DB db;
  473. db.open(csFile);
  474. db.execDML(_T("PRAGMA auto_vacuum = 1"));
  475. db.execDML(_T("CREATE TABLE Main(")
  476. _T("lID INTEGER PRIMARY KEY AUTOINCREMENT, ")
  477. _T("lDate INTEGER, ")
  478. _T("mText TEXT, ")
  479. _T("lShortCut INTEGER, ")
  480. _T("lDontAutoDelete INTEGER, ")
  481. _T("CRC INTEGER, ")
  482. _T("bIsGroup INTEGER, ")
  483. _T("lParentID INTEGER, ")
  484. _T("QuickPasteText TEXT, ")
  485. _T("clipOrder REAL, ")
  486. _T("clipGroupOrder REAL, ")
  487. _T("globalShortCut INTEGER, ")
  488. _T("lastPasteDate INTEGER, ")
  489. _T("stickyClipOrder REAL, ")
  490. _T("stickyClipGroupOrder REAL, ")
  491. _T("MoveToGroupShortCut INTEGER, ")
  492. _T("GlobalMoveToGroupShortCut INTEGER);"));
  493. db.execDML(_T("CREATE TABLE Data(")
  494. _T("lID INTEGER PRIMARY KEY AUTOINCREMENT, ")
  495. _T("lParentID INTEGER, ")
  496. _T("strClipBoardFormat TEXT, ")
  497. _T("ooData BLOB);"));
  498. db.execDML(_T("CREATE TABLE Types(")
  499. _T("lID INTEGER PRIMARY KEY AUTOINCREMENT, ")
  500. _T("TypeText TEXT);"));
  501. db.execDML(_T("CREATE UNIQUE INDEX Main_ID on Main(lID ASC)"));
  502. db.execDML(_T("CREATE UNIQUE INDEX Data_ID on Data(lID ASC)"));
  503. db.execDML(_T("CREATE INDEX Main_ClipOrder on Main(clipOrder DESC)"));
  504. db.execDML(_T("CREATE INDEX Main_ClipGroupOrder on Main(clipGroupOrder DESC)"));
  505. db.execDML(_T("CREATE INDEX Main_ParentId on Main(lParentID DESC)"));
  506. db.execDML(_T("CREATE INDEX Main_IsGroup on Main(bIsGroup DESC)"));
  507. db.execDML(_T("CREATE INDEX Main_ShortCut on Main(lShortCut DESC)"));
  508. db.execDML(_T("CREATE TRIGGER delete_data_trigger BEFORE DELETE ON Main FOR EACH ROW\n")
  509. _T("BEGIN\n")
  510. _T("INSERT INTO MainDeletes VALUES(old.lID, datetime('now'));\n")
  511. _T("END\n"));
  512. db.execDML(_T("CREATE TABLE CopyBuffers(")
  513. _T("lID INTEGER PRIMARY KEY AUTOINCREMENT, ")
  514. _T("lClipID INTEGER, ")
  515. _T("lCopyBuffer INTEGER)"));
  516. db.execDML(_T("CREATE TABLE MainDeletes(")
  517. _T("clipID INTEGER,")
  518. _T("modifiedDate)"));
  519. db.execDML(_T("CREATE TRIGGER MainDeletes_delete_data_trigger BEFORE DELETE ON MainDeletes FOR EACH ROW\n")
  520. _T("BEGIN\n")
  521. _T("DELETE FROM CopyBuffers WHERE lClipID = old.clipID;\n")
  522. _T("DELETE FROM Data WHERE lParentID = old.clipID;\n")
  523. _T("END\n"));
  524. db.execDML(_T("CREATE INDEX Main_NoGroup ON Main(bIsGroup ASC, stickyClipOrder DESC, clipOrder DESC);"));
  525. db.execDML(_T("CREATE INDEX Main_InGroup ON Main(lParentId ASC, bIsGroup ASC, stickyClipGroupOrder DESC, clipGroupOrder DESC);"));
  526. db.execDML(_T("CREATE INDEX Data_ParentId_Format ON Data(lParentID COLLATE BINARY ASC, strClipBoardFormat COLLATE NOCASE ASC);"));
  527. db.close();
  528. }
  529. CATCH_SQLITE_EXCEPTION_AND_RETURN(FALSE)
  530. return TRUE;
  531. }
  532. BOOL CompactDatabase()
  533. {
  534. // if(!theApp.CloseDB())
  535. // return FALSE;
  536. //
  537. // CString csDBName = GetDBName();
  538. // CString csTempDBName = csDBName;
  539. // csTempDBName.Replace(".mdb", "TempDBName.mdb");
  540. //
  541. // //Compact the database
  542. // try
  543. // {
  544. // CDaoWorkspace::CompactDatabase(csDBName, csTempDBName);//, dbLangGeneral, 0, "andrew");//DATABASE_PASSWORD);
  545. // }
  546. // catch(CDaoException* e)
  547. // {
  548. // AfxMessageBox(e->m_pErrorInfo->m_strDescription);
  549. // DeleteFile(csTempDBName);
  550. // e->Delete();
  551. // return FALSE;
  552. // }
  553. // catch(CMemoryException* e)
  554. // {
  555. // AfxMessageBox("Memory Exception");
  556. // DeleteFile(csTempDBName);
  557. // e->Delete();
  558. // return FALSE;
  559. // }
  560. //
  561. // //Since compacting the database creates a new db delete the old one and replace it
  562. // //with the compacted db
  563. // if(DeleteFile(csDBName))
  564. // {
  565. // try
  566. // {
  567. // CFile::Rename(csTempDBName, csDBName);
  568. // }
  569. // catch(CFileException *e)
  570. // {
  571. // e->ReportError();
  572. // e->Delete();
  573. // return FALSE;
  574. // }
  575. // }
  576. // else
  577. // AfxMessageBox("Error Compacting Database");
  578. return TRUE;
  579. }
  580. BOOL RepairDatabase()
  581. {
  582. // if(!theApp.CloseDB())
  583. // return FALSE;
  584. // try
  585. // {
  586. // CDaoWorkspace::RepairDatabase(GetDBName());
  587. // }
  588. // catch(CDaoException *e)
  589. // {
  590. // AfxMessageBox(e->m_pErrorInfo->m_strDescription);
  591. // e->Delete();
  592. // return FALSE;
  593. // }
  594. return TRUE;
  595. }
  596. BOOL RemoveOldEntries(bool checkIdleTime)
  597. {
  598. Log(StrF(_T("Beginning of RemoveOldEntries MaxEntries: %d - Keep days: %d"), CGetSetOptions::GetMaxEntries(), CGetSetOptions::GetExpiredEntries()));
  599. try
  600. {
  601. CppSQLite3DB db;
  602. CString csDbPath = CGetSetOptions::GetDBPath();
  603. db.open(csDbPath);
  604. if(CGetSetOptions::GetCheckForMaxEntries())
  605. {
  606. long lMax = CGetSetOptions::GetMaxEntries();
  607. if(lMax >= 0)
  608. {
  609. CClipIDs IDs;
  610. int clipId;
  611. 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);
  612. while(q.eof() == false)
  613. {
  614. int shortcut = q.getIntField(_T("lShortCut"));
  615. int dontDelete = q.getIntField(_T("lDontAutoDelete"));
  616. int parentId = q.getIntField(_T("lParentID"));
  617. double stickyClipOrder = q.getFloatField(_T("stickyClipOrder"));
  618. double stickyClipGroupOrder = q.getFloatField(_T("stickyClipGroupOrder"));
  619. //Only delete entries that have no shortcut and don't have the flag set and aren't in groups and
  620. if(shortcut == 0 &&
  621. dontDelete == 0 &&
  622. parentId <= 0 &&
  623. stickyClipOrder == -(2147483647) &&
  624. stickyClipGroupOrder == -(2147483647))
  625. {
  626. clipId = q.getIntField(_T("lID"));
  627. IDs.Add(clipId);
  628. Log(StrF(_T("From MaxEntries - Deleting Id: %d"), clipId));
  629. }
  630. q.nextRow();
  631. }
  632. if(IDs.GetCount() > 0)
  633. {
  634. IDs.DeleteIDs(false, db);
  635. }
  636. }
  637. }
  638. if(CGetSetOptions::GetCheckForExpiredEntries())
  639. {
  640. long lExpire = CGetSetOptions::GetExpiredEntries();
  641. if(lExpire)
  642. {
  643. CTime now = CTime::GetCurrentTime();
  644. now -= CTimeSpan(lExpire, 0, 0, 0);
  645. CClipIDs IDs;
  646. CppSQLite3Query q = db.execQueryEx(_T("SELECT lID FROM Main ")
  647. _T("WHERE lastPasteDate < %d AND ")
  648. _T("bIsGroup = 0 AND lShortCut = 0 AND lParentID <= 0 AND lDontAutoDelete = 0 AND stickyClipOrder = -(2147483647) AND stickyClipGroupOrder = -(2147483647)"), (int)now.GetTime());
  649. while(q.eof() == false)
  650. {
  651. IDs.Add(q.getIntField(_T("lID")));
  652. Log(StrF(_T("From Clips Expire - Deleting Id: %d"), q.getIntField(_T("lID"))));
  653. q.nextRow();
  654. }
  655. if(IDs.GetCount() > 0)
  656. {
  657. IDs.DeleteIDs(false, db);
  658. }
  659. }
  660. }
  661. int toDeleteCount = db.execScalar(_T("SELECT COUNT(clipID) FROM MainDeletes"));
  662. Log(StrF(_T("Before Deleting emptied out data, count: %d, Idle Seconds: %f"), toDeleteCount, IdleSeconds()));
  663. //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
  664. //to lock up
  665. CppSQLite3Query q = db.execQueryEx(_T("SELECT * FROM MainDeletes LIMIT %d"), CGetSetOptions::GetMainDeletesDeleteCount());
  666. int deleteCount = 0;
  667. while(q.eof() == false)
  668. {
  669. double idleSeconds = IdleSeconds();
  670. if(checkIdleTime == false || idleSeconds > CGetSetOptions::GetIdleSecondsBeforeDelete())
  671. {
  672. //delete any data items sitting out there that the main table data was deleted
  673. //this was done to speed up deleted from the main table
  674. deleteCount = db.execDMLEx(_T("DELETE FROM MainDeletes WHERE clipID=%d"), q.getIntField(_T("clipID")));
  675. }
  676. else
  677. {
  678. Log(StrF(_T("Computer has not been idle long enough to delete clips, Min Idle: %d, current Idle: %d"),
  679. CGetSetOptions::GetIdleSecondsBeforeDelete(), idleSeconds));
  680. break;
  681. }
  682. q.nextRow();
  683. }
  684. toDeleteCount = db.execScalar(_T("SELECT COUNT(clipID) FROM MainDeletes"));
  685. Log(StrF(_T("After Deleting emptied out data rows, Count: %d, toDelete: %d"), deleteCount, toDeleteCount));
  686. }
  687. CATCH_SQLITE_EXCEPTION
  688. Log(_T("End of RemoveOldEntries"));
  689. return TRUE;
  690. }
  691. BOOL EnsureDirectory(CString csPath)
  692. {
  693. TCHAR drive[_MAX_DRIVE];
  694. TCHAR dir[_MAX_DIR];
  695. TCHAR fname[_MAX_FNAME];
  696. TCHAR ext[_MAX_EXT];
  697. SPLITPATH(csPath, drive, dir, fname, ext);
  698. CString csDir(drive);
  699. csDir += dir;
  700. if(FileExists(csDir) == FALSE)
  701. {
  702. if(CreateDirectory(csDir, NULL))
  703. return TRUE;
  704. }
  705. else
  706. return TRUE;
  707. return FALSE;
  708. }
  709. // BOOL RunZippApp(CString csCommandLine)
  710. // {
  711. // CString csLocalPath = GETENV(_T("U3_HOST_EXEC_PATH"));
  712. // FIX_CSTRING_PATH(csLocalPath);
  713. //
  714. // CString csZippApp = GETENV(_T("U3_DEVICE_EXEC_PATH"));
  715. // FIX_CSTRING_PATH(csZippApp);
  716. // csZippApp += "7za.exe";
  717. //
  718. // csZippApp += " ";
  719. // csZippApp += csCommandLine;
  720. //
  721. // Log(csZippApp);
  722. //
  723. // STARTUPINFO StartupInfo;
  724. // PROCESS_INFORMATION ProcessInformation;
  725. //
  726. // ZeroMemory(&StartupInfo, sizeof(StartupInfo));
  727. // StartupInfo.cb = sizeof(StartupInfo);
  728. // ZeroMemory(&ProcessInformation, sizeof(ProcessInformation));
  729. //
  730. // StartupInfo.dwFlags = STARTF_USESHOWWINDOW;
  731. // StartupInfo.wShowWindow = SW_HIDE;
  732. //
  733. // BOOL bRet = CreateProcess(NULL, csZippApp.GetBuffer(csZippApp.GetLength()), NULL, NULL, FALSE,
  734. // CREATE_DEFAULT_ERROR_MODE | NORMAL_PRIORITY_CLASS, NULL, csLocalPath,
  735. // &StartupInfo, &ProcessInformation);
  736. //
  737. // if(bRet)
  738. // {
  739. // WaitForSingleObject(ProcessInformation.hProcess, INFINITE);
  740. //
  741. // DWORD dwExitCode;
  742. // GetExitCodeProcess(ProcessInformation.hProcess, &dwExitCode);
  743. //
  744. // CString cs;
  745. // cs.Format(_T("Exit code from unzip = %d"), dwExitCode);
  746. // Log(cs);
  747. //
  748. // if(dwExitCode != 0)
  749. // {
  750. // bRet = FALSE;
  751. // }
  752. // }
  753. // else
  754. // {
  755. // bRet = FALSE;
  756. // Log(_T("Create Process Failed"));
  757. // }
  758. //
  759. // csZippApp.ReleaseBuffer();
  760. //
  761. // return bRet;
  762. // }
  763. // BOOL CopyDownDatabase()
  764. // {
  765. // BOOL bRet = FALSE;
  766. //
  767. // CString csZippedPath = GETENV(_T("U3_APP_DATA_PATH"));
  768. // FIX_CSTRING_PATH(csZippedPath);
  769. //
  770. // CString csUnZippedPath = csZippedPath;
  771. // csUnZippedPath += "Ditto.db";
  772. //
  773. // csZippedPath += "Ditto.7z";
  774. //
  775. // CString csLocalPath = GETENV(_T("U3_HOST_EXEC_PATH"));
  776. // FIX_CSTRING_PATH(csLocalPath);
  777. //
  778. // if(FileExists(csZippedPath))
  779. // {
  780. // CString csCommandLine;
  781. //
  782. // //e = extract
  783. // //surround command line arguments with quotes
  784. // //-aoa = overight files with extracted files
  785. //
  786. // csCommandLine += "e ";
  787. // csCommandLine += "\"";
  788. // csCommandLine += csZippedPath;
  789. // csCommandLine += "\"";
  790. // csCommandLine += " -o";
  791. // csCommandLine += "\"";
  792. // csCommandLine += csLocalPath;
  793. // csCommandLine += "\"";
  794. // csCommandLine += " -aoa";
  795. //
  796. // bRet = RunZippApp(csCommandLine);
  797. //
  798. // csLocalPath += "Ditto.db";
  799. // }
  800. // else if(FileExists(csUnZippedPath))
  801. // {
  802. // csLocalPath += "Ditto.db";
  803. // bRet = CopyFile(csUnZippedPath, csLocalPath, FALSE);
  804. // }
  805. //
  806. // if(FileExists(csLocalPath) == FALSE)
  807. // {
  808. // Log(_T("Failed to copy files from device zip file"));
  809. // }
  810. //
  811. // g_Opt.nLastDbWriteTime = GetLastWriteTime(csLocalPath);
  812. //
  813. // return bRet;
  814. // }
  815. //BOOL CopyUpDatabase()
  816. //{
  817. // CStringA csZippedPath = "C:\\";//getenv("U3_APP_DATA_PATH");
  818. // FIX_CSTRING_PATH(csZippedPath);
  819. // csZippedPath += "Ditto.zip";
  820. // CStringA csLocalPath = GetDBName();//getenv("U3_HOST_EXEC_PATH");
  821. // //FIX_CSTRING_PATH(csLocalPath);
  822. // //csLocalPath += "Ditto.db";
  823. //
  824. // CZipper Zip;
  825. //
  826. // if(Zip.OpenZip(csZippedPath))
  827. // {
  828. // Zip.AddFileToZip(csLocalPath);
  829. // }
  830. //
  831. // return TRUE;
  832. //}