Check-in [acd83823df]

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: acd83823df7dd22bed84238add0c69876b77691e
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
;