External data storage

Beginning with 4D v13, you can choose where to save the data of each BLOB, Picture and Text type field. In addition to the existing internal saving options (in the record or in the data file), you can choose to store these fields outside of the data file. In this case, your data is saved as external files that are managed by 4D and can be handled by third-party applications - provided that you take care to maintain data integrity. 

This is transparent for the user: data access is the same regardless of their location. 

External data storage lets you optimize the functioning of the application, for example, by allowing you to transport voluminous data or to access, from the operating system, text or pictures contained in the database, even when it is not open.

Support for external data storage can be carried out in two modes:

• Automatic mode: In this mode, 4D creates and manages a default folder, structured in a specific way and containing all the external storage data. In this case, this data is managed transparently and you have the same functions and automatic functioning as for fields stored internally.
  To enable this automatic mode, you can either:
  
  • Configure external storage in the Structure editor (setting saved with the database structure). This option is described in Stored in the record.
  • Use the SET EXTERNAL DATA PATH command with an empty string in the path parameter (setting valid for the session). 

• Custom mode: In this mode, you are free to choose the place where external files are stored for each field and each record. In this case, 4D maintains only the relation between the field and its data and some of the database mechanisms are no longer available.
  Custom mode is enabled using the SET EXTERNAL DATA PATH command and indicating a folder (other than the default one) in the path parameter.

The following table compares the functions and automatic functioning available in the automatic and custom modes:

Data stored outside of the data file are organized according to the following principles:

• For each record, data is stored in an external file named xxx.txt (Text field), xxx.blob (Blob field) or xxx.jpg, xxx.tiff... (Picture field, whose extension depends on the picture type), where xxx is a unique identifier (UUID) managed by 4D. 

• In automatic mode, all external data is placed in a folder named <DatabaseName>.ExternalData where <DatabaseName> is replaced by the name of the database structure file. This folder is placed next to the database data file (.4DD file).
  Within this folder, 4D creates a folder for each table that has external data (named "Table+table number"), then a subfolder for each external field (named "Field+field number"). The first 100 items of the field are stored at the first level of this folder, the following ones are stored in subfolders numbered beginning with "2", with each subfolder containing up to 100 elements. For example, if field No. 5 of table No. 3 in the "Accounting" database is stored outside the data file, the following tree structure is created by 4D:
  Accounting.4DD
  [Accounting.ExternalData]
      Table3
          Field 5
              Data_1B7F3A 56F6544B45951EFA60426D5ABC.txt
              Data_1B7F3A 56F6544B45951EFA60426D5CCC.txt
              ...
              2
                  Data_2ADBFBA478AAE4409DA9C2D13C90A53B.txt
                  Data_32F8A30B87EE7E4BBC802468D553DC43.txt
                  ...

• In custom mode, the name and location of the folder can be set freely and can be specified separately for each field that is stored externally through the SET EXTERNAL DATA PATH command. This setting is not stored in the database structure.

Recording a field into an external file is done when the record is saved to disk (after validation of the transaction if need be):

• If the external file does not exist, it is created;
• If an external file already exists, 4D replaces it with the new one.
  If you want to keep it, you can either specify a different path (using the SET EXTERNAL DATA PATH command), or use the RELOAD EXTERNAL DATA command in order to load the contents of the field into memory from its external file before saving it to disk again. This option is useful when the file was changed by another application after the record was loaded.

The storage place for the data is a local parameter in each database. When synchronizing or replicating data (see Replication via SQL, these parameters may differ between the local database and the remote one. In this case, the storage complies with the parameters of each database; the synchronization or replication does not change them.

For example, if a Picture field in the remote database is normally saved outside of the data file and this same field in the local database is saved in the data file, when replication occurs, any data added to this field in the local database (in the data file) will still be stored outside of the data file in the remote database.

External storage files can be accessed in read/write by applications other than 4D (operating systems, text or graphical editors, and so on). However, this must be done with precaution because it may alter the functioning of the application:

• If an external storage file is deleted, renamed or moved by the operating system or by a third-party application, 4D considers that the corresponding field has the Null value and the file will be created again (if it is not Null) when the record is saved.
• If you use indexes and the storage files are changed by a third-party application without the parent records being rewritten to disk, the indexes will not be updated.

Note: External Text files are saved in UTF-8 without BOM format. If they are opened by a third-party application and then saved with a BOM, they can still be opened again by 4D but will then be saved without BOM.

Note that loading a record in "read only" does not lock the external files for the fields of this record. These files can still be modified on disk by 4D or by third-party applications, even though their contents are loaded in memory by 4D.