Horizon – Blame information for rev 25

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 }
25 office 1123 catch(Exception e1)
1 office 1124 {
25 office 1125 var a = e1;
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);
1156 }
1 office 1157 }
1158 }
1159  
1160  
5 office 1161 break;
1162 }
1163  
1164 SnapshotRevert?.Invoke(this, new SnapshotRevertSuccessEventArgs(name));
1 office 1165 }
1166 }
1167 }
5 office 1168 catch
1169 {
1170 SnapshotRevert?.Invoke(this, new SnapshotRevertFailureEventArgs(name));
1 office 1171  
5 office 1172 throw;
1173 }
1 office 1174 }
1175 }
1176 }
5 office 1177 finally
1178 {
1179 _databaseLock.Release();
1180 }
1 office 1181 }
1182  
11 office 1183 public async Task RemoveFileFastAsync(IEnumerable<string> hashes, CancellationToken cancellationToken)
1 office 1184 {
5 office 1185 await _databaseLock.WaitAsync(cancellationToken);
1186 try
1 office 1187 {
10 office 1188 using (var sqliteConnection = new SQLiteConnection(_sqliteConnectionStringBuilder.ConnectionString))
5 office 1189 {
1190 await sqliteConnection.OpenAsync(cancellationToken);
1 office 1191  
5 office 1192 using (var dbTransaction = sqliteConnection.BeginTransaction())
1 office 1193 {
5 office 1194 try
1195 {
1196 var transactionCommands = new List<Task>();
1 office 1197  
5 office 1198 foreach (var hash in hashes)
1 office 1199 {
5 office 1200 // Insert the file change.
1201 using (var sqliteCommand =
1202 new SQLiteCommand(RemoveSnapshotFromHashSql, sqliteConnection, dbTransaction))
1 office 1203 {
5 office 1204 sqliteCommand.Parameters.AddRange(new[]
1205 {
1206 new SQLiteParameter("@hash", hash)
1207 });
1 office 1208  
5 office 1209 sqliteCommand.Prepare();
1 office 1210  
5 office 1211 var command = sqliteCommand.ExecuteNonQueryAsync(cancellationToken);
1 office 1212  
5 office 1213 transactionCommands.Add(command);
1214 }
1 office 1215 }
1216  
5 office 1217 await Task.WhenAll(transactionCommands);
1 office 1218  
5 office 1219 dbTransaction.Commit();
1220 }
1221 catch
1222 {
1223 dbTransaction.Rollback();
1 office 1224  
5 office 1225 throw;
1226 }
1 office 1227 }
1228 }
1229 }
5 office 1230 finally
1231 {
1232 _databaseLock.Release();
1233 }
1 office 1234 }
1235  
11 office 1236 public async Task RemoveFileAsync(string hash, CancellationToken cancellationToken)
1 office 1237 {
5 office 1238 await _databaseLock.WaitAsync(cancellationToken);
1239 try
1 office 1240 {
10 office 1241 using (var sqliteConnection = new SQLiteConnection(_sqliteConnectionStringBuilder.ConnectionString))
5 office 1242 {
1243 await sqliteConnection.OpenAsync(cancellationToken);
1 office 1244  
5 office 1245 using (var dbTransaction = sqliteConnection.BeginTransaction())
1 office 1246 {
5 office 1247 // Insert the file change.
1248 using (var sqliteCommand =
1249 new SQLiteCommand(RemoveSnapshotFromHashSql, sqliteConnection, dbTransaction))
1 office 1250 {
1251 sqliteCommand.Parameters.AddRange(new[]
1252 {
1253 new SQLiteParameter("@hash", hash)
1254 });
1255  
1256 sqliteCommand.Prepare();
1257  
5 office 1258 try
1259 {
1260 await sqliteCommand.ExecuteNonQueryAsync(cancellationToken);
1 office 1261  
5 office 1262 dbTransaction.Commit();
1263 }
1264 catch
1265 {
1266 dbTransaction.Rollback();
1 office 1267  
5 office 1268 throw;
1269 }
1 office 1270 }
1271 }
1272 }
1273 }
5 office 1274 finally
1275 {
1276 _databaseLock.Release();
1277 }
1 office 1278 }
1279  
11 office 1280 public async Task UpdateColorAsync(string hash, Color color, CancellationToken cancellationToken)
1 office 1281 {
5 office 1282 await _databaseLock.WaitAsync(cancellationToken);
1283  
1284 try
1 office 1285 {
10 office 1286 using (var sqliteConnection = new SQLiteConnection(_sqliteConnectionStringBuilder.ConnectionString))
5 office 1287 {
1288 await sqliteConnection.OpenAsync(cancellationToken);
1 office 1289  
5 office 1290 using (var dbTransaction = sqliteConnection.BeginTransaction())
1 office 1291 {
5 office 1292 // Insert the file change.
1293 using (var sqliteCommand =
1294 new SQLiteCommand(UpdateColorFromHashSql, sqliteConnection, dbTransaction))
1 office 1295 {
1296 sqliteCommand.Parameters.AddRange(new[]
1297 {
20 office 1298 new SQLiteParameter("@hash", hash)
1 office 1299 });
1300  
20 office 1301 var numeric = color.ToArgb();
1302 switch (numeric)
1303 {
1304 case 0:
1305 sqliteCommand.Parameters.Add(
1306 new SQLiteParameter("@color", DBNull.Value));
1307 break;
1308 default:
1309 sqliteCommand.Parameters.Add(
1310 new SQLiteParameter("@color", numeric));
1311 break;
1312 }
1313  
1 office 1314 sqliteCommand.Prepare();
1315  
5 office 1316 try
1317 {
1318 await sqliteCommand.ExecuteNonQueryAsync(cancellationToken);
1 office 1319  
5 office 1320 dbTransaction.Commit();
1321 }
1322 catch
1323 {
1324 dbTransaction.Rollback();
1 office 1325  
5 office 1326 throw;
1327 }
1 office 1328 }
1329 }
1330 }
1331 }
5 office 1332 finally
1333 {
1334 _databaseLock.Release();
1335 }
1 office 1336 }
1337  
11 office 1338 public async Task RemoveColorAsync(string hash, CancellationToken cancellationToken)
1 office 1339 {
5 office 1340 await _databaseLock.WaitAsync(cancellationToken);
1341 try
1 office 1342 {
10 office 1343 using (var sqliteConnection = new SQLiteConnection(_sqliteConnectionStringBuilder.ConnectionString))
5 office 1344 {
1345 await sqliteConnection.OpenAsync(cancellationToken);
1 office 1346  
5 office 1347 using (var dbTransaction = sqliteConnection.BeginTransaction())
1 office 1348 {
5 office 1349 // Insert the file change.
1350 using (var sqliteCommand =
1351 new SQLiteCommand(RemoveColorFromHashSql, sqliteConnection, dbTransaction))
1 office 1352 {
1353 sqliteCommand.Parameters.AddRange(new[]
1354 {
1355 new SQLiteParameter("@hash", hash)
1356 });
1357  
1358 sqliteCommand.Prepare();
1359  
5 office 1360 try
1361 {
1362 await sqliteCommand.ExecuteNonQueryAsync(cancellationToken);
1 office 1363  
5 office 1364 dbTransaction.Commit();
1365 }
1366 catch
1367 {
1368 dbTransaction.Rollback();
1 office 1369  
5 office 1370 throw;
1371 }
1 office 1372 }
1373 }
1374 }
1375 }
5 office 1376 finally
1377 {
1378 _databaseLock.Release();
1379 }
1 office 1380 }
1381  
11 office 1382 public async Task<SnapshotPreview> RetrievePreviewAsync(string hash, CancellationToken cancellationToken)
1 office 1383 {
5 office 1384 await _databaseLock.WaitAsync(cancellationToken);
1385 try
1 office 1386 {
10 office 1387 using (var sqliteConnection = new SQLiteConnection(_sqliteConnectionStringBuilder.ConnectionString))
5 office 1388 {
1389 await sqliteConnection.OpenAsync(cancellationToken);
1 office 1390  
5 office 1391 // Insert the file change.
1392 using (var sqliteCommand = new SQLiteCommand(RetrievePreviewFromHashSql, sqliteConnection))
1 office 1393 {
5 office 1394 sqliteCommand.Parameters.AddRange(new[]
1395 {
1396 new SQLiteParameter("@hash", hash)
1397 });
1 office 1398  
5 office 1399 var note = string.Empty;
1 office 1400  
5 office 1401 sqliteCommand.Prepare();
1 office 1402  
5 office 1403 using (var sqlDataReader = await sqliteCommand.ExecuteReaderAsync(cancellationToken))
1 office 1404 {
5 office 1405 while (await sqlDataReader.ReadAsync(cancellationToken))
1 office 1406 {
5 office 1407 if (!(sqlDataReader["Note"] is DBNull))
1 office 1408 {
5 office 1409 note = (string)sqlDataReader["Note"];
1 office 1410 }
1411  
5 office 1412 Bitmap shot = null;
1 office 1413  
5 office 1414 if (!(sqlDataReader["Shot"] is DBNull))
1 office 1415 {
5 office 1416 var readStream = sqlDataReader.GetStream(2);
1 office 1417  
1418 readStream.Position = 0L;
1419  
12 office 1420 try
1 office 1421 {
12 office 1422 using (var zipStream = new GZipStream(readStream, CompressionMode.Decompress))
5 office 1423 {
12 office 1424 using (var image = Image.FromStream(zipStream))
1425 {
1426 shot = new Bitmap(image);
1427 }
5 office 1428 }
1 office 1429 }
12 office 1430 catch (Exception exception)
1431 {
1432 Log.Error(exception, $"Could not retrieve image preview for snapshot {hash}.");
1433 return null;
1434 }
1 office 1435 }
5 office 1436  
1437 return new SnapshotPreview(hash, shot, note);
1 office 1438 }
5 office 1439  
1440 return null;
1 office 1441 }
1442 }
1443 }
1444 }
5 office 1445 finally
1446 {
1447 _databaseLock.Release();
1448 }
1 office 1449 }
1450  
11 office 1451 public async Task<MemoryStream> RetrieveFileStreamAsync(string hash, CancellationToken cancellationToken)
1 office 1452 {
5 office 1453 await _databaseLock.WaitAsync(cancellationToken);
1454 try
1 office 1455 {
10 office 1456 using (var sqliteConnection = new SQLiteConnection(_sqliteConnectionStringBuilder.ConnectionString))
5 office 1457 {
1458 await sqliteConnection.OpenAsync(cancellationToken);
1 office 1459  
5 office 1460 // Insert the file change.
1461 using (var sqliteCommand = new SQLiteCommand(RetrieveDataFromHashSql, sqliteConnection))
1 office 1462 {
5 office 1463 sqliteCommand.Parameters.AddRange(new[]
1464 {
1465 new SQLiteParameter("@hash", hash)
1466 });
1 office 1467  
5 office 1468 sqliteCommand.Prepare();
1 office 1469  
5 office 1470 using (var sqlDataReader = await sqliteCommand.ExecuteReaderAsync(cancellationToken))
1 office 1471 {
5 office 1472 while (await sqlDataReader.ReadAsync(cancellationToken))
1 office 1473 {
5 office 1474 using (var readStream = sqlDataReader.GetStream(1))
1 office 1475 {
5 office 1476 using (var memoryStream = new MemoryStream())
1477 {
1478 readStream.Position = 0L;
1 office 1479  
5 office 1480 await readStream.CopyToAsync(memoryStream);
1 office 1481  
5 office 1482 memoryStream.Position = 0L;
1 office 1483  
5 office 1484 using (var zipStream = new GZipStream(memoryStream, CompressionMode.Decompress))
1485 {
1486 // Do not dispose the returned stream and leave it up to callers to dispose.
1487 var outputStream = new MemoryStream();
1 office 1488  
5 office 1489 await zipStream.CopyToAsync(outputStream);
1 office 1490  
5 office 1491 outputStream.Position = 0L;
1 office 1492  
5 office 1493 return outputStream;
1494 }
1 office 1495 }
1496 }
1497 }
5 office 1498  
1499 return null;
1 office 1500 }
1501 }
1502 }
1503 }
5 office 1504 finally
1505 {
1506 _databaseLock.Release();
1507 }
1 office 1508 }
1509  
11 office 1510 public async Task RelocateFileAsync(string hash, string path, CancellationToken cancellationToken)
1 office 1511 {
5 office 1512 await _databaseLock.WaitAsync(cancellationToken);
1513 try
1 office 1514 {
10 office 1515 using (var sqliteConnection = new SQLiteConnection(_sqliteConnectionStringBuilder.ConnectionString))
5 office 1516 {
1517 await sqliteConnection.OpenAsync(cancellationToken);
1 office 1518  
5 office 1519 using (var dbTransaction = sqliteConnection.BeginTransaction())
1 office 1520 {
5 office 1521 // Insert the file change.
1522 using (var sqliteCommand =
1523 new SQLiteCommand(RelocateFileFromHashSql, sqliteConnection, dbTransaction))
1 office 1524 {
1525 sqliteCommand.Parameters.AddRange(new[]
1526 {
1527 new SQLiteParameter("@hash", hash),
1528 new SQLiteParameter("@path", path)
1529 });
1530  
1531 sqliteCommand.Prepare();
1532  
5 office 1533 try
1534 {
1535 await sqliteCommand.ExecuteNonQueryAsync(cancellationToken);
1 office 1536  
5 office 1537 dbTransaction.Commit();
1538 }
1539 catch
1540 {
1541 dbTransaction.Rollback();
1 office 1542  
5 office 1543 throw;
1544 }
1 office 1545 }
1546 }
1547 }
1548 }
5 office 1549  
1550 finally
1551 {
1552 _databaseLock.Release();
1553 }
1 office 1554 }
1555  
11 office 1556 public async Task UpdateNoteAsync(string hash, string note, CancellationToken cancellationToken)
1 office 1557 {
5 office 1558 await _databaseLock.WaitAsync(cancellationToken);
1559 try
1 office 1560 {
10 office 1561 using (var sqliteConnection = new SQLiteConnection(_sqliteConnectionStringBuilder.ConnectionString))
5 office 1562 {
1563 await sqliteConnection.OpenAsync(cancellationToken);
1 office 1564  
5 office 1565 using (var dbTransaction = sqliteConnection.BeginTransaction())
1 office 1566 {
5 office 1567 // Insert the file change.
1568 using (var sqliteCommand =
1569 new SQLiteCommand(UpdateNoteFromHashSql, sqliteConnection, dbTransaction))
1 office 1570 {
1571 sqliteCommand.Parameters.AddRange(new[]
1572 {
1573 new SQLiteParameter("@hash", hash),
1574 new SQLiteParameter("@note", note)
1575 });
1576  
1577 sqliteCommand.Prepare();
1578  
5 office 1579 try
1580 {
1581 await sqliteCommand.ExecuteNonQueryAsync(cancellationToken);
1 office 1582  
5 office 1583 dbTransaction.Commit();
1 office 1584  
5 office 1585 SnapshotNoteUpdate?.Invoke(this, new SnapshotNoteUpdateSuccessEventArgs(note));
1586 }
1587 catch
1588 {
1589 dbTransaction.Rollback();
1 office 1590  
5 office 1591 SnapshotNoteUpdate?.Invoke(this, new SnapshotNoteUpdateFailureEventArgs());
1 office 1592  
5 office 1593 throw;
1594 }
1 office 1595 }
1596 }
1597 }
1598 }
5 office 1599 finally
1600 {
1601 _databaseLock.Release();
1602 }
1 office 1603 }
1604  
11 office 1605 public async Task<string> UpdateFileAsync(string hash, byte[] data, CancellationToken cancellationToken)
1 office 1606 {
5 office 1607 await _databaseLock.WaitAsync(cancellationToken);
1608 try
1609 {
1610 using (var dataMemoryStream = new MemoryStream(data))
1 office 1611 {
10 office 1612 using (var sqliteConnection = new SQLiteConnection(_sqliteConnectionStringBuilder.ConnectionString))
5 office 1613 {
1614 await sqliteConnection.OpenAsync(cancellationToken);
1 office 1615  
5 office 1616 using (var dbTransaction = sqliteConnection.BeginTransaction())
1 office 1617 {
5 office 1618 try
1 office 1619 {
5 office 1620 using (var md5 = MD5.Create())
1 office 1621 {
5 office 1622 using (var hashMemoryStream = new MemoryStream())
1623 {
1624 dataMemoryStream.Position = 0L;
1625 await dataMemoryStream.CopyToAsync(hashMemoryStream);
1 office 1626  
5 office 1627 hashMemoryStream.Position = 0L;
1628 var recomputedHash = md5.ComputeHash(hashMemoryStream);
1629 var hashHex = BitConverter.ToString(recomputedHash).Replace("-", "")
1630 .ToLowerInvariant();
1 office 1631  
5 office 1632 using (var fileMemoryStream = new MemoryStream())
1 office 1633 {
5 office 1634 using (var fileZipStream =
1635 new GZipStream(fileMemoryStream, CompressionMode.Compress, true))
1636 {
1637 dataMemoryStream.Position = 0L;
1638 await dataMemoryStream.CopyToAsync(fileZipStream);
1639 fileZipStream.Close();
1 office 1640  
5 office 1641 fileMemoryStream.Position = 0L;
1 office 1642  
5 office 1643 // Insert the file change.
1644 using (var sqliteCommand =
1645 new SQLiteCommand(UpdateFileSql, sqliteConnection,
1646 dbTransaction))
1 office 1647 {
5 office 1648 sqliteCommand.Parameters.AddRange(new[]
1649 {
1650 new SQLiteParameter("@dataLength", fileMemoryStream.Length),
1651 new SQLiteParameter("@recomputedHash", hashHex),
1652 new SQLiteParameter("@hash", hash)
1653 });
1 office 1654  
5 office 1655 sqliteCommand.Prepare();
1656 await sqliteCommand.ExecuteNonQueryAsync(cancellationToken);
1657 }
1 office 1658  
5 office 1659 using (var sqliteCommand =
1660 new SQLiteCommand(GetRowFromHashSql, sqliteConnection,
1661 dbTransaction))
1 office 1662 {
5 office 1663 sqliteCommand.Parameters.AddRange(new[]
1664 {
1665 new SQLiteParameter("@hash", hashHex)
1666 });
1 office 1667  
5 office 1668 sqliteCommand.Prepare();
1 office 1669  
5 office 1670 using (var sqlDataReader =
1671 await sqliteCommand.ExecuteReaderAsync(cancellationToken))
1 office 1672 {
5 office 1673 while (await sqlDataReader.ReadAsync(cancellationToken))
1 office 1674 {
5 office 1675 if (sqlDataReader["id"] is long rowId)
1 office 1676 {
5 office 1677 using (var sqliteBlob = SQLiteBlob.Create(
1678 sqliteConnection,
1679 "main",
1680 "Snapshots",
1681 "Data",
1682 rowId, false))
1683 {
1684 var fileMemoryStreamData =
1685 fileMemoryStream.ToArray();
1 office 1686  
5 office 1687 sqliteBlob.Write(fileMemoryStreamData,
1688 fileMemoryStreamData.Length,
1689 0);
1690 }
1 office 1691 }
1692 }
1693 }
1694 }
1695  
5 office 1696 dbTransaction.Commit();
1 office 1697  
5 office 1698 SnapshotDataUpdate?.Invoke(this,
1699 new SnapshotDataUpdateSuccessEventArgs(hash, hashHex));
1 office 1700  
5 office 1701 return hashHex;
1702 }
1 office 1703 }
1704 }
1705 }
1706 }
5 office 1707 catch
1708 {
1709 dbTransaction.Rollback();
1 office 1710  
5 office 1711 SnapshotDataUpdate?.Invoke(this, new SnapshotDataUpdateFailureEventArgs(hash));
1 office 1712  
5 office 1713 throw;
1714 }
1 office 1715 }
1716 }
1717 }
1718 }
5 office 1719 finally
1720 {
1721 _databaseLock.Release();
1722 }
1 office 1723 }
1724  
11 office 1725 public async Task UpdateHashAsync(string from, string to, CancellationToken cancellationToken)
1 office 1726 {
5 office 1727 await _databaseLock.WaitAsync(cancellationToken);
1728 try
1 office 1729 {
10 office 1730 using (var sqliteConnection = new SQLiteConnection(_sqliteConnectionStringBuilder.ConnectionString))
5 office 1731 {
1732 await sqliteConnection.OpenAsync(cancellationToken);
1 office 1733  
5 office 1734 using (var dbTransaction = sqliteConnection.BeginTransaction())
1 office 1735 {
5 office 1736 // Insert the file change.
1737 using (var sqliteCommand =
1738 new SQLiteCommand(UpdateHashFromHashSql, sqliteConnection, dbTransaction))
1 office 1739 {
1740 sqliteCommand.Parameters.AddRange(new[]
1741 {
1742 new SQLiteParameter("@from", from),
1743 new SQLiteParameter("@to", to)
1744 });
1745  
1746 sqliteCommand.Prepare();
1747  
5 office 1748 try
1749 {
1750 await sqliteCommand.ExecuteNonQueryAsync(cancellationToken);
1 office 1751  
5 office 1752 dbTransaction.Commit();
1753 }
1754 catch
1755 {
1756 dbTransaction.Rollback();
1 office 1757  
5 office 1758 throw;
1759 }
1 office 1760 }
1761 }
1762 }
1763 }
5 office 1764 finally
1765 {
1766 _databaseLock.Release();
1767 }
1 office 1768 }
1769  
1770 #endregion
1771  
1772 #region Private Methods
1773  
5 office 1774 private async Task SetAutoVacuum(CancellationToken cancellationToken)
1 office 1775 {
5 office 1776 await _databaseLock.WaitAsync(cancellationToken);
1777 try
1 office 1778 {
5 office 1779 using (var sqliteConnection =
10 office 1780 new SQLiteConnection(_sqliteConnectionStringBuilder.ConnectionString))
5 office 1781 {
1782 await sqliteConnection.OpenAsync(cancellationToken);
1 office 1783  
5 office 1784 // Set auto vacuum.
1785 using (var sqliteCommand = new SQLiteCommand(SetAutoVacuumSql, sqliteConnection))
1786 {
1787 await sqliteCommand.ExecuteNonQueryAsync(cancellationToken);
1788 }
1 office 1789 }
1790 }
5 office 1791 finally
1792 {
1793 _databaseLock.Release();
1794 }
1 office 1795 }
1796  
5 office 1797 private async Task CreateDatabase(CancellationToken cancellationToken)
1 office 1798 {
5 office 1799 await _databaseLock.WaitAsync(cancellationToken);
1800 try
1 office 1801 {
10 office 1802 using (var sqliteConnection = new SQLiteConnection(_sqliteConnectionStringBuilder.ConnectionString))
5 office 1803 {
1804 await sqliteConnection.OpenAsync(cancellationToken);
1 office 1805  
5 office 1806 using (var dbTransaction = sqliteConnection.BeginTransaction())
1 office 1807 {
5 office 1808 // Create the table if it does not exist.
1809 using (var sqliteCommand = new SQLiteCommand(CreateTableSql, sqliteConnection, dbTransaction))
1 office 1810 {
5 office 1811 try
1812 {
1813 await sqliteCommand.ExecuteNonQueryAsync(cancellationToken);
1 office 1814  
5 office 1815 dbTransaction.Commit();
1816 }
1817 catch
1818 {
1819 dbTransaction.Rollback();
1 office 1820  
5 office 1821 throw;
1822 }
1 office 1823 }
1824 }
1825 }
1826 }
5 office 1827 finally
1828 {
1829 _databaseLock.Release();
1830 }
1 office 1831 }
1832  
1833 #endregion
12 office 1834  
1835  
1 office 1836 }
1837 }