Horizon – Blame information for rev 23

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