QuickImage – Blame information for rev 8

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 1 using System;
2 using System.Collections.Generic;
3 using System.Data;
4 using System.Data.Common;
5 using System.Data.SQLite;
6 using System.Drawing;
7 using System.Drawing.Imaging;
8 using System.Globalization;
9 using System.IO;
10 using System.IO.Compression;
11 using System.Linq;
12 using System.Runtime.CompilerServices;
13 using System.Security.Policy;
14 using System.Text.RegularExpressions;
15 using System.Threading;
16 using System.Threading.Tasks;
17 using System.Threading.Tasks.Dataflow;
18 using QuickImage.Utilities.Serialization.Comma_Separated_Values;
19 using Serilog;
20 using Shipwreck.Phash;
21  
22 namespace QuickImage.Database
23 {
24 public class QuickImageDatabase
25 {
26 private const string CreateImageTableSql =
27 "CREATE TABLE IF NOT EXISTS \"Images\" (\"id\" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, \"File\" TEXT NOT NULL, \"Tags\" TEXT NOT NULL, \"Hash\" TEXT NOT NULL, \"Thumbnail\" BLOB, UNIQUE (\"File\") ON CONFLICT REPLACE)";
28  
29 private const string RetrieveImageTagsSql =
30 "SELECT \"Tags\" FROM \"Images\" WHERE File = @file";
31 private const string RetrieveImageHashSql = "SELECT \"Hash\" FROM \"Images\" WHERE File = @file";
32  
33 private const string UpdateImageTagsSql =
34 "UPDATE \"Images\" SET Tags = @tags WHERE File = @file";
35  
36 private const string UpdateImageHashSql = "UPDATE \"Images\" SET Hash = @hash WHERE File = @file";
37  
38 private const string UpdateFileHashSql = "UPDATE \"Images\" SET File = @file WHERE Hash = @hash";
39  
40 private const string UpdateFileSql = "UPDATE \"Images\" SET File = @to WHERE File = @from";
41  
42 private const string UpdateImageThumbnailSql = "UPDATE \"Images\" SET Thumbnail = zeroblob(@thumbnailLength) WHERE File = @file";
43  
44 private const string CheckForFileSql = "SELECT COUNT(*) FROM \"Images\" WHERE File = @file";
45  
46 private const string CountImagesSql = "SELECT COUNT(*) FROM \"Images\"";
47  
48 private const string InsertImageSql = "INSERT INTO \"Images\" ( \"File\", \"Tags\", \"Hash\", \"Thumbnail\" ) VALUES ( @file, @tags, @hash, zeroblob(@thumbnailLength) )";
49  
50 private const string RemoveImageSql = "DELETE FROM \"Images\" WHERE File = @file";
51  
52 private const string RetrieveImagesSql = "SELECT * FROM \"Images\"";
53  
54 private const string RetrieveImageSql = "SELECT * FROM \"Images\" WHERE File = @file";
55  
56 private const string StripImageTagsSql = "UPDATE \"Images\" SET Tags = \"\" WHERE File = @file";
57  
58 private const string SetAutoVacuumSql = "PRAGMA auto_vacuum = FULL";
59  
60 private const string GetLastRowInsertSql = "SELECT last_insert_rowid()";
61  
62 private static readonly string DatabaseConnectionString = $"Data Source={Constants.DatabaseFilePath};";
63  
64 private readonly CancellationToken _cancellationToken;
65 private readonly SemaphoreSlim _databaseLock;
66  
67 private QuickImageDatabase()
68 {
69 Directory.CreateDirectory(Constants.DatabaseDirectory);
70 _databaseLock = new SemaphoreSlim(1, 1);
71 }
72  
73 public QuickImageDatabase(CancellationToken cancellationToken) : this()
74 {
75 _cancellationToken = cancellationToken;
76  
77 CreateDatabase(_cancellationToken).ContinueWith(async createDatabaseTask =>
78 {
79 try
80 {
81 await createDatabaseTask;
82  
83 try
84 {
85 await SetAutoVacuum(_cancellationToken);
86 }
87 catch (Exception exception)
88 {
89 Log.Error(exception, "Unable to set auto vacuum for database.");
90 }
91 }
92 catch (Exception exception)
93 {
94 Log.Error(exception, "Unable to create database;");
95 }
96 }).Wait(_cancellationToken);
97 }
98  
99 private async Task SetAutoVacuum(CancellationToken cancellationToken)
100 {
101 var connectionString = new SQLiteConnectionStringBuilder
102 {
103 ConnectionString = DatabaseConnectionString
104 };
105  
106 try
107 {
108 await _databaseLock.WaitAsync(cancellationToken);
109 }
110 catch
111 {
112 return;
113 }
114  
115 try
116 {
117 using var sqliteConnection =
118 new SQLiteConnection(connectionString.ConnectionString);
119 await sqliteConnection.OpenAsync(cancellationToken);
120  
121 // Set auto vacuum.
122 using var sqliteCommand = new SQLiteCommand(SetAutoVacuumSql, sqliteConnection);
123 await sqliteCommand.ExecuteNonQueryAsync(cancellationToken);
124 }
125 finally
126 {
127 _databaseLock.Release();
128 }
129  
130 }
131  
132 private async Task CreateDatabase(CancellationToken cancellationToken)
133 {
134 var connectionString = new SQLiteConnectionStringBuilder
135 {
136 ConnectionString = DatabaseConnectionString
137 };
138  
139 try
140 {
141 await _databaseLock.WaitAsync(cancellationToken);
142 }
143 catch
144 {
145 return;
146 }
147 try
148 {
149 using var sqliteConnection = new SQLiteConnection(connectionString.ConnectionString);
150 await sqliteConnection.OpenAsync(cancellationToken);
151  
152 using var dbTransaction = sqliteConnection.BeginTransaction();
153 // Create the table if it does not exist.
154 using var sqliteCommand = new SQLiteCommand(CreateImageTableSql, sqliteConnection, dbTransaction);
155 try
156 {
157 await sqliteCommand.ExecuteNonQueryAsync(cancellationToken);
158  
159 dbTransaction.Commit();
160 }
161 catch (Exception exception)
162 {
163 Log.Error(exception, "Could not create database table.");
164  
165 dbTransaction.Rollback();
166  
167 throw;
168 }
169 }
170 finally
171 {
172 _databaseLock.Release();
173 }
174  
175 }
176  
177 public bool Exists(string file, CancellationToken cancellationToken)
178 {
179 var connectionString = new SQLiteConnectionStringBuilder
180 {
181 ConnectionString = DatabaseConnectionString
182 };
183  
184 try
185 {
186 _databaseLock.Wait(cancellationToken);
187 }
188 catch
189 {
190 return false;
191 }
192  
193 try
194 {
195 using var sqliteConnection = new SQLiteConnection(connectionString.ConnectionString);
196 sqliteConnection.Open();
197  
198 using var sqLiteCommand = new SQLiteCommand(CheckForFileSql, sqliteConnection);
199 sqLiteCommand.Parameters.AddRange(new[]
200 {
201 new SQLiteParameter("@file", file)
202 });
203 sqLiteCommand.Prepare();
204  
205 using var sqlDataReader = sqLiteCommand.ExecuteReader();
206  
207 long count = 0;
208 while (sqlDataReader.Read())
209 {
210 if (!(sqlDataReader[0] is long dbCount))
211 {
212 count = -1;
213 break;
214 }
215  
216 count = dbCount;
217 }
218  
219 return count != 0;
220 }
221 finally
222 {
223 _databaseLock.Release();
224 }
225 }
226  
227 public async Task<bool> ExistsAsync(string file, CancellationToken cancellationToken)
228 {
229 var connectionString = new SQLiteConnectionStringBuilder
230 {
231 ConnectionString = DatabaseConnectionString
232 };
233  
234 try
235 {
236 await _databaseLock.WaitAsync(cancellationToken);
237 }
238 catch
239 {
240 return false;
241 }
242  
243 try
244 {
245 using var sqliteConnection = new SQLiteConnection(connectionString.ConnectionString);
246 await sqliteConnection.OpenAsync(cancellationToken);
247  
248 using var sqLiteCommand = new SQLiteCommand(CheckForFileSql, sqliteConnection);
249 sqLiteCommand.Parameters.AddRange(new[]
250 {
251 new SQLiteParameter("@file", file)
252 });
253 sqLiteCommand.Prepare();
254  
255 using var sqlDataReader = await sqLiteCommand.ExecuteReaderAsync(cancellationToken);
256  
257 long count = 0;
258 while (await sqlDataReader.ReadAsync(cancellationToken))
259 {
260 if (!(sqlDataReader[0] is long dbCount))
261 {
262 count = -1;
263 break;
264 }
265  
266 count = dbCount;
267 }
268  
269 return count != 0;
270 }
271 finally
272 {
273 _databaseLock.Release();
274 }
275 }
276  
277 public async Task<long> CountAsync(CancellationToken cancellationToken)
278 {
279 var connectionString = new SQLiteConnectionStringBuilder
280 {
281 ConnectionString = DatabaseConnectionString
282 };
283  
284 try
285 {
286 await _databaseLock.WaitAsync(cancellationToken);
287 }
288 catch
289 {
290 return -1;
291 }
292  
293 try
294 {
295 using var sqliteConnection = new SQLiteConnection(connectionString.ConnectionString);
296 await sqliteConnection.OpenAsync(cancellationToken);
297  
298 using var sqLiteCommand = new SQLiteCommand(CountImagesSql, sqliteConnection);
299 sqLiteCommand.Prepare();
300  
301 using var sqlDataReader = await sqLiteCommand.ExecuteReaderAsync(cancellationToken);
302  
303 long count = 0;
304 while (await sqlDataReader.ReadAsync(cancellationToken))
305 {
306 if (!(sqlDataReader[0] is long dbCount))
307 {
308 count = -1;
309 break;
310 }
311  
312 count = dbCount;
313 }
314  
315 return count;
316 }
317 finally
318 {
319 _databaseLock.Release();
320 }
321 }
322  
323 public async Task<bool> RemoveImageAsync(string file, CancellationToken cancellationToken)
324 {
325 var connectionString = new SQLiteConnectionStringBuilder
326 {
327 ConnectionString = DatabaseConnectionString
328 };
329  
330 try
331 {
332 await _databaseLock.WaitAsync(cancellationToken);
333 }
334 catch
335 {
336 return false;
337 }
338  
339 try
340 {
341 using var sqliteConnection = new SQLiteConnection(connectionString.ConnectionString);
342 await sqliteConnection.OpenAsync(cancellationToken);
343  
344 using var dbTransaction = sqliteConnection.BeginTransaction();
345 using var sqLiteCommand = new SQLiteCommand(RemoveImageSql, sqliteConnection, dbTransaction);
346 sqLiteCommand.Parameters.AddRange(new[]
347 {
348 new SQLiteParameter("@file", file)
349 });
350 sqLiteCommand.Prepare();
351  
352 try
353 {
354 await sqLiteCommand.ExecuteNonQueryAsync(cancellationToken);
355  
356 dbTransaction.Commit();
357  
358 return true;
359 }
360 catch (Exception exception)
361 {
362 Log.Error(exception, "Could not remove.");
363  
364 dbTransaction.Rollback();
365 }
366  
367 return false;
368 }
369 finally
370 {
371 _databaseLock.Release();
372 }
373 }
374  
375 public async Task<bool> AddImageAsync(string file, Digest hash, IEnumerable<string> keywords, Bitmap thumbnail,
376 CancellationToken cancellationToken)
377 {
378 var connectionString = new SQLiteConnectionStringBuilder
379 {
380 ConnectionString = DatabaseConnectionString
381 };
382  
383 try
384 {
385 await _databaseLock.WaitAsync(cancellationToken);
386 }
387 catch
388 {
389 return false;
390 }
391  
392 try
393 {
394 using var bitmapMemoryStream = new MemoryStream();
395 using var bitmapZipStream =
396 new GZipStream(bitmapMemoryStream, CompressionMode.Compress,
397 true);
398 thumbnail.Save(bitmapZipStream, ImageFormat.Bmp);
399 bitmapZipStream.Close();
400 bitmapMemoryStream.Position = 9L;
401  
402 using var sqliteConnection = new SQLiteConnection(connectionString.ConnectionString);
403 await sqliteConnection.OpenAsync(cancellationToken);
404  
405 using var dbTransaction = sqliteConnection.BeginTransaction();
406  
407 try
408 {
409 using (var sqLiteCommand = new SQLiteCommand(InsertImageSql, sqliteConnection, dbTransaction))
410 {
411 var words = new HashSet<string>();
412 foreach (var word in keywords)
413 {
414 if (string.IsNullOrEmpty(word))
415 {
416 continue;
417 }
418  
419 words.Add(word);
420 }
421 sqLiteCommand.Parameters.AddRange(new[]
422 {
423 new SQLiteParameter("@file", file),
424 new SQLiteParameter("@tags", new Csv(words)),
425 new SQLiteParameter("@hash", Convert.ToBase64String(hash.Coefficients)),
426 new SQLiteParameter("@thumbnailLength", bitmapMemoryStream.Length)
427 });
428 sqLiteCommand.Prepare();
429 await sqLiteCommand.ExecuteNonQueryAsync(cancellationToken);
430 }
431  
432 using (var getLastRowInsertCommand =
433 new SQLiteCommand(GetLastRowInsertSql, sqliteConnection, dbTransaction))
434 {
435 getLastRowInsertCommand.Prepare();
436  
437 var rowId =
438 (long)await getLastRowInsertCommand.ExecuteScalarAsync(cancellationToken);
439  
440 using var sqliteBlob =
441 SQLiteBlob.Create(sqliteConnection, "main", "Images", "Thumbnail", rowId, false);
442  
443 var fileMemoryStreamData = bitmapMemoryStream.ToArray();
444  
445 sqliteBlob.Write(fileMemoryStreamData,
446 fileMemoryStreamData.Length,
447 0);
448 }
449  
450 dbTransaction.Commit();
451  
452 return true;
453 }
454 catch (Exception exception)
455 {
456 Log.Error(exception, "Could not insert.");
457  
458 dbTransaction.Rollback();
459 }
460  
461 return false;
462 }
463 finally
464 {
465 _databaseLock.Release();
466 }
467 }
468  
469 public async Task<bool> StripTagsAsync(string file, CancellationToken cancellationToken)
470 {
471 var connectionString = new SQLiteConnectionStringBuilder
472 {
473 ConnectionString = DatabaseConnectionString
474 };
475  
476 try
477 {
478 await _databaseLock.WaitAsync(cancellationToken);
479 }
480 catch
481 {
482 return false;
483 }
484  
485 try
486 {
487 using var sqliteConnection = new SQLiteConnection(connectionString.ConnectionString);
488 await sqliteConnection.OpenAsync(cancellationToken);
489  
490 using var dbTransaction = sqliteConnection.BeginTransaction();
491 try
492 {
493 using var sqLiteCommand = new SQLiteCommand(StripImageTagsSql, sqliteConnection, dbTransaction);
494 sqLiteCommand.Parameters.AddRange(new[]
495 {
496 new SQLiteParameter("@file", file)
497 });
498 sqLiteCommand.Prepare();
499  
500 await sqLiteCommand.ExecuteNonQueryAsync(cancellationToken);
501  
502 dbTransaction.Commit();
503  
504 return true;
505 }
506 catch (Exception exception)
507 {
508 Log.Error(exception, "Could not insert.");
509  
510 dbTransaction.Rollback();
511 }
512  
513 return false;
514 }
515 finally
516 {
517 _databaseLock.Release();
518 }
519 }
520  
521 public async Task<bool> RemoveTagsAsync(string file, IEnumerable<string> keywords,
522 CancellationToken cancellationToken)
523 {
524 var connectionString = new SQLiteConnectionStringBuilder
525 {
526 ConnectionString = DatabaseConnectionString
527 };
528  
529 try
530 {
531 await _databaseLock.WaitAsync(cancellationToken);
532 }
533 catch
534 {
535 return false;
536 }
537  
538 try
539 {
540 using var sqliteConnection = new SQLiteConnection(connectionString.ConnectionString);
541 await sqliteConnection.OpenAsync(cancellationToken);
542  
543 using var dbTransaction = sqliteConnection.BeginTransaction();
544 try
545 {
546 using var sqLiteCommand = new SQLiteCommand(RetrieveImageTagsSql, sqliteConnection, dbTransaction);
547 sqLiteCommand.Parameters.AddRange(new[]
548 {
549 new SQLiteParameter("@file", file)
550 });
551 sqLiteCommand.Prepare();
552  
553 using var sqlDataReader = await sqLiteCommand.ExecuteReaderAsync(cancellationToken);
554  
555 var tagWords = new HashSet<string>();
556 while (await sqlDataReader.ReadAsync(cancellationToken))
557 {
558 var tags = (string)sqlDataReader["Tags"];
559 if (string.IsNullOrEmpty(tags))
560 {
561 continue;
562 }
563  
564 foreach (var word in new Csv(tags))
565 {
566 if (string.IsNullOrEmpty(word))
567 {
568 continue;
569 }
570  
571 tagWords.Add(word);
572 }
573 }
574  
575 sqlDataReader.Close();
576  
577 var merged = new Csv(tagWords.Except(keywords));
578  
579 using var updateTagsSqliteCommand =
580 new SQLiteCommand(UpdateImageTagsSql, sqliteConnection, dbTransaction);
581 updateTagsSqliteCommand.Parameters.AddRange(new[]
582 {
583 new SQLiteParameter("@file", file),
584 new SQLiteParameter("@tags", merged)
585 });
586 updateTagsSqliteCommand.Prepare();
587  
588 await updateTagsSqliteCommand.ExecuteNonQueryAsync(cancellationToken);
589  
590 dbTransaction.Commit();
591  
592 return true;
593 }
594 catch (Exception exception)
595 {
596 Log.Error(exception, "Could not insert.");
597  
598 dbTransaction.Rollback();
599 }
600  
601 return false;
602 }
603 finally
604 {
605 _databaseLock.Release();
606 }
607 }
608  
8 office 609 public async Task<bool> AddTagsAsync(string file, IEnumerable<string> keywords, CancellationToken cancellationToken)
1 office 610 {
611 var connectionString = new SQLiteConnectionStringBuilder
612 {
613 ConnectionString = DatabaseConnectionString
614 };
615  
616 try
617 {
618 await _databaseLock.WaitAsync(cancellationToken);
619 }
620 catch
621 {
622 return false;
623 }
624  
625 try
626 {
627 using var sqliteConnection = new SQLiteConnection(connectionString.ConnectionString);
628 await sqliteConnection.OpenAsync(cancellationToken);
629  
630 using var dbTransaction = sqliteConnection.BeginTransaction();
631 try
632 {
633 using var sqLiteCommand = new SQLiteCommand(RetrieveImageTagsSql, sqliteConnection, dbTransaction);
634 sqLiteCommand.Parameters.AddRange(new[]
635 {
636 new SQLiteParameter("@file", file)
637 });
638 sqLiteCommand.Prepare();
639  
640 using var sqlDataReader = await sqLiteCommand.ExecuteReaderAsync(cancellationToken);
641 var tagWords = new HashSet<string>();
642 while (await sqlDataReader.ReadAsync(cancellationToken))
643 {
644 var tags = (string)sqlDataReader["Tags"];
645 if (string.IsNullOrEmpty(tags))
646 {
647 continue;
648 }
649  
650 var databaseWords = new List<string>();
651 foreach (var word in new Csv(tags))
652 {
653 if (string.IsNullOrEmpty(word))
654 {
655 continue;
656 }
657  
658 databaseWords.Add(word);
659  
660 }
661  
662 tagWords.UnionWith(databaseWords);
663 }
664  
665 sqlDataReader.Close();
666  
667 var suppliedWords = new List<string>();
668 foreach (var word in keywords)
669 {
670 if (string.IsNullOrEmpty(word))
671 {
672 continue;
673 }
674  
675 suppliedWords.Add(word);
676 }
677  
678 tagWords.UnionWith(suppliedWords);
679  
680 var merged = new Csv(tagWords);
681  
682 using var updateTagsSqliteCommand =
683 new SQLiteCommand(UpdateImageTagsSql, sqliteConnection, dbTransaction);
684 updateTagsSqliteCommand.Parameters.AddRange(new[]
685 {
686 new SQLiteParameter("@file", file),
687 new SQLiteParameter("@tags", merged)
688 });
689 updateTagsSqliteCommand.Prepare();
690  
691 await updateTagsSqliteCommand.ExecuteNonQueryAsync(cancellationToken);
692  
693 dbTransaction.Commit();
694  
695 return true;
696 }
697 catch (Exception exception)
698 {
699 Log.Error(exception, "Could not insert.");
700  
701 dbTransaction.Rollback();
702 }
703  
704 return false;
705 }
706 finally
707 {
708 _databaseLock.Release();
709 }
710 }
711  
712 public async IAsyncEnumerable<QuickImage> Search(IEnumerable<string> keywords, QuickImageSearchType searchType,
713 QuickImageSearchParameters searchParameters,
714 [EnumeratorCancellation] CancellationToken cancellationToken)
715 {
716 var stringComparer = StringComparer.OrdinalIgnoreCase;
717 var stringComparison = StringComparison.OrdinalIgnoreCase;
718 if (searchParameters.HasFlag(QuickImageSearchParameters.CaseSensitive))
719 {
720 stringComparer = StringComparer.Ordinal;
721 stringComparison = StringComparison.Ordinal;
722 }
723  
724 var connectionString = new SQLiteConnectionStringBuilder
725 {
726 ConnectionString = DatabaseConnectionString
727 };
728  
729 try
730 {
731 await _databaseLock.WaitAsync(cancellationToken);
732 }
733 catch
734 {
735 yield break;
736 }
737  
738 try
739 {
740 using var sqliteConnection = new SQLiteConnection(connectionString.ConnectionString);
741 await sqliteConnection.OpenAsync(cancellationToken);
742  
743 using var retrieveTagsIdsSqLiteCommand = new SQLiteCommand(RetrieveImagesSql, sqliteConnection);
744 retrieveTagsIdsSqLiteCommand.Prepare();
745  
746 using var sqlDataReader = await retrieveTagsIdsSqLiteCommand.ExecuteReaderAsync(cancellationToken);
747  
748 var searchWords = new HashSet<string>(keywords, stringComparer);
749 if (searchParameters.HasFlag(QuickImageSearchParameters.Split))
750 {
751 foreach (var keyword in new HashSet<string>(searchWords))
752 {
753 var split = Regex.Split(keyword,@"\s+", RegexOptions.Compiled);
754 foreach (var word in split)
755 {
756 if (string.IsNullOrEmpty(word))
757 {
758 continue;
759 }
760  
761 searchWords.Add(word);
762 }
763 }
764 }
765  
766 while (await sqlDataReader.ReadAsync(cancellationToken))
767 {
768 var file = (string)sqlDataReader["File"];
769 var fileName = Path.GetFileNameWithoutExtension(file);
770 var tags = (string)sqlDataReader["Tags"];
771 if (string.IsNullOrEmpty(tags))
772 {
773 continue;
774 }
775  
776 // Partial match.
777 var databaseTags = new Csv(tags);
778 var databaseWords = new HashSet<string>(databaseTags);
779 if (searchParameters.HasFlag(QuickImageSearchParameters.Split))
780 {
781 foreach (var keyword in new List<string>(databaseWords))
782 {
783 var split = keyword.Split(' ');
784 foreach (var word in split)
785 {
786 if (string.IsNullOrEmpty(word))
787 {
788 continue;
789 }
790  
791 databaseWords.Add(word);
792 }
793 }
794 }
795 switch (searchType)
796 {
797 case QuickImageSearchType.Any:
798 if (!searchWords.Intersect(databaseWords, stringComparer).Any() &&
799 !(searchParameters.HasFlag(QuickImageSearchParameters.Metadata) && searchWords.Any(tag => fileName.IndexOf(tag, stringComparison) != -1)))
800 {
801 continue;
802 }
803  
804 break;
805 case QuickImageSearchType.All:
806 if (!searchWords.Except(databaseWords, stringComparer).Any() &&
807 !(searchParameters.HasFlag(QuickImageSearchParameters.Metadata) && searchWords.All(tag => fileName.IndexOf(tag, stringComparison) != -1)))
808 {
809 continue;
810 }
811  
812 break;
813 }
814  
815 var digest = Convert.FromBase64String((string)sqlDataReader["Hash"]);
816 var digestHash = new Digest
817 {
818 Coefficients = digest
819 };
820  
821 Bitmap thumbnail = null;
822  
823 if (!(sqlDataReader["Thumbnail"] is DBNull))
824 {
825 using var readStream = sqlDataReader.GetStream(4);
826  
827 readStream.Position = 0L;
828  
829 using var zipStream = new GZipStream(readStream, CompressionMode.Decompress);
830 using var image = Image.FromStream(zipStream);
831 thumbnail = new Bitmap(image);
832 }
833  
834 yield return new QuickImage(file, digestHash, databaseTags, thumbnail);
835 }
836 }
837 finally
838 {
839 _databaseLock.Release();
840 }
841 }
842  
843 public async Task<QuickImage> GetImageAsync(string file, CancellationToken cancellationToken)
844 {
845 var connectionString = new SQLiteConnectionStringBuilder
846 {
847 ConnectionString = DatabaseConnectionString
848 };
849  
850 try
851 {
852 await _databaseLock.WaitAsync(cancellationToken);
853 }
854 catch
855 {
856 return null;
857 }
858  
859 try
860 {
861 using var sqliteConnection = new SQLiteConnection(connectionString.ConnectionString);
862 await sqliteConnection.OpenAsync(cancellationToken);
863  
864 using var sqLiteCommand = new SQLiteCommand(RetrieveImageSql, sqliteConnection);
865 sqLiteCommand.Parameters.AddRange(new[]
866 {
867 new SQLiteParameter("@file", file)
868 });
869 sqLiteCommand.Prepare();
870  
871 using var sqlDataReader = await sqLiteCommand.ExecuteReaderAsync(cancellationToken);
872 while (await sqlDataReader.ReadAsync(cancellationToken))
873 {
874 var imageFile = (string)sqlDataReader["File"];
875 var imageTags = (string)sqlDataReader["Tags"];
876 var imageHash = (string)sqlDataReader["Hash"];
877 var digest = Convert.FromBase64String(imageHash);
878 var hash = new Digest()
879 {
880 Coefficients = digest
881 };
882 var imageKeywords = new Csv();
883 if (!string.IsNullOrEmpty(imageTags))
884 {
885 imageKeywords = new Csv(imageTags);
886 }
887  
888 Bitmap thumbnail = null;
889  
890 if (!(sqlDataReader["Thumbnail"] is DBNull))
891 {
892 using var readStream = sqlDataReader.GetStream(4);
893  
894 readStream.Position = 0L;
895  
896 using var zipStream = new GZipStream(readStream, CompressionMode.Decompress);
897 using var image = (Bitmap)Image.FromStream(zipStream);
898 thumbnail = new Bitmap(image);
899 }
900  
901 return new QuickImage(imageFile, hash, imageKeywords, thumbnail);
902 }
903 }
904 finally
905 {
906 _databaseLock.Release();
907 }
908  
909 return null;
910 }
911  
912 public async IAsyncEnumerable<QuickImage> GetAll([EnumeratorCancellation] CancellationToken cancellationToken)
913 {
914 var connectionString = new SQLiteConnectionStringBuilder
915 {
916 ConnectionString = DatabaseConnectionString
917 };
918  
919 try
920 {
921 await _databaseLock.WaitAsync(cancellationToken);
922 }
923 catch
924 {
925 yield break;
926 }
927  
928 try
929 {
930 using var sqliteConnection = new SQLiteConnection(connectionString.ConnectionString);
931 await sqliteConnection.OpenAsync(cancellationToken);
932  
933 using var retrieveTagsIdsSqLiteCommand = new SQLiteCommand(RetrieveImagesSql, sqliteConnection);
934 retrieveTagsIdsSqLiteCommand.Prepare();
935  
936 using var sqlDataReader = await retrieveTagsIdsSqLiteCommand.ExecuteReaderAsync(cancellationToken);
937 while (await sqlDataReader.ReadAsync(cancellationToken))
938 {
939 var imageFile = (string)sqlDataReader["File"];
940 var imageTags = (string)sqlDataReader["Tags"];
941 var imageHash = (string)sqlDataReader["Hash"];
942 var digest =Convert.FromBase64String(imageHash);
943 var hash = new Digest()
944 {
945 Coefficients = digest
946 };
947 var imageKeywords = new Csv();
948 if (!string.IsNullOrEmpty(imageTags))
949 {
950 imageKeywords = new Csv(imageTags);
951 }
952  
953 Bitmap thumbnail = null;
954  
955 if (!(sqlDataReader["Thumbnail"] is DBNull))
956 {
957 using var readStream = sqlDataReader.GetStream(4);
958  
959 readStream.Position = 0L;
960  
961 using var zipStream = new GZipStream(readStream, CompressionMode.Decompress);
962 using var image = (Bitmap)Image.FromStream(zipStream);
963 thumbnail = new Bitmap(image);
964 }
965  
966 yield return new QuickImage(imageFile, hash, imageKeywords, thumbnail);
967 }
968 }
969 finally
970 {
971 _databaseLock.Release();
972 }
973 }
974  
975 public async Task SetFile(string file, Digest hash, CancellationToken cancellationToken)
976 {
977 var connectionString = new SQLiteConnectionStringBuilder
978 {
979 ConnectionString = DatabaseConnectionString
980 };
981  
982 try
983 {
984 await _databaseLock.WaitAsync(cancellationToken);
985 }
986 catch
987 {
988 return;
989 }
990  
991 try
992 {
993 using var sqliteConnection = new SQLiteConnection(connectionString.ConnectionString);
994 await sqliteConnection.OpenAsync(cancellationToken);
995  
996 using var dbTransaction = sqliteConnection.BeginTransaction();
997 using var sqLiteCommand = new SQLiteCommand(UpdateFileHashSql, sqliteConnection, dbTransaction);
998 sqLiteCommand.Parameters.AddRange(new[]
999 {
1000 new SQLiteParameter("@file", file),
1001 new SQLiteParameter("@hash", Convert.ToBase64String(hash.Coefficients))
1002 });
1003 sqLiteCommand.Prepare();
1004  
1005 try
1006 {
1007 await sqLiteCommand.ExecuteNonQueryAsync(cancellationToken);
1008  
1009 dbTransaction.Commit();
1010 }
1011 catch (Exception exception)
1012 {
1013 Log.Error(exception, "Could not update hash.");
1014  
1015 dbTransaction.Rollback();
1016 }
1017 }
1018 finally
1019 {
1020 _databaseLock.Release();
1021 }
1022 }
1023  
1024 public async Task<bool> SetFile(string from, string to, CancellationToken cancellationToken)
1025 {
1026 var connectionString = new SQLiteConnectionStringBuilder
1027 {
1028 ConnectionString = DatabaseConnectionString
1029 };
1030  
1031 try
1032 {
1033 await _databaseLock.WaitAsync(cancellationToken);
1034 }
1035 catch
1036 {
1037 return false;
1038 }
1039  
1040 try
1041 {
1042 using var sqliteConnection = new SQLiteConnection(connectionString.ConnectionString);
1043 await sqliteConnection.OpenAsync(cancellationToken);
1044  
1045 using var dbTransaction = sqliteConnection.BeginTransaction();
1046 using var sqLiteCommand = new SQLiteCommand(UpdateFileSql, sqliteConnection, dbTransaction);
1047 sqLiteCommand.Parameters.AddRange(new[]
1048 {
1049 new SQLiteParameter("@from", from),
1050 new SQLiteParameter("@to", to)
1051 });
1052 sqLiteCommand.Prepare();
1053  
1054 try
1055 {
1056 await sqLiteCommand.ExecuteNonQueryAsync(cancellationToken);
1057  
1058 dbTransaction.Commit();
1059  
1060 return true;
1061 }
1062 catch (Exception exception)
1063 {
1064 Log.Error(exception, "Could not update file.");
1065  
1066 dbTransaction.Rollback();
1067 }
1068 }
1069 finally
1070 {
1071 _databaseLock.Release();
1072 }
1073  
1074 return false;
1075 }
1076  
1077 public async Task SetHash(string file, Digest hash, CancellationToken cancellationToken)
1078 {
1079 var connectionString = new SQLiteConnectionStringBuilder
1080 {
1081 ConnectionString = DatabaseConnectionString
1082 };
1083  
1084 try
1085 {
1086 await _databaseLock.WaitAsync(cancellationToken);
1087 }
1088 catch
1089 {
1090 return;
1091 }
1092  
1093 try
1094 {
1095 using var sqliteConnection = new SQLiteConnection(connectionString.ConnectionString);
1096 await sqliteConnection.OpenAsync(cancellationToken);
1097  
1098 using var dbTransaction = sqliteConnection.BeginTransaction();
1099 using var sqLiteCommand = new SQLiteCommand(UpdateImageHashSql, sqliteConnection, dbTransaction);
1100 sqLiteCommand.Parameters.AddRange(new[]
1101 {
1102 new SQLiteParameter("@file", file),
1103 new SQLiteParameter("@hash", Convert.ToBase64String(hash.Coefficients))
1104 });
1105 sqLiteCommand.Prepare();
1106  
1107 try
1108 {
1109 await sqLiteCommand.ExecuteNonQueryAsync(cancellationToken);
1110  
1111 dbTransaction.Commit();
1112 }
1113 catch (Exception exception)
1114 {
1115 Log.Error(exception, "Could not update hash.");
1116  
1117 dbTransaction.Rollback();
1118 }
1119 }
1120 finally
1121 {
1122 _databaseLock.Release();
1123 }
1124 }
1125  
1126 public async Task SetThumbnail(string file, Bitmap thumbnail, CancellationToken cancellationToken)
1127 {
1128 var connectionString = new SQLiteConnectionStringBuilder
1129 {
1130 ConnectionString = DatabaseConnectionString
1131 };
1132  
1133 try
1134 {
1135 await _databaseLock.WaitAsync(cancellationToken);
1136 }
1137 catch
1138 {
1139 return;
1140 }
1141  
1142 try
1143 {
1144 using var bitmapMemoryStream = new MemoryStream();
1145 using var bitmapZipStream =
1146 new GZipStream(bitmapMemoryStream, CompressionMode.Compress,
1147 true);
1148 thumbnail.Save(bitmapZipStream, ImageFormat.Bmp);
1149 bitmapZipStream.Close();
1150 bitmapMemoryStream.Position = 9L;
1151  
1152 using var sqliteConnection = new SQLiteConnection(connectionString.ConnectionString);
1153 await sqliteConnection.OpenAsync(cancellationToken);
1154  
1155 using var dbTransaction = sqliteConnection.BeginTransaction();
1156  
1157 try
1158 {
1159  
1160 using (var sqLiteCommand =
1161 new SQLiteCommand(UpdateImageThumbnailSql, sqliteConnection, dbTransaction))
1162 {
1163 sqLiteCommand.Parameters.AddRange(new[]
1164 {
1165 new SQLiteParameter("@file", file),
1166 new SQLiteParameter("@thumbnailLength", bitmapMemoryStream.Length)
1167 });
1168 sqLiteCommand.Prepare();
1169  
1170 await sqLiteCommand.ExecuteNonQueryAsync(cancellationToken);
1171 }
1172  
1173 using (var getLastRowInsertCommand =
1174 new SQLiteCommand(GetLastRowInsertSql, sqliteConnection, dbTransaction))
1175 {
1176 getLastRowInsertCommand.Prepare();
1177  
1178 var rowId =
1179 (long)await getLastRowInsertCommand.ExecuteScalarAsync(cancellationToken);
1180  
1181 using var sqliteBlob =
1182 SQLiteBlob.Create(sqliteConnection, "main", "Images", "Thumbnail", rowId, false);
1183  
1184 var fileMemoryStreamData = bitmapMemoryStream.ToArray();
1185  
1186 sqliteBlob.Write(fileMemoryStreamData,
1187 fileMemoryStreamData.Length,
1188 0);
1189 }
1190  
1191 dbTransaction.Commit();
1192 }
1193 catch (Exception exception)
1194 {
1195 Log.Error(exception, "Could not update hash.");
1196  
1197 dbTransaction.Rollback();
1198 }
1199 }
1200 finally
1201 {
1202 _databaseLock.Release();
1203 }
1204 }
1205  
1206 public async Task<Digest> GetHash(string file, CancellationToken cancellationToken)
1207 {
1208 var connectionString = new SQLiteConnectionStringBuilder
1209 {
1210 ConnectionString = DatabaseConnectionString
1211 };
1212  
1213 try
1214 {
1215 await _databaseLock.WaitAsync(cancellationToken);
1216 }
1217 catch
1218 {
1219 return null;
1220 }
1221  
1222 try
1223 {
1224 using var sqliteConnection = new SQLiteConnection(connectionString.ConnectionString);
1225 await sqliteConnection.OpenAsync(cancellationToken);
1226  
1227 using var sqLiteCommand = new SQLiteCommand(RetrieveImageHashSql, sqliteConnection);
1228 sqLiteCommand.Parameters.AddRange(new[]
1229 {
1230 new SQLiteParameter("@file", file)
1231 });
1232 sqLiteCommand.Prepare();
1233  
1234 using var sqlDataReader = await sqLiteCommand.ExecuteReaderAsync(cancellationToken);
1235 while (await sqlDataReader.ReadAsync(cancellationToken))
1236 {
1237 var imageHash = (string)sqlDataReader["Hash"];
1238 var digest = Convert.FromBase64String(imageHash);
1239 var hash = new Digest()
1240 {
1241 Coefficients = digest
1242 };
1243  
1244 return hash;
1245 }
1246 }
1247 finally
1248 {
1249 _databaseLock.Release();
1250 }
1251  
1252 return null;
1253 }
1254  
1255 public async IAsyncEnumerable<string> GetTags(string file, [EnumeratorCancellation] CancellationToken cancellationToken)
1256 {
1257 var connectionString = new SQLiteConnectionStringBuilder
1258 {
1259 ConnectionString = DatabaseConnectionString
1260 };
1261  
1262 try
1263 {
1264 await _databaseLock.WaitAsync(cancellationToken);
1265 }
1266 catch
1267 {
1268 yield break;
1269 }
1270  
1271 try
1272 {
1273 using var sqliteConnection = new SQLiteConnection(connectionString.ConnectionString);
1274 await sqliteConnection.OpenAsync(cancellationToken);
1275  
1276 using var sqLiteCommand = new SQLiteCommand(RetrieveImageTagsSql, sqliteConnection);
1277 sqLiteCommand.Parameters.AddRange(new[]
1278 {
1279 new SQLiteParameter("@file", file)
1280 });
1281 sqLiteCommand.Prepare();
1282  
1283 using var sqlDataReader = await sqLiteCommand.ExecuteReaderAsync(cancellationToken);
1284 while (await sqlDataReader.ReadAsync(cancellationToken))
1285 {
1286 var tags = (string)sqlDataReader["Tags"];
1287 if (string.IsNullOrEmpty(tags))
1288 {
1289 continue;
1290 }
1291  
1292 foreach (var keyword in new Csv(tags))
1293 {
1294 yield return keyword;
1295 }
1296 }
1297 }
1298 finally
1299 {
1300 _databaseLock.Release();
1301 }
1302 }
1303  
1304 public async Task<bool> AddImageAsync(QuickImage destinationImage, CancellationToken cancellationToken)
1305 {
1306 return await AddImageAsync(destinationImage.File, destinationImage.Hash,destinationImage.Tags, (Bitmap)destinationImage.Thumbnail,
1307 cancellationToken);
1308 }
1309  
1310 public async Task<bool> RemoveImageAsync(QuickImage image, CancellationToken cancellationToken)
1311 {
1312 return await RemoveImageAsync(image.File, cancellationToken);
1313 }
1314 }
1315  
1316 }