Horizon – Blame information for rev 12

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