Check-in [b5185ef288]

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

Overview
Comment:Move retrieval of existing messageIDs to FolderMessages repository.
Timelines: family | ancestors | descendants | both | dotnet
Files: files | file ages | folders
SHA1: b5185ef288b790633ce29ebf42edea8ac2134d72
User & Date: tinus 2019-09-13 18:54:26
Wiki:dotnet
Context
2019-09-13
18:56
More logical population of message-related things. check-in: 94dd79783d user: tinus tags: dotnet
18:54
Move retrieval of existing messageIDs to FolderMessages repository. check-in: b5185ef288 user: tinus tags: dotnet
2019-09-10
19:32
Added repository for MessageReferences. check-in: e1327afe6c user: tinus tags: dotnet
Changes

Changes to MailSynchronizer.cs.

157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172

173
174
175
176
177
178
179
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
216
217
218
219
220
221
222
...
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
...
393
394
395
396
397
398
399






















































400
401
402
403
404
405
406
...
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
...
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
...
645
646
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
...
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
                Con.WriteLineErr("\t!!! No access to folder!", ConsoleColor.Red);
                return;
            }
            try
            {
                // Get the entire list of UIDs for this folder
                Con.WriteLine("\tFetching list of message UIDs...", TraceLevel.Verbose);
                Dictionary<ulong, (long? folderMsgID, long? messageID)> messageIDs;
                var summaries = await mailFolder.FetchAsync(0, -1, MessageSummaryItems.UniqueId
                                                                   | MessageSummaryItems.Flags
                                                                   | MessageSummaryItems.InternalDate
                                                                   | MessageSummaryItems.Size
                                                                   | MessageSummaryItems.GMailMessageId
                                                                   | MessageSummaryItems.GMailLabels,
                                                            _cancellationToken);


                using (var work = new UnitOfWork(_cancellationToken))
                {
                    IQueryable<FolderMessage> folderMessages = work.DB.FolderMessages
                        .Where(folderMsg => folderMsg.FolderID == dbFolder.ID);

                    // Check for deleted messages, and delete them from the database
                    Con.WriteLine("\tChecking for deleted messages...", TraceLevel.Verbose);
                    var uidsOnServer = summaries.Select(item => item.UniqueId.Id);
                    var deletedMessageIDs = await folderMessages
                        .Where(fm => !uidsOnServer.Contains(fm.UID))
                        .Select(fm => fm.ID)
                        .ToListAsync(_cancellationToken);
                    if (deletedMessageIDs.Any())
                    {
                        Con.WriteLine($"\tDeleting {deletedMessageIDs.Count} messages.", TraceLevel.Verbose);
                        work.FolderMessages.DeleteByIDs(deletedMessageIDs);
                        _haveDeleted = true;
                    }

................................................................................
                    // Skip the messages we downloaded before...
                    uint maxUID = await work.FolderMessages.GetMaxUIDAsync(dbFolder);
                    int oldCount = summaries.Count;
                    summaries = summaries.Where(s => s.UniqueId.Id > maxUID).ToList();
                    _totalCountAllFolders -= (oldCount - summaries.Count);

                    Con.WriteLine("\tMatching up with existing messages...", TraceLevel.Verbose);
                    var globalIDs = summaries.Select(s => s.GMailMessageId.Value);
                    var idSets = from dbMessage in work.DB.Messages
                                 where globalIDs.Contains(dbMessage.GlobalID.Value)
                                 join folderMsg in folderMessages
                                      on dbMessage.ID equals folderMsg.MessageID
                                      into folderMsgs
                                 from folderMsg in folderMsgs.DefaultIfEmpty()
                                 select new
                                 {
                                     globalID = dbMessage.GlobalID.Value,
                                     folderMsgID = folderMsg == null ? 0 : folderMsg.ID,
                                     messageID = dbMessage == null ? 0 : dbMessage.ID,
                                 };
                    messageIDs = await idSets.ToDictionaryAsync(idSet => idSet.globalID,
                                                idSet => (idSet.folderMsgID == 0 ? (long?)null : idSet.folderMsgID,
                                                          idSet.messageID == 0 ? (long?)null : idSet.messageID),
                                                _cancellationToken);
                }

                int totalCount = summaries.Count, counter = 0;
                Con.WriteLine($"\tFetching {totalCount} new and/or updated messages...", TraceLevel.Verbose);
                DateTime start = DateTime.UtcNow;
                DateTime lastProgressUpdate = DateTime.MinValue;
                foreach (var summary in summaries)
................................................................................

                    // Otherwise, check out the existing message
                    Message dbMessage = null;
                    if (messageID != null)
                    {
                        dbMessage = await work.Messages.GetByIdAsync(messageID.Value);
                    }

                    if (folderMsgID == null
                        && dbMessage != null
                        && summary.Flags == dbMessage.Flags
                        && summary.InternalDate == dbMessage.DateReceived
                        && string.Join(", ", summary.Keywords) == dbMessage.Keywords)
                    {
                        var folderMessage = new FolderMessage
                        {
                            FolderID = folderID,
                            UniqueId = summary.UniqueId,
                            Message = dbMessage,
                        };
                        await work.FolderMessages.AddAsync(folderMessage);
                    }
                    else if (dbMessage == null
                            || folderMsgID == null
                            || summary.Flags != dbMessage.Flags
                            || summary.InternalDate != dbMessage.DateReceived
                            || string.Join(", ", summary.Keywords) != dbMessage.Keywords)
                    {
                        MimeMessage mailMessage;
                        try

                        {
                            mailMessage = await mailFolder.GetMessageAsync(summary.UniqueId, _cancellationToken);
                        }
                        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 && !line.StartsWith(' ') && !line.StartsWith('\t'))
                                    {
                                        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);
                                }
                            }
                        }
                        // save message to database
                        var 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
