Lets take a closer look at how the latter part works in this in-depth analysis of the e-mail folders system of SugarCRM's e-mail client.
To begin, lets take a look at what the default e-mail folder configuration looks like:
- My Email
- My Drafts
- My Sent Email
Ironically, there are occasions on which the folders will disappear through no action of the user, thus making it difficult to access the e-mail messages they contain.
Troubleshooting this problem helps one obtain a good understanding of the underlying architecture of the e-mail folders system, so lets dive in and take a look.
First and foremost, to troubleshoot this type of issue we need direct access to the database. Folders utilize three different tables within the SugarCRM database.
The three tables are:
folders_rel contains the relationship information pertaining to which folder houses a particular message. It is only used for messages stored in user created folders. If an incoming e-mail is not stored in a user created folder, it is automatically stored in the My Email folder and an entry for that message is not made within folders_rel.
folders_subscriptions ties the folders table to the currently logged in user. It contains the relationship information pertaining to the folders that should be displayed for each user.
Here is what the relationships look like graphically:
Given that we are interested in determining what happened to our default system folders, the first table we will want to examine is: folders. This will help us ensure that the corresponding folder record actually exists. If it does not -- say we are missing the record for the My Email folder -- one can correct the issue by simply copying the My Email record of another user, then updating the following columns on the copy of the record to reflect the ID of the user experiencing the problem:
If it doesn't exist, it can be easily recreated manually. Simply retrieved the ID column value of the My Email folder record and create the entry. Suppose that the corresponding ID value is '123456' and that the user that should be seeing that folder has an ID value of 'abcdef', this is how we could create the folders_subscriptions record to correct the problem:
- INSERT INTO folders_subscriptions (newid(), '123456', 'abcdef')
- INSERT INTO folders_subscriptions(uuid(), '123456', 'abcdef')
While folders may be displaying correctly, their contents may actually be incorrect, or most likely, empty.
To associate an individual message with a specific folder, the message must have an entry in the folders_rel table. Upon initial inspection of folders_rel, one might notice two oddly named columns: polymorphic_module and polymorphic_id. The first is the name of the module that contains the message, i.e. 'Emails', and the latter is the ID value of the record from said module. As a side note, the polymorphic_module value is always 'Emails.'
Notice as well there is a column for a folder_id value. That is extracted from the folders table and that is how SugarCRM knows which messages should be displayed in the various folder.
Note that the folders_rel table is blank by default. SugarCRM only uses this table for messages stored in a folder other than the default system folders (My Email, Drafts, Sent Email).