Check-in [c3c2517228]

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Merge dotnet into dotnet/timers.
Timelines: family | ancestors | descendants | both | mistake
Files: files | file ages | folders
SHA1: c3c251722803d739e46fc03eb81788697152aab7
User & Date: tinus 2019-09-26 06:55:17
Context
2019-09-26
07:24
Merge dotnet into dotnet/timers. Leaf check-in: a28a3e8d4e user: tinus tags: mistake
06:55
Merge dotnet into dotnet/timers. check-in: c3c2517228 user: tinus tags: mistake
2019-09-25
21:51
Upgraded to .NET Core 3.0. Set project to use non-nullable reference types by default. check-in: 9eecbcebf2 user: tinus tags: dotnet/nullable
2019-09-14
05:54
Merge dotnet into dotnet/timers (error reporting improvements). Leaf check-in: 1e71405b77 user: tinus tags: dotnet/timers
Changes

Added .editorconfig.































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
[*.cs]

# CA2007: Consider calling ConfigureAwait on the awaited task
dotnet_diagnostic.CA2007.severity = silent

# CA1051: Do not declare visible instance fields
dotnet_diagnostic.CA1051.severity = silent

# CA1303: Do not pass literals as localized parameters
dotnet_diagnostic.CA1303.severity = suggestion

# CA1031: Do not catch general exception types
dotnet_diagnostic.CA1031.severity = suggestion

# CA1820: Test for empty strings using string length
dotnet_diagnostic.CA1820.severity = none

# CA1305: Specify IFormatProvider
dotnet_diagnostic.CA1305.severity = suggestion

# CS8629: Nullable value type may be null.
dotnet_diagnostic.CS8629.severity = suggestion

# CA1819: Properties should not return arrays
dotnet_diagnostic.CA1819.severity = silent

# CA1062: Validate arguments of public methods
dotnet_diagnostic.CA1062.severity = suggestion

# CS8602: Dereference of a possibly null reference.
dotnet_diagnostic.CS8602.severity = suggestion

Changes to MailJanitor.csproj.

1
2
3
4
5






6
7
8
9
10
11
12
13
14

15
16
17
18
19
20
21
22
23
24
<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>netcoreapp2.2</TargetFramework>






  </PropertyGroup>

  <ItemGroup>
    <None Remove="_FOSSIL_" />
  </ItemGroup>

  <ItemGroup>
    <PackageReference Include="CommandLineParser" Version="2.6.0" />
    <PackageReference Include="mailkit" Version="2.2.0" />

    <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="2.2.6" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="2.2.6">
      <IncludeAssets>runtime; build; native; contentfiles; analyzers</IncludeAssets>
      <PrivateAssets>all</PrivateAssets>
    </PackageReference>
    <PackageReference Include="nuglify" Version="1.5.13" />
    <PackageReference Include="ReverseMarkdown" Version="3.8.0" />
  </ItemGroup>

</Project>




|
>
>
>
>
>
>



|




|
>
|
|








1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>netcoreapp3.0</TargetFramework>
    <StartupObject>MailJanitor.Program</StartupObject>
    <Nullable>enable</Nullable>
  </PropertyGroup>

  <PropertyGroup Condition="'$(Configuration)|$(Platform)'=='Debug|AnyCPU'">
    <CheckForOverflowUnderflow>false</CheckForOverflowUnderflow>
  </PropertyGroup>

  <ItemGroup>
    <None Remove="_FOSSIL_"  />
  </ItemGroup>

  <ItemGroup>
    <PackageReference Include="CommandLineParser" Version="2.6.0" />
    <PackageReference Include="mailkit" Version="2.3.1.6" />
    <PackageReference Include="Microsoft.CodeAnalysis.FxCopAnalyzers" Version="2.9.3" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="3.0.0" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="3.0.0">
      <IncludeAssets>runtime; build; native; contentfiles; analyzers</IncludeAssets>
      <PrivateAssets>all</PrivateAssets>
    </PackageReference>
    <PackageReference Include="nuglify" Version="1.5.13" />
    <PackageReference Include="ReverseMarkdown" Version="3.8.0" />
  </ItemGroup>

</Project>

Changes to MailJanitorContext.cs.

1
2
3
4
5
6
7
8
9
10
11

12
13
14
15
16
17
18
19
20

21
22
23
24
25
26
27
..
29
30
31
32
33
34
35






36
37
38
39
40
41
42
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MailJanitor.Models;
using Microsoft.EntityFrameworkCore;

namespace MailJanitor
{
    public class MailJanitorContext : DbContext
    {

        public DbSet<Account> Accounts { get; set; }
        public DbSet<Folder> Folders { get; set; }
        public DbSet<FolderMessage> FolderMessages { get; set; }
        public DbSet<Message> Messages { get; set; }
        public DbSet<MessageParticipant> MessageParticipants { get; set; }
        public DbSet<Participant> Participants { get; set; }
        public DbSet<Keyword> Keywords { get; set; }
        public DbSet<MessageKeyword> MessageKeywords { get; set; }
        public DbSet<MessageReference> MessageReferences { get; set; }


        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder
                .UseSqlite("Data source=mailjanitor.sqlite");
        }

................................................................................
        {
            base.OnModelCreating(modelBuilder);

            modelBuilder.Entity<Message>(b =>
            {
                b.HasIndex(m => m.GlobalID);
                b.HasIndex(m => m.RfcMessageID);






            });

            modelBuilder.Entity<Participant>(b =>
            {
                b.HasIndex(p => new { p.Address, p.Name })
                    .IsUnique();
            });
<
<
<
<





|

>









>







 







>
>
>
>
>
>











1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
..
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46




using MailJanitor.Models;
using Microsoft.EntityFrameworkCore;

namespace MailJanitor
{
	public class MailJanitorContext : DbContext
    {
#nullable disable
        public DbSet<Account> Accounts { get; set; }
        public DbSet<Folder> Folders { get; set; }
        public DbSet<FolderMessage> FolderMessages { get; set; }
        public DbSet<Message> Messages { get; set; }
        public DbSet<MessageParticipant> MessageParticipants { get; set; }
        public DbSet<Participant> Participants { get; set; }
        public DbSet<Keyword> Keywords { get; set; }
        public DbSet<MessageKeyword> MessageKeywords { get; set; }
        public DbSet<MessageReference> MessageReferences { get; set; }
#nullable restore

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder
                .UseSqlite("Data source=mailjanitor.sqlite");
        }

................................................................................
        {
            base.OnModelCreating(modelBuilder);

            modelBuilder.Entity<Message>(b =>
            {
                b.HasIndex(m => m.GlobalID);
                b.HasIndex(m => m.RfcMessageID);
                //b.Property(m => m.Date)
                //    .HasConversion(new DateTimeOffsetToBinaryConverter());
                //b.Property(m => m.DateReceived)
                //    .HasConversion(new DateTimeOffsetToBinaryConverter());
                //b.Property(m => m.DateRetrievedUTC)
                //    .HasConversion(new DateTimeToBinaryConverter());
            });

            modelBuilder.Entity<Participant>(b =>
            {
                b.HasIndex(p => new { p.Address, p.Name })
                    .IsUnique();
            });

Changes to MailSynchronizer.cs.

25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48





49
50
51
52
53
54
55
56
57
58
59
60
...
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
...
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
...
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
...
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
...
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459


460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499

500
501
502
503
504
505
506
507
508










509
510
511
512
513
514
515
516
517
518
519
520
521
522
...
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
...
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
...
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
...
613
614
615
616
617
618
619

620
621
622
623
624
625
626
627
628
629
630
631
632
633
...
647
648
649
650
651
652
653


654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718










719
720
721
722
723


724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764










765
766
767
768
769
770
771
772
773
774
775


776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832










833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
...
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
        {
            GithubFlavored = true,
            RemoveComments = false,
            SmartHrefHandling = true,
            TableWithoutHeaderRowHandling = ReverseMarkdown.Config.TableWithoutHeaderRowHandlingOption.Default,
            UnknownTags = ReverseMarkdown.Config.UnknownTagsOption.Bypass,
        };
        private Dictionary<(string, bool), long> _keywords;
        private Dictionary<string, long> _participants;
        private int _totalCountAllFolders;
        private int _countAllFolders;
        private DateTime _startAccount;
        private bool _haveDeleted = false;


        public MailSynchronizer(CancellationToken cancellationToken)
        {
            _cancellationToken = cancellationToken;
        }

        public async Task SynchronizeAccount(string host, short port, NetworkCredential credentials,
                                             Func<IMailFolder,bool> folderFilter = null,
                                             bool vacuumDatabase = false)
        {





            Account account;
            using (var work = new UnitOfWork(_cancellationToken))
            {
                account = await work.Accounts.GetOrCreateAsync(host, port, credentials.UserName);
                _participants = await work.Participants.GetDictionaryAsync(p => p.Address ?? p.Name, p => p.ID, StringComparer.InvariantCultureIgnoreCase);
                _keywords = await work.Keywords.GetDictionaryAsync(kw => (kw.Name, kw.IsLabel), kw => kw.ID, KeywordEqualityComparer.InvariantCultureIgnoreCase);
            }

            using (var client = new MailKit.Net.Imap.ImapClient())
            {
                Con.WriteLine("Connecting to server...", TraceLevel.Verbose);
                await client.ConnectAsync(host, port, true, _cancellationToken);
................................................................................
                            _totalCountAllFolders -= mailFolder.Count;
                            continue;
                        }
                    }

                    TimerCollection.Global.Start("SynchronizeFolder");
                    try {
                    await SynchronizeFolder(mailFolder, dbFolder);
                    } finally {
                        TimerCollection.Global.Stop("SynchronizeFolder");
                    }
                    //Con.WriteLine(); Con.WriteLine(TimerCollection.Global.ToString(), System.Diagnostics.TraceLevel.Verbose);

                    if (mailFolder.UidNext != null)
                    {
................................................................................
                        }
                    }
                } // foreach folder

                // If we have remaining folders that aren't on the server anymore, delete them
                if (remainingFolderIDs.Any())
                {
                    using (var work = new UnitOfWork(_cancellationToken))
                    {
                        // First verify that they don't have any child folders; otherwise we'd be deleting those too...
                        remainingFolderIDs = work.Folders.Select(f => remainingFolderIDs.Contains(f.ID) && !f.Children.Any(),
                                                                 f => f.ID).ToList();
                        if (remainingFolderIDs.Any())
                        {
                            Con.WriteLine("Deleting local folder(s):");
                            Con.WriteLine("- " + string.Join("\n- ", await work.Folders.GetFullNamesAsync(remainingFolderIDs)));

                            work.Folders.DeleteByIDs(remainingFolderIDs);
                            await work.SaveChangesAsync();
                            _haveDeleted = true;
                        }
                    }
                }
            }

            TimerCollection.Global.Start("ClearUnunsedObjects");
            try
            {
................................................................................
                            Con.Write("; ETA " + (_startAccount.ToLocalTime() + TimeSpan.FromTicks(elapsed.Ticks * _totalCountAllFolders / _countAllFolders)).ToString("s").Replace('T', ' '));
                        }
                        lastProgressUpdate = DateTime.UtcNow;
                    }

                    TimerCollection.Global.Start("SynchronizeMessage");
                    try {
                    await SynchronizeMessage(summary, mailFolder, dbFolder.ID, folderMsgID, messageID);
                    } finally {
                        TimerCollection.Global.Stop("SynchronizeMessage");
                    }

                } // foreach (summary)
            }
            finally