................................................................................
                        throw; // those need to fall through
                    }
                    await Con.WriteLineAsync();
                    await Con.WriteLineAsync(ex);
                }
            }
        }























































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

................................................................................
                }
            }
        }

        private async Task PopulateMessageAsync(UnitOfWork work, Message targetMsg, MimeMessage sourceMsg,
                                                IEnumerable<string> keywords, IEnumerable<string> labels)
        {
            targetMsg.RfcMessageID = sourceMsg.ResentMessageId ?? sourceMsg.MessageId;
            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);
................................................................................
            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.Trim(' ', '\'', '"', '<', '>', '(', ')') == address)
                    name = null;

                var msgParticipant = new MessageParticipant
                {
                    Message = targetMsg,
                    Field = field,
                    Order = ++order,
................................................................................
                }
                else
                {
                    var participant = new Participant
                    {
                        Address = address,
                        Name = name,
                        Messages = new List<MessageParticipant>(),
                    };
                    await work.Participants.AddAsync(participant);
                    // We only know the IDs after saving the entities
                    await work.SaveChangesAsync();
                    _participants.TryAdd(key, participant.ID);

                    msgParticipant.Participant = participant;
                }

                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 pairs = allKeywords.Select(mailKeyword => (mailKeyword: mailKeyword,

                                                           keywordID: _keywords.GetValueOrDefault(mailKeyword, 0)));
            var keywordSets = from pair in pairs
                              join dbMsgKeyword in work.DB.MessageKeywords.Where(mk => mk.Message == targetMsg)
                                   on pair.keywordID equals dbMsgKeyword.KeywordID
                                   into msgKeywordSet
                              from dbMsgKeyword in msgKeywordSet.DefaultIfEmpty()
                              select (pair.mailKeyword.Name,

                                      pair.mailKeyword.IsLabel,
                                      msgKeywordID: dbMsgKeyword == null ? (long?)null : dbMsgKeyword.ID,
                                      keywordID: pair.keywordID == 0 ? (long?)null : pair.keywordID);

            int index = 0;
            foreach ((string mailKeyword, bool isLabel, long? msgKeywordID, long? keywordID) in keywordSets)
            {
                index++;
                if (msgKeywordID == null)
                {
                    long kwID = keywordID ?? 0;
                    if (kwID == 0)
                    {
                        var keyword = new Keyword
                        {
                            Name = mailKeyword,
                            IsLabel = isLabel
                        };
                        await work.Keywords.AddAsync(keyword);
                        await work.SaveChangesAsync();
                        kwID = keyword.ID;
                        _keywords.Add((mailKeyword, isLabel), kwID);
                    }
                    await work.DB.MessageKeywords.AddAsync(new MessageKeyword
                    {
                        Message = targetMsg,
                        Order = index,
                        KeywordID = kwID,
                    }, _cancellationToken);
                }
            }
        }

        /// <summary>
        /// Store any references between messages.
