Email Attachment Storage on FileSystem

[abodner]

Hello,

We have sugar running, and currently receiving about 30-40mb worth of attachments on daily basis. Short to say, the DB size is growing enrmously! I was wondering if it is possible to have Sugar automatically store attachments on the filesystem of the server that we are hosting it on, instead of keeping them in the DB. Any ideas?

[anesupport]

Does anyone know if this is planned for Sugar 5 or 6? Would also like to see Sugar have the ability to store attachments separately on the filesystem, instead of bloating database size.

At least, there should be some way to remove the attachment from the email, keeping only the text of the email.

Also noticed that if we get, say, a piece of spam, via IMAP, and delete it, it still shows up if you grep sugar's emails.MYD database for the subject. Is there a way to permanently delete the email from the database?

[Carol]

We are interested in this feature too and we would be grateful if anyone has any suggestions as to how we can do this.

[Angel]

They are not being stored in the DB per se. The file/attachment itself is being stored in the <sugar install dir>/cache/upload directory, i.e. on the filesystem on the Sugar server.

An entry is automatically made to the database which serves as a reference point to the file, but the file itself is not actually stored in the database.

If you wish to store it elsewhere on the filesystem, you could use symbolic links (on Unix/Linux systems) to point the /upload folder elsewhere. I believe they are called "junctions" in the Windows/NTFS world, but I've never done it on Windows. Similar idea from what I understand.

[al3]

They are not being stored in the DB per se. The file/attachment itself is being stored in the <sugar install dir>/cache/upload directory, i.e. on the filesystem on the Sugar server.

An entry is automatically made to the database which serves as a reference point to the file, but the file itself is not actually stored in the database.

.

Is there a way within Sugar to get rid of the physical attachment. When I deleted one CRM showed it gone, but the physical file was still in the uploads folder.

Thanks.
A

[tjareson]

Is there a way within Sugar to get rid of the physical attachment. When I deleted one CRM showed it gone, but the physical file was still in the uploads folder.
A

I've noticed the same problem. Is there any possibility to "clean up" the attachement folder by finding out all attachments with no reference in the database? This would also keep backups smaller...

kind regards
Tjareson

[al3]

I'm not a PHP expert but this should be a slam-dunk easy thing to put into the admin page... a button to "clean up" deleted documents and purge them from the DB. Or better yet just add some code so that when the user deletes them individually to delete the physical file as well. I can't believe the developers have engineered the system to keep what could be tons of space-wasting "stuff" in the DB... making backup files much larger... when it is not necessary.

Al

[mylo78]

Which table is the email attachment reference kept in?

Nothing in the database has an attachment

emails;
+------------------+--------------+------+-----+---------+-------+
| Field            | Type         | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| id               | char(36)     | NO   | PRI |         |       | 
| date_entered     | datetime     | NO   |     |         |       | 
| date_modified    | datetime     | NO   |     |         |       | 
| assigned_user_id | char(36)     | YES  | MUL | NULL    |       | 
| modified_user_id | char(36)     | YES  |     | NULL    |       | 
| created_by       | char(36)     | YES  |     | NULL    |       | 
| deleted          | tinyint(1)   | NO   |     | 0       |       | 
| date_sent        | datetime     | YES  |     | NULL    |       | 
| message_id       | varchar(255) | YES  | MUL | NULL    |       | 
| name             | varchar(255) | YES  | MUL | NULL    |       | 
| type             | varchar(25)  | YES  |     | NULL    |       | 
| status           | varchar(25)  | YES  |     | NULL    |       | 
| intent           | varchar(25)  | YES  |     | pick    |       | 
| mailbox_id       | char(36)     | YES  |     | NULL    |       | 
| parent_type      | varchar(25)  | YES  |     | NULL    |       | 
| parent_id        | char(36)     | YES  | MUL | NULL    |       | 
| flagged          | tinyint(1)   | YES  |     | NULL    |       | 
| reply_to_status  | tinyint(1)   | YES  |     | NULL    |       | 
+------------------+--------------+------+-----+---------+-------+