................................................................................
                }
            }
        }

        private async Task SynchronizeMessage(IMessageSummary summary, IMailFolder mailFolder,
                                              long folderID, long? folderMsgID, long? messageID)
        {
            using (var work = new UnitOfWork(_cancellationToken))
            {
                try
                {
                    // Deleted message: don't download it; delete it from the database instead
                    if (summary.Flags.Value.HasFlag(MessageFlags.Deleted))
                    {
                        if (folderMsgID != null)
                        {
                            work.FolderMessages.DeleteByID(folderMsgID.Value);
                            await work.SaveChangesAsync();
                            _haveDeleted = true;
                        }
                        return;
                    }

                    // Otherwise, check out the existing message
                    Message dbMessage = null;
                    if (messageID != null)
                    {
                        dbMessage = await work.Messages.GetByIdAsync(messageID.Value);
                    }
                    FolderMessage folderMessage = null;
                    if (folderMsgID == null
                        && dbMessage != null
                        && summary.Flags == dbMessage.Flags
                        && summary.InternalDate == dbMessage.DateReceived
                        && string.Join(", ", summary.Keywords) == dbMessage.Keywords)
                    {
                        folderMessage = new FolderMessage
                        {
                            FolderID = folderID,
                            UniqueId = summary.UniqueId,
                            Message = dbMessage,
                        };
                        await work.FolderMessages.AddAsync(folderMessage);
                    }
                    else if (dbMessage == null
                            || summary.Flags != dbMessage.Flags
                            || summary.InternalDate != dbMessage.DateReceived
                            || string.Join(", ", summary.Keywords) != dbMessage.Keywords)
                    {
                        MimeMessage mailMessage = await GetMailMessageAsync(summary, mailFolder);
                        // save message to database
                        if (folderMsgID != null)
                        {
                            folderMessage = await work.DB.FolderMessages
                                .Where(fm => fm.ID == folderMsgID)
                                .Include(fm => fm.Message)
                                .ThenInclude(m => m.Participants)
                                .SingleOrDefaultAsync(_cancellationToken);
                        }
                        if (folderMessage == null)
                        {
                            folderMessage = new FolderMessage
                            {
                                FolderID = folderID,
                                UniqueId = summary.UniqueId,
                                Message = dbMessage ?? new Message
                                {
                                    Participants = new List<MessageParticipant>(),
                                },
                            };
                            await work.FolderMessages.AddAsync(folderMessage);
                        }
                        dbMessage = folderMessage.Message;
                        dbMessage.GlobalID = summary.GMailMessageId;
                        dbMessage.DateRetrievedUTC = DateTime.UtcNow;
                        dbMessage.Flags = summary.Flags ?? MessageFlags.None;
                        dbMessage.DateReceived = summary.InternalDate;
                        TimerCollection.Global.Start("PopulateMessageAsync");
                        try
                        {
                            await PopulateMessageAsync(dbMessage, mailMessage, summary.Keywords);
                        }
                        finally
                        {
                            TimerCollection.Global.Stop("PopulateMessageAsync");
                        }

                        // Store all separate entities contained in the mail message
                        TimerCollection.Global.Start("PopulateParticipants");
                        await PopulateParticipantsAsync(work, dbMessage, mailMessage);    // from headers
                        TimerCollection.Global.Stop("PopulateParticipants");
                        TimerCollection.Global.Start("PopulateKeywords");
                        await PopulateKeywordsAsync(work, dbMessage, summary.Keywords, summary.GMailLabels); // from summary
                        TimerCollection.Global.Stop("PopulateKeywords");
                        TimerCollection.Global.Start("PopulateReferences");
                        await PopulateReferencesAsync(work, dbMessage, mailMessage);      // from summary + headers
                        TimerCollection.Global.Stop("PopulateReferences");
                    }
                    await work.SaveChangesAsync();
                }
                catch (Exception ex)
                {
                    if (ex is TaskCanceledException || ex is OperationCanceledException || ex is ServiceNotConnectedException)
                    {
                        throw; // those need to fall through
                    }
                    await Con.WriteLineAsync();
                    await Con.WriteLineAsync(ex);
                }
            }
        }

        private async Task<MimeMessage> GetMailMessageAsync(IMessageSummary summary, IMailFolder mailFolder)
        {
            MimeMessage mailMessage;
            try
................................................................................
                    TimerCollection.Global.Stop("mailFolder.GetMessageAsync");
                }
            }
            catch (FormatException)
            {
                var mailEncoding = Encoding.GetEncoding("ISO-8859-1");
                // Upon failure to parse the message, try to download the entire message stream
                using (Stream mailStream = await mailFolder.GetStreamAsync(summary.UniqueId, 0, (int)summary.Size.Value, _cancellationToken))
                using (StreamReader reader = new StreamReader(mailStream, mailEncoding, true))
                {
                    using (var fs = new FileStream($"{summary.GMailMessageId}.eml", FileMode.Create, FileAccess.ReadWrite, FileShare.Read))
                    {
                        await mailStream.CopyToAsync(fs, _cancellationToken);
                        mailStream.Position = 0;
                    }
                    // Now, attempt to fix the mail message, by skipping lines until one doesn't start with a spacing character
                    var sbSource = new StringBuilder((int)mailStream.Length);
                    while (!reader.EndOfStream)
                    {
                        string line = await reader.ReadLineAsync();
                        if (line.Length == 0 || !char.IsWhiteSpace(line[0]))
                        {
                            sbSource.Append(line);
                            sbSource.Append("\r\n");
                            sbSource.Append(await reader.ReadToEndAsync());
                        }
                    }
                    using (var fixedStream = new MemoryStream((int)mailStream.Length))
                    {
                        await fixedStream.WriteAsync(reader.CurrentEncoding.GetBytes(sbSource.ToString()), _cancellationToken);
                        fixedStream.Position = 0;
                        var options = new ParserOptions
                        {
                            AddressParserComplianceMode = RfcComplianceMode.Loose,
                            AllowAddressesWithoutDomain = true,
                            AllowUnquotedCommasInAddresses = false,
                            CharsetEncoding = mailEncoding,
                            MaxAddressGroupDepth = 3,
                            ParameterComplianceMode = RfcComplianceMode.Loose,
                            RespectContentLength = true,
                            Rfc2047ComplianceMode = RfcComplianceMode.Loose,
                        };
                        mailMessage = await MimeMessage.LoadAsync(options, fixedStream, _cancellationToken);
                    }
                }
            }

            return mailMessage;
        }

        public async Task ClearUnunsedObjectsAsync(bool vacuumDatabase = false)
        {
            using (var work = new UnitOfWork(_cancellationToken))
            {
                await Con.WriteLineAsync("Cleaning up database...", TraceLevel.Verbose);

                // Delete any messages not linked to a folder
                work.Messages.DeleteByIDs(m => !m.Folders.Any(), m => m.ID);
                int numDeleted = await work.SaveChangesAsync();

                // Delete any participants not linked to a message
                work.Participants.DeleteByIDs(p => !p.Messages.Any(), p => p.ID);
                numDeleted += await work.SaveChangesAsync();

                if (vacuumDatabase && (_haveDeleted || numDeleted > 0))
                {
                    await work.DB.Database.ExecuteSqlCommandAsync("VACUUM", _cancellationToken);
                }
            }
        }

        private async Task PopulateMessageAsync(Message targetMsg, MimeMessage sourceMsg, IEnumerable<string> keywords)
        {


            targetMsg.RfcMessageID = sourceMsg.ResentMessageId ?? sourceMsg.MessageId; // TODO: reverse these?
            targetMsg.Subject = sourceMsg.Subject;
            targetMsg.Date = sourceMsg.Date;
            targetMsg.From = new InternetAddressList(sourceMsg.From.Concat(sourceMsg.ResentFrom)).ToString();
            targetMsg.To = new InternetAddressList(sourceMsg.To.Concat(sourceMsg.ResentTo)).ToString();
            targetMsg.Cc = new InternetAddressList(sourceMsg.Cc.Concat(sourceMsg.ResentCc)).ToString();
            targetMsg.Bcc = new InternetAddressList(sourceMsg.Bcc.Concat(sourceMsg.ResentBcc)).ToString();
            targetMsg.Keywords = string.Join(", ", keywords);

            // TODO: only do this if we've downloaded more than just the headers
            // store text (if present) and ALSO extract text from HTML (if present); they might not contain the same text.
            TimerCollection.Global.Start("ExtractBodyText");
            try {
            targetMsg.Body = await ExtractBodyText(sourceMsg);
            } finally {
                TimerCollection.Global.Stop("ExtractBodyText");
            }

            if (sourceMsg.Attachments.Any())
            {
                targetMsg.AttachmentSummary = string.Join(Environment.NewLine,
                                                          sourceMsg.Attachments.Select((a, i) => $"{i + 1}. {(a is MimePart part ? part.FileName : null)} [{a.ContentType.MimeType}] " + a.Headers[HeaderId.ContentDescription]));
            }
            else
            {
                targetMsg.AttachmentSummary = null;
            }

            TimerCollection.Global.Start("ZipArchive");
            using (var zipStream = new MemoryStream())
            {
                using (var zip = new ZipArchive(zipStream, ZipArchiveMode.Update))
                {
                    string filename = CreateMessageFilename(sourceMsg);
                    var entry = zip.CreateEntry(filename, CompressionLevel.Optimal);
                    try
                    {
                        entry.LastWriteTime = sourceMsg.Date;
                    }
                    catch {

                        // ignore errors; just use today's date instead
                    }
                    using (var compressedStream = entry.Open())
                        await sourceMsg.WriteToAsync(compressedStream);
                }
                targetMsg.Original = zipStream.ToArray();
            }
            TimerCollection.Global.Stop("ZipArchive");
            // TODO: skip the above if we only have the headers










        } // PopulateMessageAsync

        private static string CreateMessageFilename(MimeMessage sourceMsg, string extension = ".eml")
        {
            string subject = MakeSafe(sourceMsg.Subject);
            InternetAddress sender = sourceMsg.From.FirstOrDefault();
            string senderName = MakeSafe(sender?.Name ?? sender?.ToString());

            string fileName = $"{subject}{(subject != null && senderName != null ? " - " : "")}{senderName}";

            // If there's nothing left, just use a default name
            if (fileName.Length <= 3)
                return "message" + extension;
            else if (fileName.Length > 250)
................................................................................
        private static readonly char[] _forbiddenChars = new char[] { '<', '>', '|', '?', '*', ':', '/', '\\', '"' };
        private static readonly UnicodeCategory[] _spacingCategories = new UnicodeCategory[]
        {
            UnicodeCategory.LineSeparator,
            UnicodeCategory.ParagraphSeparator,
            UnicodeCategory.SpaceSeparator,
        };
        private static string MakeSafe(string text)
        {
            if (string.IsNullOrWhiteSpace(text))
                return null;

            // First, trim spaces and strip all diacritics; replace all spacing by a normal space
            char[] chars = text.Trim().Normalize(NormalizationForm.FormD)
                .Where(c => CharUnicodeInfo.GetUnicodeCategory(c) != UnicodeCategory.NonSpacingMark)
................................................................................

            // Finally remove all forbidden characters from the file name (including '?')
            return new string(chars.Where(c => c >= ' ' && !_forbiddenChars.Contains(c)).ToArray())
                .Normalize(NormalizationForm.FormC)
                .Trim();
        }

        private async Task<string> ExtractBodyText(MimeMessage sourceMsg)
        {
            string bodyText = sourceMsg.TextBody?.Trim();
            if (sourceMsg.HtmlBody != null)
            {
                if (bodyText != null)
                    bodyText += Environment.NewLine + Environment.NewLine;
                else
                    bodyText = "";
                var errors = new List<Exception>();
................................................................................
                                throw errList.First();
                            }
                            else
                            {
                                throw new AggregateException(errList);
                            }
                        }
                        bodyText += result.Code.Trim();
                        return bodyText;
                    }
                    catch (Exception ex)
                    {
                        errors.Add(ex);
                    }
                    try
                    {
                        var html = sourceMsg.HtmlBody;
                        // This HtmlConverter is known to sometimes get stuck in an infinite loop, so wait for a finite amount of time
                        using(var timeoutTokenSource = new CancellationTokenSource())
                        {
                            var task = Task.Run(() => new ReverseMarkdown.Converter(_htmlConverterConfig).Convert(html).TrimEnd(), timeoutTokenSource.Token);
                            if (task.Wait(300000))
                            {
                                task.Dispose();
                            }
                            else
                            {
                                timeoutTokenSource.Cancel();
................................................................................
                                using (var writer = new StreamWriter(fs, Encoding.UTF8))
                                    await writer.WriteAsync(sourceMsg.HtmlBody);
                                File.SetLastWriteTimeUtc(tempFileName, sourceMsg.Date.UtcDateTime);
                                // TODO: remove above temp file saving

                                _ = task.ConfigureAwait(false);
                                task = task.ContinueWith((completedTask, data) => {

                                    var (gaveupAt, newFileName) = ((DateTime, string))data;
                                    Con.WriteLineErr($"\nHTML conversion finished, but at {DateTime.Now:HH:mm:ss}; we stopped waiting at {gaveupAt:HH:mm:ss}.", ConsoleColor.Cyan);
                                    var markDown = completedTask.Result;
                                    using (var writer = new StreamWriter(newFileName, false, Encoding.UTF8))
                                        writer.Write(markDown);
                                    return (string)null;
                                }, (DateTime.Now, Path.ChangeExtension(tempFileName, ".md")));
                                throw new TimeoutException("Timeout while converting HTML to markdown.");
                            }
                            bodyText += task.Result;
                        }
                        return bodyText;
                    }
                    catch (Exception ex)
................................................................................
                }
            }
            return bodyText;
        }

        private async Task PopulateParticipantsAsync(UnitOfWork work, Message targetMsg, MimeMessage sourceMsg)
        {


            var participantList = new List<(ParticipantField Field, string Address, string Name)>();
            GatherParticipants(ref participantList, sourceMsg.From, ParticipantField.From);
            GatherParticipants(ref participantList, sourceMsg.To, ParticipantField.To);
            GatherParticipants(ref participantList, sourceMsg.Cc, ParticipantField.Cc);
            GatherParticipants(ref participantList, sourceMsg.Bcc, ParticipantField.Bcc);
            GatherParticipants(ref participantList, sourceMsg.ReplyTo, ParticipantField.ReplyTo);
            GatherParticipants(ref participantList, sourceMsg.Sender, ParticipantField.Sender);
            GatherParticipants(ref participantList, sourceMsg.ResentFrom, ParticipantField.ResentFrom);
            GatherParticipants(ref participantList, sourceMsg.ResentTo, ParticipantField.ResentTo);
            GatherParticipants(ref participantList, sourceMsg.ResentCc, ParticipantField.ResentCc);
            GatherParticipants(ref participantList, sourceMsg.ResentBcc, ParticipantField.ResentBcc);
            GatherParticipants(ref participantList, sourceMsg.ResentReplyTo, ParticipantField.ResentReplyTo);
            GatherParticipants(ref participantList, sourceMsg.ResentSender, ParticipantField.ResentSender);

            int order = 0;
            foreach (var (field, mailAddress, mailName) in participantList)
            {
                string address = mailAddress == "" ? null : mailAddress;
                string name = mailName == "" ? null : mailName;
                if (name != null && name.Trim(' ', '\'', '"', '<', '>', '(', ')') == address)
                    name = null;

                var msgParticipant = new MessageParticipant
                {
                    Message = targetMsg,
                    Field = field,
                    Order = ++order,
                    Name = name,
                };

                var key = address ?? name;
                if (_participants.TryGetValue(key, out long participantID))
                {
                    msgParticipant.ParticipantID = participantID;
                    /* TODO: when cleaning up the database? Put the most used name for this address in the participant.
                    UPDATE Participants p
                       SET Name = (SELECT mp.Name
                                     FROM MessageParticipants mp
                                    WHERE ParticipantID = p.ID
                                      AND mp.Name IS NOT NULL
                                 GROUP BY mp.Name
                                 ORDER BY count(*) DESC
                                 ,        ID ASC
                                    LIMIT 1)
                     WHERE p.ID IN ({string.Join(', ', participantIDs)});
                    */
                }
                else
                {
                    var participant = new Participant
                    {
                        Address = address,
                        Name = name,
                    };
                    await work.Participants.AddAsync(participant);
                    // We only know the IDs after saving the entities
                    await work.SaveChangesAsync();
                    _participants.TryAdd(key, participant.ID);

                    msgParticipant.ParticipantID = participant.ID;
                }

                await work.DB.MessageParticipants.AddAsync(msgParticipant, _cancellationToken);
            }
            await work.SaveChangesAsync();










        } // PopulateParticipantsAsync

        private async Task PopulateKeywordsAsync(UnitOfWork work, Message targetMsg,
                                                 IEnumerable<string> keywords, IEnumerable<string> labels)
        {


            // Store the keywords separately, and link them to this message
            var allKeywords = keywords.Select(k => (Name: k, IsLabel: false))
                        .Concat(labels.Select(l => (Name: l, IsLabel: true)));
            var triplets = allKeywords.Select(mailKeyword => (Name: mailKeyword.Name,
                                                              IsLabel: mailKeyword.IsLabel,
                                                              ID: _keywords.GetValueOrDefault(mailKeyword, 0)));
            var keywordSets = from triplet in triplets
                              join dbMsgKeyword in work.DB.MessageKeywords.Where(mk => mk.Message == targetMsg)
                                   on triplet.ID equals dbMsgKeyword.KeywordID
                                   into msgKeywordSet
                              from dbMsgKeyword in msgKeywordSet.DefaultIfEmpty()
                              select (triplet.Name,
                                      triplet.IsLabel,
                                      msgKeywordID: dbMsgKeyword == null ? (long?)null : dbMsgKeyword.ID,
                                      keywordID: triplet.ID);
            int index = 0;
            foreach ((string mailKeyword, bool isLabel, long? msgKeywordID, long kwID) in keywordSets)
            {
                index++;
                if (msgKeywordID == null)
                {
                    long keywordID = kwID;
                    if (keywordID == 0)
                    {
                        var keyword = new Keyword
                        {
                            Name = mailKeyword,
                            IsLabel = isLabel
                        };
                        await work.Keywords.AddAsync(keyword);
                        await work.SaveChangesAsync();
                        keywordID = keyword.ID;
                        _keywords.Add((mailKeyword, isLabel), keywordID);
                    }
                    await work.DB.MessageKeywords.AddAsync(new MessageKeyword
                    {
                        Message = targetMsg,
                        Order = index,
                        KeywordID = keywordID,
                    }, _cancellationToken);
                }










            }
        }

        /// <summary>
        /// Store any references between messages.
        /// </summary>
        /// <param name="work">The <see cref="UnitOfWork"/> to use when storing the references.</param>
        /// <param name="targetMsg">The (newly stored) <see cref="Message"/> that should link to the references.</param>
        /// <param name="sourceMsg">The <see cref="MimeMessage"/> containing the references.</param>
        private async Task PopulateReferencesAsync(UnitOfWork work, Message targetMsg, MimeMessage sourceMsg)
        {


            // Include the In-Reply-To header in the list of references
            var sourceReferences = new List<string>(sourceMsg.References);
            if (!string.IsNullOrWhiteSpace(sourceMsg.InReplyTo) && !sourceReferences.Contains(sourceMsg.InReplyTo))
                sourceReferences.Insert(0, sourceMsg.InReplyTo);

            var dbMessageRefs = await work.MessageReferences
                .GetDictionaryAsync(mr => mr.Message == targetMsg,
                                    mr => mr.ReferencedRfcMessageID,
                                    mr => mr);
            var dbReferencedMessageIDs = work.Messages
                .GetLookup(m => sourceReferences.Contains(m.RfcMessageID),
                           m => m.RfcMessageID,
                           m => m?.ID); // make this a nullable long, so FirstOrDefault will return a null.
            int index = 0;
            foreach (string mailRef in sourceReferences)
            {
                index++;
                MessageReference dbReference = dbMessageRefs.GetValueOrDefault(mailRef, null);
                long? dbMessageID = null;
                if (dbReferencedMessageIDs.Contains(mailRef))
                {
                    dbMessageID = dbReferencedMessageIDs[mailRef].FirstOrDefault(id => id.HasValue);
                }
                if (dbReference == null)
                {
                    await work.MessageReferences.AddAsync(new MessageReference
                    {
                        Message = targetMsg,
                        ReferencedRfcMessageID = mailRef,
                        InReplyTo = mailRef == sourceMsg.InReplyTo,
                        Order = index,
                        ReferencedMessageID = dbMessageID,
                    });
                }
                else
                {
                    dbReference.Order = index;
                    dbReference.ReferencedMessageID = dbMessageID;
                    dbMessageRefs.Remove(mailRef);
                }
            }

            // Any remaining references are no longer in use, so delete them
            work.MessageReferences.Delete(dbMessageRefs.Values);

            // Find any existing references to targetMsg, and fill them in
            if (targetMsg.RfcMessageID != null)
            {
                var previousReferences = work.MessageReferences
                    .GetList(mr => mr.ReferencedRfcMessageID == targetMsg.RfcMessageID && mr.Message == null);
                foreach (var dbReference in previousReferences)
                {
                    dbReference.Message = targetMsg;
                }
            }

            await work.SaveChangesAsync();










        } // PopulateReferencesAsync

        private static void GatherParticipants(ref List<(ParticipantField Field, string Address, string Name)> participantList, InternetAddress ia, ParticipantField field)
        {
            if (ia == null) return;
            var list = new InternetAddressList(new InternetAddress[] { ia });
            GatherParticipants(ref participantList, list, field);
        }
        private static void GatherParticipants(ref List<(ParticipantField Field, string Address, string Name)> participantList, InternetAddressList list, ParticipantField field)
        {
            foreach (var ia in list)
            {
                if (ia is GroupAddress group)
                {
                    if (group.Members.Count > 0)
                    {
                        participantList.AddRange(group.Members
                                                 .Select(ma => ma as MailboxAddress)
                                                 .Select(ma => (field, ma.Address, ma.Name)));
                    }
                    else
                    {
                        participantList.Add((field, null, group.Name));
                    }
                }
                else if (ia is MailboxAddress mailbox)
................................................................................
                    participantList.Add((field, null, ia.Name));
                }
            }
        }

        private class KeywordEqualityComparer : IEqualityComparer<(string Name, bool IsLabel)>
        {
            private static readonly KeywordEqualityComparer _default = new KeywordEqualityComparer();
            public static KeywordEqualityComparer InvariantCultureIgnoreCase { get => _default; }

            public bool Equals((string Name, bool IsLabel) x, (string Name, bool IsLabel) y)
            {
                return x.IsLabel == y.IsLabel
                    && x.Name.Equals(y.Name, StringComparison.InvariantCultureIgnoreCase);
            }

            public int GetHashCode((string Name, bool IsLabel) obj)
            {
                return obj.Name.GetHashCode() ^ obj.IsLabel.GetHashCode();
            }
        }

    }
}







