Attachments database

Applies to version: 2020.1.x and above; author: Dawid Golonka

Introduction

In the workflows designed in WEBCON BPS, you can enable users to add attachments – e.g. .pdf, .docx, .xlsx, tiif, and zip. Their storage location is a particularly important aspect of the system configuration. When organizing your document management system, you can decide to create a separate database to store the attachments.

What is the attachments database and why is it created?

An attachments database is a dedicated database that stores the attachment files. After creation, the database will be connected to the selected content database. The attachments database allowing for separating the place of storing files from the business and process configuration data.

Why the attachments database is created?

• Due to the number and size of attachments for the selected processes (e.g. invoice cost, contracts), the size of the database may reach even several Terabytes. The backup/recovery procedures for such databases are very time consuming. Division into a dedicated database allows you to simplify the procedures and save time.
• Unlike the main database containing metadata and entire instance history (lots of operations I/O), the attachments databases are characterized by a large volume but the relatively small number of operations I/O. While the fast disc systems (e.g. SSD) are required for the main database, the attachments database can be maintained on relatively slow and cheaper discs.

Construction of an Attachments database

An external attachments database consists of two tables – the “GlobalParameters” table contains a database configuration, while the “WFAttachmentFiles” stores the attachment files.

Fig. 1. Construction of external attachments database

The most important columns in the “WFAttachmentFIles” table are:

• ATF_ID – a row ID
• ATF_ATTID – a relation to the WFDataAttachmets table in the content database
• ATF_WFDID – an instance ID
• ATF_Value – a content of the attachment in the code form
• ATF_FileType – a file format
• ATF_OrginalName – a file name

How to create an attachments database?

The attachments database can be created during the installation of the WEBCON BPS environment on the server. This can be done after creating the content database, on the “Content database creation” step. In the upper part of the window there is the ability of changing the connection parameters with the configuration database. The bottom part allows for selecting a process database that will be connected with the attachments database and creating a new database by entering the user-selected name.

Fig. 2. Creating the attachments database during installation WEBCON BPS

The attachments database can be added to the existing installation – go to the WEBCON BPS installer and select the “Tools for application management”.

Fig. 3. The installer of WEBCON BPS

Next, go to the “Database creation” step, select the “Attachments databases” section and then the “Create attachment database” option.

Fig. 4. Adding a new attachment database

In the next tab select the information about the created database, language and method of connection to the configuration database.

Fig. 5. Adding a new attachments database

How to remove the attachments database?

When you want to remove a database, go to the WEBCON BPS installer and in the “Database creation” tab select the existing database, and click the “X” button on the right.

Fig. 6. Removing the attachments database

Is the attachments database available for every WEBCON BPS license?
No. The ability of creating a separate attachments database is available only in the “Enterprise” version. The “Express” version does not support this functionality.

How many attachments database can I have?
You can create multiple databases, however, only one attachments database can be assigned to each process.

How to assign a database to the process?
Each newly created process is assigned a default database which is set in the global system configuration. A list of all available databases and the ability of selecting one of them can be found in the “System configuration” window by selecting the “Global parameters” -> “Content and attachments databases” option.

Fig. 7. Setting of default attachments database

There is also the ability of defining the default database for the selected process. After selecting the process, go to the “Settings” tab and change the attachments database in the “Attachments database” section.

Fig. 8. Changing of the database in the process

For each process you can select only one attachments database.

Change of the attachments database in the existing processes

When can I change the attachments database?
For processes in which attachments have not yet been added, a database change is available from the WEBCON BPS Designer Studio level. Once attachments have been added in the process, you will not be able to change the attachments database in Designer Studio. There is then a need to manually migrate attachments to the new database including updating data in the process definition (more on migration in section “c”).

Can an attachment database be assigned to a process that already exists?
Yes, if there no attachments added to the existing process. Otherwise, the change can not be possible from the Designer Studio level and you must migrate the attachments (more on migration in section “c”).

Attachments migration – how does it work?
The attachments data is stored in the WFDataAttachments table and the actual attachment file is stored in the database indicated in the process configuration in the WFAttchamentFiles. The process of migrating attachments requires entering the data into the attachments table in the new database, removing them from the old database, and the last step is to update data in the process definition so that attachments are downloaded from the new database. It is necessary to ensure the right amount of disc space, both for the database and for the log files (2x the size of attachments moved).

Where will the old files be stored?
During the attachment migration process, if you  decide to delete data from the old database, then those attachments will no longer be available. When this step is skipped, duplicate data will remain in the old attachment database.