Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Store the name of a participant each time it occurs. |
|---|---|
| Timelines: | family | ancestors | descendants | both | dotnet |
| Files: | files | file ages | folders |
| SHA1: |
acd83823df7dd22bed84238add0c6987 |
| User & Date: | tinus 2019-09-07 06:33:18 |
| Wiki: | dotnet |
Context
|
2019-09-10
| ||
| 19:00 | Bugfix: PopulateReferences should now be able to cope with duplicate RfcMessageIDs. check-in: 0f00f13c99 user: tinus tags: dotnet | |
|
2019-09-07
| ||
| 06:33 | Store the name of a participant each time it occurs. check-in: acd83823df user: tinus tags: dotnet | |
|
2019-09-03
| ||
| 20:28 | Bugfix: forgot to await GetFullNamesAsync. check-in: 0670e5cdeb user: tinus tags: dotnet | |
Changes
Changes to MailSynchronizer.cs.
607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 |
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, address, name) in participantList)
{
var msgParticipant = new MessageParticipant
{
Message = targetMsg,
Field = field,
Order = ++order,
};
var key = address ?? name;
if (_participants.TryGetValue(key, out long participantID))
{
msgParticipant.ParticipantID = participantID;
}
else
{
var participant = new Participant
{
Address = address,
Name = name,
|
| > > > > > > > > > > > > > > > > > > |
607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 |
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.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,
|
Changes to Models/MessageParticipant.cs.
28 29 30 31 32 33 34 35 36 |
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; }
}
}
|
> |
28 29 30 31 32 33 34 35 36 37 |
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 Repositories/FoldersRepository.cs.
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 |
public Task<IEnumerable<string>> GetFullNamesAsync(IEnumerable<Folder> folders)
{
return GetFullNamesAsync(folders.Select(f => f.ID));
}
public async Task<IEnumerable<string>> GetFullNamesAsync(IEnumerable<long> ids)
{
string sql = $@"
WITH RECURSIVE FullName(ID, Name, ParentID) AS (
SELECT ID
, Name
, ParentFolderID
FROM Folders
WHERE ID IN ({string.Join(", ", ids)})
UNION
SELECT fn.ID
, pf.Name || '/' || fn.Name
, pf.ParentFolderID
FROM FullName fn
LEFT JOIN Folders pf
ON fn.ParentID = pf.ID
WHERE pf.ID IS NOT NULL
)
SELECT ID
, Name
FROM FullName
WHERE ParentID IS NULL
;";
#pragma warning disable EF1000
var fullNameById = await _set.FromSql<dynamic>(sql)
.ToDictionaryAsync(row => (long)row.ID, row => (string)row.Name);
#pragma warning restore EF1000
return ids.Select(id => fullNameById.GetValueOrDefault(id));
}
}
}
|
| | | | | | | | < | | > | | < < |
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 |
public Task<IEnumerable<string>> GetFullNamesAsync(IEnumerable<Folder> folders)
{
return GetFullNamesAsync(folders.Select(f => f.ID));
}
public async Task<IEnumerable<string>> GetFullNamesAsync(IEnumerable<long> ids)
{
string sql = $@"
WITH RECURSIVE FullNames(ID, Name, ParentID) AS (
SELECT ID
, Name
, ParentFolderID
FROM Folders
WHERE ID IN ({string.Join(", ", ids)})
UNION
SELECT fn.ID
, pf.Name || '/' || fn.Name
, pf.ParentFolderID
FROM FullNames fn
LEFT JOIN Folders pf
ON fn.ParentID = pf.ID
WHERE fn.ParentID IS NOT NULL
)
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
}
}
}
|
Name change from Scripts/Participants with message counts.sql to Scripts/MessageParticipants.sql.
1 2 3 4 5 6 7 8 9 |
SELECT p.Address , p.Name , count(*) AS MessageCount FROM Participants p LEFT JOIN MessageParticipants mp ON p.ID = mp.ParticipantID GROUP BY p.ID ORDER BY MessageCount DESC ; |
| | | |
1 2 3 4 5 6 7 8 9 |
SELECT count(mp.MessageID) AS MessageCount , p.Address , p.Name FROM Participants p LEFT JOIN MessageParticipants mp ON p.ID = mp.ParticipantID GROUP BY p.ID ORDER BY MessageCount DESC ; |
Added Scripts/MessageParticipantsDated.sql.
> > > > > > > > > > > > > |
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT count(mp.MessageID) AS MessageCount , p.Address , p.Name , min(m.Date) AS First , max(m.Date) AS Last FROM Participants p LEFT JOIN MessageParticipants mp ON p.ID = mp.ParticipantID LEFT JOIN Messages m ON mp.MessageID = m.ID GROUP BY p.ID ORDER BY MessageCount DESC ; |