|
|












|


>
>
>
>
>




|







 







|







 







|
<
|
|
|
|
|
|
|

|
|
|
<







 







|







 







|
<
|
|
|
|
|
|
|
|
|
|
|
|
|

|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|

|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
<







 







|
|
<
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
<
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
<
<





|
<
|

|
|
|

|
|
|

|
|
|
<





>
>
|
|
|
|
|
|
|
|

|
|
|
|
|
|
|
|

|
|
|
|
|
|
|
|
|

|
|
<








|
>


|
|


<
|
|
>
>
>
>
>
>
>
>
>
>




|

|







 







|







 







|

|







 







|












|







 







>





|
|







 







>
>
|
|
|
|
|
|
|
|
|
|
|
|
|

|
|
|
|
|
|
|

|
|
|
|
|
|
|

|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|

|
|

|
|
|
>
>
>
>
>
>
>
>
>
>





>
>
|
|
|
|
<
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
>
>
>
>
>
>
>
>
>
>









|

>
>
|
|
|
|

|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|

|
|

|
|
|
|
|
|
|
|
|
|

|
>
>
>
>
>
>
>
>
>
>


|





|









|







 







<
|









|





25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
...
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
...
132
133
134
135
136
137
138
139

140
141
142
143
144
145
146
147
148
149
150

151
152
153
154
155
156
157
...
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
...
269
270
271
272
273
274
275
276

277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375

376
377
378
379
380
381
382
...
388
389
390
391
392
393
394
395
396

397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414

415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430


431
432
433
434
435
436

437
438
439
440
441
442
443
444
445
446
447
448
449

450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486

487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502

503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
...
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
...
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
...
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
...
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
...
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748

749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
...
906
907
908
909
910
911
912

913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
        {
            GithubFlavored = true,
            RemoveComments = false,
            SmartHrefHandling = true,
            TableWithoutHeaderRowHandling = ReverseMarkdown.Config.TableWithoutHeaderRowHandlingOption.Default,
            UnknownTags = ReverseMarkdown.Config.UnknownTagsOption.Bypass,
        };
        private Dictionary<(string, bool), long> _keywords = new Dictionary<(string, bool), long>();
        private Dictionary<string, long> _participants = new Dictionary<string, long>();
        private int _totalCountAllFolders;
        private int _countAllFolders;
        private DateTime _startAccount;
        private bool _haveDeleted = false;


        public MailSynchronizer(CancellationToken cancellationToken)
        {
            _cancellationToken = cancellationToken;
        }

        public async Task SynchronizeAccount(string host, short port, NetworkCredential credentials,
                                             Func<IMailFolder,bool>? folderFilter = null,
                                             bool vacuumDatabase = false)
        {
            if (credentials is null)
            {
                throw new ArgumentNullException(nameof(credentials));
            }

            Account account;
            using (var work = new UnitOfWork(_cancellationToken))
            {
                account = await work.Accounts.GetOrCreateAsync(host, port, credentials.UserName);
                _participants = await work.Participants.GetDictionaryAsync(p => p.Address ?? p.Name ?? "", p => p.ID, StringComparer.InvariantCultureIgnoreCase);
                _keywords = await work.Keywords.GetDictionaryAsync(kw => (kw.Name, kw.IsLabel), kw => kw.ID, KeywordEqualityComparer.InvariantCultureIgnoreCase);
            }

            using (var client = new MailKit.Net.Imap.ImapClient())
            {
                Con.WriteLine("Connecting to server...", TraceLevel.Verbose);
                await client.ConnectAsync(host, port, true, _cancellationToken);
................................................................................
                            _totalCountAllFolders -= mailFolder.Count;
                            continue;
                        }
                    }

                    TimerCollection.Global.Start("SynchronizeFolder");
                    try {
                        await SynchronizeFolder(mailFolder, dbFolder);
                    } finally {
                        TimerCollection.Global.Stop("SynchronizeFolder");
                    }
                    //Con.WriteLine(); Con.WriteLine(TimerCollection.Global.ToString(), System.Diagnostics.TraceLevel.Verbose);

                    if (mailFolder.UidNext != null)
                    {
................................................................................
                        }
                    }
                } // foreach folder

                // If we have remaining folders that aren't on the server anymore, delete them
                if (remainingFolderIDs.Any())
                {
                    using var work = new UnitOfWork(_cancellationToken);

                    // First verify that they don't have any child folders; otherwise we'd be deleting those too...
                    remainingFolderIDs = work.Folders.Select(f => remainingFolderIDs.Contains(f.ID) && !f.Children.Any(),
                                                             f => f.ID).ToList();
                    if (remainingFolderIDs.Any())
                    {
                        Con.WriteLine("Deleting local folder(s):");
                        Con.WriteLine("- " + string.Join("\n- ", await work.Folders.GetFullNamesAsync(remainingFolderIDs)));

                        work.Folders.DeleteByIDs(remainingFolderIDs);
                        await work.SaveChangesAsync();
                        _haveDeleted = true;

                    }
                }
            }

            TimerCollection.Global.Start("ClearUnunsedObjects");
            try
            {
................................................................................
                            Con.Write("; ETA " + (_startAccount.ToLocalTime() + TimeSpan.FromTicks(elapsed.Ticks * _totalCountAllFolders / _countAllFolders)).ToString("s").Replace('T', ' '));
                        }
                        lastProgressUpdate = DateTime.UtcNow;
                    }

                    TimerCollection.Global.Start("SynchronizeMessage");
                    try {
                        await SynchronizeMessage(summary, mailFolder, dbFolder.ID, folderMsgID, messageID);
                    } finally {
                        TimerCollection.Global.Stop("SynchronizeMessage");
                    }

                } // foreach (summary)
            }
            finally
