Working with files

Database specification

Independent of where file data is stored, there is always a record for the file in the system table [dbo].[file], and the record always has values in the following columns:

• filetype = 1

• fileextension

• filename

• size

• timestamp

Example: If the table [dbo].[document] is related to the file-table, you can join the document and file-table on the document column.

Select * from document d inner join file f on d.document = f.idfile

Configurations

Storage types

Lime CRM supports storing file content in several storage locations. The storage location used for a specific file is determined by its storage type property. When new files are created, a default storage location is read from the server configuration. The server configuration can also list storage types that may be overriden on a per-application basis. This is useful in occasions such as overriding the cloud environment default storage with something else.

The following is an example where applications are allowed to override the default storage and use the sql internal file storage in specific applications.

Server configuration (config.yaml):

file:
   default_storage: 's3'
   default_storage_in_app_config:
      - 'sql'

Application configuration (application_config.yaml):

solution-coolsolution:
   config:
      file:
         default_storage: 'sql'

SQL (Default)

This is the default storage type which stores the data in the SQL database. This in an internal storage and means all file data will be backed up when backing up the SQL database for an application.

The data is stored in the [data] column of the [dbo].[file] table and [storage_type] is ‘sql’ or NULL.

Example config:

file:
   default_storage: 'sql'

S3 (Amazon)

Provides an external storage using the Amazon S3 service. This means file data won’t be backed up along with the SQL database backups. The files stored on the S3 service must also be copied when copying a database in order to maintain file data consistency in the new application. This storage type is the default storage provided by the Lime Cloud environment. Services and data are located in the AWS region on Ireland (eu-west-1).

Example config:

file:
   default_storage: 's3'
   s3_storage_bucket: 'name-of-s3-bucket'

OSFS (File system, on disk)

Provides an external storage using the local file system. This means file data won’t be backed up along with the SQL database backups.

To successfully use this storage in production:

• Ensure the designated file system folder is backed up using the same retention policy as being used with the SQL server database in order to achieve full recoverability

• Ensure the app server has write access to the files. Pay extra attention to this when placing the SQL workload on a server separate from the app server

Example config:

file:
   default_storage: 'osfs'
   osfs_storage:
      path: 'files'
      join_webservice_path: True

When a file is stored on disk, the following applies to columns in [dbo].[file].

• [data] = null

• storage_type = osfs

• idreference: file path relative to path configured on application config.

Example: Given the config path ‘c:files’ and the [dbo].[file].[idreference] ‘12345` in the database solution-coolsolution, the data will be stored in the path c:\files\solution-coolsolution\12345.

Sharepoint Online (onedrive)

Storing files in Sharepoint Online enables easy editing of and viewing using the online services provided in Microsoft 365. No direct access to the document library is provided to the Lime CRM users, instead they are given view and edit access by using sharing links on a per-file basis.

To create the sharing links, the lime_file.FileContentService provides these for files that supports it:

content_service = FileContentService(application, file)
print(content_service.supports_edit_link) # True
print(content_service.edit_link) # https://company.sharepoint.com/.....
print(content_service.supports_view_link)
print(content_service.view_link)

These links are also exposed in the File REST API for use in frontend code.

Example configuration

The Sharepoint Online need to be set up on the application level. To have new files uploaded to Sharepoint Online, the default storage needs to be configured to onedrive on the server level or application level. An example showing how to set it up for a single application follows.

Server configuration (config.yaml):

file:
   default_storage_in_app_config:
      - 'onedrive'

Application configuration (application_config.yaml):

solution-coolsolution:
   config:
      file:
         default_storage: 'onedrive'
      onedrive:
         client_id: <client id>
         tenant_id: <tenant id>
         site_id: <site id>
   secrets:
      onedrive:
         client_secret: <client secret>

Migrate existing files

The following steps are required to migrate files from SQL internal storage.

1. Configure the new default_storage

2. Verify new files are stored on the new storage by uploading and downloading a file

3. On-premise: Use limefu to start the migration of existing files (limefu file migrate)

4. Cloud: Use Lime Admin to start the migration of existing files

Please note that several internal file types, such as actionpads, will remain in the internal SQL storage.

Config description

Property	Parent	Default value	Description
default_storage	file	sql	Where files should be stored (sql, s3 or osfs)
s3_storage_bucket	file	lime-filestorage	What bucket to store files when using s3 as storage type
osfs_storage	file	-	Parent with sub properties
path	osfs_storage	files	path to folder to store files on disk, when using osfs
join_webservice_path	osfs_storage	True	If the path property should be relative from webservice_path or not