Outsource Attachments

SquashTM allows you to store attachments outside the database, directly in a files system. By default, attachments are stored within the database, since it is simpler. Indeed, it is very convenient for storing standard data volumes.

In a new SquashTM install, you simply have to input this storage mode in the settings. In an already existing SquashTM, you first have to extract the existing attachments before changing the settings.

Focus

The change of storage from database to files system has consequences on data saving. Thus, the administrator must make these necessary changes:

Organize an attachments-saving system that is synchronous with the database system;
Free up enough space to store the attachments and monitor the evolution of the space available;
Give reading and writing privileges to the user starting the SquashTM process on the folder that is going to receive the attachments.

Configure Settings

Warning

If there are already existing attachments in the database, you must first extract them BEFORE changing SquashTM settings.

To configure settings, considering ~SQUASH as SquashTM's deployment folder:

Stop SquashTM and edit the file squash.tm.cfg.properties in the directory ~SQUASH/conf;
Add the following property to activate the external storage of attachments:
```
squashtm.feature.file.repository=true
```
Add the following property to define the storage location of the attachments:
```
squash.path.file.repository=<Path chosen for file storage> 
```
By default, if this property is absent, SquashTM will store the attachments in the repository ~SQUASH/attachments. However, this step is necessary once you extract attachments from the database;
Make all the necessary system changes, especially if your chosen path is not the default path. (This is especially the case when you give permissions to a user, who must be able to write and change the process in both SquashTM and the OS);
Restart SquashTM;
Add a new attachment from SquashTM and verify that it is stored in your chosen location. Verify that you can properly download and delete the attachment.

Focus

Once the attachments are stored in a files system, you cannot go back to a database storage unless you are willing to lose all the attachments you have stored on that external repository.

Extract Existing Attachments

To go from a database storage to a files system storage already in SquashTM, you must migrate the database attachments to the file format expected by SquashTM.

You can use this tool provided to you by Henix to help you migrate, available on the Downloads page. The artefact to use is named migration-extract-attachment.

Preconditions

Warning

You must save the database BEFORE starting the migration to be able to restore in case there is a problem.

Linux Procedure

To extract the existing attachments from a SquashTM database to a Linux files system:

Considering ~SquashTM as SquashTM's deployment folder:

Stop SquashTM;
Extract the tool migration-extract-attachment to SquashTM's root repository ~SQUASH;
Open the file extract-attachments.sh in a text editor;
Search for the following lines:
```
DB_TYPE=
DB_URL=
DB_USERNAME=
DB_PASSWORD=
```
Change them with the required values to access SquashTM's database. They must be identical to the ones in the configuration file (squash.tm.cfg.properties, squash.tm.cfg-mariadb.properties, or squash.tm.cfg-postgresql.properties depending on where database accesses are configured) ;
Search for the following line:
```
REPO_PATH=./attachments
```
Edit the property to indicate the path in which the attachments will be extracted. If this property value is not modified, the attachments will directly be extracted in (~SQUASH/attachments), which also is the default path in which SquashTM will get them after the migration;
Execute the file extract-attachments.sh. The migration automatically starts. Here are its three phases:
- Extracting attachments;
- Verifying the extracted files;
- Deleting the attachments in the database.
Once the migration is over, configure the file squash.tm.cfg.properties as written in Configuration.

Windows Procedure

To extract existing attachments from a SquashTM database to a files system in Windows:

Considerring ~SquashTM as SquashTM's deployment folder:

Stop SquashTM;
Extract the tool migration-extract-attachment to SquashTM's root repository ~SQUASH;
Open the file extract-attachments.bat in a text editor;
Search for the following lines:
```
set DB_TYPE=
set DB_URL=
set DB_USERNAME=
set DB_PASSWORD=
```
Change them with the required values to access SquashTM's database. They must be identical to the ones in the configuration file (squash.tm.cfg.properties, squash.tm.cfg-mariadb.properties, or squash.tm.cfg-postgresql.properties depending on where database accesses are configured) ;
Search for the following line:
```
set REPO_PATH=./attachments
```
Edit the property to indicate the path in which the attachments will be extracted. If this property value is not modified, the attachments will directly be extracted in (~SQUASH/attachments), which also is the default path in which SquashTM will get them after the migration;
Execute the file extract-attachments.bat. The migration automatically starts. Here are its three phases:
- Extracting attachments;
- Verifying the extracted files;
- Deleting the attachments in the database.
Once the migration is over, configure the file squash.tm.cfg.properties as written in Configuration.

Post migration

Depending on the RDBMS, it might be necessary to perform a maintenance operation on the database using SQL queries to free the disk space taken by the attachments, even if the migrator has deleted them.

For a PostgreSQL database, use the programs vacuumlo or vacuumdb to permanently delete large objects.
For MariaDB, use optimize table from the Table Inspector tool to permanently delete large objects.

Please read the RDBMS documentation to learn more.