email_cache
+------------+------------------+------+-----+---------+-------+
| Field      | Type             | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+-------+
| ie_id      | char(36)         | NO   | MUL |         |       | 
| mbox       | varchar(60)      | NO   |     |         |       | 
| subject    | varchar(255)     | YES  | MUL | NULL    |       | 
| fromaddr   | varchar(100)     | YES  |     | NULL    |       | 
| toaddr     | varchar(255)     | YES  |     | NULL    |       | 
| senddate   | datetime         | NO   |     |         |       | 
| message_id | varchar(255)     | YES  |     | NULL    |       | 
| mailsize   | int(10) unsigned | NO   |     |         |       | 
| imap_uid   | int(10) unsigned | NO   |     |         |       | 
| msgno      | int(10) unsigned | YES  |     | NULL    |       | 
| recent     | tinyint(4)       | NO   |     |         |       | 
| flagged    | tinyint(4)       | NO   |     |         |       | 
| answered   | tinyint(4)       | NO   |     |         |       | 
| deleted    | tinyint(4)       | NO   |     |         |       | 
| seen       | tinyint(4)       | NO   |     |         |       | 
| draft      | tinyint(4)       | NO   |     |         |       | 
+------------+------------------+------+-----+---------+-------+

email_addr_bean_rel ;
+------------------+-------------+------+-----+---------+-------+
| Field            | Type        | Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------+-------+
| id               | char(36)    | NO   | PRI |         |       | 
| email_address_id | char(36)    | NO   | MUL |         |       | 
| bean_id          | char(36)    | NO   | MUL |         |       | 
| bean_module      | varchar(25) | NO   |     |         |       | 
| primary_address  | tinyint(1)  | YES  |     | 0       |       | 
| reply_to_address | tinyint(1)  | YES  |     | 0       |       | 
| date_created     | datetime    | YES  |     | NULL    |       | 
| date_modified    | datetime    | YES  |     | NULL    |       | 
| deleted          | tinyint(1)  | YES  |     | 0       |       | 
+------------------+-------------+------+-----+---------+-------+

email_addresses;
+--------------------+--------------+------+-----+---------+-------+
| Field              | Type         | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| id                 | char(36)     | NO   | PRI |         |       | 
| email_address      | varchar(255) | NO   | MUL |         |       | 
| email_address_caps | varchar(255) | NO   | MUL |         |       | 
| invalid_email      | tinyint(1)   | YES  |     | 0       |       | 
| opt_out            | tinyint(1)   | YES  |     | 0       |       | 
| date_created       | datetime     | YES  |     | NULL    |       | 
| date_modified      | datetime     | YES  |     | NULL    |       | 
| deleted            | tinyint(1)   | YES  |     | 0       |       | 
+--------------------+--------------+------+-----+---------+-------+

email_cache
+------------+------------------+------+-----+---------+-------+
| Field      | Type             | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+-------+
| ie_id      | char(36)         | NO   | MUL |         |       | 
| mbox       | varchar(60)      | NO   |     |         |       | 
| subject    | varchar(255)     | YES  | MUL | NULL    |       | 
| fromaddr   | varchar(100)     | YES  |     | NULL    |       | 
| toaddr     | varchar(255)     | YES  |     | NULL    |       | 
| senddate   | datetime         | NO   |     |         |       | 
| message_id | varchar(255)     | YES  |     | NULL    |       | 
| mailsize   | int(10) unsigned | NO   |     |         |       | 
| imap_uid   | int(10) unsigned | NO   |     |         |       | 
| msgno      | int(10) unsigned | YES  |     | NULL    |       | 
| recent     | tinyint(4)       | NO   |     |         |       | 
| flagged    | tinyint(4)       | NO   |     |         |       | 
| answered   | tinyint(4)       | NO   |     |         |       | 
| deleted    | tinyint(4)       | NO   |     |         |       | 
| seen       | tinyint(4)       | NO   |     |         |       | 
| draft      | tinyint(4)       | NO   |     |         |       | 
+------------+------------------+------+-----+---------+-------+

