File API

Indicium's file API allows for files to be streamed to the client. This enhances Indicium's filestorage capabilities:

• The binary data is no longer part of the JSON payload.
• It allows the use of local (browser) caching.
• It no longer loads the data in memory.

File download

When data from a table with one or more file columns is retrieved using a GET request, Indicium will return a JSON object containing the filename and a download url for each file column.

tip

When using database storage it is advised to use the $deselect query parameter to deselect the storage columns when retrieving data from a table. This will reduce the response size drastically and improve performance.

200 OK
{
  "@odata.context": "http://localhost/iam/appl/$metadata#files/$entity",
  "file_id": 1,
  "file": {
    "FileName": "textfile.txt",
    "Url": "iam/appl/files(1)/appl.download_file(file_id=null)"
  }
}

The Url that is retrieved can be used to download a file. This can be done by submitting a GET request.

Request template

GET
/iam/appl/{table}({key})/appl.download_{file_column}

Request example

GET
/iam/appl/files(1)/appl.download_file

If the request is successful, Indicium will return a 200 OK together with the file content as a binary stream.

Image size

When an image is downloaded, additional query parameters can be used to control the returned image size.

Query Parameter	Description
$size	The size of a square size image thumbnail. The size must be given as a whole number, for example $size=100.
$width	Width of the thumbnail, height must also be given.
$height	Height of the thumbnail, width must also be given.

Keep in mind that the image aspect ratio is always respected. Indicium will cache the image based on the requested size.

Caching

The File API uses two caching methods.

The first is regular browser caching by including an ETag header in the response, where the ETag is the requested file's hashcode. For following requests which contain the ETag header, Indicium will verify the ETag, via the If-None-Match header against the current file hashcode. When the file did not change, Indicium will respond with a 304 Not Modified statuscode.

The other caching mechanism saves roundtrips to the server. By using the $eager query parameter, Indicium will use this caching method.

Only use this method when browser caching offers a large advantage, which is in the following situations:

• Calls to Indicium originate from a browser instead of a backend application.
• Images.
• Images that you expect to retrieve regularly.
• A relatively small data set (paged data set). You do not want to apply $eager to a request that retrieves a large number of rows.

$eager can be used for specific columns, for example:

/iam/appl/files(1)?$eager=file

The file hashcode is calculated beforehand and added as file_id function parameter, like so:

/iam/appl/files(1)/appl.download_file(file_id='123456789')

This way the browser is able to cache the url locally.

note

Please be aware that Indicium needs to calculate the file hashcode for each file beforehand, which could potentially delay the first response. However, for files that do not change often, the saved roundtrips will have a greater benefit overall.

Query Parameter	Description
$eager	A comma separated list of column id’s which will have a file_id in the generated download url.

Document preview

Indicium's document preview enables clients to preview various documents by converting files from a data format that a browser cannot preview by default, into a format that a browser can preview (.html or .pdf).

When data from a table is retrieved containing a file column with a data format that is supported by Indicium, then Indicium will return two extra file properties for this file column: PreviewUrl and PreviewSandBoxMode.

200 OK
{
  "@odata.context": "http://localhost/iam/appl/$metadata#files/$entity",
  "file_id": 123,
  "file": {
    "FileName": "wordfile.docx",
    "PreviewUrl": "iam/appl/files(123)/appl.preview_file(file_id=null)",
    "PreviewSandboxMode": "allow-scripts",
    "Url": "iam/appl/files(123)/appl.download_file(file_id=null)"
  }
}

The PreviewUrl is almost the same as the Url that can be used to download a file, but instead of starting with download_{file_column} the PreviewUrl starts with preview_{file_column}.

The PreviewUrl can be used to obtain a file, by submitting a GET request.

The PreviewSandBoxMode contains the sandbox mode for the iframe so it can preview the file safely.

Request template

GET
/iam/appl/{table}({key})/appl.preview_{file_column}

Request example

GET
/iam/appl/files(123)/appl.preview_file

Instead of just giving back the requested file, Indicium will convert the file and return it together with a statuscode 200 OK.

Supported data formats

The following data formats are currently supported by Indicium:

Data format	Will be converted to
Excel: .xlsx and .xls	.html
Word: .docx and .doc	.pdf
PowerPoint: .ppt and .pptx	.pdf
Images: .png, .jpg, .jpeg, .svg and .gif	.html
Email: .msg and .eml	.html
Webpages: .html and .htm	.html
Pdf	.pdf

When a file column has a data format that Indicium does not support, no PreviewUrl and PreviewSandboxMode will be returned when retrieving the data.

File upload

The following code template example illustrates how to upload a file using a POST request, and insert a new record into the database.

POST
/iam/appl/{table}
{
  "col_1": col_value_1,
  "col_2": "col_value_2",
  "my_file_column": {
    "FileName": "value_file_name",
    "File": "value_base_64_binary_string"
  }
}

In this template:

• my_file_column is the database column where value_file_name will be stored.
• value_file_name is the name of the (local) file that will be uploaded and stored.
• value_base_64_binary_string is the binary string with the file contents.

In the model, the my_file_column column is also linked to the actual location where value_base_64_binary_string is stored. This can be either a database column or a file storage location on Azure or AWS.

Convert a file into a BASE 64 binary string

To convert a file into a BASE 64 binary string, you can use the following SQL statement:

DECLARE @value_base_64_binary_string VARBINARY(MAX)
SELECT @value_base_64_binary_string = col
FROM OPENROWSET(
BULK N'<local file name>', SINGLE_BLOB
) Tbl(col)

----------------------- Convert to BASE64
SELECT col
FROM openjson(
  (
  SELECT col
  FROM (SELECT @value_base_64_binary_string AS col) T
  FOR json AUTO
  )
) WITH(col varchar(max))
GO