................................................................................
                }
            }
        }

        private async Task SynchronizeMessage(IMessageSummary summary, IMailFolder mailFolder,
                                              long folderID, long? folderMsgID, long? messageID)
        {
            using var work = new UnitOfWork(_cancellationToken);

            try
            {
                // Deleted message: don't download it; delete it from the database instead
                if (summary.Flags.Value.HasFlag(MessageFlags.Deleted))
                {
                    if (folderMsgID != null)
                    {
                        work.FolderMessages.DeleteByID(folderMsgID.Value);
                        await work.SaveChangesAsync();
                        _haveDeleted = true;
                    }
                    return;
                }

                // Otherwise, check out the existing message
                Message? dbMessage = null;
                if (messageID != null)
                {
                    dbMessage = await work.Messages.GetByIdAsync(messageID.Value);
                }
                FolderMessage? folderMessage = null;
                if (folderMsgID == null
                    && dbMessage != null
                    && summary.Flags == dbMessage.Flags
                    && summary.InternalDate == dbMessage.DateReceived
                    && string.Join(", ", summary.Keywords) == dbMessage.Keywords)
                {
                    folderMessage = new FolderMessage
                    {
                        FolderID = folderID,
                        UID = summary.UniqueId.Id,
                        Message = dbMessage,
                    };
                    await work.FolderMessages.AddAsync(folderMessage);
                }
                else if (dbMessage == null
                        || summary.Flags != dbMessage.Flags
                        || summary.InternalDate != dbMessage.DateReceived
                        || string.Join(", ", summary.Keywords) != dbMessage.Keywords)
                {
                    MimeMessage mailMessage = await GetMailMessageAsync(summary, mailFolder);
                    // save message to database
                    if (folderMsgID != null)
                    {
                        folderMessage = await work.DB.FolderMessages
                            .Where(fm => fm.ID == folderMsgID)
                            .Include(fm => fm.Message)
                            .ThenInclude(m => m.Participants)
                            .SingleOrDefaultAsync(_cancellationToken);
                    }
                    if (folderMessage == null)
                    {
                        folderMessage = new FolderMessage
                        {
                            FolderID = folderID,
                            UID = summary.UniqueId.Id,
                            Message = dbMessage ?? new Message
                            {
                                Participants = new List<MessageParticipant>(),
                            },
                        };
                        await work.FolderMessages.AddAsync(folderMessage);
                    }
                    dbMessage = folderMessage.Message;
                    dbMessage.GlobalID = summary.GMailMessageId;
                    dbMessage.DateRetrievedUTC = DateTime.UtcNow;
                    dbMessage.Flags = summary.Flags ?? MessageFlags.None;
                    dbMessage.DateReceived = summary.InternalDate;
                    TimerCollection.Global.Start("PopulateMessageAsync");
                    try
                    {
                        await PopulateMessageAsync(dbMessage, mailMessage, summary.Keywords);
                    }
                    finally
                    {
                        TimerCollection.Global.Stop("PopulateMessageAsync");
                    }

                    // Store all separate entities contained in the mail message
                    TimerCollection.Global.Start("PopulateParticipants");
                    await PopulateParticipantsAsync(work, dbMessage, mailMessage);    // from headers
                    TimerCollection.Global.Stop("PopulateParticipants");
                    TimerCollection.Global.Start("PopulateKeywords");
                    await PopulateKeywordsAsync(work, dbMessage, summary.Keywords, summary.GMailLabels); // from summary
                    TimerCollection.Global.Stop("PopulateKeywords");
                    TimerCollection.Global.Start("PopulateReferences");
                    await PopulateReferencesAsync(work, dbMessage, mailMessage);      // from summary + headers
                    TimerCollection.Global.Stop("PopulateReferences");
                }
                await work.SaveChangesAsync();
            }
            catch (Exception ex)
            {
                if (ex is TaskCanceledException || ex is OperationCanceledException || ex is ServiceNotConnectedException)
                {
                    throw; // those need to fall through
                }
                await Con.WriteLineAsync();
                await Con.WriteLineAsync(ex);

            }
        }

        private async Task<MimeMessage> GetMailMessageAsync(IMessageSummary summary, IMailFolder mailFolder)
        {
            MimeMessage mailMessage;
            try
................................................................................
                    TimerCollection.Global.Stop("mailFolder.GetMessageAsync");
                }
            }
            catch (FormatException)
            {
                var mailEncoding = Encoding.GetEncoding("ISO-8859-1");
                // Upon failure to parse the message, try to download the entire message stream
                using Stream mailStream = await mailFolder.GetStreamAsync(summary.UniqueId, 0, (int)summary.Size.Value, _cancellationToken);
                using StreamReader reader = new StreamReader(mailStream, mailEncoding, true);

                using (var fs = new FileStream($"{summary.GMailMessageId}.eml", FileMode.Create, FileAccess.ReadWrite, FileShare.Read))
                {
                    await mailStream.CopyToAsync(fs, _cancellationToken);
                    mailStream.Position = 0;
                }
                // Now, attempt to fix the mail message, by skipping lines until one doesn't start with a spacing character
                var sbSource = new StringBuilder((int)mailStream.Length);
                while (!reader.EndOfStream)
                {
                    string line = await reader.ReadLineAsync() ?? "";
                    if (line.Length == 0 || !char.IsWhiteSpace(line[0]))
                    {
                        sbSource.Append(line);
                        sbSource.Append("\r\n");
                        sbSource.Append(await reader.ReadToEndAsync());
                    }
                }
                using var fixedStream = new MemoryStream((int)mailStream.Length);

                await fixedStream.WriteAsync(reader.CurrentEncoding.GetBytes(sbSource.ToString()), _cancellationToken);
                fixedStream.Position = 0;
                var options = new ParserOptions
                {
                    AddressParserComplianceMode = RfcComplianceMode.Loose,
                    AllowAddressesWithoutDomain = true,
                    AllowUnquotedCommasInAddresses = false,
                    CharsetEncoding = mailEncoding,
                    MaxAddressGroupDepth = 3,
                    ParameterComplianceMode = RfcComplianceMode.Loose,
                    RespectContentLength = true,
                    Rfc2047ComplianceMode = RfcComplianceMode.Loose,
                };
                mailMessage = await MimeMessage.LoadAsync(options, fixedStream, _cancellationToken);
            }



            return mailMessage;
        }

        public async Task ClearUnunsedObjectsAsync(bool vacuumDatabase = false)
        {
            using var work = new UnitOfWork(_cancellationToken);

            await Con.WriteLineAsync("Cleaning up database...", TraceLevel.Verbose);

            // Delete any messages not linked to a folder
            work.Messages.DeleteByIDs(m => !m.Folders.Any(), m => m.ID);
            int numDeleted = await work.SaveChangesAsync();

            // Delete any participants not linked to a message
            work.Participants.DeleteByIDs(p => !p.Messages.Any(), p => p.ID);
            numDeleted += await work.SaveChangesAsync();

            if (vacuumDatabase && (_haveDeleted || numDeleted > 0))
            {
                await work.DB.Database.ExecuteSqlRawAsync("VACUUM", _cancellationToken);

            }
        }

        private async Task PopulateMessageAsync(Message targetMsg, MimeMessage sourceMsg, IEnumerable<string> keywords)
        {
            try
            {
                targetMsg.RfcMessageID = sourceMsg.ResentMessageId ?? sourceMsg.MessageId; // TODO: reverse these?
                targetMsg.Subject = sourceMsg.Subject;
                targetMsg.Date = sourceMsg.Date;
                targetMsg.From = new InternetAddressList(sourceMsg.From.Concat(sourceMsg.ResentFrom)).ToString();
                targetMsg.To = new InternetAddressList(sourceMsg.To.Concat(sourceMsg.ResentTo)).ToString();
                targetMsg.Cc = new InternetAddressList(sourceMsg.Cc.Concat(sourceMsg.ResentCc)).ToString();
                targetMsg.Bcc = new InternetAddressList(sourceMsg.Bcc.Concat(sourceMsg.ResentBcc)).ToString();
                targetMsg.Keywords = string.Join(", ", keywords);

                // TODO: only do this if we've downloaded more than just the headers
                // store text (if present) and ALSO extract text from HTML (if present); they might not contain the same text.
                TimerCollection.Global.Start("ExtractBodyText");
                try {
                    targetMsg.Body = await ExtractBodyText(sourceMsg);
                } finally {
                    TimerCollection.Global.Stop("ExtractBodyText");
                }

                if (sourceMsg.Attachments.Any())
                {
                    targetMsg.AttachmentSummary = string.Join(Environment.NewLine,
                                                              sourceMsg.Attachments.Select((a, i) => $"{i + 1}. {(a is MimePart part ? part.FileName : null)} [{a.ContentType.MimeType}] " + a.Headers[HeaderId.ContentDescription]));
                }
                else
                {
                    targetMsg.AttachmentSummary = null;
                }

                TimerCollection.Global.Start("ZipArchive");
                using var zipStream = new MemoryStream();

                using (var zip = new ZipArchive(zipStream, ZipArchiveMode.Update))
                {
                    string filename = CreateMessageFilename(sourceMsg);
                    var entry = zip.CreateEntry(filename, CompressionLevel.Optimal);
                    try
                    {
                        entry.LastWriteTime = sourceMsg.Date;
                    }
                    catch
                    {
                        // ignore errors; just use today's date instead
                    }
                    using var compressedStream = entry.Open();
                    await sourceMsg.WriteToAsync(compressedStream);
                }
                targetMsg.Original = zipStream.ToArray();

                TimerCollection.Global.Stop("ZipArchive");
                // TODO: skip the above if we only have the headers
            }
            catch (Exception ex)
            {
                if (ex is TaskCanceledException || ex is OperationCanceledException || ex is ServiceNotConnectedException)
                {
                    throw; // those need to fall through
                }
                await Con.WriteLineAsync();
                await Con.WriteLineAsync(ex);
            }
        } // PopulateMessageAsync

        private static string CreateMessageFilename(MimeMessage sourceMsg, string extension = ".eml")
        {
            string? subject = MakeSafe(sourceMsg.Subject);
            InternetAddress sender = sourceMsg.From.FirstOrDefault();
            string? senderName = MakeSafe(sender?.Name ?? sender?.ToString());

            string fileName = $"{subject}{(subject != null && senderName != null ? " - " : "")}{senderName}";

            // If there's nothing left, just use a default name
            if (fileName.Length <= 3)
                return "message" + extension;
            else if (fileName.Length > 250)
................................................................................
        private static readonly char[] _forbiddenChars = new char[] { '<', '>', '|', '?', '*', ':', '/', '\\', '"' };
        private static readonly UnicodeCategory[] _spacingCategories = new UnicodeCategory[]
        {
            UnicodeCategory.LineSeparator,
            UnicodeCategory.ParagraphSeparator,
            UnicodeCategory.SpaceSeparator,
        };
        private static string? MakeSafe(string? text)
        {
            if (string.IsNullOrWhiteSpace(text))
                return null;

            // First, trim spaces and strip all diacritics; replace all spacing by a normal space
            char[] chars = text.Trim().Normalize(NormalizationForm.FormD)
                .Where(c => CharUnicodeInfo.GetUnicodeCategory(c) != UnicodeCategory.NonSpacingMark)
................................................................................

            // Finally remove all forbidden characters from the file name (including '?')
            return new string(chars.Where(c => c >= ' ' && !_forbiddenChars.Contains(c)).ToArray())
                .Normalize(NormalizationForm.FormC)
                .Trim();
        }

        private async Task<string?> ExtractBodyText(MimeMessage sourceMsg)
        {
            string? bodyText = sourceMsg.TextBody?.Trim();
            if (sourceMsg.HtmlBody != null)
            {
                if (bodyText != null)
                    bodyText += Environment.NewLine + Environment.NewLine;
                else
                    bodyText = "";
                var errors = new List<Exception>();
................................................................................
                                throw errList.First();
                            }
                            else
                            {
                                throw new AggregateException(errList);
                            }
                        }
                        bodyText += result.Code?.Trim();
                        return bodyText;
                    }
                    catch (Exception ex)
                    {
                        errors.Add(ex);
                    }
                    try
                    {
                        var html = sourceMsg.HtmlBody;
                        // This HtmlConverter is known to sometimes get stuck in an infinite loop, so wait for a finite amount of time
                        using(var timeoutTokenSource = new CancellationTokenSource())
                        {
                            Task<string?> task = Task.Run(() => new ReverseMarkdown.Converter(_htmlConverterConfig).Convert(html)?.TrimEnd(), timeoutTokenSource.Token);
                            if (task.Wait(300000))
                            {
                                task.Dispose();
                            }
                            else
                            {
                                timeoutTokenSource.Cancel();
................................................................................
                                using (var writer = new StreamWriter(fs, Encoding.UTF8))
                                    await writer.WriteAsync(sourceMsg.HtmlBody);
                                File.SetLastWriteTimeUtc(tempFileName, sourceMsg.Date.UtcDateTime);
                                // TODO: remove above temp file saving

                                _ = task.ConfigureAwait(false);
                                task = task.ContinueWith((completedTask, data) => {
                                    if (data is null) throw new ArgumentNullException(nameof(data));
                                    var (gaveupAt, newFileName) = ((DateTime, string))data;
                                    Con.WriteLineErr($"\nHTML conversion finished, but at {DateTime.Now:HH:mm:ss}; we stopped waiting at {gaveupAt:HH:mm:ss}.", ConsoleColor.Cyan);
                                    var markDown = completedTask.Result;
                                    using (var writer = new StreamWriter(newFileName, false, Encoding.UTF8))
                                        writer.Write(markDown);
                                    return (string?)null;
                                }, (DateTime.Now, Path.ChangeExtension(tempFileName, ".md")), TaskScheduler.Current);
                                throw new TimeoutException("Timeout while converting HTML to markdown.");
                            }
                            bodyText += task.Result;
                        }
                        return bodyText;
                    }
                    catch (Exception ex)
................................................................................
                }
            }
            return bodyText;
        }

        private async Task PopulateParticipantsAsync(UnitOfWork work, Message targetMsg, MimeMessage sourceMsg)
        {
            try
            {
                var participantList = new List<(ParticipantField Field, string? Address, string Name)>();
                GatherParticipants(ref participantList, sourceMsg.From, ParticipantField.From);
                GatherParticipants(ref participantList, sourceMsg.To, ParticipantField.To);
                GatherParticipants(ref participantList, sourceMsg.Cc, ParticipantField.Cc);
                GatherParticipants(ref participantList, sourceMsg.Bcc, ParticipantField.Bcc);
                GatherParticipants(ref participantList, sourceMsg.ReplyTo, ParticipantField.ReplyTo);
                GatherParticipants(ref participantList, sourceMsg.Sender, ParticipantField.Sender);
                GatherParticipants(ref participantList, sourceMsg.ResentFrom, ParticipantField.ResentFrom);
                GatherParticipants(ref participantList, sourceMsg.ResentTo, ParticipantField.ResentTo);
                GatherParticipants(ref participantList, sourceMsg.ResentCc, ParticipantField.ResentCc);
                GatherParticipants(ref participantList, sourceMsg.ResentBcc, ParticipantField.ResentBcc);
                GatherParticipants(ref participantList, sourceMsg.ResentReplyTo, ParticipantField.ResentReplyTo);
                GatherParticipants(ref participantList, sourceMsg.ResentSender, ParticipantField.ResentSender);

                int order = 0;
                foreach (var (field, mailAddress, mailName) in participantList)
                {
                    string? address = mailAddress == "" ? null : mailAddress;
                    string? name = mailName == "" ? null : mailName;
                    if (name != null && name.Trim(' ', '\'', '"', '<', '>', '(', ')') == address)
                        name = null;

                    var msgParticipant = new MessageParticipant
                    {
                        Message = targetMsg,
                        Field = field,
                        Order = ++order,
                        Name = name,
                    };

                    string key = address ?? name ?? "";
                    if (_participants.TryGetValue(key, out long participantID))
                    {
                        msgParticipant.ParticipantID = participantID;
                        /* TODO: when cleaning up the database? Put the most used name for this address in the participant.
                        UPDATE Participants p
                           SET Name = (SELECT mp.Name
                                         FROM MessageParticipants mp
                                        WHERE ParticipantID = p.ID
                                          AND mp.Name IS NOT NULL
                                     GROUP BY mp.Name
                                     ORDER BY count(*) DESC
                                     ,        ID ASC
                                        LIMIT 1)
                         WHERE p.ID IN ({string.Join(', ', participantIDs)});
                        */
                    }
                    else
                    {
                        var participant = new Participant
                        {
                            Address = address,
                            Name = name,
                        };
                        await work.Participants.AddAsync(participant);
                        // We only know the IDs after saving the entities
                        await work.SaveChangesAsync();
                        _participants.TryAdd(key, participant.ID);

                        msgParticipant.ParticipantID = participant.ID;
                    }

                    await work.DB.MessageParticipants.AddAsync(msgParticipant, _cancellationToken);
                }
                await work.SaveChangesAsync();
            }
            catch (Exception ex)
            {
                if (ex is TaskCanceledException || ex is OperationCanceledException || ex is ServiceNotConnectedException)
                {
                    throw; // those need to fall through
                }
                await Con.WriteLineAsync();
                await Con.WriteLineAsync(ex);
            }
        } // PopulateParticipantsAsync

        private async Task PopulateKeywordsAsync(UnitOfWork work, Message targetMsg,
                                                 IEnumerable<string> keywords, IEnumerable<string> labels)
        {
            try
            {
                // Store the keywords separately, and link them to this message
                var allKeywords = keywords.Select(k => (Name: k, IsLabel: false))
                            .Concat(labels.Select(l => (Name: l, IsLabel: true)));
                var triplets = allKeywords.Select(mailKeyword => (mailKeyword.Name, mailKeyword.IsLabel,

                                                                  ID: _keywords.GetValueOrDefault(mailKeyword, 0)));
                var keywordSets = from triplet in triplets
                                  join dbMsgKeyword in work.DB.MessageKeywords.Where(mk => mk.Message == targetMsg)
                                       on triplet.ID equals dbMsgKeyword.KeywordID
                                       into msgKeywordSet
                                  from dbMsgKeyword in msgKeywordSet.DefaultIfEmpty()
                                  select (triplet.Name,
                                          triplet.IsLabel,
                                          msgKeywordID: dbMsgKeyword == null ? (long?)null : dbMsgKeyword.ID,
                                          keywordID: triplet.ID);
                int index = 0;
                foreach ((string mailKeyword, bool isLabel, long? msgKeywordID, long kwID) in keywordSets)
                {
                    index++;
                    if (msgKeywordID == null)
                    {
                        long keywordID = kwID;
                        if (keywordID == 0)
                        {
                            var keyword = new Keyword
                            {
                                Name = mailKeyword,
                                IsLabel = isLabel
                            };
                            await work.Keywords.AddAsync(keyword);
                            await work.SaveChangesAsync();
                            keywordID = keyword.ID;
                            _keywords.Add((mailKeyword, isLabel), keywordID);
                        }
                        await work.DB.MessageKeywords.AddAsync(new MessageKeyword
                        {
                            Message = targetMsg,
                            Order = index,
                            KeywordID = keywordID,
                        }, _cancellationToken);
                    }
                }
            }
            catch (Exception ex)
            {
                if (ex is TaskCanceledException || ex is OperationCanceledException || ex is ServiceNotConnectedException)
                {
                    throw; // those need to fall through
                }
                await Con.WriteLineAsync();
                await Con.WriteLineAsync(ex);
            }
        }

        /// <summary>
        /// Store any references between messages.
        /// </summary>
        /// <param name="work">The <see cref="UnitOfWork"/> to use when storing the references.</param>
        /// <param name="targetMsg">The (newly stored) <see cref="Message"/> that should link to the references.</param>
        /// <param name="sourceMsg">The <see cref="MimeMessage"/> containing the references.</param>
        private static async Task PopulateReferencesAsync(UnitOfWork work, Message targetMsg, MimeMessage sourceMsg)
        {
            try
            {
                // Include the In-Reply-To header in the list of references
                var sourceReferences = new List<string>(sourceMsg.References);
                if (!string.IsNullOrWhiteSpace(sourceMsg.InReplyTo) && !sourceReferences.Contains(sourceMsg.InReplyTo))
                    sourceReferences.Insert(0, sourceMsg.InReplyTo);

                var dbMessageRefs = await work.MessageReferences
                    .GetDictionaryAsync(mr => mr.Message == targetMsg,
                                        mr => mr.ReferencedRfcMessageID,
                                        mr => mr);
                var dbReferencedMessageIDs = work.Messages
                    .GetLookup(m => sourceReferences.Contains(m.RfcMessageID ?? ""),
                               m => m.RfcMessageID,
                               m => m?.ID); // make this a nullable long, so FirstOrDefault will return a null.
                int index = 0;
                foreach (string mailRef in sourceReferences)
                {
                    index++;
                    MessageReference? dbReference = dbMessageRefs.GetValueOrDefault(mailRef, null);
                    long? dbMessageID = null;
                    if (dbReferencedMessageIDs.Contains(mailRef))
                    {
                        dbMessageID = dbReferencedMessageIDs[mailRef].FirstOrDefault(id => id.HasValue);
                    }
                    if (dbReference == null)
                    {
                        await work.MessageReferences.AddAsync(new MessageReference
                        {
                            Message = targetMsg,
                            ReferencedRfcMessageID = mailRef,
                            InReplyTo = mailRef == sourceMsg.InReplyTo,
                            Order = index,
                            ReferencedMessageID = dbMessageID,
                        });
                    }
                    else
                    {
                        dbReference.Order = index;
                        dbReference.ReferencedMessageID = dbMessageID;
                        dbMessageRefs.Remove(mailRef);
                    }
                }

                // Any remaining references are no longer in use, so delete them
                work.MessageReferences.Delete(dbMessageRefs.Values);

                // Find any existing references to targetMsg, and fill them in
                if (targetMsg.RfcMessageID != null)
                {
                    var previousReferences = work.MessageReferences
                        .GetList(mr => mr.ReferencedRfcMessageID == targetMsg.RfcMessageID && mr.Message == null);
                    foreach (var dbReference in previousReferences)
                    {
                        dbReference.Message = targetMsg;
                    }
                }

                await work.SaveChangesAsync();
            }
            catch (Exception ex)
            {
                if (ex is TaskCanceledException || ex is OperationCanceledException || ex is ServiceNotConnectedException)
                {
                    throw; // those need to fall through
                }
                await Con.WriteLineAsync();
                await Con.WriteLineAsync(ex);
            }
        } // PopulateReferencesAsync

        private static void GatherParticipants(ref List<(ParticipantField Field, string? Address, string Name)> participantList, InternetAddress ia, ParticipantField field)
        {
            if (ia == null) return;
            var list = new InternetAddressList(new InternetAddress[] { ia });
            GatherParticipants(ref participantList, list, field);
        }
        private static void GatherParticipants(ref List<(ParticipantField Field, string? Address, string Name)> participantList, InternetAddressList list, ParticipantField field)
        {
            foreach (var ia in list)
            {
                if (ia is GroupAddress group)
                {
                    if (group.Members.Count > 0)
                    {
                        participantList.AddRange(group.Members
                                                 .Select(ma => ma as MailboxAddress)
                                                 .Select(ma => (field, ma?.Address, ma?.Name ?? "")));
                    }
                    else
                    {
                        participantList.Add((field, null, group.Name));
                    }
                }
                else if (ia is MailboxAddress mailbox)
................................................................................
                    participantList.Add((field, null, ia.Name));
                }
            }
        }

        private class KeywordEqualityComparer : IEqualityComparer<(string Name, bool IsLabel)>
        {

            public static KeywordEqualityComparer InvariantCultureIgnoreCase { get; } = new KeywordEqualityComparer();

            public bool Equals((string Name, bool IsLabel) x, (string Name, bool IsLabel) y)
            {
                return x.IsLabel == y.IsLabel
                    && x.Name.Equals(y.Name, StringComparison.InvariantCultureIgnoreCase);
            }

            public int GetHashCode((string Name, bool IsLabel) obj)
            {
                return obj.Name.GetHashCode(StringComparison.InvariantCultureIgnoreCase) ^ obj.IsLabel.GetHashCode();
            }
        }

    }
}