emailman;
+------------------+--------------+------+-----+---------+----------------+
| Field            | Type         | Null | Key | Default | Extra          |
+------------------+--------------+------+-----+---------+----------------+
| date_entered     | datetime     | YES  |     | NULL    |                | 
| date_modified    | datetime     | YES  |     | NULL    |                | 
| user_id          | char(36)     | YES  |     | NULL    |                | 
| id               | int(11)      | NO   | PRI | NULL    | auto_increment | 
| campaign_id      | char(36)     | YES  | MUL | NULL    |                | 
| marketing_id     | char(36)     | YES  |     | NULL    |                | 
| list_id          | char(36)     | YES  | MUL | NULL    |                | 
| send_date_time   | datetime     | YES  |     | NULL    |                | 
| modified_user_id | char(36)     | YES  |     | NULL    |                | 
| in_queue         | tinyint(1)   | YES  |     | 0       |                | 
| in_queue_date    | datetime     | YES  |     | NULL    |                | 
| send_attempts    | int(11)      | YES  |     | 0       |                | 
| deleted          | tinyint(1)   | YES  |     | 0       |                | 
| related_id       | char(36)     | YES  | MUL | NULL    |                | 
| related_type     | varchar(100) | YES  |     | NULL    |                | 
+------------------+--------------+------+-----+---------+----------------+

emails_beans;
+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| id            | varchar(36) | NO   | PRI |         |       | 
| email_id      | varchar(36) | YES  | MUL | NULL    |       | 
| bean_id       | varchar(36) | YES  | MUL | NULL    |       | 
| bean_module   | varchar(36) | YES  |     | NULL    |       | 
| campaign_data | text        | YES  |     | NULL    |       | 
| date_modified | datetime    | YES  |     | NULL    |       | 
| deleted       | tinyint(1)  | NO   |     | 0       |       | 
+---------------+-------------+------+-----+---------+-------+

emails_email_addr_rel ;
+------------------+------------+------+-----+---------+-------+
| Field            | Type       | Null | Key | Default | Extra |
+------------------+------------+------+-----+---------+-------+
| id               | char(36)   | NO   | PRI |         |       | 
| email_id         | char(36)   | NO   | MUL |         |       | 
| address_type     | varchar(4) | NO   |     |         |       | 
| email_address_id | char(36)   | NO   | MUL |         |       | 
| deleted          | tinyint(1) | YES  |     | 0       |       | 
+------------------+------------+------+-----+---------+-------+

emails_text ;
+------------------+--------------+------+-----+---------+-------+
| Field            | Type         | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| email_id         | varchar(36)  | NO   | PRI |         |       | 
| from_addr        | varchar(255) | YES  | MUL | NULL    |       | 
| to_addrs         | text         | YES  |     | NULL    |       | 
| cc_addrs         | text         | YES  |     | NULL    |       | 
| bcc_addrs        | text         | YES  |     | NULL    |       | 
| description      | longtext     | YES  |     | NULL    |       | 
| description_html | longtext     | YES  |     | NULL    |       | 
| raw_source       | longtext     | YES  |     | NULL    |       | 
| deleted          | tinyint(1)   | YES  |     | 0       |       | 
| reply_to_addr    | varchar(255) | YES  |     | NULL    |       | 
+------------------+--------------+------+-----+---------+-------+

[al3]

Attachments are kept in a folder where the file name is changed to some kind of unique hashed gibberish name... which is used as a link.

I forget which folder, but if you hunt around you will find them.

Only the links (file name) are kept in the database. Putting files in a database is a huge waste of space and is never done on well-engineered systems.

Al

[mylo78]

It's in cache/modules/Emails/<user_id>/attachments from memory.

I want the link to this attachment - wherever it is stored.
I'm chasing up why some attachments only come through as a number, and not their original filename.