QuickImage – Blame information for rev 1

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  
609 public async Task<bool> AddTagsAsync(string file, IEnumerable<string> keywords,
610 CancellationToken cancellationToken)
611 {
612 var connectionString = new SQLiteConnectionStringBuilder
613 {
614 ConnectionString = DatabaseConnectionString
615 };
616  
617 try
618 {
619 await _databaseLock.WaitAsync(cancellationToken);
620 }
621 catch
622 {
623 return false;
624 }
625  
626 try
627 {
628 using var sqliteConnection = new SQLiteConnection(connectionString.ConnectionString);
629 await sqliteConnection.OpenAsync(cancellationToken);
630  
631 using var dbTransaction = sqliteConnection.BeginTransaction();
632 try
633 {
634 using var sqLiteCommand = new SQLiteCommand(RetrieveImageTagsSql, sqliteConnection, dbTransaction);
635 sqLiteCommand.Parameters.AddRange(new[]
636 {
637 new SQLiteParameter("@file", file)
638 });
639 sqLiteCommand.Prepare();
640  
641 using var sqlDataReader = await sqLiteCommand.ExecuteReaderAsync(cancellationToken);
642 var tagWords = new HashSet<string>();
643 while (await sqlDataReader.ReadAsync(cancellationToken))
644 {
645 var tags = (string)sqlDataReader["Tags"];
646 if (string.IsNullOrEmpty(tags))
647 {
648 continue;
649 }
650  
651 var databaseWords = new List<string>();
652 foreach (var word in new Csv(tags))
653 {
654 if (string.IsNullOrEmpty(word))
655 {
656 continue;
657 }
658  
659 databaseWords.Add(word);
660  
661 }
662  
663 tagWords.UnionWith(databaseWords);
664 }
665  
666 sqlDataReader.Close();
667  
668 var suppliedWords = new List<string>();
669 foreach (var word in keywords)
670 {
671 if (string.IsNullOrEmpty(word))
672 {
673 continue;
674 }
675  
676 suppliedWords.Add(word);
677 }
678  
679 tagWords.UnionWith(suppliedWords);
680  
681 var merged = new Csv(tagWords);
682  
683 using var updateTagsSqliteCommand =
684 new SQLiteCommand(UpdateImageTagsSql, sqliteConnection, dbTransaction);
685 updateTagsSqliteCommand.Parameters.AddRange(new[]
686 {
687 new SQLiteParameter("@file", file),
688 new SQLiteParameter("@tags", merged)
689 });
690 updateTagsSqliteCommand.Prepare();
691  
692 await updateTagsSqliteCommand.ExecuteNonQueryAsync(cancellationToken);
693  
694 dbTransaction.Commit();
695  
696 return true;
697 }
698 catch (Exception exception)
699 {
700 Log.Error(exception, "Could not insert.");
701  
702 dbTransaction.Rollback();
703 }
704  
705 return false;
706 }
707 finally
708 {
709 _databaseLock.Release();
710 }
711 }
712  
713 public async IAsyncEnumerable<QuickImage> Search(IEnumerable<string> keywords, QuickImageSearchType searchType,
714 QuickImageSearchParameters searchParameters,
715 [EnumeratorCancellation] CancellationToken cancellationToken)
716 {
717 var stringComparer = StringComparer.OrdinalIgnoreCase;
718 var stringComparison = StringComparison.OrdinalIgnoreCase;
719 if (searchParameters.HasFlag(QuickImageSearchParameters.CaseSensitive))
720 {
721 stringComparer = StringComparer.Ordinal;
722 stringComparison = StringComparison.Ordinal;
723 }
724  
725 var connectionString = new SQLiteConnectionStringBuilder
726 {
727 ConnectionString = DatabaseConnectionString
728 };
729  
730 try
731 {
732 await _databaseLock.WaitAsync(cancellationToken);
733 }
734 catch
735 {
736 yield break;
737 }
738  
739 try
740 {
741 using var sqliteConnection = new SQLiteConnection(connectionString.ConnectionString);
742 await sqliteConnection.OpenAsync(cancellationToken);
743  
744 using var retrieveTagsIdsSqLiteCommand = new SQLiteCommand(RetrieveImagesSql, sqliteConnection);
745 retrieveTagsIdsSqLiteCommand.Prepare();
746  
747 using var sqlDataReader = await retrieveTagsIdsSqLiteCommand.ExecuteReaderAsync(cancellationToken);
748  
749 var searchWords = new HashSet<string>(keywords, stringComparer);
750 if (searchParameters.HasFlag(QuickImageSearchParameters.Split))
751 {
752 foreach (var keyword in new HashSet<string>(searchWords))
753 {
754 var split = Regex.Split(keyword,@"\s+", RegexOptions.Compiled);
755 foreach (var word in split)
756 {
757 if (string.IsNullOrEmpty(word))
758 {
759 continue;
760 }
761  
762 searchWords.Add(word);
763 }
764 }
765 }
766  
767 while (await sqlDataReader.ReadAsync(cancellationToken))
768 {
769 var file = (string)sqlDataReader["File"];
770 var fileName = Path.GetFileNameWithoutExtension(file);
771 var tags = (string)sqlDataReader["Tags"];
772 if (string.IsNullOrEmpty(tags))
773 {
774 continue;
775 }
776  
777 // Partial match.
778 var databaseTags = new Csv(tags);
779 var databaseWords = new HashSet<string>(databaseTags);
780 if (searchParameters.HasFlag(QuickImageSearchParameters.Split))
781 {
782 foreach (var keyword in new List<string>(databaseWords))
783 {
784 var split = keyword.Split(' ');
785 foreach (var word in split)
786 {
787 if (string.IsNullOrEmpty(word))
788 {
789 continue;
790 }
791  
792 databaseWords.Add(word);
793 }
794 }
795 }
796 switch (searchType)
797 {
798 case QuickImageSearchType.Any:
799 if (!searchWords.Intersect(databaseWords, stringComparer).Any() &&
800 !(searchParameters.HasFlag(QuickImageSearchParameters.Metadata) && searchWords.Any(tag => fileName.IndexOf(tag, stringComparison) != -1)))
801 {
802 continue;
803 }
804  
805 break;
806 case QuickImageSearchType.All:
807 if (!searchWords.Except(databaseWords, stringComparer).Any() &&
808 !(searchParameters.HasFlag(QuickImageSearchParameters.Metadata) && searchWords.All(tag => fileName.IndexOf(tag, stringComparison) != -1)))
809 {
810 continue;
811 }
812  
813 break;
814 }
815  
816 var digest = Convert.FromBase64String((string)sqlDataReader["Hash"]);
817 var digestHash = new Digest
818 {
819 Coefficients = digest
820 };
821  
822 Bitmap thumbnail = null;
823  
824 if (!(sqlDataReader["Thumbnail"] is DBNull))
825 {
826 using var readStream = sqlDataReader.GetStream(4);
827  
828 readStream.Position = 0L;
829  
830 using var zipStream = new GZipStream(readStream, CompressionMode.Decompress);
831 using var image = Image.FromStream(zipStream);
832 thumbnail = new Bitmap(image);
833 }
834  
835 yield return new QuickImage(file, digestHash, databaseTags, thumbnail);
836 }
837 }
838 finally
839 {
840 _databaseLock.Release();
841 }
842 }
843  
844 public async Task<QuickImage> GetImageAsync(string file, CancellationToken cancellationToken)
845 {
846 var connectionString = new SQLiteConnectionStringBuilder
847 {
848 ConnectionString = DatabaseConnectionString
849 };
850  
851 try
852 {
853 await _databaseLock.WaitAsync(cancellationToken);
854 }
855 catch
856 {
857 return null;
858 }
859  
860 try
861 {
862 using var sqliteConnection = new SQLiteConnection(connectionString.ConnectionString);
863 await sqliteConnection.OpenAsync(cancellationToken);
864  
865 using var sqLiteCommand = new SQLiteCommand(RetrieveImageSql, sqliteConnection);
866 sqLiteCommand.Parameters.AddRange(new[]
867 {
868 new SQLiteParameter("@file", file)
869 });
870 sqLiteCommand.Prepare();
871  
872 using var sqlDataReader = await sqLiteCommand.ExecuteReaderAsync(cancellationToken);
873 while (await sqlDataReader.ReadAsync(cancellationToken))
874 {
875 var imageFile = (string)sqlDataReader["File"];
876 var imageTags = (string)sqlDataReader["Tags"];
877 var imageHash = (string)sqlDataReader["Hash"];
878 var digest = Convert.FromBase64String(imageHash);
879 var hash = new Digest()
880 {
881 Coefficients = digest
882 };
883 var imageKeywords = new Csv();
884 if (!string.IsNullOrEmpty(imageTags))
885 {
886 imageKeywords = new Csv(imageTags);
887 }
888  
889 Bitmap thumbnail = null;
890  
891 if (!(sqlDataReader["Thumbnail"] is DBNull))
892 {
893 using var readStream = sqlDataReader.GetStream(4);
894  
895 readStream.Position = 0L;
896  
897 using var zipStream = new GZipStream(readStream, CompressionMode.Decompress);
898 using var image = (Bitmap)Image.FromStream(zipStream);
899 thumbnail = new Bitmap(image);
900 }
901  
902 return new QuickImage(imageFile, hash, imageKeywords, thumbnail);
903 }
904 }
905 finally
906 {
907 _databaseLock.Release();
908 }
909  
910 return null;
911 }
912  
913 public async IAsyncEnumerable<QuickImage> GetAll([EnumeratorCancellation] CancellationToken cancellationToken)
914 {
915 var connectionString = new SQLiteConnectionStringBuilder
916 {
917 ConnectionString = DatabaseConnectionString
918 };
919  
920 try
921 {
922 await _databaseLock.WaitAsync(cancellationToken);
923 }
924 catch
925 {
926 yield break;
927 }
928  
929 try
930 {
931 using var sqliteConnection = new SQLiteConnection(connectionString.ConnectionString);
932 await sqliteConnection.OpenAsync(cancellationToken);
933  
934 using var retrieveTagsIdsSqLiteCommand = new SQLiteCommand(RetrieveImagesSql, sqliteConnection);
935 retrieveTagsIdsSqLiteCommand.Prepare();
936  
937 using var sqlDataReader = await retrieveTagsIdsSqLiteCommand.ExecuteReaderAsync(cancellationToken);
938 while (await sqlDataReader.ReadAsync(cancellationToken))
939 {
940 var imageFile = (string)sqlDataReader["File"];
941 var imageTags = (string)sqlDataReader["Tags"];
942 var imageHash = (string)sqlDataReader["Hash"];
943 var digest =Convert.FromBase64String(imageHash);
944 var hash = new Digest()
945 {
946 Coefficients = digest
947 };
948 var imageKeywords = new Csv();
949 if (!string.IsNullOrEmpty(imageTags))
950 {
951 imageKeywords = new Csv(imageTags);
952 }
953  
954 Bitmap thumbnail = null;
955  
956 if (!(sqlDataReader["Thumbnail"] is DBNull))
957 {
958 using var readStream = sqlDataReader.GetStream(4);
959  
960 readStream.Position = 0L;
961  
962 using var zipStream = new GZipStream(readStream, CompressionMode.Decompress);
963 using var image = (Bitmap)Image.FromStream(zipStream);
964 thumbnail = new Bitmap(image);
965 }
966  
967 yield return new QuickImage(imageFile, hash, imageKeywords, thumbnail);
968 }
969 }
970 finally
971 {
972 _databaseLock.Release();
973 }
974 }
975  
976 public async Task SetFile(string file, Digest hash, CancellationToken cancellationToken)
977 {
978 var connectionString = new SQLiteConnectionStringBuilder
979 {
980 ConnectionString = DatabaseConnectionString
981 };
982  
983 try
984 {
985 await _databaseLock.WaitAsync(cancellationToken);
986 }
987 catch
988 {
989 return;
990 }
991  
992 try
993 {
994 using var sqliteConnection = new SQLiteConnection(connectionString.ConnectionString);
995 await sqliteConnection.OpenAsync(cancellationToken);
996  
997 using var dbTransaction = sqliteConnection.BeginTransaction();
998 using var sqLiteCommand = new SQLiteCommand(UpdateFileHashSql, sqliteConnection, dbTransaction);
999 sqLiteCommand.Parameters.AddRange(new[]
1000 {
1001 new SQLiteParameter("@file", file),
1002 new SQLiteParameter("@hash", Convert.ToBase64String(hash.Coefficients))
1003 });
1004 sqLiteCommand.Prepare();
1005  
1006 try
1007 {
1008 await sqLiteCommand.ExecuteNonQueryAsync(cancellationToken);
1009  
1010 dbTransaction.Commit();
1011 }
1012 catch (Exception exception)
1013 {
1014 Log.Error(exception, "Could not update hash.");
1015  
1016 dbTransaction.Rollback();
1017 }
1018 }
1019 finally
1020 {
1021 _databaseLock.Release();
1022 }
1023 }
1024  
1025 public async Task<bool> SetFile(string from, string to, CancellationToken cancellationToken)
1026 {
1027 var connectionString = new SQLiteConnectionStringBuilder
1028 {
1029 ConnectionString = DatabaseConnectionString
1030 };
1031  
1032 try
1033 {
1034 await _databaseLock.WaitAsync(cancellationToken);
1035 }
1036 catch
1037 {
1038 return false;
1039 }
1040  
1041 try
1042 {
1043 using var sqliteConnection = new SQLiteConnection(connectionString.ConnectionString);
1044 await sqliteConnection.OpenAsync(cancellationToken);
1045  
1046 using var dbTransaction = sqliteConnection.BeginTransaction();
1047 using var sqLiteCommand = new SQLiteCommand(UpdateFileSql, sqliteConnection, dbTransaction);
1048 sqLiteCommand.Parameters.AddRange(new[]
1049 {
1050 new SQLiteParameter("@from", from),
1051 new SQLiteParameter("@to", to)
1052 });
1053 sqLiteCommand.Prepare();
1054  
1055 try
1056 {
1057 await sqLiteCommand.ExecuteNonQueryAsync(cancellationToken);
1058  
1059 dbTransaction.Commit();
1060  
1061 return true;
1062 }
1063 catch (Exception exception)
1064 {
1065 Log.Error(exception, "Could not update file.");
1066  
1067 dbTransaction.Rollback();
1068 }
1069 }
1070 finally
1071 {
1072 _databaseLock.Release();
1073 }
1074  
1075 return false;
1076 }
1077  
1078 public async Task SetHash(string file, Digest hash, CancellationToken cancellationToken)
1079 {
1080 var connectionString = new SQLiteConnectionStringBuilder
1081 {
1082 ConnectionString = DatabaseConnectionString
1083 };
1084  
1085 try
1086 {
1087 await _databaseLock.WaitAsync(cancellationToken);
1088 }
1089 catch
1090 {
1091 return;
1092 }
1093  
1094 try
1095 {
1096 using var sqliteConnection = new SQLiteConnection(connectionString.ConnectionString);
1097 await sqliteConnection.OpenAsync(cancellationToken);
1098  
1099 using var dbTransaction = sqliteConnection.BeginTransaction();
1100 using var sqLiteCommand = new SQLiteCommand(UpdateImageHashSql, sqliteConnection, dbTransaction);
1101 sqLiteCommand.Parameters.AddRange(new[]
1102 {
1103 new SQLiteParameter("@file", file),
1104 new SQLiteParameter("@hash", Convert.ToBase64String(hash.Coefficients))
1105 });
1106 sqLiteCommand.Prepare();
1107  
1108 try
1109 {
1110 await sqLiteCommand.ExecuteNonQueryAsync(cancellationToken);
1111  
1112 dbTransaction.Commit();
1113 }
1114 catch (Exception exception)
1115 {
1116 Log.Error(exception, "Could not update hash.");
1117  
1118 dbTransaction.Rollback();
1119 }
1120 }
1121 finally
1122 {
1123 _databaseLock.Release();
1124 }
1125 }
1126  
1127 public async Task SetThumbnail(string file, Bitmap thumbnail, CancellationToken cancellationToken)
1128 {
1129 var connectionString = new SQLiteConnectionStringBuilder
1130 {
1131 ConnectionString = DatabaseConnectionString
1132 };
1133  
1134 try
1135 {
1136 await _databaseLock.WaitAsync(cancellationToken);
1137 }
1138 catch
1139 {
1140 return;
1141 }
1142  
1143 try
1144 {
1145 using var bitmapMemoryStream = new MemoryStream();
1146 using var bitmapZipStream =
1147 new GZipStream(bitmapMemoryStream, CompressionMode.Compress,
1148 true);
1149 thumbnail.Save(bitmapZipStream, ImageFormat.Bmp);
1150 bitmapZipStream.Close();
1151 bitmapMemoryStream.Position = 9L;
1152  
1153 using var sqliteConnection = new SQLiteConnection(connectionString.ConnectionString);
1154 await sqliteConnection.OpenAsync(cancellationToken);
1155  
1156 using var dbTransaction = sqliteConnection.BeginTransaction();
1157  
1158 try
1159 {
1160  
1161 using (var sqLiteCommand =
1162 new SQLiteCommand(UpdateImageThumbnailSql, sqliteConnection, dbTransaction))
1163 {
1164 sqLiteCommand.Parameters.AddRange(new[]
1165 {
1166 new SQLiteParameter("@file", file),
1167 new SQLiteParameter("@thumbnailLength", bitmapMemoryStream.Length)
1168 });
1169 sqLiteCommand.Prepare();
1170  
1171 await sqLiteCommand.ExecuteNonQueryAsync(cancellationToken);
1172 }
1173  
1174 using (var getLastRowInsertCommand =
1175 new SQLiteCommand(GetLastRowInsertSql, sqliteConnection, dbTransaction))
1176 {
1177 getLastRowInsertCommand.Prepare();
1178  
1179 var rowId =
1180 (long)await getLastRowInsertCommand.ExecuteScalarAsync(cancellationToken);
1181  
1182 using var sqliteBlob =
1183 SQLiteBlob.Create(sqliteConnection, "main", "Images", "Thumbnail", rowId, false);
1184  
1185 var fileMemoryStreamData = bitmapMemoryStream.ToArray();
1186  
1187 sqliteBlob.Write(fileMemoryStreamData,
1188 fileMemoryStreamData.Length,
1189 0);
1190 }
1191  
1192 dbTransaction.Commit();
1193 }
1194 catch (Exception exception)
1195 {
1196 Log.Error(exception, "Could not update hash.");
1197  
1198 dbTransaction.Rollback();
1199 }
1200 }
1201 finally
1202 {
1203 _databaseLock.Release();
1204 }
1205 }
1206  
1207 public async Task<Digest> GetHash(string file, CancellationToken cancellationToken)
1208 {
1209 var connectionString = new SQLiteConnectionStringBuilder
1210 {
1211 ConnectionString = DatabaseConnectionString
1212 };
1213  
1214 try
1215 {
1216 await _databaseLock.WaitAsync(cancellationToken);
1217 }
1218 catch
1219 {
1220 return null;
1221 }
1222  
1223 try
1224 {
1225 using var sqliteConnection = new SQLiteConnection(connectionString.ConnectionString);
1226 await sqliteConnection.OpenAsync(cancellationToken);
1227  
1228 using var sqLiteCommand = new SQLiteCommand(RetrieveImageHashSql, sqliteConnection);
1229 sqLiteCommand.Parameters.AddRange(new[]
1230 {
1231 new SQLiteParameter("@file", file)
1232 });
1233 sqLiteCommand.Prepare();
1234  
1235 using var sqlDataReader = await sqLiteCommand.ExecuteReaderAsync(cancellationToken);
1236 while (await sqlDataReader.ReadAsync(cancellationToken))
1237 {
1238 var imageHash = (string)sqlDataReader["Hash"];
1239 var digest = Convert.FromBase64String(imageHash);
1240 var hash = new Digest()
1241 {
1242 Coefficients = digest
1243 };
1244  
1245 return hash;
1246 }
1247 }
1248 finally
1249 {
1250 _databaseLock.Release();
1251 }
1252  
1253 return null;
1254 }
1255  
1256 public async IAsyncEnumerable<string> GetTags(string file, [EnumeratorCancellation] CancellationToken cancellationToken)
1257 {
1258 var connectionString = new SQLiteConnectionStringBuilder
1259 {
1260 ConnectionString = DatabaseConnectionString
1261 };
1262  
1263 try
1264 {
1265 await _databaseLock.WaitAsync(cancellationToken);
1266 }
1267 catch
1268 {
1269 yield break;
1270 }
1271  
1272 try
1273 {
1274 using var sqliteConnection = new SQLiteConnection(connectionString.ConnectionString);
1275 await sqliteConnection.OpenAsync(cancellationToken);
1276  
1277 using var sqLiteCommand = new SQLiteCommand(RetrieveImageTagsSql, sqliteConnection);
1278 sqLiteCommand.Parameters.AddRange(new[]
1279 {
1280 new SQLiteParameter("@file", file)
1281 });
1282 sqLiteCommand.Prepare();
1283  
1284 using var sqlDataReader = await sqLiteCommand.ExecuteReaderAsync(cancellationToken);
1285 while (await sqlDataReader.ReadAsync(cancellationToken))
1286 {
1287 var tags = (string)sqlDataReader["Tags"];
1288 if (string.IsNullOrEmpty(tags))
1289 {
1290 continue;
1291 }
1292  
1293 foreach (var keyword in new Csv(tags))
1294 {
1295 yield return keyword;
1296 }
1297 }
1298 }
1299 finally
1300 {
1301 _databaseLock.Release();
1302 }
1303 }
1304  
1305 public async Task<bool> AddImageAsync(QuickImage destinationImage, CancellationToken cancellationToken)
1306 {
1307 return await AddImageAsync(destinationImage.File, destinationImage.Hash,destinationImage.Tags, (Bitmap)destinationImage.Thumbnail,
1308 cancellationToken);
1309 }
1310  
1311 public async Task<bool> RemoveImageAsync(QuickImage image, CancellationToken cancellationToken)
1312 {
1313 return await RemoveImageAsync(image.File, cancellationToken);
1314 }
1315 }
1316  
1317 }