................................................................................
            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);
            int index = 0;
            foreach (string mailRef in sourceReferences)
            {
                index++;
                MessageReference dbReference = dbMessageRefs.GetValueOrDefault(mailRef, null);
                long? dbMessageID = null;
                if (dbReferencedMessageIDs.Contains(mailRef))







<







<
>


<
<




|
|
|
|







 







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







 







>






|








<




|
|
>

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







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







|







 







|







 







<






|













|
>
|
|

|


<
>
|

<
>

|




|
|








|
|





|







 







|







157
158
159
160
161
162
163

164
165
166
167
168
169
170

171
172
173


174
175
176
177
178
179
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
...
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
...
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
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
...
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
...
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
...
637
638
639
640
641
642
643

644
645
646
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
...
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
                Con.WriteLineErr("\t!!! No access to folder!", ConsoleColor.Red);
                return;
            }
            try
            {
                // Get the entire list of UIDs for this folder
                Con.WriteLine("\tFetching list of message UIDs...", TraceLevel.Verbose);

                var summaries = await mailFolder.FetchAsync(0, -1, MessageSummaryItems.UniqueId
                                                                   | MessageSummaryItems.Flags
                                                                   | MessageSummaryItems.InternalDate
                                                                   | MessageSummaryItems.Size
                                                                   | MessageSummaryItems.GMailMessageId
                                                                   | MessageSummaryItems.GMailLabels,
                                                            _cancellationToken);

                Dictionary<ulong, (long? folderMsgID, long? messageID)> messageIDs;
                using (var work = new UnitOfWork(_cancellationToken))
                {



                    // Check for deleted messages, and delete them from the database
                    Con.WriteLine("\tChecking for deleted messages...", TraceLevel.Verbose);
                    var uidsOnServer = summaries.Select(item => item.UniqueId.Id);
                    var deletedMessageIDs = work.FolderMessages
                        .Select(fm => fm.FolderID == dbFolder.ID && !uidsOnServer.Contains(fm.UID),
                                fm => fm.ID)
                        .ToList();
                    if (deletedMessageIDs.Any())
                    {
                        Con.WriteLine($"\tDeleting {deletedMessageIDs.Count} messages.", TraceLevel.Verbose);
                        work.FolderMessages.DeleteByIDs(deletedMessageIDs);
                        _haveDeleted = true;
                    }

................................................................................
                    // Skip the messages we downloaded before...
                    uint maxUID = await work.FolderMessages.GetMaxUIDAsync(dbFolder);
                    int oldCount = summaries.Count;
                    summaries = summaries.Where(s => s.UniqueId.Id > maxUID).ToList();
                    _totalCountAllFolders -= (oldCount - summaries.Count);

                    Con.WriteLine("\tMatching up with existing messages...", TraceLevel.Verbose);
                    IEnumerable<ulong> globalIDs = summaries.Select(s => s.GMailMessageId.Value);
                    messageIDs = await work.FolderMessages.GetIDsByGlobalIDAsync(dbFolder.ID, globalIDs);















                }

                int totalCount = summaries.Count, counter = 0;
                Con.WriteLine($"\tFetching {totalCount} new and/or updated messages...", TraceLevel.Verbose);
                DateTime start = DateTime.UtcNow;
                DateTime lastProgressUpdate = DateTime.MinValue;
                foreach (var summary in summaries)
................................................................................

                    // 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
................................................................................
                        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
            {
                mailMessage = await mailFolder.GetMessageAsync(summary.UniqueId, _cancellationToken);
            }
            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);

................................................................................
                }
            }
        }

        private async Task PopulateMessageAsync(UnitOfWork work, Message targetMsg, MimeMessage sourceMsg,
                                                IEnumerable<string> keywords, IEnumerable<string> labels)
        {
            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);
................................................................................
            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,
................................................................................
                }
                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.
................................................................................
            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))

Changes to Repositories/FolderMessagesRepository.cs.


1
2
3
4
5
6
7
..
15
16
17
18
19
20
21
22













23







using System.Linq;
using System.Threading;
using System.Threading.Tasks;
using MailJanitor.Models;
using Microsoft.EntityFrameworkCore;

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













}






>







 







|
>
>
>
>
>
>
>
>
>
>
>
>
>
|
>
>
>
>
>
>
1
2
3
4
5
6
7
8
..
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 System.Collections.Generic;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;
using MailJanitor.Models;
using Microsoft.EntityFrameworkCore;

namespace MailJanitor.Repositories
................................................................................
        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);
        }
    }
}

Changes to Repositories/Repository.cs.

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
        {
            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.First();
                    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 => numericIDs.Contains((long)e.Property(key.Name).CurrentValue));
                        foreach (var entry in entries)
                        {
                            entry.State = EntityState.Detached;
                        }
                        return numDeleted;
                    }
                }







|








|







|







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
        {
            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)
                        {
                            entry.State = EntityState.Detached;
                        }
                        return numDeleted;
                    }
                }