Skip to content

eLegere Changes in Attachments Persistence

Info

The document concerns anyone working directly with the eLegere database without passing through the service.

The changes take effect from 2.13-prerelease.2.

This document explains how eLegere attachments persistence has changed for environments' databases.

Abstract

Differences from Previous Versions

From 2.13-prerelease.2, eLegere 2.13 finally manages:

  • Upload of files above 2 GB
  • Organization and navigation of folders
  • Upload of entire folders

The above features have required changes in how eLegere manages the attachments in the database.

Previously

In versions before 2.13-prerelease.2, each eLegere Storage had a database table named with the following format: STORAGENAME_Attachments.

eLegere before 2.13-prerelease.2 stores both the binary data of the file and the related meta-data in STORAGENAME_Attachments.

A record of the table STORAGENAME_Attachments had the following fields:

After 2.13-prerelease.2

After 2.13-prerelease.2, there are two tables in a eLegere database for each Storage: STORAGENAME_Attachments and STORAGENAME_Attachments_Chunks.

  • STORAGENAME_Attachments now stores only the meta-data of the attachment. (Referred as _Attachments)
  • STORAGENAME_Attachments_Chunks stores the files eventually divided in chunks for optimization reasons. (Referred as _Attachments_Chunks)
_Attachments Table

The new _Attachments table doesn't have the fields Link, BinaryObject, and FileStreamGuid anymore. Link was already deprecated. BinaryObject (now called Data) and FileStreamGuid are moved to the _Attachments_Chunks table.

Instead, the _Attachments table has now the following new fields:

Field Name Description
IsFolder Boolean True if the record is a folder.
FolderId Id of the folder containing the record. NULL if the record is in the root
Size Size of the file
IsDeleted Boolean flagging a file to be deleted. When the user deletes an attachment in the frontend, the deletion is soft: the file remains on the database and IsDeleted takes value True. The database will delete later the record and eLegere won't consider it.
Path String value chaining the Folders' IDs of the tree containing the file. A dot divides each ID. NULL if the record is in the root.
Note on the Path Field

Example

Taking as example the path Home/Invoices/Incoming/012023.pdf, _Attachments records it as:

Id Name ... IsFolder Path
1 Invoices 1 NULL
2 Incoming 1 .1.
3 012023.pdf 0 .1.2.
_Attachments_Chunks Table

Attachments_Chunks stores the files eventually divided in chunks.

A record in _Attachments_Chunks has the following fields:

Field Name Description
Id The record's Id
FileId The Id identifying the file's row with the metadata in _Attachments
FileIndex Numeric that defines the order to rebuild the attachments from the chunks. It must start from 0.
Data The file's binary
FileStreamGuid The filestream value. It's unique and you must generate it through the NEWID() function.
IsDeleted Boolean flagging a file to be deleted. When the user deletes an attachment in the frontend, the deletion is soft: the file remains on the database and IsDeleted takes value True. The database will delete later the record and eLegere won't consider it.

Reading an Attachment

Warning

eLegere might split in chunks the attachments for performance optimization.

This section explains how to access and read an eLegere attachment from the Database.

Interacting with the database's tables directly, you can read any attachment following the steps below:

  1. In the _Attachments table, filter for IsDeleted as False and identify the attachment. Take note of the Id field's value.
  2. Go to the _Attachments_Chunks table.
    • Filter FileId by the value of Id in step (1) and IsDeleted as False.
    • Sort by FileIndex in ascending order.
  3. Reconstruct the data binaries stored in the field Data by following the ascending order of FileIndex strictly.

You will be able to access the row's stored attachment and read it.

Inserting an Attachment

Note

It is not required to split the attachments in chunks during the insertion.

This section explains how to insert an attachment in eLegere directly in the Database.

  1. In the _Attachments table, create a new entry. Keep note of the Id: it will be the value of FileId in the _Attachments_Chunks table. You must record the Path field value with the correct format (see Note on the Path Field).
  2. Go to the _Attachments_Chunks table and insert a new entry. FileIndex value must be 0.

You will have inserted an attachment.