Horizon – Blame information for rev 20

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