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 Explains the differences before and after 2.13-prerelease.2 version.
- Reading an Attachment Explains how to access and read an attachment after 2.13-prerelease.2.
- Inserting an Attachment Explains how to insert an attachment after 2.13-prerelease.2.
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:
- In the
_Attachments
table, filter forIsDeleted
asFalse
and identify the attachment. Take note of theId
field's value. - Go to the
_Attachments_Chunks
table.- Filter
FileId
by the value ofId
in step (1) andIsDeleted
asFalse
. - Sort by
FileIndex
in ascending order.
- Filter
- Reconstruct the data binaries stored in the field
Data
by following the ascending order ofFileIndex
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.
- In the
_Attachments
table, create a new entry. Keep note of theId
: it will be the value ofFileId
in the_Attachments_Chunks
table. You must record thePath
field value with the correct format (see Note on thePath
Field). - Go to the
_Attachments_Chunks
table and insert a new entry.FileIndex
value must be 0.
You will have inserted an attachment.