[Vtigercrm-developers] Bizzare database design for Emails - what is going on?

[Dennis Grant]

OK, so my customer asked that attachments to Emails show up in the
attachments list for both the associated Contact and the Contact's
Account.

I had already done something similar for "normal" attachments where if
you make an attachment to a Contact, it is added to the list of
Attachments for the Account as well.

That was pretty simple - look up the AccountID associated with the
Contact, and then add another line to seattachmentsrel with the
AccountID and the AttachmentID.

I was expecting to do something similar here.

First, however, it took a while to figure out where the Email
attachments were getting added into seattachmentsrel. That turns out to
be buried in a function in data/CRMEntity.php But that is when I made an
astounding discovery:

1) The email table contains:
mysql> describe emails;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| emailid     | int(19)     |      | PRI | 0       |       |
| filename    | varchar(50) | YES  |     | NULL    |       |
| description | longtext    | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+

Oddly, no Subject field... and the body of the email is in
"description".

But then I discover that "description" is carried over into the
"description" field of the associated CRMentity, and in the Name field
of the associated attachment!

WHAT?

Check this out: (dashes deleted for clarity)

mysql> select description from emails where emailid=15776;

| description 
 

| Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Curabitur
placerat nisi in libero. Mauris egestas dolor nec sem. Vestibulum leo
nisl, egestas in, adipiscing dignissim, consectetuer sed, urna. Donec
imperdiet risus in eros. Vivamus metus sapien, commodo eget, tempus at,
venenatis et, leo. Vestibulum laoreet cursus dui. Ut vitae nisi.
Pellentesque lacinia. Fusce dui. Nunc accumsan. Praesent ante risus,
vehicula quis, sagittis at, pretium sit amet, est. Aliquam lectus.
Mauris ac tellus eu mauris viverra vestibulum. |

1 row in set (0.01 sec)

mysql> select description from crmentity where crmid=15776;

| description                   
 

| Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Curabitur
placerat nisi in libero. Mauris egestas dolor nec sem. Vestibulum leo
nisl, egestas in, adipiscing dignissim, consectetuer sed, urna. Donec
imperdiet risus in eros. Vivamus metus sapien, commodo eget, tempus at,
venenatis et, leo. Vestibulum laoreet cursus dui. Ut vitae nisi.
Pellentesque lacinia. Fusce dui. Nunc accumsan. Praesent ante risus,
vehicula quis, sagittis at, pretium sit amet, est. Aliquam lectus.
Mauris ac tellus eu mauris viverra vestibulum. |

1 row in set (0.01 sec)

mysql> select attachmentsid from seattachmentsrel where crmid=15776;
+---------------+
| attachmentsid |
+---------------+
|         15777 |
+---------------+
1 row in set (0.01 sec)

mysql> select description from attachments where attachmentsid=15777;

| description   
 

| Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Curabitur
placerat nisi in libero. Mauris |

1 row in set (0.00 sec)

WTF?

Surely this cannot be correct?

It would make much more sense to put the email Subject line (wherever
that is stored...) in the description fields of the crmentity and the
attachment....

DG