Horizon – Blame information for rev 26

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