Horizon – Blame information for rev 11

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 1 using System;
2 using System.Collections.Generic;
3 using System.Data.SQLite;
4 using System.Drawing;
5 using System.Drawing.Imaging;
6 using System.Globalization;
7 using System.IO;
8 using System.IO.Compression;
5 office 9 using System.Runtime.CompilerServices;
1 office 10 using System.Security.Cryptography;
11 using System.Threading;
12 using System.Threading.Tasks;
13 using Horizon.Snapshots;
14 using Horizon.Utilities;
15 using Serilog;
16  
17 namespace Horizon.Database
18 {
19 public class SnapshotDatabase : IDisposable
20 {
21 #region Static Fields and Constants
22  
23 private const string CreateTableSql =
24 "CREATE TABLE IF NOT EXISTS \"Snapshots\" ( \"id\" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, \"Name\" TEXT NOT NULL, \"Path\" TEXT NOT NULL, \"Time\" TEXT NOT NULL, \"Hash\" TEXT NOT NULL, \"Data\" BLOB, \"Color\" INTEGER, \"Shot\" BLOB, \"Note\" TEXT, UNIQUE (\"Hash\") ON CONFLICT FAIL)";
25  
26 private const string SetAutoVacuumSql = "PRAGMA auto_vacuum = FULL";
27  
28 private const string SnapshotFileSql =
29 "INSERT INTO \"Snapshots\" ( \"Name\", \"Path\", \"Time\", \"Data\", \"Shot\", \"Color\", \"Hash\" ) VALUES ( @name, @path, @time, zeroblob(@dataLength), zeroblob(@shotLength), @color, @hash )";
30  
31 private const string SnapshotFileNoScreenshotSql =
32 "INSERT INTO \"Snapshots\" ( \"Name\", \"Path\", \"Time\", \"Data\", \"Shot\", \"Color\", \"Hash\" ) VALUES ( @name, @path, @time, zeroblob(@dataLength), null, @color, @hash )";
33  
34 private const string RetrieveSnapshotsSql =
35 "SELECT \"Name\", \"Path\", \"Time\", \"Color\", \"Hash\" FROM \"Snapshots\" ORDER BY datetime(\"Time\") DESC";
36  
37 private const string RetrieveDataPathFromHashSql =
38 "SELECT \"id\", \"Path\", \"Data\" FROM \"Snapshots\" WHERE Hash = @hash";
39  
40 private const string RetrieveDataFromHashSql =
41 "SELECT \"id\", \"Data\" FROM \"Snapshots\" WHERE Hash = @hash";
42  
43 private const string UpdateFileSql =
44 "UPDATE \"Snapshots\" SET Data = zeroblob(@dataLength), Hash = @recomputedHash WHERE Hash = @hash";
45  
46 private const string RemoveSnapshotFromHashSql =
47 "DELETE FROM \"Snapshots\" WHERE Hash = @hash";
48  
49 private const string RemoveScreenshotFromHashSql =
50 "UPDATE \"Snapshots\" SET Shot = null WHERE Hash = @hash";
51  
52 private const string UpdateColorFromHashSql =
53 "UPDATE \"Snapshots\" SET Color = @color WHERE Hash = @hash";
54  
55 private const string UpdateNoteFromHashSql =
56 "UPDATE \"Snapshots\" SET Note = @note WHERE Hash = @hash";
57  
58 private const string UpdateHashFromHashSql = "UPDATE \"Snapshots\" SET Hash = @to WHERE Hash = @from";
59  
60 private const string RelocateFileFromHashSql =
61 "UPDATE \"Snapshots\" SET Path = @path WHERE Hash = @hash";
62  
63 private const string RemoveColorFromHashSql =
64 "UPDATE \"Snapshots\" SET Color = null WHERE Hash = @hash";
65  
66 private const string RetrievePreviewFromHashSql =
67 "SELECT \"id\", \"Note\", \"Shot\" FROM \"Snapshots\" WHERE Hash = @hash";
68  
69 private const string CountSnapshotsSql = "SELECT COUNT(*) FROM \"Snapshots\"";
70  
71 private const string GetLastRowInsertSql = "SELECT last_insert_rowid()";
72  
73 private const string GetRowFromHashSql = "SELECT \"id\" FROM \"Snapshots\" WHERE Hash = @hash";
74  
75 private const string RetrieveTimeFromHash = "SELECT \"Time\" FROM \"Snapshots\" WHERE Hash = @hash";
76  
77 private const string UpdateTimeFromHash = "UPDATE \"Snapshots\" SET Time = @time WHERE Hash = @hash";
78  
79 private static readonly string DatabaseConnectionString = $"Data Source={Constants.DatabaseFilePath};";
80  
81 private static CancellationToken _cancellationToken;
82  
83 #endregion
84  
85 #region Public Events & Delegates
86  
87 public event EventHandler<SnapshotDataUpdateEventArgs> SnapshotDataUpdate;
88  
89 public event EventHandler<SnapshotNoteUpdateEventArgs> SnapshotNoteUpdate;
90  
91 public event EventHandler<SnapshotCreateEventArgs> SnapshotCreate;
92  
93 public event EventHandler<SnapshotRevertEventArgs> SnapshotRevert;
94  
95 #endregion
96  
97 #region Private Delegates, Events, Enums, Properties, Indexers and Fields
98  
99 private readonly CancellationTokenSource _cancellationTokenSource;
5 office 100 private readonly SemaphoreSlim _databaseLock;
10 office 101 private readonly SQLiteConnectionStringBuilder _sqliteConnectionStringBuilder;
1 office 102  
103 #endregion
104  
105 #region Constructors, Destructors and Finalizers
106  
5 office 107 private SnapshotDatabase()
1 office 108 {
5 office 109 Directory.CreateDirectory(Constants.DatabaseDirectory);
110  
111 _databaseLock = new SemaphoreSlim(1, 1);
10 office 112  
113 _sqliteConnectionStringBuilder = new SQLiteConnectionStringBuilder
114 {
115 ConnectionString = DatabaseConnectionString
116 };
5 office 117 }
118  
119 public SnapshotDatabase(CancellationToken cancellationToken) : this()
120 {
1 office 121 _cancellationTokenSource = new CancellationTokenSource();
5 office 122 var localCancellationToken = _cancellationTokenSource.Token;
123 var combinedCancellationTokenSource =
124 CancellationTokenSource.CreateLinkedTokenSource(localCancellationToken, cancellationToken);
125 _cancellationToken = combinedCancellationTokenSource.Token;
1 office 126  
127 CreateDatabase(_cancellationToken).ContinueWith(async createDatabaseTask =>
128 {
129 try
130 {
131 await createDatabaseTask;
132  
133 try
134 {
135 await SetAutoVacuum(_cancellationToken);
136 }
10 office 137 catch (Exception exception)
1 office 138 {
10 office 139 Log.Error(exception, "Unable to set auto vacuum for database.");
1 office 140 }
141 }
3 office 142 catch (Exception exception)
1 office 143 {
10 office 144 Log.Error(exception, "Unable to create database;");
1 office 145 }
146 }).Wait(_cancellationToken);
147 }
148  
149 public void Dispose()
150 {
151 _cancellationTokenSource.Cancel();
152 }
153  
154 #endregion
155  
156 #region Public Methods
157  
11 office 158 public async Task DeleteScreenshotAsync(string hash, CancellationToken cancellationToken)
1 office 159 {
160 var connectionString = new SQLiteConnectionStringBuilder
161 {
162 ConnectionString = DatabaseConnectionString
163 };
164  
5 office 165 await _databaseLock.WaitAsync(cancellationToken);
166 try
1 office 167 {
5 office 168 using (var sqliteConnection = new SQLiteConnection(connectionString.ConnectionString))
169 {
170 await sqliteConnection.OpenAsync(cancellationToken);
1 office 171  
5 office 172 using (var dbTransaction = sqliteConnection.BeginTransaction())
1 office 173 {
5 office 174 // Insert the file change.
175 using (var sqliteCommand =
176 new SQLiteCommand(RemoveScreenshotFromHashSql, sqliteConnection, dbTransaction))
1 office 177 {
178 sqliteCommand.Parameters.AddRange(new[]
179 {
180 new SQLiteParameter("@hash", hash)
181 });
182  
183 sqliteCommand.Prepare();
184  
5 office 185 try
186 {
187 await sqliteCommand.ExecuteNonQueryAsync(cancellationToken);
1 office 188  
5 office 189 dbTransaction.Commit();
190 }
191 catch
192 {
193 dbTransaction.Rollback();
1 office 194  
5 office 195 throw;
196 }
1 office 197 }
198 }
199 }
200 }
5 office 201 finally
202 {
203 _databaseLock.Release();
204 }
1 office 205 }
206  
11 office 207 public async Task NormalizeTimeAsync(string hash, CancellationToken cancellationToken)
1 office 208 {
5 office 209 await _databaseLock.WaitAsync(cancellationToken);
210 try
1 office 211 {
5 office 212 using (var sqliteConnection =
10 office 213 new SQLiteConnection(_sqliteConnectionStringBuilder.ConnectionString))
5 office 214 {
215 await sqliteConnection.OpenAsync(cancellationToken);
1 office 216  
5 office 217 using (var readSQLiteCommand = new SQLiteCommand(RetrieveTimeFromHash, sqliteConnection))
1 office 218 {
5 office 219 readSQLiteCommand.Parameters.AddRange(new[]
220 {
221 new SQLiteParameter("@hash", hash)
222 });
1 office 223  
5 office 224 readSQLiteCommand.Prepare();
1 office 225  
5 office 226 using (var sqlDataReader = await readSQLiteCommand.ExecuteReaderAsync(cancellationToken))
1 office 227 {
5 office 228 using (var dbTransaction = sqliteConnection.BeginTransaction())
1 office 229 {
5 office 230 try
1 office 231 {
5 office 232 while (await sqlDataReader.ReadAsync(cancellationToken))
1 office 233 {
5 office 234 var time = (string)sqlDataReader["Time"];
1 office 235  
5 office 236 // Skip if already ISO 8601
237 if (DateTime.TryParseExact(time,
238 "yyyy-MM-ddTHH:mm:ss.fff",
239 CultureInfo.InvariantCulture,
240 DateTimeStyles.None, out _))
241 {
242 continue;
243 }
1 office 244  
5 office 245 if (!DateTime.TryParse(time, out var dateTime))
1 office 246 {
5 office 247 dateTime = DateTime.Now;
248 }
1 office 249  
5 office 250 using (var writeSQLiteCommand =
251 new SQLiteCommand(UpdateTimeFromHash, sqliteConnection, dbTransaction))
252 {
253 writeSQLiteCommand.Parameters.AddRange(new[]
254 {
255 new SQLiteParameter("@time",
256 dateTime.ToString("yyyy-MM-ddTHH:mm:ss.fff")),
257 new SQLiteParameter("@hash", hash)
258 });
1 office 259  
5 office 260 writeSQLiteCommand.Prepare();
261  
262 await writeSQLiteCommand.ExecuteNonQueryAsync(cancellationToken);
263 }
1 office 264 }
5 office 265  
266 dbTransaction.Commit();
1 office 267 }
5 office 268 catch
269 {
270 dbTransaction.Rollback();
1 office 271  
5 office 272 throw;
273 }
1 office 274 }
275 }
276 }
277 }
278 }
5 office 279 finally
280 {
281 _databaseLock.Release();
282 }
1 office 283 }
284  
11 office 285 public async Task<long> CountSnapshotsAsync(CancellationToken cancellationToken)
1 office 286 {
5 office 287 await _databaseLock.WaitAsync(cancellationToken);
288 try
1 office 289 {
10 office 290 using (var sqliteConnection = new SQLiteConnection(_sqliteConnectionStringBuilder.ConnectionString))
1 office 291 {
5 office 292 await sqliteConnection.OpenAsync(cancellationToken);
1 office 293  
5 office 294 // Insert the file change.
295 using (var sqliteCommand = new SQLiteCommand(CountSnapshotsSql, sqliteConnection))
296 {
297 long count = 0;
1 office 298  
5 office 299 sqliteCommand.Prepare();
300  
301 using (var sqlDataReader = await sqliteCommand.ExecuteReaderAsync(cancellationToken))
1 office 302 {
5 office 303 while (await sqlDataReader.ReadAsync(cancellationToken))
1 office 304 {
5 office 305 if (!(sqlDataReader[0] is long dbCount))
306 {
307 count = -1;
308 break;
309 }
310  
311 count = dbCount;
1 office 312 }
313  
5 office 314 return count;
1 office 315 }
316 }
317 }
318 }
5 office 319 finally
320 {
321 _databaseLock.Release();
322 }
1 office 323 }
324  
11 office 325 public async IAsyncEnumerable<Snapshot> LoadSnapshotsAsync([EnumeratorCancellation] CancellationToken cancellationToken)
1 office 326 {
5 office 327 await _databaseLock.WaitAsync(cancellationToken);
328 try
1 office 329 {
5 office 330 using (var sqliteConnection =
10 office 331 new SQLiteConnection(_sqliteConnectionStringBuilder.ConnectionString))
1 office 332 {
5 office 333 await sqliteConnection.OpenAsync(cancellationToken);
1 office 334  
5 office 335 // Insert the file change.
336 using (var sqliteCommand = new SQLiteCommand(RetrieveSnapshotsSql, sqliteConnection))
1 office 337 {
5 office 338 sqliteCommand.Prepare();
339  
340 using (var sqlDataReader = await sqliteCommand.ExecuteReaderAsync(cancellationToken))
1 office 341 {
5 office 342 //var snapshots = new List<Snapshot>();
343 while (await sqlDataReader.ReadAsync(cancellationToken))
344 {
345 var name = (string)sqlDataReader["Name"];
346 var path = (string)sqlDataReader["Path"];
347 var time = (string)sqlDataReader["Time"];
348 var hash = (string)sqlDataReader["Hash"];
1 office 349  
5 office 350 var color = Color.Empty;
1 office 351  
5 office 352 if (!(sqlDataReader["Color"] is DBNull))
353 {
354 var dbColor = Convert.ToInt32(sqlDataReader["Color"]);
1 office 355  
5 office 356 switch (dbColor)
357 {
358 case 0:
359 color = Color.Empty;
360 break;
361 default:
362 color = Color.FromArgb(dbColor);
363 break;
364 }
1 office 365 }
5 office 366  
367 yield return new Snapshot(name, path, time, hash, color);
1 office 368 }
369 }
370 }
371 }
372 }
5 office 373 finally
374 {
375 _databaseLock.Release();
376 }
1 office 377 }
378  
11 office 379 public async Task CreateSnapshotAsync(string name, string path, Color color, CancellationToken cancellationToken)
1 office 380 {
5 office 381 await _databaseLock.WaitAsync(cancellationToken);
382 try
1 office 383 {
5 office 384 using (var sqliteConnection =
10 office 385 new SQLiteConnection(_sqliteConnectionStringBuilder.ConnectionString))
5 office 386 {
387 await sqliteConnection.OpenAsync(cancellationToken);
1 office 388  
5 office 389 using (var dbTransaction = sqliteConnection.BeginTransaction())
1 office 390 {
5 office 391 try
1 office 392 {
5 office 393 using (var md5 = MD5.Create())
1 office 394 {
5 office 395 using (var hashMemoryStream = new MemoryStream())
1 office 396 {
5 office 397 using (var fileStream =
398 await Miscellaneous.GetFileStream(path, FileMode.Open, FileAccess.Read,
399 FileShare.Read,
400 cancellationToken))
401 {
402 fileStream.Position = 0L;
403 await fileStream.CopyToAsync(hashMemoryStream);
1 office 404  
5 office 405 hashMemoryStream.Position = 0L;
406 var hash = md5.ComputeHash(hashMemoryStream);
407 var hashHex = BitConverter.ToString(hash).Replace("-", "")
408 .ToLowerInvariant();
1 office 409  
5 office 410 using (var fileMemoryStream = new MemoryStream())
1 office 411 {
5 office 412 using (var fileZipStream =
413 new GZipStream(fileMemoryStream, CompressionMode.Compress, true))
414 {
415 fileStream.Position = 0L;
416 await fileStream.CopyToAsync(fileZipStream);
417 fileZipStream.Close();
1 office 418  
5 office 419 var time = DateTime.Now.ToString("yyyy-MM-ddTHH:mm:ss.fff");
1 office 420  
5 office 421 fileMemoryStream.Position = 0L;
1 office 422  
5 office 423 // Insert the file change.
424 using (var sqliteCommand =
425 new SQLiteCommand(SnapshotFileNoScreenshotSql, sqliteConnection,
426 dbTransaction))
1 office 427 {
5 office 428 sqliteCommand.Parameters.AddRange(new[]
429 {
430 new SQLiteParameter("@name", name),
431 new SQLiteParameter("@time", time),
432 new SQLiteParameter("@path", path),
433 new SQLiteParameter("@dataLength",
434 fileMemoryStream.Length),
435 new SQLiteParameter("@hash", hashHex)
436 });
1 office 437  
5 office 438 var numeric = color.ToArgb();
439 switch (numeric)
440 {
441 case 0:
442 sqliteCommand.Parameters.Add(
443 new SQLiteParameter("@color", null));
444 break;
445 default:
446 sqliteCommand.Parameters.Add(
447 new SQLiteParameter("@color", numeric));
448 break;
449 }
1 office 450  
5 office 451 sqliteCommand.Prepare();
1 office 452  
5 office 453 await sqliteCommand.ExecuteNonQueryAsync(cancellationToken);
454 }
1 office 455  
5 office 456 // Insert the data blobs.
457 using (var sqliteCommand =
458 new SQLiteCommand(GetLastRowInsertSql, sqliteConnection,
459 dbTransaction))
460 {
461 sqliteCommand.Prepare();
1 office 462  
5 office 463 var rowId =
464 (long)await sqliteCommand.ExecuteScalarAsync(cancellationToken);
1 office 465  
5 office 466 using (var sqliteBlob =
467 SQLiteBlob.Create(sqliteConnection, "main", "Snapshots",
468 "Data",
469 rowId,
470 false))
471 {
472 var fileMemoryStreamData = fileMemoryStream.ToArray();
1 office 473  
5 office 474 sqliteBlob.Write(fileMemoryStreamData,
475 fileMemoryStreamData.Length,
476 0);
477 }
1 office 478 }
479  
5 office 480 dbTransaction.Commit();
1 office 481  
5 office 482 SnapshotCreate?.Invoke(this,
483 new SnapshotCreateSuccessEventArgs(name, time, path, color,
484 hashHex));
485 }
1 office 486 }
487 }
488 }
489 }
490 }
5 office 491 catch (SQLiteException exception)
492 {
493 dbTransaction.Rollback();
1 office 494  
5 office 495 if (exception.ResultCode != SQLiteErrorCode.Constraint)
496 {
497 SnapshotCreate?.Invoke(this,
498 new SnapshotCreateFailureEventArgs(name, path, color, exception));
499 }
500  
501 throw;
502 }
503 catch (Exception exception)
1 office 504 {
5 office 505 dbTransaction.Rollback();
506  
1 office 507 SnapshotCreate?.Invoke(this,
508 new SnapshotCreateFailureEventArgs(name, path, color, exception));
5 office 509  
510 throw;
1 office 511 }
512 }
513 }
514 }
5 office 515 finally
516 {
517 _databaseLock.Release();
518 }
1 office 519 }
520  
11 office 521 public async Task CreateSnapshotAsync(string name, string path,
1 office 522 Bitmap shot, Color color, CancellationToken cancellationToken)
523 {
5 office 524 await _databaseLock.WaitAsync(cancellationToken);
525 try
1 office 526 {
5 office 527 using (var sqliteConnection =
10 office 528 new SQLiteConnection(_sqliteConnectionStringBuilder.ConnectionString))
5 office 529 {
530 await sqliteConnection.OpenAsync(cancellationToken);
1 office 531  
5 office 532 using (var dbTransaction = sqliteConnection.BeginTransaction())
1 office 533 {
5 office 534 try
1 office 535 {
5 office 536 using (var md5 = MD5.Create())
1 office 537 {
5 office 538 using (var hashMemoryStream = new MemoryStream())
1 office 539 {
5 office 540 using (var fileStream =
541 await Miscellaneous.GetFileStream(path, FileMode.Open, FileAccess.Read,
542 FileShare.Read,
543 cancellationToken))
544 {
545 fileStream.Position = 0L;
546 await fileStream.CopyToAsync(hashMemoryStream);
1 office 547  
5 office 548 hashMemoryStream.Position = 0L;
549 var hash = md5.ComputeHash(hashMemoryStream);
550 var hashHex = BitConverter.ToString(hash).Replace("-", "")
551 .ToLowerInvariant();
1 office 552  
5 office 553 using (var fileMemoryStream = new MemoryStream())
1 office 554 {
5 office 555 using (var fileZipStream =
556 new GZipStream(fileMemoryStream, CompressionMode.Compress, true))
557 {
558 fileStream.Position = 0L;
559 await fileStream.CopyToAsync(fileZipStream);
560 fileZipStream.Close();
1 office 561  
5 office 562 using (var bitmapMemoryStream = new MemoryStream())
1 office 563 {
5 office 564 using (var bitmapZipStream =
565 new GZipStream(bitmapMemoryStream, CompressionMode.Compress,
566 true))
567 {
568 shot.Save(bitmapZipStream, ImageFormat.Bmp);
569 bitmapZipStream.Close();
1 office 570  
5 office 571 var time = DateTime.Now.ToString("yyyy-MM-ddTHH:mm:ss.fff");
1 office 572  
5 office 573 fileMemoryStream.Position = 0L;
574 bitmapMemoryStream.Position = 0L;
1 office 575  
5 office 576 // Insert the file change.
577 using (var sqliteCommand =
578 new SQLiteCommand(SnapshotFileSql, sqliteConnection,
579 dbTransaction))
1 office 580 {
5 office 581 sqliteCommand.Parameters.AddRange(new[]
582 {
583 new SQLiteParameter("@name", name),
584 new SQLiteParameter("@time", time),
585 new SQLiteParameter("@path", path),
586 new SQLiteParameter("@shotLength",
587 bitmapMemoryStream.Length),
588 new SQLiteParameter("@dataLength",
589 fileMemoryStream.Length),
590 new SQLiteParameter("@hash", hashHex)
591 });
1 office 592  
5 office 593 var numeric = color.ToArgb();
594 switch (numeric)
595 {
596 case 0:
597 sqliteCommand.Parameters.Add(
598 new SQLiteParameter("@color", null));
599 break;
600 default:
601 sqliteCommand.Parameters.Add(
602 new SQLiteParameter("@color", numeric));
603 break;
604 }
605  
606 sqliteCommand.Prepare();
607  
608 await sqliteCommand.ExecuteNonQueryAsync(cancellationToken);
1 office 609 }
610  
5 office 611 // Insert the data blobs.
612 using (var sqliteCommand =
613 new SQLiteCommand(GetLastRowInsertSql, sqliteConnection,
614 dbTransaction))
615 {
616 sqliteCommand.Prepare();
1 office 617  
5 office 618 var rowId =
619 (long)await sqliteCommand.ExecuteScalarAsync(
620 cancellationToken);
1 office 621  
5 office 622 using (var sqliteBlob =
623 SQLiteBlob.Create(sqliteConnection, "main",
624 "Snapshots",
625 "Data",
626 rowId,
627 false))
628 {
629 var fileMemoryStreamData = fileMemoryStream.ToArray();
1 office 630  
5 office 631 sqliteBlob.Write(fileMemoryStreamData,
632 fileMemoryStreamData.Length,
633 0);
634 }
1 office 635  
5 office 636 using (var sqliteBlob =
637 SQLiteBlob.Create(sqliteConnection, "main",
638 "Snapshots",
639 "Shot",
640 rowId,
641 false))
642 {
643 var bitmapMemoryStreamData =
644 bitmapMemoryStream.ToArray();
1 office 645  
5 office 646 sqliteBlob.Write(bitmapMemoryStreamData,
647 bitmapMemoryStreamData.Length,
648 0);
649 }
1 office 650 }
651  
5 office 652 dbTransaction.Commit();
1 office 653  
5 office 654 SnapshotCreate?.Invoke(this,
655 new SnapshotCreateSuccessEventArgs(name, time, path, color,
656 hashHex));
1 office 657 }
658 }
659 }
660 }
661 }
662 }
663 }
664 }
5 office 665 catch (SQLiteException exception)
666 {
667 dbTransaction.Rollback();
1 office 668  
5 office 669 if (exception.ResultCode != SQLiteErrorCode.Constraint)
670 {
671 SnapshotCreate?.Invoke(this,
672 new SnapshotCreateFailureEventArgs(name, path, color, exception));
673 }
674  
675 throw;
676 }
677 catch (Exception exception)
1 office 678 {
5 office 679 dbTransaction.Rollback();
680  
1 office 681 SnapshotCreate?.Invoke(this,
682 new SnapshotCreateFailureEventArgs(name, path, color, exception));
5 office 683  
684 throw;
1 office 685 }
686 }
687 }
688 }
5 office 689 finally
690 {
691 _databaseLock.Release();
692 }
1 office 693 }
694  
11 office 695 public async Task SaveFileAsync(string path, string hash, CancellationToken cancellationToken)
1 office 696 {
5 office 697 await _databaseLock.WaitAsync(cancellationToken);
698 try
1 office 699 {
10 office 700 using (var sqliteConnection = new SQLiteConnection(_sqliteConnectionStringBuilder.ConnectionString))
5 office 701 {
702 await sqliteConnection.OpenAsync(cancellationToken);
1 office 703  
5 office 704 // Insert the file change.
705 using (var sqliteCommand =
706 new SQLiteCommand(RetrieveDataPathFromHashSql, sqliteConnection))
1 office 707 {
5 office 708 sqliteCommand.Parameters.AddRange(new[]
709 {
710 new SQLiteParameter("@hash", hash)
711 });
1 office 712  
5 office 713 sqliteCommand.Prepare();
1 office 714  
5 office 715 using (var sqlDataReader = await sqliteCommand.ExecuteReaderAsync(cancellationToken))
1 office 716 {
5 office 717 while (await sqlDataReader.ReadAsync(cancellationToken))
1 office 718 {
5 office 719 // Create directories if they do not exist.
720 var dir = Path.GetDirectoryName(path);
1 office 721  
5 office 722 if (dir != null && !Directory.Exists(dir))
1 office 723 {
5 office 724 Directory.CreateDirectory(dir);
725 }
1 office 726  
5 office 727 using (var readStream = sqlDataReader.GetStream(2))
728 {
729 using (var fileStream =
730 await Miscellaneous.GetFileStream(path, FileMode.Create, FileAccess.Write,
731 FileShare.Write,
732 cancellationToken))
1 office 733 {
5 office 734 readStream.Position = 0L;
735  
736 using (var zipStream = new GZipStream(readStream, CompressionMode.Decompress))
737 {
738 await zipStream.CopyToAsync(fileStream);
739 }
1 office 740 }
741 }
742 }
743 }
744 }
745 }
746 }
5 office 747 finally
748 {
749 _databaseLock.Release();
750 }
1 office 751 }
752  
11 office 753 public async Task RevertFileAsync(string name, string hash, CancellationToken cancellationToken, bool atomic = true)
1 office 754 {
5 office 755 await _databaseLock.WaitAsync(cancellationToken);
756 try
1 office 757 {
10 office 758 using (var sqliteConnection = new SQLiteConnection(_sqliteConnectionStringBuilder.ConnectionString))
5 office 759 {
760 await sqliteConnection.OpenAsync(cancellationToken);
1 office 761  
5 office 762 // Insert the file change.
763 using (var sqliteCommand =
764 new SQLiteCommand(RetrieveDataPathFromHashSql, sqliteConnection))
1 office 765 {
5 office 766 try
1 office 767 {
5 office 768 sqliteCommand.Parameters.AddRange(new[]
769 {
770 new SQLiteParameter("@hash", hash)
771 });
1 office 772  
5 office 773 sqliteCommand.Prepare();
1 office 774  
5 office 775 using (var sqlDataReader = await sqliteCommand.ExecuteReaderAsync(cancellationToken))
1 office 776 {
5 office 777 while (await sqlDataReader.ReadAsync(cancellationToken))
778 {
779 var path = (string)sqlDataReader["Path"];
1 office 780  
5 office 781 // Create directories if they do not exist.
782 var dir = Path.GetDirectoryName(path);
1 office 783  
5 office 784 if (dir != null && !Directory.Exists(dir))
785 {
786 Directory.CreateDirectory(dir);
787 }
1 office 788  
5 office 789 switch (atomic)
790 {
791 case true:
792 // Atomic
793 var temp = Path.Combine(Path.GetDirectoryName(path),
794 $"{Path.GetFileName(path)}.temp");
1 office 795  
5 office 796 using (var readStream = sqlDataReader.GetStream(2))
1 office 797 {
5 office 798 using (var fileStream = new FileStream(temp, FileMode.Create,
799 FileAccess.Write,
800 FileShare.None))
1 office 801 {
5 office 802 using (var zipStream =
803 new GZipStream(readStream, CompressionMode.Decompress))
804 {
805 zipStream.CopyTo(fileStream);
806 }
1 office 807 }
808 }
809  
810 try
811 {
5 office 812 File.Replace(temp, path, null, true);
1 office 813 }
5 office 814 catch
1 office 815 {
5 office 816 try
817 {
818 File.Delete(temp);
819 }
820 catch (Exception exception)
821 {
822 // Suppress deletion errors of temporary file.
823 Log.Warning(exception, "Could not delete temporary file.", temp);
824 }
825  
826 throw;
1 office 827 }
828  
5 office 829 break;
830 default:
831 // Asynchronous
832 using (var readStream = sqlDataReader.GetStream(2))
1 office 833 {
5 office 834 using (var fileStream =
835 await Miscellaneous.GetFileStream(path, FileMode.Create,
836 FileAccess.Write,
837 FileShare.Write,
838 cancellationToken))
839 {
840 readStream.Position = 0L;
1 office 841  
5 office 842 using (var zipStream =
843 new GZipStream(readStream, CompressionMode.Decompress))
844 {
845 await zipStream.CopyToAsync(fileStream);
846 }
1 office 847 }
848 }
849  
850  
5 office 851 break;
852 }
853  
854 SnapshotRevert?.Invoke(this, new SnapshotRevertSuccessEventArgs(name));
1 office 855 }
856 }
857 }
5 office 858 catch
859 {
860 SnapshotRevert?.Invoke(this, new SnapshotRevertFailureEventArgs(name));
1 office 861  
5 office 862 throw;
863 }
1 office 864 }
865 }
866 }
5 office 867 finally
868 {
869 _databaseLock.Release();
870 }
1 office 871 }
872  
11 office 873 public async Task RemoveFileFastAsync(IEnumerable<string> hashes, CancellationToken cancellationToken)
1 office 874 {
5 office 875 await _databaseLock.WaitAsync(cancellationToken);
876 try
1 office 877 {
10 office 878 using (var sqliteConnection = new SQLiteConnection(_sqliteConnectionStringBuilder.ConnectionString))
5 office 879 {
880 await sqliteConnection.OpenAsync(cancellationToken);
1 office 881  
5 office 882 using (var dbTransaction = sqliteConnection.BeginTransaction())
1 office 883 {
5 office 884 try
885 {
886 var transactionCommands = new List<Task>();
1 office 887  
5 office 888 foreach (var hash in hashes)
1 office 889 {
5 office 890 // Insert the file change.
891 using (var sqliteCommand =
892 new SQLiteCommand(RemoveSnapshotFromHashSql, sqliteConnection, dbTransaction))
1 office 893 {
5 office 894 sqliteCommand.Parameters.AddRange(new[]
895 {
896 new SQLiteParameter("@hash", hash)
897 });
1 office 898  
5 office 899 sqliteCommand.Prepare();
1 office 900  
5 office 901 var command = sqliteCommand.ExecuteNonQueryAsync(cancellationToken);
1 office 902  
5 office 903 transactionCommands.Add(command);
904 }
1 office 905 }
906  
5 office 907 await Task.WhenAll(transactionCommands);
1 office 908  
5 office 909 dbTransaction.Commit();
910 }
911 catch
912 {
913 dbTransaction.Rollback();
1 office 914  
5 office 915 throw;
916 }
1 office 917 }
918 }
919 }
5 office 920 finally
921 {
922 _databaseLock.Release();
923 }
1 office 924 }
925  
11 office 926 public async Task RemoveFileAsync(string hash, CancellationToken cancellationToken)
1 office 927 {
5 office 928 await _databaseLock.WaitAsync(cancellationToken);
929 try
1 office 930 {
10 office 931 using (var sqliteConnection = new SQLiteConnection(_sqliteConnectionStringBuilder.ConnectionString))
5 office 932 {
933 await sqliteConnection.OpenAsync(cancellationToken);
1 office 934  
5 office 935 using (var dbTransaction = sqliteConnection.BeginTransaction())
1 office 936 {
5 office 937 // Insert the file change.
938 using (var sqliteCommand =
939 new SQLiteCommand(RemoveSnapshotFromHashSql, sqliteConnection, dbTransaction))
1 office 940 {
941 sqliteCommand.Parameters.AddRange(new[]
942 {
943 new SQLiteParameter("@hash", hash)
944 });
945  
946 sqliteCommand.Prepare();
947  
5 office 948 try
949 {
950 await sqliteCommand.ExecuteNonQueryAsync(cancellationToken);
1 office 951  
5 office 952 dbTransaction.Commit();
953 }
954 catch
955 {
956 dbTransaction.Rollback();
1 office 957  
5 office 958 throw;
959 }
1 office 960 }
961 }
962 }
963 }
5 office 964 finally
965 {
966 _databaseLock.Release();
967 }
1 office 968 }
969  
11 office 970 public async Task UpdateColorAsync(string hash, Color color, CancellationToken cancellationToken)
1 office 971 {
5 office 972 await _databaseLock.WaitAsync(cancellationToken);
973  
974 try
1 office 975 {
10 office 976 using (var sqliteConnection = new SQLiteConnection(_sqliteConnectionStringBuilder.ConnectionString))
5 office 977 {
978 await sqliteConnection.OpenAsync(cancellationToken);
1 office 979  
5 office 980 using (var dbTransaction = sqliteConnection.BeginTransaction())
1 office 981 {
5 office 982 // Insert the file change.
983 using (var sqliteCommand =
984 new SQLiteCommand(UpdateColorFromHashSql, sqliteConnection, dbTransaction))
1 office 985 {
986 sqliteCommand.Parameters.AddRange(new[]
987 {
988 new SQLiteParameter("@hash", hash),
989 new SQLiteParameter("@color", color.ToArgb())
990 });
991  
992 sqliteCommand.Prepare();
993  
5 office 994 try
995 {
996 await sqliteCommand.ExecuteNonQueryAsync(cancellationToken);
1 office 997  
5 office 998 dbTransaction.Commit();
999 }
1000 catch
1001 {
1002 dbTransaction.Rollback();
1 office 1003  
5 office 1004 throw;
1005 }
1 office 1006 }
1007 }
1008 }
1009 }
5 office 1010 finally
1011 {
1012 _databaseLock.Release();
1013 }
1 office 1014 }
1015  
11 office 1016 public async Task RemoveColorAsync(string hash, CancellationToken cancellationToken)
1 office 1017 {
5 office 1018 await _databaseLock.WaitAsync(cancellationToken);
1019 try
1 office 1020 {
10 office 1021 using (var sqliteConnection = new SQLiteConnection(_sqliteConnectionStringBuilder.ConnectionString))
5 office 1022 {
1023 await sqliteConnection.OpenAsync(cancellationToken);
1 office 1024  
5 office 1025 using (var dbTransaction = sqliteConnection.BeginTransaction())
1 office 1026 {
5 office 1027 // Insert the file change.
1028 using (var sqliteCommand =
1029 new SQLiteCommand(RemoveColorFromHashSql, sqliteConnection, dbTransaction))
1 office 1030 {
1031 sqliteCommand.Parameters.AddRange(new[]
1032 {
1033 new SQLiteParameter("@hash", hash)
1034 });
1035  
1036 sqliteCommand.Prepare();
1037  
5 office 1038 try
1039 {
1040 await sqliteCommand.ExecuteNonQueryAsync(cancellationToken);
1 office 1041  
5 office 1042 dbTransaction.Commit();
1043 }
1044 catch
1045 {
1046 dbTransaction.Rollback();
1 office 1047  
5 office 1048 throw;
1049 }
1 office 1050 }
1051 }
1052 }
1053 }
5 office 1054 finally
1055 {
1056 _databaseLock.Release();
1057 }
1 office 1058 }
1059  
11 office 1060 public async Task<SnapshotPreview> RetrievePreviewAsync(string hash, CancellationToken cancellationToken)
1 office 1061 {
5 office 1062 await _databaseLock.WaitAsync(cancellationToken);
1063 try
1 office 1064 {
10 office 1065 using (var sqliteConnection = new SQLiteConnection(_sqliteConnectionStringBuilder.ConnectionString))
5 office 1066 {
1067 await sqliteConnection.OpenAsync(cancellationToken);
1 office 1068  
5 office 1069 // Insert the file change.
1070 using (var sqliteCommand = new SQLiteCommand(RetrievePreviewFromHashSql, sqliteConnection))
1 office 1071 {
5 office 1072 sqliteCommand.Parameters.AddRange(new[]
1073 {
1074 new SQLiteParameter("@hash", hash)
1075 });
1 office 1076  
5 office 1077 var note = string.Empty;
1 office 1078  
5 office 1079 sqliteCommand.Prepare();
1 office 1080  
5 office 1081 using (var sqlDataReader = await sqliteCommand.ExecuteReaderAsync(cancellationToken))
1 office 1082 {
5 office 1083 while (await sqlDataReader.ReadAsync(cancellationToken))
1 office 1084 {
5 office 1085 if (!(sqlDataReader["Note"] is DBNull))
1 office 1086 {
5 office 1087 note = (string)sqlDataReader["Note"];
1 office 1088 }
1089  
5 office 1090 Bitmap shot = null;
1 office 1091  
5 office 1092 if (!(sqlDataReader["Shot"] is DBNull))
1 office 1093 {
5 office 1094 var readStream = sqlDataReader.GetStream(2);
1 office 1095  
1096 readStream.Position = 0L;
1097  
5 office 1098 using (var zipStream = new GZipStream(readStream, CompressionMode.Decompress))
1 office 1099 {
5 office 1100 using (var image = Image.FromStream(zipStream))
1101 {
1102 shot = new Bitmap(image);
1103 }
1 office 1104 }
1105 }
5 office 1106  
1107 return new SnapshotPreview(hash, shot, note);
1 office 1108 }
5 office 1109  
1110 return null;
1 office 1111 }
1112 }
1113 }
1114 }
5 office 1115 finally
1116 {
1117 _databaseLock.Release();
1118 }
1 office 1119 }
1120  
11 office 1121 public async Task<MemoryStream> RetrieveFileStreamAsync(string hash, CancellationToken cancellationToken)
1 office 1122 {
5 office 1123 await _databaseLock.WaitAsync(cancellationToken);
1124 try
1 office 1125 {
10 office 1126 using (var sqliteConnection = new SQLiteConnection(_sqliteConnectionStringBuilder.ConnectionString))
5 office 1127 {
1128 await sqliteConnection.OpenAsync(cancellationToken);
1 office 1129  
5 office 1130 // Insert the file change.
1131 using (var sqliteCommand = new SQLiteCommand(RetrieveDataFromHashSql, sqliteConnection))
1 office 1132 {
5 office 1133 sqliteCommand.Parameters.AddRange(new[]
1134 {
1135 new SQLiteParameter("@hash", hash)
1136 });
1 office 1137  
5 office 1138 sqliteCommand.Prepare();
1 office 1139  
5 office 1140 using (var sqlDataReader = await sqliteCommand.ExecuteReaderAsync(cancellationToken))
1 office 1141 {
5 office 1142 while (await sqlDataReader.ReadAsync(cancellationToken))
1 office 1143 {
5 office 1144 using (var readStream = sqlDataReader.GetStream(1))
1 office 1145 {
5 office 1146 using (var memoryStream = new MemoryStream())
1147 {
1148 readStream.Position = 0L;
1 office 1149  
5 office 1150 await readStream.CopyToAsync(memoryStream);
1 office 1151  
5 office 1152 memoryStream.Position = 0L;
1 office 1153  
5 office 1154 using (var zipStream = new GZipStream(memoryStream, CompressionMode.Decompress))
1155 {
1156 // Do not dispose the returned stream and leave it up to callers to dispose.
1157 var outputStream = new MemoryStream();
1 office 1158  
5 office 1159 await zipStream.CopyToAsync(outputStream);
1 office 1160  
5 office 1161 outputStream.Position = 0L;
1 office 1162  
5 office 1163 return outputStream;
1164 }
1 office 1165 }
1166 }
1167 }
5 office 1168  
1169 return null;
1 office 1170 }
1171 }
1172 }
1173 }
5 office 1174 finally
1175 {
1176 _databaseLock.Release();
1177 }
1 office 1178 }
1179  
11 office 1180 public async Task RelocateFileAsync(string hash, string path, CancellationToken cancellationToken)
1 office 1181 {
5 office 1182 await _databaseLock.WaitAsync(cancellationToken);
1183 try
1 office 1184 {
10 office 1185 using (var sqliteConnection = new SQLiteConnection(_sqliteConnectionStringBuilder.ConnectionString))
5 office 1186 {
1187 await sqliteConnection.OpenAsync(cancellationToken);
1 office 1188  
5 office 1189 using (var dbTransaction = sqliteConnection.BeginTransaction())
1 office 1190 {
5 office 1191 // Insert the file change.
1192 using (var sqliteCommand =
1193 new SQLiteCommand(RelocateFileFromHashSql, sqliteConnection, dbTransaction))
1 office 1194 {
1195 sqliteCommand.Parameters.AddRange(new[]
1196 {
1197 new SQLiteParameter("@hash", hash),
1198 new SQLiteParameter("@path", path)
1199 });
1200  
1201 sqliteCommand.Prepare();
1202  
5 office 1203 try
1204 {
1205 await sqliteCommand.ExecuteNonQueryAsync(cancellationToken);
1 office 1206  
5 office 1207 dbTransaction.Commit();
1208 }
1209 catch
1210 {
1211 dbTransaction.Rollback();
1 office 1212  
5 office 1213 throw;
1214 }
1 office 1215 }
1216 }
1217 }
1218 }
5 office 1219  
1220 finally
1221 {
1222 _databaseLock.Release();
1223 }
1 office 1224 }
1225  
11 office 1226 public async Task UpdateNoteAsync(string hash, string note, CancellationToken cancellationToken)
1 office 1227 {
5 office 1228 await _databaseLock.WaitAsync(cancellationToken);
1229 try
1 office 1230 {
10 office 1231 using (var sqliteConnection = new SQLiteConnection(_sqliteConnectionStringBuilder.ConnectionString))
5 office 1232 {
1233 await sqliteConnection.OpenAsync(cancellationToken);
1 office 1234  
5 office 1235 using (var dbTransaction = sqliteConnection.BeginTransaction())
1 office 1236 {
5 office 1237 // Insert the file change.
1238 using (var sqliteCommand =
1239 new SQLiteCommand(UpdateNoteFromHashSql, sqliteConnection, dbTransaction))
1 office 1240 {
1241 sqliteCommand.Parameters.AddRange(new[]
1242 {
1243 new SQLiteParameter("@hash", hash),
1244 new SQLiteParameter("@note", note)
1245 });
1246  
1247 sqliteCommand.Prepare();
1248  
5 office 1249 try
1250 {
1251 await sqliteCommand.ExecuteNonQueryAsync(cancellationToken);
1 office 1252  
5 office 1253 dbTransaction.Commit();
1 office 1254  
5 office 1255 SnapshotNoteUpdate?.Invoke(this, new SnapshotNoteUpdateSuccessEventArgs(note));
1256 }
1257 catch
1258 {
1259 dbTransaction.Rollback();
1 office 1260  
5 office 1261 SnapshotNoteUpdate?.Invoke(this, new SnapshotNoteUpdateFailureEventArgs());
1 office 1262  
5 office 1263 throw;
1264 }
1 office 1265 }
1266 }
1267 }
1268 }
5 office 1269 finally
1270 {
1271 _databaseLock.Release();
1272 }
1 office 1273 }
1274  
11 office 1275 public async Task<string> UpdateFileAsync(string hash, byte[] data, CancellationToken cancellationToken)
1 office 1276 {
5 office 1277 await _databaseLock.WaitAsync(cancellationToken);
1278 try
1279 {
1280 using (var dataMemoryStream = new MemoryStream(data))
1 office 1281 {
10 office 1282 using (var sqliteConnection = new SQLiteConnection(_sqliteConnectionStringBuilder.ConnectionString))
5 office 1283 {
1284 await sqliteConnection.OpenAsync(cancellationToken);
1 office 1285  
5 office 1286 using (var dbTransaction = sqliteConnection.BeginTransaction())
1 office 1287 {
5 office 1288 try
1 office 1289 {
5 office 1290 using (var md5 = MD5.Create())
1 office 1291 {
5 office 1292 using (var hashMemoryStream = new MemoryStream())
1293 {
1294 dataMemoryStream.Position = 0L;
1295 await dataMemoryStream.CopyToAsync(hashMemoryStream);
1 office 1296  
5 office 1297 hashMemoryStream.Position = 0L;
1298 var recomputedHash = md5.ComputeHash(hashMemoryStream);
1299 var hashHex = BitConverter.ToString(recomputedHash).Replace("-", "")
1300 .ToLowerInvariant();
1 office 1301  
5 office 1302 using (var fileMemoryStream = new MemoryStream())
1 office 1303 {
5 office 1304 using (var fileZipStream =
1305 new GZipStream(fileMemoryStream, CompressionMode.Compress, true))
1306 {
1307 dataMemoryStream.Position = 0L;
1308 await dataMemoryStream.CopyToAsync(fileZipStream);
1309 fileZipStream.Close();
1 office 1310  
5 office 1311 fileMemoryStream.Position = 0L;
1 office 1312  
5 office 1313 // Insert the file change.
1314 using (var sqliteCommand =
1315 new SQLiteCommand(UpdateFileSql, sqliteConnection,
1316 dbTransaction))
1 office 1317 {
5 office 1318 sqliteCommand.Parameters.AddRange(new[]
1319 {
1320 new SQLiteParameter("@dataLength", fileMemoryStream.Length),
1321 new SQLiteParameter("@recomputedHash", hashHex),
1322 new SQLiteParameter("@hash", hash)
1323 });
1 office 1324  
5 office 1325 sqliteCommand.Prepare();
1326 await sqliteCommand.ExecuteNonQueryAsync(cancellationToken);
1327 }
1 office 1328  
5 office 1329 using (var sqliteCommand =
1330 new SQLiteCommand(GetRowFromHashSql, sqliteConnection,
1331 dbTransaction))
1 office 1332 {
5 office 1333 sqliteCommand.Parameters.AddRange(new[]
1334 {
1335 new SQLiteParameter("@hash", hashHex)
1336 });
1 office 1337  
5 office 1338 sqliteCommand.Prepare();
1 office 1339  
5 office 1340 using (var sqlDataReader =
1341 await sqliteCommand.ExecuteReaderAsync(cancellationToken))
1 office 1342 {
5 office 1343 while (await sqlDataReader.ReadAsync(cancellationToken))
1 office 1344 {
5 office 1345 if (sqlDataReader["id"] is long rowId)
1 office 1346 {
5 office 1347 using (var sqliteBlob = SQLiteBlob.Create(
1348 sqliteConnection,
1349 "main",
1350 "Snapshots",
1351 "Data",
1352 rowId, false))
1353 {
1354 var fileMemoryStreamData =
1355 fileMemoryStream.ToArray();
1 office 1356  
5 office 1357 sqliteBlob.Write(fileMemoryStreamData,
1358 fileMemoryStreamData.Length,
1359 0);
1360 }
1 office 1361 }
1362 }
1363 }
1364 }
1365  
5 office 1366 dbTransaction.Commit();
1 office 1367  
5 office 1368 SnapshotDataUpdate?.Invoke(this,
1369 new SnapshotDataUpdateSuccessEventArgs(hash, hashHex));
1 office 1370  
5 office 1371 return hashHex;
1372 }
1 office 1373 }
1374 }
1375 }
1376 }
5 office 1377 catch
1378 {
1379 dbTransaction.Rollback();
1 office 1380  
5 office 1381 SnapshotDataUpdate?.Invoke(this, new SnapshotDataUpdateFailureEventArgs(hash));
1 office 1382  
5 office 1383 throw;
1384 }
1 office 1385 }
1386 }
1387 }
1388 }
5 office 1389 finally
1390 {
1391 _databaseLock.Release();
1392 }
1 office 1393 }
1394  
11 office 1395 public async Task UpdateHashAsync(string from, string to, CancellationToken cancellationToken)
1 office 1396 {
5 office 1397 await _databaseLock.WaitAsync(cancellationToken);
1398 try
1 office 1399 {
10 office 1400 using (var sqliteConnection = new SQLiteConnection(_sqliteConnectionStringBuilder.ConnectionString))
5 office 1401 {
1402 await sqliteConnection.OpenAsync(cancellationToken);
1 office 1403  
5 office 1404 using (var dbTransaction = sqliteConnection.BeginTransaction())
1 office 1405 {
5 office 1406 // Insert the file change.
1407 using (var sqliteCommand =
1408 new SQLiteCommand(UpdateHashFromHashSql, sqliteConnection, dbTransaction))
1 office 1409 {
1410 sqliteCommand.Parameters.AddRange(new[]
1411 {
1412 new SQLiteParameter("@from", from),
1413 new SQLiteParameter("@to", to)
1414 });
1415  
1416 sqliteCommand.Prepare();
1417  
5 office 1418 try
1419 {
1420 await sqliteCommand.ExecuteNonQueryAsync(cancellationToken);
1 office 1421  
5 office 1422 dbTransaction.Commit();
1423 }
1424 catch
1425 {
1426 dbTransaction.Rollback();
1 office 1427  
5 office 1428 throw;
1429 }
1 office 1430 }
1431 }
1432 }
1433 }
5 office 1434 finally
1435 {
1436 _databaseLock.Release();
1437 }
1 office 1438 }
1439  
1440 #endregion
1441  
1442 #region Private Methods
1443  
5 office 1444 private async Task SetAutoVacuum(CancellationToken cancellationToken)
1 office 1445 {
5 office 1446 await _databaseLock.WaitAsync(cancellationToken);
1447 try
1 office 1448 {
5 office 1449 using (var sqliteConnection =
10 office 1450 new SQLiteConnection(_sqliteConnectionStringBuilder.ConnectionString))
5 office 1451 {
1452 await sqliteConnection.OpenAsync(cancellationToken);
1 office 1453  
5 office 1454 // Set auto vacuum.
1455 using (var sqliteCommand = new SQLiteCommand(SetAutoVacuumSql, sqliteConnection))
1456 {
1457 await sqliteCommand.ExecuteNonQueryAsync(cancellationToken);
1458 }
1 office 1459 }
1460 }
5 office 1461 finally
1462 {
1463 _databaseLock.Release();
1464 }
1 office 1465 }
1466  
5 office 1467 private async Task CreateDatabase(CancellationToken cancellationToken)
1 office 1468 {
5 office 1469 await _databaseLock.WaitAsync(cancellationToken);
1470 try
1 office 1471 {
10 office 1472 using (var sqliteConnection = new SQLiteConnection(_sqliteConnectionStringBuilder.ConnectionString))
5 office 1473 {
1474 await sqliteConnection.OpenAsync(cancellationToken);
1 office 1475  
5 office 1476 using (var dbTransaction = sqliteConnection.BeginTransaction())
1 office 1477 {
5 office 1478 // Create the table if it does not exist.
1479 using (var sqliteCommand = new SQLiteCommand(CreateTableSql, sqliteConnection, dbTransaction))
1 office 1480 {
5 office 1481 try
1482 {
1483 await sqliteCommand.ExecuteNonQueryAsync(cancellationToken);
1 office 1484  
5 office 1485 dbTransaction.Commit();
1486 }
1487 catch
1488 {
1489 dbTransaction.Rollback();
1 office 1490  
5 office 1491 throw;
1492 }
1 office 1493 }
1494 }
1495 }
1496 }
5 office 1497 finally
1498 {
1499 _databaseLock.Release();
1500 }
1 office 1501 }
1502  
1503 #endregion
1504 }
1505 }