[rt-devel] inconsistent Attachments.Subject DB column content for non ASCII subjects

[Christian Loos]

Hi,

in the attempt to fix [1] I just take a deeper look in which formats the
subject's are saved in the DB (vanilla RT 4.2.9 and MySQL).
Actually they are UTF-8 or MIME encoded.
Is this behavior intentional or is this a bug?

Regarding 29714:
If you reply or comment on a ticket AND change the transaction subject,
the resulting attachment gets the subject set as UTF-8 and this subject
is shown in the ticket history, so far so good.
If you forward a ticket the resulting attachment gets the subject set as
MIME encoded string. As RT::Transaction->Subject doesn't perform a MIME
decoding the subject for a forward transaction is shown as a MIME
encoded string which isn't readable for the user (and maybe confuses
them if they don't know anything about MIME encoding).
Should RT::Transaction->Subject expect an MIME encoded string and decode it?
Or should the content of the Attachments.Subject column always be
consistent UTF-8 (this would result in a huge upgrade step)?

Also, for create, reply and comment EmailRecord attachments (in the
example id's 42, 51 and 56) the SubjectTag is added BEFORE the subject
is MIME encoded.
For Forward EmailRecord attachments (in the example id 70) the
SubjectTag is added AFTER the subject is MIME encoded, which I think is
wrong as the SubjectTag could contain non ASCII characters.


Chris

[1] http://issues.bestpractical.com/Ticket/Display.html?id=29714
-------------- next part --------------
select t.id as t, txn.id as txn, txn.Type as txn_type, a.id as a, a.Parent, a.Subject
from Attachments a
join Transactions txn on (txn.id = a.TransactionId)
join Tickets t on (t.id = txn.ObjectId)
where length(a.Subject) > 0

+---+-----+--------------------+----+--------+--------------------------------------------------------------------------+
| t | txn | txn_type           | a  | Parent | Subject                                                                  |
+---+-----+--------------------+----+--------+--------------------------------------------------------------------------+
| 4 |  62 | Create             | 41 |      0 | umlaut äöü web                                                           |
| 4 |  63 | EmailRecord        | 42 |      0 | =?UTF-8?B?W2xvY2FsICM0XSBBdXRvUmVwbHk6IHVtbGF1dCDDpMO2w7wgd2Vi?=         |
| 4 |  63 | EmailRecord        | 43 |     42 | AutoReply: umlaut äöü web                                                |
| 4 |  71 | Correspond         | 50 |      0 | umlaut äöü web reply subject                                             |
| 4 |  72 | EmailRecord        | 51 |      0 | =?UTF-8?B?W2xvY2FsICM0XSB1bWxhdXQgw6TDtsO8IHdlYiByZXBseSBzdWJqZWN0?=     |
| 4 |  75 | Comment            | 55 |      0 | umlaut äöü web comment subject                                           |
| 4 |  76 | CommentEmailRecord | 56 |      0 | =?UTF-8?B?W2xvY2FsICM0XSB1bWxhdXQgw6TDtsO8IHdlYiBjb21tZW50IHN1YmplY3Q=?= |
| 4 |  79 | EmailRecord        | 61 |      0 | =?UTF-8?B?W2xvY2FsICM0XSB1bWxhdXQgw6TDtsO8IHdlYg==?=                     |
| 4 |  82 | CommentEmailRecord | 66 |      0 | =?UTF-8?B?W2xvY2FsICM0XSB1bWxhdXQgw6TDtsO8IHdlYg==?=                     |
| 4 |  83 | Forward Ticket     | 69 |      0 | =?UTF-8?B?RndkOiB1bWxhdXQgw6TDtsO8IHdlYiBmb3J3YXJk?=                     |
| 4 |  84 | EmailRecord        | 70 |      0 | [local #4] =?UTF-8?B?RndkOiB1bWxhdXQgw6TDtsO8IHdlYiBmb3J3YXJk?=          |
+---+-----+--------------------+----+--------+--------------------------------------------------------------------------+