Name change from Migrations/20190903195645_Initial.Designer.cs to Migrations/20190914062021_Initial.Designer.cs.

5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
...
192
193
194
195
196
197
198


199
200
201
202
203
204
205
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Migrations;
using Microsoft.EntityFrameworkCore.Storage.ValueConversion;

namespace MailJanitor.Migrations
{
    [DbContext(typeof(MailJanitorContext))]
    [Migration("20190903195645_Initial")]
    partial class Initial
    {
        protected override void BuildTargetModel(ModelBuilder modelBuilder)
        {
#pragma warning disable 612, 618
            modelBuilder
                .HasAnnotation("ProductVersion", "2.2.6-servicing-10079");
................................................................................
                {
                    b.Property<long>("ID")
                        .ValueGeneratedOnAdd();

                    b.Property<int>("Field");

                    b.Property<long>("MessageID");



                    b.Property<int>("Order");

                    b.Property<long>("ParticipantID");

                    b.HasKey("ID");








|







 







>
>







5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
...
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Migrations;
using Microsoft.EntityFrameworkCore.Storage.ValueConversion;

namespace MailJanitor.Migrations
{
    [DbContext(typeof(MailJanitorContext))]
    [Migration("20190914062021_Initial")]
    partial class Initial
    {
        protected override void BuildTargetModel(ModelBuilder modelBuilder)
        {
#pragma warning disable 612, 618
            modelBuilder
                .HasAnnotation("ProductVersion", "2.2.6-servicing-10079");
................................................................................
                {
                    b.Property<long>("ID")
                        .ValueGeneratedOnAdd();

                    b.Property<int>("Field");

                    b.Property<long>("MessageID");

                    b.Property<string>("Name");

                    b.Property<int>("Order");

                    b.Property<long>("ParticipantID");

                    b.HasKey("ID");

Name change from Migrations/20190903195645_Initial.cs to Migrations/20190914062021_Initial.cs.

178
179
180
181
182
183
184
185

186
187
188
189
190
191
192
                columns: table => new
                {
                    ID = table.Column<long>(nullable: false)
                        .Annotation("Sqlite:Autoincrement", true),
                    MessageID = table.Column<long>(nullable: false),
                    ParticipantID = table.Column<long>(nullable: false),
                    Field = table.Column<int>(nullable: false),
                    Order = table.Column<int>(nullable: false)

                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_MessageParticipants", x => x.ID);
                    table.ForeignKey(
                        name: "FK_MessageParticipants_Messages_MessageID",
                        column: x => x.MessageID,







|
>







178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
                columns: table => new
                {
                    ID = table.Column<long>(nullable: false)
                        .Annotation("Sqlite:Autoincrement", true),
                    MessageID = table.Column<long>(nullable: false),
                    ParticipantID = table.Column<long>(nullable: false),
                    Field = table.Column<int>(nullable: false),
                    Order = table.Column<int>(nullable: false),
                    Name = table.Column<string>(nullable: true)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_MessageParticipants", x => x.ID);
                    table.ForeignKey(
                        name: "FK_MessageParticipants_Messages_MessageID",
                        column: x => x.MessageID,

Changes to Migrations/MailJanitorContextModelSnapshot.cs.

190
191
192
193
194
195
196


197
198
199
200
201
202
203
                {
                    b.Property<long>("ID")
                        .ValueGeneratedOnAdd();

                    b.Property<int>("Field");

                    b.Property<long>("MessageID");



                    b.Property<int>("Order");

                    b.Property<long>("ParticipantID");

                    b.HasKey("ID");








>
>







190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
                {
                    b.Property<long>("ID")
                        .ValueGeneratedOnAdd();

                    b.Property<int>("Field");

                    b.Property<long>("MessageID");

                    b.Property<string>("Name");

                    b.Property<int>("Order");

                    b.Property<long>("ParticipantID");

                    b.HasKey("ID");

Changes to Models/Account.cs.

5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
..
27
28
29
30
31
32
33
34
35
36
using System.Text;

namespace MailJanitor.Models
{
    public class Account
    {
        public long ID { get; set; }
        [Required][Column(TypeName = "TEXT COLLATE NOCASE")]
        public string Host { get; set; }
        public short Port { get; set; }
        [Required][Column(TypeName = "TEXT COLLATE NOCASE")]
        public string UserName { get; set; }
        public long? InboxFolderID { get; set; }
        //public Folder InboxFolder { get; set; } TODO: restore these, and use annotations to fix migration
        public long? DraftsFolderID { get; set; }
        //public Folder DraftsFolder { get; set; }
        public long? SentFolderID { get; set; }
        //public Folder SentFolder { get; set; }
        public long? FlaggedFolderID { get; set; }
................................................................................
        public long? AllFolderID { get; set; }
        //public Folder AllFolder { get; set; }
        public long? TrashFolderID { get; set; }
        //public Folder TrashFolder { get; set; }
        public long? SpamFolderID { get; set; }
        //public Folder SpamFolder { get; set; }

        public ICollection<Folder> Folders { get; set; }
    }
}







|
|

|
|







 







|


5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
..
27
28
29
30
31
32
33
34
35
36
using System.Text;

namespace MailJanitor.Models
{
    public class Account
    {
        public long ID { get; set; }
        [Column(TypeName = "TEXT COLLATE NOCASE")]
        public string Host { get; set; } = "";
        public short Port { get; set; }
        [Column(TypeName = "TEXT COLLATE NOCASE")]
        public string UserName { get; set; } = "";
        public long? InboxFolderID { get; set; }
        //public Folder InboxFolder { get; set; } TODO: restore these, and use annotations to fix migration
        public long? DraftsFolderID { get; set; }
        //public Folder DraftsFolder { get; set; }
        public long? SentFolderID { get; set; }
        //public Folder SentFolder { get; set; }
        public long? FlaggedFolderID { get; set; }
................................................................................
        public long? AllFolderID { get; set; }
        //public Folder AllFolder { get; set; }
        public long? TrashFolderID { get; set; }
        //public Folder TrashFolder { get; set; }
        public long? SpamFolderID { get; set; }
        //public Folder SpamFolder { get; set; }

        public ICollection<Folder>? Folders { get; internal set; }
    }
}

Changes to Models/Folder.cs.

6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

namespace MailJanitor.Models
{
    public class Folder
    {
        public long ID { get; set; }
        public long AccountID { get; set; }
        public Account Account { get; set; }
        [Required]
        public string Name { get; set; }
        public uint UIDValidity { get; set; }
        public uint? UIDNext { get; set; }
        public FolderAttributes Attributes { get; set; }
        public long? ParentFolderID { get; set; }
        public Folder ParentFolder { get; set; }

        public ICollection<Folder> Children { get; set; }
        public ICollection<FolderMessage> Messages { get; set; }
    }
}







|
<
|




|

|
|


6
7
8
9
10
11
12
13

14
15
16
17
18
19
20
21
22
23
24

namespace MailJanitor.Models
{
    public class Folder
    {
        public long ID { get; set; }
        public long AccountID { get; set; }
        public Account? Account { get; set; }

        public string Name { get; set; } = "";
        public uint UIDValidity { get; set; }
        public uint? UIDNext { get; set; }
        public FolderAttributes Attributes { get; set; }
        public long? ParentFolderID { get; set; }
        public Folder? ParentFolder { get; set; }

        public ICollection<Folder>? Children { get; }
        public ICollection<FolderMessage>? Messages { get; }
    }
}

Changes to Models/FolderMessage.cs.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
namespace MailJanitor.Models
{
    public class FolderMessage
    {
        public long ID { get; set; }

        public long FolderID { get; set; }
        public Folder Folder { get; set; }
        public long MessageID { get; set; }
        public Message Message { get; set; }
        public uint UID { get; set; }
        [NotMapped]
        public UniqueId UniqueId
        {
            get => new UniqueId(Folder.UIDValidity, UID);
            set => UID = value.Id;
        }
    }
}







|

|

<
<
<
<
<
|
|
<
7
8
9
10
11
12
13
14
15
16
17





18
19

namespace MailJanitor.Models
{
    public class FolderMessage
    {
        public long ID { get; set; }

        public long FolderID { get; set; }
        public Folder? Folder { get; set; }
        public long MessageID { get; set; }
        public Message? Message { get; set; }
        public uint UID { get; set; }





    }
}

Changes to Models/Keyword.cs.

3
4
5
6
7
8
9
10
11
12

13
14
15
using System.ComponentModel.DataAnnotations.Schema;

namespace MailJanitor.Models
{
    public class Keyword
    {
        public long ID { get; set; }
        [Required][Column(TypeName = "TEXT COLLATE NOCASE")]
        public string Name { get; set; }
        public bool IsLabel { get; set; }

        public ICollection<MessageKeyword> Messages { get; set; }
    }
}







|
|

>
|


3
4
5
6
7
8
9
10
11
12
13
14
15
16
using System.ComponentModel.DataAnnotations.Schema;

namespace MailJanitor.Models
{
    public class Keyword
    {
        public long ID { get; set; }
        [Column(TypeName = "TEXT COLLATE NOCASE")]
        public string Name { get; set; } = "";
        public bool IsLabel { get; set; }

        public ICollection<MessageKeyword>? Messages { get; internal set; }
    }
}

Changes to Models/Message.cs.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
{
    public class Message
    {
        public long ID { get; set; }

        public MessageFlags Flags { get; set; }
        public ulong? GlobalID { get; set; }
        public string RfcMessageID { get; set; }

        public DateTimeOffset Date { get; set; }
        public DateTimeOffset? DateReceived { get; set; }
        public DateTime DateRetrievedUTC { get; set; } = DateTime.UtcNow;

        public string From { get; set; }
        public string To { get; set; }
        public string Cc { get; set; }
        public string Bcc { get; set; }

        public string Subject { get; set; }
        public string Body { get; set; }
        public string HTMLBody { get; set; }
        public string Keywords { get; set; }
        public string AttachmentSummary { get; set; }

        public byte[] Original { get; set; }
        public DownloadStatus DownloadStatus { get; set; }

        public ICollection<MessageParticipant> Participants { get; set; }
        public ICollection<FolderMessage> Folders { get; set; }
        public ICollection<MessageKeyword> KeywordList { get; set; }
        [InverseProperty("Message")]
        public ICollection<MessageReference> References { get; set; }
        [InverseProperty("ReferencedMessage")]
        public ICollection<MessageReference> ReferencedBy { get; set; }
    }

    public enum DownloadStatus
    {
        Nothing,
        Headers, // only the message headers have been downloaded
        Preview, // the message headers and the preview text has been downloaded
        Body, // the body text (and HTML if relevant) have been downloaded. The HTML has not been converted to text yet.
        FullText, // the text and HTML body is searchable as text. The attachments are searchable by their file name and description.
        Attachments, // the attachments have been converted to text (if possible) and are searchable.
    }
}







|





|
|
|
|

|
|
|
|
|

|


|
|
|

|

|












8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
{
    public class Message
    {
        public long ID { get; set; }

        public MessageFlags Flags { get; set; }
        public ulong? GlobalID { get; set; }
        public string? RfcMessageID { get; set; }

        public DateTimeOffset Date { get; set; }
        public DateTimeOffset? DateReceived { get; set; }
        public DateTime DateRetrievedUTC { get; set; } = DateTime.UtcNow;

        public string? From { get; set; }
        public string? To { get; set; }
        public string? Cc { get; set; }
        public string? Bcc { get; set; }

        public string? Subject { get; set; }
        public string? Body { get; set; }
        public string? HTMLBody { get; set; }
        public string? Keywords { get; set; }
        public string? AttachmentSummary { get; set; }

        public byte[]? Original { get; set; }
        public DownloadStatus DownloadStatus { get; set; }

        public ICollection<MessageParticipant>? Participants { get; internal set; }
        public ICollection<FolderMessage>? Folders { get; }
        public ICollection<MessageKeyword>? KeywordList { get; }
        [InverseProperty("Message")]
        public ICollection<MessageReference>? References { get; }
        [InverseProperty("ReferencedMessage")]
        public ICollection<MessageReference>? ReferencedBy { get; }
    }

    public enum DownloadStatus
    {
        Nothing,
        Headers, // only the message headers have been downloaded
        Preview, // the message headers and the preview text has been downloaded
        Body, // the body text (and HTML if relevant) have been downloaded. The HTML has not been converted to text yet.
        FullText, // the text and HTML body is searchable as text. The attachments are searchable by their file name and description.
        Attachments, // the attachments have been converted to text (if possible) and are searchable.
    }
}

Changes to Models/MessageKeyword.cs.

4
5
6
7
8
9
10
11
12
13
14
15
16

namespace MailJanitor.Models
{
    public class MessageKeyword
    {
        public long ID { get; set; }
        public long MessageID { get; set; }
        public Message Message { get; set; }
        public long KeywordID { get; set; }
        public Keyword Keyword { get; set; }
        public int Order { get; set; }
    }
}







|

|



4
5
6
7
8
9
10
11
12
13
14
15
16

namespace MailJanitor.Models
{
    public class MessageKeyword
    {
        public long ID { get; set; }
        public long MessageID { get; set; }
        public Message? Message { get; set; }
        public long KeywordID { get; set; }
        public Keyword? Keyword { get; set; }
        public int Order { get; set; }
    }
}

Changes to Models/MessageParticipant.cs.

21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
    }

    public class MessageParticipant
    {
        public long ID { get; set; }

        public long MessageID { get; set; }
        public Message Message { get; set; }

        public long ParticipantID { get; set; }
        public Participant Participant { get; set; }

        public ParticipantField Field { get; set; }
        public int Order { get; set; }
        public string Name { get; set; }
    }
}







|


|



|


21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
    }

    public class MessageParticipant
    {
        public long ID { get; set; }

        public long MessageID { get; set; }
        public Message? Message { get; set; }

        public long ParticipantID { get; set; }
        public Participant? Participant { get; set; }

        public ParticipantField Field { get; set; }
        public int Order { get; set; }
        public string? Name { get; set; }
    }
}

Changes to Models/MessageReference.cs.

6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

namespace MailJanitor.Models
{
    public class MessageReference
    {
        public long ID { get; set; }
        public long MessageID { get; set; }
        public Message Message { get; set; }
        public bool InReplyTo { get; set; }
        public int Order { get; set; }
        [Required]
        public string ReferencedRfcMessageID { get; set; }
        public long? ReferencedMessageID { get; set; }
        public Message ReferencedMessage { get; set; }
    }
}







|


<
|

|


6
7
8
9
10
11
12
13
14
15

16
17
18
19
20

namespace MailJanitor.Models
{
    public class MessageReference
    {
        public long ID { get; set; }
        public long MessageID { get; set; }
        public Message? Message { get; set; }
        public bool InReplyTo { get; set; }
        public int Order { get; set; }

        public string ReferencedRfcMessageID { get; set; } = "";
        public long? ReferencedMessageID { get; set; }
        public Message? ReferencedMessage { get; set; }
    }
}

Changes to Models/Participant.cs.

6
7
8
9
10
11
12
13
14
15
16
17
18
19

namespace MailJanitor.Models
{
    public class Participant
    {
        public long ID { get; set; }
        [Column(TypeName = "TEXT COLLATE NOCASE")]
        public string Address { get; set; }
        [Column(TypeName = "TEXT COLLATE NOCASE")]
        public string Name { get; set; }

        public ICollection<MessageParticipant> Messages { get; set; }
    }
}







|

|

|


6
7
8
9
10
11
12
13
14
15
16
17
18
19

namespace MailJanitor.Models
{
    public class Participant
    {
        public long ID { get; set; }
        [Column(TypeName = "TEXT COLLATE NOCASE")]
        public string? Address { get; set; }
        [Column(TypeName = "TEXT COLLATE NOCASE")]
        public string? Name { get; set; }

        public ICollection<MessageParticipant>? Messages { get; internal set; }
    }
}

Changes to Options.cs.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
using CommandLine;

namespace MailJanitor
{
    public class Options
    {
        [Value(0, MetaName = "<username>", Required = true, HelpText = "User name (e-mail address).")]
        public string EmailAddress { get; set; }

        [Value(1, MetaName = "<password>", Required = true, HelpText = "Password for the mail server.")]
        public string Password { get; set; }

        [Value(2, MetaName = "<mail server>", Required = false, Default = "imap.gmail.com",
            HelpText = "The host name of the e-mail server. Defaults to imap.gmail.com.")]
        public string MailServer { get; set; }

        [Value(3, MetaName = "<port number>", Required = false, Default = 993, 
            HelpText = "The port number of the IMAP e-mail server. Defaults to 993 (IMAP over SSL).")]
        public short PortNumber { get; set; }

        [Option('v', "verbose", Required = false, HelpText = "Show more output.")]
        public bool Verbose { get; set; }

        [Option('c', "compact-database", Required = false, Default = false, HelpText = "Vacuum the database when done.")]
        public bool VacuumDatabase { get; set; }

    }
}




|


|


|



|

|











1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
using CommandLine;

namespace MailJanitor
{
    public class CommandLineOptions
    {
        [Value(0, MetaName = "<username>", Required = true, HelpText = "User name (e-mail address).")]
        public string EmailAddress { get; set; } = "";

        [Value(1, MetaName = "<password>", Required = true, HelpText = "Password for the mail server.")]
        public string Password { get; set; } = "";

        [Value(2, MetaName = "<mail server>", Required = false, Default = "imap.gmail.com",
            HelpText = "The host name of the e-mail server. Defaults to imap.gmail.com.")]
        public string MailServer { get; set; } = "";

        [Value(3, MetaName = "<port number>", Required = false, Default = 993,
            HelpText = "The port number of the IMAP e-mail server. Defaults to 993 (IMAP over SSL).")]
        public short PortNumber { get; set; }

        [Option('v', "verbose", Required = false, HelpText = "Show more output.")]
        public bool Verbose { get; set; }

        [Option('c', "compact-database", Required = false, Default = false, HelpText = "Vacuum the database when done.")]
        public bool VacuumDatabase { get; set; }

    }
}

Changes to Program.cs.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
..
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
..
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
..
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
using System;
using System.Net;
using System.Threading;
using System.Threading.Tasks;
using CommandLine;
using MailKit;
using MC.Utilities;
using Microsoft.EntityFrameworkCore;

namespace MailJanitor
{
    class Program
    {
        public enum ResultCode
        {
................................................................................


        static int Main(string[] args)
        {
            ResultCode result = ResultCode.Exception;
            try
            {
                Parser.Default.ParseArguments<Options>(args)
                    .WithParsed(o =>
                    {
                        using (var tokenSource = new CancellationTokenSource())
                        {
                            Console.CancelKeyPress += new ConsoleCancelEventHandler((sender, eventArgs) =>
                            {
                                eventArgs.Cancel = true; //(eventArgs.SpecialKey != ConsoleSpecialKey.ControlBreak);
................................................................................
                            } finally {
                                TimerCollection.Global.Stop("SynchronizeAccount");
                            }
                            bool FetchFolder(IMailFolder mailFolder)
                            {
                                var offendingAttributes = FolderAttributes.Drafts
                                                        | FolderAttributes.All
                                                        | FolderAttributes.Archive 
                                                        | FolderAttributes.Trash 
                                                        | FolderAttributes.Junk;
                                return (mailFolder.Attributes & offendingAttributes) == FolderAttributes.None;
                            }
                        }
                        result = ResultCode.Ok;
                    })
                    .WithNotParsed(error =>
................................................................................
                    {
                        result = ResultCode.WrongArgument;
                    });
            }
            catch (Exception ex)
            {
                Con.WriteLine(ex);
                // #if DEBUG
                //                 if (!Console.IsErrorRedirected)
                //                 {
                //                     Console.WriteLine("Press a key to end...");
                //                     Console.ReadKey();
                //                 }
                // #endif

                // Return meaningful error codes
                if (ex is TaskCanceledException || ex is OperationCanceledException) result = ResultCode.Cancelled; // operation canceled
                else if (ex is ServiceNotAuthenticatedException) result = ResultCode.AuthenticationProblem; // authentication problem
                else if (ex is ServiceNotConnectedException) result = ResultCode.ConnectionProblem; // mail server connection problem
                else result = ResultCode.Exception; // unexpected problem
            }
            finally
            {
                Con.WriteLine(TimerCollection.Global.ToString());
            }

            return (int)result;
        }

        // TODO: use an FTS table to enable searching for messages
    }
}







<







 







|







 







|
|







 







|
|
|
|
|
|
|
|
|
|
<
<
<












1
2
3
4
5
6
7

8
9
10
11
12
13
14
..
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
..
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
..
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80



81
82
83
84
85
86
87
88
89
90
91
92
using System;
using System.Net;
using System.Threading;
using System.Threading.Tasks;
using CommandLine;
using MailKit;
using MC.Utilities;


namespace MailJanitor
{
    class Program
    {
        public enum ResultCode
        {
................................................................................


        static int Main(string[] args)
        {
            ResultCode result = ResultCode.Exception;
            try
            {
                Parser.Default.ParseArguments<CommandLineOptions>(args)
                    .WithParsed(o =>
                    {
                        using (var tokenSource = new CancellationTokenSource())
                        {
                            Console.CancelKeyPress += new ConsoleCancelEventHandler((sender, eventArgs) =>
                            {
                                eventArgs.Cancel = true; //(eventArgs.SpecialKey != ConsoleSpecialKey.ControlBreak);
................................................................................
                            } finally {
                                TimerCollection.Global.Stop("SynchronizeAccount");
                            }
                            bool FetchFolder(IMailFolder mailFolder)
                            {
                                var offendingAttributes = FolderAttributes.Drafts
                                                        | FolderAttributes.All
                                                        | FolderAttributes.Archive
                                                        | FolderAttributes.Trash
                                                        | FolderAttributes.Junk;
                                return (mailFolder.Attributes & offendingAttributes) == FolderAttributes.None;
                            }
                        }
                        result = ResultCode.Ok;
                    })
                    .WithNotParsed(error =>
................................................................................
                    {
                        result = ResultCode.WrongArgument;
                    });
            }
            catch (Exception ex)
            {
                Con.WriteLine(ex);

                // Return meaningful error codes
                if (ex is TaskCanceledException || ex is OperationCanceledException)
                    result = ResultCode.Cancelled;
                else if (ex is ServiceNotAuthenticatedException)
                    result = ResultCode.AuthenticationProblem;
                else if (ex is ServiceNotConnectedException)
                    result = ResultCode.ConnectionProblem;
                else
                    result = ResultCode.Exception;



            }
            finally
            {
                Con.WriteLine(TimerCollection.Global.ToString());
            }

            return (int)result;
        }

        // TODO: use an FTS table to enable searching for messages
    }
}

Changes to Repositories/AccountsRepository.cs.

5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
using MailJanitor.Models;
using Microsoft.EntityFrameworkCore;

namespace MailJanitor.Repositories
{
    public class AccountsRepository : Repository<Account>
    {
        public AccountsRepository(MailJanitorContext db, CancellationToken cancellationToken = default(CancellationToken)) : base(db, cancellationToken)
        {
        }

        public async Task<Account> GetOrCreateAsync(string host, short port, string userName)
        {
            Account account = await _set
                .Include(a => a.Folders)







|







5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
using MailJanitor.Models;
using Microsoft.EntityFrameworkCore;

namespace MailJanitor.Repositories
{
    public class AccountsRepository : Repository<Account>
    {
        public AccountsRepository(MailJanitorContext db, CancellationToken cancellationToken = default) : base(db, cancellationToken)
        {
        }

        public async Task<Account> GetOrCreateAsync(string host, short port, string userName)
        {
            Account account = await _set
                .Include(a => a.Folders)

Changes to Repositories/FolderMessagesRepository.cs.

5
6
7
8
9
10
11
12
13
14
15



16
17
18
19
20
21
22
23









24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
using MailJanitor.Models;
using Microsoft.EntityFrameworkCore;

namespace MailJanitor.Repositories
{
    public class FolderMessagesRepository : Repository<FolderMessage>
    {
        public FolderMessagesRepository(MailJanitorContext db, CancellationToken cancellationToken = default(CancellationToken)) : base(db, cancellationToken)
        {
        }




        public Task<uint> GetMaxUIDAsync(Folder folder)
        {
            return _db.FolderMessages
                .Where(fm => fm.Folder == folder)
                .DefaultIfEmpty()
                .MaxAsync(fm => fm == null ? 0 : fm.UID, _cancellationToken);
        }










        public async Task<Dictionary<ulong, (long? folderMsgID, long? messageID)>> GetIDsByGlobalIDAsync(long dbFolderID, IEnumerable<ulong> globalIDs)
        {
            return await(from dbMessage in _db.Messages
                         where globalIDs.Contains(dbMessage.GlobalID.Value)
                         join folderMsg in _set.Where(folderMsg => folderMsg.FolderID == dbFolderID)
                              on dbMessage.ID equals folderMsg.MessageID
                              into folderMsgs
                         from folderMsg in folderMsgs.DefaultIfEmpty()
                         select new
                         {
                             globalID = dbMessage.GlobalID.Value,
                             folderMsgID = folderMsg == null ? null : (long?)folderMsg.ID,
                             messageID = dbMessage == null ? null : (long?)dbMessage.ID,
                         })
                        .ToDictionaryAsync(idSet => idSet.globalID,
                                           idSet => (idSet.folderMsgID, idSet.messageID),
                                           _cancellationToken);
        }
    }
}







|



>
>
>








>
>
>
>
>
>
>
>
>



|






|









5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
using MailJanitor.Models;
using Microsoft.EntityFrameworkCore;

namespace MailJanitor.Repositories
{
    public class FolderMessagesRepository : Repository<FolderMessage>
    {
        internal FolderMessagesRepository(MailJanitorContext db, CancellationToken cancellationToken = default) : base(db, cancellationToken)
        {
        }

        /// <summary>
        /// Retrieves the highest UID in the given <paramref name="folder"/>.
        /// </summary>
        public Task<uint> GetMaxUIDAsync(Folder folder)
        {
            return _db.FolderMessages
                .Where(fm => fm.Folder == folder)
                .DefaultIfEmpty()
                .MaxAsync(fm => fm == null ? 0 : fm.UID, _cancellationToken);
        }

        /// <summary>
        /// Retrieves a list of <see cref="FolderMessage.ID"/>s and <see cref="Message.ID"/>s matching the given
        /// <paramref name="globalIDs"/> in the given <paramref name="dbFolderID"/>.
        /// </summary>
        /// <param name="dbFolderID">The ID of the folder containing those messages.</param>
        /// <param name="globalIDs">A list of server-wide IDs for the messages we're looking for.</param>
        /// <returns>A <see cref="Dictionary{ulong, (long? folderMsgID, long? messageID)}"/> containing, for each server-wide ID,
        /// a tuple with the corresponding <see cref="FolderMessage.ID"/> and <see cref="Message.ID"/>. These can be
        /// <see cref="null"/> if not present in the database.</returns>
        public async Task<Dictionary<ulong, (long? folderMsgID, long? messageID)>> GetIDsByGlobalIDAsync(long dbFolderID, IEnumerable<ulong> globalIDs)
        {
            return await(from dbMessage in _db.Messages
                         where globalIDs.Contains(dbMessage.GlobalID ?? 0)
                         join folderMsg in _set.Where(folderMsg => folderMsg.FolderID == dbFolderID)
                              on dbMessage.ID equals folderMsg.MessageID
                              into folderMsgs
                         from folderMsg in folderMsgs.DefaultIfEmpty()
                         select new
                         {
                             globalID = dbMessage.GlobalID ?? 0,
                             folderMsgID = folderMsg == null ? null : (long?)folderMsg.ID,
                             messageID = dbMessage == null ? null : (long?)dbMessage.ID,
                         })
                        .ToDictionaryAsync(idSet => idSet.globalID,
                                           idSet => (idSet.folderMsgID, idSet.messageID),
                                           _cancellationToken);
        }
    }
}

Changes to Repositories/FoldersRepository.cs.

7
8
9
10
11
12
13
14
15
16
17
18
19





20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
..
67
68
69
70
71
72
73





74
75
76
77
78
79
80
...
102
103
104
105
106
107
108
109
110
111
112
113
using MailKit;
using Microsoft.EntityFrameworkCore;

namespace MailJanitor.Repositories
{
    public class FoldersRepository : Repository<Folder>
    {
        public FoldersRepository(MailJanitorContext db, CancellationToken cancellationToken = default(CancellationToken)) : base(db, cancellationToken)
        {
        }

        public async Task<Folder> GetOrCreateAsync(IMailFolder mailFolder, Account account)
        {





            if (_db.Entry(account).State == EntityState.Detached)
                account = _db.Accounts.Find(account.ID); // Make sure we've got an entity from the current DbContext

            // If the mail folder has a parent, make sure that exists first
            Folder dbParentFolder = null;
            if (mailFolder.ParentFolder != null && !string.IsNullOrEmpty(mailFolder.ParentFolder.Name))
            {
                dbParentFolder = await GetOrCreateAsync(mailFolder.ParentFolder, account);
            }

            Folder dbFolder = await _set
                .Where(f => f.AccountID == account.ID && f.Name == mailFolder.Name && f.ParentFolderID == (dbParentFolder == null ? (long?)null : dbParentFolder.ID))
                .Include(f => f.ParentFolder)
                .SingleOrDefaultAsync(_cancellationToken);
            if (dbFolder == null)
            {
                dbFolder = new Folder
                {
................................................................................
            }
            await _db.SaveChangesAsync(_cancellationToken);
            return dbFolder;
        }

        public Task<string> GetFullNameAsync(Folder folder)
        {





            return GetFullNameAsync(folder.ID);
        }
        public async Task<string> GetFullNameAsync(long id)
        {
            return (await GetFullNamesAsync(new long[] { id })).First();
        }

................................................................................
            )
              SELECT Name
                FROM FullNames
               WHERE ParentID IS NULL
            ORDER BY CASE ID {string.Join("\n", ids.Select((id, index) => $"WHEN {id} THEN {index}"))} END
            ;";
#pragma warning disable EF1000 // There's no risk of SQL injection with this query.
            return await _set.FromSql(sql).Select(f => f.Name).ToListAsync();
#pragma warning restore EF1000
        }
    }
}







|





>
>
>
>
>




|





|







 







>
>
>
>
>







 







|




7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
..
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
...
112
113
114
115
116
117
118
119
120
121
122
123
using MailKit;
using Microsoft.EntityFrameworkCore;

namespace MailJanitor.Repositories
{
    public class FoldersRepository : Repository<Folder>
    {
        internal FoldersRepository(MailJanitorContext db, CancellationToken cancellationToken = default) : base(db, cancellationToken)
        {
        }

        public async Task<Folder> GetOrCreateAsync(IMailFolder mailFolder, Account account)
        {
            if (account is null)
            {
                throw new ArgumentNullException(nameof(account));
            }

            if (_db.Entry(account).State == EntityState.Detached)
                account = _db.Accounts.Find(account.ID); // Make sure we've got an entity from the current DbContext

            // If the mail folder has a parent, make sure that exists first
            Folder? dbParentFolder = null;
            if (mailFolder.ParentFolder != null && !string.IsNullOrEmpty(mailFolder.ParentFolder.Name))
            {
                dbParentFolder = await GetOrCreateAsync(mailFolder.ParentFolder, account);
            }

            Folder? dbFolder = await _set
                .Where(f => f.AccountID == account.ID && f.Name == mailFolder.Name && f.ParentFolderID == (dbParentFolder == null ? (long?)null : dbParentFolder.ID))
                .Include(f => f.ParentFolder)
                .SingleOrDefaultAsync(_cancellationToken);
            if (dbFolder == null)
            {
                dbFolder = new Folder
                {
................................................................................
            }
            await _db.SaveChangesAsync(_cancellationToken);
            return dbFolder;
        }

        public Task<string> GetFullNameAsync(Folder folder)
        {
            if (folder is null)
            {
                throw new ArgumentNullException(nameof(folder));
            }

            return GetFullNameAsync(folder.ID);
        }
        public async Task<string> GetFullNameAsync(long id)
        {
            return (await GetFullNamesAsync(new long[] { id })).First();
        }

................................................................................
            )
              SELECT Name
                FROM FullNames
               WHERE ParentID IS NULL
            ORDER BY CASE ID {string.Join("\n", ids.Select((id, index) => $"WHEN {id} THEN {index}"))} END
            ;";
#pragma warning disable EF1000 // There's no risk of SQL injection with this query.
            return await _set.FromSqlRaw(sql).Select(f => f.Name).ToListAsync();
#pragma warning restore EF1000
        }
    }
}

Changes to Repositories/KeywordsRepository.cs.

1
2
3
4
5
6
7
8
9
10
11
12
using System.Threading;
using MailJanitor.Models;

namespace MailJanitor.Repositories
{
    public class KeywordsRepository : Repository<Keyword>
    {
        public KeywordsRepository(MailJanitorContext db, CancellationToken cancellationToken = default(CancellationToken)) : base(db, cancellationToken)
        {
        }
    }
}







|




1
2
3
4
5
6
7
8
9
10
11
12
using System.Threading;
using MailJanitor.Models;

namespace MailJanitor.Repositories
{
    public class KeywordsRepository : Repository<Keyword>
    {
        internal KeywordsRepository(MailJanitorContext db, CancellationToken cancellationToken = default) : base(db, cancellationToken)
        {
        }
    }
}

Changes to Repositories/MessageReferencesRepository.cs.

1
2
3
4
5
6
7
8
9
10
11
12
using System.Threading;
using MailJanitor.Models;

namespace MailJanitor.Repositories
{
    public class MessageReferencesRepository : Repository<MessageReference>
    {
        public MessageReferencesRepository(MailJanitorContext db, CancellationToken cancellationToken = default(CancellationToken)) : base(db, cancellationToken)
        {
        }
    }
}







|




1
2
3
4
5
6
7
8
9
10
11
12
using System.Threading;
using MailJanitor.Models;

namespace MailJanitor.Repositories
{
    public class MessageReferencesRepository : Repository<MessageReference>
    {
        internal MessageReferencesRepository(MailJanitorContext db, CancellationToken cancellationToken = default) : base(db, cancellationToken)
        {
        }
    }
}

Changes to Repositories/MessagesRepository.cs.

1
2
3
4
5
6
7
8
9
10
11
12
using System.Threading;
using MailJanitor.Models;

namespace MailJanitor.Repositories
{
    public class MessagesRepository : Repository<Message>
    {
        public MessagesRepository(MailJanitorContext db, CancellationToken cancellationToken = default(CancellationToken)) : base(db, cancellationToken)
        {
        }
    }
}







|




1
2
3
4
5
6
7
8
9
10
11
12
using System.Threading;
using MailJanitor.Models;

namespace MailJanitor.Repositories
{
    public class MessagesRepository : Repository<Message>
    {
        internal MessagesRepository(MailJanitorContext db, CancellationToken cancellationToken = default) : base(db, cancellationToken)
        {
        }
    }
}

Changes to Repositories/ParticipantsRepository.cs.

1
2
3
4
5
6
7
8
9
10
11
12
using System.Threading;
using MailJanitor.Models;

namespace MailJanitor.Repositories
{
    public class ParticipantsRepository : Repository<Participant>
    {
        public ParticipantsRepository(MailJanitorContext db, CancellationToken cancellationToken = default(CancellationToken)) : base(db, cancellationToken)
        {
        }
    }
}







|




1
2
3
4
5
6
7
8
9
10
11
12
using System.Threading;
using MailJanitor.Models;

namespace MailJanitor.Repositories
{
    public class ParticipantsRepository : Repository<Participant>
    {
        internal ParticipantsRepository(MailJanitorContext db, CancellationToken cancellationToken = default) : base(db, cancellationToken)
        {
        }
    }
}

Changes to Repositories/Repository.cs.

1
2
3

4
5
6
7
8
9
10
..
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54

55
56
57
58
59
60

61
62
63
64
65
66
67
68
69
70
71

72
73
74
75
76
77
78

79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
...
154
155
156
157
158
159
160





161
162
163
164
165
166
167
...
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
using System;
using System.Collections;
using System.Collections.Generic;

using System.Linq;
using System.Linq.Expressions;
using System.Threading;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata;

................................................................................
{
    public abstract class Repository<T> where T : class
    {
        protected readonly MailJanitorContext _db;
        protected readonly DbSet<T> _set;
        protected readonly CancellationToken _cancellationToken;

        public Repository(MailJanitorContext db, CancellationToken cancellationToken)
        {
            _db = db;
            _set = _db.Set<T>();
            _cancellationToken = cancellationToken;
        }

        public Task<T> GetByIdAsync(long id)
        {
            return _set.FindAsync(new object[] { id }, _cancellationToken);
        }

        public Task<T> SingleOrDefaultAsync(Expression<Func<T, bool>> where)
        {
            return _set.SingleOrDefaultAsync(where, _cancellationToken);
        }

        public IEnumerable<T> GetList(Expression<Func<T,bool>> where = null)
        {
            IQueryable<T> result = _set;
            if (where != null)
                result = result.Where(where);
            return result.AsEnumerable();
        }
        public IEnumerable<T> GetList<TKey>(Expression<Func<T, bool>> where,
                                         Expression<Func<T, TKey>> orderBy,
                                         IComparer<TKey> keyComparer = null)
        {
            return _set
                .Where(where)
                .OrderBy(orderBy, keyComparer ?? Comparer<TKey>.Default)
                .AsEnumerable();
        }

        public Task<Dictionary<TKey, T>> GetDictionaryAsync<TKey>(Func<T, TKey> keySelector,
                                                                  IEqualityComparer<TKey> keyComparer = null)

        {
            return GetDictionaryAsync((Expression<Func<T, bool>>)null, keySelector, keyComparer);
        }
        public Task<Dictionary<TKey, T>> GetDictionaryAsync<TKey>(Expression<Func<T, bool>> where,
                                                                  Func<T, TKey> keySelector,
                                                                  IEqualityComparer<TKey> keyComparer = null)

        {
            IQueryable<T> result = _set;
            if (where != null)
                result = result.Where(where);
            return result.ToDictionaryAsync(keySelector,
                                            keyComparer ?? EqualityComparer<TKey>.Default,
                                            _cancellationToken);
        }
        public Task<Dictionary<TKey,TValue>> GetDictionaryAsync<TKey, TValue>(Func<T, TKey> keySelector,
                                                                              Func<T, TValue> valueSelector,
                                                                              IEqualityComparer<TKey> keyComparer = null)

        {
            return GetDictionaryAsync(null, keySelector, valueSelector, keyComparer);
        }
        public Task<Dictionary<TKey,TValue>> GetDictionaryAsync<TKey, TValue>(Expression<Func<T, bool>> where,
                                                                              Func<T, TKey> keySelector,
                                                                              Func<T, TValue> valueSelector,
                                                                              IEqualityComparer<TKey> keyComparer = null)

        {
            IQueryable<T> result = _set;
            if (where != null)
                result = result.Where(where);
            return result.ToDictionaryAsync<T, TKey, TValue>(keySelector,
                                                             valueSelector,
                                                             keyComparer ?? EqualityComparer<TKey>.Default,
                                                             _cancellationToken);
        }

        public ILookup<TKey, T> GetLookup<TKey>(Func<T, TKey> keySelector,
                                                IEqualityComparer<TKey> keyComparer = null)
        {
            return GetLookup((Expression<Func<T, bool>>)null, keySelector, keyComparer);
        }
        public ILookup<TKey, T> GetLookup<TKey>(Expression<Func<T, bool>> where,
                                                Func<T, TKey> keySelector,
                                                IEqualityComparer<TKey> keyComparer = null)
        {
            IQueryable<T> result = _set;
            if (where != null)
                result = result.Where(where);
            return result.ToLookup(keySelector,
                                   keyComparer ?? EqualityComparer<TKey>.Default);
        }
        public ILookup<TKey, TValue> GetLookup<TKey, TValue>(Func<T, TKey> keySelector,
                                                             Func<T, TValue> valueSelector,
                                                             IEqualityComparer<TKey> keyComparer = null)
        {
            return GetLookup(null, keySelector, valueSelector, keyComparer);
        }
        public ILookup<TKey, TValue> GetLookup<TKey, TValue>(Expression<Func<T, bool>> where,
                                                             Func<T, TKey> keySelector,
                                                             Func<T, TValue> valueSelector,
                                                             IEqualityComparer<TKey> keyComparer = null)
        {
            IQueryable<T> result = _set;
            if (where != null)
                result = result.Where(where);
            return result.ToLookup(keySelector,
                                   valueSelector,
                                   keyComparer ?? EqualityComparer<TKey>.Default);
        }

        public IEnumerable<TResult> Select<TResult>(Expression<Func<T, bool>> where,
                                                    Expression<Func<T, TResult>> valueExpression,
                                                    Expression<Func<TResult, bool>> valueFilter = null,
                                                    IComparer<TResult> valueSorter = null)
        {
            return Select(valueExpression, where, valueFilter, valueSorter);
        }
        public IEnumerable<TResult> Select<TResult>(Expression<Func<T, TResult>> valueExpression,
                                                    Expression<Func<T, bool>> entityFilter = null,
                                                    Expression<Func<TResult, bool>> valueFilter = null,
                                                    IComparer<TResult> valueSorter = null)
        {
            IQueryable<T> list = _set;
            if (entityFilter != null)
                list = list.Where(entityFilter);
            IQueryable<TResult> result = list.Select(valueExpression);
            if (valueFilter != null)
                result = result.Where(valueFilter);
................................................................................

        public void Update(params T[] entities)
        {
            Update((IEnumerable<T>)entities);
        }
        public void Update(IEnumerable<T> entities)
        {





            foreach (T entity in entities)
            {
                _db.Entry(entity).State = EntityState.Modified;
            }
        }

        public void Delete(Expression<Func<T, bool>> predicate)
................................................................................
        public int DeleteByIDs<TKey>(Expression<Func<T, bool>> predicate, Expression<Func<T, TKey>> idSelector)
        {
            IEnumerable<TKey> ids = _set.Where(predicate).Select(idSelector).AsEnumerable();
            return DeleteByIDs(ids);
        }
        public int DeleteByID<TKey>(params TKey[] ids)
        {
            return DeleteByIDs((IEnumerable<TKey>)ids);
        }
        public virtual int DeleteByIDs<TKey>(IEnumerable<TKey> ids)
        {
            if (typeof(long).IsAssignableFrom(typeof(TKey)))
            {
                IEntityType entityType = _db.Model.FindEntityType(typeof(T));
                IReadOnlyList<IProperty> keyProperties = entityType.FindPrimaryKey().Properties;
                if (keyProperties.Count == 1)
                {
                    IProperty key = keyProperties[0];
                    if (typeof(long).IsAssignableFrom(key.ClrType))
                    {
                        // Ensure we've got Int64s, and nothing else
                        var numericIDs = ids.Select(id => Convert.ToInt64(id));

                        // Prepare the SQL statement
                        string tableName = entityType.Relational().TableName;
                        string keyColumn = key.Relational().ColumnName;
                        string sql = $"DELETE FROM [{tableName}] WHERE [{keyColumn}] IN ({string.Join(", ", numericIDs)})";
                        #pragma warning disable EF1000 // SQL command has been checked for SQL injection vectors, and found safe.
                        int numDeleted = _db.Database.ExecuteSqlCommand(sql);
                        #pragma warning restore EF1000

                        // Detach all corresponding entries
                        var entries = _db.ChangeTracker.Entries()
                            .Where(e => e.Metadata == entityType)
                            .Where(e => ids.Contains((TKey)e.Property(key.Name).CurrentValue));
                        foreach (var entry in entries)



>







 







|

|






|







|








|








|
>

|

|

|
>










|
>



|


|
>




|
|
|
|



|

|

|

|









|



|


|











|
|




|
|
|







 







>
>
>
>
>







 







|













|


|
|


|







1
2
3
4
5
6
7
8
9
10
11
..
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
...
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
...
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
using System;
using System.Collections;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Linq.Expressions;
using System.Threading;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata;

................................................................................
{
    public abstract class Repository<T> where T : class
    {
        protected readonly MailJanitorContext _db;
        protected readonly DbSet<T> _set;
        protected readonly CancellationToken _cancellationToken;

        internal Repository(MailJanitorContext db, CancellationToken cancellationToken)
        {
            _db = db ?? throw new ArgumentNullException(nameof(db));
            _set = _db.Set<T>();
            _cancellationToken = cancellationToken;
        }

        public Task<T> GetByIdAsync(long id)
        {
            return _set.FindAsync(new object[] { id }, _cancellationToken).AsTask();
        }

        public Task<T> SingleOrDefaultAsync(Expression<Func<T, bool>> where)
        {
            return _set.SingleOrDefaultAsync(where, _cancellationToken);
        }

        public IEnumerable<T> GetList(Expression<Func<T, bool>>? where = null)
        {
            IQueryable<T> result = _set;
            if (where != null)
                result = result.Where(where);
            return result.AsEnumerable();
        }
        public IEnumerable<T> GetList<TKey>(Expression<Func<T, bool>> where,
                                         Expression<Func<T, TKey>> orderBy,
                                         IComparer<TKey>? keyComparer = null)
        {
            return _set
                .Where(where)
                .OrderBy(orderBy, keyComparer ?? Comparer<TKey>.Default)
                .AsEnumerable();
        }

        public Task<Dictionary<TKey, T>> GetDictionaryAsync<TKey>(Func<T, TKey> keySelector,
                                                                  IEqualityComparer<TKey>? keyComparer = null)
                                                                  where TKey: notnull
        {
            return GetDictionaryAsync((Expression<Func<T, bool>>?)null, keySelector, keyComparer);
        }
        public Task<Dictionary<TKey, T>> GetDictionaryAsync<TKey>(Expression<Func<T, bool>>? where,
                                                                  Func<T, TKey> keySelector,
                                                                  IEqualityComparer<TKey>? keyComparer = null)
                                                                  where TKey : notnull
        {
            IQueryable<T> result = _set;
            if (where != null)
                result = result.Where(where);
            return result.ToDictionaryAsync(keySelector,
                                            keyComparer ?? EqualityComparer<TKey>.Default,
                                            _cancellationToken);
        }
        public Task<Dictionary<TKey,TValue>> GetDictionaryAsync<TKey, TValue>(Func<T, TKey> keySelector,
                                                                              Func<T, TValue> valueSelector,
                                                                              IEqualityComparer<TKey>? keyComparer = null)
                                                                              where TKey : notnull
        {
            return GetDictionaryAsync(null, keySelector, valueSelector, keyComparer);
        }
        public Task<Dictionary<TKey,TValue>> GetDictionaryAsync<TKey, TValue>(Expression<Func<T, bool>>? where,
                                                                              Func<T, TKey> keySelector,
                                                                              Func<T, TValue> valueSelector,
                                                                              IEqualityComparer<TKey>? keyComparer = null)
                                                                              where TKey : notnull
        {
            IQueryable<T> result = _set;
            if (where != null)
                result = result.Where(where);
            return result.ToDictionaryAsync(keySelector,
                                            valueSelector,
                                            keyComparer ?? EqualityComparer<TKey>.Default,
                                            _cancellationToken);
        }

        public ILookup<TKey, T> GetLookup<TKey>(Func<T, TKey> keySelector,
                                                IEqualityComparer<TKey>? keyComparer = null)
        {
            return GetLookup((Expression<Func<T, bool>>?)null, keySelector, keyComparer);
        }
        public ILookup<TKey, T> GetLookup<TKey>(Expression<Func<T, bool>>? where,
                                                Func<T, TKey> keySelector,
                                                IEqualityComparer<TKey>? keyComparer = null)
        {
            IQueryable<T> result = _set;
            if (where != null)
                result = result.Where(where);
            return result.ToLookup(keySelector,
                                   keyComparer ?? EqualityComparer<TKey>.Default);
        }
        public ILookup<TKey, TValue> GetLookup<TKey, TValue>(Func<T, TKey> keySelector,
                                                             Func<T, TValue> valueSelector,
                                                             IEqualityComparer<TKey>? keyComparer = null)
        {
            return GetLookup(null, keySelector, valueSelector, keyComparer);
        }
        public ILookup<TKey, TValue> GetLookup<TKey, TValue>(Expression<Func<T, bool>>? where,
                                                             Func<T, TKey> keySelector,
                                                             Func<T, TValue> valueSelector,
                                                             IEqualityComparer<TKey>? keyComparer = null)
        {
            IQueryable<T> result = _set;
            if (where != null)
                result = result.Where(where);
            return result.ToLookup(keySelector,
                                   valueSelector,
                                   keyComparer ?? EqualityComparer<TKey>.Default);
        }

        public IEnumerable<TResult> Select<TResult>(Expression<Func<T, bool>> where,
                                                    Expression<Func<T, TResult>> valueExpression,
                                                    Expression<Func<TResult, bool>>? valueFilter = null,
                                                    IComparer<TResult>? valueSorter = null)
        {
            return Select(valueExpression, where, valueFilter, valueSorter);
        }
        public IEnumerable<TResult> Select<TResult>(Expression<Func<T, TResult>> valueExpression,
                                                    Expression<Func<T, bool>>? entityFilter = null,
                                                    Expression<Func<TResult, bool>>? valueFilter = null,
                                                    IComparer<TResult>? valueSorter = null)
        {
            IQueryable<T> list = _set;
            if (entityFilter != null)
                list = list.Where(entityFilter);
            IQueryable<TResult> result = list.Select(valueExpression);
            if (valueFilter != null)
                result = result.Where(valueFilter);
................................................................................

        public void Update(params T[] entities)
        {
            Update((IEnumerable<T>)entities);
        }
        public void Update(IEnumerable<T> entities)
        {
            if (entities is null)
            {
                throw new ArgumentNullException(nameof(entities));
            }

            foreach (T entity in entities)
            {
                _db.Entry(entity).State = EntityState.Modified;
            }
        }

        public void Delete(Expression<Func<T, bool>> predicate)
................................................................................
        public int DeleteByIDs<TKey>(Expression<Func<T, bool>> predicate, Expression<Func<T, TKey>> idSelector)
        {
            IEnumerable<TKey> ids = _set.Where(predicate).Select(idSelector).AsEnumerable();
            return DeleteByIDs(ids);
        }
        public int DeleteByID<TKey>(params TKey[] ids)
        {
            return DeleteByIDs(ids);
        }
        public virtual int DeleteByIDs<TKey>(IEnumerable<TKey> ids)
        {
            if (typeof(long).IsAssignableFrom(typeof(TKey)))
            {
                IEntityType entityType = _db.Model.FindEntityType(typeof(T));
                IReadOnlyList<IProperty> keyProperties = entityType.FindPrimaryKey().Properties;
                if (keyProperties.Count == 1)
                {
                    IProperty key = keyProperties[0];
                    if (typeof(long).IsAssignableFrom(key.ClrType))
                    {
                        // Ensure we've got Int64s, and nothing else
                        var numericIDs = ids.Select(id => Convert.ToInt64(id, CultureInfo.InvariantCulture));

                        // Prepare the SQL statement
                        string tableName = entityType.GetTableName();
                        string keyColumn = key.GetColumnName();
                        string sql = $"DELETE FROM [{tableName}] WHERE [{keyColumn}] IN ({string.Join(", ", numericIDs)})";
                        #pragma warning disable EF1000 // SQL command has been checked for SQL injection vectors, and found safe.
                        int numDeleted = _db.Database.ExecuteSqlRaw(sql);
                        #pragma warning restore EF1000

                        // Detach all corresponding entries
                        var entries = _db.ChangeTracker.Entries()
                            .Where(e => e.Metadata == entityType)
                            .Where(e => ids.Contains((TKey)e.Property(key.Name).CurrentValue));
                        foreach (var entry in entries)

Changes to UnitOfWork.cs.

10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
..
32
33
34
35
36
37
38
39


40
41

42
43


44
45
46
47
48
49
50
..
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83

84
85
86
87
88
    public class UnitOfWork : IDisposable
    {
        private readonly MailJanitorContext _db;
        private readonly CancellationToken _cancellationToken;

        public MailJanitorContext DB { get => _db; }

        public UnitOfWork(CancellationToken cancellationToken = default(CancellationToken))
        {
            _cancellationToken = cancellationToken;
            _db = new MailJanitorContext();
            _db.ChangeTracker.LazyLoadingEnabled = false;
            Accounts = new AccountsRepository(_db, _cancellationToken);
            Folders = new FoldersRepository(_db, _cancellationToken);
            FolderMessages = new FolderMessagesRepository(_db, _cancellationToken);
................................................................................
        public FoldersRepository Folders { get; private set; }
        public FolderMessagesRepository FolderMessages { get; private set; }
        public MessagesRepository Messages { get; private set; }
        public ParticipantsRepository Participants { get; private set; }
        public KeywordsRepository Keywords { get; private set; }
        public MessageReferencesRepository MessageReferences { get; private set; }

        public void LoadProperty<T, TProperty>(T entity, Expression<Func<T, IEnumerable<TProperty>>> propertyExpression) where T : class where TProperty : class


        {
            _db.Entry<T>(entity).Collection<TProperty>(propertyExpression);

        }
        public void LoadProperty<T, TProperty>(T entity, Expression<Func<T, TProperty>> propertyExpression) where T : class where TProperty : class


        {
            _db.Entry(entity).Reference(propertyExpression);
        }

        public Task<int> SaveChangesAsync()
        {
            return _db.SaveChangesAsync(_cancellationToken);
................................................................................
                if (disposing)
                {
                    // dispose managed state (managed objects).
                    _db.Dispose();
                }

                // set large fields to null.
                Accounts = null;
                Folders = null;
                FolderMessages = null;
                Messages = null;
                Participants = null;
                Keywords = null;

                _disposedValue = true;
            }
        }

        // This code added to correctly implement the disposable pattern.
        public void Dispose()
        {
            // Do not change this code. Put cleanup code in Dispose(bool disposing) above.
            Dispose(true);

        }
        #endregion

    }
}







|







 







|
>
>

<
>

|
>
>







 







<
<
<
<
<
<










>





10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
..
32
33
34
35
36
37
38
39
40
41
42

43
44
45
46
47
48
49
50
51
52
53
54
..
65
66
67
68
69
70
71






72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
    public class UnitOfWork : IDisposable
    {
        private readonly MailJanitorContext _db;
        private readonly CancellationToken _cancellationToken;

        public MailJanitorContext DB { get => _db; }

        public UnitOfWork(CancellationToken cancellationToken = default)
        {
            _cancellationToken = cancellationToken;
            _db = new MailJanitorContext();
            _db.ChangeTracker.LazyLoadingEnabled = false;
            Accounts = new AccountsRepository(_db, _cancellationToken);
            Folders = new FoldersRepository(_db, _cancellationToken);
            FolderMessages = new FolderMessagesRepository(_db, _cancellationToken);
................................................................................
        public FoldersRepository Folders { get; private set; }
        public FolderMessagesRepository FolderMessages { get; private set; }
        public MessagesRepository Messages { get; private set; }
        public ParticipantsRepository Participants { get; private set; }
        public KeywordsRepository Keywords { get; private set; }
        public MessageReferencesRepository MessageReferences { get; private set; }

        public void LoadProperty<T, TProperty>(T entity, Expression<Func<T, IEnumerable<TProperty>>> propertyExpression)
            where T : class
            where TProperty : class
        {

            _db.Entry(entity).Collection(propertyExpression);
        }
        public void LoadProperty<T, TProperty>(T entity, Expression<Func<T, TProperty>> propertyExpression)
            where T : class
            where TProperty : class
        {
            _db.Entry(entity).Reference(propertyExpression);
        }

        public Task<int> SaveChangesAsync()
        {
            return _db.SaveChangesAsync(_cancellationToken);
................................................................................
                if (disposing)
                {
                    // dispose managed state (managed objects).
                    _db.Dispose();
                }

                // set large fields to null.







                _disposedValue = true;
            }
        }

        // This code added to correctly implement the disposable pattern.
        public void Dispose()
        {
            // Do not change this code. Put cleanup code in Dispose(bool disposing) above.
            Dispose(true);
            GC.SuppressFinalize(this);
        }
        #endregion

    }
}

Changes to Utilities/ConsoleExtensions.cs.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
..
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
...
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
...
146
147
148
149
150
151
152
153
154
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Threading.Tasks;

namespace MC.Utilities
{
    public static class Con
    {
        public static readonly Dictionary<TraceLevel,ConsoleColor> LevelColors = new Dictionary<TraceLevel, ConsoleColor>
        {
            { TraceLevel.Off, ConsoleColor.Black },
            { TraceLevel.Verbose, ConsoleColor.DarkGray },
            { TraceLevel.Info, ConsoleColor.Gray },
            { TraceLevel.Warning, ConsoleColor.Yellow },
................................................................................
                color = ConsoleColor.Gray;
            }
            if (level.HasFlag(TraceLevel.Error | TraceLevel.Warning))
                WriteErr(text, color);
            else
                Write(text, color);
        }
        
        public static void WriteLine(string text, ConsoleColor color)
        {
            Write(text + Environment.NewLine, color);
        }
        public static void Write(string text, ConsoleColor color)
        {
            ConsoleColor oldColor = ConsoleColor.Gray;
................................................................................
                color = ConsoleColor.Gray;
            }
            if (level.HasFlag(TraceLevel.Error | TraceLevel.Warning))
                await WriteErrAsync(text, color);
            else
                await WriteAsync(text, color);
        }
        
        public static async Task WriteLineAsync(string text, ConsoleColor color)
        {
            await WriteAsync(text + Environment.NewLine, color);
        }
        public static async Task WriteLineAsync(Exception ex, ConsoleColor color)
        {
            await WriteLineAsync(ex.ToString(), color);
................................................................................
            await Console.Error.WriteAsync(text);
            if (!System.Console.IsOutputRedirected)
            {
                Console.ForegroundColor = oldColor;
            }
        }

    }	
}







|







 







|







 







|







 







|

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
..
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
...
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
...
146
147
148
149
150
151
152
153
154
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Threading.Tasks;

namespace MC.Utilities
{
    internal static class Con
    {
        public static readonly Dictionary<TraceLevel,ConsoleColor> LevelColors = new Dictionary<TraceLevel, ConsoleColor>
        {
            { TraceLevel.Off, ConsoleColor.Black },
            { TraceLevel.Verbose, ConsoleColor.DarkGray },
            { TraceLevel.Info, ConsoleColor.Gray },
            { TraceLevel.Warning, ConsoleColor.Yellow },
................................................................................
                color = ConsoleColor.Gray;
            }
            if (level.HasFlag(TraceLevel.Error | TraceLevel.Warning))
                WriteErr(text, color);
            else
                Write(text, color);
        }

        public static void WriteLine(string text, ConsoleColor color)
        {
            Write(text + Environment.NewLine, color);
        }
        public static void Write(string text, ConsoleColor color)
        {
            ConsoleColor oldColor = ConsoleColor.Gray;
................................................................................
                color = ConsoleColor.Gray;
            }
            if (level.HasFlag(TraceLevel.Error | TraceLevel.Warning))
                await WriteErrAsync(text, color);
            else
                await WriteAsync(text, color);
        }

        public static async Task WriteLineAsync(string text, ConsoleColor color)
        {
            await WriteAsync(text + Environment.NewLine, color);
        }
        public static async Task WriteLineAsync(Exception ex, ConsoleColor color)
        {
            await WriteLineAsync(ex.ToString(), color);
................................................................................
            await Console.Error.WriteAsync(text);
            if (!System.Console.IsOutputRedirected)
            {
                Console.ForegroundColor = oldColor;
            }
        }

    }
}